数据库技术及应用1复习.ppt

1,数据库技术及应用何ikehelin计算机科学与技术教研室计算机与信息工程学院,2,教材：高等学校计算机教育系列规划教材数据库应用基础教程王小玲刘卫国主编严晖周肆清副主编中国铁道出版社,3,总复习,4,第1章数据库系统概论,5,数据库系统的组成数据库系统是由计算机系统、数据库及其描述机构、数据库管理系统和有关人员组成的具有高度组织性的总体。DBS=DB+DBMS+DBA,6,概念模型-实体联系图（E-R图）概念模型是反映实体之间联系的模型。E-R模型:描述现实世界的概念模型，独立于具体的DBMS所支持的数据模型，它是各种数据模型的共同基础。实体：用矩形框表示，框内写入实体名。属性：用椭圆形表示，属性的名称记入椭圆形内。联系：用菱形框表示实体间的相互关系，框内注明联系的名称。,7,E-R模型示例,8,数据模型数据模型是实体之间联系的抽象描述即数据结构。数据模型不同，描述和实现方法也不同，相应的支持软件DBMS也不同。,常见的数据模型：层次数据模型、网状数据模型、关系数据模型。,9,以学生信息系统中的E-R模型为例，按实体间不同的联系方式来分别讨论将E-R图转化为关系模型的一般方法，进而讨论一个关系数据库的实际例子。,11n联系到关系模型的转化这种联系在进行关系模型转化时，把每个实体分别转化为一个关系，实体名作为关系名，实体属性作为关系的属性，并在1n联系的n方（本例是学生实体）增加一个属性，该属性存放与该实体相联系的另一个实体（本例中是学院）的关键字，即学院编号属性。这样，根据学院与学生这两个实体所转化的关系是：学生（学号，姓名，性别，出生日期，班级，电话，地址，简历，编号），其中学号作为关键字学院（编号，学院名称），其中编号作为关键字,10,2mn联系到关系模型的转化对这样的联系进行关系模型转化时，把两个实体独立地转化为两个关系，转化时，将实体名作为关系名，实体属性转化为关系属性，除此以外单独设置一个关系描述两个实体间的联系，其属性由两个实体的关键字组成。这样，根据学生和课程这两个实体及其联系转化所得到的关系共有3个：学生（学号，姓名，性别，出生日期，班级，电话，地址，简历），其中学号作为关键字课程（课程号，课程名称，课程类型，学分，备注），其中课程号作为关键字选课（学号，课程号，成绩），其中学号和课程号的组合作为关键字,11,311联系到关系模型的转化其转化方法是，将两个实体按上述实体转化方法分别转化为两个关系，并对每个属性增加一个外部关键字，外部关键字由与本实体相联系的对方实体的关键字组成。将一个E-R图中的每组联系的两个实体按上述方法分别转化为关系后，还需要对转化所得到的关系进行整理。如本例，学生实体因为既与学院实体有联系，同时也与课程有联系，上述转化过程中得到了两个不同的学生关系，像这种情况下应取包含较多属性的关系作为最后结果。因此，根据图1.4转化的关系模型应该是：学生（学号，姓名，性别，出生日期，班级，电话，地址，简历，编号）学院（编号，学院名称）课程（课程号，课程名称，课程类型，学分，备注）选课（学号，课程号，成绩）需要说明的是，本系统约定学生关系中的学号由10位数字组成，从左边数起的前2位表示所在学院，其次的2位表示专业，再2位表示年级，再后2位表示班级，最后2位表示所在班级的学生编号。因此，学生表中的学院编号属性可以省去。,12,启动SQLServer2000的有关组件,13,提供了丰富的图形化管理工具，完成几乎所有的数据库管理工作。1、SQLServer服务管理器,14,每个实例有4种服务程序SQLServer服务SQLServerAgent(代理)服务DistributedTransactionCoordinator(DTC，分布式事务协调器）服务MicrosoftSearch（全文检索）服务种服务有3种状态：停止、暂停、运行。,15,SQLServer数据类型,1、数值型整数：int，smallint，tinyint，bigint浮点：float，real精确数值：decimal(p,s)，numeric(p,s)货币数据：money，smallmoney2、字符和二进制型字符型：char(n)，varchar(n)，text二进制型：binary，varbinary，image3、日期时间型：datetime，smalldatetime4、逻辑型：bit,16,9.1数据与表达式,标识符标识符包含的字符数必须在1128之间。标识符有两种类型：常规标识符和分隔标识符。常规标识符的第一个字符必须是字母、下划线（_）、at符号（）或者数字符号（#）。后续字符可以为字母、数字或“”符号、“$”符号、数字符号或下划线。,17,9.1.2常量与变量,常量常量是表示特定数据值的符号，其格式取决于其数据类型1、字符串：Cincinnati2、二进制：0 xAE3、日期/时间：2011-10-204、数值整型常量：1894浮点常量：101.5E5、0.5E-2精确数值常量：1894.1204、2.0货币常量：$12.5、$542023.145、逻辑：1,0，非0的数字当作1处理6、空值：NULL。空值可以表示整型、实型、字符型数据。,18,变量1、局部变量：首字母为单个“”。DECLAREnamevarchar(30),typeint2、全局变量：由系统定义并维护，通过在名称前面加“”符号,9.1.2常量与变量,19,9.1数据与表达式,运算符与表达式,Transact-SQL运算符共有五类：1、算术运算符*、/、%，+、-2、位运算符按位与()、或(|)、异或()、求反()3、比较运算符、=、!=4、逻辑运算符AND、OR、NOT5、连接运算符（字符串运算符）+SQLServer中各种运算符的优先顺序如下：()|*、/、%+、-NOTANDOR,20,第2章SQLServer数据库的管理,21,9.1数据与表达式,数据库对象,基本对象是：表和视图,22,9.1数据与表达式,数据库文件及文件组,SQLServe数据库文件有：1、主数据文件MDF来存储数据库的启动信息和部分或全部数据。2、次数据文件NDF用来存储主数据文件没存储的其他数据。3、事务日志文件LDF用来记录数据库更新情况的文件。SQLServer中文件组出于分配和管理目的，可以将数据库文件分成不同的文件组。一些系统可以通过控制在特定磁盘驱动器上放置的数据和索引来提高自身的性能。主文件组和用户定义的文件组。,23,9.1数据与表达式,SQLServe数据库类型,1、系统数据库Master：记录了SQLServer系统的所有系统信息。Model：所有用户数据库和Tempdb数据库的模板数据库。Msdb：是代理服务数据库，为其警报、任务调度和记录操作员的操作提供存储空间。Tempdb：为所有的临时表、临时存储过程及其它临时操作提供存储空间。2、用户数据库系统示例数据库：Pubs，Northwind用户创建的数据库,24,9.1数据与表达式,数据库的创建,CREATEDATABASERsxxgl_dbON/*创建主文件组的数据文件Rsx1_dat,Rsx2_dat*/PRIMARY(NAME=Rsx1_dat,FILENAME=f:mydbRsx1dat.mdf,SIZE=2MB,MAXSIZE=20,FILEGROWTH=5%),(NAME=Rsx2_dat,FILENAME=f:mydbRsx2dat.ndf,SIZE=2MB,MAXSIZE=20,FILEGROWTH=5%),25,9.1数据与表达式,数据库的创建,/*创建文件组RsxxglGroup1的数据文件RGrp1Fi1_dat,RGrp1Fi2_dat*/FILEGROUPRsxxglGroup1(NAME=RGrp1Fi1_dat,FILENAME=f:mydbRG1Fi1dt.ndf,SIZE=2MB,MAXSIZE=20,FILEGROWTH=5),(NAME=RGrp1Fi2_dat,FILENAME=f:mydbRG1Fi2dt.ndf,SIZE=2MB,MAXSIZE=20,FILEGROWTH=5),26,9.1数据与表达式,数据库的创建,/*创建文件组RsxxglGroup2的数据文件RGrp2Fi1_dat,RGrp2Fi2_dat*/FILEGROUPRsxxglGroup2(NAME=RGrp2Fi1_dat,FILENAME=f:mydbRG2Fi1dt.ndf,SIZE=2MB,MAXSIZE=20,FILEGROWTH=5),(NAME=RGrp2Fi2_dat,FILENAME=f:mydbRG2Fi2dt.ndf,SIZE=2MB,MAXSIZE=20,FILEGROWTH=5),27,9.1数据与表达式,数据库的创建,LOGON/*创建事务日志文件Archlog1*/(NAME=Rsxxgl_log,FILENAME=f:mydbRsxxgllog.ldf,SIZE=1MB,MAXSIZE=25MB,FILEGROWTH=3MB),28,数据库的修改,ALTERDATABASEdatabaseADDFILE,.nTOFILEGROUPfilegroup_name|ADDLOGFILE,.n|REMOVEFILElogical_file_name|ADDFILEGROUPfilegroup_name|REMOVEFILEGROUPfilegroup_name|MODIFYFILE|MODIFYNAME=new_dbname|MODIFYFILEGROUPfilegroup_namefilegroup_property|NAME=new_filegroup_name|SET,.nWITH|COLLATE,29,数据库的删除,DROPDATABASEdatabase_name,.n,30,数据库的分离和附加,分离数据库就是将数据库从SQLServer中删除，但是保持组成该数据的数据文件和事务日志文件中的数据完好无损。在实际工作中，分离数据库作为对数据库的一种备份方法来使用。附加数据库的工作是分离数据库的逆操作，通过附加数据库，可以将没有加入SQLServer服务器的数据库文件添加到服务器中。还可以很方便地在SQLServer服务器之间利用分离后的数据文件和事务日志文件组成新的数据库。,31,数据库备份,备份类型在SQLServer2000中提供了4种不同的类型进行数据备份，它们是：数据库备份（DatabaseBackups）差异备份（DifferentialDatabaseBackups）事务日志备份（TransactionLogBackup）文件或文件组备份（FileandFileGroupBackup）,32,数据库备份,备份设备用来存储数据库、文件和文件组、事务日志备份的存储介质。在SQLServer中进行备份时，必须先创建存放备份数据的备份设备，备份设备可以是磁盘或磁带。如果是备份到磁盘中，可以有两种形式：文件形式和备份设备形式。无论哪种形式，在磁盘中的体现都是文件的形式。,第3章SQLServer数据表,数据表的创建,CREATETABLEdatabase_name.owner.|owner.table_name(|column_nameAScomputed_column_expression|=CONSTRAINTconstraint_name|PRIMARYKEY|UNIQUE)ONfilegroup|DEFAULTTEXTIMAGE_ONfilegroup|DEFAULT=column_namedata_typeCOLLATEDEFAULTconstant_expression,数据表的创建,CREATETABLEBOOK(编号CHAR(6),分类号CHAR(6),书名CHAR(6),单价NUMERIC(10,2),创建Student_1学生信息表，要求包含S_NO（学号）、NAME（姓名）、AGE（年龄）信息，并限制年龄在15到25周岁之间，指定学号为主关键字。创建数据表Student_1的语句如下：CREATETABLEStudent_1(S_NOCHAR(7)NOTNULL,NAMECHAR(10),AGESMALLINT,PRIMARYKEY(S_NO),CHECK(AGEBETWEEN15AND25),createtablestu_con(学号char(4)primarykey(学号),姓名char(8)unique(姓名),性别char(2)default男,出生日期datetimecheck(出生日期1988-1-1),家庭住址varchar(50),语法格式：ALTERTABLEtableALTERCOLUMNcolumn_namenew_data_type参数说明：（1）table：要更改的数据表的名称。（2）ALTERCOLUMN：指定要更改的列。（3）column_name：要更改、添加的列的名称。,数据表的修改,例3-8：删除例3-6中增加的ADDRESS字段。删除该字段的语句如下：ALTERTABLEStudentdropcolumnADDRESS,例3-6：在student学生信息表中增加联系地址ADDRESS字段。增加该字段的语句如下：ALTERTABLEStudentADDADDRESSvarchar(30),例3-7：对上例中增加的字段ADDRESS的宽度由30修改为50。修改该字段的语句如下：ALTERTABLEStudentaltercolumnADDRESSvarchar(50),数据表的删除,可使用DROPTABLE语句语法格式：DROPTABLEtable_name参数说明：table：要删除的数据表的名称。,例3-9：删除当前数据库中的Student_1学生信息表。删除该表的语句如下：DropTABLEStudent_1,注意：（1）DROPTABLE不能用于删除由FOREIGNKEY约束引用的表。必须先删除引用的FOREIGNKEY约束或引用的表。（2）在系统表上不能使用DROPTABLE语句。,例3-10：在同一语句中指定多个表对它们进行删除。假设有一stu数据库，此数据库中存在book表和temp2表，同时删除这两张表的语句如下：DROPTABLEbook,temp2例3-11：删除指定数据库中的表。假设stu数据库内有temp1表，可以在任何数据库内执行如下语句，完成删除temp1表的操作。删除该表的语句如下：DROPTABLEstu.dbo.temp1,表数据的管理,1、表数据的添加可以使用INSERTINTO语句，语法格式：INSERTINTOtable_or_view(column_list)data_values参数说明：INTO：一个可选的关键字，可以将它用在INSERT和目标表之间。table_or_view：要输入数据的表名或视图名。(column_list)：要在其中插入数据的一列或多列的列表。必须用圆括号将column_list括起来，并且用逗号进行分隔。data_values：作为一行或者多行插入已命名的表或视图中。,例3-13：假设数据库Student_db中已经创建好了课程信息数据表C_Info（参考1.3.3节的相关内容），试将新课程记录（9720044，网络技术与应用，选修，3）添加到课程信息数据表C_Info中。添加新课程记录的语句如下：INSERTINTOC_InfoVALUES(9720044,网络技术与应用,选修,3,null),例3-14：在C_Info表中添加一条新记录，课程类别和课程说明暂缺。根据题意知，要求添加3个字段的数据，使用如下语句：INSERTINTOC_Info(C_NO,C_Name,C_Credit)VALUES(9720045,Web开发技术,2),注意：使用INSERT语句一次只能为表插入一行数据。如果INSERT语句违反约束或规则，或者它有与列的数据类型不兼容的值，那么该语句就会失败，并且SQLServer将显示错误信息。此时必须列出列名（因为提供的值的个数与表中列的个数不一致）。C_Info中的课程类别和课程说明列必须允许为NULL，因为系统实际插入的数据为：(9720045,Web开发技术,null,2,null)。,2、表数据修改使用UPDATE语句，语法格式：UPDATEtable_name|view_nameSETcolumn_name=expression|DEFAULT|NULL|variable=expression|variable=column=expression,.nFROM,.n,参数说明：table_name|view_name：要修改数据的表名或视图名。SET子句：引出后面的赋值表达式。column_name=expression|DEFAULT|NULL：指定要更改数据的列的名称或变量名称和它们的新值。也可指定使用对列定义的默认值替换列中的现有值。variable=expression：已声明的变量，该变量将设置为expression所返回的值。variable=column=expression,.n：将变量设置为与列相同的值。FROM,.n：指定修改的数据将来自一个或多个表或视图。,例3-15：将数据表C_Info中的所有学分加1。这是无条件的修改数据，使用语句如下：UPDATEC_InfoSETC_Credit=C_Credit+1例3-16：将数据表C_Info中课程号为“9710011”的学分减1。这是有条件的修改数据，使用语句如下：UPDATEC_InfoSETC_Credit=C_Credit-1WHEREC_NO=9710011,3、表数据的删除数据表中当确定有些记录不需要时，就可以将其删除。其语法格式：DELETEFROMtable_name|view_nameFROM,.n参数说明：FROM：是可选的关键字，可用在DELETE关键字与目标table_name、view_name之间。table_name|view_name：要删除行的表名或视图名。FROM,.n：指定删除时用到的额外的表或视图及联接的条件。,例3-17：在student表中删除所有学生记录，但保留数据表结构。这是无条件的删除记录，使用语句如下：DELETEFROMstudent例3-18：在C_Info表中删除课程号为“9720045”的记录。这是有条件的删除记录，使用语句如下：DELETEFROMC_InfoWHEREC_NO=9720045,数据完整性管理,1、实体完整性：主属性不能为空标识列IDENTITY主键约束PRIMARYKEY唯一性约束UNIQUE唯一性索引UNIQUEINDEX2、域完整性：限制向列中输入的内容限制类型：定义表结构时确定列类型使用约束：CHECK,DEFAULT,NOTNULL规则默认3、引用完整性：设置外键，或为空，或为相关联表中主键的值。,第4章数据查询,SQL操作是通过关系运算实现的，基本的关系运算有选择、投影、联接三种形式。选择操作用来限制表中与给定条件相匹配的行。投影操作通常被用来限制结果集的列数，可以垂直地选择单列或列集作为输出结果。联接操作被用来将一个表与另一个表中列值匹配的行连接起来，通常用一个表中的主键与另一个表中的外键匹配。,SELECT语句的主要子句如下：SELECTselect_listINTOnew_tableFROMtable_sourceWHEREsearch_conditionGROUPBYgroup_by_expressionHAVINGsearch_conditionORDERBYorder_expressionASC|DESC,使用如下3个表，写出操作语句。部门：部门号C(8)，部门名C(12)，负责人C(6)，电话C(16)职工：部门号C(8)，职工号C(10)，姓名C(8)，性别C(2)，出生日期D工资：职工号C(10)，基本工资N(8.2)，津贴N(8.2)，奖金N(8.2)，扣除N(8.2),查询职工的实发工资。SELECT姓名,(基本工资+津贴+奖金-扣除)AS实发工资FROM工资,职工WHERE职工.职工号=工资.职工号查询1962年10月27日出生的职工信息。SELECT*FROM职工WHERE出生日期=1962-10-27,查询每个部门年龄最长者的信息，要求得到的信息包括部门名和最长者的出生日期。SELECT部门名,MIN(出生日期)FROM部门JOIN职工ON部门.部门号=职工.部门号GROUPBY部门名,查询所有目前年龄在35岁以上(不含35岁)的职工信息(姓名、性别和年龄)。SELECT姓名,性别,YEAR(GETDATE()-YEAR(出生日期)AS年龄FROM职工WHEREYEAR(GETDATE()-YEAR(出生日期)35,查询有10名以上(含10名)职工的部门信息(部门名和职工人数)，并按职工人数降序排序。SELECT部门名,COUNT(职工号)AS职工人数FROM部门,职工WHERE部门.部门号=职工.部门号GROUPBY部门名HAVINGCOUNT(*)=10ORDERBY职工人数DESC,LIKE的通配符SQL提供了LIKE子句来进行模糊搜索。,WHERE子句中的条件运算符IN、ALL、BETWEEN等,联接查询,联接类型1、联接条件在WHERE子句中指定时，它与WHERE子句的搜索条件组合，用于控制FROM子句引用的基表中所选定的行。,SELECTSt.St_Id,St.St_Name,C.C_Name,SC.ScoreFROMSt_Infost,C_Infoc,S_C_InfoscWHERESt.St_Id=SC.St_IdANDSC.C_No=C.C_Noandscore80,2、联接条件也可以在FROM子句中建立。由FROM子句指定联接有助于将联接操作与WHERE子句中的搜索条件区分开来。所以，在Transact-SQL中推荐使用这种方法。,FROM子句联接的语法格式如下：FROMjoin_tablejoin_typeJOINjoin_tableONjoin_condition其中：（1）join_table指出参与联接操作的表名（2）join_type指出联接类型，可分为内联接、外联接和交叉联接三种类型。（3）ONjoin_condition指出联接条件，,它由被联接表中的列和比较运算符、逻辑运算符等构成。,SELECTs.St_Id,s.St_Name,c.C_Name,sc.ScoreFROMSt_InfosJOINS_C_InfoscONs.St_Id=sc.St_IdJOINC_InfocONsc.C_No=c.C_NoWHEREScore80,第5章索引与视图,索引,索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可以加快数据的查找，并且能够实现某些数据的完整性。索引键上建立逻辑顺序，基本表上为物理顺序,索引的分类1惟一性索引建立惟一性索引时，组成该索引的字段或字段组合在表中具有惟一值，也就是说，对于表中的任何两行记录来说，索引键的值都各不相同。2主键索引数据库表中通常有一个字段或多个字段组合，其值用来惟一标识表中的每一行记录，该字段或字段组合称为表的“主键”。在数据库关系图中为表定义一个主键时，将自动创建主键索引，主键索引是惟一索引的特殊类型。3聚集索引表中各记录的物理顺序与键值的逻辑顺序相同，数据会按照索引键值指定的顺序存储到表中。在一个表中只能建立一个聚集索引。可建立多个非聚集索引。,视图,视图是一个虚拟表，并不包含任何的物理数据，数据仍存放在定义视图的基本表中。视图如同基本表一样，可作为查询的数据来源，视图在逻辑上也包含一系列带有名称的列和数据行，但数据库中只存放视图的定义。对视图的操作与对表的操作一样，可以对其进行查询、修改和删除。当对通过视图看到的数据进行修改时，相应的基表的数据也会发生变化，同样，若基表的数据发生变化，这种变化也会自动地反应到视图中。对视图所引用的基本表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。,创建视图,使用SQL语言创建视图的命令格式如下：CREATEVIEWview_nameASselect_statement参数说明：（1）view_name：视图名称（2）select_statement：数据查询语句,在student_db数据库中创建score_view视图，该视图选择3个基表(st_Info,c_Info,s_c_info)中的数据来显示学生成绩。CREATEVIEWscore_viewASSELECTst_info.st_ID,st_info.st_name,st_info.cl_name,c_info.c_name,c_info.c_type,s_c_info.scoreFROMc_infoINNERJOINs_c_infoONc_Info.c_no=s_c_info.c_noINNERJOINst_infoONs_c_info.st_ID=st_info.st_ID,修改视图,语法格式：ALTERVIEWview_nameASselect_statement参数说明：（1）view_name：视图名称（2）select_statement：数据查询语句,修改st_view视图：将视图中选择学生信息表st_info中的所有女学生修改为选择所有男学生。ALTERVIEWst_viewASSELECT*FROMst_infoWHEREst_sex=男,删除视图,语法格式：DROPVIEWview,.n参数说明：（1）view：要删除的视图名称（2）n：表示可以指定多个视图,删除score_view视图。USEstudent_dbIFEXISTS(SELECT*FROMsysobjectsWHERENAME=score_view)DROPVIEWscore_viewGO,如果不需要确认视图是否存在，则不必对视图进行存在性检查,第6章存储过程与触发器,存储过程,存储过程（StoredPeocedure）是一种数据库对象，是SQLServer服务器中一组预编译的TransactSQL语句的集合，可包含程序流、逻辑控制流以及对数据库的查询，可以接受输入参数、输出参数、返回单个或多个结果集以及状态值，并可以重用和嵌套调用，可供用户、其他过程或触发器调用，向向调用者返回数据或实现表中数据的更改以及执行特定的数据库管理任务。,创建存储过程,语法格式：,CREATEPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n,执行存储过程,执行存储过程的语法格式：,EXECUTEreturn_status=procedure_name;number|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,.nWITHRECOMPILE,例：创建带一个输入参数和一个输出参数的存储过程，通过输入参数在St_Info表中查询指定学号的学生，以输出参数的形式返回学生所在的班级名称（cl_name字段）。,CREATEPROCEDUREStClassstidchar(10),class_namechar(20)OUTPUTASSELECTclass_name=cl_nameFROMSt_InfoWHERESt_Info.St_ID=stid,使用方式：使用该存储过程时，必须首先声明一个变量，用于接收该输出变量返回的值：,DECLAREget_clnamechar(20)EXECStClass0603060109,get_clnameOUTPUTPRINTget_clname,修改存储过程,语法格式如下（与创建语句类似）：ALTERPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONF