超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

2022-08-15 22:46:57

我正在参与CSDN《新程序员》有奖征文
茫茫人海千千万万，感谢这一秒你看到这里。希望我的文章对你的有所帮助！

愿你在未来的日子，保持热爱，奔赴山海！

文章目录

1. DQL语言的学习

主要是用于对表的查询，而查询不会对数据库中的数据进行修改，只是一种显示数据的方式。这一章可以说是MySQL中重点的部分了。查询的关键字为：SELECT。

接下来的学习按层层渐进的方式。既然我们现在是来学习查询的操作，我们就得要有数据对吧，这里直接把数据先准备给大家吧，这里就可以不用来连插入修改其他的命令，主要是来练习查询的。这里准备的是我在尚硅谷网上视频中学习数据库的数据。非常感谢，我自己也学习得很深刻，讲解的也非常详细。本来是想直接把数据写在这里的，但是因为真的太长了。

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

大家有条件的话，还是去我的百度网盘链接去下载吧，也不大，很快就下载完的了。然后怎么操作就教下大家

链接：https://pan.baidu.com/s/1SdwP62kse1tA5Bp5MvtAFw
提取码：fxjo

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

现在默认大家执行完啦，大家都有数据啦，那进行接下来的学习吧。

进阶1. 基础查询

1.1 基础语法

  1. 先来看看它的语法吧：

    SELECT 查询列表（字段名1， 字段名2...） FROM 表名;

    这个查询出来，其实有点类似Java中的System.out.println()打印到控制台一样。

    还有一个语法就是，如果我想查询表中所有的字段名，难道我们要一个一个手敲出来吗，当然不是啦。我们可

    以用*来查询表中所有的字段名，也就是查询所有。

    SELECT * FROM 表名;

  2. 特点：

    • 查询列表可以是常量值、可以是表达式、可以是字段、可以是函数。
    • 我们查询完的结果 ，是一个虚拟的表格，不是真实存在。

  3. 接下来就进行实战操作吧：

    -- 1.查询表中的单个字段
SELECT `name` FROM beauty; 
-- 这里的name是关键字，但是也被我们用来做字段，虽然可以执行，但是我们可以通过加``这个符号，避免歧义。

-- 2.查询表中的多个字段
SELECT `name` , sex FROM beauty;

-- 3.查询表中的所有字段
SELECT * FROM beauty;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

  4. 这里需要注意的一个细节：

    因为有些字段它可能是MySQL中的关键字，我们可以为了避免歧义，可以加上``这个字符即：`name`。

  5. 除了查询字段，还可以查询常量值、表达式、函数。

    #4.查询常量值
SELECT 100;
SELECT 'john';
 
#5.查询表达式
SELECT 100%98;
 
#6.查询函数
SELECT VERSION();

    这里就不显示结果，自己尝试一下就可以得到结果啦。

1.2 起别名

为什么要起别名呢，因为有些字段它有可能重名情况，这里就可以起别名来区分啦。

  1. 语法：

    SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名;

    注意： 这里的关键字AS可以省略，但是其中的空格一定要有，不要就会报错。

  2. 特点：

    • 查询时给列、表指定别名需要使用AS关键字。
    • 使用别名的好处是方便观看和处理查询到的数据。
    • 如果要查询的字段有重名的情况，使用别名可以区分开来。

  3. 演示一下：

    -- 原来的查询
SELECT last_name, first_name FROM employees;
-- 使用 AS起别名
SELECT last_name AS 姓, first_name AS 名 FROM employees;
-- 省略AS,空格不能省略
SELECT last_name 姓, first_name 名 FROM employees;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

1.3 去重

如果我们查询的数据有一堆重复值，这个时候我们就可以消除重复值，关键字：DISTINCT。

  • 语法：

    SELECT DISTINCT 字段名 FROM 表名;

  • 演示下：

    -- 查询员工表中涉及到的所有的部门编号
SELECT department_id FROM employees;
SELECT DISTINCT department_id FROM employees;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

1.4 查询结果参加运算

我们查询得到的结果也可以来参加运算。

  • 语法：

    -- 某列数据和固定值运算
SELECT 字段名 + 值 FROM 表名;

-- 某列数据和其他列数据参与运算
SELECT 字段1 + 字段2 FROM 表名;

-- 直接运算
SELECT 值 + 值;

  • 运算符中+特点：

    1. 如果两个操作数都为数值型，则做加法运算。
    2. 只要其中一方为字符型，试图将字符型数值转换成数值型，其中如果转换成功，则继续做加法运算，如果转换失败，则将字符型数值转换成0。
    3. 只要其中一方为NULL，则结果肯定为NULL。

  • 演示下吧：

    -- 某列数据和固定值运算
SELECT lowest_sal + 20 FROM job_grades;

-- 某列数据和其他列数据参与运算
SELECT lowest_sal + highest_sal FROM job_grades;

-- 直接运算
-- 如果两个操作数都为数值型，则做加法运算。
SELECT 90 + 10;

-- 只要其中一方为字符型，试图将字符型数值转换成数值型，
-- 其中如果转换成功，则继续做加法运算，
SELECT '99' + 88 ;
-- 如果转换失败，则将字符型数值转换成0
SELECT 'hello' + 100;

-- 只要其中一方为null，则结果肯定为null
SELECT NULL + 100;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

进阶2：条件查询

前面我们的查询都是将所有数据都查询出来，但是有时候我们只想获取到满足条件的数据。

2.1 基础语法

在原来查询语法基础加上关键词WHERE。

  • 语法：

    SELECT 查询列表 FROM 表名 WHERE 筛选条件;

    那这个筛选条件怎么写呢？它可以有以下分类：

    1. 按条件表达式筛选

      简单条件运算符：> < = != <> >= <=。

    2. 按逻辑表达式筛选

      逻辑运算符：&& || ! and or not。

    3. 模糊查询：

    like between and in is null。

    怎么使用呢，接下来就详细讲解。

2.2 条件表达式筛选

比较运算符：

  • > < =：大于，小于，等于。
  • != <>：这两个都是不等于。
  • >= <=：大于等于， 小于等于。

演示一下：

-- 查询工资>12000的员工名和工资
SELECT last_name 员工名, salary 工资 FROM employees 
WHERE salary > 12000;

-- 查询部门编号不等于90号的员工名和部门编号
SELECT last_name 员工名, department_id 部门编号 FROM employees 
WHERE department_id <> 90;
SELECT last_name 员工名, department_id 部门编号 FROM employees 
WHERE department_id != 90;

