DQL数据查询语言（下）

1. DQL数据查询语言（下）

1.1 连接查询

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

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行。发生原因：没有有效的连接条件。如何避免：添加有效的连接条件。

按年代分类：

• sql92标准：仅仅支持内连接。
• sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接。

按功能分类：

• 内连接：等值连接、非等值连接、自连接。
• 外连接：左外连接、右外连接、全外连接。
• 交叉连接。

1.1.1 sql92标准

1.1.1.1 等值连接

/*
等值连接特点：
    多表等值连接的结果为多表的交集部分。
    n表连接，至少需要n-1个连接条件。
    多表的顺序没有要求。
    一般需要为表起别名。
    可以搭配前面介绍的所有子句使用，比如排序、分组、筛选。
*/
#案例1：查询女神名和对应的男神名
select
	NAME,
	boyName
from
	boys,
	beauty
where
	beauty.boyfriend_id = boys.id;

#案例2：查询员工名和对应的部门名
select
	last_name,
	department_name
from
	employees,
	departments
where
	employees.`department_id` = departments.`department_id`;

#1.为表起别名
/*
优点：
    提高语句的简洁度。
    区分多个重名的字段。
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定。
*/
#查询员工名、工种号、工种名
select
	e.last_name,
	e.job_id,
	j.job_title
from
	employees e,
	jobs j
where
	e.`job_id` = j.`job_id`;

#2.两个表的顺序是否可以调换
#查询员工名、工种号、工种名
select
	e.last_name,
	e.job_id,
	j.job_title
from
	jobs j,
	employees e
where
	e.`job_id` = j.`job_id`;

#3.可以加筛选
#案例：查询有奖金的员工名、部门名
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%';

#4.可以加分组
#案例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`;

#5.可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
select
	job_title,
	count(*)
from
	employees e,
	jobs j
where
	e.`job_id` = j.`job_id`
group by
	job_title
order by
	count(*) desc;

#6.可以实现三表连接？
#案例：查询员工名、部门名和所在的城市
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;

1.1.1.2 非等值连接

#案例1：查询员工的工资和工资级别
select
	salary,
	grade_level
from
	employees e,
	job_grades g
where
	salary between g.`lowest_sal` and g.`highest_sal`
	and g.`grade_level` = 'A';

1.1.1.3 自连接

#案例：查询员工名和上级的名称
select
	e.employee_id,
	e.last_name,
	m.employee_id,
	m.last_name
from
	employees e,
	employees m
where
	e.`manager_id` = m.`employee_id`;

1.1.2 sql99语法

语法：

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

分类：

• 内连接：inner

• 外连接：左外：left【outer】、右外：right【outer】、全外：full【outer】

• 交叉连接：cross

1.1.2.1 内连接

/*
语法：
	select 
    	查询列表
    from 
    	表1 别名
    inner join 表2 别名 on 
    	连接条件;

分类：等值、非等值、自连接。

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

#1.等值连接
#案例1.查询员工名、部门名
select
	last_name,
	department_name
from
	departments d
join employees e 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
	e.`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
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_id` = j.`job_id`
order by
	department_name desc;

#2.非等值连接
#案例1.查询员工的工资级别
select
	salary,
	grade_level
from
	employees e
join job_grades g on
	e.`salary` between g.`lowest_sal` and g.`highest_sal`;

#案例2.查询工资级别的个数>20的个数，并且按工资级别降序
select
	count(*),
	grade_level
from
	employees e
join 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;

#3.自连接
#案例1.查询员工的名字、上级的名字
select
	e.last_name,
	m.last_name
from
	employees e
join employees m on
	e.`manager_id` = m.`employee_id`;

#案例2.查询姓名中包含字符k的员工的名字、上级的名字
select
	e.last_name,
	m.last_name
from
	employees e
join employees m on
	e.`manager_id` = m.`employee_id`
where
	e.`last_name` like '%k%';

1.1.2.2 外连接

/*
 应用场景：用于查询一个表中有，另一个表没有的记录。
 
 特点：
     1、外连接的查询结果为主表中的所有记录，
        如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null。
        外连接查询结果=内连接结果+主表中有而从表没有的记录。
     2、左外连接，left join左边的是主表。右外连接，right join右边的是主表。
     3、左外和右外交换两个表的顺序，可以实现同样的效果。
     4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的。
 */
