【MySQL】多表查询 综合练习

多表查询笔记

综合练习

# 1.显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name,e.department_id,d.department_name
FROM employees e 
LEFT JOIN departments d 
ON e.department_id = d.department_id;

SELECT e.last_name,e.department_id,d.department_name
FROM employees e 
LEFT JOIN departments d 
USING(department_id);

# 2.查询90号部门员工的job_id和90号部门的location_id
SELECT e.employee_id,e.job_id,d.department_id,d.location_id
FROM employees e,departments d 
WHERE e.department_id = d.department_id
AND e.department_id = 90;

SELECT e.employee_id,e.job_id,d.department_id,d.location_id
FROM employees e
JOIN departments d 
ON e.department_id = d.department_id
WHERE e.department_id = 90;

SELECT e.employee_id,e.job_id,d.department_id,d.location_id
FROM employees e
JOIN departments d 
USING(department_id)
WHERE e.department_id = 90;

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.employee_id, e.last_name,d.department_name,l.location_id,l.city
FROM employees e 
JOIN departments d ON e.department_id = d.department_id 
JOIN locations l ON d.location_id = l.location_id
WHERE NOT ISNULL(e.commission_pct);

SELECT e.employee_id, e.last_name,d.department_name,l.location_id,l.city
FROM employees e 
JOIN departments d ON e.department_id = d.department_id 
JOIN locations l ON d.location_id = l.location_id
WHERE e.commission_pct IS NOT NULL;


# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT last_name , job_id , department_id , department_name
FROM employees e 
JOIN departments d 
USING(department_id)
JOIN locations l 
USING(location_id)
WHERE l.city = 'Toronto';


# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT e.employee_id,e.last_name,e.salary,e.job_id,d.department_name,l.street_address
FROM employees e 
JOIN departments d ON e.department_id = d.department_id 
JOIN locations l ON d.location_id = l.location_id
WHERE d.department_name = 'Executive';


# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
# kochhar 101 king 100
SELECT e.last_name,e.employee_id,m.last_name,m.manager_id
FROM employees e , employees m
WHERE e.manager_id = m.employee_id;

SELECT CONCAT(e.last_name,'  ',e.employee_id,'  ',m.last_name,'  ',IFNULL(m.manager_id,100))
FROM employees e , employees m
WHERE e.manager_id = m.employee_id


# 7.查询哪些部门没有员工
SELECT e.employee_id,d.department_id,d.department_name
FROM employees e 
RIGHT JOIN departments d USING(department_id) -- 查询显示右表 --
WHERE e.employee_id IS NULL; -- 多虑左边 --


#方式1：
SELECT d.department_id
FROM departments d LEFT JOIN employees e
ON e.department_id = d.`department_id`
WHERE e.department_id IS NULL
#方式2：
SELECT department_id
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE e.`department_id` = d.`department_id`
)

# 8. 查询哪个城市没有部门
SELECT d.department_name,l.location_id,l.city
FROM departments d 
RIGHT JOIN locations l      --   查右 滤左     --
ON d.location_id = l.location_id
where d.department_id IS NULL;

SELECT l.location_id,l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL


# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id 
WHERE d.department_name = 'Sales'
OR d.department_name = 'IT';

SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND d.`department_name` IN ('Sales','IT');

新建表2：

CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

#1.所有有门派的人员信息 ( A、B两表共有）

INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);


#【题目】
#1.所有有门派的人员信息 （ A、B两表共有）
SELECT e.id,e.`name`,e.age,d.id AS "门派ID",d.deptName
FROM t_emp e 
JOIN t_dept d
ON e.deptId = d.id

#2.列出所有用户，并显示其机构信息 （A的全集）
SELECT e.id,e.`name`,e.age,d.id AS "门派ID",d.deptName
FROM t_emp e 
LEFT JOIN t_dept d
ON e.deptId = d.id

#3.列出所有门派 （B的全集）
SELECT *
FROM t_dept;

SELECT d.id AS "门派ID",d.deptName
FROM t_emp e 
RIGHT JOIN t_dept d
ON e.deptId = d.id;

#4.所有不入门派的人员 （A的独有）
# 公示： 查左 滤右
SELECT e.id,e.`name`,e.age,d.id AS "门派ID",d.deptName
FROM t_emp e 
LEFT JOIN t_dept d  
ON e.deptId = d.id
WHERE d.id IS NULL;

#5.所有没人入的门派 （B的独有）
# 公式： 查右 滤左
SELECT e.id,e.`name`,e.age,d.id AS "门派ID",d.deptName
FROM t_emp e 
RIGHT JOIN t_dept d
ON e.deptId = d.id
WHERE e.deptId IS NULL;


#6.列出所有人员和机构的对照关系 (AB全有)
# 左右外连接 使用 UNION 再连接
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId = d.id
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId = d.id;

#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
#7.列出所有没入派的人员和没人入的门派 （A的独有+B的独有）
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId = d.id WHERE d.id IS NULL
UNION ALL
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId = d.id WHERE e.deptId IS NULL;