MySQL初阶 - 易错知识点整理（待更新）

MySQL初阶 - 易错知识点整理（待更新）

Note：这里根据 CSDN Mysql技能树 整理的易错题，可参考MySQL 有这一篇就够，MySQL详细学习教程（建议收藏），MySQL 菜鸟教程

一、数据库的使用

• 初始化账户（create + grant），参考mysql创建新用户并授权
• mysql登录，登录远程服务器，参考MySQL的两种登录方式，MySQL登录教程
  Note：
  • 如果Joe的开发机上已经部署了ssh服务，那么他不可以通过mysql -h Joe的开发机地址-u joe-p mysql在局域网内的任何计算机上使用。
  • mysql -h IP地址 -u 用户 -p 密码 -P 端口登录。
• 创建和删除数据库（create database 数据库名 或者 drop ...）
• 创建表（create）
• 自增字段（id INT PRIMARY KEY AUTO_INCREMENT）
• 删除表（drop table 表名），参考MySQL 有这一篇就够
• 数据库编码，参考MySql修改数据库编码为UTF8，show create database语法
• MySQL 存储引擎，确保数据表的存储引擎为innodb，参考MySQL常见的三种存储引擎，mysql索引详解
  Note：
  • InnoDB 支持事务和外键，有更好的并发能力。更新（删除）操作频率也高，或者要保证数据的完整性，比如OA自动化办公系统。
  • MyISAM 不支持事务和外键，结构简单，可以压缩为只读状态。以读写插入为主的应用程序，比如博客系统、新闻门户网站。
  • Memory 引擎将数据保存在内存中，重启会丢失数据，读速度快很快，适合作为会话表和缓存表。
  • 临时表不一定使用Memory引擎：
    • 内存临时表采用的是memory存储引擎
    • 磁盘临时表采用的是myisam存储引擎 (磁盘临时表也可以使用innodb存储引擎,通过internal_tmp_disk_storage_engine参数来控制使用哪种存储引擎,
      从mysql5.7.6之后默认为innodb存储引擎,之前版本默认为myisam存储引擎。

Note：

• DDL代表数据定义语言，是一种有助于创建数据库模式的SQL命令。DDL中常用的命令有：create，drop，alter，truncate和rename等等。
• DML代表数据操作语言，是一种有助于检索和管理关系数据库中数据的SQL命令。DML中常用的命令有：insert，update，delete和select等等。

二、数据类型

• 数值的隐式类型转换，可参考MySQL 的隐式转换
  Note：
  • 若字符串是以数字开头，并且全部都是数字，则转换的数字结果是整个字符串；部分是数字，则转换的数字结果是截止到第一个不是数字的字符为止。比如varchar str = "123dafa" // 转换为数字是 123
  • DECIMAL(P,D)：P是表示有效数字数的精度，P范围为1〜65，D是表示小数点后的位数，D的范围是0~30，MySQL要求D小于或等于(<=)P 比如DECIMAL(4,2)，值为23.46。
  • 比如对于int类型的x和y，select id, (x^2 + y^2)/2 as result from points;输出的result类型为decimal类型。
• 时间默认值（default约束，这题datetime和timestamp都行)，可参考MySQL默认值约束，mysql datetime与timestamp区别
• 文本字段，可参考MySQL 中 varchar 和 char 区别，MySQL 中面对VARCHAR 和 TEXT该如何选择 ？，mysql一个字符占用多少个字节
  Note：
  • varchar类型的长度是可变的，而char类型的长度是固定的，当定义为 char(10)时，即使插入的内容是 abc 3 个字符，它依然会占用 10 个字符，其中包含了 7 个空字符的存储空间。
  • char 长度最大为 255 个字符，varchar 长度最大为 65535 个字符；varchar类型的查找效率比较低，而char类型的查找效率比较高，因此变长度使用 varchar，固定长度使用char。
  • MySQL 5.0.3版的一项更改包括将VARCHAR字段的最大长度从255个字符增加到65,535个字符。这使得VARCHAR类型比以往任何时候都更类似于TEXT。虽然两种数据类型共享的最大长度为65,535个字符，但VARCHAR中的VAR表示您可以将最大大小设置为1到65,535之间的任何值。 TEXT字段的最大固定大小为65,535个字符。
  • MEDIUMTEXT最多可存储16 MB的字符串，LONGTEXT最多可存储4 GB的字符串。
