第 3 章 关系数据库语言 SQL 2.DML 3.DCL( 第四部分） 4 。嵌入式使用. 4.2 3 、 DML: 插入 / 修改 / 删除记录 n DML Insert ：插入记录 Delete ：删除记录 Update ：修改记录 Select ：查询记录.

Presentation on theme: "第 3 章 关系数据库语言 SQL 2.DML 3.DCL( 第四部分） 4 。嵌入式使用. 4.2 3 、 DML: 插入 / 修改 / 删除记录 n DML Insert ：插入记录 Delete ：删除记录 Update ：修改记录 Select ：查询记录."— Presentation transcript:

1 第 3 章 关系数据库语言 SQL 2.DML 3.DCL( 第四部分） 4 。嵌入式使用

2 4.2 3 、 DML: 插入 / 修改 / 删除记录 n DML Insert ：插入记录 Delete ：删除记录 Update ：修改记录 Select ：查询记录

3 4.3 1) 、插入新记录到基本表中 n Insert Into ( 列名 1 ，列名 2 ， …… ，列名 n) Values （值 1 ，值 2 ， …… ，值 n ） 例 1 ： Insert Into Student （ S#, Sname, Age, Sex ） Values （ ’s001’ ， ’John’ ， 21 ， ’M’ ） Create Table Student( S# Varchar2(10) Constraint PK Primary Key, Sname Varchar2(20), Age Number(3), Sex Char(1) DEFAULT ’F’ )

4 4.4 （ 1 ） Insert 其它例子 例 2 ： Insert Into Student Values （ ’s002’ ， ’Mike’ ， 21 ， ’M’ ） 如果插入的值与表的列名 精确匹配（顺序，类型） ，则可以省略列名表 例 3 ： Insert Into Student （ s#, sname ） Values （ ’s003’ ， ’Mary’ ） 如果列名没有出现在列表 中，则插入记录时该列自 动以默认值填充，若没有 默认值则设为空 S#SnameAgeSex s003MaryF

5 4.5 （ 2 ）日期数据的插入 例 4 ： Alter Table Student Add birth Date; Insert Into Student Values （ ’s004’ ， ’Rose’, 22, ‘F’, to_date(’11/08/1981’, ‘dd/mm/yyyy’) ） ; Insert Into Student Values （ ’s005’ ， ’Jack’, 22, ‘M’, to_date(’12-08-1981’, ‘dd-mm-yyyy’) ） ; 使用 To_Date 函数插 入日期型

6 4.6 2) 、修改表中的数据 n Update Set ＝ ， ＝ ， …… Where n 将符合 的记录的一个或多个列设置新值

7 4.7 （ 1 ） Update 例子 n 将学生 John 的性别改为 ‘F’ ，年龄改为 23 n 将所有学生的年龄都减 1 岁 例 1 ： Update Student Set sex ＝ ‘F’ ， age ＝ 23 Where sname ＝ ‘John’ 例 2 ： Update Student Set age ＝ age － 1

8 4.8 3) 、删除表中的记录 n Delete From Where n 将符合 的记录从表中删除 例 1 ：从数据库中删除学号为 s001 的学生 Delete From Student Where s# = ‘s001’ 例 2 ：从数据库中删除所有的学生 Delete From Student

9 4.9 4) 、 DML ：查询数据 n SELECT 查询结构 n SELECT 基本查询 n 联接查询 n 嵌套查询 n 查询结果的连接：并、交、差

10 4.10 (1) 、 Select 查询结构 n Select －－指定希望查看的列 From －－指定要查询的表 Where －－指定查询条件 Group By －－指定要分组的列 Having －－指定分组的条件 Order By －－指定如何排序

11 4.11 (2) 、 Select 基本查询 n 查询全部记录：查询全部的学生信息 Select * From Student * 表示所有列 等同于 Select s#, sname, age, sex From Student n 查询特定的列：查询所有学生的学号和姓名 Select s#, sname From Student

12 4.12 (2) 、 Select 基本查询 n 使用别名：查询所有学生的学号和姓名 Select s# AS 学号, sname AS 姓名 From Student 如果别名包含空格，须使用双引号 Select s# AS “Student Number” From Student

