文章目录
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';