-- 查询员工编号<=110 的员工名和员工编号
SELECT last_name 员工名, employee_id 员工编号 FROM employees 
WHERE employee_id <= 110;

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

2.3 逻辑表达式筛选
  • && and ：多个条件同时满足。
  • || or：多个条件其中一个满足。
  • ! not：不满足。
  • 一般我们都是用and or not比较多。

演示一下：

-- 查询工资z在10000到20000之间的员工名、工资
SELECT last_name 员工名, salary 工资 FROM employees
WHERE salary >= 10000 AND salary <= 20000;
SELECT last_name 员工名, salary 工资 FROM employees
WHERE salary >= 10000 && salary <= 20000;

-- 查询工资低于10000，或者工资高于20000的员工名、工资
SELECT last_name 员工名, salary 工资 FROM employees
WHERE salary <= 10000 OR salary >= 20000;
SELECT last_name 员工名, salary 工资 FROM employees
WHERE salary <= 10000 || salary >= 20000;

-- 查询员工编号不是在100和150之间的员工名、员工编号
SELECT last_name 员工名, employee_id 员工编号 FROM employees
WHERE NOT(employee_id >= 100 AND employee_id <= 150);
SELECT last_name 员工名, employee_id 员工编号 FROM employees
WHERE !(employee_id >= 100 && employee_id <= 150);

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

2.4 模糊查询
2.4.1 like关键字

  • LIKE：通常与两个通配符一起使用：

    • % ：任意多个字符,包含0 个字符
    • _ ：任意单个字符

  • 语法：

    SELECT 查询列表 FROM 表名 WHERE 字段名 LIKE '通配符字符串';

  • 具体演示下：

    -- 查询员工名中以字符a开头的员工名
SELECT last_name 员工名 FROM employees
WHERE last_name LIKE 'a%';

-- 查询员工名中以字符a结尾的员工名
SELECT last_name 员工名 FROM employees
WHERE last_name LIKE '%a';

-- 查询员工名中包含字符a的员工名
SELECT last_name 员工名 FROM employees
WHERE last_name LIKE '%a%';

-- 查询员工名中第三个字符为s，第五个字符为i的员工名和工资
SELECT last_name 员工名, salary 工资 FROM employees
WHERE last_name LIKE '__s_i%';

-- 查询员工名中第二个字符为_的员工名
/*
这里有个难点就是_这个不是通配符吗，那可以直接使用吗，当然是不行的
所以我们也就需要到转义符，如Java一样可以使用\来转义。
MySQL 有个特有的方式 即 定义一个字符 后面加上关键字ESCAPE 然后定义 '字符'。
*/
SELECT last_name 员工名, salary 工资 FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';

    注意：如果我们需要查找的字符包含了我们的通配符类似等，那我们可以使用转义字符：

  • 如Java一样可以使用\来转义。

    • MySQL有个特有的方式 即 定义一个字符 后面加上关键字ESCAPE 然后定义字符。如上面所示。

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

2.4.2 between and 关键词

  • between and：包含临界值并且，两个临界值不要调换顺序。

  • 直接演示下：

    -- 查询员工编号在100到120之间的员工信息
SELECT * FROM employees
WHERE employee_id BETWEEN 100 AND 120;

-- 错误，不会报错，但是没有结果。
SELECT * FROM employees
WHERE employee_id BETWEEN 120 AND 100;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

2.4.3 in 关键词

  • in： in列表的值类型必须一致或兼容，而且in列表中不支持通配符。

  • 直接演示下：

    -- 查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT last_name, job_id FROM employees
WHERE job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

2.4.3 is null 关键词

  • is nul：

    因为=或<>不能用于判断NULL值，而我们可以使用is nul或is not nul来判断NULL值。

  • 直接演示下：

    -- 查询没有奖金的员工名和奖金率
SELECT last_name 员工名, commission_pct 奖金率 FROM employees
WHERE commission_pct IS NULL;

-- 查询有奖金的员工名和奖金率
SELECT last_name 员工名, commission_pct 奖金率 FROM employees
WHERE commission_pct IS NOT NULL;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

进阶3：排序查询

我们发现我们查询出来的可能是无序，那我们可以把查出来的表按一定规则排序。排序关键词： ORDER BY

  • 语法：

    SELECT 查询列表 FROM 表名【where  筛选条件】ORDER BY 字段名 ASC|DESC;

  • 特点：

    1. ASC代表的是升序，可以省略，而DESC代表的是降序。
    2. ORDER BY子句可以支持 单个字段（单列）、别名、表达式、函数、多个字段（多列）。
    3. ORDER BY子句在查询语句的最后面，除了limit子句。这个后面再讲。

  • 实战演示下：

    单个字段中的排序即单列排序：

    -- 按单个字段排序(单列排序)
-- 查询员工名和工资，并按工资降序
SELECT last_name, salary 
FROM employees
ORDER BY salary DESC;
-- 添加筛选条件再排序
-- 查询部门编号>=90的员工名、员工编号，并按员工编号降序
SELECT last_name, employee_id
FROM employees
WHERE employee_id >= 90
ORDER BY employee_id;
-- 按表达式排序
-- 查询员工名、工资、年薪 按年薪降序
-- 年薪= salary*12*(1+IFNULL(commission_pct,0))
SELECT last_name, salary, salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
-- 按别名排序
-- 查询员工名、工资、年薪 按年薪升序 
SELECT last_name, salary, salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 ASC;
-- 按函数排序
-- 查询员工名、名字的长度，并且按名字的长度降序
-- 长度的函数是length() 函数后面会详细介绍
SELECT last_name, LENGTH(last_name) 长度
FROM employees
ORDER BY LENGTH(last_name) ASC;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

    上面演示的都是单个字段中排序，那我们要是排序后，排序列中出现了相同的字段，那其他列不按正常排序怎么办？那我们可以多字段，多列排序吗？不用想，当然。

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

    那具体怎么操作呢：

    -- 按多个字段排序(多列排序)
-- 查询员工名和工资，要求先按工资降序，再按员工名升序
SELECT last_name, salary
FROM employees
ORDER BY salary DESC, last_name ASC;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

进阶4：常见函数

包含了MySQL中函数，分为单行函数和分组函数（聚合函数、统计函数、组函数）

4.1 单行函数
4.1.1 分类

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

单行函数可以分为：

  • 字符函数
  • 数学函数
  • 日期函数
  • 其他函数
  • 高级函数（控制语句）

具体内容往下一起看吧：

4.1.2 字符函数

常见的字符函数：