• 二进制字段（该题目有点问题，应该使用mediumBlob），可参考MySQL各个类型详解（看评论），Blob数据类型及应用，MySQL：BLOB与TEXT及其最大存储限制
  Note：
  • blob用于存放二进制数，最大字节数为65535，即文件大小最大为65K；而varchar最大字符数为65535。
  • char,varchar,text是以字符为基本单位；而blob是以字节为基本单位，没有字符集的说法。

三、数据修改

• 插入数据（将源表数据复制到目标表中），可参考INSERT INTO SELECT 用法，select into from用法（mysql中不可用），select into from语句
  Note：
  • INSERT INTO SELECT语句从一个表复制数据，然后把数据插入到一个已存在的表中，要求目标表和源表都存在，mysql支持INSERT INTO SELECT语句。
  • SELECT INTO FROM语句在插入时会自动创建目标表，并将源表中指定字段数据插入到目标表中，这里要求目标表不存在。但mysql中不可用 SELECT INTO FROM语句，建议先创建目标表，接着使用INSERT INTO SELECT语句把源表数据转存到目标表中。
• 更新数据
• 删除数据

四、数据查询

• select查询，参考sql和MySQL的查询语句的执行顺序
  Note：
  • from
  • on
  • join
  • where
  • group by(开始使用select中的别名，后面的语句中都可以使用)
  • avg,sum…
  • having
  • select
  • distinct
  • order by
  • limit
    所有的 查询语句都是从from开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。
• where条件查询（指定查询语句的条件限制）
• 数值计算，参考MySQL运算符(外加优先级)
• 函数与过程（该题有问题），参考MySQL的存储函数与存储过程的区别，存储过程—输入参数(in) / 输出参数(out) / 输入输出参数(inout)，MySQL的存储过程—流程控制-case
  Note：
  存储函数与存储过程的区别
  • 存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。
  • 存储函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。
  • 存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；
  • 存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
  • 存储过程可以调用存储函数、但函数不能调用存储过程。
  • 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。
