章节学习-第五章数据库

计算机软件技术基础

第五章数据库信息技术系徐家臻本课主要内容数据库基础知识关系数据模型VisualStudio管理数据库SqlServerSQL语言DDLDML数据库基础知识数据库（Database,简称DB）是指长期存储在计算机内、有组织、可共享的数据集合。数据库管理系统(DatabaseManagementSystem,简称DBMS)是指用于完成数据库的定义、建立、运行和维护，使用户能方便地操纵和控制数据，并能保证数据的安全性、完整性、并发性和可恢复性的软件系统。数据库基础知识数据库系统在计算机系统中的位置用系应统

发工开具用软应件BMDS编统译系作系操统硬件数据库基础知识应用程序与数据的关系DBMS应用程序1应用程序2数据库…数据库基础知识ACCESSSQLiteMYSQLSQLSERVERORACLESYSBASEDB2支持操作系统WinWin/UnixWin/UnixWinWin/UnixWin/UnixUnix公司MS开源开源MSORACLESYSBASEIBM专业性非专业小型专业小型专业大中型专业大中型专业大型专业大型专业大型当前主要数据库管理系统数据库基础知识DBMS的主要功能数据定义功能:提供数据定义语言(DDL,DataDefinitionLanguage)，用于定义数据库中的数据对象。数据操纵功能:提供数据操纵语言(DML,DataManipulationLanguage)，用于操纵数据实现对数据库的基本操作(查询、插入、删除和修改)。数据库的运行管理:保证数据的安全性、完整性、多用户对数据的并发使用及发生故障后的系统恢复。数据库基础知识DBMS的主要功能数据库的运行管理:保证数据的安全性、完整性、多用户对数据的并发使用及发生故障后的系统恢复。数据库的建立和维护功能:提供实用程序，完成数据库数据批量装载，数据库转储，介质故障恢复，数据库的重组织和性能监视等数据库基础知识概念模型实体（Entity）客观存在并可相互区别的事物称为实体。可以是具体的人、事、物或抽象的概念。属性（Attribute）实体所具有的某一特性称为属性。一个实体可以由若干个属性来刻画。码/键（Key）唯一标识实体的属性集称为码/键。实体集（EntitySet）同型实体的集合称为实体集。联系（Relationship）数据库基础知识联系（Relationship）现实世界中事物内部以及事物之间的联系在信息世界中反映为实体内部的联系和实体之间的联系。实体内部的联系：组成实体的各属性之间的联系。实体之间的联系：不同实体集之间的联系。三类实体间联系一对一联系（1:1）一对多联系（1:n）多对多联系（m:n）数据库基础知识一对一联系如果对于实体集A中的每一个实体，实体集B中至多有一个实体与之联系，反之亦然，则称实体集A与实体集B具有一对一联系。记为1:1。实例班级与班长之间的联系：一个班级只有一个正班长，而一个班长只在一个班中任职数据库基础知识一对多联系如果对于实体集A中的每一个实体，实体集B中有n个实体（n≥0）与之联系，反之，对于实体集B中的每一个实体，实体集A中至多只有一个实体与之联系，则称实体集A与实体B有一对多联系。记为1:n实例班级与学生之间的联系：一个班级中有若干名学生，而每个学生只在一个班级中学习数据库基础知识多对多联系（m:n）如果对于实体集A中的每一个实体，实体集B中有n个实体（n≥0）与之联系，反之，对于实体集B中的每一个实体，实体集A中也有m个实体（m≥0）与之联系，则称实体集A与实体B具有多对多联系。记为m:n实例课程与学生之间的联系：一门课程同时有若干个学生选修，而一个学生可以同时选修多门课程数据库基础知识班级班级-班长班长111:1联系课程选修学生mnm:n联系班级组成学生1n1:n联系表示方法（E-R图）E-R图实体型用矩形表示，矩形框内写明实体名。学生教师E-R图属性用椭圆形表示，并用无向边将其与相应的实体连接起来学生学号年龄性别姓名E-R图(续)联系用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体连接起来，同时在无向边旁标上联系的类型（1:1、1:n或m:n）E-R图实体型1联系名实体型2111:1联系实体型1联系名实体型2mnm:n联系实体型1联系名实体型21n1:n联系E-R图多个实体型间的一对多联系实例对于课程、教师与参考书三个实体型，如果一门课程可以有若干个教师讲授，使用若干本参考书，而每一个教师只讲授一门课程，每一本参考书只供一门课程使用，则课程与教师、参考书之间的联系是一对多的E-R图课程讲授教师1m多个实体型间的1:n联系参考书nE-R图联系联系的属性：联系本身也是一种实体型，也可以有属性。如果一个联系具有属性，则这些属性也要用无向边与该联系连接起来。联系属性的表示方法课程选修学生mn成绩关系数据模型现在的绝大多数数据库都采用关系数据模型。关系模型的基本数据结构在用户观点下，关系模型中数据的逻辑结构是一张二维表，它由行和列组成。关系数据模型将概念模型转换为关系模型。实体集：直接用数据表来表示。属性：用属性名表示。实体名（属性1，属性2，…，属性n）例如：学生信息表（学号，姓名，年龄，性别，年级）联系一对一联系：隐含在实体表中。一对多联系：隐含在实体表中。多对多联系：直接用关系表来表示。关系数据模型例1学生、系、系与学生之间的一对多联系：学生（学号，姓名，年龄，性别，系号，年级）系(系号，系名，办公地点)例2学生、课程、学生与课程之间的多对多联系：

