《数据库系统原理与应用（第二版）》课件第3章

第三章关系数据库标准语言SQL关系数据库标准语言SQL提纲SQL概述数据定义数据查询数据更新视图数据控制3关系数据库标准语言SQL3.1SQL概述

SQL语言也叫结构化查询语言（StructuredQueryLanguage），是一种介于关系代数与关系演算之间的语言。 其功能包括：数据定义、数据查询、数据操作和数据控制四个方面，是一个通用的、功能很强的关系数据库语言。3关系数据库标准语言SQL3.2数据定义

SQL数据定义功能包括定义基本表、定义视图和定义索引等3操作对象操作方式创建删除修改基本表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW索引CREATEINDEXDROPINDEX关系数据库标准语言SQL3.2数据定义（续） 基本表定义CREATETABLE〈表名〉(列名1数据类型[列级完整性约束条件]，列名2数据类型[列级完整性约束条件]，……列名N数据类型[列级完整性约束条件][表级完整性约束条件])3关系数据库标准语言SQL3.2数据定义（续）

关于完整性定义

(1)实体完整性定义语法

[CONSTRAINT约束名]PRIMARYKEY[(属性列表)](2)参照完整性定义语法：

[CONSTRAINT约束名]FOREIGNKEY(列名)REFERENCES<被参照表表名>(被参照表列名)(3)自定义完整性定义语法：列值非空：[CONSTRAINT约束名]NOTNULL列值惟一：[CONSTRAINT约束名]UNIQUE[(属性列表)]逻辑表达式：[CONSTRAINT约束名]CHECK(表达式)

3关系数据库标准语言SQL3.2数据定义（续）

SQLServer2005常用数据类型Int或smallint：整型。Bit：整型，只能存储0或1。通常用于存储逻辑型数据。Float：浮点型。Real：实型。Text或Ntext：文本。Ntext采用的是Unicode编码，text采用的是非Unicode编码。Image：图形和图像。Binary：二进制数。Varbinary：可变长二进制数。Char或Nchar：字符型。Nchar采用的是Unicode编码，char采用的是非Unicode编码。Numeric或decimal：固定精度数值型。Varchar或Nvarchar：可变长字符型。Nvarchar采用的是Unicode编码，varchar采用的是非Unicode编码。Datetime或smalldatetime：日期时间。3关系数据库标准语言SQL3.2数据定义（续） 基本表定义（续）例3-1在Oracle中用SQL命令创建图书信息表、读者信息表和借阅表。（1）创建图书信息表。

CREATETABLEBOOK(BOOKIDCHAR(20)PRIMARYKEY,BOOKNAMEVARCHAR(60)NOTNULL,EDITORCHAR(8),PRICENUMERIC(5,2),PUBLISHCHAR(30),PUBDATEDATETIME,QTYINT)3关系数据库标准语言SQL3.2数据定义（续） 基本表定义（续）（2）创建读者信息。CREATETABLEREADER(CARDIDCHAR(10)PRIMARYKEY,NAMECHAR(8),SEXCHAR(2),DEPTCHAR(20),CLASSINT)3关系数据库标准语言SQL3.2数据定义（续） 基本表定义（续）（3）创建借阅表。CREATETABLEBORROW(BOOKIDCHAR(20),CARDIDCHAR(10),BDATEDATETIMENOTNULL,SDATEDATETIME,PRIMARYKEY(BOOKID,CARDID,BDATE),CONSTRAINTFK_BOOKIDFOREIGNKEY(BOOKID)REFERENCESBOOK(BOOKID),CONSTRAINTFK_CARDIDFOREIGNKEY(CARDID)REFERENCESREADER(CARDID))3关系数据库标准语言SQL3.2数据定义（续） 修改基本表SQL语言用ALTERTABLE语句修改基本表，其一般格式为：ALTERTABLE<表名>ALTERCOLUMN<列名><新的类型>[NULL|NOTNULL]ADD<新列名><数据类型>[完整性约束]ADD<表级完整定义>DROPCONSTRAINT<完整性约束名>DROPCOLUMN<列名>3关系数据库标准语言SQL3.2数据定义（续） 修改基本表（续）例3-2在图书信息表中增加一列出版时期（PUBDATE），并将BOOKID列宽改为15。

