MySQL SQL 语句

Chapter 13 SQL Statements
https://dev.mysql.com/doc/refman/5.7/en/sql-statements.html

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
) t;

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;

用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 表间数据复制

数据插入时某个id实时查询获取

向 user 表插入数据，但 class_id 不能写死，需要动态去查 ‘高三八班’ 的 class_id。
可用 insert select 实现，select 语句从 class 表查，只取id结果，其他都是 user 常量。

INSERT INTO user (name, gender, class_id)
SELECT '小明', '男', id FROM class WHERE name='高三八班';

同一数据库内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

truncate

https://dev.mysql.com/doc/refman/5.7/en/truncate-table.html

truncate 是 ddl 语句，需要 drop 权限。

清空表truncate和delete区别

truncate table wp_comments;
delete from wp_comments;

这两者都是将wp_comments表中数据清空，不过也是有区别的，如下：

truncate 是整体删除（速度较快），delete 是逐条删除（速度较慢）。
truncate 不写服务器log，delete 写服务器log，也就是 truncate 效率比delete高的原因。
truncate 不激活trigger(触发器)
truncate 会重置 auto_increment 字段的值为 1。delete 删除后，auto_increment 依旧是接着被删除的最近的那一条记录ID加1后进行记录。
truncate 不走事务，无法回滚。delete 有事务，可以回滚。
truncate 也不会生产大量日志写入日志文件。delete 会产生大量行日志。

关于加锁：

TRUNCATE TABLE 命令会获取一个表级别的排它锁（X锁），这意味着其他会话无法同时对同一张表执行写操作，包括插入、更新和删除等操作。这样做是为了确保 TRUNCATE TABLE 命令能够安全地截断表并清空所有数据。由于它通常比 DELETE 操作更快，所以锁定时间相对较短。
DELETE 语句通常会以行级别的方式获取锁。它会逐行删除表中的数据，并在每个被删除的行上获取锁。这意味着其他会话仍然可以同时对表执行读操作，但在删除大量数据时可能会导致锁冲突和性能问题。

delete

外键导致删除失败

比如从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
);

删除重复行只保留id最大的

删除 user_id 重复的数据，保留id最大的

DELETE FROM drive_booking
WHERE id NOT IN (
    SELECT MAX(id)
    FROM drive_booking
    GROUP BY user_id
);

报错 [HY000][1093] You can’t specify target table ‘drive_booking’ for update in FROM clause
https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause

原因：
在MySQL中不允许在 UPDATE 或 DELETE 语句中使用子查询来操作同一个表

改为：

DELETE FROM drive_booking
WHERE id NOT IN (
    select mid from (
        SELECT MAX(id) mid
        FROM drive_booking
        GROUP BY user_id
    ) as a
);

重复行只保留一条

删除表中多余的重复记录，重复记录是根据单个字段（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);

alter table 重建表释放磁盘

有大量增删改操作的表在 delete 删除数据后磁盘空间也无法释放，通过 alter table t_name engine=Innodb; 重建表释放磁盘空间。

内部流程：
建立临时文件tmp_file，把表的B+树存储到临时文件中。若此时有对表的操作，则会记录在row log文件中。
把数据从原表全部刷到临时文件后，此时临时文件的数据就跟原表的数据一致。
最后用临时文件替换表A的数据文件。

update

update多列

update user u
set u.name = '张三', u.mobile = '13613661366'
where u.id = 23;

update replace 替换更新

把 name 里的张都替换成王

