网络数据库--T-SQL语言及子查询.ppt

1、第六部分 T-SQL语言及子查询,本章内容： T-SQL语言概述 T-SQL语言的简单查询 T-SQL语言的高级查询 视图 重点：熟练使用SELECT、INSERT、DELETE、UPDATE命令，创建视图 难点：高级查询、视图的使用,T-SQL语言概述,SQL的组成(分类),DATABASE TABLE CREATE VIEW INDEX SQL DDL： ALTER DROP SQL QUERY：SELECT FROM INSERT SQL SQL DML：UPDATE DELETE GRANT SQL DCL：REVOKE AUDIT 嵌入式SQL使用：在宿主语言程序中的使用规则。,SQ

2、L 语言的特点, 自含式和嵌入式两种形式 语言简洁、易学易用 高度非过程化 支持RDB三级模式结构 (5) 面向集合的操作方式,SQL(structured Query language)作为标准的结构化查询语言，已成为当前最流行的数据库操作语言。 语言的主要特点如下：, 自含式和嵌入式两种形式,自含式SQL：能够独立地进行联机交互，用户只需在终端键盘上直接键入SQL命令就可以对DB进行操作； 嵌入式SQL：能够嵌入到高级语言，如C、COBOL、FORTRAN、JAVA程序中来实现对DB的数据存取操作，给程序员设计应用程序提供了很大的方便。 在自含式SQL和嵌入式SQL不同的使用方式中，SQL

3、的语法结构基本上一致。, 语言简洁、易学易用,尽管SQL语言功能极强又有两种使用方式，但由于设计巧妙，其语言十分简洁，完成核心功能的语句只用了9个动词。 SQL语法很简单，容易学习和使用。 SQL的命令动词表：, 高度非过程化,SQL是一种第四代语言(4GL)，用户只需要提出“干什么”，无需具体指明“怎么干”； 像存取路径选择和具体处理操作等，均由系统自动完成。, 支持RDB三级模式结构, 全体基本表构成DB的概念模式 视图和部分基本表构成DB的外模式 DB的存储文件和它们的索引文件构成RDB的内模式,三级模式结构图,用户1,用户2,用户4,视图V1,视图V1,用户3,基本表B1,基本表B2,

4、基本表B4,基本表B3,存储文件S1,存储文件S2,存储文件S4,存储文件S3,SQL用户,VIEW,Base Table,Stored file,SQL语言支持的关系数据库的三级模式结构,外模式,模式,内模式,(5) 面向集合的操作方式,SQL语言采用集合操作方式，不仅查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。 非关系数据模型采用的是面向记录的操作方式，任何一个操作其对象都是一条记录。例如查询所有平均成绩在80分以上的学生姓名，用户必须说明完成该请求的具体处理过程，即如何用循环结构按照某条路径一条一条地把满足条件的学生记录读出来。,作业思考题,1.创建一

5、个数据库以你们班级为名的数据库。含有两个数据文件，一个日志文件。数据文件的大小分别为10M和8M，日志文件的大小为11M，三个 文件的最大尺寸为20M，文件增量为1M。 2.建立teacher(teacherNo,teacherName,courseno)表，course(courseno,coursename,coursetype,limitnum,willnum)表, student(studentno,studentname,courseno,grade)表。 3.为上面三个文件插入你们班相应情况的记录。 4.为student表的studentno建立聚集索引。 5.创建视图文件teco

6、ustudent(studentno,courseno,coursetype,teacherno,grade). 6.创建一个名为tel（电话号码）的用户定义数据类型，定义为varchar数据类型，长度为15位，且该列不能为空。 7.创建名为surplusnum的自定义函数，surplusnum=willnum-limitnum.并将其绑定到course表中。即在course表中增加surplusnum列。 8.检索出学java课程的成绩为前10名的同学的记录。 9.检索出学C课程的成绩在7080之间的学生的记录。用where和between两种方式检索。 10.计算在一学期的你们班各门课程的