函数 描述
LENGTH 获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
CHAR_LENGTH 返回字符串的字符数
CONCAT 拼接字符串
SUBSTR 截取字符串
INSTR 返回子串第一次出现的索引，如果找不到返回0
TRIM 去前后空格，或者可以去前后指定字符
UPPER、LOWER 将字母变变大写或者变小写
LPAD、RPAD 用指定的字符实现（左或右）填充指定长度
REPLACE 将字符串中的源字符串换成新的字符串

演示一下：

-- length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
SELECT LENGTH('hello'); -- 5
SELECT LENGTH('中国'); -- 6

-- char_length:返回字符串的字符数
SELECT CHAR_LENGTH('hello');-- 5
SELECT CHAR_LENGTH('中国');-- 2

-- concat：拼接字符串
SELECT CONCAT(last_name, '_', first_name) 姓名
FROM employees;

-- substr：截取
-- 注意：MySQL的索引是从1开始
-- 截取从指定索引处后面所有字符
SELECT SUBSTR('小龙女和杨过', 5); -- 杨过
-- 截取从指定索引处指定字符长度的字符
SELECT SUBSTR('小龙女和杨过', 1, 3); -- 小龙女

-- instr：返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('小龙女和杨过', '龙女'); -- 2
SELECT INSTR('龙女小龙女和杨过', '龙女'); -- 1
SELECT INSTR('龙女小龙女和杨过', '龙男'); -- 0 找不到返回0

-- trim：去前后空格，或者可以去前后指定字符
SELECT CHAR_LENGTH('   北极星   ');  -- 9
SELECT CHAR_LENGTH(TRIM('   北极星   ')); -- 3
-- 可以去前后指定字符
SELECT TRIM('a' FROM 'aaa北aa极星aaa');-- 北aa极星 中间的没有去掉

-- upper、lower：变大写，变小写
SELECT UPPER('hello');-- HELLO
SELECT LOWER('heLLo');-- hello

-- lpad、rpad ：用指定的字符实现左|右填充指定长度
SELECT LPAD('hello', 10, 'a'); -- aaaaahello
SELECT RPAD('hello', 10, 'ab'); -- helloababa 只到10个字符。
-- 测试没有超出本来字符的呢
SELECT LPAD('hello', 2, 'a');  -- he  截断
SELECT RPAD('hello', 3, 'ab'); -- hel 截断

-- replace: 将字符串中的源字符串换成新的字符串
SELECT REPLACE('杨过和小龙女和杨过', '杨过', '我'); -- 我和小龙女和我

需要注意的：

MySQL的索引是从1开始。

4.1.3 数学函数

常见的数学函数：

函数 描述
ROUND 四舍五入
RAND 返回 0 到 1 的随机数
CEIL 向上取整,返回>=该参数的最小整数
FLOOR 向下取整，返回<=该参数的最大整数
TRUNCATE 截断
MOD 取余

演示一下：

-- round：四舍五入
SELECT ROUND(1.65); -- 2
SELECT ROUND(1.45); -- 1

-- rand :返回 0 到 1 的随机数
SELECT RAND(); -- 0.6992990986437528  不是固定的

-- ceil：向上取整,返回>=该参数的最小整数
SELECT CEIL(1.2); -- 2
SELECT CEIL(1.6); -- 2

-- floor：向下取整，返回<=该参数的最大整数
SELECT FLOOR(1.2); -- 1
SELECT FLOOR(1.6); -- 1

-- truncate：截断
SELECT TRUNCATE(1.8999, 1); -- 1.8
SELECT TRUNCATE(RAND(), 1); -- 0.9 

-- mod：取余
SELECT MOD(10, 3); -- 1
4.1.4 日期函数

常见的日期函数：

函数 描述
NOW 返回当前系统日期+时间
CURDATE 返回当前系统日期，不包含时间
CURTIME 返回当前时间，不包含日期
DATEDIFF 计算两个日期相差的天数
YEAR、MONTH、MONTHNAME
DAY、HOUR、MINUTE、SECOND		 获取指定的部分，年、月、日、小时、分钟、秒
STR_TO_DATE 将字符通过指定的格式转换成日期
DATE_FORMAT 将日期转换成字符

演示一下：

-- now：返回当前系统日期+时间
SELECT NOW();

-- curdate：返回当前系统日期，不包含时间
SELECT CURDATE();

-- curtime：返回当前时间，不包含日期
SELECT CURTIME();

-- datediff : 计算两个日期相差的天数
SELECT DATEDIFF('2020-01-01', '1988-01-01');
SELECT DATEDIFF('1988-01-01', '2020-01-01');

-- 可以获取指定的部分，年、月、日、小时、分钟、秒
-- year：获取指定的年
SELECT YEAR(NOW());
SELECT YEAR('2020-02-02');
-- month：获取指定的月
SELECT MONTH(NOW());
-- monthname：获取指定的月英文单词
SELECT MONTHNAME(NOW());
-- day：获取指定的天
SELECT DAY(NOW());
-- hour：获取指定的小时
SELECT HOUR(NOW());
-- minute：获取指定的分钟
SELECT MINUTE(NOW());
-- second：获取指定的秒
SELECT SECOND(NOW());

-- str_to_date：将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('2020-02-02', '%Y-%m-%d');
SELECT STR_TO_DATE('02/02 2020', '%m/%d %Y');

-- date_format：将日期转换成字符
SELECT DATE_FORMAT('2020-02-02', '%Y年%m月%d日');
SELECT DATE_FORMAT(NOW(), '%y年%c月%d日');
SELECT DATE_FORMAT(NOW(), '%m-%d %Y');

注意：

在使用str_to_date和date_format这两个日期函数的时候，第二个参数定义的格式为：

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

4.1.5 其他函数

常见的其他函数：

函数 描述
version 返回当前MySQL的版本
database 返回当前使用的数据库
user 返回当前使用的用户

演示一下：

-- version：返回当前MySQL的版本
SELECT VERSION(); -- 5.5.40

-- database：返回当前使用的数据库
SELECT DATABASE(); -- data_test

-- user：返回当前使用的用户
SELECT USER(); -- root@localhost
4.1.6 高级函数（控制函数）

  1. if函数：可以相当于if else的效果。

    • 语法：

      IF(条件, '条件成立返回的值', '条件不成立返回的值')

    • 演示下：

      SELECT IF(66>88, '大', '小'); -- 小
-- 查询工资大于20000的员工名，显示哎呦不错呦，否则显示我觉得不行，请继续加油
SELECT last_name,
IF(salary > 20000, '哎呦不错呦', '我觉得不行，请继续加油')
FROM employees;

  2. case函数：case函数有两种用法：

    • 方式1：可以相当于switch case的效果。

      • 语法：

        select 字段名1, 字段名2, case 要判断的字段或表达式
