数据库系统概论

Transcription

1 第三章关系数据库标准语言 SQL 2005/9/27

2 第三章关系数据库标准语言 SQL 3.1 SQL 概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据完整性 3.7 嵌入式 SQL ODBC 及 JDBC 触发器

3 关系数据库标准语言 SQL( 续 ) SQL 语言 (Structured Query Language) 1974 年由 Boyce 和 Chamberlin 提出 1975 年 ~1979 年 IBM 公司在 System R 原型系统上实现 是关系数据库的标准语言, 是数据库领域中一个主流语言

4 关系数据库标准语言 SQL( 续 ) SQL 标准 SQL-86 第一个 SQL 标准 由美国国家标准局 (American National Standard Institute, 简称 ANSI) 公布 1987 年国际标准化组织 ( International Organization for Standardization, 简称 ISO) 通过 SQL-89 SQL-92 SQL:1999 ( previously called SQL3) ftp://jerry.ece.umassd.edu/isowg3/

5 第三章关系数据库标准语言 SQL 3.1 SQL 概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式 SQL

6 3.1 SQL 概述 SQL 的特点 SQL 语言的基本概念

7 3.1.1 SQL 的特点 ⒈ 综合统一 ⒉ 高度非过程化 ⒊ 面向集合的操作方式 4. 同一种语法结构提供两种使用方式 5. 语言简捷, 易学易用

8 ⒈ 综合统一 SQL 语言集数据定义语言 DDL 数据操纵语言 DML 数据控制语言 DCL 的功能于一体 非关系模型的数据语言 模式数据定义语言 ( 模式 DDL) 外模式数据定义语言 ( 外模式 DDL 或子模式 DDL) 与数据存储有关的描述语言 (DSDL) 数据操纵语言 (DML)

9 ⒉ 同一种语法结构提供两种使用方式 自含式语言 能够独立地用于联机交互的使用方式 嵌入式语言 能够嵌入到高级语言 ( 例如 C,COBOL, FORTRAN,PL/1) 程序中, 供程序员设计程序时使用 两种不同使用方式下,SQL 语言的语法结构基本一致

10 ⒊ 高度非过程化 用户只需提出 做什么, 而不必指明 怎么做 存取路径的选择以及 SQL 语句的操作过程由系统自动完成 大大减轻了用户负担, 而且有利于提高数据独立性

11 4. 面向集合的操作方式 SQL 语言采用集合操作方式 操作对象 查找结果可以是元组的集合 一次插入 删除 更新操作的对象可以是元组的集合 非关系数据模型采用的是面向记录的操作方式, 操作对象是一条记录

12 5. 语言简捷, 易学易用 SQL 功能 数据查询数据定义 表 3.1 SQL 语言的动词 动词 SELECT CREATE,DROP,ALTER 数据操纵 INSERT, UPDATE, DELETE 数据控制 GRANT,REVOKE

13 3.1 SQL 概述 SQL 的特点 SQL 语言的基本概念

14 3.1.2 SQL 语言的基本概念 SQL 语言支持关系数据库三级模式结构 SQL 视图 1 视图 2 外模式 基本表 1 基本表 2 基本表 3 基本表 4 模式 存储文件 1 存储文件 2 内模式 图 3.1 SQL 对关系数据库模式的支持

15 SQL 语言的基本概念 ( 续 ) 用户用 SQL 语言对基本表和视图进行操作 基本表 本身独立存在的表, 在 SQL 中一个关系就对应一个表 一个 ( 或多个 ) 基本表对应一个存储文件 一个表可以带若干索引, 索引也存放在存储文件中 存储文件 存储文件的逻辑结构组成了关系数据库的内模式 存储文件的物理结构是任意的, 对用户是透明的 视图 从一个或几个基本表或视图导出的表 是虚表, 只存放视图的定义而不存放对应数据

16 第三章关系数据库标准语言 SQL 3.1 SQL 概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式 SQL

17 三级模式举例外模式 (PL/1) 外模式 (COBOL) DCL 1 EMP, 概念模式 2 EMP# CHAR(6) 2 SAL FIXED BIN(31) EMPLOYEE 0 1 EMPC, 02 EMPNO PIC X(6) 02 DEPTNO PIC X(4) EMPLOYEE_NUMBER CHAR (6) DEPARTMENT_NUMBER CHAR (4) SALARY NUMERIC(5) 内模式 STORED_EMP BYTES=20 PREFIX TYPE=BYTE(6),OFFSET=0 EMP# TYPE=BYTE(6),OFFSET=6, INDEX=EMPX DEPT# TYPE=BYTE(4),OFFSET=12 PAY TYPE=FULLWORD,OFFSET=16

