代码编织梦想

全部章节   >>>>


本章目录

3.1 子查询定义和单行子查询

3.1.1 子查询定义

3.1.2 单行子查询应用

3.1.4 实践练习

3.2 多行子查询应用

3.2.1 in 比较符

3.2.3 any|some 关键字子查询

3.2.4 实践练习

3.3 子查询特殊应用

3.3.1 from 子句中的子查询

3.3.2 select 子句中的子查询

3.3.4 实践练习

3.4 DML 语句中的子查询

3.4.1 update 子句中的子查询

3.4.3 实践练习

总结:


3.1 子查询定义和单行子查询

3.1.1 子查询定义

子查询(subquery)是数据库经常用到的一个操作,它不仅用在数据查询语句中,在 DML 语句中也都会用到子查询

子查询将一个查询语句嵌套(nest)在另一个查询语句中,在特定情况下,一个查询语句的条件需要另一个查询语句来获取,内层查询语句的查询结果可以为外层查询语句提供查询条件

子查询的实质:一个 select 语句的查询结果能够作为另一个语句的输入值。子查询不仅可用于 where 子句中,还能够用于 from 子句中,此时子查询的结果将作为一个临时表(temporary table)来使用

子查询还能以字段的形式出现在 select 语句的选择列中。根据子查询所返回的结果行数,可以将其分为单行子查询和多行子查询

3.1.2 单行子查询应用

单行子查询指子查询的返回结果只有一行数据。当在主查询的条件语句中引用子查询的结果时,可使用单行比较符(如=、>、<、>=、<= 和 < >)进行比较

查询“战争”类题材电影的具体信息,要求输出片名和导演名

示例:

(1)电影(movie)表包含电影名、导演名和电影类型编号信息,但并不包含电影类型名称信息;电影类型(movie_type)表既包含电影类型编号信息又包含电影类型名称信息。上述这两张表的共同信息是电影类型编号,所以查询时需连接电影表和电影类型表,并以电影类型编号作为两表的连接。首先,从电影类型表查询出类型名称为“战争”的电影类型编号,使用 SQL1 作为标记

select id from movie_type where typeName=' 战争 '

(2)根据 SQL1 查询出的地区编号,在电影表中检索出电影名和导演名信息。使用 SQL2 作为标记,并将 SQL1作为查询条件代入 SQL2

select movieName 电影名 ,director 导演名 from movie where typeId=
(select id from movie_type where typeName=' 战争 ')

由于在 movie_type 表中 typeName 是唯一约束列,因而子查询 SQL1 的执行结果只能有 1 条(单行子查询)或 0 条记录。

此例还可以采用连接查询实现

select movieName 电影名 ,director 导演名 from movie m, movie_type mt
where m.typeId=mt.movie_type and typeName=' 战争 '

示例:

查询票价高于平均票价的电影信息,要求输出电影名和导演名

(1)获得平均票价,使用 SQL1 标记

select avg(ticketPrice) from movie

(2)查询票价大于平均票价的电影信息,要求输出电影名和导演名。使用 SQL2 标记,并将 SQL1 作为查询条件代入 SQL2

select movieName 电影名 ,director 导演名 from movie  where ticketPrice>( select avg(ticketPrice) from movie)

示例:

查询票价高于“战争”类题材的电影信息,要求输出电影名和导演名

(1)查询类型名为“战争”的电影类型编号,使用 SQL1 标记

select id from movie_type where typeName=' 战争 '

(2)查询“战争”类题材电影的平均票价,使用 SQL2 标记,执行时将 SQL1 作为查询条件代入 SQL2

select avg(ticketPrice) from movie where typeID=(select id from movie_type where typeName=' 战争 ')

(3)查询票价大于“战争”类题材电影平均票价的电影信息,要求输出电影名和导演名。查询语句使用 SQL3 标记,使用时将 SQL2 作为条件代入 SQL3

select movieName 电影名 ,director 导演名 from movie where ticketPrice>
(select avg(ticketPrice) from movie where typeID=(
select id from movie_type where typeName=' 战争 '))