update table1
set name = replace(name, '张', '王‘)
where name like '%张%';

还可以结合 regexp 操作符正则匹配，把姓张的都替换成王

update table1
set name = replace(name, '张', '王‘)
where name regexp '^张';

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 条件表达式更新

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)
);

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 `idx_car_order_no`(`car_order_no`),
  KEY `idx_vehicle_id` (`vehicle_id`),
  KEY `idx_vin_code` (`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)

前缀索引(最大3072字节)

InnoDB 索引长度上限是 767 字节，当启用 innodb_large_prefix 时，上限可以达到 3072 字节。
如果字段过长，可以只使用前缀创建索引，即前缀索引。
如果是 utf8 编码，占 3 个字节，可以索引最大 3072/3=1024 个字符。
如果是 utf8mb4 编码，占 4个字节，最大可索引 3072/4=768 个字符。

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)

[42000][1071] Specified key was too long; max key length is 3072 bytes

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

拷贝创建新表

创建新表同时拷贝数据(丢失约束等)

复制表结构及数据到新表
CREATE TABLE 新表 SELECT * FROM 旧表
这种方法会将oldtable中所有的内容都拷贝过来，当然我们可以用delete from newtable;来删除。
不过这种方法的一个最不好的地方就是新表中没有了旧表的primary key、Extra（auto_increment）等属性。需要自己用alter table语句添加，而且容易搞错。

只拷贝表结构(丢失约束等)

只复制表结构到新表
CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
其中的where只要是个恒为false的表达式即可，此方法同样会丢失约束等额外信息

只拷贝表结构(保留约束等)

CREATE TABLE 新表 LIKE 旧表
此方法会保留表的约束等所有额外信息，推荐使用

创建新表并复制数据(保留约束等)

完整复制表的方法:
先创建新表，保留约束等，在插入旧表的所有数据
CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

例如

create table user_bk like user;
insert into user_bk select * from user;

MySQL复制表结构表数据
https://www.cnblogs.com/emanlee/p/5140670.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

修改数据库名(数据库重命名)

Mysql 没有直接更改数据库名称的命令。

RENAME DATABASE 这条命令在 MySQL 5.1.7 中被加入，但很快就发现这条命令所带来的危险，于是在 MySQL 5.1.23 中这条命令被移除。

可通过 mysqldump 导入导出来复制数据库，数据量大时谨慎使用
在数据库外执行以下命令，将 db_old 改名为 db_new

mysqladmin -uroot -ppswd create db_new
mysqldump -uroot -ppswd db_old | mysql -uroot -ppswd db_new

之后选择删除 db_old

唯一约束

唯一键约束，可以在一个列上添加约束，也可以在多个列上添加唯一约束。

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

创建表时添加唯一约束

在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;

在多列上添加唯一约束

CREATE TABLE `jw_resource` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `resource_name` VARCHAR(128) DEFAULT NULL,
  `resource_type` TINYINT(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `resource_name` (`resource_name`,`resource_type`)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=gbk;

在已有表上添加唯一约束

ALTER TABLE `t_user` ADD unique(`username`);

或者：

create unique index UserNameIndex on 't_user' ('username');

在多列上添加唯一约束：

ALTER TABLE table_name
ADD UNIQUE KEY index_name(resource_name, resource_type);

查看唯一约束

show index from table_name;

修改唯一约束

必须先删除唯一约束，再添加唯一约束

删除唯一约束

注意唯一约束用index关键字，因为唯一约束其实就是索引

ALTER TABLE table_name DROP INDEX index_name;

MySQL中添加唯一约束和联合唯一约束
https://blog.csdn.net/yumushui/article/details/38960619

外键约束

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
FOREIGN KEY 约束可定义在列级，也可以定义在表级。
CONSTRAINT关键字可为约束命名。
ON DELETE CASCADE 声明级联删除
ON UPDATE CASCADE 声明级联更新

定义表时添加外键约束

下面是postgresql中的一个建表语句：

CREATE TABLE "ais"."re_ais_opencloseclass_agent" (
    "agent_id" int4 NOT NULL,
    "airline" varchar(2) COLLATE "default" NOT NULL,
    "savetime" int8 NOT NULL,
    "saveuser" text COLLATE "default" NOT NULL,
    CONSTRAINT "idx_re_ais_opencloseclass_agent_pk" PRIMARY KEY ("agent_id"),
    CONSTRAINT "re_ais_opencloseclass_agent_agent_id_fkey1" FOREIGN KEY ("agent_id") REFERENCES "ais"."re_ais_base_agent" ("agent_id") ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT "re_ais_opencloseclass_agent_agent_id_airline_key" UNIQUE ("agent_id", "airline")
)

在现有表上添加外键约束

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

或者：

alter table orders
add FOREIGN KEY user_id_fk(user_id) REFERENCES user(id);

修改约束

若要修改主键约束、外键约束、唯一约束、检查约束，必须先删除现有的约束，然后再用新定义重新创建该约束。

删除外键约束

ALTER TABLE table_name
DROP FOREIGN KEY foreign_key_name[1,...n]

Can’t write; duplicate key in table xxx

错误：
使用 Jpa 的 SpringBoot 服务启动时报错：

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Unable to execute schema management to JDBC target [alter table common_user_role add constraint FKjui11rhjxr1u2hd6un2fdggkp foreign key (role_id) references common_role (id)]
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Can't write; duplicate key in table '#sql-1_42613'

由于设置了 hibernate.hbm2ddl.auto=update 启动时执行了 alter 语句，添加一个外键约束时失败了。

原因：
数据库中已有一个同名约束，即约束的名字 FKjui11rhjxr1u2hd6un2fdggkp 重复了。
MySQL 约束名在整个数据库中必须是唯一的，只在一个表上是唯一的还不行。
也就是说，假如同数据库的表 tableA 上有名字是 constraintA 的约束，那么 tableB 上再创建名为 constraintA 的约束就会失败。

在 information_schema 库的 KEY_COLUMN_USAGE 表中可以查看某个约束名在数据库上是否已经存在：

SELECT TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME = 'FKjui11rhjxr1u2hd6un2fdggkp';

解决：
果然同一个库上有个测试表上已有名字是 FKjui11rhjxr1u2hd6un2fdggkp 的约束，手动 drop 表后解决。

mysql外键

在MySQL 3.23版本后，InnoDB引擎类型的表支持了外键约束，外键主要用于引用和参照完整性的约束检查。外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。但是外键的使用是有一些必要条件的：

两个表必须是InnoDB表，MyISAM表暂时不支持外键（据说以后的版本有可能支持，但至少目前不支持）；
外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；
外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

在InnoDB存储引擎中，对于一个外键列，如果没有显示地对这个列加索引，InnoDB存储引擎会自动对其加一个索引，因为这样可以避免表锁。这比Oracle数据库做得好，Oracle数据库不会自动添加索引，用户必须自己手动添加，这也导致了Oracle数据库中可能产生死锁。

外键的定义语法：

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法可以在CREATE TABLE和ALTER TABLE时使用，如果不指定CONSTRAINT symbol，MySQL会自动生成一个名字。对于ON DELETE、ON UPDATE表示事件触发限制。

一般来说，称被引用的表为父表，引用的表为子表。外键定义时ON DELETE和ON UPDATE表示在对父表进行DELETE或UPDATE操作时，对子表所做的操作，可定义的子表操作有：

CASCADE（跟随外键改动）：表示当父表发送DELETE或UPDATE操作时，对相应的子表中的数据也进行DELETE和UPDATE操作。
SET NULL（设空值）：表示当父表发送DELETE或UPDATE操作时，对相应的子表中的数据更新为NULL值，但子表对应的列必须允许为NULL。
SET DEFAULT（设默认值）：表示当父表发送DELETE或UPDATE操作时，对相应的子表中的数据更新为默认值。
NO ACTION（无动作，默认的）：表示当父表发送DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。
RESTRICT（限制外表中的外键改动）：表示当父表发送DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。如果定义外键时没有指定ON DELETE或ON UPDATE，RESTRICT就是默认的外键设置。

在其他数据库中，如Oracle数据库中，有一种称为延时检查（deferred check）的外键约束，即检查在SQL语句运行完成后再进行。而目前MySQL数据库的外键约束都是即时检查，因此从上面的定义可以看出，在MySQL数据库中NO ACTION和RESTRICT的功能是相同的。

对于参照完整性约束，外键能起到一个非常好的作用。但是对于数据的导入操作时，外键往往导致在外键约束的检查上花费大量时间。因此MySQL数据库的外键是即时检查的，所以对导入的每一行都会进行外键检查。但是用户可以在导入过程中忽视外键的检查，如：

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA ....

mysql> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)

对于外键值的插入或更新，首先需要检查父表中的记录，既SELECT父表。但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这会发生数据不一致的问题，因此这时使用的是SELECT…LOCK IN SHARE MODE方式，即主动对父表加一个S锁。如果这时父表上已经这样加X锁，子表上的操作会被阻塞，如下：

先创建测试环境，如下：

# 创建parent表;
create table parent(
  tag_id int primary key auto_increment not null,
  tag_name varchar(20)
);

# 创建child表;
create table child(
  article_id int primary key auto_increment not null,
  article_tag int(11),
  CONSTRAINT  tag_at FOREIGN KEY (article_tag) REFERENCES parent(tag_id)
);

# 插入数据;
insert into parent(tag_name) values('mysql');
insert into parent(tag_name) values('oracle');
insert into parent(tag_name) values('mariadb');

开始测试

# Session A
mysql> begin
mysql> delete from parent where tag_id = 3;

# Session B
mysql> begin
mysql> insert into child(article_id, article_tag) values(1,3);   #阻塞

Session B 执行insert语句时被阻塞。当Session A手动执行commit提交事务后，Session B才能继续。

在上述的例子中，两个会话中的事务都没有进行COMMIT或ROLLBACK操作，而会话B的操作会被阻塞。这是因为tag_id为3的父表在会话中已经加了一个X锁，而此时在会话B中用户又需要对父表中tag_id为3的行加一个S锁，这时INSERT的操作会被阻塞。设想如果访问父表时，使用的是一致性的非锁定读，这时Session B会读到父表有tag_id=3的记录，可以进行插入操作。但是如果会话A对事务提交了，则父表中就不存在tag_id为3的记录。数据在父、子表就会存在不一致的情况。

MySQL InnoDB外键约束详解 - 运维那点儿事质量很高的文章
http://www.ywnds.com/?p=9135

外键与锁

在mysql的多个存储引擎中，innodb支持外键，但是由于外键，也会对innodb表增加锁定机制；

所有的外键相关的操作都在数据更改时，比如检查数据完整性、增加锁定等；

假设一个表为 parent ，一个表为child，child表有外键pid REFERENCES parent(id)；在一个session中set autocommit=0，执行对parent或child的操作，在另一个
session执行对child或parent的操作，从而得出以下结论：

一、对父表的操作
1、insert to parent ，新插入行的id值为XXX
child会锁外键值为XXX的行,不会锁其他行

2、update parent ，原id为XXX，现id为YYY
child会锁外键值为XXX,YYY的行,不会锁其他行

3、delete from parent ，删除行的id为XXX
child会锁外键值为XXX,不会锁其他行

二、对子表的操作
1、insert to child，插入行的外键值为XXX
parent 会锁值XXX的行,不会锁其他行

2、update child，更新行的外键值原为XXX，现为YYY
parent 会锁XXX,YYY行,但要注意:存在间隔锁,也会锁其他行(XXX,YYY之间的位置)

3、delete from child，删除行的外键值为XXX
parent 会锁XXX的行,但要注意:存在间隔锁,也会锁其他行(XXX-1的位置)

mysql 外键锁机制
https://blog.csdn.net/lan12334321234/article/details/70049370

InnoDB会自动为外键创建索引

下面是 mysql(mariadb) 中的一个建表语句，原始sql如下：

-- 用户地址表, 常用地址和收货地址
DROP TABLE IF EXISTS user_address;
CREATE TABLE user_address (
  id               BIGINT                        NOT NULL AUTO_INCREMENT,
  app_id           INT                           NOT NULL,
  user_id          BIGINT                        NOT NULL,
  status           ENUM ('enable', 'disable')    NOT NULL DEFAULT 'enable'
  COMMENT '状态',
  province_id      BIGINT
  COMMENT '省份id',
  province         VARCHAR(64)
  COMMENT '省份',
  city_id          BIGINT
  COMMENT '城市id',
  city             VARCHAR(64)
  COMMENT '城市',
  region_id        BIGINT
  COMMENT '区县id',
  region           VARCHAR(64)
  COMMENT '区县',
  detailed_address VARCHAR(512)                  NOT NULL
  COMMENT '详细地址',
  type             ENUM ('usual', 'deliverable') NOT NULL
  COMMENT 'usual:常用联系地址，deliverable:收货地址',
  priority         INT                           NOT NULL DEFAULT 1
  COMMENT '序号，用于排序',
  is_default       BOOLEAN                       NOT NULL DEFAULT FALSE
  COMMENT '是否默认地址',
  ext_data         VARCHAR(1024)                 NOT NULL
  COMMENT '姓名、手机号地区码、手机号、别名,json字符串',
  `create_time`    DATETIME                      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`    TIMESTAMP                     NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user (id),
  FOREIGN KEY (province_id) REFERENCES china_region (id),
  FOREIGN KEY (city_id) REFERENCES china_region (id),
  FOREIGN KEY (region_id) REFERENCES china_region (id)
)
  ENGINE = InnoDB,
  DEFAULT CHARSET = utf8mb4;

