多表查詢

本文介紹了多表查詢中的表關系概念和操作方法。主要內容包括：1.三種表關系類型（一對多、多對多、一對一）及其實現方式；2.多表查詢的四種連接方式（內連接、左外連接、右外連接、自連接）及語法；3.子查詢的使用方法；4.通過員工-部門-薪資等級表的實際案例，演示了9種典型查詢場景的實現方式，包括基礎查詢、條件篩選、分組統計、子查詢應用等。案例涵蓋了從簡單到復雜的多表查詢操作，展示了SQL在數據關聯分析中的強大功能。

多表關系

概念

一對多（多對一）

一張表中的一列可以和另外一張表中多條數據關聯，拿學生表和成績表舉例，一個學生有多個成績。

案例：部門與員工

多對多

拿學生表和科目表舉例，一個學生可以選擇多門課程，一個課程也可以被很多學生選擇；多對多關系實現需要借助第三張中間表。中間表至少包含兩個字段，將多對多的關系，拆成一對多的關系

案例：學生與課程

建立一個中間表

一對一

一張表中的一條數據對應另外一張表中的一列數據，比如一個人只有一張身份證，一張身份證對應一個人。一對一關系比較少見，因為一對一這種關系的表可以合并成一張表

多表查詢概述

內連接

兩張表交集的部分

隱式

select 字段列表 from 表一，表二 where 條件;

顯式

?select 字段列表 from 表一 inner join 表二 on 連接條件;

外連接

左外:左表所有數據包含交集部分

select 字段列表 from 表一 left join 表二 on 條件;

右外:右表所有數據包含交集部分

select 字段列表 from 表一 right join 表二 on 條件;

自連接

自連接查詢，顧名思義，就是自己連接自己，也就是把一張表連接查詢多次。

自連接的查詢語法：

?SELECT 字段列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ... ;

子查詢

SQL語句中嵌套SELECT語句，稱為嵌套查詢，又稱子查詢。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ); 1

多表查詢案例

數據準備

emp員工表

create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年齡',
job varchar(20) comment '職位',
salary int comment '薪資',
entrydate date comment '入職時間',
managerid int comment '直屬領導ID',
dept_id int comment '部門ID'
)comment '員工表';
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '總裁',20000, '2000-01-01', null,5),
(2, '張無忌', 20, '項目經理',12500, '2005-12-05', 1,1),
(3, '楊逍', 33, '開發', 8400,'2000-11-03', 2,1),
(4, '韋一笑', 48, '開發',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '開發',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序員鼓勵師',6600, '2004-10-12', 2,1),
(7, '滅絕', 60, '財務總監',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '會計',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出納',5250, '2009-05-13', 7,3),
(10, '趙敏', 20, '市場部總監',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '職員',3750, '2006-10-03', 10,2),
(12, '鶴筆翁', 19, '職員',3750, '2007-05-09', 10,2),
(13, '方東白', 19, '職員',5500, '2009-02-12', 10,2),
(14, '張三豐', 88, '銷售總監',14000, '2004-10-12', 1,4),
(15, '俞蓮舟', 38, '銷售',4600, '2004-10-12', 14,4),
(16, '宋遠橋', 40, '銷售',4600, '2004-10-12', 14,4),
(17, '陳友諒', 42, null,2000, '2011-10-12', 1,null);

dept部門表

salgrade薪資等級表

create table salgrade(
grade int,
losal int,
hisal int
) comment '薪資等級表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

查詢案例

1、查詢員工的姓名、年齡、職位、部門信息

select emp.name,age,job,dept.name from db5.emp,dept where emp.dept_id=dept.id;

2、查詢年齡小于30歲的員工的姓名、年齡、職位、部門信息（顯式內連接）

select emp.name,age,job,d.name from db5.emp inner join dept d on emp.dept_id = d.id where age<30;

3、查詢擁有員工的部門ID、部門名稱

select distinct d.id,d.name from dept d,db5.emp e where d.id=e.dept_id;

4、查詢所有年齡大于40歲的員工, 及其歸屬的部門名稱; 如果員工沒有分配部門, 也需要展示出

來(外連接)

select e.name,e.age,d.name from dept d left join emp e on d.id = e.dept_id where e.age>40;

5、查詢所有員工的工資等級

select e.*,s.* from db5.emp e,salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
#或者
select e.*,s.* from db5.emp e,salgrade s where e.salary between s.losal and s.hisal;

6、查詢 "研發部" 所有員工的信息及 工資等級

select e.*,d.name,s.grade from emp e , dept d, salgrade s where e.dept_id=d.id and (e.salary between s.losal and s.hisal) and d.name='研發部';

7、查詢 "研發部" 員工的平均工資

select avg(salary) '平均工資',d.name from dept d left join emp e on d.id = e.dept_id where d.name='研發部';

8、查詢工資比 "滅絕" 高的員工信息。

select salary from emp where name='滅絕';

select * from emp where salary>(select salary from emp where name='滅絕');

9、查詢比平均薪資高的員工信息

select avg(salary) from emp;
select * from emp where salary>(select avg(salary) from emp);