数据库习题讲解课件

数据库系统概论

AnIntroductiontoDatabaseSystems2012.6.7AnIntroductiontoDatabaseSystem2.2.1基本关系操作

常用的关系操作查询：选择、投影、连接、除、并、交、差数据更新：插入、删除、修改查询的表达能力是其中最主要的部分选择、投影、并、差、笛卡尔基是5种基本操作关系操作的特点集合操作方式：操作的对象和结果都是集合，一次一集合的方式AnIntroductiontoDatabaseSystem集合运算符∪-∩×并差交笛卡尔积比较运算符＞≥＜≤＝<>大于大于等于小于小于等于等于不等于运算符含义运算符含义表2.4关系代数运算符

概述AnIntroductiontoDatabaseSystem2.4关系代数

传统的集合运算二目

并、差、交、笛卡尔积R和S具有相同的目n（即两个关系都有n个属性）相应的属性取自同一个域AnIntroductiontoDatabaseSystem1.并（Union）R∪S

仍为n目关系，由属于R或属于S的元组组成R∪S={t|t

R∨tS}AnIntroductiontoDatabaseSystem2.差（Difference）R-S

仍为n目关系，由属于R而不属于S的所有元组组成

R-S={t|tR∧tS}AnIntroductiontoDatabaseSystem3.交（Intersection）R∩S仍为n目关系，由既属于R又属于S的元组组成R∩S={t|t

R∧tS} R∩S=R

–(R-S）AnIntroductiontoDatabaseSystem专门的关系运算(续)(a)

Student4)学生-课程数据库:学生关系Student、课程关系Course和选修关系SC学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept200215121李勇男20CS200215122刘晨女19IS200215123王敏女18MA200215125张立男19ISAnIntroductiontoDatabaseSystem专门的关系运算(续)

(c)SC学号Sno课程号Cno成绩Grade200215121192200215121285200215121388200215122290200215122380AnIntroductiontoDatabaseSystem选择（续）3)选择运算是从关系R中选取使逻辑表达式F为真的元组，是从行的角度进行的运算σAnIntroductiontoDatabaseSystem选择（续）(a)

Student学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept200215121李勇男20CS200215122刘晨女19IS200215123王敏女18MA200215125张立男19IS[例1]查询信息系（IS系）全体学生

σSdept

='IS'(Student)

或σ5='IS'(Student)

AnIntroductiontoDatabaseSystem[例1]查询信息系（IS系）全体学生

σSdept

='IS'(Student)

或σ5='IS'(Student)

SnoSnameSsexSageSdept200215122刘晨女19IS200215125张立男19ISAnIntroductiontoDatabaseSystem选择（续）(a)

Student学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept200215121李勇男20CS200215122刘晨女19IS200215123王敏女18MA200215125张立男19IS[例2]查询年龄小于20岁的学生 σSage<20(Student) 或σ4<20(Student)AnIntroductiontoDatabaseSystem选择（续）[例2]查询年龄小于20岁的学生 σSage<20(Student) 或σ4<20(Student) 结果：

SnoSnameSsexSageSdept200215122刘晨女19IS200215123王敏女18MA200215125张立男19ISAnIntroductiontoDatabaseSystem2.投影（Projection）2）投影操作主要是从列的角度进行运算但投影之后不仅取消了原关系中的某些列，而且还可能取消某些元组（避免重复行）πAnIntroductiontoDatabaseSystem投影（续）(a)

Student学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept200215121李勇男20CS200215122刘晨女19IS200215123王敏女18MA200215125张立男19IS[例3]查询学生的姓名和所在系即求Student关系上学生姓名和所在系两个属性上的投影πSname，Sdept(Student) 或π2，5(Student)AnIntroductiontoDatabaseSystem投影（续）(a)

Student学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept200215121李勇男20CS200215122刘晨女19IS200215123王敏女18MA200215125张立男19IS[例3]查询学生关系Student中都有哪些系

即求Student关系上所在系这个属性上的投影πSdept(Student) 或π5(Student)AnIntroductiontoDatabaseSystem投影（续）[例4]查询学生关系Student中都有哪些系

πSdept(Student) 结果：SdeptCSISMAAnIntroductiontoDatabaseSystem连接(续)3）两类常用连接运算等值连接（equijoin）什么是等值连接θ为“＝”的连接运算称为等值连接

等值连接的含义从关系R与S的广义笛卡尔积中选取A、B属性值相等的那些元组，即等值连接为：

