数据库原理与应用课件第4章关系数据库的标准语言SQL5

第4章关系数据库的标准语言SQL第4章关系数据库的标准语言SQL4.1SQL语言4.3数据查询4.4数据操作语句4.6索引4.5视图4.2数据定义使学生能够掌握SQL的数据的插入、删除和修改操作功能教学目标能熟练地对记录进行插入和修改操作技能目标重点掌握SQL的插入、删除、修改等数据更新功能重点SQL的插入、删除、修改

难点

前面我们掌握了数据表的查询,那么表中的数据是怎么输进去的呢？怎么修改和删除呢？简述SELECT语句的一般执行步骤

什么是相关子查询与不相关子查询？

返回`4.4数据操作语句4.4.1插入语句（Insert）4.4.2更新语句（Update）4.4.3删除语句（Delete）SQL的功能SQL的功能和相应的语句——SQL功能语句数据定义CREATE，ALTER，DROP数据查询SELECT数据修改INSERT，UPDATE，DELETE数据控制GRANT，REVOKE，……

4.4.1插入语句格式1:插入一个新元组InsertInto

关系[(属性1,属性2,…)]

Values

(值1,值2,…)注意所列值的个数必须和属性的个数相等，且一一对应在插入的新元组中，对没有指定的属性填入缺省值（CreateTable时定义），没有缺省值时填入空值为新元组的哪些属性赋值。缺省为全部属性为前面的属性赋予什么值例：InsertIntoRValues

('刘朝','物理',80)姓名课程成绩王红物理70张军化学90姓名课程成绩王红物理70张军化学90刘朝物理80RR插入一个元组4.4.1插入语句例：新建表S：

CreateTableS(学号VARCHAR(4),姓名VARCHAR(10)notnull,年龄SMALLINTdefault22,班级VARCHAR(20));在S中插入元组InsertInto

S(学号,姓名)Values

(1,'王红')

学号姓名年龄班级学号姓名年龄班级1王红22NullSS4.4.1插入语句格式2:插入多个元组（一个SQL查询的结果）InsertInto

关系[(属性1,属性2,…)]

Select

……注意同样地，对其它的属性填入缺省值或空值为新元组的哪些属性赋值。缺省为全部属性查询结果的各个属性赋值给前面的属性，所以要一一对应：类型相容，属性名可不同4.4.1插入语句例：添加所有学生选修数学课程的信息

InsertInto

R(姓名,课程)

Select

姓名,课程From

S,C

Where

课程=‘数学’姓名课程成绩王红物理70刘朝物理80张军化学90姓名课程成绩王红物理70刘朝物理80张军化学90王红数学Null刘朝数学Null张军数学NullRR姓名王红张军C课程物理数学化学S4.4.1插入语句例：统计每门课程的平均成绩，然后保存到一个表里1.Createtable

C

(课程varchar(20),平均成绩float)

2.

Insert

Into

C

Select

课程,Avg(成绩)

From

R

Group

By

课程姓名课程成绩王红物理70王红数学80张军数学90课程平均成绩物理70数学85RC4.4.1插入语句4.4数据操作语句4.4.1插入语句（Insert）4.4.2更新语句（Update）4.4.3删除语句（Delete）格式

Update

关系Set

属性1=表达式1

[,属性2=表达式2]

……

[Where

条件]说明：在关系中找到满足条件的元组，然后更新：表达式1的值赋予属性1；表达式2的值赋予属性2……没有Where子句时，则对关系的全部元组都要更新4.4.2更新语句例：给销售部门的职工增加10%的工资

Update

R

Set

工资=工资*1.1

Where

部门=“销售”R工号姓名部门工资1老张销售32002老李研发28003老孙销售40004老陈生产2000工号姓名部门工资1老张销售35202老李研发30803老孙销售44004老陈生产2200R4.4.2更新语句例：在原有的学生关系S里面增加一个新属性：选修课程数。然后填充正确的数值。Update

S

set

选修课程数

=

(

select

count(课程)

from

R

where

R.姓名=S.姓名)姓名课程成绩王红物理70王红数学80张军数学90姓名选修课程数王红?张军?RS4.4.2更新语句4.4数据操作语句4.4.1插入语句（Insert）4.4.2更新语句（Update）4.4.3删除语句（Delete）格式DeleteFrom

关系[Where

条件]说明：在关系中找到满足条件的元组，并删除之如果没有Where子句，表示删除关系的全部元组(保留结构)一次只能删除一个关系中的元组4.4.3删除语句Delete语句例：删除物理课的选修信息DeleteFrom

RWhere

课程=‘物理’RR姓名课程成绩王红物理50王红数学60张军数学90张军化学90刘朝物理80姓名课程成绩王红数学60张军数学90张军化学904.4.3删除语句例：删除全部选修信息Delete

From

RRR姓名课程成绩王红物理50王红数学60张军数学90张军化学90刘朝物理80姓名课程成绩4.4.3删除语句例：删除平均分不及格的学生的选修信息

DeleteFrom

RWhere

姓名

in

(select

姓名

from

R

groupby

姓名

having

avg(成绩)<60)R姓名课程成绩王红物理50王红数学60张军数学90张军化学100刘朝物理80R姓名课程成绩张军数学90张军化学100刘朝物理804.4.3删除语句在进行数据的增、删、改时数据库管理系统自动检查数据的完整性约束，而且这些检查是在对数据进行操作之前进行的，只有当数据完全满足完整性约束条件时才进行数据更改操作。对删除和更新操作，无条件操作和有条件操作，对有条件的删除和更新操作。小结

下课了。。。休息一会儿。。。追求第4章关系数据库的标准语言SQL4.1SQL语言4.3数据查询4.4数据操作语句4.6索引4.5视图4.2数据定义理解视图和的索引概念，掌握使用SQL语句定义视图、删除视图和更新视图的方法；创建和删除索引的方法。教学目标培养学生积极思考、敢于动手、自主探究的能力，鼓励学生要善于协作学习、大胆创新。培养学生团结互助的团队合作意识技能目标定义视图、删除视图和更新视图；索引的创建和删除方法。重点定义视图、删除视图和更新视图；索引的创建和删除方法。难点

前面我们掌握了如何进行单表查询,那么多表查询数据库如何实现?简述SELECT语句的一般执行步骤

数据操作中的删除和数据定义中的删除有什么不同？

返回`

