第三章 SQL语言

Transcription

1 第三章 SQL 语言 1 SQL 语言概貌及特点 1. SQL 语言概述 1) SQL:Structured Query Language,2) 结构化查询语言 3) SEQUEL:Structured English Query language4) 一种介于关系代数与关系演算之间的语言 5) 成为关系数据库的标准语言 7) 支持 :System R SQL/DS Oracle Informix Sybase INGRES DB2 FoxPro Access 2. SQL 语言的功能 1) 定义 :DDL(Data Definition Language) 语言 2) 查询 3) 操纵 :DML(Data Manipulation Language) 语言 4) 控制 :DCL(Data Control Language) 语言 3. SQL 语言的标准化 1) SQL86 (1) 由 ANSI(American National Standard Institute, 美国国家标准局 ) 于 1986 年 10 月公布 (2) ISO(International Standardization Organization, 国际标准化组织 ) 于 1987 年通过 (3) 数据定义语言 (4) 数据操纵语言 (5) 模块语言 (6) 嵌入式语法 2) SQL89 (1) ISO 于 1989 年公布 (2) 增加简单的完整性描述语句 3) SQL92 (1) ISO 于 1992 年公布 (2) 也称 SQL2 4) SQL3 (1) 酝酿中的新标准 (2) 增加面向对象功能 4. SQL 语言支持的三级逻辑结构 1) 基本表 (Base Table) (1) 数据库中实际独立存在的表 ( 关系 ), 存储在实际的文件中 (2) 不是由其他表导出 2) 视图 (View) (1) 虚拟表换个角度看实际表的结果 (3) 由一个或几个基本表导出没有实际的存储位置 (3) S(S#,SN,AGE,SEX,DEP) CS_S(S#,SN,AGE,SEX)(4) 库中只保存视图的定义, 不存放对应的数据 3) 存储文件 (1) 存储基本表的数据 5. SQL 语言的主要特点 1) 综合统一 (1) 操作一体化 : 查询 操作 定义 控制 2) 高度非过程化 (1) 用户 干什么 (2) RDBMS 怎么干 (3) 隐蔽数据的存取路径 3) 面向集合的操作方式 (1) 操作对象是一个或多个关系 (2) 操作结果也是一个新关系 4) 以同一种语法结构提供两种使用方式 (1) 自含型 :DBMS 中独立使用,(2) 针对 DB 的所有用户

2 (3) 宿主型 : 嵌入到宿主语言中使用, 针对应用程序员 (4) 两种类型的语法结构基本一致 6) 语言简洁, 易学易用 (1) 类似于英语的自然语言 (2) 操作谓词少 2 SQL 数据查询功能 1. 概述 1) 检索不改变数据本身 2) 对现成的基本表 ( 关系 ) 和视图 ( 虚表 ) 进行数据查询 3) SQL 语言的格式 4) SQL 结果的转向 (1) 缺省 : 输出到临时窗口 (2) TO SCREEN: 屏幕输出 (3) TO <FileName>: 输出到 TXT 文件 (4) INTO TABLE <TableName>: 输出到表 2. 单表查询 1) 投影查询 SELECT (1) 查询指定列 1. 查询学生的姓名年龄和性别 (1) SELECT Sn,Age,Sex FROM Student 2. 查询学生选修课的课程号 ** (1) SELECT Cno FROM Sc (2) SELECT DISTINCT Cno FROM Sc (3) 查询全部列 1. 查询全体学生的详细记录 (1) SELECT * FROM Student 2. 按人为次序显示全体学生的详细记录 (1) SELECT Sn,Dept,Age,Sex,Sno FROM Student (4) 查询经过计算的值 1. 查询全体学生的出生年份 (1) SELECT Sn,2002-Age FROM Student 2. 以小写字母显示系名 (1) SELECT Sn,LOWER(Dept) FROM Student (2) 列标题使用别名 1. 查询全体学生的出生年份, 并使用别名. (1) SELECT Sn Name,2002-Age Birthday FROM Student 2) 选取查询 WHERE (1) 比较大小 :(NOT) 比较运算符 1. 查询学习了课程 C2 的学生 (1) SELECT * FROM Sc WHERE Cno= C2 2. 查询年龄大于 19 的学生 (1) SELECT * FROM Student WHERE Age>19 (2) SELECT * FROM Student WHERE NOT Age<=19 (2) 多重条件查询 :AND / OR 1. 查询课程号为 C2 且成绩高于 85 分以上的学生 (1) SELECT * FROM Sc WHERE Cno= C2 AND G>85 2. 查询选修 C1 或 C2 且不 3. 低于 70 分的学生

