2017年07月03号课堂笔记

最新推荐文章于 2024-11-08 18:40:59 发布
最新推荐文章于 2024-11-08 18:40:59 发布
阅读量120 收藏
点赞数
文章标签： 面试 数据库
原文链接：http://www.cnblogs.com/wsnedved2017/p/7111032.html
版权

2017年07月03号 星期一  多云 空气质量：轻度污染~中度污染

内容：MySQL第四节课

in和not in；两个表的内连接；exists和not exsits的使用；all,any和some；

使用子查询的注意事项；sql优化（使用exists 代替 in）；group by；两道mysql面试题

一、in和not in

1、in
-- 使用in替换 等于（=）的子查询语句！
-- in后面的子查询可以返回多条记录！

1）例题1
-- 查询年级编号是1或者2 的 所有学生列表

SELECT * FROM student WHERE gradeId IN(1,2)
2）例题2
-- 查询 年级名称是 大一或者大二的所有学生信息

-- 分析：学生表中没有 年级名称 但是有年级编号

-- 01.根据 年级名称 查询出 年级编号
SELECT gradeID FROM grade WHERE gradeName IN('大一','大二');

-- 02.再根据 年级编号 查询学生信息
SELECT * FROM student WHERE
gradeID
IN (SELECT gradeID FROM grade WHERE gradeName IN('大一','大二'))

3）例题3（使用where ... =）
-- 查询参加最近一次 高等数学-1 考试的学生 成绩的最高分和最低分

-- 01. 发现成绩表中 没有 编号 只有科目名称！根据名称取编号

SELECT SubjectNo FROM `subject` WHERE
subjectName='高等数学-1'

-- 02.查询最近一次 高等数学-1 考试的时间

SELECT MAX(ExamDate) FROM result
WHERE
SubjectNo=(SELECT SubjectNo FROM `subject` WHERE
subjectName='高等数学-1')

-- 02(2).所有最近考试的成绩(老师上课演示用，可以不写)
SELECT * FROM result
WHERE ExamDate='2013-11-11 16:00:00'

-- 03.开始获取最高分和 最低分
SELECT MAX(studentResult) AS 最高分,
MIN(studentResult) AS 最低分
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'))

4）例题4（使用where ... in）
-- 查询 高等数学-1 考试成绩是 60 分的 学生信息

-- 01.根据 科目名称 获取 科目编号
SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1'

-- 02.根据科目编号 和 考试成绩条件 查询所有的学生编号
SELECT studentNo FROM result
WHERE SubjectNo=(SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
AND StudentResult=60; -- 成绩=60

-- 03.根据符合条件学生编号 查询对应的学生信息
SELECT * FROM student
WHERE studentNo IN
(SELECT studentNo FROM result
WHERE SubjectNo=(SELECT SubjectNo FROM `subject`
WHERE SubjectName='高等数学-1')
AND StudentResult=60)


2、not in
-- not in :不在某个范围之内

1）例题1
-- 查询未参加 “高等数学-1” 课程最近一次考试的在读学生名单

-- 01.根据 科目名称 获取 科目编号
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,StudentName FROM student
WHERE studentNo NOT IN
(
SELECT StudentNo 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'))
)

 

二、高级查询

1、笛卡尔乘积

1）概念：笛卡尔乘积是指在数学中，两个 集合 X和 Y的笛卡尓积（Cartesian product），又称 直积，表示为 X ×  Y，
第一个对象是 X的成员而第二个对象是 Y的所有可能 有序对的其中一个成员。
假设集合A={a, b}，集合B={0, 1, 2}，
则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。
A表示所有声母的集合，B表示所有韵母的集合，那么A和B的笛卡尔积就为所有可能的汉字全拼。

2）老师mysql举例： SELECT * FROM `grade`INNER JOIN `student`
笛卡尔积 ：两个表数据的乘积！

 

2、两个表的内连接
SELECT * FROM `grade`INNER JOIN `student`
ON grade.`GradeID`=student.`GradeId`

on 两个表通过那一列建立关联关系

 

3、exists的使用

1）语法和作用

-- 01. 用于检测表，数据库等等 是否存在

-- 02.检查子查询中是否会返回一行数据！其实子查询并不返回任何数据！
只返回 true或者false！

SELECT * FROM Student WHERE EXISTS(SELECT NULL)
SELECT * FROM Student WHERE EXISTS(SELECT 9*9)
SELECT * FROM Student WHERE EXISTS(SELECT StudentName FROM student)

图示如下：

 

2）exists例题1　　exists和in的转换写法

-- 01.exists的写法

SELECT * FROM Student

WHERE EXISTS(SELECT studentName FROM Student WHERE studentName='张三')

-- 02.in的写法

SELECT * FROM Student WHERE studentName IN(SELECT studentName FROM Student)

-- 03.in 效果等同于 =any
SELECT * FROM Student WHERE
studentName =ANY(SELECT studentName FROM Student)

 

3）all,any和some
-- 01.all 大于子查询语句中的 最大值   >(1,2,3) >3
SELECT * FROM student
WHERE studentNo>ALL
(SELECT studentNo FROM student WHERE studentNo IN(1003,1004,1005))

-- 02.any 大于子查询语句中的 最小值   >(1,2,3) >1
SELECT * FROM student
WHERE studentNo>ANY
(SELECT studentNo FROM student WHERE studentNo IN(1003,1004,1005))

-- 03.some 和any功能一样
SELECT * FROM student
WHERE studentNo>SOME
(SELECT studentNo FROM student WHERE studentNo IN(1003,1004,1005))

 

4）exists例题2
-- 检查“高等数学-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.显示分数排在前5名的学员学号和分数
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

 

同样题目：

-- 检查“高等数学-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.查询学号和成绩
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

 

5）exists例题3
-- 如果有 年级名称是大二 的学生，就 查询出 年级名称是大一的 所有学生信息

-- 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='大一'
)

 