18 3.2 数据定义 概述 SQL 的数据定义功能 定义表 ( 模式 ) 创建表 删除表 修改表定义 定义视图 ( 外模式 ) 创建视图 删除视图 间接修改视图定义 : 删除 + 创建 定义索引 ( 内模式 ) 创建索引 删除索引 间接修改索引定义 : 删除 + 创建

19 数据定义 ( 续 ) 操作对象 表 视图 索引 表 3.2 SQL 的数据定义语句 操作方式 创建删除修改 CREATE TA BL E CREATE VIEW CREATE INDEX DROP TA BL E DROP VIEW DROP INDEX ALTER TA BL E

20 数据字典 (Data Dictionary) Data dictionary (system catalog) contains information about all database objects (tables, views, indexes, sequences, etc). In Oracle, the data dictionary has about 300 system tables. Use select * from dict; to find the names of all system tables.

21 Commonly Used Oracle Data Dictionary Tables user_objects user_tables user_tab_columns user_views user_sequences Replace user by all to show all users data dictionary information.

22 user_objects (1) It provides information about database objects owned by the current user. Example database objects include: tables views sequences indexes packages

23 user_objects (2) SQL> desc user_objects Name Null? Type object_name varchar2(128) object_id number object_type varchar2(13) created date last_ddl_time date timestamp varchar2(75) status varchar2(7)

24 user_tables It shows user s table definitions. SQL> desc user_tables Name Null? Type table_name not null varchar2(30) tablespace_name not null varchar2(30) num_rows number blocks number empty_blocks number avg_row_len number

25 user_tab_columns (1) It shows information about attributes of tables owned by the current user. SQL> desc user_tab_columns Name Null? Type table_name not null varchar2(30) column_name not null varchar2(30) data_type varchar2(9) data_length not null number nullable varchar2(1)

26 user_tab_columns (2) Name Null? Type column_id not null number default_length number data_default long num_distinct number low_value raw(32) high_value raw(32)

27 user_views It shows user s view definitions. SQL> desc user_views Name Null? Type view_name not null varchar2(30) text_length number text long text shows the SQL query defining the view.

28 3.2 数据定义 定义 删除与修改基本表 建立与删除索引

29 3.2.1 定义 删除与修改基本表 一 定义基本表 (CREATE TABLE) 二 修改基本表 (ALTER TABLE) 三 删除基本表 (DROP TABLE)

30 一 定义基本表 语句格式 CREATE TABLE < 表名 >(< 列名 > < 数据类型 >[ < 列级完整性约束条件 > ] [,< 列名 > < 数据类型 >[ < 列级完整性约束条件 >] ] ) [,< 表级完整性约束条件 > ]; < 表名 >: 所要定义的基本表的名字 < 列名 >: 组成该表的各个属性 ( 列 ) < 列级完整性约束条件 >: 涉及相应属性列的完整性约束条件 < 表级完整性约束条件 >: 涉及一个或多个属性列的完整性约束条件

31 定义基本表 ( 续 ) 数据类型 不同的数据库系统支持的数据类型不完全相同 IBM DB2 SQL 支持的数据类型 SMALLINT 半字长二进制整数 INTEGER 或 INT 全字长二进制整数 DECIMAL(p[,q]) 压缩十进制数, 共 p 位, 其中小数点后有 q 或 DEC(p[,q]) 位 0 q p 15,q=0 时可以省略不写 FLOAT 双字长浮点数

32 定义基本表 ( 续 ) 数据类型 ( 续 ) CHARTER(n) 或 CHAR(n) 长度为 n 的定长字符 串 VARCHAR(n) 最大长度为 n 的变长字符串 GRAPHIC(n) 长度为 n 的定长图形字符串 VARGRAPHIC(n) 最大长度为 n 的变长图形字符 串 DATE 日期型, 格式为 YYYY-MM-DD TIME 时间型, 格式为 HH.MM.SS TIMESTAMP 日期加时间

33 定义基本表 ( 续 ) 数据类型 ( 续 ) ORACLE SQL 支持的数据类型 VARCHAR2(n) CHAR(n) NUMBER[(p[,q])] DATE LONG RAW 或 LONGRAW

34 例题 [ 例 1] 建立一个 学生 表 Student, 它由学号 Sno 姓名 Sname 性别 Ssex 年龄 Sage 所在系 Sdept 五个属性组成 其中学号不能为空, 值是唯一的, 并且姓名取值也唯一 CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20) UNIQUE, Ssex CHAR(1), Sage INT, Sdept CHAR(15));

35 例题 ( 续 ) Sno Sname Ssex Sage Sdept 字符型字符型字符型整数字符型长度为 5 长度为 20 长度为 1 长度为 15 不能为空值

36 创建表在数据字典中记录 (1) User_Objects: object_name object_id object_type created last_ddl_time Students table 30/8/05 30/8/05. User_tables: table_name tablespace_name num_rows blocks avg_row_len Students RUC 0 25

