slide1
Download
Skip this Video
Download Presentation
第 5 章 关系数据库标准语言 ——SQL

Loading in 2 Seconds...

play fullscreen
1 / 213

第 5 章 关系数据库标准语言 ——SQL - PowerPoint PPT Presentation


  • 105 Views
  • Uploaded on

第 5 章 关系数据库标准语言 ——SQL. 本章概要. SQL 语言发展史 SQL 语言是当前最为成功、应用最为广泛的关系数据库语言,其发展主要经历了以下几个阶段: 1974 年由 CHAMBERLIN 和 BOYEE 提出,当时称为 SEQUEL(STUCTURED ENGLISH QUERY LANGUAGE) ; IBM 公司对其进行了修改,并用于其 SYSTEM R 关系数据库系统中; 1981 年 IBM 推出其商用关系关系数据库 SQL/DS ,并将其名字改为 SQL ,由于 SQL 语言功能强大,简洁易用,因此得到了广泛的使用;

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about '第 5 章 关系数据库标准语言 ——SQL' - tessa


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2
本章概要
  • SQL语言发展史

SQL语言是当前最为成功、应用最为广泛的关系数据库语言,其发展主要经历了以下几个阶段:

    • 1974年由CHAMBERLIN和BOYEE提出,当时称为SEQUEL(STUCTURED ENGLISH QUERY LANGUAGE);
    • IBM公司对其进行了修改,并用于其SYSTEM R关系数据库系统中;
    • 1981年 IBM推出其商用关系关系数据库SQL/DS,并将其名字改为SQL,由于SQL语言功能强大,简洁易用,因此得到了广泛的使用;
    • 今天广泛应用于各种大型数据库,如SYBASE、INFORMIX、 ORACLE、DB2、INGRES等,也用于各种小型数据库,如FOXPRO、ACCESS。
5 1 sql
5.1 SQL的功能及特点

1、了解SQL

  • 字面看SQL只是一个查询语言,而实际上SQL作为一种标准数据库语言,从对数据库的随机查询到数据库的管理和程序设计,SQL都能完成,功能十分丰富.
  • SQL语言是一种关系数据库语言,提供数据的定义、查询、更新和控制等功能。
  • SQL语言不是一个应用程序开发语言,只提供对数据库的操作能力,不能完成屏幕控制、菜单管理、报表生成等功能,可成为应用开发语言的一部分。
  • SQL语言不是一个DBMS,它属于DBMS语言处理程序。
  • 大部分DBMS产品都支持SQL,成为操作数据库的标准语言
5 1 sql1
5.1 SQL的功能及特点

2.SQL的功能

  • SQL是结构化查询语言(Structured Query Language)的缩写,其功能包括四个部分:
    • 数据查询
    • 数据操纵
    • 数据定义
    • 数据控制。
3 sql
3、SQL的特点
  • SQL具有自含式与嵌入式两种形式
    • 交互式SQL:一般DBMS都提供联机交互工具,用户可直接键入SQL命令对数据库进行操作由DBMS来进行解释
    • 嵌入式SQL:能将SQL语句嵌入到高级语言(宿主语言),使应用程序充分利用SQL访问数据库的能力、宿主语言的过程处理能力,一般需要预编译,将嵌入的SQL语句转化为宿主语言编译器能处理的语句
    • SQL的语法结构基本一致
    • SQL 语言简洁、方便实用、功能齐全,已成为目前应用最广的关系数据库语言。
3 sql2
3、SQL的特点
  • SQL支持三级模式结构
    • 一个SQL数据库的总体逻辑结构是基本表(Table)的集合,对应于概念模式
    • SQL数据库的底层存储结构采用文件,一个或几个表对应一个存储文件,以及索引文件。对应内模式
    • 用户所见的数据结构是视图(View),用户可直接操作的表,可为视图或部分基本表。对应外模式
slide8
基本表和视图
    • 基本表(BASE TABLE):是独立存在的表,不是由其它的表导出的表。一个关系对应一个基本表,一个或多个基本表对应一个存储文件。
    • 视图(VIEW):是一个虚拟的表,是从一个或几个基本表导出的表。它本身不独立存在于数据库中,数据库中只存放视图的定义而不存放视图对应的数据,这些数据仍存放在导出视图的基本表中。当基本表中的数据发生变化时,从视图中查询出来的数据也随之改变。
slide9
基本表和视图的例子
    • 例如,学生数据库中有学生基本情况表STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT)
    • 此表为基本表,对应一个存储文件。可以在其基础上定义一个男生基本情况表
    • STUDENT_MALE(SNO,SNAME,SAGE,SDEPT),
      • 它是从STUDENT中选择SSEX=’男’的各个行,然后在SNO,SNAME,SAGE,SDEPT上投影得到的。
      • 在数据库中只存有STUDENT_MALE的定义,而STUDENT_MALE的记录不重复存储。
      • 在用户看来,视图是通过不同路径去看一个实际表,就象一个窗口一样,透过视图可以看到数据库中自己感兴趣的内容。
slide10
SQL

视图1

视图2

外模式

基本表1

基本表2

基本表3

基本表4

模式

存储文件1

存储文件2

内模式

  • SQL语言支持数据库的三级模式结构,如图所示。其中外模式对应于视图和部分基本表,模式对应于基本表,内模式对应于存储文件。

图 SQL语言支持的关系数据库的三级逻辑结构

slide11
SQL的特点
  • SQL语言具有:
    • 数据定义(DEFINITION)
    • 数据查询(QUERY)
    • 数据操纵(MANIPULATION)
    • 数据控制(CONTROL)
  • 下面以SQL SERVER 为例分别介绍其各个功能。各例题中所用的基本表如图所示。
5 2 sql
5.2 SQL数据定义
  • SQL语言使用数据定义语言(DATA DEFINITION LANGUAGE,简称DDL)实现其数据定义功能。
slide13
SQL语句格式的约定符号
  • 语句格式中,<> 中的内容是必须的,是用户自定义语义;
  • []为任选项
  • {}或分隔符|表示必选项,即必选其中之一项
  • [,…N]表示前面得项可以重复多次
slide14
5.2数据定义语
  • 数据表是关系数据库的基本组成单位,它物理地存储于数据库的存储文件中。

CREATE TABLE [<库名.>]<表名>

(<列名> <数据类型> [列级完整性约束条件] [,<列名> <数据类型> [列级完整性约束条件]]

[, …n]

[,<表级完整性约束条件>][, …n])

(1)<表名>是合法标识符,最多可有128个字符,如S,SC,C,不允许重名。