子查询应用经验初步归纳如下:

  • 子查询一般用于 select 语句的 where 子句中,且可以嵌套
  • 编写复杂的子查询的解决思路是逐层分解查询,即从最内层的子查询开始分解,将嵌套的 SQL 语句拆分为一个个独立的 SQL 语句
  • 子查询的执行过程遵循“由里及外”的原则,即先执行最内层的子查询语句,然后将执行结果与外层的语句进行合并,依次逐层向外扩展并最终形成完整的 SQL 语句
  • 一般情况下,连接查询可改为子查询实现;但子查询却不一定可改为连接查询实现
  • 子查询与连接查询执行效率的比较:当子查询执行结果的行数较大,而主查询执行结果的行数较小时,子查询执行效率较高;反之,则连接查询执行效率较高

3.1.4 实践练习

 

3.2 多行子查询应用

3.2.1 in 比较符

使用多行比较符 in 时,主查询会与子查询中的每一个值进行比较,如果与其中的任意一个值相同,则返回。not in 与 in 的含义恰好相反

查询“战争”和“喜剧”类题材电影的相关信息,要求输出片名和导演名

示例:

(1)查询类型名为“战争”和“喜剧”的电影类型编号

select id from movie_type where typeName=' 战争 ' or typeName=' 喜剧 '

(2)查询“战争”和“喜剧”类题材电影的相关信息

select movieName 电影名 ,director 导演名 from movie where typeID in
(select id from movie_type where typeName=' 战争 ' or typeName=' 喜剧 ')

由于多行子查询返回的结果行数可以为一个,因而单行子查询也是多行子查询的一种特殊情况,所以单行子查询的“=”比较符可以替换为多行子查询的“in”比较符。但不能将多行子查询的“in”比较符替换为单行子查询的“=”比较符。

查询客户“zhang01”顾客所预订电影的具体信息,要求输出电影名、导演名、票价和片长,并按照票价升序排列

示例:

(1)查询客户“zhang01”的客户编号

select id from customer where username='zhang01'

(2)查询客户“zhang01”所预订的所有电影的排片编号

select scheduleId from ticket_sell where customerID in
(select id from customer where username='zhang01')

(3)查询客户“zhang01”所预订的所有电影的电影编号

select movieId from `schedule` where id in (select scheduleId from ticket_sell where customerID in  (select id from customer where username='zhang01'))

(4)查询客户“zhang01”所预订电影的具体信息

select movieName 电影名 ,director 导演名 ,ticketPrice 票价(元),filmLength 片长(分钟)
from movie where id in(select movieId from `schedule` where id in
(select scheduleId from ticket_sell where customerID in
(select id from customer where username='zhang01'))) order by ticketPrice

此示例嵌套较深,如果采用连接查询写法会相对简洁,特别是当商品记录数较大的情况下,连接查询的效率会更高。使用连接查询实现的 SQL 语句如下:

select distinct movieName 电影名 ,director 导演名 ,ticketPrice 票价(元),filmLength   片长(分钟) from movie m,`schedule` s,ticket_sell ts,customer c where m.id=s.movieId 
and  s.id=ts.scheduleId and ts.customerId=c.id and c.username='zhang01' order by ticketPrice

上面例子的子查询实现很好地体现了子查询应用的解决之道──“由里及外”的原则,即逐层生成包含子查询在内的主查询,逐层生成的主查询又成为上一层主查询所包含的子查询,如此依次递进(recursion),最终生成最上层的包含所有子查询的主查询。

all 关键字位于多行比较运算符之后,通过 all 关键字将一个表达式或列的值与子查询所返回的一列值中的每一行进行比较,只要有一次比较的结果为 false(假),则 all 测试返回 false,主查询不执行;否则返回 true,执行主查询

all 运算符的含义如下:

  • 当 <all 时,表示小于最小值
  • 当 >all 时,表示大于最大值

语法:

表达式或字段 多行比较运算符 all( 子查询 )

查询比所有“喜剧”类题材电影的票价都高的电影信息,要求输出片名和导演名

示例:

(1)查询类型为“喜剧”的电影类型编号

select id from movie_type where typeName=' 喜剧 '

(2)查询所有“喜剧”类题材电影的票价

select ticketPrice from movie where typeID=(
select id from movie_type where typeName=' 喜剧 ')

(4)查询比所有“战争”类题材电影的票价都高的电影信息

