MySQL(1) DQL

MySQL 名字的由来：SQL 为结构化语言，My 来自于 创始人 Monty 的大女儿的名字

DBMS 分为两类

• 基于共享文件系统的 DBMS
• 基于 客户端—服务器的 DBMS，即 C/S 架构 (MySQL，Oracle，SqlServer)。C：负责完成与用户的交互任务，S：负责数据的管理

需要准备的两个数据库

/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.7.18-log : Database - girls
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`girls` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `girls`;

/*Table structure for table `admin` */

DROP TABLE IF EXISTS `admin`;

CREATE TABLE `admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `password` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*Data for the table `admin` */

insert  into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666');

/*Table structure for table `beauty` */

DROP TABLE IF EXISTS `beauty`;

CREATE TABLE `beauty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `sex` char(1) DEFAULT '女',
  `borndate` datetime DEFAULT '1987-01-01 00:00:00',
  `phone` varchar(11) NOT NULL,
  `photo` blob,
  `boyfriend_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

/*Data for the table `beauty` */

insert  into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) values (1,'柳岩','女','1988-02-03 00:00:00','18209876577',NULL,8),(2,'苍老师','女','1987-12-30 00:00:00','18219876577',NULL,9),(3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),(4,'热巴','女','1993-02-03 00:00:00','18209876579',NULL,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),(6,'周芷若','女','1988-02-03 00:00:00','18209876577',NULL,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),(8,'小昭','女','1989-02-03 00:00:00','18209876567',NULL,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),(10,'王语嫣','女','1992-02-03 00:00:00','18209179577',NULL,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),(12,'赵敏','女','1992-02-03 00:00:00','18209179577',NULL,1);

/*Table structure for table `boys` */

DROP TABLE IF EXISTS `boys`;

CREATE TABLE `boys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `boyName` varchar(20) DEFAULT NULL,
  `userCP` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `boys` */

insert  into `boys`(`id`,`boyName`,`userCP`) values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.7.18-log : Database - girls
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`girls` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `girls`;

/*Table structure for table `admin` */

DROP TABLE IF EXISTS `admin`;

CREATE TABLE `admin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `password` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*Data for the table `admin` */

insert  into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666');

/*Table structure for table `beauty` */

DROP TABLE IF EXISTS `beauty`;

CREATE TABLE `beauty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `sex` char(1) DEFAULT '女',
  `borndate` datetime DEFAULT '1987-01-01 00:00:00',
  `phone` varchar(11) NOT NULL,
  `photo` blob,
  `boyfriend_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

/*Data for the table `beauty` */

insert  into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) values (1,'柳岩','女','1988-02-03 00:00:00','18209876577',NULL,8),(2,'苍老师','女','1987-12-30 00:00:00','18219876577',NULL,9),(3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),(4,'热巴','女','1993-02-03 00:00:00','18209876579',NULL,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),(6,'周芷若','女','1988-02-03 00:00:00','18209876577',NULL,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),(8,'小昭','女','1989-02-03 00:00:00','18209876567',NULL,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),(10,'王语嫣','女','1992-02-03 00:00:00','18209179577',NULL,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),(12,'赵敏','女','1992-02-03 00:00:00','18209179577',NULL,1);

/*Table structure for table `boys` */

DROP TABLE IF EXISTS `boys`;

CREATE TABLE `boys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `boyName` varchar(20) DEFAULT NULL,
  `userCP` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `boys` */

insert  into `boys`(`id`,`boyName`,`userCP`) values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

基础查询

语法：select 查询列表 from 表名;

        select 字段1,字段2,字段3 form 表名;

查询单个字段

select last_name from employees;

查询多个字段

select last_name, first_name from employees;

查询全部字段 ( 但是使用 * 来查询没有写出全部字段来查询效率高，并且写出字段语义性强，还可以控制查询字段 )

select * from employees;

调用函数并获取返回值

select database();  #获取当前数据库名
select version(); #获取当前数据库版本
select user(); #获取当前数据库用户

查看表结构

desc 表名;
show columns from 表名;

起别名

相当于将查询出来的结果集的列名更改为别名

• 使用 as```java select user() as 用户名;

  ```mysql
  select last_name as ln, first_name as fn from employees;
  

• 使用空格```mysql select user() 用户名;

  ```mysql
  select last_name ln, first_name fn from employees;
  

MySQL 中加法的作用只有加法运算

• 如果两个操作数都是数值型：相加出结果
• 如果其中一个操作数为字符型：将字符型数据强制转换为数值型，如果无法转换，则转换为 0 后再相加出结果
• 其中一个操作数为 null ( 或者两个操作数都为 null )：结果为 null

需求：查询 first_name 和 last_name 拼接而成的全名，最终起别名为姓 名

使用 concat 拼接函数

select concat(first_name, last_name) as "姓 名"
from employees;

distinct 的使用

需求：查询员工表涉及到的部门编号有哪些 ( 即统计有哪些部门编号在 employees 这张表中出现，因此需要去重 )

select distinct department_id from employees;

练习

显示出 employees 的全部列，各个列之间用逗号连接，列名显示为 OUT_PUT

#以其中几个字段为例
select 
concat(`employee_id`, ',', 
    `first_name`, ',' ,
    `last_name`, ',' ,
    `commission_pct`)
as OUT_PUT
from employees;

但是发现结果有的结果为 null，有的不为 null

这是因为字段 commission_pct 可能为 null，null 与任何字符串拼接都为 null

这里我们可以使用 IFNULL() 函数来优化

#第一个参数是可能为 null 的字段，第二个参数是如果某记录中的该字段的值为 null 是在结果集中的显示
select IFNULL(commission_pct, "空") from employees;

因此需求的 SQL 可以优化成如下形式

select 
concat(`employee_id`, ',', 
    `first_name`, ',' ,
    `last_name`, ',' ,
    ifnull(commission_pct, ''))
as OUT_PUT
from employees;

可以看见就没有 null 数据了

条件查询

语法：select 查询列表 from 表名 where 条件;

执行顺序：from 子句 -> where 子句 -> select 子句

筛选方式

1. 按关系运算符筛选：>、<、>=、<=、=、<> ( != )
2. 按逻辑运算符筛选：and ( && )、or ( || )、not ( ! )

#查询部门编号不在 50-100 之间的员工的姓名，部门编号，邮箱
SELECT CONCAT(last_name, first_name) AS "姓名",
       department_id, email
FROM employees
WHERE department_id < 50 OR department_id > 100;
#或者
SELECT CONCAT(last_name, first_name) AS "姓名",
       department_id, email
FROM employees
WHERE NOT(department_id >= 50 AND department_id <= 100);

#奖金率 > 0.03 或者 员工编号在 60-110 之间的员工信息
select *
from employees
where commission_pct > 0.03 
or 
      (employee_id >=60 and employee_id <= 110);

模糊查询

like

一般和通配符搭配使用，对字符型数据进行部分匹配查询

常见的通配符

• _：任意单个字符
• %：任意多个字符 ( 0-n )

#查询姓名中包含字符 a 的员工信息
SELECT *
FROM employees
WHERE CONCAT(last_name, first_name)
LIKE "%a%";