3 (1) SELECT * FROM Sc WHERE (Cno= C1 OR Cno= C2 ) AND G>=70 (3) 确定范围 :(NOT) BETWEEN AND 1. 查询成绩在 60 至 75 之间的学生 (1) SELECT * FROM Sc WHERE G>=60 AND G<=75 (2) SELECT * FROM Sc WHERE G BETWEEN 60 AND 75 (4) 消除重复的行 :DISTINCT 1. 查询选修了课程 C1 或 C2 的学号 (1) SELECT Sno FROM Sc WHERE Cno= C1 OR Cno= C2 (2) SELECT DISTINCT Sno FROM Sc WHERE Cno= C1 OR Cno= C2 (3) DISTINCT 与 ALL( 默认 ) 对应 (6) 确定集合 :(NOT) IN 1. 查询选修了课程 C1 或 C2 的学号 (1) SELECT DISTINCT Sno FROM Sc WHERE Cno= C1 OR Cno= C2 (2) SELECT DISTINCT Sno FROM Sc WHERE Cno IN ( C1, C2 ) 2. 查询既不是计算机系, 也不是数学系的学生 (1) SELECT * FROM Student WHERE Dept NOT IN ('CS','MA') (2) SELECT * FROM Student WHERE NOT Dept IN ('CS','MA') (7) 字符匹配 ( 模糊查询 ):(NOT) LIKE 1. 查询计算机系的学生 (1) SELECT * FROM Student WHERE Dept = 'CS' (2) SELECT * FROM Student WHERE Dept LIKE 'CS' 2. 查询不以 P 开头的课程 (1) SELECT * FROM Course WHERE Cn NOT LIKE 'P%' (2) %( 百分号 ): 代表任意 0-n 个字符 (3) _( 下划线 ): 代表任意 1 个字符 4. 查询第二个字符为 o 的课程 (1) SELECT * FROM Course WHERE Cn LIKE '_o%' 5. 查询姓赵的学生 ( 汉字?) (1) SELECT * FROM Student WHERE Sn LIKE ' 赵 %' (2) SELECT * FROM Student WHERE Sn LIKE ' 赵 _' (8) 涉及空值的查询 :IS (NOT) NULL 1. 查询没填的课程名 (1) SELECT * FROM Course WHERE Cn IS NULL (2) SELECT * FROM Course WHERE LEN(TRIM(Cn))=0 3) 排序查询 ORDER BY (1) 查询选修了 C1 的学生,(2) 成绩按降序排列 1. SELECT * FROM Sc WHERE Cno= C1 ORDER BY G DESCENDING 2. DESCENDING 降序,3. ASC 升序 ( 默认 ) (3) 查询学号为 S1 S3 和 S5 的学生,(4) 年龄按升序排列 1. SELECT * FROM Student WHERE Sno= S1 OR Sno= S3 OR Sno= S5 ORDER BY Age 2. SELECT * FROM Student WHERE Sno IN ( S1, S3, S5 ) ORDER BY Age (5) 按系显示学生, 同 (6) 系学生按年龄从大到小排列 1. SELECT * FROM Student ORDER BY Dept,Age DESCENDING 4) 库函数 ( 集函数 ) 查询

