首页
视频
资源
登录
小可爱
什么时候才能领悟,取之越多失之越多
博主信息
排名
6
文章
6
粉丝
16
评论
8
文章类别
ASP.NET
16篇
JS
1篇
.NET
23篇
英语
4篇
redis
4篇
随笔
30篇
EF
3篇
前端
53篇
.net core
25篇
python
8篇
版本控制
1篇
java
2篇
nginx
2篇
docker
6篇
后端
5篇
更多
mysql
8篇
数据库
15篇
微服务
4篇
移动开发
7篇
架构
6篇
linux
2篇
最新文章
最新评价
{{item.articleTitle}}
{{item.blogName}}
:
{{item.content}}
关于我们
ICP备案 :
渝ICP备18016597号-1
网站信息:
2018-2024
TNBLOG.NET
技术交流:
群号656732739
联系我们:
contact@tnblog.net
欢迎加群
欢迎加群交流技术
原
mysql子查询
3604
人阅读
2022/5/4 11:20
总访问:
868636
评论:
0
收藏:
0
手机
分类:
mysql
代码如下: ``` /* 子查询:一个sql语句里边包含多条查询语句 子查询的分类:标量子查询、列子查询、行子查询、表子查询 标量子查询:返回一行一列,也就是单个值 列子查询:返回一列 行子查询:返回一行 表子查询:子查询的结果是一个表 */ /* 标量子查询 */ -- 查询1213班的所有学生信息 select * from class where ClassName = '计网1213' select * from students where classid = 3 select * from students where classid = (select id from class where ClassName = '计网1212') -- 查询紫嫣之后入职的员工 select * from students -- 第1步:查询紫嫣入职的时间 select entrydate from students where username = '紫嫣' -- 第2步:查询紫嫣入职之后的时间 select * from students where entrydate > (select entrydate from students where username = '紫嫣') /* 列子查询 可以使用in(在什么里边) all(一列的每个条件都需要满足) any(满足一个即可) */ -- 查询1211班与1212班所有学生的信息 select id from class where classname = '计网1211' or classname = '计网1212' select * from students where classid in (select id from class where classname = '计网1211' or classname = '计网1212') -- 查询比1213班所有人工资都高的学生 -- 第一步:查询1213班所有的人工资 select salary from students where classid = (select id from class where ClassName = '计网1213') -- 第二步:找出比这些工资都高的人,使用max函数 select * from students where salary > (select max(salary) from students where classid = (select id from class where ClassName = '计网1213')) -- 方法2:all关键字会挨着一个一个比较,要比所有的都高,其实就是比最高的高就行了 select * from students where salary > all (select salary from students where classid = (select id from class where ClassName = '计网1213')) -- 查询比1211班任意一个工资高的学生(不需要比所有人都高) select id from class where classname = '计网1211' select salary from students where classid = (select id from class where classname = '计网1211') -- 方法1 min函数 select * from students where salary > (select min(salary) from students where classid = (select id from class where ClassName = '计网1211')) -- 方法2 any select * from students where salary > any (select salary from students where classid = (select id from class where ClassName = '计网1211')) /* 行子查询 */ -- 查询与诸葛亮工资和直属领导相同的员工 select * from students -- a:查询诸葛亮的工资与直属领导是谁 select salary,managerid from students where username = '诸葛亮' -- b:查询薪水是12500,managerid为1 select * from students where salary = 12500 and managerid = 1 -- 方法1: select * from students where salary = (select salary from students where username = '诸葛亮') and managerid = (select managerid from students where username = '诸葛亮') -- 方法2: select * from students where (salary,managerid) = (12500,1) select * from students where (salary,managerid) = (select salary,managerid from students where username = '诸葛亮') /* 表子查询 返回的是多行多列,相当于一张表 */ -- 查询与赵云,诸八戒职位和工资都相同的员工 -- a: 赵云,猪八戒职位和工资 select job,salary from students where username='赵云' or username='诸八戒' -- 方法1:单个单个查询然后在合并结合 select * from students where (job,salary) = (select job,salary from students where username = '赵云') union select * from students where (job,salary) = (select job,salary from students where username = '诸八戒') -- 方法2: select * from students where (job,salary) in (select job,salary from students where username='赵云' or username='诸八戒') -- 查询1990之后入职员工的信息与班级 -- a:查询1990之后入职的员工信息 select * from students where entrydate > '1990-1-1' -- b:把1990之后入职的员工信息和班级进行一个连接 -- 方法1: select stu.username,stu.entrydate,class.classname from (select * from students where entrydate > '1990-1-1') stu join class on stu.classid = class.id -- 方法2:(作业) ```
欢迎加群讨论技术,1群:677373950(满了,可以加,但通过不了),2群:656732739
👈{{preArticle.title}}
👉{{nextArticle.title}}
评价
{{titleitem}}
{{titleitem}}
{{item.content}}
{{titleitem}}
{{titleitem}}
{{item.content}}