CHAPTER 3: RELATIONAL DATABASE LANGUAGE: SQL

Size: px

Start display at page:

Download "CHAPTER 3: RELATIONAL DATABASE LANGUAGE: SQL"

丞陈
5 years ago
Views:

1 第三章关系数据库标准语言 SQL 2012/3/13

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

3 关系数据库由来 (1) 系统而严格地提出关系模型的是美国 IBM 公司的 E.F.Codd 1923 年 8 月 19 日生于英格兰中部, 当过兵, 教过书, 在 IBM 做研究 1948 年在牛津大学获数学学士和硕士学位, 之后到美国求职,13 年后重返大学, 在密歇根大学进修计算机与通信,1965 年获博士学位 E.F.Codd 本性是个数学家, 他最大的愿望是为数据库建立一个优美的数学模型关系数据库之父 1969: Edgar F. "Ted" Codd invents the relational database.

4 关系数据库由来 (2) E.F.Codd, A Relational Model of Data for Large Shared Data Banks, Communication of the ACM,1970,6 5 pages ACM 在 1983 年把该文列为从 1958 年以来四分之一世纪中具有里程碑式意义的 25 篇论文之一关系模型简单明了, 有坚实的数学基础, 即关系代数. 从关系代数的基础推演出了一套关系数据库理论, 即范式, 可以检查数据库是否有冗余和不一致性等. 定义了一系列通用的数据基本操作, 原来在层次网状数据库中的复杂操作变的得逻辑简明扼要

5 关系数据库由来 (3) 两个开创性的原型系统 1974, IBM System R => SQL UC Berkely, Ingres => QUEL 两系统双双获得 ACM 的 1988 年软件系统奖

6 关系数据库由来 (4) The story from IBM 1974, IBM 完成 System R 原型 ,IBM 推出第一个关系数据库产品 IBM 产品化步伐缓慢的三个原因 IBM 重视信誉, 重视质量, 尽量减少故障 IBM 是个大公司, 官僚体系庞大 IBM 内部已经有层次数据库产品, 相关人员不积极, 甚至反对错误的技术路线 :Project Eagle(1980), 试图基于 IMS 建立关系数据库

7 关系数据库由来 (5) The story from Oracle Oracle 前身叫 SDL, 由 Larry Ellison 和另两个编程人员在 1977 创办开发自己的拳头产品, 在市场上大量销售开发关系数据库产品做事方法三个要点第一, 不做研究, 只做产品开发第二, 以尽快推出产品为第一目标第三, 产品要能够在销量较大的平台上运行一个数据库两个客户三种操作系统五个人 SQL: 86 年成为业界标准

关系数据库由来 (6) The story from Ingres INGRES ( Interactive Graphics Retrieval System) project at the University of California at Berkely at about the same time as

8 关系数据库由来 (6) The story from Ingres INGRES ( Interactive Graphics Retrieval System) project at the University of California at Berkely at about the same time as System R Led to an academic version of INGRES Spawned the commercial products INGRES (now INGRES II from Computer Associates) Ingres 创始人不愿将 QUEL 语言交给标准委员会 M. Stonebracker

9 关系数据库由来 (7) 1970 年以后,E. F. Codd 继续完善和发展关系理论 1972, 提出关系代数和关系演算, 定义了关系的并交差投影选择连接的各种基本运算创办了一个研究所 :The Relational Institute 和一个公司 Codd & Associations 1990 年, 出版了专著 The Relational Model for Database Management: Version 年 11 月 9 日在洛杉矶在召开的 ACM 年会上, 获图灵奖, 演讲题目为 Relational Database: A Practical Foundation for Productivity

10 关系数据库由来 (8) 今天国际上主流数据库产品都采用了关系数据库技术典型实验系统 System R University INGRES 典型商用系统 ORACLE SYBASE INFORMIX DB2 INGRES SQL Server

11 Ingres Tree Sybase Microsoft's DBMS Bob Epstein, One of his key lieutenants (and successors) Ingres Jerry Held Tandem Oracle 1974 Mike POSTGRES PostgreSQL 1992,Illustra Informix IBM CTO

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

13 关系数据库标准语言 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, Sybase, Ingres, Informix, DB2.

14 关系数据库标准语言 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.

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

16 Major Components of SQL (2) Embedded DML (Embedded SQL) which supports the manipulation of data from a program in a high-level programming language.

17 表 3.1 SQL 语言的动词 SQL 功能动词数据查询 SELECT 数据定义 CREATE,DROP,ALTER 数据操纵 INSERT,UPDATE,DELETE 数据控制 GRANT,REVOKE

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

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

20 三级模式举例外模式外模式 VIEW EMP, 2 EMP# CHAR(6) 2 SAL FIXED BIN(31) 概念模式 EMPLOYEE VIEW 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

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

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

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

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

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

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

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

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

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

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

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

32 完整性定义 Integrity constraints are rules or regulations imposed on data to ensure their integrity and correctness. Constraints on individual columns. Constraints on a table. Constraints on multiple tables. 详见第 5 章

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

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

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

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

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

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

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

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

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

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

43 Truncate a Table truncate table Departments; It removes all rows from a table and release the storage space used by the table. delete from Departments; It removes all rows from a table but does not release the storage space used by the table.

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

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

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

47 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 n values R1 R5 R3 R6 R9 R14 R17 R21 R25 The length of these chains impacts performance

48 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 nonclustered indexes per table. Records Records

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

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

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

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

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

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

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

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

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

58 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 a schema of a different user if appropriate access privilege is granted.

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

60 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

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

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

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

64 建表语句的处理 DDL 语法分析执行建立关系模式建立存储表的空间数据索引数据字典

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

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

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

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

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

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

71 第三章习题 1-4

72 习题讲评

数据库系统概论

信息学院 2015 级,2017-2~6, 教 2221 数据库系统概论孟小峰中国人民大学 xfmeng@ruc.edu.cn http://idke.ruc.edu.cn 数据库系统概论 An Introduction to Database Systems 第三章 SQL 语言 ( 之 DDL 部分 ) 2017, 3, 14 关系代数上节课基本运算 : 选择投影并差卡氏积导出运算