《数据库原理与应用》06.表数据的选择操作

孙发勤yzu.sun@163.com扬州大学新闻与传媒学院第6章

SQLServer2005T-SQL数据查询《数据库原理与应用SQLServer2005》第6讲SQLServer2005T-SQL数据查询教学目标：熟练掌握查询语法格式和使用掌握投影查询、连接查询、选择查询、分组统计查询、限定查询、排序查询和保存查询掌握常用的连接查询一般掌握子查询SQLServer2005第6章主要内容

SELECT查询语句1连接查询2

子查询3联合查询41

小结5SQLServer20056.1SELECT查询语句6.1.1SELECT语句的语法格式SELECT[ALL|DISTINCT]列表达式[INTO新表名]FROM表名列表[WHERE逻辑表达式][GROUPBY列名][HAVING逻辑表达式][ORDERBY列名[ASC|DESC]]SQLServer20056.1.2

SELECT语句的执行方式

步骤：单击工具栏上的“新建查询”按钮，在右边窗口输入查询语句，单击工具栏或“查询”菜单中的“执行”，可在右下方的窗口看到查询的结果。例子：图书管理系统数据库Library表Reader(RID,Rname,Rsex,RnativeP,Rbirthday,TypeID,Lendnum)PK：RIDFK:TypeID表Book(BID,Bname,Author,PubComp,PubDate,Price)PK：BID表Borrow(RID,BID,LendDate,ReturnDate)PK：RID,BID

FK：RID和BID表ReaderType(TypeID,Typename,LimitNnm,LimitDays)SQLServer20056.1.3SELECT子句投影查询语法：SELECT[ALL|DISTINCT][TOPinteger|TOPintegerPERCENT][WITHTIES]列名表达式1,列名表达式2,…列名表达式n

其中：表达式中含列名，常量，运算符，列函数下面通过几个例子将参数应用进行说明：投影部分列投影所有列TOP关键字限制返回行数是否去重复元组自定义列名字段函数（列函数）INTO保存查询SQLServer20056.1.3

SELECT子句投影查询投影部分列Exp1:从读者表Reader中中查询出读者的编号、姓名和性别三列的记录。

USELibGOSELECTRID,Rname,RsexFROMReaderSQLServer20056.1.3SELECT子句投影查询投影所有列：(通配符*：所有字段

)

Exp2：从图书管理数据库Library的读者类型表ReaderType中查询所有纪录.

USELibSELECT*FROMReaderTypeSQLServer20056.1.3

SELECT子句投影查询TOP关键字限制返回行数

Exp3:图书管理数据库Library的图书表Book中查询出前5条纪录。

USELibGOSELECTTOP5BID,Bname,AuthorFROMBookGO

SQLServer20056.1.3SELECT子句投影查询是否去重复元组：

All：检出全部信息（默认）Distinct：去掉重复信息

Exp4:从读者表Reader中查询出籍贯RnativeP的名称。

USELibGOSELECTDISTINCTRnativePFROMReaderSQLServer20056.1.3SELECT子句投影查询自定义列名：

‘指定的列标题‘=列名或者

列名AS‘指定的列标题’Exp5：用中文显示ReaderType列名

SELECTTypeIDAS类型编号,TypenameAS类型名称,LimitNumAS限借数量,LimitDaysAS限借天数FROMReaderTypeSQLServer20056.1.3SELECT子句投影查询字段函数（列函数）：

求和：SUM平均：AVG最大：MAX最小：MIN统计：COUNT

Exp6:从图书管理数据库Library中图书表Book中查询出

图书最高价和最低价SELECTMAX(Price)AS最高价,MIN(Price)AS最低价FROMBookSQLServer20056.1.3SELECT子句投影查询INTO保存查询：

into临时表名（根据查询建立临时基本表）

Exp7：从借阅表中将读者的借书证号、所借书号、借出日期

的内容另存在临时表Reader_Borrrow中

SELECTRID,BID,LendDateINTOReader_BorrowFROMBorrowSQLServer20056.1.4WHERE子句选择查询格式：WHERE逻辑表达式功能：实现有条件的查询运算下面通过几个例子将不同运算符应用进行说明：比较运算符逻辑运算符范围运算符模式匹配运算符列表运算符空值判断符SQLServer20056.1.4WHERE子句选择查询比较运算符(

＝，<>,>,<,>=,<=)例1：选择所有男读者的信息.SELECT*FROMReaderWHERERSex=’男’逻辑运算符(and,or)