when 值1 then 返回的值
when 值2 then 返回的值
...
else 要显示的值n或语句n;
end  别名

      • 用法：

        /*查询员工的工资，要求
部门号=30，显示的工资为2倍
部门号=40，显示的工资为3倍
部门号=50，显示的工资为4倍
其他部门，显示的工资为原工资
*/
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*2
WHEN 40 THEN salary*3
WHEN 50 THEN salary*4
ELSE salary
END 新工资
FROM employees;

    • 方式2：可以相当于多重if的效果。

      • 语法：

        select 字段名1, 字段名2, case 
when 判断条件1 then 要显示的值1或语句1
when 判断条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

      • 用法：

        /*查询员工的工资的情况
如果工资>20000,显示哎呦不错了
如果工资>15000,显示很可以了
如果工资>10000，显示太棒了
否则，显示请继续加油
*/
SELECT salary,
CASE 
WHEN salary > 20000 THEN '哎呦不错了'
WHEN salary > 15000 THEN '很可以了'
WHEN salary > 10000 THEN '太棒了'
ELSE '请继续加油'
END 工资描述
FROM employees;
4.2 分组函数（聚合函数）

分组函数实际上查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值。另外分组函数会忽略空值NULL。

4.2.1 五个分组函数

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

函数 描述
COUNT 统计指定列记录数，记录为NULL的不统计
SUM 计算指定列的数值和，如果不是数值类型，那么计算结果为0
AVG 计算指定列的平均值
MAX、MIN 计算指定列的最大值
4.2.2 特点

  1. 以上五个分组函数都可以忽略NULL值。

  2. sum和avg一般用于处理数值型，max、min、count可以处理任何数据类型。

  3. 都可以搭配distinct使用，用于统计去重后的结果。

  4. count的参数可以支持：字段、*、常量值：一般放1。

4.2.3 用法
-- 1、简单使用
SELECT SUM(salary) 总和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees; -- 691400.00， 6461.682243， 24000.00， 2100.00， 107

-- 2、参数支持哪些类型
SELECT SUM(last_name), AVG(last_name) FROM employees; -- 0 , 0
SELECT SUM(hiredate), AVG(hiredate) FROM employees; -- 虽然都不会报错，但是没有什么意义

SELECT MAX(last_name),MIN(last_name) FROM employees;-- Zlotkey, Abel
SELECT MAX(hiredate),MIN(hiredate) FROM employees;-- 2016-03-03 00:00:00, 1992-04-03 00:00:00

SELECT COUNT(commission_pct) FROM employees; -- 35 
SELECT COUNT(last_name) FROM employees;  -- 107 

-- 3、是否忽略null
SELECT commission_pct FROM employees; -- 此行有包含null值
SELECT COUNT(commission_pct) FROM employees; -- 35  忽略了null值
SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees; -- 0.40， 0.10 忽略了null值
SELECT SUM(commission_pct) ,AVG(commission_pct), SUM(commission_pct)/35, SUM(commission_pct)/107 --  忽略了null值
FROM employees; -- 7.80，  0.222857  ， 0.222857 ， 0.072897

-- 4、和distinct搭配
SELECT salary FROM employees;  -- 此行有包含重复值
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees; -- 397900.00， 691400.00
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees; -- 57 ， 107

-- 5、count函数的详细介绍
SELECT COUNT(salary) FROM employees; -- 107
SELECT COUNT(*) FROM employees;-- 107
SELECT COUNT(1) FROM employees;-- 107
SELECT COUNT(commission_pct) FROM employees; -- 35 不记录为null

-- 6、和分组函数一同查询的字段有限制
SELECT employee_id FROM employees; -- 多行
SELECT AVG(salary),employee_id  FROM employees; -- 6461.682243, 100 只显示一行。

注意：

  1. count(*)一般用于统计个数，相对于参数写字段的话，因为字段可能有时候里面会有NULL，所以不会进行统计，无法正确的统计表中全部的数据。
  2. 和分组函数一同查询的字段有限制，因为分组函数查询出来是一行，而其他字段查询的出来是多行，但是MySQL是不会报错，它是直接显示一行数据，所以没什么意义了。如果要加其他字段的话，可以使用group by后的字段。group by这个下面就来讲解。

进阶5 ：分组查询

是对查询信息进行分组显示，其原理是将分组字段结果中相同内容作为一组。主要搭配分组函数来使用。分组的目的就是为了更好的统计。

概念引进：比如在员工表中，有个字段是部门编号，部门编号重复代表了这些员工都在这一部门下，那我们是不是可以统计每个部门有多少人呢？再比如说，一个班级中，有男有女，那我们是不是可以统计下男生有多少，女士有多少呢？

那具体如何使用呢？这里分组用到的关键字是：group by。

5.1 语法

  • 普通语法格式：

    SELECT 查询列表 FROM 表名 GROUP BY 字段名;

    这里的查询列表为分组函数，字段名即group by后的字段名。

  • 复制语法格式：

    加上我们之前学习的查询关键字，我们可以这样写：、

    SELECT 查询列表 FROM 表名 【where 筛选条件】 GROUP BY 字段名 【order by 排序的字段】;
5.2 实际操作下
-- 简单使用 查询每个部门有多少人
SELECT COUNT(*), department_id 
FROM employees 
GROUP BY department_id;

-- 添加筛选条件使用 查询部门编号不为Null且部门编号在20~60之间的部门的有多少人
SELECT COUNT(*), department_id FROM employees
WHERE department_id IS NOT NULL 
AND department_id BETWEEN 20 AND 60
GROUP BY department_id;

-- 分组再添加筛选条件 这里就需要一个关键字 having
-- 查询哪个部门的员工个数>5
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
WHERE COUNT(*) > 5;  -- 分组后筛选就不能调用where了 不然会报错

SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

-- 也可以使用别名 再来一个案例来试试吧：
-- 每个工种员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary) 最高工资, job_id
FROM employees
GROUP BY job_id
HAVING 最高工资 > 12000;

-- 分组后再排序
-- 每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT MAX(salary) 最高工资, job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING 最高工资 > 6000
ORDER BY 最高工资 ASC;

-- 也可以多个字段分组
-- 查询每个工种每个部门的最低工资,并且最低工资大于7000，并按最低工资降序
SELECT MIN(salary), job_id, department_id
FROM employees
GROUP BY job_id, department_id
HAVING MIN(salary) > 7000
ORDER BY MIN(salary) DESC;

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

注意：

如果我们分组后还想要再去筛选条件过滤，如果在写where关键字就会报错，而分组后筛选有一个关键字having。