select movieName 电影名 ,director 导演名 from movie where ticketPrice > all  (select ticketPrice from movie where typeID=(select id from movie_type where typeName=' 喜剧 '))

由于“>all(子查询)”的含义是“大于子查询返回结果的最大值”,所以还可以采用“>(子查询所获取的最大列值)”的方式求解上面这个例子,该方法的 SQL 语句如下:

select movieName 电影名 ,director 导演名 from movie where ticketPrice > (select max(ticketPrice) from movie where typeID=( select id from movie_type where typeName=' 喜剧 ' ))

3.2.3 any|some 关键字子查询

any 与 some 的查询功能相同

any 或 some 用于子查询之前,通过 any|some 比较运算符,将一个表达式或列的值与子查询所返回的一列值中的每一行进行比较,只要有一次比较的结果为 true,则 any 或 some 测试返回 true,主查询执行;否则结果为false,主查询不执行

any|some 运算符的含义如下:

  • 当 <any|some 时,表示小于最大值
  • 当 =any|some 时,表示与 in 运算符等价
  • 当 >any|some 时,表示大于最小值

语法:

表达式或字段  多行比较运算符  any|some(子查询)

查询比任意一个“喜剧”类题材电影的票价高的电影信息,要求输出电影名和导演名

示例:

select movieName 电影名 ,director 导演名 from movie where ticketPrice > any
(select ticketPrice from movie where typeID=(
select id from movie_type where typeName=' 喜剧 '))

由于“>any(子查询)”的含义是“大于子查询返回结果的最小值”,所以还可以采用“>(子查询所获取的最小列值)”的方式求解上面这个例子,该方法的 SQL 语句如下:

select movieName 电影名 ,director 导演名 from movie where ticketPrice > (select min(ticketPrice) from movie where typeID=( select id from movie_type where typeName=' 喜剧 ' ))

3.2.4 实践练习

 

3.3 子查询特殊应用

3.3.1 from 子句中的子查询

子查询通常用于 where 子句中,但其也可在 from 子句和 select 子句中使用

示例:

影院在线售票系统为了提升影片的上座率,优化票价定价的科学性。为了了解每一个影片的票价与该类题材电影整体票价的对照关系,比较合适的做法是在显示每一个影片票价的同时,显示所属类型电影的平均票价

select mt.typeName 电影类型 , m.movieName 电影名 , m.director 导演名 , round(m.ticketPrice,2)
票价(元), round(A.avgPrice,2) 该电影类型平均票价(元) from movie m, movie_type mt,
(select typeId,avg(ticketPrice) avgPrice from movie group by typeId) A where m.typeId=mt.id and m. typeId=A.typeId order by mt.id

3.3.2 select 子句中的子查询

在 select 子句中使用子查询,其实质是将子查询的执行结果作为 select 子句的列,可以起到与连接查询异曲同工的作用

示例:

分别获取张艺谋所导演影片的上映数量以及上映班次

(1)获取张艺谋所导演影片的上映数量

select count(*) 张艺谋所导演影片的上映数量 from movie where director=' 张艺谋 '

(2)获取张艺谋所导演影片的上映班次

select count(movieId) 张艺谋所导演影片的上映班次 from `schedule` where movieId in
(select id from movie where director=' 张艺谋 ')

(3)将(1)和(2)获得的查询结果,即张艺谋所导演影片的上映数量和上映班次作为 select 子句的查询列,其形式即为 select 子句中的子查询。

select count(*) 张艺谋所导演影片的上映数量 , (select count(movieId) from `schedule` where
movieId in (select id from movie where director=' 张艺谋 ')) 张艺谋所导演影片的上映班次
from movie where director=' 张艺谋 '

exists 用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值 true 或false。exists 指定一个子查询,用于检测行的存在。当子查询的行存在时,则执行主查询表达式,否则不执行

查询所有通过影院在线售票系统预订电影票的客户姓名

语法:

主查询表达式  [not] exists ( 子查询 )

示例:

(1)主查询用于从客户表获取客户姓名。

(2)exists 指定的子查询将从售票表中获取满足“客户编号 = 客户表 . 客户编号”条件的任意数据。