(2)列名(字母开头,可含字母、数字、#、$、_ <=128字符)。 同一表中不许有重名列;

(2)数据类型:见下表;

(3)字段的长度、精度和小数位数;

sql p118
SQL数据类型(P118)
  • 域类型(SQL-92)
    • char(n):固定长度的字符串
    • varchar(n):可变长字符串
    • int:整数
    • smallint:小整数类型
    • numeric(p,d):定点数,小数点左边p位,右边q位
    • real:浮点数
    • double precision:双精度浮点数
    • date:日期(年、月、日)
    • time:时间(小时、分、秒)
    • interval:两个date或time类型数据之间的差
sql server
SQLServer 字段数据类型

1、当用SQL语句定义表时,需要为表中的每一个字段设置一个数据类型,用来指定字段所存放的数据是整数、字符串、货币或是其它类型的数据。

2、SQL SERVER 的数据类型有很多种,分为以下9类:

  • 1. 整数数据类型:依整数数值的范围大小,有BIT, INT , SMALLINT, TINYINT四种。
  • 2. 精确数值类型:用来定义可带小数部分的数字,有NUMERIC和DECIMAL两种。十进制数,共P位,其中小数点后S位。0<=S<=P,S=0时可省略。如:123.0、8000.56
slide17
3. 近似浮点数值数据类型:

当数值的位数太多时,可用此数据类型来取其近似值,用FLOAT和REAL两种。如:1.23E+10

4. 日期时间数据类型:

用来表示日期与时间,依时间范围与精确程度可分为DATETIME与SMALLDATETIME两种。如:1998-06-08 15:30:00

5. 字符串数据类型:

用来表示字符串的字段。包括:CHAR, VARCHAR, TEXT三种,如:“数据库”

6.标记数据类型:

有UNIQUEIDENTIFIER ,TIMESTAMP两种,此数据类型通常系统自动产生,而不是用户输入的,TIMESTAMP记录数据更新的时间戳印,而UNIQUEIDENTIFIER用来识别每一笔数据的唯一性。

slide18
INT

数据类型

-2^31到2^31-1

数据内容与范围

占用的字节

4BYTES

SMALLINT

BIT

-2^15至2^15-1

0, 1, NULL

实际使用1BIT,但会占用1BYTE,若一个数据中有数个BIT字段,则可共占1个BYTE

2BYTES

TINYINT

0至255

1BYTES

  • 各种数据类型的有关规定如下表:
slide19
NUMERIC

-10^38-1至10^38-1

1-9位数使用5BYTES

10-19位数使用9BYTES

20-28位数使用13BYTES

29-38位数使用17BYTES

DECIMAL

-10^38-1至10^38-1

5-17BYTES因长度而异,与NUMERIC相同

FLOAT

-1.79E+306至1.79E+308, 最多可表示53位数

8BYTES

REAL

-3.40E+38到3.40E+38,最多可表示24位数

4BYTES

slide20
DATETIME

1753/1/1至9999/12/31

8BYTES

SMALLDATETIME

1900/1/1至2079/6/6

4BYTES

CHAR

1-8000个字符

1个字符占1B,尾端空白字符保留

VARCHAR

1-8000个字符

1个字符占1B,尾端空白字符删除。

TEXT

2^31-1个字符

1个字符占2B,最大可存储2GB

slide21
字段的长度、精度和小数位数

①字段的长度:指字段所能容纳的最大数据量,但对不同的数据类型来说,长度对字段的意义可能有些不同。

  • 对字符串数据类型而言,长度代表字段所能容纳的字符的数目,因此它会限制用户所能输入的文本长度。
  • 对数值类的数据类型而言,长度则代表字段使用多少个字节来存放数字。。

②精度和小数位数

  • 精度是指数中数字的位数,包括小数点左侧的整数部分和小数点右侧的小数部分;
  • 小数位数则是指数字小数点右侧的位数。
  • 例如:数字12345.678,其精度为8,小数位数为3;
  • 所以只有数值类的数据类型才有必要指定精度和小数位数。
slide22
经常以如下所示的格式来表示数据类型以及它所采用的长度、精度和小数位数,其中的N代表长度,P代表精度,S表示小数位数。
    • BINARY(N) -------- BINARY(10)
    • CHAR(N) -------- CHAR(20)
    • NUMERIC(P,[S]) ------- NUMERIC(8,3)
  • 但有的数据类型的精度与小数位数是固定的,对采用此类数据类型的字段而言,不需设置精度与小数位数,
    • 如:如果某字段采用INT数据类型,其长度固定是4,精度固定是10,小数位数则固定是0,这表示字段将能存放10位数没有小数点的整数。存储大小则是4个字节。
slide23
例3 建立一学生表
    • USE STUDENT
    • CREATE TABLE S

(SNO CHAR(8) ,

SN VARCHAR(20),

AGE INT,

SEX CHAR(2),

DEPT VARCHAR(20));

  • 执行该语句后,便产生了学生基本表的表框架,此表为一个空表。
slide24
3. 定义完整性约束
  • 约束的意义
    • 创建数据表时可以采用约束,如主键、空值的设定,使数据库用户能够根据应用的需要对基本表的定义做出更为精确和详尽的规定。
  • SQL SERVER约束的方式
    • 列约束
    • 是对某一个特定列的约束,包含在列定义中,直接跟在该列的其他定义之后,用空格分隔,不必指定列名;
    • 表约束
    • 与列定义相互独立,不包括在列定义中,通常用于对多个列一起进行约束,定义表约束时必须指出要约束的那些列的名称。完整性约束的基本语法格式为:

[ CONSTRAINT <约束名> ] <约束类型>

  • 约束名:约束不指定名称时,系统会给定一个名称。
slide25
例,建立一个S表,定义SN+SEX为唯一。
  • USE STUDENT

CREATE TABLE S

( SNO CHAR(5),

SN CHAR(8),

SEX CHAR(2),

CONSTRAINT S_UNIQ UNIQUE(SN,SEX));

slide26
USE STUDENT
  • CREATE TABLE S

(SNO CHAR(10) NOT NULL ,

SN VARCHAR(20),

AGE INT,

SEX CHAR(2) DEFAULT '男' ,

DEPT VARCHAR(20));

slide27
约束类型
  • 在定义完整性约束时必须指定完整性约束的类型。
  • 在SQL SERVER中可以定义五种类型的完整性约束,下面分别加以介绍:

(1)NULL/NOT NULL

    • 是否允许该字段的值为NULL。
    • NULL值不是0也不是空白,更不是填入字符串“NULL”,而是表示“不知道”、“不确定”或“没有数据”的意思。
    • 当某一字段的值一定要输入才有意义的时候,则可以设置为NOT NULL。
    • 如主键列就不允许出现空值,否则就失去了唯一标识一条记录的作用
    • 只能用于定义列约束,
    • 其语法格式如下:

[CONSTRAINT <约束名> ][NULL|NOT NULL]

slide28
例3 建立一个S表,对SNO字段进行NOT NULL约束。
    • USE STUDENT
    • CREATE TABLE S

(SNO CHAR(10) CONSTRAINT S_CONS NOT NULL,

SN VARCHAR(20),

AGE INT,

SEX CHAR(2) DEFAULT ’男’ ,

DEPT VARCHAR(20));

  • 当SNO为空上时,系统给出错误信息,无NOT NULL约束时,系统缺省为NULL。
  • 其中S_CONS为指定的约束名称,当约束名称省略时,系统自动产生一个名字。如下列功能同上,只是省略约束名称。
slide29
(2)UNIQUE约束
  • UNIQUE约束用于指明基本表在某一列或多个列的组合上的取值必须唯一。
  • 定义了UNIQUE约束的那些列称为唯一键,系统自动为唯一键建立唯一索引,从而保证了唯一键的唯一性。
  • 唯一键允许为空,但系统为保证其唯一性,最多只可以出现一个NULL值。
  • UNIQUE既可用于列约束,也可用于表约束。
  • UNIQUE用于定义列约束时,其语法格式如下:

[CONSTRAINT <约束名>] UNIQUE

  • 例、建立一个S表,定义SN为唯一键。
    • USE STUDENT
    • CREATE TABLE S

(SNO CHAR(6),

SN CHAR(8) CONSTRAINT SN_UNIQ UNIQUE,

SEX CHAR(2),

AGE NUMERIC(2));

slide30
其中SN_UNIQ为指定的约束名称,约束名称可以省略,
  • 如下例:
    • USE STUDENT
    • CREATE TABLE S

(SNO CHAR(6),

SN CHAR(8) UNIQUE,

SEX CHAR(2),

AGE NUMERIC(2));

  • UNIQUE用于定义表约束时,其语法格式如下:

[CONSTRAINT <约束名>] UNIQUE(<列名>[{,<列名>}])

slide31
例3 建立一个S表,定义SN+SEX为唯一键。
    • USE STUDENT
    • CREATE TABLE S

( SNO CHAR(5),

SN CHAR(8),

SEX CHAR(2),

CONSTRAINT S_UNIQ UNIQUE(SN,SEX));

  • 系统为SN+SEX建立唯一索引,确保同一性别的学生没有重名。

(3)PRIMARY KEY约束

  • PRIMARY KEY约束用于定义基本表的主键,起唯一标识作用,其值不能为NULL,也不能重复,以此来保证实体的完整性。
slide32
PRIMARY KEY与UNIQUE约束类似,通过建立唯一索引来保证基本表在主键列取值的唯一性,但它们之间存在着很大的区别:
    • ①在一个基本表中只能定义一个PRIMARY KEY约束,但可定义多个UNIQUE约束;
    • ②对于指定为PRIMARY KEY的一个列或多个列的组合,其中任何一个列都不能出现空值,而对于UNIQUE所约束的唯一键,则允许为空。
  • 注意:不能为同一个列或一组列既定义UNIQUE约束,又定义PRIMARY KEY约束。
  • PRIMARY KEY既可用于列约束,也可用于表约束。
  • PRIMARY KEY用于定义列约束时,其语法格式如下:

CONSTRAINT <约束名> PRIMARY KEY

slide33
例3 建立一个S表,定义SNO为S的主键
    • USE STUDENT
    • CREATE TABLE S

(SNO CHAR(5) NOT NULL CONSTRAINT S_PRIM PRIMARY KEY,

SN CHAR(8),

AGE NUMERIC(2));

  • PRIMARY KEY用于定义表约束时,即将某些列的组合定义为主键,其语法格式如下:

[CONSTRAINT <约束名>] PRIMARY KEY (<列名>[{<列名>}])

slide34
例3 建立一个SC表,定义SNO+CNO为SC的主键
    • USE STUDENT
    • CREATE TABLE SC

(SNO CHAR(5) NOT NULL,

CNO CHAR(5) NOT NULL,

SCORE NUMERIC(3),

CONSTRAINT SC_PRIM PRIMARY KEY(SNO,CNO));

slide35
(4)FOREIGN KEY约束
  • FOREIGN KEY约束指定某一个列或一组列作为外码,其中,包含外码的表称为从表,包含外部键所引用的主键或唯一键的表称主表。
  • 系统保证从表在外码上的取值要么是主表中某一个主码值,要么取空值。以此保证两个表之间的连接,确保了实体的参照完整性。
  • FOREIGN KEY既可用于列约束,也可用于表约束,
  • 其语法格式为:

[CONSTRAINT <约束名>] FOREIGN KEY REFERENCES <主表名> (<列名>[{<列名>}])

slide36
例建立一个SC表,定义SNO,CNO为SC的外码。
    • USE STUDENT
    • CREATE TABLE SC

(SNO CHAR(5) NOT NULL CONSTRAINT S_FORE FOREIGN KEY REFERENCES S(SNO),

CNO CHAR(5) NOT NULL CONSTRAINT C_FORE FOREIGN KEY REFERENCES C(CNO),

SCORE NUMERIC(3),

CONSTRAINT S_C_PRIM PRIMARY KEY (SNO,CNO));

slide37
(5)CHECK约束
  • CHECK约束用来检查字段值所允许的范围,如,一个字段只能输入整数,而且限定在0-100的整数,以此来保证域的完整性。
  • CHECK既可用于列约束,也可用于表约束,
  • 其语法格式为:

[CONSTRAINT <约束名>] CHECK (<条件>)

  • 例3建立一个SC表,定义SCORE 的取值范围为0到100之间。
    • USE STUDENT
    • CREATE TABLE SC

(SNO CHAR(5),

CNO CHAR(5),

SCORE NUMERIC(5,1) CONSTRAINT SCORE_CHK CHECK(SCORE>=0 AND SCORE <=100));

slide38
例建立包含完整性定义的学生表
  • USE STUDENT
  • CREATE TABLE S

(SNO CHAR(6) CONSTRAINT S_PRIM PRIMARY KEY,

SN CHAR(8) CONSTRAINT SN_CONS NOT NULL,

AGE NUMERIC(2) CONSTRAINT AGE_CONS NOT NULL

CONSTRAINT AGE_CHK CHECK (AGE BETWEEN 15 AND 50),

SEX CHAR(2) DEFAULT '男',

DEPT CHAR(10) CONSTRAINT DEPT_CONS NOT NULL);

slide39
修改基本表
  • 由于应用环境和应用需求的变化,经常需要修改基本表的结构,比如,增加新列和完整性约束、修改原有的列定义和完整性约束等。
  • SQL语言使用ALTER TABLE命令来完成这一功能,有如下三种修改方式:

1. ADD方式

  • 用于增加新列和完整性约束,定义方式同CREATE TABLE语句中的定义方式相同,其语法格式为:

ALTER TABLE <表名> ADD <列定义> | <完整性约束定义>

  • 例3 在S表中增加一个班号列和住址列。
    • USE STUDENT
    • ALTER TABLE S ADD CLASS_NO CHAR(6), ADDRESS CHAR(40)
slide40
注意:使用此方式增加的新列自动填充NULL值,所以不能为增加的新列指定NOT NULL约束 。
  • 例在SC表中增加完整性约束定义,使SCORE在0-100之间。
    • USE STUDENT
    • ALTER TABLE SC

ADD

CONSTRAINT SCORE_CHK CHECK(SCORE BETWEEN 0 AND 100)

slide41
2. ALTER 方式
  • 用于修改某些列,其语法格式为:

ALTER TABLE<表名>

ALTER COLUMN <列名><数据类型>[NULL|NOT NULL]

  • 例把S表中的SNO列加宽到8位字符宽度
    • USE STUDENT
    • ALTER TABLE S

ALTER COLUMN

SNO CHAR(8)

slide42
注意:使用此方式有如下一些限制:

①不能改变列名;

②不能将含有空值的列的定义修改为NOT NULL约束;

③若列中已有数据,则不能减少该列的宽度,也不能改变其数据类型;

④只能修改NULL|NOT NULL约束,其它类型的约束在修改之前必须先删除,然后再重新添加修改过的约束定义。

3.DROP方式

  • 删除完整性约束定义,其语法格式为:

ALTER TABLE<表名>

DROP CONSTRAINT <约束名>

slide43
例删除S表中的AGE_CHK约束
    • USE STUDENT
    • ALTER TABLE S

DROP

CONSTRAINT AGE_CHK

  • 改变基本表的名字
    • 使用RENAME命令,可以改变基本表的名字,其语法格式为:

RENAME <旧表名> TO <新表名>

  • 例将S表的名字更改为STUDENT
    • USE STUDENT
    • RENAME S TO STUDENT
slide44
删除基本表
  • 当某个基本表无用时,可将其删除。
  • 删除后,该表中的数据和在此表上所建的索引都被删除,而建立在该表上的视图不会随之删除,系统将继续保留其定义,但已无法使用。
  • 如果重新恢复该表,这些视图可重新使用。
  • 删除表的语法格式:

DROP TABLE <表名>

  • 例删除表STUDENT
    • USE STUDENT
    • DROP TABLE STUDENT
  • 注意:只能删除自己建立的表,不能删除其他用户所建的表。
slide45
5.2.2 索引的定义与维护

1 索引的作用

    • 在日常生活中我们会经常遇到索引,例如图书目录、词典索引等。
    • 借助索引,人们会很快地找到需要的东西。
    • 索引是数据库随机检索的常用手段,它实际上就是记录的关键字与其相应地址的对应表。
    • 例如,当我们要在本书中查找有关“SQL查询”的内容时,应该先通过目录找到“SQL查询”所对应的页码,然后从该页码中找出所要的信息。这种方法比直接翻阅书的内容要快。
    • 如果把数据库表比作一本书,则表的索引就如书的目录一样,通过索引可大大提高查询速度。
    • 此外,在SQL SERVER中,行的唯一性也是通过建立唯一索引来维护的。
  • 索引的作用可归纳为:
    • 1. 加快查询速度;
    • 2. 保证行的唯一性。
slide46
2 索引的分类

1. 按照索引记录的存放位置可分为聚集索引与非聚集索引

  • 聚集索引:按照索引的字段排列记录,并且依照排好的顺序将记录存储在表中。
  • 非聚集索引:按照索引的字段排列记录,但是排列的结果并不会存储在表中,而是另外存储。

2. 唯一索引的概念

  • 唯一索引表示表中每一个索引值只对应唯一的数据记录,
  • 这与表的PRIMARY KEY的特性类似,因此唯一性索引常用于PRIMARY KEY的字段上,以区别每一笔记录。
  • 当表中有被设置为UNIQUE的字段时,SQL SERVER会自动建立一个非聚集的唯一性索引。
  • 而当表中有PRIMARY KEY的字段时,SQL SERVER会在PRIMARY KEY字段建立一个聚集索引。

3. 复合索引的概念

  • 复合索引是将两个字段或多个字段组合起来建立的索引,而单独的字段允许有重复的值。
slide47
3 建立索引
  • 建立索引的语句是CREATE INDEX,其语法格式为:

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名> [次序] [{,<列名>}] [次序]…)

    • UNIQUE表明建立唯一索引。
    • CLUSTER表示建立聚集索引。
    • 次序用来指定索引值的排列顺序,可为ASC(升序)或DESC(降序),缺省值为ASC。
  • 例为表SC在SNO和CNO上建立唯一索引。
    • USE STUDENT
    • CREATE UNIQUE INDEX SCI ON SC(SNO,CNO)
