关系数据库标准语言SQL

普三章关系数据库标准语言SQL

■SQL就述

■SQL照据定义功犍

作业P1483、4、

■SQL照据重询功犍5、11、12

■SQL照据修改功犍

■视囹的使用

■SQL照据搜刷功犍

■嵌八式SQL

1

j3.1SQL概述

■历史

■SQL:StruceuredQueryLanguang

■1974年，由Boyce和Chamber提出

■1975-1979年，在SystemR上实现，由IBM的San

Jose研究室研制，称为Sequel

■后来SEQUEL简称为SQL(StructuredQuery

Language),即“结构式查询语言”，SQL的发音

仍为“sequel”。现在SQL已经成为一个标准。

2

3-1SQL概述

Storedfile

SQL数据库体系结构

3

3.1SQL概述

■特支

■一体化

.集DDL,DML,DCL于一体

■单一的结构一关系，带来了数据操作符的统一

■面向集合的操作方式

■一次一集合

■高度非过程化

-用户只需提出“做什么”，无须告诉“怎么做”，不必了斛存取路

楂

■两种使用方式，统一的语法结构

-SQL既是自含式语言（用户使用），又是•鼠人式语言1程序员使用）

■语言简洁，易学易用

4

3.1SQL概述

SQL功惚据作符

照据重询SELECT

熬据定义CREATE，ALTER，DROP

照据嫌犯INSERT.UPDATE.DELETE

熬据控制GRANT，REVOKE

5

3.2SQL数据定义功能

■基存索的定义

■索引的定义

■SQL熬据定义特支

