数据库系统概论

Size: px

Start display at page:

Download "数据库系统概论"

偕计
5 years ago
Views:

1 信息学院 2015 级,2017-2~6, 教 2221 数据库系统概论孟小峰中国人民大学

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

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

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

5 关系数据库标准语言 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 Over 100 SQL-based products in the market. E.g.: Oracle, DB2, SQL Serve, Sybase, Ingres, Informix

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

7 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.

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

9 外模式 (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

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

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

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

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

14 用户的创建 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

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

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

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

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

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

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

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

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

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

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

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

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

27 例 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) );

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

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

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

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

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

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

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

35 索引 (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)

36 B+-Tree A B+-Tree is a balanced tree whose leaves contain a sequence of key-pointer pairs

37 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

38 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

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

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

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

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

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

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

45 系统目录 / 数据字典 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

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

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

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

49 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.

50 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)

51 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

52 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)

53 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)

54 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.

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

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

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

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

59 修改表在数据字典中的记录 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?? 30

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

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

数据库系统概论

数据库系统概论第 3 章关系数据库 SQL 语言孟小峰 xfmeng@ruc.edu.cn 信息学院 2014/3/24 关系代数上节课基本运算 : 选择投影并差卡氏积导出运算 : 连接并除其他 : 外连接元组关系演算 : 以元组为变量构造谓词域关系演算以域为变量构造谓词关系代数与关系演算的等价第三章关系数据库标准语言 SQL 3.1 SQL 概述 3.2 数据定义 3.3 数据查询