MySQL多表SQL查询

最新推荐文章于 2024-03-20 16:19:43 发布

WEL测试

最新推荐文章于 2024-03-20 16:19:43 发布

阅读量1.9k

点赞数

分类专栏： # SQL必修课文章标签： sql 多表

SQL必修课专栏收录该内容

70 篇文章 8 订阅

订阅专栏

01.创建数据库表结构并插入数据

如下代码，导入sql不产生乱码

DROP DATABASE exam;
CREATE DATABASE exam;
USE exam;


/*创建部门表*/
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(50),
loc VARCHAR(50)
);


/*创建雇员表*/
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
COMM DECIMAL(7,2),
deptno INT,
CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)
);


/*创建工资等级表*/
CREATE TABLE salgrade(
grade INT PRIMARY KEY,
losal INT,
hisal INT
);


/*创建学生表*/
CREATE TABLE stu(
sid INT PRIMARY KEY,
sname VARCHAR(50),
age INT,
gander VARCHAR(10),
province VARCHAR(50),
tuition INT
);


/*插入dept表数据*/
INSERT INTO dept VALUES (10, 'jiao yan bu', 'bei jing');
INSERT INTO dept VALUES (20, 'xue gong bu', 'shang hai');
INSERT INTO dept VALUES (30, 'xiao shou bu', 'guang zhou');
INSERT INTO dept VALUES (40, 'cai wu bu', 'wu han');


/*插入emp表数据*/
INSERT INTO emp VALUES (1009, 'a niu', 'dong shi zhang', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1004, 'liu bei', 'jing li', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1006, 'guan yu', 'jing li', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, 'zhang fei', 'jing li', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, 'zhu ge liang', 'fen xi shi', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1013, 'pang', 'fen xi shi', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1002, 'dai', 'xiao shou yuan', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, 'tian zheng', 'xiao shou yuan', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, 'xie xun', 'xiao shou yuan', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, 'wei yi xiao', 'xiao shou yuan', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, 'cheng pu', 'wen yuan', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, 'huang gai', 'wen yuan', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, 'zhou tai', 'wen yuan', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1001, 'gan nin', 'wen yuan', 1013, '2000-12-17', 8000, NULL, 20);


/*插入salgrade表数据*/
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);


