MySQL字段名规范

9.2 Schema Object Names
https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

标识符可以用反引号引住，也可以不引住，如果标识符包含特殊字符或者本身是保留字，则必须用反引号引住。
MySQL 内部使用 Unicode(UTF-8) 保存标识符，在 BMP(Basic Multilingual Plane) 中的 Unicode 字符可出现在标识符中，但增补字符不允许。

1、允许出现在无反引号标识符中的字符：

ASCII 字符，


    [0-9,a-z,A-Z$_]

大小写字母、数字、dollar符、下划线

U+0080 到 U+FFFF 的 Unicode 字符

2、允许出现在反引号标识符中的字符，包括除了 U+0000 外的全部 BMP 字符：

U+0001 到 U+007F 的 ASCII 字符

U+0080 到 U+FFFF 的 Unicode 字符

3、ASCII NUL (U+0000) 字符和高于 U+10000 的增补字符不允许出现在标识符中，无论是否带反引号。

4、 标识符可以以数字开头。除非反引号引住否则不允许全数字的标识符。

5、库名、表名、列表不能以空字符结尾。

全数字列名必须反引号引起来

表名、列名可以是全数字，但必须用反引号引起来，否则会报错

CREATE TABLE `474274538` (
    id     BIGINT(20) NOT NULL AUTO_INCREMENT,
    `123`  VARCHAR(255),
    PRIMARY KEY (id)
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
表名最长64字符
9.2.1 Identifier Length Limits
https://dev.mysql.com/doc/refman/5.7/en/identifier-length.html
数据库、表、列和索引的名称最长可达64个字符。别名最长可达256个字符。
用函数名做列名时必须反引号引住
下面的 sql 会报错，因为添加的列名 current_time 是 MySQL 的一个 函数名

alter table t1 add column current_time varchar(255);
报错：
[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘current_time varchar(255)’ at line 1
解决：
使用反引号将 current_time 引起来：
alter table t1 add column `current_time` varchar(255);
DQL
select [ all | distinct ]  <目标列表达式 | 聚集函数>[别名]...
from <表名或视图名>[别名]...
[ where <条件表达式> ]
[ group by <列名> [having <条件表达式>]  ]
[ order by <列名> [ASC | DESC] ]
SQL优化

count(*)慢优化
对于没有过滤条件的 count(*)
MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个数，效率很高；
而 InnoDB 引擎就麻烦了，它执行 count() 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

id分段count后求和
sql1: select count(*) from user where score>90;
优化为按主键id分段count后相加
sql2:

select sum(cnt) from (
select count(*) cnt from table_a where id<1000000 and age is not null
union all
select count(*) cnt from table_a where id>=1000000 and id<2000000 and age is not null
2亿8千万数据，符合条件的约3000万
sql1 执行了6个小时还没结束
非精确count-information_schema.tables
从 information_schema.tables 查询 TABLE_ROWS
对于 InnoDB 存储引擎来说是个近似值。
select TABLE_ROWS from information_schema.tables where table_schema=’db_name’ and table_name=’table_a’;

非精确-show table status like
本质和从 information_schema.tables 查询 TABLE_ROWS 一样，对于 InnoDB 存储引擎来说是个近似值。

mysql> show table status like 'my_app_table_11111';
+--------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name               | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+--------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| my_app_table_11111 | InnoDB |      10 | Dynamic    | 145394 |           4536 |   659570688 |               0 |     28540928 |   7340032 |         340429 | 2021-06-27 21:49:33 | 2021-07-14 17:02:52 | NULL       | utf8_general_ci |     NULL |                |         |
+--------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
非精确count-解析explain扫描行数
解析 explain 输出的 rows 字段

mysql> explain select * from vehicle_four_wheel;
+----+-------------+--------------------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table              | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | table_my_app       | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 140755 |   100.00 | NULL  |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+--------+----------+-------+
count(distinct)优化
sql1: select count(distinct person_no) from user where score>90;
优化为先查出distinct编号，再count
sql2: select count(*) from (select distinct(person_no) from user where score>90) t;

2亿8千万数据，符合条件的有大约1千万数据
sql1 执行了6个小时还没结束
sql2半小时执行完了。
int和varchar类型索引
当MySQL中字段为 int 类型时，搜索条件 where num=’123’ 与 where num=123 都可以使用该字段的索引。
当MySQL中字段为 varchar 类型时，搜索条件 where num=’123’ 可以使用索引，where num=123 不可以使用索引。

所以：字段类型不同会有隐式类型转换，可能导致索引失效。
这一点也是MySQL int和varchar类型选择的一个依据：
1、除了 int 效率较高之外。
2、int类型字段建索引后可以匹配int和varchar条件，但varchar类型字段建索引后只能匹配varchar条件
如果某个字段存储的是int，那就在MySQL中定义为int，而不要使用varchar。
去掉表字段上的unix_timestamp时间函数
根据更新时间查询 user 报慢查询，DBA 建议不要在 表字段 上使用函数，会导致无法使用索引。
不要在表字段上用函数，会用不到索引，在参数上用或者直接转换下参数

MariaDB [db]> explain select id from user where unix_timestamp(update_time) >= 1571673600 and unix_timestamp(update_time) < 1571760000 ;
+------+-------------+-------+-------+---------------+-------------+---------+------+--------+--------------------------+
| id   | select_type | table | type  | possible_keys | key         | key_len | ref  | rows   | Extra                    |
+------+-------------+-------+-------+---------------+-------------+---------+------+--------+--------------------------+
|    1 | SIMPLE      | user  | index | NULL          | update_time | 4       | NULL | 208320 | Using where; Using index |
+------+-------------+-------+-------+---------------+-------------+---------+------+--------+--------------------------+
1 row in set (0.012 sec)
MariaDB [db]> explain SELECT id FROM user WHERE update_time >= '2019-10-22 00:00:00.0(Timestamp)' AND update_time < '2019-10-23 00:00:00.0(Timestamp)';
+------+-------------+-------+-------+---------------------------+-------------+---------+------+--------+--------------------------+
| id   | select_type | table | type  | possible_keys             | key         | key_len | ref  | rows   | Extra                    |
+------+-------------+-------+-------+---------------------------+-------------+---------+------+--------+--------------------------+
|    1 | SIMPLE      | user  | index | update_time   | update_time | 4       | NULL | 208320 | Using where; Using index |
+------+-------------+-------+-------+---------------------------+-------------+---------+------+--------+--------------------------+
1 row in set (0.010 sec)
原因
SQL语句where中如果有functionName(colname)或者某些运算，则MYSQL无法使用基于colName的索引。使用索引需要直接查询某个字段。
索引失效的原因是索引是针对原值建的二叉树，将列值计算后，原来的二叉树就用不上了；
为了解决索引列上计算引起的索引失效问题，将计算放到索引列外的表达式上。
create_time排序改为id排序避免回表
假如 user 表使用自增 id 作为主键，且创建后 create_time 不会变，则 id 排序和 create_time 排序的结果是一样的，可使用 id 排序代替 create_time 排序。
因为 create_time 上即使有索引也是二级索引，需要回表找到主键 id 才行。

offset分页改写为where id条件
如果像下面这样循环分页查，随着 offset 的增加，查询会变的越来越慢，因为 mysql 是查出匹配条件的全部数据后再切分的。

select *
from user
where xx=xx
order by xx
limit $offset, 100;
正确的循环分页查询方法应该是把 offset 转换为 where id 条件，每次不断改变条件，使用 where id 条件和 limit 来分页，不要使用偏移量 offset。
典型的比如使用 id，每次记录上一页最后一个id当做条件
select *
from user
where id > $last_id
order by id
limit 100;
比如对外提供一个按 create_time 时间范围分页查询的接口
queryByCreateTimePageable(String t1, String t2, long offset, int count);
本来sql如下
SELECT *
FROM table t
WHERE create_time >= $t1
  AND create_time <= $t2
ORDER BY id ASC
LIMIT $offset, $count;
可以内部对其进行优化，将offset分页改写为 where id 条件
SELECT *
FROM table t
WHERE create_time >= t1
  AND create_time <= t2
  AND id >= (SELECT ID
             FROM table st force index (primary)
             WHERE create_time >= t1
               AND create_time <= t2
             ORDER BY id ASC
             LIMIT $offset,1)
ORDER BY id ASC
LIMIT $count;
两表关联的连接表如何建索引
user 人员表（id, name, birthday）
user_tag 人员标签表（id, user_id, tag_id）一个人可以有多个标签
tag 标签表 (id,name)
根据标签id列表查询人员并按生日倒序排序特别慢

select distinct u.*
from user u join user_tag ut on u.id=ut.user_id
where ut.tag_id in (1,2,3)
order by u.birthday desc limit 10;
数据量：200万 user，300万 user_tag，这种索引该怎么建？
最终建立的索引
user_tag 表
1、在 user_id 列上创建索引，为了加速”查某人的标签”
2、在 (tag_id, user_id) 列上创建联合索引，为了查某标签下有多少人
alter table user_tag
add index idx_user_id(user_id),
add index idx_tag_user(tag_id, user_id);
网上有人做了实验，结果是：关联表分别创建 user_id 与 tag_id 的单列索引 idx_user, idx_tag 最优。
MySQL两表关联的连接表该如何创建索引？
https://www.jianshu.com/p/0ec5b4dedc1a
干预索引
use index(a,b) 限制索引范围
有时我们在数据表里建立了很多索引，当MySQL对索引进行选择时，这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引，而不是全部的索引，这就需要用到USE INDEX对查询语句进行设置。
SELECT * FROM TABLE1 USE INDEX (FIELD1, FIELD2) …
无论在TABLE1中已经建立了多少个索引，MySQL在选择索引时，只考虑在FIELD1和FIELD2上建立的索引。

ignore index(a,b) 忽略索引
如果我们要考虑的索引很多，而不被使用的索引又很少时，可以使用IGNORE INDEX进行反向选取。在上面的例子中是选择被考虑的索引，而使用IGNORE INDEX是选择不被考虑的索引。
SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …
在上面的SQL语句中，TABLE1表中只有FIELD1和FIELD2上的索引不被使用。

force index(a) 强制使用索引
有时我们希望MySQL必须要使用某一个索引(由于MySQL在查询时只能使用一个索引，因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。
SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …
以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。

like
like binary 区分大小写
MySQL 的 like 查询是不区分大小写的
有时候，我们需要区分大小写的是，该怎么办呢？




    

一、一种方法是在查询时指定大小写敏感，在 like 的后面加个 binary 就可以了
select *
from user
where name like binary '%aaa%'
二、另一种方法是建表时可以设置表或行的 collation, 使其为 binary 或 case sensitive. 在 MySQL 中，对于 Column Collate 其约定的命名方法如下：
*_bin: 表示的是binary case sensitive collation，也就是说是区分大小写的
*_cs: case sensitive collation，区分大小写
*_ci: case insensitive collation，不区分大小写
like escape 转义通配符
like 子句中 % 匹配多个字符， _ 匹配单个字符。
如果要匹配的内容本身包含 % 和 _ 怎么办呢？

可以使用 escape 指定转义字符，转义字符后面的 % 或 _ 就不作为通配符了，注意前面没有转义字符的%和_仍然起通配符作用
select * from user where name like ‘%li/_%’ escape ‘/‘;
select * from user where name like ‘%li/%%’ escape ‘/‘;
regexp 正则匹配
^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。
$ 匹配输入字符串的结束位置。如果设置了 RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。
. 匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符，请使用像 ‘[.\n]’ 的模式。
[...] 字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^...] 负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1|p2|p3 匹配 p1 或 p2 或 p3。例如，’z|food’ 能匹配 “z” 或 “food”。’(z|f)ood’ 则匹配 “zood” 或 “food”。
* 匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于 {0,}
+ 匹配前面的子表达式一次或多次。例如，’zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}
{n} n 是一个非负整数。匹配确定的 n 次。例如，’o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m} m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

1、查找 long 型字符串 create_time 以 000 结尾的记录
select * from user where create_time regexp '000$';
2、查找 name 以 ‘张’ 开头的记录
select * from user where name regexp '^张';
3、查找 name 包含 ‘小’ 的记录
select * from user where name regexp '小';
12.8.2 Regular Expressions
https://dev.mysql.com/doc/refman/5.7/en/regexp.html
order by field按指定顺序排序
order by field 可以按指定的顺序排序，最好搭配in一起使用

SELECT * FROM MyTable
where id in(5, 3, 7, 1)
ORDER BY FIELD(`id`, 5, 3, 7, 1)
好像如果是数字排序的话，不加in也可以。
按指定的姓名顺序排序
SELECT * FROM MyTable
WHERE name IN ('张三', '李四', '王五', '孙六')
ORDER BY FIELD(name, '李四', '孙六', '张三', '王五');
How does ORDER BY FIELD() in MySQL work internally
https://dba.stackexchange.com/questions/109120/how-does-order-by-field-in-mysql-work-internally
exists
重复数据取最后更新的
表结构定义：

-- 用户身份历史表
DROP TABLE IF EXISTS user_role_transaction;
CREATE TABLE `user_role_transaction` (
  `id`                   BIGINT       NOT NULL    AUTO_INCREMENT,
  `user_id`              BIGINT       NOT NULL,
  `account_id`           BIGINT       NOT NULL    DEFAULT 0,
  `user_identity`        TINYINT      NOT NULL    DEFAULT 0 COMMENT '用户身份',
  `create_time`          DATETIME     NOT NULL    DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY (`user_id`),
  KEY (`account_id`)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
存储的是 user_id 的 role 值历史。
要求对于 user_id 相同的记录，选出 create_time 最新的一条，单个查的话很简单，排下序就行，现在要求根据 user_id 批量查。
sql 如下，对于外层的每条记录，去内层查有没有 create_time 更大的，没有的话，选出这条记录：
-- 批量查询每个user_id的最新身份
select *
from user_role_transaction urt
where not exists (
  select 1
  from user_role_transaction urt2
  where urt.user_id = urt2.user_id and urt2.create_time > urt.create_time
and user_id in (120253,147896)
这个 sql 的问题是，如果有 user_id 和 create_time 都相同的记录且这个 create_time 是这个 user_id 的最大的，还是会选出多条，所以就依赖于数据必须创建时间不同。
如何解决？
其实这个需求应该根据 id 筛选，直接选 id 最大的一条，肯定是唯一的，就不应该根据 create_time 查。
SQL中遇到多条相同内容只取一条的最简单实现
https://blog.csdn.net/lizeyang/article/details/18420007
重复数据取最后插入的
表结构同上，根据 user_id 批量查询，对于每个 user_id，如果存在多条数据，选择 id 最大的，即最后插入的，id 为自增主键。

select *
from user_role_transaction urt
where not exists (
  select 1
  from user_role_transaction urt2
  where urt2.user_id=urt.user_id and urt2.id>urt.id
and user_id in (153048,153037)
筛选 register_record 表中有 比 third_party_user 时间小的记录的，查出其 create_time 最小的一个
select u.id as user_id, case when tpu.name='懂车帝客户' then '6Cvz6a3pPW' else 'eUFUWmA6L6' end as 'code', u.create_time, rr.campaign_code as '首次code'
from third_party_user tpu join user u on tpu.phone=u.mobile
join leads_distribution ld on u.id = ld.user_id
left join register_record rr on u.id=rr.user_id
where tpu.clue_create_time > '2020-04-03 19:28:00' and tpu.clue_create_time < '2020-04-09 16:07:00'
and exists(select * from register_record rr2 where rr2.user_id = u.id and rr2.create_time < tpu.create_time)
and not exists(select * from register_record rr3 where rr3.user_id=u.id and rr3.create_time < rr.create_time)
order by tpu.id desc;
重复数据取最大/最小
ID相同取最小TYPE

-- 方法1:(推荐在数据量较大时使用)
select *
from Temp A
where TYPE=(
    select min(TYPE)
    from Temp
    where ID=A.ID
-- 方法2:
Select *
from Temp A
where not exists(
    select 1
    from Temp
    where ID=A.ID and TYPE<A.TYPE
-- 方法3:
select A.*
from Temp A
  join (
      select min(TYPE)TYPE,ID
      from Temp
      group by ID) B
  on A.ID=B.ID and A.TYPE=B.TYPE
Sql 处理重复记录(相同ID取另一字段最大值)
https://www.norbread.com/2018/01/14/sql-duplicated/
case when 条件表达式
case when 查及格和不及格人数
用一个SQL语句完成不同条件的分组
例如：stu_cls表有字段name class score，用一条sql查询每个class里及格和不及格的学生人数，结果形式class num(>=60)，num(<60)

select class,
sum(case when score>=60 then 1 else 0 end) '及格人数' ,
sum(case when score<60 then 1 else 0 end) '不及格人数',
count(*) '总人数'
from stu_cls
group by class;
case根据列in分组
name 是 masikkk, devgou, madaimeng 的 ，nickname 列是 myself
name 是 其他的， nickname 是 others

select mobile, name, create_time, case when name in ('masikkk','madaimeng','devgou') then 'myself' else 'others' end as 'nickname'
from user;
user_role_transaction 保存的是用户身份变更历史，每变动一次插入一条新数据
user 用户表，user 表中的数据可能在 user_role_transaction 中不存在
查询所有用户的 最新实时 身份 identity：
select u.id, case when urt.id is not null then urt.user_identity else 1 end
from  user u left join user_role_transaction urt on urt.user_id = u.id
WHERE urt.id is null or (urt.user_id = u.id AND NOT exists(
    SELECT *
    FROM user_role_transaction urt2
    WHERE urt2.user_id = urt.user_id AND urt2.id > urt.id)
order by u.id;
使用了 case when，对于在 user_role_transaction 表中的 user（即 urt.id is not null）结果为 urt.user_identity，否则（即不在 user_role_transaction 表中的 user）结果为固定值 1
update中使用case when赋值
需求：
user 表的 identity 字段是当前用户身份，新加的字段
user_role_transaction 保存的是用户身份变更历史，每变动一次插入一条新数据
之前只有 user_role_transaction 历史表，user 表中没有 identity 字段，现在刚加上，要用 user_role_transaction 的 user_identity 字段初始化 user 表的 identity 字段，规则是：
对于 user_id 在 user_role_transaction 表中的 user，把 user 表的 identity 字段设置为此 user_id 在 user_role_transaction 表中的最新一个值（即id最大的）
对于 user_id 不在 user_role_transaction 表中的 user，把 user 表的 identity 字段设置为固定值 1

sql 如下：
UPDATE user u left join user_role_transaction urt on urt.user_id = u.id
set u.identity = case when urt.id is not null then  urt.user_identity else 1 end
WHERE urt.id is null or (urt.user_id = u.id AND NOT exists(
    SELECT *
    FROM user_role_transaction urt2
    WHERE urt2.user_id = urt.user_id AND urt2.id > urt.id)
解释：
使用了 update join 根据查询条件更新，由于 user 表中的数据有可能不在 user_role_transaction 表中，使用了 left join 左外连接来保留 user 中非关联数据
筛选条件是 urt.id 为空（即左外关联后 user_role_transaction 表对应列是空的，即不在 user_role_transaction 表中的 user），或者关联后 user_role_transaction 中 id 最大的（这里用了个 not exists 筛选没有比他 id 更大的）
set 赋值语句使用了 case when，对于在 user_role_transaction 表中的 user（即 urt.id is not null）设置为选出的 urt.user_identity，否则（即不在 user_role_transaction 表中的 user）设置为 1
引用列别名
MySQL 中，where 中不能使用别名，group by/having 和 order by 中可以使用别名
Oracle/Hive/标准SQL/达梦数据库 中，where/group by/having 中不能使用列别名，order by 中可以使用别名
解决方法是将别名放到子查询中，例如 select …. from (select col1 ccc from table) where ccc > 1 

这是由 sql 语句的执行顺序决定的：
where 子句先执行，此时肯定不知道 select 中的别名从哪里来的，所以肯定无法使用别名
标准SQL中是先执行 group by 和 having 再执行 select 中的列筛选、函数、别名，所以 group by/having 中还不知道别名的含义
MySQL 中对查询做了加强处理，可以先知道别名，所以 group by/having 中可以使用别名
order by 最后执行，使用别名肯定是没有问题的，order by 中甚至可以直接使用栏位的下标来进行排序，如：order by 1 desc,2 asc
例1、where 中使用列别名，报错 [42S22][1054] Unknown column ‘times’ in ‘where clause’
select FROM_UNIXTIME(SUBSTRING(timestamp,1,10),'%Y-%m-%d %H:%i') as times
from user
where times='2022-01-24 02:30'
limit 10;
例2、order by 中可使用列别名
select FROM_UNIXTIME(SUBSTRING(timestamp,1,10),'%Y-%m-%d %H:%i') as times
from user
order by times desc
limit 10;
例3、MySQL中 group by/having 中可使用列别名
select FROM_UNIXTIME(SUBSTRING(timestamp,1,10),'%Y-%m-%d %H:%i') as times, count(*)
from user
group by times
having times > '2022-01-26 02:17';
不等号
mysql中用 <> 与 != 都是可以的，但sqlserver中不识别 !=，所以建议用 <>

OR条件没加括号直接和其他条件AND
比如想查询指定user_id的没有订单号的数据，如果写成下面这样就大错特错了：

SELECT * FROM order
WHERE user_id = 2812 AND order_no IS NULL OR order_no = '' ;
正确的写法是用括号把或条件括起来：
SELECT * FROM order
WHERE user_id = 2812 AND
  ( order_no IS NULL OR order_no = '' );
join
left join 或 left outer join : 左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
right join 或 right outer join: 右连接，返回右表中所有的记录以及左表中连接字段相等的记录。
inner join 或 join : 内连接，又叫等值连接，只返回两个表中连接字段相等的行。
full join 或 full outer join: 外连接，返回两个表中的行：left join + right join。
cross join : 结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。

join和inner join是完全相同的
mysql 和 标准sql 中，join 和 inner join 都是完全相同的
Difference between JOIN and INNER JOIN
https://stackoverflow.com/questions/565620/difference-between-join-and-inner-join
连接中的on和where
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用 left jion 时，on 和 where 条件的区别如下：
1、on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

SQL JOIN 中 on 与 where 的区别
https://www.runoob.com/w3cnote/sql-join-the-different-of-on-and-where.html
where逗号分隔等价于join或inner join
1、如下两个 sql 是相等的，都是 带有 on 条件的 inner join

select * from A a join B b on a.id=b.a_id;
select * from A a, B b where a.id=b.a_id;
2、如下两个 sql 也是相等的，都是不带 on 条件的 cross join，结果是两表的笛卡尔积，行数等于 A表行数 乘以 B表行数




    

select * from A a join B b;
select * from A a, B b;
但更推荐使用join语法
INNER JOIN ON vs WHERE clause
https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause
用left join代替not in
比如我们想查询用户地址表 user_address 中的 user_id 不在 user 表中的脏数据，这些脏数据可能是由于 user_address 表的 user_id 字段没加外键约束导致的。
可以用下面的 not in 子句进行条件筛选：

SELECT *
FROM user_address
where user_id not in (select id from user );
或者 使用 left join 左外连接 把 user_address 表中独有的数据保留下来，结果中右边user表中没数据的都是null，直接用user的某个字段是否null判断即可
select *
from user_address as ua left join user as u ON ua.user_id=u.id
where u.id is null;
可以用关联后的 user 表的任意字段是否 null 进行判断。
用 left join 显得更高端一点儿。
mysql中cross join和join以及inner join完全相同
mysql 中，cross join和join以及inner join完全相同，无任何区别。
见 5.6 版本官网文档
13.2.9.2 JOIN Syntax
https://dev.mysql.com/doc/refman/5.6/en/join.html
In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.
当没有on连接条件时，cross join和join以及inner join 都是笛卡尔积。
例如
select * from A cross join B;
可以替换为 inner join 或 join ，和标准sql一样，是做笛卡尔积，结果个数是表A行数乘以表B行数
由于mysql将这三者视为等同，所以cross join也可以加on条件，而标准sql中cross join是不能加条件的。
例如
select *
from A as a cross join B as b on a.id=b.a_id;
和使用 inner join 或 join 完全相同。
mysql中没有full join全连接
mysql 中没有 full join 语法，下面的sql会报语法错误：

select *
from A as a full join B as b on a.id=b.a_id;
可使用 union 并集代替全连接。
How to do a FULL OUTER JOIN in MySQL?
https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql
union
MySQL UNION 用于把来自多个 SELECT 语句的结果组合到一个结果集合中。语法为：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
在多个 SELECT 语句中，对应的列应该具有相同的字段属性，且第一个 SELECT 语句中被使用的字段名称也被用于结果的字段名称。
UNION 常用于数据类似的两张或多张表查询，如分库分表中的数据分表，或者是按时间划分的数据历史表等。
注意：
1、union 会去除结果中的重复记录，这里的重复指的是所有字段完全相同，有任意字段不同也算作不同记录。
2、第一个 SELECT 语句中被使用的字段名称也被用于结果的字段名称
3、各 SELECT 语句字段名称可以不同，但字段属性必须一致。
union与union all区别
当使用 UNION 时，MySQL 会把结果集中重复的记录删掉，而使用 UNION ALL ，MySQL 会把所有的记录返回，且效率高于 UNION。

使用 UNION ALL 的时候，只是单纯的把各个查询组合到一起而不会去判断数据是否重复。因此，当确定查询结果中不会有重复数据或者不需要去掉重复数据的时候，应当使用 UNION ALL 以提高查询效率。
为什么用 union all 效率更高？
因为他不需要去重
MySQL UNION 与 UNION ALL 语法与用法
https://www.cnblogs.com/zhangminghui/p/4408546.html
union all 多表count求和
select count(*) from t_pic_record_0 where groupid='collection_test_3'
union all
select count(*) from t_pic_record_1 where groupid='collection_test_3'
union all
select count(*) from t_pic_record_2 where groupid='collection_test_3';
+----------+
| count(*) |
+----------+
|        6 |
|        5 |
|        5 |
+----------+
要求和的话在外层再加一次 sum() 或 count() 即可
select sum(cnt)
from (
    select count(*) cnt from t_pic_record_0 where groupid='collection_test_3'
    union all
    select count(*) cnt from t_pic_record_1 where groupid='collection_test_3'
    union all
    select count(*) cnt from t_pic_record_2 where groupid='collection_test_3'
) t_cnt;
+----------+
| sum(cnt) |
+----------+
|       16 |
+----------+
SQL算术运算
SQL 支持 加减乘除求余算术运算

select (select 5)  + (select 3);
select (select 5)  - (select 3);
select (select 5)  * (select 3);
select (select 5)  / (select 3);
select (select 5)  % (select 3);
distinct多列
select distinct a,b,c from tableA where c > const;
等价于
select a,b,c from tableA where c > const group by a,b,c;

8.2.1.16 DISTINCT Optimization
https://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html
注意此时是将a,b,c三列所有不同的组合全部列出来，而不仅仅只是distinct a
如果想 distinct 只作用于a列，同时又能查出对应的b和c列，可以使用group by a来代替，此时如果唯一的a列对应的b，c列值不同，查出的结果可能具有不确定性。
mysql实现distinct限制一列而查多列的方法
https://blog.csdn.net/liuxiao723846/article/details/79181857
group by
group by 将查询结果按某一列或多列的值分组，值相等的为一组

where 子句中不能使用聚集函数作为条件表达式
join 后 group by 聚合为拼接串
user_label_mapping 是 user id 和 标签 id 映射表
user_label 是 标签表
一个 user 可以有多个标签，查出同一 user 的多个标签并串接为 逗号分隔字符串

select user_id, group_concat(ul.name) 'hobbies'
from user_label_mapping ulm join user_label ul on ulm.user_label_id = ul.id
where ulm.type='user_hobby'
group by user_id
join 后先 group by 按 user_id 分组，然后把 标签名 group_concat 串接起来。
select字段必须是分组字段或聚集函数
select 中指定的字段要么就要包含在 group by 语句的后面，作为分组的依据；要么就要被包含在聚合函数中。
含有 group by 子句的 select 中指定的字段必须是“分组依据字段”，其他字段若想出现在 select 中则必须包含在聚合函数中。

order字段必须是分组字段或聚集函数
group by后的order by子句中的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中。
例如

SELECT errortype, isschchg, count(*)
FROM "ais"."re_ais_dcm_changeresult"
WHERE "airlinecode" = 'HU'
group by errortype, isschchg
ORDER BY savedate
执行时报错：ERROR:  column “re_ais_dcm_changeresult.savedate” must appear in the GROUP BY clause or be used in an aggregate function
因为savedate字段不是group by分组字段，也不在聚集函数中，若将order by子句改为：ORDER BY count(savedate)，即将savedate放入聚集函数则没问题。
ERROR 1055 (42000) sql_mode=only_full_group_by
select *
from user
group by name;
报错
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘x.x’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
原因是
SQL 标准中不允许 SELECT 列表，HAVING 条件语句，或 ORDER BY 语句中出现分组字段和聚集函数之外的字段。而 MySQL 中有一个状态 ONLY_FULL_GROUP_BY 来标识是否遵从这一标准，默认为开启状态。
解决
1、关闭 ONLY_FULL_GROUP_BY
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));
要想永久生效，需要修改 my.cnf 配置并重启mysql，
2、通过 ANY_VALUE() 来改造查询语句以避免报错
使用 ANY_VALUE() 包裹的值不会被检查，跳过该错误。
SELECT gender, ANY_VALUE(last_name)
FROM   employees
GROUP  BY gender
having子句中不能使用列别名
group by 或 having 子句中不能使用列的别名
例：

SELECT a as c, sum(b)
FROM test
GROUP BY c
会提示错误，group by c 改成 group by a 就行。
我在开发中遇到的错误：
[ERROR] 2016-07-05 11:01:04,694 method:com.masikkk.myservice.integratedAnalyse(AgentBookWs.java:83)
integerated analyse query failed!
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: column "ras_agent_psgbook.bookingoffice" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 8
### The error may exist in mybatis/PsgBookMapper.xml
### The error may involve com.masikkk.dao.PsgBookMapper.selectIntegratedAnalyseByOffice-Inline
### The error occurred while setting parameters
### SQL: select bookingoffice as operatingOffice, oc as oc,count(1) as addNum    from ras_agent_psgbook    where    oc=? and depdate between ? and ? and bookingdate between ? and ?                  and (depdate > ? or (depdate=? and deptime > ?))                  group by operatingoffice,oc      order by addNum desc
### Cause: org.postgresql.util.PSQLException: ERROR: column "ras_agent_psgbook.bookingoffice" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 8
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "ras_agent_psgbook.bookingoffice" must appear in the GROUP BY clause or be used in an aggregate function
select中给bookingoffice设定别名为operatingOffice，group by中使用别名operatingoffice导致SQL报错。
\G 列变行
\G 的作用是将查到的结构旋转 90 度变成纵向

DCL
手动提交事务
使用 START TRANSACTION, BEGIN 来显式地开启一个事务。
在显式开启事务后，在默认设置下（即参数 completion_type 等于0），MySQL会自动地执行 SET AUTOCOMMIT=0 的命令，并在 COMMIT 或 ROLLBACK 结束一个事务后执行 SET AUTOCOMMIT=1

start transction 或 begin：显示的开启一个事务
commit 或 commit work：commit work与completion_type的关系，commit work是用来控制事务结束后的行为，是chain还是release的，可以通过参数completion_type来控制，默认为0（或者NO_CHAIN），表示没有任何操作 与commit效果一样。
MariaDB [uds]> begin;
Query OK, 0 rows affected (0.010 sec)
MariaDB [uds]> update user set name='手动commit3' where id=136228;
Query OK, 1 row affected (0.015 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [uds]> commit;
Query OK, 0 rows affected (0.014 sec)
MySQL事务控制语句（学习笔记）
https://blog.csdn.net/mchdba/article/details/8690935
mysql start transaction 和 set autocommit = 0 的区别
1、mysql使用InnoDB的引擎，那么是自动开启事务的，也就是每一条sql都是一个事务（除了select）。
2、由于第一条的原因，所以我们需要autocommit为on，否则每个query都要写一个commit才能提交。
3、在mysql的配置中，默认缺省autocommit就是为on，这里要注意，不用非要去mysql配置文件中显示地配置一下。
4、最关键的来了，当我们显示地开启一个事务，也就是写了begin的时候，autocommit对此事务不构成影响。而不是网上大家说的，必须要写一个query临时设置autocommit为off，否则比如三个query只能回滚最后一个query，这是完全不对的。

1、set autocommit = 0
关闭当前会话中事务的自动提交，需要手动 commit 或者 rollback，相当于开启一个全局的事务。在 mysql 的事务中，默认 autocommit = 1，每一次 sql 操作都被认为是一个单次的事务，被隐式提交
2、start transaction
挂起 autocommit 的状态，开启一个事务上下文。首先数据库会隐式提交之前的还未被提交的操作，同时开启一个新事务。挂起 autocommit 的意思是保存 autocommit 的当前状态，然后 start transaction，直到 commit or rollback 结束本次事务，再恢复之前挂起的 autocommit 的状态。
如果 start transaction 前 autocommit = 1，则完成本次事务后 autocommit 还是 1
如果 start transaction 前 autocommit = 0，则完成本次事务后 autocommit 还是 0，接下来的操作你仍需手动 commit 才可以提交。
mysql start transaction 和 set autocommit = 0 的区别
https://my.oschina.net/sallency/blog/785476
show 语句
show status 查看服务端状态变量
13.7.5.35 SHOW STATUS Statement
https://dev.mysql.com/doc/refman/5.7/en/show-status.html
SHOW [GLOBAL | SESSION] STATUS
    [LIKE 'pattern' | WHERE expr]
例如：
show status; 查看所有变量
或
show status like '%变量名%'; 查看某个具体变量值




    

常用变量说明：
Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
Connections 试图连接MySQL服务器的次数。
Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
Delayed_writes 用INSERT DELAYED写入的行数。
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
Flush_commands 执行FLUSH命令的次数。
Handler_delete 请求从一张表中删除行的次数。
Handler_read_first 请求读入表中第一行的次数。
Handler_read_key 请求数字基于键读行。
Handler_read_next 请求读入基于一个键的一行的次数。
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
Handler_update 请求更新表中一行的次数。
Handler_write 请求向表中插入一行的次数。
Key_blocks_used 用于关键字缓存的块的数量。
Key_read_requests 请求从缓存读入一个键值的次数。
Key_reads 从磁盘物理读入一个键值的次数。
Key_write_requests 请求将一个关键字块写入缓存次数。
Key_writes 将一个键值块物理写入磁盘的次数。
Max_used_connections 同时使用的连接的最大数目。
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
Open_tables 打开表的数量。
Open_files 打开文件的数量。
Open_streams 打开流的数量(主要用于日志记载）
Opened_tables 已经打开的表的数量。
Questions 发往服务器的查询的数量。
Slow_queries 要花超过long_query_time时间的查询数量。
Threads_connected 当前打开的连接的数量。
Threads_running 不在睡眠的线程数量。
Uptime 服务器工作了多少秒。
show plugins 查看所有插件
13.7.5.26 SHOW PLUGINS Syntax
https://dev.mysql.com/doc/refman/5.6/en/show-plugins.html
SHOW PLUGINS 查看支持的插件，
或者从 INFORMATION_SCHEMA.PLUGINS 表中查看支持的插件
MariaDB [uds]> show plugins;
+-------------------------------+----------+--------------------+---------+---------+
| Name                          | Status   | Type               | Library | License |
+-------------------------------+----------+--------------------+---------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| wsrep                         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MyISAM                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CLIENT_STATISTICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INDEX_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| TABLE_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| USER_STATISTICS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| PERFORMANCE_SCHEMA            | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| SEQUENCE                      | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| Aria                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                       | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEEDBACK                      | DISABLED | INFORMATION SCHEMA | NULL    | GPL     |
| partition                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+-------------------------------+----------+--------------------+---------+---------+
DML
insert
on duplicate key update
ON DUPLICATE KEY UPDATE 为Mysql特有语法，作用是当insert已经存在的记录时，执行Update

如果在INSERT语句末尾指定了 ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的问题，则插入新行。
例如，如果列 a 为 主键 或 拥有UNIQUE索引，并且包含值1，则以下两个语句具有相同的效果：
INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;
insert多条记录
INSERT INTO user(user_id, name_en, name_cn) VALUES
  ( 8, "llll", "小拉"),
  ( 9, "zsan", "张三"),
  (10, "lisi", "李四"),
  (27, "wwu",  "王五");
insert select 表间数据复制
同一数据库内insert select
1、表结构相同的表，且在同一数据库（如，table1,table2)

# 完全复制
insert into table1 select * from table2;
# 不复制重复纪录
insert into table1 select distinct * from table2;
# 前五条纪录
insert into table1 select top 5 * from table2;
不同数据库间insert select
2、不在同一数据库中（如，db1 table1,db2 table2)

# 完全复制
insert into db1.table1 select * from db2.table2;
# 不复制重复纪录
insert into db1.table1 select distinct * from db2.table2;
# 前五条纪录
insert into tdb1.able1 select top 5 * from db2.table2;
表结构不相同insert select
如果表tb1和tb2只有部分字段是相同的，要实现将tb1中的部分字段导入到tb2中相对应的相同字段中，则使用以下命令：

insert into user_address(user_id, province_id, city_id, province, city)
    select user_id, province_id, city_id, province, city
    from user_region
    where user_id=12345;
Mysql-两个表之间复制数据
https://blog.csdn.net/qingwuh/article/details/81350470
delete
清空表truncate和delete
truncate table wp_comments;
delete from wp_comments;
其中truncate操作中的table可以省略。这两者都是将wp_comments表中数据清空，不过也是有区别的，如下：
truncate是整体删除（速度较快）， delete是逐条删除（速度较慢）。
truncate不写服务器log，delete写服务器log，也就是truncate效率比delete高的原因。
truncate不激活trigger(触发器)，但是会重置Identity（标识列、自增字段），相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的ID数。而delete删除以后，Identity依旧是接着被删除的最近的那一条记录ID加1后进行记录。
如果只需删除表中的部分记录，只能使用DELETE语句配合where条件。 DELETE FROM wp_comments WHERE……
外键导致删除失败
比如从user表中删除数据，如果还有其他表引用此行数据会导致删除失败：

[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`uds`.`table_name`, CONSTRAINT `user_fk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`))
如果忽略数据一致性，强行删除的话，可以暂时关闭外键检查：
SET FOREIGN_KEY_CHECKS = 0;
delete from user where user_id=81681419;
SET FOREIGN_KEY_CHECKS = 1;
Can’t drop table: A foreign key constraint fails
https://stackoverflow.com/questions/11100911/cant-drop-table-a-foreign-key-constraint-fails
mysql 外键引发的删除失败
http://www.cnblogs.com/FlyAway2013/p/6864466.html
delete语句使用别名
mysql delele table语句使用别名：
语法：

delete t from table t where t.column = value;
或者不使用别名：
delete from table where column = value;
但如果使用别名，delete后一定要加别名t，否则在某些严格语法检查的情况下会报错。
删除重复行
删除所有重复行
用in

delete from table1
where user_id in (
  SELECT user_id
  FROM table1
  GROUP BY user_id
  HAVING count(*) > 1);
或者用exists
delete from table1
where exists (
  SELECT user_id
  FROM table1
  GROUP BY user_id
  HAVING count(*) > 1);
mariadb中都会报错：
[HY000][1093] Table ‘table1’ is specified twice, both as a target for ‘DELETE’ and as a separate source for data
因为同时进行查询和修改。
解决方法：查询语句外面再套一个查询形成一张临时表
delete from drive_booking
where user_id in (
    select *
    from (
      SELECT user_id
      FROM drive_booking
      GROUP BY user_id
      HAVING count(*) > 1) as temp_table
重复行只保留一条
删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

delete from people
where peopleId in (
  select peopleId
  from people
  group by peopleId
  having count(peopleId) > 1
and rowid not in (
  select min(rowid)
  from people
  group by peopleId
  having count(peopleId )>1
上面这条语句在mysql中执行会报错，因为同时对表进行查询和更新，mysql不支持这种方式。oracel和msserver都支持这种方式。
解决方法：查询语句外面再套一个查询形成一张临时表
删除 user_region 表中 user_id 重复的数据，只保留id最小的
delete from user_region
where user_id in (
  select user_id
  from (
    select user_id
    from user_region
    GROUP BY user_id
    HAVING count(*)>1
  ) as a
and id not in (
  select id
  from (
    select min(id) as id
    from user_region
    GROUP BY user_id
    having count(*)>1
  ) as b
问：为什么不能只用 not in min(id) 来删除呢？
答：因为这样会多删除，会把 user_id 不重复的也删掉，非常危险，使用 not in 的话必须加user_id in 条件.
当表中没有id自增主键时，有时候需要根据 更新时间排重，但不是很精确。
删除 user_employee_info 表中 user_id 重复的数据，保留 update_time 最大的
select * from user_employee_info
where user_id in (
  select user_id
  from (
    select user_id
    from user_employee_info
    group by user_id
    having count(*) > 1
  ) as a
and update_time not in (
  select update_time
  from (
    select max(update_time) as update_time
    from user_employee_info
    group by user_id
    having count(*)>1
  ) as b
这个sql对于两条user_id 相同且 update_time 相同的数据就删除不了。
例如 user_address 表中有 user_id 和地址类型 type，一个user同一类型地址只能存一个，假如表上没有唯一约束造成了重复数据，删除重复数据，保留id最大的
delete from user_address
where user_id in (
  select user_id
  from (
    select user_id
    from user_address
    GROUP BY user_id, type
    HAVING count(*)>1
  ) as a
and id not in (
  select id
  from (
    select max(id) as id
    from user_address
    GROUP BY user_id, type
    having count(*)>1
  ) as b
delete join
mysql 支持 delete 语句中 join 连表查询

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;
delete t1 from table1 t1 left join table2 t2 on t1.t2_id=t2.id
where t1.id in (1,2,3)
and (t2.status='disable' or t2.id is null);
update
update多列
update user u
set u.name = '张三', u.mobile = '13613661366'
where u.id = 23;
update join 跨表关联更新
在 MySQL 中, 我们也可以在 UPDATE 语句中使用 JOIN 子句执行跨表更新

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2,
    T2.C3 = expr
WHERE condition
当你希望更新一批值，且值是通过select条件查询出来时
解决思路 1：
使用 INNER JOIN (最简洁)
UPDATE A a INNER JOIN B b ON b.key_id = a.key_id
SET a.val = 2 WHERE b.satisfy = 1
解决思路 2：
UPDATE A a, (SELECT A.id from A
             LEFT JOIN B ON B.key_id= A.key_id
             WHERE B.satisfy = 1) b
SET a.val = 2
WHERE a.id = b.id
根据无关表 table2 中的某些信息去更新 table1 表
UPDATE `table1` AS `dest`,
    ( SELECT * FROM `table2` WHERE `id` = x ) AS `src`
SET `dest`.`col1` = `src`.`col1`
WHERE `dest`.`id` = x ;
https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query
update user_info as ui join user as u on ui.user_id = u.id
set ui.ext_info='', ui.address='beijing'
where u.name like '%ST'
and ui.user_id in (
  SELECT * FROM (
         SELECT user_id
         FROM user_info
         WHERE ext_info like '%特殊%'
       ) AS special_user
update app_user_relationship as aur join user_account_info as uai on aur.account1_id=uai.account_id
set aur.user1_id=uai.user_id
where aur.user1_id != uai.user_id;
-- join 两个表
UPDATE user u join user_address ua on u.id=ua.user_id join user_address ua2 on ua.pid=ua2.id
set u.city_id=cr2.id, u.province_id=cr2.pid
where ua.satisfy = 1;
update case when 条件表达式更新
需求：
user 表的 identity 字段是当前用户身份，新加的字段
user_role_transaction 保存的是用户身份变更历史，每变动一次插入一条新数据
之前只有 user_role_transaction 历史表，user 表中没有 identity 字段，现在刚加上，要用 user_role_transaction 的 user_identity 字段初始化 user 表的 identity 字段，规则是：
对于 user_id 在 user_role_transaction 表中的 user，把 user 表的 identity 字段设置为此 user_id 在 user_role_transaction 表中的最新一个值（即id最大的）
对于 user_id 不在 user_role_transaction 表中的 user，把 user 表的 identity 字段设置为固定值 1

sql 如下：




    

UPDATE user u left join user_role_transaction urt on urt.user_id = u.id
set u.identity = case when urt.id is not null then  urt.user_identity else 1 end
WHERE urt.id is null or (urt.user_id = u.id AND NOT exists(
    SELECT *
    FROM user_role_transaction urt2
    WHERE urt2.user_id = urt.user_id AND urt2.id > urt.id)
解释：
使用了 update join 根据查询条件更新，由于 user 表中的数据有可能不在 user_role_transaction 表中，使用了 left join 左外连接来保留 user 中非关联数据
筛选条件是 urt.id 为空（即左外关联后 user_role_transaction 表对应列是空的，即不在 user_role_transaction 表中的 user），或者关联后 user_role_transaction 中 id 最大的（这里用了个 not exists 筛选没有比他 id 更大的）
set 赋值语句使用了 case when，对于在 user_role_transaction 表中的 user（即 urt.id is not null）设置为选出的 urt.user_identity，否则（即不在 user_role_transaction 表中的 user）设置为 1
specified twice both target and source
当要更新的目标表同时也在查询条件中时，会报错：
Table name is specified twice, both as a target for ‘UPDATE’ and as a separate source for data in mysql
解决方法是把作为条件的目标表查询为一个中间表。

UPDATE MYTABLE
SET COL=COL+1
WHERE ID IN (
    SELECT ID
    FROM MYTABLE
    WHERE OTHERCOL=0
DELETE FROM MYTABLE
WHERE ID IN (
  SELECT ID
  FROM MYTABLE
  WHERE OTHERCOL=0
UPDATE MYTABLE
SET COL=COL+1
WHERE ID IN (
  SELECT *
  FROM (
    SELECT ID
    FROM MYTABLE
    WHERE OTHERCOL=0
  ) AS TEMP
DELETE FROM MYTABLE
WHERE ID IN (
  SELECT *
  FROM (
    SELECT ID
    FROM MYTABLE
    WHERE OTHERCOL=0
  ) AS TEMP
例如：
FROM manager AS m2
改为
FROM (select * from manager) AS m2
Mysql: Table ‘name’ is specified twice, both as a target for ‘UPDATE’ and as a separate source for data
https://www.cnblogs.com/liusonglin/p/4387543.html
Table is specified twice, both as a target for ‘UPDATE’ and as a separate source for data in mysql
https://stackoverflow.com/questions/44970574/table-is-specified-twice-both-as-a-target-for-update-and-as-a-separate-source
Every derived table must have its own alias
意思是每个派生出来的表（或者叫子查询）必须有一个自己的别名。
一般是在多表查询或者子查询的时候会出现这个错误，因为在嵌套查询中，子查询的结果是作为一个派生表给上一级进行查询，所以子查询的结果必须有一个别名。

SELECT id
FROM (
    SELECT id
    FROM user
会报错 Every derived table must have its own alias.
改为
SELECT id
FROM (
    SELECT id
    FROM user
) as temp;
在子查询的后面增加一句 as temp，相当于给子查询的结果集派生表取别名为temp，问题就解决了。
但是下面这条sql就不会报错
-- 删除地址表中user_id不存在的脏数据
delete from user_address
where user_id not in (select id from user);
What is the error “Every derived table must have its own alias” in MySQL?
https://stackoverflow.com/questions/1888779/what-is-the-error-every-derived-table-must-have-its-own-alias-in-mysql
Mysql错误：Every derived table must have its own alias
https://chenzhou123520.iteye.com/blog/2041684
update返回值与useAffectedRows
比如如下mybatis sql语句：

@Update({"UPDATE user SET name = null WHERE id = #{id}"})
void updateUserProfileById(@Param("id") long id);
默认情况下，mybatis 的 update 操作的返回值是 matched 的记录数，并不是受影响的记录数。
严格意义上来将，这并不是 mybatis 的返回值，mybatis 仅仅只是返回的数据库连接驱动（通常是 JDBC ）的返回值
通过对 JDBC URL 显式的指定 useAffectedRows选项，我们将可以得到受影响的记录的条数：
jdbc:mysql://${jdbc.host}/${jdbc.db}?useAffectedRows=true
那么有没有办法让 mybatis 的 update 操作的返回值是受影响的行数呢。因为我们业务逻辑中有时会根据这个返回值做业务判断。答案当然是有的。
修改数据库链接配置为：增加了 useAffectedRows 字段信息。
JDBC默认返回的是符合的行数Rows matched， 如果想返回修改过（ Changed）的行数  ，需要使用useAffectedRows参数
useAffectedRows的含义 ：是否用受影响的行数替代查找到的行数来返回数据
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm?useAffectedRows=true
jdbc.username=root
jdbc.password=123456
mybatis 中的 update 返回值你真的明白吗
https://www.jianshu.com/p/80270b93082a
聊聊Mybatis Update操作返回值
https://notes.wanghao.work/2017-09-06-%E8%81%8A%E8%81%8AMybatis-Update%E6%93%8D%E4%BD%9C%E8%BF%94%E5%9B%9E%E5%80%BC.html
DDL
字段名避免使用mysql关键字
创建一个字典表， 键 使用了 key, 加反引号执行 DDL 的时候没问题

CREATE TABLE `dict`(
    `id`                 bigint(20)   NOT NULL AUTO_INCREMENT,
    `key`                varchar(255) NOT NULL,
    `value`              text         NOT NULL,
    `description`        text         DEFAULT NULL,
    `enabled`            BOOLEAN      DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
但 jpa auto ddl 时生成的 sql 字段名是不带反引号的，执行sql就会报错， key varchar(255) 处会报错，和 mysql 创建索引的关键字 KEY 冲突了。
create table dict
    id                 bigint not null auto_increment,
    description        varchar(255),
    key varchar(255),
    status             varchar(255) default 'enabled',
    value              varchar(255),
    primary key (id)
) engine = InnoDB
DDL与事务
MySQL不支持事务型DDL
MySQL DDL 不支持事务，DDL语句执行后会立即提交。
所以 drop table, create table, alter table 这些 DDL 是不支持事务的。

13.3.2 Statements That Cannot Be Rolled Back
https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html
常见数据库的事务DDL支持情况
PostgreSQL 的这篇调研说明了主要数据库是否支持 事务DDL 以及为什么。
Transactional DDL in PostgreSQL: A Competitive Analysis
https://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis
总结如下：
PostgreSQL - yes
MySQL - no; DDL causes an implicit commit
Oracle Database 11g Release 2 and above - by default, no, but an alternative called edition-based redefinition exists
Older versions of Oracle - no; DDL causes an implicit commit
SQL Server - yes
Sybase Adaptive Server - yes
DB2 - yes
Informix - yes
Firebird (Interbase) - yes
Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?
https://stackoverflow.com/questions/4692690/is-it-possible-to-roll-back-create-table-and-alter-table-statements-in-major-sql
MySQL事务中混合DDL会怎样？
应当将 DDL 和 DML 语句以及 DCL 语句严格分开，避免事务被隐性“破坏”，导致误操作情况发生。

#禁用自动提交
set autocommit=off;
#创建tb1
create table tb1(id int auto_increment primary key,c1 int);
#开始事务
start transaction;
#插入数据
insert into tb1(c1) select 1;
insert into tb1(c1) select 2;
insert into tb1(c1) select 3;
#创建tb2
create table tb2(id int auto_increment primary key,c1 int);
执行完上述 sql 后，如果想回滚3条插入操作，会发现无法将这3条数据删除，因为 create table tb2 这条 DDL 执行完后会自动提交，顺带也会把之前的 DML 提交。
当执行到DDL语句时，会隐式的将当前回话的事务进行一次“COMMIT”操作，因此在MySQL中执行DDL语句时，应该严格地将DDL和DML完全分开，不能混合在一起执行。
MySQL的三种DDL处理方式
MySQL 各版本，对于 DDL 的处理方式是不同的，主要有三种：

Copy Table(可读不可写)
Copy Table 方式， 这是 InnoDB 最早支持的方式。顾名思义，通过临时表拷贝的方式实现的。新建一个带有新结构的临时表，将原表数据全部拷贝到临时表，然后 rename，完成创建操作。这个方式过程中，原表是可读的，不可写。但是会消耗一倍的存储空间。

处理过程：
1、首先新建 Temp table，表结构是 ALTAR TABLE 新定义的结构
2、然后把原表中数据导入到这个 Temp table
3、删除原表
4、最后把临时表 rename 为原来的表名
为了保持数据的一致性，中间复制数据（Copy Table）全程锁表只读，如果有写请求进来将无法提供服务，连接数爆张。
在 MySQL 5.1（带InnoDB Plugin）和 5.5 中，有个新特性叫 Fast Index Creation(FIC)，就是在添加或者删除二级索引的时候，可以不用复制原表。
引入 FIC 之后，创建二级索引时会对原表加上一个S锁，创建过程不需要重建表（no-rebuild）；删除 InnoDB 二级索引只需要更新内部视图，并标记这个索引的空间可用，去掉数据库元数据上该索引的定义即可。这个过程也只允许读操作，不能写入，但大大加快了修改索引的速度（不含主键索引，InnoDB 的按主键聚簇存储特性决定了修改主键依然需要 Copy Table ）。
FIC 只对索引的创建删除有效，MySQL 5.6 Online DDL 把这种特性扩展到了添加列、删除列、修改列类型、列重命名、设置默认值等等，实际效果要看所使用的选项和操作类别来定。
Inplace(可读不可写)
Inplace 方式，这是原生 MySQL 5.5，以及 innodb_plugin 中提供的方式。所谓 Inplace 也就是在原表上直接进行，不会拷贝临时表。相对于 Copy Table 方式，这比较高效率。原表同样可读的，但是不可写。

Online(可读可写,5.6及以上)
Online 这是 MySQL 5.6 以上版本中提供的方式。无论是 Copy Table 方式，还是 Inplace 方式，原表只能允许读取，不可写。对应用有较大的限制，因此 MySQL 最新版本中，InnoDB 支持了所谓的 Online 方式 DDL. 与以上两种方式相比，online 方式支持 DDL 时不仅可以读，还可以写，对于 dba 来说，这是一个非常棒的改进。

MySQL5.6 中的 InnoDB Online DDL
14.13 InnoDB and Online DDL
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html
14.13 InnoDB 在线 DDL - 官方文档的中文翻译
https://zhuanlan.zhihu.com/p/40443907
从 MySQL 5.6 开始，InnoDB 存储引擎提供一种叫 在线 DDL(Online DDL) 的 DDL 执行方式，允许 Inplace 更改表 和 并发 DML 操作。
此功能的好处包括：
在繁忙的生产环境中提高响应能力和可用性，使表不可用几分钟或几小时是不切实际的。
使用 LOCK 子句在 DDL 操作期间调整性能和并发性之间平衡的能力。请参阅 LOCK 子句。
与 table-copy 方法相比，使用的磁盘空间和 I/O 开销更少。
Online DDL 基于 MySQL 5.5 开始提供的 **快速索引创建特性(fast index creation)**，快速索引创建特性可以在不拷贝表的情况下进行索引创建和删除。
Online DDL 是默认开启的，无需执行任何特殊操作即可启用在线 DDL. 默认情况下，MySQL 在允许的情况下执行操作，并尽可能少地锁定。
可以使用 ALTER TABLE 语句的 LOCK 和 ALGORITHM 子句控制 DDL 操作的各个方面。
这些子句放在语句的末尾，用逗号分隔表和列。 例如：
ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
LOCK 子句可用于微调对表的并发访问程度。
ALGORITHM 子句主要用于性能比较，并且在遇到任何问题时作为旧表复制行为的后备。
例如：
为避免意外地使表不可用于读取，写入或两者，请在 ALTER TABLE 语句中指定一个子句，例如 LOCK = NONE（允许读取和写入）或 LOCK = SHARED（允许读取）。 如果请求的并发级别不可用，则操作立即停止。
要比较性能，请运行 ALGORITHM = INPLACE 和 ALGORITHM = COPY 语句。
为避免使用复制表的 ALTER TABLE 操作来绑定服务器，请包括 ALGORITHM = INPLACE。 如果语句不能使用 in-place 机制，则该语句立即停止。
Online DDL 选项
MySQL 在线 DDL 分为 INPLACE 和 COPY 两种方式，通过在 ALTER 语句的 ALGORITHM 参数指定。

ALGORITHM=INPLACE，原地操作，可以避免重建表带来的 IO 和 CPU 消耗，保证 DDL 期间依然有良好的性能和并发。
ALGORITHM=COPY，需要拷贝原始表，所以不允许并发 DML 写操作，可读。这种 copy 方式的效率不如 inplace, 因为前者需要记录 undo 和 redo log, 而且因为临时占用 buffer pool 引起短时间内性能受影响。
上面只是 Online DDL 内部的实现方式，此外还有 LOCK 选项控制是否锁表，根据不同的 DDL 操作类型有不同的表现：默认 mysql 尽可能不去锁表，但是像修改主键这样的昂贵操作不得不选择锁表。
LOCK=NONE，即 DDL 期间允许并发读写涉及的表，比如为了保证 ALTER TABLE 时不影响用户注册或支付，可以明确指定，好处是如果不幸该 alter 语句不支持对该表的继续写入，则会提示失败，而不会直接发到库上执行。ALGORITHM=COPY 默认 LOCK 级别
LOCK=SHARED，即 DDL 期间表上的写操作会被阻塞，但不影响读取。
LOCK=DEFAULT，让 mysql 自己去判断 lock 的模式，原则是 mysql 尽可能不去锁表
LOCK=EXCLUSIVE，即 DDL 期间该表不可用，堵塞任何读写请求。如果你想 alter 操作在最短的时间内完成，或者表短时间内不可用能接受，可以手动指定。
但是有一点需要说明，无论任何模式下，online ddl 开始之前都需要一个短时间排它锁(exclusive)来准备环境，所以 alter 命令发出后，会首先等待该表上的其它操作完成，在 alter 命令之后的请求会出现等待waiting meta data lock。同样在ddl结束之前，也要等待alter期间所有的事务完成，也会堵塞一小段时间。所以尽量在 ALTER TABLE 之前确保没有大事务在执行，否则一样出现连环锁表。
mysql 5.6 在线 DDL
https://www.cnblogs.com/wyy123/p/10272496.html
Online DDL 索引操作
创建或增加二级索引，删除索引，重命名索引都支持 in-place 的方式，均支持并发 DML，但是不能重建表。其中，删除索引和重命名索引只修改元数据。

创建和增加二级索引
CREATE INDEX name ON table (col_list);
ALTER TABLE tbl_name ADD INDEX name (col_list);
在创建索引时，该表仍可用于读写操作。 CREATE INDEX 语句仅在完成访问表的所有事务完成后才结束，因此索引的初始状态反映了表的最新内容。
在线DDL支持添加二级索引意味着您通常可以通过先创建没有二级索引的表，再加载数据，最后添加二级索引，来加速创建和加载表及相关索引的整个过程。
新创建的辅助索引仅包含CREATE INDEX或ALTER TABLE语句完成执行时表中的已提交数据。 它不包含任何未提交的值，旧版本的值或标记为删除但尚未从旧索引中删除的值。
如果服务器在创建二级索引时退出，则在恢复时，MySQL会删除任何部分创建的索引。 您必须重新运行ALTER TABLE或CREATE INDEX语句。
某些因素会影响此操作的性能，空间使用和语义。




    

DROP INDEX name ON table;
ALTER TABLE tbl_name DROP INDEX name;
在删除索引时，该表仍可用于读写操作。 DROP INDEX语句仅在完成访问表的所有事务完成后才结束，因此索引的初始状态反映了表的最新内容。
14.13.1 Online DDL Operations
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html
MySQL给已存在的表增加索引时会锁表吗？
看版本，MySQL 5.6 及以上的话，支持 Online DDL 操作，不会锁表。
MySQL 5.6 以下版本，不支持 Online DDL 操作，会锁表

Online DDL 主键操作
增加主键（原来无显式主键），删除后再增加主键，都支持in-place，重建表，可并发DML，并不仅仅只修改元数据。
删除主键 不支持并发DML和in-place，并不仅仅只修改元数据。

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
修改主键需要重建表，因为 innodb 是按主键聚簇存储的，需要大量数据重组操作，需要复制表，因此，最好在创建表时定义主键，而不是稍后发出ALTER TABLE … ADD PRIMARY KEY
如果你创建一个没有主键的表，InnoDB会为你选择一个，它可以是在NOT NULL列上定义的第一个UNIQUE键，或者是系统生成的键。 为避免不确定性以及额外隐藏列的潜在空间要求，请将PRIMARY KEY子句指定为CREATE TABLE语句的一部分。
主键修改过程：
MySQL通过将原始表中的现有数据复制到具有所需索引结构的临时表来创建新的聚簇索引。 将数据完全复制到临时表后，把原始表重命名为一个新的临时表，然后把刚才的临时表重命名为原始表名，然后删除原始表。
14.13.1 Online DDL Operations
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html
Online DDL 字段操作
除了改变字段类型以外的字段操作，均支持并发 DML。所有的操作都支持 in-place 的方式。

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;
注意：添加自增列时不允许并发DML。
尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作。当添加列是auto-increment，不允许DML并发
ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INPLACE, LOCK=NONE;
删除列需要进行大量数据重组，是一项开销很大的操作。
14.13.1 Online DDL Operations
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html
14.13 InnoDB 在线 DDL - 官方文档的中文翻译
https://zhuanlan.zhihu.com/p/40443907
第三方Schema迁移工具
pt-online-schema-change
pt-online-schema-change
https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html
gh-ost
github / gh-ost
https://github.com/github/gh-ost
不需要触发器支持的 MySQL 在线更改表结构的工具
所有在线表结构修改工具的操作方式都类似：
创建与原表结构一致的临时表，该临时表已经是按要求修改后的表结构了，缓慢增量的从原表中复制数据，同时记录原表的更改(所有的 INSERT, DELETE, UPDATE 操作) 并应用到临时表。当工具确认表数据已经同步完成，它会进行替换工作，将临时表更名为原表。
索引
show index from table查看索引
show index from table_name;
或
show keys from table_name;

Non_unique 是否非唯一，0不是，1是
Key_name 索引的名称。
Seq_in_index 索引中的列序列号，从1开始。
Column_name 列名称。
Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
MariaDB [uds]> show index from user_address;
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user_address |          0 | PRIMARY     |            1 | id          | A         |        2807 |     NULL | NULL   |      | BTREE      |         |               |
| user_address |          1 | user_id     |            1 | user_id     | A         |        2807 |     NULL | NULL   |      | BTREE      |         |               |
| user_address |          1 | province_id |            1 | province_id | A         |          56 |     NULL | NULL   | YES  | BTREE      |         |               |
| user_address |          1 | city_id     |            1 | city_id     | A         |         140 |     NULL | NULL   | YES  | BTREE      |         |               |
| user_address |          1 | region_id   |            1 | region_id   | A         |         255 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.012 sec)
创建表时添加索引
-- 车辆信息表
DROP TABLE IF EXISTS `vehicle_info`;
CREATE TABLE `vehicle_info` (
  `id`                    BIGINT                     NOT NULL AUTO_INCREMENT,
  `car_order_id`          BIGINT                     NOT NULL
  COMMENT '整车订单id',
  `car_order_no`          VARCHAR(32)                NOT NULL
  COMMENT '整车订单订单号',
  `vehicle_id`            VARCHAR(32)
  COMMENT '车辆id',
  `vin_code`              VARCHAR(40)
  COMMENT '车辆vin码',
  `create_time`           DATETIME                   NOT NULL               DEFAULT CURRENT_TIMESTAMP
  COMMENT '创建时间',
  `update_time`           TIMESTAMP                  NOT NULL
  COMMENT '修改时间',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`car_order_id`) REFERENCES `car_order` (`id`),
  UNIQUE KEY (`car_order_id`),
  KEY (`car_order_no`),
  KEY (`vehicle_id`),
  KEY (`vin_code`)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;
create index在现有表上添加索引
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
前缀索引(字符串最大768字节)
MySQL 的 varchar/text 索引只支持不超过768个字节，过长时可以指定前缀
mysql最大索引 768 个字节, utf8 占 3 个字节，768/3=256
mysql最大索引 768 个字节, utf8mb4 占 4个字节，768/4=192

ALTER TABLE table_name ADD index index_name (column_name(prefix_length));
alter table page_view add index pathname (`pathname`(255));
MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)
key和index区别
KEY
key 是数据库的物理结构，它包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等。

primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个index；
unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个index；
foreign key也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index；
MySQL中的key是同时具有constraint和index的意义。
另外，在MySQL中，对于一个Primary Key的列，MySQL已经自动对其建立了Unique Index，无需重复再在上面建立索引了。
INDEX
index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；因此，索引只是索引，它不会去约束索引的字段的行为。
create table
查看建表语句
show create table 查看建表语句
show create table table_name;
show create database 查看建库语句
show create database database_name;
desc 查看列
desc table_name;
AUTO_INCREMENT 自增值设置与修改
创建表时设置自增主键：，默认起始值是 1

CREATE TABLE `orders` (
  `order_num` int(11) NOT NULL auto_increment,
  `order_date` datetime NOT NULL,
  `cust_id` int(11) NOT NULL,
  PRIMARY KEY  (`order_num`),
  KEY `fk_orders_customers` (`cust_id`),
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;
创建表格后添加：
alter table users AUTO_INCREMENT=10000;
而且该语句也适用于修改现有表的id上， 比如大批量删除数据后，想id从654321退回123456开始
alter table users AUTO_INCREMENT=123456;
13.1.17 CREATE TABLE Statement - AUTO_INCREMENT
https://dev.mysql.com/doc/refman/5.6/en/create-table.html
alter table
add column 添加列
alter table table_name
add column column_name varchar(30);
alter table table_name
add column `data_type` TINYINT NOT NULL DEFAULT 1 COMMENT '数据类型 0:unknown 1:male 2:owner';
drop column 删除列
ALTER TABLE table_name
DROP COLUMN field_name;
或者不加column关键字也行
ALTER TABLE table_name
DROP field_name;
删除的列不能是约束的一部分
注意：要删除的列不能是约束的一部分，比如 group_type 是多列唯一约束的其中一列，则删除时提示找不到列：
ERROR 1072 (42000): Key column ‘group_type’ doesn’t exist in table
需要先将唯一约束删除才能继续。

add index/key 添加索引
使用Alter语法:

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE [KEY|INDEX] [index_name](column_list)
ALTER TABLE table_name ADD PRIMARY KEY [index_name](column_list)
alter table user add unique (mobile, country_code);
alter table user add unique key (mobile, country_code);
alter table user add unique index (mobile, country_code);
alter table user add unique index mobile(mobile, country_code);
alter table page_view_transaction add index `pathname` (`pathname`(255));
alter table page_view_transaction add index host (host);
drop index/key 删除索引
DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
同一条alter语句中删除索引，同时添加索引
ALTER TABLE `dbname`.`table_name`
  DROP INDEX `idx_tppa_userid`,
  ADD UNIQUE `idx_tppa_userid` USING BTREE (`user_id`) comment '';
MySQL 中没有 drop constraint
注意：mysql 没有 DROP CONSTRAINT 语法，必须使用 drop index/key

>alter table user drop constraint UK_name;
[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'constraint UK_name' at line 1
first/after 添加列时指定字段顺序
FIRST，添加为第一列
AFTER col_name，添加到指定列名后
默认是添加为最后一列

ALTER TABLE user
ADD column_name varchar(128) null COMMENT '姓名' FIRST;
ALTER TABLE user
ADD column_name varchar(128) null COMMENT '姓名' AFTER id;
https://dev.mysql.com/doc/refman/5.6/en/alter-table.html#alter-table-add-drop-column
同时添加/删除/修改列和索引
可在同一个 ALTER TABLE 语句中同时指定多个修改子句，例如添加多列，或者添加多列、删除多列、添加索引等组合在一起，都可以：

1、同时删除多列
alter table user
drop column email,
drop column education,
drop column job,
drop column marriage,
drop column phone;
2、列和索引一起删除：
alter table user
  drop COLUMN user_uuid,
  drop COLUMN code,
  drop COLUMN name,
  drop COLUMN age,
  drop KEY user_uuid;
3、同时添加多列和索引
ALTER TABLE table_name
  ADD COLUMN `uuid` BIGINT NOT NULL AFTER `id`,
  ADD COLUMN `code` VARCHAR(10) COMMENT '编码' AFTER uuid,
  ADD COLUMN `name` VARCHAR(20) COMMENT '名字' AFTER code,
  ADD UNIQUE KEY (uuid);
4、同时添加多列、修改列、添加索引
ALTER TABLE table_name
  ADD COLUMN `uuid` BIGINT NOT NULL AFTER `id`,
  ADD COLUMN `code` VARCHAR(10) COMMENT '编码' AFTER uuid,
  ADD COLUMN `name` VARCHAR(20) COMMENT '名字' AFTER code,
  MODIFY COLUMN `enabled` BOOLEAN NOT NULL DEFAULT TRUE,
  ADD UNIQUE KEY (uuid);
change column 修改列名
change column 可以修改列名、列数据类型，或者调整顺序

alter table table_name
CHANGE COLUMN old_name new_name BIGINT NOT NULL COMMENT '用户id'
modify column 调整列顺序
modify column 可以列数据类型，或者调整顺序，但不能修改列名。
column_name 放到 column_name2 后面

alter table table_name
modify column column_name tinyint(4) NOT NULL DEFAULT '0' after column_name2;
modify column 修改列类型
无论 column_name 原来是什么类型，直接改为想要的类型

alter table table_name
modify column column_name varchar(22);
alter table user_employee_info
modify column en_title varchar(1024) comment '英文title',
modify column cn_title varchar(1024) comment '中文title';
modify column 修改列注释
alter table table_name
MODIFY column column_name tinyint default '0' not null comment '用户身份, 0未知, 1非车主, 2意向金车主, 3定金车主, 4共同车主, 5正式车主'
modify column 修改列为大小写敏感的
修改表字段属性为大小写敏感的，即把列的字符序改为大小写敏感的字符序

ALTER TABLE table_name
MODIFY COLUMN code varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin not null comment '大小写敏感的code';
修改表默认字符集和所有字符列的字符集
只修改表默认字符集

alter table page_view default charset utf8;
修改表默认字符集 同时 把所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集
alter table page_view convert to charset utf8;
-- 同时修改字符序
alter table page_view convert to charset utf8 COLLATE utf8_general_ci;
auto_increment 修改自增ID
alter table table_name auto_increment=73;

rename table 表重命名
RENAME TABLE old_table TO new_table;
等价于
ALTER TABLE old_table RENAME new_table;

13.1.33 RENAME TABLE Statement
https://dev.mysql.com/doc/refman/5.7/en/rename-table.html
唯一约束
唯一键约束，可以在一个列上添加约束，也可以在多个列上添加唯一约束。

在一列上添加唯一约束，主要是让该列在表中只能有唯一的一行，例如注册邮箱时的邮箱名、手机号等信息
创建表时添加唯一约束
在username列上添加唯一约束

CREATE TABLE `t_user` (
    `Id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(18) NOT NULL unique,
    `password` varchar(18) NOT NULL,
    PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1018 DEFAULT CHARSET=gbk;