数据库系统概论

信息学院 2015 级,2017-2~6, 教 2221 数据库系统概论 孟小峰中国人民大学 xfmeng@ruc.edu.cn http://idke.ruc.edu.cn

数据库系统概论 An Introduction to Database Systems 第三章 SQL 语言 ( 之 DDL 部分 ) 2017, 3, 14

关系代数 上节课 基本运算 : 选择 投影 并 差 卡氏积 导出运算 : 连接 并 除 其他 : 外连接 元组关系演算 : 以元组为变量构造谓词 域关系演算 以域为变量构造谓词 关系代数与关系演算的等价

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

关系数据库标准语言 SQL Some interesting facts about SQL: Full name of SQL --- Structural Query Language Old name --- SEQUEL (Structural English QUEry Language) 1974 年由 Boyce 和 Chamberlin 提出 First developed at IBM's San Jose (now Almaden) Research Lab. in 1974. Over 100 SQL-based products in the market. E.g.: Oracle, DB2, SQL Serve, Sybase, Ingres, Informix

关系数据库标准语言 SQL( 续 ) The first SQL standard (SQL-86) was published in 1986 by ANSI. (about 100 pages). SQL1 was also adopted by ISO in 1987. A major revision made in 1989 (SQL-89). The second SQL standard (SQL2) was announced in 1992. It has about 600 pages. The third SQL standard (SQL-99) for ORDB was released in 1999.

Major Components of SQL (1) DDL --- Data Definition Language used to define tables. DML --- Data Manipulation Language used to manipulate data directly. DCL---Data Control Language Authorization Transaction Definition which defines which group of commands should be treated as an atomic command.

Major Components of SQL (2) Interactive SQL( 交互式 SQL) Embedded SQL/Programming SQL ( 嵌入式 SQL, 编程 SQL)

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

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

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

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

数据定义 3.3.1 模式的定义与删除 3.3.2 基本表的定义 删除与修改 3.3.2 索引的建立与删除

用户的创建 DBA 用户 : SQL> create user userid identified by password; SQL> create user Alice identified by a23456 Some Oracle system privileges for a typical user: create chema: allow user to connect to a database create schemas: allow user to create schemas create table: allow user to create tables drop table: allow user to drop tables

模式定义 删除 一 定义模式 (CREATE SCHEMA) 二 删除模式 (DROP SCHEMA)

定义模式 (CREATE SCHEMA) 定义语句格式 CREATE SCHEMA < 模式名 > AUTHORIZATION < 用户名 > 删除语句格式 DROP SCHEMA < 模式名 > CASCADE RESTRICT

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

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

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

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

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

定义基本表 ( 续 ) 完整性约束 实体完整性 : PRIMARY KEY,UNIQUE,NOT NULL 参照完整性 : FOREIGN KEY < 属性 > REFERENCES < 属性 > 用户定义完整性 : CHECK < 条件 >

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

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

例 6 建立课程表 CREATE TABLE Course (Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), CpnoCHAR(4), Ccredit SMALLINT, FOREIGN KEY Cpno REFERENCES Course(Cno) );

例 6 ( 续 ) 建立课程表 CREATE TABLE Course (Cno CHAR(4) NOT NULL UNIQUE, Cname CHAR(40), Cpno CHAR(4) REFERENCES Course(Cno), Ccredit SMALLINT, );

例 7 建立学生选课表 SC CREATE TABLE SC ( Sno CHAR(9), Cno CHAR(4), GradeSMALLINT, PRIMARY KEY (Sno,Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno) FOREIGN KEY (Cno) REFERENCES Course(Cno) );

模式与表 每一个表都隶属一个模式 可以在定义表中说明其所属的模式 CREATE TABLE S-T Student ( ) CREATE TABLE S-T Course( ) CREATE TABLE S-T SC ( )

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

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

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

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

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

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

索引 (Index) An index is a data structure that supports efficient access to data Condition on attribute value index Set of Records Matching records (search key)

B+-Tree A B+-Tree is a balanced tree whose leaves contain a sequence of key-pointer pairs. 96 75 83 107 33 48 69 75 80 81 83 92 95 96 98 103 107 110 120

Hash Index A hash index stores key-value pairs based on a pseudo-randomizing function called a hash function. key 2341 Hash function Hashed key 0 1 values R1 R5 R3 R6 R9 R14 R17 R21 R25 n The length of these chains impacts performance

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

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

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

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

例题 [ 例 ] 为学生 - 课程数据库中的 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);

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

User DDL 处理 Authorization Control Integrity Checker DDL processor Query Processor Transaction Manager Storage Manager Stored Data System Catalog

系统目录 / 数据字典 A repository of information (metadata or data about data) describing the data in the database. Typically stores: Names of authorized users. Names of data items in the database. Constraints on each data item. Data items accessible by a user and the type of access. 43

user_objects user_tables 常见的数据字典表 user_tab_columns user_views user_sequences 数据区 Replace user by all to show all users data dictionary information. 数据字典

数据字典 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.

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

User Schema Each user may create many objects in his/her account: tables, indexes, views, sequences, synonyms, All objects belonging to a user form a user schema. The name of the schema is the user id of the user. A user may access objects belonging to the another schema of a different user if appropriate access privilege is granted.

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)

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

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)

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)

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.

例题 [ 例 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));

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

创建表在数据字典中记录 (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:

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

修改表在数据字典中的记录 User_Objects: object_name object_id object_type created last_ddl_time --------------------------------------------------------------------------------- Students 3000454 table 30/8/05 30/9/05. User_tables: table_name tablespace_name num_rows blocks avg_row_len --------------------------------------------------------------------------------- Students RUC 0?? 30

创建表在数据字典中记录 (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 Students scome DATE 10 yes User_constraint:

第三章习题 1-4 ( 四版, 五版 ) 学习小组 : 精通 SQL SQL 标准