4 (1) 库函数 1. AVG: 按列计算平均值, 对数值有效 2. SUM: 按列计算值的总和, 对数值有效 3. COUNT: 按列值计个数, 用 DISTINCT 消去重复行 4. COUNT *: 统计元组个数, 用 DISTINCT 消去重复行 5. MAX: 在列中找出最大值 6. MIN: 在列中找出最小值 (2) 求计算机系学生的平均年龄 1. SELECT AVG(Age) FROM Student WHERE Dept='CS' (3) 求 S3 学生的总分和平均分 1. SELECT SUM(G),AVG(G) FROM Sc WHERE Sno='S3' (4) 求计算机系的学生总数 1. SELECT COUNT(Sno) FROM Student WHERE Dept='CS' 2. SELECT COUNT(*) FROM Student WHERE Dept='CS' (5) 查询共有几个系 1. SELECT COUNT (*) FROM Student 2. SELECT COUNT (Dept) FROM Student 3. SELECT COUNT (DISTINCT Dept) FROM Student (6) 求课程 C1 的最高分和最低分以及高低分之间的差距 1. SELECT MAX(G),MIN(G),MAX(G)-MIN(G) FROM Sc WHERE Cno= C1 5) 分组查询 GROUP BY (1) 语句 1. 分组子句 GROUP BY 2. 分组条件 HAVING,3. 去掉不 4. 符合条件的若干组 (2) 查询各个课程的选修人数 1. SELECT Cno,COUNT(*) FROM Sc GROUP BY Cno (3) 查询出至少选修了 4 门课程的学号和门数 1. SELECT Sno,COUNT(*) FROM Sc GROUP BY Sno 2. SELECT Sno,COUNT(Sno) FROM Sc GROUP BY Sno HAVING COUNT(Sno)>=4 (4) 求选课在 4 门以上的平均成绩,(5) 不 (6) 统计不 (7) 及格的课程,(8) 按降序排列总成绩 1. SELECT Sno,AVG(G) Average FROM Sc WHERE G>=60 GROUP BY Sno HAVING COUNT(*)>=4 ORDER BY Average DESCENDING 2. SELECT Sno,AVG(G) FROM Sc WHERE G>=60 GROUP BY Sno HAVING COUNT(*)>=4 ORDER BY 2 DESCENDING 3. 求解过程 (1) FROM: 指 (2) 明操作对象 (3) WHERE: 选取 (4) GROUP: 分组 (5) HAVING: 选组 (6) SELECT: 投影 (7) ORDER: 排序 3. 连表查询 1) 等值连接

5 (1) 查询每个学生及其选修课程的情况 1. SELECT Student.*,Sc.* FROM Student,Sc 2. SELECT Student.*,Sc.* FROM Student,Sc WHERE Student.Sno=Sc.Sno 3. SELECT Student.Sno,SN,Sex,Age,Dept,Cno,G FROM Student,Sc WHERE Student.Sno=Sc.Sno 2) 复合条件连接 (1) 查询 张三 的成绩 1. SELECT Cno,G FROM Student,Sc WHERE Student.Sno=Sc.Sno AND Sn= 张三 (2) 查询所有学生选修的课程名 (3) 和成绩 1. SELECT Sn,Cn,G FROM Student,Course,Sc WHERE Student.Sno=Sc.Sno AND Course.Cno=Sc.Cno 3) 自身连接 (1) 查询所有比 周八 大的姓名 (2) 和年龄 1. SELECT X.Sn,X.Age FROM Student X,Student Y WHERE X.Age>Y.Age AND Y.Sn=' 周八 ' 4. 子查询块嵌套查询 1) 概念 (1) 嵌套查询是 SQL 结构化的体现 (2) 内部查询 ( 内层查询 子查询 ) 外部查询( 外层查询 父查询 主查询 ) (3) 子查询不 (4) 能使用 ORDER 2) 带有 IN 谓词的子查询 (1) 查询和 吴二 同岁的学生 1. SELECT Age FROM Student WHERE Sn= 吴二 2. SELECT Sn,Age FROM Student WHERE Age=18 3. SELECT Sn,Age FROM Student WHERE Age IN (SELECT Age FROM Student WHERE Sn= 吴二 ) 4. SELECT X.Sn,X.Age FROM Student X,Student Y WHERE X.Age=Y.Age AND Y.Sn=' 吴二 ' 5. SELECT Y.Sn,Y.Age? (3) 查询选修了数学课的学生姓名 1. SELECT Cno FROM Course WHERE Cn='Maths' 2. SELECT Sno FROM Sc WHERE Cno='C1' 3. SELECT Sn FROM Student WHERE Sno IN ('S1','S2','S6','S3','S4') 4. SELECT Sn FROM Student WHERE Sno IN (SELECT Sno FROM Sc WHERE Cno='C1') 5. SELECT Sn FROM Student WHERE Sno IN (SELECT Sno FROM Sc WHERE Cno IN(SELECT Cno FROM Course WHERE Cn='Maths')) 6. VFP6 不能嵌套两层?! 7. SELECT Sn FROM Student WHERE Sno IN (SELECT Sno FROM Sc,Course WHERE Sc.Cno=Course.Cno AND Cn='Maths') 8. SELECT Sn FROM Student,Sc,Course WHERE Cn='Maths' AND Student.Sno=Sc.Sno AND Sc.Cno=Course.Cno (4) 查询没有选修 C2 课程的学生姓名 1. SELECT Sn FROM Student WHERE Sno NOT IN (SELECT Sno FROM Sc WHERE Cno='C2') 3) 带有比较运算符的子查询 (1) 查询和 吴二 同岁的学生

