mysql虚拟表查询_单表查询、多表查询、虚拟表连接查询

最新推荐文章于 2023-01-12 08:35:57 发布
最新推荐文章于 2023-01-12 08:35:57 发布
阅读量1k 收藏 1
点赞数
文章标签： mysql虚拟表查询
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39710991/article/details/113464472
版权

单表查询，以下面这个表为例：

+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+

| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |

+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+

| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |

| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |

| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300 | 401 | 1 |

| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500 | 401 | 1 |

| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100 | 401 | 1 |

| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000 | 401 | 1 |

| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000 | 401 | 1 |

| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000 | 401 | 1 |

| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |

| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |

| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |

| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |

| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |

| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |

| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000 | 403 | 3 |

| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000 | 403 | 3 |

| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000 | 403 | 3 |

| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000 | 403 | 3 |

+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+

select distinct 字段1,字段2，字段3，... from 库名.表名 #distinct 去重复功能

where约束条件

group by 按照分组依据，分组查询

having 分组后的过滤条件(针对组级别的过滤)

order by 排序依据

limit n; 限制显示的条目

语法：

1.注意先切换路径use db8，找到文件、打开文件、for循环可能是多读多行才发送给服务端，这样会减少IO

select * from db8.emp

from db8.emp

2.优先级顺序

from打开文件(把表从硬盘读入内存) ——>where条件过滤——>group by分组

执行顺序——————>————————>

group by:聚合函数(avg、sum、max、min等)只能在分完组之后才能使用

如果不写，其实默认是有where、group by分组的

例如：

select max(salary) from emp;

+-------------+

| max(salary) |

+-------------+

| 1000000.31 |

+-------------+

distinct:去的是记录的重复

select distinct sex from emp; 去除筛选的重复的sex

+--------+

| sex |

+--------+

| male |

| female |

+--------+

where 过滤：

1.between..and (包含=两边的)

select id,name from emp where id between 3and6; # id>=3 and id<=6

not between 3 and 6 #可以取反

2.in

select * from emp where salaryin(17000,18000,20000); #最终版(取代连续or的条件)

not in #可以取反

3.like: _代表任意单个字符 %：代表任意无穷个字符

要求:查找emp里面含有‘i’字母的员工姓名与其薪资

select name,salary from emp where name like '%i%';

要求:查询员工姓名由四个字符组成的员工姓名与其薪资

select name,salary from emp where namelike '____'；

要求:查询员工职位描述post_comment为空姓名与职位: (判断NULL只能用is，不能用=)

select name,post from emp where post_comment isNULL;

is not NULL #取反

group by分组： 聚合函数(avg、sum、max、min等)只能在分完组之后才能使用

什么是分组：找重复度高的，即有大量重复的字段去使用(id、name重复度太低，不能使用group by)

为何要分组：当我们要以组为单位进行统计时就必须分组

补充：#设置sql_mode为only_full_group_by,一位着以后分组，只能取分组的依据

查看sql_mode:

1.show variables like 'sql_mode';

+---------------+---------------------+

| Variable_name | Value |

+---------------+---------------------+

| sql_mode | STRICT_TRANS_TABLES |

+---------------+---------------------+

2.set global sql_mode='strict_trans_tables,only_full_group_by' ; 这个严格模式设置完之后以后就只能取组名，单 单独分组后的内容是看不到的

即只能取：select post from emp group by post; (name,id。。等都取不到)，说明分组是为了整体来用，把数据聚集在一起，不是为 了单独取某条数据

3.select @@sql_mode #查看是否更改成功

1.max求每个部门最高工资(select * from emp group by post;分完组之后默认*取出的是每个组的第一个人)

分组目的是以组为单位做一个整体的聚合，拿到聚合之后的结果

select post,max(salary) from emp group by post;

取每个部门最低工资：

select post,min(salary) from emp group by post;

取部门的平均工资：

select post,avg(salary) from emp group by post;

取部门的工资总和：

select post,sum (salary) from emp group by post;

统计每个部门个数：(按照id)

select post,count(id) from emp group by post;

having by：过滤条件，可以用聚合函数，因为是在分组之后

where分组之前做的过滤

having 分组之后专门对聚合的结果做进一步筛选