/*插入stu表数据*/
INSERT INTO `stu` VALUES ('1', '001', '23', 'nan', 'bei jing', '1500');
INSERT INTO `stu` VALUES ('2', '002', '25', 'nan', 'liao ning', '2500');
INSERT INTO `stu` VALUES ('3', '003', '22', 'nan', 'bei jing', '3500');
INSERT INTO `stu` VALUES ('4', '004', '25', 'nan', 'bei jing', '1500');
INSERT INTO `stu` VALUES ('5', '005', '23', 'nv', 'bei jing', '1000');
INSERT INTO `stu` VALUES ('6', '006', '22', 'nv', 'shan dong', '2500');
INSERT INTO `stu` VALUES ('7', '007', '21', 'nv', 'bei jing', '1600');
INSERT INTO `stu` VALUES ('8', '008', '23', 'nan', 'bei jing', '3500');
INSERT INTO `stu` VALUES ('9', '009', '23', 'nv', 'guang zhou', '2500');
INSERT INTO `stu` VALUES ('10', '010', '18', 'nan', 'shan xi', '3500');
INSERT INTO `stu` VALUES ('11', '011', '23', 'nan', 'hu bei', '4500');
INSERT INTO `stu` VALUES ('12', '011', '24', 'nan', 'bei jing', '1500');
INSERT INTO `stu` VALUES ('13', '011', '24', 'nan', 'liao ning', '2500');
INSERT INTO `stu` VALUES ('14', '011', '22', 'nan', 'bei jing', '3500');
INSERT INTO `stu` VALUES ('15', '011', '25', 'nan', 'bei jing', '1500');
INSERT INTO `stu` VALUES ('16', '011', '23', 'nv', 'bei jing', '1000');
INSERT INTO `stu` VALUES ('17', '011', '22', 'nv', 'shan dong', '2500');
INSERT INTO `stu` VALUES ('18', '011', '21', 'nv', 'bei jing', '1600');
INSERT INTO `stu` VALUES ('19', '011', '23', 'nan', 'bei jing', '3500');
INSERT INTO `stu` VALUES ('20', '011', '23', 'nv', 'guang zhou', '2500');
INSERT INTO `stu` VALUES ('21', '011', '18', 'nan', 'shan xi', '3500');
INSERT INTO `stu` VALUES ('22', '011', '23', 'nan', 'hu bei', '4500');
INSERT INTO `stu` VALUES ('23', '011', '23', 'nan', 'bei jing', '1500');
INSERT INTO `stu` VALUES ('24', '011', '25', 'nan', 'liao ning', '2500');
INSERT INTO `stu` VALUES ('25', '011', '22', 'nan', 'bei jing', '3500');
INSERT INTO `stu` VALUES ('26', '011', '25', 'nan', 'bei jing', '1500');
INSERT INTO `stu` VALUES ('27', '011', '23', 'nv', 'bei jing', '1000');
INSERT INTO `stu` VALUES ('28', '011', '22', 'nv', 'shan dong', '2500');
INSERT INTO `stu` VALUES ('29', '011', '21', 'nv', 'bei jing', '1600');
INSERT INTO `stu` VALUES ('30', '011', '23', 'nan', 'bei jing', '3500');
INSERT INTO `stu` VALUES ('31', '011', '23', 'nv', 'guang zhou', '2500');
INSERT INTO `stu` VALUES ('32', '011', '18', 'nan', 'shan xi', '3500');
INSERT INTO `stu` VALUES ('33', '033', '23', 'nan', 'hu bei', '4500');
INSERT INTO `stu` VALUES ('34', '034', '23', 'nan', 'bei jing', '1500');
INSERT INTO `stu` VALUES ('35', '035', '25', 'nan', 'liao ning', '2500');
INSERT INTO `stu` VALUES ('36', '036', '22', 'nan', 'bei jing', '3500');
INSERT INTO `stu` VALUES ('37', '037', '25', 'nan', 'bei jing', '1500');
INSERT INTO `stu` VALUES ('38', '038', '23', 'nv', 'bei jing', '1000');
INSERT INTO `stu` VALUES ('39', '039', '22', 'nv', 'shan dong', '2500');
INSERT INTO `stu` VALUES ('40', '040', '21', 'nv', 'bei jing', '1600');
INSERT INTO `stu` VALUES ('41', '041', '23', 'nan', 'bei jing', '3500');
INSERT INTO `stu` VALUES ('42', '042', '23', 'nv', 'guang zhou', '2500');
INSERT INTO `stu` VALUES ('43', '043', '18', 'nan', 'shan xi', '3500');
INSERT INTO `stu` VALUES ('44', '044', '23', 'nan', 'hu bei', '4500');
INSERT INTO `stu` VALUES ('45', '045', '23', 'nan', 'bei jing', '1500');
INSERT INTO `stu` VALUES ('46', '046', '25', 'nan', 'liao ning', '2500');
INSERT INTO `stu` VALUES ('47', '047', '22', 'nan', 'bei jing', '3500');
INSERT INTO `stu` VALUES ('48', '048', '25', 'nan', 'bei jing', '1500');
INSERT INTO `stu` VALUES ('49', '049', '23', 'nv', 'bei jing', '1000');
INSERT INTO `stu` VALUES ('50', '050', '22', 'nv', 'shan dong', '2500');
INSERT INTO `stu` VALUES ('51', '051', '21', 'nv', 'bei jing', '1600');
INSERT INTO `stu` VALUES ('52', '052', '23', 'nan', 'bei jing', '3500');
INSERT INTO `stu` VALUES ('53', '053', '23', 'nv', 'guang zhou', '2500');
INSERT INTO `stu` VALUES ('54', '054', '18', 'nan', 'shan xi', '3500');
INSERT INTO `stu` VALUES ('55', '055', '23', 'nan', 'hu bei', '4500');


