一、创建数据库,表
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); ALTER TABLE `t_dept` ADD CEO INT(11) ; # CEO=2 值,都应该是t_emp 中id的值。 UPDATE t_dept SET CEO=2 WHERE id=1; UPDATE t_dept SET CEO=4 WHERE id=2; UPDATE t_dept SET CEO=6 WHERE id=3; UPDATE t_dept SET CEO=8 WHERE id=4; UPDATE t_dept SET CEO=9 WHERE id=5;
# 1.查询所有门派的信息
SELECT * FROM t_dept d LEFT JOIN t_emp e ON d.`id` = e.`deptId`;
# 2.列出所有用户,并显示其机构信息
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.`deptId` = d.`id`;
-- 3.列出不入门派的人员,方法一
SELECT * FROM t_emp WHERE deptId IS NULL;
-- 方法二
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.`deptId` = d.`id` WHERE e.`deptId` IS NULL;
-- 4.列出所有没人入的门派
SELECT * FROM t_dept d LEFT JOIN t_emp e ON d.`id` = e.`deptId` WHERE e.id IS NULL;
-- 5.列出所有 人员和 门派的对照关系
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`;
-- 6.列出所有没入派的人员和没人入的门派
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.`deptId` = d.`id` WHERE e.`deptId` IS NULL UNION SELECT * FROM t_emp e RIGHT JOIN t_dept d ON d.`id` = e.`deptId` WHERE e.`id` IS NULL;
-- 7.求各个门派对应的掌门人
SELECT * FROM t_dept d LEFT JOIN t_emp e ON e.`id` = d.`CEO`;
-- 8.求所有掌门人的平均年龄
SELECT AVG(age) FROM t_dept d INNER JOIN t_emp e ON d.`CEO` = e.id;
-- 求所有人物对应的掌门的名称
/*
列:ID,name,deptId,ceo
表:t_emp,t_dept
条件:emp.deptId = dept.id,emp.id = dept.ceo
*/
SELECT e1.id,e1.name,d1.`deptName`,e2.`name` FROM t_emp e1 LEFT JOIN t_dept d1 ON e1.`deptId` = d1.`id` LEFT JOIN t_emp e2 ON e2.`id` = d1.`CEO`;