常用DQL

时间：2020-06-30 19:45:54 阅读：46 评论：0 收藏：0 [点我收藏+]

SQL语言---DQL

数据查询语言 --- DQL，仅作为学习记录，自己敲得看着更熟悉

练习用表

基础查询

# SELECT 查询列表（字段；函数...） FROM 表名；

# 1. 查询常量
SELECT 100;

# 2. 查询表达式
SELECT 100%3;

# 3. 查询单个字段
SELECT `last_name` FROM `employees`;

# 4. 查询多个字段
SELECT `last_name`,`email`,`employee_id` FROM `employees`;

# 5. 查询所有字段; 格式化 F12
SELECT * FROM `employees`;

SELECT 
`employee_id`,
`first_name`,
`last_name`,
`email` ,
`phone_number`
FROM
`employees` 

# 6. 查询函数（调用方法，获取返回值）
SELECT DATABASE();# 查询当前所在数据库
SELECT VERSION();# 查询当前mysql版本
SELECT USER();# root 用户

# 7. 起别名
# 方式一：使用as关键字
SELECT USER() AS "用户名";

SELECT last_name AS "姓 名" FROM employees;
# 方式二：使用空格
SELECT last_name "姓名" FROM employees;


# 8. 拼接字段
-- 需求：拆线呢 first_name 和last_name 拼接成的全名，
-- 最终起别名为： 姓名
# 方案一： 使用+ 错误！
SELECT first_name+last_name AS "姓 名" 
FROM employees
# 方案二： 使用concat
SELECT CONCAT(first_name, last_name) AS "姓 名"
FROM employees

# 9. distinct的使用
-- 需求 查询员工涉及到的部门编号有哪些
SELECT DISTINCT department_id FROM employees; # 查询所有并去重

# 10. 查询表的结构
DESC employees;
SHOW COLUMNS FROM employees;

# 11. IFNULL的使用
-- 如果表达式1为null，则显示表达式2
SELECT commission_pct,IFNULL(commission_pct,‘空‘) FROM employees;

条件查询

/*
select 查询列表 from 表名 where 筛选条件；
执行顺序：
1. from子句
2. where子句

select last_name, first_name from employees where salary > 20000

特点：
1. 按关系表达式筛选
关系运算符：>   <   >=   <=   =   <>(!=)  

2. 按逻辑表达式筛选：
逻辑运算符：and（&&）   or（||）   not（!）

3. 模糊查询
like
in
between and
is null
*/

实例

# 一、按关系表达式查询

# 案例1、 查询部门编号不是100的员工信息
SELECT * FROM employees WHERE job_id <> 100;

# 案例2、查询工资小于15000的姓名、工资
SELECT last_name,salary FROM employees WHERE salary < 15000;

# 二、按逻辑表达式查询

# 案例1、查询部门标号不是50-100之间员工姓名、部门编号、邮箱
# 方式1
SELECT last_name,department_id,email FROM employees WHERE department_id < 50 OR department_id > 100
# 方式2
SELECT last_name,department_id,email FROM employees WHERE NOT(department_id >= 50 AND department_id <= 100)

# 案例2、奖金率大于0.03 或者 员工编号在 60-110 之间的员工信息
SELECT *
FROM employees
WHERE commission_pct > 0.03 OR (employee_id >= 60 AND employee_id <= 100)

# 三、模糊查询

-- 一. LIKE
/*
功能：一般和通配符搭配使用，对字符型数据进行匹配查询
常见通配符
_ 任意单个字符
% 任意多个字符 支持0个-多个
*/

# 案例1： 查询姓名中包含字符a的员工信息
SELECT *
FROM employees
WHERE last_name LIKE ‘%a%‘;

# 案例2： 查询姓名中最后一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_name LIKE ‘%e‘;

# 案例3： 查询姓名中第一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_name LIKE ‘e%‘;

# 案例4： 查询姓名中第三个字符为x的员工信息
SELECT *
FROM employees
WHERE last_name LIKE ‘__x%‘;