/*
select * from emp;
select * from dept;
select * from salgrade;
select * from stu;
*/

如下代码，mysql编码格式为GBD2312

[sql]view plaincopy 
   
 DROP DATABASE exam;  
 CREATE DATABASE exam;  
 USE exam;  
   
   
 /*创建部门表*/  
 CREATE TABLE dept(  
 deptno INT PRIMARY KEY,  
 dname VARCHAR(50),  
 loc VARCHAR(50)  
 );  
   
   
 /*创建雇员表*/  
 CREATE TABLE emp(  
 empno INTPRIMARY KEY,  
 ename VARCHAR(50),  
 job VARCHAR(50),  
 mgr INT,  
 hiredate DATE,  
 sal DECIMAL(7,2),  
 COMM DECIMAL(7,2),  
 deptno INT,  
 CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)  
 );  
   
   
 /*创建工资等级表*/  
 CREATE TABLE salgrade(  
 grade INTPRIMARY KEY,  
 losal INT,  
 hisal INT  
 );  
   
   
 /*创建学生表*/  
 CREATE TABLE stu(  
 sid INTPRIMARY KEY,  
 sname VARCHAR(50),  
 age INT,  
 gander VARCHAR(10),  
 province VARCHAR(50),  
 tuition INT  
 );  
   
   
 /*插入dept表数据*/  
 INSERT INTO dept VALUES (10, '教研部', '北京');  
 INSERT INTO dept VALUES (20, '学工部', '上海');  
 INSERT INTO dept VALUES (30, '销售部', '广州');  
 INSERT INTO dept VALUES (40, '财务部', '武汉');  
   
   
 /*插入emp表数据*/  
 INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);  
 INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);  
 INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);  
 INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);  
 INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);  
 INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);  
 INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);  
 INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);  
 INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);  
 INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);  
 INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);  
 INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);  
 INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);  
 INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);  
   
   
 /*插入salgrade表数据*/  
 INSERT INTO salgrade VALUES (1, 7000, 12000);  
 INSERT INTO salgrade VALUES (2, 12010, 14000);  
 INSERT INTO salgrade VALUES (3, 14010, 20000);  
 INSERT INTO salgrade VALUES (4, 20010, 30000);  
 INSERT INTO salgrade VALUES (5, 30010, 99990);  
   
   
 /*插入stu表数据*/  
 INSERT INTO `stu` VALUES ('1', '王永', '23', '男', '北京', '1500');  
 INSERT INTO `stu` VALUES ('2', '张雷', '25', '男', '辽宁', '2500');  
 INSERT INTO `stu` VALUES ('3', '李强', '22', '男', '北京', '3500');  
 INSERT INTO `stu` VALUES ('4', '宋永合', '25', '男', '北京', '1500');  
 INSERT INTO `stu` VALUES ('5', '叙美丽', '23', '女', '北京', '1000');  
 INSERT INTO `stu` VALUES ('6', '陈宁', '22', '女', '山东', '2500');  
 INSERT INTO `stu` VALUES ('7', '王丽', '21', '女', '北京', '1600');  
 INSERT INTO `stu` VALUES ('8', '李永', '23', '男', '北京', '3500');  
 INSERT INTO `stu` VALUES ('9', '张玲', '23', '女', '广州', '2500');  
 INSERT INTO `stu` VALUES ('10', '啊历', '18', '男', '山西', '3500');  
 INSERT INTO `stu` VALUES ('11', '王刚', '23', '男', '湖北', '4500');  
 INSERT INTO `stu` VALUES ('12', '陈永', '24', '男', '北京', '1500');  
 INSERT INTO `stu` VALUES ('13', '李雷', '24', '男', '辽宁', '2500');  
 INSERT INTO `stu` VALUES ('14', '李沿', '22', '男', '北京', '3500');  
 INSERT INTO `stu` VALUES ('15', '王小明', '25', '男', '北京', '1500');  
 INSERT INTO `stu` VALUES ('16', '王小丽', '23', '女', '北京', '1000');  
 INSERT INTO `stu` VALUES ('17', '唐宁', '22', '女', '山东', '2500');  
 INSERT INTO `stu` VALUES ('18', '唐丽', '21', '女', '北京', '1600');  
 INSERT INTO `stu` VALUES ('19', '啊永', '23', '男', '北京', '3500');  
 INSERT INTO `stu` VALUES ('20', '唐玲', '23', '女', '广州', '2500');  
 INSERT INTO `stu` VALUES ('21', '叙刚', '18', '男', '山西', '3500');  
 INSERT INTO `stu` VALUES ('22', '王累', '23', '男', '湖北', '4500');  
 INSERT INTO `stu` VALUES ('23', '赵安', '23', '男', '北京', '1500');  
 INSERT INTO `stu` VALUES ('24', '关雷', '25', '男', '辽宁', '2500');  
 INSERT INTO `stu` VALUES ('25', '李字', '22', '男', '北京', '3500');  
 INSERT INTO `stu` VALUES ('26', '叙安国', '25', '男', '北京', '1500');  
 INSERT INTO `stu` VALUES ('27', '陈浩难', '23', '女', '北京', '1000');  
 INSERT INTO `stu` VALUES ('28', '陈明', '22', '女', '山东', '2500');  
 INSERT INTO `stu` VALUES ('29', '孙丽', '21', '女', '北京', '1600');  
 INSERT INTO `stu` VALUES ('30', '李治国', '23', '男', '北京', '3500');  
 INSERT INTO `stu` VALUES ('31', '张娜', '23', '女', '广州', '2500');  
 INSERT INTO `stu` VALUES ('32', '安强', '18', '男', '山西', '3500');  
 INSERT INTO `stu` VALUES ('33', '王欢', '23', '男', '湖北', '4500');  
 INSERT INTO `stu` VALUES ('34', '周天乐', '23', '男', '北京', '1500');  
 INSERT INTO `stu` VALUES ('35', '关雷', '25', '男', '辽宁', '2500');  
 INSERT INTO `stu` VALUES ('36', '吴强', '22', '男', '北京', '3500');  
 INSERT INTO `stu` VALUES ('37', '吴合国', '25', '男', '北京', '1500');  
 INSERT INTO `stu` VALUES ('38', '正小和', '23', '女', '北京', '1000');  
 INSERT INTO `stu` VALUES ('39', '吴丽', '22', '女', '山东', '2500');  
 INSERT INTO `stu` VALUES ('40', '冯含', '21', '女', '北京', '1600');  
 INSERT INTO `stu` VALUES ('41', '陈冬', '23', '男', '北京', '3500');  
 INSERT INTO `stu` VALUES ('42', '关玲', '23', '女', '广州', '2500');  
 INSERT INTO `stu` VALUES ('43', '包利', '18', '男', '山西', '3500');  
 INSERT INTO `stu` VALUES ('44', '威刚', '23', '男', '湖北', '4500');  
 INSERT INTO `stu` VALUES ('45', '李永', '23', '男', '北京', '1500');  
 INSERT INTO `stu` VALUES ('46', '张关雷', '25', '男', '辽宁', '2500');  
 INSERT INTO `stu` VALUES ('47', '送小强', '22', '男', '北京', '3500');  
 INSERT INTO `stu` VALUES ('48', '关动林', '25', '男', '北京', '1500');  
 INSERT INTO `stu` VALUES ('49', '苏小哑', '23', '女', '北京', '1000');  
 INSERT INTO `stu` VALUES ('50', '赵宁', '22', '女', '山东', '2500');  
 INSERT INTO `stu` VALUES ('51', '陈丽', '21', '女', '北京', '1600');  
 INSERT INTO `stu` VALUES ('52', '钱小刚', '23', '男', '北京', '3500');  
 INSERT INTO `stu` VALUES ('53', '艾林', '23', '女', '广州', '2500');  
 INSERT INTO `stu` VALUES ('54', '郭林', '18', '男', '山西', '3500');  
 INSERT INTO `stu` VALUES ('55', '周制强', '23', '男', '湖北', '4500');  
   
   
 /*  
 select * from emp;  
 select * from dept;  
 select * from salgrade;  
 select * from stu;  
 */  