6）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')
)

 

7） 使用子查询的注意事项
-- 01.任何允许使用表达式的地方都可以使用子查询
-- 02.只出现在子查询中但是没有在父查询中出现的列，结果集中的列不能包含！

 

4、sql优化（使用exists 代替 in）

1）使用exists 代替 in
使用not exists 代替not in

exists 只返回true或者false.不返回结果集
in 返回结果集

-->exists性能更高！

2）例题1
-- 查询姓李的学生信息 % 代表0或者多个字符 _代表一个字符

-- 01.使用like
SELECT * FROM student WHERE StudentName LIKE '李%'
SELECT * FROM student WHERE StudentName LIKE '李_'

-- 02.使用in完成上述代码
SELECT * FROM student WHERE StudentName IN(
SELECT studentName FROM student WHERE StudentName LIKE '李%')
-- in(多条数据--》返回结果集)

-- 03.使用exists替换
SELECT * FROM student WHERE EXISTS(
SELECT studentName FROM student)
AND StudentName LIKE '李%'
-- exists（有没有数据）

 

5. GROUP BY 列名 分组 　　having 分组之后的条件
1）例题1
-- 统计每门课程平均分各是多少
SELECT subjectno,AVG(studentresult) FROM result
GROUP BY subjectno

2）例题2
-- 查询出课程平均分大于60的课程编号 和 平均分
SELECT subjectno,AVG(studentresult) FROM result
GROUP BY subjectno
HAVING AVG(studentresult)>60 -- 分组之后的条件

3）例题3
-- 01.统计每门课程平均分各是多少 降序排列
SELECT subjectno,AVG(studentresult) FROM result
GROUP BY subjectno
ORDER BY AVG(studentresult) DESC

-- 02.如果成绩相同 再按照 课程编号 升序排序
SELECT subjectno,AVG(studentresult) FROM result
GROUP BY subjectno
ORDER BY AVG(studentresult) DESC,subjectno

4）例题4
-- 分组统计每个年级的 男女人数

SELECT gradeid 年级编号,sex 性别,COUNT(sex) 人数
FROM student
GROUP BY gradeid,sex

 

6、面试题
1）面试题1

老师代码：（数据不完全对应题目，领会解题方法）

-- 创建表
CREATE TABLE IF NOT EXISTS examTest(
id INT(2) NOT NULL,
sex VARCHAR(20)
)

-- 同时新增多条数据
INSERT INTO examTest VALUES(1,'男'),(2,'男'),(3,'女'),(4,NULL);

01.使用where
SELECT sex AS '性别',COUNT(sex) AS '人数' FROM examTest
WHERE sex IS NOT NULL
GROUP BY sex
ORDER BY COUNT(sex) DESC

02.使用having
SELECT sex AS '性别',COUNT(sex) AS '人数' FROM examTest
GROUP BY sex
HAVING sex IS NOT NULL
ORDER BY COUNT(sex) DESC