13 4.13 (2) 、 Select 基本查询 n 使用表达式：查询所有学生的学号、姓名和 出生年份，返回两列信息，其中一列是 “ 学 号：姓名 ” ，另一列是出生年份 Select s# || “:” || sname AS 学生， 2003 － age AS 出生年份 From Student 字符串表达式 算术表达式 函数表达式 Select sno, to_char(birth, ‘mm-dd-yyyy’) AS birthday From Student Select Count(sno) As 学生人数 From Student

14 4.14 (2) 、 Select 基本查询 n 检索特定的记录：查询 20 岁以上的学生的学 号和姓名 Select s# AS 学号, sname AS 姓名 From Student Where age > 20 无 Where 子句时返回全部的记录 WHERE 子句中的关系运算符 算术比较符： >, =, IN IS NULL 和 IS NOT NULL LIKE EXISTS

15 4.15 (2) 、 Select 基本查询 n IN ：查询 ‘s001’,’s003’,’s006’ 和 ’s008’ 四学生的信息 Select * From Student Where s# IN (‘s001’,’s003’,’s006’,’s008’) n IS [NOT] NULL ：查询缺少年龄数据的学生 Select * From Student Where age IS NULL n LIKE ：查询姓名的第一个字母为 ‘R’ 的学生 Select * From Student Where sname LIKE ‘R%’ % ：任意长度的字符串 _ ：单个字符 查询姓名的第一个字母为 ‘R’ 并且倒数第二个字母为 ‘S’ 的学生 Select * From Student Where sname LIKE ‘R%S_’ n 多个比较式可用 NOT 、 AND 和 OR 连接 Select * From Student Where age IS NULL and sname LIKE ‘R%’

16 4.16 (2) 、 Select 基本查询 n 去除重复记录：查询学生的姓名 Select Distinct sname From Student Distinct 只对记录有效，不针对某个特定列 Select Distinct sname, age From Student n 排序查询结果： 查询所有学生信息并将结果按年龄升序排列 Select * From Student Order By age 将结果按年龄升序排列, 按姓名降序排列 Select * From Student Order By age ASC ， sname DESC ASC 表示升序， DESC 表示降序

17 4.17 (2) 、 Select 基本查询 n 使用聚集函数 Count( 列名 ) ：对一列中的值计数 Count(*) ：计算记录个数 SUM( 列名 ) ：求一列值的总和（数值） AVG ( 列名 ) ：求一列值的平均值 MIN ( 列名 ) ：求一列值的最小值 MAX ( 列名 ) ：求一列值的最大值

18 4.18 (2) 、 Select 基本查询 n 聚集函数例子 求学生的总人数 Select count(*) From student 求选修了课程的学生人数 Select count(distinct s#) From SC 求学生的平均年龄 Select avg(age) as average_age From student n 单独使用聚集函数时（ Select 子句中的列 名都是聚集函数形式），表示对所有记录 进行聚集

19 4.19 (2) 、 Select 基本查询 n 聚集函数和分组操作： 聚集函数： MIN, MAX, SUM, AVG, COUNT 聚集函数一般与分组操作一起使用 γL(R) 查询男生和女生的平均年龄 Select sex, AVG(age) as Average_age From Student Group By sex 除聚集函数外的属性必须全部出现在 Group By 子句中 分组字段 聚集字段

20 4.20 (2) 、 Select 基本查询 n 返回满足特定条件的分组结果 查询不同年龄的学生人数，并返回人数在 5 人以上的结 果 Select age, COUNT(*) as students From Student Group By age Having COUNT(*) > 5 Having 子句中必须聚集函数的比较式，而且聚集函数 的比较式也只能通过 Having 子句给出 Having 中的聚集函数可与 Select 中的不同 查询人数在 60 以上的各个班级的学生平均年龄 Select class, AVG(age) From Student Group By class Having COUNT(*) > 60

21 4.21 (3) 、联接查询 n 一个查询从两个表中联合数据 n 返回两个表中与联接条件相互匹配的记录，不返 回不相匹配的记录 S#SnameAge 01Sa20 02Sb21 03sc21 S#C#Score 01C180 01C285 02C189 Student 表 SC 表 (s# 是外键, c# 是外键 ) c#Cnamecredit C1Ca3 C2Cb4 C3Cc3.5 Course 表

22 4.22 （ A ）联接查询例子 n 查询学生的学号，姓名和所选课程号 Select student.s#, student.sname,sc.c# From student,sc Where student.s# = sc.s# －－联接条件 n 若存在相同的列名，须用表名做前缀 n 查询学生的学号，姓名，所选课程号和课 程名 Select student.s#, student.sname,sc.c#,course.cname From student,sc,course Where student.s# = sc.s# and sc.c# = course.c# －－联接条件

23 4.23 （ B ）使用表别名 n 查询姓名为 ‘sa’ 的学生所选的课程号和课程名 Select b.c#, c.cname From student a, sc b, course c Where a.s#=b.s# and b.c#=c.c# and a.sname=‘sa’ 表别名可以在查询中代替原来的表名使用 n 联接查询与基本查询结合：查询男学生的学号， 姓名和所选的课程数，结果按学号升序排列 Select a.s#, b.sname, count(b.c#) as c_count From student a, sc b Where a.s# = b.s# and a.sex=‘M’ Group By a.s#, b.sname Order By student.s#

24 4.24 4) 、嵌套查询 n 在一个查询语句中嵌套了另一个查询语句 n 无关子查询 n 相关子查询 n 联机视图

