mysql简单介绍2.0

含有<<SQL必知必会(第5版)>> <>两本书的内容

1. distinct 去除重复数据

select school, teacher_age from xin_teacher_t

select distinct school, teacher_age from xin_teacher_t

select distinct school, teacher_age, id from xin_teacher_t

2. order by 排序

默认是升序
select teacher_age from xin_teacher_t order by teacher_age

select teacher_age, id from xin_teacher_t order by teacher_age ,id desc
第一条件相同时再按第二个条件排序，第一个条件没指明升序或者降序，第二个指明了，这种情况第一个条件还是默认升序

3. mysql ddl collate 设置校对字符集

utf8_bin：是以二进制值比较，也就是区分大小写
utf_8_general_ci：一般比较，不区分大小写

4. generated alwanys as virtual 虚拟列

虚拟列不支持insert和update

`a_name_obj` text comment '';
`a_name` varchar(200) character set uft8 collate utf8_bin generated always as (json_unquote(json_extract(`a_name_obj, '$.pid'`))) virtual comment '';
1
2

插入a_name列数据{“pid”:“111”}，列a_name会自动插入值111

5. json_unquote 去掉引号

6. json_extract 提取json中的值（根据键取值）

7. mysql使用与索引失效关系

name = upper(#{name})：索引不会失效
upper(name) = #{name}：索引会失效
1
2

8. 删除：truncate delete drop

1. truncate 删除表结构 -> 新建表结构（表数据清空），这个效率比delete高
2. delete 删除表数据
3. drop 删除表结构

9. 联合索引字段顺序问题

重复率低的字段在前，因为更有区分度，索引更好的起到作用，当使用到部分索引时，前面的使用度更高

10. null 与空（“”）的区别

mysql中字段值是null，java属性值映射为null；
mysql中字段值是“”，java属性值映射为“”；
且StringUtils.equals(null, “”) = false，但实际业务中我们应该认为它没有改变

11. order by field (name, ‘xiaohua’, ‘xiaowang’)

# mysql函数：按name字段排列，排序时'xiaohua’在前
order by field (name, 'xiaohua', 'xiaowang')

# mysql函数：按name字段排列，排序时'xiaowang’在前
order by field (name, 'xiaohua', 'xiaowang') desc
1
2
3
4
5

12. left join有时可能会导致全表扫描

右表不为空时尽量用join（inner join，同join）

13. 查询单个记录的时候尽量用limit 1，不过有脏数据会感知不到

因为多条数据会返回1条，所以有多条数据有脏数据的场景就被规避了

14. mysql Unsigned：表示无符号，非负数

15. 数据库三范式

第一范式（1NF）：每列都是原子性的，不可再拆分
第二范式（2NF）：每行都唯一的，且非主属性与主属性关联
第三范式（3NF）：非主属性与主属性都是直接关联，而非间接关联

间接关联：小明 -> 租房 -> 价格，小明与租房价格就是间接关联
主属性：一般都是指主键，有的表没有主键，唯一代表每行的列就是主属性
要求：尽量满足第一、第二范式，第三范式可以不满足，增加一些冗余字段，与主属性无直接关系也是可以的

16. 数据库update操作时，如果所有where条件都没有使用到索引就会锁表

只要有一个where条件用到了索引，那么就只是锁满足这个条件的那些行，而不会锁整个表，所以update时要在where条件中包含索引列，不包含时可以先查出满足条件的id，再update，这样就用到了覆盖索引，避免回表，且不会锁表了。

17. mybatis查询语句，没有查询出记录时是返回null，不是空对象

18. 全局锁：让整个库所有表都处于只读状态，禁止插入和更新

主要是为了全库备份，防止各表数据不统一

19. 表锁：分为表锁和MDL（Metadata Lock，无数据锁）

20. mysql事务命令

开始事务：start transaction;
提交事务：commit
回滚事务：rollback
退出事务：quit
开启两个事务：打开两个界面，执行start transaction

21. 表字段值

表字段状态、阶段等都应该用单个字符（推荐varchar类型的数字）来表示，在注释维护好含义，因为这样匹配时更轻量级

22. 对表设置字符集：CHARSET=utf8mb4 collate=utf8_bin

对字段单独设置字符集：collate utf8_bin

23. 修改表的排序规则只对新字段有效，可通过脚本修改

alter table table1 convert to character set utf8 collate utf8_general_ci

24. show full columns form tables; 查看字段校对（核对）字符集

25. left join 索引失效原因

连接字段核对字符集不同；
左表较大

26. 笛卡尔积查询

a表3行4列，b表2行5列；
select * from a, b;
笛卡尔积查询结果：3*2=6行
4+5=9列

27. mysql join（left join，inner join）规则

mysql join的实现是通过nested loop join算法，通过驱动表作为基础数据，循环这些基础数据一条一条的作为条件到另一个表查询数据，然后合并结果，如果还有第三个参与join，将前两个表join的结果集作为查询条件去这张表查询，所以基础驱动表越小越好；
join时，mysql在没有其他过滤条件的情况下，一般选择小表作为驱动表，而left join一般选择左表作为驱动表，所以尽量不要用left join，而用inner join；
总结：

1. 表连接就是一个表的数据一条条的作为条件去另一张表查数据，然后返回合并结果，所以驱动表尽量要小
2. 而left join是以左表作为驱动表，从左往右进行连接，有可能左表较大，查询效果较差，也就是说left join左表是小表，查询效果好，左表是右表，查询效果差
3. 而join是以小表作为驱动表，表从小到大进行连接，查询效率更高
4. 必须使用left join时以数据量较小的表作为左表

28. 子查询会锁表

28. 更新条件没有用到索引也会锁表

show engine innodb status 查询存储引擎日志

29. 字段 not null与default null互转时的情况

当列字段not null改为default null不会有问题，当default null改为not null时，若没有null值不会有问题，
若有null，会报错；但新建一列为非空时，值默认设为0；设为非空时，插入一条记录时，非空列一定要设值，否则会报错

30. sql语句的执行过程

1. 从数据库连接池中获得一个连接
2. sql接口接收sql命令，有缓存则直接返回缓存结果，没有缓存继续下面的步骤
3. 解析器对sql语句进行解析，理解这个sql要干什么
4. 优化器对sql进行优化，调整执行顺序，产生执行计划
5. 执行器调用存储引擎对表进行读写
6. sql接口返回sql语句的执行结果

31. using filesort 非索引排序