第一部分的内容:
SQL(2)DQL语言 条件查询+排序查询+常见函数+分组查询+连接查询
https://blog.csdn.net/Lee_Yu_Rui/article/details/107527594
感谢以下链接的教学分享,很好的视频和资源,笔记记录在这里,便于日后查看
新i版-链接: https://pan.baidu.com/s/1GDhRzV_3lUQaETsH4CM8zQ 提取码: rpte
DQL : Data query language、
子查询
# 进阶7 :子查询
/*
含义:出现在其他语句中的select语句,称为字查询或内查询
外部的查询语句,称为主查询或外查询
分类:
按照子查询出现的位置:
select后面:
仅仅标量字查询(结果只有一行一列)
from后面
表子查询(结果集,一般多行多列)
where或having后面 ★
标量字查询(单行:结果只有一行一列)★
列子查询(多行:结果多行一列)★
行子查询(多列)
exists后面(相关子查询)
表子查询(结果集,一般多行多列)
按照结果集的行列数不同
标量字查询(结果只有一行一列)
列子查询(结果多行一列)
行子查询(多列)
表子查询(结果集,一般多行多列)
*/
# 一.where或having后面
/*
特点:
1.子查询放在小括号内
2.子查询一般放在条件右侧
3.单行子查询,一般搭配单行操作符 < > >= <= = <>
多行子查询,一般搭配多行操作符 in any/some all
*/
#1.标量子查询(单行子查询)
#案例1 谁得工资比Abel高?
#①查询Abel工资
SELECT `salary`
FROM`employees`
WHERE `last_name` = 'Abel'
#②查询哪个salary>①
SELECT *
FROM `employees`
WHERE salary>(
SELECT `salary`
FROM`employees`
WHERE `last_name` = 'Abel'
);
#案例2. 返回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
);
#案例3 返回公司工资最少的员工的last_name 和 salary
SELECT last_name,`job_id`,salary
FROM `employees`
WHERE salary = (
SELECT MIN(salary)
FROM `employees`
)
# 案例4 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT `department_id`,MIN(`salary`)
FROM `employees`
GROUP BY `department_id`
HAVING MIN(salary) > (
SELECT MIN(`salary`)
FROM `employees`
WHERE `department_id`=50
);
#2.列子查询(多行子查询)
# 案例1: 返回location_id 是1400或1700的部门中的所有员工姓名
SELECT `last_name`
FROM employees
WHERE `department_id` IN (
SELECT DISTINCT `department_id`
FROM `departments`
WHERE `location_id` IN ('1400','1700')
);
#案例2: 返回其他部门中比Job_id为‘IT_PROG’部门任一工资低的员工的员工号,姓名,job_id以及salary
## Job_id为‘IT_PROG’部门的最低工资
## 其他部门工资
SELECT `employee_id`,`job_id`,`salary`
FROM `employees`
WHERE `salary` < ANY(
SELECT DISTINCT `salary`
FROM `employees`
WHERE Job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG'
SELECT `employee_id`,`job_id`,`salary`
FROM `employees`
WHERE `salary` < (
SELECT MAX(`salary`)
FROM `employees`
WHERE Job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG'
#案例3: 返回其他部门中比Job_id为‘IT_PROG’部门所有低的员工的员工号,姓名,job_id以及salary
SELECT `employee_id`,`job_id`,`salary`
FROM `employees`
WHERE `salary` < (
SELECT MIN(`salary`)
FROM `employees`
WHERE Job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG'
#3.行子查询(多列)
# 案例 查询 员工编号最小并且工资最高的员工
SELECT *
FROM employees
WHERE `employee_id` =(
SELECT MIN(`employee_id`)
FROM
`employees`
) AND (
SELECT MAX(`salary`)
FROM employees
)
SELECT *
FROM employees
WHERE (`employee_id`,`salary`) = (
SELECT MIN(`employee_id`), MAX(`salary`)
FROM `employees`
)
#二.select 后面
#案例1: 查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM `employees` e
WHERE e.`department_id` = d.`department_id`
) 个数
FROM `departments` d
#案例2: 查询员工号等于102的部门名
SELECT (
SELECT `department_name`
FROM `departments` d
INNER JOIN `employees` e
ON e.`department_id` = d.`department_id`
WHERE e.`employee_id` = 102
) 部门名;
# 三.from后面
#案例1: 查询每个部门的平均工资的工资等级
SELECT AVG(salary),department_id
FROM `employees`
GROUP BY department_id
SELECT * FROM job_grades;
#②连接①的结果集和job_grades表,筛选条件平均工资在最大最小之间
SELECT ag_dep .*,g.grade_level
FROM (
SELECT AVG(salary) ag,department_id
FROM `employees`
GROUP BY department_id
)ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal anglehighest_sal
# 四 exists 后面(相关子查询)
/*
exists() 是否有查询结果
*/
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary = 300000)
#案例1: 查询有员工的部门名
SELECT `department_name`
FROM `departments` d
WHERE EXISTS(
SELECT *
FROM `employees` e
WHERE d.`department_id` = e.`department_id`
)
SELECT `department_name`
FROM `departments` d
WHERE d.`department_id` IN (
SELECT `department_id`
FROM `employees`
)
#案例2: 查询没有女朋友的男神信息
SELECT bo.*
FROM `boys` bo
WHERE bo.id NOT IN(
SELECT `boyfriend_id`
FROM `beauty`
);
SELECT bo.*
FROM `boys` bo
WHERE NOT EXISTS(
SELECT `boyfriend_id`
FROM `beauty` b
WHERE bo.`id` = b.`boyfriend_id`
);
分页查询
## 进阶8:分页查询 ⭐
/*
应用场景:当数据一页显示不全,需要分页提交请求
语法
select 查询列表
from 表名
....
limit offset,size;
offset 要显示条目的起始索引(起始索引从0开始) 如果是0可以省略
size 要显示的条目个数
特点:
1.执行和语法都在最后
2.公式
要显示的页数 page,每页条目数 size
limit (page-1)*size,size;
*/
# 案例1:前五条员工信息
SELECT * FROM `employees` LIMIT 0,5;
# 案例2:11-25
SELECT * FROM `employees` LIMIT 10,15;
联合查询
# 进阶9 联合查询 union
/*
union 联合 合并:将多条查询语句的结果合并成一个结果
特点:
1.要求多查询语句的查询列数必须一致
2.要求多条查询语句的查询的各类顺序最好一致
3.union 去重查询,可以用union all 不去重
*/
# 案例1 部门编号大于90或者邮箱中包含a的邮箱信息
SELECT * FROM `employees` WHERE email LIKE'%a%'
UNION
SELECT * FROM `employees` WHERE `department_id`>90;
查询语法总结
语法:(后面数字是执行顺序)
select 查询列表 ⑦
from 表格 别名 ①
inner |left|right join 表2 别名 ②
on 连接条件 ③
where 筛选条件 ④
group By 分组条件 ⑤
having 后筛选 ⑥
order by 排序列表 ⑧
limit 起始条目索引,条目数; ⑨