25 4.25 （ 1 ）无关子查询举例 n 父查询与子查询相互独立，子查询语句不依 赖父查询中返回的任何记录，可以独立执行 n 查询没有选修课程的所有学生的学号和姓名 Select s#,sname From student Where s# NOT IN ( select distinct s# From sc) 子查询返回选修了课程的学生学号集合，它与外 层的查询无依赖关系，可以单独执行 无关子查询一般与 IN 一起使用，用于返回一个值 列表

26 4.26 （ 2 ）相关子查询举例 n 相关子查询的结果依赖于父查询的返回值 n 查询选修了课程的学生学号和姓名 Select s#, sname From student Where EXISTS (Select * From sc Where sc.s# = student.s#) 相关子查询不可单独执行，依赖于外层查询 EXISTS （子查询）：当子查询返回结果非空时为真 ，否则为假 执行分析：对于 student 的每一行，根据该行的 s# 去 sc 中查找有无匹配记录

27 4.27 （ 3 ）联接视图 n 子查询出现在 From 子句中作为表使用 n 查询只选修了 1 门或 2 门课程的学生学号、姓 名和课程数 Select s#, count_c# From (Select s.s# as s#, count(sc.s#) as count_c# From student s, sc Where s.s#=sc.s# Group by s.s#) SC2, student Where sc2.s# = student.s# and (count_c#=1 OR count_c#=2) n 联机视图可以和其它表一样使用

28 4.28 5 ）、查询结果的连接 n Union 和 Union All n Minus n Intersect

29 4.29 （ 1 ） Union 和 Union All n 查询课程平均成绩在 90 分以上或者年龄小于 20 的学生学号 （ Select s# From student where age 90) SC2 ） n UNION 操作自动去除重复记录 n Union All 操作不去除重复记录

30 4.30 （ 2 ） Minus 操作：差 n 查询未选修课程的学生学号 （ Select s# From Student ） Minus （ Select distinct s# From SC ）

31 4.31 (3)Intersect 操作 n 返回两个查询结果的交集 n 查询课程平均成绩在 90 分以上并且年龄小 于 20 的学生学号 （ Select s# From student where age 90) SC2 ）

32 4.32 视图（ View ） n 视图（ View ）给出了 SQL 数据库的外模式 定义 外模式 概念模式 内模式 关系子模式 关系模式 存储模式 视图 (View) 基本表 文件 SQL 用户 ANSI/SPARC 关系数据库 SQL 数据库

33 4.33 （ 1) 、视图的概念 n 视图是从一个或几个基本表中导出的虚拟 表，其数据没有实际存储，但可以和表一 样操作 视图具有和表一样的逻辑结构定义 但视图没有相应的存储文件，而每个表都有 相应的存储文件

34 4.34 （ 2) 、视图的用途 n 逻辑数据独立性： 用户程序与数据库 结构 n 简化了用户眼中的 数据，使用户可以 集中于所关心的数 据上 n 同一数据库对不同 用户提供不同的数 据呈现方式 n 安全保护 S#SnameAge 001S122 002S221 S#C#score 001C188 002C299 SnoNameCnoscore 视图定义 用户程序 视图视图 表 用户用户

35 4.35 （ 3) 、视图的定义 n Create View （列名 1 ，列名 2 ， … ） AS [With Read Only] n 是一个 Select 语句，指明视图定义在哪些 基本表上，定义了什么内容的数据 n 定义了视图的逻辑结构，与 中 返回的数据相对应 n 若加上 With Read Only 选项表示所定义的视图 是只读视图