select age from emp group by age having age > 28;

group_concat字符串拼接：

查看所有部门和对应的员工名字：

select post,group_concat(name) from emp group by post;

+----------------------------+-------------------------------------------------------+

| post | group_concat(name) |

+----------------------------+-------------------------------------------------------+

| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |

| sale | 格格,星星,丁丁,丫丫,歪歪 |

| teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |

| 老男孩驻沙河办事处外交大使 | egon |

+----------------------------+-------------------------------------------------------+

select post,group_concat(name,'_perfect') from emp group by post; #实质就是一个字符串的凭借

select post,group_concat(salary) from emp group by post;

select post,group_concat(name,':',salary) from emp group by post;

练习：

1.查询岗位名以及包含的所有员工的名字：

select post,group_concat(name) from emp group by post;

2.查询岗位名已经岗位内包含的员工个数

select post,count(id) from emp group by post;

3.所有男员工、女员工的个数：

select sex,count(id) from emp group by sex;

4.取部门的平均工资：

select post,avg(salary) from emp group by post;

5.查询男员工与女员工的平均薪资

select sex,avg(salary) from emp group by sex;

6.统计各部门年龄在30岁以上的员工平均工资：(分析表的结果包含：部门、平均薪资)

select post,avg(salary) from emp where age >= 30 group by post;

+---------+---------------+

| post | avg(salary) |

+---------+---------------+

| sale | 2500.240000 |

| teacher | 255450.077500 |

+---------+---------------+

补充concat不分组时用

select name as 姓名，salary as 薪资 from emp;

select concat('Name:',name) as 姓名,concat('Sal:',salary) as 薪资 from emp;

+-----------------+----------------+

| 姓名 | 薪资 |

+-----------------+----------------+

|Name:egon |Sal:7300.33 |

| Name:alex | Sal:1000000.31 |

| Name:wupeiqi | Sal:8300.00 |

| Name:yuanhao | Sal:3500.00 |

| Name:liwenzhou | Sal:2100.00 |

| Name:jingliyang | Sal:9000.00 |

| Name:jinxin | Sal:30000.00 |

| Name:成龙 | Sal:10000.00 |

| Name:歪歪 | Sal:3000.13 |

| Name:丫丫 | Sal:2000.35 |

| Name:丁丁 | Sal:1000.37 |

| Name:星星 | Sal:3000.29 |

| Name:格格 | Sal:4000.33 |

| Name:张野 | Sal:10000.13 |

| Name:程咬金 | Sal:20000.00 |

| Name:程咬银 | Sal:19000.00 |

| Name:程咬铜 | Sal:18000.00 |

| Name:程咬铁 | Sal:17000.00 |

+-----------------+----------------+

select concat(name,':',age,':',sex) from emp; #正常情况每个字段间都要输入：

select concat_ws(':',name,age,sex) from emp; #concat_ws就是需要在多个表中间要加‘：’时，只需要在最前面加‘：’就自动会在多个字段加

mysql> select concat_ws(':',name,age,

sex) from emp;

+-----------------------------+

| concat_ws(':',name,age,sex) |

+-----------------------------+

| egon:18:male |

| alex:78:male |

| wupeiqi:81:male |

| yuanhao:73:male |

| liwenzhou:28:male |

| jingliyang:18:female |

| jinxin:18:male |

| 成龙:48:male |

| 歪歪:48:female |

| 丫丫:38:female |

| 丁丁:18:female |

| 星星:18:female |

| 格格:28:female |

| 张野:28:male |

| 程咬金:18:male |

| 程咬银:18:female |

| 程咬铜:18:male |

| 程咬铁:18:female |

+-----------------------------+

四则运算：

计算年薪：

select name,salary*12as annual_salary from emp; #默认可以不写as

+------------+---------------+

| name | annual_salary |

+------------+---------------+

| egon | 87603.96 |

| alex | 12000003.72 |

| wupeiqi | 99600.00 |

| yuanhao | 42000.00 |

| liwenzhou | 25200.00 |

| jingliyang | 108000.00 |

| jinxin | 360000.00 |

| 成龙 | 120000.00 |

| 歪歪 | 36001.56 |

| 丫丫 | 24004.20 |

