MySQL基础2

最新推荐文章于 2023-02-13 16:12:40 发布

磊哥的小迷妹

最新推荐文章于 2023-02-13 16:12:40 发布

阅读量291

点赞数 1

分类专栏：笔记文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_48496502/article/details/120881315

版权

笔记专栏收录该内容

112 篇文章 3 订阅

订阅专栏

DQL语言

DQL语言
1、基础查询
- 1.1、语法
- 1.2、查询表中字段
- 1.3 、查询常量值、表达式、函数
- 1.4、起列的别名
- 1.5、去重`DISTINCT`
- 1.6、连接`CONCAT`
2、条件查询
- 2.1、语法
- 2.2、条件表达式查询
- 2.3、逻辑表达式查询
3、模糊查询
4、排序查询
- 4.1、语法
- 4.2、案例
5、常见函数
- 5.1、函数介绍
- 5.2、字符函数
- 5.3、数学函数
- 5.4、日期函数
- 5.5、其他函数
- 5.6、流程控制函数
- - 5.6.1、if 函数
  - 5.6.2、case 结构
- 5.7、练习
6、分组函数
- 6.1、语法
- 6.2、COUNT函数的详细介绍
- 6.3、练习
7、分组查询
- 7.1、语法
- 7.2、简单分组查询
- 7.3、添加分组筛选
- 7.4、按函数分组
- 7.5、按多个字段分组
- 7.6、添加排序
- 7.7、练习
8、连接查询
- 8.1、笛卡尔集
- 8.2、sql92
- 8.3、sql99
- 8.4、全外连接
- 8.5、交叉连接
- 8.6、练习
9、子查询
- 9.1、where、having后的子查询
- 9.2、select后的
- 9.3、from后的
- 9.4、exists后的
- 9.5、练习
- 9.6、子查询经典案例
10、分页查询
11、联合查询

DQL语言

Data Query Language
基础查询、条件查询、排序查询、常见函数、分组函数、分组查询、连接查询、子查询、分页查询、union联合查询

1、基础查询

1.1、语法

语法：

SELECT
		查询列表
FROM
		表名;

类似于 java中的 System.out.println(); 输出语句

特点：
语法中的查询列表可以是常量值、表达式、函数、表中的字段；
查询的结果是虚拟的表格。

1.2、查询表中字段

①查询表中的单个字段

SELECT
		last_name
FROM
		employees;

②查询表中的多个字段

SELECT
		`last_name`,
		`first_name`,
		`job_id`
FROM
		employees;

可以直接双击左边表中的字段名，自动加到输入框中
SQLyog中需要选中要执行的句子，然后执行
三击鼠标左键，选中光标所在行

f9键：执行
—— 执行需要先选中需要执行的句子
f12键：格式化
—— 格式化不需要选中，只需要将光标放在需要格式化的句子上

③查询表中的所有字段

SELECT
		*
FROM
		employees;

④注意：
查询操作之前，建议先打开此库

USE employees;

在这里插入图片描述
使用``这个符号来区分字段名和关键字，比如如果有个字段名为SELECT

SELECT
		`SELECT`
FROM
		employees;

在MySQL中，没有字符和字符串之分，所以用单引号和双引号都可以

1.3 、查询常量值、表达式、函数

①查询常量值

输出的就直接是常量值
在这里插入图片描述

以上几个例子表名，这里的 SELECT 语句和 Java 中的打印输出语句功能一样，只是输出，并有检查语法的功能

注意字符型和日期型的常量值必须用单引号

查询函数：

函数必须有返回值

SELECT 函数名(实参列表);

1.4、起列的别名

（不只是给列起别名，表名也可以用别名，但是注意一旦给表起了别名，整个句子就不可再有原表名，只可用别名）

①即是更改字段名的操作，重命名一个列

SELECT
		100%98
AS
		结果;

在这里插入图片描述
紧跟列名，也可以在列名和别名之间加入关键字‘AS’

别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写

②方式一：

SELECT
		`last_name` AS 姓,
		`first_name` AS 名
FROM
		employees;

③方式二：

SELECT
		`last_name`  姓,
		`first_name`  名
FROM
		employees;

效果如图
在这里插入图片描述

例题：查询salary，显示结果为OUT PUT

SELECT
		`salary`  AS  "OUT PUT"
FROM
		employees;

1.5、去重`DISTINCT`

展示所有员工编号，去重

SELECT
DISTINCT
		`job_id`
FROM
		employees;

只可以对一个字段名去重，不然会矛盾，不知道以哪个为准

1.6、连接`CONCAT`

① 在Java中的+：
运算符：左右两边都是数值型，就是运算符
连接符：当有一边是字符串，就是连接符

② MySQL中的+：
只是作为运算符
SELECT 100+90; 左右两边都是数值型，就是运算符
SELECT '123'+90; 当有一边是字符型，就将字符型转换为数值型（‘123’），若转换失败（‘Tom’），，就转换为0
SELECT NULL+90;当有一边是null，则结果必为null

在这里插入图片描述

③ 在MySQL中，要实现字符的连接，就需要用到关键字CONCAT

④ 在使用关键字CONCAT 的时候，注意，若里面的参数有一个是null，则结果必为null
此时就引用另一个函数 IFNULL(e1,e2) ，e1为可能有null值的字段名，e2指将null的部分替换成e2
在这里插入图片描述
使用函数IFNULL(e1,e2)

⑤例题：
将员工姓与名连接起来

SELECT 
  CONCAT(`last_name`, `first_name`) AS 姓名 
FROM
  `employees` ;

在这里插入图片描述

2、条件查询

2.1、语法

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

以上语句的执行顺序为：from---where---select

条件查询的分类：

①按条件表达式筛选
条件运算符 > 、 < 、 = 、 != 、 <> 、 >= 、 <=
②按逻辑表达式筛选
逻辑运算符 && 、 || 、 ! 、 and 、 or 、 not
③模糊查询
like、between and 、in、is null

2.2、条件表达式查询

查询工资>12000的员工信息

#查询工资>12000的员工信息
SELECT 
  * 
FROM
  `employees` 
WHERE `salary` > 12000 ;

查询部门编号不等于90的员工名和部门编号

#查询部门编号不等于90的员工名和部门编号
SELECT 
  `department_id`,
  CONCAT(`last_name`,' ',`first_name`) AS '姓名' 
FROM
  `employees` 
WHERE `department_id` <> 90 ;

2.3、逻辑表达式查询

作用：用于连接条件表达式
&& 、and ：两个都为true，结果才是true
|| 、or：有一个是true，结果就是true
！、not：表达式本身是false，结果就是true

查询工资在10000到20000之间的员工名、工资

#查询工资在10000到20000之间的员工名、工资
SELECT 
  `first_name`,
  `salary` 
FROM
  `employees` 
WHERE `salary` >= 10000 
  AND `salary` <= 20000 ;

查询部门编号不在90-110之间，或者工资高于15000的员工信息

#查询部门编号不在90-110之间，或者工资高于15000的员工信息
SELECT 
  * 
FROM
  `employees` 
WHERE `department_id` < 90 
  OR `department_id` > 110 
  OR `salary` > 15000 ;

或者是

SELECT 
  * 
FROM
  `employees` 
WHERE ! (`department_id` BETWEEN 90 
    AND 110) 
  OR `salary` > 15000 ;

3、模糊查询

通配符
%：表示任意多个字符，包含0个字符
_：表示任意单个字符
\：转义符
like ‘a’
一般和通配符一起使用

查询员工名中包含字符a的员工信息

#查询员工名中包含字符a的员工信息
SELECT 
  * 
FROM
  `employees` 
WHERE `last_name` LIKE '%a%' ;

注意：上面的代码中，字母a是不区分大小写的
在这里插入图片描述

查询员工名中第三个字符为n，第五个字符为 l 的员工名和工资

#查询员工名中第三个字符为n，第五个字符为l的员工名和工资
SELECT 
  `last_name`,
  `salary` 
FROM
  `employees` 
WHERE `last_name` LIKE '__n_l%' ;

查询员工名中第二个字符为_的员工名
（使用指定的转义符 \ ）

#查询员工名中第二个字符为_的员工名
SELECT 
  `last_name` 
FROM
  `employees` 
WHERE `last_name` LIKE '_\_%' ;

（使用自定义的转义符）

#查询员工名中第二个字符为_的员工名
SELECT 
  `last_name` 
FROM
  `employees` 
WHERE `last_name` LIKE '_^_%' ESCAPE '^';

between 1 and 2
注意前小后大，不可颠倒，若颠倒不会报错，只不过没有数据