36 4.36 （ 3) 、视图的定义 n 例 1 ：定义计算机系的学生视图 Create View cs_view (sno, name, age) As Select s#,sname,age From student Where Dept=‘ 计算机系 ‘ With Read Only Create View cs_view As Select s#,sname,age From student Where Dept=‘ 计算机系 ‘ With Read Only 若省略视图的列名表，则自动获得 Select 查询返回的 列名 cs_view(sno,name,age) cs_view(s#,sname,age)

37 4.37 （ 3) 、视图的定义 n 例 2 ：把每门课程的课程号和平均成绩定义为一 个视图 Create View c_view As Select c#, AVG(score) as avg_score From sc Group By c# Create View c_view (cno, avg_score) As Select c#, AVG(score) From sc Group By c# n 在查询中使用了函数时 若省略列名表，则必须为函数指定别名 若使用了列名表，则可以不指定函数的别名

38 4.38 （ 4) 、视图的查询 n 与基本表的查询相同 n 例：查询平均成绩在 80 分以上的课程号与 课程名 不使用视图 Select a.c#, a. cname From Course a, (select c#,avg(score) as avg_score From sc Group By c#) SC2 Where a.c#=sc2.c# and SC2.avg_score>80 使用前面定义的视图 c_view Select a.c#, a.cname From course a, c_view b Where a.c#=b.c#

39 4.39 （ 5) 、视图的更新 n 与表的更新类似 n 例：将计算机系学号为 ’001’ 的学生的姓名改为 ‘Rose’ Update cs_view Set name=‘Rose’ Where s#=‘001’ 执行时先转换为 student 上的更新语句再执行 n 不是所有视图都是可更新的 基于联接查询的视图不可更新 使用了函数的视图不可更新 使用了分组操作的视图不可更新 只有建立在单个表上而且没有使用函数的视图才是可 更新的

40 4.40 （ 6) 、视图的删除 n Drop View

41 4.41 4 。嵌入式使用 1) 概述 2 ）实例说明

42 4.42 4 。嵌入式使用 1) 概述 SQL 是非过程化语言，可以独立使用，称为交互式使用 （ Interactive SQL ）。 但 ISQL 的功能仅限于数据库操作，缺少数据处理能力。 另一种使用方式是嵌入式（ Embedded SQL ），即将 SQL 嵌入在主语言（ PL ）中。 因为一种是非过程化语言，另一种是过程化语言，必须 解决 4 个匹配问题： （ 1 ）编译器识别问题； （ 2 ）程序与 DBMS 传递数据与消息； （ 3 ）元组集合一个个元组处理； （ 4 ）数据类型转换问题。

43 4.43 4 。嵌入式使用 2 ）实例说明 例 1 ： /* 例题程序 1*/ #include /* 功能：依照用户的提示，向 EMP 表插入记录 */ /* 程序首部：定义主变量 */ EXEC SQL BEGIN DECLARE SECTION; VARCHAR uid[20]; VARCHAR pwd[20]; int empno; VARCHAR ename[15]; VARCHAR job[10]; float sal; int deptno; EXEC SQL END DECLARE SECTION ； EXEC SQL INCLUDE SQLCA;