#案例1：查询哪个部门没有员工
#左外
select
	d.*,
	e.employee_id
from
	departments d
left outer join employees e on
	d.`department_id` = e.`department_id`
where
	e.`employee_id` is null;

#右外
select
	d.*,
	e.employee_id
from
	employees e
right outer join departments d on
	d.`department_id` = e.`department_id`
where
	e.`employee_id` is null;

#全外，MySQL不支持全外连接
select
	b.*,
	bo.*
from
	beauty b
full outer join boys bo on
	b.`boyfriend_id` = bo.id;

1.1.2.3 交叉连接

select
	b.*,
	bo.*
from
	beauty b
cross join boys bo;

1.2 子查询

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

分类：

• 按子查询出现的位置：

  select后面：仅仅支持标量子查询。

  from后面：支持表子查询。

  where或having后面：标量子查询（单行） 、列子查询 （多行） 、行子查询。

  exists后面（相关子查询）：表子查询。

• 按结果集的行列数不同：

  标量子查询（结果集只有一行一列）

  列子查询（结果集只有一列多行）

  行子查询（结果集有一行多列）

  表子查询（结果集一般为多行多列）

1.2.1 where或having后面

/*
特点：
    子查询放在小括号内。
    子查询一般放在条件的右侧。
    标量子查询，一般搭配着单行操作符使用：> < >= <= = <>。
    列子查询，一般搭配着多行操作符使用：in、any/some、all。
    子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果。
*/
#1.标量子查询
#案例1：谁的工资比Abel高
#①查询Abel的工资
select
	salary
from
	employees
where
	last_name = 'Abel';

#②查询员工的信息，满足salary>①结果
select
	*
from
	employees
where
	salary > (
	select
		salary
	from
		employees
	where
		last_name = 'Abel' );

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
#①查询141号员工的job_id
select
	job_id
from
	employees
where
	employee_id = 141;

#②查询143号员工的salary
select
	salary
from
	employees
where
	employee_id = 143;

#③查询员工的姓名，job_id和工资，要求job_id=①并且salary>②
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 );

#案例3：返回公司工资最少的员工的last_name,job_id和salary
#①查询公司的最低工资
select
	min(salary)
from
	employees

#②查询last_name,job_id和salary，要求salary=①
select
	last_name,
	job_id,
	salary
from
	employees
where
	salary = (
	select
		min(salary)
	from
		employees );

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#①查询50号部门的最低工资
select
	min(salary)
from
	employees
where
	department_id = 50

#②查询每个部门的最低工资
select
	min(salary),
	department_id
from
	employees
group by
	department_id

#③在②基础上筛选，满足min(salary)>①
select
	min(salary),
	department_id
from
	employees
group by
	department_id
having
	min(salary) > (
	select
		min(salary)
	from
		employees
	where
		department_id = 50 );

#非法使用标量子查询
select
	min(salary),
	department_id
from
	employees
group by
	department_id
having
	min(salary) > (
	select
		salary
	from
		employees
	where
		department_id = 250 );

#2.列子查询（多行子查询）
#案例1：返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
select
	distinct department_id
from
	departments
where
	location_id in (1400, 1700);

#②查询员工姓名，要求部门号是①列表中的某一个
select
	last_name
from
	employees
where
	department_id in (
	select
		distinct department_id
	from
		departments
	where
		location_id in (1400,1700) );

#案例2：返回其它工种中比job_id为IT_PROG工种任一工资低的员工的员工号、姓名、job_id 以及salary
#①查询job_id为IT_PROG部门任一工资
select
	distinct salary
from
	employees
where
	job_id = 'IT_PROG'

#②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
select
	last_name,
	employee_id,
	job_id,
	salary
from
	employees
where
	salary < any (
	select
		distinct salary
	from
		employees
	where
		job_id = 'IT_PROG' )
	and job_id <> 'IT_PROG';

#或
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';

#案例3：返回其它部门中比job_id为IT_PROG部门所有工资都低的员工的员工号、姓名、job_id以及salary
select
	last_name,
	employee_id,
	job_id,
	salary
from
	employees
where
	salary < all (
	select
		distinct salary
	from
		employees
	where
		job_id = 'IT_PROG' )
	and job_id <> 'IT_PROG';