查询员工编号在100-120之间的员工信息

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

查询不在某范围内的数据可以用NOT BETEWEEN 1 AND 2

in(e1,e2,e3)
含义：判断某字段的值是否属于in列表中的某一项
特点：注意in列表的值类型必须兼容或一致，123与’123’

查询工种编号为 IT_PROG、AD_VP 中的一个员工名和工种编号
不使用关键字 in

SELECT 
  `last_name`,
  `job_id` 
FROM
  `employees` 
WHERE `job_id` = 'IT_PROG' 
  OR `job_id` = 'AD_VP' 
  OR `job_id` = 'AD_PRES' ;

使用关键字 in

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

注意：
在此处，关键字 in 就等价于是等于括号里的内容，所以不可以有以下写法

WHERE `job_id` IN ('IT_PROG', 'AD_%');
等同于
WHERE `job_id` = 'IT_PROG' OR `job_id` = 'AD_%';
出现错误，输出结果为空

is null
（=或<>不能判断null值，只有<=>、is null、is not null 可以判断null 值）

查询没有奖金的员工名和奖金率

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

或者查询非null

SELECT 
  `last_name`,
  `commission_pct` 
FROM
  `employees` 
WHERE `commission_pct` IS NOT NULL ;

SELECT 
  `last_name`,
  `commission_pct` 
FROM
  `employees` 
WHERE ! (`commission_pct` IS NULL) ;

<=>安全等于
可以判断null值，也可以判断正常数值

查询没有奖金的员工名和奖金率

#查询没有奖金的员工名和奖金率
SELECT 
  `last_name`,
  `commission_pct` 
FROM
  `employees` 
WHERE `commission_pct` <=> NULL ;

查询工资为 12000 的员工信息

#查询工资为 12000 的员工信息
SELECT 
  * 
FROM
  `employees` 
WHERE `salary` <=> 12000 ;

查询表的结构

DESC `employees`;

在这里插入图片描述

4、排序查询

4.1、语法

ASC代表升序，DESC代表降序，不写则默认为升序
ORDER BY后面可以跟多个排序列表

SELECT 
  查询列表
FROM
  表 
WHERE 筛选条件    /*此行可不写*/
ORDER BY 排序列表 ASC/DESC ;   /*此行可不写*/

执行顺序如下
在这里插入图片描述

4.2、案例

案例：查询员工信息，要求工资从高到低排序

#查询员工信息，要求工资从高到低排序
SELECT 
  * 
FROM
  `employees` 
ORDER BY `salary` ASC ;

案例：查询部门编号>=90的员工信息，按入职时间的先后进行排序（添加筛选条件）

#查询部门编号>=90的员工信息，按入职时间的先后进行排序
SELECT 
  * 
FROM
  `employees` 
WHERE `department_id` >= 90 
ORDER BY `hiredate` ;

案例：按年薪的高低显示员工的信息和年薪（按表达式排序）

#按年薪的高低显示员工的信息和年薪
SELECT 
  *,
  `salary` * 12 * (1+ IFNULL(`commission_pct`, 0)) AS 年薪 
FROM
  `employees` 
ORDER BY 年薪 DESC ;

案例：按姓名的长度显示员工的姓名和工资（按函数排序）

#按姓名的长度显示员工的姓名和工资
SELECT 
  `last_name`,
  `salary` 
FROM
  `employees` 
ORDER BY LENGTH(`last_name`) DESC ;

案例：查询员工信息，要求按工资升序，再按员工编号降序（按多个字段排序）

#查询员工信息，要求按工资升序，再按员工编号降序
SELECT 
  * 
FROM
  `employees` 
ORDER BY `salary` ASC,
  `employee_id` DESC ;

案例：查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序

#查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序
USE `myemployees` ;

SELECT 
  `last_name`,
  `department_id`,
  `salary` * 12 * (1+ IFNULL(`commission_pct`, 0)) AS 年薪 
FROM
  `employees` 
ORDER BY 年薪 DESC,

5、常见函数

5.1、函数介绍

概念：类似于 Java 中的方法，是将一组逻辑语句封装起来，可重复使用
语法：

SELECT 函数名(实参列表) FROM 表名；

注意：
当实参列表涉及到表的时候，后面就要跟 FROM 表名

分类：

单行函数：CONCAT、LENGTH、IFNULL等
分组函数：是做统计使用的，又称为统计函数、聚合函数、组函数

单行函数

字符函数：length、concat、substr、instr、trim、upper、lower、lpad、rpad、replace
数学函数：round、ceil、floor、truncate、mod
日期函数：now、curdate、curtime、year、month、monthname、day、hour、minute、secend、str_to_date、date_format、datediff
其他函数：version、database、user
流程控制函数：if、case

5.2、字符函数

属于单行函数

LENGTH(str) 获取参数str的字节个数

注意，在utf-8中，字符占3个字节，gbk中占2个字节

#LENGTH(str)
SELECT 
  LENGTH('你好aaa') ;

在这里插入图片描述

CONCAT(str1,str2...) 拼接字符串

#CONCAT(str1,str2...)
USE `myemployees`;
SELECT 
  CONCAT(`last_name`, '_', `first_name`) AS 姓名 
FROM
  `employees` ;

UPPER(str)、LOWER(str) 改变大小写

#UPPER(str)、LOWER(str)
USE `myemployees`;
SELECT 
  CONCAT(
    UPPER(`last_name`),
    ' ',
    LOWER(`first_name`)
  ) AS 姓名 
FROM
  `employees` ;

SUBSTR(str,pos)、SUBSTR(str,pos,len) 字符截取

pos 指截取的索引、len 指截取的字符长度，注意非字节

注意在MySQL中，索引是从1开始的，在Java中是从0开始的

截取指定索引处后面的所有字符
#SUBSTR(str,pos)
SELECT SUBSTR('进一步有一步的欢喜',3);

在这里插入图片描述
注意此处的长度指的是字符长度

截取指定索引之后的指定字符长度的字符
#SUBSTR(str,pos,len)
SELECT SUBSTR('进一步有一步的欢喜',4,2);

在这里插入图片描述
使用全英文的情况，选取的是字母个数

使用全英文的情况
#SUBSTR(str,pos,len)
SELECT SUBSTR('abcdefgh',4,2);

在这里插入图片描述
案例：姓名中的首字符大写，其他字符小写然后用_拼接，显示出来

#姓名中的首字符大写，其他字符小写然后用_拼接，显示出来
USE `myemployees`;
SELECT 
  CONCAT(
    UPPER(SUBSTR(`last_name`, 1, 1)),
    '_',
    LOWER(SUBSTR(`last_name`, 2))
  ) 姓名 
FROM
  `employees` ;

INSTR(str,substr) 返回substr在str中首次出现的索引，若无，则返回0

#INSTR(str,substr)
SELECT INSTR('进一步有一步的欢喜','一');

在这里插入图片描述

TRIM() 去掉字符前后空格，或去掉字符前后的指定字符

去掉字符前后空格的情况

/*
以下执行结果为 12 
*/
SELECT 
  LENGTH('   张三   ') ;

/*
以下执行结果为 6 
*/
SELECT 
  LENGTH(TRIM('   张三   ')) ;

去掉字符前后的指定字符的情况

SELECT 
  TRIM('a' FROM 'aaaaa张三aaaa') AS 结果 ;

在这里插入图片描述
注意，这里是把 aa 看作一个字符，所以结果中还留有一个 a

SELECT 
  TRIM('aa' FROM 'aaaaa张三aaaa') AS 结果 ;

在这里插入图片描述

LPAD(str,len,padstr) 实现左填充，右边减短
将 str 变成指定 len 字符长度的字符串，以 padstr 来填充，若过长，从右边减短

将 str 变成指定 len 字符长度的字符串，以 padstr 来填充

SELECT 
  LPAD('开心', 5, 'a') AS 结果 ;

在这里插入图片描述
str 的长度超过 len 的长度，从右边减短至 len 的长度

SELECT 
  LPAD('很好开心', 2, 'a') AS 结果 ;

在这里插入图片描述

RPAD(str,len,padstr) 实现右填充，右边减短

SELECT 
  RPAD('很好开心', 9, 'ab') AS 结果 ;

在这里插入图片描述

REPLACE(str,from_str,to_str)替换

SELECT 
  REPLACE(
    '进一步有一步的欢喜',
    '一',
    '二'
  ) AS 结果 ;

在这里插入图片描述

5.3、数学函数

ROUND()四舍五入

默认四舍五入保留的是整数位

SELECT 
  ROUND(1.4544) ;

在这里插入图片描述

SELECT 
  ROUND(- 1.55) ;