• 【问】如何使用SQL求交集（join、in、exists）或差集（not in、not exists），参考SQL求交集与差集，日期大小比较（DATEDIFF('2017-11-30','2017-11-27')）
  Note：
  • 假设我有一张会员注册表，表结构和数据如下所示：

    user_id | begin_date | end_date | pay
    100	|  2023-02-11 13:34:38	| 2023-02-12 13:34:43	 | 1
    101	| 2023-02-13 12:30:44	 | 2023-02-17 12:30:47	| 1
    102	| 2023-02-13 12:31:26    | 2023-02-17 12:31:29	| 1
    103	| 2023-02-15 12:35:22 	| 2023-02-16 12:35:28	 | 1
    104	| 2023-02-15 12:35:46	 | 2023-02-16 12:35:55	| 1
    105	| 2023-02-15 12:36:08 	| 2023-02-16 12:36:13	 | 1
    101	| 2023-02-16 12:30:44	 | 2023-02-17 12:30:47	| 1
    102	| 2023-02-16 12:31:26 	| 2023-02-17 12:31:29 	| 1
    101	| 2023-02-18 12:53:49 	| 2023-02-19 12:53:54	 | 1
    105	| 2023-03-12 13:43:39	 |  2023-03-14 13:43:46	| 1
    

    假设我想从上表中筛选出2023.2.14之后新注册的会员个数（标准答案为3，即103,104,105），SQL语句的编写思路如下：
    • 先查询在2023.2.14之前就已经注册的会员，查询字段为DISTINCT(user_id)，查询结果记为b；

      select DISTINCT(b.user_id) from (
      	select * from vip_test where DATEDIFF(begin_date,'2023-02-14') < 0
      ) as b
      ---
      user_id
      101
      102
      100
      

    • 接着查询在2023.2.14之后注册的会员（这些会员中可能包括2023.2.14之前已注册的会员），查询字段为user_id，查询结果记为a；

      select * from vip_test where DATEDIFF(begin_date,'2023-02-14') > 0
      ---
      user_id
      103
      104
      105
      101
      102
      101
      105
      

    • 以查询结果a作为基准，求解不在b.user_id中的a.user_id（即求a,b之间的差集，用not in或not exists），并且进行去重处理。

      #正确答案为3
      select count(DISTINCT(a.user_id)) from (
      		select user_id from vip_test where DATEDIFF(begin_date,'2023-02-14') > 0 ) as a
      where a.user_id not in ( 
      		select DISTINCT(b.user_id) from (
      			select * from vip_test where DATEDIFF(begin_date,'2023-02-14') < 0
      		) as b
      )
      ---
      count(DISTINCT(a.user_id))
      3
      

  • 下面列举出上面例题的两种错误写法：
    • 错误写法1：没有使用DATEDIFF进行日期的比较，忽略掉了105

      #错误答案为2
      select DISTINCT(a.user_id) from (
      		select * from vip_test where day(begin_date) > 14 ) as a
      where a.user_id not in ( 
      		select DISTINCT(b.user_id) from (
      			select * from vip_test where day(begin_date) < 14
      		) as b
      )
      ---
      user_id
      103
      104
      

    • 错误写法2：先使用inner join，对左右两表（左表为2023.2.14前，右表为2023.2.14后）中相同的user_id进行内连接，得到两表中user_id的交集，记作a；

      select DISTINCT(a.user_id) from (
      	select * from vip_test where DATEDIFF(begin_date,'2023-02-14') < 0 
      ) as a
      INNER JOIN (
      	select * from vip_test where DATEDIFF(begin_date,'2023-02-14') > 0 
      ) as b
      ON a.user_id = b.user_id
      ---
      user_id
      101
      102
      

      接着以全表为基准，使用not in求解不在a.user_id中的用户编号，此时查询结果多了一个100这个用户

    	#错误答案为4
    	select DISTINCT(user_id) from vip_test as c where user_id not in ( 
    		select DISTINCT(a.user_id) from (
    			select * from vip_test where DATEDIFF(begin_date,'2023-02-14') < 0 
    		) as a
    		INNER JOIN (
    			select * from vip_test where DATEDIFF(begin_date,'2023-02-14') > 0 
    		) as b
    		ON a.user_id = b.user_id
    	)
    	---
    	user_id
    	103
    	104
    	105
    	100
    

五、数据库组成

• 表的基本结构（InnoDB存储引擎用B+Tree实现其索引结构，B+树相较于B-树的特点是：非叶子节点只存储键值信息；所有叶子节点之间都有一个链指针；数据记录都存放在叶子节点中），可参考mysql索引详解

• 建表语句（根据业务要求建表）

• 主键（主键或唯一键可以被引用为外键约束foregin key，可 级联更新 和 级联删除），可参考MySQL外键约束详解

• 唯一约束1（如何保证user输入的book唯一，在 isbn 列上加唯一约束或者设置为主键），唯一约束2（不修改表字段，可添加唯一约束），可参考MySQL添加唯一约束（UNIQUE）
  Note：
  唯一约束用来保证一列（或一组列）中的数据是唯一的，类似与主键，但是有以下区别：

  • 表可包含多个唯一约束，但每个表只允许一个主键。
  • 唯一约束列可包含 NULL 值。
  • 唯一约束列可修改或更新。
  • 唯一约束列的值可重复使用。
  • 唯一约束不能用来定义外键。

