不可错过的MYSQL数据库实用命令

这篇文章主要记录自己平时学习和工作碰到的常用的MYSQL常用命令，后续会持续更新。。。

数据库在运行时的先后顺序

MySQL的语句一共分为11步，如下图所标注的那样，最先执行的总是FROM操作，最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入，只是这些虚拟的表对用户来说是透明的，但是只有最后一个虚拟的表才会被作为结果返回。如果没有在语句中指定某一个子句，那么将会跳过相应的步骤。

(8)SELECT (9) DISTINCT (11)< Top Num> < select list>
(1) FROM [left_table]
(3)<join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH <CUBE | RollUP>
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>

下面我们来具体分析一下查询处理的每一个阶段

1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
2. ON: 对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。
3. JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
4. WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
5. GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
6. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
7. HAVING： 对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中。
8. SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
9. DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.
10. ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
11. LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

数据库连接和修改密码

获取连接

mysql -uroot  -p
mysql -uroot -p123456

查看版本

select version();

修改密码(分5.6和5.7)

5.6版本:

use mysql;
update user set password=password("123456") where user="root";

5.7版本mysql数据库下的user表中已经没有Password字段了,所以要下面这样

update user set authentication_string=password('123456') where user='root' and Host = 'localhost';

如果在windows界面，可以直接在Navicat或者Sqlyog软件直接图形化找到设置密码的地方修改

此外还有两种方式进行设置(不分版本都可以)：

1.没有登录mysql数据库时使用mysqladmin -u用户名 -p旧密码 password 新密码

mysqladmin -uroot -p123 password 123456 ;

2.set password for root@localhost = password('123456');

数据库备份和恢复

数据备份

#备份表名：原表名_当天日期
CREATE TABLE backup_tables.t_demo_20190605 LIKE t_demo;                                    
#备份数据 
INSERT INTO backup_tables.t_demo_20190605
SELECT * FROM t_demo WHERE  recpt_no ='SKD20181105234083';                               

或者：

 #备份表名：临时表名_当天日期
