数据库增删改查语句

创建数据库：CREAT DATABASE base_name;
删除数据库：DROP database base_name;
创建数据表：CREATE TABLE table_name (column_name column_type);
删除数据表：DROP table table_name；
增加数据：INSERT INTO table_name values（）；
删除数据：DELETE FROM table_name WHERE column_name;
修改数据：UPDATE table_name SET 修改后 where 修改前；
查询数据：SELECT column_name,column_name
FROM table_name
WHERE ；
模糊查询：SELECT * FROM table_name WHERE column_name LIKE'%?' ；
数据分组：GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）
使用形式如下：
SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称；
SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称；

MySQL 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

多表连接

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录：

select * from emp e inner join dept d on d.deptid=e.deptid;

内外连接

内连接和外连接
1、内联接（典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。
2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。
1）LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
3）FULL JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

左右连接

LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录:

select * from dept d left join emp e on d.deptid=e.deptid;

RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录:

select * from emp e right join dept d on d.deptid=e.deptid;

数据库标识符

MySQL事务

事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

数据库优化

SQL编写技巧

SQL编写有以下几个通用的技巧：

• 合理使用索引

索引少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能。选择率高（重复值少）且被where频繁引用需要建立B树索引；
一般join列需要建立索引；复杂文档类型查询采用全文索引效率更好；索引的建立要在查询和DML性能之间取得平衡；复合索引创建时要注意基于非前导列查询的情况

• 使用UNION ALL替代UNION

UNION ALL的执行效率比UNION高，UNION执行时需要排重；UNION需要对数据进行排序，而UNION ALL没有去除重复数据的步骤。

• 避免select * 写法

执行SQL时优化器需要将 * 转成具体的列；每次查询都要回表，不能走覆盖索引。

• JOIN字段建议建立索引

一般JOIN字段都提前加上索引

• 避免复杂SQL语句

提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理

• 避免where 1=1写法

• 避免order by rand()类似写法

RAND()导致数据列被多次扫描

优化案例
表结构

CREATE TABLE a
(
id int(11) NOT NULLAUTO_INCREMENT,
seller_id bigint(20) DEFAULT NULL,
seller_name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
gmt_create varchar(30) DEFAULT NULL,
PRIMARY KEY (id)
);
CREATE TABLE b
(
id int(11) NOT NULLAUTO_INCREMENT,
seller_name varchar(100) DEFAULT NULL,
user_id varchar(50) DEFAULT NULL,
user_name varchar(100) DEFAULT NULL,
sales bigint(20) DEFAULT NULL,
gmt_create varchar(30) DEFAULT NULL,
PRIMARY KEY (id)
);
CREATE TABLE c
(
id int(11) NOT NULLAUTO_INCREMENT,
user_id varchar(50) DEFAULT NULL,
order_id varchar(100) DEFAULT NULL,
state bigint(20) DEFAULT NULL,
gmt_create varchar(30) DEFAULT NULL,
PRIMARY KEY (id)
);
三张表关联，查询当前用户在当前时间前后10个小时的订单情况，并根据订单创建时间升序排列，具体SQL如下

select a.seller_id,
a.seller_name,
b.user_name,
c.state
from a,
b,
c
where a.seller_name = b.seller_name
and b.user_id = c.user_id
and c.user_id = 17
and a.gmt_create
BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create；
查看数据量

原执行时间

原执行计划

初步优化思路

SQL中 where条件字段类型要跟表结构一致，表中 user_id 为varchar(50)类型，实际SQL用的int类型，存在隐式转换，也未添加索引。将b和c表 user_id 字段改成int类型。
因存在b表和c表关联，将b和c表 user_id创建索引
因存在a表和b表关联，将a和b表 seller_name字段创建索引
利用复合索引消除临时表和排序
初步优化SQL

alter table b modify user_id int(10) DEFAULT NULL;
alter table c modify user_id int(10) DEFAULT NULL;
alter table c add index idx_user_id(user_id);
alter table b add index idx_user_id_sell_name(user_id,seller_name);
alter table a add index idx_sellname_gmt_sellid(gmt_create,seller_name,seller_id);
查看优化后执行时间

查看优化后执行计划

查看warnings信息

继续优化alter table a modify "gmt_create" datetime DEFAULT NULL;

查看执行时间

查看执行计划

总结

查看执行计划 explain
如果有告警信息，查看告警信息 show warnings;
查看SQL涉及的表结构和索引信息
根据执行计划，思考可能的优化点
按照可能的优化点执行表结构变更、增加索引、SQL改写等操作
查看优化后的执行时间和执行计划
如果优化效果不明显，重复第四步操作
————————————————