02.SQL多表查询

/*1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。*/
/*
列：部门编号、部门名称、部门位置、部门人数(分组)
列：dept、emp(部门人数没有员工表不行)
条件：没有
分组条件：人数>1

部门编号、部门名称、部门位置在dept表中都有，只有部门人数需要使用emp表，使用deptno来分组得到。
我们让dept和（emp的分组查询），这两张表进行连接查询
*/
# 解答

[sql]view plaincopy 
   
 SELECT d.*,z.cnt FROM dept d,(SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) z WHERE d.deptno=z.deptno;  

# 答案

[sql]view plaincopy 
   
 SELECT  
 z.*,d.dname,d.loc  
 FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z  
 WHERE z.deptno=d.deptno;  

/**************************************************/
/*2. 列出薪金比关羽高的所有员工。*/
/*
列：所有
表：emp
条件：sal>关羽的sal，其中关羽的sal需要子查询
*/
# 解答

[sql]view plaincopy 
   
 SELECT e.* FROM emp e WHERE e.sal>(SELECT sal FROM emp WHERE ename='关羽');  

# 答案

[sql]view plaincopy 
   
 SELECT *  
 FROM emp e  
 WHERE e.sal > (SELECT sal FROM emp WHERE ename='关羽')  

/ **************************************************/
/*3. 列出所有员工的姓名及其直接上级的姓名。*/
/*
列：员工名、领导名
表：emp、emp
条件：领导.empno=员工.mgr

emp表中存在自身关联，即empno和mgr的关系。
我们需要让emp和emp表连接查询。因为要求是查询所有员工的姓名，所以不能用内连接，因为曾阿牛是BOSS，没有上级，内连接是查询不到它的。
*/
# 解答
/* 内连接 */

