数据库多表查询练习

一、创建数据库,表

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`;

 

数据库多表查询练习

上一篇:mysql RLIKE REGEXP正则匹配


下一篇:Canal 作为 消息中间件 实时消费MySQL中新增数据