• 自增序列（自增字段必须是主键；即使插入操作失败，自增计数仍然会被递增，下次操作使用下一个整数；），可参考关于 MySQL 自增 ID 的事儿，自增主键与UUID的优缺点
  Note：

  • 自增主键的优点：索引空间占比小、范围查询与排序都友好、避免像 UUID 这样随机字符串带来的页分裂问题；
    缺点：当系统与其它系统集成时，需要数据导入时，很难保证原系统的ID不发生主键冲突（前提是老系统也是数字型的）。特别是在新系统上线时，新旧系统并行存在，并且是异库异构的数据库的情况下，需要双向同步时，自增主键将是你的噩梦。
  • UUID唯一识别码 (Universally Unique Identifier)：是指在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的。
    优点：出现数据拆分、合并存储的时候，能达到全局的唯一性。
    缺点：影响插入速度，并且造成硬盘使用率低；uuid之间比较大小相对数字慢不少， 影响查询速度；uuid占空间大， 如果你建的索引越多，影响越严重。
  • 当自增 ID 到达上限后，受到主键数据类型的影响，计数器发放的下一个 ID 也是当前这个 Max ID ，当执行语句时则会提示主键冲突：1062 - Duplicate entry ‘4294967295’ for key ‘PRIMARY’，此时可以直接选择 Bigint 类型，它的取值范围是无符号情况下： 0 ~ 2^64–1（18446744073709551615），在一般场景下它是足够使用：假设每秒增加的记录数为1亿/秒，大约5849年才会用完。

• 视图（ 视图是从一个或多个表中导出来的表，是一种虚拟存在的表，其物理上是不存在的，但可用于查询），可参考【MySQL视图】视图的概念、创建、查看、删除和修改

• 计税（存储函数，create function得到个人所得税函数），可参考MySQL的存储函数
  Note：

  create function individual_income_tax(salary decimal(12, 4)) 
  returns decimal(12, 4)
  deterministic
  begin 
      -- ...
  end;
  

• 创建存储过程，参考存储过程——输入参数（in）、输出参数（out）、输入输出参数（inout）
  Note：

  create procedure sp_idt(in salary decimal(12, 4), out tax decimal(12, 4), out take_home decimal(12, 4)) 
  begin 
      set tax = individual_income_tax(salary);
      set take_home = salary - tax;
  end;
  

  触发器是一种特殊的存储过程。

• 删除函数（drop function individual_income_tax;）

• 触发器，交易审计（定义insert和delete类型的trigger），可参考MySQL触发器
  Note：

  • 触发器是一种特殊的存储过程，触发器和存储过程一样是一个能完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据库表中的数据执行DML操作（insert,update,delete）时自动触发这个SQL片段的执行, 无需手动调用。语法如下：

    create trigger 触发器名 before|after 触发事件
    on 表名 for each row
    begin
    	执行语句列表
    end;
    

  • 触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作，例如：

    create trigger trigger_test after insert
    on user for each row
    begin
    	insert into user_logs values(1,now,'有新用户增加');
    end 
    

  • MYSQL中触发器中不能对本表进行insert,update,delete操作,以免递归循环触发
  • 尽量少使用触发器,假设触发器触发每次执行1s, insert table 500条数据,那么就需要触发500次触发器,光是触发器执行的时间就要花费500s, 而insert 500条数据一共是1s，那么insert的效率就非常的低了。
  • 触发器是针对每一行的,对增删改非常频繁的表上切记不要使用触发器，因为他会非常消耗资源。
  • 查看触发器：show triggers;，删除触发器drop trigger if exists trigger_test;。

• 存储引擎修改（将MyISAM修改成innoDB：alter table goods engine innodb;）

六、查询进阶

• 别名（as可省略），可参考mysql中别名(列别名和表别名)，MySQL 别名
  Note：
  不能在WHERE子句中使用列别名。原因是当MySQL评估求值WHERE子句时，SELECT子句中指定的列的值可能尚未确定。
• BETWEEN AND（配Where不配Having，WHERE age BETWEEN 18 AND 24相当于WHERE age >= 18 AND age <= 24），可参考MySQL中的BETWEEN...AND的用法（BETWEEN是闭区间）
• MySQL透视表（透视表的核心就是按照行列同时分组（首行还是列名，其余行为汇总的数据），然后对分组后的值进行某种汇总运算，MySQL用group by进行分组，用case when ... then ... when ... then ... end增加多个判断条件），可参考Sql 实现数据透视表功能，MySQL之数据分组与数据透视表
• 数据去重 - DISTINCT，可参考DISTINCT关键字理解
• 排序 - order by ，可参考MySQL order by关键字详解
  Note：
  • 按dept升序按salary降序排序：select id, name, dept, salary from employee order by dept, salary desc;