#查询 last_name 中第三个字符为 x 的员工信息
SELECT *
FROM employees
WHERE last_name
LIKE "__x%";  #两个_后再接 x 就是第三个字符了

in

用于查询某一字段的值是否属于指定的列表之内

in(值1, 值2, 值3, …)

not in(值1, 值2, 值3, …)

#查询部门编号是 30,50,40 的员工名和部门编号
SELECT CONCAT(first_name, last_name),
       department_id
FROM
        employees
WHERE department_id IN(30,50,40);

#查询 job_id 不是 ST_CLERK 或 IT_PROG 的员工的姓名和 job_id
SELECT CONCAT(first_name, last_name),
       job_id
FROM
    employees
WHERE job_id NOT IN('ST_CLERK', 'IT_PROG');

between and

判断某个字段的值是否在某一区间内

between and

not between and

#查询部门编号是 30 - 90 之间的员工姓名和部门编号
SELECT CONCAT(last_name, first_name), department_id
FROM employees
WHERE department_id BETWEEN 30 AND 90;

is null / is not null

#查询奖金率为 null 的员工信息
select *
from employees
where commission_pct is null;

= 和 is null 的区别： = 只能判断普通类型的数值；is null 只能判断是否为 null 值

<=>：安全等于，既能判断普通数值又能判断 null，缺点是语义性有些差

查询练习

#查询工资大于 12000 的员工姓名和工资
SELECT CONCAT(first_name, last_name), salary
FROM employees
WHERE salary > 12000;

#查询员工号为 176 的员工的姓名、部门号、年薪
#commission_pct 可能为 null，如果为 null 就替换为 0
select concat(first_name, last_name), department_id, salary*12*(1+IFNULL(commission_pct, 0)) as '年薪'
from employees
where employee_id = 176;

#查询工资不在 5000 到 12000 的员工的姓名和工资
SELECT CONCAT(first_name, last_name), salary
FROM employees
WHERE salary BETWEEN 5000 AND 12000;

#查询在 20 或 59 号部门工作的员工姓名和部门号
SELECT CONCAT(first_name, last_name), department_id
FROM employees
WHERE department_id IN (20, 59);

#查询在公司中没有管理者的员工姓名及 job_id
select concat(first_name, last_name), job_id
from employees
where manager_id is null;

#查询公司中有奖金的员工姓名，工资和奖金率
SELECT CONCAT(first_name, last_name)AS '姓名', salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

#查询员工姓名的第三个字母是 a 的员工
select concat(first_name, last_name)as '姓名'
from employees
where CONCAT(first_name, last_name) like "__a%";

#查询表 employees 中 first_name 以 e 结尾的员工信息
select first_name
from employees
where first_name like "%e";

#查询表 employees 中部门编号在 80-100 之间的员工姓名和职位
select concat(first_name, last_name)as '姓名', job_id
from employees
where department_id between 80 and 100;

#查询表 employees 的 manager_id 是 100,101,110 的员工姓名和职位
SELECT CONCAT(first_name, last_name)AS '姓名', job_id
FROM employees
WHERE manager_id IN (100, 101, 110);

#查询姓名中有 a 和 e 员工姓名
SELECT CONCAT(first_name, last_name)AS '姓名'
FROM employees
WHERE CONCAT(first_name, last_name) LIKE '%a%e%' OR '%e%a%';

排序查询

语法：

select 查询列表

from 表名

[where 筛选条件]

order by 排序列表;

执行顺序：from -> where -> select -> order by

order by 的特点

• 排序列表可以是单个字段，也可以是多个字段，表达式，函数，常量 (代表列的下标)，别名，或前面的组合
• 升序通过 asc 声明，降序通过 desc 声明。默认排序方式为升序

#按 salary 单个字段进行升序查询
SELECT * FROM employees
ORDER BY salary;
#按 salary 单个字段进行降序查询
SELECT * FROM employees
ORDER BY salary DESC;

#对有奖金的员工，按照年薪降序排序 
SELECT *, salary*12*(1+commission_pct) AS '年薪'
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFNULL(commission_pct, 0)) DESC;

#或者按照别名排序
select *, salary*12*(1+commission_pct) as 年薪
from employees
where commission_pct is not null
order by 年薪 desc;

#按函数返回值排序
#根据姓名的字母长度进行升序排序
select length(concat(first_name, last_name)) as 长度, CONCAT(first_name, last_name) as 姓名
from employees
order by 长度;

#按多个字段排序
#查询员工的姓名，工资，部门编号，先按工资升序，再按部门编号降序
#每个排序条件间用逗号隔开，越后面的条件优先级越低，优先级低的条件在优先级高的条件的排序基础上再进行排序
SELECT last_name, salary, department_id
FROM employees
ORDER BY salary, department_id DESC;

#按列数进行排序，和按照对应列数的列名进行排序的效果一样
SELECT * FROM employees
ORDER BY 2; #等同于 order by first_name

练习

#查询员工的姓名和部门号和年薪，按年薪排序，姓名升序
SELECT last_name, department_id, salary*12*(1+IFNULL(commission_pct, 0)) AS 年薪
FROM employees
ORDER BY 年薪, last_name;

#选择工资不再 8000-17000 的员工的姓名和工资，按工资降序
SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

#查询邮箱中包含 e 的员工信息，并优先按邮箱长度降序，再按部门号升序
SELECT *
FROM employees
WHERE email LIKE "%e%"
ORDER BY LENGTH(email) DESC, department_id;

常见函数

字符函数

用于处理字符型数据的函数

CONCAT

拼接字符串

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

LENGTH

获取字节长度 (与字符集有关)

select LENGTH("我是？") 长度;

CHAR_LENGTH

获取字符长度 (与字符集无关)

select char_length("我是？") 长度;

SUBSTRING

截取子串

select substring('2333,34567', 1, 4);

注意，MySQL 中的 SubString 的索引是从 1 开始的，第三个参数代表截取长度，而不是终止索引，当第三个参数省略时，将截取从索引起始位开始到主串结束，以作为子串

INSTR

获取字符第一次出现的索引

SELECT INSTR('2345342', '34');

TRIM

去掉前后的指定字符，默认去掉空格

SELECT TRIM(" 成  昆  ") AS a;

select trim("x" from "xxxxxxx成xxx昆xxxxx") as a;

LPAD / RPAD

左填充 / 右填充

SELECT LPAD("wweww", 10, 'a') AS a;

使用 a 去左填充 wweww 这个字符串以达到 10 个字符

SELECT RPAD("wweww", 10, 'a') AS a;

当该函数的参数2已经小于指定要填充的字符串时，会从指定字符串上截取下参数2大小的子串

SELECT RPAD("wweww", 3, 'a') AS a;

UPPER / LOWER

大小写变换

select LOWER("dwdweWEEFE");

练习

查询员工姓名，要求格式：姓首字母大写，其它字符小写，名所有字符大写，且姓和名之间用 _ 分割，最后起别名为 output

select concat(upper(substring(first_name, 1, 1)), 
              lower(substring(first_name, 2)),
              "_",
              upper(last_name)) as output
from employees;

STRCMP

比较两个字符大小，返回 1 表示前面的大，-1 表示后面的大

SELECT STRCMP("asd", "axv");

LEFT / RIGHT

截取子串