创建完后在 DataGrip 中查看建表语句的话，发现多了几个外键的创建索引语句，这是mysql自动给加外键的索引

create table user_address
(
    id bigint auto_increment primary key,
    user_id bigint not null,
    province_id bigint null comment '省份id',
    province varchar(64) null comment '省份',
    city_id bigint null comment '城市id',
    city varchar(64) null comment '城市',
    region_id bigint null comment '区县id',
    region varchar(64) null comment '区县',
    detailed_address varchar(512) not null comment '详细地址',
    type enum('usual', 'deliverable') not null comment 'usual:常用联系地址，deliverable:收货地址',
    priority int default '1' not null comment '序号，用于排序',
    is_default tinyint(1) default '0' not null,
    ext_data varchar(1024) null comment '姓名、手机号地区码、手机号、别名,json字符串',
    app_id int not null,
    status enum('enable', 'disable') default 'enable' not null comment '状态',
    create_time datetime default CURRENT_TIMESTAMP not null,
    update_time timestamp default CURRENT_TIMESTAMP not null,
    constraint user_address_ibfk_1
        foreign key (user_id) references user (id),
    constraint user_address_ibfk_2
        foreign key (province_id) references china_region (id),
    constraint user_address_ibfk_3
        foreign key (city_id) references china_region (id),
    constraint user_address_ibfk_4
        foreign key (region_id) references china_region (id)
);