slide48
执行此命令后,为SC表建立一个索引名为SCI的唯一索引,
  • 此索引为SNO和CNO两列的复合索引,即对SC表中的行先按SNO的递增顺序索引,对于相同的SNO,又按CNO的递增顺序索引。
  • 由于有UNIQUE的限制,所以该索引在(SNO,CNO)组合列的排序上具有唯一性,不存在重复值。
  • 例为教师表T在TN上建立聚集索引。

CREATE CLUSTERED INDEX TI ON T(TN)

  • 执行此命令后,为T表建立一个索引名为TI的聚集索引,T表中的记录将按照TN值的升序存放。
slide49
注意:

1. 改变表中的数据(如增加或删除记录)时,索引将自动更新。

索引建立后,在查询使用该列时,系统将自动使用索引进行查询。

2. 索引数目无限制,但索引越多,更新数据的速度越慢。对于仅用

于查询的表可多建索引,对于数据更新频繁的表则应少建索引。

删除索引

  • 建立索引是为了提高查询速度,但随着索引的增多,数据更新时,系统会花费许多时间来维护索引。这时,应删除不必要的索引。
  • 删除索引的语句是DROP INDEX,其语法格式为:

DROP INDEX 数据表名.索引名

  • 例删除表SC的索引SCI。
    • DROP INDEX SC.SCI
slide50
教学管理系统设计
  • 某学校要开发一教学管理系统,该学校有若干个系,每个系统有许多学生和教师,只有一个系主任,系主任同时也要上课。教师有编号,姓名、年龄和工资等属性,每个教师只在一个系工作;学生有学号,姓名,年龄,性别;课程有编号,课名,学分等属性,一门可由多位教师讲授,一个教师可讲授多门课程。
slide51
1

DEPT

PC

1

N

DP

S

N

N

SC

PROF

N

M

PC

COURSE

M

教学管理系统设计

教学管理系统的E—R图

slide52
教学管理系统设计