6 1. SELECT Sn,Age FROM Student WHERE Age IN (SELECT Age FROM Student WHERE Sn= 吴二 ) 2. SELECT Sn,Age FROM Student WHERE Age =(SELECT Age FROM Student WHERE Sn= 吴二 ) (3) 查询选修了数学课的学生姓名 1. SELECT Sn FROM Student WHERE Sno IN (SELECT Sno FROM Sc WHERE Cno IN(SELECT Cno FROM Course WHERE Cn='Maths')) 2. VFP6 不 3. 能嵌套两层! 4. SELECT Sn FROM Student WHERE Sno IN (SELECT Sno FROM Sc WHERE Cno =(SELECT Cno FROM Course WHERE Cn='Maths')) 4) 带有 ANY 或 ALL 谓词的子查询 (1) 查询选修了 C2 的学生姓名 1. SELECT Sn FROM Student,Sc WHERE Student.Sno=Sc.Sno AND Cno='C2' (1) {16345} 2. SELECT Sn FROM Student WHERE Sno IN (SELECT Sno FROM Sc WHERE Cno='C2') (1) {13456} 3. SELECT Sn FROM Student WHERE Sno=ANY(SELECT Sno FROM Sc WHERE Cno='C2') (1) {13456} (3) 查询选修了 C2 的学生中成绩最高的学号 1. SELECT Sno,MAX(G) FROM Sc WHERE Sno IN (SELECT Sno FROM Sc WHERE Cno='C2') 2. SELECT Sno FROM Sc WHERE Cno='C2' AND G >= (SELECT MAX(G) FROM Sc WHERE Cno='C2') 3. SELECT Sno FROM Sc WHERE Cno='C2' AND G>= ALL(SELECT G FROM Sc WHERE Cno='C2') 4. SELECT X.Sno,MAX(X.G) FROM Sc X,Sc Y WHERE X.Sno=Y.Sno AND Y.Cno='C2' (4) ANY,ALL 与集函数的对应关系 5) 带有 EXISTS 谓词的子查询 (1) 查询选修了 C2 的学生姓名 1. SELECT Sn FROM Student,Sc WHERE Student.Sno=Sc.Sno AND Cno='C2' 2. SELECT Sn FROM Student WHERE Sno=ANY(SELECT Sno FROM Sc WHERE Cno='C2') 3. SELECT Sn FROM Student WHERE EXISTS (SELECT * FROM Sc WHERE Cno='C2') 4. SELECT Sn FROM Student WHERE EXISTS (SELECT * FROM Sc WHERE Cno='C2' AND Sno=Student.Sno) (3) 查询没有选修 C2 课程的学生姓名 (4) 1. SELECT Sn FROM Student WHERE NOT EXISTS (SELECT * FROM Sc WHERE Cno='C2' AND Sno=Student.Sno) 5. 并交差集合查询 1) 概述 (1) 并操作 UNION (2) 交操作 INTERSECT (3) 差操作 MINUS 2) 查询选修了 C2 或 C3 课程的学生 (1) SELECT Sno,Cno FROM Sc WHERE Cno='C2'

