【MySQL练习】多表查询练习（二）

查看代码：数据库和表数据创建

CREATE DATABASE `wudang`;
USE `wudang`;
 
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;
 
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.所有有门派的人员信息

inner join的效果和join是一样的

SELECT a.*
FROM t_emp a
INNER JOIN t_dept b ON a.deptId = b.id;

2.列出所有用户，并显示其门派信息

所有用户，包括没有门派的用户

SELECT *
FROM t_emp a
LEFT JOIN t_dept b ON a.deptId = b.id;

3.列出所有门派

SELECT * FROM t_dept;

4.所有不入门派的人员

SELECT a.*
FROM t_emp a
left JOIN t_dept b ON a.deptId = b.id
WHERE b.id IS NULL;

5.所有没人入的门派

SELECT
FROM t_dept a
LEFT JOIN t_emp b ON a.id = b.deptId
WHERE b.deptId IS NULL;

6.列出所有人员和机构的对照关系

使用full join 得到两张表的并集。
MySQL不支持full join，使用left join + union(可去除重复数据)+ right join达到该效果。

SELECT a.*,b.*
FROM t_emp a
LEFT JOIN t_dept b ON a.deptId = b.id
UNION
SELECT a.*,b.* 
FROM t_emp a
RIGHT JOIN t_dept b ON a.deptId = b.id;

7.列出所有没入派的人员和没人入的门派

SELECT a.*,b.*
FROM t_emp a
LEFT JOIN t_dept b ON a.deptId = b.id
WHERE b.id IS NULL
UNION
SELECT a.*,b.* 
FROM t_emp a
RIGHT JOIN t_dept b ON a.deptId = b.id
WHERE a.id IS NULL;