数据库SQL多表查询

qq_48475590

已于 2022-09-25 19:37:49 修改

阅读量441

点赞数 1

文章标签：数据库 mysql sql

于 2022-09-19 15:17:29 首次发布

本文链接：https://blog.csdn.net/qq_48475590/article/details/126934276

版权

准备工作!!!

复制以下代码到mysql中,点击执行自动创建emp表,detp表.

后面学习都基于emp表,detp表.

CREATE DATABASE /*!32312 IF NOT EXISTS*/`empdb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `empdb`;

DROP TABLE IF EXISTS `dept`;

CREATE TABLE `dept` (
                        `id` int(4) NOT NULL AUTO_INCREMENT,
                        `name` varchar(14) NOT NULL,
                        `loc` varchar(13) DEFAULT NULL,
                        PRIMARY KEY (`id`),
                        UNIQUE KEY `DNAME` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

insert  into `dept`(`id`,`name`,`loc`) values (1,'神仙','天庭'),(2,'妖怪','盘丝洞'),(3,'普通人','北京'),(4,'赛亚人','外星球');

DROP TABLE IF EXISTS `emp`;

CREATE TABLE `emp` (
                       `id` int(4) NOT NULL AUTO_INCREMENT,
                       `name` varchar(10) NOT NULL,
                       `job` varchar(9) DEFAULT NULL,
                       `manager` int(4) DEFAULT NULL,
                       `hiredate` date DEFAULT NULL,
                       `sal` double(7,2) DEFAULT NULL,
                       `comm` double(7,2) DEFAULT NULL,
                       `dept_id` int(4) DEFAULT NULL,
                       PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;


insert  into `emp`(`id`,`name`,`job`,`manager`,`hiredate`,`sal`,`comm`,`dept_id`) values (1,'孙悟空','销售',4,'1980-12-17',800.00,NULL,1),(2,'猪八戒','销售',4,'1981-02-20',1600.00,300.00,1),(3,'沙僧','销售',4,'1981-02-22',1250.00,500.00,1),(4,'唐僧','销售经理',8,'1981-04-02',2975.00,NULL,1),(5,'刘备','项目经理',NULL,'1981-09-28',1250.00,1400.00,3),(6,'关羽','程序员',5,'1981-05-01',2850.00,NULL,3),(7,'张飞','程序员',5,'1981-06-09',2450.00,NULL,3),(8,'观音','CEO',NULL,'1981-11-17',5000.00,NULL,1),(9,'白骨精','人事',8,'1981-09-08',1500.00,0.00,2),(10,'蜘蛛精','人事',8,'1981-12-03',950.00,NULL,2),(11,'黑熊怪','市场',8,'1981-12-03',3000.00,NULL,2);

#   SQL92语法：
# select 字段/字面量/
# from A表名/子查询(临时表),B表名/(子查询（临时表）)
# WHERE 连接条件(子查询)

#   SQL99语法
# select 字段/字面量/(子查询)
# from A表名/
# [left/right/full][inner] join B表名/(子查询（临时表）)
# on 连接条件
# [left/right/full][inner] join B表名/(子查询（临时表）)
# on 连接条件
# ...
# where 筛选条件(子查询);


#   SQL92与SQL99连接查询最大的区分是把连接表和连接条件分开了，
#           1、连接表92是在from中用’,‘隔开多个表。而99是用join来加入表，可以有多个join on。
#           2、连接条件92是在where中，99则是on里。
#           3、99的join on后面可以继续加入where条件。如此99的join on增加分担了where的责任，代码可读性更强了。

# [inner] join 新（原）表 on 连接条件:内连接,inner可省略不写。
#   自连接： join 原表（别名）
#           1、该表中的一条记录可以对应该表中的其他多条记录。换而言之就是自己连接自己。
#           2、当然这个自己指的是同一张表看做两张或两张以上表（取别名区分）连接查询。


# 如：查询每个员工的上级是谁
    SELECT a.name,b.name '领导'
    FROM emp a
    LEFT JOIN emp b ON a.manager=b.id;

#   等值连接：on 原表.字段=新表.字段
 



# left/right/full [outer] join on:外连接，outer可省略不写。
#   左外连接
# left [outer] join 新(原)表 on 连接条件
#       1、以左表为主也就是来自from的表，满足on的连接条件两表之间的对应数据相连，但是主表（左表）是一定会显示全部数据行的

#   右外连接
# right [outer] join 新(原)表 on 连接条件
#       1、以右表为主也就是来自from的表，满足on的连接条件两表之间的对应数据相连，但是主表（右表）是一定会显示全部数据行的

#   全连接
#   full outer join 新(原)表 on 连接条件 [（这里MYSQL不支持）但是我们可以使用一下union方法达到全连接效果]
# union（连接）:    left+right=全连接效果
#       union使用注意事项：
#               1、字段个数相等。
#               2、字段类型相等。
#               3、全连接实现原理就是将左外连接和右外连接出来的数据想加，就得到一个全（左右）连接。
#               4、由此结论：全连接数据行>=左（右）连接数据行数。


# 区分使用inner内连接、outer外连接场景
#   内连接查询的数据一定是两张表共有的数据，而外连接是以某张左右主表为中心，进行连接，
#   哪怕主表中有不符合两张表共有条件的数据那也会出现在主表中显示，而内连接则不会。
#   所以可以得出结论内连接<=外连接数据条数；


# 子查询
#   分三种情况
#       1、select + (子查询)
#           注意事项:查询的数据是一个字段，如果子查询中多余一个字段，在外面的select识别字段不出来是会报错的。
#       2、from + (子查询)
#           注意事项：查询的是一张临时表，作为一张临时表只生效于本次查询语句之中。作为表享有正常查询表的权利。
#       3、where + (子查询)
#           注意事项：查询出来的字段结果，注意字段只能是一个，如果多余一个是无法做判断的





# 案例

# 内连接案例：查询emp表中工资大于1300有部门的人员信息。
#   分析：emp表与dept表相连，并且加上判断条件工资大于1300。两者连接条件是emp的dept_id与dept的id，我们还可以确定emp表有工资所以on中放emp表的sal>1300


SELECT *
FROM emp a
         INNER JOIN dept d
                    on a.dept_id=d.id  #等值连接
WHERE a.sal>1300;

# 左外连接案例：查询emp表中所有人的部门信息（包括没有部门的）
#   分析：emp是主表。需要从dept表中去找寻emp.dept_id的数据，并且没有dept_id部门信息的员工也得查询出来。


SELECT *
FROM emp e
         LEFT OUTER JOIN dept d
                         ON e.dept_id=d.id;

#例题1：查询刘备的手下都有谁
#     思路：使用自连接，将a作为主表b作为副表。a表看做刘备，b表看做刘备手下


SELECT *
FROM emp a
         JOIN  emp b
               ON a.manager=b.id
WHERE b.name='刘备';

# 例题2：谁的工资高于孙悟空的领导工资
#       思路：先找出孙悟空的领导，再判断高于领导工资的人员信息


SELECT *
FROM emp c
         JOIN (SELECT b.name,b.id,b.sal
               FROM emp a
                        JOIN emp b ON a.manager=b.id
               WHERE a.name='孙悟空') d
WHERE c.sal>d.sal;
# 或者
SELECT *
FROM emp
WHERE sal>(SELECT b.sal
           FROM emp a
                    JOIN emp b ON a.manager=b.id
           WHERE a.name='孙悟空');
# 以上两种用法考察的是from和where后子查询、自连接使用。

#查询比本部门平均薪资高的员工信息
#   分析：本部门的话是需要分组的。如果考虑到在where中分组求平均会得出多个数据值，用子查询单一判断是不可能的。
#           所以我们把子查询放到from中做一个临时表使用，把分组的部门信息看做一个临时表。紧接着对原表（主表）进行内连接查询。


SELECT *
FROM emp e
         join (SELECT avg(sal) sal,dept_id id
               FROM emp
               GROUP BY dept_id) d
              ON e.dept_id=d.id
WHERE e.sal>d.sal;

# 查看比所在地区平均工资高的员工


# 一、
#   SELECT * FROM emp;
#   SELECT * FROM dept;
#   比较笨的两张临时表查询
#   分析：1、员工信息在emp表中
#        2、地区在dept表中
#        3、如此我们需要将两张表结合起来，然后再求出每个地区的工资平均值
#        4、思路：①两张表结合可以内连接为一张总表临时表，②给每个地区求平均工资临时表，
#        5、对着前①②步两张临时表进行左外连接（①主表），然后判断①表>②表数据
SELECT *
FROM (SELECT z.name,z.sal,z.dept_id,y.id,y.loc
        FROM emp z
            JOIN dept y
        ON z.dept_id=y.id) H
LEFT JOIN (SELECT AVG(e.sal) avg_sal,d.loc
      FROM emp e
               JOIN dept d
                    ON e.dept_id=d.id
      GROUP BY loc) J
ON H.loc = J.loc
WHERE H.sal>J.avg_sal;


# 二、
# 利用中间表的方式去查询，
#   实现思路：将dept表作为中间表，dept连接emp、和地区平均工资临时表。然后判断emp的工资>地区平均工资临时表;
SELECT *
FROM emp e
join dept d
join (select avg(sal) avg_sal,d.loc FROM emp e,dept d WHERE e.dept_id=d.id group by d.loc) c
on d.loc=c.loc and e.sal>c.avg_sal and e.dept_id=d.id;

# 查看每个地区的最高工资是谁


#     步骤一：查询地区的最高工资: SELECT MAX(sal),loc FROM emp e JOIN dept d ON e.dept_id = d.id GROUP BY d.loc
#     步骤二：引入主表emp员工表: FROM emp
#     步骤三：引入中间表dept部门表: JOIN dept
#     步骤四：查询条件编写: ON e.dept_id=d.id AND d.loc=c.loc AND e.sal=c.max_sal
#             1、先判断员工表和部门表之间的联系。
#             2、再判断部门表和地区最高工资临时表之间的联系
#             3、最后判断员工表中工资等于最高工资临时表的员工信息。
SELECT e.name
FROM emp e
JOIN dept d
JOIN   (SELECT MAX(sal) max_sal,loc
        FROM emp e
            JOIN dept d ON e.dept_id = d.id
        GROUP BY d.loc) c
ON e.dept_id=d.id
AND d.loc=c.loc
AND e.sal=c.max_sal;

可以拿以下六十二个题练练手:

1. 查询工资大于等于3000的员工姓名和工资
2. 查询1号部门的员工姓名和工作
3. 查询不是程序员的员工姓名和工作(两种写法)
4. 查询奖金等于300的员工姓名,工资和工作
5. 查询1号部门工资大于2000的员工信息
6. 查询3号部门或工资等于5000的员工信息
7. 查询出CEO和项目经理的名字
8. 查询工资为3000,1500和5000的员工信息
9. 查询工资不等于3000,1500和5000的员工信息
10. 查询工资在1000到2000之间的员工信息
11. 查询工资在1000到2000以外的员工信息
12. 查询有领导的员工姓名和领导id
13. 查询没有领导的员工姓名和领导id
14. 查询员工表中出现了哪几种不同的工作
15. 查询员工表中出现了那几个部门的id
16. 查询姓孙的员工姓名
17. 查询名字最后一个字是精的员工信息
18. 查询工作中包含销售的员工信息
19. 查询工作中第二个字是售的员工信息
20. 查询名字中包含僧的员工并且工资高于2000的员工信息
21. 查询1号和2号部门中工作以市开头的员工信息
22. 查询所有员工的姓名和工资 按照工资升序排序
23. 查询所有员工的姓名和工资 按照工资降序排序
24. 查询所有员工姓名 工资和部门id 按照部门id降序排序,如果部门id一致则按照工资升序排序
25. 查询员工表中3号部门工资高于1500的员工信息
26. 查询2号部门员工或者没有领导的员工信息
27. 查询有领导的员工姓名,工资按照工资降序排序
28. 查询2号和3号部门的员工姓名和入职日期hiredate 按照入职日期降序排序
29. 查询名字中包含僧和包含精的员工姓名
30. 查询工资高于2000的工作有哪几种?
31. 查询工资最高的前三个员工
32. 查询员工表按照id排序, 第2页的5条数据
33. 查询员工表按照id排序, 第3页的4条数据
34. 查询3号部门工资最低的员工姓名和工资
35. 查询工作不是人事的员工中工资降序第二页的3条数据
36. 查询没有领导的员工和3号部门的员工,工资降序取前三条
37. 查询2号部门的最高工资
40. 查询有领导的员工中工资在1000到2000之间的人数
41. 查询3号部门的工资总和
42. 查询程序员和销售的总人数
43. 查询1号部门有领导的员工的平均工资
44. 查询1号部门的最低工资和最高工资
45. 查询和销售相关的工作人数
46. 查询工资不是1500和3000的员工人数
47. 查询1号部门出现了哪几种工作
48. 查询名字包含精的员工数量
49. 查询和销售相关的工作一个月工资总和
50. 查询2号部门的最高工资和最低工资起别名
51.查询每个部门的平均工资
52. 查询每种工作的平均工资
53. 查询每个部门的最高工资
54. 查询每种工作的最低工资
55. 查询每个部门工资高于2000的人数
56. 查询每个部门有领导的员工人数
57. 查询1号部门每种工作的最低工资
58. 查询平均工资最高的部门id和平均工资
59. 查询每个部门的平均工资,要求平均工资大于2000
60. 查询每种工作的人数,只查询人数大于1的
61. 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400.
62. 查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的

                         如下是我自己做的(可以按自己的思路去实现):


# 1. 查询工资大于等于3000的员工姓名和工资
SELECT name,sal FROM emp WHERE sal>=3000;

# 2. 查询1号部门的员工姓名和工作
SELECT name,job FROM emp WHERE dept_id=1;

# 3. 查询不是程序员的员工姓名和工作(两种写法)
SELECT name,job FROM emp WHERE name<>'程序员';
SELECT name,job FROM emp WHERE name!='程序员';

# 4. 查询奖金等于300的员工姓名,工资和工作
SELECT name,sal,job FROM emp WHERE comm=300;
SELECT name,sal,job FROM emp WHERE comm IN(300);

# 5. 查询1号部门工资大于2000的员工信息
SELECT * FROM emp WHERE sal>2000 AND dept_id=1;

# 6. 查询3号部门或工资等于5000的员工信息
SELECT * FROM emp WHERE dept_id=3 OR sal=5000;

# 7. 查询出CEO和项目经理的名字
SELECT name FROM emp WHERE job IN('CEO','项目经理');
SELECT name FROM emp WHERE job='CEO' OR job='项目经理';

# 8. 查询工资为3000,1500和5000的员工信息
SELECT * FROM emp WHERE sal IN(3000,1500,5000);

# 9. 查询工资不等于3000,1500和5000的员工信息
SELECT * FROM emp WHERE sal NOT IN(3000,1500,5000);

# 10. 查询工资在1000到2000之间的员工信息
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000;

# 11. 查询工资在1000到2000以外的员工信息
SELECT * FROM emp WHERE sal NOT BETWEEN 1000 AND 2000;

# 12. 查询有领导的员工姓名和领导id
SELECT name,id FROM emp WHERE manager IS NOT NULL;

# 13. 查询没有领导的员工姓名和领导id
SELECT name,id FROM emp WHERE manager IS NULL;

# 14. 查询员工表中出现了哪几种不同的工作
SELECT DISTINCT job FROM emp;

# 15. 查询员工表中出现了那几个部门的id
SELECT DISTINCT dept_id FROM emp;

# 16. 查询姓孙的员工姓名
SELECT name FROM emp WHERE name LIKE '%孙%';

# 17. 查询名字最后一个字是精的员工信息
SELECT * FROM emp WHERE name LIKE '%精';

# 18. 查询工作中包含销售的员工信息
SELECT * FROM emp WHERE job LIKE '%销售%';

# 19. 查询工作中第二个字是售的员工信息
SELECT * FROM emp WHERE job LIKE '_售%';

# 20. 查询名字中包含僧的员工并且工资高于2000的员工信息
SELECT * FROM emp WHERE sal>2000 AND name LIKE '%僧%';

# 21. 查询1号和2号部门中工作以市开头的员工信息
SELECT * FROM emp WHERE job LIKE '市%' AND dept_id IN(1,2);

# 22. 查询所有员工的姓名和工资 按照工资升序排序
SELECT name,sal FROM emp ORDER BY sal ASC ;

# 23. 查询所有员工的姓名和工资 按照工资降序排序
SELECT name,sal FROM emp ORDER BY sal DESC ;

# 24. 查询所有员工姓名 工资和部门id 按照部门id降序排序,如果部门id一致则按照工资升序排序
SELECT name,sal,dept_id FROM emp ORDER BY dept_id DESC,sal ASC;

# 25. 查询员工表中3号部门工资高于1500的员工信息
SELECT * FROM emp WHERE dept_id=3 AND sal>1500;

# 26. 查询2号部门员工或者没有领导的员工信息
SELECT * FROM emp WHERE dept_id=2 OR manager IS NULL;

# 27. 查询有领导的员工姓名,工资按照工资降序排序
SELECT name FROM emp WHERE manager IS NOT NULL ORDER BY sal DESC;

# 28. 查询2号和3号部门的员工姓名和入职日期hiredate 按照入职日期降序排序
SELECT name,hiredate FROM emp WHERE dept_id IN(2,3) ORDER BY hiredate DESC ;

# 29. 查询名字中包含僧和包含精的员工姓名
SELECT name FROM emp WHERE name LIKE '%僧%' OR name LIKE '%精%';

# 30. 查询工资高于2000的工作有哪几种?
SELECT DISTINCT job FROM emp WHERE sal>2000;

# 31. 查询工资最高的前三个员工
SELECT name,sal FROM emp ORDER BY sal DESC LIMIT 0,3;

# 32. 查询员工表按照id排序, 第2页的5条数据
SELECT * FROM emp ORDER BY id ASC LIMIT 5,5;

# 33. 查询员工表按照id排序, 第3页的4条数据
SELECT * FROM emp ORDER BY id ASC LIMIT 8,4;

# 34. 查询3号部门工资最低的员工姓名和工资
SELECT name,sal FROM emp WHERE dept_id=3 ORDER BY sal ASC LIMIT 0,1;
# SELECT MIN(sal) FROM emp WHERE dept_id IN (SELECT dept_id FROM emp WHERE dept_id IN(3));
SELECT MIN(sal) FROM emp GROUP BY dept_id HAVING dept_id=3;

# 35. 查询工作不是人事的员工中工资降序第二页的3条数据
SELECT * FROM emp WHERE job NOT IN('人事') ORDER BY sal DESC LIMIT 3,3;

# 36. 查询没有领导的员工和3号部门的员工,工资降序取前三条
SELECT * FROM emp WHERE manager IS NULL AND dept_id=3 ORDER BY sal DESC LIMIT 0,3;

# 37. 查询2号部门的最高工资
SELECT MAX(sal) FROM emp WHERE dept_id=2;

# 40. 查询有领导的员工中工资在1000到2000之间的人数
SELECT COUNT(*) FROM emp WHERE manager IS NOT NULL AND sal BETWEEN 1000 AND 2000;

# 41. 查询3号部门的工资总和
SELECT SUM(sal) FROM emp WHERE dept_id=3;

# 42. 查询程序员和销售的总人数
SELECT COUNT(*) FROM emp WHERE job IN('程序员','销售');

# 43. 查询1号部门有领导的员工的平均工资
SELECT AVG(IFNULL(sal,0)) FROM emp WHERE dept_id=1;

# 44. 查询1号部门的最低工资和最高工资
SELECT MIN(sal) AS 'min_sal',MAX(sal) AS 'max_sal' FROM emp WHERE dept_id=1;

# 45. 查询和销售相关的工作人数
SELECT COUNT(*) FROM emp WHERE job LIKE '%销售%';

# 46. 查询工资不是1500和3000的员工人数
SELECT COUNT(*) FROM emp WHERE sal NOT IN(1500,3000);

# 47. 查询1号部门出现了哪几种工作
SELECT DISTINCT job FROM emp WHERE dept_id=1;

# 48. 查询名字包含精的员工数量
SELECT COUNT(*) FROM emp WHERE name LIKE '%精%';

# 49. 查询和销售相关的工作一个月工资总和
SELECT SUM(sal) FROM emp WHERE job LIKE '%销售%';

# 50. 查询2号部门的最高工资和最低工资起别名
SELECT MAX(sal) AS 'max_sal',MIN(sal) AS 'min_sal' FROM emp WHERE dept_id=2;

# 51.查询每个部门的平均工资
SELECT AVG(IFNULL(sal,0)) FROM emp GROUP BY dept_id;

# 52. 查询每种工作的平均工资
SELECT AVG(IFNULL(sal,0)) FROM emp GROUP BY job;

# 53. 查询每个部门的最高工资
SELECT MAX(sal) FROM emp GROUP BY dept_id;

# 54. 查询每种工作的最低工资
SELECT MIN(sal) FROM emp GROUP BY job;

# 55. 查询每个部门工资高于2000的人数
SELECT COUNT(*) FROM emp WHERE sal>2000 GROUP BY dept_id;

# 56. 查询每个部门有领导的员工人数
SELECT COUNT(*) FROM emp WHERE manager IS NOT NULL GROUP BY dept_id;

# 57. 查询1号部门每种工作的最低工资
SELECT MIN(sal) FROM emp WHERE dept_id=1 GROUP BY job;

# 58. 查询平均工资最高的部门id和平均工资
SELECT AVG(IFNULL(sal,0)) AS 'avg_sal',dept_id FROM emp GROUP BY dept_id ORDER BY avg_sal DESC LIMIT 0,1;

# 59. 查询每个部门的平均工资,要求平均工资大于2000
SELECT AVG(IFNULL(sal,0)) AS 'avg_sal' FROM emp GROUP BY dept_id HAVING avg_sal>2000;

# 60. 查询每种工作的人数,只查询人数大于1的
SELECT COUNT(*) FROM emp GROUP BY job HAVING COUNT(*)>1;

# 61. 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400.
SELECT SUM(sal) FROM emp WHERE manager IS NOT NULL  GROUP BY dept_id HAVING SUM(sal)>5400;

# 62. 查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的
SELECT AVG(IFNULL(sal,0)) FROM emp WHERE sal BETWEEN 1000 AND 3000 GROUP BY dept_id HAVING AVG(IFNULL(sal,0))>2000;

DQL单表查询语句见：https://blog.csdn.net/qq_48475590/article/details/126907188?spm=1001.2014.3001.5502

JDBC操作可看此文: https://blog.csdn.net/qq_48475590/article/details/127041394?spm=1001.2014.3001.5502