DEPT(D#,DNAME,DEAN)

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

COURSE(C#,CN,PC#,CREDIT)

SC(S#,C#,SCORE)

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

PC(P#,C#)

6

*基本表的定义

■基本表的灾义(CREATE)

■格^式

createtable表名(

列名数据类型、列级完整性约束条件、

L列名数据类型、列级完整性约束条件、

L表级完整性约束条件、

7

*基本表的定义

■熬据类型fSQL-92；

■charCnJ:固定长度的字符串

■varcharCnJ:可变长字符串

■int：整教

■smallint：小整数类型

■numeric（p,dj:定点数，共p佳，小教点后有q核

■real：浮点数

■doubleprecision：双精度浮点教

■date：日期（年、月、口）

■time：时间（小时、分、秒）

8

I.基本表的定义

F用完整桃的束

■主码约束：primarykey（列名3列名］…）

■唯一性约束：UNIQUE

■非空值约束：NOTNULL

■参照完整性约束：

foreignkey（列名［,列名］…）

references表名（列名［,列名］…）

■check（条件）

■default缺省值

■〈列级约束条件,：涉及相应属性列的完整性约束条件

■〈表级约束条件〉：涉及一个或多个属性列的约束条件

9

基本表的定义

例建立一个“学生”表Student,它由学号Sno,

姓名Sname、性别Ssex、年龄Sage、所在东

Sdept五个属性组成。其中学号为主码，并且

姓名取值不允许,生。

CREATETABLEStudent

(SnoCHAR(5)NOTNULL,

SnameCHAR(20)NOTNULL,

SsexCHAR⑴，

SageINT,

SdeptCHAR(15),

PRIMARYKEY回⑥；

10

基本表的定义

CREATETABLE5C

(SnoCHARC4J,

CnoCHARC4J,

GRADEnumeric(4,1J/

PRIMARYKEYCno),

FOREIGNKEY(Sno)

REFERENCESS(Sno),

FOREIGNKEY{Cr\6)

REFERENCESC(Sno),

O/F(X(GRADEISNULL)OR

GRADEBETWEENQAND100))

11

I.基本表的定义

氢基本表定义（ALTER）

■格式：

ALTERTABLEv表名〉

[ADD〈新列名〉〈数据类型》[完整性约束]]

[DROPv完整性约束名＞]

[MODIFYv列名〉〈数据类型〉]；

■〈表名〉：要修改的基本表

■ADD子句：增加新列和新的完整性约束条件

-DROP子句：删除指定的完整性约束条件

■MODIFY子句：用于修改列名和数据类型

12

基本表的定义

的1：向Student表增加“入学时间”列，其数据类型为日期

型。

ALTERTABLEStudentADDScomeDATE；

■不会基本表中原来是否已有数据，新增加的列一律为空

值。

例2：将年龄的熬据类型改，生冬衣卷照。

ALTERTABLEStudentMODIFYSageSMALLINT；

■注：修改原有的列定义有可能会破坏已有数据

13

*基本表的定义

■删除皋存表(drop)

■格式

droptable表名

■示例

droptableDEPT

■DANGER

删除基本表后，基本表的定义、表中数据、索引、

以及由此表导出的视图的定义都被删除，但有些系

统视图往往仍然保留，但无法引用

14

基本表的定义

■上看用户建克索

■user_tables

■tab

■至看票表的定义

■user_tab_columns

■Desc表名

■上看约束的定义

■userconstraints

15

索引的定义

■建政索引是加快重荷盛度的帝数手段

■建比索引

■DBA或表的属主（即建立表的人）根据需要建立

■有些DBMS自动建立以下列上的素引

■PRIMARYKEY

■UNIQUE

■维萨索引

■DBMS自动完成

■使用索引

■DBMS自动选择是否使用素引以及使用哪些素引

16

索引的定义

■索引的定义

■格式I

create[unique\[cluster]index索引名

on表名（列名[asc/desc\[,列名韭“曲s©]...）

unique：唯一性索引，不允许表中不同的行在索引列上

取相同值。若已有相同值存在，则系统给出相关信息,

不建此案引。系统并拒绝违背唯一性的插入、更新

cluster：聚簇素引，表中元组按索引项的值排序并物理

地聚集在一起。一个基本表上只能建一个聚集主引

asc/desc^索引表中索引值的排序次序，缺酒为asc

17

*索引的定义

■倒，老成Student袤按学号升序建唯一索引

CREATEUNIQUEINDEXStusnoONStudent(Sno)；

■例

CREATECLUSTERINDEXStusnameON

Student(Sname)；

在Student表的Sname(找名)列上建立一个聚簇素引，而

且Student表中的记录将按照Sname值的升序存放

18

索别的定义

■索引的删除

■格式

dropindex索引名

■索引的有关说阚

■可以动忐地定义素引，即可以随时建立和删除素引

-不允许用户在数据操作中引用索引。素引如何使用完全由

系统决定，这支持了数据的物理独立性

■应该在使用频率高的、经常用于连接的列上建索引

■一个表上可建多个素引。素引可以提高查询效率，但素引

过多耗费空间，且降低了插入、删除、更新的效率

19

jSQL数据定义特点

■SQL中，任何时候都可以执行一个数据定义语句，随

时修改数据库结构。而在非关东型的数据库系统中,

必须在数据库的装入和使用前全部完成数据库的定

义。若要修改已投入运行的数据库，则需停下一切

数据库活动，把数据库卸出，修改数据库定义并重

新编玲，再按修改过的数据库结构重新装入数据

■数据库定义不断增长（不必一开始就定义完整）

■数据库定义随时修改（不必一开始就完全合理）

■可进行增加索引、撤消索引的实验，检验其对效率

的影响

20

“3・3简单数据更新操作

■插入命令

insertinto表名［（列名［,列名］.・・］

values（值［,值］

插入一条指定好值的元组

insertintoPROF

values（'P123：'王明L35,'D08',498）

21

3.3简单数据更新操作

■删除命令

deletefrom表名［w力e/e条件表达式］

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

则删除所有元组

■赤例

■清除所有选课记录

deletefromSC

22

13.3简单数据更新操作

重新命令

update表名

set列名=表达式

L列名=表达

[where条件表达式]

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

■示例

■老师工咨上调5%

updatePROFsetSAL=SAL*L05

23

3-4SQL数据查询功能

3.4.1概述

3.4.2单表圭询

3.4.3逡接上询

3.4.4城杰圭询

345集合室询

24

3-4-1SQL数据查询概述

■语句珞式

SELECT[ALL|DISTINCT]<目标列表达式〉

[,v目标列表达式…

FROM（表名或视图名>[,V表名或视图名〉]…

[WHEREv条件表达式》]

[GROUPBYV列名1>[HAVINGv条件表达式>]]

[ORDERBYV列名2>[ASQDESC]]；

o

□Ai/力2/…/An（（Jp（「ix。x...x[m））

25

3・4,2SQL单表查询

重询以涉及一个表，是一种景简单的重询猱作

■投影：选择表中若干列

■更名运算

■选择：选择表中若干元组

■变值使用

■挑序：对查询结果排序

■分组和聚集国数

26

选择表中若干列

目标列表达式

可以为列名，枳，算术表达灰，函数

和聚集函教

■查询指定列

selectdeptnofdnamefromdept;

指定列表达式时，先后顺序可以与定义表的顺序不同,

即可以改变列的显示顺序

27

选择表中若干列

□查询全部列

select*fromDEPT

□查询经过计算的列

给出所有老师的聂名及税后工咨额O

selectPNAME,SAL*0.95fromPROF

selectenamefsal+nvl(commfO)fromemp;

28

更名运算

■格式

o!d_nameasnew_name

为关系和属性重新命名，可出现在select和

from子句中

注：as可选

29

*更名运算

■示例

■属性更名（使用列别名改变查询结果的列标题）

例：给出所有老师的聂名、所纳税额及税后工咨额

selectPNAME,SAL*0.05astaxi,

SAL*0.95asincoming

fromPROF

30

$选择表中的若干元组

■消除取值量复的行

缺省为保留重复元组，也可用关键字all显式指明。

若要去掉重复元组，可用关键字distinct指明

示例：找出所有选修课程的学生

selectdistinctSNOfromSC

■至询满足条件的无俎（WHERE）

31

i---查-询-满足条件的元组

■比较运算符

■确定范围

■确定集合

■字符匹配

■全值

■多重条件

32

查询满足条件的元组

■语法成今

■比较运算符

＜、＜=、＞、＞=、=、＜＞

■查询年龄在20以下的所有学生妙名及年龄

SelectSname,Sagefroms

wheresage＜20

33

查询满足条件的元组

■确定范围：[not]betweenand

判断表达式的值是否在某范围内

列加工资库500-800^间的老婶四名

selectPNAME

fromPROF

whereSALbetween500and800

34

查询满足条件的元组

■确定集合范词(IN、NOTIN)

查询信息系(IS)、教学系(MA)和计算机科学系

(CS)的学生的姓名和性别。

SELECT妙名，性别FROM学生

WHERE京名IN("S'JMA，JCS。;

35

查询满足条件的元组

■室符匹配命令格式：

■列名like，匹配串'[Escape'转义字符']

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

■匹配规则

■“％”：匹配零个或多个字符

■：匹配任意单个字符

■Escape：定义转义字符，使转义字符后面的字符被作

为普通字符看待，如escape"\”，定义\作为转义字符,

则可用\%去匹配％,用、—去匹配—

36

查询满足条件的元组

■名符匹配示例

■列出找名以“张”打头的教师的所有信息

select*fromPROF

wherePNAMElike“张％”

■列出名称中含有4个字符以上，且倒数第3个字符是d,倒教

第2个字符七的宗的所有信息

select*fromPROF

wherePNAMElike<(%-d\_"

■全询名室中第二个冬,“女”有的老女四名和老号。

selectsname,snofromstudent

wheresnamelike'文％'

37

查询满足条件的元组

■变伍恻依

is[nof\null

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

■示例

找出年龄值为左的老师聂名

selectPNAMEfromPROF

whereAGEisnull

不可写为whereAGE=null

38

i---查-询-满足条件的元组

■空值灌意事项

■除is[not]null之外，空值不满足任何查找条件

■如果null参与算术运算，则该算术表达式的值为null

■如果null参与比较运算，则结果可视为false。在

SQL-92中可看成unknown

■如果null参与聚集运算，则除count(*)之外其它聚

集函数都忽略null

39

I查询满足条件的元组

■，唾条件至询：近握运算符and,or,not

■查询信息宗（IS）和计算机科学系（CS）的学生的

妙名和性别。

SELECT」名，性别FROM学生

WHERE条名="S'or条名='CS，;

■列出艰名以“张”打头且工咨大于800元教师的

所有信息。

select*fromPROF

wherePNAMElike'张%'andsal>800

40

排序：元组显示顺序

命令orderby列名[asc|desc]

■示例：按工密从小到大排列教师的姓名和工资

selectpname,salfromproforderbysal;

注意：对于无值，若按升序排,含全值的

元组最后显示，若按降序排，含空值的元组最

先显示。有时可以使用列的序号来指定排序的

列，这在列为常教、函教或列名较长时尤其有

效。

41

排序：元组显示顺序

■按条号升序列出学生所在系号和聂名，同一条中

学生按聂名降序排列。

selectDNO,SNAME

fromS

orderbyDNOasc,SNAMEdesc

42

分组和聚集函数

■今俎命令

groupby列名［having条件表达式］

groupby将表中的元组按指定列上值相等的原则分

组，然后在每一分组上使用聚集函数，得到单一值

having则对分组进行选择，只将聚集函数作用到满

足条件的分组上

43

分组和聚集函数

■本关系的3集上运用聚集茁熬，得到一个新的

关系

44

分组和聚集函数

列出每个学生的半靖鼠楼

Selects#,avg(g)fromsc列出每门偏程的半物鼠债

groupbys#

S#C#Gs#C#G

sicl84sicl84

90

sic290sic290

sic396sic396

s2cl80s2cl8092

85

s2c290s2c290

s3c296s3c29692

92

s3c388s3c388

groupbyS#groupbyC#

45

火眼金睛

R(A,B,C)

selectAfromRgroupbyB

selectA,BfromRgroupbyA

selectA,CfromRgroupbyA,B

selectAfromRgroupbyA,C

selectAfromRgroupbyA

select*fromRgroupbyA,B

46

分组和聚集函数

■聚集旃叙（5类£要集茶熬）

■计数

COUNTf[DISTINCT|ALL]*)

COUNTC[DISTINCT|ALL]〈列名〉)

■计算总和：SUM（［DISTINCT］AL□〈列名〉）

-计算平均值：AVG（［DISTINCTIAL□〈列名〉）

-求最大值MAX（［DISTINCTIAL□〈列名〉）

■求最小值MIN（［DISTINCTIALL］v列名R

■能足DISTINCT,表示计寡时取漪指定,列

中的变复值，ALL，款堵值

47

*分组和聚集函数

■示例

■列出各半的老师的最高、最低、平均工咨

selectDNO,max(SAL),min(SAL),avg(SAL)

fromPROFgroupbyDNO

■列出每一年龄组中男学生(超过50人)的人教

selectAGE,count(S#)fromS

whereSEX=(Mf

groupbyAGEhavingcount(*)>50

48

分组和聚集函数

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

selectcount(DISTINCTSNO)

fromSC

selectcount(SNO)

fromSC

49

OS

#SAqdnoj6

09=<aavys

DSHIOJJ

%那》与

（3QVyD）6AB'#s133RS

09=<（3avyD）U!iuBUIABII

#SAqdnoi6

DSUIOJJ

（3QVMD）6AB'#S词as

分组和聚集函数

例：selectsum(G)350

fromSCs#C#G

sicl80

sic290

sic395

s2cl85

例：selectcount(*)6s2c2null

fromSCs3c2null

51

3.4.3连接查询

■——

逡接上询：重询同时涉及两个上的表

■等值与旅等值接接

■自身速接

■外接接

■符合条件速接

52

等值与非等值连接

■连接查询中用来连接两个表的条件称为连接条件

或连接谓词

［袤名L］列名1〈比较运算符,［袤名2」列名2

咨比较运算符，='时锦，等值座接

任何子句中引用表1和表2中同名属性时，都必

须加表名前缀。引用唯一属性名时可以加也可

以疳略表名前缀。

53

i等—值与非等值连接

■等僮逡接，自炫it接、旅等值逡接

例重询每个老皮及其逡修谓福的情施。

SELECTStudent.*,SC.*FROMStudent,SC

WHEREStudent.Sno=SCSno；

自然连接：如果是按照两个表中的公共属性此行

等值连接，且目标列中去掉重复的属性列。

SELECTStudent.Sno,Sname,Ssex,Sage,

Sdept,Cno,Grade

FROMStudent,SCWHEREStudent.Sno=SC.Sno

54

i—等值与非等值连接

假设Student表、SC表分别有下列数据:

Student表SC表

SnoSnameSsexSageSdeptSnoCnoGrade

95001李勇男20CS

95001192

95002刘晨女19IS

95001285

王敏女

9500318MA95001388

95004张立男19IS95002290

95002380

55

等值与非等值连接

Student.SnoSnameSsexSageSdeptSC.SnoCnoGrach

李

勇

95001男20CS95001192

李

勇

95001男20CS95001285

李

勇

95001男20CS95001388

刘

晨

95002女950022

刘19IS90

晨

95002女19IS95002380

56

等值与非等值连接

-Ji---------

■燧套循环法(NESTED-LOOP)

-首先在表1中找到第一个元组，然后从头开始扫描表2,

逐一查找满足连接条件的元组，找到后就将表1中的第

一个元组与该元组拼接起来，形成结果表中一个元组。

■表2全部查找完后，再找表1中第二个元组，然后再从

头开始扫描表2,逐一查找满足连接条件的元组，找到

后就将表1中的第二个元组与该元组拼接起来，形成结

果表中一个元组。

■重复上述操作，直到表1中的全部元组都处理完毕

57

等值与非等值连接

-Ji---------

■排序合畀法(SORT-MERGE)否用于=逡接

■首先接连接属性对表1和表2排序

■对表1的第一个元组，从头开始扫描表2,顺序

查找满足连接条件的元组，找到后就将表1中

的第一个元组与该元组拼接起来，形成结果表

中一个元组。当遇到表2中第一条大于表1连接

字段值的元组时，对表2的查询不再继续

58

i—等值与非等值连接

■排序合笄法(SORT-MERGE)帝用于二透接

■找到表1的第二条元组，然后从刚才的中断点处继续顺

序扫描表2,查找满足连接条件的元组，找到后就将表

1中的第一个元组与该元组拼接起来，形成结果表中一

个元组。直接辿到表2中大于表1连接字段值的元组时,

对表2的查询不再继续

■重复上述操作，直到表1或表2中的全部元组都处理完

毕为止

59

i—等值与非等值连接

■索引这接(INDEX-JOIN)

■对表2接连接字段建立索引

■对表1中的每个元组，依次根据其连接字段值

查询表2的索引，从中找到满足条件的元组，

找到后就将表1中的第一个元组与该元组拼接

起来，形成结果表中一个元组

60

i—等值与非等值连接

■如：找出职工的姓名、工咨、条别和条号。

selectPNAME,SAL,DNAME,dept,dno

fromPROF,DEPT

wherePROF.DNO=DEPT.DNO

61

■一个表与其自己进行逡接

■需要作表起别名。示区别

■由于所有属桃名都是同名属桃，国此处须使

用别名瑞候

62

FIRST表（Course表）

CnoCnameCpnoCcredit

1数据库54

2数学2

3信息系统14

4操作系统63

5数据结构74

6数据处理2

7PASCAL语言64

查询每一门课的间接先修课（即先修课的先修课

63

i—自身连接

SELECTFIRST.Cno,SECOND.Cpno

FROMCourseFIRST,CourseSECOND

WHEREFIRST.Cpno=SECOND.Cno；

上询倍累____________

enoepno

17

35

5~6

64

■外逡接与普通逡接的区别

■普通连接操作只输出满足连接条件的元组

■外连接操作以指定表为连接主体，将主体表中

不满足连接条件的元组一并输出

65

逡接表示方法

■外连接的表示方法，在连接谓词的某一边加符

号(*)或(+),外连接就好象为符号(*)或(+)所在

的边的表增加一个“万能”的行，这个行由全

部全值组成。它可以和另一边表中所有不满足

连接条件的元组进行连接。

66

>----外连接

例查询每个学生及其选修课程的情况包括

没有选修课程的学生一・用外连接操作

SELECTStudent.Sno,Sname,Ssex,

Sage,Sdept,Cno,Grade

FROMStudent,SC

WHEREStudent.Sno=SC.Sno(*)；

67

m------

Student.SnoSnameSsex

察勇男SageSdeptCnoGrade

192

95001

察勇男20CS

2

95001

察勇男20cs85

95001

女20CS388

刻X

95002

2

女19IS90

刻X

95002

女19IS3