7 (2) SELECT Sno,Cno FROM Sc WHERE Cno='C3' (3) SELECT Sno,Cno FROM Sc WHERE Cno='C2' UNION SELECT Sno,Cno FROM Sc WHERE Cno='C3' (4) SELECT Sno FROM Sc WHERE Cno='C2' UNION SELECT Sno FROM Sc WHERE Cno='C3' (5) SELECT Sno,Cno FROM Sc WHERE Cno='C2' OR Cno='C3' 3) 将 Student 和 Student1 两表合并 (1) SELECT Sno,Sn,Sex FROM Student UNION SELECT Sno,Sn,Sex FROM Student1 4) 差 交操作 (1) 标准 SQL 中没有直接提供集合的差和交操作,(3) 但可以用其他方法实现 5) 查询至少选修了 C2 和 C3 的学生 (1) SELECT Sno FROM Sc WHERE Cno='C2' AND Sno IN (SELECT Sno FROM Sc WHERE Cno='C3') 6. SQL 查询小结 1) SELECT 查询的一般格式 (1) SELECT [ALL DISTINCT] < 目标 (2) 列表表达式 > [ 别名 (3) ] [, < 目标 (4) 列表表达式 > [ 别名 (5) ]] FROM < 表 / 视图 > [ 别名 (6) ][,< 表 / 视图 > [ 别名 (7) ]] [WHERE < 条件表达式 >] [GROUP BY < 列名 (8) 1>] [HAVING < 条件 >]] [ORDER BY < 列名 (9) 2> [ASC DESCENDING]] 2) SELECT 中 < 目标列表达式 > 的格式 (1) * (2) < 表名 (3) >.* (4) < 集函数 > ([DISTINCT ALL] *) 1. 集函数 :SUM AVG COUNT MAX MIN (5) [< 表名 (6).]< 属性列名 (7) 表达式 > [, [< 表名 (8) >.]< 属性列名 (9) 表达式 >] 4) WHERE 中 < 条件表达式 > 的格式 (1) < 属性列名 (2) > q < 属性列名 (3) > < 常量 > [ANY/ALL] (SELECT 语句 ) (4) < 属性列名 (5) > [NOT] BETWEEN < 属性列名 (6) > < 常量 > (SELECT 语句 ) AND < 属性列名 (7) > < 常量 > [ANY/ALL] (SELECT 语句 ) (8) < 属性列名 (9) > [NOT] IN (< 值 1> [,< 值 2> ]) (SELECT 语句 ) (10) < 属性列名 (11) > [NOT] LIKE < 匹配串 > (12) < 属性列名 (13) > IS [NOT] NULL (14) [NOT] EXISTS (SELECT 语句 ) (15) < 条件表达式 > AND OR < 条件表达式 > (AND OR < 条件表达式 > ) 3 SQL 数据定义功能 1. 概述 1) SQL DDL(Data Definition Language) 语言 2) 定义和撤消的数据对象 (1) 用户 (2) 基本表 (3) 视图 (4) 索引 2. 定义用户 1) VFP5/6 不支持

