7.连接查询 -- SQL92标准

1.连接查询概念

2.笛卡尔乘积现象

3.连接查询的分类

4.SQL92标准的连接查询

---

1.连接查询概念

连接查询又称多表查询, 当查询的字段来自多个表时, 就用到了连接查询

返回顶部

---

2.笛卡尔乘积现象

发生情形: 表1具有m行数据, 表2具有n行数据, 当表1与表2进行连表查询时, 得到的结果有m*n行数据

发生原因: 连表查询时没有指定有有效的连接条件

如何规避: 连接查询时添加有效的连接条件

返回顶部

---

3.连接查询的分类

# 按功能分类:
　　内连接:
　　　　等值连接
　　　　非等值连接
　　　　自连接
　　外链接:
　　　　左外链接
　　　　右外链接
　　　　全外链接
　　交叉链接
# 按SQL语法标准分类:
　　SQL92标准:支持内连接
　　SQL99标准(推荐):支持内连接+外连接(左外&右外)+交叉连接

返回顶部

---

4.SQL92标准的连接查询

# 等值连接:
　　1).多表等值连接的结果为多表的交集部分
　　2).n张表连接,至少需要n-1个连接条件
 4　　 3).多表的顺序没有要求
　　4).等值连接查询,一般要个表起别名,以便于区分不同表中的同名字段
　　5).连接查询可以搭配其他子句进行查询,如排序子句,分组子句,筛选子句等

# 1.简单的连接查询
# 案例1: 查询员工名和对应的部门名
　　select last_name, department_name # 查询员工姓名与部门名称
　　from employees as e, departments as d # 指定从哪张表中查
　　where e.department_id = d.department_id;# 指定两张表之间的连接条件, 即两张表的关联字段
　　(注:因为两张表中都有department_id字段,为了让SQL区分同名字段而不产生歧义,在from子句中对表进行起别名,在where子句中用别名来区分字段来自哪张表, e.department_id来自employees表, d.department_id来自departments表)

# 2.调换连接的两张表, 查询结果不变
# 案例2: 上面的查询表的顺序可以调换, 查询结果没有区别
　　select last_name, department_name
　　from departments as d, employees as e
　　where d.department_id = e.department_id;

# 3.可以在连接查询时添加筛选条件, 由于where用来连接表, 所以使用and来添加筛选条件
# 案例3: 查询有奖金的员工名, 部门名
　　select last_name, department_name, commission_pct
　　from employees as e, departments as d
　　where e.department_id = d.department_id # where 用于指定连接条件
　　and e.commission_pct isnotnull;# 使用and关键字指定筛选条件
# 案例4: 查询城市名中第二个字符为o的部门名和城市名
　　select department_name, city
　　from departments as d, locations as l
　　where d.location_id = l.location_id
　　and city like '_o%';  # and指定筛选条件, like是模糊查询, '_o%'中的_代表一个任意字符, %代表任意多个任意字符

# 3.添加分组子句的连接查询
# 案例5: 查询每个城市部门的个数
　　select count(*), city
　　from locations as l, departments as d
　　where l.location_id = d.location_id
　　groupby city;# group by 子句用来分组
# 案例6: 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
　　select department_name, manager_id, min(salary)
　　from departments as d, employees as e
　　where d.department_id = e.department_id
　　and commission_pct isnotnull
　　groupby department_name;

# 4.添加排序子句的连接查询
# 案例7: 查询每个工种的工种名和员工的个数, 并且按员工个数降序排列
　　select job_title, count(*)
　　from jobs as j, employees as e
　　where j.job_id = e.job_id
　　groupby job_title
　　order by count(*) desc;# order by 子句用来对字段排序, 默认升序, 加入desc关键字后未降序排列.

# 5.三张表或多张表进行连接查询
# 案例8: 查询员工名, 部门名和所在的城市
　　select last_name, department_name, city
　　from employees as e, departments as d, locations as l
　　where e.department_id=d.department_id and d.location_id=l.location_id # 三张表至少需要两个连接条件

# 非等值连接
# 1. 非等值连接查询案例数据准备
(在实现非等值连接查询案例前需要建立一张表--工资级别表,在SQLyong中执行以下SQL语句,即可获得该表)
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal int,
highest_sal int);
INSERT INTO job_grades
VALUES ('A',1000,2999);
INSERT INTO job_grades
VALUES ('B',3000,5999);
INSERT INTO job_grades
VALUES('C',6000,9999);
INSERT INTO job_grades
VALUES('D',10000,14999);
INSERT INTO job_grades
VALUES('E',15000,24999);
INSERT INTO job_grades
VALUES('F',25000,40000);

# 2.工资级别表解释:该表共有3个字段, 分别为grade_level(工资等级, 如A,B,C...), lowest_sal(某一工资级别对应的工资下线), highest_sal(某一工资级别对应的工资上线)

# 3.案例: 查询员工的工资和工资级别
　　select salary, grade_level
23　　 from employees as e, job_grades as g
　　where salary between g.lowest_sal and g.heighest_sal;# 指定查询出的工资要在级别表中某个工资上下线的范围

 

 

# 自连接
# 1.自连接:自连接是只一张表自己与自己连接, 一张表用为为两张表, 如下面的案例, 员工编号(employee_id)与领导编号(manager_id)在同一张表中, 所以先通过该表查询出员工的领导编号, 再利用同一张表的领导编号字段查找员工对应的领导名字

# 2.案例: 查询员工名和上级领导的名字
　　select e.last_name, m.last_name
　　from employees as e, employees as m;
　　where e.manager_id = m.employee_id;

 

返回顶部