# 案例5： 查询姓名中第二个字符为_的员工信息
SELECT *
FROM employees
WHERE last_name LIKE ‘_\_%‘;

SELECT *
FROM employees
WHERE last_name LIKE ‘_$_%‘ ESCAPE ‘$‘; # 设置$为转义字符

-- 二. IN/NOT IN
/*
功能：查询某字段的值是否属于指定的列表之内
字段a in（常量值1，常量值2，常量值3，...）
字段a not in（常量值1，常量值2，常量值3，...）
*/

# 案例1：查询部门编号是30/50/90的员工名、部门编号
SELECT last_name, department_id
FROM employees
WHERE department_id IN(30,50,90)

# 方式2，如果用逻辑表达式：
SELECT last_name, department_id
FROM employees
WHERE department_id = 30 
OR department_id = 50
OR department_id = 90;

# 案例2：查询工种编号不是SH_CLERK或IT_PROG的员工信息
SELECT *
FROM employees
WHERE job_id NOT IN (‘SH_CLERK‘,‘IT_PROG‘);

# 方式2：
SELECT *
FROM employees
WHERE NOT (job_id = ‘SH_CLERK‘ OR job_id = ‘IT_PROG‘);

-- 三. between and/not between and
/*
功能：判断某个字段的值是否介于xx之间
between and/ not between
*/

# 案例1： 查询部门编号是30-90之间的部门编号、员工姓名
SELECT department_id, last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;

# 方式2：
SELECT department_id, last_name
FROM employees
WHERE department_id >=30 AND department_id <= 90;

# 案例2： 查询年薪不是1000000-2000000之间的员工姓名，工资，年薪
SELECT last_name, salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees 
WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;

-- 四. is null/ is not null

# 案例1： 查询没有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NULL;

/*
--------------------三种等于--------------------------
=     只能判断普通内容
IS    只能判断null值
<=>   安全等于，既能判断普通内容，又能判断null值
*/

排序查询

# 排序查询
/*
语法：

SELECT 查询列表
FROM 表名
WHERE 筛选条件
order by 排序列表

执行顺序：
1. from 子句
2. where 子句
3. select 子句
4. order by 子句

举例：
SELECT *
FROM employees
WHERE commission_pct IS NULL;

特点：
1. 排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合
2. 升序，通过asc, 默认行为
降序，通过desc 	
*/


-- 一、 按单个字段排序
# 案例1： 将员工编号>120的员工信息进行工资的升序
SELECT 
* 
FROM
employees 
WHERE employee_id > 120 
ORDER BY salary ASC 


-- 二、按表达式排序
# 案例1：对有奖金的员工，按年薪降序
SELECT *, salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

-- 三、按别名排序
# 案例1：对有奖金的员工，按年薪降序
SELECT *, salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY 年薪 DESC;

-- 四、按函数的结果排序
# 案例1： 按姓名的字数长度进行升序
SELECT LENGTH(last_name), last_name
FROM employees
ORDER BY LENGTH(last_name);

-- 五、按多个字段排序
# 案例1：查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序
SELECT last_name, salary, department_id 
FROM employees
ORDER BY salary ASC, department_id DESC;

-- 六、按列数排序
# 案例1： 按第二列字符升序排序
SELECT * 
FROM employees
ORDER BY 2

常见函数

/*
函数： 
1. 自定义函数
2. 调用函数：
	1. 叫什么
	2. 干什么
	
常见函数：
	字符函数
		
	数学函数
	日期函数
	流程控制函数
*/

/*
函数： 
1. 自定义函数
2. 调用函数：
	1. 叫什么
	2. 干什么
	
常见函数：
	字符函数
        1. concat 拼接字符
        2. length 获取字节长度
        3. char_length 获取字符长度
        4. substring 截取子串
        5. instr 获取字符串第一次出现的索引
        6. trim 去除前后空格（字符）
        7. lpad/rpad 左填充/右填充
        8. upper/lower 变大写/小写
        9. strtmp 比较两个字符大小
        10. left/right 截取子串
	
	数学函数
		abs
		ceil
		floor
		round
		truncate
		mod
		
	日期函数
		new
		curtime
		curdate
		datediff
		date_format
		str_to_date
		
	流程控制函数
		if
		case
*/