7、平均成绩，列名的假名为平均成绩，并显示出课程名。 11.检索出学C课程的成绩大于平均成绩的同学的记录。要求用嵌套子查询和相关子查询两种方式做，比较结果。,T-SQL语言的简单查询,T-SQL语言的简单查询 SELECT在任何一种SQL语言中，都是使用频率最高的语句。可以说SELECT是SQL语言的灵魂。SELECT语句的作用是让数据库服务器根据客户端的要求搜寻出用户所需要的信息资料，并按用户规定的格式进行整理后返回给客户端。用户使用SELECT语句除可以查看普通数据库中的表格和视图的信息外，还可以查看SQL Server的系统信息。 SELECT语句具有强大的查询功能，有的用户甚至只需要熟练掌

8、握SELECT语句的一部分，就可以轻松地利用数据库来完成自己的工作。,T-SQL语言的查询语句格式,SELECT语句格式 ： SELECT ALL|DISTINCT 表名.*|列名|表达式AS 新列名 INTO 新表名 FROM 表名表别名, WHERE 条件表达式|子查询 GROUP BY 列名1, HAVING 分组表达式 UNION|INTERSECT |EXCEPTSELECTFROM ORDER BY 列名|列序号ASC|DESC, ；,T-SQL语言的简单查询,SELECT的简单查询的语法介绍 SELECT语句的常规使用方式 SELECT 列名1,列名2,列名n FROM 表名 【

9、例】下面的例子从pubs数据库的publishers（出版商）表中检索所有的出版商代号、出版商的名字以及国籍。,use pubs go select pub_id,pub_name,country from publishers go,T-SQL语言的简单查询,用“*”表示表中所有的列 语法格式如下： SELECT * FROM 表名 服务器会按用户创建表格时声明列的顺序来显示所有的列，例如查看pubs数据库的titleauthor表中的所有记录。,use pubs go select * from titleauthor,T-SQL语言的简单查询,使用TOP关键字 SQL Server 提供

10、了TOP关键字，让用户指定返回前面一定数量的数据。当查询到的数据量非常庞大时（例如有100万行），但没有必要对所有数据进行浏览时，使用TOP关键字查询可以大大减少查询花费的时间。 语法格式如下： SELECT TOP n | TOP n PERCENT 列名1,列名2,列名n FROM 表名 其中； TOP n表示返回最前面的n行，n表示返回的行数。 TOP n PERCENT表示返回的前面的n%行。 【例】从northwind数据库的customers表中返回前面10行数据。,use northwind go select top 10 * from customers,T-SQL语言的简单

11、查询,使用DISTINCT关键字 前面介绍的最基本的查询方式会返回从表格中搜索到的所有行的数据，而不管这些数据是否重复，这常常不是用户所希望看到的。使用DISTINCT关键字就能够从返回的结果数据集合中删除重复的行，使返回的结果更简洁。 在使用DISTINCT关键字后，如果表中有多个为NULL的数据，服务器会把这些数据视为相等。 【例】从pubs数据库的publishers表中搜索出所有国家的名字（要求不能重复）。,use pubs go select distinct country from publishers go,T-SQL语言的简单查询,【例】当同时对2列数据进行查询时，如果使用了

12、DISTINCT关键字，将返回这2列数据的惟一组合。,use pubs go select distinct city,state from authors go,T-SQL语言的简单查询,使用计算列 在进行数据查询时，经常需要对查询到的数据进行再次计算处理。T-SQL允许直接在SELECT语句中使用计算列。计算列并不存在于表格所存储的数据中，它是通过对某些列的数据进行演算得来的结果。 【例】将pubs数据库中的titles表中的每本书的销售价格降低30%，同时显示每本书的书号，是哪类书，原价，和降价后的价格。,由于没有为计算列指定列名，所以返回的结果上看不到它的名字。 在Transact-S

13、QL的计算列上，允许使用+、-、*、/ 、%以及字符串连接符（+）。,use pubs select title_id , type , price , price- price* 0.3 from titles go,服务器返回的结果是： title_idtypeprice（无列名） - - - - BU1032business19.990013.99300 BU1111business11.95008.36500 BU2075business2.99002.09300 BU7832business19.990013.99300 MC2222mod_cook19.990013.99300 （