ALTERTABLEBOOKADDPUBDATEDATETIMEGOALTERTABLEBOOKALTERCOLUMNBOOKIDCHAR(15)例3-3删除借阅表中的参照完整性。ALTERTABLEborrowDROPCONSTRAINTFK_bookidDROPCONSTRAINTFK_cardid例3-4如果例3-1中没为借阅表创建参照完整性，或者已按例3-3将参照完整性删除，则可按下面方法创建参照完整性。ALTERTABLEborrowADDCONSTRAINTFK_bookidFOREIGNKEY(bookid)REFERENCESbook(bookid)ADDCONSTRAINTFK_cardidFOREIGNKEY(cardid)REFERENCESreader(cardid)3关系数据库标准语言SQL3.2数据定义（续）删除基本表

DROPTABLE<表名>3关系数据库标准语言SQL3.2数据定义（续）建立索引

CREATE[UNIQUE]INDEX<索引名>ON<表名>(<列名>[<ASC|DESC>][,<列名>[<ASC|DESC>]]…)；>3关系数据库标准语言SQL3.2数据定义（续）删除索引

DROPINDEX<表名>.<索引名>3关系数据库标准语言SQL3.3数据查询一般格式

SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>…]FROM<表名或视图名[别名]>[,<表名或视图名>[别名]]…[WHERE<条件表达式>][GROUPBY<分组表达式>[HAVING<条件表达式>]][ORDERBY<排序列名>[ASC|DESC]]；3关系数据库标准语言SQL3.3数据查询（续）单表查询（续）（1）选择表中的若干列例3-6

查询所有读者的卡号和姓名。SELECTcardid,nameFROMreader例3-7查询所有图书信息。SELECT*FROMbook例3-8使用别名，查询所有读者的卡号和姓名。SELECTcardid卡号,name姓名FROMreader3关系数据库标准语言SQL3.3数据查询（续）单表查询（续）（2）选择表中满足条件的记录3查询条件运算符比较=，>，>=，<，<=，!=，<>确定范围BETWEEN…..AND…...NOTBETWEEN….AND…集合运算IN，NOTIN字符匹配LIKE，NOTLIKE空值判断ISNULL，ISNOTNULL逻辑运算AND，OR，NOT关系数据库标准语言SQL3.3数据查询（续）单表查询（续）（2）选择表中满足条件的记录（续）例3-9查询价格在20元以上的所有图书信息。SELECT*FROMbookWHEREprice>=20例3-10查询在2003年后的借书记录。SELECT*FROMborrowWHEREdatepart(yyyy,bdate)>='2003‘例3-11查询价格在20～30元之间的所有图书信息。SELECT*FROMbookWHEREpriceBETWEEN20AND30例3-12查询电子工业出版社、清华大学出版社和高等教育出版社出版的所有图书的书名。SELECTbooknameFROMbookWHEREpublishIN('电子工业出版社','清华大学出版社','高等教育出版社')例3-13

查询以“数据库”开头的所有图书的书名和出版社。SELECTbookname,publishFROMbookWHEREbooknameLIKE‘数据库%’3关系数据库标准语言SQL3.3数据查询（续）单表查询（续）（2）选择表中满足条件的记录（续）例3-14

查询书名含有DELPHI_6的所有图书信息。SELECT*FROMbookWHEREbooknameLIKE‘%DELPHI\_6%’ESCAPE‘\’例3-15读者借书后还未还书时，借阅表中的还书日期为空值。查询所有未还书籍的读者号和借书时间。SELECTcardid,bdateFROMborrowWHEREsdateISNULL例3-16查询单位为“计算机系”且类别为学生（class=1）的所有读者信息SELECT*FROMreaderWHEREdept=’计算机系’andclass=13关系数据库标准语言SQL3.3数据查询（续）单表查询（续）（3）对查询结果排序例3-17查询2003年后出版的所有图书并按出版先后顺序排序。SELECT*FROMbookWHERE

