MySql数据库DQL（数据查询语言）二

思维导图大纲

多表查询

1、笛卡尔集

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

– 省略连接条件
– 连接条件无效
– 所有表中的所有行互相连接
• 为了避免笛卡尔集， 可以在 WHERE 加入有 效的连接条件。

2、等值/连接连接
使用连接在多个表中查询数据

• 在 WHERE 子句中写入连接条件。
• 在表中有相同列时，在列名之前加上表名前缀

区分重复的列名

• 在不同表中具有相同列名的列可以用表的别名
加以区分。
• 如果使用了表别名，则在select语句中需要使
用表别名代替表名
• 表别名最多支持32个字符长度，但建议越少越
好

表的别名

• 使用别名可以简化查询。
• 使用表名前缀可以提高执行效率。

连接多个表

• 连接 n个表,至少需要 n-1个连接条件。 
    例如：连接三个表，至少需要两个连接条件。

连接查询案例

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

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

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

分类：

	按年代分类：
	sql92标准:仅仅支持内连接
	sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
	
	按功能分类：
		内连接：
			等值连接
			非等值连接
			自连接
		外连接：
			左外连接
			右外连接
			全外连接
		
		交叉连接
		
*/

一、sql92标准
1、等值连接

/*

① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选


*/

案例1：查询女神名和对应的男神名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;

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

SELECT last_name,department_name
FROM t_mysql_employees,t_mysql_departments
WHERE t_mysql_employees.department_id=t_mysql_departments.department_id;

2、为表起别名

/*
1、提高语句的简洁度
2、区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

*/

查询员工名、工种号、工种名

SELECT e.last_name,e.job_id,j.job_title
FROM t_mysql_employees e,t_mysql_jobs j
WHERE e.job_id=j.job_id;

3、两个表的顺序是否可以调换
查询员工名、工种号、工种名

SELECT e.last_name,e.job_id,j.job_title
FROM t_mysql_jobs j,t_mysql_employees e
WHERE e.job_id=j.job_id;

4、可以加筛选
案例1：查询有奖金的员工名、部门名

SELECT last_name,department_name,commission_pct
FROM t_mysql_employees e,t_mysql_departments d
WHERE e.department_id=d.department_id
AND e.commission_pct IS NOT NULL;

案例2：查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM t_mysql_departments d,t_mysql_locations l
WHERE d.location_id = l.location_id
AND city LIKE ‘_o%’;

5、可以加分组

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

SELECT COUNT(*) 个数,city
FROM t_mysql_departments d,t_mysql_locations l
WHERE d.location_id=l.location_id
GROUP BY city;

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

SELECT department_name,d.manager_id,MIN(salary)
FROM t_mysql_departments d,t_mysql_employees e
WHERE d.department_id=e.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id;

6、可以加排序
案例：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title,COUNT()
FROM t_mysql_employees e,t_mysql_jobs j
WHERE e.job_id=j.job_id
GROUP BY job_title
ORDER BY COUNT() DESC;

7、可以实现三表连接？
案例：查询员工名、部门名和所在的城市

SELECT last_name,department_name,city
FROM t_mysql_employees e,t_mysql_departments d,t_mysql_locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id
AND city LIKE ‘s%’
ORDER BY department_name DESC;

2、非等值连接
案例1：查询员工的工资和工资级别

SELECT salary,grade_level
FROM t_mysql_employees e,t_mysql_job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level=‘A’;

3、自连接
案例：查询 员工名和上级的名称

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM t_mysql_employees e,t_mysql_employees m
WHERE e.manager_id=m.employee_id;

3、join链接
分类

– 内连接 [inner] join on
– 外连接
	• 左外连接 left [outer] join on
	• 右外连接 right [outer] join on

使用ON 子句创建连接

• 自然连接中是以具有相同名字的列为连接条件的。
• 可以使用 ON 子句指定额外的连接条件。
• 这个连接条件是与其它条件分开的。
• ON 子句使语句具有更高的易读性。

join案例

二、sql99语法

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

分类：
内连接（★）：inner
外连接
	左外(★):left 【outer】
	右外(★)：right 【outer】
	全外：full【outer】