-- 下面4个创建索引的语句是mysql自动加的
create index user_id on user_address (user_id);
create index city_id on user_address (city_id);
create index province_id on user_address (province_id);
create index region_id on user_address (region_id);

直接在命令行用 show create table user_address 看建表sql也会看到多了几个建索引的 KEY 语句：

CREATE TABLE `user_address` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `province_id` bigint(20) DEFAULT NULL COMMENT '省份id',
  `province` varchar(64) DEFAULT NULL COMMENT '省份',
  `city_id` bigint(20) DEFAULT NULL COMMENT '城市id',
  `city` varchar(64) DEFAULT NULL COMMENT '城市',
  `region_id` bigint(20) DEFAULT NULL COMMENT '区县id',
  `region` varchar(64) DEFAULT NULL COMMENT '区县',
  `detailed_address` varchar(512) NOT NULL COMMENT '详细地址',
  `type` enum('usual','deliverable') NOT NULL COMMENT 'usual:常用联系地址，deliverable:收货地址',
  `priority` int(11) NOT NULL DEFAULT '1' COMMENT '序号，用于排序',
  `is_default` tinyint(1) NOT NULL DEFAULT '0',
  `ext_data` varchar(1024) DEFAULT NULL COMMENT '姓名、手机号地区码、手机号、别名,json字符串',
  `app_id` int(11) NOT NULL,
  `status` enum('enable','disable') NOT NULL DEFAULT 'enable' COMMENT '状态',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `province_id` (`province_id`),
  KEY `city_id` (`city_id`),
  KEY `region_id` (`region_id`),
  CONSTRAINT `user_address_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `user_address_ibfk_2` FOREIGN KEY (`province_id`) REFERENCES `china_region` (`id`),
  CONSTRAINT `user_address_ibfk_3` FOREIGN KEY (`city_id`) REFERENCES `china_region` (`id`),
  CONSTRAINT `user_address_ibfk_4` FOREIGN KEY (`region_id`) REFERENCES `china_region` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10070 DEFAULT CHARSET=utf8mb4

show index 查看索引如下：

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.016 sec)

数据库管理语句

show 语句

show table status where

13.7.5.36 SHOW TABLE STATUS Statement
https://dev.mysql.com/doc/refman/5.7/en/show-table-status.html

SHOW TABLE STATUS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

Auto_increment 下一个自增id的值

例如

mysql> show table status where name='comment';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| 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 |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| comment | InnoDB |      10 | Compact    |   63 |            260 |       16384 |               0 |        16384 |         0 | 1520412196594532360 | 2020-01-10 12:46:18 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+---------------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.16 sec)

当前位置 : 首页 » 文章分类 : 开发 » MySQL-SQL语句

MySQL字段名规范

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

表名最长64字符

用函数名做列名时必须反引号引住

DQL

SQL优化

count(*)慢优化

id分段count后求和

非精确count-information_schema.tables

非精确-show table status like

非精确count-解析explain扫描行数

count(distinct)优化

int和varchar类型索引

去掉表字段上的unix_timestamp时间函数

create_time排序改为id排序避免回表

offset分页改写为where id条件

两表关联的连接表如何建索引

干预索引

use index(a,b) 限制索引范围

ignore index(a,b) 忽略索引

force index(a) 强制使用索引

like

like binary 区分大小写

like escape 转义通配符

regexp 正则匹配

order by field 按指定顺序排序

exists

重复数据取最后更新的

重复数据取最后插入的

重复数据取最大/最小

case when 条件表达式

case when 查及格和不及格人数

case根据列in分组

update中使用case when赋值

引用列别名

不等号

OR条件没加括号直接和其他条件AND

join

join和inner join是完全相同的

连接中的on和where

where逗号分隔等价于join或inner join

用left join代替not in

mysql中cross join和join以及inner join完全相同

mysql中没有full join全连接

union

union与union all区别

union all 多表count求和

SQL算术运算

distinct多列

group by

join 后 group by 聚合为拼接串

select字段必须是分组字段或聚集函数

order字段必须是分组字段或聚集函数

ERROR 1055 (42000) sql_mode=only_full_group_by

having子句中不能使用列别名

\G 列变行

DCL

手动提交事务

mysql start transaction 和 set autocommit = 0 的区别

show 语句

show status 查看服务端状态变量

show plugins 查看所有插件

DML

insert

on duplicate key update

insert 多条记录

insert select 表间数据复制

数据插入时某个id实时查询获取

同一数据库内insert select

不同数据库间insert select

表结构不相同insert select

truncate

清空表truncate和delete区别

delete

外键导致删除失败

delete语句使用别名

删除重复行

删除所有重复行

删除重复行只保留id最大的

`join`和`inner join`是完全相同的

`where`逗号分隔等价于`join`或`inner join`

mysql中`cross join`和`join`以及`inner join`完全相同

mysql中没有`full join`全连接

`\G` 列变行

`show index from table`查看索引