所以两者的区别：

  1. where关键字是作用在原始表上进行筛选，并且位置是在group by前，即分组前。
  2. having关键字是作用在group by后的结果集上进行再次筛选，并且位置是在group by后，即分组后。
  3. having后面可以使用分组函数，where后面不可以使用分组函数
5.3 特点

  1. 可以按单个字段分组 ，也可以多个字段分组，字段用逗号隔开 。

  2. 和分组函数一同查询的字段最好是分组后的字段 。

  3. 分组筛选的关键字是having。最好就是能用分组前筛选的，尽量使用分组前筛选，提高效率。

  4. having后可以支持别名。

  5. 可以支持排序的使用。

进阶6：连接查询

也可以称为多表查询，当查询的字段来自于多个表时，就会用到我们的连接查询。

概念引进下：比如我们的女神表中，有个对应的男生id，这个id对应着男神的表中的id。这个时候就需要多表来进行查询。再比如我们员工表中想通过部门编号查找对应的部门信息，这个时候也就需要多表来进行查询。

6.1 分类

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

  1. 按年代分类

    • sql92： 仅仅支持内连接（隐式内连接）。
    • sql99（更加推荐）：支持内连接（显式内连接）+外连接（左外和右外）+交叉连接。

  2. 按功能分类

    • 内连接：等值连接、非等值连接、自连接
    • 外连接：左外连接、右外连接、全外连接
    • 交叉连接
6.2 笛卡尔积现象
6.2.1 什么是笛卡尔积现象

一般出现在多表的查询中。

我们先来看看什么时候会出现这个现象的吧。

我们来查找女神对应的男神信息。这个时候就需要使用多表查询了吧。那我们具体来看看：

SELECT * FROM beauty;
SELECT * FROM boys;
SELECT * FROM beauty, boys;

以上数据其实是左表的每条数据和右表的每条数据组合。左表有12条，右表有4条，最终组合后12*4=48条数据。

左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积。

为什么会发生这种情况呢？

  • 因为我们查询的时候没有添加有效的连接条件。有些数据不是我们想得到的吧，一个女神应该对应一个男神，没有男神信息的就不应该显示出来吧。所以需要我们手动过滤掉一些没用的我们不想要的数据。
6.2.2 消除笛卡尔积现象

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

而我们需要添加的筛选条件可以称之为表连接条件。

SELECT * FROM beauty, boys
WHERE beauty.boyfriend_id = boys.`id`;

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

6.3 sql92

支持所有内连接：等值连接，非等值连接，自连接。此支持的内连接可以称为隐式内连接。

6.3.1 内连接

多表查询时获取符合条件的数据。

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

  • 分类：
    1. 等值连接
    2. 非等值连接
    3. 自连接
6.3.2 等值连接

  • 特点：

    1. 多表等值连接的结果为多表的交集部分。
    2. n表连接，至少需要n-1个连接条件。
    3. 多表的顺序没有要求，不分主次。
    4. 一般需要为表起别名，提高阅读性和性能。但是起别名后，是不能用原表的名字做操作。
    5. 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选。

  • 案例演示：

    -- 查询员工名和对应的部门名
SELECT last_name, department_name
FROM employees, departments
WHERE employees.`department_id` = departments.`department_id`;

-- 可以起别名 查询员工名、工种号、工种名
SELECT e.`last_name`, e.`job_id`, j.`job_title`
FROM employees e, jobs j
WHERE e.`job_id` = j.`job_id`;

-- 可以调换表的顺序 查询员工名、工种号、工种名
SELECT e.`last_name`, e.`job_id`, j.`job_title`
FROM  jobs j, employees e
WHERE  j.`job_id` = e.`job_id`;

-- 可以加筛选,分组，排序 
-- 查询每个工种的工种名和员工的个数，员工中有奖金，员工个数大于2，并且按员工个数降序
SELECT j.`job_title`, COUNT(*) 员工个数
FROM employees e, jobs j
WHERE e.`job_id` = j.`job_id`
AND e.`commission_pct` IS NOT NULL 
GROUP BY j.`job_title`
HAVING 员工个数 > 2
ORDER BY 员工个数 DESC;

-- 可以2表以上连接  查询员工名、部门名和所在的城市，并且城市是以o开头的。
SELECT e.`last_name`, d.`department_name`, l.`city`
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND l.`city` LIKE 'o%';

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

  • 注意：

    因为有些字段在不同表中可能回重复，MySQL不知道引用谁的，就会报错。所以要起别名。

    在起别名后，就只能使用别名来操作表，原来的表名称就用不了了，用了就会报错。

6.3.3 非等值连接

概念引进，我想查询的字段在其他字段是包含状态而不是相等状态。比如，员工表中的工资在工资级别表中是这样查询的：比如小鱼工资10000，工资等级表1000~15000是等级D。那我要查询相对应的等级，这个时候就可以使用非等值连接了。

  • 案例演示：

    -- 查询员工的工资和工资级别
SELECT e.`salary`, g.`grade_level`
FROM employees e,job_grades g
WHERE e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
ORDER BY g.`grade_level` DESC;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

6.3.4 自连接

自连接也相当于等值连接，只是等值连接是多表，而自连接是自己连接自己。

  • 案例演示

    -- 查询员工名和上级的名称
SELECT e.`last_name`, m.`last_name`
FROM employees e, employees m
WHERE e.`manager_id` = m.`employee_id`;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

6.4 sql99

相对于sql92，只是在原本基础上多了【连接类型】JOIN … ON 关键字，使用ON来加连接条件，而不再是where后加了。实现了连接条件和筛选条件分离，增加了可读性。而且还可以用外连接、交叉连接。

6.4.1 语法格式

到现在为止，可用的完整的连接查询语法：

select 查询列表
from 表1 别名 
【连接类型】 join 表2 别名 
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

注意：

  • 这里的连接类型取决你是哪种类型：可以分为：
    1. 内连接：inner关键字，inner可以省略。
    2. 外连接：左外left 【outer】关键字， 右外right 【outer】关键字，全外 full【outer】关键字outer可以省略。
    3. 交叉连接：cross关键字。
6.4.2 内连接

使用方法与sql92语法的内连接类似，只是筛选条件放在where后面，连接条件放在on后面。inner关键字可以省略。

  • 一样可以分为等值连接、非等值连接、自连接。

  • 实际演示下：把之前sql92语法使用的案例改为sql99的语法格式。

    -- 等值连接
-- 查询员工名和对应的部门名
SELECT last_name, department_name
FROM employees
INNER JOIN departments 
ON  employees.`department_id` = departments.`department_id`;

