MySQL学习笔记5（连接查询及子查询）

一、非等值查询

#案例1：查询名字中第三个字符为a，第五个字符为e的员工的工资以及对应的工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE e.salary BETWEEN g.lowest_sal ANDg.highest_sal
AND e.last_name LIKE ‘__a_e%’;

二、连接

1、内连接

join连接,属于sql99语法
分类：
	内连接：[inner] join on
	外连接：
			左外连接 left 【outer】 join on
			右外连接 right 【outer】 join on
	全外连接 full 【outer】 join on   ，

mysql中不支持！！！ sqlserver 和oracle 没有问题

语法：
	select 字段1，字段2，...
	from 表1
	inner join 表2 on 连接条件;

特点：
	① 效果和等值连接是一样的！
	② 表的顺序可以调换
	③ 可以为表起别名
	④ 可以添加筛选、分组、排序
	⑤ 将筛选条件和连接条件实现了分类，提高代码的可读性
	⑥ n表连接，至少需要n-1 个连接条件

#案例1 ：查询员工名、部门名
SELECT last_name,department_name
FROM departments d
JOIN employees e ON e.department_id=d.department_id;

2、外链接

语法：
	select 字段1，字段2，...
	from 表1
	left|right 【outer】 join 表2
	on 连接条件

特点：
	①查询结果：内连接的结果+主表中有但从表没有的记录（从表的字段用null填充）
	②左连接，左边的就是主表； 右连接，右边的就是主表
	③一般来讲要查询的字段来自于哪个表，那哪个表就是主表
	④一般用于查询主表中有但从表中没有的记录

#案例1：查询没有男朋友的女神名称
SELECT name,boyName
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;

3、全外连接和自连接

#全外连接
SELECT j.job_id,e.employee_id
FROM employees e
FULL OUTER JOINjobs j ON e.job_id=j.job_id

#自连接
#案例：查询员工名和上级领导的名字
SELECT e.last_name,m.last_name
FROM employees e
INNER JOINemployees m ONe.manager_id=m.employee_id;
等价于：
SELECT e.last_name,m.last_name
FROM employees e,employees m WHERE e.manager_id=m.employee_id;

三、子查询

1、子查询

子查询概念：
	嵌套在另一个查询中的查询语句称为子查询，外部的查询称为主查询，类似于java中的内部类
语法
	select  字段1，字段2
	 from 表1 
	where  Id  in(
   		select 字段1  from  表2
	)

注意事项：
1、子查询放在小括号内
2、放在条件右侧
3、子查询优先于主查询执行

分类：
	单行子查询：子查询的结果只有一个值，使用单行操作符（> < >= <= = <>）
	多行子查询:使用多行操作符（any、all、in、not in）

2、单行子查询

#案例1：谁的工资比Abel高

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

#②查询员工的信息满足工资>①的结果
SELECT * FROM employees
WHERE salary>(
SELECT salary FROM employees WHERE last_name = 'Abel'
);

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

#①查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=50

#②查询每个部门的最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id

#③筛选最低工资>①
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary) FROM employees WHERE department_id=50
);

3、多行子查询

#案例1：返回location_id是1400或1700的部门中的所有员工姓名

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

#②查询department_id满足①结果的员工姓名
SELECT last_name FROM employees
WHERE department_id IN(
SELECT department_id FROM departments
WHERE location_id IN(1400,1700)
)

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

#①查询job_id为‘IT_PROG’部门工资
SELECT salary
FROM employees
WHERE job_id = ‘IT_PROG’

#②返回其它部门中，工资<any ①的结果
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT MAX(salary)
FROM employees
WHERE job_id = ‘IT_PROG’
)AND job_id<>‘IT_PROG’;

#题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
SELECT salary
FROM employees
WHERE job_id = ‘IT_PROG’
)AND job_id<>‘IT_PROG’;