mysql 高级查询词_Mysql高级查询语句

最新推荐文章于 2023-08-08 17:03:09 发布
最新推荐文章于 2023-08-08 17:03:09 发布
阅读量155 收藏
点赞数
文章标签： mysql 高级查询词
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_29612723/article/details/113520745
版权
本文详细介绍了MySQL中Exists和Not Exists子查询的使用，包括它们在创建和删除数据库对象时的作用，以及在查询中的应用。通过实例展示了如何检查特定课程最近考试成绩，以及如何利用Exists优化查询。同时，还对比了Exists与IN、Not Exists与Not IN的区别，并介绍了ANY、SOME、ALL的使用以及分组查询和多表连接查询的方法。
摘要由CSDN通过智能技术生成

Exists子查询

Exists的特点

1.在执行create或drop语句前，可以使用exists语句来判断数据库对象是否存在，返回值是true或false

drop table if exists student; 意思是如果存在表student则删除！否则不删除！

Create table if not exists student; 意思是如果不存在表student则创建，否则不创建！

2.exists还可以作为where条件的子查询

Select ..... from 表名 where exists (子查询);

意思是：

如果子查询有结果，则返回值为true，继续执行外层的查询语句;

如果子查询没有结果，则返回值是false,外层的查询语句不会执行。

-- 检查“高等数学-1” 课程最近一次考试成绩

-- 如果有 80分以上的成绩，显示分数排在前5名的学员学号和分数

-- 不使用exists

-- 01.查询“高等数学-1” 课程 对应的编号

SELECT subjectNo FROM `subject` WHERE SubjectName='高等数学-1'

-- 02.查询最近的考试成绩

SELECT MAX(ExamDate)

FROM result

WHERE SubjectNo=(SELECT subjectNo

FROM subject

WHERE SubjectName='高等数学-1')

-- 03. 在02的基础上 加条件 成绩大于80

SELECT * FROM result