在这里插入图片描述

保留指定小数位的四舍五入

SELECT 
  ROUND(1.5444454, 3) ;

在这里插入图片描述

CEIL()向上取整，返回>=该参数的最小整数

SELECT CEIL(4.44);

在这里插入图片描述

FLOOR()向下取整，返回<=该参数的最大整数

SELECT FLOOR(8.555);

在这里插入图片描述

TRUNCATE()截断，截断小数位，不按照四舍五入，直接截断

SELECT TRUNCATE(6.755554,2);

在这里插入图片描述

MOD()取余，注意取余结果的正负号与被除数保持一致

注意取余结果的正负号与被除数保持一致

SELECT MOD(-10,3);

在这里插入图片描述

SELECT MOD(10,3);

在这里插入图片描述

SELECT MOD(10,-3);

在这里插入图片描述

5.4、日期函数

NOW()返回当前系统日期+时间
CURDATE()返回当前系统日期，不包含时间
CURTIME()返回当前时间，不包含日期
获取指定的部分的操作，年、月、日、小时、分钟、秒

年

#取当前时间的年份
SELECT 
  YEAR(NOW()) ;

在这里插入图片描述

#取输入时间的年份
SELECT 
  YEAR('2045-12-30') ;

在这里插入图片描述
月

SELECT MONTH(NOW());  #返回当前时间的月份
SELECT MONTHNAME(NOW());  #返回当前时间月份的英文 November

日

SELECT DAY(NOW());

STR_TO_DATE(str,format) 将日期格式的字符转换成指定格式的日期

SELECT STR_TO_DATE('10-23-2056','%m-%d-%Y') ;   #2056-10-23

在这里插入图片描述
案例：查询入职时间为1992-4-3的员工信息

#查询入职时间为1992-4-3的员工信息
SELECT 
  * 
FROM
  `employees` 
WHERE `hiredate` = '1992-4-3' ;

#查询入职时间为1992-4-3的员工信息
SELECT 
  * 
FROM
  `employees` 
WHERE `hiredate` = STR_TO_DATE('4-3 1992', '%c-%d %Y') ;

DATE_FORMAT()将日期转换为字符

SELECT 
  DATE_FORMAT('2018-6-6', '%Y年%m月%d日') ;

在这里插入图片描述

SELECT 
  DATE_FORMAT(NOW(), '%Y年%m月%d日') ;

在这里插入图片描述
案例：查询有奖金的员工名和入职日期（xx月/xx日 xx年）

#查询有奖金的员工名和入职日期（xx月/xx日 xx年）
USE `myemployees`;
SELECT 
  `last_name`,
  DATE_FORMAT(`hiredate`, '%m月/%d日 %y年') 
FROM
  `employees` 
WHERE `commission_pct` IS NOT NULL ;

DATEDIFF(d1,d2)将 d1 的日期减去 d2 日期，得到天数的差额

计算自己活了多少天

SELECT 
  DATEDIFF(NOW(), '1999-10-24') ;

在这里插入图片描述

5.5、其他函数

VERSION() 查询MySQL版本
DATABASE() 查询当前数据库
USER() 查询当前用户

5.6、流程控制函数

5.6.1、if 函数

IF(expr1,expr2,expr3) expr1 是表达式，若表达式成立，则执行 expr2 ，若不成立，则执行 expr3

案例：若员工有奖金，则输出有，若无，则输出无

#若员工有奖金，则输出有，若无，则输出无
SELECT 
  `last_name`,
  `commission_pct`,
  IF(
    `commission_pct` IS NULL,
    '无',
    '有'
  ) AS 奖金 
FROM
  `employees` ;

5.6.2、case 结构

第一种，相当于Java中的Switch-case语句

格式：

CASE 要判断的字段或表达式
WHEN 常量1 THEN 要显示的值1或语句1;   #注意这里的分号，是显示值就不要用
WHEN 常量2 THEN 要显示的值2或语句2;   #显示值     语句;
...
ELSE 要显示的值n或语句n;       #else这一句可以省略
END

案例：查询员工的工资，根据部门号，显示原工资的x倍
当部门号=30，工资变成1.1倍
当部门号=40，工资变成1.2倍
当部门号=50，工资变成1.3倍
剩下的显示原工资

此时是作为查询的值来看的，所以要用到 SELECT ，且分句间不用分号

#查询员工的工资，根据部门号，显示原工资的x倍
SELECT 
  `department_id`,
  `salary` 原工资,
  CASE
    `department_id` 
    WHEN 30 
    THEN `salary` * 1.1 
    WHEN 40 
    THEN `salary` * 1.2 
    WHEN 50 
    THEN `salary` * 1.3 
    ELSE `salary` 
  END AS 新工资 
FROM
  `employees` ;

第二种，相当于多重 if 语句

格式：

CASE 
WHEN 条件1 THEN 要显示的值1或语句1;  #显示值     语句;
WHEN 条件2 THEN 要显示的值2或语句2;  #注意这里的分号，是显示值就不要用
...
ELSE 要显示的值n或语句n;       #else这一句可以省略
END

案例：查询员工的工资等级情况

此时是作为查询的值来看的，所以要用到 SELECT ，且分句间不用分号

#查询员工的工资等级情况
SELECT 
  `salary` 工资,
  CASE
    WHEN `salary` > 20000 
    THEN 'A' 
    WHEN `salary` > 15000 
    THEN 'B' 
    WHEN `salary` > 10000 
    THEN 'C' 
    ELSE 'D' 
  END AS '等级' 
FROM
  `employees` ;

5.7、练习

显示系统时间（日期+时间）

SELECT NOW();

查询员工号、姓名、工资，以及工资提高20%后的结果

#查询员工号、姓名、工资，以及工资提高20%后的结果
USE `myemployees`;
SELECT 
  `job_id` 工号,
  `last_name` 姓名,
  `salary` 工资,
  `salary` * 1.2 新工资 
FROM
  `employees` ;

将员工的姓名按照首字母排序，并写出姓名长度

#将员工的姓名按照首字母排序，并写出姓名长度
USE `myemployees`;
SELECT 
  `last_name` 姓名,
  LENGTH(`last_name`) 
FROM
  `employees` 
ORDER BY SUBSTR(`last_name`, 1, 1) ;

使用 CASE-WHEN ，按照下面条件完成

job	grade
AD_PRES	A
ST_MAN	B
IT_PROG	C
SA_REP	D
ST_CLERK	E

USE `myemployees`;
SELECT 
  `job_id`,
  CASE
    `job_id` 
    WHEN 'AD_PRES' 
    THEN 'A' 
    WHEN 'ST_MAN' 
    THEN 'B' 
    WHEN 'IT_PROG' 
    THEN 'C' 
    WHEN 'SA_REP' 
    THEN 'D' 
    WHEN 'ST_CLERK' 
    THEN 'E' 
  END AS 'grade' 
FROM
  `employees` ;

6、分组函数

6.1、语法

功能：用作统计使用，又称为聚合函数或统计函数或组合函数

SUM求和，非 null 值的的和
AVG平均值，非 null 值的的平均值
MIN最小值，非 null 值
MAX最大值，非 null 值
COUNT计数，统计的是非 null 值的个数

注意：

以上分组函数都忽略了 null 值
SUM 、AVG 一般用于处理数值型
MAX、MIN、COUNT 可以处理任何类型
（字符和日期也可以排大小）
可以和 DISTINCT 搭配使用
和分组函数一同查询的字段要求是 group by 后的字段

SELECT 
  SUM(`salary`) 和,
  AVG(`salary`) 平均值,
  MAX(`salary`) 最大值,
  MIN(`salary`) 最小值,
  COUNT(`salary`) 计数 
FROM
  `employees` ;

在这里插入图片描述

和 DISTINCT 搭配使用

#去重前后结果不一样
SELECT 
  SUM(DISTINCT `salary`),
  SUM(`salary`) 
FROM
  `employees` ;

在这里插入图片描述
和分组函数一同查询的字段是有要求的，不是什么字段都可以的

SELECT AVG(`salary`),`last_name` FROM `employees`;

执行结果如下
这里的员工姓名是没有意义的，即不是什么字段都可以和分组函数一同查询
在这里插入图片描述

6.2、COUNT函数的详细介绍

COUNT(*)

当表有多列多个字段的时候，只要某行数据有一个字段不为 null ，就计入数，其他字段有 null 也无妨

SELECT 
  COUNT(*) 
FROM
  `employees` ;

在这里插入图片描述
2. COUNT(1)

和COUNT(*)执行得出一样的结果，相当于加了一列 1,