| 丁丁 | 12004.44 |

| 星星 | 36003.48 |

| 格格 | 48003.96 |

| 张野 | 120001.56 |

| 程咬金 | 240000.00 |

| 程咬银 | 228000.00 |

| 程咬铜 | 216000.00 |

| 程咬铁 | 204000.00 |

+------------+---------------+

select * from emp; #正常情况

select * from emp as t1; #正常情况

select empoyee.* from emp as empoyee; #如果要是表名.* 后面必须就要一致，说明 as empoyee先运行

order by排序：

升序：

select age from emporder by age; #select age from emp order by age asc; 默认是升序后面有asc

降序：

select age from emp order by age desc;

limit限制显示的条件：

select * from emp limit 4; #打印只会显示前面4条信息

+----+---------+------+-----+------------+----------------------------+--------------+------------+--------+-----------+

| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |

+----+---------+------+-----+------------+----------------------------+--------------+------------+--------+-----------+

| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |

| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |

| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300 | 401 | 1 |

| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500 | 401 | 1 |

+----+---------+------+-----+------------+----------------------------+--------------+------------+--------+-----------+

limit分页显示：

select * from emp limit 0,5; #从0开始(不包含0)往后取5条，会打印1-5

select * from emp limit 5,5; #从5开始往后取5条，会打印6-10

+----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+

| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |

+----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+

| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000 | 401 | 1 |

| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000 | 401 | 1 |

| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000 | 401 | 1 |

| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |

| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |

+----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+

正则表达式：

select * from emp where name regexp '^jin.*(g|n)$'; #jin开头，.*中间可以是任意字符，g/n结尾的名字

+----+------------+--------+-----+------------+---------+--------------+--------+--------+-----------+

| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |

+----+------------+--------+-----+------------+---------+--------------+--------+--------+-----------+

| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000 | 401 | 1 |

| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000 | 401 | 1 |

+----+------------+--------+-----+------------+---------+--------------+--------+--------+-----------+

多表查询：

emp：

+-----+----------+ +----+--------

| id | name | sex | age | dep_id |

+----+------------+--------+-----+--------+

| 1 | egon | male | 18 | 200 |

| 2 | alex | female | 48 | 201 |

| 3 | wupeiqi | male | 38 | 201 |

| 4 | yuanhao | female | 28 | 202 |

| 5 | liwenzhou | male | 18 | 200 |

| 6 | jingliyang | female | 18 | 204 |

+----+------------+--------+-----+--------+ +-----+----------+

dep：

| id | name |

+-----+----------+

| 200 | 技术 |

| 201 | 人力资源 |

| 202 | 销售 |

| 203 | 运营 |

select * from emp,dep 同时查询2张表：(本质是一份数据，员工部门表)

原理：左边的一天记录会完整的把右边的记录对应一遍：

+----+------------+--------+-----+--------+-----+----------+

| id | name | sex | age | dep_id | id | name |

+----+------------+--------+-----+--------+-----+----------+

| 1 | egon | male | 18 | 200 | 200 | 技术 | egon完整与部门表对应一次

| 1 | egon | male | 18 | 200 | 201 | 人力资源 |

| 1 | egon | male | 18 | 200 | 202 | 销售 |

| 1 | egon | male | 18 | 200 | 203 | 运营 |

| 2 | alex | female | 48 | 201 | 200 | 技术 | alex完整与部门表对应一次

| 2 | alex | female | 48 | 201 | 201 | 人力资源 |

| 2 | alex | female | 48 | 201 | 202 | 销售 |

| 2 | alex | female | 48 | 201 | 203 | 运营 |

| 3 | wupeiqi | male | 38 | 201 | 200 | 技术 |

| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |

| 3 | wupeiqi | male | 38 | 201 | 202 | 销售 |

| 3 | wupeiqi | male | 38 | 201 | 203 | 运营 |

| 4 | yuanhao | female | 28 | 202 | 200 | 技术 |

| 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 |

| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |

| 4 | yuanhao | female | 28 | 202 | 203 | 运营 |

| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |

| 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 |

| 5 | liwenzhou | male | 18 | 200 | 202 | 销售 |

| 5 | liwenzhou | male | 18 | 200 | 203 | 运营 |