14、所影响的行数为 18 行）,T-SQL语言的简单查询,【例】查询作者的姓名（用+来连接作者的姓和名）以及作者所居住的州和城市。,use pubs go select au_lname + .+ au_fname ,city+ ,+state from authors go,执行后的结果如下： - - White.JohnsonMenlo Park,CA Green.MarjorieOakland,CA Carson.CherylBerkeley,CA OLeary.MichaelSan Jose,CA （所影响的行数为 23 行）,T-SQL语言的简单查询,操作查询的列名 T-SQL提供了在S

15、ELECT语句中操作列名的方法。用户可以根据实际需要对查询数据的列标题进行修改，或者为没有标题的列加上临时的标题。 对列名进行操作有3种方式： （1）将要显示的列标题用单引号括起来后，写在列名后面，两者之间用空格隔开。 例如： （2）用“=”来连接列表达式,将要显示的列标题用单引号括起来后放在“=”前面。例如： （3）用AS关键字来连接列表达式和指定的列名。例如： 执行（1）、（2）与（3）返回结果相同。,use pubs go select title_id 图书代号,price 原价,price-price*0.3 现价 from titles,use pubs go select 图书代

16、号=title_id, 原价=price,现价=price-price*0.3 from titles go,use pubs go select title_id as图书代号,price as原价,price-price*0.3 as现价 from titles go,T-SQL语言的简单查询,SELECT的带条件的查询 使用WHERE子句的目的是为了从表格的数据集中过滤出符合条件的行。 语法格式如下： SELECT 列名1,列名2,列名n FROM 表名 WHERE 条件 使用WHERE子句可以限制查询的范围，提高查询效率。在使用时，WHERE子句必须紧跟在FROM子句后面。WHERE子

17、句中的条件表达式包括算术表达式和逻辑表达式两种；SQL Server 对WHERE子句中的查询条件的数目没有限制。,T-SQL语言的简单查询,Where 子句运算符,使用算术表达式 使用算术表达式作为搜索条件的一般表达形式是： 表达式 算术操作符 表达式 表达式为：常量、变量和列表达式的任意有效组合。 WHERE子句中允许使用的算术操作符包括：=（等于）、（大于）、（不等于）、!（不大于）、!=（大于等于）、=（小于等于）、!=（不等于）。,比较：、=、=、 确定范围：Between A and B、Not Between A and B 确定集合：IN、NOT IN 字符匹配：LIKE，NO

18、T LIKE 空值：IS NULL、IS NOT NULL 多重条件：AND、OR、NOT,T-SQL语言的简单查询,例如：查询pubs库的titles表中，价格打了8折后仍大于12美元的书号、种类以及原价。,use pubs go select title_id as 书号,type as 种类,price as 原价 from titles where price -price * 0.212,使用逻辑表达式 在T-SQL里的逻辑表达式共有3个。分别是： NOT：非，对表达式的否定。 AND：与，连接多个条件，所有的条件都成立时为真。 OR：或，连接多个条件，只要有一个条件成立就为真。 在

19、T-SQL中逻辑表达式共有3种可能的结果值，分别是TRUE，FALSE和UNKOWN。UNKOWN是由值为NULL的数据参与逻辑运算得出的结果。表6-1、表6-2和表6-3分别列出了进行逻辑运算时各种情况下的结果。,T-SQL语言的简单查询,表6-1 AND运算各种情况,表6-2 OR运算各种情况,表6-3 NOT运算各种情况,T-SQL语言的简单查询,【例】查询所有在美国加利福尼亚州的出版社。,use pubs go select pub_id as 出版社代号, pub_name as 出版社名称,city as 城市, state as 州, country as 国家 from pub

20、lishers where country=usaand state =ca go,T-SQL语言的简单查询,使用BETWEEN关键字 使用BETWEEN关键字可以更方便地限制查询数据的范围。 语法格式为： 表达式NOT BETWEEN 表达式1 AND 表达式2 例如：查询价格在15和20美元之间的书(表titles)的书号、种类和价格。,use pubs go select title_id as 书号,type as 种类 ,price as 原价 from titles where price between $15 and $20 go,使用BETWEEN表达式进行查询的效果完全可以