交叉连接：cross 

*/

一）内连接

/*
语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

分类：
等值
非等值
自连接

特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

*/

1、等值连接
案例1.查询员工名、部门名

SELECT last_name,department_name
FROM t_mysql_departments d
JOIN t_mysql_employees e
ON e.department_id = d.department_id;

案例2.查询名字中包含e的员工名和工种名（添加筛选）

SELECT last_name,job_title
FROM t_mysql_employees e
INNER JOIN t_mysql_jobs j
ON e.job_id= j.job_id
WHERE e.last_name LIKE ‘%e%’;

3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）

①查询每个城市的部门个数
②在①结果上筛选满足条件的

SELECT city,COUNT() 部门个数
FROM t_mysql_departments d
INNER JOIN t_mysql_locations l
ON d.location_id=l.location_id
GROUP BY city
HAVING COUNT()>3;

案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）

①查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM t_mysql_employees e
INNER JOIN t_mysql_departments d
ON e.department_id=d.department_id
GROUP BY department_name;

② 在①结果上筛选员工个数>3的记录，并排序

SELECT COUNT() 个数,department_name
FROM t_mysql_employees e
INNER JOIN t_mysql_departments d
ON e.department_id=d.department_id
GROUP BY department_name
HAVING COUNT()>3
ORDER BY COUNT(*) DESC;

5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）

SELECT last_name,department_name,job_title
FROM t_mysql_employees e
INNER JOIN t_mysql_departments d ON e.department_id=d.department_id
INNER JOIN t_mysql_jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;

二）非等值连接
查询员工的工资级别

SELECT salary,grade_level
FROM t_mysql_employees e
JOIN t_mysql_job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

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

SELECT COUNT(),grade_level
FROM t_mysql_employees e
JOIN t_mysql_job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING COUNT()>20
ORDER BY grade_level DESC;

三）自连接

查询员工的名字、上级的名字

SELECT e.last_name,m.last_name
FROM t_mysql_employees e
JOIN t_mysql_employees m
ON e.manager_id= m.employee_id;

查询姓名中包含字符k的员工的名字、上级的名字

SELECT e.last_name,m.last_name
FROM t_mysql_employees e
JOIN t_mysql_employees m
ON e.manager_id= m.employee_id
WHERE e.last_name LIKE ‘%k%’;

二、外连接

 /*
 应用场景：用于查询一个表中有，另一个表没有的记录
 
 特点：
 1、外连接的查询结果为主表中的所有记录
	如果从表中有和它匹配的，则显示匹配的值
	如果从表中没有和它匹配的，则显示null
	外连接查询结果=内连接结果+主表中有而从表没有的记录
 2、左外连接，left join左边的是主表
    右外连接，right join右边的是主表
 3、左外和右外交换两个表的顺序，可以实现同样的效果 
 4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
 */
 #引入：查询男朋友 不在男神表的的女神名
 
 SELECT * FROM t_mysql_beauty;
 SELECT * FROM t_mysql_boys;

左外连接
SELECT b.,bo.
FROM t_mysql_boys bo
LEFT OUTER JOIN t_mysql_beauty b
ON b.boyfriend_id = bo.id
WHERE b.id IS NULL;

案例1：查询哪个部门没有员工
左外
SELECT d.*,e.employee_id
FROM t_mysql_departments d
LEFT OUTER JOIN t_mysql_employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

加粗样式 右外

SELECT d.*,e.employee_id
FROM t_mysql_employees e
RIGHT OUTER JOIN t_mysql_departments d
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

全外

USE girls;
SELECT b.,bo.
FROM t_mysql_beauty b
FULL OUTER JOIN t_mysql_boys bo
ON b.boyfriend_id = bo.id;

交叉连接

SELECT b.,bo.
FROM t_mysql_beauty b
CROSS JOIN boys bo;

多表查询练习

1.显示所有员工的姓名，部门号和部门名称。

SELECT last_name,d.department_id,department_name
FROM t_mysql_employees e,t_mysql_departments d
WHERE e.department_id = d.department_id;

2.查询90号部门员工的job_id和90号部门的location_id