例2：从学生表中查询出年龄超过22岁的女生的信息SELECT*FROMReaderWHEREyear(getdate())-year(RBirthday)+1>22andRSex='女'SQLServer20056.1.4WHERE子句选择查询范围运算符

格式：列名[not]between开始值and结束值说明：列名是否在开始值and结束值之间。等效：列名>=开始值and列名<=结束值列名<开始值or列名>结束值（选not）例3：从图书表中查询出定价在10元到15元之间的图书信息SELECTBIDAS图书编号,BnameAS书名,PriceAS定价FROMBookWHEREPricebetween10and15SQLServer20056.1.4WHERE子句选择查询模式匹配运算符语法：[NOT]LIKE

通配符说明：通配符_：一个任意字符；通配符％：任意个任意字符例4：查询出姓“王”的所有读者的信息SELECT*FROMReaderWHERERnameLIKE'王%'

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/581fb289-29f9-412b-869c-18d33a9e93d5.htmSQLServer20056.1.4WHERE子句选择查询列表运算符语法：表达式〔NOT〕IN(列表|子查询)说明：表达式的值在（不在）列表所列出的值中

子查询在6.3中介绍例5：查询借书证号为001和002的学生的信息

SELECT*FROMReaderWHERERIDin(‘001’,’002’)

SQLServer20056.1.4WHERE子句选择查询空值判断符语法：IS[NOT]NULL例6：从借阅表中查询出没有还的图书编号.SELECTBIDFROMBorrowWHEREReturnDateISNULLSQLServer20056.1.5GROUPBY子句分组统计查询格式：GROUPBY

列名功能：与列名或列函数配合实现分组统计说明：投影列名必须出现相应的GROUPBY列名

例7：从图书表中查询各出版社图书的总价。

SELECTPubComp,'总价'=SUM(price)FROMBookGROUPBYPubCompSQLServer20056.1.6HAVING子句限定查询格式：HAVING逻辑表达式功能：与GROUPBY选项配合筛选（选择）统计结果说明：常用列函数作为条件，列函数不能放在WHERE中

例8：从借阅表中查询借书超过10本的读者的编号SELECTRID,‘借书本数'=Count(BID)FROMBorrowGROUPBYRIDHAVINGCount(BID)>10

SQLServer20056.1.7ORDERBY排序查询格式：ORDERBY列名表达式表asc/desc功能：排序例子：查询每个读者借书的本数并按借书的多少进行排序

SELECTRID,COUNT(BID)AS借书本数FROMBorrowGROUPBYRIDORDERBYCOUNT(BID)SQLServer20056.2连接查询连接方法和种类1内连接2341外连接5自连接6SQLServer20056.2.1连接方法和种类SQLServer提供了不同的语法格式支持不同的连接方式用于FROM子句的连接语法形式SELECT列名列表FROM｛表名1[连接类型]JOIN表名2ON连接条件｝WHERE逻辑表达式用于WHERE子句的SQLServer连接语法形式SELECT列名列表FROM表名列表WHERE{表名.列名JOIN_OPERATOR表名.列名}[…n]ON逻辑表达式连接种类

内连接，外连接，交叉连接SQLServer20056.2.2

内连接格式：from表名1innerjoin

表名2on

连接表达式（1）等值连接

在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。例1：查询每个读者的详细信息包括读者信息以及借阅图书信息，允许有重复列。SELECTReader.*,Borrow.*FROMReaderINNERJOINBorrowONReader.RID=Borrow.RID

SQLServer20056.2.2内连接（2）自然连接例2：查询每个读者的详细信息包括读者信息以及借阅图书信息，不允许有重复列。SELECT

Reader.RID,

Reader.Rname,

Book.BID,

Book.Bname,

LendDate,

ReturnDateFROMBorrowINNERJOINReaderONReader.RID=Borrow.RIDINNERJOINBookONBorrow.BID=Book.BIDSQLServer20056.2.3外连接（1）左外连接格式：from表名1leftouterjoin

表名2on

连接表达式功能：加入表1没形成连接的元组，表2列为NULL。SELECTReader.*,RID,BIDFROMReaderLEFTOUTERJOINBorrowONReader.RID=Borrow.RIDSQLServer20056.2.3外连接（2）右外连接格式：from表名1rightouterjoin

表名2on

连接表达式功能：加入表2没形成连接的元组，表1列为NULL。

SELECTReader.*,