DEPT(D# , DNAME , DEAN)

PROF(P# , PNAME, AGE, D# , SAL)

PC(P# , C#)

S(S# , SNAME , SEX , AGE , D#)

SC(S# , C# , SCORE)

COURSE(C# , CN , CREDIT)

slide54
SQL数据定义功能
  • 域定义
  • 基本表的定义
  • 索引的定义
  • 数据库的建立与撤消
  • SQL数据定义特点
slide55
域定义
  • 域类型(SQL-92)
    • char(n):固定长度的字符串
    • varchar(n):可变长字符串
    • int:整数
    • smallint:小整数类型
    • numeric(p,d):定点数,小数点左边p位,右边q位
    • real:浮点数
    • double precision:双精度浮点数
    • date:日期(年、月、日)
    • time:时间(小时、分、秒)
    • interval:两个date或time类型数据之间的差
slide56
域定义
  • 域定义
    • 格式

create domain域名 数据类型

    • 示例

create domainperson-name char(20)

类似C语言中:

typedefADDRESS_LIST{

char name[10];

char telephone[20];

char location[20]

char email[20]};

ADDRESS_LIST tom;

slide57
数据库的建立与撤消

有的数据库系统支持多库

  • 建立一个新数据库

create database数据库名

  • 撤消一个数据库

drop database数据库名

  • 指定当前数据库

database数据库名

  • 指定当前数据库

close database数据库名

slide58
数据库的建立与撤消

例:建立一个大学数据库

Create database college

例:删除建立的数据库

Drop database college

slide59
基本表的定义
  • 基本表的定义(CREATE)
    • 格式

create table表名(

列名 数据类型 [default缺省值] [not null]

[,列名 数据类型 [default 缺省值] [not null]]

……

[,primary key(列名 [,列名] …)]

[,foreign key(列名 [,列名] …)

references表名 (列名 [,列名] …)]

[,check(条件)])

slide60
基本表的定义

Create table DEPT(D# char(4),

DNAME char(20) ,

DEAN char(4),

primary key (d#))

slide61
基本表的定义
  • 示例

CREATE TABLE S( S# CHAR(4),

SNAME CHAR(8) NOT NULL,

AGE SMALLINT,

SEX CHAR(1),

PRIMARY KEY (S#),

CHECK (SEX=0 OR SEX=1)

)

slide62
基本表的定义

CREATE TABLE C( C# CHAR(4),

CNAME CHAR(30) NOT NULL,

Credit CHAR(8),

PRIMARY KEY (C#),

)

slide63
基本表的定义

CREATE TABLE SC

(S# CHAR(4),

C# CHAR(4),

GRADE smallint ,

PRIMARY KEY (S#,C#),

FOREIGN KEY (S#)

REFERENCES S(S#),

FOREIGN KEY (C#)

REFERENCES C(C#),

CHECK((GRADE IS NULL) OR

GRADE Between 0 AND 100))

slide64
基本表的定义
  • 示例

create table PROF

( P# char(4),

pname char(20) not null,

SAL int,

AGE int,

D# char(4),

locate char(30),

primary key (P#),

foreign key (D#)

references DEPT(D#),

check (SAL > 0))

slide65
基本表的定义

create table PC(

P# char(4) ,

C# char(4),

primary key (p#,c#),

foreign key (p#)

references prof(p#),

foreign key (c#)

references c(c#))

slide66
基本表的定义
  • 修改基本表定义(ALTER)
    • 格式:

alter table表名

[add子句] 增加新列

[drop 子句] 删除列

[modify 子句] 修改列定义

    • 示例
    • alter table PROF
    • add LOCATION char(30)
slide67
基本表的定义
  • 撤消基本表定义(drop)
    • 格式

drop table表名

    • 示例

drop tableDEPT

    • DANGER

撤消基本表后,基本表的定义、表中数据、索引、以及由此表导出的视图的定义都被删除

slide68
输入C表的数据

use college

insert into c values('C001','计算应用基础',2)

insert into c values('C003','大学英语',6)

insert into c values('C004','C语言程序设计',5.5)

insert into c values('C005','高等数据',4)

insert into c values('C006','办公自动化',2)

insert into c values('C007','市场营销',2.5)

insert into c values('C008','微型计算原理',4)

insert into c values('C009','操作系统',2)

insert into c values('C010','计算网络',3)

insert into c values('C011','信息系统的安全与保密',2)

insert into c values('C012','VC++面向对象程序设计',3)

go

slide69
插入S表的数据

use college

insert into s values('s001','李自成',21,1)

insert into s values('s003','吴三桂',16,0)

insert into s values('s004','李少说',25,1)

insert into s values('s005','吴所为',18,1)

insert into s values('s006','陈铁牛',22,0)

insert into s values('s007','苗人奂',21,0)

insert into s values('s008','张绑昌',21,1)

insert into s values('s009','林冲',23,1)

insert into s values('s010','黄三',23,0)

insert into s values('s011','王成',20,1)

insert into s values('s012','布湿',17,0)

go

slide70
插入prof表的数据

use college

insert into prof values('p001','张海工',921,23,'d001','樱花园')

insert into prof values('p003','孙定山',1260,43,'d002','桃花园')

insert into prof values('p004','王处一',2506,51,'d003','桃花园')

insert into prof values('p005','吴天',3800,31,'d004','樱花园')

insert into prof values('p006','李牛',2621,30,'d005','樱花园')

insert into prof values('p007','李本山',2211,55,'d006','百草坪')

insert into prof values('p008','王民',821,42,'d001','樱花园')

insert into prof values('p009','刘德一',923,31,'d001','桃花园')

insert into prof values('p010','孙不二',3123,40,'d002','桃花园')

insert into prof values('p011','王自成',2320,21,'d002','樱花园')

insert into prof values('p012','瑞康',1907,24,'d005','桃花园')

go

slide71
插入PC表的成绩

use college

insert into Pc values('p001','C001')

insert into Pc values('p003','C002')

insert into Pc values('p004','C003')

insert into Pc values('p005','C004')

insert into Pc values('p006','C005')

insert into Pc values('p007','C006')

insert into Pc values('p008','C001')

insert into Pc values('p009','C001')

insert into Pc values('p010','C002')

insert into Pc values('p011','C002')

insert into Pc values('p012','C005')

go

slide72
插入SC表的成绩

use college

insert into sc values('s001','C001',90)

insert into sc values('s003','C002',89)

insert into sc values('s004','C003',56)

insert into sc values('s005','C004',40)

insert into sc values('s001','C005',50)

insert into sc values('s003','C006',87)

insert into sc values('s004','C001',35)

insert into sc values('s005','C001',70)

insert into sc values('s001','C002',64)

insert into sc values('s001','C006',87)

insert into sc values('s003','C007',68)

go

slide73
插入Dept表的数据

use college

insert into dept values('D001','计算机系','P001')

insert into dept values('D002','通信信','P002')

insert into dept values('D003','管理系','P003')

insert into dept values('D004','邮政系','P004')

insert into dept values('D005','外语系','P005')

insert into dept values('D006','法经系','P006')

Go

?此时插入此表,有问题否

slide74
索引的定义
  • 索引的定义
    • 格式

create [unique/distinct] [cluster] index索引名

on 表名 (列名 [asc/desc] [ , 列名asc/desc]]…)

unique(distinct):唯一性索引,不允许表中不同的行在索引列上取相同值。若已有相同值存在,则系统给出相关信息,不建此索引。系统并拒绝违背唯一性的插入、更新

cluster:聚集索引,表中元组按索引项的值排序并物理地聚集在一起。一个基本表上只能建一个聚集索引

asc/desc:索引表中索引值的排序次序,缺省为asc

slide75
索引的定义
    • 示例:

create index s_index on S(S#)

  • 索引的删除
    • 格式

drop index索引名

slide76
索引文件示例

读者表

基于姓名的索引文件

slide78
索引的定义
  • 索引的有关说明
    • 可以动态地定义索引,即可以随时建立和删除索引
    • 不允许用户在数据操作中引用索引。索引如何使用完全由系统决定,这支持了数据的物理独立性
    • 应该在使用频率高的、经常用于连接的列上建索引
    • 一个表上可建多个索引。索引可以提高查询效率,但索引过多耗费空间,且降低了插入、删除、更新的效率
slide79
SQL数据定义特点
  • SQL中,任何时候都可以执行一个数据定义语句,随时修改数据库结构。而在非关系型的数据库系统中,必须在数据库的装入和使用前全部完成数据库的定义。若要修改已投入运行的数据库,则需停下一切数据库活动,把数据库卸出,修改数据库定义并重新编译,再按修改过的数据库结构重新装入数据
  • 数据库定义不断增长(不必一开始就定义完整)
  • 数据库定义随时修改(不必一开始就完全合理)
  • 可进行增加索引、撤消索引的实验,检验其对效率的影响
slide80
SQL数据查询功能
  • SQL数据查询基本结构
  • select子句
  • 重复元组的处理
  • from子句
  • where子句
  • 更名运算
  • 字符串操作
  • 元组显示顺序
  • 集合操作
slide81
SQL数据查询功能
  • 分组和聚集函数
  • 空值
  • 嵌套子查询
  • 派生关系
  • 视图
  • 关系的连接
slide82
SQL数据查询基本结构
  • 基本结构

selectA1 , A2 , … , An

fromr1 ,r2 , …, rm

whereP

∏A1 , A2 , … , An(p(r1  r2  …  rm))

slide83
Sql 查询结构

Select [All | DISTINCT] <目标列表达式> [别 名],<目标列表达式> [别名],……

FROM <表名或视图名>[别名]

[, <表名或视图名>[别名]…

[where <条件表达式>]

[Group By<列名1>[Having <条件式>]]

[Order By<别名2>[Asc|Desc]];

slide84
Sql 查询结构
  • 注意
  • 注意在Select语句中的where ,group,having 及order by子句的次序。
  • Where与having条件相差无几,但:
    • Having是group by的子句,它在group的基础上进行条件处理;
    • Having中可以使用集函数,而where不能使用集函数。
slide85
SQL数据查询基本结构
  • 示例

给出所有老师的姓名

select PNAME

from PROF

select
select子句
  • 目标列形式

可以为列名,* ,算术表达式,聚集函数

    • “*”:表示“所有的属性”

给出所有老师的信息

select *

from PROF

select1
select子句
  • 带,, , 的算术表达式

给出所有老师的姓名及税后工资额

select PNAME,SAL * 0.95

from PROF

slide88
重复元组的处理
  • 语法约束

缺省为保留重复元组,也可用关键字all显式指明。若要去掉重复元组,可用关键字distinct或unique指明

  • 示例

找出所有选修课程的学生

select distinct S#

from SC

slide89
from子句
  • 说明
    • from子句列出查询的对象表
    • 当目标列取自多个表时,在不混淆的情况下可以不用显式指明来自哪个关系
  • 示例
    • 例:找出工资低于500的职工的姓名、工资、系别

select PNAME , SAL , DNAME

from PROF , DEPT

where SAL < 500

and PROF.D# = DEPT.D#

slide90
from子句
  • 例:列出教授“电子商务”课程的老师的教工号及姓名

select PROF.p# , PNAME,cName

from PROF , PC , C

where PROF.p# = PC.P#

and PC.C# = C.C#

and C.CNAME like '计算应用基础'

where
where子句
  • 语法成分
    • 比较运算符
      • 、 、、、=、  
    • 逻辑运算符
      • and,or,not
    • between条件
      • 判断表达式的值是否在某范围内
where1
where子句
  • 示例
    • 列出工资在500~800之间的老师姓名

select PNAME

from PROF

where SAL between 500 and 1000

slide93
更名运算
  • 格式

old_name as new_name

为关系和属性重新命名,可出现在select和from子句中

注:as可选

slide94
更名运算
  • 示例
    • 属性更名

例:给出所有老师的姓名、所纳税额及税后工资额

select PNAME,SAL*0.05 as taxi,

SAL*0.95 as incoming

from PROF

说明,在AS 后的名字可以是汉字 (在SQL SEVER)

slide95
更名运算
  • 关系更名

找出工资比所在系主任工资高的老师姓名及工资

select P1.PNAME,P1.SAL

from PROF as P1,PROF as P2,DEPT

where P1.D# = DEPT.D#

and DEPT.DEAN = P2.P#

and P1.SAL > P2.SAL

slide96
字符串操作
  • 命令格式
    • 格式

列名 [not] like“字符串”

找出满足给定匹配条件的字符串

slide97
字符串操作
  • 匹配规则
    • “%”
      • 匹配零个或多个字符
    • “_”
      • 匹配任意单个字符
    • Escape
      • 定义转义字符,以去掉特殊字符的特定含义,使其被作为普通字符看待
      • 如escape “\”,定义 \ 作为转义字符,则可用\%去匹配%,用\_去匹配_

思考:用什么去匹配\ ?

slide98
字符串操作
  • 示例
    • 列出姓名以“张”打头的教师的所有信息

select *

from PROF

where PNAME like '张%'

slide99
字符串操作
  • 列出名称中含有4个字符以上,且倒数第3个字符是d,倒数第2个字符是_的系的所有信息

select *

from PROF

where PNAME like '% _d \_'

slide100
元组显示顺序
  • 命令

order by列名 [asc | desc]

  • 示例
    • 按系名升序列出老师姓名,所在系名,同一系中老师按姓名降序排列

select DNAME,PNAME

from PROF,DEPT

where PROF.D# = DEPT.D#

order by DNAME asc,PNAME desc

slide101
集合操作
  • 命令

集合并:union

集合交:intersect

集合差: except

    • 提示

集合操作自动去除重复元组,如果要保留重复元组的话,必须用all关键词指明

slide102
集合操作
  • 示例
    • 求选修了c001或c002号课程的学生号

(select S#,c# from SC

where C# ='c001')

union

(select S#,c#

from SC

where C#='c002')说明:标准SQL不支持集交和集合差操作,所以本例的且可通过其它操作来实现,后面有一种方法

slide103
集合操作
  • 求选修了c001(且)c002号课程的学生号

select S# ,c# from SC

where C# ='c001' and s# in

( select S#

from SC

where C#='c002')

slide104
集合操作
  • 求选修了c001或c002号而没有选c003号课程的学生号

(select S#

from SC

where C# = 'c001' or C# ='c002')

except

(select s#

from SC

where C# = 'c003')

在SQL SEVER中不支持except,所以本句应如下表示

slide105
集合操作

select S#

from SC

where (C# = 'c001' or c# = 'c002')

and s# not in

(select S#

from SC

where C# = 'c003')

slide106
分组和聚集函数
  • 分组命令

group by列名 [having条件表达式]

    • group by将表中的元组按指定列上值相等的原则分组,然后在每一分组上使用聚集函数,得到单一值
    • having则对分组进行选择,只将聚集函数作用到满足条件的分组上,没有Group by子句,不能用having.
    • 在包含Group By子句的查询中,Select 子句后的所有字段列表,除集合函数外,都应包括在Group By子句,否则将出错。
slide107
分组和聚集函数
  • Select A1,A2…An
  • From T1,T2…Tn
  • Where P1
  • Group by Ti Having
slide108
分组和聚集函数

列出每门课程的平均成绩

列出每个学生的平均成绩

82

{

90

92

{

85

92

{

92

group by S#

group by C#

slide109
分组和聚集函数
  • 聚集函数
    • 平均值:avg
    • 最小值:min
    • 最大值:max
    • 总和:sum
    • 记数:count
  • count(*)与count(列名)的差别
slide110
分组和聚集函数
  • 示例
    • 列出各系的老师的最高、最低、平均工资

select D#,max(SAL),min(SAL),avg(SAL)

from PROF

group by D#

slide111
分组和聚集函数
  • 查询出选修各门课程的学生人数

select c#,count(s#)

from sc

group by c#

slide112
分组和聚集函数
  • 火眼金睛

①求选修了课程的学生人数

select count (S# )

from SC

②select PNAME,max(SAL)

from PROF

③select D#,avg(SAL)

from PROF

group by D#

where age>10

select count (S# )

from S

where s# in (select s# from sc

)

select count (distinct s#) from sc

slide113
哪个正确?分组和聚集函数
  • 列出所有科目及格成绩的同学的平均成绩

select S#,avg(grade)

from SC

group by S#

having min(grade) >= 60

select S#,avg(grade)

from SC

where grade>=60

group by S# /*该语句是错误的*/

slide114
求没有补考的同学的平均分

select avg(grade)

From sc

Where grade>=60

select avg(grade) from sc

where s# not in(

select s# from sc

where grade<60)

  • 求有补考的同学的平均分

错误?

slide115
分组和聚集函数
  • 列出每一年龄组中男学生(超过50人)的人数

select AGE,count(S#)

from S

where SEX = 'M'

group by AGE

having count(*) > 50

slide116
分组和聚集函数
  • 求总分超过150分的同学学号,姓名

select s#,sname from s where s.s# in

(select s#

from sc

group by s#

having sum(grade)>150)

slide117
空值
  • 空值测试

is [not] null

测试指定列的值是否为空值

  • 示例

找出年龄值为空的老师姓名

select PNAME

from PROF

where AGE is null

不可写为where AGE = null

slide118
空值
  • 注意事项
    • 除is [not] null之外,空值不满足任何查找条件
    • 如果null参与算术运算,则该算术表达式的值为null
    • 如果null参与比较运算,则结果可视为false。在SQL-92中可看成unknown
    • 如果null参与聚集运算,则除count(*)之外其它聚集函数都忽略null
slide119
空值

例:select sum(Grade)

from SC

例:select count(*)

from SC

360

6

slide120
嵌套子查询
  • 集合成员资格
  • 集合之间的比较
  • 集合基数的测试
    • 测试集合是否为空
    • 测试集合是否存在重复元组
slide121
集合成员资格
  • in 子查询

表达式 [not] in(子查询)

判断表达式的值是否在子查询的结果中

  • 示例
    • 列出张军和王红同学的所有信息

select *

from S

where SNAME in ('李自成','吴三桂')

slide122
集合成员资格
  • 选修了c001号课程的学生的学号及姓名

select S#,SNAME

from S

where S# in

(select S#

from SC

where C# = 'c001')

slide123
集合成员资格
  • 列出选修了c001号和c002号课程的学生的学号

select S#

from SC

where SC.C# = 'c001'

and S# in

(select S#

from SC

where C# = 'c002')

slide124
集合之间的比较
  • some/all子查询
    • 表达式 比较运算符some(子查询)

表达式的值至少与子查询结果中的一个值相比满足比较运算符

    • 表达式 比较运算符 all(子查询)

表达式的值与子查询结果中的所有的值相比都满足比较运算符

slide125
集合之间的比较
  • 示例
    • 找出平均成绩最高的学生号

select s#

from sc

group by s#

having avg(grade)>=all(

select avg(grade)

from sc

group by s#)

select s#,sum (grade)

from sc

group by s#

slide126
集合基数的测试
  • 测试集合是否为空

[not] exists(子查询)

判断子查询的结果集合中是否有任何元组存在

in后的子查询与外层查询无关,每个子查询执行一次,而exists后的子查询与外层查询有关,需要执行多次,称之为相关子查询

slide127
集合基数的测试
  • 列出选修了c001号课程的学生的学号及姓名

select S#,SNAME

from S

where exists

(select *

from SC

where C# ='c001'

and S# = S.s#)

slide128
集合基数的测试
  • 列出选修了c001号和c002号课程的学生的学号

select S#

from SC SC1

where SC1.C# ='c001'

and exists

(select S#

from SC SC2

where SC2. C# ='c002'

and SC2.S# = SC1.S#)

slide129
集合基数的测试
  • 列出选修了全部课程的学生姓名

select SNAME

from S

where not exists

(select C#

from C

where not exists

(select *

from SC

where SC.C# = C.C#

and SC.S# = S.S#))

任意课程,所求学生选之

不存在任何一门课程,所求学生没有选之

slide130
列出至少选修了001号学生选修的所有课程的学生名

select sname

from s

where not exists(select *

from sc sc1

where sc1.s#='s001' and not exists

(select * from sc sc2

where s.s#=sc2.s#

and sc2.c#=sc1.c#));

任意课程,001号学生选之,所求学生选之

不存在任何一门课程,001号学生选之,所求学生没有选之

slide131
任意课程,001号学生选之,所求学生选之

不存在任何一门课程,001号学生选之,所求学生没有选之

  • 列出至少选修了001号学生选修的所有课程的学生名

select SNAME

from S

where not exists

(select C#

from C

where exists

(select *

from SC

where SC.C# = C.C#

and SC.S# ='s001')

and not exists

(select *

from SC

where SC.C# = C.C#

and SC.S#=S.S#))

slide132
集合基数的测试
  • 测试集合是否存在重复元组

unique (子查询)

如果子查询结果中没有重复元组,则返回true

slide133
集合基数的测试
  • 示例
    • 找出所有只教授一门课程的老师姓名

select PNAME

from PROF

where unique /*unique有错*/

(select PNO

from PC

where PC.PNO = PROF.PNO)

思考:sql sever不支持unique ,则应怎样完成本题呢?

slide134
集合基数的测试
  • 找出所有只教授一门课程的老师姓名

select PNAME

from PROF

where p# in

(select p#

from PC

group by p#

having count(c#)=1 )

slide135
集合基数的测试
  • 找出至少选修了两门课程的学生姓名
  • select SNAME
  • from S
  • where not unique
  • (select S#
  • from SC
  • where SC.s# = S.s#)

to TRUE or not to TRUE , that is the question

unique { (a , b , null) , (a , b , null) } ?

slide136
派生关系
  • 命令

(子查询) as关系名(列名,列名,…)

SQL-92中,允许在from子句中使用子查询表达式,这时可将该子查询的结果命名为一个临时关系加以引用

派生关系 Vs 视图?

slide137
派生关系
  • 示例
    • 找出平均成绩及格的学生

先求出每个学生的平均成绩,再从中找出及格的学生

select sc.S# , avg(grade)

from S,SC

where SC.S# = S.S#

group by SC .S#

slide138
派生关系

select rname, AVG_SCORE

from

(select sc.s#,avg(grade)

from S,SC

where SC.S# = S.S#

group by SC.S#)

as result(rname,AVG_SCORE )

where AVG_SCORE >= 60

slide139
视图
  • 定义视图

create view view_name[(列名[,列名] …)]

as(查询表达式)

[with check option]

视图的属性名缺省为子查询结果中的属性名,也可以显式指明

with check option指明当对视图进行insert,update时,要检查是否满足视图定义中的条件

  • 撤消视图

drop view view_name

slide140
视图
  • 示例 计算机系的教师视图

create view COMPUTER_PROF

as (select prof.P#,PNAME , SAL

from PROF,DEPT

where PROF.D# = DEPT.D#

and DEPT.DNAME ='计算机系')

slide141
视图

建立视图:各系的平均最低、最高、平均及总工资

create view DEPTSAL( DNO, LOW, HIGH, AVERAGE, TOTAL )

as ( select D#, min(SAL), max(SAL), avg(SAL), sum(SAL)

from PROF

group by D# )

slide142
视图
  • 给出计算机系工资超过800的老师姓名

select PNAME

from COMPUTER_PROF

where SAL > 800

  • 给出计算机系老师的最低、最高、平均工资以及工资总额

select LOW , HIGH , AVERAGE , TOTAL

from DEPTSAL , DEPT

where DEPTSAL.Dno = DEPT.D#

and DEPT.DNAME ='计算机系'

slide143
SQL的数据修改功能
  • 插入
  • 删除
  • 修改
  • 视图更新
slide144
插入操作
  • 命令

insert into表名 [(列名[,列名]…]

values (值 [,值]…)

插入一条指定好值的元组

insert into表名 [(列名[,列名]…]

(子查询)

插入子查询结果中的若干条元组

slide145
插入操作
  • 示例

insert into PROF

values ( 'P014', '王明',498 ,35, 'D005','三峡大坝')

insert into PROF (P#, PNAME, D#)

values ( 'P016', '老玩童', 'D005')

思考:老玩童的SAL是什么?其location呢?

如何防止插入带有空值的元组?

slide146
插入操作
  • 将平均成绩大于90的学生加入到EXCELLENT中

create table excell( sno char(4),

s_name char(6),

score int

)

insert into excell(sno,score)

select s#, avg(grade)

from SC

group by (S#)

having avg(grade)>50

slide147
插入操作

insert into excell(s_name)

select sname

from s,excell

where sno=s.s#

思考:

若建立Excell表时,指定sno为primary key,上述语句是否可行?

若不可执行,应如何为该表加入学生

slide148
怎样加入EXCELL表的学生姓名?

update excell

set s_name=

(select sname from s

where sno=s#)

slide149
插入操作

insert into PROF

select *

from PROF

不支持修改在子查询中出现的表

若支持,则完成查询后,再执行修改操作

slide150
删除操作
  • 命令

delete from表名 [where条件表达式]

从表中删除符合条件的元组,如果没有where语句,则删除所有元组

slide151
删除操作
  • 示例
    • 清除所有选课记录

delete from SC

    • 删除王明老师所有的任课记录

delete from PC

where P# in

(select P#

from PROF

where PNAME = '王明')

slide152
删除操作
  • 删除低于平均工资的老师记录

delete from PROF

where SAL <

(select avg(SAL)

from PROF)

思考:是先找到所有符合条件的元组,一并删除,还是找到一个删除一个?

slide153
更新操作
  • 命令

update 表名

set列名 = 表达式 | 子查询

列名 = [,表达式 | 子查询]…

[where条件表达式]

指定对哪些列进行更新,以及更新后的值是什么

slide154
更新操作
  • 示例
    • 老师工资上调5%

update PROF

set SAL = SAL * 1.05

slide155
更新操作
  • 将D01系系主任的工资改为该系的平均工资

update PROF

set SAL = (select avg(SAL)

from PROF

where D# ='D001')

where P# = (select DEAN

from DEPT

where D# ='D001')

slide156
更新操作
  • 当C001课程的成绩小于该课程的平均成绩时,将该成绩提高5%

update SC

set GRADE = GRADE * 1.05

where C# = 'C001'

and GRADE < (select avg(GRADE)

from SC

where C# ='C001')

slide157
更新操作
  • 工资超过2000的缴纳10%所得税,其余的缴纳5%所得税

① update PROF

set SAL = SAL * 0.9

where SAL > 2000

② update PROF

set SAL = SAL * 0.95

where SAL <= 2000

执行顺序是①,②,还是②,①?

slide158
视图更新
  • 示例

create view P_SAL

as (select P# , PNAME , SAL

from PROF )

insert into P_SAL

values ( 'P017' , '张立' , 750 )

转换为

insert into PROF

values ( P017 , “张立”, null , null , 750 )

slide159
视图更新

create view SN_AGE

as (select SNAME, AGE

from S )

insert into SN_AGE

values ( “张立”,23 )

缺主码信息

转换为

insert into S

values (null, “张立”, 23, null)

slide160
视图

create view S_G(S#, AVERAGE )

as ( select S#, avg(GRADE)

from SC

group by S# )

update S_G 如何更新SC?

set AVERAGE = 85

where S# = 'S001'

slide161
视图更新
  • 示例

create view S_C_G

as (select SNAME, CNAME , GRADE

from S, C, SC

where S.S# = SC.S#

and C.C# = SC.C# )

insert into S_C_G

values ( '张立' , '物理',97 )

往SC中插入对应姓名为张立的学号和对应课程名为物理的课程号,以及成绩为97的元组

如果有多个张立怎么办?

slide162
视图更新
  • 视图更新约束
    • select子句中的目标列不能包含聚集函数
    • select子句中不能使用unique或distinct关键字
    • 不能包括group by子句
    • 不能包括经算术表达式计算出来的列
    • 对于行列子集视图可以更新(视图是从单个基本表使用选择、投影操作导出的,并且包含了基本表的主码)
slide163
SQL数据控制功能
  • 安全性控制
  • 完整性控制
slide164
安全性控制
  • 安全性控制定义
    • 安全性控制保护数据库以防止不合法的使用所造成的数据泄露和破坏。其基本措施是存取控制
  • 安全性措施
    • 物理级
    • 人际级
    • 操作系统级
    • 网络级
    • 数据库系统级
slide165
安全性控制
  • 权限的转授和回收
    • 允许用户把已获得的权限转授给其他用户,也可以把已授给其他用户的权限再回收上来
  • 权限图
    • 结点是用户,根结点是DBA,有向边Ui→Uj,表示用户Ui把某权限授给用户Uj
    • 一个用户拥有权限的充分必要条件是在权限图中有一条从根结点到该用户结点的路径
slide166
U1

U1

U4

U4

U2

U2

DBA

DBA

U5

U5

U3

U3

U2

U2

U2

U2

DBA

DBA

DBA

DBA

U3

U3

U3

U3

安全性控制
slide167
安全性控制
  • 授权命令

grant表级权限 on {表名 | 视图名} to

{用户 [,用户]… | public}

[with grant option]

表级权限包括:select, update, insert, delete, index, alter, drop, resource以及它们的总和all,其中对select , update可指定列名

with grant option表示获得权限的用户可以把权限再授予其它用户

slide168
安全性控制

示例

  • grant select , insert on S to Liming

with grant option

  • grant all on S to public
slide169
安全性控制
  • 回收权限

revoke表级权限 on {表名 | 视图名} from

{用户 [,用户]… | public}

收回权限时,若该用户已将权限授予其它用户,则也一并收回。授权路径的起点一定是DBA

  • 示例

revoke insert on S from Liming

slide170
安全性控制
  • 思考一:grant可以基于列授权,当revoke这样的权限时,结果是什么?
  • 思考二:执行完下列语句的结果是什么?

revoke all on S from public

grant select on S to public

slide171
安全性控制
  • 支持多库的数据库系统中授权对象可以是数据库

grant 数据库级权限 to {用户 [,用户]… | public}

数据库级权限包括:

connect:允许用户在database语句中指定数据库

resource:connect权限+建表、删除表及索引权利

dba:resource权限 + 授予或撤消其他用户的connect、resource、dba权限

不允许dba撤消自己的dba权限

思考: 如何使dba撤消自己的dba权限?

slide172
完整性控制
  • 完整性
    • 数据的正确性和相容性
  • 完整性检查
    • DBMS必须提供一种机制来检查数据库中的数据是否满足规定的条件,以保证数据库中数据是正确的,避免非法的不合语义的错误数据的输入和输出,即所谓的“垃圾进垃圾出”(Garbage In Garbage Out)所造成的无效操作和错误结果
slide173
完整性控制
  • 完整性子系统
    • 数据库的非法更新
      • 数据本身是错误的
      • 数据原来是正确的,由于操作或程序错误,导致输入数据错误
      • 由于系统故障,导致数据错误
      • 事务的并发执行产生不正确结果
      • 人为故意破坏
    • 完整性子系统功能
      • 监督事务执行,检查是否违反完整性规则
      • 如有违反现象,采取相应措施(拒绝、报告、改正)
slide174
完整性控制
  • 完整性规则
    • 完整性规则集
      • 由DBA或程序员事先提供的有关数据约束的一组规则
    • 规则的构成
      • 约束条件:要检查什么样的错误(性别为男女)
      • 触发条件:什么时候使用规则进行检查(插入元组)
      • ELSE子句:若检查出错误,该怎样进行处理(显示错误)
    • 规则的分类
      • 域完整性规则:属性取值范围
      • 域联系规则:在一个或多个关系中,属性间的联系、影响和约束
      • 关系完整性规则:更新操作对数据库中值的影响和限制
slide175
完整性控制
  • 主码约束
    • 主码值不允许空,也不允许出现重复
    • 主码定义形式
      • 主码子句:PRIMARY KEY(S#)
      • 主码短语:S# CHAR(4) PRIMARY KEY
slide176
完整性控制
  • 完整性约束条件
    • 约束条件的作用对象:
    • 列级
    • 元组级
    • 关系级
    • 约束状态
    • 静态约束
    • 动态约束
slide177
完整性约束
  • 静态列级约束

指对一个列的取值域的说明

  • 对数据类型的约束,
    • 如属性的数据类型、长度、单位、精度
  • 对属性数据格式的约束
    • 如学号:310123
slide178
完整性控制
  • 外码约束
    • 定义形式
      • 在SC表中,定义FOREIGN KEY (S#) REFERENCES S(S#)
      • S#在SC中是外码,在S中是主码,S#在SC中取值或为空或为S中某个S#值
      • 作为主码的关系称为基本(参照)关系,作为外码的关系称为依赖关系
slide179
完整性控制
  • 删除基本关系元组
    • RESTRICT方式
      • 只有当依赖关系中没有一个外码值与要删除的基本关系的主码值相对应时,才可以删除该元组,否则系统拒绝此删除操作
    • CASCADE方式
      • 将依赖关系中所有外码值与基本关系中要删除的主码值所对应的元组一起删除
    • SET NULL方式
      • 删除基本关系中元组时,将依赖关系中与基本关系中被删主码值相对应的外码值置为空值
    • 如FOREIGN KEY (S#) REFERENCES S(S#) [ON DELETE [CASCADE | SET NULL] ]
slide180
完整性控制
  • 修改基本关系主码
    • RESTRICT方式
      • 只有当依赖关系中没有一个外码值与要修改的基本关系的主码值相对应时,才可以修改该元组主码,否则系统拒绝此次修改
    • CASCADE方式
      • 将依赖关系中所有与基本关系中要修改的主码值所对应的外码值一起修改为新值
    • SET NULL方式:
      • 修改基本关系中元组主码时,将依赖关系中与基本关系中被修改主码值相对应的外码值置为空值
    • 如FOREIGN KEY (S#) REFERENCES S(S#) [ON UPDATE [CASCADE | SET NULL] ]
slide181
完整性控制
  • 在属性值上的约束
    • 非空约束
      • 要求某属性取值不能为空值
      • SNAME CHAR(8) NOT NULL
    • 基于属性的检查子句
      • CHECK(AGE>15)
    • 域约束子句
      • 用CREATE DOMAIN定义域时,可以出现 CHECK
      • CREATE DOMAIN AGE SMALLINT

CHECK((VALUE >= 15) AND VALUE <= 25))

slide182
完整性控制
  • 全局约束
    • 全局约束涉及多个属性间的或多个关系间的联系

CREATE TABLE SC

(S# CHAR(4),

C# CHAR(4),

GRADE SMALLINT,

PRIMARY KEY (S#, C#),

CHECK(S# IN (SELECT S# FROM S)),

CHECK(C# IN (SELECT C# FROM C)))

如果S中删除元组,不会触发CHECK子句,只有对SC表的更新才会触发

slide183
完整性控制
  • 对约束的命名、撤消和添加
    • 命名

CONSTRAINT 约束名 <约束条件>

    • 示例

S# CHAR(4) CONSTRAINT S_PK PRIAMRY KEY

AGE SAMLLINT CONSTRAINT AGE_VAL

CHECK(AGE >= 15 AND AGE <= 25)

slide184
完整性控制
  • 关系上约束的撤消与添加

撤消用alter …drop…

添加用alter …add…

  • 示例

alter table S drop constraint S_PK

alter table SC add constraint SC_CHECK

check(S# in select S# from S)

slide185
完整性控制
  • 域约束的创建、撤消与添加

create domain AGE_DOMAIN smallint

constraint DC_AGE check(value <= 25 and value >= 15)

alter domain AGE_DOMAIN

add constraint DC_AGE check(value <= 35 and value >= 15)

alter domain AGE_DOMAIN

drop constrain DC_AGE

slide186
完整性控制
  • 断言
    • 定义

CREATE ASSERTION <断言名> CHECK <条件>

断言是谓词,表达数据库总应该满足的条件

    • 一旦定义了断言,系统验证其有效性,并且对每个可能违反该断言的更新操作都进行检查
    • 这种检查会带来巨大的系统负载,因此应该谨慎使用断言
    • 对断言“所有X, P(X)”,是通过检查“not exists X, P(X)”来实现的
slide187
完整性控制
  • 示例:不允许男同学选修张老师课程

create assertion ASSE2 check

(not exists

(select *

from SC

where C# in

(select C#

from C

where TEACHER = '张')

and S# in

(select S#

from S

where SEX= '1')))

slide188
完整性控制
  • 示例:每门课最多50名男同学选修

create assertion ASSE1 check

(50 >= all (select count(SC.S#)

from S, SC

where S.S# = SC.S#

and SEX = ‘M’

group by C#)))

  • 撤消:drop assertion 断言名

drop assertion ASSE1

slide189
SQL中完整性约束
  • 触发器
    • 触发器是一条语句,当对数据库做修改时,它自动被系统执行
    • 触发器的定义
      • 指明什么条件下触发器被执行
      • 指明触发器执行的动作是什么
    • 触发器的作用
      • 示警
      • 满足特定条件时自动执行某项任务
    • 触发器事件
      • Insert、 delete、update
slide190
begin

after

{

}

create trigger trigger-name

insert

delete

update

{

}

on table-name

[of column-name]

old row as identifier

new row as identifier

{

}

referencing

for each row

when(search-condition)

{

}

begin atomic

triggered-SQL-statement

end

SQL中完整性约束
slide191
SQL中完整性约束

EMP(ENO, ENAME, SAL, JOB)

职工工资增幅不得超过10%

create triggerRAISE_LIMIT

after update of SAL onEMP

referencing new row asnrow old row asorow

for each row

when(nrow.SAL > 1.1 * orow.SAL)

begin atomic

signal SQLSTATE ‘7500’ (“Salary increase 10%)

end

slide192
SQL中完整性约束

当帐户透支时,将帐户余额设为0,并建一笔贷款,其金额为透支额

create triggeroverdraft-trigger after update onaccount

referencing new row asnrow for each row

whennrow.balance < 0

begin atomic

insert intoborrower

(select customer-name, account-number

fromdepositor

wherenrow.account-number = depositor.account-number);

insert intoloan values(nrow.account-number,

nrow.branch-name, – nrow.balance);

updateaccount set balance = 0

where account.account-number = nrow.account-number

end

slide193
嵌入式SQL
  • 为什么使用嵌入式SQL?
  • 嵌入式SQL执行过程
  • 需要解决的几个问题
slide194
为什么使用嵌入式SQL?
  • 有些操作对于交互式SQL是不可能的任务
    • SQL的表达能力相比高级语言有一定的限制,有些数据访问要求单纯使用SQL无法完成。一方面,SQL在逐渐增强自己的表达能力(参见习题4.10),另一方面,太多的扩展会导致优化能力及执行效率的降低
  • 非声明性动作
    • 实际的应用系统是非常复杂的,数据库访问只是其中一个部件。有些动作如与用户交互、图形化显示数据等只能用高级语言实现
slide195
主语言 + 嵌入SQL

预处理

主语言 + 函数调用

主语言编译器

主语言执行程序

嵌入式SQL执行过程
slide196
需要解决的几个问题
  • 区分SQL语句与C语言语句

嵌入的SQL语句以EXEC SQL开始,以分号(;) 或END_EXEX结束

EXEC SQL delete from PROF

where DNO = 10;

slide197
需要解决的几个问题
  • 嵌入SQL语句与C语言之间的数据传递
    • 宿主变量
      • C变量,既可以用在C语句中,也可用在SQL语句中,用来在两者之间传递数据
    • 宿主变量的声明
      • 声明为通常的C变量,并将其放在下列标识语句之间

EXEC SQL BEGIN DECLARE SECTION

EXEC SQL END DECLARE SECTION

slide198
需要解决的几个问题

EXEC SQL BEGIN DECLARE SECTION

int prof_no;

char prof_name[30];

int salary;

EXEC SQL END DECLARE SECTION

slide199
需要解决的几个问题

注:宿主变量出现于SQL语句中时,前面加(:)以区别列名

宿主变量可出现的地方:SQL的数据操纵语句中可出现常数的任何地方,select,fetch等语句的into字句中

示例:EXEC SQL select PNAME , SAL

into :prof_name , :salary

from PROF

where PNO = prof_no ;

slide200
需要解决的几个问题
  • 指示变量
    • C变量,用来指示返回给宿主变量的值是否为null值,以及返回给宿主变量的字符串是否发生了截断
  • 指示变量应用场合
    • 如果一个宿主变量所对应的数据库字段允许空值,或字符串类型的宿主变量的长度可能小于所对应的数据库字段的长度,则需要一个指示变量来指明数据库访问的返回状态
  • 指示变量的返回值
    • = 0:取到主变量的值不空,没有发生截断
    • = 1:取到主变量的值为空值
    • > 0:取到主变量的值发生了截断,指示变量的值是截断前的字符串的实际长度
slide201
需要解决的几个问题
  • 指示变量的用法
    • 声明与宿主变量的声明方式一样,在数据操纵语句中,在宿主变量和指示变量之间加( : )或关键字indicator

EXEC SQL BEGIN DECLARE SECTION

int prof_no;

char prof_name[30];

int salary;

short name_id;

short sal_id;

EXEC SQL END DECLARE SECTION

EXEC SQL select PNAME , SAL

into :prof_name : name_id , :salary: sal_id

from PROF

where PNO = prof_no ;

slide202
需要解决的几个问题
  • SQL与主语言之间操作方式的协调
    • 执行方式的差别
      • SQL:一次一集合
      • C语言:一次一记录
    • 游标
      • 在查询结果的记录集合中移动的指针
      • 若一个SQL语句返回单个元组,则不用游标
      • 若一个SQL语句返回多个元组,则使用游标
slide203
需要解决的几个问题
  • 不需要游标的数据操作
    • 结果是一个元组的select语句

EXEC SQL

select PNAME , SAL

into :prof_name : name_id, :salary : sal_id

from PROF

where PNO = prof_no ;

slide204
需要解决的几个问题
  • insert语句

EXEC SQL insert into PROF values (:prof_no, :prof_name , :salary , :dept_no , : salary) ;

  • delete语句

EXEC SQL delete from PROF

values PNO > :prof_no ;

  • update语句

EXEC SQL update PROF

set SAL = :salary

where PNO = : prof_no ;

slide205
需要解决的几个问题
  • 需要游标的数据操作

当select语句的结果中包含多个元组时,使用游标可以逐个存取这些元组

活动集:selecT语句返回的元组的集合

当前行:活动集中当前处理的那一行。游标即是指向当前行的指针

slide206
需要解决的几个问题
  • 游标分类
    • 滚动游标
      • 游标的位置可以来回移动,可在活动集中取任意元组
    • 非滚动游标
      • 只能在活动集中顺序地取下一个元组
    • 更新游标
      • 数据库对游标指向的当前行加锁,当程序读下一行数据时,本行数据解锁,下一行数据加锁
slide207
需要解决的几个问题
  • 定义与使用游标的语句
    • declare

定义一个游标,使之对应一个select语句

declare游标名 [scroll] cursor for

select语句[for update [of列表名]]

for update任选项,表示该游标可用于对当前行的修改与删除

slide208
需要解决的几个问题
  • open

打开一个游标,执行游标对应的查询,结果集合为该游标的活动集

open游标名

  • fetch

在活动集中将游标移到特定的行,并取出该行数据放到相应的宿主变量中

fetch [next | prior | first | last | current | relative n | absolute m]

游标名 into [宿主变量表]

slide209
需要解决的几个问题
  • close

关闭游标,释放活动集及其所占资源。需要再使用该游标时,执行open语句

close游标名

  • free

删除游标,以后不能再对该游标执行open语句

free游标名

slide210
需要解决的几个问题
  • SQL语句执行信息反馈
    • 良好的应用程序必须提供对错误的处理,应用程序需要知道SQL语句是否正确执行了,发生错误时的错误代码,执行时遇到特殊情况时的警告信息
    • SQL通讯域SQLCA是一结构,每一嵌入SQL语句的执行情况在其执行完成后写入SQLCA结构中的各变量中, 根据SQLCA中的内容可以获得每一嵌入SQL语句执行后的信息,应用程序就可以做相应的处理
    • 为了说明 (USERCA),必须在应用程序中包括:

EXEC SQL INCLUDE SQLCA;

slide211
数据库访问标准化接口:ODBC
  • 事实标准
    • 占统治地位的产品,没有通过任何的公认程序而被作为标准广泛接受
  • 被动标准
    • 把一些已经被厂商实现的、甚至已经变成事实标准的特性标准化
  • 预见性标准
    • 引导市场的正式标准,先定义一些特性,然后厂商实现这些特性
slide212
数据库访问标准化接口:ODBC
  • ODBC(开放数据库互联标准)
    • 适用于客户-服务器体系结构,定义客户程序用以连接到数据库系统和发出SQL命令的API
    • 客户可以用同一ODBC API来连接到任何支持ODBC的数据库系统;ODBC允许用户同时连接到多个数据源并在这些数据源之间进行切换
    • 每个数据库系统必须提供一个驱动程序,受客户端的ODBC驱动程序管理器控制,负责与服务器连接和通讯以及进行所有必要的数据和查询格式转换
    • ODBC API定义一个CLI(调用层接口)、一个SQL语法定义以及关于允许的CLI调用序列的规则
slide213
数据库访问标准化接口:ODBC

Oracle服务器

DB2服务器

其它数据库服务器

ad