SELECT LEFT("zsj", 1);   #从左边开始截取 1 个
select right("zsj", 2);  #从右边开始截取 2 个

数学函数

ABS

绝对值

SELECT ABS(-10);

CEIL

向上取整

SELECT CEIL(1.08);

FLOOR

向下取整

SELECT FLOOR(1.08);

ROUND

四舍五入，当存在第二个参数时，代表保留位数，否则默认不保留小数位

SELECT ROUND(1.256565);    #1
SELECT ROUND(1.256565, 2); #1.26

TRUNCATE

截断，参数 2 代表截断小数点后几位，不足则以 0 补充

SELECT TRUNCATE(1.45, 0); #1
SELECT TRUNCATE(1.45, 1); #1.4
SELECT TRUNCATE(1.45, 2); #1.45
SELECT TRUNCATE(1.45, 3); #1.450

MOD

取余，参数1 % 参数2

SELECT MOD(-10, 3);

对取余而言，被除数的正负决定结果的正负

日期函数

NOW

获取当前时间

SELECT NOW();  #2020-09-03 18:54:10

CURDATE

获取当前日期

SELECT CURDATE(); #2020-09-03

CURTIME

获取当前时间

SELECT CURTIME();  #18:54:10

DATEDIFF

获取两个日期之差

SELECT DATEDIFF("2019-9-10", "2020-10-17"); #-403，代表相差天数

DATE_FORMAT

将日期转化为指定格式的字符串，参数1为日期，参数2为格式

SELECT DATE_FORMAT(NOW(), "%Y年%c月%日 %H时%i分%s秒");

STR_TO_DATE

按指定格式将字符串转为日期，按照参数2的格式去解析参数1

SELECT STR_TO_DATE("3/23 2019", "%m/%d %Y");

格式化字符解析对应位置的数字为对应格式

流程控制函数

IF

类似三目运算符

select if(100>60, "good", "bad");

CASE

结构1：类似于 switch 语句，用于等值判断

CASE 表达式                     #switch
WHEN 值1 THEN 结果1             #case 值
WHEN 值2 THEN 结果2
...
ELSE 结果                       #default
END                            # 结束符

#部门编号为 30 的人工资翻2倍，50翻3倍，60翻4倍
SELECT department_id, salary,
    CASE department_id
    WHEN 30 THEN salary*2
    WHEN 50 THEN salary*3
    WHEN 60 THEN salary*4
    ELSE salary
    END AS 计算
FROM employees;

结构2：类似于多重 if，可以用于区间判断

CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
...
ELSE 结果n
END

#如果工资大于 2w，显示 A；大于1w5，显示 B，大于1w，显示 C；否则显示D
SELECT department_id, salary, 
    CASE
    WHEN salary > 20000 THEN 'A'
    WHEN salary <= 20000 AND salary > 15000 THEN 'B'
    WHEN salary <=15000 AND salary > 10000 THEN 'C'
    ELSE 'D'
    END AS 计算
FROM employees;

练习

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

SELECT last_name, 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;

分组(聚合)函数

分组函数往往用于实现将一组数据进行统计计算，最终得到一个值

SUM

求和函数

SELECT SUM(salary) FROM employees;

AVG

求平均值

SELECT AVG(salary) FROM employees;

MAX

求最大值

SELECT MAX(salary) FROM employees;

MIN

求最小值

SELECT MIN(salary) FROM employees;

CONUT

统计指定字段在结果集中不为空的记录的总条数

SELECT COUNT(commission_pct) FROM employees;

COUNT 的其它写法

• count() ：统计结果集中的总行数```mysql SELECT COUNT() FROM employees WHERE salary > 10000 AND commission_pct IS NOT NULL; ```
  
• count(常量值)：和 count(*) 效果一样

和 distinct 搭配，实现去重统计

#有员工的部门个数 (员工表中有哪些部门出现过)
#先去重，再统计
SELECT COUNT(DISTINCT department_id) FROM employees;

分组查询

分组查询旨在将一个大表拆分为若干个小表，再通过这些小表来得出的想要结果。分组主要通过 GROUP BY 子句实现，就是将拥有相同值的记录归为一组，然后再对每组进行操作 ( 一般使用聚合函数进行操作 )

select sum(salary), department_id
from employees
group by department_id;

语法：

select 分组函数和被分组字段

from 表名

[where 筛选条件]

group by 分组字段1, 分组字段2 …;

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

#每个领导的手下人数
SELECT COUNT(*), manager_id
FROM employees
GROUP BY manager_id;

#每个部门中邮箱包含 a 的员工中最高的工资那个员工 (group by)
select MAX(salary), department_id
from employees
where email like '%a%'
group by department_id;

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

#查询每个姓氏中有 e 并且没有奖金的人的平均工资
SELECT AVG(salary), last_name
FROM employees
WHERE last_name LIKE "%e%" AND commission_pct <=> NULL
GROUP BY last_name;

HAVING 子句

#查询哪个部门的员工个数大于 5
#第一步：查出每个部门的员工个数
#第二步：筛选
#因此这时的筛选条件应该在分组后进行，在 group by 后面筛选不能使用 where，而是使用 having
SELECT COUNT(*) 员工个数,department_id 
FROM employees
GROUP BY department_id 
HAVING COUNT(*) > 5;

PS：分组前筛选使用 WHERE，分组后筛选使用 HAVING

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

#领导编号 > 102 的每个领导手下员工的最低工资大于5000的领导编号和最低工资
SELECT MIN(salary), manager_id
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;

还有一种特殊的分组语句 group by N (N为数字，最小为1)，这种分组表示按照 select 后的第 N 个字段进行分组

ORDER BY 子句

from -> where -> 分组 -> having -> select -> order by

#查询每个工种没有奖金的员工的最高工资 > 6000 的工种编号和最高工资，按最高工资升序
SELECT MAX(salary) 最高工资, job_id 
FROM employees
WHERE commission_pct IS NULL
GROUP BY job_id
HAVING MAX(salary) > 6000
ORDER BY 最高工资;

按多个字段分组，这种情况下，多个字段值都相同的记录为一组

#查询每个部门中每个工种的最低工资，并按最低工资降序
SELECT MIN(salary), job_id, department_id
FROM employees 
GROUP BY job_id, department_id
ORDER BY MIN(salary) DESC;

到目前为止的执行顺序

from -> where -> 分组 -> having -> select -> order by

连接查询 *

(完全)笛卡尔乘积：假设表 1 有 m 行，表 2 有 n 行，查询结果出现 m*n 行。笛卡尔乘积的发生是由于没有有效条件导致的多表间的完全连接，因此添加有效条件可以有效避免笛卡尔乘积

假设有如下两张表

前一张表名为 beauty，后一张表名为 boys

假设想要查询出每个 beauty 对应的 boyFriend，如果直接进行查询而不添加有效条件的话就会导致笛卡尔乘积

SELECT NAME, boyName 
FROM beauty, boys

可以看见结果为 12*4 = 48 行数据，结果集并不合理，因此需要添加有效条件

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

连接查询的分类

• 按照年代分类
  • SQL 92 标准：MySQL 只支持内连接
  • SQL 99 标准：MySQL 支持内连接，外连接 (左外和右外)，交叉连接