RS={|tr

R∧ts

S∧tr[A]=ts[B]}A=BtrtsAnIntroductiontoDatabaseSystem连接(续)自然连接（Naturaljoin）

自然连接是一种特殊的等值连接两个关系中进行比较的分量必须是相同的属性组在结果中把重复的属性列去掉自然连接的含义 R和S具有相同的属性组B

R

S={|trR∧tsS∧tr[B]=ts[B]}trtsAnIntroductiontoDatabaseSystem连接(续)4）一般的连接操作是从行的角度进行运算。

自然连接还需要取消重复列，所以是同时从行和列的角度进行运算。

AθBRSAnIntroductiontoDatabaseSystem连接(续)一般连接RS的结果如下：

C＜EAnIntroductiontoDatabaseSystem连接(续)等值连接RS的结果如下：

R.B=S.BAnIntroductiontoDatabaseSystem连接(续)

自然连接R

S的结果如下：

AnIntroductiontoDatabaseSystem综合举例(a)

Student学生-课程数据库:学生关系Student、课程关系Course和选修关系SC学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept200215121李勇男20CS200215122刘晨女19IS200215123王敏女18MA200215125张立男19ISAnIntroductiontoDatabaseSystem综合举例课程号Cno课程名Cname先行课Cpno学分Ccredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64Course(b)AnIntroductiontoDatabaseSystem综合举例

(c)SC学号Sno课程号Cno成绩Grade200215121192200215121285200215121388200215122290200215122380AnIntroductiontoDatabaseSystem综合举例检索所有的学生姓名。

检索“数据库原理”课的课程号和学分。检索选修了“数据库原理”课程的学生的学号和成绩。检索选修“数据库原理”课的学生姓名和成绩。检索没有选修“数据库原理”课的学生号。

AnIntroductiontoDatabaseSystem综合举例课后习题

AnIntroductiontoDatabaseSystem3.3.3创建基本表MSSQL数据类型整数数据

精确数值近似浮点数值日期时间数据bigint，int，smallint，tinyintnumeric和decimalfloat和realdatetime与smalldatetimeAnIntroductiontoDatabaseSystem字符串数据Unicode字符串数据二进制数据货币数据char、varchar、textnchar、nvarchar与ntextbinary、varbinary、imagemoney与smallmoney标记数据timestamp和uniqueidentifierAnIntroductiontoDatabaseSystemINT-2^31到2^31-14BYTESSMALLINT-2^15至2^15-12BYTESTINYINT0至2551BYTESNUMERIC-10^38-1至10^38-11-9位数使用5BYTES10-19位数使用9BYTES20-28位数使用13BYTES29-38位数使用17BYTESDECIMAL-10^38-1至10^38-15-17BYTES因长度而异，与NUMERIC相同FLOAT-1.79E+306至1.79E+308,最多可表示53位数8BYTESREAL-3.40E+38到3.40E+38，最多可表示24位数4BYTESAnIntroductiontoDatabaseSystemDATETIME1753/1/1至9999/12/318BYTESSMALLDATETIME1900/1/1至2079/6/64BYTESCHAR1-8000个字符1个字符占1B，尾端空白字符保留VARCHAR1-8000个字符1个字符占1B，尾端空白字符删除。TEXT2^31-1个字符1个字符占2B，最大可存储2GBNCHAR1-4000个字符1个字符占2B，尾端空白字符保留NVARCHAR1-4000个字符1个字符占2B，尾端空白字符删除AnIntroductiontoDatabaseSystemNTEXT2^30-1个字符1个字符占2B，最大可存储2GBBINARY1-8000个字符在存储时，SQLSERVER会另外增加4B，尾端空白字符会保留VARBINARY1-8000个字符在存储时，SQLSERVER会另外增加4B，尾端空白字符会删除IMAGE2^31-1个字符最大可存储2GBMONEY-2^63-2^63-18BSMALLMONEY-2^31-2^31-14BTIMESTAMP16进制8BUNIQUEIDENTIFIER全局唯一标识符（GUID）可用NEWID()函数生成一个该种类型的字段值。AnIntroductiontoDatabaseSystem创建基本表

CREATETABLE<表名> (<列定义>[{，<列定义>|<表约束>}])[例]用SQL命令建立一个学生表S。

CREATETABLES ( SNoCHAR(6), SNVARCHAR(8), SexCHAR(2)DEFAULT'男', AgeINT, DeptVARCHAR(20))<列名><数据类型>[DEFAULT][{<列约束>}]