学生信息表（学号，姓名，年龄，性别，系号，年级）课程信息表（课程号，课程名，学分）(选课)成绩表（学号，课程号，成绩）

学生学生选课课程关系数据模型SQL语言SQL(结构化查询语言，StructuredQueryLanguage)SQL语言定义了一套标准语法，集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体。各种数据库产品基本都实现了这些SQL语法。用户只需提出做什么，而不必指明怎么做。SQL语言是不区分大小写的SQL语言SQL语言基本表和视图基本表本身独立存在的表，一个实体集/关系对应一个表一个（或多个）基本表对应一个存储文件一个表可以带若干索引，索引也存放在存储文件中主键(primarykey)是表中的一个或多个字段，它的值用于惟一地标识表中的某一条记录。存储文件存储文件的物理结构是任意的，对用户是透明的视图从一个或几个基本表或视图导出的表是虚表，只存放视图的定义而不存放对应数据SQL语言创建表语句格式CREATETABLE<表名>

（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；<表名>：所要定义的基本表的名字<列名>：组成该表的各个属性（列）<列级完整性约束条件>：涉及相应属性列的完整性约束条件<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件创建表常用完整性约束主码约束：PRIMARYKEY参照完整性约束默认值：DEFAULT非空值约束：NOTNULL取值约束：CHECK常见的数据类型char(n)最大长度为n的定长字符串，ANSI字符集存储方式（存储每个英文字符占用1长度，存储每个中文字符占用2长度）。nchar(n)最大长度为n的定长字符串，Unicode字符集存储方式（存储每个字符占用2长度）。varchar(n)、nvarchar(n)最大长度为n的变长字符串。与char(n)、nchar(n)的区别是：若一个列被声明为定长字符串，无论实际存储多少个字符，都会占据n个字节存储空间，实际长度不足n的在字符串后面补空格；变长字符串则只占据比实际存储内容稍大的存储空间。定长字符串的好处是存储和读取速度更快。常见的数据类型int整数，取值范围：–2,147,483,648~2,147,483,647float、real浮点数。近似值，不保证100%精确。float取值范围：–1.79e+308~1.79e+308real取值范围：–3.40e+38~3.40e+38decimal(d,p)/numeric(d,p)一定范围的精确数值d–小数点左右两边的数字位数之和，d≤38p–小数点右边的数字位数常见的数据类型datetime日期时间bit值为0或1，通常用来存储trueorfalse类型数据binary(n)、varbinary(n)n个字节的二进制数据修改表语句格式ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][MODIFY<列名><数据类型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性约束条件MODIFY子句：用于修改列名和数据类型删除表

语句格式

DROPTABLE<表名>;系统删去该表的描述和该表上的所有索引的描述系统从文件中删去表中的数据表上的视图往往仍然保留，但无法引用

建立与删除索引

建立索引是加快查询速度的有效手段建立索引

DBMS自动建立

PRIMARYKEYDBA或开发人员根据需要建立

使用索引

DBMS自动选择是否使用索引以及使用哪些索引建立索引语句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)；

用<表名>指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引建立索引唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束。建立索引聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致。聚簇索引对于那些经常要搜索范围值的列特别有效。使用聚簇索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。对于某些类型的查询，可以提高查询效率。在一个基本表上最多只能建立一个聚簇索引查询单表查询连接查询嵌套查询集合查询查询语句格式SELECT[DISTINCT]<目标列表达式>[<别名>][，<目标列表达式>[<别名>]]…FROM<表名或视图名>[<别名>][，<表名或视图名>[<别名>]]…[WHERE<条件表达式>][GROUPBY<列名>[,<列名>]…[HAVING<条件表达式>]][ORDERBY<列名>[,<列名>]…[ASC|DESC]]；查询SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的记录为一个组。通常会在每组中作用集函数。HAVING短语：筛选出满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序或降序排序示例数据库学生-课程数据库学生表：

Student(Sno，Sname，Ssex，Sage，Sdept)课程表：

Course(Cno，Cname，Cpno，Ccredit)

学生(选课)成绩表：

