自考互联网数据库 第三章 关系数据库标准语言SQL

《自考互联网数据库 第三章 关系数据库标准语言SQL》由会员分享，可在线阅读，更多相关《自考互联网数据库 第三章 关系数据库标准语言SQL（107页珍藏版）》请在装配图网上搜索。

1、第三章 关系数据库标准语言SQL3.1 SQL概述3.2 数据定义3.3 数据操纵3.4 视图3.5 数据控制3.1 SQL概述nSQL（Structured Query Language）：结构化查询语言。（发音：按字母或sequel）nSQL是关系数据库的标准语言关系数据库的标准语言。2SQL标准nSQL-86：1986年10月由美国国家标准局（American National Standard Institute，简称ANSI）公布。nSQL-89：ANSI（SQL1标准标准）nSQL-92：ANSI（SQL2标准标准）nSQL-99：ANSI+ISO（SQL3标准标准）33.1.1

2、SQL的特点1.综合统一 SQL语言集DDL、DML、DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动。在关系模型中，实体和实体间联系均用关系表示，这种数据结构的单一性带来了数据操作的统一性。4SQL的特点续一2.高度非过程化 非关系模型的数据库操纵语言是面向过程的语言，用其完成某项请求，必须指定存取路径。SQL语言进行数据操作，只要提出“做什么”，而不需指明“怎么做”。5SQL的特点续二3.面向集合的操作方式 非关系数据模型采用的是面向记录的操作方式，任何一个操作其对象都是一条记录。SQL语言采用集合操作方式，不仅查找结果可以是元组的集合，而且一次插入、删除、更新操作

3、的对象也可以是元组的集合。6SQL的特点续三4.以同一种语法结构提供两种使用方式 SQL语言既是自含式语言，又是嵌入式语言。在两种不同的使用方式下，SQL语言的语法结构基本上是一致的。7SQL的特点续四5.语言简洁，易学易用 SQL语言功能极强，而语言十分简洁，完成数据定义、数据操纵、数据控制的核心功能只用了9个动词：CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE。SQL语言语法简单，接近英语口语。8SQL语言的动词SQL 功能动词数据查询 SELECT数据定义 CREATE,DROP,ALTER数据操纵 INSERT,UP

4、DATE,DELETE数据控制 GRANT,REVOKE93.1.2 SQL语言的基本概念nSQL语言支持关系数据库三级模式结构。10SQL语言的基本概念续一n外模式视图、部分基本表n模式基本表n内模式存储文件11SQL语言的基本概念续二n基本表基本表是本身独立存在的表，在SQL中一个关系对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引存放在存储文件中。12SQL语言的基本概念续三n视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。13SQL语言的基本