8 3) 建立数据库用户 CREATE USER (1) 格式 :CREATE USER < 用户名 (2) > IDENTIFIED BY < 口令 > (3) 例子 :CREATE USER zhang IDENTIFIED BY 0ffice2K 4) 更改用户口令 ALTER USER (1) 格式 :ALTER USER < 用户名 (2) > IDENTIFIED BY < 口令 > (3) 例子 :ALTER USER zhang IDENTIFIED BY Windows2K 5) 删除用户 DROP USER (1) 格式 :DROP USER < 用户名 (2) > (3) 例子 :DROP USER zhang 3. 定义基本表 1) 定义基本表 CREATE TABLE (1) 格式 1. CREATE TABLE < 表名 2. > (< 列名 3. > < 数据类型 > [ 列级完整性约束条件 ][,< 列名 4. > < 数 据类型 > [ 列级完整性约束条件 ] ][,< 表级完整性约束条件 >] (2) 数据类型 (VFP) (3) 列级完整性约束条件 1. NULL NOT NULL 2. CHECK Expression [ERROR MessageText] 3. DEFAULT Expression 4. PRIMARY KEY UNIQUE 5. REFERENCES TableName [TAG TagName] 6. FOREIGN KEY eexpression4 TAG TagName4 [NODUP (4) 例子 1. CREATE TABLE Student2 (Sno CHAR(10) NOT NULL UNIQUE,Sn CHAR(10), Sex CHAR(2) DEFAULT M, Age NUMBER(4), Dept CHAR(10)) 2. CREATE TABLE salesman (SalesID c(6) PRIMARY KEY, SaleName C(20)) 3. CREATE TABLE customer ; (SalesID c(6), ; CustId i PRIMARY KEY, CustName c(20) UNIQUE, SalesBranch c(3), FOREIGN KEY SalesId TAG SalesId REFERENCES salesman) 4. CREATE TABLE orders ; (OrderId i PRIMARY KEY, ; CustId i REFERENCES customer TAG CustId, ; OrderAmt y(4), ; OrderQty i DEFAULT 10 ; CHECK (OrderQty > 9) ERROR "Order Quantity must be at least 10", ; DiscPercent n(6,2) NULL DEFAULT.NULL., ; CHECK (OrderAmt > 0) ERROR "Order Amount Must be > 0" ) 2] 修改基本表 ALTER TABLE (1) 修改内容 1. 增加新的属性 2. 修改原有的列定义 3. 修改完整性约束条件 (2) 格式

9 1. ALTER TABLE < 表名 2. > [ADD < 新列名 3. > < 数据类型 > [ 完整性约束 ]][DROP < 完整性 约束 >][ALTER < 列名 4. >< 数据类型 >] (3) 例子 1. ALTER TABLE customer ADD COLUMN fax c(20) NULL 2. ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id 3. ALTER TABLE customer ALTER COLUMN cust_id c(5) PRIMARY KEY 4. ALTER TABLE orders ALTER COLUMN orderqty SET CHECK orderqty >= 0 ERROR "Quantities must be non-negative" 5. ALTER TABLE orders ADD FOREIGN KEY custid TAG cust_id REFERENCES customer 6. ALTER TABLE orders ALTER COLUMN orderqty DROP CHECK 7. ALTER TABLE orders DROP FOREIGN KEY TAG cust_id SAVE 8. ALTER TABLE customer ADD COLUMN fax2 c(20) NOT NULL 9. ALTER TABLE customer ALTER COLUMN fax2 NULL ALTER COLUMN fax2 SET DEFAULT.NULL. 10. ALTER TABLE customer DROP COLUMN fax2 3) 重命名基本表 RENAME TABLE (1) 格式 1. RENAME TABLE < 旧表名 2. > TO < 新表名 3. > (2) 例子 1. RENAME TABLE Student2 TO Stud2 2. RENAME Student2 TO Stud2 4) 删除基本表 DROP TABLE (1) 删除什么 1. 基本表的结构 2. 数据 3. 索引 4. 不删视图, 但视图无法使用 (2) 格式 1. DROP TABLE < 表名 2. > (3) 例子 1. DROP TABLE Stud2 4. 定义视图 1) 什么是视图 (1) 虚表 (2) 从一个或几个基本表 ( 或视图 ) 导出的表 (3) 用户的外视图由基本表和视图组成 (4) 只保存视图的定义, 不存放视图的数据 (5) 以多种角度观察数据库中的数据 (6) 通过视图这样的窗口, 看到数据库中用户感兴趣的数据 2) 视图的操作 (1) 定义 (2) 查询 (3) 更新 (4) 删除 (5) 在上面再定义视图 3) 视图的优点 ( 用途 ) (1) 简化用户的操作 1. 能够为复杂的查询构造视图 2. 能够隐藏数据的复杂性 3. 从而有效地简化查询操作 (2) 以多种角度看待同一数据 1. 使不同目的的用户共享同一个数据库

10 4. 减少冗余 (4) 提供了一定的逻辑独立性 1. 通过构造视图,2. 能够在用户和应用程序与实际的基本表之间提供更好的数据独立性 (5) 安全保护机密数据 1. 能够将对数据库的访问限制在一定的范围内 2. 有利于数据的保密 4) 定义视图 CREATE SQL VIEW (1) 格式 1. CREATE SQL VIEW < 视图名 2. > [(< 视图列的列表 >)] AS < 查询块 / 子查询 > (2) 说明 1. < 查询块 > (1) 视图由查询块的查询结果来定义 (2) 任意复杂的 SELECT 语句 (3) 查询块不许排序 (ORDER BY) 2. < 视图列的列表 > (1) 省略 1. 包含查询块的所有字段 (2) 指定 1. 有公共列名时需指定列名 2. 有表达式或库函数时需指定列名 3. 启动新列名 (3) 例子 1. 创建一个有关计算机系学生情况的视图 CS_S (1) CREATE SQL VIEW CS_S AS SELECT Sno,Sn,Age,Sex FROM Student WHERE Dept= CS 2. 创建一个有关学生成绩情况的视图 S_SC_C (1) CREATE SQL VIEW S_SC_C AS SELECT Sn,Cn,G FROM Student,Course,Sc WHERE Student.Sno=Sc.Sno AND Sc.Cno=Course.Cno (2) VFP 不 (3) 能指 (4) 定视图列名 (5)?! 3. 创建一个有关学生平均成绩的视图 AVGG (1) CREATE SQL VIEW AVGG AS SELECT Sno,AVG(G) FROM Sc GROUP BY Sno 5) 查询视图 (1) 查找视图 CS_S 中小于 20 岁者 1. SELECT * FROM CS_S WHERE Age<20 2. 实际操作 :SELECT Sno,Sn,Age,Sex FROM Student WHERE Dept= CS AND Age<20 6) 重命名视图 RENAME VIEW (1) 格式 :RENAME VIEW < 旧视图名 (2) > TO < 新视图名 (3) > 1. RENAME < 旧名 2. > TO < 新名 3. >: 数据库 DBS 换文件名 (4) 例子 :RENAME VIEW CS_S TO S_CS 7) 删除视图 DROP VIEW (1) 格式 :DROP VIEW < 视图名 (2) > (3) 例子 :DROP VIEW S_CS (4) 说明 1. 视图定义从数据字典中撤消 2. 基本表的数据不受影响

