SQL查询-高级查询2 (1)

1、 SQL SQL查询查询复复 习习 ？知识点？知识点1.什么条件的表合适连接？2.基本格式？ SELECT ? FROM ? WHERE ? 多张表要建立连接查询呢？第第7章章 SQL 查询查询 连接查询连接查询 子查询（嵌套查询）子查询（嵌套查询）7.5 子查询子查询 将一个查询块嵌套在另一个查询块的将一个查询块嵌套在另一个查询块的条件子句中的查询被称为条件子句中的查询被称为嵌套查询（子查嵌套查询（子查询）。询）。nSQL Server 允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。n嵌套查询一般的求解方法是由里向外进行处理，即每个子查询在上一级查询处理之前求解n子查询的SELECT

2、语句中不能使用ORDER BY 子句，ORDER BY子句只能对最终查询结果排序7.5 子查询子查询一般一般SELECT子查询是嵌套在子查询是嵌套在SELECT、INSERT、UPDATE、DELETE语句的条件中使语句的条件中使用。用。子查询能够将比较复杂的查询分解成几个简子查询能够将比较复杂的查询分解成几个简单的查询，使得问题容易解决，并且能减少查询单的查询，使得问题容易解决，并且能减少查询运行时间。运行时间。嵌套查询的过程是：嵌套查询的过程是：首先执行内部查询（子首先执行内部查询（子查询），它查询出来的数据并不被显示出来，而查询），它查询出来的数据并不被显示出来，而是传递给外层语句，并作

3、为外层语句的查询条件是传递给外层语句，并作为外层语句的查询条件来使用。来使用。7.5.1 使用比较运算符的子查询使用比较运算符的子查询在在Book2表中，查询高于平均价格的图书信息。表中，查询高于平均价格的图书信息。分析：分析： 1.求出图书的平均价格求出图书的平均价格 2.比平均价格高的比平均价格高的SELECT * FROM Book2 WHERE 定价定价 ( SELECT AVG( 定价定价) FROM Book2 )加个问题：加个问题：某个出版社的平均价格呢？7.5 子查询子查询使用子查询时要注意以下几点：使用子查询时要注意以下几点：1) 子查询需用圆括号子查询需用圆括号( )括起来

4、；括起来；2) 子查询内还可以再嵌套子查询；子查询内还可以再嵌套子查询；3) 子查询的子查询的SELECT语句中不能使用语句中不能使用image、text、ntext数据类型；数据类型；4) 子查询返回的结果值的数据类型必须匹配新增列子查询返回的结果值的数据类型必须匹配新增列或或WHERE子句中的数据类型；子句中的数据类型；5) 子查询中不能使用子查询中不能使用COMPUTEBY和和INTO子子句。句。 6)子查询的子查询的SELECT语句中语句中不宜不宜使用使用ORDER BY 子子句，句，ORDER BY子句只能对最终查询结果排（子句只能对最终查询结果排（除非另除非另外还指定了外还指定了T

5、OP ，否则，否则，ORDER BY 子句在视图、内子句在视图、内联函数、子查询和公用表表达式联函数、子查询和公用表表达式等中无效等中无效）P177.5.1 使用比较运算符的子查询使用比较运算符的子查询【例【例7-24】在】在Book表中，查询高于平均价格的表中，查询高于平均价格的图书信息。图书信息。USE LibrarySELECT * FROM Book WHERE Price ( SELECT AVG( Price ) FROM Book )子查询的返回值子查询的返回值连接运算符：连接运算符：= = 连接运算符：连接运算符：IN或： ALL SOME ANY注意：注意：该子查询的返回值只

6、有一个，用该子查询的返回值只有一个，用=思考：思考：如果是某姓名（李晶）的如何？如果是某姓名（李晶）的如何？返回唯一值的子查询返回唯一值的子查询问题：问题：子查询的结果唯一吗？如果要同时子查询的结果唯一吗？如果要同时输出姓名，查询方式为多表查询输出姓名，查询方式为多表查询 or 子查子查询？询？子查询-嵌套查询 如果子查询的返回值不止一个，而是一个集合时，如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以使用则不能直接使用比较运算符，可以使用IN、NOT IN或或者在比较运算符和子查询之间插入者在比较运算符和子查询之间插入ANY或或ALL。NOT IN子查询：集合子查询

7、：集合1包含在集合包含在集合2里为真里为真,反之为反之为假假 集合集合1 NOT IN 集合集合2 比较子查询：比较结果为真假，为真返回该行比较子查询：比较结果为真假，为真返回该行 集合集合1 ALL|ANY|SOME 集合集合2返回一组值的子查询返回一组值的子查询7.5.2 使用使用IN的子查询的子查询【例【例7-25】在】在UserTb表和表和Lending表中，查询表中，查询借过书的借过书的读者信息。读者信息。分析：分析：.？ USE LibrarySELECT *FROM UserTbWHERE UserId IN ( SELECT UserId FROM Lending )7.5.2

