《刘淳-数据库系统原理与应用 第3章 关系数据库标准语言SQL》由会员分享，可在线阅读，更多相关《刘淳-数据库系统原理与应用 第3章 关系数据库标准语言SQL（73页珍藏版）》请在金锄头文库上搜索。
1、第3章 关系数据库标准语言SQL,3.1 SQL概述 3.2 数据定义 3.3 数据查询 3.4 数据更新 3.5 视图 3.6 数据控制,3.1 SQL概述,1SQL语言 SQL语言是1974年由BOYCE和CHAMBERLIN提出的。1975年至1979年IBM公司SANJOSE RESEARCH LABORATORY研究的关系数据库管理系统原型系统SYSTEM R实现了这种语言，由于它功能丰富，语言简洁，使用方便，被众多计算机公司和软件公司所采用，经各公司不断修改、扩充和完善，SQL语言最终发展为关系数据库的标准语言。,返回首页,2扩展SQL语言 尽管ASNI和ISO已经针对SQL制定了
2、一些标准，但标准SQL语言只能完成数据库的大部分操作，不适合为关系数据库编写各种类型的程序，各家厂商针对其各自的数据库软件版本做了某些扩充和修改，一般都根据需要增加了一些非标准的SQL语言。经扩充后的SQL语言称为扩展SQL语言。,返回本节,3.2 数据定义,1基本表定义 2修改基本表 3删除基本表 4建立索引 5删除索引,返回首页,SQL数据定义功能包括定义基本表、定义视图和定义索引等，如表3-1所示。由于视图是基于基本表的虚表，索引是基于基本表的，因此SQL通常不提供修改视图和索引语句，用户如果要修改视图或索引，只能先将它们删除，然后重新创建。,表3-1 SQL数据定义语句,1基本表定义
3、CREATE TABLE 表名 说明： （1）其中表名是要定义的基本表的名称。一个表可以由一个或多个属性列组成。 （2）创建表时通常还可以定义与该表有关的完整性约束条件。 （3）数据类型：SQL Server 2000常用数据类型。,1）实体完整性定义语法： CONSTRAINT 约束名 PRIMARY KEY(属性列表) 2）参照完整性定义语法： CONSTRAINT 约束名 FOREIGN KEY(列名)REFERENCES (被参照表列名)。,3）自定义完整性定义语法： 列值非空：CONSTRAINT 约束名 NOT NULL。 列值惟一：CONSTRAINT 约束名 UNIQUE(属性
4、列表)。 逻辑表达式：CONSTRAINT 约束名 CHECK(表达式)。,例3-1 创建图书信息表、读者信息表和借阅表。,（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),（2）创建读者信息表。 CREATE TABLE READER (CARDID CHAR(10) PRIMARY KEY, NAME
5、 CHAR(8), SEX CHAR(2), DEPT CHAR(20), CLASS INT) -读者类型：1代表教师，2代表学生，3代表临时读者。,（3）创建借阅表。 CREATE TABLE BORROW (BOOKID CHAR(20), CARDID CHAR(10), BDATE DATETIME NOT NULL, SDATE DATETIME NOT NULL, PRIMARY KEY(BOOKID,CARDID,BDATE), CONSTRAINT FK_BOOKID FOREIGN KEY(BOOKID) REFERENCES BOOK(BOOKID), CONSTRAIN
6、T FK_CARDID FOREIGN KEY(CARDID) REFERENCES READER (CARDID),2修改基本表 随着应用环境和应用需求的变化，有时需要修改已建立好的基本表，包括增加新列、增加新的完整性约束条件、修改原有的列定义或删除已有的完整性约束条件等。SQL语言用ALTER TABLE语句修改基本表，其一般格式为： ALTER TABLE ALTER COLUMN NULL| NOT NULL ADD 完整性约束 ADD DROP CONSTRAINT DROP COLUMN ,例3-2 在图书信息表中增加一列出版时期（PUBDATE），并将BOOKID列宽改为15。,
7、ALTER TABLE BOOK ADD PUBDATE DATETIME GO ALTER TABLE BOOK ALTER COLUMN BOOKID CHAR(15) 例3-3 删除借阅表中的参照完整性。 ALTER TABLE BORROW DROP CONSTRAINT FK_BOOKID GO ALTER TABLE BORROW DROP CONSTRAINT FK_CARDID,3删除基本表 当某个基本表不再需要时，可以使用SQL语句DROP TABLE进行删除。其一般格式为： DROP TABLE ,4建立索引 在SQL语言中，建立索引使用CREATE INDEX语句，其一般
8、格式为： CREATE UNIQUE CLUSTER INDEX ON ( , )；,5删除索引 索引一经建立，就由系统使用和维护它，不需要用户干预。建立索引是为了减少查询操作的时间，但如果数据修改频繁，系统会花费许多时间来维护索引。这时，可以删除一些不必要的索引。,返回本节,3.3 数据查询,3.3.1 单表查询 3.3.2 多表查询 3.3.3 嵌套查询,返回首页,建立数据库的目的是为了查询数据，因此，可以说数据库查询是数据库的核心操作。SQL语言提供了SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。其一般格式为： SELECT ALL | DISTINCT , F
9、ROM , 别名 WHERE GROUP BY HAVING ORDER BY ASC | DESC ；,3.3.1 单表查询,1选择表中的若干列 2选择表中满足条件的记录 3对查询结果排序 4使用集函数 5分组统计,1选择表中的若干列,选择表中的全部列或部分列，这类运算又称为投影。其变化方式主要表现在SELECT子句的上。 例3-6 查询所有读者的卡号和姓名。 SELECT CARDID, NAME FROM READER 例3-7 查询所有图书信息。 SELECT * FROM BOOK 说明：*代表所有列,2选择表中满足条件的记录,查询满足指定条件的元组可以通过WHERE子句实现。条件表
10、达式是操作数与运算符的组合，操作数可以包括常数、变量和字段等。常用运算符如表3-2所示。 （1）比较运算： （2）确定范围： （3）集合运算： （4）字符匹配： （5）空值判断： （6）逻辑运算：,表3-2 常用运算符,3对查询结果排序,如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序（通常是元组在表中的先后顺序）输出查询结果。用户也可以用ORDER BY子句指定按照一个或多个属性列的升序（ASC）或降序（DESC）重新排列查询结果，其中升序ASC为缺省值。,4使用集函数,为了进一步方便用户，增强检索功能，SQL提供了许多集函数，主要包括： COUNT ( DISTINCT |ALL
11、 * ) 统计元组个数。 COUNT ( DISTINCT | ALL ) 统计一列中值的个数。 SUM ( DISTINCT | ALL ) 计算一列值的总和（此列必须是数值型）。 AVG（DISTINCT | ALL ） 计算一列值的平均值（此列必须是数值型）。 MAX（DISTINCT | ALL ） 计算一列值的最大值。 MIN（DISTINCT | ALL ） 计算一列值的最小值。,5分组统计,GROUP BY子句可以将查询结果中的各行按一列或多列取值相等的原则进行分组。分组一般与集函数一起使用。 对查询结果分组的目的是为了细化集函数的作用范围。如果未对查询结果分组，集函数将作用于整
12、个查询结果，即对查询结果中的所有记录进行计算。如果有分组，集函数将作用于每一个分组，即集函数对每个组分别进行计算。,返回本节,3.3.2 多表查询,1等值与非等值连接查询 2自然连接 3自身连接 4外连接 5连接查询综合实例,1等值与非等值连接查询,当用户的一个查询请求涉及到数据库的多个表时，必须按照一定的条件把这些表连接在一起，以便能够共同提供用户需要的信息。用来连接两个表的条件称为连接条件或连接谓词，其一般格式为： . .,例3-24 查询所有有借书未还的读者的姓名。 SELECT NAME FROM READER,BORROW WHERE READER.CARDID=BORROW.CAR
13、DID AND SDATE IS NULL 结果如下： NAME - 丁钰 张清蜂 -,例3-25 查询所有读者信息及借阅情况。 SELECT READER.* , BORROW.* FROM READER , BORROW WHERE READER.CARDID=BORROW.CARDID 该查询结果中将包含READER和BORROW表中的所有列。,2自然连接,如果按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留所有不重复的属性列，则称为自然连接。,例3-26 自然连接READER和BORROW表。 SELECT READER.CARDID, NAME, SEX, D
14、EPT, CLASS , BOOKID, BDATE, SDATE FROM READER,BORROW WHERE READER.CARDID=BORROW.CARDID 查询结果如下：,3自身连接,连接操作不仅可以在两个表之间操作，也可以是一个表与其自己进行连接，这种操作称为自身连接。,例3-27 查询书名相同而出版社不同的所有图书的书名。 SELECT DISTINCT B1.BOOKNAME FROM BOOK B1,BOOK B2 WHERE B1.BOOKNAME=B2.BOOKNAME AND B1.PUBLISHB2.PUBLISH 查询结果为： BOOKNAME - 数据结构
15、 -,4外连接,在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。如例3-26，如果某读者还没有借书记录，在结果集中就看不到该读者的信息。外连接又分为左外连、右外连和全外连。 左外连：查询结果中不仅包含符合连接条件的行，而且包含左表中所有数据行。 右外连：查询结果中不仅包含符合连接条件的行，而且包含右表中所有数据行。 全外连：查询结果中不仅包含符合连接条件的行，而且包含两个连接表中所有数据行。,5连接查询综合实例,例3-29 查询借书期限超过2个月的所有读者的姓名、所借书籍名和借书日期。 SELECT NAME,BOOKNAME,BDATE FROM BOOK,READER,BORROW WHERE BOOK.BOOKID=BORROW.BOOKID AND READER.CARDID=BORROW.CARDID AND DATEDIFF(MM, BDATE,GETDATE() 2,例3-30 按读者姓名查询指定读者的借还书历史记录。假设读者姓名为“刘勇”。 SELECT BOOK.NAME,BDATE,SDATE FROM BORROW,BOOK,READER WHERE BOOK.BOOKID=BORROW.BOOKID AND READER.CARDID=BORROW.CARDI