所以也可以是COUNT(2) 、COUNT(‘阿巴阿巴’) ，只不过COUNT(1) 效率更高

6.3、练习

查询员工工资的最大值、最小值、平均值、总和

SELECT 
  MAX(`salary`),
  MIN(`salary`),
  ROUND(AVG(`salary`),2),  #保留两位小数
  SUM(`salary`) 
FROM
  `employees` ;

查询员工表中最大入职时间和最小入职时间的相差天数

SELECT 
  DATEDIFF(MAX(`hiredate`), MIN(`hiredate`)) 
FROM
  `employees` ;

在这里插入图片描述

查询部门编号为90的员工个数

#查询部门编号为90的员工个数
SELECT 
  COUNT(*) 
FROM
  `employees` 
WHERE `department_id` = 90 ;

在这里插入图片描述

7、分组查询

7.1、语法

引入：查询每个部门的平均工资

语法：

SELECT 
  分组函数,列     /*注意这里的列要出现在GROUP BY的后面*/
FROM
  表 
WHERE 筛选条件       /*此行可不写*/
GROUP BY 分组的列表
HAVING 筛选条件       /*此行可不写*/
ORDER BY 排序列表 ASC/DESC ;   /*此行可不写*/

注意：

查询列表必须特殊，要求是分组函数和GROUP BY后出现的字段
这里的 WHERA 语句是对表的原始数据进行的筛选，假如要对分组之后的数据进行筛选，就要用 HAVING
即是分组函数做条件的筛选，放在 HAVING 子句中
GROUP BY 支持多个字段分组
也可添加排序，排序语句放在最后面

7.2、简单分组查询

案例1：
查询每个工种的最高工资

#查询每个工种的最高工资
SELECT 
  MAX(`salary`),
  `job_id` 
FROM
  `employees` 
GROUP BY `job_id` ;

案例2：
查询每个位置的部门个数

#查询每个位置的部门个数
SELECT 
  COUNT(1),
  `location_id` 
FROM
  `departments` 
GROUP BY `location_id` ;

7.3、添加分组筛选

案例1：
查询邮箱中包含 a 字符的，每个部门的平均工资

#查询邮箱中包含 a 字符的，每个部门的平均工资
SELECT 
  AVG(`salary`),
  `department_id` 
FROM
  `employees` 
WHERE `email` LIKE '%a%' 
GROUP BY `department_id` ;

案例2：
查询每个领导手下员工的有奖金的最高工资

#查询每个领导手下员工的有奖金的最高工资
SELECT 
  MAX(`salary`),
  `manager_id` 
FROM
  `employees` 
WHERE `commission_pct` IS NOT NULL 
GROUP BY `manager_id` ;

案例3：
查询哪个部门员工个数>2
要对分组之后的数据进行筛选，用 HAVING

#查询哪个部门员工个数>2
SELECT 
  COUNT(*),
  `department_id` 
FROM
  `employees` 
GROUP BY `department_id` 
HAVING COUNT(*) > 2 ;

案例4：
查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT 
  MAX(`salary`),
  `job_id` 
FROM
  `employees` 
WHERE `commission_pct` IS NOT NULL 
GROUP BY `job_id` 
HAVING MAX(`salary`) > 12000 ;

案例5：
查询领导编号>102的每个领导手下的最低工资>5000的领导编号及最低工资

#查询领导编号>102的每个领导手下的最低工资>5000的领导编号及最低工资
SELECT 
  MIN(`salary`),
  `manager_id` 
FROM
  `employees` 
WHERE `manager_id` > 102 
GROUP BY `manager_id` 
HAVING MIN(`salary`) > 5000 ;

7.4、按函数分组

案例1：
按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪几组

#按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪几组
SELECT 
  COUNT(*),
  LENGTH(`last_name`) 
FROM
  `employees` 
GROUP BY LENGTH(`last_name`) 
HAVING COUNT(*) > 5 ;

7.5、按多个字段分组

案例1：
查询每个部门每个工种的员工的平均工资

#查询每个部门每个工种的员工的平均工资
SELECT 
  AVG(`salary`),
  `department_id`,
  `job_id` 
FROM
  `employees` 
GROUP BY `department_id`,
  `job_id` ;

此时注意 GROUP BY 语句后的分句的顺序

7.6、添加排序

案例1：
查询每个部门每个工种的员工的平均工资，并按照平均工资降序

#查询每个部门每个工种的员工的平均工资，并按照平均工资降序
SELECT 
  AVG(`salary`),
  `department_id`,
  `job_id` 
FROM
  `employees` 
GROUP BY `department_id`,
  `job_id` 
ORDER BY AVG(`salary`) DESC ;

7.7、练习

查询各工种的员工工资最大值、最小值、平均值、总和，并按照工种升序

#查询各工种的员工工资最大值、最小值、平均值、总和，并按照工种升序
SELECT 
  MAX(`salary`),
  MIN(`salary`),
  AVG(`salary`),
  SUM(`salary`),
  `job_id` 
FROM
  `employees` 
GROUP BY `job_id` 
ORDER BY `job_id` ASC ;

查询员工最高工资和最低工资的差额

#查询员工最高工资和最低工资的差额
SELECT 
  MAX(`salary`) - MIN(`salary`) 
FROM
  `employees` ;

查询各管理者手下员工的最低工资，查询最低工资不能低于6000的，没有管理者的员工不算在内

#查询各管理者手下员工的最低工资，查询最低工资不能低于6000的，没有管理者的员工不算在内
SELECT 
  MIN(`salary`),
  `manager_id` 
FROM
  `employees` 
WHERE `manager_id` IS NOT NULL 
GROUP BY `manager_id` 
HAVING MIN(`salary`) >= 6000 ;

查询所有部门的编号、员工数量、工资平均值，并按照工资平均值降序

#查询所有部门的编号、员工数量、工资平均值，并按照工资平均值降序
SELECT 
  `department_id` ,
  AVG(`salary`),
  COUNT(*)
FROM
  `employees` 
GROUP BY `department_id` 
ORDER BY AVG(`salary`) DESC ;

这里的部门编号不一定要放在最后面

查询每个工种的员工人数

#查询每个工种的员工人数
SELECT 
  `job_id`,
  COUNT(*) 
FROM
  `employees` 
GROUP BY `job_id` ;

8、连接查询

含义： 又称多表查询，当查询的字段来自多个表时，就会用到连接查询

分类：

按年代分：

sql92标准：仅仅支持内连接
sql99标准：支持内连接+外连接（左外、右外）+交叉连接

按功能分：

内连接
1. 等值连接
2. 非等值连接
3. 自连接
外连接
1. 左外连接
2. 右外连接
3. 全外连接
交叉连接

8.1、笛卡尔集

#查询两个表的数据
USE `girls`;
SELECT `name`,`boyName` FROM `beauty`,`boys`;

以上代码试图查询两个表的数据，执行结果是美女表的每一行数据都对应一个男友表，显示的结果就会有 12*4 条的数据

这种现象称为笛卡尔乘积现象

出现的原因是没有有效的连接条件，没让两个表在查询的时候有连接匹配的条件
在这里插入图片描述

笛卡尔集会在下面条件下产生:

省略连接条件
连接条件无效
所有表中的所有行互相连接

为了避免这种情况，在美女表里面有一列数据，为 boyfriend_id，使用此列与男友表的id进行匹配

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

在这里插入图片描述

8.2、sql92

8.2.1、sql92—等值连接

上面的例子就是等值连接，就是指连接条件里面是等于号

多表连接的结果为多表的交集部分
n 表连接，至少需要 n-1 个连接条件
多表的顺序没有要求
一般要为表起别名
可以搭配排序、分组、筛选等子句使用

注意给表起别名的情况（案例3）
注意两个表的顺序可以调换（案例4）
可以加筛选条件（案例5、6）
可以加分组（案例7、8）
可以加排序（案例9）
可以实现三表连接查询（案例10、11）

案例1：
查询女神名对应的男神名

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

案例2：
查询员工名和对应的部门名

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

案例3：
查询员工名、工种号、工种名

#查询员工名、工种号、工种名
SELECT 
  `last_name`,
  `employees`.`job_id`,   #注意这里是用表名来限定是哪个表的数据
  `job_title` 
FROM
  `employees`,
  `jobs` 
WHERE `jobs`.`job_id` = `employees`.`job_id` ;

此时用到表名的地方比较多，就可以给表起别名
注意： 一旦给表起别名，则语句中涉及到表名的地方都要用到别名，不可用原表名

#查询员工名、工种号、工种名
SELECT 
  `last_name`,
  e.`job_id`,
  `job_title` 