WHERE ExamDate=(SELECT MAX(ExamDate)

FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1'))

AND StudentResult>80

-- 04.优化

SELECT studentNo,StudentResult FROM result

WHERE ExamDate=(SELECT MAX(ExamDate)

FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1'))

AND StudentResult>80

ORDER BY StudentResult DESC

LIMIT 0,5

-- 使用exists

-- 检查“高等数学-1” 课程最近一次考试成绩

-- 如果有 80分以上的成绩，显示分数排在前5名的学员学号和分数

-- 01.查询“高等数学-1” 课程 对应的编号

SELECT subjectNo FROM `subject` WHERE SubjectName='高等数学-1'

-- 02.查询最近的考试成绩

SELECT MAX(ExamDate)

FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

-- 03.查询学号和成绩

SELECT StudentNo,StudentResult FROM result

WHERE EXISTS(SELECT * FROM result

WHERE subjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

AND ExamDate=(SELECT MAX(ExamDate) FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1'))

AND StudentResult>80)

AND subjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1')

AND ExamDate=(SELECT MAX(ExamDate) FROM result

WHERE SubjectNo=(SELECT subjectNo FROM `subject`

WHERE SubjectName='高等数学-1'))

ORDER BY StudentResult DESC

LIMIT 0,5

Not Exists子查询

-- 检查“高等数学-1”课程最近一次考试成绩

-- 如果全部未通过考试(60分及格)，认为本次考试偏难，计算的该次考试平均分加5分

-- 01.查询“高等数学-1” 课程 对应的编号

SELECT subjectNo FROM `subject` WHERE SubjectName='高等数学-1'

-- 02.查询最近的考试成绩

SELECT MAX(ExamDate)

FROM result

WHERE SubjectNo=(SELECT subjectNo

FROM `subject`

WHERE SubjectName='高等数学-1')

-- 03.查询成绩大于60的 反着来

SELECT StudentResult

FROM result

WHERE StudentResult>60

AND SubjectNo=(SELECT subjectNo

FROM `subject`

WHERE SubjectName='高等数学-1')

AND ExamDate=(SELECT MAX(ExamDate)

FROM result

WHERE SubjectNo=(SELECT subjectNo

FROM `subject`

WHERE SubjectName='高等数学-1'))

-- 04. 如果全部未通过考试，考试平均分加5分

SELECT AVG(StudentResult)+5

FROM result

WHERE NOT EXISTS(SELECT StudentResult

FROM result

WHERE StudentResult>60

AND SubjectNo=(SELECT subjectNo

FROM `subject`

WHERE SubjectName='高等数学-1')

AND ExamDate=(SELECT MAX(ExamDate)

FROM result

WHERE SubjectNo=(SELECT subjectNo

FROM `subject`

WHERE SubjectName='高等数学-1')))

AND SubjectNo=(SELECT subjectNo

FROM `subject`

WHERE SubjectName='高等数学-1')

AND ExamDate=(SELECT MAX(ExamDate)

FROM result

WHERE SubjectNo=(SELECT subjectNo

FROM `subject`

WHERE SubjectName='高等数学-1'))

-- 如果有 年级名称是大二 的学生，就 查询出 年级名称是大一的 所有学生信息

-- 01.先查询出 对应的年级编号

SELECT GradeId FROM grade WHERE GradeName='大一'

SELECT GradeId FROM grade WHERE GradeName='大二'

-- 02.在学生表中是否存在 年级名称是大二 的学生

SELECT *

FROM student

WHERE gradeID=(SELECT GradeId

FROM grade

WHERE GradeName='大二')

-- 03.如果有查询出 年级名称是大一的 所有学生信息

SELECT *

FROM student

WHERE EXISTS(SELECT *

FROM student

WHERE gradeID=(SELECT GradeId

FROM grade

WHERE GradeName='大二'))

AND GradeId=(SELECT GradeId

FROM grade

WHERE GradeName='大一')

Exists与IN , Not Exists与Not IN 的区别

91895d311413

image

1. IN和Not IN 做的是一个区间的判断，查询数据是否在区间内

Exists 和 Not Exists 都是根据查询语句返回 true 或者 false !

2. 例子：

select a.*

from A a

where a.id

in(select id from B)

如果:

A表中有1000条数据

B表中有1000条数据

分析步骤：

01\. select id from B 会查询出B表中的所有id，然后缓存起来，共1000条(因为使用in 会先执行子查询)

02.然后分别拿A表中的每一个id和B表中的1000个id进行比较，也就是比较了 1000*1000次

03.这样效率是非常慢的

04.如果B表中只有100或者10条数据(只是举例说明数据量小)，那么就会比较1000*10

这样相对来说效率会高点！

结论：

子查询中涉及的表(B)数据量小于 主查询中涉及的表(A)数据量 时，使用In来查询！可以提高效率

In 查询做的是 外表和内表的hash连接

hash连接就是 以 外层查询的表作为hash table ，内层查询的表在hash table中查询数据！

很显然，如果内层查询的数量大 ，查询效率就慢，查询数据量小，效率就高！

例子：

select a.*

from A a

where exists (select id

from B b

where a.id=b.id)

如果:

A表中有1000条数据

B表中有1000条数据

分析步骤：

1. 使用了exists(会以外层查询为驱动)上面的sql语句只会执行1000次(因为A表中有多少条数据，就会执行几次)

2. exists查询不需要数据的结果集，只需要返回true或者false

结论：

子查询中涉及的表(B)数据量大于 主查询中涉及的表(A)数据量时，使用exists来查询！可以提高效率

Exists查询做的是loop循环

如果子查询中涉及的表(B)数据量 和 主查询中涉及的表(A)数据量 差不多时，建议使用IN来查询！

因为In查询是在内存中的查询，exists需要查询数据库，所以内存中的查询肯定比查询数据库性能高！

05.not exists 在任何时候都比not in 效率高！

因为not in 那么内外表都进行[全表扫描]，没有用到索引！而not exists的子查询仍然可以用到索引！

any,

some,all的使用

SELECT *

FROM student

WHERE studentname

IN(SELECT studentName FROM student)

-- 使用any(只要是在区间就行) 替换in

SELECT *

FROM student

WHERE studentname=ANY(SELECT studentName FROM student)

-- all 满足子查询中编号最大的

SELECT *

FROM student

WHERE studentNo>ALL(SELECT studentNo

FROM student

WHERE studentNo IN(1014,1001,1002))

-- any 满足子查询中编号最小的

SELECT *

FROM student

WHERE studentNo>ANY(SELECT studentNo

FROM student

WHERE studentNo IN(1014,1001,1002))

-- 和 any 效果一致

SELECT * FROM student WHERE studentNo>SOME

分组查询

-- 分组 group by

-- 01. 查询 每门课程的名称 以及平均分

-- 并且按照平均分降序排列

SELECT subjectName,AVG(StudentResult)

FROM `result` r,`subject` s

WHERE r.`SubjectNo`=s.`SubjectNo`

GROUP BY subjectName

ORDER BY AVG(StudentResult) DESC

-- 02.在上述案例中增加 条件 having

-- 平均分>73的 显示

SELECT subjectName,AVG(StudentResult)

FROM `result` r,`subject` s

WHERE r.`SubjectNo`=s.`SubjectNo`

GROUP BY subjectName

HAVING AVG(StudentResult)>73

ORDER BY AVG(StudentResult) DESC

-- 03.统计每个年级的男女人数 多列进行分组

SELECT gradeid '年级编号',COUNT(sex) '性别人数',sex '性别'

FROM student

WHERE sex IS NOT NULL

AND gradeid IS NOT NULL

GROUP BY gradeid,sex

-- 04. 找出每个课程成绩的前三名

SELECT *

FROM result r1

WHERE (SELECT COUNT(1)

FROM result r2

WHERE r1.subjectNo=r2.`SubjectNo`

AND r1.studentresult

ORDER BY subjectNo,studentResult DESC

多表连接查询

比如之前写的小例子，查询学生的成绩，我们获取的是学生编号和成绩！

但是如果获取了学生姓名和成绩岂不是更好？ 但是学生姓名和成绩不在一张表中！

这时候就需要我们的连接查询！

常用的连接查询方式：

内连接

外连接

内连接

内连接是典型的最常用的连接查询！ 特点就是两个表中存在主外健关系时，通常使用！查询两张表中共同的数据！内连接的实现方式有两种：

在where条件中指定连接条件,比如 查询学生姓名以及对应的年级名称

Select studentName,gradeName

From student,grade

Where student.gradeId=grade.gradeId

2.在form 子句中增加 inner join 表 on 关系,比如查询学生姓名，科目名称以及考试成绩

SELECT studentName,subjectName,studentresult

FROM student s

INNER JOIN result r ON s.studentNo=r.studentNo

INNER JOIN `subject` su ON su.subjectNo=r.subjectNo

注意点：

001.inner可以省略

002.inner join 用来连接两个表

003.on用来设置条件

004. s r su是用的别名

外连接

外连接查询是至少返回一个表中的所有记录，根据匹配条件有选择地返回另一张表的数据！外连接有主表和从表的概念！以主表为准匹配从表的数据，符合连接条件的数据直接返回到结果集中，不符合的数据将被赋予null之后再返回到结果集中！外连接查询又分为：

1.左外连接 Left outer join

以左表为主表，从表(右边的表)中如果没有匹配的数据返回null

例子1： 查询学生的姓名，考试科目以及成绩！

SELECT studentName,subjectNo,studentResult

FROM student s

LEFT JOIN result r ON r.`studentNo`=s.`studentNo`

例子2：查询所有科目对应的学生成绩

SELECT subjectName,s.subjectNo,studentResult

FROM `subject` s

LEFT JOIN result r ON s.`SubjectNo`=r.`SubjectNo`

2.右外连接 right outer join

以右表为主表，从表(左边的表)中如果没有匹配的数据返回null

例子：查询年级名称和学生名称 两个结果是否一致？

SELECT gradeName,studentName

FROM grade

RIGHT JOIN student ON grade.`GradeID`=student.`GradeId`

SELECT gradeName,studentName FROM grade

INNER JOIN student ON grade.`GradeID`=student.`GradeId`

大千世界，求同存异；相遇是缘，相识是份，相知便是“猿粪”(缘分)

From MZou

创新先锋
关注
mysql查询所有数据库对象_SQL语句查询数据库对象
weixin_36269385的博客
01-20 986
1、用户查看当前用户的缺省表空间SQL>select username,default_tablespace from user_users;查看当前用户的角色SQL>select * from user_role_privs;查看当前用户的系统权限和表级权限SQL>select * from user_sys_privs;SQL>select * from user_ta...
mysql子查询总分最高的学生_mysql高级内容
weixin_35915385的博客
02-08 1828
一. 简介实体与实体之间有3种对应关系，这些关系也需要存储下来在开发中需要对存储的数据进行一些处理，用到内置的一些函数视图用于完成查询语句的封装事务可以保证复杂的增删改操作有效二. 关系创建成绩表scores，结构如下id学生科目成绩思考：学生列应该存什么信息呢？答：学生列的数据不是在这里新建的，而应该从学生表引用过来，关系也是一条数据；根据范式要求应该存储学生的编号，而不是学生的姓名等其它信息同...
MySql高级查询语句
BiggerLong的博客
02-27 589
高级查询： 一：多表连接 1.select Info.Code,Info.Name,Nation.Name from Info,Nation where Info.Nation = Nation.Code 查几张表就就输出几张表，查那个条件就输出那个条件 列的查询 select * from Info,Nation 全部输出4x4 2.join连接 select * from Info
MySQL高级查询语句
weixin_48118868的博客
12-28 846
文章目录5.函数5.1数学函数5.2聚合函数5.3字符串函数5.4日期时间函数 5.函数 MySQL提供了实现各种功能的函数 常用的函数分类： 数学函数、聚合函数、字符串函数、日期时间函数 5.1数学函数 abs(x) ：返回x的绝对值 rand() ：返回0到1的随机数 mod(x,y) ： 返回x除以y以后的余数 power(x,y) ：返回x的y次方 round(x) ：返回离x最近的整数 sqrt(x) ：返回x的平方根 truncate(x,y) ：返回数字x截断为y位小数的值 ceil(x) ：
Mysql高级查询语句
西门飘雪的博客
10-28 755
Exists子查询 Exists的特点 1.在执行create或drop语句前，可以使用exists语句来判断数据库对象是否存在，返回值是true或false drop table if exists student; 意思是如果存在表student则删除！否则不删除！ Create table if not exists student; 意思是如果不存在表student则创建，...
MySQL数据库高级查询语句
郑思旭的博客
04-20 639
1.子查询： 一个查询语句中嵌套另一个查询 例如：–查询金额超过平均值的订单 select * from orders where amt>(select avg(atm) from orders) –查询所有名字以D开头的所有用户下的订单 select * from orders where cust_id in (select distinct(cust_id) from custome...
mysql5.7 查询引起奔溃_MySQL · 引擎特性 · MySQL5.7 崩溃恢复优化
weixin_28718793的博客
01-19 365
在MySQL5.7之前的版本中, InnoDB每次做crash recovery之前都需要扫描数据目录，打开每个文件并创建内存对象。当目录下文件个数特别多时，会严重影响到崩溃恢复的速度。为了解决这个问题，MySQL5.7通过结合checkpoint + 标注被修改的文件的方式，从一个checkpoint点开始，可以找到所有崩溃恢复需要打开的文件，从而避免扫描数据目录。本文简单的记录了相关的代码，...
c mysql trans_MYSQL STRICT_TRANS_TABLES使用图解教程
weixin_28857713的博客
02-06 1663
mysql 在insert的时候，常常提示没有默认值，MySQL 1364 错误提示：#1364 - Field "XXX" doesn't have a default value。XXX字段没有默认的数值；也就是说我们没有为其分配数值，而表中此字段也没有设置默认值。这是mysql模式开启存储引擎启用严格模式，非法数据值被拒绝， 我把操作实际问题贴一下图：而我的Tbl_User表里面字段：解决方...
MySQL_5.1_zh.zip
07-06
`MySQL_5.1_zh.chm`文件是一个帮助文件，通常用于存储离线文档，用户可以通过它查询MySQL 5.1的相关信息。CHM是Microsoft的“Compiled HTML Help”格式，它将一系列HTML页面打包成一个文件，便于阅读和搜索。在这个...
这是一个敏感词的SQL文件
08-19
在多数网站，敏感词一般是指带有敏感政治倾向、暴力倾向、不健康色彩的词或不文明语，也有一些网站根据自身实际情况，设定一些只适用于本网站的特殊敏感词。比如，当你发贴的时候带有某些事先设定的词时，这个贴是不能发出的。或者这个词被自动替换为星号 (*)，或者说是被和谐掉了。请注意敏感词只有小写字母，文本如果中的大写字母当做小写字母处理，出现敏感单词，即使作为子串出现也要被和谐，多个子串重叠他们都要被和谐。
敏感词汇过滤sql数据
12-07
敏感词汇sql文件，可用于对平台用户在不平场景下进行的昵称设置等进行过滤
MySQL数据库——高级查询语句
weixin_67300995的博客
06-28 6414
数据库是用来存储数据，更新，查询数据的工具，而查询数据是一个数据库最为核心的功能，数据库是用来承载信息，而信息是用来分析和查看的。例：SELECT A.Store_Name Store, SUM(A.Sales) “Total Sales” FROM fxk003 A GROUP BY A.Store_Name;例：SELECT Store_Name, SUM(Sales) FROM fxk003 GROUP BY Store_Name HAVING SUM(Sales) > 1500;
MySQL之select语法
weixin_51796408的博客
06-17 753
MySQL之select语法 select语句是在所有数据库操作中使用频率最高的SQL语句 单表查询 首先要先有表 简单查询 #专业表 create database selectTest; use selectTest; create table specialty( zno varchar(4) not null primary key, zname varchar(50) not null ); #课程表 create table course( cno varchar(8) not null
mysql的高级查询语句
最新发布
欲买桂花同载酒
08-08 1509
mysql的高级查询语句，基础概念介绍，详细命令行讲解，图文示例演示等
＜MySQL入门到精通＞阅读笔记之查询数据
qq_42913304的博客
09-09 383
基本查询语句 定义数据库表 CREATE TABLE fruits( f_id CHAR(10) NOT NULL, s_id INT NOT NULL, f_name CHAR(255) NOT NULL, f_price DECIMAL(8,2) NOT NULL, PRIMARY KEY (f_id) ) 插入数据 INSERT INTO fruits(f_id,s_id,f_name,f_price) VALUES ('al', 101, 'apple',5.2), ('bl', 10
MySQL高级查询语句——超详细，一篇就够了
热门推荐
CSDN著名博主
10-18 1万+
MySQL高级查询语句一、MySQL进阶查询1.1、按关键字排序——order by1.2、对结果进行分组——group by1.2.1、单个分组1.2.2、分组结合order by使用1.3、限制结果条目二、MySQL数据库函数三、MySQL存储过程 一、MySQL进阶查询 1.1、按关键字排序——order by ■ 使用ORDER BY 语句来实现排序 ■ 排序可针对一个或多个字段 ■ ASC：升序，默认排序方式 ■ DESC：降序 ■ ORDER BY 的语法结构 SELECT column1, c
mysql 敏感词_过滤敏感词方式
weixin_31225753的博客
02-01 1569
一、利用正则表达式关键正则表达式.*(关键词1|关键词2|关键词3).*模拟业务代码@WebServlet(name = "PatternControl", urlPatterns = {"/p"})public class PatternControl extends HttpServlet {private static final Pattern pattern = initPattern(...
初识MySQL数据库以及语法使用（个人理解心得）
weixin_46058245的博客
07-24 397
#新建表 #使用数据库 USE MYSCHOOL DROP DATABASE IF EXISTS grade #删除表 DROP TABLE subject; DROP TABLE grade; #创建表并添加字段 DEFAULT NULL代表默认值为null CREATE TABLE subject( LoginPwd VARCHAR(20) NULL, StudentNo INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT’学号’, St
MySQL 基本与高级查询操作指南
本文主要介绍了MySQL数据库中的一些基本和高级SQL语句，包括选择、插入、删除、更新、查找、排序、统计等操作，以及UNION、EXCEPT等查询运算符，同时也涉及到了数据库、表、索引、视图的创建、修改和删除。...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值