• 按照功能分类
  • 内连接
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接
  • 交叉连接

SQL92 标准

等值连接

等值连接：在 where 条件中使用 = 比较被连接表中指定列的值，如果做连接的两张表中，用于比较的字段值相等，则将这两条记录合并作为结果集的一条记录············

多表等值连接具有以下特点

• 多表等值连接的结果为连接条件相等的多表的交集部分
• n 表连接至少需要 n-1 个连接条件
• 可以配合前面的所有子句使用

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

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

#查询员工名，工种号，工种名
select last_name, jobs.job_id, job_title
from employees, jobs
where employees.`job_id` = jobs.`job_id`;

添加筛选条件

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

#查询城市名中第二个字符为 o 的城市中有哪些部门，并显示城市名
SELECT  d.`department_name`, l.`city`
FROM departments AS d, locations AS l
WHERE d.`location_id` = l.`location_id`
AND l.`city` LIKE '_o%';

添加分组

#查询每个城市的部门个数
SELECT COUNT(*) 部门个数, l.`city`
FROM departments AS d, locations AS l
WHERE d.`location_id` = l.`location_id`
GROUP BY l.`city`;

#查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
#有奖金的每个部门的意思就是这个部门下所有员工的奖金率都不为 null
select d.`department_name`, d.`manager_id`, min(salary)
from employees as e, departments as d
where d.`department_id` = e.`department_id`
and e.`commission_pct` is not null
group by `department_name`, `manager_id`;

添加排序

#每个工种的工种名和员工个数，按员工个数降序
SELECT j.`job_id`, COUNT(*) 员工个数
FROM employees AS e, jobs AS j
WHERE e.`job_id` = j.`job_id`
GROUP BY j.`job_id`
ORDER BY 员工个数 DESC;

三表连接

#查询员工的名字及其所在部门名和所在的城市名
SELECT last_name, d.department_id, city
FROM employees AS e, departments AS d, locations AS l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;

非等值连接

就是把连接条件从 = 换成了不是等于号的其他符号

新建一张工资等级表

USE  myemployees;
DROP TABLE IF EXISTS sal_grade;
CREATE TABLE sal_grade (
    id INT PRIMARY KEY AUTO_INCREMENT,
    min_salary DOUBLE ,
    max_salary DOUBLE,
    grade CHAR

);

INSERT INTO sal_grade VALUES(NULL,2000,3999,'A');
INSERT INTO sal_grade VALUES(NULL,4000,5999,'B');
INSERT INTO sal_grade VALUES(NULL,6000,9999,'C');
INSERT INTO sal_grade VALUES(NULL,10000,12999,'D');
INSERT INTO sal_grade VALUES(NULL,13000,14999,'E');
INSERT INTO sal_grade VALUES(NULL,15000,99999,'F');

#查询员工的工资和其工资等级
select s.`grade`, e.`salary`, e.`last_name`
from sal_grade s, employees e
where e.`salary` between s.`min_salary` and s.`max_salary`;

自连接

自连接和等值连接类似，但是涉及的多张表都是自己

#查询员工和其上级的名称
select e1.`last_name`, e2.`last_name` 领导名
from employees e1, employees e2
where e1.`manager_id` = e2.`employee_id`;

练习

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

#查询员工表的 employee_id, job_id, last_name，按 department_id 降序，salary 升序
SELECT e.`employee_id`, e.`job_id`, e.`last_name`
FROM employees e
ORDER BY e.`department_id` DESC, salary;

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

复习

排序查询

语法

select 查询列表
from 表
where 筛选条件
order by 排序列表 [asc | desc];

排序列表支持单个字段，多个字段，函数，表达式，别名等

order by 一般放在查询语句的最后 (除 limit 语句外)

字符函数

concat：拼接字符串

substr：截取子串

upper：变大写

lower：变小写

replace：替换

length：获取字节长度

char_length：获取字符长度

lpad：左填充

rpad：右填充

instr：子串第一次出现的索引

数学函数

ceil：向上取整

round：四射五日

mod：取模

floor：向下取整

truncate：截断

rand：获取随机数，返回 0-1 之间的小数

日期函数

now：返回当前日期+时间

year：返回年

month：返回月

day：返回日

date_format：将日期转化为字符

curdate：返回当前日期

str_to_date：将字符转换为日期

curtime：返回当前时间

hour，minute，second：时分秒

datediff：返回两个日期相差的天数

monthname：英文形式返回月

其它函数

version：返回数据库服务器当前版本

database：当前打开的数据库

user：当前用户

password(‘字符串’)：返回该字符的密码形式

md5(‘字符串’)：返回 md5 加密后的字符串

分组函数

max，min，sum，avg，count

分组函数的特点

1. select max(字段) from 表名：
2. sum 和 avg 一般用于处理数值类型；另外三种可以处理任何类型
3. 以上分组函数都忽略 null 值
4. 可以搭配 distinct 使用，实现去重统计
5. count 支持
   1. count(字段)：统计该字段值不为 null 的记录的条数
   2. count(*)：统计结果集的行数
   3. count(1)：相当于往表里面添加了一列都是 1 的值，然后统计 1 的个数。就结果而言和 count(*) 相同
   4. count() 和 count(1) 的效率问题：在 MyISAM 存储引擎下，count() 效率高；在 InnoDB 存储引擎下，count(1) 和 count(*) 差不多
6. 和分组查询一同查询的字段，要求是 group by 后出现的字段

分组查询

select 分组函数, 分组后的字段
from 表名
[where 筛选条件]
group by 分组字段
[having 分组后筛选]
[order by 排序列表]

连接查询

当查询中涉及到多个表的字段，需要使用多表连接查询

等值连接

select 字段1, 字段2
from 表1, 表2...
where 连接条件 [and 筛选条件 | 连接条件]
[group by 分组字段]
[having 分组后筛选]
[order by 排序列表];

练习

#查询所有员工的姓名，部门号和部门名称
SELECT last_name, d.`department_id`, d.`department_name`
FROM employees AS e, departments AS d
WHERE e.`department_id` = d.`department_id`;

#查询 90 号部门所有员工的 job_id 和 90 号部门的 location_id
SELECT e.`job_id`, d.`location_id`
FROM employees e, departments d
WHERE e.`department_id` = 90
AND e.`department_id` = d.`department_id`;

-- 或者
SELECT e.`job_id`, d.`location_id`
FROM employees e, departments d
WHERE e.`department_id` = 90
AND d.`department_id` = 90;

#选择所有有奖金的员工的 last_name，department_name，location_id，city
select e.`last_name`, d.`department_name`, l.`country_id`, l.`city`
from employees e, departments d, locations l 
where e.`department_id` = d.`department_id`
and d.`location_id` = l.`location_id`
and e.`commission_pct` is not null