-- 可以起别名、inner可以省略
-- 查询员工名、工种号、工种名
SELECT e.`last_name`, e.`job_id`, j.`job_title`
FROM employees e
JOIN jobs j
ON e.`job_id` = j.`job_id`;

-- 可以调换表的顺序 查询员工名、工种号、工种名
SELECT e.`last_name`, e.`job_id`, j.`job_title`
FROM  jobs j
JOIN employees e
ON j.`job_id` = e.`job_id`;

-- 可以加筛选,分组，排序 
-- 查询每个工种的工种名和员工的个数，员工中有奖金，员工个数大于2，并且按员工个数降序
SELECT j.`job_title`, COUNT(*) 员工个数
FROM employees e
JOIN jobs j
ON e.`job_id` = j.`job_id`
WHERE e.`commission_pct` IS NOT NULL 
GROUP BY j.`job_title`
HAVING 员工个数 > 2
ORDER BY 员工个数 DESC;

-- 可以2表以上连接  查询员工名、部门名和所在的城市，并且城市是以o开头的。
-- 这种类似三表连接，还是有一定顺序的，如果两者没有连接条件，就不能是连接条件了
SELECT e.`last_name`, d.`department_name`, l.`city`
FROM employees e
JOIN departments d ON e.`department_id` = d.`department_id`
JOIN locations l ON d.`location_id` = l.`location_id`
WHERE l.`city` LIKE 'o%';

-- 非等值连接
-- 查询员工的工资和工资级别
SELECT e.`salary`, g.`grade_level`
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
ORDER BY g.`grade_level` DESC;

-- 自连接
-- 查询员工名和上级的名称
SELECT e.`last_name`, m.`last_name`
FROM employees e
JOIN employees m
ON e.`manager_id` = m.`employee_id`;

  • 可以看出，sql99语法可以更好的看出哪种是连接条件，哪种筛选条件。

6.4.3 外连接

主要用于查询一个表中有，另一个表没有的记录。

概念引进：我们在上面查询女神对应男神信息，这种结果就称之为内连接，而女神中在男神表中找不到对应的男神关系，而我们又可以利用外连接，来查询这种类似查找不在男神表的女神名。

  • 分类：

    1. 左外连接：将满足要求的数据显示，左表不满足要求的数据也显示，保证左表的数据全部显示。主表是左表，从表是右表，使用left 【outer】关键字。

      超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

      简单使用下：

      -- 左连接
SELECT b.`name`, bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
ORDER BY bo.`id`;

      超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

      可以看到，得到了内连接的结果+左表中不满足数据的结果。我们就可以以此来得到不在男神表的女神信息了（就是那些有NULL值的女神）。

    2. 右外连接：将满足要求的数据显示，右表不满足要求的数据也显示，保证左表的数据全部显示。主表是右表，从表是左表，使用right【outer】关键字。

      超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

      简单使用下：

      -- 右连接
SELECT b.`name`, bo.*
FROM boys bo
RIGHT JOIN beauty b
ON b.`boyfriend_id` = bo.`id`
ORDER BY bo.`id`;

      此方式可以得到与左连接相同的结果。所以两者其实可以互换即可。

    3. 全外连接：将满足要求的数据显示，将左表不满足要求和右表不满足要求的数据也全部显示，即全部显示。使用full【outer】关键字。这种方式在MySQL 暂时无法体现。大家知道有这么个概念就行啦。

      超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

  • 特点：

    1. 外连接的查询结果为主表中的所有记录：

      如果从表中有和它匹配的，则显示匹配的值，而如果从表中没有和它匹配的，则显示NULL。
      可以相当于外连接查询结果=内连接结果+主表中有而从表没有的记录。

    2. 左外连接，left join左边的是主表。
      右外连接，right join右边的是主表。

    3. 左外和右外交换两个表的顺序，可以实现同样的效果 。

    4. 全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的。

  • 案例演示下：

    -- 不在男神表的的女神名
SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;
 
-- 查询哪个部门没有员工
SELECT d.*,e.employee_id
FROM employees e
RIGHT JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

6.4.4 交叉连接

这个相当于sql92语法中出现的笛卡尔积现象。

直接演示下吧：

SELECT b.* , bo.*
FROM beauty b
CROSS JOIN boys bo;

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

进阶7：子查询

出现在其他语句中的select语句，称为子查询或内查询，而外部的查询语句，称为主查询或外查询。

7.1 分类

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

7.2 简单使用

具体怎么使用呢？子查询的结果是作为主查询的一部分，那就代表着子查询是比主查询先得到结果。那我们先了解下如何使用吧。

-- 子查询怎么使用？
-- 谁的工资比 Abel 高?
-- 先查询出Abel的工资吧
SELECT salary
FROM employees
WHERE last_name = 'Abel';

-- 查询员工信息，筛选工资比Abel的工资高的结果。
SELECT * FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);

可以看到子查询是放在（）里，并且作为了主查询的一部分。

7.3 按结果集的行列数分

按我们查询得到的行列数具体来分：

  • 标量子查询(一行一列)

    SELECT salary FROM employees WHERE last_name = 'Abel';

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

    一般搭配着单行操作符使用有：< > = >= <= <> !=

  • 列子查询(一列多行)

    SELECT DISTINCT department_id FROM departments
WHERE location_id IN(1400,1700);

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

    一般搭配着多行操作符使用：in any all。

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

  • 行子查询(一行多列)

    SELECT MIN(employee_id),MAX(salary) FROM employees;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

  • 表子查询(多行多列)

    SELECT * FROM employees;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

7.4 按位置分
7.4.1 在where或having后面

在where或having后面，它支持标量子查询、列子查询、行子查询。

  • 标量子查询

    -- 标量子查询
-- 可以实现多个子查询 
-- 返回job_id与141号员工相同，salary比143号员工工资多的员工姓名，job_id和工资
-- 第一次就比较详细的分析，写出来，后面就直接写出来
-- 1.查询141号员工的job_id
SELECT job_id FROM employees
WHERE employee_id = 141;

-- 2.查询143号员工的salary
SELECT salary FROM employees
WHERE employee_id = 143;

-- 3.查询员工的姓名，job_id 和工资，要求job_id=1并且salary>2
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
	SELECT job_id FROM employees
	WHERE employee_id = 141
)
AND salary > (
	SELECT salary FROM employees
	WHERE employee_id = 143
);

-- 返回公司工资最少的员工的last_name,job_id和salary
-- 1.查询公司的最低工资 2. 查询last_name,job_id和salary，要求salary=1的结果
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
);