| 6 | jingliyang | female | 18 | 204 | 200 | 技术 |

| 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 |

| 6 | jingliyang | female | 18 | 204 | 202 | 销售 |

| 6 | jingliyang | female | 18 | 204 | 203 | 运营 |

+----+------------+--------+-----+--------+-----+----------+

笛卡尔积

汇总员工表emp里面对应的部门信息：

select * from emp,dep where emp.dep_id = dep.id; (基础写法)

+-----+----------+----+-----------+--------+-----+--------+

| id | name | id | name | sex | age | dep_id |

+-----+----------+----+-----------+--------+-----+--------+

| 200 | 技术 | 1 | egon | male | 18 | 200 |

| 201 | 人力资源 | 2 | alex | female | 48 | 201 |

| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |

| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |

| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |

+-----+----------+----+-----------+--------+-----+--------+

只打印技术部门的人：

select * from dep,emp where emp.dep_id = dep.id and dep.name='技术';(在基础上再加and的判断语句)

+-----+------+----+-----------+------+-----+--------+

| id | name | id | name | sex | age | dep_id |

+-----+------+----+-----------+------+-----+--------+

| 200 | 技术 | 1 | egon | male | 18 | 200 |

| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |

+-----+------+----+-----------+------+-----+--------+

只查技术部的人名：

selectemp.name from emp,dep where emp.dep_id = dep.id and dep.name='技术';

3.1、2条件基础上对结果近一步筛选 1.笛卡尔积筛选后的基础表格 2.基于基础添加判断进一步筛选

+-----------+

| name |

+-----------+

| egon |

| liwenzhou |

+-----------+

查egon所在的部门名：

select dep.name from emp,dep where emp.dep_id = dep.id and emp.name='egon';

+------+

| name |

+------+

| 技术 |

+------+

1.内连接：不推荐

select * from emp,dep where emp.dep_id = dep.id;

2.内连接标准写法：inner join...on：只取两张表有对应关系的记录

inner join...on 只取相同部分，与上面原理一样

select * from emp inner join dep on emp.dep_id = dep.id where dep.name='技术';

思考：emp里面没有对应关系的 jingliyang 204没出来，如何做保留

左连接：在内连接的基础上，保留左表没有对应关系的记录

select * from emp left joindep on emp.dep_id = dep.id;

+----+------------+--------+-----+--------+------+----------+

| id | name | sex | age | dep_id | id | name |

+----+------------+--------+-----+--------+------+----------+

| 1 | egon | male | 18 | 200 | 200 | 技术 |

| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |

| 2 | alex | female | 48 | 201 | 201 | 人力资源 |

| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |

| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |

| 6 | jingliyang | female | 18 | 204 | NULL | NULL|

+----+------------+--------+-----+--------+------+----------+

6 rows in set

右连接：在内连接的基础上，保留右表没有对应关系的记录

select * from emp right join dep on emp.dep_id = dep.id;

+------+-----------+--------+------+--------+-----+----------+

| id | name | sex | age | dep_id | id | name |

+------+-----------+--------+------+--------+-----+----------+

| 1 | egon | male | 18 | 200 | 200 | 技术 |

| 2 | alex | female | 48 | 201 | 201 | 人力资源 |

| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |

| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |

| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |

|NULL | NULL | NULL | NULL | NULL |203 | 运营 |

+------+-----------+--------+------+--------+-----+----------+

全连接：在内连接的基础上保留右表没有对应关系的记录

select * from emp left join dep on emp.dep_id = dep.id union select * from emp right join dep on emp.dep_id = dep.id;

+------+------------+--------+------+--------+------+----------+

| id | name | sex | age | dep_id | id | name |

+------+------------+--------+------+--------+------+----------+

| 1 | egon | male | 18 | 200 | 200 | 技术 |

| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |

| 2 | alex | female | 48 | 201 | 201 | 人力资源 |

| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |

| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |

| 6 | jingliyang | female | 18 | 204 | NULL | NULL |

| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |

+------+------------+--------+------+--------+------+----------+

工作顺序就是：

1.先实现笛卡尔积

2.再看是数据inner join、left join、 right join

虚拟表连接查询：

环境是只有一个emp员工信息工资表：