(3)只要 exists 子查询的结果集中有数据行返回,exists 子查询的返回结果若为 true,则执行主查询获得所有预订电影票的客户姓名;exists 子查询的返回结果若为 false,则不执行主查询。最终的 SQL 语句如下:

select customerName 客户姓名 from customer c where exists
(select * from orders where customerID=c.customerID)

3.3.4 实践练习

 

3.4 DML 语句中的子查询

3.4.1 update 子句中的子查询

  • 子查询不仅可在 select 语句中使用,以实现需要嵌套的查询功能,还可以维护数据,完成复杂的更新、删除和插入功能
  • 为了完成上述数据维护功能,需要在 DML 的 update 语句、delete 语句和 insert 语句中使用子查询
  • 在 DML 语句中使用子查询与在 select 语句中使用子查询的原理是一致的,均为将内层子查询的结果作为外层主查询中 where 条件的参考值来使用

示例:

为响应政府提升公民灾难意识和应对能力的号召,院线将所有灾难片电影的票价降低 20%

(1)在子查询中获取类型为“灾难”的电影类型编号。

(2)在主查询中,使用 update 语句将所有灾难片电影的票价降低 20%

update product set currentPrice=currentPrice*0.9 where categoryID in(
select categoryID from category where categoryName=' 灾难 ')

示例:

计算所有客户预订电影票的总金额,并使用该金额更新客户表中“累计订票金额”的字段值

(1)在售票表中,根据客户编号进行分组,并使用“sum( 实际票价 )”汇总出每个客户的总订票金额

select customerID 客户编号 , sum(purchasePrice) 总订票金额(元) from ticket_sellgroup by customerID

(2)因为要将(1)中汇总出的每个客户的总订票金额赋给客户表中的“累计订票金额”字段,所以可以将(1)中的 SQL 作为子查询,并在主查询中执行“update 客户表 set 累计购票金额 =( 子查询中获取的每个客户的总订票金额 )”。

(3)为实现 update 语句,需在子查询中删除选择列“客户编号”,并且为了建立主查询与子查询的关联,还需要在子查询的 where 条件中设定“客户编号 = 售票表 . 客户编号”

update customer c set totalFee=(select sum(purchasePrice) from ticket_sell where customerID=c.ID group by customerID)

使用子查询删除客户“chen01”

示例:

删除数据时需要考虑表的主从关系,正确的做法是先删除从表数据,再删除主表数据。

(1)使用子查询删除售票表中客户“chen01”所有的订票记录

delete from ticket_sell where customerID in
(select id from customer where username='chen01')

(2)删除客户表中客户“chen01”的记录

delete from customer where username='chen01'

3.4.3 实践练习

 

总结:

  • 子查询将一个查询语句嵌套在另一个查询语句中,在特定情况下,一个查询语句的条件需要另一个查询语句来获取
  • 比较运算符 all 关键字用于子查询之前,通过该关键字将一个表达式或列的值,与子查询所返回的一列值中的每一行进行比较
  • exists 用于检测行的存在,该子查询实际上并不返回任何数据,而是返回值 true 或false。当子查询的行存在时,则执行主查询表达式,否则不执行
  • 在 DML 语句中使用子查询与在 select 语句中使用子查询的原理是一致的,均为将内层子查询的结果作为外层主查询中 where 条件的参考值来使用

 

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 本文链接: https://blog.csdn.net/weixin_44893902/article/details/111094651

mac 上的mysql 修改了my.cnf后不生效问题处理-爱代码爱编程

在mac上安装了mysql后,需要修改下mysql的配置,但是在mysql的安装目录下的修改了my.cnf后不生效,目录如下图: 原因是,mysql默认的配置文件并不是这个文件。 使用命令( mysql --verbose --help | grep my.cnf )查看mysql的配置文件加载顺序: MyMac:~ wx$ mysql --ve

SQL优化(二)-爱代码爱编程

order by排序方式 一般分两种,在索引中排序(索引里面数据有序),在内存中排序(内存不够的话会产生临时文件辅助排序)。其中走索引的排序会快很多。 索引排序 既然我们知道排序走索引会快很多,那我们排序时应该尽量让排序走索引。那什么情况下排序会走索引呢?我们知道查询排序语句一般由这几个部分构成:select +where+order by+limi

SQL之sqli-labs注入Less-2~4-爱代码爱编程