[sql]view plaincopy 
   
 SELECT e.*,m.ename FROM emp e, emp m WHERE e.mgr=m.empno;  

/* 左外连接 */

[sql]view plaincopy 
   
 SELECT e.*,IFNULL(m.ename,'BOSS') leader FROM emp e  
 LEFT JOIN emp m ON e.mgr=m.empno  

/* 右外连接 */

[sql]view plaincopy 
   
 SELECT e.*,m.ename leader FROM emp e  
 RIGHT JOIN emp m ON e.mgr=m.empno  

# 答案
/* 左外连接 */

[sql]view plaincopy 
   
 SELECT e.ename, IFNULL(m.ename, 'BOSS') AS lead  
 FROM emp e   
 LEFT JOIN emp m ON e.mgr=m.empno;  

/**************************************************/
/*4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。*/
/*
列：编号、姓名、部门名称
表：emp、dept
条件：hiredate < 领导.hiredate

emp表需要查。部门名称在dept表中，所以也需要查。领导的hiredate需要查，这说明需要两个emp和一个dept连接查询
即三个表连接查询
*/
# 解答
/* 内连接 */

[sql]view plaincopy 
   
 SELECT e.empno,e.ename,d.dname  
 FROM emp e,emp m,dept d  
 WHERE e.mgr=m.empno AND e.deptno=d.deptno AND e.hiredate<m.hiredate  
 ORDER BY d.dname ASC, e.empno ASC;  

/* 左外连接 */

[sql]view plaincopy 
   
 SELECT e.empno,e.ename,d.dname  
 FROM emp e  
 LEFT JOIN emp m ON e.mgr=m.empno  
 LEFT JOIN dept d ON e.deptno=d.deptno  
 WHERE e.hiredate<m.hiredate  