t1:表格

+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+

| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |

+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+

| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |

| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |

| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300 | 401 | 1 |

| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500 | 401 | 1 |

| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100 | 401 | 1 |

| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000 | 401 | 1 |

| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000 | 401 | 1 |

| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000 | 401 | 1 |

| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |

| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |

| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |

| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |

| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |

| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |

| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000 | 403 | 3 |

| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000 | 403 | 3 |

| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000 | 403 | 3 |

| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000 | 403 | 3 |

+----+------------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+

t2表格：(虚拟表)

+----------------------------+-------------+

| post | max(salary) |

+----------------------------+-------------+

| operation | 20000 |

| sale | 4000.33 |

| teacher | 1000000.31 |

| 老男孩驻沙河办事处外交大使 | 7300.33 |

+----------------------------+-------------+

实例2:

1.思考怎么实现找到薪资最高的人，首先不能直接在emp这张表上直接实现得到

2.想得到每个部门薪资最高的人，其实就是在t2的基础上做近一步的筛选

3.为了拿到人名，所有将t1与t2合并，从新表里再取提取对应的薪资最高的人

将2张表格 inner join

select t1.id,t1.name,t1.salary,t1.post,t2.post,t2.ms

from emp as t1

inner join

(select post,max(salary)as ms from emp group by post) as t2 #将t2的max(salary)字段变为ms ；as t2是将整个t2表格变为t2

on t1.post = t2.post;

+----+------------+------------+----------------------------+----------------------------+------------+

| id | name | salary | post | post | ms |

+----+------------+------------+----------------------------+----------------------------+------------+

| 1 | egon | 7300.33 | 老男孩驻沙河办事处外交大使 | 老男孩驻沙河办事处外交大使 | 7300.33 |

| 2 | alex | 1000000.31| teacher | teacher | 1000000.31 |

| 3 | wupeiqi | 8300 | teacher | teacher | 1000000.31 | #前面的名字是数据后面部门的，后面的薪资是这个部门的最高工资

| 4 | yuanhao | 3500 | teacher | teacher | 1000000.31 |

| 5 | liwenzhou | 2100 | teacher | teacher | 1000000.31 |

| 6 | jingliyang | 9000 | teacher | teacher | 1000000.31 |

| 7 | jinxin | 30000 | teacher | teacher | 1000000.31 |

| 8 | 成龙 | 10000 | teacher | teacher | 1000000.31 |

| 9 | 歪歪 | 3000.13 | sale | sale | 4000.33 |

| 10 | 丫丫 | 2000.35 | sale | sale | 4000.33 |

| 11 | 丁丁 | 1000.37 | sale | sale | 4000.33 |

| 12 | 星星 | 3000.29 | sale | sale | 4000.33 |

| 13 |格格 | 4000.33| sale |sale | 4000.33|

| 14 | 张野 | 10000.13 | operation | operation | 20000 |

| 15 | 程咬金 | 20000| operation |operation | 20000 |

| 16 | 程咬银 | 19000 | operation | operation | 20000 |

| 17 | 程咬铜 | 18000 | operation | operation | 20000 |

| 18 | 程咬铁 | 17000 | operation | operation | 20000 |

+----+------------+------------+----------------------------+----------------------------+------------+

18 rows in set

连表的目的是：判断员工薪资是不是部门最高工资 t1.salary = t2.ms

select t1.id,t1.name,t1.salary,t1.post,t2.post,t2.ms

from emp as t1

inner join

(select post,max(salary) as ms from emp group by post) as t2

on t1.post = t2.post

where t1.salary=t2.ms;

+----+--------+------------+----------------------------+----------------------------+------------+

| id | name | salary | post | post | ms |

+----+--------+------------+----------------------------+----------------------------+------------+

| 1 | egon | 7300.33 | 老男孩驻沙河办事处外交大使 | 老男孩驻沙河办事处外交大使 | 7300.33 |

| 2 | alex | 1000000.31 | teacher | teacher | 1000000.31 |

| 13 | 格格 | 4000.33 | sale | sale | 4000.33 |

| 15 | 程咬金 | 20000 | operation | operation | 20000 |

+----+--------+------------+----------------------------+----------------------------+------------+

