版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
- 数据准备
-
CREATE
DATABASE test02;
-
USE test02;
-
-- 部门表
-
CREATE
TABLE dept (
-
id
INT PRIMARY
KEY,
-- 部门id
-
dname
VARCHAR(
50),
-- 部门名称
-
loc
VARCHAR(
50)
-- 部门所在地
-
);
-
-
-- 添加4个部门
-
INSERT
INTO dept(
id,dname,loc)
VALUES
-
(
10,
'教研部',
'北京'),
-
(
20,
'学工部',
'上海'),
-
(
30,
'销售部',
'广州'),
-
(
40,
'财务部',
'深圳');
-
-
-- 职务表，职务名称，职务描述
-
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);
- 需求：查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述。
-
#1.查询出来员工的信息
-
SELECT e.
`id`,e.
`ename`,e.
`salary`
FROM emp e;
-
#2.联合job表查询对应的职务
-
SELECT e.
`id`,e.
`ename`,e.
`salary`,j.
`jname`,j.
`description`
FROM emp e, job j
WHERE e.
`job_id`=j.
`id`;
- 需求：查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
-
SELECT e.
`id`,e.
`ename`,e.
`salary`,j.
`jname`,j.
`description`,d.
`dname`,d.
`loc`
-
FROM emp e,job j,dept d
WHERE e.
`job_id`=j.
`id`
AND d.
`id`=e.
`dept_id`;
- 需求：查询所有员工信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT e.`id`,e.`ename`,e.`salary`,j.`jname`,j.`description`,d.`dname`,d.`loc`,s.`grade` FROM emp e,job j,dept d,salarygrade s WHERE e.`job_id`=j.`id` AND d.`id`=e.`dept_id`
AND e.`salary` BETWEEN s.`losalary` AND s.`hisalary`;
- 需求: 查询经理的信息。显示员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT e.`id`,e.`ename`,e.`salary`,j.`jname`,j.`description`,d.`dname`,d.`loc`,s.`grade` FROM emp e,job j,dept d,salarygrade s WHERE e.`job_id`=j.`id` AND d.`id`=e.`dept_id`
AND e.`salary` BETWEEN s.`losalary` AND s.`hisalary` AND j.`jname`='经理';
- 需求：查询出部门编号、部门名称、部门位置、部门人数
-
#1.先对员工表的员工部门进行分组，统计出每组的人数
-
SELECT dept_id,
COUNT(dept_id)
FROM emp
GROUP
BY dept_id;
-
#2.联合部门表进行查询
-
SELECT*
FROM dept d,(
SELECT dept_id,
COUNT(dept_id)
FROM emp
GROUP
BY dept_id)e
WHERE d.
`id`=e.dept_id;
- 多表查询规律总结：
- 不管我们查询几张表，表连接查询会产生笛卡尔积现象，我们需要消除笛卡尔积，拿到正确的数据。我们需要找到表与表之间通过哪个字段关联起来的（通常是外键=主键）
- 消除笛卡尔积规律：2张表需要1个条件，3张表需要2个条件，4张表需要3个条件。（条件的数量=表的数量-1），每张表都要参与进来。
- 多表查询的步骤：3.1确定要查询哪张表 3.2 确定表连接条件 3.3 确定查询字段
更多资料，搜索或扫码关注公众号：数说Cloud
扫一扫
464
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
