茫茫人海千千万万,感谢这一秒你看到这里。希望我的文章对你的有所帮助!

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

@TOC

1. DQL语言的学习

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

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

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

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

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

#导入MD文档图片#超硬核学习手册系列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;

    #导入MD文档图片#超硬核学习手册系列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;

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

1.3 去重

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

  • 语法:

    SELECT DISTINCT 字段名 FROM 表名;
  • 演示下:

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

    #导入MD文档图片#超硬核学习手册系列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 ;-- 如果转换失败,则将字符型数值转换成0SELECT 'hello' + 100;-- 只要其中一方为null,则结果肯定为nullSELECT NULL + 100;

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

进阶2:条件查询

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

2.1 基础语法

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

  • 语法:

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

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

    1. 按条件表达式筛选

      简单条件运算符:> < = != <> >= <=

    2. 按逻辑表达式筛选

      逻辑运算符:&& || ! and or not

    3. 模糊查询:

    like <br/>between and in is null

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

2.2 条件表达式筛选

比较运算符:

  • &gt; &lt; =:大于,小于,等于。
  • != &lt;&gt;:这两个都是不等于。
  • &gt;= &lt;=:大于等于, 小于等于。

演示一下:

-- 查询工资>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;

#导入MD文档图片#超硬核学习手册系列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);

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

2.4 模糊查询
2.4.1 like关键字
  • LIKE:通常与两个通配符一起使用:

    • % :任意多个字符,包含0 个字符
    • _ :任意单个字符
  • 语法:

    SELECT 查询列表 FROM 表名 WHERE 字段名 LIKE '通配符字符串';
  • 具体演示下:

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

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

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

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

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

2.4.2 between and 关键词
  • between and:包含临界值并且,两个临界值不要调换顺序。

  • 直接演示下:

    -- 查询员工编号在100到120之间的员工信息SELECT * FROM employeesWHERE employee_id BETWEEN 100 AND 120;-- 错误,不会报错,但是没有结果。SELECT * FROM employeesWHERE employee_id BETWEEN 120 AND 100;

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

2.4.3 in 关键词
  • inin列表的值类型必须一致或兼容,而且in列表中不支持通配符。

  • 直接演示下:

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

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

2.4.3 is null 关键词
  • is nul

    因为=&lt;&gt;不能用于判断NULL值,而我们可以使用is nulis not nul来判断NULL值。

  • 直接演示下:

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

    #导入MD文档图片#超硬核学习手册系列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 employeesORDER BY salary DESC;-- 添加筛选条件再排序-- 查询部门编号>=90的员工名、员工编号,并按员工编号降序SELECT last_name, employee_idFROM employeesWHERE employee_id >= 90ORDER BY employee_id;-- 按表达式排序-- 查询员工名、工资、年薪 按年薪降序-- 年薪= salary*12*(1+IFNULL(commission_pct,0))SELECT last_name, salary, salary*12*(1+IFNULL(commission_pct,0)) 年薪FROM employeesORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;-- 按别名排序-- 查询员工名、工资、年薪 按年薪升序 SELECT last_name, salary, salary*12*(1+IFNULL(commission_pct,0)) 年薪FROM employeesORDER BY 年薪 ASC;-- 按函数排序-- 查询员工名、名字的长度,并且按名字的长度降序-- 长度的函数是length() 函数后面会详细介绍SELECT last_name, LENGTH(last_name) 长度FROM employeesORDER BY LENGTH(last_name) ASC;

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

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

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

    那具体怎么操作呢:

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

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

进阶4:常见函数

包含了MySQL中函数,分为单行函数和分组函数(聚合函数、统计函数、组函数)

4.1 单行函数
4.1.1 分类