#选择 city 在 Toronto 工作的员工的 last_name ,job_id, department_id, department_name
SELECT last_name, job_id, e.department_id, department_name
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id`  = l.`location_id`
AND l.`city` = 'Toronto';

#查询每个工种，每个部门的部门名，工种名和最低工资
SELECT e.`job_id`, d.`department_name`, MIN(salary)
FROM employees e, departments d, jobs j
WHERE e.`department_id` = d.`department_id`
AND e.`job_id` = j.`job_id`
GROUP BY e.`job_id`, d.`department_name`;

#查询每个国家下的部门个数大于 2 的国家编号
SELECT l.`country_id`, COUNT(*) 部门个数
FROM locations l, departments d
WHERE d.`location_id` = l.`location_id`
GROUP BY l.`country_id`
HAVING COUNT(*) > 2;

#选择指定员工的姓名，员工号，以及其管理者的姓名和员工号
select e1.`last_name`, e1.`employee_id`, e2.`last_name`, e2.`employee_id`
from employees e1, employees e2 
where e1.`manager_id` = e2.`employee_id`;

SQL 99 标准

语法

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

从语法上来说，就是将 92语法中通过 , 连接多张表改为 [连接类型] join 来连接多张表，将写在 where 后的连接条件改为写在了 on 后面

支持内连接，外连接，交叉连接

连接类型关键字分别为

• 内连接：inner
• 左外连接：left [outer]
• 右外连接：right [outer]
• 全外连接：full [outer]
• 交叉连接：cross [outer]

内连接

select 查询列表
from 表1 [别名] inner join 表2 [别名]
on 连接条件
[where] 分组前筛选
[group by] 分组列表 [having] 分组后筛选
[order by] 排序列表

等值连接

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

筛选

#查询名字中包含 e 的员工名和其工种名
select last_name, j.job_id
from employees e inner join jobs j
on e.`job_id` = j.`job_id`
where e.`last_name` like "%e%";

分组+筛选

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

排序

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

#查询员工名，部门名，工种名，并按部门名降序
select last_name, department_name, job_title
from employees e inner join departments d inner join jobs j
on e.`department_id` = d.`department_id` and e.`job_id` = j.`job_id`
order by d.`department_name` DESC;

-- 或者
SELECT last_name, department_name, job_title
FROM employees e 
INNER JOIN departments d ON e.`department_id` = d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY d.`department_name` DESC;

##使用后面这种方式需要注意，每一个 ON 前连接的两张表都需要有连接关系，不然 ON 后面的连接条件写不出来

特点

• 筛选条件放在 where 后面；连接条件放在 on 后面

非等值连接

#查询员工的工资级别
SELECT last_name, salary, grade
FROM employees e
INNER JOIN sal_grade s
ON e.`salary` BETWEEN s.`min_salary` AND s.`max_salary`;

#查询每个工资级别的总数大于 20 的工资级别，并且按工资级别降序
select count(*) 工资阶级人数, grade
from employees e
inner join sal_grade s
on e.`salary` between s.`min_salary` and s.`max_salary`
group by s.grade
having count(*) > 20
order by grade desc;

自连接

#查询员工的名字，上级的名字
select e1.last_name, e2.`last_name`
from employees e1 inner join employees e2
on e1.`manager_id` = e2.`employee_id`;

外连接

内连接一般用于查询多张表的交集

外连接一般用于查询一张表中有另一张表中没有的记录

在外连接中，分为主表和从表，从表中如果根据连接条件找不到可以和主表匹配的记录，则会拼接一条字段全为 null 的记录到连接条件连接的主表记录上

特点

• 外连接的查询结果为主表中的所有记录，如果从表中有和其匹配的 (满足连接条件)，则显示匹配的记录
• 左外连接，left join 左边的是主表；右外连接，right join 右边的是主表
• 左外和右外交换两个表的顺序，可以实现同样的效果

小诀窍：最后要查的结果属于哪张表就用哪张表作为主表

#查询男朋友不在男神表上的女神名
#由于男神表中只有4条记录，因此认为女神表中的 boyfriend_id 大于 4的属于没有男朋友的
select be.`name`, bo.*
from beauty be left [outer] join boys bo
on be.`boyfriend_id` = bo.`id`
where bo.`id` is null;

#查询哪个部门没有员工
#连接后员工id不存在的部门就是鬼部门
SELECT d.`department_name`
FROM departments d LEFT OUTER JOIN employees e
ON e.`department_id` = d.`department_id`
WHERE e.`employee_id` IS NULL;

全外连接

MySQL 本身并不支持全外连接

FULL OUTER JOIN，会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为 null

交叉连接

关键字：cross join

交叉连接的结果就是多表的完全笛卡尔乘积

总结

练习

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

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

#查询部门名为 SAL 或 IT 的员工信息
select e.*
from employees e left join departments d
on e.`department_id` = d.`department_id`
where d.`department_name` = "SAL" or d.`department_name` = "IT";
-- 内连接
SELECT e.*
FROM departments d INNER JOIN employees e
ON e.`department_id` = d.`department_id`
WHERE d.`department_name` = "SAL" OR d.`department_name` = "IT";

子查询

出现在其它语句中的 select 语句称之为子查询 / 内查询

外部的查询语句称为主查询 / 外查询

#查询部门地址在 1700 的所有部门的所有员工
SELECT first_name
FROM
  employees e 
WHERE department_id IN 
  (SELECT 
    department_id 
  FROM
    departments 
  WHERE location_id = 1700) ;

子查询的分类

• 按照子查询出现的位置
  • select 后面：只支持标量子查询
  • from 后面：支持表子查询
  • where / having 后面：标量子查询和列子查询，行子查询
  • exists 后面 (相关子查询)：支持表子查询
• 按结果集行列数不同
  • 标量子查询：结果集只有一行一列，单行子查询
  • 列子查询：结果集只有多行一列， 多行子查询
  • 行子查询：结果集一行多列
  • 表子查询：结果集多行多列

where 后的标量子查询

标量子查询特点

• 子查询放在小括号内
• 子查询一般放在条件的右侧
• 标量子查询，一般搭配着条件运算符使用；列子查询一般搭配多行操作符使用 ( IN，ANY / SOME，ALL )
• 子查询的执行优先于主查询，主查询会用到子查询的结果

#谁的工资比 Abel 高的员工姓名
#先查名字叫 Abel 的人的工资，结果只有一行一列，所以是标量
#再查询员工信息，满足 salary > 第一步查出来的结果
SELECT 
  e.`first_name` 
FROM
  employees e 
WHERE e.`salary` > 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE last_name = 'Abel')

#返回 job_id 与 141 号员工相同，salary 比 143 号员工多的员工姓名，job_id 和工资
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
select 
  last_name,
  job_id,
  salary 
from
  employees 
where salary = 
  (select 
    min(salary) 
  from
    employees) ;

#查询最低工资大于 50 号部门最低工资的部门 id 和其最低工资
SELECT 
  MIN(salary) 最低工资,
  d.`department_id` 
FROM
  departments d 
  INNER JOIN employees e 
    ON d.`department_id` = e.`department_id` 
GROUP BY e.`department_id` 
HAVING 最低工资 > 
  (SELECT 
    MIN(salary) 
  FROM
    departments d 
    INNER JOIN employees e 
      ON d.`department_id` = e.`department_id` 
  GROUP BY e.`department_id` 
  HAVING e.`department_id` = 50) ;

where 后的列子查询

列子查询即多行子查询，并且使用多行操作符

IN(值1, 值2 ….)：当值等于列表中的其中一个值时，可以被查询

ANY(值1, 值2 …)：a > ANY(值1, 值2 …)，代表 a 大于列表中任意一个值即可满足被查询条件，对于这个示例来说，可以替换成 a>min(列表)，代表大于最小值即可

ALL：a > ALL(值1, 值2 …)，a 大于列表中所有的即可被查询，同理可以替换为 a > max(列表)，代表只要大于最大值即可被查出来

#查询 location_id 是 1400 / 1700 的部门中所有员工的姓名
select last_name
from
  employees 
where department_id in 
  (select 
    department_id 
  from
    departments 
  where location_id in (1400, 1700)) ;

#返回其它工种中比 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";

-- 或者使用判断，因为是小于任意一个，所以最低要求是小于最大的

SELECT employee_id, last_name, job_id, salary
FROM
  employees 
WHERE salary <
  (SELECT 
    MAX(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 < ALL 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE job_id = "IT_PROG") 
  AND job_id <> "IT_PROG" ;

-- 

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" ;

where 后的行子查询

#查询员工编号最小并且工资最高的员工信息
-- 一般查询方式
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);

当两个筛选条件都用 = 与子查询连接时，可以尝试改为行子查询

select 后的子查询

#查询每个部门的员工个数 (包含没有员工的部门) 和部门名
SELECT 
  d.*,
  (SELECT 
    COUNT(*) 
  FROM
    employees e
  WHERE d.`department_id` = e.department_id) 个数
FROM
  departments d ;

#查询员工号等于 = 102 的员工的部门名
select 
  (select 
    department_name 
  from
    departments d
  where d.department_id = e.`department_id`) as 部门名
from
  employees e 
where e.`employee_id` = 102 ;

-- 或者使用内连接
SELECT 
  d.`department_name` 部门名
FROM
  employees e 
  INNER JOIN departments d 
    ON e.`department_id` = d.`department_id` 
WHERE e.`employee_id` = 102 ;

注意：select 后子查询只能返回一行一列

from 后的子查询

from 后面的子查询相当于把子查询的结果当成一个表来使用

#查询每个部门的平均工资的工资等级
#1. 查询每个部门的平均工资
select 
  grade,
  ag.s 平均工资 
from
  (select 
    avg(salary) s 
  from
    employees 
  group by department_id) ag 
  inner join sal_grade s 
    on ag.s between s.`min_salary` 
    and s.`max_salary` ;

exists 后的子查询

exists 后的子查询即相关子查询

exists 只关心是否存在查询结果，如果有值则返回 1，没有则返回 0。只有这两种情况

select exists(select employee_id from employees);

select exists(select employee_id from employees where employee_id = 2000);

#查询有员工的部门名
select department_name
from departments d
where exists(
    select *
    from employees e
    where e.`department_id` = d.`department_id`
);

-- exists 可以用 IN 来代替
select department_name
from departments d
where d.`department_id` in(
    select department_id
    from employees e
);

exists 是先执行外查询，然后根据外查询的值来过滤内查询的值

使用 exists 的都可以用 in 代替

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

-- 使用 not exists
select bo.*
from boys bo
where not exists(
    select *
    from beauty be
    where be.`boyfriend_id` = bo.`id`
);

练习

#查询和 Zlotkey 相同部门的员工姓名和工资
SELECT e2.last_name, e2.`salary`
FROM employees e INNER JOIN employees e2
ON e.`department_id` = e2.`department_id`
WHERE e.last_name = 'Zlotkey' ;

-- 或者使用子查询

select last_name, salary
from employees 
where department_id = (
    select department_id  from employees
    where last_name = "Zlotkey"
);

#2. 查询工资比公司平均工资高的员工的员工号，姓名和工资
select e.`employee_id`, e.`last_name`, e.`salary`
from employees e
where e.`salary` > (
    select avg(salary)
    from employees
);

#查询每个部门中工资比本部门平均工资高的员工的员工号，姓名，和工资
#①查询各个部门的平均工资
SELECT AVG(salary)
    FROM employees e2
    GROUP BY e2.`department_id`;
#然后连接大表和小表再进行筛选
SELECT employee_id, last_name, salary, e.`department_id`
FROM (SELECT AVG(salary) ag, department_id 
    FROM employees
    GROUP BY department_id) ags
    INNER JOIN employees e
ON e.`department_id`  = ags.department_id 
WHERE e.`salary` > ags.ag;

-- 或者使用标量子查询
SELECT e.`employee_id`, last_name, salary, e.`department_id`
FROM employees e
WHERE e.`salary` > (
    SELECT AVG(salary)
    FROM employees e2
    GROUP BY e2.`department_id`
    #过滤，当大表员工的部门与分组表相同时
    #才让对应部门的平均值 (单行单列记录) 与 e 中记录比较，
    #满足了单行操作符
    HAVING e.`department_id` = e2.`department_id`
);

#查询和名字中包含字母 u 的员工在相同部门的员工的员工号和姓名
# 先查出来有名字里包含 u 的员工的部门id
# 然后发现结果为多行单列，再使用 in 进行子查询，查出员工的department_id 在里面的员工
select employee_id, last_name
from employees
where department_id in (select department_id
from employees 
where last_name like "%u%");

#查询在部门的 location_id 为 1700 的部门工作的员工的员工号
# 首先先查出 lacation_id 为 1700 的部门id
# 然后根据子查询结果查出这些部门下的员工
select employee_id
from employees
where department_id in (SELECT department_id
FROM departments
WHERE location_id = 1700);

#查询管理者是 K_ing 的员工姓名和工资
# 先查询姓名为 k_ing 的员工编号，发现有两个
# 然后筛选出 manager_id in 子查询结果的员工
select 
  last_name,
  salary 
from
  employees 
where manager_id in 
  (SELECT 
    employee_id 
  FROM
    employees 
  WHERE last_name = "K_ing") ;

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

#查询部门中工资最高的有部门的员工
SELECT *
FROM employees e1
WHERE salary IN (
SELECT  MAX(salary)
FROM employees e2
GROUP BY department_id
HAVING e1.`department_id` = e2.`department_id`
);

-- 或者
SELECT *
FROM
employees e1 
  INNER JOIN 
    (SELECT 
      MAX(salary) mas,
      department_id 
    FROM
      employees 
    GROUP BY department_id) e2 
    ON e1.salary = e2.mas
    WHERE e1.`department_id` = e2.department_id;

#查询工资最低的员工信息：last_name, salary
SELECT 
  last_name,
  salary 
FROM
  employees 
WHERE salary = 
  (SELECT 
    MIN(salary) 
  FROM
    employees);

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

分页查询

当要显示的数据一页显示不全或者数据太多一页显示太长，这时需要提交 sql 分页请求

特点：

• 放在查询语句的最后，并且执行顺序上也是在最后
• 公式：要显示的页数 page，每页条目数 size
  select 查询列表 from 表 limit (page-1)*size, size;

语法

select 查询列表
from 表名
[join type join 表2
on 连接条件
where 分组前筛选
group by 分组字段
having 分组后筛选
order by 排序字段]
limit 起始条目索引offset, 条目数size;

offset：要显示的条目的起始索引，认为记录索引从 0 开始

size：要显示的条目个数

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

当起始索引为 0 时，可以省略

#查询第 11 条到第 25 条员工信息
select *
from employees
limit 10, 15;

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

复习

SQL 99 语法

内连接

语法

select 查询列表
from 表1 别名 [inner] join 表2 别名
on 连接条件 
[表3 inner join on 连接条件]
[where 分组前筛选]
[group by 分组]
[having 分组后筛选]
[order by 排序]
[limit 分页起始索引, 分页大小]

特点

• 不分主表和从表，连接顺序可以调换
• 内连接的结果 = 多表的交集
• n 表连接至少需要 n-1 个连接条件

外连接

select 查询列表
from 表1 别名 left|right|cross|full [outer] join 表2 别名
on 连接条件 
[表3 left|right|cross|full [outer] join on 连接条件]
[where 分组前筛选]
[group by 分组]
[having 分组后筛选]
[order by 排序]
[limit 分页起始索引, 分页大小]

特点

• 查询结果 = 主表中所有的行，从表中和其匹配的将显示匹配行，如果从表没有匹配的则显示 null
• left join 左边是主表，right join 右边的是主表
• 一般用于查询除了交集部门的其余部分

交叉连接

select 查询列表
from 表1 别名 cross join 表2 别名
on 连接条件 
[表3 cross join on 连接条件]
[where 分组前筛选]
[group by 分组]
[having 分组后筛选]
[order by 排序]
[limit 分页起始索引, 分页大小]

生成一个完全笛卡尔乘积的结果

子查询

嵌套在其它语句内部的 select 语句称为子查询或者内查询

外部语句可以是 Insert，delete，update，select 等

一般以 select 作为外部语句，此时外部的 select 称为外查询 / 主查询

分类

按位置分类

• select 后面：只支持标量子查询
• from 后面：表子查询
• where 或者 having 后面：标量子查询，列子查询，行子查询
• exists 后面：标量子查询，列子查询，行子查询，表子查询

按结果集的行列分类

• 标量子查询 (子查询结果为一行一列)
• 列子查询 (子查询结果为多行一列)
• 行子查询 (子查询结果为多列一行)
• 表子查询 (嵌套子查询，结果集任意)

分页查询

查询条目数过多时，可以使用分页

经典题目

#查询工资最低的员工信息: last_name, salary
select last_name, salary 
from employees
where salary = (
    select min(salary)
    from employees
);

#查询平均工资最低的部门信息和平均工资
#先查询每个部门的平均工资
select avg(salary)
from employees e
group by department_id;

#再查询最低平均工资
select min(avgs.ag)
from (SELECT AVG(salary) ag
FROM employees e
GROUP BY department_id) avgs;

#再查询部门信息
select avg(salary), d.*
from employees e inner join departments d
on e.`department_id` = d.`department_id`
group by d.department_id
having avg(salary) = (SELECT MIN(avgs.ag)
FROM (SELECT AVG(salary) ag
FROM employees e
GROUP BY department_id) avgs);

-- 或者使用分页来这么做
select d.*
from employees e inner join departments d
on e.`department_id` = d.`department_id`
group by d.`department_id`
having avg(salary) = (
    select avg(salary) 
    from employees
    group by department_id
    #排序后取第一个，就是最低平均值
    order by avg(salary)
    limit 1
);

-- 或者内连接结果集
SELECT 
  d.*,
  e.ag 
FROM
  (SELECT 
    AVG(salary) ag,
    department_id 
  FROM
    employees 
  GROUP BY department_id 
  ORDER BY AVG(salary) 
  LIMIT 1) e 
  INNER JOIN departments d 
    ON e.`department_id` = d.`department_id` 

-- 如果只查询部门信息甚至这么写
SELECT 
  * 
FROM
  departments 
WHERE department_id = 
  (SELECT 
    department_id 
  FROM
    employees 
  GROUP BY department_id 
  ORDER BY AVG(salary) 
  LIMIT 1);

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

#先查询job的平均工资
SELECT AVG(salary)
FROM employees
GROUP BY job_id;
#再查询最高工资的 job_id
SELECT 
    job_id 
  FROM
    employees 
  GROUP BY job_id 
  order by avg(salary) desc
  limit 1

#最后使用子查询
select 
  * 
from
  jobs 
where job_id = 
  (SELECT 
    job_id 
  FROM
    employees 
  GROUP BY job_id 
  order by avg(salary) desc
  limit 1)

-- 或者

SELECT 
  * 
FROM
  (SELECT 
    AVG(salary) ag,
    job_id 
  FROM
    employees 
  GROUP BY job_id 
  ORDER BY AVG(salary) desc
  LIMIT 1) ags 
  JOIN jobs j 
WHERE ags.job_id = j.`job_id` ;

#查询平均工资高于公司的所有部门的平均工资的平均工资的部门有哪些
#思路：查找出所有部门的平均工资，然后再求平均工资的平均工资，最后用 all
SELECT AVG(salary)
FROM employees
GROUP BY department_id;

select 
  avg(ags.ag) 
from
  (SELECT 
    AVG(salary) ag 
  FROM
    employees 
  GROUP BY department_id) ags ;

-- last
SELECT 
  d.*,
  AVG(salary) 
FROM
  employees e 
  INNER JOIN departments d 
    ON e.`department_id` = d.`department_id` 
GROUP BY department_id 
HAVING AVG(salary) > ALL 
  (SELECT 
    AVG(ags.ag) 
  FROM
    (SELECT 
      AVG(salary) ag 
    FROM
      employees 
    GROUP BY department_id) ags) ;

#查询出公司中所有 manager 的信息
# 根据每个员工的 manager_id 来查，然后去重，最后根据去重结果来选出员工
select 
  * 
from
  employees 
where employee_id in 
  (SELECT DISTINCT 
    e1.manager_id 
  FROM
    employees e1 
  WHERE e1.`manager_id` IS NOT NULL) ;

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

#先查询各个部门的最高工资
select max(salary)
from employees
group by department_id;
#找到最高工资中，最低的那个工资对应的部门id
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1;
#根据部门id查找部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = (
    SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1
);

#查询平均工资最高的部门的 manager 的详细信息：last_name, department, email, salary

#首先查出平均工资最高的部门，因此需要先查出所有部门的平均工资
select avg(salary)
from employees
group by department_id;
#查出最高的平均工资对应的部门编号
select 
  department_id 
from
  (select 
    department_id 
  from
    employees 
  group by department_id 
  order by avg(salary) desc 
  limit 1) ags ;

#根据department_id查出对应的department的对应的manager_id，根据部门的管理者id再查出对应的员工
select 
  e.* 
from
  departments d 
  inner join employees e 
  #查询出每个部门的管理员，筛选出指定部门的管理员
    on d.`manager_id` = e.`employee_id` 
where d.`department_id` = 
  (select 
    department_id 
  from
    (select 
      department_id 
    from
      employees 
    group by department_id 
    order by avg(salary) desc 
    limit 1) ags) ;

练习

导入这几张表

CREATE TABLE student(
    studentno VARCHAR(10) NOT NULL PRIMARY KEY,
    studentname VARCHAR(20) NOT NULL,
    loginpwd VARCHAR(8) NOT NULL,
    sex CHAR(1) ,
    majorid INT NOT NULL REFERENCES grade(majorid),
    phone VARCHAR(11),
    email VARCHAR(20) ,
    borndate DATETIME
);

CREATE TABLE major(
    majorid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    majorname VARCHAR(20) NOT  NULL

);
CREATE TABLE result(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    studentno VARCHAR(10) NOT NULL REFERENCES student(studentno),
    score DOUBLE
);


INSERT INTO major VALUES(NULL,'javaee');
INSERT INTO major VALUES(NULL,'html5');
INSERT INTO major VALUES(NULL,'android');


INSERT INTO student VALUES('S001','张三封','8888','男',1,'13288886666','zhangsanfeng@126.com','1966-9-1');
INSERT INTO student VALUES('S002','殷天正','8888','男',1,'13888881234','yintianzheng@qq.com','1976-9-2');
INSERT INTO student VALUES('S003','周伯通','8888','男',2,'13288886666','zhoubotong@126.com','1986-9-3');
INSERT INTO student VALUES('S004','张翠山','8888','男',1,'13288886666',NULL,'1995-9-4');
INSERT INTO student VALUES('S005','小小张','8888','女',3,'13288885678','xiaozhang@126.com','1990-9-5');

INSERT INTO student VALUES('S006','张无忌','8888','男',2,'13288886666','zhangwuji@126.com','1998-8-9');
INSERT INTO student VALUES('S007','赵敏','0000','女',1,'13288880987','zhaomin@126.com','1998-6-9');
INSERT INTO student VALUES('S008','周芷若','6666','女',1,'13288883456','zhouzhiruo@126.com','1996-7-9');
INSERT INTO student VALUES('S009','殷素素','8888','女',1,'13288886666','yinsusu@163.com','1986-1-9');
INSERT INTO student VALUES('S010','宋远桥','6666','男',3,'1328888890','songyuanqiao@qq.com','1996-2-9');


INSERT INTO student VALUES('S011','杨不悔','6666','女',2,'13288882345',NULL,'1995-9-9');
INSERT INTO student VALUES('S012','杨逍','9999','男',1,'13288885432',NULL,'1976-9-9');
INSERT INTO student VALUES('S013','纪晓芙','9999','女',3,'13288888765',NULL,'1976-9-9');
INSERT INTO student VALUES('S014','谢逊','9999','男',1,'13288882211',NULL,'1946-9-9');
INSERT INTO student VALUES('S015','宋青书','9999','男',3,'13288889900',NULL,'1976-6-8');



INSERT INTO result VALUES(NULL,'s001',100);
INSERT INTO result VALUES(NULL,'s002',90);
INSERT INTO result VALUES(NULL,'s003',80);

INSERT INTO result VALUES(NULL,'s004',70);
INSERT INTO result VALUES(NULL,'s005',60);
INSERT INTO result VALUES(NULL,'s006',50);
INSERT INTO result VALUES(NULL,'s006',40);
INSERT INTO result VALUES(NULL,'s005',95);
INSERT INTO result VALUES(NULL,'s006',88);

#查询每个专业的学生人数和专业信息
select count(*), m.*
from student s inner join major m
on s.majorid = m.`majorid`
group by majorid;

#查询参加考试的学生中，每个学生的平均分和最高分
#先查出参加考试了的学生 id，平均分和最高分
SELECT r.`studentno`, AVG(r.`score`), MAX(r.`score`)
FROM result r
GROUP BY r.`studentno`;

#查询姓张的每个学生最低分大于 60 的学号，姓名
SELECT studentname, s.studentno, MIN(score)
FROM student s INNER JOIN
result r
ON r.`studentno` = s.studentno
where s.studentname like "张%"
GROUP BY r.`studentno`
HAVING MIN(score) > 60;

#查询每个专业生日在 1988-1-1 后的学生姓名，专业名称

#由于 borndate 是字符串，直接比较即可，筛选出字典序大于 1988-1-1 的即可 （或者使用 datediff 函数）
SELECT *
FROM student s
#where datadiff(borndate, "1988-1-1") > 0
WHERE s.`borndate` > "1988-1-1";

#内连接
SELECT s.`studentname`, m.`majorname`
FROM student s INNER JOIN major m
ON s.`majorid` = m.`majorid`
WHERE s.`borndate` > "1988-1-1";

#查询每个专业的男生人数和女生人数
SELECT COUNT(*), s.`sex`, s.`majorid`
FROM student s
GROUP BY s.`majorid`, s.`sex`;

-- 或者为了更好地效果，可以采用
SELECT 
  majorid,
  (SELECT 
    COUNT(*) 
  FROM student
  WHERE sex = "男" AND majorid = s.`majorid`) 男,
  (SELECT 
    COUNT(*) 
  FROM student
  WHERE sex = "女" AND majorid = s.`majorid`) 女 
FROM
  student s
 GROUP BY majorid;

#查询专业和张翠山一样的学生的最低分
#查出张翠山的专业编号
SELECT s.`majorid`
FROM student s
WHERE s.`studentname` = "张翠山";

#内连接成绩表，查出存在成绩的学生中专业 1 学生，然后显示最低分
select min(score)
from student s inner join result r
on r.`studentno`  = s.`studentno`
where s.`majorid` = (select s.`majorid`
from student s
where s.`studentname` = "张翠山");

#查询大于 60 分的学生的姓名，密码，专业名
select r.`score`, s.`studentname`, s.`loginpwd`, m.`majorname`
from student s inner join result r
on s.`studentno` = r.`studentno`
inner join major m
on m.`majorid` = s.`majorid`
where r.`score` > 60;

#按照邮箱位数分组，查询每组的学生个数
SELECT COUNT(*)
FROM student s
GROUP BY LENGTH(s.`email`);

#查询哪个专业没有学生，分别使用左连接和右连接实现
select *
from major m left join student s
on m.`majorid` = s.`majorid`
where s.`studentno` is null;

SELECT *
FROM student s right JOIN major m
ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;

#查询没有成绩的人数
#先查询出有成绩的人的学生号
select s.`studentno`
from result r left join student s
on r.`studentno` = s.`studentno`;
#再查出不在有成绩的学生号里面的人数
SELECT COUNT(*)
FROM student s
WHERE s.`studentno` NOT IN(
    SELECT s.`studentno`
    FROM result r LEFT JOIN student s
    ON r.`studentno` = s.`studentno`
);

联合查询 union

将多条查询语句的结果集合成一个结果集

语法：

查询语句1
union
查询语句2
union
......

#使用查询部门号大于 90，或者邮箱中包含 a 的员工信息
SELECT *
FROM employees s
WHERE s.`email` LIKE "%a%"
UNION
SELECT *
FROM employees s
WHERE s.`department_id` > 90;

当所需要的结果来自多张表，且多张表之间没有连接关系时，使用联合查询

联合查询需要注意的事项

• 联合的多张表的查询语句的结果集需要具有相同的列数
• 要求多条查询语句的查询的每一列的类型和顺序最好一致
• 使用 UNION 会自动去重，如果不想去重，则使用 UNION ALL

总结

语法

select 查询列表
from 表1 别名
连接类型 join 表2 别名
on 连接条件 [连接类型 join 表3 别名 ...]
where 分组前筛选
group by 分组列表
having 分组后筛选
order by 排序列表
limit 分页起始索引, 分页大小;