数据库-进阶6-连接查询

进阶6-连接查询

# 进阶6：连接查询
/*
	含义：又称为多表查询，当查询的字段来自多个表时，就会用到连接查询
	
	笛卡尔乘积现象：表1有 m 行，表2有 n 行，结果= m*n 行
	发生原因：没有有效的连接条件
	如何避免：添加有效的连接条件
	
	分类：
		按年代分类：
		sql92 标准：仅仅支持内连接
		sql99 标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
		
		按功能分类：
			内连接：
				等值连接：
				非等值连接：
				自连接：
			外连接：
				左外连接：
				右外连接：
				全外连接：
			交叉链接：
				
*/
	SELECT NAME,boyName 
	FROM boy,beauty
	WHERE boy.boyfriend_id = beauty.id;

	#一、sql92标准
		#1.等值连接
			/*
				①：多表等值连接的结果为多表的交集部分
				②：n 表连接，至少需要 n-1 个连接条件
				③：多表的顺序没有要求
				④：一般需要为表起别名
				⑤：可以搭配前面介绍的所有字句使用，比如排序、分组、筛选
			*/
				#案例1：查询女神名和对应的男神名
					SELECT NAME,boyName
					FROM employees
					WHERE boy.boyfriend_id = beauty.id;
				#案例2：查询员工名和对应的部门名
					SELECT last_name,department_id
					FROM employees,departments
					WHERE employees.department_id = departments.department_id;
			#2.为表起别名
			/*
				① 提高语句的简洁度
				② 区分多个重名字段
				
				注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
			*/
				# 查询员工名、工种号、工种名
					# 无别名
					SELECT last_name,employees.job_id,job.title
					FROM employees,jobs
					WHERE employees.id = jobs.id;
					# 有别名
					SELECT e.last_name,e.job_id,j.title
					FROM employees e,jobs j
					WHERE e.id = j.id;
			#3.两个表的顺序是否可以做调换(可以)
				# 查询员工名、工种号、工种名
					SELECT e.last_name,e.job_id,j.title
					FROM jobs j,employees e
					WHERE e.id = j.id;
			#4.可以加筛选？
				#案例1：查询有奖金的员工名和部门名
					SELECT last_name,department_name,commission_pct
					FROM employees e,departments d
					WHERE e.department_id = d.department_id
					AND e.commission_pct IS NOT NULL;
				#案例2：查询城市名中第二个字符为 o 的部门名和城市名	
					SELECT department_name,city
					FROM departments d,locations l
					WHERE d.location_id = l.location_id
					AND city LIKE '_o%';
			#5.可以加分组
				#案例1.查询每个城市的部门个数
					SELECT COUNT(*) 个数,city
					FROM departments d,locations l
					WHERE d.location_id = l.location_id
					GROUP BY 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;
			#6.可以加排序
				#案例：查询每个工种的工种名和员工的个数，并按员工的个数降序
					SELECT job_title,COUNT(*)
					FROM employees e,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 employees e,departments d,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_velel
				FROM employees e,job_grades j
				WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
				AND g.grand_level = 'A';
			
				SELECT * FROM job_grades;
				CREATE TABLE job_grades
				(grade_velel VARCHAR(3),
				lowest_sal INT,
				highest_sal INT);
				
				INSERT INTO job_grades
				VALUE ('A',1000,2999);
			
				INSERT INTO job_grades
				VALUE ('B',3000,5999);
				
				INSERT INTO job_grades
				VALUE ('C',6000,9999);
				
				INSERT INTO job_grades
				VALUE ('D',10000,14999);
				
				INSERT INTO job_grades
				VALUE ('E',15000,24999);
				
				INSERT INTO job_grades
				VALUE ('F',25000,40000);
		#3.自连接
			#案例：查询员工名和上级的名称
				SELECT e.employees_id,e.last_name,m.employees_id,m.last_name
				FROM employees e,employees m
				WHERE e.employees = m.employees;
		#例题1：显示员工表的最大工资、平均工资
			SELECT MAX(salary),AVG(salary)
			FROM employees;
		#例题2：查询员工的 employees_id,job_id,last_name,按照 department_id 升序，salary 降序	
			SELECT employees_id,job_id,last_name FROM employees ORDER BY department_id DESC,salary ASC;
		#例题3：查询员工表中的job_id中包含 a 和 e 的，并且 a 在 e 的前面
			SELECT job_id FROM employees WHERE job_id LIKE '%a%e';
		#例题4：已知表 student，里面有 id(学号)，name，grandId(年级编号)
		#       已知表 grade，里面有 id(年级编号)，name(年纪名)
		#	已知表 result，里面有 id，score，studentNo(学号)
		#	要求查询姓名、年级名、成绩
			SELECT s.name,g.name,r.score
			FROM student s,grade g,result r
			WHERE s.id = r.studentNo.id
			AND s.grandId = g.id;
		#例题5：显示当前日期，以及去前后空格，截取子字符串的函数
			SELECT NOW();
			SELECT TRIM(字符串 FROM '');
			SELECT SUBSTR(str,startIndex);
			SELECT SUBSTR(str,startIndex,LENGTH);
		#例题6：查询90号部门员工的job_id和90号部门的locatioin_id
			SELECT e.job_id,d.location_id
			FROM employees e,departmrnts d
			WHERE e.department_id = d.department_id
			AND e_department_id = 90;
		#例题7：选择所有有奖金的员工的 last_name,department_name,location_id,city
			SELECT last_name,department_name,location_id,city
			FROM employees e,departments d,locations l
			WHERE e.department_id = d.department_id
			AND l.location_id = d.location_id
			AND commission_pct IS NOT NULL;
		#例题8：选择 city 在 Toronto 工作的员工的 last_name,job_id,department_id,department_name
			SELECT last_name,job_id,d.department_id,department_name
			FROM employees e,departments d,locations l
			WHERE e.department_id = d.department_id
			AND l.location_id = d.location_id
			AND city = 'Toronto';
		#例题9：查询每个工种、每个部门的部门名、工种名和最低工资
			SELECT department_name,job_title,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 department_name,job_title
		#例题10：查询每个国家下的部门个数大于2的国家编号
			SELECT country_id,COUNT(*) AS 部门个数
			FROM employees e,locations l
			WHERE e.location_id = l.location_id
			GROUP BY country_id,department_id
			HAVING COUNT(*) > 2
			#或者
			HAVING 部门个数 > 2#不建议
		#例题11：选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式	
		#	employees		Emp#		manager		Mgr#
		#	kochhar			101		king		100
			SELECT e.last_name employees,e.employees_id "Emp#",m.last_name manager,m.employees_id "Mgr#"
			FROM employees e,employees m
			WHERE m.employees_id = e.manager_id
			AND last_name = 'kochhar';
	#二、sql99 语法
	/*
	语法：
		select 查询列表
		from 表1 别名 【连接类型】
		join 表2 别名
		on 连接条件
		【where 筛选条件】
		【group by 分组】
		【having 筛选条件】
		【order by 排序列表】
	
	分类：
		内连接【※】重要：inner
		外连接：
			左外【※】重要：left【outer】
			右外【※】重要：right【outer】
			全外：full【outer】
		交叉连接：cross join
		
	*/
	# 一）内连接
	/*
	语法：
		select 查询列表
		from 表1 别名
		inner join 表2 别名
		on 连接条件；
		
	分类：
		等值：
		非等值：
		自连接：
		
		特点：
			①：添加排序，分组，筛选
			②：inner可以省略
			③：筛选条件放在 where 后面，连接条件放在 on 后面，提高分离性，便于阅读。
			④：inner join 连接和 sql92语法中的等值连接效果是一样的，都是查询多表的交集
	*/
		#1.等值连接
			#案例1：查询员工名，部门名（调换位置）
				SELECT last_name,department_name
				FROM employees e
				INNER JOIN departments d
				ON e.department_id = d.department_id;
			#案例2：查询名字中包含 e 的员工名和工种名（添加筛选）
				SELECT last_name,job_title
				FROM employees e
				INNER JOIN jobs j
				ON e.job_id = j.job_id
				WHERE last_name LIKE '%e%';
			#案例3：查询部门个数 > 3 的城市名和部门个数，（添加分组和筛选）
			
				# ①查询每个城市的部门个数
				# ②在①结果上筛选满足条件的
				SELECT city，count(*) 部门个数
				FROM departments d
				INNER JOIN locations l
				ON d.location_id = l.location_id
				GROUP BY city
				HAVING COUNT(*) > 3;
				
			#案例4：查询哪个部门的部门员工个数 >3 的部门名和部门个数，并按个数降序（添加排序）
			
				# ①查询每个员工的员工个数
				SELECT COUNT(*),department_name
				FROM employees e
				INNER JOIN departments d
				ON e.department_id = d.department_id
				GROUP BY department_name;
				# ②在①的结果上筛选员工个数 >3 的记录，并排序
				SELECT COUNT(*),department_name
				FROM employees e
				INNER JOIN 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 employees e
				INNER JOIN departments d ON e.department_id = d.department_id
				INNER JOIN jobs j ON e.job_title = j.job_title
				ORDER BY department_name DESC;
		
		#二）非等值连接	
			#查询员工的工资级别
				SELECT COUNT(*),grade_level
				FROM employees e
				INNER JOIN job_grades g
				ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;
				GROUP BY grade_level
				HAVING COUNT(*) > 20
				ORDER BY grader_level DESC;
			#查询工资级别个数 >2 的个数，并且按工资级别降序	
		
		#三）自连接
			#查询员工的名字，上级的名字
				SELECT e.last_name,m.last_name
				FROM employees e
				INNER JOIN employees m
				ON e.manager_id = m.employees_id;
			#查询字符中包含 k 的员工的名字，上级的名字
				SELECT e.last_name,m.last_name
				FROM employees e
				INNER JOIN employees m
				ON e.manager_id = m.employees_id
				WHERE e.last_name LIKE '%k%';
	#二、外连接
	/*
		应用场景：用于查询一个表中有，另一个表中没有的记录
		特点：
			1.外连接的查询结果为主表中的所有记录
				如果表中有和它匹配的，则显示匹配的值。
				如果从表中没有和它匹配的，则显示 null
				外连接查询结果=内连接查询结果+主表中有而从表中没有的记录
			2.左外连接：left join 左边的是主表
			  右外连接：right join 右边的是主表
			3.左外和右外交换两个表的顺序，可以实现同样的效果  	
			4.全外连接=内连接的结果+表 1 中有但表 2 没有的+表 2 中有，但表 1 中没有的
	*/
		#引入：查询男朋友  不在男神表的女神名
			# 左外连接
				SELECT b.name
				FROM beauty b
				LEFT OUTER JOIN boys bo
				ON b.boyfriend_id = bo.id
				WHERE bo.id IS NULL;
			UPDATE boys SET userup = NULL WHERE id = 3;	
			# 右外连接
				SELECT b.name
				FROM boys bo
				RIGHT OUTER JOIN beauty b
				ON b.boyfriend_id = bo.id
				WHERE bo.id IS NULL;
			#案例1：查询哪个部门没有员工
			#左外
				SELECT d.*,e.employee_id
				FROM departments d
				LEFT OUTER JOIN employess e
				ON d.department_id = e.department_id
				WHERE e.employee_id IS NULL;
			#右外
				SELECT d.*,e.employee_id
				FROM employess e
				RIGHT OUTER JOIN departments d
				ON d.department_id = e.department_id
				WHERE e.employee_id IS NULL;
			#全外：
				USE girls;
				SELECT b.*,bo.*
				FROM beauty b
				FULL OUTER JOIN boys bo
				ON b.boyfriend_id = bo.id;
			#交叉连接：
				SELECT b.*,bo.*
				FROM beauty b
				CROSS OUTER JOIN boys bo;
	#一、查询编号 > 3 的女神的男朋友信息，如果有则列出详细信息，如果没有，则用 null 填充
		SELECT b.id,b.name,bo.*
		FROM beauty b
		LEFT OUTER JOIN boys bo
		ON b.boyfriend_id = bo.id;
	#二、查询哪个城市没有部门
		SELECT city
		FROM departments d
		RIGHT OUTER JOIN locations l
		ON d.location_id = d.location_id
		WHERE d.department_id IS NULL;
	#三、查询部门名为 SAL 或者 IT 的员工信息
		SELECT e.*,d.department_id
		FROM departments d
		LEFT JOIN employees e
		ON e.department_id = d.department_id
		WHERE d.department_name IN ('SAL','IT');