SELECT job_id,location_id
FROM t_mysql_employees e,t_mysql_departments d
WHERE e.department_id=d.department_id
AND e.department_id=90;

3. 选择所有有奖金的员工的

SELECT last_name , department_name , l.location_id , city
FROM t_mysql_employees e,t_mysql_departments d,t_mysql_locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND e.commission_pct IS NOT NULL;

4.选择city在Toronto工作的员工的

SELECT last_name , job_id , d.department_id , department_name
FROM t_mysql_employees e,t_mysql_departments d ,t_mysql_locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND city = ‘Toronto’;

5.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT department_name,job_title,MIN(salary) 最低工资
FROM t_mysql_employees e,t_mysql_departments d,t_mysql_jobs j
WHERE e.department_id=d.department_id
AND e.job_id=j.job_id
GROUP BY department_name,job_title;

6.查询每个国家下的部门个数大于2的国家编号

SELECT country_id,COUNT(*) 部门个数
FROM t_mysql_departments d,t_mysql_locations l
WHERE d.location_id=l.location_id
GROUP BY country_id
HAVING 部门个数>2;

7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

SELECT e.last_name employees,e.employee_id “Emp#”,m.last_name manager,m.employee_id “Mgr#”
FROM t_mysql_employees e,t_mysql_employees m
WHERE e.manager_id = m.employee_id
AND e.last_name=‘kochhar’;

外连接案例

一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
SELECT b.id,b.name,bo.*
FROM t_mysql_beauty b
LEFT OUTER JOIN t_mysql_boys bo
ON b.boyfriend_id = bo.id
WHERE b.id>3;
二、查询哪个城市没有部门

SELECT city
FROM t_mysql_departments d
RIGHT OUTER JOIN t_mysql_locations l
ON d.location_id=l.location_id
WHERE d.department_id IS NULL;

三、查询部门名为SAL或IT的员工信息

SELECT e.*,d.department_name,d.department_id
FROM t_mysql_departments d
LEFT JOIN t_mysql_employees e
ON d.department_id = e.department_id
WHERE d.department_name IN(‘SAL’,‘IT’);

SELECT * FROM t_mysql_departments
WHERE department_name IN(‘SAL’,‘IT’);

常见函数

字符函数

数字函数

日期函数

常见函数案例

/*

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节  2、提高代码的重用性
调用：select 函数名(实参列表) 【from 表】;
特点：
	①叫什么（函数名）
	②干什么（函数功能）

分类：
	1、单行函数
	如 concat、length、ifnull等
	2、分组函数
	
	功能：做统计使用，又称为统计函数、聚合函数、组函数
	
常见函数：
	一、单行函数
	字符函数：
	length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
	concat
	substr
	instr
	trim
	upper
	lower
	lpad
	rpad
	replace
	
	数学函数：
	round
	ceil
	floor
	truncate
	mod
	
	日期函数：
	now
	curdate
	curtime
	year
	month
	monthname
	day
	hour
	minute
	second
	str_to_date
	date_format
	其他函数：
	version
	database
	user
	控制函数
	if
	case
	
*/

一、字符函数

1.length 获取参数值的字节个数
SELECT LENGTH(‘john’);
SELECT LENGTH(‘张三丰hahaha’);

SHOW VARIABLES LIKE ‘%char%’

2.concat 拼接字符串

SELECT CONCAT(last_name,’_’,first_name) 姓名 t_mysql_FROM employees;

3.upper、lower
SELECT UPPER(‘john’);
SELECT LOWER(‘joHn’);
示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM t_mysql_employees;

4.substr、substring
注意：索引从1开始
4.1、截取从指定索引处后面所有字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’,7) out_put;
4.2、截取从指定索引处指定字符长度的字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’,1,3) out_put;

4.3、案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),’_’,LOWER(SUBSTR(last_name,2))) out_put
FROM t_mysql_employees;

5.instr 返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR(‘杨不殷六侠悔爱上了殷六侠’,‘殷八侠’) AS out_put;

6.trim