字符函数

-- 一、字符函数
# 1. concat 拼接字符
SELECT CONCAT(‘hello‘, first_name, last_name) 备注 FROM employees

# 2. length 获取字节长度
SELECT LENGTH(‘hello,大侠‘);

# 3. char_length 获取字符长度
SELECT CHAR_LENGTH(‘hello,大侠‘);

# 4. substring 截取子串
# substring(str,起始索引（从1开始）,截取字符长度)
# substring(str,起始索引（从1开始）)
SELECT SUBSTRING(‘你好哇！李银河‘,1,3);

# 5. instr 获取字符串第一次出现的索引
SELECT INSTR(‘hello,world,我是侠奢‘, ‘侠奢‘);

# 6. trim 去除前后空格(任意字符)
SELECT TRIM(‘   侠奢   ‘) AS 我;
SELECT TRIM(‘a‘ FROM ‘aaaaaaa侠奢aaa‘) AS 我;

# 7. lpad/rpad 左填充/ 右填充
SELECT LPAD(‘侠奢‘,10,‘帅‘);
SELECT RPAD(‘侠奢‘,10,‘帅‘);

# 8. upper/lower 变大写/小写

# 案例1：查询员工表的姓名，要求格式：姓首字母大写，其他字符小写，名所有字符大写，且姓和名之间用_分割。
# 最后起别名“OUTPUT”
SELECT UPPER(SUBSTR(first_name,1,1)), first_name
FROM employees;

SELECT LOWER(SUBSTR(first_name,2)) 
FROM employees;

SELECT UPPER(last_name) FROM employees;

SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)), LOWER(SUBSTR(first_name,2)) ,‘_‘, UPPER(last_name)) "OUTPUT"
FROM employees;


# 9. strtmp 比较两个字符大小
# 前面大返回1，后面大返回-1.否则返回0
SELECT STRCMP(‘abc‘, ‘abc‘);

# 10. left/right 截取子串
SELECT LEFT(‘golang天下第一‘,6); # 返回golang

数学函数

-- 二、数学函数
# 1. ABS 求绝对值
SELECT ABS(-2.4);

# 2. ceil 向上取整
SELECT CEIL(1.09) # 返回2

# 3. floor 向下取整
SELECT FLOOR(1.99)； # 返回1

# 4. round 四舍五入
SELECT ROUND（4.6） # 返回5

# 5. truncate 截断
SELECT TRUNCATE(1.876567, 1);  # 返回1.8
SELECT TRUNCATE(1.876567, 2);  # 返回1.87

# 6. mod 取余
SELECT MOD(-10, 3); # 返回1
SELECT -10%3;

日期函数

-- 三、日期函数
# 1. NOW 当前日期+时间
SELECT NOW();

# 2. curdate 当前日期
SELECT CURDATE();

# 3. curtime 当前时间
SELECT CURTIME();

# 4. datediff 日期之差
SELECT DATEDIFF(‘2020-1-1‘,‘2000-1-1‘);

# 5. date_farmat 日期固定格式
SELECT DATE_FORMAT(‘2010-2-2‘,‘%Y年%M月%d日 %H时%i分%s秒‘)；
# 返回 ；2010年February月02日 00时00分00秒

# 6. str_to_date 按指定格式解析字符串为日期类型
SELECT STR_TO_DATE(‘3/15 1998‘, ‘%m/%d %Y‘)；

流程控制函数

-- 四、流程控制函数
# 1. if 函数
SELECT IF(100>99,‘yes‘,‘no‘);

# 2. case 函数
# 情况一
CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
...
ELSE 结果n
END

# 案例：
部门编号为30，工资显示为2倍，如果部门编号为50，工资为3倍，
如果部门编号为40，工资翻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 newSalary
FROM employees;

# 情况2 类似于多重if语句，实现区间判断
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
...
ELSE 结果n
END


#案例
如果工资>20000，显示级别A
工资>15000，显示级别B
工资>10000，显示级别C
否则显示级别D