视图是从一个或几个基本表（或视图）导出的虚表。

（用户外模式是由若干基本表和/或若干视图构成的）

视图是一个虚表，只存储视图的定义，数据存在所基于

的基本表中。

视图定义后就可象基本表一样来使用。可创建、删除视图可用来定义新的视图可在视图上查询（SELECT）可更新（INSERT，DELETE，UPDATE）视图，但受限制4.5视图4.5.1视图的定义格式：CREATEVIEW视图名

[（字段名[，字段名]…）]

AS子查询

[WITHCHECKOPTION]；功能：在数据字典中存储视图的定义（但并不执行子查询），视图名就可作为一个表来使用。例1建立信息系学生的视图。

CREATEVIEWIS_StudentASSELECT*FROMStudentWHERESdept=‘IS'WITHCHECKOPTION对视图UPDATE或INSERT时,记录要满足子查询中的条件视图中包含的字段属性列省略，隐含同4.5视图

例2在上例创建的视图（IS_Student）的基础上在派生视图，建立信息系男学生的视图。CREATEVIEWIS_Student_sexASSELECT*FROMIS_StudentWHERESsex='男'

例3

建立包含计算机系选修了C001号课程的学生学号，姓名，课程号，成绩的视图。CREATEVIEWV_CS_S1(学号,姓名,课程号,成绩)ASSELECTStudent.Sno,Sname,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoWHERESdept='计算机系'ANDCno='C001'4.5视图

例4

定义一个反映学生出生年份的视图。CREATEVIEWV_birth(Sno,Sname,Sbirth)ASSELECTSno,Sname,2015-SageFROMStudent

例5建立一个反应各个系人数的视图CREATEVIEWV_sdept_countASSELECTSdept

系名,COUNT(*)AS各系人数FROMStudentGROUPBYSdept思考：建立男女学生的平均年龄视图？4.5视图4.5.2修改和删除视图定义视图后，如果其结构不能满足用户的要求，则可以对其进行修改。如果一个视图如果不再具有使用价值，则可以将其删除。ALTERVIEW<视图名>[(<列名>[,<列名>]...)]AS<子查询>[WITHCHECKOPTION]4.5视图例7删除上例创建的视图IS_Student。DROPVIEWIS_Student

例6修改上例创建的V_CS_S1视图。修改为包含计算机系选修了C001号课程并且成绩大于90分的学生学号，姓名，课程号，成绩的视图。ALTERVIEWV_CS_S1(学号,姓名,课程号,成绩)ASSELECTStudent.Sno,Sname,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoWHERESdept='计算机系'ANDCno='C001'AND在删除基本表和视图时要注意是否存在引用被删除对象的视图，如果有应同时删除。4.5视图4.5.3查询视图例8查询信息系学生视图（IS_Student）当中年龄小于20岁的学生。SELECT*FROMIS_StudentWHERESage<20CREATEVIEWIS_StudentASSELECT*FROMStudentWHERESdept=‘CS'WITHCHECKOPTION修改为SELECTS#，SAFROMWHERESA<20;Sdept=‘CS'ANDstudent4.5视图例9SELECT*FROMIS_StudentWHERESage<20修改为StudentSdept=‘IS'CREATEVIEWIS_StudentASSELECT*FROMStudentWHERESdept=‘IS'WITHCHECKOPTIONANDSELECT*FROMWHERE