PUBDATE>="2003-01-01“ORDERBYpubdate3关系数据库标准语言SQL3.3数据查询（续）单表查询（续）（4）使用集函数SQL提供了许多集函数，主要包括：COUNT([DISTINCT|ALL]*)统计元组个数。COUNT([DISTINCT|ALL]<列名>)统计一列中值的个数。SUM([DISTINCT|ALL]<列名>)计算一列值的总和（此列必须是数值型）。AVG（[DISTINCT|ALL]<列名>）计算一列值的平均值（此列必须是数值型）。MAX（[DISTINCT|ALL]<列名>）计算一列值的最大值。MIN（[DISTINCT|ALL]<列名>）计算一列值的最小值。3关系数据库标准语言SQL3.3数据查询（续）单表查询（续）（4）使用集函数（续）例3-18查询读者总数。SELECTCOUNT(*)FROMreader例3-19查询有未还书的读者数。SELECTCOUNT(DISTINCTcardid)FROMborrowWHEREsdateISNULL例3-20查询库存书总数。SELECTSUM(qty)FROMbook3关系数据库标准语言SQL3.3数据查询（续）单表查询（续）（5）分组统计例3-21统计不同类型的读者数。SELECTclass,COUNT(cardid)FROMreaderGROUPBYclass例3-22按出版年份统计库存量。SELECTDATEPART(YY,PUBDATE),SUM(QTY)FROMbookGROUPDATEPART(YY,PUBDATE)例3-23查询借书数量大于10本的读者卡号。SELECTcardidFROMborrowWHEREsdateISNULLGROUPBYcardidHAVINGCOUNT(bookid)>103关系数据库标准语言SQL3.3数据查询（续）多表查询（1）等值与非等值连接查询例3-24查询所有借书未还的读者的姓名。SELECTnameFROMreader,borrowWHEREreader.cardid=borrow.cardidANDsdateISNULL例3-25查询所有读者信息及借阅情况。SELECTreader.*,borrow.*FROMreader,borrowWHEREreader.cardid=borrow.cardid3关系数据库标准语言SQL3.3数据查询（续）多表查询（续）（2）自然连接例3-26自然连接READER和BORROW表。SELECTreader.cardid,name,sex,dept,class,bookid,bdate,sdateFROMreader,borrowWHEREreader.cardid=borrow.cardid3关系数据库标准语言SQL3.3数据查询（续）多表查询（续）（3）自身连接例3-27查询书名相同而出版社不同的所有图书的书名。SELECTDISTINCTB1.booknameFROMbookB1,bookB2WHEREB1.bookname=B2.booknameANDB1.publish<>B2.publish3关系数据库标准语言SQL3.3数据查询（续）多表查询（续）（4）外连接左外连：查询结果中不仅包含符合连接条件的行，而且包含左表中所有数据行。右外连：查询结果中不仅包含符合连接条件的行，而且包含右表中所有数据行。全外连：查询结果中不仅包含符合连接条件的行，而且包含两个连接表中所有数据行。3关系数据库标准语言SQL3.3数据查询（续）多表查询（续）（4）外连接（续）例3-28在例3-26中要求将没有借书记录的读者也显示出来。SELECTreader.cardid,name,sex,dept,class,bookid,bdate,sdateFROMreader,borrowWHEREreader.cardid=*borrow.cardid说明：*在右边，保证了左边表中的所有记录都会被取到。例3-28也可以改写成：SELECTreader.cardid,name,sex,dept,class,bookid,bdate,sdateFROMreaderLEFTOUTERJOINborrowONreader.cardid=borrow.cardid3关系数据库标准语言SQL3.3数据查询（续）多表查询（续）（5）综合事例例3-29查询借书期限超过2个月的所有读者的姓名、所借书籍名和借书日期。SELECTNAME,BOOKNAME,BDATEFROMBOOK,READER,BORROWWHEREBOOK.BOOKID=BORROW.BOOKIDANDREADER.CARDID=BORROW.CARDIDANDDATEDIFF(MM,BDATE,GETDATE())>2ANDSDATEISNULL3关系数据库标准语言SQL3.3数据查询（续）多表查询（续）（5）综合事例（续）例3-30按读者姓名查询指定读者的借还书历史记录。假设读者姓名为“刘勇”。SELECT,bdate,sdateFROMborrow,book,readerWHEREbook.bookid=borrow.bookidANDreader.cardid=borrow.cardidAND='刘勇'例3-31查询指定图书的去向。如指定书名为“数据结构”。SELECT,deptFROMborrow,reader,bookWHEREborrow.cardid=reader.cardidANDborrow.bookid=book.bookidANDbook.bookname='数据结构'ANDsdateISNULL3关系数据库标准语言SQL3.3数据查询（续）嵌套查询（1）带IN谓词的子查询例3-32查询借了“数据库系统”书籍的所有读者的姓名。SELECTnameFROMreaderWHEREcardidIN(SELECTcardidFROMborrowWHEREbookidIN(SELECTbookidFROMbookWHEREbookname='数据库系统'））3关系数据库标准语言SQL3.3数据查询（续）嵌套查询（续）（2）带有比较运算符的子查询例3-33查询与“刘勇”在同一个部门的所有读者的信息。SELECT*FROMreaderWHEREdept=(SELECTdeptFROMreaderWHEREname='刘勇')3关系数据库标准语言SQL3.3数据查询（续）嵌套查询（续）（3）带有ANY或ALL谓词的子查询语义为：>ANY：大于子查询结果中的某个值。<ANY：小于子查询结果中的某个值。>=ANY：大于等于子查询结果中的某个值。<=ANY：小于等于子查询结果中的某个值。=ANY：等于子查询结果中的某个值。!=ANY或<>ANY：不等于子查询结果中的某个值。>ALL：大于子查询结果中的所有值。<ALL：小于子查询结果中的所有值。>=ALL：大于等于子查询结果中的所有值。<=ALL：小于等于子查询结果中的所有值。!=ALL或<>ANY：不等于子查询结果中的任何一个值。3关系数据库标准语言SQL3.3数据查询（续）嵌套查询（续）（3）带有ANY或ALL谓词的子查询（续）例3-34查询所有正借阅“中国水利水电出版社”出版的书籍的读者姓名。SELECTnameFROMreaderWHEREcardid=ANY(SELECTcardidFROMborrowWHEREsdateISNULLANDbookid=ANY(SELECTbookidFROMbookWHEREpublish='中国水利水电出版社'))3关系数据库标准语言SQL3.3数据查询（续）嵌套查询（续）（4）带有EXISTS谓词的子查询例3-35查询借阅了书号为TP2003--002图书的所有读者姓名。SELECTnameFROMreaderWHEREEXISTS(SELECT*FROMborrowWHEREborrow.cardid=reader.cardidANDbookid='TP2003--002')3关系数据库标准语言SQL3.4数据更新插入数据（1）插入单个元组插入单个元组的INSERT语句的语法格式为：INSERTINTO<表名>[(<属性列1>[,<属性列2>…])]VALUES(<常量1>[,<常量2>…])例3-37在读者表中插入一条新的记录（'T0031','刘伟','男','计算机系',1）。INSERTINTOreaderVALUES('T0031','刘伟','男','计算机系',1)3关系数据库标准语言SQL3.4数据更新（续）插入数据（续）（2）插入子查询结果插入子查询结果的INSERT语句的格式为：INSERTINTO<表名>[(<属性列1>[,<属性列2>…])]子查询；例3-38按书号统计每种图书的借出数量并保存到另一个表中。第一步先创建表：CREATETABLEBOOKQTY(bookidCHAR(20),qtyINT)第二步插入数据：INSERTINTOBOOKQTYSELECTbookid,COUNT(*)FROMborrowWHEREsdateISNULLGROUPBYbookid3关系数据库标准语言SQL3.4数据更新（续）修改数据修改操作又称为更新操作，其语句格式为：UPDATE<表名>SET<列名>=<表达式>[，<列名>=<表达式>]…[WHERE<条件>]；3关系数据库标准语言SQL3.4数据更新（续）修改数据（续）例3-39读者还书操作。设读者卡号为S0101，书号为TP2003--002。UPDATEborrowSETsdate=SYSDATEWHEREbookid=‘TP2003--002’ANDcardid=‘S0101’；UPDATEbookSETqty=qty+1WHEREbookid='TP2003--002'例3-40读者借书操作。设读者卡号为T0001，书号为TP2004--005。UPDATEbookSETqty=qty-1WHEREbookid='TP2004--005'INSERTINTOborrow(bookid,cardid,bdate)VALUES('TP2004--005','T0001',SYSDATE)3关系数据库标准语言SQL3.4数据更新（续）删除数据删除数据指删除表中的某些记录，删除语句的一般格式为：DELETEFROM<表名>[WHERE<条件>]；3关系数据库标准语言SQL3.4数据更新（续）删除数据（续）例3-41删除卡号为T0035的读者的所有借书记录，然后删除该读者信息。DELETEFROMborrowWHEREcardid=‘T0035’；DELETEFROMreaderWHEREcardid='T0035'例3-42清空借阅表。DELETEFROMborrow例3-43带子查询的删除操作。删除没有借书记录的所有读者。DELETEFROMreaderWHERENOTEXISTS(SELECT*FROMborrowWHEREborrow.cardid=reader.cardid)3关系数据库标准语言SQL3.5视图 视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。 视图是从一个或几个基本表（或视图）导出的表，它与基本表不同，是一个虚表。3关系数据库标准语言SQL3.5视图（续）建立视图SQL语言用CREATEVIEW命令建立视图，其一般格式为：CREATEVIEW<视图名>[(<列名1>[，<列名2>]…)]AS<子查询>[WITHCHECKOPTION]；例3-44建立读者类别为学生（class=1）的读者视图。CREATEVIEWS_readerASSELECT*FROMreaderWHEREclass=13关系数据库标准语言SQL3.5视图（续）建立视图（续）例3-45创建教师读者（class=2）视图，并要求进行修改和插入操作时仍保证视图只有教师记录。CREATEVIEWT_readerASSELECTcardid,name,sex,deptFROMreaderWHEREclass=2WITHCHECKOPTION例3-46创建教师借阅视图。CREATEVIEWT_borrowASSELECTcardid,bookid,bdate,sdateFROMborrowWHEREcardidIN(SELECTcardidFROMreaderWHEREclass=2)3关系数据库标准语言SQL3.5视图（续）建立视图（续）例3-47创建尚有未归还图书的读者借阅视图，包括读者姓名、所借书名和借书时间。CREATEVIEWV_borrowASSELECTname,bookname,bdateFROMborrow,reader,bookWHEREborrow.bookid=book.bookidANDborrow.cardid=reader.cardidANDsdateISNULL例3-48

统计每种图书的藏书数量（=在库数量+借出数量）。先计算每种图书的借出数量：CREATEVIEWB_qty(bookid,NUM)ASSELECTbookid,COUNT(*)FROMborrowWHEREsdateISNULLGROUPBYbookid再计算每种图书的总数量（藏书数量）：CREATEVIEWbook_qty(bookid,SUMQTY)ASSELECTbook.bookid,qty+NUMFROMbook,B_qtyWHEREbook.bookid=B_qty.bookid3关系数据库标准语言SQL3.5视图（续）删除视图DROPVIEW<视图名>；3关系数据库标准语言SQL3.5视