SELECT salary,
CASE
WHEN salary>20000 THEN ‘A‘
WHEN salary>15000 THEN ‘B‘
WHEN salary>10000 THEN ‘C‘
ELSE ‘D‘ 
END grade
FROM employees;

分组查询

分组函数/聚合函数

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

/*
sum(字段名) ：求和
avg(字段名) ：求平均数
max(字段名) ：求最大值
min(字段名) ：求最小值
count(字段名) ：计算非空字段值的个数
*/

-- 案例1 ：查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数
SELECT SUM(salary), AVG(salary), MIN(salary), MAX(salary), COUNT(salary)
FROM employees;

-- 案例2：查询月薪大于2500的人数：
SELECT COUNT(salary) FROM employees WHERE salary>2500;


COUNT 补充：
# 1.查询结果集的行数，推荐使用count(*)
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

# 表格中department_id=30共有多少行
SELECT COUNT(*) FROM employees WHERE department_id = 30;

# 2.搭配distinct实现去重的统计
# 需求： 查询有员工的部门的个数
SELECT COUNT(DISTINCT department_id) FROM employees;

分组查询

# 分组查询 ---> group by
/*
语法：
select 查询列表
from 	表名
where	筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表

执行顺序：
1. from 子句
2. where 子句
3. group by 子句
4. having 子句
5. select 子句
6. order by 子句

特点：
1. 查询列表往往是 分组函数和被分组的字段
2. 分组查询中的筛选分为两类
		  筛选的基表	    使用的关键词	      位置
分组前筛选	原始表		     where		group by 之前

分组后筛选	分组后的结果集	  having      group by 之后

注： 分组函数做条件只能放在having之后
*/

-- 一、基本操作
# 案例1：查询每个工种的员工的平均工资
SELECT AVG(salary), job_id
FROM employees
GROUP BY job_id;

# 案例2：查询每个领导的手下人数
SELECT COUNT(*), manager_id 
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id

-- 二、可以实现分组前的筛选
# 案例1：查询邮箱中包含a字符的每个部门的最高工资
SELECT MAX(salary) 最高工资, email, department_id
FROM employees
WHERE email LIKE ‘%a%‘
GROUP BY department_id;

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

-- 三、可以实现分组后的筛选 --->  HAVING
# 案例1：查询那个部门的员工个数>5
1. 查询每个部门的员工个数
SELECT COUNT(*) 员工个数, department_id
FROM employees
GROUP BY department_id;

2. 在刚才的结果基础上，筛选哪个部门的员工个数>5
SELECT COUNT(*) 员工个数, department_id
FROM employees
GROUP BY department_id;
HAVING COUNT(*) > 5


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

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

-- 四、可以实现排序
# 案例：查询有奖金的员工的最高工资>6000的工种编号和最高工资，按最高工资升序

# 分析1： 按工种分组，查询每个工种有奖金的员工的最高工资
SELECT MAX(salary) 最高工资, job_id 工种编号
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY salary
HAVING MAX(salary) > 6000;
ORDER BY MAX(salary) ASC;

-- 五、按多个字段分组
# 案例：查询每个工种每个部门的最低工资，并按最低工资降序
# 注：工种和部门相同的，为一组
SELECT MIN(salary) 最低工资, job_id, department_id 
FROM employees
GROUP BY job_id, department_id
ORDER BY MIN(salary) DESC;

连接查询

可分为：

内连接
	等值连接
	非等值连接
	自连接 
外连接
	左外链接
	右外链接
	全外链接（mysal不支持）
交叉连接

外连接一图以蔽之（SQL99标准）

技术分享图片

# 连接查询
/*
含义：又称多表查询，当我们查询的字段来自多个表时，就会使用连接查询

笛卡尔乘积：表1有m行，表2有n行，结果= m*n 行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
	按年代：
	sql92<仅支持内连接>
	sql99<支持内连接、外链接（左外+右外）、交叉连接>
	
	按功能分类：
	内连接
		等值连接
		非等值连接
		自连接 
	外连接
		左外链接
		右外链接
		全外链接<mysql不支持>
	交叉连接


*/
# 引例（等值连接）：
SELECT `name`,`boyName`
FROM beauty , boys
WHERE beauty.boyfriend_id = boys.id