缺省值为“男”AnIntroductiontoDatabaseSystem定义数据表的约束正确性有效性相容性数据的完整性约束（Constraint）默认（Default）规则（Rule）触发器（Trigger）存储过程（StoredProcedure）SQLServer的数据完整性机制AnIntroductiontoDatabaseSystem完整性约束的基本语法格式[CONSTRAINT<约束名>]<约束类型>NULL/NOTNULLUNIQUEPRIMARYKEYFOREIGNKEYCHECKAnIntroductiontoDatabaseSystemNULL/NOTNULL约束NULL表示“不知道”、“不确定”或“没有数据”的意思主键列不允许出现空值 [CONSTRAINT<约束名>][NULL|NOTNULL][例]建立一个S表，对SNo字段进行NOTNULL约束。 CREATETABLES (SNoCHAR(6)CONSTRAINT

S_Cons

NOTNULL, SNVARCHAR(8), SexCHAR(2), AgeINT, DeptVARCHAR(20))可省略约束名称:SNoCHAR(6)NOTNULLAnIntroductiontoDatabaseSystemUNIQUE约束（惟一约束）指明基本表在某一列或多个列的组合上的取值必须惟一在建立UNIQUE约束时，需要考虑以下几个因素：使用UNIQUE约束的字段允许为NULL值。一个表中可以允许有多个UNIQUE约束。可以把UNIQUE约束定义在多个字段上。UNIQUE约束用于强制在指定字段上创建一个UNIQUE索引，缺省为非聚集索引。

UNIQUE用于定义列约束[CONSTRAINT<约束名>]UNIQUE

UNIQUE用于定义表约束

[CONSTRAINT<约束名>]UNIQUE（<列名>[{,<列名>}]）AnIntroductiontoDatabaseSystem[例]建立一个S表，定义SN为惟一键。CREATETABLES( SNoCHAR(6),SNCHAR(8)CONSTRAINT

SN_UniqUNIQUE,SexCHAR(2),AgeINT,DeptVARCHAR(20))SN_Uniq可以省略SNCHAR(8)UNIQUEAnIntroductiontoDatabaseSystem[例]

建立一个S表，定义SN+SEX为惟一键，此约束为表约束。CREATETABLES( SNoCHAR(6),SNCHAR(8)UNIQUE,SexCHAR(2),AgeINT,DeptVARCHAR(20),CONSTRAINTS_UNIQUNIQUE(SN,Sex))AnIntroductiontoDatabaseSystemPRIMARYKEY约束（主键约束）用于定义基本表的主键，起惟一标识作用PRIMARYKEY与UNIQUE的区别：一个基本表中只能有一个PRIMARYKEY，但可多个UNIQUE对于指定为PRIMARYKEY的一个列或多个列的组合，其中任何一个列都不能出现NULL值，而对于UNIQUE所约束的惟一键，则允许为NULL不能为同一个列或一组列既定义UNIQUE，又定义PRIMARYKEY约束。不能为NULL不能重复AnIntroductiontoDatabaseSystemPRIMARYKEY用于定义列约束CONSTRAINT<约束名>PRIMARYKEYPRIMARYKEY用于定义表约束[CONSTRAINT<约束名>]PRIMARYKEY(<列名>[{,<列名>}])AnIntroductiontoDatabaseSystem[例]建立一个S表，定义SNo为S的主键，建立另外一个数据表C，定义CNo为C的主键。

CREATETABLES( SNoCHAR(6)CONSTRAINTS_PrimPRIMARYKEY,SNCHAR(8),SexCHAR(2),AgeINT,DeptVARCHAR(20))CREATETABLEC( CNoCHAR(5)CONSTRAINTC_PrimPRIMARYKEY,CNCHAR(20),CTINT)AnIntroductiontoDatabaseSystem[例]建立一个SC表，定义SNo+CNo为SC的主键。CREATETABLESC( SNoCHAR(5)NOTNULL,CNoCHAR(5)NOTNULL,ScoreNUMERIC(4,1),CONSTRAINTSC_PrimPRIMARYKEY(SNo,CNo))

AnIntroductiontoDatabaseSystem

FOREIGNKEY约束（外键约束）[CONSTRAINT<约束名>]FOREIGNKEYREFERENCES

<主表名>(<列名>[{,<列名>}])外部键从表主键主表引用