11 3. 使得在上定义的视图失效 5. 定义索引 1) VFP 不支持?! 2) 索引的作用 (1) 提供多个存取路径 (2) 提高存取速度 (3) 保证行的唯一性 3) 建立索引 CREATE INDEX (1) 格式 1. CREATE [UNIQUE] INDEX < 索引名 2. > ON < 表名 3. > (< 列名 4. >[{,< 列名 5. >}]) (2) 例子 1. CREATE UNIQUE INDEX SCI ON Sc(Sno,Cno) (3) 说明 1. 一个表可以有任意多个索引 2. 索引会影响系统的开销 ( 空间 速度 ) 3. 索引由系统自动使用和维护 4. 先录数据后建索引 5. 要对 WHERE 子句用到的列名 6. 建立索引 7. 对索引列尽量定义为 NOT NULL8. 使用唯一索引保证列值的唯一性 5) 删除索引 DROP INDEX (1) DROP INDEX < 索引名 (2) > 4 SQL 数据操纵 ( 更新 ) 功能 1. 概述 1) 数据存储操作 2) SQL DML(Data Manipulation Language) 语句 2. 插入数据 INSERT 1) 格式 (1) INSERT INTO < 表名 (2) >[(< 列名 (3) 1>[{,< 列名 (4) 2>}])] VALUES (< 值 1>[{,< 值 2>}]) 2) 说明 (1) 列名 (2) 顺序不 (3) 一定与表结构一致 (4) 列表名 (5) 与 VALUE 值一一对应 (6) 空值用 NULL 表示 3) 例子 (1) 插入单个元组 ( 一个记录 ) 1. INSERT INTO Student VALUES( S11, lin, M,18, CS ) (2) 插入单个元组的部分数据值 1. INSERT INTO Sc(Sno,Cno) VALUES( S11, C4 ) (3) 插入子查询结果 ( 多行记录 ) 1. INSERT INTO Sc(Sno) SELECT Sno FROM Student WHERE Dept= CS 3. 删除数据 DELETE 1) 格式 (1) DELETE FROM < 表名 (2) > [WHERE < 条件 >] 2) 说明 (1) 省略 WHERE 则删除表中的全部元组 (2) 只删表的数据, 不删表的定义 (3) 注意保证数据的一致性 3) 例子 (1) 删除一个元组的值 ( 一行记录 )

12 1. DELETE FROM Student WHERE Sno= S11 (2) 删除多个元组的值 ( 多行记录 ) 1. DELETE FROM Sc WHERE G<60 2. DELETE FROM Sc (3) 带子查询的删除语句 1. DELETE FROM Sc WHERE Sno IN (SELECT Sno FROM Student WHERE Sn= lin ) 4. 修改 ( 更新 ) 数据 UPDATE 1) 格式 (1) UPDATE < 表名 (2) > SET < 列名 (3) > = < 表达式 >[,< 列名 (4) > = < 表达式 >] [WHERE < 条件 >] 2) 说明 (1) 表达式可为具体值 计算结果 子查询 (2) 省略 WHERE 则修改所有元组 (3) 注意保证数据的一致性 3) 例子 (1) 修改一个元组的某些列值 1. UPDATE Student SET Age=20 WHERE Sno= S1 (2) 修改多个元组的值 ( 多行 ) 1. UPDATE Student SET Age=Age+1 2. UPDATE Emp SET Salary=2*Salary WHERE Job= PROGRAMMER AND Salary <=3000 (3) 带子查询的修改语句 1. UPDATE Sc SET G=0 WHERE CS =(SELECT Dept FROM Student WHERE Student.Sno=Sc.Sno) 2. UPDATE Emp SET Salary=(SELECT 1.5*AVG(Salary) FROM Emp) (4) 修改操作与数据库的一致性 1. UPDATE Student SET Sno= WHERE Sno= UPDATE Sc SET Sno= WHERE Sno= SQL 数据控制功能 1. 概述 1) SQL DCL(Data Control Language) 语句 2) 数据保护 (1) 安全性控制 (2) 完整性控制 (3) 并发控制 (4) 数据恢复 3) SQL 数据控制功能 (1) 管理数据库用户 (2) 控制用户权限的使用 (3) 控制权限传递 4) VFP 不支持 2. 权限和角色 1) 权限 ( 特权 ) (1) 新用户必须授予权限 (2) 限定用户的操作及操作的数据 (3) 将用户的操作限定在指 (4) 定的范围内 (5) 禁止用户越权非法操作 (6) 通过为用户设置权限来保证数据的安全 2) 系统权限