#----------------------SQL92语法--------------------------#
-- 一、内连接

-- 二、等值连接
/*
语法： 
	select 查询列表
	from   表名1 别名1，表名2 别名2....
	where  等值连接连接条件

特点：
	1. 为了解决多表中的字段名重名问题，往往为表起别名，提高语义性
	2. 表的顺序无要求
*/

#1. 简单的两表连接
USE myemployees;
# 案例：
SELECT last_name, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;



# 2. 添加筛选条件
# 案例1：查询部门编号>100的部门名和所在的城市名
SELECT department_name, city
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
AND d.`department_id` > 100;

# 案例2：查询有奖金的员工名和部门名
SELECT last_name, department_name
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL

# 案例3：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name, city
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE ‘_o%‘;



# 3. 添加分组+筛选
#案例1：查询每个城市的部门个数
SELECT COUNT(*) 部门个数, l.`city` 城市名
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY l.`city`;

# 案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
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;

# 案例3：查询部门中员工个数>10的部门名
SELECT COUNT(*) 员工个数, d.department_name
FROM employees e, departments d
WHERE e.`department_id`=d.`department_id`
GROUP BY d.`department_id`
HAVING 员工个数>10;



# 4. 添加分组+筛选+排序

#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

# 5. 三表连接

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


-- 三、非等值连接

# 案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`

#执行一下下面命令，引入grade_level表
/*
select salary,employee_id from employees;
select * from job_grades;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);

INSERT INTO job_grades
VALUES (‘A‘, 1000, 2999);

INSERT INTO job_grades
VALUES (‘B‘, 3000, 5999);

INSERT INTO job_grades
VALUES(‘C‘, 6000, 9999);

INSERT INTO job_grades
VALUES(‘D‘, 10000, 14999);

INSERT INTO job_grades
VALUES(‘E‘, 15000, 24999);

INSERT INTO job_grades
VALUES(‘F‘, 25000, 40000);
*/



-- 四、自连接 （自己连接自己）

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




#--------------------------------------------------------#
#----------------------SQL99语法--------------------------#
#-----------支持内连接、外链接（左外+右外）、交叉连接-----------#
#--------------------------------------------------------#

-- 内连接
语法：
	SELECT 查询列表
	FROM 表名1 别名
	【INNER】 JOIN 表名2 别名
	ON 连接条件
	WHERE 筛选条件
	GROUP BY 分组列表
	HAVING 分组后筛选
	ORDER BY 排序列表

SQL92 与 SQL99 区别：
SQL99用JOIN关键字代替了之前的都厚，并且将连接条件和筛选条件进行了分割，以提高阅读性


-- 一. 等值连接
# 1.简单连接
# 查询员工名和部门名
SELECT last_name, department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id

# 2.添加筛选条件
# 案例：查询部门编号>100的部门名和所在的城市名
SELECT department_name, city
FROM departments d
JOIN locations l
ON d.location_id = l.location_id
WHERE d.department_id > 100


# 3.添加分组+筛选
# 案例：查询每个城市的部门个数
SELECT COUNT(*) 部门个数, l.city
FROM departments d
JOIN locations l
ON d.location_id = l.location_id
GROUP BY l.city;

# 4.添加分组+筛选+排序
# 查询部门中员工个数>10的部门名，并按个数降序
SELECT COUNT(*) 部门员工个数, d.department_name 部门名
FROM departments d
JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY d.`department_id`
HAVING 部门员工个数 > 10
ORDER BY 部门员工个数 DESC;


-- 二. 非等值连接
# 案例：查询部门编号在10-90之间的员工的工资级别，并按级别进行分组
SELECT * FROM job_grades

SELECT COUNT(*) 个数, grade_level
FROM employees e
JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
WHERE e.department_id BETWEEN 10 AND 90
GROUP BY j.grade_level;
 