37 创建表在数据字典中记录 (2) user_tab_columns table_name column_name data_type data_length nullable Students sno char 5 no Students sname char 20 yes Students ssex char 1 yes Students sage int yes Students sdept char 15 yes User_constraint:

38 二 修改基本表 语句格式 ALTER TABLE < 表名 > [ ADD < 新列名 > < 数据类型 > [ 完整性约束 ] ] [ DROP < 完整性约束名 > ] [ MODIFY < 列名 > < 数据类型 > ]; < 表名 >: 要修改的基本表 ADD 子句 : 增加新列和新的完整性约束条件 DROP 子句 : 删除指定的完整性约束条件 MODIFY 子句 : 用于修改列名和数据类型注意 : 只能间接删除属性列 把表中要保留的列及其内容复制到一个新表中 删除原表 再将新表重命名为原表名

39 例题 [ 例 2] 向 Student 表增加 入学时间 列, 其数据类型为日期型 ALTER TABLE Student ADD Scome DATE; 注 : 不论基本表中原来是否已有数据, 新增加的列一律为空值 [ 例 3] 将年龄的数据类型改为半字长整数 ALTER TABLE Student MODIFY Sage SMALLINT; 注 : 修改原有的列定义有可能会破坏已有数据 [ 例 4] 删除学生姓名必须取唯一值的约束 ALTER TABLE Student DROP UNIQUE(Sname);

40 修改表在数据字典中的记录 User_Objects: object_name object_id object_type created last_ddl_time Students table 30/8/05 30/9/05. User_tables: table_name tablespace_name num_rows blocks avg_row_len Students RUC 0?? 20 user_tab_columns table_name column_name data_type data_length nullable Students scome DATE 10 yes

41 三 删除基本表 语句格式 DROP TABLE < 表名 >; 基本表删除 数据 表上的索引都删除 表上的视图往往仍然保留, 但无法引用

42 删除基本表 ( 续 ) 例题 [ 例 5] 删除 Student 表 DROP TABLE Student ;

43 3.2 数据定义 定义 删除与修改基本表 建立与删除索引

44 3.2.2 建立与删除索引 建立索引是加快查询速度的有效手段 建立索引 DBMS 自动建立 PRIMARY KEY UNIQUE DBA 或表的属主 ( 即建立表的人 ) 根据需要建立 维护索引 DBMS 自动完成 使用索引 DBMS 自动选择是否使用索引以及使用哪些索引

45 一 建立索引 语句格式 CREATE [ UNIQUE ] [ CLUSTER ] INDEX < 索引名 > ON < 表名 >(< 列名 >[< 次序 >][,< 列名 >[< 次序 >] ] ); 用 < 表名 > 指定要建索引的基本表名字 索引可以建立在该表的一列或多列上, 各列名之间用逗号分隔 用 < 次序 > 指定索引值的排列次序, 升序 :ASC, 降序 : DESC 缺省值 :ASC UNIQUE 表明此索引的每一个索引值只对应唯一的数据记录 CLUSTER 表示要建立的索引是聚簇索引

46 Clustered / Non clustered index Clustered index (primary index) A clustered index on attribute X co-locates records whose X values are near to one another. Non-clustered index (secondary index) A non clustered index does not constrain table organization. There might be several non-clustered indexes per table. Records Records

47 建立索引 ( 续 ) 聚簇索引 建立聚簇索引后, 基表中数据也需要按指定的聚簇属性值的升序或降序存放 也即聚簇索引的索引项顺序与表中记录的物理顺序一致 例 : CREATE CLUSTER INDEX Stusname ON Student(Sname); 在 Student 表的 Sname( 姓名 ) 列上建立一个聚簇索引, 而且 Student 表中的记录将按照 Sno 值的升序存放

48 建立索引 ( 续 ) 在一个基本表上最多只能建立一个聚簇索引 聚簇索引的用途 : 对于某些类型的查询, 可以提高查询效率 聚簇索引的适用范围 很少对基表进行增删操作 很少对其中的变长列进行修改操作

49 例题 [ 例 6] 为学生 - 课程数据库中的 Student,Course,SC 三个表建立索引 其中 Student 表按学号升序建唯一索引,Course 表按课程号升序建唯一索引,SC 表按学号升序和课程号降序建唯一索引 CREATE UNIQUE INDEX Stusno ON Student(Sno); CREATE UNIQUE INDEX Coucno ON Course(Cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

50 二 删除索引 语句格式 DROP INDEX < 索引名 >; 删除索引时, 系统会同时从数据字典中删去有关该索引的描述 例题 [ 例 7] 删除 Student 表的 Stusname 索引 DROP INDEX Stusname;

51