SQL 的多表查询（Navicat ）
-- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学⼯部','上海'),
(30,'销售部','⼴州'),
(40,'财务部','深圳');
-- 职务表，职务名称，职务描述sql left join 多表连接
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员⼯'),
(3, '销售员', '向客⼈推销产品'),
(4, '⽂员', '使⽤办公软件');
-- 员⼯表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员⼯id
ename VARCHAR(50), -- 员⼯姓名
job_id INT, -- 职务id
mgr INT , -- 上级领导
joindate DATE, -- ⼊职⽇期
salary DECIMAL(7,2), -- ⼯资
bonus DECIMAL(7,2), -- 奖⾦
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 添加员⼯
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪⼋戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴⽤',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'⼩⽩龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关⽻',4,1007,'2002-01-23','13000.00',NULL,10);
-- ⼯资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低⼯资
hisalary INT -- 最⾼⼯资
);
-- 添加5个⼯资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
ER 结构：
-- 需求：
-- 1.查询所有员⼯信息。查询员⼯编号，员⼯姓名，⼯资，职务名称，职务描述
/*
分析：
1).
员⼯编号，员⼯姓名，⼯资，emp
职务名称，职务描述 job
2).条件:
emp.job_id = job.id
*/SELECT
t1.,-- 员⼯编号
t1.,-- 员⼯姓名
t1.,-- ⼯资
id ename salary jname
t2.,-- 职务名称
t2. -- 职务描述
FROM
emp t1,
job t2
WHERE
t1. = t2.;
-- 2.查询员⼯编号，员⼯姓名，⼯资，职务名称，职务描述，部门名称，部门位置
/*
分析：
1).员⼯编号，员⼯姓名，⼯资 emp
职务名称，职务描述 job
部门名称，部门位置 dept 2). 条件：emp.job_id = job.id AND emp.dept_id = dept.id
*/
SELECT
t1.,-- 员⼯编号
t1.,-- 员⼯姓名
t1.,-- ⼯资
t2.,-- 职务名称
t2.,-- 职务描述
t3.,-- 部门名称
t3. -- 部门位置FROM
emp t1,
job t2,
dept t3
WHERE
t1. = t2.AND t1. = t3.;
-- 3.查询员⼯姓名，⼯资，⼯资等级/*
分析：1).
员⼯姓名，⼯资 emp
⼯资等级 salarygrade
2).条件：
emp.salary >= salarygrade.losalary AND emp.salary <= salarygrade.hisalary
或者
emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
*/
SELECT
t1.,
t2.FROM
emp t1,
salarygrade t2
WHERE
t1. BETWEEN t2.AND t2.;
-- 4.查询员⼯姓名，⼯资，职务名称，职务描述，部门名称，部门位置，⼯资等级
/*
分析：
1.
员⼯姓名，⼯资 emp
职务名称，职务描述 job
部门名称，部门位置，dept
⼯资等级 salarygrade
2. 条件：
emp.job_id = job.id and emp.dept_id = dept.id and emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary */
SELECT
t1.,
t1.,
t2.,
t2.,
t3.,
t3.,
t4.FROM
emp t1,
job t2,
dept t3,
salarygrade t4
WHERE
t1. = t2. -- 加上⾯写的条件
AND t1. = t3. -- 条件之间⽤ AND 连接
AND t1. BETWEEN t4.AND t4.; -- 最低和最⾼之间，表可以直接拿来⽤
-- 5.查询出部门编号、部门名称、部门位置、部门⼈数（这个并没有直接给出）
/*
jname description job_id id id ename salary jname description dname loc job_id id
dept_id id salary grade
salary losalary
hisalary ename salary jname description dname loc grade
job_id id dept_id id salary losalary
hisalary
分析：
1.
部门编号、部门名称、部门位置 dept
所在部门编号 emp （通过这个来统计⼈数，作为分组的依据）
2.使⽤分组查询。按照emp.dept_id 完成分组，查询count(id)
3.使⽤⼦查询将第2步的查询结果和dept 表进⾏关联查询
*/SELECT
t1.,
t1.,
t1.,
dept t1,( SELECT dept_id, COUNT( id ) total FROM emp GROUP BY dept_id ) t2 -- 中间的⼦查询是第2步的查询结果WHERE
t1. = t2.dept_id;
-- 6.查询所有员⼯的姓名及其直接上级的姓名，没有领导的员⼯也需要查询
/*
分析：
1.
姓名 emp
直接上级的姓名 emp （emp 表的id 和 mgr 是⾃关联）
2.条件:
emp.id =
3.
查询左表的所有数据，和 交集数据（使⽤左外连接查询）
*/
SELECT
<,
t2.,
FROM
emp t1,
emp t2 -- ⾃关联其实是两张表的关联，但是emp 是⼀张表，我们通过给emp 起别名t1,t2把emp 当成两张表操作SELECT
<,
t2.,
t2.FROM
emp t1
LEFT JOIN emp t2 ON t1. = t2.;-- 左外连接查询，意思是把左边（emp t2）的所有数据都输出，和右边（t1. = t2.）与左边（emp t2）交集数据输出。id dname loc id id id ename
mgr id mgr id

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。