FROM
  `employees` AS e,
  `jobs` AS j 
WHERE j.`job_id` = e.`job_id` ;

案例4：
查询员工名、工种号、工种名

可以将表名顺序调换

#查询员工名、工种号、工种名
SELECT 
  `last_name`,
  e.`job_id`,
  `job_title` 
FROM
  `jobs` AS j,
  `employees` AS e 
WHERE j.`job_id` = e.`job_id` ;

案例5：
查询有奖金的员工名、部门名

可以加筛选条件

#查询有奖金的员工名、部门名
SELECT 
  `last_name`,
  `department_name` 
FROM
  `departments`,
  `employees` 
WHERE `employees`.`department_id` = `departments`.`department_id` 
  AND `employees`.`commission_pct` IS NOT NULL ;

案例6：
查询城市名中第二个字母为o的对应的城市名和部门名

#查询城市名中第二个字母为o的对应的城市名和部门名
SELECT 
  `department_name`,
  `city` 
FROM
  `departments` d,
  `locations` l 
WHERE d.`location_id` = l.`location_id` 
  AND l.`city` LIKE '_o%' ;

案例7：
查询每个城市的部门个数

可以加分组

#查询每个城市的部门个数
SELECT 
  COUNT(*),    #注意这里是部门的个数，但是却没有限制是部门的表，直接用函数
  `city` 
FROM
  `departments` d,
  `locations` l 
WHERE d.`location_id` = l.`location_id` 
GROUP BY `city` ;

案例8：
查询有奖金的每个部门的部门名和部门领导的编号和该部门的最低工资

#查询有奖金的每个部门的部门名和部门领导的编号和该部门的最低工资
SELECT 
  `department_name` '部门名',
  d.`manager_id` '领导编号',
  MIN(`salary`) '最低工资' 
FROM
  `departments` d,
  `employees` e 
WHERE d.`department_id` = e.`department_id` 
  AND `commission_pct` IS NOT NULL 
GROUP BY `department_name`,
  d.`manager_id` ;

案例9：
查询每个工种的工种名和员工的个数，并且按员工个数降序

可以加排序

#查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT 
  `job_title` '工种名',
  COUNT(*) 员工个数 
FROM
  `employees` e,
  `jobs` j 
WHERE e.`job_id` = j.`job_id` 
GROUP BY `job_title`    #这里的工种名是一个表里独有的，就不需要表名限制
ORDER BY COUNT(*) DESC ;

案例10：
查询员工名、部门名、所在城市

可以实现三表连接查询

#查询员工名、部门名、所在城市
SELECT 
  `last_name` 员工名,
  `department_name` 部门名,
  `city` 城市 
FROM
  `departments` d,
  `employees` e,
  `locations` l 
WHERE d.`department_id` = e.`department_id` 
  AND d.`location_id` = l.`location_id` ;

案例11：
加上筛选条件和排序的三表连接

#查询员工名、部门名、所在城市，城市名以s开头，按员工名降序
SELECT 
  `last_name` 员工名,
  `department_name` 部门名,
  `city` 城市 
FROM
  `departments` d,
  `employees` e,
  `locations` l 
WHERE d.`department_id` = e.`department_id` 
  AND d.`location_id` = l.`location_id` 
  AND `city` LIKE 's%' 
ORDER BY `last_name` DESC ;

8.2.2、sql92—非等值连接

提供一个表，里面有工资的等级范围，以及对应的等级，此时给员工工资分等级，就要查看员工工资是在哪个范围内的，即查看员工工资大于最小和小于最大

此时就用到非等值连接

在这里插入图片描述

案例1：
查询员工工资和工资级别，筛选级别为A的

#查询员工工资和工资级别，筛选级别为A的
SELECT 
  `salary` 工资,
  `grade_level` 级别 
FROM
  `job_grades` g,
  `employees` e 
WHERE `salary` BETWEEN `lowest_sal` 
  AND `highest_sal` 
  AND `grade_level` = 'A' ;

8.2.3、sql92—自连接

就是连接的两组数据都是源自一个表，就要把一个表做两个表用

案例1：
查询员工名和上级名

原理：找出员工名对应的领导编号，再根据领导编号找对应的员工编号，得到领导名

#查询员工名和上级名
SELECT 
  员工表.`last_name` 员工名,
  员工表.`manager_id` 领导编号,
  领导表.`employee_id` 领导员工号,
  领导表.`last_name` 领导名 
FROM
  `employees` 员工表,
  `employees` 领导表 
WHERE 领导表.`employee_id` = 员工表.`manager_id` ;

8.2.4、sql92—练习

显示员工表的最大工资、工资平均值

#显示员工表的最大工资、工资平均值
SELECT 
  MAX(`salary`),
  AVG(`salary`) 
FROM
  `employees` ;

查询员工表的员工编号、工种编号、姓名，并按部门编号降序，工资升序

#查询员工表的员工编号、工种编号、姓名，并按部门编号降序，工资升序
SELECT 
  `employee_id`,
  `job_id`,
  `last_name` 
FROM
  `employees` 
ORDER BY `department_id` DESC,
  `salary` ASC ;

查询员工表的工种编号包含 a 和 e 的，并且 a 在 e 的前面

#查询员工表的工种编号包含 a 和 e 的，并且 a 在 e 的前面
SELECT 
  `job_id` 
FROM
  `employees` 
WHERE `job_id` LIKE '%a%e%' ;

查询部门编号 90 的员工的工种编号、地址编号
城市在 Toronto 的员工的姓名、工种编号、部门编号、部门名
查询每个工种、每个部门的部门名、工种名、最低工资
查询每个国家的部门个数>2的国家编号
查询指定员工姓名，对应的员工编号、管理者名、管理者的员工号

8.3、sql99

注意：

注意多表连接时 from 表和 join 表的顺序
inner 可以省略
添加排序、分组、筛选

语法：

SELECT 
  查询列表
FROM
  表1 别名 
  【连接类型】 JOIN 表2 别名 
    ON 连接条件2
  【连接类型】 JOIN 表3 别名 
    ON 连接条件3
  ...
【WHERE 筛选条件】     
【GROUP BY 分组的列表】
【HAVING 筛选条件】     
【ORDER BY 排序列表 ASC/DESC】 ;

连接类型：

内连接：inner
外连接
1. 左外：left outer
2. 右外：right outer
3. 全外：full outer
交叉连接：crose

8.3.1、sql99—等值连接

案例1 ：查询员工名、部门名

#查询员工名、部门名
SELECT 
  `last_name`,
  `department_name` 
FROM
  `employees` e 
  INNER JOIN `departments` d 
    ON e.`department_id` = d.`department_id` ;

案例2 ：查询名字中包含 e 的员工名和工种名

#查询名字中包含 e 的员工名和工种名
SELECT 
  `last_name`,
  `job_title` 
FROM
  `employees` e 
  INNER JOIN `jobs` j 
    ON j.`job_id` = e.`job_id` 
WHERE `last_name` LIKE '%e%' ;

案例3 ：查询部门个数>3的城市名和部门个数

#查询部门个数>3的城市名和部门个数
SELECT 
  `city`,
  COUNT(*) 
FROM
  `locations` l 
  INNER JOIN `departments` d 
    ON l.`location_id` = d.`location_id` 
GROUP BY `city` 
HAVING COUNT(*) > 3 ;

案例4 ：查询哪个部门的部门员工个数>3部门名和员工个数，并按个数降序

#查询哪个部门的部门员工个数>3部门名和员工个数，并按个数降序
SELECT 
  `department_name`,
  COUNT(*) 
FROM
  `departments` d 
  INNER JOIN `employees` e 
    ON d.`department_id` = e.`department_id` 
GROUP BY `department_name` 
HAVING COUNT(*) > 3 
ORDER BY COUNT(*) DESC ;

案例5 ：查询员工名、部门名、工种名，并按部门名降序

#查询员工名、部门名、工种名，并按部门名降序
SELECT 
  `last_name`,
  `department_name`,
  `job_title` 
FROM
  `departments` d 
  INNER JOIN `employees` e 
    ON d.`department_id` = e.`department_id` 
  INNER JOIN `jobs` j 
    ON e.`job_id` = j.`job_id` 
ORDER BY `department_name` DESC ;

注意

这里涉及到多表连接，就需要多个 join 语句

注意 from 的表要和 join 的表有连接部分

所以第一个 join 不可以是如下：
会报错

FROM
  `departments` d 
  INNER JOIN  `jobs` j 
    ON d.`department_id` = e.`department_id` 
  INNER JOIN `employees` e
    ON e.`job_id` = j.`job_id`

