mysql多表联合查询练习题
1、查询⾄少有⼀个员⼯的部门，显⽰部门编号，部门名称，部门位置，部门⼈数
/*
1、查询⾄少有⼀个员⼯的部门，显⽰部门编号，部门名称，部门位置，部门⼈数
列：d.deptno, d.dname, d.loc, 部门⼈数  (要查询部门⼈数需要使⽤分组)
表：dept d, emp e
条件：e.deptno = d.deptno
*/
SELECT *    -- 查询部门表
FROM dept;
SELECT deptno, COUNT(*)  -- 根据分组查询员⼯表部门⼈数
FROM emp
GROUP BY deptno;
-- 先将单独的两部分内容分别查询出来，在使⽤内链接把他们联合起来
-- 第⼀步：先分析有那些列，需要查询那些表，条件有什么
-- 第⼆步：将要查询的信息在⼦表中分别查询出来、
-- 第三步：将查询的信息链接在⼀起
-- 第四步：筛选出要使⽤的信息
SELECT d.*, z1.ent as '部门⼈数'  -- 在SELECT中不能直接只⽤ z1.COUNT(*) 要使⽤别名
FROM dept d, (SELECT deptno, COUNT(*) ent FROM emp GROUP BY deptno) z1    -- 在from中添加⼦查询（内连接）WHERE d.deptno = z1.deptno;    -- 去笛卡尔积  -- 使⽤了内连接40部门没有员⼯，所以没有显⽰在结果中
2、列出所有员⼯的姓名及其直接上级的名字
/*
2、列出所有员⼯的姓名及其直接上级的名字
列：员⼯姓名、上级姓名
表：emp e, emp m  -- ⼀个表重复查询两次⼀个表⽰员⼯⼀个表⽰经理
条件：e.empno = m.mgr
*/
ame, m.ename
FROM emp e, emp m
= m.empno;
-- 在表中曾阿⽜没有对应的领导，如果想显⽰他就必须使⽤左外链接
-- ame, m.ename -- 可以使⽤ifnull将null值修改为指定信息
ame as '员⼯', ame,'BOSS') as '领导'
FROM emp e LEFT JOIN emp m
= m.empno
3、列出受雇⽇期早于直接上级的所有员⼯的编号、姓名、部门名称
3、列出受雇⽇期早于直接上级的所有员⼯的编号、姓名、部门名称
列：e.ame d.dname
表：emp e, emp m, dept d
条件：e.hiredate < m.hiredate
思路：1、先进⾏单表查询（不查询部门名称），只查询部门编号
列：e.empno, e.ename, e.deptno
表：emp e, emp m;
条件：去笛卡尔积（e.mgr = m.empno）, e.hiredate < m.hiredate
pno, e.ename, e.deptno -- , e.hiredate, m.ename, m.hiredate
FROM emp e, emp m
= m.empno AND e.hiredate < m.hiredate;
*/
pno, e.ename, d.dname
FROM emp e, emp m, dept d
= m.empno AND e.hiredate < m.hiredate AND e.deptno = d.deptno;
4、列出部门名称和这些部门的员⼯信息，同时列出那些没有员⼯的部门
/*
5、列出部门名称和这些部门的员⼯信息，同时列出那些没有员⼯的部门
列：*
表：emp e, dept d
条件：e.deptno = d.deptno
先查出部门名称和员⼯信息
SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno
然后在查询没有员⼯的部门（右外连接）
*/
SELECT *
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno
6、列出最低薪⾦⼤于15000的各种⼯作以及从事此⼯作的员⼯⼈数
/*
6、列出最低薪⾦⼤于15000的各种⼯作以及从事此⼯作的员⼯⼈数
列：job count(*)
表：emp e
条件：min(sal)>15000
分组：job
分组后查询⽤HAVING
*/
SELECT job, COUNT(*)
FROM emp e
GROUP BY job
HAVING MIN(sal) > 15000
7、列出在销售部⼯作的员⼯的姓名，假定不知道销售部的部门编号
7、列出在销售部⼯作的员⼯的姓名，假定不知道销售部的部门编号
列：e.ename
表：empcluster统计
条件：e.deptno={SELECT deptno FROM dept WHERE dnam='销售部'}  -- 设置⼦查询
*/
SELECT *
FROM emp e
WHERE e.deptno=(SELECT deptno FROM dept WHERE dname='销售部')
8、列出薪资⾼于公司⼯资平均薪⾦的所有员⼯信息，所在部门名称，上级领导，⼯资等级
/*
8、列出薪资⾼于公司⼯资平均薪⾦的所有员⼯信息，所在部门名称，上级领导，⼯资等级
-- 分别查询三张表中的信息
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
列：*
表：emp e
条件：sal>(查询出公司的平均⼯资)  -- ⼦查询
8.1 列出薪资⾼于公司⼯资平均薪⾦的所有员⼯信息
SELECT *
FROM emp e
WHERE e.sal>(SELECT AVG(sal) FROM emp)
8.2 加上：部门名称
SELECT e.*, d.dname
FROM emp e, dept d  -- 这样查询会少⼀条张三的信息。因为他没有部门，所以要才⽤左连接的⽅式
WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno = d.deptno
8.4加上：上级领导
SELECT e.*, d.dname, m.ename
FROM emp e, dept d, emp m  -- 这样查询会少两条信息，张三没有部门，曾阿⽜没有上级领导
WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno = d.deptno = m.empno
8.5加上：⼯资等级
SELECT e.*, d.dname, m.ename, s.grade
FROM emp e, dept d, emp m, salgrade s
WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno = d.deptno = m.empno AND e.sal BETWEEN s.losal AND s.hisal  -- ⼯资只要在losal和h
8.6 使⽤左外链接查询缺省数据
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 = m.empno
LEFT OUTER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal>(SELECT AVG(sal) FROM emp)
mysql语句多表查询*/
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 = m.empno
LEFT OUTER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal>(SELECT AVG(sal) FROM emp)
9、列出与庞统从事相同⼯作的所有员⼯及部门名称
9、列出与庞统从事相同⼯作的所有员⼯及部门名称
列：e.*. d.dname
表：emp e, dept d
条件：job=(查询出庞统的⼯作)  -- 注意：去笛卡尔
*/
SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND job=(SELECT job FROM emp WHERE ename='庞统' )
10、列出薪⾦⾼于在部门30⼯作的所有员⼯的薪资的员⼯姓名和薪⾦、部门名称
/*
10、列出薪⾦⾼于在部门30⼯作的所有员⼯的薪资的员⼯姓名和薪⾦、部门名称。
列：e.ename, e.sal, d.dname
表：emp e, dept d
条件：sal > all (30部门薪⾦)
*/
shell正则匹配ame, e.sal, d.dname
FROM  emp e, dept d
WHERE e.deptno = d.deptno AND sal > ALL(SELECT sal FROM emp WHERE deptno=30)
11、 查出年份、利润、年增长⽐
/*
11、查出年份、利润、年增长⽐
11.1 -- 在同⼀⾏中的数据才能进⾏计算，所以⼀个要查询两次，然后在去笛卡尔积
SELECT *
FROM tb_year y1, tb_year y2  -- 注意第⼀年的数据没有关联关系，所以⽆法查询，要使⽤左外链接查询  ar = y2.year+1;
11.2 左外链接查询
SELECT *
FROM tb_year y1 LEFT OUTER JOIN tb_year y2
ar = y2.year+1;
11.3 计算年增长⽐
-- 使⽤ ifnull 修改 null 数据显⽰类型，使⽤ concat 添加%号，使⽤ as 添加别名
SELECT y1.*, IFNULL(CONCAT((y1.)/y2.zz*100,'%'), '0%')  AS '增长⽐'
FROM tb_year y1 LEFT OUTER JOIN tb_year y2
ar = y2.year+1;
*/
SELECT y1.*, IFNULL(CONCAT((y1.)/y2.zz*100,'%'), '0%')  AS '增长⽐'
FROM tb_year y1 LEFT OUTER JOIN tb_year y2
ar = y2.year+1;
数据源：mysql5.0.0
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int(11) NOT NULL,
the forest下载`dname` varchar(50) default NULL,
`loc` varchar(50) default NULL,
PRIMARY KEY  (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('10', '教研部', '北京');
INSERT INTO `dept` VALUES ('20', '学⼯部', '上海');
INSERT INTO `dept` VALUES ('30', '销售部', '⼴州');
INSERT INTO `dept` VALUES ('40', '财务部', '武汉');
-- ----------------------------
-
- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`empno` int(11) NOT NULL,
`ename` varchar(50) default NULL,
`job` varchar(50) default NULL,
`mgr` int(11) default NULL,
`hiredate` date default NULL,
`sal` decimal(7,2) default NULL,
`COMM` decimal(7,2) default NULL,
`deptno` int(11) default NULL,
PRIMARY KEY  (`empno`),
KEY `fk_emp` (`mgr`),
CONSTRAINT `fk_emp` FOREIGN KEY (`mgr`) REFERENCES `emp` (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('1001', '⽢宁', '⽂员', '1013', '2000-12-17', '8000.00', null, '20'); INSERT INTO `emp` VALUES ('1002', '黛绮丝', '销售员', '1006', '2001-02-20', '16000.00', '3000.00', '30'); INSERT INTO `emp` VALUES ('1003', '殷天正', '销售员', '1006', '2001-02-22', '12500.00', '5000.00', '30'); INSERT INTO `emp` VALUES ('1004', '刘备', '经理', '1009', '2001-04-02', '29750.00',
null, '20'); INSERT INTO `emp` VALUES ('1005', '谢逊', '销售员', '1006', '2001-09-28', '12500.00', '14000.00', '30'); INSERT INTO `emp` VALUES ('1006', '关⽻', '经理', '1009', '2001-05-01', '28500.00', null, '30'); INSERT INTO `emp` VALUES ('1007', '张飞', '经理', '1009', '2001-09-01', '24500.00', null, '10'); INSERT INTO `emp` VALUES ('1008', '诸葛亮', '分析师', '1004', '2007-09-01', '30000.00', null, '20'); INSERT INTO `emp` VALUES ('1009', '曾阿⽜', '董事长', null, '2001-11-17', '50000.00', null, '10'); INSERT INTO `emp` VALUES ('1010', '韦⼀笑', '销售员', '1006', '2001-09-08', '15000.00', '0.00', '30'); INSERT INTO `emp` VALUES ('1011', '周泰', '⽂员', '1008', '2007-05-28', '11000.00', null, '20'); INSERT INTO `emp` VALUES ('1012', '程普', '⽂员', '1006', '2001-12-03', '9500.00', null, '30'); INSERT INTO `emp` VALUES ('1013', '庞统', '分析师', '1004', '2001-12-09', '30000.00', null, '20'); INSERT INTO `emp` VALUES ('1014', '黄盖', '⽂员', '1007', '2002-01-23', '13000.00', null, '10'); INSERT INTO `emp` VALUES ('1015', '张三', '保洁员', '1009', '1999-09-09', '80000.00', '90000.00', '50');
-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`grade` int(11) NOT NULL,
`losal` int(11) default NULL,
`hisal` int(11) default NULL,
如何制作网页背景视频PRIMARY KEY  (`grade`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of salgrade
-- ----------------------------
java map遍历如何防止删除元素INSERT INTO `salgrade` VALUES ('1', '7000', '12000');
INSERT INTO `salgrade` VALUES ('2', '12010', '14000');
INSERT INTO `salgrade` VALUES ('3', '14010', '20000');

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。