/* 右外连接 */

[sql]view plaincopy 
   
 SELECT e.empno,e.ename,d.dname  
 FROM emp e  
 RIGHT JOIN emp m ON e.mgr=m.empno  
 RIGHT JOIN dept d ON e.deptno=d.deptno  
 WHERE e.hiredate<m.hiredate  

# 答案

[sql]view plaincopy 
   
 SELECT e.empno, e.ename, d.dname  
 FROM emp e   
 LEFT JOIN emp m ON e.mgr=m.empno   
 LEFT JOIN dept d ON e.deptno=d.deptno  
 WHERE e.hiredate<m.hiredate;  

/**************************************************/
/*5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。*/
/*
列：员工表所有列、部门名称
表：emp, dept
要求列出没有员工的部门，这说明需要以部门表为主表使用外连接
*/
# 解答
/* 左外连接 */

[sql]view plaincopy 
   
 SELECT d.dname,e.*  
 FROM dept d  
 LEFT JOIN emp e ON d.deptno=e.deptno  

# 答案

[sql]view plaincopy 
   
 SELECT e.*, d.dname  
 FROM emp e   
 RIGHT JOIN dept d ON e.deptno=d.deptno;  

/**************************************************/
/*6. 列出所有文员的姓名及其部门名称，部门的人数。*/
/*
列：姓名、部门名称、部门人数
表：emp emp dept
条件：job=文员
分组：emp以deptno得到部门人数
连接：emp连接emp分组，再连接dept
*/
# 解答
/* 内连接 */

[sql]view plaincopy 
   
 SELECT e.ename,e.job,d.dname,c.cnt  
 FROM emp e,dept d,(SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) c  
 WHERE e.deptno=d.deptno AND e.deptno=c.deptno AND e.job='文员';  

/* 左外连接 */

[sql]view plaincopy 
   
 SELECT e.ename,e.job,d.dname,c.cnt  
 FROM emp e  
 LEFT JOIN dept d ON e.deptno=d.deptno  
 LEFT JOIN (SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) c ON e.deptno=c.deptno  
 WHERE e.job='文员';  

/* 右外连接 */

[sql]view plaincopy 
   
 SELECT e.ename,e.job,d.dname,c.cnt  
 FROM emp e  
 RIGHT JOIN dept d ON e.deptno=d.deptno  
 RIGHT JOIN (SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) c ON e.deptno=c.deptno  
 WHERE e.job='文员'  
 ORDER BY c.cnt ASC;  

/**************************************************/
/*7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。*/
/*
列：工作，该工作人数
表：emp
分组：使用job分组
分组条件：min(sal)>15000
*/
# 解答
/* 内连接 */

[sql]view plaincopy 
   
 SELECT e.job,c.cnt   
 FROM emp e, (SELECT job,COUNT(*) cnt, MIN(sal) minsal FROM emp GROUP BY job) c  
 WHERE e.job = c.job AND c.minsal>15000;  

/* 左外连接 */

[sql]view plaincopy 
   
 SELECT e.job,c.cnt  
 FROM emp e  
 LEFT JOIN (SELECT job, COUNT(*) cnt, MIN(sal) minsal FROM emp GROUP BY job) c ON e.job=c.job  
 WHERE c.minsal>15000;  

/* 右外连接 */

[sql]view plaincopy 
   
 SELECT DISTINCT e.job,c.cnt  
 FROM emp e  
 RIGHT JOIN (SELECT job, COUNT(*) cnt, MIN(sal) minsal FROM emp GROUP BY job) c ON e.job=c.job  
 WHERE c.minsal>15000;  

# 答案

[sql]view plaincopy 
   
 SELECT job, COUNT(*)  
 FROM emp e  
 GROUP BY job  
 HAVING MIN(sal) > 15000;  

# 练习