8.3.2、sql99—非等值连接

案例1 ：
查询员工的工资级别

#查询员工的工资级别
SELECT 
  `grade_level`,
  `salary` 
FROM
  `employees` e 
  JOIN `job_grades`    #INNER可以省略
    ON `salary` BETWEEN `lowest_sal` 
    AND `highest_sal` ;

案例2 ：
查询工资级别的个数>20，并且按工资级别降序

#查询工资级别的个数>20，并且按工资级别降序
SELECT 
  `salary`,
  `grade_level`,
  COUNT(*) 
FROM
  `employees` e 
  INNER JOIN `job_grades` g 
    ON `salary` BETWEEN `lowest_sal` 
    AND `highest_sal` 
GROUP BY `grade_level` 
HAVING COUNT(*) > 20 
ORDER BY `grade_level` DESC ;

8.3.3、sql99—自连接

案例1 ：
查询员工的名字、上级名字

#查询员工的名字、上级名字
SELECT 
  e.`last_name` 员工名,
  m.`last_name` 上级名 
FROM
  `employees` e 
  INNER JOIN `employees` m 
    ON e.`manager_id` = m.`employee_id` ;

案例2 ：
查询员工姓名中包含k的员工名字、上级名字

#查询员工姓名中包含k的员工名字、上级名字
SELECT 
  e.`last_name` 员工名,
  m.`last_name` 上级名 
FROM
  `employees` e 
  INNER JOIN `employees` m 
    ON e.`manager_id` = m.`employee_id` 
WHERE e.`last_name` LIKE '%k%' ;

8.3.4、sql99—左（右）外连接

外连接

应用场景： 用于查询在两表相对应之后，一个表中有，而另一个表中没有的记录

特点：

在主从表连接对应之后，外连接的查询结果为主表中的所有记录
1. 如果从表中有和它匹配的，则显示记录；
2. 如果从表中没有和它匹配的，则显示null
所以外连接查询结果=内连接结果+主表中有而从表中没有的记录
左外连接，left join左边的是主表
右外连接，right join右边的是主表
所以，左外和右外交换两个表的顺序，可以实现同样的效果
一般要查的数据在哪个表，哪个表就作为主表

注意：
要查询主表中有而从表中没有的记录的时候，需要筛选出从表数据为null的部分

此时筛选条件一般用从表的主键=null，因为作为主键的字段设置的是内容不可为null

如果筛选条件用其他字段，就有可能碰到某行内容为null，筛选之后会出现多出数据的情况

案例1：
查询男朋友不在男神表的女神名

左外连接

#查询男朋友不在男神表的女神名
SELECT 
  `name`,
  `boyfriend_id`,
  bo.* 
FROM
  `beauty` b 
  LEFT OUTER JOIN `boys` bo 
    ON b.`boyfriend_id` = bo.`id` 
WHERE bo.`id` IS NULL ;

右外连接

#查询男朋友不在男神表的女神名
SELECT 
  `name`,
  `boyfriend_id`,
  bo.* 
FROM
  `boys` bo 
  RIGHT OUTER JOIN `beauty` b 
    ON b.`boyfriend_id` = bo.`id` 
WHERE bo.`id` IS NULL ;

在这里插入图片描述

案例2：
查询哪个部门没有员工的部门名

左外连接

#查询哪个部门没有员工的部门名
SELECT 
  `department_name`,
  e.* 
FROM
  `departments` d 
  LEFT JOIN `employees` e 
    ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;

右外连接

#查询哪个部门没有员工的部门名
SELECT 
  `employee_id`,
  d.* 
FROM
  `employees` e 
  RIGHT JOIN `departments` d 
    ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;

在这里插入图片描述

8.4、全外连接

全外连接在MySQL数据库中无法使用，在Oracle中可以体现

外连接查询结果=内连接结果+主表中有而从表中没有的记录

全外连接查询结果=内连接结果+主表中有而从表中没有的记录+从表中有而主表中没有的记录

USE `girls`;
SELECT 
  b.*,
  bo.* 
FROM
  `beauty` b FULL 
  OUTER JOIN `boys` bo 
    ON b.`boyfriend_id` = bo.`id` ;

8.5、交叉连接

交叉连接执行的结果就是笛卡尔乘积

即表1有11行数据，表2有4行数据，那么交叉连接执行之后有11×4=44行数据

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

8.6、练习

查询编号＞3的女神的男友信息，如果有则列出信息，若没有，用null填充

#查询编号＞3的女神的男友信息，如果有则列出信息，若没有，用null填充
SELECT 
  b.`id`,
  bo.* 
FROM
  `beauty` b 
  LEFT OUTER JOIN `boys` bo 
    ON b.`boyfriend_id` = bo.`id` 
WHERE b.`id` > 3 ;

查询哪个城市没有部门

#查询哪个城市没有部门
SELECT 
  l.`city`,
  d.* 
FROM
  `locations` l 
  LEFT JOIN `departments` d 
    ON l.`location_id` = d.`location_id` 
WHERE `department_id` IS NULL ;

查询部门名为SAL或IT的员工信息（因为有的部门可能没有员工，用null填充）

#查询部门名为SAL或IT的员工信息
SELECT 
  e.*,
  `department_name` 
FROM
  `employees` e 
  LEFT JOIN `departments` d 
    ON e.`department_id` = d.`department_id` 
WHERE `department_name` IN ('SAL', 'IT') ;

9、子查询

子查询指出现在其他语句内部的select语句，称为子查询或内查询

内部嵌套其他select语句的select语句，称为外查询或主查询

示例：

SELECT 
  first_name 
FROM
  employees 
WHERE department_id IN 
  (SELECT 
    department_id 
  FROM
    departments 
  WHERE location_id = 1700)

该小节主要讲解子查询的外部语句是select查询语句的情况，即是两个查询语句

分类：

按子查询出现的位置
1. select后面：仅仅支持量子查询
2. from后面：支持表子查询
3. where、having后面：标量子查询（单行）、列子查询（多行）、行子查询
4. exists后面（相关子查询）：支持表子查询
按结果集的行列数不同
1. 标量子查询：结果集只有一行一列
2. 列子查询：结果集有一列多行
3. 行子查询：结果集有多行多列，但一般一行多列用的情况多
4. 表子查询：结果集一般多行多列，也可以是一行或一列，容纳上面几种情况

9.1、where、having后的子查询

标量子查询（单行子查询）
列子查询（多行子查询）
行子查询（多行多列）

特点：
① 子查询放在小括号内
② 子查询一般放在条件的右侧
③ 标量子查询，一般搭配着单行操作符使用> < >= <= = <>
④ 列子查询，一般搭配着多行操作符使用IN、ANY/SOME、ALL
⑤ 子查询的执行优先于主查询，主查询的执行条件用到了子查询的结果
⑥ 注意非法使用标量子查询的情况：使用了单行操作符，就要保证子查询的结果是一行一列的，保证是标量子查询

9.1.1、标量子查询

案例引入：
查询谁的工资比 Abel 高？

#1、先要查询出 Abel 的工资（子查询的结果需要是一行一列）
SELECT 
  `salary` 
FROM
  `employees` 
WHERE `last_name` = 'Abel' ;

#2、查询员工信息，满足 工资>①的结果
SELECT 
  * 
FROM
  `employees` 
WHERE `salary` > 
  (SELECT 
    `salary` 
  FROM
    `employees` 
  WHERE `last_name` = 'Abel') ;

案例2：
返回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为①，salary比②高的员工信息
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) ;

案例3：
返回公司工资最少的员工的last_name,job_id和salary

#1、查询最少的工资
SELECT 
  MIN(`salary`) 
FROM
  `employees` ;

#2、查询员工工资=①的员工信息
SELECT 
  `last_name`,
  `job_id`,
  `salary` 
FROM
  `employees` 
WHERE `salary` = 
  (SELECT 
    MIN(`salary`) 
  FROM
    `employees`) ;

案例4：
查询最低工资大于50号部门最低工资的部门id和其最低工资

#1、查询50号部门最低工资
SELECT 
  MIN(`salary`) 
FROM
  `employees` 
WHERE `department_id` = 50 ;

#2、查询部门最低工资比①高的部门id和其最低工资
SELECT 
  e.`department_id`,
  MIN(e.`salary`) 
FROM
  `employees` e 
GROUP BY e.`department_id` 
HAVING MIN(e.`salary`) > 
  (SELECT 
    MIN(m.`salary`) 
  FROM
    `employees` m 
  WHERE m.`department_id` = 50) ;

非法使用标量子查询的情况：

子查询的结果不是单行单列，但却用了单行操作符
在这里插入图片描述
子查询结果为空的情况：

