1、第三章 关系数据库标准语言SQL关系数据库标准语言关系数据库标准语言SQL 提纲 SQL概述 数据定义 数据查询 数据更新 视图 数据控制3关系数据库标准语言关系数据库标准语言SQL 3.1 SQL概述SQL语言也叫结构化查询语言（Structured Query Language），是一种介于关系代数与关系演算之间的语言。其功能包括：数据定义、数据查询、数据操作和数据控制四个方面，是一个通用的、功能很强的关系数据库语言。3关系数据库标准语言关系数据库标准语言SQL 3.2 数据定义SQL数据定义功能包括定义基本表、定义视图和定义索引等3操作对象操作对象操作方式操作方式创建创建删除删除修改修改
2、基本表基本表CREATE TABLEDROP TABLEALTER TABLE视图视图CREATE VIEWDROP VIEW索引索引CREATE INDEXDROP INDEX关系数据库标准语言关系数据库标准语言SQL 3.2 数据定义（续）基本表定义CREATE TABLE 表名(列名1 数据类型 列级完整性约束条件，列名2 数据类型 列级完整性约束条件，列名N 数据类型 列级完整性约束条件表级完整性约束条件)3关系数据库标准语言关系数据库标准语言SQL 3.2 数据定义（续）关于完整性定义 (1)实体完整性定义语法 CONSTRAINT 约束名 PRIMARY KEY(属性列表)(2)参
3、照完整性定义语法：CONSTRAINT 约束名 FOREIGN KEY(列名)REFERENCES(被参照表列名)(3)自定义完整性定义语法：列值非空：CONSTRAINT 约束名 NOT NULL列值惟一：CONSTRAINT 约束名 UNIQUE(属性列表)逻辑表达式：CONSTRAINT 约束名 CHECK(表达式)3关系数据库标准语言关系数据库标准语言SQL 3.2 数据定义（续）SQL Server 2005常用数据类型 Int或smallint：整型。Bit：整型，只能存储0或1。通常用于存储逻辑型数据。Float：浮点型。Real：实型。Text或Ntext：文本。Ntext采用
4、的是Unicode编码，text采用的是非Unicode编码。Image：图形和图像。Binary：二进制数。Varbinary：可变长二进制数。Char或Nchar：字符型。Nchar采用的是Unicode编码，char采用的是非Unicode编码。Numeric或decimal：固定精度数值型。Varchar或Nvarchar：可变长字符型。Nvarchar采用的是Unicode编码，varchar采用的是非Unicode编码。Datetime或smalldatetime：日期时间。3关系数据库标准语言关系数据库标准语言SQL 3.2 数据定义（续）基本表定义（续）例3-1 在Oracle
5、中用SQL命令创建图书信息表、读者信息表和借阅表。（1）创建图书信息表。CREATE TABLE BOOK(BOOKID CHAR(20)PRIMARY KEY,BOOKNAME VARCHAR(60)NOT NULL,EDITOR CHAR(8),PRICE NUMERIC(5,2),PUBLISH CHAR(30),PUBDATE DATETIME,QTY INT)3关系数据库标准语言关系数据库标准语言SQL 3.2 数据定义（续）基本表定义（续）（2）创建读者信息。CREATE TABLE READER(CARDID CHAR(10)PRIMARY KEY,NAME CHAR(8),SE
6、X CHAR(2),DEPT CHAR(20),CLASS INT)3关系数据库标准语言关系数据库标准语言SQL 3.2 数据定义（续）基本表定义（续）（3）创建借阅表。CREATE TABLE BORROW(BOOKID CHAR(20),CARDID CHAR(10),BDATE DATETIME NOT NULL,SDATE DATETIME,PRIMARY KEY(BOOKID,CARDID,BDATE),CONSTRAINT FK_BOOKID FOREIGN KEY(BOOKID)REFERENCES BOOK(BOOKID),CONSTRAINT FK_CARDID FOREIG
7、N KEY(CARDID)REFERENCES READER(CARDID)3关系数据库标准语言关系数据库标准语言SQL 3.2 数据定义（续）修改基本表SQL语言用ALTER TABLE语句修改基本表，其一般格式为：ALTER TABLE ALTER COLUMN NULL|NOT NULLADD 完整性约束 ADD DROP CONSTRAINT DROP COLUMN 3关系数据库标准语言关系数据库标准语言SQL 3.2 数据定义（续）修改基本表（续）例3-2在图书信息表中增加一列出版时期（PUBDATE），并将BOOKID列宽改为15。ALTER TABLE BOOK ADD PUBD
8、ATE DATETIME GO ALTER TABLE BOOK ALTER COLUMN BOOKID CHAR(15)例3-3 删除借阅表中的参照完整性。ALTER TABLE borrow DROP CONSTRAINT FK_bookid DROP CONSTRAINT FK_cardid 例3-4 如果例3-1中没为借阅表创建参照完整性，或者已按例3-3将参照完整性删除，则可按下面方法创建参照完整性。ALTER TABLE borrow ADD CONSTRAINT FK_bookid FOREIGN KEY(bookid)REFERENCES book(bookid)ADD CON
9、STRAINT FK_cardid FOREIGN KEY(cardid)REFERENCES reader(cardid)3关系数据库标准语言关系数据库标准语言SQL 3.2 数据定义（续）删除基本表DROP TABLE 3关系数据库标准语言关系数据库标准语言SQL 3.2 数据定义（续）建立索引CREATE UNIQUE INDEX ON (,)；3关系数据库标准语言关系数据库标准语言SQL 3.2 数据定义（续）删除索引DROP INDEX.3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询 一般格式SELECT ALL|DISTINCT ,FROM ,别名 WHERE GR
10、OUP BY HAVING ORDER BY ASC|DESC ；3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）单表查询（续）（1）选择表中的若干列例3-6 查询所有读者的卡号和姓名。SELECT cardid,name FROM reader例3-7 查询所有图书信息。SELECT *FROM book例3-8 使用别名，查询所有读者的卡号和姓名。SELECT cardid卡号,name姓名 FROM reader3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）单表查询（续）（2）选择表中满足条件的记录 3查询条件查询条件运算符运算符比较比较=，=，
11、=，!=，确定范围确定范围BETWEEN.AND.NOT BETWEEN.AND集合运算集合运算IN，NOT IN字符匹配字符匹配LIKE，NOT LIKE空值判断空值判断IS NULL，IS NOT NULL逻辑运算逻辑运算AND，OR，NOT关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）单表查询（续）（2）选择表中满足条件的记录（续）例3-9 查询价格在20元以上的所有图书信息。SELECT*FROM book WHERE price=20例3-10 查询在2003年后的借书记录。SELECT*FROM borrow WHERE datepart(yyyy,bdate)
12、=2003例3-11 查询价格在2030元之间的所有图书信息。SELECT*FROM book WHERE price BETWEEN 20 AND 30例3-12 查询电子工业出版社、清华大学出版社和高等教育出版社出版的所有图书的书名。SELECT bookname FROM bookWHERE publish IN(电子工业出版社,清华大学出版社,高等教育出版社)例3-13 查询以“数据库”开头的所有图书的书名和出版社。SELECT bookname,publish FROM bookWHERE bookname LIKE 数据库%3关系数据库标准语言关系数据库标准语言SQL 3.3 数据
13、查询（续）单表查询（续）（2）选择表中满足条件的记录（续）例3-14 查询书名含有DELPHI_6的所有图书信息。SELECT*FROM bookWHERE bookname LIKE%DELPHI_6%ESCAPE 例3-15 读者借书后还未还书时，借阅表中的还书日期为空值。查询所有未还书籍的读者号和借书时间。SELECT cardid,bdateFROM borrow WHERE sdate IS NULL例3-16 查询单位为“计算机系”且类别为学生（class=1）的所有读者信息SELECT*FROM reader WHERE dept=计算机系 and class=13关系数据库标准
14、语言关系数据库标准语言SQL 3.3 数据查询（续）单表查询（续）（3）对查询结果排序 例3-17 查询2003年后出版的所有图书并按出版先后顺序排序。SELECT*FROM bookWHERE PUBDATE=2003-01-01“ORDER BY pubdate3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）单表查询（续）（4）使用集函数 SQL提供了许多集函数，主要包括：COUNT(DISTINCT|ALL *)统计元组个数。COUNT(DISTINCT|ALL )统计一列中值的个数。SUM(DISTINCT|ALL )计算一列值的总和（此列必须是数值型）。AVG（D
15、ISTINCT|ALL ）计算一列值的平均值（此列必须是数值型）。MAX（DISTINCT|ALL ）计算一列值的最大值。MIN（DISTINCT|ALL ）计算一列值的最小值。3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）单表查询（续）（4）使用集函数（续）例3-18 查询读者总数。SELECT COUNT(*)FROM reader例3-19 查询有未还书的读者数。SELECT COUNT(DISTINCT cardid)FROM borrowWHERE sdate IS NULL例3-20 查询库存书总数。SELECT SUM(qty)FROM book3关系数据库
16、标准语言关系数据库标准语言SQL 3.3 数据查询（续）单表查询（续）（5）分组统计 例3-21 统计不同类型的读者数。SELECT class,COUNT(cardid)FROM readerGROUP BY class例3-22 按出版年份统计库存量。SELECT DATEPART(YY,PUBDATE),SUM(QTY)FROM bookGROUP DATEPART(YY,PUBDATE)例3-23 查询借书数量大于10本的读者卡号。SELECT cardidFROM borrowWHERE sdate IS NULLGROUP BY cardid HAVING COUNT(bookid
17、)103关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）多表查询（1）等值与非等值连接查询 例3-24 查询所有借书未还的读者的姓名。SELECT nameFROM reader,borrowWHERE reader.cardid=borrow.cardid AND sdate IS NULL例3-25 查询所有读者信息及借阅情况。SELECT reader.*,borrow.*FROM reader,borrowWHERE reader.cardid=borrow.cardid3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）多表查询（续）（2）自然连接
18、例3-26 自然连接READER和BORROW表。SELECT reader.cardid,name,sex,dept,class,bookid,bdate,sdateFROM reader,borrowWHERE reader.cardid=borrow.cardid3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）多表查询（续）（3）自身连接 例3-27 查询书名相同而出版社不同的所有图书的书名。SELECT DISTINCT B1.booknameFROM book B1,book B2WHERE B1.bookname=B2.bookname AND B1.publi
19、shB2.publish3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）多表查询（续）（4）外连接 左外连：查询结果中不仅包含符合连接条件的行，而且包含左表中所有数据行。右外连：查询结果中不仅包含符合连接条件的行，而且包含右表中所有数据行。全外连：查询结果中不仅包含符合连接条件的行，而且包含两个连接表中所有数据行。3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）多表查询（续）（4）外连接（续）例3-28 在例3-26中要求将没有借书记录的读者也显示出来。SELECT reader.cardid,name,sex,dept,class,bookid,bda
20、te,sdateFROM reader,borrowWHERE reader.cardid=*borrow.cardid说明：*在右边，保证了左边表中的所有记录都会被取到。例3-28也可以改写成：SELECT reader.cardid,name,sex,dept,class,bookid,bdate,sdateFROM reader LEFT OUTER JOIN borrowON reader.cardid=borrow.cardid3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）多表查询（续）（5）综合事例例3-29 查询借书期限超过2个月的所有读者的姓名、所借书籍名
21、和借书日期。SELECT NAME,BOOKNAME,BDATEFROM BOOK,READER,BORROWWHERE BOOK.BOOKID=BORROW.BOOKID AND READER.CARDID=BORROW.CARDIDAND DATEDIFF(MM,BDATE,GETDATE()2AND SDATE IS NULL3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）多表查询（续）（5）综合事例（续）例3-30 按读者姓名查询指定读者的借还书历史记录。假设读者姓名为“刘勇”。SELECT book.name,bdate,sdateFROM borrow,book
22、,readerWHERE book.bookid=borrow.bookid AND reader.cardid=borrow.cardidAND reader.name=刘勇例3-31 查询指定图书的去向。如指定书名为“数据结构”。SELECT reader.name,deptFROM borrow,reader,bookWHERE borrow.cardid=reader.cardidAND borrow.bookid=book.bookidAND book.bookname=数据结构 AND sdate IS NULL3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）嵌
23、套查询（1）带IN谓词的子查询 例3-32 查询借了“数据库系统”书籍的所有读者的姓名。SELECT nameFROM readerWHERE cardid IN (SELECT cardid FROM borrow WHERE bookid IN (SELECT bookid FROM book WHERE bookname=数据库系统）3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）嵌套查询（续）（2）带有比较运算符的子查询 例3-33 查询与“刘勇”在同一个部门的所有读者的信息。SELECT*FROM readerWHERE dept=(SELECT dept FRO
24、M reader WHERE name=刘勇)3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）嵌套查询（续）（3）带有ANY或ALL谓词的子查询 语义为：ANY：大于子查询结果中的某个值。=ANY：大于等于子查询结果中的某个值。=ANY：小于等于子查询结果中的某个值。=ANY：等于子查询结果中的某个值。!=ANY或ANY：不等于子查询结果中的某个值。ALL：大于子查询结果中的所有值。=ALL：大于等于子查询结果中的所有值。=ALL：小于等于子查询结果中的所有值。!=ALL或ANY：不等于子查询结果中的任何一个值。3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询
25、（续）嵌套查询（续）（3）带有ANY或ALL谓词的子查询（续）例3-34 查询所有正借阅“中国水利水电出版社”出版的书籍的读者姓名。SELECT nameFROM readerWHERE cardid=ANY(SELECT cardid FROM borrow WHERE sdate IS NULL AND bookid=ANY(SELECT bookid FROM book WHERE publish=中国水利水电出版社)3关系数据库标准语言关系数据库标准语言SQL 3.3 数据查询（续）嵌套查询（续）（4）带有EXISTS谓词的子查询 例3-35 查询借阅了书号为TP2003-002图书的
26、所有读者姓名。SELECT nameFROM readerWHERE EXISTS(SELECT*FROM borrow WHERE borrow.cardid=reader.cardid AND bookid=TP2003-002)3关系数据库标准语言关系数据库标准语言SQL 3.4 数据更新 插入数据（1）插入单个元组 插入单个元组的INSERT语句的语法格式为：INSERT INTO(,)VALUES(,)例3-37 在读者表中插入一条新的记录（T0031,刘伟,男,计算机系,1）。INSERT INTO reader VALUES(T0031,刘伟,男,计算机系,1)3关系数据库标准语
27、言关系数据库标准语言SQL 3.4 数据更新（续）插入数据（续）（2）插入子查询结果 插入子查询结果的INSERT语句的格式为：INSERT INTO(,)子查询；例3-38 按书号统计每种图书的借出数量并保存到另一个表中。第一步先创建表：CREATE TABLE BOOKQTY(bookid CHAR(20),qty INT)第二步插入数据：INSERT INTO BOOKQTYSELECT bookid,COUNT(*)FROM borrowWHERE sdate IS NULLGROUP BY bookid3关系数据库标准语言关系数据库标准语言SQL 3.4 数据更新（续）修改数据修改操
28、作又称为更新操作，其语句格式为：UPDATE SET=，=WHERE；3关系数据库标准语言关系数据库标准语言SQL 3.4 数据更新（续）修改数据（续）例3-39 读者还书操作。设读者卡号为S0101，书号为TP2003-002。UPDATE borrowSET sdate=SYSDATEWHERE bookid=TP2003-002 AND cardid=S0101；UPDATE bookSET qty=qty+1WHERE bookid=TP2003-002例3-40 读者借书操作。设读者卡号为T0001，书号为TP2004-005。UPDATE bookSET qty=qty-1WHER
29、E bookid=TP2004-005INSERT INTO borrow(bookid,cardid,bdate)VALUES(TP2004-005,T0001,SYSDATE)3关系数据库标准语言关系数据库标准语言SQL 3.4 数据更新（续）删除数据删除数据指删除表中的某些记录，删除语句的一般格式为：DELETEFROMWHERE；3关系数据库标准语言关系数据库标准语言SQL 3.4 数据更新（续）删除数据（续）例3-41 删除卡号为T0035的读者的所有借书记录，然后删除该读者信息。DELETE FROM borrowWHERE cardid=T0035；DELETE FROM rea
30、derWHERE cardid=T0035例3-42 清空借阅表。DELETE FROM borrow例3-43 带子查询的删除操作。删除没有借书记录的所有读者。DELETE FROM readerWHERE NOT EXISTS(SELECT*FROM borrowWHERE borrow.cardid=reader.cardid)3关系数据库标准语言关系数据库标准语言SQL 3.5 视图视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。视图是从一个或几个基本表（或视图）导出的表，它与基本表不同，是一个虚表。3关系数据库标准语言关系数据库标准语言SQL 3.5 视图（续）建
31、立视图SQL语言用CREATE VIEW命令建立视图，其一般格式为：CREATE VIEW(，)ASWITH CHECK OPTION；例3-44 建立读者类别为学生（class=1）的读者视图。CREATE VIEW S_readerASSELECT*FROM readerWHERE class=13关系数据库标准语言关系数据库标准语言SQL 3.5 视图（续）建立视图（续）例3-45 创建教师读者（class=2）视图，并要求进行修改和插入操作时仍保证视图只有教师记录。CREATE VIEW T_readerASSELECT cardid,name,sex,deptFROM readerW
32、HERE class=2WITH CHECK OPTION例3-46 创建教师借阅视图。CREATE VIEW T_borrowASSELECT cardid,bookid,bdate,sdateFROM borrowWHERE cardid IN(SELECT cardidFROM readerWHERE class=2)3关系数据库标准语言关系数据库标准语言SQL3.5 视图（续）建立视图（续）例3-47 创建尚有未归还图书的读者借阅视图，包括读者姓名、所借书名和借书时间。CREATE VIEW V_borrowASSELECT name,bookname,bdateFROM borrow
33、,reader,bookWHERE borrow.bookid=book.bookidAND borrow.cardid=reader.cardidAND sdate IS NULL例3-48 统计每种图书的藏书数量（=在库数量+借出数量）。先计算每种图书的借出数量：CREATE VIEW B_qty(bookid,NUM)ASSELECT bookid,COUNT(*)FROM borrowWHERE sdate IS NULLGROUP BY bookid再计算每种图书的总数量（藏书数量）：CREATE VIEW book_qty(bookid,SUMQTY)ASSELECT book.b
34、ookid,qty+NUMFROM book,B_qtyWHERE book.bookid=B_qty.bookid3关系数据库标准语言关系数据库标准语言SQL 3.5 视图（续）删除视图DROP VIEW；3关系数据库标准语言关系数据库标准语言SQL 3.5 视图（续）查询视图例3-50 查询读者“刘伟”的借书信息。SELECT name,bookname,bdateFROM V_borrowWHERE name=刘伟例3-51 统计图书馆藏书总量。SELECT SUM(SUMQTY)FROM book_qty 3关系数据库标准语言关系数据库标准语言SQL 3.5 视图（续）更新视图例3-5
35、2 通过视图T_READER修改读者（T0078）为“计算机系”。UPDATE T_readerSET dept=计算机系WHERE cardid=T00783关系数据库标准语言关系数据库标准语言SQL 3.5 视图（续）视图的用途（1）视图能够简化用户的操作。（2）视图使用户能以多种角度看待同一数据。（3）视图对重构数据库提供了一定程度的逻辑独立性。（4）视图能够对机密数据提供安全保护。3关系数据库标准语言关系数据库标准语言SQL 3.6 数据控制 数据控制也称为数据保护，包括数据的安全性控制、完整性控制、并发控制和恢复。3关系数据库标准语言关系数据库标准语言SQL 3.6 数据控制（续）授
36、权SQL语言用GRANT 语句向用户授予操作权限，GRANT语句的一般格式为：GRANT ,2ON TO ,WITH GRANT OPTION其功能是将指定对象的指定操作权限授予指定的用户。3关系数据库标准语言关系数据库标准语言SQL 3.6 数据控制（续）授权（续）例3-53 假若USER1是数据库的用户，把对BOOK表的查询和修改权限授给用户USER1。GRANT SELECT,UPDATE ON book TO USER1例3-54 授予用户USER1创建表的权限，并允许他将此权限授予其他用户。GRANT CREATE TABLE TO USER1WITH GRANT OPTION3关系
37、数据库标准语言关系数据库标准语言SQL 3.6 数据控制（续）权限收回授予的权限可以由DBA或其授权者用REVOKE语句收回，REVOKE 语句的一般格式为：REVOKE ,ON FROM ,其功能是从指定的用户中收回指定的权限。3关系数据库标准语言关系数据库标准语言SQL 3.6 数据控制（续）权限收回（续）例3-58 回收USER1对BOOK表的查询与修改权限。REVOKE SELECT,UPDATE ON book FROM USER1如果要收回USER1的所有权限可以使用下面的命令：REVOKE ALL PRIVILEGES ON book FROM USER1 -拒绝语句 DENYALL|权限ON TO用户3
