小徐带你了解sql99总结以及相关练习题重点难点

内连接：

内连接特点

外连接

外连接特点

交叉连接

交叉连接特点:

	类似于笛卡尔乘积

子查询 含义

分类：

示例

where或having后面1：标量子查询

查询最低工资的员工姓名和工资

SELECT last_name, salary FROM employees WHERE salary =( SELECT min( salary ) FROM employees );

查询所有是领导的员工姓名 重点

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

分页查询应用场景

语法：

子查询经典案例 重点难点

案例一：查询工资最低的员工信息last_name，salary

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

案例二：查询平均工资最低的部门信息 重点难点

#第一种方式使用limit
SELECT * FROM departments WHERE department_id =( SELECT department_id FROM employees GROUP BY department_id ORDER BY avg( salary ) ASC LIMIT 1 );
#第二种方式
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 
	));

案例三：查询平均工资最低的部门信息和该部门的平均工资 重点难点

SELECT
	* 
FROM
	( SELECT AVG( salary ), department_id id FROM employees GROUP BY department_id ORDER BY AVG( salary ) ASC LIMIT 0, 1 ) avg_temp
	INNER JOIN departments ON departments.department_id = avg_temp.id;

案例四：查询平均工资最高的job信息

SELECT * FROM jobs WHERE job_id =( SELECT job_id FROM employees GROUP BY job_id ORDER BY AVG( salary ) DESC LIMIT 1 );

案例五：查询平均工资高于公司平均工资的部门有哪些？

SELECT AVG( salary ), department_id FROM employees GROUP BY department_id HAVING AVG( salary ) >( SELECT AVG( salary ) FROM employees );

案例六：查询公司中所有manager的详细信息

SELECT
	* 
FROM
	employees e 
WHERE
	e.employee_id IN ( SELECT DISTINCT manager_id FROM employees );

案例七：各个部门中，最高工资中最低的那个部门的最低工资多少

SELECT min( salary ), department_id FROM employees WHERE department_id =( SELECT department_id FROM employees GROUP BY department_id ORDER BY MAX( salary ) LIMIT 1 );

案例八：查询平均工资最高的部门的manager的详细信息，last_name，department_id，email，salary

SELECT
	last_name,
	d.department_id,
	email,
	salary 
FROM
	employees e
	INNER JOIN departments d ON e.employee_id = d.manager_id 
WHERE
	d.department_id =(
	SELECT
		department_id 
	FROM
		employees 
	GROUP BY
		department_id 
	ORDER BY
		AVG( salary ) DESC 
	LIMIT 1 
	);

练习题：

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

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
	studentname,
	majorname 
FROM
	student s
	JOIN major m ON s.majorid = m.majorid 
WHERE
	datediff ( borndate, '1988-1-1' )> 0;

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

#方式一
SELECT COUNT(*) '个数',sex,majorid
FROM student GROUP BY sex,majorid;
#方式二
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 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 m.majorid = s.majorid
	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(*),score
FROM student s
LEFT JOIN result r
on r.studentno=s.studentno WHERE r.id is null;