数据库原理与应用第三章

数据库原理与应用第三章第1页，共75页，2023年，2月20日，星期六3.1SQL概述SQL的特点1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方法5.语言简洁，易学易用第2页，共75页，2023年，2月20日，星期六5.语言简捷，易学易用第3页，共75页，2023年，2月20日，星期六3.2数据定义第4页，共75页，2023年，2月20日，星期六3.2.1定义语句格式CREATETABLE<表名>(<列名><数据类型>[<列级完整性约束>],<列名><数据类型>[<列级完整性约束>],……,[<表级完整性约束>])[<其它参数>]Ÿ

<表名>给出要创建的基本表的名称；Ÿ

<列名>给出列名或字段名；Ÿ

<数据类型>Ÿ

<列级完整性约束>Ÿ

<表级完整性约束>Ÿ

<其它参数>第5页，共75页，2023年，2月20日，星期六为列指定数据类型及其数据宽度；

关系数据库支持非常丰富的数据类型，不同的数据库管理系统支持的数据类型基本是一样的，右表列出了常用的数据类型。

数据类型第6页，共75页，2023年，2月20日，星期六用于定义列或字段一级的完整性约束，一般包括：NOTNULL和NULL约束

PRIMARYKEY约束

UNIQUE约束

FOREIGNKEY约束

DEFAULT定义

CHECK约束

列级完整性约束第7页，共75页，2023年，2月20日，星期六用于定义表一级的完整性约束，一般包括：PRIMARYKEY约束（复合属性构成的主关键字说明）FOREIGNKEY约束（外部关键字及参照关系说明）CHECK约束（同时涉及到多个属性的域完整性约束）

表级完整性约束第8页，共75页，2023年，2月20日，星期六不是SQL的标准选项，一般用于与物理存储有关的说明，不同的数据库管理系统定义的方式肯定不同，另外该项参数一般也不是必需的。

其他参数第9页，共75页，2023年，2月20日，星期六例题[例1]建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15))；第10页，共75页，2023年，2月20日，星期六二、修改基本表ALTERTABLE<表名>[ADD<新列名><数据类型>[<列级完整性约束>]]|[DROP<完整性约束名>][MODIFY<列名><数据类型>][DROPCOLUMN<列名>][ALTERCOLUMN<列名><数据类型>[<列级完整性约束>]]第11页，共75页，2023年，2月20日，星期六例题[例2]向Student表增加“入学时间”列，其数据类型为日期型。

ALTERTABLEStudentADDScomeDATE；不论基本表中原来是否已有数据，新增加的列一律为空值。

第12页，共75页，2023年，2月20日，星期六例题[例3]将年龄的数据类型改为半字长整数。

ALTERTABLEStudentMODIFYSageSMALLINT；修改原有的列定义可能会破坏已有数据。

第13页，共75页，2023年，2月20日，星期六例题[例4]删除关于学号必须取唯一值的约束。

ALTERTABLEStudentDROPUNIQUE（Sno）；SQL没有提供删除属性列的语句，只能间接实现，先将原表中要保留的列及其内容复制到一个新表中，然后删除原表，并将新表命名为原表名。

第14页，共75页，2023年，2月20日，星期六三、删除基本表 DROPTABLE<表名>;

基本表删除 数据、表上的索引都删除 表上的视图往往仍然保留，但 无法引用删除基本表时，系统会自动从数据字典中删去有关该基本表及其索引的描述，因此建立在此表上的视图虽然已保留，但已无法引用第15页，共75页，2023年，2月20日，星期六例题例5删除Student表DROPTABLEStudent;第16页，共75页，2023年，2月20日，星期六3.2.2建立与删除索引建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立和删除有些DBMS自动建立以下列上的索引

PRIMARYKEYUNIQUE维护索引

DBMS自动完成

使用索引

DBMS自动选择是否使用索引以及使用哪些索引第17页，共75页，2023年，2月20日，星期六一、建立索引语句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引第18页，共75页，2023年，2月20日，星期六建立索引（续）唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束第19页，共75页，2023年，2月20日，星期六建立索引（续）聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放

