9、连接查询

一、连接查询

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

2、笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

分类：

按年代分类：

sql92标准：仅仅支持内连接，对外连接的支持不太友好

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

按功能分类：

内连接：

等值连接

非等值连接

自连接

外连接：

左外连接

右外连接

全外连接

交叉连接

 

二、sql92标准

1、等值连接

/* 为表起别名：

1、提高语句的简洁度

2、区分多个重名的字段 注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

/*
为表起别名：
    1、提高语句的简洁度
    2、区分多个重名的字段
    注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
# 查询女神名和对应的男神名
select b.name,a.boyName
from boys a, beauty b
where a.id=b.boyfriend_id;

# 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
select e.last_name,d.department_name,d.manager_id,min(e.salary) 最低工资
from departments d,employees e
where d.department_id = e.department_id and e.commission_pct is not null
group by d.department_name,d.manager_id;

2、非等值连接

# 查询员工的工资和工资级别
select e.salary,j.grade_level
from employees e,job_grades j
where e.salary between j.lowest_sal and j.highest_sal;

3、自连接

在同一张表中进行连接查询数据

# 查询员工名和上级领导名称
select e.last_name 员工名称,e.employee_id 员工编号,f.last_name 领导名称,f.employee_id 领导编号
from employees e,employees f
where e.manager_id=f.employee_id;

 

三、sql99标准

1、内连接：inner joni....on（实际使用时inner可以省略不写）

# 1、等值连接 
# 查询员工名和部门名
select last_name,department_name
from employees e
inner join departments d
on e.department_id = d.department_id;
# 查询员工名、部门名、工种名，并按部门名降序
select e.last_name,e.department_id,j.job_title,d.department_name
from employees e
inner join jobs j on e.job_id=j.job_id
inner join departments d on e.department_id = d.department_id
order by d.department_name desc;

# 2、非等值连接
# 查询员工的工资级别
select e.salary,j.grade_level
from employees e
inner join job_grades j
on e.salary between j.lowest_sal and highest_sal;

# 3、自连接
# 查询员工名和上级领导名称
select e.last_name 员工名称,e.employee_id 员工编号,f.last_name 领导名称,f.employee_id 领导编号
from employees e
inner join employees f
on e.manager_id=f.employee_id;.

2、外连接

语法：

左连接：select * from 主表 left join 从表 on 主表.字段=从表.字段;

右连接：select * from 从表 right join 主表 on 从表.字段=主表.字段;

全外连接：select * from 表A full outer join 表B on 表A.字段=表B.字段;

特点：

1、外连接的查询结果为主表中的所有记录。

如果从表中有喝它匹配的，则显示匹配的值。

如果从表中没有和他匹配的，则显示null。

外连接查询结果=内连接结果+主表中有而从表没有的记录。

2、左外连接，left join 左边的是主表

右外连接，right join右边的是主表

全外连接，full join

3、左外和右外交互两个表的顺序，可以实现同样的效果

4、全外连接=内连接+表1中有表2中没有+表2中有表1中没有

# 1、左连接
# 查询男朋友不在男神表的女神名
select b.name,s.boyname from beauty b
left join boys s
on b.boyfriend_id = s.id;

# 2、右连接
select b.name,s.boyname from boys s
right join beauty b
on b.boyfriend_id = s.id;

# 3、全外连接
select b.name,s.boyname from boys s
full outer join beauty b
on b.boyfriend_id = s.id;

 

四、交叉连接（类似于笛卡尔乘积）

语法：

select 查询列表

from 表1 别名

cross join 表2 别名；

 

五、练习

# 显示员工表的最大工资、工资平均值
select max(salary) 最大工资,avg(salary) 平均工资 from employees;

# 查询员工表的employee_id，job_id，last_name，按departmengt_id降序，salary升序
select employee_id,job_id,last_name from employees order by department_id desc,salary asc;

# 查询员工表的job_id中包含a和e的，并且a在e前面
select job_id from employees where job_id like '%a%e%';

use student
# 已知student里面有id(学号)、name、gradeid（年纪编号），已知表garde里面有id（年纪编号)、name（年级名），
# 已知表result里面有id、score、studentno（学号），要求查询姓名、年级名、成绩
select s.name 姓名,g.name 年纪名,r.score 成绩
from student s,garde g,result r
where s.id = r.studentno and s.gradeid = g.id;

# 显示当前日期，以及去前后空格，截取子字符串的函数
select now() 当前时间;
select trim('   ooo   ooo   ') 去除前后空格;
select substr('hello word',2,4) 截取子字符串; -- 结果：ello
# 显示所有员工的姓名、部门号和部门名称
select e.last_name,e.department_id,d.department_name from employees e,departments d where e.department_id=d.department_id;

# 查询90号部门员工的job_id和90号部门的location_id
select e.job_id,d.location_id,e.department_id
from employees e,departments d 
where e.department_id = d.department_id 
and e.department_id = 90;

# 查询所有有奖金的员工的last_name、department_name、location_id、city
select e.last_name,d.department_name,d.location_id,l.city,e.commission_pct
from employees e,locations l,departments d 
where e.department_id = d.department_id 
and d.location_id = l.location_id
and commission_pct is not null;

# 查询每个工种、每个部门的部门名、工种名和最低工资
select d.department_name,j.job_title,min(salary) 最低工资
from employees e,jobs j,departments d
where e.department_id = d.department_id
and e.job_id = j.job_id
group by e.job_id,e.department_id;

# 查询每个国家下的部门个数大于2的国家编号
select count(*),l.country_id,d.department_id,d.location_id
from locations l,departments d
where d.location_id = l.location_id
group by l.country_id
having count(*) > 2;

# 选择指定员工的姓名、员工号以及他的管理者的姓名和员工号，结果类似于下面的格式
#    employees    emp      #manager mgr#
#    kochhar      101       king     100
select e.last_name 员工姓名,e.employee_id 员工编号,f.last_name 管理姓名,f.employee_id 管理编号
from employees e,employees f
where e.manager_id = f.employee_id;