RID,

BIDFROMReader

RIGHTOUTERJOINBorrowONReader.RID=Borrow.RIDSQLServer20056.2.3外连接（3）全外连接格式：from表名1fullouterjoin

表名2on

连接表达式功能：加入表1没形成连接的元组，表2列为NULL，加入表2没形成连接的元组，表1列为NULL。SELECTReader.*,

Borrow.RID,

BIDFROMBorrow

FULLOUTERJOINReaderONReader.RID=Borrow.RIDSQLServer20056.2.4自连接格式：from表名1ajoin表名1bon

连接表达式

SELECTa.BID,

a.Bname,

a.AuthorFROMbookaJOINbookbona.Bname=b.BnameWHEREa.BID<>b.BIDSQLServer20056.2.5交叉连接格式：from表名1crossjoin

表名2on

连接表达式

说明：两个表做笛卡尔积SELECTReader.*,

Borrow.RID,BIDFROMReaderCROSSJOINBorrowSQLServer20056.3

子查询[NOT]IN子查询1比较子查询2[NOT]EXISTS子查询3在其他语句中使用4SQLServer20056.3.1[NOT]IN子查询列名[not]in(常量表)|(子查询)说明：列值被包含或不（not）被包含在集合中等价：列名=any(子查询)例子：查询没有借过书的读者的信息SELECT*FROMReaderWHERERIDNOTIN

(SELECTDISTINCTRIDFROMBorrow)SQLServer20056.3.2比较子查询（1）列名

比较符all

（子查询）说明：子查询中的每个值都满足比较条件例子：查询读者编号RID最大的读者的借书情况SELECT*FROMBorrowWHERERID>=ALL

(SELECTRIDFROMReader)SQLServer20056.3.2比较子查询（2）列名比较符any|some

（子查询）

说明：子查询中的任一个值满足比较条件例子：查询选修C++语言课程的学生

SELECT姓名,成绩FROM学生ASxINNERJOIN选修ASyONx.学号=y.学号WHERE课程号=any(select课程号from课程

where课程名='C++语言');

SQLServer20056.3.3[NOT]EXISTS子查询功能：用集合运算实现元组与（子查询）说明：子查询中空或非空例子：查询借阅了清华大学的图书的读者的编号

SELECTDISTINCTRIDFROMBorrowWHEREEXISTS(SELECT*FROMbooksWHEREBorrow.BID=books.BIDANDPubComp=’清华大学’)SQLServer20056.3.4在其他语句中使用子查询例子：计算读者中的已借数量的值UPDATEReaderSETLendnum=(SELECTCOUNT(*)FROMdbo.BorrowWHEREReturnDateISNULLandReader.RID=Borrow.RID)SQLServer20056.4联合查询UNION操作符1联合查询结果排序2对单张表使用UNION操作符3交操作和叉操作41UNION操作符和JOIN操作符区别与联系5连接查询和子查询的比较6SQLServer20056.4.1UNION操作符格式：SELECT_1UNION[ALL]SELECT_2{UNION[ALL]SELECT_n例1：查询出“清华大学”出版的图书的编号和被借阅过的图书的编号，不包括重复的列SELECTBIDFROMbooksWHEREPubComp=’清华大学’

UNIONSELECTBIDFROMBorrowSQLServer20056.4.1UNION操作符例2：查询出“清华大学”出版的图书的编号和被借阅过的图书的编号，可以包括重复的列

SELECTBIDFROMbooksWHEREPubComp=’清华大学’

UNIONALLSELECTBIDFROMBorrowSQLServer20056.4.2联合查询结果排序例3：查询出“清华大学”出版的图书和被借阅过的图书的编号，不包括重复的列并将查询结果进行排序SELECTBIDFROMbooksWHEREPubComp=’人民出版社’

UNIONSELECTBIDFROMBorrow

ORDERBYSQLServer20056.4.3对单张表使用UNION操作符例4：查询“清华大学”和“微软”出版的图书的书名和作者

SELECTBname,AuthorFROMbooksWHEREPubComp=’清华大学’

UNIONSELECTBname,AuthorFROMbooksWHEREPubComp=’微软’SQLServer20056.4.4交操作和差操作例1：查询被借阅过的“清华大学”出版的图书的图书编号SELECTBIDFROMbooksWHEREPubComp=’清华大学’

ANDEXISTS(SELECT*FROMBorrowWHEREBorrow.BID=books.BID)SQLServer20056