44 4.44 4 。嵌入式使用 /* 程序体 */ Main( ） { int sret; /*scanf 函数返回码 */ /* 登录 ORACLE*/ strcpy(uid.arr, “SCOTT”); /* 初始化用户名 */ uid.len=strlen(uid.arr); strcpy(pwd.arr, “TIGER”); /* 初始化口令 */ pwd.len=strlen(pwd.arr); EXEC SQL CONNECT :uid IDENTIFIED BY :pwd; printf(“Connected to ORACLE as user : %s\n\n\n”, uid.arr);

45 4.45 4 。嵌入式使用 while(1) { printf(“Enter employee number (or 0 to end):”); sret=scanf(“%d”,&empno); if (sret==EOF ║sret==0║empno==0) break; /* 退出循环 */ printf(“Enter employee name:”); scanf(“%s”,ename.arr); ename.len=strlen=strlen(ename.arr); /* 设置长度 */ printf(“Enter employee’s job:”); scanf(“%s”,job.arr); job.len=strlen(job.arr); /* 设置长度 */

46 4.46 4 。嵌入式使用 printf(“Enter employee salary:”); scanf(“%f”,&sal); printf(“Enter employee deptno:”); scanf(“%d”,&deptno); EXEC SQL INSERT INTO EMP (empno,ename,job,sal,deptno) VALUES(:empno,:ename,:job,:sal,:deptno); EXEC SQL COMMIT WORK; printf(“Employee %s added.\n\n”,ename.arr); } EXEC SQL COMMIT ＷＯＲＫ ＲＥＬＥＡＳＥ；／＊退出数 据库＊／ ｅｘｉｔ（）； ｝

47 4.47 4 。嵌入式使用 例２： ／＊例题程序２＊／ ＃ include stdio.h /* 该程序完成查询多行的功能 */ EXEC SQL BEGIN DECLARE SECTION; VARCHAR uid[20]; VARCHAR pwd[20]; float sal, comm; char ename[11]; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SYS$ORACLE: SQLCA; main( ) { /* 登录 ORACLE*/

48 4.48 4 。嵌入式使用 strcpy(uid.arr, “SCOTT”); /* 初始化用户名 */ uid.len=strlen(uid.arr); strcpy(pwd.arr, “TIGER”); /* 初始化口令 */ pwd.len=strlen(pwd.arr); EXEC SQL WHENEVER SQLERROR STOP; EXEC SQL CONNECT :uid IDENTIFIED BY :pwd; printf(“Connected to ORACLE user :%s\n”, uid.arr); EXEC SQL DECLARE C1 CURSOR FOR SELECT ENAME, SAL, COMM FROM EMP WHERE JOB = ‘SALESMAN’; EXEC SQL OPEN C1; EXEC SQL WHENEVER NOT FOUND STOP; printf(“SALESMAN NAME\t\tSALARY\t\tCOMMISSION\n\n”); for(; ; ) { EXEC SQL FETCH C1 INTO :ename, :sal, :comm; printf(“%---10s\t\t%6.2f\t\t%6.2f\n” ename, sal, comm); }

49 4.49 4 。嵌入式使用 EXEC SQL CLOSE C1; EXEC SQL WHENEVER SQLERROR CONTINUE; /* 避免陷入死巡环 */ EXEC SQL COMMIT WORK RELEASE; /* 退出数据库 */ exit( ); }

50 4.50

51 4.51 作业 n 归纳一下关系代数与 SQL 之间的关系 n 用 SQL 语句完成下列操作： 创建三个表 学生表（学号，姓名，系名，年龄） 成绩表（学号，课程号，成绩） 课程表（课程号，课程名，先行课程号） 其中成绩表的学号和课程号都是外键，课程表的先 行课程号是外键（引用课程表的课程号）

52 4.52 作业（续） n 求年龄在 20 到 22 之间的数学系学生的学号，姓 名和出生年份 n 求不是数学系和计算机系的学生的全部信息 n 求姓王并且缺少课程号为 ’C2’ 的课程成绩的学生 学号和姓名 n 求选修课程超过 3 门的学生学号和姓名 n 求选修人数超过 30 的课程号及学生的最高成绩和 平均成绩 n 求每一门课程的间接先行课（先行课的先行课）

53 4.53 Oracle8i 简介 n 流行的关系数据库管理系统 n Oracle 数据库是数据库对象的集合 基本表、视图、存储过程、触发器、索引等 n 服务器端工具＋客户端工具 服务器端 管理数据库（ Database Administration ） 企业管理器（ Enterprise Manager ） 客户端工具 SQL 编辑、交互工具（ SQL PLUS ） 客户端配置 （ Net8 Assistant ， Net8 Configuration Assistant ）

54 4.54 Oracle 数据库应用结构

55 4.55 进入 SQL PLUS n 开始菜单 程序 Oracle － OraHome81 Application Development »SQL PLUS

56 4.56 登录

57 4.57 退出 n Exit 或 Quit

58 4.58 执行 SQL ：以分号结束

59 4.59 查看表和表结构 n 查看自己定义的所有表名 Select table_name From user_tables; n Describe 命令查看特定表的表结构定义 Describe

60 4.60 上机任务 n 建立教材上的 “ 零件－工程－项目 ” 数据库 的相应表结构 n 设计例子检验 关系数据库的列类型限制 主键约束和实体完整性 参照完整性 自定义完整性 n 用 SQL 实现完成书上的关系代数查询