#导入MD文档图片#超硬核学习手册系列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'); -- 5SELECT LENGTH('中国'); -- 6-- char_length:返回字符串的字符数SELECT CHAR_LENGTH('hello');-- 5SELECT CHAR_LENGTH('中国');-- 2-- concat:拼接字符串SELECT CONCAT(last_name, '_', first_name) 姓名FROM employees;-- substr:截取-- 注意:MySQL的索引是从1开始-- 截取从指定索引处后面所有字符SELECT SUBSTR('小龙女和杨过', 5); -- 杨过-- 截取从指定索引处指定字符长度的字符SELECT SUBSTR('小龙女和杨过', 1, 3); -- 小龙女-- instr:返回子串第一次出现的索引,如果找不到返回0SELECT INSTR('小龙女和杨过', '龙女'); -- 2SELECT INSTR('龙女小龙女和杨过', '龙女'); -- 1SELECT INSTR('龙女小龙女和杨过', '龙男'); -- 0 找不到返回0-- trim:去前后空格,或者可以去前后指定字符SELECT CHAR_LENGTH('   北极星   ');  -- 9SELECT CHAR_LENGTH(TRIM('   北极星   ')); -- 3-- 可以去前后指定字符SELECT TRIM('a' FROM 'aaa北aa极星aaa');-- 北aa极星 中间的没有去掉-- upper、lower:变大写,变小写SELECT UPPER('hello');-- HELLOSELECT LOWER('heLLo');-- hello-- lpad、rpad :用指定的字符实现左|右填充指定长度SELECT LPAD('hello', 10, 'a'); -- aaaaahelloSELECT 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<br />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_datedate_format这两个日期函数的时候,第二个参数定义的格式为:

#导入MD文档图片#超硬核学习手册系列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_idWHEN 30 THEN salary*2WHEN 40 THEN salary*3WHEN 50 THEN salary*4ELSE salaryEND 新工资FROM employees;
    • 方式2:可以相当于多重if的效果。

      • 语法:
      select 字段名1, 字段名2, case when 判断条件1 then 要显示的值1或语句1when 判断条件2 then 要显示的值2或语句2...else 要显示的值n或语句nend
      • 用法:
      /*查询员工的工资的情况如果工资>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 五个分组函数

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

函数 描述
COUNT 统计指定列记录数,记录为NULL的不统计
SUM 计算指定列的数值和,如果不是数值类型,那么计算结果为0
AVG 计算指定列的平均值
MAX、MIN 计算指定列的最大值
4.2.2 特点
  1. 以上五个分组函数都可以忽略NULL值。

  2. sumavg一般用于处理数值型,maxmincount可以处理任何数据类型。

  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 , 0SELECT SUM(hiredate), AVG(hiredate) FROM employees; -- 虽然都不会报错,但是没有什么意义SELECT MAX(last_name),MIN(last_name) FROM employees;-- Zlotkey, AbelSELECT MAX(hiredate),MIN(hiredate) FROM employees;-- 2016-03-03 00:00:00, 1992-04-03 00:00:00SELECT COUNT(commission_pct) FROM employees; -- 35 SELECT COUNT(last_name) FROM employees;  -- 107 -- 3、是否忽略nullSELECT 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.00SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees; -- 57 , 107-- 5、count函数的详细介绍SELECT COUNT(salary) FROM employees; -- 107SELECT COUNT(*) FROM employees;-- 107SELECT COUNT(1) FROM employees;-- 107SELECT 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 employeesWHERE department_id IS NOT NULL AND department_id BETWEEN 20 AND 60GROUP BY department_id;-- 分组再添加筛选条件 这里就需要一个关键字 having-- 查询哪个部门的员工个数>5SELECT COUNT(*), department_idFROM employeesGROUP BY department_idWHERE COUNT(*) > 5;  -- 分组后筛选就不能调用where了 不然会报错SELECT COUNT(*), department_idFROM employeesGROUP BY department_idHAVING COUNT(*) > 5;-- 也可以使用别名 再来一个案例来试试吧:-- 每个工种员工的最高工资>12000的工种编号和最高工资SELECT MAX(salary) 最高工资, job_idFROM employeesGROUP BY job_idHAVING 最高工资 > 12000;-- 分组后再排序-- 每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序SELECT MAX(salary) 最高工资, job_idFROM employeesWHERE commission_pct IS NOT NULLGROUP BY job_idHAVING 最高工资 > 6000ORDER BY 最高工资 ASC;-- 也可以多个字段分组-- 查询每个工种每个部门的最低工资,并且最低工资大于7000,并按最低工资降序SELECT MIN(salary), job_id, department_idFROM employeesGROUP BY job_id, department_idHAVING MIN(salary) > 7000ORDER BY MIN(salary) DESC;

#导入MD文档图片#超硬核学习手册系列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 分类

#导入MD文档图片#超硬核学习手册系列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 消除笛卡尔积现象

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

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

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

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

6.3 sql92

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

6.3.1 内连接

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

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

  • 分类:
    1. 等值连接
    2. 非等值连接
    3. 自连接
6.3.2 等值连接
  • 特点:

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

    -- 查询员工名和对应的部门名SELECT last_name, department_nameFROM employees, departmentsWHERE employees.`department_id` = departments.`department_id`;-- 可以起别名 查询员工名、工种号、工种名SELECT e.`last_name`, e.`job_id`, j.`job_title`FROM employees e, jobs jWHERE e.`job_id` = j.`job_id`;-- 可以调换表的顺序 查询员工名、工种号、工种名SELECT e.`last_name`, e.`job_id`, j.`job_title`FROM  jobs j, employees eWHERE  j.`job_id` = e.`job_id`;-- 可以加筛选,分组,排序 -- 查询每个工种的工种名和员工的个数,员工中有奖金,员工个数大于2,并且按员工个数降序SELECT j.`job_title`, COUNT(*) 员工个数FROM employees e, jobs jWHERE e.`job_id` = j.`job_id`AND e.`commission_pct` IS NOT NULL GROUP BY j.`job_title`HAVING 员工个数 > 2ORDER BY 员工个数 DESC;-- 可以2表以上连接  查询员工名、部门名和所在的城市,并且城市是以o开头的。SELECT e.`last_name`, d.`department_name`, l.`city`FROM employees e, departments d, locations lWHERE e.`department_id` = d.`department_id`AND d.`location_id` = l.`location_id`AND l.`city` LIKE 'o%';

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

  • 注意:

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

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

6.3.3 非等值连接

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

  • 案例演示:

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

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

6.3.4 自连接

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

  • 案例演示

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

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

6.4 sql99

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

6.4.1 语法格式

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

```sql\
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 `的语法格式。

  ```sql
  -- 等值连接-- 查询员工名和对应的部门名SELECT last_name, department_nameFROM employeesINNER JOIN departments ON  employees.`department_id` = departments.`department_id`;-- 可以起别名、inner可以省略-- 查询员工名、工种号、工种名SELECT e.`last_name`, e.`job_id`, j.`job_title`FROM employees eJOIN jobs jON e.`job_id` = j.`job_id`;-- 可以调换表的顺序 查询员工名、工种号、工种名SELECT e.`last_name`, e.`job_id`, j.`job_title`FROM  jobs jJOIN employees eON j.`job_id` = e.`job_id`;-- 可以加筛选,分组,排序 -- 查询每个工种的工种名和员工的个数,员工中有奖金,员工个数大于2,并且按员工个数降序SELECT j.`job_title`, COUNT(*) 员工个数FROM employees eJOIN jobs jON e.`job_id` = j.`job_id`WHERE e.`commission_pct` IS NOT NULL GROUP BY j.`job_title`HAVING 员工个数 > 2ORDER BY 员工个数 DESC;-- 可以2表以上连接  查询员工名、部门名和所在的城市,并且城市是以o开头的。-- 这种类似三表连接,还是有一定顺序的,如果两者没有连接条件,就不能是连接条件了SELECT e.`last_name`, d.`department_name`, l.`city`FROM employees eJOIN 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 eJOIN job_grades gON 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 eJOIN employees mON e.`manager_id` = m.`employee_id`;
  • 可以看出,sql99语法可以更好的看出哪种是连接条件,哪种筛选条件。
6.4.3 外连接

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

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

  • 分类:

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

      #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

      简单使用下:

      ``sq;<br/>-- 左连接SELECT b.name, bo.*FROM beauty bLEFT OUTER JOIN boys boON b.boyfriend_id= bo.idORDER BY bo.id`;

      
      
      ![](https://s4.51cto.com/images/blog/202108/03/45d146231990138943451ef5c2a41562.png?x-oss-process=image/watermark,size_14,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_20,type_ZmFuZ3poZW5naGVpdGk=)
      
      可以看到,得到了内连接的结果+左表中不满足数据的结果。我们就可以以此来得到不在男神表的女神信息了(就是那些有NULL值的女神)。
    2. 右外连接:将满足要求的数据显示,右表不满足要求的数据也显示,保证左表的数据全部显示。主表是右表,从表是左表,使用right【outer】关键字。

      #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

      简单使用下:

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

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

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

      #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

  • 特点:

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

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

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

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

    4. 全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的。
  • 案例演示下:

    -- 不在男神表的的女神名SELECT b.nameFROM beauty bLEFT OUTER JOIN boys boON b.`boyfriend_id` = bo.`id`WHERE bo.`id` IS NULL; -- 查询哪个部门没有员工SELECT d.*,e.employee_idFROM employees eRIGHT JOIN departments dON d.`department_id` = e.`department_id`WHERE e.`employee_id` IS NULL;

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

6.4.4 交叉连接

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

直接演示下吧:

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

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

进阶7:子查询

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

7.1 分类

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

7.2 简单使用

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

-- 子查询怎么使用?-- 谁的工资比 Abel 高?-- 先查询出Abel的工资吧SELECT salaryFROM employeesWHERE last_name = 'Abel';-- 查询员工信息,筛选工资比Abel的工资高的结果。SELECT * FROM employeesWHERE salary > (    SELECT salary   FROM employees  WHERE last_name = 'Abel');

可以看到子查询是放在()里,并且作为了主查询的一部分。

7.3 按结果集的行列数分

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

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

    SELECT salary FROM employees WHERE last_name = 'Abel';

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

    一般搭配着单行操作符使用有:&lt; &gt; = &gt;= &lt;= &lt;&gt; !=

  • 列子查询(一列多行)

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

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

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

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

  • 行子查询(一行多列)

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

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

  • 表子查询(多行多列)

    SELECT * FROM employees;

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

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

wherehaving后面,它支持标量子查询、列子查询、行子查询。

  • 标量子查询

    -- 标量子查询-- 可以实现多个子查询 -- 返回job_id与141号员工相同,salary比143号员工工资多的员工姓名,job_id和工资-- 第一次就比较详细的分析,写出来,后面就直接写出来-- 1.查询141号员工的job_idSELECT job_id FROM employeesWHERE employee_id = 141;-- 2.查询143号员工的salarySELECT salary FROM employeesWHERE employee_id = 143;-- 3.查询员工的姓名,job_id 和工资,要求job_id=1并且salary>2SELECT last_name, job_id, salaryFROM employeesWHERE 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, salaryFROM employeesWHERE salary = (  SELECT MIN(salary)  FROM employees);-- 在having后-- 查询最低工资大于50号部门最低工资的部门id和其最低工资-- 1.查询50号部门的最低工资  2.查询每个部门的最低工资  3. 在2的基础上筛选,满足min(salary)>1的结果SELECT MIN(salary), department_idFROM employeesGROUP BY department_idHAVING MIN(salary) > (   SELECT  MIN(salary) FROM employees  WHERE department_id = 50);

    #导入MD文档图片#超硬核学习手册系列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'
    );

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

  • 行子查询

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

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

    #导入MD文档图片#超硬核学习手册系列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 ) 部门名;

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

7.4.3 在from后面

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

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

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

7.4.4 exists后面(相关子查询)
  • 语法:

    exists(完整的查询语句)

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

    -- 查询有员工的部门名SELECT department_idFROM departments dWHERE EXISTS(   SELECT * FROM employees e   WHERE e.`department_id` = d.`department_id`);-- 查询没有男神的女神信息SELECT b.* FROM beauty bWHERE EXISTS(    SELECT bo.`id`  FROM boys bo    WHERE bo.id = b.`boyfriend_id`);

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

进阶8:分页查询

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

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

8.1 语法
select 查询列表 from 表【join type join 表2on 连接条件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 employeesWHERE commission_pct IS NOT NULLORDER BY salary DESCLIMIT 10;

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

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

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

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

    limit (page-1)*size, size

进阶9:联合查询

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

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

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

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

总结

学到这里的查询,可以看下完整的语法:

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

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

2. 约束

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

2.1 分类

#导入MD文档图片#超硬核学习手册系列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;

    #导入MD文档图片#超硬核学习手册系列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;

    #导入MD文档图片#超硬核学习手册系列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=数字;设置步长,也可以通过手动插入值,设置起始值。

    -- 设置步长5DROP 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');

    #导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备

4. 完结

#导入MD文档图片#超硬核学习手册系列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

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

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

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

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

#导入MD文档图片#超硬核学习手册系列2查询篇——深入浅出MySQL的知识点,学习收藏必备