03.使用where和in（取巧）
SELECT sex AS '性别',COUNT(sex) AS '人数' FROM examTest
WHERE sex IN('男','女')
GROUP BY sex
ORDER BY COUNT(sex) DESC


2）面试题2

作业，待解答

下次课确认答案后补上

老师代码：

 

 

 

三、作业

1、复习之前所讲的mysql课程（老师txt文件里的），预习事务，下节课考试

2、面试题第二题

四、老师辛苦了！

 

 

转载于:https://www.cnblogs.com/wsnedved2017/p/7111032.html

weixin_30577801
关注
李兴华2016年java课堂笔记
10-24
《李兴华2016年Java课堂笔记》是一份珍贵的学习资料，它全面记录了知名讲师李兴华在2016年教授Java课程时的精髓内容。这份笔记不仅涵盖了Java的基础知识，还深入探讨了许多高级主题，是Java初学者和进阶者提升技能的...
检查Logic Java 课程最近一次考试。如果全部没有通过考试（60分及格），则认为本次考试偏难，计算该次考试平均分加5分。
rbuli的博客-又起秋风
11-29 2282
检查Logic Java 课程最近一次考试。如果全部没有通过考试（60分及格），则认为本次考试偏难，计算该次考试平均分加5分。 SELECT AVG(studentResult)+5 平均分 FROM result 如果子查询语句没有结果 则 返回 true WHERE NOT EXISTS ( #查询Lo
检查 Logic Java 课程最近一次考试。如果有成绩到达80分以上者，则显示分数排在前5名学员的学号和分数
rbuli的博客-又起秋风
11-29 3473
检查 Logic Java 课程最近一次考试。如果有成绩到达80分以上者，则显示分数排在前5名学员的学号和分数 SELECT studentNO 学号,studentResult 成绩 FROM result WHERE EXISTS( #查询Logic java 最后一次考试成绩大于80的记录 SELECT * FROM result WHERE s
accp8.0转换教材第4章MySQL高级查询(二)理解与练习
weixin_30617695的博客
07-02 701
知识点：EXISTS子查询、NOT EXISTS子查询、分页查询、UNION联合查询 一.单词部分 ①exist存在②temp临时的③district区域 ④content内容⑤temporary暂时的 二.预习部分 1.表连接都可以用子查询替换吗 是的 2.检测某列是否存在某个范围可以在子查询中使用什么关键字 EXISTS 3.哪些sql语句可以嵌套子查询 较复杂的数据查询语...
Mysql 数据库（九）
weixin_30918633的博客
11-02 134
一 视图 　　什么是视图：视图就是一张虚拟表。方便查看。 　　创建视图：create view 起名 as sql语句 #两张有关系的表 mysql> select * from course; +-----+--------+------------+ | cid | cname | teacher_id | +-----+--------+---------...
MySQL数据管理
kxindouhao5491的博客
09-24 1981
外键管理 外键作用 创建外键： 建表是指定外键约束 建表后修改 删除外键 删除外键、删除表   添加主键约束 语法： ALTER TABLE 表名 ADD CONSTRAINT 主键名          PRIMARY KEY 表名（主键字段）; 问题： 如何设置grade表中gradeId字段为主键？ ALTER TABLE `grade` ADD CONSTRAINT...
2023年领导学导论课堂笔记武汉大学国际软件学院.docx
11-08
2023年领导学导论课堂笔记武汉大学国际软件学院.docx
css课堂笔记前端css课堂笔记
08-05
CSS课堂笔记 本文档详细介绍了CSS的基本概念、语法、选择器、导入方式等知识点，为读者提供了系统的CSS基础知识。 一、CSS概念 CSS（Cascading Style Sheets）是一种用来表现HTML或XML等文件样式的计算机语言。...
HCIA、OSPF课堂笔记
08-09
HCIA、OSPF课堂笔记
mybatis12月7号课堂笔记.zip_mybatis_ssm框架笔记
09-23
在学习过程中，这份"mybatis12月7号课堂笔记.docx"文档应该包含了关于SSM框架的详细讲解，包括MyBatis的配置文件、Mapper接口和XML映射文件的编写，以及Spring和Spring MVC的相关配置和注解使用。通过阅读和实践，你...
Mysql 第三章 高级查询
doudou19930614的博客
07-25 5233
EXISTS子查询的语法 问题：如何用sql语句检测temp表是否已经创建？       方法一：这句话的意思是如果这个表存在，就删除        DROP TABLE IF EXISTS temp;            方法二：子查询的语法          语法：SELECT …… FROM 表名 WHERE EXISTS(子查询);        子查询有返回结果：exists 子查询...
mysql查男女各多少人_sql查询一个班级中总共有多少人以及男女分别多少人
weixin_29515317的博客
02-07 2034
iOS关于html缓存方式一:截取请求正则.图片缓存 实现webview缓存网页内容难点在缓存图片上.html代码的缓存相对简单,具体实现思路是这样的:第1步.先获取html页面里所有图片地址.方法一:离线获取获取到htm ...PHP-----数据类型,运算符文件解析流程: 基本语法: 和Javascript很像.Ps:变量名可以使用关键字,但是最好不建议使用.关于PHP和H...
SQL Server数据库第六课：使用group by和having进行分组查询、多表连接查询的使用
王迪
05-08 3363
知识点：group by进行分组查询、having子句进行分组查询、多表连接查询的使用。 1、使用SQL语句创建多表（本章学习的准备工作） 使用SQL语句单独创建数据库、创建表，我们都会了，那么当两个表有关系的时候，怎么创建呢？引用外键约束的时候，如何使用SQL语句？ 思考： ①分清楚两个表之间的关系（主表和从表、主键和外键） ②分清楚从表中的哪个键进行引用，引用的...
微服务架构面试内容整理-Sleuth
不务正业的猿
11-07 361
当请求在服务之间传播时，Sleuth 会将 Trace ID 和 Span ID 作为请求头的一部分传递，从而实现对整个请求路径的追踪。在微服务架构中，服务之间的请求链路往往很复杂，Sleuth 可以帮助开发者追踪请求在多个服务中的流转情况。当请求跨多个微服务传播时，每个服务的日志中都会包含相同的 Trace ID，帮助开发者跟踪整个请求的流转。Sleuth 能够对请求进行跟踪，为每个请求生成唯一的追踪 ID（Trace ID），并在服务之间传播。
【IC每日一题：IC验证面试常问-3】
li_kin的博客
11-07 602
IC验证常问问题
摩尔线程嵌入式面试题及参考答案（2万字长文）
大模型大数据攻城狮的专栏
11-06 363
说一下你对 drm 框架的理解。 DRM（Direct Rendering Manager）是 Linux 系统中用于管理图形显示设备的一个重要框架。 从架构层面来讲，它处于内核空间，主要目的是为用户空间的图形应用程序提供一个统一的接口来访问图形硬件。DRM 包括内核态的驱动模块和用户态的库。内核态的驱动负责和硬件进行交互，比如对显存的管理、硬件寄存器的配置等。用户态的库则方便应用程序利用内核态提供的功能进行图形渲染等操作。 在图形显示的具体流程中，DRM 可以控制显示设备的分辨率、刷
[面试]Mysql的锁机制
最新发布
ouyfg的博客
11-08 395
MySQL中的锁主要用于控制多个事务对数据库资源的并发访问，以确保数据的一致性和完整性。锁机制是数据库并发控制的核心，通过锁定数据库中的资源来防止并发操作引发的数据冲突。行锁允许多个事务在同一个表中并发执行不同行上的操作，而不会互相阻塞，因此具有较高的并发性。但相比于表锁，行锁需要更多的资源来维护锁的状态，开销更大。表锁是一种较粗粒度的锁机制，会锁定整个表，而不是某个具体的行或记录。综上，MySQL的锁机制是数据库并发控制的核心。除了表级锁和行级锁，MySQL还提供了其他类型的锁来支持不同的并发控制需求。
14：00面试，14：06就出来了，问的问题有点变态。。。
qq_48811377的博客
11-07 675
整份文档一共有将近 200 页，全部为大家展示出来肯定是不太现实的，为了不影响大家的阅读体验就只展示了部分内容，还望大家海涵，希望能帮助到您面试前的复习且找到一个好的工作，也节省大家在网上搜索资料的时间来学习！
spring相关的面试题
qq_45089709的博客
11-07 629
Spring 框架是 Java 开发中最常用的框架之一，因此在面试中经常会被问到与 Spring 相关的问题。以下是一些常见的 Spring 面试题及其答案。
"2023年JAVA课堂笔记详解：环境配置与程序运行
The document "2023年JAVA课堂笔记基础详细解释.doc" provides a detailed explanation of the basic concepts of JAVA programming. It covers essential topics such as the development environment, ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值