[sql]view plaincopy 
   
 SELECT job, COUNT(*)  
 FROM emp e  
 GROUP BY job  
 HAVING MIN(sal) > 15000;  

/**************************************************/
/*8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。*/
/*
列：姓名
表：emp, dept
条件：所在部门名称为销售部，这需要通过部门名称查询为部门编号，作为条件
*/
/**************************************************/
# 解答
/* 内连接 */

[sql]view plaincopy 
   
 EXPLAIN SELECT e.ename  
 FROM emp e,dept d  
 WHERE e.deptno=d.deptno AND d.dname='销售部';  

/* 左外连接 */

[sql]view plaincopy 
   
 EXPLAIN SELECT e.ename  
 FROM emp e  
 LEFT OUTER JOIN dept d ON e.deptno=d.deptno  
 WHERE d.dname='销售部';  

/* 右外连接 */

[sql]view plaincopy 
   
 EXPLAIN SELECT e.ename  
 FROM emp e  
 RIGHT OUTER JOIN dept d ON e.deptno=d.deptno  
 WHERE d.dname='销售部';  

/* 子查询 */

[sql]view plaincopy 
   
 EXPLAIN SELECT e.ename  
 FROM emp e  
 WHERE e.deptno = (SELECT deptno FROM dept WHERE dname='销售部');  

# 答案

[sql]view plaincopy 
   
 SELECT e.ename  
 FROM emp e  
 WHERE e.deptno = (SELECT deptno FROM dept WHERE dname='销售部');  

/*9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。*/
/*
列：员工所有信息(员工表)，部门名称(部门表)，上级领导(员工表)，工资等级(等级表)
表：emp, dept, emp, salgrade
条件：sal>平均工资，子查询
所有员工，说明需要左外
*/
# 解答
/* 内连接 */

[sql]view plaincopy 
   
 SELECT e.*,d.dname,m.ename,s.grade  
 FROM emp e,dept d,emp m,salgrade s  
 WHERE e.deptno=d.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal AND s.hisal AND e.sal>(SELECT AVG(sal) avgsal FROM emp )  
 ORDER BY s.grade;  

/* 左外连接 */

[sql]view plaincopy 
   
 SELECT e.*,d.dname,m.ename,s.grade  
 FROM emp e  
 LEFT OUTER JOIN dept d ON e.deptno=d.deptno  
 LEFT OUTER JOIN emp m ON e.mgr=m.empno  
 LEFT OUTER JOIN salgrade s ON e.sal> s.losal AND e.sal<s.hisal  
 WHERE e.sal>(SELECT AVG(sal) avgsal FROM emp)  
 ORDER BY s.grade;  

/* 右外连接 */

[sql]view plaincopy 
   
 SELECT e.*,d.dname,m.ename,s.grade  
 FROM emp e  
 RIGHT JOIN dept d ON e.deptno=d.deptno  
 RIGHT OUTER JOIN emp m ON e.mgr=m.empno  
 RIGHT OUTER JOIN salgrade s ON e.sal> s.losal AND e.sal<s.hisal  
 WHERE e.sal>(SELECT AVG(sal) avgsal FROM emp)  
 ORDER BY s.grade;  

/* 子查询 */
# 内连接、左外连接、右外连接在where条件中都包含了子查询

# 答案

[sql]view plaincopy 
   
 SELECT e.*, d.dname, m.ename, s.grade  
 FROM emp e   
   NATURAL LEFT JOIN dept d  
   LEFT JOIN emp m ON m.empno=e.mgr  
   LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal  
 WHERE e.sal > (SELECT AVG(sal) FROM emp);  

/**************************************************/
/*10.列出与庞统从事相同工作的所有员工及部门名称。*/
/*
列：员工表所有列，部门表名称
表：emp, dept
条件：job=庞统的工作，需要子查询，与部门表连接得到部门名称
*/
# 解答
/* 内连接 */

[sql]view plaincopy 
   
 SELECT e.*,d.dname  
 FROM emp e, dept d  
 WHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='庞统');  