最终版：代码实现拿到薪资最高对应的人

select t1.* #最后要的只是t1表里面的信息，唯一就是筛选出了最高工资对应的人，(t2实质就是中间过度判断的依据)

from emp as t1

inner join

(select post,max(salary) as ms from emp group by post) as t2

on t1.post = t2.post

where t1.salary=t2.ms;

+----+--------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+

| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |

+----+--------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+

| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |

| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |

| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |

| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000 | 403 | 3 |

+----+--------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+

weixin_39710991
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql虚拟表查询_mysql虚拟表
weixin_32770423的博客
01-19 3819
虚拟表，顾名思义，就是实际上并不存在(物理上不存在)，但是逻辑上存在的表。这样说很抽象，还是看一些实际的例子吧。在mysql中，存在三种虚拟表：临时表、内存表和视图。视图会单独讲，本节仅仅将临时表和内存表。一、mysql临时表1、什么是临时表临时表是建立在系统临时文件夹中的表，如果使用得当，完全可以像普通表一样进行各种操作。 临时表的数据和表结构都储存在内存之中，退出时，其所占的空间会自动被释放。...
MySQL中利用虚拟表进行组合查询
最新发布
m0_56353506的博客
11-30 876
虚拟表（也称为派生表或子查询）可以在查询中作为临时表来使用，它的执行方式与普通的表操作略有不同。下面是虚拟表的执行方式。在MySQL中，查询语句首先会被解析器解析，然后交给优化器进行优化。在优化阶段，MySQL会尝试找到最佳的执行计划，以便快速地获取查询结果。对于包含虚拟表的查询语句，MySQL会将其分成两部分：外层查询和内层查询。外层查询是主查询，内层查询是虚拟表查询。MySQL会分别对这两部分查询进行解析和优化，并生成相应的执行计划。
MySQL——虚拟表以及索引（笔记整理）
m0_59998867的博客
10-08 5749
在MySQL中有三种虚拟表：临时表、内存表、视图。索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是 某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单
mysql虚拟表
u012632105的博客
08-05 1579
mysql虚拟表
mysql的虚拟表_mysql中的view(虚拟表)
weixin_39521651的博客
01-18 400
视图是从一个或多个表/视图中导出来的虚拟表。mysql支持可更新的视图。1 创建CREATE[OR REPLACE] ##使用这个选项，若视图已存在，则等图create，若视图不存在，则等同alter[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}][DEFINER = { user | CURRENT_USER }][SQL SECURITY { DEF...
数据库实验2 视图与多表查询.doc
12-10
3. 视图的概念：视图是一种虚拟表，基于一个或多个基本表派生出来的。视图可以简化复杂的查询操作，提高查询效率和安全性。视图可以根据需要创建、修改和删除。 4. 多表查询的概念：多表查询是指同时查询多个表的...
详解数据库多表连接查询的实现方法
09-09
本文主要探讨了如何在数据库系统中实现多表连接查询，特别是针对MySQL这样的关系型数据库。通过连接查询，可以从多个相关联的表中获取所需的数据，从而提高数据分析和信息提取的效率。 连接查询的核心在于连接...
mysql 单表多表查询
07-30
7. **视图**: 视图是虚拟表，基于一个或多个表的查询结果。创建视图后，可以像操作普通表一样进行查询，但其实并未在数据库中存储任何数据。 8. **索引**: 索引可以极大地提高查询性能，特别是对大表。主键索引、...
浅谈MySQL使用笛卡尔积原理进行多表查询
01-19
MySQL的多表查询(笛卡尔积原理) 先确定数据要用到哪些表。 将多个表先通过笛卡尔积变成一个表。 然后去除不符合逻辑的数据（根据两个表的关系去掉）。 最后当做是一个虚拟表一样来加上条件即可。 注意：列名最好...
三、MySQL查询 学习PPT
06-11
视图是一个虚拟表，基于一个或多个表的查询结果。视图可以简化复杂的查询操作，提高查询效率。例如，CREATE VIEW stu_info AS SELECT s.name, sd.age FROM students s LEFT JOIN student_details sd ON s.s_id = sd....
SQL 虚拟表与关联查询-超级有用
04-05
能够帮助你使用一些虚拟的表。来帮助你的SQL进行优化或分解。对你一定有帮助。
关系数据库SQL之高级数据查询:去重复、组合查询、连接查询、虚拟表
weixin_34061555的博客
05-07 209
前言 接上一篇关系数据库SQL之基本数据查询:子查询、分组查询、模糊查询，主要是关系型数据库基本数据查询。包括子查询、分组查询、聚合函数查询、模糊查询，本文是介绍一下关系型数据库几种高级数据查询SQL语法，包括虚拟表、去重复查询、组合查询、连接查询。 去重复(DISTINCT) DISTINCT:用于返回唯一不同的值，主要是用于某一...
MYSQL中的连接查询
ZgaoYi的博客
03-02 5821
通过对DQL的学习，我们可以很轻松的从⼀张数据表中查询出需要的数据；在企业的应 ⽤开发中，我们经常需要从多张表中查询数据（例如：我们查询学⽣信息的时候需要同 时查询学⽣的班级信息），可以通过连接查询从多张数据表提取数据: 在MySQL中可以使⽤join实现多表的联合查询——连接查询， join按照其功能不同分为 三个操作： inner join 内连接 left join 左连接 right join 右连接 1.数据准备 1.1 创建数据表 创建班级信息表 和 学⽣信息表 create table cla
mysql用with as 虚拟空间同表同字段自关联查询不同季度的比率
weixin_42126120的博客
03-28 840
需求：获取所选企业的所有年份的4季报的资产总额增长率 分析： 第1步： 使用with as 关键字，模拟一个临时表空间，定义它的别名，用作复用语句。 第2步： 使用左关联，获取同表同字段不同年份的"资产总额"，使用函数DATE_ADD() 函数向日期添加指定的时间间隔。 语法：DATE_ADD(date,INTERVAL expr type)。 第3步： 使用 IFNULL()函数处理计算公式中的null为0。 使用ROUND(X,D) 函数进行四舍五入取值。 使用concat()拼接函数显示百分比%号。
java回顾：MySQL性能、索引、视图(虚拟查询表)、触发器(可留存日志)
m0_56678122的博客
09-27 446
java回顾：MySQL性能、索引、视图(虚拟查询表)、触发器(可留存日志)
连接查询之外连接(左外链接和右外连接)
heliuerya的博客
01-12 1522
连接查询之外连接(左外链接和右外连接)
MySQL数据库虚表_【MYSQL】创建虚表来辅助数据库查询
weixin_32304579的博客
02-28 976
在进行数据库查询时，有时需要用到对既有的数据表进行多表查询得出的临时条件的数据表，就可以暂时创建成为虚表，并赋予简单明了的字段名以及临时表名。例题a：查询出每门课程低于平均成绩的学生姓名、课程名称、分数分析：1. 要完成题目，首先需要一张所有学生的课程及分数表，所需字段：学生姓名、课程名称、分数；select a.Name as Name,b.C_name as C_name,b.Grade as...
墨天轮MySQL免费在线SQL测试服务器
weixin_55094944的博客
11-05 296
墨天轮在线实训平台V1.0发布，免费提供部署好的数据库线上环境，可以随时随地打开浏览器学习，一键连接Linux WEB终端，目前已支持Oracle、MySQL、Redis、PostgreSQL、openGauss、MogDB。 秉承乐知乐享同心共济的愿景，现对广大技术爱好者开放免费90天限时申请：墨天轮->云市场->云上资源云市场 - 墨天轮 如下是MySQL实训环境，使用chrome浏览器一键连接到底层操作系统命令行终端，可以进行操作系统命令学习，可以连接到数据库做任何操作、测试、实验。.
mysql中左连接查询_mysql左连接
weixin_29015051的博客
02-01 928
1.on 后面的条件和where 后面的条件的区别查询语句开始 会根据 on后面的条件创建一张虚拟表，左边表是全部数据，右边表会根据on后面的条件进行筛选。然后再根据where后面的条件进行筛选虚拟表中的数据作为最终数据所以如果是筛选右表中的条件 放在了where 中则则会过滤掉 部分左表中的数据结论：筛选右表的条件和左右表关联的条件写在on中 筛选左表的条件写在where中2.右表中的条件放在...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值