MySQL 了解

我不是女神ヾ 2022-05-27 12:53 97阅读 0赞

## 前言 ##

### API ###

*  [菜鸟教程][Link 1]
 *  [W3Cshool][]

### 学习 ###

官网 [数据库样例][Link 2] ，如sakila database等

[我的MYSQL学习心得（共17章）][MYSQL_17]

### 英语 ###

临时表temporary、虚拟表vitual table

### 概念 ###

*  虚拟表、临时表  
    SQL查询时，每一步骤都会为下一个步骤生成一个虚拟表（vitual table，也称临时表 temporary），这个虚拟表将作为下一个执行步骤的输入。
 *  驱动表  
    多表连接查询时，MySQL 优化器会确定以谁为驱动表（也就是说以哪个表为基准），MySQL 的解决方法：哪个表的结果集小，就以哪个表为驱动表（小结果集驱动大结果集）。验证的话，在 EXPLAIN 结果中，第一行出现的表就是驱动表。  
    当然MySQL优化器实际的处理方式会复杂许多，具体可以参考：[MySQL优化器如何选择索引和JOIN顺序][MySQL_JOIN]。
 *  Nested Loop  
    SQL，优化的目标是尽可能减少JOIN中Nested Loop的循环次数。  
    以此保证：永远用小结果集驱动大结果集。

注意：

1.  MySQL 没有 full join，用 union 代替；
2.  MySQL 没有 top 写法，用 limit 代替；

## 执行顺序 ##

7  SELECT 8 DISTINCT <select_list> 9 TOP num 1 FROM <left_table> 3 <join_type> JOIN <right_table> 2 ON <join_condition> 4 WHERE <where_condition> 5 GROUP BY <group_by_list> 6 HAVING <having_condition> 10 ORDER BY <order_by_condition> 11 LIMIT <limit_number>

1、SELECT 语句的顺序是固定的。例如GROUP BY子句不会位于WHERE子句的前面。  
2、SELECT 子句是必选的，其它子句如WHERE子句、GROUP BY子句等是可选的。  
3、执行过程

*  from 入口  
    from 入口是告诉数据库从哪张表开始查，若有 join 表，会先对 `<left_table>` 表和 `<right_table>` 表执行笛卡尔积（选择相对小的表做基础表），生成一个虚拟表 VT1；
 *  on 筛选器  
    从 VT1 中筛选出符合 ON逻辑表达式的 VT2；
 *  添加外部行  
    这一步只有在连接类型为OUTER JOIN时才发生，如 LEFT OUTER JOIN、RIGHT OUTER JOIN和FULL OUTER JOIN。在大多数的时候，我们都是会省略掉OUTER关键字的，但OUTER表示的就是外部行的概念。  
    若为 left outer jion 就把左表在第二步中过滤掉的行添加进来，若为 right outer join 就把右表在第二步中过滤掉的行添加进来，得到 VT3；
 *  where 条件过滤  
    只有符合 `<where_condition>` 条件的记录会被筛选出来，得到 VT4；
    
        a、由于数据还没有分组，因此现在还不能在where过滤条件中使用where_condition=MIN(col)这类对分组统计的过滤；
        b、由于还没有进行列的选取操作，因此在select中使用列的别名也是不被允许的。
 *  group by 分组  
    对 VT4 分组得到 VT5；
 *  having 分组过滤  
    对分组后的 VT5 进行条件过滤提到 VT6；
    
        having 子句主要和 group by 子句配合使用。
 *  select  
    从 VT6 中选出我们需要的内容，得到 VT7；
    
        此时才执行到SELECT子句（不要以为SELECT子句被写在第一行，就是第一个被执行的）。
 *  distinct  
    如果 select 中使用有 distinct，则会创建一张内存临时表（如果内存放不下，就需要存放在硬盘了）。这张临时表的表结构和上一步产生的虚拟表是一样的，不同的是对进行 distinct 操作的列增加了一个唯一索引，以此来除重复数据。
 *  top  
    top 结合 order by 筛选多少行，但这里的数据没有排序只是把多少行数据列出来而已。
 *  order by 排序  
    对 VT7 按指定列排序得到 VT8；
 *  limit  
    从 VT8 中选出从指定位置开始的指定行数据，常用来做分页；
    
        MySQL数据库的LIMIT支持如下形式的选择：limit n,m
        表示从第n条记录开始选择m条记录。
        对于小数据，使用LIMIT子句没有任何问题；
        对于大数据，使用LIMIT n, m是非常低效的。因为LIMIT的机制是每次都是从头开始扫描，如果需要从第60万行开始，读取3条数据，就需要先扫描定位到60万行，然后再进行读取，而扫描的过程是一个非常低效的过程。

## 常用语句 ##

### 变量声明 ###

MySQL 中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。

// set 赋值可以用“=”或“:=”，但 select 赋值必须用“:=” set @d=now();
    set @d2:=now();
    select @d, @d2, @d3:=now();

### 控制流程函数 ###

#### IF ####

1、IF 表达式

IF(expr1, expr2, expr3)

> expr1 为 TRUE (expr1 <> 0 and expr1 <> NULL) 时返回 expr2; 否则返回 expr3。  
> IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

2、流程控制语句（存储过程中）  
在存储过程中可作为流程控制语句使用。

IF search_condition THEN 
        statement_list  
    [ELSEIF search_condition THEN]  
        statement_list ...  
    [ELSE 
        statement_list]  
    END IF

#### CASE ####

CASE value 
        WHEN condition1 THEN result1 
        WHEN condition2 THEN result2 
        ... 
        ELSE result_n 
    END AS '别名'

> 若没有匹配到值，也没有写 else 时，返回 NULL；

#### IFNULL ####

IFNULL(expr1, expr2)

> expr1 为 NULL 时返回 expr2，不为 NULL 时返回 expr1 自身。  
> IFNULL() 的返回值是数字或是字符串，具体情况取决于其所使用的语境。

### 类型 ###

### 表是否有某列 ###

-- 有此列返回1，无此列返回0
    SELECT count(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='dev' AND TABLE_NAME='t_sys_dic' AND COLUMN_NAME='n_id';

### 表是否包含某些条件的记录 ###

exists(select * from a where id>100);
    -- 优化：增加`limit 1`，只要找到一条记录就不再往下找了
    exists(select 1 from a where id>100 limit 1);

### exists 和 in ###

### FIND\_IN\_SET(str,strlist) ###

假如字符串 str 在由N 子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。一个字符串列表就是一个由一些被‘,’符号分开的自链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则 FIND\_IN\_SET() 函数被优化，使用比特计算。如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。  
注意 find\_in\_set 是全表扫描的。

### 排序 ###

MySQL 默认对 null 记录排到数字后面。  
若希望提前：order by 语句对null字段的排序

// 将 null 强制放在最前
    ORDER BY IF(ISNULL(字段名),0,1) ASC
    
    // 将 null 强制放在最后
    ORDER BY IF(ISNULL(字段名),0,1) DESC
    或
    ORDER BY IF(ISNULL(字段名),1,0) ASC

[Link 1]: http://www.runoob.com/mysql/mysql-tutorial.html
[W3Cshool]: https://www.w3cschool.cn/mysql/
[Link 2]: https://dev.mysql.com/doc/index-other.html
[MYSQL_17]: http://www.cnblogs.com/lyhabc/p/3691555.html
[MySQL_JOIN]: http://www.orczhou.com/index.php/2013/04/how-mysql-choose-index-in-a-join/