5、概念续四n用户可以用SQL语言对视图和基本表进行查询。n在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。143.2 数据定义n关系数据库由模式、外模式和内模式组成，即关系数据库的基本对象是表、视图和索引。n因此SQL的数据定义功能包括定义表、定义视图和定义索引。15SQL的数据定义语句操作对象操作方式创建删除修改表CREATE TABELDROP TABLEALTER TABLE视图CREATE VIEWDROP VIEW索引CREATE INDEXDROP INDEX163.2.1 定义、删除与修改基本表1.定义基本表 基本格式：CREATE TABLE (列级完整性约束条件列级

6、完整性约束条件 ,列级完整性约束条件列级完整性约束条件.,);17定义、删除与修改基本表续一n例1：建立一个“职工”表personel，它由职工号no、姓名name、性别sex、年龄age、所有部门dept五个属性组成，其职工号属性不能为空，并且值是唯一的。CREATE TABLE personel (no CHAR(5)NOT NULL UNIQUE,name CHAR(20),sex CHAR(1),age INT,dept CHAR(15);18SQL的基本数据类型n数值型INTEGER 长整数（也可写成INT）SMALLINT 短整数REAL 浮点数DOUBLE PRECISION 双

7、精度浮点数FLOAT(n)浮点数，精度至少为n位数字NUMERIC(p,d)定点数，有p位数字（不包括符号、小数点）组成，小数点后面有d位数字（也可写成DECIMAL(p,d)或DEC(p,d)）。19SQL的基本数据类型续n字符串型CHAR(n)长度为n的定长字符串VARCHAR(n)有最大长度为n的变长字符串n位串型BIT(n)长度为n的二进制位串BIT VARYING(n)最大长度为n的变长二进制位串n时间型DATE 日期，形为YYYY-MM-DDTIME 时间，形为HH:MM:SS20定义、删除与修改基本表续二n系统执行上面的CREATE TABLE语句后，就在数据库中建立一个新的空的

8、“职工”表personel，并将有关“职工”表的定义及有关约束条件存放在数数据字典据字典中。21定义、删除与修改基本表续三2.修改基本表 修改已建立好的基本表（的结构），包括增加新列、完整性约束条件、修改原有的列定义或删除已有的完整性约束条件等。格式为：ALTER TABLE ADD 完整性约束完整性约束 DROP MODIFY ;22定义、删除与修改基本表续四n例2：向personel表增加“参加工作时间”列，其数据类型为日期型。ALTER TABLE personel ADD come DATE;23定义、删除与修改基本表续五n例3：将年龄的数据类型改为半字长整数。ALTER TALBE

9、personel MODIFY Sage SMALLINT;24定义、删除与修改基本表续六n例4：删除关于学号必须唯一的约束。ALTER TABLE personel DROP UNIQUE(no);25定义、删除与修改基本表续七3.删除基本表 格式为：DROP TABLE ;例5：删除personel表 DROP TABLE personel;基本表定义一旦删除，表中的数据和在此表上建立的索引都将自动被删除掉。263.2.2 建立与删除索引n建立索引是加快表的查询速度的有效手段。n可以根据需要在基本表上建立一个或多个索引。27建立与删除索引续一1.建立索引 格式为：CREATE UNQUE

10、CLUSTER INDEX ON (,次序次序 .);UNQUE：每一个索引值只对应唯一的记录 CLUSTER：聚簇索引 次序：指定索引值的排列次序，包括升序（ASC）和降序（DESC）两种。28建立与删除索引续二n例6：为职工personel表建立索引，按职工号升序和姓名降序建唯一索引。CREATE UNIQUE INDEX on_index ON personel(no ASC,name DESC);29建立与删除索引续三2.删除索引 格式：DROP INDEX ;例7：删除personel表的name_index索引。DROP INDEX name_index;删除索引时，系统会同时从数

11、据字典中删去有关该索引的描述。303.3 数据操纵nSQL数据操纵功能包括SELECT、INSERT、DELETE和UPDATE四个语句，即检索查询和更新（包括增、删、改）两部分功能。313.3.1 SQL查询语句n数据库查询是数据库的核心。SQL语言提供了SELECT语句进行数据库的查询，格式为：SELECT ALL|DISTINCT ,.FROM ,.WHERE GROUP BY HAVING 内部函数表达式内部函数表达式 ORDER BY ASC|DESC ;32SQL查询语句续一n下面用学生课程数据库为例说明SELECT的各种用法。student(no,name,sex,age,dep

12、t)course(seq,subject,credit)choose_course(no,seq,grade)33SQL查询语句续二1.简单查询：仅涉及数据库中的一个表 (1)查询表中的若干列。例1：求全体学生的详细信息。SELECT*FROM student;其中*表示FROM后面指定的表的全部属性。34SQL查询语句续三1.简单查询 (1)查询表中的若干列。例2：求学生姓名及其出生年份。SELECT name,2009-age FROM student;SELECT语句后面可以是字段名，可以是字段和常数组成的算术表达式，也可以是字符串常数。35SQL查询语句续四1.简单查询 (2)消除取值

13、重复的行。例3：查询选修了课程的学生学号。SELECT DISTINCT no FROM choose_course;36SQL查询语句续五1.简单查询 (3)条件查询：通过WHERE子句实现，WHERE子句常用查询条件如下：查询条件谓词比较=,!=,!,!;not+上述比较符确定范围BETWEEN AND,NOT BETWEEN AND确定集合IN,NOT IN字符匹配LIKE,NOT LIKE空值IS NULL,IS NOT NULL多重条件AND,OR37SQL查询语句续六1.简单查询 (3)条件查询。例4：求年龄在20岁与22岁之间的学生姓名和年龄。SELECT name,age FR

14、OM student WHERE age BETWEEN 20 AND 22;38SQL查询语句续七1.简单查询 (3)条件查询。例5：求学号为99001的学生的详细情况。SELECT *FROM student WHERE no LIKE 99001;39字符匹配nLIKE谓词的一般形式是：列名 LIKE 字符串常量n通配符“_”（下划线）：可以表示任意的单个字符；“%”：可以表示任意多个任意字符。40SQL查询语句续八1.简单查询 (3)条件查询。例6：求缺少学习成绩的学生学号和课程号。SELECT no,seq FROM choose_course WHERE grade IS NULL

15、;41SQL查询语句续九1.简单查询 (4)对查询结果排序。例7：查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT *FROM student ORDER BY dept,age DESC;42SQL查询语句续十1.简单查询 (5)使用集函数（聚合函数）。COUNT(DISTINCT|ALL*)：统计元组个数：统计元组个数 COUNT(DISTINCT|ALL)：统计一列中值的个数：统计一列中值的个数 SUM(DISTINCT|ALL)：计算一列值的总和：计算一列值的总和(数值型数值型)AVG(DISTINCT|ALL)：计算一列值的平均值：计算一列

16、值的平均值(数值数值)MAX(DISTINCT|ALL)：求一列值中的最大值：求一列值中的最大值 MIN(DISTINCT|ALL)：求一列值中的最小值：求一列值中的最小值 如果指定DISTINCT短语，表示计算时取消指定列中的重复值。43SQL查询语句续十一1.简单查询 (5)使用集函数（聚合函数）。例8：查询学生总人数。SELECT COUNT(*)FROM student;44SQL查询语句续十二1.简单查询 (6)对查询结果分组。例9：查询选修了3门以上课程的学生学号。SELECT no FROM choose_course GROUP BY no HAVING COUNT(*)3;4

17、5SQL查询语句续十三2.连接查询 若查询同时涉及两个以上的表，则称之为连接查询。(1)等值与非等值连接查询。例10：查询每个学生及其选修课情况 SELECT student.*,choose_course.*FROM student,choose_course WHERE student.no=choose_course.no;46SQL查询语句续十四2.连接查询 (2)自身连接 例11：查询课程号等于学分的课程名称。SELECT name FROM course WHERE seq=credit;47SQL查询语句续十五2.连接查询 (3)外连接 不作要求！不作要求！48SQL查询语句续十

18、六2.连接查询 (4)复合条件连接。例13：查询选修课程4且成绩在80分以上的所有学生。SELECT s.no,name FROM student s,choose_course c WHERE s.no=c.no AND seq=4 AND grade 80;49SQL查询语句续十七3.嵌套查询 嵌套查询也称为子查询，是指一个SELECT.FROM.WHERE.查询块可以嵌入另一个查询块之中。SQL中允许多层嵌套。50SQL查询语句续十八3.嵌套查询 例14：求选修了课程名为“数据库”的学生姓名。SELECT name FROM student WHERE no IN (SELECT no

19、FROM choose_course WHERE seq IN (SELECT seq FROM course WHERE subject=数据库数据库);51SQL查询语句续十九3.嵌套查询 (1)谓词IN的子查询。例15：查询与“杨敏”在同一系学习的学生。SELECT no,name,dept FROM student s1 WHERE s1.dept IN(SELECT dept FROM student s2 WHERE s2.name=杨敏杨敏);52SQL查询语句续二十3.嵌套查询 (2)谓词为比较运算符的子查询。若能知道内层查询返回的是单值，则可以用比较算符(=，等等)，如上例：

20、SELECT no,name,dept FROM student WHERE dept =(SELECT dept FROM student s2 WHERE s2.name=杨敏杨敏);53SQL查询语句续二十一3.嵌套查询 (3)EXISTS谓词的子查询。例16：求不选修2号课程的学生姓名。SELECT name FROM student WHERE NOT EXISTS (SELECT *FROM choose_course WHERE no=student.no AND seq=2);54SQL查询语句续二十二3.嵌套查询 (4)集合查询。多个SELECT语句的结果可进行集合操作，用得

21、最多的是并操作UNION。例17：查询选修了课程1或选修了课程2的学生。SELECT no FROM choose_course WHERE seq=1 UNION SELECT no FROM choose_course WHERE seq=2;EXCEPT,INTERSECT553.3.2 SQL更新语句1.修改（UPDATE)：格式：UPDATE SET =,=.WHERE 谓词谓词;56SQL更新语句续一1.修改 例18：单记录修改。把学生“李兰”的姓名改为“张岩”。UPDATE student SET name=张岩张岩 WHERE name=李兰李兰;57SQL更新语句续二1.修改

22、 例19：多记录修改，把所有学生的年龄加2。UPDATE student SET age=age+2;58SQL更新语句续三2.删除（Delete）：格式：DELETE FROM WHERE 谓词谓词 ;59SQL更新语句续四2.删除 例20：单记录删除。把学生99001删除。DELETE FROM student WHERE no=99001;60SQL更新语句续五2.删除 例21：多记录删除。删除所有的学生选课记录。DELETE FROM choose_course;61SQL更新语句续六3.插入（INSERT）:(1)格式一：插入一个元组 INSERT INTO (,.VALUES(,.

23、);62SQL更新语句续七3.插入 (2)格式二：插入子查询结果 INSERT INTO (,.子查询子查询;63SQL更新语句续八3.插入 例22：单记录插入。把一个新学生：99006,姓名“张明”，信息系，20岁，插入表中。INSERT INTO student VALUES(99006,张明张明,信息系信息系,20);64SQL更新语句续九3.插入 例23：多记录插入。对每一个系，求学生的平均年龄，并把结果存入数据库。CREATE TABLE deptage (dept CHAR(15),avgage SMALLINT);INSERT INTO deptage(dept,avgage)S

24、ELECT dept,AVG(age)FROM student GROUP BY dept ;653.4 视图n视图是从一个或几个基本表（或视图）导出的表，它与基本表不同，是一个虚表。n数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。n对视图的查询（检索）与基本相同，但不是所有的视图都可以被更新。663.4.1 定义视图1.建立视图 格式：CREATE VIEW (,.)AS WITH CHECK OPTION ;其中的子查询可以是任意的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语。67定义视图续一1.建立视图 例1：建立计算机

25、系学生的视图。CREATE VIEW IS_Student AS SELECT no,name,age FROM student WHERE dept=计算机计算机;68定义视图续二1.建立视图 例2：建立电子商务系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息系的学生。CREATE VIEW IS_Student AS SELECT no,name,age FROM student WHERE dept=电子商务电子商务 WITH CHECK OPTION;69定义视图续三n若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了码，称这类视图为行行列子集

26、视图列子集视图。n只有行列子集视图才可以更新。70定义视图续四1.建立视图 例3：建立计算机系选修2号课程的学生的视图。CREATE VIEW IS_S1(no,name,grade)AS SELECT student.no,name,grade FROM student,choose_course WHERE dept=计算机计算机 AND student.no=choose_course.no AND choose_course.seq=2;71定义视图续五1.建立视图 例4：建立计算机系选修了2号课程且成绩在90分以上的学生的视图。CREATE VIEW IS_S2 AS SELECT

27、no,name,grade FROM IS_S1 WHERE grade=90;72定义视图续六1.建立视图 例5：定义一个反映学生出生年份的视图。CREATE VIEW birthday(no,name,birth)AS SELECT no,name,2009-age FROM student;带虚拟列的视图称为带表达式的视图。73定义视图续七1.建立视图 例6：将学生的学号及其平均成绩定义为一个视图。CREATE VIEW S_G(no,gavg)AS SELECT no,AVG(grade)FROM choose_course GROUP BY no;(分组视图)74定义视图续八1.建立

28、视图 例7：将student表中所有女生记录定义为一个视图。CREATE VIEW F_Student(stdnu,name,sex,age,dept)AS SELECT *FROM student WHERE sex=女女;75定义视图续九2.删除视图 格式：DROP VIEW ;例8：删除视图IS_S1；DROP VIEW IS_S1;执行此语句后，IS_S1视图的定义将从数据词典中删除。763.4.2 查询视图nDBMS执行对视图的查询时，首先进行有效性检查。n从数据字典中取出查询涉及的视图的定义，把定义中的子查询和用户对视图的查询结合起来，转换成对基本表的查询。n执行这个经过修正的查询

29、。n对视图的查询转换为对基本表的查询的过程称为视图的消解视图的消解。77查询视图续一n例9：在计算机系学生的视图中找出年龄小于20岁的学生。SELECT no,age FROM IS_Student WHERE age 20;78查询视图续二n例10：查询计算机系选修了1号课程的学生。SELECT no,name FROM IS_Student,choose_course WHERE IS_Student.no=choose.no AND choose_course.seq=1;793.4.3 更新视图n更新视图包括插入（INSERT）、删除（DELETE）和修改（UPDATE）三类操作。n可

30、以更新的视图要满足两个条件：视图必须是“行列子集视图”；视图定义时应加上“WITH CHECK OPTION”子句。80更新视图续一n例11：将计算机系学生视图IS_Student中学号为99002的学生姓名改为“李旭”。UPDATE IS_Student SET Sname=李旭李旭 WHERE Sno=99002;P64 例例181更新视图续二n例12：向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为99092，名为赵新，年龄为20岁。INSERT INTO IS_Student VALUES (99092,赵新赵新,20);82更新视图续三n例13：删除计算机系学生

31、视图IS_Student中学号为99029的记录。DELETE FROM IS_Student WHERE no=99029;833.4.4 视图的作用(1)简化用户的操作 视图机制使用户可以将注意力集中在他所关心的数据上。如果这些数据不是直接来自基本表，则可以通过定义视图，使用户眼中的数据库结构简单、清晰，并且可以简化用户的数据查询操作。84视图的作用续一(2)使用户能以多种角度看待同一数据 视图机制能使不同的用户以不同的方式看待同一数据，当许多不同种类的用户用同一个数据库时，这种灵活性是非常重要的。85视图的作用续二(3)对重构数据库提供了一定程度的逻辑独立性 数据的逻辑独立性是指当数据库

32、重构造时，如增加新的关系或对原有关系增加新的字段等，用户和用户程序不会受影响。86视图的作用续三(4)能够对机密数据提供安全保护 在设计数据库应用系统时，对不同的用户定义不同的视图，使机密数据不出现在不应看到这些数据的用户视图上，这样就由视图的机制自动提供了对机密数据的安全保护功能。873.5 数据控制nSQL中数据控制功能包括事务管理功能和数据保护功能，即数据库的恢复、并发控制，数据库的安全性和完整性。n本节主要讨论SQL语言的安全控制功能。88数据控制续n数据库管理系统保证数据安全的主要措施是进行存取控制，即规定不同用户对于不同数据对象所允许执行的操作，并控制各用户只能存取他有权存取的数据

33、。n不同的用户对不同的数据应具有不同的操作权力。893.5.1 授权nSQL语言用GRANT语句向用户授予操作权限，格式为：GRANT ,.ON TO ,.WITH GRANT OPTION 90不同类型对象允许的操作权限对象对象类型操作权限属性列TABLESELECT、INSERT、UPDATE、DELETE、ALL PRIVILEGES视图TABLESELECT、INSERT、UPDATE、DELETE、ALL PRIVILEGES基本表TABLESELECT、INSERT、UPDATE、DELETE、ALTER、INDEX、ALL PRIVILEGES数据库DATABASECREATE

34、TABLE91授权续一 nALL PRIVILEGES表示所有的权限。nPUBLIC表示所有的用户。nWITH GRANT OPTION表示获得某种权限的用户还可以把这种权限再授予其他用户。92授权续二n例1：把查询course表的权限授给用户user1。GRANT SELECT ON TABLE course TO user1;93授权续三n例2：把对student表和course表的全部操作权限授予用户user2和user3。GRANT ALL PRIVILEGES ON TABLE student,course TO user2,user3;94授权续四n例3：把对表choose_cou

35、rse的查询权限授予所有用户。GRANT SELECT ON TABLE choose_course TO PUBLIC;95授权续五n例4：把查询choose_course表和修改成绩的权限授给用户user4。GRANT UPDATE(grade),SELECT ON TABLE choose_course TO user4;96授权续六n例5：把对表student的INSERT权限授予user5用户，并允许将此权限再授予其他用户。GRANT INSERT ON TABLE student TO user5 WITH GRANT OPTION;97授权续七n例6：DBA把对数据库app_db

36、中建立表的权限授予用户user8。GRANT CREATE TABLE ON DATABASE app_db TO user8;983.5.2 收回权限n授予的权限可以由DBA或其他授权者用REVOKE语句收回。格式：REVOKE ,.ON FROM ,.;99收回权限续一n例7：把用户user4修改学生学号的权限收回。REVOKE UPDATE(grade)ON TABLE choose_course FROM user4;100收回权限续二n例8：收回所有用户对表student的查询权限。REVOKE SELECT ON TABLE student FROM PUBLIC;101收回权限续

37、三n例9：把用户user5对student表的INSERT权限收回。REVOKE INSERT ON TABLE student FROM user5;102补充练习n有一个教学数据库，包括三个关系模式：(1)学生S(S#,SNAME,AGE,SEX)(2)学习SC(S#,C#,GRADE)(3)课程C(C#,CNAME,TEACHER)根据以上3个关系模式用SQL语句完成下列问题：103补充练习续一1.检索选修课程包含LIU老师所授课的学生学号。SELECT S#FROM SC,CWHERE SC.C#=C.C#AND TEACHER=LIU;104补充练习续二2.把低于总平均成绩的女同学成绩提高5%。UPDATE SCSET GRADE=GRADE*1.05WHERE GRADE=3);106补充练习续四4.检索“王兰”同学不学的课程的课程号。SELECT C#FROM SCWHERE S#NOT IN (SELECT S#FROM S WHERE SNAME=王兰王兰);107