/*
select salary,employee_id from employees;
select * from job_grades;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);

INSERT INTO job_grades
VALUES (‘A‘, 1000, 2999);

INSERT INTO job_grades
VALUES (‘B‘, 3000, 5999);

INSERT INTO job_grades
VALUES(‘C‘, 6000, 9999);

INSERT INTO job_grades
VALUES(‘D‘, 10000, 14999);

INSERT INTO job_grades
VALUES(‘E‘, 15000, 24999);

INSERT INTO job_grades
VALUES(‘F‘, 25000, 40000);
*/

-- 三. 自连接
# 案例：查询员工名和对应的领导名

SELECT e.`last_name`, m.`last_name`
FROM employees e
JOIN employees m
ON e.`manager_id` = m.`employee_id`




-- 外连接
/*

说明：查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项。如果从表没有匹配项，则显示null
应用场景：一般用查询主表中有，但从表中没有的记录
特点：
	1. 外连接分主从表，两表的顺序不能任意调换
	2. 左连接的话，left join  左边为主表
	   右连接的话，right join 右边为主表

语法： 

select 查询列表
from 表1 别名
left|right|full 【outer】 join 表2 别名
on 连接条件
where 筛选条件

*/

USE girls;
# 案例1：查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示null
# 左连接
# 主表：beauty     从表：boys
SELECT b.*, bo.*
FROM beauty b 
LEFT JOIN boys bo 
ON b.`boyfriend_id` = bo.`id`;

# 右连接
# 主表：boys     从表：beauty
SELECT b.*, bo.*
FROM boys bo 
RIGHT JOIN beauty b  
ON b.`boyfriend_id` = bo.`id`;


# 案例2：查哪个女神没有男朋友
# 左连接
SELECT b.`name`
FROM beauty b
LEFT JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;

# 右连接
SELECT b.`name`
FROM boys bo
RIGHT JOIN beauty b
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;

# 案例3：我想查询哪个部门没有员工，并显示其部门编号和部门名
SELECT d.`department_id`, d.`department_name`
FROM departments d
LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL

子查询

当一个查询语句中又嵌套了另一个完整得select语句，则被嵌套得select语句被称为 子查询 或 内查询
外面的select语句被称为 主查询 或 外查询

注：子查询不一定出现在select内部，只是出现在select内部的情况比较多

-- 分类：
按子查询出现的位置进行分类

1. select后面
	要求：子查询的结果为单行单列（标量子查询）
2. from后面
	要求：子查询的结果可以为多行多列
3. where或 having后面 
	要求：子查询的结果必须为单列
		单行子查询
		多行子查询
4. exists后面
	要求：子查询结果必须为单列（相关子查询）

-- 特点：
	1. 子查询放在条件中，要求必须放在条件的右侧
	2. 子查询一般放在小括号中
	3. 子查询的执行优先于主查询
    4. 单行子查询对应了 单行操作符： > < >= <= = <>
       多行子查询对应了 多行操作符： any / some all in

案例

-- 单行子查询

#案例1： 查询和 zlotkey 相同部门的员工姓名和工资

# 第一步. 查询zlotkey的部门编号
SELECT department_id
FROM employees
WHERE last_name = ‘zlotkey‘

# 第二步. 查询department_id = 第一步结果 的员工姓名和工资
SELECT last_name, salary
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	WHERE last_name = ‘zlotkey‘
);


# 案例2： 查询工资比公司平均工资高的员工的员工号，姓名和工资

# 第一步. 查询平均工资
SELECT AVG(salary)
FROM employees

# 第二步。查询salary > 平均工资的....
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
);

# 案例3. 查询最低工资大于50号部门最低工资的部门id和其最低工资

# 第一步. 50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50;

# 第二步. 查询最低工资大于 ... 的信息
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);

-- 多行子查询

/*
in：查询某字段是否在指定列表内，
	例如： x in (10, 30)
	
any/some ：判断某字段值是否满足其中任意一个
	例如： x > any(10, 30, 50) 等于 x > min()
	       x = any(10, 30, 50) 等于 x in (10, 30. 50)
		  
all：判断某字段的值是否满足里面所有的
	例如： x > all(10, 30, 50) 等于 x > max()	
*/