create table backup_tables.t_loan_operate_record_20190605(                               
apply_id bigint comment "appid",
operate_time datetime comment "time",
primary key (apply_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='样例表';

 #备份数据
INSERT INTO backup_tables.t_loan_operate_record_20190605(apply_id,operate_time)
select lor.apply_id,max(lor.operate_time) operate_time from t_loan_operate_record lor where lor.first_state=12 group by lor.apply_id;         

mysqldump的使用

mysqldump 是 MySQL 自带的逻辑备份工具。

它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。

命令格式

mysqldump [选项] 数据库名 [表名] > 脚本名

或

mysqldump [选项] --数据库名 [选项 表名] > 脚本名

或

mysqldump [选项] --all-databases [选项]  > 脚本名

选项说明

参数名	缩写	含义
–host	-h	服务器IP地址
–port	-P	服务器端口号
–user	-u	MySQL 用户名
–pasword	-p	MySQL 密码
–databases		指定要备份的数据库
–all-databases		备份mysql服务器上的所有数据库
–compact		压缩模式，产生更少的输出
–comments		添加注释信息
–complete-insert		输出完成的插入语句
–lock-tables		备份前，锁定所有数据库表
–no-create-db/–no-create-info		禁止生成创建数据库语句
–force		当出现错误时仍然继续备份操作
–default-character-set		指定默认字符集
–add-locks		备份数据库表时锁定数据库表

实例

1、备份所有数据库：

mysqldump -uroot -p --all-databases > /backup/mysqldump/all.db

2、备份指定数据库：

mysqldump -uroot -p test > /backup/mysqldump/test.db

3、备份指定数据库指定表(多个表以空格间隔)

mysqldump -uroot -p  mysql db event > /backup/mysqldump/2table.db

4、备份指定数据库排除某些表

mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.db

还原命令

1、系统行命令

mysqladmin -uroot -p create db_name 
mysql -uroot -p  db_name < /backup/mysqldump/db_name.db

注：在导入备份数据库前，db_name如果没有，是需要创建的； 而且与db_name.db中数据库名是一样的才可以导入。

2、soure 方法

mysql > use db_name
mysql > source /backup/mysqldump/db_name.db

数据修改

#简单sql直接更新(推荐)
UPDATE t_offline_receipt  SET  is_business = 3  where   recpt_no ='SKD20181105234083';    或
UPDATE t_demo a
INNER JOIN backup_tables.t_demo_20171130 b ON a.id=b.id        #主键相等                    SET a.is_business = 3
where  1 = 1;
或
UPDATE t_demo
INNER JOIN backup_tables.t_demo_20171130 b ON t_demo.id=b.id   #主键相等                    SET t_demo.is_business = 3
where  1 = 1;

#主键相等，删除的表不允许用别名                    
DELETE t_demo
FROM t_demo INNER JOIN backup_tables.t_demo_20190605 b ON t_demo.id=b.id    
where  1 = 1;

数据恢复

#update误更新数据通过备份表恢复
UPDATE t_demo a
INNER JOIN backup_tables.t_demo_20190605 b ON a.id=b.id             #主键相等
SET a.is_business = b.is_business                                   #原表字段值=备份表字段值
where a.col_name1 = 1;

#delete误删数据通过备份表恢复
insert into t_demo(id, name, en_name, is_business, content, create_emp, create_time, modify_emp, modify_time)
select id, name, en_name, is_business, content, create_emp, create_time, modify_emp, modify_time
from backup_tables.t_demo_20190605
where 1=1;

DDL

数据库：

创建数据库：create databases;

删除数据库：drop databases;

选择数据库：use mysql;

数据表：

创建表：

CREATE TABLE t_demo (                                                                                           #表名t_开头 (表名和字段名必须小写)
  id BIGINT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',                                            #标准格式
  name VARCHAR(32) NOT NULL COMMENT '名称',                                                     
  en_name VARCHAR(50) COMMENT '英文名称',                                                                       #可为空列
  is_business TINYINT(3) NOT NULL DEFAULT 0 COMMENT '是否营运(0否，1是)',
  content TEXT COMMENT '内容',                                                                                  #text字段不能为not null
  create_emp INT(20) COMMENT '创建人',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',                                   #时间类型默认值CURRENT_TIMESTAMP
  modify_emp BIGINT(20) COMMENT '修改人',
  modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (id),                                                                                             #主键字段(表必须有主键)
  KEY idx_create_time (create_time),                                                                            #普通索引(命名规范：idx_字段名)
  UNIQUE KEY uniq_name (NAME)                                                                                   #唯一索引(命名规范：uniq_字段名)，尽可能避免使用唯一索引，容易引起性能问题和死锁
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='样例表';    

删除表：drop table t_demo

修改表：

添加索引：

ALTER TABLE t_demo ADD INDEX idx_modify_time(modify_time);

修改列：

#修改字段请使用 modify，不使用 change
ALTER TABLE t_demo MODIFY COLUMN en_name VARCHAR(200) DEFAULT NULL COMMENT '英文名称';  
#mysql5.7及以下版本修改字符集
ALTER TABLE t_demo MODIFY en_name VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '三方退款原因名称'; 

添加列

ALTER TABLE t_demo ADD col_name1 TINYINT COMMENT '列说明';
或
#非空列：not null必须显示指定默认值
ALTER TABLE t_demo ADD col_name1 TINYINT NOT NULL DEFAULT 1 COMMENT '列说明';             

注意：

mysql中对not null类型的默认值如下：

int 或 bigint 默认值为 0

varchar 默认值为 ‘’

datetime 默认值为 ‘0000-00-00 00:00:00’ (实际使用中建议使用非零日期，例：‘2000-01-01 00:00:00’)

timestamp 默认值为 当前时间

DML

这里是我创建的三个表的脚本t_student，t_class，t_teacher

CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

CREATE TABLE `t_teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT

insert 一条：

insert into t_student values(1,'aa',18,1)

或者是指定对应列名

insert into t_student(id,sname) values(1,'aa')

insert多条：

insert into t_student values(1,'aa',15,2),(2,'bb',16,1)

从其他表复制： t_student_bak和t_student表的结构一致

create table t_student_bak like t_student

insert into t_student
select * from t_student_bak

上面这种方式经常在备份表时常用，备份时还有一种方式：

create table t_student_bak(select * from t_student)

UPDATE

简单更新

update t_student set age=15 where id=2

关联更新： 将教师表的年龄查询出来更新到学生表，关联条件是id，并且学生id=1

update t_student ts,t_teacher tt  set ts.age=tt.age
where ts.id=tt.id and ts.id=1

或者是

update t_student ts INNER JOIN t_teacher tt on ts.id=tt.id
set ts.age=tt.age  where ts.id=1

DELETE/TRUNCATE

区别：

• delete删除的时候就是一条一条的删除记录，它可以配合事务，将删除掉的数据找回。 roolback

• truncate删除，它是将整个表摧毁，然后再创建一张一模一样的表，它删除的数据无法找回。

• delete删除uid不会重置；再使用truncate操作，uid会重置。因为它删除了表结构，然后再创建一张一模一样的表，所以再次插入数据的时候，会从1开始。

• truncate不能引发触发器

delete：

简单删除：

delete from t_student  where id=1

关联删除： 将教师表和学生表中id关联相等的并且id=1的关联删除

如果只要删除教师表的，则delete后面接的表名为tt就好

delete ts,tt from t_student ts,t_teacher tt 
where tt.id=ts.id  and tt.id=1

或者是

delete ts,tt from t_student ts INNER JOIN  t_teacher tt  on tt.id=ts.id 
where tt.id=1

上面的from后面如果写了别名，delete后面要写的是别名，不能delete t_student,不然会报错：

ERROR 1109 (42S02): Unknown table 'xxx' in MULTI DELETE

truncate：

只能全表删除，不能带条件

truncate table t_student

DQL

SELECT

CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

CREATE TABLE `t_class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INNER/LEFT/RIGHT JOIN

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）： 获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

INNER JOIN

INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以下两张表来读取t_student表中所有class_id字段在t_classl表对应的id字段值：

1.
select * from t_student  ts
INNER JOIN	t_class tc
ON ts.class_id =tc.id
-- 等价于
2.
select * from t_student ts,t_class tc
where ts.class_id=tc.id

第二种方式会把所有需要连接的表都放到 FROM 之后，然后在 WHERE 中写明连接的条件。而 第一种 在这方面更灵活，它不需要一次性把所有需要连接的表都放到 FROM 之后，而是采用 JOIN 的方式，每次连接一张表，可以多次使用 JOIN 进行连接。

所以，建议多表连接使用 第一种 标准，因为层次性更强，可读性更强，比如：

SELECT ...
FROM table1
    JOIN table2 ON table1和table2的连接条件
        JOIN table3 ON table2和table3的连接条件

它的嵌套逻辑类似我们使用的 FOR 循环：

for t1 in table1:
    for t2 in table2:
       if condition1:
           for t3 in table3:
              if condition2:
                  output t1 + t2 + t3

采用的这种嵌套结构非常清爽，即使再多的表进行连接也都清晰可见。如果采用 第二种，可读性就会大打折扣。

LEFT JOIN

以下实例中使用了 LEFT JOIN，该语句会读取左边的数据表 t_student 的所有选取的字段数据，即便在右侧表 t_class中 没有对应的字段值，没有会用null值标识。

SELECT ts.*,tc.* FROM `t_student` ts 
left join  t_class tc 
on ts.class_id=tc.id
--这里左连接其实只要把连接的表顺序换一下，再将LEFT JOIN 换成RIGHT JOIN,结果是一致的！
--table1 RIGHT JOIN table2==table2 LEFT JOIN table1
--右连接同理
-- 等价于
SELECT ts.*,tc.* FROM t_class tc
RIGHT join   `t_student` ts
on ts.class_id=tc.id

RIGHT JOIN

以下实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 t_class的所有选取的字段数据，即便在左侧表 t_student 中没有对应的字段值，没有会用null值标识。

SELECT  tc.*,ts.* FROM `t_student` ts 
RIGHT  join  t_class tc 
on ts.class_id=tc.id
-- 等价于
SELECT  tc.*,ts.* FROM t_class tc
LEFT  join   `t_student` ts 
on ts.class_id=tc.id

子查询和自连接

• 子查询（Sub Query）或者说内查询（Inner Query），也可以称作嵌套查询（NestedQuery），是一种嵌套在其他 SQL 查询的 WHERE 子句中的查询。
• 子查询用于为主查询返回其所需数据，或者对检索数据进行进一步的限制。
• 子查询可以在 SELECT、INSERT、UPDATE 和 DELETE 语句中，同 =、<、>、>=、<=、IN、BETWEEN 等运算符一起使用。

使用子查询必须遵循以下几个规则：

• 子查询必须括在圆括号中。

• 子查询的 SELECT 子句中只能有一个列，除非主查询中有多个列，用于与子
  查询选中的列相比较。

• 子查询不能使用 ORDER BY，不过主查询可以。在子查询中，GROUP BY 可
  以起到同 ORDER BY 相同的作用。

• 返回多行数据的子查询只能同多值操作符一起使用，比如 IN 操作符。

• SELECT 列表中不能包含任何对 BLOB、ARRAY、CLOB 或者 NCLOB 类
  型值的引用。

• 子查询不能直接用在集合函数中。

• BETWEEN 操作符不能同子查询一起使用，但是 BETWEEN 操作符可以用
  在子查询中。

关联子查询： 在关联子查询中，对于外部查询返回的每一行数据，内部查询都要执行一次。另外，关联子查询的信息流是双向的，外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录，之后外部查询根据返回的记录做出决策。

SELECT * from t_student ts WHERE EXISTS(SELECT * FROM t_class tc WHERE ts.class_id=tc.id )

1、先执行外层查询

2、再执行内层查询

非关联子查询： 非相关子查询是独立于外部查询的子查询，子查询执行完毕后将值传递给外部查询。

SELECT * from t_student ts WHERE class_id in (SELECT MIN(id) FROM t_class)

1、先执行内层查询

2、再执行外层查询

表数据如下：

select子查询

SELECT * FROM `t_student` 
WHERE class_id in( SELECT id FROM t_class WHERE id = 1 )

insert子查询

INSERT INTO t_student_bak
SELECT * from t_student
WHERE class_id in( SELECT id FROM t_class WHERE id = 1 )

update子查询

UPDATE t_student set sname='hhh'
WHERE class_id in ( SELECT id FROM t_class WHERE id = 1 )

delete子查询

DELETE FROM t_student 
WHERE class_id in ( SELECT id FROM t_class WHERE id = 1 )

自连接：
TODO。。。

COUNT(*)/COUNT(1)/COUNT(字段)

1、在MySQL InnoDB存储引擎中，COUNT(*)和COUNT(1)都是对的所有结果进行的COUNT。如果有WHERE子句，则是对所有符合筛选条件的数据行进行统计。如果没有WHERE子句，则是对数据表的数据行数进行统计。因此COUNT(*)和COUNT(1)本质上没有区别，执行的复杂度都是O(N)，也就是采用全表扫描，进行循环+计数的方式进行统计。

2、如果是MySQL MyISAM存储引擎，统计数据表的行数只需要O(1)复杂度，这是因为每张MyISAM的数据表都有一个meta信息有存储了row_count值。而一致性由表级锁来保证。

而InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MyISAM一样，只维护一个row_count变量。因此就需要采用扫描全表，进行循环+计数的方式来完成统计。
3、要注意的是，在实际执行中COUNT(*)和COUNT(1)执行时间可能略有差别，不过你还是可以把这两个在执行效率上看成是相等的。

4、另外在InnoDB引擎中，如果是采用COUNT(*)和COUNT(1)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。
5、对于查找具体的行来说，采用主键索引效率更高。而对于COUNT(*)和COUNT(1)这种，不需要查找具体的行，只是统计行数来说，系统会自动采用占用空间更小的二级索引来进行统计。
6、如果有多个二级索引的时候，会使用key_len小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

优化总结：
1、一般情况下：COUNT(*) = COUNT(1) > COUNT(字段),所以尽量使用COUNT(*)，当然如果你要统计的是就是某个字段的非空数据行数，那另当别论。毕竟执行效率比较的前提是要结果一样才行。
2、如果要统计COUNT(*)，尽量在数据表上建立二级索引，系统会自动采用key_len小的二级索引进行扫描，这样当我们使用SELECT COUNT(*)的时候效率就会提升，有时候提升几倍甚至更高都是有可能的。

去重DISTINCT

t_student表数据

我们对sname字段去重

SELECT DISTINCT sname FROM t_student

但是如果我们再加上一个age字段呢？

SELECT DISTINCT sname,age FROM `t_student`

如果换成这样的写法呢？SELECT age, DISTINCT sname FROM t_student语句是否正确？

这里我们要注意：

1.DISTINCT 需要放到所有列名的前面，如果写成SELECT age, DISTINCT sname FROM t_student会报错。

2.DISTINCT 其实是对后面所有列名的组合进行去重，你能看到最后的结果是 4 条，因为这 4个年龄不同，都有sname这个属性值。如果你想要看都有哪些不同的姓名，只需要写DISTINCT sname即可，后面不需要再加其他的列名了。

GROUP BY+WITH ROLLUP+HAVING

CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');

+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+

我们使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录,并且使用HAVING过滤每个人记录总数大于6的：

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计

 SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP HAVING SUM(singin)>6;

其中记录 NULL 表示所有人的登录次数。

我们可以使用 coalesce来设置一个可以取代 NUll 的名称，coalesce 语法

select coalesce(a,b,c);

参数说明： 如果a= =null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

以下实例中如果名字为空我们使用总数代替：

SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

UNION/UNION ALL

union 中的每个 select 语句必须具有相同的列数
• 这些列也必须具有相似的数据类型
• 每个 SELECT 语句中的列也必须以相同的顺序排列

union语句：对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序;(去重和排序操作)

union all 语句：对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;(不去重排序)

所以： union all的执行效率要比union高

这里用上面的t_student和t_teacher表为例：

SELECT snameFROM t_student union SELECT tnamefrom t_teacher

SELECT sname FROM `t_student`  union all SELECT tname from t_teacher

这里select后面的字段只要类型一样就可以了，不必字段名称要一样!

注意：

1.使用UNION命令时我们需要注意，只能在最后使用一个ORDER BY命令，是将两个查询结果合在一起之后，再进行排序！绝对不能写两个ORDER BY命令。另外，在使用ORDER BY排序时，注意两个结果的别名保持一致，使用别名排序很方便。当然也可以使用列数。

2.UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。 一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL

3.有时候就一定要用union all计算重复，不然比如两个count数量一样，sum总数的时候就会不一致,比如count（*）都是2的话，sum总数就是2因为去重了，但是不去重的话，总数应该是2+2=4

SELECT 
  SUM(o) FROM 
  (SELECT 
    COUNT(*) o
  FROM
    `t_count_date` 
  WHERE id IN (2, 3) 
  UNION 
  SELECT 
    COUNT(*) o
  FROM
    `t_count_date` 
  WHERE id IN (6, 7, 10))  

IS NULLl/IS NOT NULL/ISNULL()

• IS NULL: 当列的值是 NULL,此运算符返回 true。
• IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL

t_teacher表的数据如下：

这里直接使用=比较：

SELECT * FROM `t_teacher`  where  age=null

使用is null

SELECT * FROM `t_teacher`  where  age is null

这里还可以使用isnull()函数,结果和is null一致！

SELECT * FROM `t_teacher`  where  isnull(age)

注意：MySQL 字段属性应该尽量设置为 NOT NULL

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议，请往下看。

1、 首先，我们要搞清楚空值 “” 和 NULL 的概念：

• 1）空值是不占用空间的
• 2）MySQL中的NULL其实是占用空间的

所谓的 NULL 就是什么都没有，连 \0 都没有，\0 在字符串中是结束符，但是在物理内存是占空间的，等于一个字节，而 NULL 就是连这一个字节都没有。

2、 其次，在数据库里是严格区分的，任何数跟 NULL 进行运算都是 NULL, 判断值是否等于 NULL，不能简单用 =，而要用 IS NULL关键字。

3、 数据库的字段 col1 设为 NOT NULL, 仅仅说明该字段不能为 NULL, 也就是说只有在:

INSERT INTO table1(col1) VALUES(NULL);

这种情况下数据库会报错，而:

INSERT INTO table1(col1) VALUES('');

不会报错。

（如果字段是自增ID，第一句不会报错，这不能说明是可以为NULL,而是 数据库系统会根据ID设的缺省值填充，或者如果是自增字段就自动加一等缺省操作。）

4、 含有空值的列很难进行查询优化，而且对表索引时不会存储 NULL 值的，所以如果索引的字段可以为 NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用 0、一个特殊的值或者一个空串代替空值。

5、 联表查询的时候，例如 LEFT JOIN table2，若没有记录，则查找出的 table2 字段都是 null。假如 table2 有些字段本身可以是 null，那么除非把 table2 中 not null 的字段查出来，否则就难以区分到底是没有关联记录还是其他情况。

IN和EXISTS

TODL。。。。

实用SQL笔记

在编写SQL时，灵活运用一些技巧，可以大大简化程序逻辑。

插入或替换

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。此时，可以使用REPLACE语句，这样就不必先查询，再决定是否先删除再插入：

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。

插入或更新

可参考：https://www.cnblogs.com/east7/p/11706729.html

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句：

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。

插入或忽略

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO ...语句：

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。

快照

如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT：

-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

新创建的表结构和SELECT使用的表结构完全一致。

写入查询结果集

如果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。

例如，创建一个统计成绩的表statistics，记录各班的平均成绩：

CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

然后，我们就可以用一条语句写入各班的平均成绩：

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

确保INSERT语句的列和SELECT语句的列能一一对应，就可以在statistics表中直接保存查询的结果：

> SELECT * FROM statistics;
+----+----------+--------------+
| id | class_id | average      |
+----+----------+--------------+
|  1 |        1 |         86.5 |
|  2 |        2 | 73.666666666 |
|  3 |        3 | 88.333333333 |
+----+----------+--------------+
3 rows in set (0.00 sec)

强制使用指定索引

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。例如：

> SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

指定索引的前提是索引idx_class_id必须存在。

事务理解

在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。例如，一个转账操作：

-- 从id=1的账户给id=2的账户转账100元
-- 第一步：将id=1的A账户余额减去100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步：将id=2的B账户余额加上100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

这两条SQL语句必须全部执行，或者，由于某些原因，如果第一条语句成功，第二条语句失败，就必须全部撤销。

这种把多条语句作为一个整体进行操作的功能，被称为数据库事务。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

可见，数据库事务具有ACID这4个特性：

• A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
• C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
• I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
• D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。

要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务，例如，把上述的转账操作作为一个显式事务：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

很显然多条SQL语句要想作为一个事务执行，就必须使用显式事务。

COMMIT是指提交事务，即试图把事务内的所有SQL所做的修改永久保存。如果COMMIT语句执行失败了，整个事务也会失败。

有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

数据库事务是由数据库系统保证的，我们只需要根据业务逻辑使用它就可以。

隔离级别

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：

Isolation Level	脏读（Dirty Read）	不可重复读（Non Repeatable Read）	幻读（Phantom Read）
Read Uncommitted	Yes	Yes	Yes
Read Committed	-	Yes	Yes
Repeatable Read	-	-	Yes
Serializable	-	-	-

我们会依次介绍4种隔离级别的数据一致性问题。

小结

数据库事务具有ACID特性，用来保证多条SQL的全部执行。

读未提交（Read Uncommitted）

Read Uncommitted是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。

我们来看一个例子。

首先，我们准备好students表的数据，该表仅一行记录：

mysql> select * from students;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

然后，分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：

时刻	事务A	事务B
1	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2	BEGIN;	BEGIN;
3	UPDATE students SET name = ‘Bob’ WHERE id = 1;
4		SELECT * FROM students WHERE id = 1;
5	ROLLBACK;
6		SELECT * FROM students WHERE id = 1;
7		COMMIT;

当事务A执行完第3步时，它更新了id=1的记录，但并未提交，而事务B在第4步读取到的数据就是未提交的数据。

随后，事务A在第5步进行了回滚，事务B再次读取id=1的记录，发现和上一次读取到的数据不一致，这就是脏读。

可见，在Read Uncommitted隔离级别下，一个事务可能读取到另一个事务更新但未提交的数据，这个数据有可能是脏数据。

读已提交（Read Committed）

在Read Committed隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。

不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。

我们仍然先准备好students表的数据：

mysql> select * from students;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

然后，分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：

时刻	事务A	事务B
1	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2	BEGIN;	BEGIN;
3		SELECT * FROM students WHERE id = 1;
4	UPDATE students SET name = ‘Bob’ WHERE id = 1;
5	COMMIT;
6		SELECT * FROM students WHERE id = 1;
7		COMMIT;

当事务B第一次执行第3步的查询时，得到的结果是Alice，随后，由于事务A在第4步更新了这条记录并提交，所以，事务B在第6步再次执行同样的查询时，得到的结果就变成了Bob，因此，在Read Committed隔离级别下，事务不可重复读同一条记录，因为很可能读到的结果不一致。

可重复读（Repeatable Read）

在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。

幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。

我们仍然先准备好students表的数据：

mysql> select * from students;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

然后，分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：

时刻	事务A	事务B
1	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2	BEGIN;	BEGIN;
3		SELECT * FROM students WHERE id = 99;
4	INSERT INTO students (id, name) VALUES (99, ‘Bob’);
5	COMMIT;
6		SELECT * FROM students WHERE id = 99;
7		UPDATE students SET name = ‘Alice’ WHERE id = 99;
8		SELECT * FROM students WHERE id = 99;
9		COMMIT;

事务B在第3步第一次读取id=99的记录时，读到的记录为空，说明不存在id=99的记录。随后，事务A在第4步插入了一条id=99的记录并提交。事务B在第6步再次读取id=99的记录时，读到的记录仍然为空，但是，事务B在第7步试图更新这条不存在的记录时，竟然成功了，并且，事务B在第8步再次读取id=99的记录时，记录出现了。

可见，幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。

序列化（Serializable）

Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

默认隔离级别

如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read。

TODO。。。后续补充

参考文章：
https://www.liaoxuefeng.com/wiki/1177760294764384