Sage<20;请看下例2、注意事项当视图中的字段对应的是一个库函数或字段表达式时，有些系统转换后的查询可能会不正确4.5视图studentGROUPBYSdeptSdept,count(*)SELECTFROMWHEREcount(*)>=4例10对于一个反映各个系人数的视图V_sdept_count，现在要求查询出人数大于或等于4人的系，列出系名和人数。SELECT*FROMV_sdept_countWHERE各系人数>=4CREATEVIEWV_sdept_countASSELECTSdept系名,

COUNT(*)AS各系人数FROMStudentGROUPBYSdept修改为正确吗

?

正确的应为:SELECTSdept,count(*)FROMV_sdept_countGROUPBYSdeptHAVINGcount(*)>=44.5视图例11查询信息系的学生的基本学生信息和选课情况,未选课的学生也列出基本学生信息。SELECT*FROMIS_StudentLEFTJOINSCONIS_Student.Sno=SC.SnoSELECT*FROMStudentLEFTJOINSCONStudent.Sno=SC.SnoWHEREStudent.Sdept='‘IS”转换4.5视图4.5.4更新视图数据

视图是不实际存储数据的虚表、因此对视图的更新，最终要转换为对基本表的更新。像查询视图那样，对视图的更新操作也是通过视图消解、转换为对基本表的更新操作。例12将前面建立的视图IS_Student中学号为S0006的学生姓名改为刘向丽。UPDATEIS_StudentSETSname='刘向丽'WHERESno='S0006'UPDATEStudentSETSname='刘向丽'WHERESno='S0006'ANDSdept=‘IS'转换4.5视图例13向信息系学生视图IS_Student插入一条新记录，其中学号为S0012，姓名为黄俊，性别为男，年龄为21岁。INSERTINTOIS_StudentVALUES('S0012','黄俊','男',21,'信息系')INSERTINTOStudentVALUES('S0012','黄俊','男',21,'信息系')转换例14删除信息系学生视图IS_Student一条记录，其学号为S0012。DELETE

FROMIS_StudentWHERESno='S0012'DELETE

FROMStudentWHERESno='S0012'转换4.5视图例15将视图V_sdept_count中的数学系的人数修改为４人。UPDATEV_sdept_countSET各系人数=4WHERE系名='数学系'不是所有的视图都可以进行插入，修改和删除操作，4.5视图4.5.5视图的作用1、能够简化用户的操作2、用户能以不同的方式对待同一数据，方便灵活3、提供一定程度的逻辑独立性4、有利于安全保密4.5视图第4章关系数据库的标准语言SQL4.1SQL语言4.3数据查询4.4数据操作语句4.6索引4.5视图4.2数据定义

4.6索引

汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等。我们可以根据拼音或偏旁部首，快速查找某个字词。索引概念：数据库中的索引是一个列表，在这个列表中包含了某个表中一列或者若干列值的集合，以及这些值的记录在数据表中的存储位置的物理地址。索引的作用：通过使用索引，可以大大提高数据库和检索速度，改善数据库性能。

4.6索引

索引的分类：

1、聚集索引：

2、非聚集索引：

3、唯一索引：

4.6索引

聚集索引特点：1、表中的数据页会依照该索引的顺序来存放。即：表中的数据的物理存储顺序和排列顺序相同，可按表中的某一字段或多个字段来排序记录2、每个数据库中的每个表只能有一个聚簇索引。因为一个表中的记录只能以一种物理顺序存放。3、通常情况下是对一个表按照主码（关键字）建立聚簇索引。也可以用其它字段建立聚簇索引。

4.6索引

例16职员表（未建立聚簇索引之前）注意：

按‘姓名’建立一个聚簇索引，表中的记录会自动按姓名的拼音顺序进行存储，如下图：编号姓名性别工资20056李海龙男350020012丁亮男300020009杨秋生男500020032李秀华女4000

4.6索引

编号姓名性别工资20012丁亮男300020056李海龙男350020032李秀华女400020009杨秋生男5000基于“姓名”字段建立了聚簇索引以后：注意：现在向表中添加一条记录“20006，牛东华，女，6000”，则该记