一、Less-2 与Less-1相同,我们同样使用?id=1'看是否可以注入, 发现不可以,并且提示错误,我们可以知道,这里不需要引号,这就是与Less-1不同的地方,除此之外,以下步骤与Less-1相同 2.接下来我们使用 order by 查看有多少列 仍然有三列? id=1 order by 3--+3.使用联合查询 union select 看有

JavaEE之Mybatis关系映射-爱代码爱编程

JavaEE之Mybatis关系映射 创建封装工具类一对一(旅客与护照)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper文件测试一对多(部门与职员)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper文件测试多对多(学生与科目)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper

jdbc复习-爱代码爱编程

今天又maven写了一下jdbc复习。 结果发现,怎么都运行不成功。 就在我一遍又一遍的找错之后,发现,竟然没导包,没添加依赖!!!! 共勉,一定要细心。 可能是因为,间隔时间长了,所以一开始也没注意,然后,一直不知道哪里错了。 现在上干货。 首先打开数据库,创建数据库db4. 在数据库中创建student表。 id,name,age,birth

jsp、servlet、jdbc实现留言板(可记住密码)-爱代码爱编程

文章目录 一、目标二、JDBC使用说明三、代码总结 使用工具:mysql、IDEA 一、目标 对留言板进行优化: 登陆页面输入用户名、密码,提交给某个servlet,该servlet可以检索数据库,验证用户名和密码是否合法,给出验证结果二、JDBC使用说明 JDBC基本功能 建立连接、发送SQL语句,处理数据库操作结果 mysql

SQL优化(二)-爱代码爱编程

order by排序方式 一般分两种,在索引中排序(索引里面数据有序),在内存中排序(内存不够的话会产生临时文件辅助排序)。其中走索引的排序会快很多。 索引排序 既然我们知道排序走索引会快很多,那我们排序时应该尽量让排序走索引。那什么情况下排序会走索引呢?我们知道查询排序语句一般由这几个部分构成:select +where+order by+limi

SQL:多表查询语句(嵌套子查询,多表连接)操作实例-爱代码爱编程

一、SQL Server多表查询,包括连接操作和嵌套子查询 背景知识: 一、连接:分成内连接和外连接,内连接相当于取交集,外连接相当于取并集 二、嵌套子查询:连接操作浪费资源,使用嵌套子查询可以避免连接同时加快执行速度,分成相关子查询和非相关子查询 名称解释(不区分大小写) student学生表: 包含属性列:sno学号、sname学

SQL之sqli-labs注入Less-2~4-爱代码爱编程

一、Less-2 与Less-1相同,我们同样使用?id=1'看是否可以注入, 发现不可以,并且提示错误,我们可以知道,这里不需要引号,这就是与Less-1不同的地方,除此之外,以下步骤与Less-1相同 2.接下来我们使用 order by 查看有多少列 仍然有三列? id=1 order by 3--+3.使用联合查询 union select 看有

JavaEE之Mybatis关系映射-爱代码爱编程

JavaEE之Mybatis关系映射 创建封装工具类一对一(旅客与护照)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper文件测试一对多(部门与职员)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper文件测试多对多(学生与科目)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper

使用子查询统计“国内长线游”线路数、线路最高价格和线路最低价格-爱代码爱编程

查看本章节 查看作业目录 需求说明: 使用子查询统计“国内长线游”线路数、线路最高价格和线路最低价格 使用子查询获得指定客户(如“魏国兰”)订购线路的相关信息,要求显示订单名和订单日期 说明:客户姓名“魏国兰”没有重名 实现思路: 需求说明(1)的解决思路: 单行子查询:从线路类型表获取类型名为“国内长线游”的类型编号主查询:使用聚合函数从

数据库设计规范与设计工具-爱代码爱编程

数据库的设计要遵守三范式来设计,这样的设计的好处是可以消除数据冗余,缺点是增加了sql语句的难度。因此,我们在设计表结构的时候不一定要完全遵守三范式,如果遇到复杂问题,可以尝试着打破三范式找到解决问题或优化性能的方法。 一、三范式 第一范式 表中的字段要保持原子性,即字段不可再分 eg 学生信息表中的联系方式有两种——邮箱和电话