# 案例一：返回location_id是1400或1700的部门中的所有员工姓名
# 1.
SELECT 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)
);


# 案例二：返回其它部门中比job_id为‘IT_PROG‘部门任一工资低的员工的员工号、姓名、job_id 以及 salary

# 1. 
SELECT DISTINCT salary
FROM employees
WHERE job_id = ‘IT_PROG‘；

# 2.
SELECT employee_id, last_name, 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

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = ‘IT_PROG‘
);

-- 在其他位置子查询（非where/having之后）


# 二、放在select之后

# 案例：查询部门编号是50的员工个数
SELECT (
	SELECT COUNT(*)
	FROM employees
	WHERE department_id = 50
);



# 三、放在from后面 -- 表子查询

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

# 第一步. 查询每个部门的平均平均工资 --- 多行多列
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id

# 第二步. 将第一步和sal_grade两表连接查询
SELECT dep_ag.department_id, dep_ag.ag, grade_level
FROM job_grades g
JOIN (
	SELECT AVG(salary) ag, department_id
	FROM employees
	GROUP BY department_id
) dep_ag
ON dep_ag.ag BETWEEN g.lowest_sal AND g.highest_sal


/*
select salary,employee_id from employees;
select * from job_grades;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);

INSERT INTO job_grades
VALUES (‘A‘, 1000, 2999);

INSERT INTO job_grades
VALUES (‘B‘, 3000, 5999);

INSERT INTO job_grades
VALUES(‘C‘, 6000, 9999);

INSERT INTO job_grades
VALUES(‘D‘, 10000, 14999);

INSERT INTO job_grades
VALUES(‘E‘, 15000, 24999);

INSERT INTO job_grades
VALUES(‘F‘, 25000, 40000);
*/



# 四、放在exists后面

# 案例：查询有无名字叫侠奢的员工信息, 0 代表无，1 代表有
SELECT EXISTS (
	SELECT *
	FROM employees
	WHERE last_name = ‘侠奢‘
);

子查询练习题

子查询感觉略难，附加几个案例用作练习

-- 案例1. 查询工资最低的员工信息: last_name, salary
SELECT last_name, salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
);


-- 案例2. 查询平均工资最低的部门信息
# 一：各部门平均工资
SELECT AVG(salary) ,department_id 
FROM employees 
GROUP BY department_id;



# 二：查询 一 结果上的最低平均工资
SELECT MIN(ag)
FROM (
	SELECT AVG(salary) ag, department_id
	FROM employees
	GROUP BY department_id
	) ag_dep;
	


# 三：查询哪个部门的平均工资 = 二
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
	SELECT MIN(ag)
	FROM (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
	) ag_dep);
	
# 四：查询部门信息	
SELECT d.*
FROM departments d
WHERE d.department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary)=(
		SELECT MIN(ag)
		FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
		) ag_dep));



-- 案例3. 查询平均工资最低的部门信息和该部门的平均工资

# 前面三步和上一题相同，只是增加了select后面的子查询
SELECT d.*, (SELECT AVG(salary)   
                      FROM employees   
                      WHERE department_id = d.department_id
                      ) 平均工资
FROM departments d
WHERE department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary) = (
	SELECT MIN(ag)
	FROM (
		SELECT AVG(salary) ag, department_id
		FROM employees
		GROUP BY department_id
		) ag_dep
	)
);




-- 案例4. 查询平均工资最高的 job 信息

# 1
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id

# 2
SELECT MAX(ag)
FROM (
	SELECT AVG(salary) ag, job_id
	FROM employees
	GROUP BY job_id
	) ag_dep;

# 3
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (
	SELECT MAX(ag)
	FROM (
		SELECT AVG(salary) ag, job_id
		FROM employees
		GROUP BY job_id
		) ag_dep

	);