13 (1) 用户操作数据库系统的权力,(2) 由 DBA 授予 3) 对象权限 (1) 用户操作数据库对象 ( 基本表 视图 ) 的权力,(2) 由对象所有者授予 4) 角色 (1) 多种权限的集合 (2) 可授予用户或其他角色 (3) 授予了角色代表授予了该角色所代表的全部权限 (4) 可避免权限的多次授予,(5) 简化权限的管理 5) 预定义角色 (1) CONNECT: 拥有登录数据库的权限 (2) RESOURCE: 拥有操作数据的权限 (3) DBA: 拥有全部权限 3. 权限与角色的授予 GRANT 1) 格式 (1) GRANT < 系统权限 > < 角色 >[{,< 系统权限 > < 角色 >}] TO < 用户名 (2) > < 角色 > PUBLIC [{,< 用户名 (3) > < 角色 >}] [WITH ADMIN OPTION] (4) GRANT ALL < 对象权限 >[( 列名 (5) [{, 列名 (6) }])][,< 对象权限 >[( 列名 (7) [{, 列名 (8) }])]] ON < 数据库对象名 (9) > TO < 用户名 (10) > < 角色 > PUBLIC [{,< 用户名 (11) > < 角色 >}] [WITH GRANT OPTION] 2) 说明 (1) PUBLIC: 数据库中的全部用户 (2) < 对象权限 > 1. SELECT2. INSERT3. DELETE4. ALTER5. INDEX6. UPDATE (3) < 数据库对象名 > 1. 基本表 2. 视图 (4) [WITH ADMIN OPTION]: 允许权限或角色的传递 (6) [WITH GRANT OPTION] 1. 允许传递 3) 例子 (1) 授予 CONNECT 角色所代表的权限 1. GRANT CONNECT TO U1 (2) 把 CREATE TABLE 权限授予自定义角色 CREATE_TABLE 1. GRANT CREATE TABLE TO CREATE_TABLE (3) 将角色 CREATE_TABLE 所代表的权限授予全部用户 1. GRANT CREATE_TABLE TO PUBLIC (4) 把查询 Student 表的权限授予用户 U2 1. GRANT SELECT ON TABLE Student TO U2 (5) 将表 Student 的插入和修改学号的权限授予用户 U3 和 U4 1. GRANT INSERT,UPDATE(Sno) ON TABLE Student TO U3,U4 (6) 将表 Course 的所有权限授予 U5,(7) 并允许传递 1. GRANT ALL ON TABLE Course TO U5 WITH GRANT OPTION (8) 把对表 Sc 的查询权限授予所有用户 1. GRANT SELECT ON TABLE Sc TO PUBLIC (9) DBA 把在数据库 Scm 中建表的权限授予用户 U6 1. GRANT CREATETAB ON DATABASE Scm TO U6

14 4. 系统权限与角色的收回 REVOKE 1) 格式 (1) REVOKE < 系统权限 > < 角色 >[{,< 系统权限 > < 角色 >}] FROM < 用户名 (2) > < 角色 > PUBLIC[{,< 用户名 (3) > < 角色 >}] (4) REVOKE ALL < 对象权限 >[{,< 对象权限 >}] ON < 数据库对象名 (5) > FROM < 用户名 (6) > < 角色 > PUBLIC [{,< 用户名 (7) > < 角色 >}] 2) 例子 (1) 收回用户 Lin 的 CREATE TABLE 权限 1. REVOKE CREATE TABLE FROM Lin (2) 收回用户 U6 对表 Student 的修改权限 1. REVOKE UPDATE ON TABLE Student FROM U6 (3) 收回所有用户对表 Sc 的查询权限 1. REVOKE SELECT ON TABLE Sc FROM PUBLIC