• 两个结果集取并集 - UNION（不包括重复行，进行默认规则的排序），可参考MySQL的union 和 union all
  Note：
  • UNION 和 UNION ALL 内部的 SELECT 语句必须拥有相同数量的列。
  • 每条 SELECT 语句中列的顺序必须相同，因为union时是按照字段顺序匹配，而不是按照字段名称匹配。
  • union 对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序。
  • union all 对两个结果集进行并集操作，包括重复行，不进行排序。
• 正则表达式REGEXP 与 模糊查询like，可参考MySQL中的正则表达式（REGEXP），MySql like模糊查询语句用法，MySQL学习笔记: like和regexp的区别
  Note：
  • 可以在 WHERE 子句中使用LIKE子句，并且使用LIKE子句代替等号 =。

    '%a'     以a结尾的数据
    'a%'     以a开头的数据
    '%a%'    含有a的数据
    '_a_'    三位且中间字母是a的
    '_a'     两位且结尾字母是a的
    'a_'    两位且开头字母是a的
    

  • 搜索 Contacts.Phone 以查找以 00 结尾的所有电话号码：

    SELECT Surname, Surname, City, Phone
    FROM Contacts
    WHERE Phone REGEXP '\\d{8}00';
    

• 分页查询 - limit，可参考MySQL中limit用法

七、内置函数