# 4	
SELECT * 
FROM jobs 
WHERE job_id = (
	SELECT job_id
	FROM employees
	GROUP BY job_id
	HAVING AVG(salary) = (
		SELECT MAX(ag)
		FROM (
			SELECT AVG(salary) ag, job_id
			FROM employees
			GROUP BY job_id
			) ag_dep

		)
	);




-- 案例5. 查询平均工资高于公司平均工资的部门有哪些?

# 1.公司平均工资
SELECT AVG(salary) s
FROM employees

# 2.各部门平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id

# 3.查询符合要求部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
	SELECT AVG(salary) s
	FROM employees
	);

# 4.查询部门信息
SELECT *
FROM departments
WHERE department_id IN (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary) > (
		SELECT AVG(salary) s
		FROM employees
		)
	);



-- 案例6. 查询出公司中所有 manager 的详细信息.

SELECT manager_id
FROM employees


SELECT * 
FROM employees
WHERE employee_id IN(
	SELECT manager_id
	FROM employees
	);



-- 案例8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

# 1. 各部门平均工资
SELECT AVG(salary) ag
FROM employees
G
ROUP BY department_id

# 2. 平均工资中最高工资
SELECT MAX(ag)
FROM (
	SELECT AVG(salary) ag
	FROM employees
	GROUP BY department_id
	) ag_dep


# 3. 最高平均工资的部门id
SELECT department_id
FROM employees
GROUP BY department_id 
HAVING AVG(salary) = (
	SELECT MAX(ag)
	FROM (
		SELECT AVG(salary) ag
		FROM employees
		GROUP BY department_id
		) ag_dep)


# 4. 最高平均工资的部门领导id
SELECT d.manager_id
FROM departments d
WHERE d.department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id 
	HAVING AVG(salary) = (
		SELECT MAX(ag)
		FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
			) ag_dep));


# 5. 所要查询的信息	
SELECT last_name, department_id, email, salary
FROM employees 
WHERE employee_id = (
	SELECT d.`manager_id`
FROM departments d
WHERE d.department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary)=(
		SELECT MAX(ag)
		FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
		) ag_dep))
);

分页查询

-- 分页查询

/*
应用场景：当页面上的数据，一页显示不全，则需要分页显示

分页查询的sql命令请求数据库服务器 ---> 服务器响应查询到的多条数据 ---> 前台页面



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

limit 起始条目索引，显示的条目数

执行顺序
1. from
2. join
3. on
4. where
5. group by 
7. having
8. order by
9. limit

特点：
	1. 起始条目索引如果不写，默认是0
	2. limit后面支持两个参数
	参数1：显示的其实条目索引
	参数2：条目数
	--- limit 起始索引, 条目数


公式：
假如要显示的页数是page，每页显示的条目数为size

select *
from employees
limit (page-1)*size, size

page size=10
1		limit 0,10
2		limit 10,10
3		limit 20,10
4		limit 30,10


*/


# 案例1：查询员工信息表的前5条
SELECT * FROM employees LIMIT 0,5;
等价于
SELECT * FROM employees LIMIT 5;


# 案例2：查询有奖金的，且工资较高的第11名到20名
SELECT  * 
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10, 10;

联合查询

-- 联合查询

/*
说明：当查询结果来自多张表，当多张表直接没有关联，这个时候往往使用联合查询，也称为union查询

语法：
select 查询列表 from 表1 where 筛选条件
	union
select 查询列表 from 表2 where 筛选条件

特点：
    1. 多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致
    2. union实现去重查询
       union all 实现全部查询，包含重复项
*/

# 案例： 查询中美两国的年龄>20岁的用户信息

SELECT * FROM chinese WHERE age > 20 UNION
SELECT * FROM usa WHERE uage > 20

# 查询所有国家的用户姓名和年龄

SELECT uname, uage FROM usa
UNION 
SELECT `name`, age FROM chines

# 案例3：union 自动去重/ 若想不去重，使用 union all
SELECT 1, ‘猪头‘
UNION / UNION ALL
SELECT 1, ‘猪头‘

常用DQL

原文：https://www.cnblogs.com/newbase/p/13215243.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)