/* 左外连接 */

[sql]view plaincopy 
   
 SELECT e.*,d.dname  
 FROM emp e  
 LEFT JOIN dept d ON e.deptno=d.deptno  
 WHERE e.job=(SELECT job FROM emp WHERE ename='庞统');  

/* 右外连接 */

[sql]view plaincopy 
   
 SELECT e.*,d.dname  
 FROM emp e  
 RIGHT JOIN dept d ON e.deptno=d.deptno  
 WHERE e.job=(SELECT job FROM emp WHERE ename='庞统');  

/* 子查询 */
# 内连接、左外连接、右外连接在where条件部分都包含了子查询

# 答案

[sql]view plaincopy 
   
 SELECT e.*, d.dname  
 FROM emp e, dept d  
 WHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='庞统');  

/**************************************************/
/*11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。*/
/*
列：姓名、薪金、部门名称(需要连接查询)
表：emp, dept
条件：sal > all(30部门薪金)，需要子查询
*/
# 解答
/* 内连接 */

[sql]view plaincopy 
   
 SELECT e.ename,e.sal,d.dname  
 FROM emp e,dept d  
 WHERE e.deptno=d.deptno AND e.sal>(SELECT MAX(sal) maxsal FROM emp WHERE deptno=30)  
 ORDER BY e.sal;  

/* 左外连接 */

[sql]view plaincopy 
   
 SELECT e.ename,e.sal,d.dname  
 FROM emp e  
 LEFT JOIN dept d ON e.deptno=d.deptno  
 WHERE e.sal>(SELECT MAX(sal) maxsal FROM emp WHERE deptno=30)  
 ORDER BY e.sal;  

/* 右外连接 */

[sql]view plaincopy 
   
 SELECT e.ename,e.sal,d.dname  
 FROM emp e  
 RIGHT JOIN dept d ON e.deptno=d.deptno  
 WHERE e.sal>(SELECT MAX(sal) maxsal FROM emp WHERE deptno=30)  
 ORDER BY e.sal;  

/* 子查询 */
# 内连接、左外连接、右外连接在where条件中都存在子查询

# 答案

[sql]view plaincopy 
   
 SELECT e.ename, e.sal, d.dname  
 FROM emp e, dept d  
 WHERE e.deptno=d.deptno AND sal > ALL(SELECT sal FROM emp WHERE deptno=30)  

/**************************************************/
/*12.列出在每个部门工作的员工数量、平均工资。*/
/*
列：部门名称, 部门员工数，部门平均工资
表：emp, dept
分组：deptno
*/
# 解答
/* 内连接 */

[sql]view plaincopy 
   
 SELECT d.dname,c.cnt,c.avgsal  
 FROM dept d,(SELECT deptno,COUNT(*) cnt,AVG(sal) avgsal FROM emp GROUP BY deptno) c  
 WHERE d.deptno=c.deptno;  

/* 左外连接 */

[sql]view plaincopy 
   
 SELECT d.dname,c.cnt,c.avgsal  
 FROM dept d  
 LEFT JOIN (SELECT deptno,COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) c ON d.deptno=c.deptno  

/* 右外连接 */

[sql]view plaincopy 
   
 SELECT d.dname,c.cnt,c.avgsal  
 FROM dept d  
 RIGHT JOIN (SELECT deptno,COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) c ON d.deptno=c.deptno  

/* 子查询 */
# 内连接、左外连接、右外连接中在条件where部分都包含了子查询

# 答案

[sql]view plaincopy 
   
 SELECT d.dname, e.cnt, e.avgsal  
 FROM (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept d  
 WHERE e.deptno=d.deptno; 

WEL测试

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
MySQL多表SQL查询

01.创建数据库表结构并插入数据[sql] view plain copyDROP DATABASE exam; CREATE DATABASE exam; USE exam; /*创建部门表*/ CREATE TABLE dept( deptno INT PRIMARY KEY, dnam
复制链接

扫一扫