第20页，共75页，2023年，2月20日，星期六建立索引（续）在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可以提高查询效率聚簇索引的适用范围很少对基表进行增删操作很少对其中的变长列进行修改操作第21页，共75页，2023年，2月20日，星期六聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。例CREATECLUSTERINDEXStusnameONStudent(Sname);将会在Student表的Sname列上建一个聚簇索引，而且记录会按照Sname值的升序存放。用户可以在最常查询的列上建立聚簇索引以提高查询效率。但在一个基本表上只能建一个，而且更新索引列数据会导致表中记录的物理顺序的变更，因此经常更新的数据列不宜建立。第22页，共75页，2023年，2月20日，星期六例题[例6]为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；第23页，共75页，2023年，2月20日，星期六二、删除索引DROPINDEX<索引名>；删除索引时，系统会从数据字典中删去有关该索引的描述。[例7]删除Student表的Stusname索引。 DROPINDEXStusname；第24页，共75页，2023年，2月20日，星期六3.3.1概述语句格式SELECT[ALL|DISTINCT]<目标表达式>[,<目标表达式>]…FROM<表名或视图名>[,<表名或视图名>…][WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；第25页，共75页，2023年，2月20日，星期六示例数据库学生-课程数据库学生表：Student(Sno，Sname，Ssex，Sage，Sdept)课程表：Course(Cno，Cname，Cpno，Ccredit)学生选课表：SC(Sno，Cno，Grade)第26页，共75页，2023年，2月20日，星期六3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结第27页，共75页，2023年，2月20日，星期六3.3.2单表查询

查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组

第28页，共75页，2023年，2月20日，星期六第29页，共75页，2023年，2月20日，星期六第30页，共75页，2023年，2月20日，星期六从职工关系中检索所有工资值SELECT工资FROM职工结果是：12201210125012301250SELECTDISTINCT工资FROM职工结果是：1220121012501230第31页，共75页，2023年，2月20日，星期六单表查询用户在查询时可根据应用的需要改变列的显示顺序例2查询全体学生的姓名、学号、所在系。查询全部列-将所有的列名在SELECT后面列出或者用*表示列名例3SELECT*FROMStudent;查询经过计算的值例4查询全体学生姓名及其出生年份SELECTSname,1996-SageFROMStudent;第32页，共75页，2023年，2月20日，星期六单表查询<目标列表达式>不仅可以是算术表达式，还可以是字符串常量、函数等。例5查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名SELECTSname,’YearofBirth:’,1996-Sage,ISLOWER(Sdept)FROMStudent;输出结果：Sname’YearofBirth:’1996-SageISLOWER(Sdept)------------------------------------------------------李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is第33页，共75页，2023年，2月20日，星期六[例5.*]使用列别名改变查询结果的列标题SELECTSnameNAME，'YearofBirth:’

BIRTH，

2000-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROM

Student；输出结果：

NAMEBIRTHBIRTHDAYDEPARTMENT------------------------------------------------------李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is第34页，共75页，2023年，2月20日，星期六二、选择表中的若干元组消除取值重复的行查询满足条件的元组第35页，共75页，2023年，2月20日，星期六1.消除取值重复的行在SELECT子句中使用DISTINCT短语假设SC表中有下列数据 SnoCnoGrade---------------------9500119295001285950013889500229095002380第36页，共75页，2023年，2月20日，星期六ALL与

DISTINCT

[例6]查询选修了课程的学生学号。(1)SELECTSnoFROMSC;(因为默认的是ALL)SELECTALLSnoFROMSC;

所以结果为：Sno-------9500195001950019500295002第37页，共75页，2023年，2月20日，星期六例题（续）(2)SELECTDISTINCTSnoFROMSC;

结果：

Sno-------9500195002总结：两个本来并不完全相同的元组，投影到指定的某些列上后，可能变成完全相同的行，于是必须用DISTINCT短语或象下面那样用WHERE语句。第38页，共75页，2023年，2月20日，星期六2.查询满足条件的元组WHERE子句常用的查询条件第39页，共75页，2023年，2月20日，星期六(1)比较大小在WHERE子句的<比较条件>中使用比较运算符=，>，<，>=，<=，!=或<>，!>，!<，逻辑运算符NOT+比较运算符[例8]查询所有年龄在20岁以下的学生姓名及其年龄。

SELECTSname，Sage

FROMStudentWHERESage<20；或SELECTSname，SageFROMStudentWHERENOTSage>=20；第40页，共75页，2023年，2月20日，星期六例9查询考试成绩有不及格的学生的学号SELECTDISTINCTSnoFROMSCWHEREGrade<60;第41页，共75页，2023年，2月20日，星期六(2)确定范围使用谓词BETWEEN…(下限)AND…（上限）NOTBETWEEN…AND…[例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；

第42页，共75页，2023年，2月20日，星期六例题（续）[例11]查询年龄不在20~23岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；

第43页，共75页，2023年，2月20日，星期六(3)确定集合使用谓词IN<值表>,NOTIN<值表><值表>：用逗号分隔的一组取值[例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');第44页，共75页，2023年，2月20日，星期六(3)确定集合[例13]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');第45页，共75页，2023年，2月20日，星期六(4)字符串匹配[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，即不含通配符时可以用=运算符取代LIKE谓词用!=或<>运算符取代NOTLIKE谓词第46页，共75页，2023年，2月20日，星期六通配符%(百分号)代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_(下横线)代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串第47页，共75页，2023年，2月20日，星期六ESCAPE短语：当用户要查询的字符串本身就含有%或_时，要使用ESCAPE'<换码字符>'短语对通配符进行转义。第48页，共75页，2023年，2月20日，星期六例题1)匹配模板为固定字符串[例14]查询学号为95001的学生的详细情况。

SELECT*FROMStudentWHERESnoLIKE'95001'；等价于：

SELECT*FROMStudentWHERESno='95001'；第49页，共75页，2023年，2月20日，星期六例题（续）2)匹配模板为含通配符的字符串[例15]查询所有姓刘学生的姓名、学号和性别。

SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘刘%’；第50页，共75页，2023年，2月20日，星期六例题（续）匹配模板为含通配符的字符串（续）[例16]查询姓"欧阳"且全名为三个汉字的学生的姓名。

SELECTSnameFROMStudentWHERESnameLIKE'欧阳__'；第51页，共75页，2023年，2月20日，星期六例题（续）匹配模板为含通配符的字符串（续）[例17]查询名字中第2个字为"阳"字的学生的姓名和学号。

SELECTSname，SnoFROMStudentWHERESnameLIKE'__阳%'；第52页，共75页，2023年，2月20日，星期六例题（续）匹配模板为含通配符的字符串（续）[例18]查询所有不姓刘的学生姓名。

SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'刘%'；第53页，共75页，2023年，2月20日，星期六例题（续）3)使用换码字符将通配符转义为普通字符