子查询语句结果为空，则主查询最后的结果也为空
在这里插入图片描述

9.1.2、列子查询

使用多行比较操作符
在这里插入图片描述
案例1 ：
查询领导信息

SELECT 
  `last_name`,
  `salary` 
FROM
  `employees` 
WHERE `employee_id` IN 
  (SELECT 
    `manager_id` 
  FROM
    `employees` 
  WHERE `manager_id` IS NOT NULL) ;

案例2：
返回location_id是1400或1700的部门中的所有员工姓名

#使用列子查询
#1、location_id是1400或1700的部门
SELECT DISTINCT 
  `department_id` 
FROM
  `departments` 
WHERE `location_id` IN (1400, 1700) ;

#2、查询部门编号是①的员工姓名
SELECT 
  `last_name` 
FROM
  `employees` 
WHERE `department_id` IN 
  (SELECT DISTINCT 
    `department_id` 
  FROM
    `departments` 
  WHERE `location_id` IN (1400, 1700)) ;

#未使用列子查询
SELECT 
  `last_name`,
  `location_id` 
FROM
  `employees` e 
  INNER JOIN `departments` d 
    ON d.`department_id` = e.`department_id` 
WHERE `location_id` IN (1400, 1700) ;

案例3：
返回其它工种中比job_id为‘IT_PROG’工种任一工资都低的员工的员工号、姓名、job_id 以及salary

#返回其它工种中比job_id为‘IT_PROG’工种任一工资都低的员工的员工号、姓名、job_id 以及salary
SELECT 
  `employee_id`,
  `last_name`,
  `job_id`,
  `salary` 
FROM
  `employees` 
WHERE `salary` < ANY 
  (SELECT 
    `salary` 
  FROM
    `employees` 
  WHERE `job_id` = 'IT_PROG') 
  AND `job_id` <> 'IT_PROG' ;

或者不用列子查询，用最小值代替

#返回其它工种中比job_id为‘IT_PROG’工种任一工资都低的员工的员工号、姓名、job_id 以及salary
SELECT 
  `employee_id`,
  `last_name`,
  `job_id`,
  `salary` 
FROM
  `employees` 
WHERE `salary` < 
  (SELECT 
    MIN(`salary`) 
  FROM
    `employees` 
  WHERE `job_id` = 'IT_PROG') 
  AND `job_id` <> 'IT_PROG' ;

案例4：
返回其它工种中比job_id为‘IT_PROG’工种所有工资都低的员工的员工号、姓名、job_id 以及salary

#返回其它工种中比job_id为‘IT_PROG’工种全部工资都低的员工的员工号、姓名、job_id 以及salary
SELECT 
  `employee_id`,
  `last_name`,
  `job_id`,
  `salary` 
FROM
  `employees` 
WHERE `salary` < ALL 
  (SELECT 
    `salary` 
  FROM
    `employees` 
  WHERE `job_id` = 'IT_PROG') 
  AND `job_id` <> 'IT_PROG' ;

子查询结果为空的情况：

子查询语句结果为空，则主查询最后的结果也为空

9.1.3、行子查询

结果集一行多列或多行多列

案例：
查询员工编号最小并且工资最高的员工信息

不使用行子查询的情况：

#1、 查询最小的员工编号
SELECT 
  MIN(`employee_id`) 
FROM
  `employees` ;

#2、 查询最高的员工工资
SELECT 
  MAX(`salary`) 
FROM
  `employees` ;