21、用含有=和和的逻辑表达式来代替。,T-SQL语言的简单查询,例如：上面的查询语句可以用下面的语句代替。 use pubs go select title_id as 书号,type as 种类,price as 原价 from titles where price = $15 and price =$20 go 【例】查询书价大于20和书价小于15的书的代号、种类和价格。,使用BETWEEN限制查询数据范围时同时包括了边界值，而使用NOT BETWEEN进行查询时没有包括边界值。,若使用BETWEEN，查询语句如下：,use pubs go select title_id as 书号,type

22、 as 种类, price as 原价 from titles where price $20 go,use pubs go select title_id 书号,type 种类,price 价格 from titles where price not between $15 and $20 go,T-SQL语言的简单查询,思考作业题： 在Xk数据库中的Course表中查询WillNum（报名人数）在35至66之间的课程名称，学分，教师，限制选课人数，被选中上该课程的人数情况，并以报名人数降序排列。,T-SQL语言的简单查询,使用IN关键字 同BETWEEN关键字一样，IN的引入也是为了更方便

23、地限制检索数据的范围，灵活使用IN关键字，可以用简洁的语句实现结构复杂的查询。 语法格式为： 表达式 NOT IN (表达式1 , 表达式2 ,表达式n) 【例】查询所有居住在KS、CA、MI或IN州的作家。,use pubs go select au_id,au_fname,au_lname from authors where state IN (CA,KS,MI,IN) go,如果不使用IN关键字，这些语句可以使用where的语句代替：,use pubs go select au_id,au_fname,au_lname from authors where state=CA or st

24、ate=KSor state=MIor state=IN,T-SQL语言的简单查询,【例】查询所有不在上述4个州居住的作家。,use pubs go select au_id,au_lname,au_fname from authors where stateCA and stateKS and stateMI and stateIN go,use pubs go select au_id,au_lname,au_fname from authors where state not in (CA,KS,MI,IN) go,与使用where的语句等价：,T-SQL语言的简单查询,模糊查询 在实际

25、的应用中，用户不会总是能够给出精确的查询条件。因此，经常需要根据一些并不确切的线索来搜索信息。T-SQL提供了LIKE子句来进行这类模糊搜索。 语法格式为： 表达式 NOT LIKE 条件 条件通常与通配符配合使用。 通配符的使用 LIKE子句在大多数情况下会与通配符配合使用。SQL Server提供了以下4种通配符供用户灵活实现复杂的查询条件。 %（百分号）：表示从0n个任意字符。 _（下划线）：表示单个的任意字符。 （封闭方括号）：表示方括号里列出的任意一个字符。 ：任意一个没有在方括号里列出的字符。 【例】查询所有名字以D打头的作家。,use pubs go select au_lnam

26、e + .+au_fname from authors where au_fname like D%,T-SQL语言的简单查询,【例】查询所有au_id满足前2个字符为“72”，第4个字符为“-”的作家的姓名和电话号码。,use pubs go select au_lname,au_fname, phone, au_id from authors where au_id like 72_-% go,使用方括号可以将字符搜索的范围进一步缩小。 【例】查询出所有au_id满足前2个字符为“72”，第3个字符为3、4、5中的一个，第4个字符为“-”的作家的姓名和电话号码。,use pubs go s

27、elect au_lname,au_lname, phone, au_id from authors where au_id like 72345-% go,T-SQL语言的简单查询,还可以使用方括号和连字符来指定一个值的范围。 【例】查询所有au_id的第一个字符为5-9、第二个字符为1-4的作家的姓名和电话号码,use pubs go select au_lname,au_fname, phone, au_id from authors where au_id like 5-91-4% go,【例】查询所有名字不以D打头的作家。,use pubs go select au_lname +

