MySQL 了解_mysql了解

前言

API

• 菜鸟教程
• W3Cshool

学习

官网 数据库样例 ，如sakila database等

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

英语

临时表temporary、虚拟表vitual table

概念

• 虚拟表、临时表
  SQL查询时，每一步骤都会为下一个步骤生成一个虚拟表（vitual table，也称临时表 temporary），这个虚拟表将作为下一个执行步骤的输入。
• 驱动表
  多表连接查询时，MySQL 优化器会确定以谁为驱动表（也就是说以哪个表为基准），MySQL 的解决方法：哪个表的结果集小，就以哪个表为驱动表（小结果集驱动大结果集）。验证的话，在 EXPLAIN 结果中，第一行出现的表就是驱动表。
  当然MySQL优化器实际的处理方式会复杂许多，具体可以参考：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
2
3
4
5
6
7
8
9
10
11

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中使用列的别名也是不被允许的。
  1
  2

• group by 分组
  对 VT4 分组得到 VT5；

• having 分组过滤
  对分组后的 VT5 进行条件过滤提到 VT6；

  having 子句主要和 group by 子句配合使用。
  1

• select
  从 VT6 中选出我们需要的内容，得到 VT7；

  此时才执行到SELECT子句（不要以为SELECT子句被写在第一行，就是第一个被执行的）。
  1

• 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万行，然后再进行读取，而扫描的过程是一个非常低效的过程。
  1
  2
  3
  4

常用语句

变量声明

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

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

控制流程函数

IF

1、IF 表达式

IF(expr1, expr2, expr3)
1

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 
1
2
3
4
5
6
7

CASE

CASE value 
    WHEN condition1 THEN result1 
    WHEN condition2 THEN result2 
    ... 
    ELSE result_n 
END AS '别名'
1
2
3
4
5
6

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

IFNULL

IFNULL(expr1, expr2)
1

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';
1
2

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

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

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
1
2
3
4
5
6
7