#或
select
	last_name,
	employee_id,
	job_id,
	salary
from
	employees
where
	salary < (
	select
		min(salary)
	from
		employees
	where
		job_id = 'IT_PROG' )
	and job_id <> 'IT_PROG';

#3、行子查询（结果集一行多列或多行多列）
#案例：查询员工编号最小并且工资最高的员工信息
select
	*
from
	employees
where
	(employee_id,
	salary) = (
	select
		min(employee_id),
		max(salary)
	from
		employees );

#①查询最小的员工编号
select
	min(employee_id)
from
	employees

#②查询最高工资
select
	max(salary)
from
	employees

#③查询员工信息
select
	*
from
	employees
where
	employee_id = (
	select
		min(employee_id)
	from
		employees )
	and salary = (
	select
		max(salary)
	from
		employees );

1.2.2 select后面

/*
特点：仅仅支持标量子查询
*/
#案例1：查询每个部门的员工个数
select
	d.*,
	(
	select
		count(*)
	from
		employees e
	where
		e.department_id = d.`department_id` ) 个数
from
	departments d;

#案例2：查询员工号=102的部门名
select
	(
	select
		department_name
	from
		departments d
	inner join employees e on
		d.department_id = e.department_id
	where
		e.employee_id = 102 ) 部门名;

1.2.3 from后面

/*
将子查询结果充当一张表，要求必须起别名
*/
#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
select
	avg(salary),
	department_id
from
	employees
group by
	department_id 

#②连接①的结果集和job_grades表，筛选条件平均工资：between lowest_sal and highest_sal
select
	ag_dep.*,
	g.`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;

1.2.4 exists后面

/*
语法：exists(完整的查询语句)
结果：1或0
*/
select
	exists(
		select employee_id
	from
		employees
	where
		salary = 300000);

#案例1：查询有员工的部门名
#in
select
	department_name
from
	departments d
where
	d.`department_id` in (
	select
		department_id
	from
		employees )

#exists
select
	department_name
from
	departments d
where
	exists(
	select
		*
	from
		employees e
	where
		d.`department_id` = e.`department_id` );

#案例2：查询没有女朋友的男神信息
#in
select
	bo.*
from
	boys bo
where
	bo.id not in (
	select
		boyfriend_id
	from
		beauty )

#exists
select
	bo.*
from
	boys bo
where
	not exists(
	select
		boyfriend_id
	from
		beauty b
	where
		bo.`id` = b.`boyfriend_id` );

1.3 分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求。

语法：

select 
	查询列表
from 
	表
【join type join 表2 on 
	连接条件
where 
	筛选条件
group by 
	分组字段
having 
	分组后的筛选
order by 
	排序的字段】
limit 【offset,】 size;

/*
特点：
	limit语句放在查询语句的最后，offset要显示条目的起始索引（起始索引从0开始），size要显示的条目个数。
	公式：要显示的页数page，每页的条目数size，limit (page-1)*size, size;
*/
#案例1：查询前五条员工信息
SELECT * FROM employees LIMIT 0, 5;
SELECT * FROM employees LIMIT 5;

#案例2：查询第11条至第25条
SELECT * FROM employees LIMIT 10, 15;

#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
select
	*
from
	employees
where
	commission_pct is not null
order by
	salary desc
limit 10;

1.4 联合查询

union：联合、合并。将多条查询语句的结果合并成一个结果。

语法：

查询语句1
union
查询语句2
union
...

应用场景：查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

特点：

• 要求多条查询语句的查询列数是一致的。
• 要求多条查询语句的查询的每一列的类型和顺序最好一致。
• union关键字默认去重，如果使用union all可以包含重复项。

#案例1：查询部门编号>90或邮箱包含a的员工信息
select
	*
from
	employees
where
	email like '%a%'
	or department_id > 90;
#--------------------------------------------------------
select
	*
from
	employees
where
	email like '%a%'
union select
	*
from
	employees
where
	department_id > 90;

#案例2：查询中国用户中男性的信息以及外国用户中年男性的用户信息
select
	id,
	cname
from
	t_ca
where
	csex = '男'
union all select
	t_id,
	tname
from
	t_ua
where
	tGender = 'male';