SELECT LENGTH(TRIM(’ 张翠山 ')) AS out_put;

SELECT TRIM(‘aa’ FROM ‘aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa’) AS out_put;

7.lpad 用指定的字符实现左填充指定长度

SELECT LPAD(‘殷素素’,2,’*’) AS out_put;

8.rpad 用指定的字符实现右填充指定长度

SELECT RPAD(‘殷素素’,12,‘ab’) AS out_put;

9.replace 替换

SELECT REPLACE(‘周芷若周芷若周芷若周芷若张无忌爱上了周芷若’,‘周芷若’,‘赵敏’) AS out_put;

二、数学函数

1、round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);

2、ceil 向上取整,返回>=该参数的最小整数

SELECT CEIL(-1.02);

3、floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99);

4、truncate 截断

SELECT TRUNCATE(1.69999,1);

5、mod取余

/*
mod(a,b) ：  a-a/b*b

mod(-10,-3):-10- (-10)/(-3)*（-3）=-1
*/

SELECT MOD(10,-3);
SELECT 10%3;

三、日期函数
1、now 返回当前系统日期+时间
SELECT NOW();

2、curdate 返回当前系统日期，不包含时间
SELECT CURDATE();

3、curtime 返回当前时间，不包含日期
SELECT CURTIME();

4、可以获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) 年;
SELECT YEAR(‘1998-1-1’) 年;

SELECT YEAR(hiredate) 年 FROM t_mysql_employees;

SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;

5、str_to_date 将字符通过指定的格式转换成日期

SELECT STR_TO_DATE(‘1998-3-2’,’%Y-%c-%d’) AS out_put;

6、查询入职日期为1992–4-3的员工信息

SELECT * FROM employees WHERE hiredate = ‘1992-4-3’;

SELECT * FROM employees WHERE hiredate = STR_TO_DATE(‘4-3 1992’,’%c-%d %Y’);

7、date_format 将日期转换成字符

SELECT DATE_FORMAT(NOW(),’%y年%m月%d日’) AS out_put;

8、查询有奖金的员工名和入职日期(xx月/xx日 xx年)

SELECT last_name,DATE_FORMAT(hiredate,’%m月/%d日 %y年’) 入职日期
FROM t_mysql_employees
WHERE commission_pct IS NOT NULL;

四、其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

五、流程控制函数
1.if函数： if else 的效果

SELECT IF(10<5,‘大’,‘小’);

SELECT last_name,commission_pct,IF(commission_pct IS NULL,‘没奖金，呵呵’,‘有奖金，嘻嘻’) 备注
FROM t_mysql_employees;

2.case函数的使用一： switch case 的效果

/*
java中
switch(变量或表达式){
	case 常量1：语句1;break;
	...
	default:语句n;break;


}

mysql中

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
*/

/*案例：查询员工的工资，要求

部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资

*/

SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary1.1
WHEN 40 THEN salary1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM t_mysql_employees;

3.case 函数的使用二：类似于 多重if

/*
java中：
if(条件1){
	语句1；
}else if(条件2){
	语句2；
}
...
else{
	语句n;
}

mysql中：

case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句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 AS 工资级别
FROM t_mysql_employees;

子查询

子查询案例

/*
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
	select后面：
		仅仅支持标量子查询
	
	from后面：
		支持表子查询
	where或having后面：★
		标量子查询（单行） √
		列子查询  （多行） √
		
		行子查询
		
	exists后面（相关子查询）
		表子查询
按结果集的行列数不同：
	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一列多行）
	行子查询（结果集有一行多列）
	表子查询（结果集一般为多行多列）



*/


#一、where或having后面
/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）

3、行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>

列子查询，一般搭配着多行操作符使用
in、any/some、all

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

*/

1.标量子查询★
案例1：谁的工资比 Abel 高?

①查询Abel的工资
SELECT salary
FROM t_mysql_employees
WHERE last_name = ‘Abel’

②查询员工的信息，满足 salary>①结果
SELECT *
FROM t_mysql_employees
WHERE salary>(
SELECT salary
FROM t_mysql_employees
WHERE last_name = ‘Abel’
);

案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资

①查询141号员工的job_id
SELECT job_id
FROM t_mysql_employees
WHERE employee_id = 141