-- 在having后
-- 查询最低工资大于50号部门最低工资的部门id和其最低工资
-- 1.查询50号部门的最低工资  2.查询每个部门的最低工资  3. 在2的基础上筛选，满足min(salary)>1的结果
SELECT MIN(salary), department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT  MIN(salary)
	FROM employees
	WHERE department_id = 50

);

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

  • 列子查询

    -- 列子查询
-- 返回location_id是1400或1700的部门中的所有员工姓名 (in)
-- 1.查询location_id是1400或1700的部门编号 2. 查询员工姓名，要求部门号是1列表中的某一个
SELECT last_name FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id 
	FROM departments 
	WHERE location_id IN(1400, 1700)
);

-- 返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary (any)
-- 1.查询job_id为‘IT_PROG’部门任一工资 2. 查询员工号、姓名、job_id 以及salary，salary<(1)的任意一个
SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary < ANY(
	SELECT DISTINCT salary FROM employees 
	WHERE job_id = 'IT_PROG'
);

-- 返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary (all)
-- 思路与上面类似
SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary < ALL(
	SELECT DISTINCT salary FROM employees 
	WHERE job_id = 'IT_PROG'
);

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

  • 行子查询

    用处相对来说不是特别的多。

    -- 查询员工编号最小并且工资最高的员工信息
-- 1. 最小的员工编号 2. 工资最高 3. 在2,1 的基础上筛选
-- 以前的做法
SELECT * FROM employees
WHERE employee_id = (SELECT MIN(employee_id) FROM employees)
AND salary = (SELECT MAX(salary) FROM employees);
-- 行子查询的做法
SELECT * FROM employees
WHERE (employee_id, salary)=(
	SELECT MIN(employee_id), MAX(salary)
	FROM employees
);

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

7.4.2 在select后面

仅仅支持标量子查询

-- select 后面
-- 查询每个部门的员工个数
SELECT d.*,(

	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;

-- 查询员工号=102的部门名
SELECT (
	SELECT department_name,e.department_id
	FROM departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102
	
) 部门名;

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

7.4.3 在from后面

相当于把子查询充当一张表，但是这个必须得起别名。

-- from 后面
-- 查询每个部门的平均工资的工资等级
-- 1.查询每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;
-- 2.连接1的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT avg_sa.* , g.`grade_level`
FROM (
	SELECT AVG(salary) sa, department_id
	FROM employees
	GROUP BY department_id
) avg_sa
INNER JOIN job_grades g
ON avg_sa.sa BETWEEN g.`lowest_sal` AND g.`highest_sal`;

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

7.4.4 exists后面（相关子查询）

  • 语法：

    exists(完整的查询语句)。

    返回的结果一般是1或者0。

    -- 查询有员工的部门名
SELECT department_id
FROM departments d
WHERE EXISTS(
	SELECT * FROM employees e
	WHERE e.`department_id` = d.`department_id`
);

-- 查询没有男神的女神信息
SELECT b.* 
FROM beauty b
WHERE EXISTS(
	SELECT bo.`id`
	FROM boys bo
	WHERE bo.id = b.`boyfriend_id`
);

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

进阶8：分页查询

当我们要显示的数据，一页显示不全的时候，则就需要分页提交SQL请求。常见的应用场景有，我们刷淘宝、京东等电商网站的时候，看一件商品可能有几十万条数据，它不可能一页显示完全吧，这就需要分页来实现了。分页的关键字是LIMIT。·

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

8.1 语法
select 查询列表 from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;

解释下：

其中的【】中是可写可不写的。offset要显示条目的起始索引（起始索引从0开始），size要显示的条目个数。

8.2 用法
-- 查询前五条员工信息
SELECT * FROM  employees LIMIT 0,5;
-- 如果offset是0的话，可以省略
SELECT * FROM  employees LIMIT 5;


-- 查询第11条——第15条员工信息
SELECT * FROM employees LIMIT 10, 5;


-- 有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

8.3 特点

  1. limit语句放在查询语句的最后面中。

  2. offset如果是0的话，可以省略不写。

  3. 公式：要显示的页数page ，每页的条目数size，可以写成

    limit (page-1)*size, size。

进阶9：联合查询

将多条查询语句的结果合并成一个结果。联合的关键字：union。主要的应用场景是要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

9.1 语法
查询语句1
union
查询语句2
union
...
9.2 用法
-- 查询部门编号>90或邮箱包含a的员工信息
-- 以前用法
SELECT * FROM employees
WHERE employee_id > 90 OR email LIKE '%a%';
-- 使用联合查询
SELECT * FROM employees WHERE employee_id > 90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

9.3 特点
  • 多条查询语句的查询的列数必须是一致的。
  • 多条查询语句的查询的列的类型几乎相同。
  • union代表去重，union all代表不去重。

总结

学到这里的查询，可以看下完整的语法：

SELECT 查询列表 FROM 表名
【连接类型】 join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段
limit 【offset,】size;

当然你可以认为这是一个查询语句中具备的执行顺序。

2. 约束

目前有些字段的类型对数据进行限制，但是这个限制不够全面。而我们需要进一步限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性。

2.1 分类

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

  • PRIMARY KEY: 主键约束

    什么时候可以用到呢？通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

  • UNIQUE: 唯一约束

    主要作用就是让字段的值唯一，不能重复。

  • NOT NULL: 非空约束

    主要作用就是让这个字段的值不能为空。

  • DEFAULT: 默认值约束

    主要作用是如果这个字段不设置值，就使用默认值。

  • FOREIGN KEY: 外键约束

    什么时候需要用到呢？当需要一个表中的某个字段引用其他表的主键的时候，这个时候就可以添加外键约束。这个字段也就叫做外键。

    主表： 主键所在的表，约束别人的表，将数据给别人用。
    副表/从表： 外键所在的表，被约束的表，使用别人的数据。

  • CHECK：检查约束。不支持，了解即可。

也可分为：列级约束和表级约束。

  • 列级约束：六大约束语法上都支持，但外键约束没有效果。
  • 表级约束：除了非空、默认，其他的都支持。

语法格式可以这样写：

CREATE TABLE 表名(
	字段名 字段类型 列级约束,
	字段名 字段类型,
	表级约束
);

2.2 使用约束的时机

  1. 创建表时。
  2. 修改表时。

2.3 创建表时用法

这里有用到外键表，就先创建一个表：

-- 先创建一个major 表
CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);

2.3.1 列级约束

  • 语法：直接在字段名和类型后面追加 约束类型即可。

    CREATE TABLE 表名(
	字段名 字段类型 列级约束,
	字段名 字段类型 列级约束,
);

  • 实例：

    -- 列级约束