8、 使用使用IN的子查询的子查询【例【例7-26】在】在UserTb表和表和Lending表中，查询表中，查询没有借过书的没有借过书的读者信息。读者信息。USE LibrarySELECT * FROM UserTbWHERE UserId NOT IN ( SELECT UserId FROM Lending )案例：案例：查询查询201003班没有学习班没有学习900011课程的学课程的学生的学号、姓名生的学号、姓名分析：分析：查哪张表获得学习了查哪张表获得学习了900011课程的学生课程的学生的学号？的学号？SELECT 学号学号,姓名姓名From 学生信息表学生信息表Where 班级编号

9、班级编号=201003 and 学号学号 not IN (Select 学号学号 From 成绩表成绩表 Where 课程代码课程代码=900011)子查询子查询-嵌套查询嵌套查询7.5.4 使用使用ALL的子查询的子查询 ALL的用法和的用法和ANY或或SOME一样，也是把列值与一样，也是把列值与子查询结果进行比较，但它不要求任意结果值的列值为子查询结果进行比较，但它不要求任意结果值的列值为真，而是要求所有列的结果都为真，否则就不返回行。真，而是要求所有列的结果都为真，否则就不返回行。【例【例7-28】查询没有在】查询没有在“2009-01-02”日期前借过书的日期前借过书的学生信息。学生信

10、息。USE LibrarySELECT *FROM UserTbWHERE UserId ALL ( SELECT UserId FROM Lending WHERE LendDate =all (Select avg(成绩成绩) From 成绩表成绩表 group by 课程代码课程代码)思考题：思考题：1.查比查比各位同学平均分各位同学平均分都高的学号、成绩都高的学号、成绩2.平均成绩最高的学号？平均成绩最高的学号？ 姓名？姓名？子查询子查询-嵌套查询嵌套查询子查询子查询-使用使用ALLALL关键字关键字 见见: :例例6_06_0子查子查询实例询实例思考题：思考题：1. 查询成绩表中学生

11、所考某课程且成绩小于本课程平均查询成绩表中学生所考某课程且成绩小于本课程平均分的学生成绩记录。分的学生成绩记录。2.查询所有有成绩的学生的基本信息查询所有有成绩的学生的基本信息。没成绩的呢？没成绩的呢？3.查询学生表中比查询学生表中比某某班每个学生年龄都要大的学生记录班每个学生年龄都要大的学生记录？ 参见：参见：例例6_0子查询实例子查询实例4.如何查找书籍定价比如何查找书籍定价比平均定价高的平均定价高的出版社？出版社？子查询-嵌套查询使用使用SOME和和ANY的子查询的子查询-利用利用ANY谓词实现查询所有学习了数据结构谓词实现查询所有学习了数据结构课课(课程号：课程号：900002)的学号

12、和姓名。的学号和姓名。 SELECT S.学号学号,姓名姓名 FROM 学生信息表学生信息表 S WHERE 学号学号 = ANY (SELECT 学号学号 FROM 成绩表成绩表 WHERE 课程代码课程代码=900002);见见: :例例6_06_0子查子查询实例询实例使用使用SOME和和ANY的子查询的子查询-查询学号最小的学生成绩情况查询学号最小的学生成绩情况SELECT * FROM 成绩表成绩表 where 学号学号 = all (SELECT 学号学号 FROM 学生信息表学生信息表);或：或：SELECT * FROM 成绩表成绩表 where 学号学号 = (SELECT t

13、op 1 学号学号 FROM 学生信息表学生信息表 order by 学号学号 );P4见见: :例例6_06_0子查子查询实例询实例使用使用SOME和和ANY的子查询的子查询【例【例7-27】在】在Book中，查询低于平均价格的图书信息。中，查询低于平均价格的图书信息。 USE LibrarySELECT *FROM BookWHERE Price SOME ( SELECT AVG ( Price ) FROM Book) SOME是把每一行指定的列值与子查询的结果进行是把每一行指定的列值与子查询的结果进行比较，如果哪行的比较结果为真，满足条件就返回该行。比较，如果哪行的比较结果为真，满足

14、条件就返回该行。 ANY和和SOME完全等价，即能用完全等价，即能用SOME的地方完全的地方完全可以使用可以使用ANY。7.5.5 使用使用EXISTS的子查询的子查询相关子查询是指在子查询中, 子查询的查询条件中引用了外层查询表中的属性值。-查询没有学习任何课程的学生的学号、姓名查询没有学习任何课程的学生的学号、姓名Use 教学成绩管理数据库教学成绩管理数据库goselect 学号学号, 姓名姓名from 学生信息表学生信息表where not exists (select * from 成绩表成绩表 where 成绩表成绩表.学号学号=学生信息表学生信息表.学号学号)子查询实例子查询实例7

15、.5.5 使用使用EXISTS的子查询的子查询EXISTS只注重子查询是否返回行。只注重子查询是否返回行。【例【例7-29】查询在】查询在2010-01-01被借阅的图被借阅的图书名称、作者和出版社。书名称、作者和出版社。USE LibrarySELECT BookName, Author, PublishFROM Book AWHERE EXISTS (SELECT * FROM Lending WHERE LendDate = 2010-01-01 AND A.BookId =BookId )连接查询连接查询句式简单易于阅读，句式简单易于阅读，SELECT的结的结果列来自于多表时使用较适合，系统开销较大。果列来自于多表时使用较适合，系统开销较大。子查询子查询句式层次清晰，句式层次清晰，SELECT的结果列来的结果列来自于单表