②查询143号员工的salary
SELECT salary
FROM t_mysql_employees
WHERE employee_id = 143

③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②

SELECT last_name,job_id,salary
FROM t_mysql_employees
WHERE job_id = (
SELECT job_id
FROM t_mysql_employees
WHERE employee_id = 141
) AND salary>(
SELECT salary
FROM t_mysql_employees
WHERE employee_id = 143

);

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

①查询公司的 最低工资
SELECT MIN(salary)
FROM t_mysql_employees

②查询last_name,job_id和salary，要求salary=①
SELECT last_name,job_id,salary
FROM t_mysql_employees
WHERE salary=(
SELECT MIN(salary)
FROM t_mysql_employees
);

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

①查询50号部门的最低工资

SELECT MIN(salary)
FROM t_mysql_employees
WHERE department_id = 50

②查询每个部门的最低工资

SELECT MIN(salary),department_id
FROM t_mysql_employees
GROUP BY department_id

③ 在②基础上筛选，满足min(salary)>①

SELECT MIN(salary),department_id
FROM t_mysql_employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM t_mysql_employees
WHERE department_id = 50
);

非法使用标量子查询

SELECT MIN(salary),department_id
FROM t_mysql_employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT salary
FROM t_mysql_employees
WHERE department_id = 250
);

2.列子查询（多行子查询）★
案例1：返回location_id是1400或1700的部门中的所有员工姓名

①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM t_mysql_departments
WHERE location_id IN(1400,1700)

②查询员工姓名，要求部门号是①列表中的某一个

SELECT last_name
FROM t_mysql_employees
WHERE department_id <>ALL(
SELECT DISTINCT department_id
FROM t_mysql_departments
WHERE location_id IN(1400,1700)
);

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

①查询job_id为‘IT_PROG’部门任一工资

SELECT DISTINCT salary
FROM t_mysql_employees
WHERE job_id = ‘IT_PROG’

②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM t_mysql_employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM t_mysql_employees
WHERE job_id = ‘IT_PROG’

) AND job_id<>‘IT_PROG’;

#或
SELECT last_name,employee_id,job_id,salary
FROM t_mysql_employees
WHERE salary<(
SELECT MAX(salary)
FROM t_mysql_employees
WHERE job_id = ‘IT_PROG’

) AND job_id<>‘IT_PROG’;

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

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE t_mysql_salary<ALL(
SELECT DISTINCT salary
FROM t_mysql_employees
WHERE job_id = ‘IT_PROG’

) AND job_id<>‘IT_PROG’;

或

SELECT last_name,employee_id,job_id,salary
FROM t_mysql_employees
WHERE salary<(
SELECT MIN( salary)
FROM t_mysql_employees
WHERE job_id = ‘IT_PROG’

) AND job_id<>‘IT_PROG’;

3、行子查询（结果集一行多列或多行多列）
案例：查询员工编号最小并且工资最高的员工信息

SELECT *
FROM t_mysql_employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM t_mysql_employees
);

①查询最小的员工编号
SELECT MIN(employee_id)
FROM t_mysql_employees

②查询最高工资
SELECT MAX(salary)
FROM t_mysql_employees

③查询员工信息
SELECT *
FROM t_mysql_employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM t_mysql_employees
)AND salary=(
SELECT MAX(salary)
FROM t_mysql_employees
);

二、select后面

/*
仅仅支持标量子查询
*/

案例：查询每个部门的员工个数

SELECT d.*,(

SELECT COUNT(*)
FROM t_mysql_employees e
WHERE e.department_id = d.`department_id`

) 个数
FROM t_mysql_departments d;

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

SELECT (
SELECT department_name,e.department_id
FROM t_mysql_departments d
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102
) 部门名;

三、from后面

/*
将子查询结果充当一张表，要求必须起别名
*/

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

SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id;

SELECT * FROM t_mysql_job_grades;

②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal

SELECT ag_dep.*,g.grade_level
FROM (
SELECT AVG(salary) ag,department_id
FROM t_mysql_employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

四、exists后面（相关子查询）

/*
语法：
exists(完整的查询语句)
结果：
1或0
*/

SELECT EXISTS(SELECT employee_id FROM t_mysql_employees WHERE salary=300000);

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

1、in

SELECT department_name
FROM t_mysql_departments d
WHERE d.department_id IN(
SELECT department_id
FROM t_mysql_employees

)

2、exists

SELECT department_name
FROM t_mysql_departments d
WHERE EXISTS(
SELECT *
FROM t_mysql_employees e
WHERE d.department_id=e.department_id
);

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

1、in

SELECT bo.*
FROM t_mysql_boys bo
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM t_mysql_beauty
)

2、exists
SELECT bo.*
FROM t_mysql_boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM t_mysql_beauty b
WHERE bo.id=b.boyfriend_id

);

综合查询案例

一、查询每个专业的学生人数

SELECT majorid,COUNT(*)
FROM student
GROUP BY majorid;

二、查询参加考试的学生中，每个学生的平均分、最高分

SELECT AVG(score),MAX(score),studentno
FROM result
GROUP BY studentno;

三、查询姓张的每个学生的最低分大于60的学号、姓名

SELECT s.studentno,s.studentname,MIN(score)
FROM student s
JOIN result r
ON s.studentno=r.studentno
WHERE s.studentname LIKE ‘张%’
GROUP BY s.studentno
HAVING MIN(score)>60;

四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称

SELECT m.majorname,s.studentname
FROM student s
JOIN major m
ON m.majorid=s.majorid
WHERE DATEDIFF(borndate,‘1988-1-1’)>0
GROUP BY m.majorid;

五、查询每个专业的男生人数和女生人数分别是多少

SELECT COUNT(*),sex,majorid
FROM student
GROUP BY sex,majorid;

六、查询专业和张翠山一样的学生的最低分
①查询张翠山的专业编号

SELECT majorid
FROM student
WHERE studentname = ‘张翠山’

②查询编号=①的所有学生编号

SELECT studentno
FROM student
WHERE majorid=(
SELECT majorid
FROM student
WHERE studentname = ‘张翠山’
)

②查询最低分

SELECT MIN(score)
FROM result
WHERE studentno IN(
SELECT studentno
FROM student
WHERE majorid=(
SELECT majorid
FROM student
WHERE studentname = ‘张翠山’
)
)

七、查询大于60分的学生的姓名、密码、专业名

SELECT studentname,loginpwd,majorname
FROM student s
JOIN major m ON s.majorid= m.majorid
JOIN result r ON s.studentno=r.studentno
WHERE r.score>60;

八、按邮箱位数分组，查询每组的学生个数

SELECT COUNT(*),LENGTH(email)
FROM student
GROUP BY LENGTH(email);

九、查询学生名、专业名、分数

SELECT studentname,score,majorname
FROM student s
JOIN major m ON s.majorid= m.majorid
LEFT JOIN result r ON s.studentno=r.studentno

十、查询哪个专业没有学生，分别用左连接和右连接实现
左

SELECT m.majorid,m.majorname,s.studentno
FROM major m
LEFT JOIN student s ON m.majorid = s.majorid
WHERE s.studentno IS NULL;

右

SELECT m.majorid,m.majorname,s.studentno
FROM student s
RIGHT JOIN major m ON m.majorid = s.majorid
WHERE s.studentno IS NULL;

十一、查询没有成绩的学生人数

SELECT COUNT(*)
FROM student s
LEFT JOIN result r ON s.studentno = r.studentno
WHERE r.id IS NULL

分页查询

分页查询案例

/*
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
	select 查询列表
	from 表
	【join type join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段】
	limit 【offset,】size;
	
	offset要显示条目的起始索引（起始索引从0开始）
	size 要显示的条目个数
特点：
	①limit语句放在查询语句的最后
	②公式
	要显示的页数 page，每页的条目数size
	
	select 查询列表
	from 表
	limit (page-1)*size,size;
	
	size=10
	page  
	1	0
	2  	10
	3	20
	
*/

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

SELECT * FROM t_mysql_employees LIMIT 0,5;
SELECT * FROM t_mysql_employees LIMIT 5;

案例2：查询第11条——第25条
SELECT * FROM t_mysql_employees LIMIT 10,15;

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

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