CREATE TABLE stuinfo(
	id INT PRIMARY KEY, -- 主键
	stuName VARCHAR(20) NOT NULL, -- 非空
	gender CHAR(1) CHECK(gender='男' OR gender ='女'), -- 检查
	seat INT UNIQUE, -- 唯一
	age INT DEFAULT  18, -- 默认约束
	majorId INT REFERENCES major(id) -- 外键
);

-- 可以通过以下命令查看表结构和索引信息
DESC stuinfo;
SHOW INDEX FROM stuinfo;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

  • 特点：

    可以看出检查约束，不会报错，而且外键约束也不会报错，但是没有效果。

2.3.2 表级约束

  • 语法：在各个字段的最下面

    CREATE TABLE 表名(
	字段名 字段类型,
	字段名 字段类型,
    【constraint 约束名】 约束类型(字段名) 
);

  • 实例：

    -- 表级约束
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	
	CONSTRAINT pk PRIMARY KEY(id),-- 主键
	CONSTRAINT uq UNIQUE(seat),-- 唯一键
	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'), -- 检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) -- 外键
);
DESC stuinfo;
SHOW INDEX FROM stuinfo;

-- 【CONSTRAINT 名称】 这个可以省略
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	
	PRIMARY KEY(id),-- 主键
	UNIQUE(seat),-- 唯一键
	CHECK(gender ='男' OR gender  = '女'), -- 检查
	FOREIGN KEY(majorid) REFERENCES major(id) -- 外键
);
SHOW INDEX FROM stuinfo;

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

2.3.3 小结

我们通用的写法可以这么写：

-- 类似这样既可
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

2.4 修改表时用法

2.4.1 语法

-- 修改表时用法
-- 添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;

-- 添加表级约束
ALTER TABLE 表名 ADD 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

2.4.2 添加约束用法

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
);
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.添加唯一

#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);

#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

2.4.3 删除约束

-- 修改表时删除约束
-- 删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

-- 删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

-- 删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

-- 删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

-- 删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

2.5 主键和唯一的区别

  1. 主键和唯一都保证了字段的唯一性。
  2. 主键是不允许字段为空的，而唯一可以为空。
  3. 一个表中最多只有一个主键，而可以有多个唯一键。
  4. 主键和唯一都可以允许组合。

2.6 外键的特点

  1. 要求在从表设置外键关系。
  2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求。
  3. 主表的关联列必须是一个key（一般是主键或唯一）。
  4. 插入数据时，先插入主表，再插入从表，而删除数据时，先删除从表，再删除主表。

2.7 外键级联

在有了外键约束后不能直接修改和删除数据的，那我们要如何在修改和删除主表的主键时，同时更新或删除从表的外键值呢。

我们将在修改和删除主表的主键时，同时更新或删除从表的外键值的这种操作称之为级联操作。

  • 语法：

    ON UPDATE CASCADE ：级联更新 主表主键修改后，从表的数据也跟着修改。

ON DELETE CASCADE ：级联删除 主表主键删除后，从表数据也跟着删除。

  • 具体用法

    CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) ON UPDATE CASCADE ON DELETE CASCADE
);

3. 标识列

又称为自增长列，可以不用手动的插入值，系统提供默认的序列值

3.1 语法

字段名 字段类型 PRIMARY KEY AUTO_INCREMENT

关键字：AUTO_INCREMENT 表示自动增长(字段类型必须是数值类型)

3.2 用法

-- 创建表时设置标识列
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
-- 插入5条数据
INSERT INTO tab_identity (id,NAME) VALUES(NULL,'hello');
SELECT * FROM tab_identity;

-- 修改表时添加标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT,
	NAME VARCHAR(20)
);
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

-- 删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;

3.3 特点

  1. 标识列必须和一个key搭配，不一定是主键。

    CREATE TABLE tab_identity(
	id INT UNIQUE AUTO_INCREMENT,
	NAME VARCHAR(20)
);

  2. 一个表可以最多一个的标识列。

    CREATE TABLE tab_identity(
	id INT UNIQUE AUTO_INCREMENT,
	NAME INT UNIQUE AUTO_INCREMENT -- 就会报错
);

  3. 标识列的类型只能是数值型。

    CREATE TABLE tab_identity(
	id INT,
	NAME VARCHAR(20) UNIQUE AUTO_INCREMENT -- 类型不对也会报错
);

  4. 标识列可以通过 SET auto_increment_increment=数字;设置步长，也可以通过手动插入值，设置起始值。

    -- 设置步长5
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
-- 查看步长
SHOW VARIABLES LIKE '%auto_increment%';
SET auto_increment_increment=5;
-- 设置起始索引即开始位置。
INSERT INTO tab_identity (id,NAME) VALUES(100,'hello');

    超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

4. 完结

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

相信各位看官看到查询篇，是不是觉得一个小小查询会有这么多知识点，而我们在实际应用，查询的一些语句的复杂程度还是相当大的，所以打好基础吧，不仅为了学习提升自我，也为了自己能面对面试考察sql语句时能做到不慌张！并且面试中比较常问的面试题中都有MySQL的身影，所以MySQL的学习也是必不可少滴！

我们还是小小的总结下吧：

DQL：主要是用于对表的查询，
SELECT 查询列表 FROM 表名
【连接类型】 join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段
limit 【offset,】size;

约束：
列级约束：
CREATE TABLE 表名(
	字段名 字段类型 列级约束,
	字段名 字段类型 列级约束,
);

表级约束：
CREATE TABLE 表名(
	字段名 字段类型,
	字段名 字段类型,
    【constraint 约束名】 约束类型(字段名) 
);
-- 添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;

-- 添加表级约束
ALTER TABLE 表名 ADD 【constraint 约束名】 约束类型(字段名) 【外键的引用】;


设置标识列：
字段名 字段类型 PRIMARY KEY AUTO_INCREMENT

当然啦，更多语句命令的一些细节，希望大家可以仔细学习！

学到这里，今天的世界打烊了，晚安！虽然这篇文章完结了，但是我还在，永不完结。我会努力保持写文章。来日方长，何惧车遥马慢！

感谢各位看到这里！愿你韶华不负，青春无悔！

注: 如果文章有任何错误和建议，请各位大佬尽情留言！如果这篇文章对你也有所帮助，希望可爱亲切的您给个三连关注下，非常感谢啦！

超硬核学习手册系列2查询篇——深入浅出MySQL的知识点，学习收藏必备

上一篇：2021,10,29 模拟赛题解报告


下一篇：temporal credit assignment in reinforcement learning 【强化学习 经典论文】