28、.+au_fname from authors where au_fname not like D%,select au_lname+.+au_fname from authors where au_fname like d%,T-SQL语言的简单查询,函数的使用 为了有效处理用户通过使用SQL查询得到的数据集合，SQL Server提供了一系列统计函数。这些函数把存储在数据库中的数据描述为一个整体而不是一行行孤立的记录，通过使用这些函数可以实现数据集合的汇总或是求平均值等各种运算。 常用统计函数 最常见的统计函数如表6-4所示。 表6-4 SQL Server 的统计函数,T-SQL语言的简

29、单查询,统计函数的使用格式： COUNT(DISTINCT|ALL *|列名) SUM(DISTINCT|ALL 列名) AVG(DISTINCT|ALL 列名) MAX(DISTINCT|ALL 列名) MIN(DISTINCT|ALL 列名),统计函数可用于SELECT子句中的目标列表中，或在HAVING子句的分组表达式中用作条件。 对分出的每一组用HAVING进行筛选,筛选条件要用到统计函数。,T-SQL语言的简单查询,例如：查询titles中所有书的平均价格。 use pubs go select avg(price) from titles 返回的结果是： - 14.7662 （所影

30、响的行数为 1 行） 使用统计函数所返回的结果同使用计算列一样，没有列标题。不过用户可以像使用计算列一样，为统计函数返回的结果指定一个假的列名。 例如：可以为上例的avg(price)指定列名avg_price。,use pubs select avg(price) avg_price from titles go 返回的结果是： avg_price - 14.7662,T-SQL语言的简单查询,与统计函数一起使用WHERE子句 通过与子句一起使用集合函数，可以指定集合操作中应该包括哪些行。 例如：查询各种商业图书的平均价格。 use pubs go select avg(price) avg

31、_price from titles where type = business 与统计函数一起使用DISTINCT关键字 在T-SQL中，允许与统计函数如count()，sum()和avg()一起使用DISTINCT关键字来处理列或表达式中不同的值。 例如：查询在sales表中有多少家书店。 use pubs go select count(distinct stor_id) from sales 若不用DISTINCT关键字，则返回结果为21。,T-SQL语言的简单查询,思考作业题： 作业一：在Xk数据库中的Student表中，统计班级编码为20000001的学生数。 作业二：统计表Cou

32、rse中00电子商务班的最少报名人数、最多报名人数及平均报名人数。,作业情况,5. declare i int,sum int select i=1,sum=0 while i0 begin select sum=sum+i,i=i+1 if i130 break end print sum,declare i int,sum int select i=1,sum=0 while i=130 begin select sum=sum+i,i=i+1 end print sum,declare i int,sum int,csum int select i=1,sum=0 while i0 be

33、gin select sum=sum+i,i=i+1 if i130 continue else begin select csum=sum-100 break end end print csum,declare i int,sum int,x int set i=1 set sum=0 set x=100 while i0 begin select sum=sum+i,i=i+1 if i130 continue else break end set sum=sum-x select sum as 数据结果 print sum go,出现的问题,删除一个数据库中的文件 alter data

34、base lfj remove file lfj_data 增加一个文件组 Alter database lfj Add filegroup warehouse 在文件组上加一个文件 alter database lfj add file (name=lfj_Data, filename=e:sqlserverexamplelfj_data.ndf, size=2) to filegroup warehouse,alter database lfj remove filegroup lr,declare i int,sum int,csum char(20) select i=0,sum=0

35、while i130 begin select i=i+1 if i=100 continue select sum=sum+i end print sum,T-SQL语言的简单查询,查询结果排序 SELECT语句获得的数据一般是没有排序的。为了方便阅读和使用，最好对查询的结果进行一次排序。SQL语言中，用于排序的是ORDER BY子句。 语法格式为： ORDER BY 表达式1 ASC | DESC ,表达式2 ASC | DESC,n 其中，表达式是用于排序的列。可以用于多列进行排序，各列在ORDER BY子句中的顺序决定了排序过程中的优先级。 Ntext、Text或Image类型的列不允