SC(Sno，Cno，Grade)SELECT查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent在SELECT关键字后面列出所有列名在FROM关键字后面列出表名查询全体学生的所有属性记录SELECT*FROMStudentDISTINCT在SELECT子句中使用DISTINCT短语消除取值重复的行。假设SC表中有下列数据

SnoCnoGrade------------------------20110011922011001285201100138820110022902011002380DISTINCT SELECTDISTINCTSnoFROMSC; Sno----------20110012011002WHERE通过WHERE子句实现 比较大小 确定范围 字符串匹配 涉及空值的查询 多重条件查询WHEREWHERE子句常用的查询条件WHERE查询计算机系全体学生的名单。SELECTSnameFROMStudentWHERESdept='CS'；查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage<20；WHERE查询既不是历史、数学系，也不是计算机系的学生的姓名和性别。SELECTSname，SsexFROMStudent WHERESdeptNOTIN('HIS'，'MA'，'CS');查所有有成绩的学生学号和课程号。

SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；WHERE查询所有姓刘学生的姓名、学号和性别。

SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘刘%’；通配符%代表任意长度（长度可以为0）的字符串。

_代表任意单个字符。WHERE多重查询用逻辑运算符AND和OR来联结多个查询条件

AND的优先级高于OR

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

[NOT]IN[NOT]BETWEEN…AND…WHERE查询计算机系年龄在20岁以下的学生姓名。

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；ORDERBY使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的记录最后显示DESC：排序列为空值的记录最先显示ORDERBY查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT*FROMStudentORDERBYSdept，SageDESC；聚集函数COUNT(列名，下同) 符合条件的记录数SUM() 求和MAX() 求最大值MIN() 求最小值AVG() 求平均值聚集函数查询学生总人数。

SELECTCOUNT(*)FROMStudent；查询选修了课程的学生人数。

SELECTCOUNT(DISTINCTSno)FROMSC；GROUPBY使用GROUPBY子句分组分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和聚集函数GROUPBY查询各个课程号及相应的选课人数。

SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；查询各个课程号，及相应的课程成绩在90分以上的学生人数。

SELECTCno，COUNT(Sno)FROMSCWHEREGrade>=90GROUPBYCno；连接查询同时涉及多个表的查询称为连接查询连接条件用来连接两个表的条件称为连接条件或连接谓词。连接含义逐一查找满足表1和表2连接条件的记录，每找到一对这样的记录，就将它们拼接起来，按照选择的列，形成结果表中一条记录。连接查询查询每个学生及其选修课程的情况。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；连接查询外连接外连接两表时，一张为主体表，一张为非主体表。非主体表有一"万能"的虚行，该行全部由空值组成。虚行可以和主体表中所有不满足连接条件的记录进行连接。由于虚行的存在，主体表中的每条记录至少在查询结果中出现一次。由于虚行各列全部是空值，因此与虚行连接的结果中，来自非主体表的属性值全部是空值。左外连接左表为主体表 右外连接右表为主体表连接查询查询每个学生及其选修课程的情况，要求即使该学生没有选修任何课程，他的信息也会在结果中出现一次。

SELECTStudent.Sno，Sname，Ssex， Sage，Sdept，Cno，GradeFROMStudentLEFTOUTERJOINSCONStudent.Sno=SC.Sno；嵌套查询嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块中的查询称为嵌套查询查询所有选了编号为2的课程的学生姓名。

SELECTSname FROMStudentWHERESnoIN

(SELECTSnoFROMSCWHERECno='2');嵌套查询EXISTS谓词存在量词

带有EXISTS谓词的子查询不返回任何数据，只产生真(true)或假(false)查询所有没有选修1号课程的学生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoAND Cno='1')；数据更新插入数据INSERT修改数据UPDATE删除数据DELETE插入数据语句格式

INSERTINTO<表名>[(<属性列1>[，<属性列2>…)]VALUES(<常量1>[，<常量2>]…)DBMS在执行插入语句时会检查所插记录是否破坏表上已定义的约束。对于有NOTNULL约束的属性列是否提供了非空值对于有UNIQUE约束的属性列是否提供了非重复值对于有值域约束的属性列所提供的属性值是否在值域范围内插入数据将一个新学生记录（学号：2011020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。

INSERTINTOStudentVALUES('2011020'，'陈冬'，'男'，'IS'，18)；插入一条选课记录('2011020'，'1')。

INSERTINTOSC(Sno，Cno)VALUES('2011020'，'1')；修改数据语句格式

UPDATE<表名>SET<列名>=<表达式>[，<列名>=<表达式>]…[WHERE<条件>]；修改指定表中满足WHERE子句条件的记录。DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的约束。修改数据将学生2011001的年龄改为22岁。

UPDATEStudentSETSage=22WHERESno='2011001'将所有学生的年龄增加1岁。

UPDATEStudentSETSage=Sage+1；修改数据将计算机科学系全体学生的成绩置零。

UPDATESCSETGrade=0WHERESNOin(SELETESn