#3、 查询员工信息，满足①和②
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`) ;

9.2、select后的

仅仅支持标量子查询

注意子查询结果只可以是单行单列

案例1： ★★★★
查询每个部门的员工个数，及部门的信息

#1、查询员工个数
SELECT COUNT(*) FROM `employees`;

#2、查询每个部门的①,及部门信息，注意要保证这个个数的统计是每个部门的个数统计
SELECT 
  d.*,
  (SELECT 
    COUNT(*) 
  FROM
    `employees` e 
  WHERE e.`department_id` = d.`department_id`) 个数 
FROM
  `departments` d ;

案例2：
查询员工号=102的部门名

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

9.3、from后的

支持表子查询

因为 from 后接的是一个表
将子查询的结果充当一张表，要求必须起别名

案例：
查询每个部门的平均工资的工资等级

#1、查询每个部门的平均工资
SELECT 
  AVG(`salary`),
  `department_id` 
FROM
  `employees` 
GROUP BY `department_id` ;

#2、连接①的结果集和`job_grades`表，筛选条件为BETWEEN `lowest_sal` AND `highest_sal`
SELECT 
  g.`grade_level`,
  e.ag,
  e.`department_id` 
FROM
  (SELECT 
    AVG(`salary`) ag,    #后面要用到这个数据，可以起别名替代
    `department_id` 
  FROM
    `employees` 
  GROUP BY `department_id`) e 
  LEFT JOIN `job_grades` g 
    ON e.ag BETWEEN g.`lowest_sal` 
    AND g.`highest_sal` ;

9.4、exists后的

exists(完整的查询语句)
结果是 1 或 0，表示括号内的是否存在

能用 exists 写出来的题目，都可以用列子查询的 in 来实现

一般先执行主查询，然后结果拿到子查询去验证

示例：

SELECT 
  EXISTS 
  (SELECT 
    `employee_id` 
  FROM
    `employees`) ;

案例1： ★★★★
查询有员工的部门名

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

案例2：
查询没有女朋友的男神信息

用 EXISTS

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

用IN

#查询没有女朋友的男神信息
SELECT 
  bo.* 
FROM
  `boys` bo 
WHERE bo.`id` NOT IN 
  (SELECT 
    b.`boyfriend_id` 
  FROM
    `beauty` b) ;

9.5、练习

查询和 Zlotkey 相同部门的员工姓名和工资

#查询和 Zlotkey 相同部门的员工姓名和工资
SELECT 
  `last_name`,
  `salary` 
FROM
  `employees` 
WHERE `department_id` = 
  (SELECT 
    `department_id` 
  FROM
    `employees` 
  WHERE `last_name` = 'Zlotkey') ;

查询工资比公司平均工资高的员工的员工号，姓名和工资

#查询工资比公司平均工资高的员工的员工号，姓名和工资
SELECT 
  `employee_id`,
  `last_name`,
  `salary` 
FROM
  `employees` 
WHERE `salary` > 
  (SELECT 
    AVG(`salary`) 
  FROM
    `employees`) ;

查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资★★★★

#查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT 
  `employee_id`,
  `last_name`,
  `salary` 
FROM
  `employees` e 
  INNER JOIN 
    (SELECT 
      AVG(`salary`) av,
      `department_id` 
    FROM
      `employees` 
    GROUP BY `department_id`) f 
    ON e.`department_id` = f.`department_id` 
WHERE e.`salary` > f.av ;

查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名

#查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名
SELECT 
  `employee_id`,
  `last_name` 
FROM
  `employees` 
WHERE `department_id` IN 
  (SELECT DISTINCT 
    `department_id` 
  FROM
    `employees` 
  WHERE `last_name` LIKE '%u%') ;

查询在部门的 location_id 为 1700 的部门工作的员工的员工号

#查询在部门的 location_id 为 1700 的部门工作的员工的员工号
SELECT 
  `employee_id` 
FROM
  `employees` e 
  JOIN `departments` d 
    ON e.`department_id` = d.`department_id` 
WHERE d.`location_id` = 1700 ;

查询管理者是 King 的员工姓名和工资

#查询管理者是 King 的员工姓名和工资
SELECT 
  `last_name`,
  `salary` 
FROM
  `employees` 
WHERE `manager_id` IN 
  (SELECT 
    `employee_id` 
  FROM
    `employees` 
  WHERE `last_name` = 'K_ing') ;

查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列，列名为姓.名

#查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓.名
SELECT 
  CONCAT(`last_name`,'.', `first_name`) 
FROM
  `employees` 
WHERE `salary` = 
  (SELECT 
    MAX(`salary`) 
  FROM
    `employees`) ;

9.6、子查询经典案例

查询工资最低的员工信息: last_name, salary

SELECT 
  `last_name`,
  `salary` 
FROM
  `employees` 
WHERE `salary` = 
  (SELECT 
    MIN(`salary`) 
  FROM
    `employees`) ;

在这里插入图片描述

查询平均工资最低的部门信息

方式一：用排序，再LIMIT

#方法一：
SELECT 
  d.* 
FROM
  `departments` d 
WHERE d.`department_id` = 
  (SELECT 
    `department_id` id 
  FROM
    `employees` e 
  GROUP BY `department_id` 
  ORDER BY AVG(`salary`) ASC 
  LIMIT 1) ;

方式二：用子查询

#方法二：
#查询部门平均工资
SELECT 
  AVG(`salary`) ag1,
  e1.`department_id` id1 
FROM
  `employees` e1 
GROUP BY e1.`department_id` ;

#查询 ① 中最低的工资
SELECT 
  MIN(ag1) mn2 
FROM
  (SELECT 
    AVG(`salary`) ag1,
    e1.`department_id` id1 
  FROM
    `employees` e1 
  GROUP BY e1.`department_id`) f2 ;

#查询部门平均工资等于 ② 的部门id
SELECT 
  `department_id` id3 
FROM
  `employees` 
GROUP BY `department_id` 
HAVING AVG(`salary`) = 
  (SELECT 
    MIN(ag1) mn2 
  FROM
    (SELECT 
      AVG(`salary`) ag1,
      e1.`department_id` id1 
    FROM
      `employees` e1 
    GROUP BY e1.`department_id`) f2) ;

#查询部门id等于 ③ 的部门信息
SELECT 
  d4.* 
FROM
  `departments` d4 
WHERE d4.`department_id` = 
  (SELECT 
    `department_id` id3 
  FROM
    `employees` 
  GROUP BY `department_id` 
  HAVING AVG(`salary`) = 
    (SELECT 
      MIN(ag1) mn2 
    FROM
      (SELECT 
        AVG(`salary`) ag1,
        e1.`department_id` id1 
      FROM
        `employees` e1 
      GROUP BY e1.`department_id`) f2)) ;

在这里插入图片描述

查询平均工资最低的部门信息和该部门的平均工资

#查询平均工资最低的部门信息和该部门的平均工资
SELECT 
  d4.*,
  f3.ag3 
FROM
  `departments` d4 
  JOIN 
    (SELECT 
      `department_id` id3,
      AVG(`salary`) ag3 
    FROM
      `employees` 
    GROUP BY `department_id` 
    HAVING AVG(`salary`) = 
      (SELECT 
        MIN(ag1) mn2 
      FROM
        (SELECT 
          AVG(`salary`) ag1,
          e1.`department_id` id1 
        FROM
          `employees` e1 
        GROUP BY e1.`department_id`) f2)) f3 
    ON f3.id3 = d4.`department_id` ;

在这里插入图片描述

查询平均工资最高的 job 信息

#查询平均工资最高的 job 信息
SELECT 
  j4.* 
FROM
  `jobs` j4 
WHERE j4.`job_id` = 
  (SELECT 
    `job_id` id3 
  FROM
    `employees` 
  GROUP BY `job_id` 
  HAVING AVG(`salary`) = 
    (SELECT 
      MAX(ag1) mx2 
    FROM
      (SELECT 
        AVG(`salary`) ag1,
        e1.`job_id` id1 
      FROM
        `employees` e1 
      GROUP BY e1.`job_id`) f2)) ;

在这里插入图片描述

查询平均工资高于公司平均工资的部门有哪些?

#查询公司平均工资
SELECT 
  AVG(`salary`) 
FROM
  `employees` ;

#部门平均工资高于 ① 
SELECT 
  AVG(`salary`),
  `department_id` 
FROM
  `employees` 
GROUP BY `department_id` 
HAVING AVG(`salary`) > 
  (SELECT 
    AVG(`salary`) 
  FROM
    `employees`) ;

在这里插入图片描述

查询出公司中所有 manager 的详细信息

#查询出公司中所有 manager 的详细信息
SELECT 
  * 
FROM
  `employees` 
WHERE `employee_id` IN 
  (SELECT 
    `manager_id` 
  FROM
    `employees`) ;

各个部门中最高工资中最低的那个部门的最低工资是多少

#各个部门中 最高工资中最低的那个部门的 最低工资是多少
SELECT 
  d4.*,
  f3.mx3 
FROM
  `departments` d4 
  JOIN 
    (SELECT 
      `department_id` id3,
      MAX(`salary`) mx3 
    FROM
      `employees` 
    GROUP BY `department_id` 
    HAVING MAX(`salary`) = 
      (SELECT 
        MIN(mx1) mn2 
      FROM
        (SELECT 
          MAX(`salary`) mx1,
          e1.`department_id` id1 
        FROM
          `employees` e1 
        GROUP BY e1.`department_id`) f2)) f3 
    ON f3.id3 = d4.`department_id` ;

查询平均工资最高的部门的 manager 的详细息:last_name,department_id, email, salary

以下代码用的是排序 + LIMIT

#查询平均工资最高的部门的 manager 的详细信息:last_name,department_id, email, salary
SELECT 
  `last_name`,
  d.`department_id`,
  `email`,
  `salary` 
FROM
  `employees` e 
  JOIN `departments` d 
    ON d.`manager_id` = e.`employee_id` 
WHERE d.`department_id` = 
  (SELECT 
    `department_id` id 
  FROM
    `employees` e 
  GROUP BY `department_id` 
  ORDER BY AVG(`salary`) DESC 
  LIMIT 1) ;

在这里插入图片描述

10、分页查询

应用场景:
当要显示的数据，一页显示不全，需要分页提交 sql 请求

特点：
① LIMIT 语句放在查询语句最后
② offset 起始位置是从第一条开始的时候，offset 可省略
③ 公式：(page-1)*size 显示第page页的size个条目
要显示的页数page，要显示的个数size

语法：

SELECT 
  查询列表
FROM
  表1 别名 
  【连接类型 JOIN 表2 别名 
    ON 连接条件2
    WHERE 筛选条件  
    GROUP BY 分组的列
    HAVING 筛选条件     
    ORDER BY 排序列表 ASC/DESC】
  LIMIT offset,size ;

offset：要显示条目的起始索引（起始索引从 0 开始），如果是从第一条开始的就可以省略
size：要显示的条目个数

案例1：
查询前五条员工信息

#查询前五条员工信息
SELECT 
  * 
FROM
  `employees` 
LIMIT 5 ;

案例2：
查询第11条-25条

#查询前五条员工信息
SELECT 
  * 
FROM
  `employees` 
LIMIT 10, 15 ;

案例3：
查询有奖金的员工信息，且工资较高的前 10 名显示出来

SELECT 
  * 
FROM
  `employees` 
WHERE `commission_pct` IS NOT NULL 
ORDER BY `salary` DESC 
LIMIT 10 ;

11、联合查询

应用场景：
要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时

注意：

要求查询语句的查询列数一致
查询的结果显示的字段名默认是第一条查询语句的查询列表名
要求多条查询语句的查询列表的类型、顺序保持一致
UNION 关键字查询的结果是默认去重的，想要不去重的结果，用 UNION ALL

语法：

查询语句1
UNION
查询语句2
UNION
查询语句3
...

引入案例：
查询部门编号>90 或邮箱包含 a 的员工信息

不使用联合查询
SELECT 
  * 
FROM
  `employees` 
WHERE `department_id` > 90 
  OR `email` LIKE '%a%' ;

使用联合查询
SELECT 
  * 
FROM
  `employees` 
WHERE `department_id` > 90 
UNION
SELECT 
  * 
FROM
  `employees` 
WHERE `email` LIKE '%a%' ;

案例：
查询用户表里编号2的姓名及编号，以及女神表里面编号2的女神名及编号

SELECT 
  `id`,
  `username` 
FROM
  `admin` 
WHERE `id` = 2 
UNION
SELECT 
  `id`,
  `name` 
FROM
  `beauty` 
WHERE `id` = 2 ;

显示结果为
（显示的表的字段为第一个查询语句的查询字段）
在这里插入图片描述
若将女神表的两个字段交换位置
（所以要求多条查询语句的查询列表的类型、顺序保持一致）

SELECT 
  `id`,
  `username` 
FROM
  `admin` 
WHERE `id` = 2 
UNION
SELECT 
  `name` ,
  `id`
FROM
  `beauty` 
WHERE `id` = 2 ;

在这里插入图片描述

磊哥的小迷妹

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础2

MySQL基础11、数据库相关概念2、MySQL常见命令1、数据库相关概念DB ：DataBase ，数据库，保存一系列有组织的数据的容器，“仓库”DBMS ：DataBaseManagementSystem，数据库管理系统，又称为数据库软件（产品），SQL ：结构化查询语言，专门用来与数据库通信的语言数据库存储数据的特点：1、数据放进表中，表放进数据库中2、一个数据库可以有多个表，表名互不同，表明具有唯一性3、表具有一定特性，定义了数据如何在表中存储，类似于Java中的“类”4、表中的列
复制链接

扫一扫