MySQL（入门）第二篇：连接查询、子查询、分页查询、联合查询

　　连接查询/多表查询

sql92版本

# 内连接
#1.等值连接
#查询员工名和对应部门名
SELECT `last_name`, `department_name`
FROM `employees`, `departments`
WHERE `employees`.`department_id` = `departments`.`department_id`;
#2.非等值连接
#查询员工工资和工资级别
SELECT salary, grade_level
FROM `employees` e, `job_grades` g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#3.自连接
#查询员工名和上司名
SELECT e.`last_name`,m.`last_name`
FROM `employees` e,`employees`m
WHERE e.`manager_id`=m.`employee_id`;

sql99版本　　

/*
语法：
	select 查询列表
	from 表1 别名
	【连接类型】 join 表2 别名
	on 连接条件
	【where 筛选条件】
*/

# 内连接：inner(inner可省略)
#1.等值连接
#查询员工名、部门名
SELECT `last_name`,`department_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`;
#2.非等值连接
#查询员工工资级别
SELECT salary,`grade_level`
FROM `employees` e
JOIN `job_grades` g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#3.自连接
#查询员工名及其领导名
SELECT e.`last_name`,m.`last_name`
FROM `employees` e
JOIN `employees` m
ON e.`manager_id`=m.`employee_id`;

# 外连接：左外left，右外right，全外full
/*
用于查询一个表有，另一个表没有的记录
外连接查询结果为主表全部记录
	如果从表有和它匹配的值，则显示匹配的值
	如果从表没有有和它匹配的值，则显示null
*/
#查询哪个部门没有员工
SELECT d.*
FROM `departments` d
LEFT JOIN `employees` e
ON d.`department_id`=e.`department_id`
WHERE e.`department_id` IS NULL;


# 交叉连接：cross 使用就是笛卡尔连接
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;　　

 　　子查询

含义：出现在其他语句中的select语句，也称为内查询

 一、where或having后面
1.标量子查询（单行子查询）
2.列子查询（多行子查询）
3.行子查询（多列多行）
特点：
①子查询放在小括号里
②子查询一般放在条件的右侧
③标量子查询一般搭配单行操作符使用：> < <= >= = <>
    列子查询一般搭配多行操作符使用：in any/some all

 # 1.标量子查询
 #查询谁的工资比Abel高
 SELECT *
 FROM `employees`
 WHERE salary > (
	 SELECT salary
	 FROM `employees`
	 WHERE `last_name` = 'Abel'
);
#返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id，工资
SELECT `last_name`, `job_id`, salary
FROM `employees`
WHERE job_id=(
	SELECT job_id
	FROM `employees`
	WHERE `employee_id`=141
) AND salary>(
	SELECT salary
	FROM `employees`
	WHERE `employee_id`=143
);
#返回公司工资最少的员工的last_name,job_id和salary
SELECT `last_name`,`job_id`,salary
FROM `employees`
WHERE salary=(
	SELECT MIN(salary)
	FROM `employees`
);
#查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT MIN(salary),`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM `employees`
	WHERE `department_id`=50
);

# 2.列子查询
#返回location_id是1400或1700的部门中的所有员工姓名
SELECT `last_name`
FROM `employees`
WHERE `department_id` IN (
	SELECT DISTINCT `department_id`
	FROM `departments`
	WHERE `location_id` IN(1400,1700)
);

二.select后面

　　仅支持标量子查询

#查询每个部门的员工数
SELECT d.*, (
	SELECT COUNT(*)
	FROM `employees` e
	WHERE e.`department_id` = d.`department_id`
) 个数
FROM `departments` d;

三.from后面　　

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

#查询每个部门平均工资的工资等级
SELECT ag_dep.*,`grade_level`
FROM (
	SELECT AVG(salary) ag,`department_id`
	FROM `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 `department_name`
FROM `departments` d
WHERE EXISTS(
	SELECT *
	FROM `employees` e
	WHERE d.`department_id`=e.`department_id`
);　　

　　分页查询

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

#查询前5条员工信息
SELECT *
FROM `employees`
LIMIT 0,5;　　　　

　　联合查询

/*
union 联合 合并：将多条查询语句结果合并成一个结果
应用场景：
	要查询结果来自多个表，且多个表没有直接联系，但要查询信息一致时
注意：
	1.要求多条查询语句的查询列数一致
	2.要求多条查询语句的查询列的类型和顺序最好一致
	3.union关键字默认去重，如果使用union all可以包含重复项
*/

#查询部门编号>90或邮箱变化和包含a的员工信息
SELECT * FROM `employees` WHERE `email` LIKE '%a%'
UNION
SELECT * FROM `employees` WHERE `department_id`>90;