36、许出现在ORDER BY子句中。 在默认的情况下，ORDER BY按升序进行排列即默认使用的是ASC关键字。如果用户特别要求按降序进行排列，必须使用DESC关键字。 【例】查询pubs数据库中的表stores中商店的名字。 use pubs go select stor_name from stores 【例20】查询pubs数据库中的表stores中商店的名字，并按商店名的降序给商店排序。,use pubs go select stor_name from stores order by stor_name DESC,T-SQL语言的简单查询,如果在某一列中使用了一个计算列，例如对某一列的值

37、使用了函数或表达式，而用户又希望针对该列的值进行排序，那么必须在ORDER BY子句中再包含该函数或表达式，或者使用为该计算列临时分配的列名。 【例】查询titles中各类书的销售收入和书号，并按照各种书的销售利润降序排列。 use pubs go select title_id,income=price*ytd_sales from titles order by income desc 用户也可以根据未曾出现在 SELECT列表中的值进行排序。 【例】查询titles中各类书的销售收入和书号，并按照各种书的价格降序排列。,use pubs go select title_id,income

38、=price*ytd_sales from titles order by price desc,T-SQL语言的简单查询,也可以根据两列或多列的结果进行排序，只要用逗号分隔开不同的排序关键字就可以了。 【例】查询titles中各类书的书号、价格、年销售量和销售收入，并用年销售量和书价进行升序排列。 use pubs go select title_id,price,ytd_sales, income=price*ytd_sales from titles order by ytd_sales,price go,title_idpriceytd_salesincome - - - - MC30

39、26NULLNULLNULL PC9999NULLNULLNULL PS21067.0000111777.0000 TC321820.95003757856.2500 PS137221.59003758096.2500 MC222219.9900203240619.6800 PS209110.9500204522392.7500 （所影响的行数为 18 行）,查询结果为：,T-SQL语言的简单查询,使用分组 在大多数情况下，使用统计函数返回的是所有行数据的统计结果。如果需要按某一列数据的值进行分类，在分类的基础上再进行查询，就要使用GROUP BY子句了。 简单分组 【例】按书的种类分类，求出

40、3种类型(business,mod_cook,trad_cook)书籍的价格总和、平均价格以及各类书籍的数量。 use pubs go select type,sum(price) sum_price,avg(price) avg_price,count(*) number from titles where type in(business,mod_cook,trad_cook) group by type,type sum_priceavg_price number - - - - business 54.9200 13.7300 4 mod_cook 22.9800 11.4900 2

41、trad_cook 47.8900 15.9633 3,通过这个结果可以看出，所有的统计函数都是对查询出的每一行数据进行分类以后再进行统计计算。所以在结果集合中，对所进行分类的列的每一种数据都有一行统计结果值与之对应。,T-SQL语言的简单查询,我们也可以根据多列进行分组。这时统计函数按照这些列的惟一组合来进行统计计算。 【例】按titles表中的书的种类和出版商代号分类，返回一个平均价格和总价格。,use pubs select type,pub_id,avg(price) avg_price,sum(price) sum_price from titles where type in (b

42、usiness,trade_cook,mod_cook) group by type,pub_id,使用HAVING筛选结果 当完成数据结果的查询和统计后，可以使用HAVING关键字来对查询和统计的结果进行进一步的筛选。 【例】查询所有价格超过10美元的书的种类和平均价格。,use pubs go select type , avg(price) avg_price from titles where price$10 group by type,T-SQL语言的简单查询,【例】在所有价格超过10美元的书中，查询所有平均价格超过18美元的书的种类和平均价格。,use pubs go selec

43、t type , avg(price) avg_price from titles where price10 group by type having avg(price)$18,WHERE子句在求平均值之前从表中选择所需要的行，HAVING子句在进行统计计算后产生的结果中选择所需要的行。即：WHERE子句作用于基本表或视图，HAVING短语作用于组。,T-SQL语言的简单查询,说明：GROUP BY子句中不支持对列分配的假名，也不支持任何使用了统计函数的集合列。另外，对SELECT后面每一列数据除了出现在统计函数中的列以外，都必须在GROUP BY子句中应用，即SELECT子句中的选项列表