[例19]查询DB_Design课程的课程号和学分。SELECTCno，CcreditFROMCourseWHERECnameLIKE'DB\_Design'

ESCAPE'\'第54页，共75页，2023年，2月20日，星期六例题（续）使用换码字符将通配符转义为普通字符(续)[例20]查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；第55页，共75页，2023年，2月20日，星期六(5)涉及空值的查询

使用谓词ISNULL或ISNOTNULL

“ISNULL”不能用“=NULL”代替[例21]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECTSno，CnoFROMSCWHEREGradeISNULL；第56页，共75页，2023年，2月20日，星期六例题(续)[例22]查所有有成绩的学生学号和课程号。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；第57页，共75页，2023年，2月20日，星期六(6)多重条件查询用逻辑运算符AND和OR来联结多个查询条件

AND的优先级高于OR

可以用括号改变优先级可用来实现多种其他谓词

[NOT]IN[NOT]BETWEEN…AND…第58页，共75页，2023年，2月20日，星期六例题[例23]查询计算机系年龄在20岁以下的学生姓名。

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；第59页，共75页，2023年，2月20日，星期六改写[例12][例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS')可改写为：SELECTSname，SsexFROMStudentWHERESdept='IS'ORSdept='MA'ORSdept='CS'；第60页，共75页，2023年，2月20日，星期六改写[例10][例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；可改写为：

SELECTSname，Sdept，SageFROMStudentWHERESage>=20ANDSage<=23；第61页，共75页，2023年，2月20日，星期六三、对查询结果排序

若没有指定查询结果的显示顺序，DBMS按最方便的（元组在表中的先后）方式输出查询结果使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示第62页，共75页，2023年，2月20日，星期六对查询结果排序（续）[例24]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；第63页，共75页，2023年，2月20日，星期六查询结果

SnoGrade--------------9501095024950079295003829501082950097595014619500255第64页，共75页，2023年，2月20日，星期六对查询结果排序（续）[例25]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT*FROMStudentORDERBYSdept，SageDESC；第65页，共75页，2023年，2月20日，星期六四、使用集函数5类主要集函数计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和，（此列必须是数值型的）SUM（[DISTINCT|ALL]<列名>） 计算平均值，（此列必须是数值型的）AVG（[DISTINCT|ALL]<列名>）第66页，共75页，2023年，2月20日，星期六使用集函数（续）求最大值MAX（[DISTINCT|ALL]<列名>）

求最小值MIN（[DISTINCT|ALL]<列名>） DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值第67页，共75页，2023年，2月20日，星期六使用集函数（续）[例26]查询学生总人数。

SELECTCOUNT(*)FROMStudent；

[例27]查询选修了课程的学生人数。

SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重复计算