• 锁函数，可参考MySQL加密/解密/加锁/解锁函数（password、md5、encode、get_lock、is_free_lock、is_used_lock等）
• JSON函数（mysql5.7以上提供了一种新的字段格式json，为的是实现非关系型数据库），可参考MySQL常用Json函数，mysql的json函数
  Note：
  MySQL里的json分为json array和json object。 $表示整个json对象，在索引数据时用下标(对于json array，从0开始)或键值(对于json object，含有特殊字符的key要用"括起来，比如$."my name")。
  例如：[3, {"a": [5, 6], "b": 10}, [99, 100]]，那么：

  $[0]：3
  
  $[1]： {"a": [5, 6], "b": 10}
  
  $[2] ：[99, 100]
  
  $[3] ： NULL
  
  $[1].a：[5, 6]
  
  $[1].a[1]：6
  
  $[1].b：10
  
  $[2][0]：99
  

• 类型转换（CAST()），可参考MySQL基础—数据类型转换CAST
  Note：
  错误语法：

  SELECT CAST('123' AS INT);  //语法错误，没有INT表达式
  

  正确语法：

  SELECT CAST('123' AS SIGNED);  //结果：123；结果转换为整数；
  select CAST('3.1415926' AS DECIMAL(4,2)); //结果：3.14；结果转换为浮点型；
  SELECT CAST(NOW() AS DATE); //结果：2020-10-22；结果转化为DATE类型；NOW() 输出为DATETIME类型，被转换成DATE类型；
  

• 编码转换（CONVERT()，不使用ENCODE，DECODE加密解密），可参考[Mysql] CONVERT函数，MySQL ENCODE和DECODE加密列
  Note：
  • 转换指定字符集

    //语法：CONVERT(expr USING transcoding_name)
    -- utf8mb4
    SELECT CHARSET('ABC');
    -- gbk
    SELECT CHARSET(CONVERT('ABC' USING gbk));
    

  • 转换指定数据类型

    //语法：CONVERT(expr,type)
    -- 2022-05-25
    SELECT CONVERT('2022-05-25', DATE);
    -- 2022-05-25 17:58:48
    SELECT NOW();
    -- 2022-05-25
    SELECT CONVERT(NOW(), DATE);
    

窗口函数：

• 窗口函数 - 头尾函数（FIRST_VALUE()，LAST_VALUE()分别获取分组后表的首行和末行数据），可参考MySQL FIRST_VALUE() 函数 | LAST_VALUE()函数 | NTH_VALUE()函数，row_number() over(partition by) 和 first_value over(partition by) 实用例子
  Note：窗口函数主要配合排序order by，分组partition by使用。
• 窗口函数 - 排名函数（RANK() 可以将数值映射跳跃间断的排名，会跳过重复的序号；而dense_rank()不会跳过重复的序号），可参考Mysql常用函数之Rank 排名函数
• 窗口函数 - 偏移函数（LAG()和LEAD()分别表示向下和向上偏移数据），可参考MYSQL lag() 和lead() 函数用法
• 窗口函数 - 分布函数（排名百分比PERCENT_RANK() 和 小于或等于其RANK值的百分比CUME_DIST()），可参考[Mysql] PERCENT_RANK()函数 | CUME_DIST()函数

时间日期函数：

• 获取“当天是几号“（DAY和DAYOFMONTH），可参考MySQL日期时间操作函数（挺全的），MySQL常用的日期时间函数
• 获取当前时间 - 不包含日期（curtime()和current_time()）
• 周几映射为整数（WeekDay(date_str) 值为0表示星期一），可参考MySQL weekday()函数
• 返回公元0年1月1日到给定日期的天数 - TO_DAYS(date_str)，可参考Mysql中TO_DAYS函数
• 通过给定公元0年1月1日起的天数，返回指定日期 - FROM_DAYS(days_count)，可参考MYSQL的日期处理函数to_days()和from_days()
• 从日期中提取你感兴趣的 - EXTRACT(unit FROM date)，可参考MySQL extract()函数
  Note：
  • unit参数的有效间隔主要包括：DAY, DAY_HOUR, DAY_MICROSECOND, DAY_MINUTE, DAY_SECOND, HOUR, HOUR_MICROSECOND, HOUR_MINUTE, HOUR_SECOND, MICROSECOND, MINUTE, MINUTE_MICROSECOND, MINUTE_SECOND
  • extract(second from date) 相当于 second(date)
  • extract(minute from date) 相当于 minute(date)
  • extract(hour from date) 相当于 hour(date)
  • extract(day from date) 相当于 day(date)
  • extract(week from date) 相当于 week(date)
  • extract(month from date) 相当于 month(date)
  • extract(quarter from date) 相当于 quarter(date)
  • extract(year from date) 相当于 year(date)
• UNIX 时间戳 和 日期互转 - UNIX_TIMESTAMP()，可参考mysql的UNIX_TIMESTAMP 和 FROM_UNIXTIME用法
• 日期时间构造方法 - MAKEDATE(year,n)和MAKETIME(hour,minute,second)
• 日期时间计算，可参考MySQL日期时间操作函数（挺全的），mysql 日期时间计算函数
• 日期格式化 - date_format(date,format)和time_format(time,format)分别将日期和时间转字符串，可参考MySQL日期格式化，日期格式转换(str_to_date,date_format,time_format)

八、聚合和分组

• 分组函数 - group by详解（可进行多个字段的分组，但非多次分组）
  Note：
  • 多个字段进行分组时，需要将多个字段，比如name和grade看成一个整体，只要是name和grade相同的可以分成一组；如果只是name相同，grade不同就不是一组；而不是先进行name分组，再进行grade分组。
• 聚合函数 - 统计（COUNT()，答案有误），可参考MySql统计函数COUNT详解（COUNT()在开发中常用来统计表中数据，全部数据，不为NULL数据，或者去重数据）
  Note：
  • COUNT(1)：统计不为NULL 的记录。
  • COUNT(*)：统计所有的记录（包括NULL）。
  • COUNT(字段)：统计该"字段"不为NULL 的记录。
    1.如果这个字段是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加。
    2.如果这个字段定义允许为null的话，判断到有可能是null，还要把值取出来在判断一下，不是null才累加。
  • COUNT(DISTINCT 字段)：统计该"字段"去重且不为NULL 的记录。
• 聚合函数 - 求和（SUM()，配合group by使用）
• 聚合函数 - 最小值（min()，配合group by使用）
• 聚合函数 - 最大值（max()，配合group by使用）
• 聚合函数 - 平均值（avg()，配合group by使用）
• 聚合函数筛选 - HAVING可筛选聚合函数处理后的结果，可参考MySQL 中 having的用法
  Note：
  • where字句在聚合前先筛选记录，执行顺序在group by和having字句前，因此where 不能筛选聚合函数值，即where salary = max(salary)是错误的。
  • having子句在聚合后对组记录进行筛选，这些数据是通过一些聚合函数产生的：

    //显示每个类型的用户数量．仅显示用户数量大于14的类型
    mysql> select type,count(*) as count from ts_user group by type having count >14;
    

  • having单独使用，与where类似：

    //查询用户id=1000的用户信息，查询结果一样
    SELECT name,sex FROM `ts_user`  having id = 1000 ;
    SELECT name,sex FROM `ts_user`  where id = 1000 ;
    

九、子查询

• 子查询（单列子查询包含多种操作符），参考【MySQL】子查询详解

• 带ANY的子查询（外层查询满足内层查询结果中的一个即可），参考MySQL使用IN、EXISTS、ANY、ALL关键字的子查询

• 带ALL的子查询（外层查询满足内层查询结果中的一个即可），参考MySQL使用IN、EXISTS、ANY、ALL关键字的子查询

• 带Exists的子查询（内层查询不返回结果表，返回真假值），参考MySQL使用IN、EXISTS、ANY、ALL关键字的子查询

  Note：

  • ANY关键字表示满足其中任意一个条件。使用ANY关键字时，只要满足内层查询语句返回的结果中的任意一个，就可以通过该条件来执行外层查询语句。

    //构造一个员工列表，排除每个部门最高工资的员工
    select id, name, dept, salary
    from employee as o
    where o.salary < any(select salary from employee as i where i.dept=o.dept)
    

  • ALL关键字表示满足所有条件。使用ALL关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。

    // 找出所有其所在部门没有助理（post 为 assistant）的员工信息
    select id, name, dept 
    from employee as o
    where 'assistant' != all(select post from employee as i where o.dept = i.dept); 
    

  • 使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回true，否则false。

    //找出所有其所在部门没有助理（post 为 assistant）的员工信息，使用exists实现
    select id, name, dept
    from employee as o
    where not exists(select * from employee as i where o.dept = i.dept and post='assistant'); 
    

• 带IN子查询（in用于列表查询，不用select），参考MySQL使用IN、EXISTS、ANY、ALL关键字的子查询
  Note：只有子查询返回的结果列包含一个值时，比较运算符才适用。假如一个子查询返回的结果集是值的列表，这时比较运算符就必须用IN运算符代替。

  // 查询出研发部（dept为'rd')和人力资源部(dept为'hr')的员工列表
  select id, dept, name, post 
  from employee
  where dept in ('dev', 'hr');
  

• 列子查询（子表和主表的列匹配，当指定列唯一时，可以进行类似group by的分组聚合），参考MySQL — 子查询 - 列子查询(多行子查询)

  // 在不使用group by下，统计每个部门的人数 
  select distinct(dept)  as dept,
                 (select count(*)
                  from employee as i
                  where i.dept = o.dept) as emp
  from employee as o;
  

十、连接查询

参考MySQL中的内连接，左连接，右连接，全连接，交叉连接等相关总结

• 自连接（INNER JOIN，自表与自表连接取交集，关键字A join B on ...，一般用于描述树级结构），例子如下：

  //现有 node 表如下：
  create table node(
      id int primary key auto_increment,
      pid int,
      content varchar(256)
  )
  //想要给出 content 以 fork- 开头的所有节点，和它们的子节点，输出 parent_id, parent_content, child_id, child_content 。
  	select l.id as parent_id, 
         l.content as parent_content,
         r.id as child_id,
         r.content as child_content
  from node as l
      join node as r on l.id = r.pid
  where l.content like 'fork-%';
  

• 左连接（LEFT JOIN，以A left join B on ...的左表A为基准），例子如下：

  //列出所有的部门，如果这个部门有部门助理（post 为 assistant），则将 stuff 的名字也列出来
  select d.id, d.name, e.name as assistant
  from department as d
      left join employee as e on e.dept = d.id
  where e.post = 'assistant'
  

• 右连接（RIGHT JOIN，以A right join B on ...的右表B为基准），例子如下：

  //写一个查询，找出部门信息写错的员工，这些员工所在的部门在 department 表中没有对应记录
  select e.id, e.name, e.dept
  from department as d
      right join employee as e on d.id = e.dept
  where d.id is null;
  

• 交叉连接（CROSS JOIN，交叉联接cross join（不带on进行值比较）也称作笛卡尔积：左表中的所有行，左表中的每一行与右表中的所有行组合；实际中用的少）

  //Joe 需要生成 goods 表中所有T恤（category为T-Shirt）的所有尺寸，尺寸信息在 size 表
  select g.id, g.name, s.name as size
  from goods as g
      cross join size as s
  where g.category = 'T-Shirt';
  

• 复杂连接 - 结合内/左/右连接求部门中工资最高的人

  //给出每个部门工资最高的员工的 id, name, dept, salary 四项信息
  select l.id, l.name, l.dept, l.salary
  from employee as l
           join (select max(salary) as salary, dept
                 from employee
                 group by dept) as r
                on l.dept = r.dept and l.salary = r.salary
  

  这里注意，下面这种写法是错误的，group by在对多字段分组时，是把多个字段看成一个整体，只有当多个字段相同时才分为一组，并不是多次分组：

  select id, name, dept, max(salary)
  from employee
  group by dept, id, name;
  

  上面语句等价于对主键id进行分组

  select id, name, dept, max(salary)
  from employee
  group by id;
  

十一、索引

• 关于索引的描述，可参考MySQL索引的创建与使用，mysql中的聚集索引(聚簇索引)、非聚集索引、稀疏索引、稠密索引
  Note：
  • 1）聚集索引：索引项的排序方式和表中数据记录排序方式一致的索引。聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
    2）非聚集索引： 索引顺序与物理存储顺序不同
    3）稠密索引：每个索引键值都对应有一个索引项
    4）稀疏索引：相对于稠密索引，稀疏索引只为某些搜索码值建立索引记录（类似于一级间接索引地址）
  • InnoDB默认对主键建立聚集索引；聚集索引可以应用于非自增的字段；索引可以基于一个或多个字段。
  • 索引的优势：
    提高查询效率（降低IO使用率）；降低CPU使用率；
    比如查询order by age desc，因为B+索引树本身就是排好序的，所以再查询如果触发索引，就不用再重新查询了。
  • 索引的弊端：
    索引本身很大，可以存放在内存或硬盘上，通常存储在硬盘上；
    索引不是所有情况都使用，比如①少量数据 ②频繁变化的字段 ③很少使用的字段；
    索引会降低增删改的效率。参考mysql索引详解
  • 索引的分类：
    1）单值索引
    2）唯一索引
    3）联合索引
    4）主键索引
• 创建索引（alter table ... add index或create index；一个类别category_id包含多种商品，因此对于goods表，不能为goods表创建关于category_id的唯一性索引），可参考MySQL索引的创建与使用，别踩坑！使用MySQL唯一索引请注意
  Note：
  • 索引创建（表创建之后）：

    ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL]  INDEX | KEY  [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法];
    

    或者

    CREATE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX  索引名 ON  表名(字段名) [USING 索引方法]；
    

  • 索引说明（索引方法默认使用B+树）：
    UNIQUE：可选。表示索引为唯一性索引。
    FULLTEXT：可选。表示索引为全文索引。
    SPATIAL：可选。表示索引为空间索引。
    INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是 一样的。
    索引名：可选。给创建的索引取一个新名称。
    字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。
    长度:可选。指索引的长度，必须是字符串类型才可以使用。
    ASC：可选。表示升序排列。
    DESC：可选。表示降序排列。
• 删除索引（alter table ... drop index），可参考MySQL索引的创建与使用