44、中出现的列包含在聚合函数中或者包含在GROUP BY子句中 。 下面的查询是错误的。 use pubs select pub_id, type, avg(price)avg_price,sum(price)sum_price from titles where type in (business,trad_cook,mod_cook) group by type 服务器返回错误信息： 服务器: 消息 8120，级别 16，状态 1，行 2 列 titles.pub_id 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。,T-SQL语言的简单查询,GROUP

45、 BY子句能完成汇总，但是却不能显示细节。 利用COMPUTE子句，汇总结果是附加在细节之后显示的，这样用户既能看到细节，又能看到汇总行。,T-SQL语言的简单查询,COMPUTE (),() BY , 这里只能是用于SELECT计算查询的函数SUM、AVG、MIN、MAX和COUNT等。 COMPUTE子句中的BY子句是用来说明分组的，如果在COMPUTE子句中不使用BY子句，则是对整个表进行汇总。 这里使用BY子句，也必须使用ORDER BY子句，BY子句指出的列必须和ORDER BY子句指出的列顺序相同，但BY子句的列数可以少于ORDER BY子句的列数。,T-SQL语言的简单查询,使用

46、compute子句 Compute用来计算总计或进行分组小计，总计值或小计值将作为附加新行出现在检索结果中。该子句用在where子句之后。 例：查找书的种类为business的出版商号，标题和价格，并计算此类书的平均价格和总价格。,use pubs select pub_id as 出版商号,title as 标题,price as 价格 from titles where type=business compute avg(price),sum(price),T_SQL的简单查询,使用compute by子句 使用compute by子句对by后面给出的列进行分组显示，并计算该列的分组小计。

47、使用compute by子句时必须使用order by对compute by中by指定的列进行排序。 例：按type显示titles表中的type,pub_id,title,pubdate，并计算每类书的平均价格。,use pubs select type,pub_id,title,price,pubdate from titles order by type compute avg(price) by type,而如果要用group by的话，查询语句只能用： use pubs select type,avg(price) avg_price from titles group by typ

48、e,T_SQL语言的高级查询,使用子查询 子查询是指一条SELECT语句作为另一条SELECT语句的一部分，外层的SELCT语句称为外部查询，内层的SELECT语句称为内部查询（或子查询）。子查询分两种：嵌套子查询和相关子查询。,嵌套子查询 嵌套子查询的执行不依赖于外部嵌套。 嵌套子查询的执行过程为：首先执行子查询，子查询得到的结果集不被显示出来，而是传给外部查询，作为外部查询的条件使用，然后执行外部查询，并显示查询结果。子查询可以多层嵌套。 嵌套子查询一般也分为两种：子查询返回单个值和子查询返回一个值列表。 （1）返回单个值，该值被外部查询的比较操作（如，= 、!=、=）使用，该值可以使子查

49、询中使用集合函数得到的值。 【例30】查询所有价格高于平均价格的书。,use pubs go select title from titles where price (select avg(price) from titles),在这个例子中，SQL Server首先获得“select avg(price) from titles”的结果集，该结果集为单行单列，然后将其作为外部查询的条件执行外部查询，并得到最终的结果。,T_SQL语言的高级查询,嵌套查询： 在SELECT FROM WHERE语句结构的 WHERE子句中可嵌入一个SELECT语句块 其上层查询称为外层查询或父查询 其下层查询

50、称为内层查询或子查询 SQL语言允许使用多重嵌套查询 在子查询中不允许使用ORDER BY子句 嵌套查询的实现一般是从里到外，即先进行 子查询，再把其结果用于父查询作为条件,T_SQL语言的高级查询,【例】查询书号为pc1035的作者的作者号、作者姓名。,use pubs go select au_id,au_lname,au_fname from authors where au_id=(select au_id from titleauthor where title_id=pc1035 ) 得到的结果为： au_id au_lname au_fname - - - 238-95-7766