AnIntroductiontoDatabaseSystem[例]建立一个SC表，定义SNo,CNo为SC的外部键。CREATETABLESC( SNoCHAR(5)NOTNULLCONSTRAINTS_ForeFOREIGNKEYREFERENCESS(SNo),CNoCHAR(5)NOTNULLCONSTRAINTC_ForeFOREIGNKEYREFERENCESC(CNo),ScoreNUMERIC(4,1),CONSTRAINTS_C_PrimPRIMARYKEY(SNo,CNo));

AnIntroductiontoDatabaseSystemCHECK约束CHECK约束用来检查字段值所允许的范围在建立CHECK约束时，需要考虑以下几个因素：一个表中可以定义多个CHECK约束。每个字段只能定义一个CHECK约束。在多个字段上定义的CHECK约束必须为表约束。当执行INSERT、UNDATE语句时CHECK约束将验证数据。

[CONSTRAINT<约束名>]CHECK(<条件>)AnIntroductiontoDatabaseSystem[]建立一个SC表，定义Score的取值范围为0～100之间。

CREATETABLESC ( SNoCHAR(5), CNoCHAR(5), ScoreNUMERIC(4,1)CONSTRAINTScore_ChkCHECK(Score>=0ANDScore<=100))AnIntroductiontoDatabaseSystem[例]建立包含完整性定义的学生表。 CREATETABLES ( SNoCHAR(6)CONSTRAINTS_PrimPRIMARYKEY, SNCHAR(8)CONSTRAINTSN_ConsNOTNULL, SexCHAR(2)DEFAULT'男', AgeINTCONSTRAINTAge_ConsNOTNULL CONSTRAINTAge_ChkCHECK(AgeBETWEEN15AND50), DeptCHAR(10)CONSTRAINTDept_ConsNOTNULL)

AnIntroductiontoDatabaseSystemAnIntroductiontoDatabaseSystem数据查询语句格式SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；

AnIntroductiontoDatabaseSystem综合举例(a)

Student学生-课程数据库:学生关系Student、课程关系Course和选修关系SC学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept200215121李勇男20CS200215122刘晨女19IS200215123王敏女18MA200215125张立男19ISAnIntroductiontoDatabaseSystem综合举例课程号Cno课程名Cname先行课Cpno学分Ccredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64Course(b)AnIntroductiontoDatabaseSystem综合举例

(c)SC学号Sno课程号Cno成绩Grade200215121192200215121285200215121388200215122290200215122380AnIntroductiontoDatabaseSystem综合举例查询“信息”系所有学生的信息。将所有学生的年龄加1岁。查询学分在3至5之间的课程的名称和学分。查询所有姓'张'的学生的姓名、学号和性别。查询选课表，查询结果按学号升序列排列，同一学号的按成绩的降序排列。查询选修了3门及3门以上课程的学生的学号。查询其他系中比信息系所有学生年龄都大的学生名单和所在系。AnIntroductiontoDatabaseSystem参考答案(1)SELECT*FROMSWHERESD='信息'(2)UPDATESSETSA=SA+1(3)SELECTCN,CCFROMCWHERECCBETWEEN3AND5(4)SELECTSN,SNO,SSFROMSWHERESNLIKE'张%'(5)SELECT*FROMSCORDERBYSNO,GDESC(6)SELECTSNOFROMSCGROUPBYSNOHAVINGCOUNT(SNO)>=5(7)SELECTSN,SDFROMSWHERESA>ALL

(SELECTSAFROMSWHERESD='信息')

ANDSD<>'信息'AnIntroductiontoDatabaseSystemAnIntroductiontoDatabaseSystemE-R图向关系模型的转换（续）E-R图向关系模型的转换要解决的问题如何将实体型和实体间的联系转换为关系模式如何确定这些关系模式的属性和码转换内容将E-R图转换为关系模型：将实体、实体的属性和实体之间的联系转换为关系模式。AnIntroductiontoDatabaseSystemE-R图向关系模型的转换转换原则1.一个实体型转换为一个关系模式。关系的属性：实体型的属性关系的码：实体型的码例，学生实体可以转换为如下关系模式：

学生（学号，姓名，出生日期，所在系，年级，平均成绩）

性别、宿舍、班级、档案材料、教师、课程、教室、教科书都分别转换为一个关系模式。AnIntroductiontoDatabaseSystemE-R图向关系模型的转换AnIntroductiontoDatabaseSystemE-R图向关系模型的转换转换原则2.一个m:n联系转换为一个关系模式。关系的属性：与该联系相连的各实体的码以