51、 Carson Cheryl （所影响的行数为 1 行） 上例的查询也可以用前面讲过的表连接来实现，代码如下：,use pubs go select authors.au_id,au_lname,au_fname from authors,titleauthor where authors.au_id=titleauthor.au_id and title_id=pc1035,得到的结果与例2使用子查询一样，但连接操作要比子查询快，所以能使用表连接的时候应尽量使用表连接。,T_SQL语言的高级查询,（2）返回一个值列表，该列表被外部查询的IN、NOT IN、ANY或ALL比较操作使用。 IN表

52、示属于，即外部查询中用于判断的表达式的值与子查询返回的值列表中的一个值相等；NOT IN表示不属于。,【例】查询所有出版了书的作者的信息。,ANY、SOME和ALL用于一个值与一组值的比较，以“”为例，ANY 表示大于一组值中的任意一个，ALL表示大于一组值中的每一个。比如，ANY(1,2,3)表示大于1；而ALL(1,2,3)表示大于3。SOME在SQL-92标准中与ANY含义相同。,use pubs select au_id,au_lname,au_fname from authors where au_id in (select au_id from titleauthor),【例】查询

53、没有出版书的作者的信息。,use pubs select au_id,au_lname,au_fname from authors where au_id not in (select au_id from titleauthor),T_SQL语言的高级查询,相关子查询 在相关子查询中，子查询的执行依赖于外部查询，多数情况下是子查询的WHERE子句中引用了外部查询的表。 相关子查询的执行过程与嵌套子查询完全不同，嵌套子查询中子查询只执行一次，而相关子查询中的子查询需要重复地执行。相关子查询的执行过程如下： （1）子查询为外部查询的每一行执行一次，外部查询将子查询引用的列的值传给子查询。 （2）

54、如果子查询的任何行与其匹配，外部查询就返回结果行。 （3）再回到第一步，直到处理完外部表的每一行。,select title_id from sales s1 where qty (select avg(qty) from sales s2 where s1.title_id=s2.title_id),例如：查找销售量大于平均销售量的书的书号、书名。,与下面的程序比较一下：,select title_id from sales where qty (select avg(qty) from sales),T_SQL语言的高级查询,在查询的基础上创建新表 使用SELECT INTO语句可以在查询

55、的基础上创建新表，SELECT INTO语句首先创建一个新表，然后用查询的结果填充新表。 语法格式为： SELECT 列 INTO 新表 FROM 源表 WHERE 条件1 GROUP BY 表达式1 HAVING 条件2 ORDER BY 表达式2ASC|DESC,例如：建立一个新表B_TITLEAUTHOR，内容为商业类书的情况。,select title,price,au_fname,au_lname into b_titleauthor from titles t join titleauthor ta on (t. title_id=ta. title_id ) join autho

56、rs a on (a.au_id=ta.au_id) where type=business,T_SQL语言的高级查询,连接查询 在数据库的应用中，经常需要从多个相关的表中查询数据，这就需要使用连接查询。有内连接、外连接和交叉连接三种形式。 内连接（Inner join）：只包含满足连接条件的数据行 自然连接：自然连接将两个表中的列进行比较，将两个表中满足连接条件的行组合起来，作为结果，但是连接的列只显示一次。自然连接消除了相等连接产生的冗余。自然连接有两种形式的语法： 语法一： SELECT 列 FROM 表1 inner JION 表2 ON 表1.列=表2.列 语法二： SELECT 列

57、 FROM 表1,表2 WHERE表1.列=表2.列,usepubs select titles.title_id,title,au_id,type,price from titles inner join titleauthor on titles.title_id=titleauthor.title_id,【例】从titles和titleauthor表中查询书的书号、书名、作者号、类型和价格。,use pubs select titles.title_id,title,au_id,type,price from titles,titleauthor where titles.title_i

58、d=titleauthor.title_id,T_SQL语言的高级查询,得到结果为： title_idtitleau_idtypeprice - - - - - BU1032The Busy Executives Database Guide213-46-8915business19.9900 BU1032The Busy Executives Database Guide409-56-7008business19.9900 BU1111Cooking with Computers: Surreptitious Balance Sheets267-41-2394business11.9500 BU1111Cooking with Computers: Surreptitious Balance S