《数据库原理》知识点总结

数据库系统概述一、有关概念1. 数据2. 数据库（DB3. 数据库管理系统（DBMSAccess桌面 DBMS VFPSQL Server Oracle客户机/服务器型DBMS MySQL DB24. 数据库系统（DBS 数据库（DBI 数据库管理系统（DBMSI 幵发工具I应用系统二、数据管理技术的发展1. 数据管理的三个阶段人工管理文件系统数据库系统数据能否保存不能保存可以保存可以保存数据面向的对 象某一应用程序某一应用程序整个应用系统数据的共享程 度无共享，一组 数据只能对应 一个应用程序。共享性差，一个 数据文件只能 对应一个应用 程序。共享性高数据的独立性不独立，它是应 用程序的一部 分。独立性差数据库与应用 系统完全分幵概念模型一、模型的三个世界1. 现实世界2 .信息世界：即根据需求分析画概念模型（即 E-R图），E-R图与DBMSc 关。3 .机器世界：将E-R图转换为某一种数据模型，数据模型与DBMS相关。注意：信息世界又称概念模型，机器世界又称数据模型二、实体及属性1. 实体：客观存在并可相互区别的事物。2. 属性:3. 关键词（码、key）:能唯一标识每个实体又不含多余属性的属性组合。一个表的码可以有多个，但主码只能有一个。例：借书表（学号，姓名，书号，书名，作者，定价，借期，还期）规定：学生一次可以借多本书，同一种书只能借一本，但可以多次续借。4. 实体型：即二维表的结构例 student（no ，name sex， age，dept）5. 实体集：即整个二维表三、实体间的联系：1. 两实体集间实体之间的联系_ 1: 1联系1: n联系m : n联系2. 同一实体集内实体之间的联系1： 1联系1: n联系m : n联系四、概念模型（常用 E-R图表示）实体型属性：联系：说明：E-R图作为用户与幵发人员的中间语言 E-R 图可以等价转换为层次、网状、关系模型。举例： 学校有若干个系，每个系有若干班级和教研室，每个教研室有若干教员， 其中有的教授和副教授每人各带若干研究生。每个班有若干学生，每个学生选修若干课程， 每门课程有若干学生选修。用 E-R 图画出概念模型。数据模型一、层次模型：用树型结构表示实体之间的联系。 每个结点代表一个实体型。 只能直接处理一对多（含一对一）的实体关系。 查找层次数据库中的记录，速度较慢。二、网状模型：用图结构表示实体之间的联系。 每个结点代表一个实体型。 可以处理多对多的实体关系。 查找网状数据库中的记录，速度最快。三、关系模型：用二维表表示实体之间的联系。1重要术语： 关系：一个关系就是一个二维表； 元组：二维表的一行，即实体； 关系模式：在实体型的基础上，注明主码。 关系模型：指一个数据库中全部二维表结构的集合。2特点： 关系模型是建立在严格的数学理论的基础上的； 关系模型的存取路径对用户透明； 查找关系数据库中的记录，速度最慢。小结：数据有三种类型，DBM就有三种类型，DB亦有三种类型。数据库系统结构一、数据库系统的 体系结构 单机结构：DBMS数据库、幵发工具、应用系统安装在一台计算机上。 C/S 结构：局域网结构 客户机：装开发工具、应用系统 服务器：装 DBM、S 数据库 B/S 结构： Internet 结构服务器：装DBMS数据库、幵发工具、应用系统客户机：装IE即可三、数据库系统的模式结构1. 三级模式 模式：是数据库中全体数据的逻辑结构和特征的描述。模式只涉及数据库的结构；模式既不涉及应用程序，又不涉及数据库结构的存储； 外模式：是模式的一个子集，是与某一个应用程序有关的逻辑表示。特点：一个应用程序只能使用一个外模式，但同一个外模式可为多个应用 程序使用。 内模式：描述数据库结构的存储，但不涉及物理记录。2. 两级映象 外模式/模式映象：保证数据库的逻辑独立性； 模式/内模式映象：保证数据库的物理独立性；3. 两级映象的意义 使数据库与应用系统完全分幵，数据库改变时，应用系统不必改变。 数据的存取完全由DBMS?理，用户不必考虑存取路径。数据库管理系统1. DBMS勺功能：负责对数据库进行统一的管理与控制。 数据定义：即定义数据库中各对象的结构 数据操纵：包括对数据库进行查询、插入、删除、修改等操作。 数据控制：包括安全性控制、完整性控制、并发控制、数据库恢复。2. DBMS勺组成：:DDL语言 DML语言DCL语言1- 实用程序 SQL集DDL DML DCL功能于一体； 所有应用程序通过SQL语句才能访问数据库一、基本概念1. 码：能唯一标识元组的属性集。2. 候选码：一个属性集既能唯一标识元组，且又不含有多余属性，一个关 系模式可以有多个候选码。3. 主码：任选候选码中的一个。4. 主属性：主码中包含的各个属性。5. 非主属性：不包含在主码中的各个属性。6 .外码：设F是关系R的一个属性，不是 R的主码，但却是另一个关系 S 的主码，则称F是关系R的外码。例：student ( sno, sname, ssex, sage, sdept)Sc ( sno, eno, grade)Sc的主码为：(sno,eno );外码为：sno关系的数学定义一、域(domain)1. 定义：域是一组具有相同类型的值的集合。2. 域的基数：域中所含数据的个数。二、笛卡尔积1. 定义：给定一组域 D1,D2,D3，贝V D1X D2X D3称为笛卡尔积。2 .笛卡尔积 D1X D2X D3对应一个二维表，所含元组的个数等于各个域的 基数之积。三、关系1定义：笛卡儿积的一部分元组称为关系。2关系的目(或度)：一个关系所含属性的个数。3. 关系的性质任意两个元组不能完全相同，但属性名允许重复。四、关系的完整性1. 实体完整性：指关系的所有主属性都不能取空值。注意：实体完整性不仅仅是主码整体不能取空值。2. 参照完整性：指一个关系外码的取值必须是相关关系中主码的有效值或 空值。例：班级(班名,人数)学生(学号,姓名，性别，密码，班名)在学生表中，班名的取值必须是班级表班名的值或空值。关系代数一、传统的集合运算设关系R、s的结构完全相同，贝y：RU S:由属于R或属于S的元组组成。RA S:由既属于R又属于S的元组组成。R- S:由属于R而不属于S的元组组成。思考：(RA S)U( R- S)二？RX S:设R有m个属性，K1个元组；S有n个属性，K2个元组，则RX S含 有(m+n)个属性，(K1 X K2)个元组。二、专门的关系运算1. 选择：从关系R中选择满足条件的元组。记为：F (R)2. 投影：从关系R中选择若干属性组成新的关系，并把新关系的重复元组 去掉。记为：A（ R）3 .条件连接：将两关系按一定条件连接成一个新关系，记为：R F S F （R S）说明：条件连接：两关系可以没有公共属性，若有公共属性，则新关系含 有重复属性。4. 自然连接：将两关系按公共属性连接成一个新的关系，并把新关系的重 复属性去掉。记为：R S说明： 自然连接：两关系至少有一个公共属性。 对于R的每个元组，S都从第一个元组幵始判断，若两元组的公 共属性值相同，则产生一个新元组添加到新关系中，最后把新关系中的重复属 性去掉。5. 除：给定关系 R （x，y）和S （y，z），则R+ S=P （x），其中x，y，z为 属性组。求解过程： 求R中x可以取哪些值，并求各值的象集。 求S在属性组y上的投影K。 检查每个象集是否包含 K注：除不是一个必须的运算，可以由其它运算符代替。 例：设有关系R, S如下图，求R- SABCalbic2a2b3c7a3b4c6alb2c3a4b6c6a2b2c3aib2ciRBCDblc2dib2cldib2c3d2Sa1，(bl, c2)， (b2, c3)， (b2, c1) (b3, c7)， (b2, c3) (b4, c6) (b6, c6) a2， a3， a4。解：在关系R中，A可以取四个值, al的象集为 a2的象集为 a3的象集为 a4的象集为S 在(B, C)上的投影 K 为 (bl, c2), (b2, c3), (b2, cl) 显然只有al的象集包含K,故R- S=a1 结论：如何写关系代数表达式？答： 查询涉及多个关系时，一般使用Xi。 查询涉及“否定”时，一般用差运算。 查询涉及“全部”时，一般用除运算。 查询涉及“至少”时，一般用x关系数据库规范化理论函数依赖一、有关概念:XHKHKM961C1OS962C2DBS962C4Al962C1OS963C1OSXMDZCJ高明D170咼飞D272咼飞D280高明D175高明D1901. 函数依赖：任给R( U)，U为属性集，x、y为U的子集，如果对于x的每个值，y有唯一确定的值与之对应，则称 x决定y，或y函数依赖于X。记为：xiy例: KHi KMXM DZ(XH,KH) iCJKHi (KM,XM)2. 完全函数依赖：若xiy，且对于x的所有真子集x:都有x y，则称x完全决定y，或y完全函数依赖于x。记为：x例 1 : (XH,KH) f CJ但 XH f CJKH/ fCJ故(XH , KH ) f CJ例 2: KHf KM贝卩KH f KM结论：若xf y,且x只包含一个属性，则x f y。3. 部分函数依赖：若xf y,且存在x的一个真子集x,满足x f y，则称x部分决定y，或 y部分函数依赖于x。记为：x p y。例 1 : (KH,KM)f XM但 KM f XM贝 y (KH,KM ) p XM例 2： (XH,KH)f DZ但 KH f DZ贝卩(XH,KH ) p DZ4. 传递函数依赖：若 xf y,y f 乙 但/ x,贝9 x t z例：KMfXM,XMfDZ,但*XM / KM二、平凡函数依赖与非平凡函数依赖设xf y,如果y是x的子集，则该依赖是平凡的。如：Sno,sn amef sno如果y中至少有一个属性不在 x中，则该依赖是非平凡的。如：Sno,sn ame sn ame,sdept如果y中没有一个属性在x中，则该依赖为完全非平凡的三、函数依赖的推理规则设有关系R,x、y、z为R的一个属性集，则有:自反律:若y x，贝y xt y0增广律:若x t y，贝 y xz T yz 0传递律:若x t y，yTz，贝卩 xtzo注意传递律与传递函数依赖的区别。 合并律：若 x t y, Xi乙则xt yz。 分解律：若 x tyz，则XT y , x tz。关系模式的规范化 一、问题提出XHKHKMXMDZCJ961C1OS高明D170962C2DBS咼飞D272962C4Al咼飞D280962C1OS高明D175963C1OS高明D190答：存在问题 数据冗余大； 修改麻烦； 插入异常：应该插入到 DB中的数据插不进去。如：新幵课程没有学生选修时，新幵课程的课程号、课程名插不进去。 删除异常：不应该删除的数据被删掉。掉。如选修某门课的学生毕业了，在删除学生信息的同时，把课程信息也删除结论：一个好的关系模式应满足： 冗余应尽可能少； 应尽可能避免插入、删除异常； 消去关系中不合适的属性依赖关系。二、范式 什么叫范式？指一个关系的非主属性函数依赖于主码的程度。 什么叫关系规范化？指一个关系从低级范式向高级范式的转换过程。 应用：关系规范化理论应用在逻辑结构设计阶段。三、关系模式的规范化1. 第一范式（1NF） 定义：若关系R的所有属性不能再分，则 R 1NF 存在问题 原因：存在非主属性对主码的部分依赖。 解决办法：消除非主属性对主码的部分依赖，将关系R一分为二，将满足完全依赖的属性集组成一个关系；将满足部分 依赖的属性集组成另一个关系；R1表R2表XHKHCJ961C170962C272962C480962C175963C190R1 主码：（XH,KH）KHKMXMDZC1OS高明D1C2DBS咼飞D2C4Al咼飞D2R2主码：KH2. 第二范式（2NF） 定义：若关系R 1NF,且它的每个非主属性都完全依赖于主码，则称 2NF。 存在问题：冗余大：R1必要冗余，R2冗余可以修改。修改麻烦R2表插入异常：如新来的教师没有上课，则该教师的信息就没办法插入 中。删除异常：若某位教师只授一门课，当该门课不幵时，该教师的信息亦 被删除。 原因：存在非主属性对主码的传递依赖。KHR XM XMR DZ,但XM KH KH t DZ传递依赖必须有两个非主属性 解决办法：将R2 一分为二R21表R22表KHKMXMXMDZC1OS咼明C2DBS咼飞C4Al咼飞R21主码：KHR22主码：XM3. 第三范式（3NF） 定义：若关系R 2NF且它的每个非主属性都不传递依赖于主码，则称 R 3NF。 规范化过程非规范关系J使每个属性都不能再分1NFJ消去非主属性对主码的部分依赖2NFJ消去非主属性对主码的传递依赖3NF4. 结论 若R 1NF,且主码只含一个属性，则 R一定为2NF。 若R 2NF,且只有01个非主属性，则 R一定为3NF。 3NF 一般控制了数据冗余，一般避免了操作异常。 范式并非越高越好，适可而止。数据库设计一、数据库设计的步骤 需求分析：了解分析用户的需要、要求。 概念结构设计：根据需求分析的结果画概念模型（即 E-R图） 逻辑结构设计：将E-R图转换为某一种数据模型，并优化。 物理结构设计 数据库实施 数据库运行与恢复概念结构设计一、局部E-R图设计1. 确定局部范围通常把系统涉及的各个部门或各个主要功能作为局部。2. 确定实体与属性 属性是不能再分的数据项； 联系只发生在两实体之间； 原则上，能够作为属性，就不要作为实体。二、合并成总体E-R图1 消除各局部E-R图的冲突问题。2 .按公共实体名合并，生成初步 E-R图。3. 消除冗余的属性和冗余的联系，生成总体E-R图。逻辑结构设计一、联系的属性和主码(1) 联系的属性：必须包含相关联的各实体型的主码。(2) 联系的主码1:1联系：可以是相关联的任一实体型的主码。1: n联系：必须是n方实体型的主码。m n联系：必须是相关联的各实体型的主码之和。二、E-R图向关系模型的转换(1)把每个实体型转换为一个关系模式。2) 1： 1联系：可以消化到相关联的任一实体型对应的关系模式中班长(XH，XM， Nl，BH)班级(BH rS)班级一(3) 1： n联系：可以消化到n方实体名对应的关系模式中。例：一个班级有多名学RS每名学生只能属于一个班级。每一个班级有一 名班长，他是学生中的一员。班级H)班长的学号(4) m n联系：必须转换为一个关系模式，并且不能消化学生(sno , sn课程选修(5)例：mss，sage ，sdept)cno， cname， sno， cnograde)供应在逻辑设计的基础上，为每个关系模式选择合适的存储结构与存储方式。 选择存储结构：即决定每个表的记录顺序。选择存取方式：即决定为哪些属性建立非聚集索引，以便加快查找速度。 般把经常查询的属性名指定为非聚集索引。2. 数据库实施主要工作：定义数据库结构；组织数据入库；编写应用程序；数据库试运行；SQL Server 2005 系统入门一、运行环境最低处理器速度：600MHZ（推荐1GB或者更快）最小内存：512MB（推荐1GB或者更大）可用硬盘空间：1.6GB二、SQL Server 2005 的主要组件1. 服务：用于启动 SQL Server 2005服务器单击“幵始”菜单，选择“控制面板”| “管理工具” | “服务”选项，将“ SQL Server （MSSQLSERVJER选项置为“自动”，即随操作系统的启动而自 动启动。安装时默认为“自动”。2 .配置管理器（Configuration Manager ）:用于启动 SQL Server 2005 服 务器注意：“服务”窗口中显示的是操作系统中全部的服务程序，而“配置管理 器”窗口中显示的仅仅是与 SQL Server 2005有关的服务程序。3. SQL Server Management Studio （简称 SSMS 管理工作室）组合了对象资源管理器、查询编辑器的功能。对象资源管理器用于管理数 据库服务器中的所有数据库对象；4. SSMS查询编辑器：主要用于输入、执行和保存 Transact-SQL命令创建和使用数据库一、数据库文件1一个数据库至少有一个主要数据文件和一个事务日志文件。如果数据库 很大，可以使用一个主要数据文件、多个次要数据文件和多个事务日志文件。主数据文件（.mdf ）次数据文件（.ndf ）用于存放数据库的各事务日志文件（df ）:用来记录对数据库对象的所有更新操作。2. 系统数据库Master数据库、Model数据库二、创建数据库1. 打幵数据库：Use数据库名2删除数据库：Drop database数据库名三、修改数据库1. 分离和附加数据库2. 备份和还原数据库3 数据的导入和导出 数据库的备份和恢复1. 将数据库备份到一个备份文件中：Backup database数据库名to disk二 路径 文件名2. 将备份文件恢复到数据库中：Restore database数据库名from disk= 路径 文件名创建和使用表数据类型1. 字符型char(n):定长字符型，n表示字符数，取值为18000。若缺省n,则默认 为1。varchar( n):变长字符型Text :可以存储任意长的字符串说明：标准字符型：每个英文字母、数字算1个字符，每个汉字算2个字符，每个字符占1个字节。2. 统一码字符型每个英文字母、数字、汉字算 1个字符，每个字符占2个字节。nchar(n) : n表示字符数，取值为 14000。n varchar( n)ntext :可以存储任意长的统一码字符串。例：设某表的结构如下：no char(6), n ame n char(6)则no可以赋予3个汉字，name可以赋予6个汉字3. 整型取值范围所占字节数Bigi nt8Int4Smalli nt-32768+327672Ti nyi nt02551bit0,1即逻辑型4.实型精确数值型Decimal(p,s)Numeric(p,s)精度P:表示全部数字的位数（不计小数点和正负号）S:表示小数位数，若缺省 S,则默认为0P-S:表示整数位数 近似数值型Real 精确到 7 位有效数字Float 精确到 15 位有效数字5贷币型Money:占8个字节，保留4位小数Smallmoney :占4个字节，保留4位小数字符串常量:由单引号括住的字符序列 贷币型常量:可以是一个实型常量 6日期时间型Datetime 、Smalldatetime 若省略日期部分，则默认为: 1900-1-1 若省略时间部分，则默认为: 00:00:00 日期格式: yyyy-mm-dd时间格式: hh:m m :ss 使用对象资源管理器创建和管理表一、创建表 1在一个表中只能定义一个标识字段。2只有整型和精确数值型 （ decimal 、numeric ）字段才能指定为标识字段。 3把某个字段指定为公式字段。二、设置约束1主键约束:每个表中只能有一列或一个组合被指定为主键，主键中的各个列不能为空 值。2外键约束: 创建外键约束，就是定义两个表的永久关联，这两个表分别称为主键表、 外键表。外键表中外键的值只能是主键表中主键的有效值或空值。3唯一性约束:主键约束与唯一性约束的区别是:（ 1） 在一个表中只能定义一个主键约束，但可定义多个唯一性约束；（ 2）指定为主键约束的列不能取空值， 但指定为唯一性约束的列允许取空值。 4唯一性约束:用于限制输入到一列或多列的值的范围，保证数据库的数据完整性。5默认值约束:T-SQL、SQL语言的特点SQL语言集数据定义、数据查询、数据操纵、数据控制的功能于一体。动词数据定义Create、drop数据查询select数据操纵Insert、delete、update数据控制Grant、revoke所有的DBMS支持SQL语言。T-SQL基础一、创建和使用数据库1创建数据库create database 数据库名2 使用数据库Use数据库名3 删除数据库drop database 数据库名二、定义表1创建表create table 表名(属性名 类型，属性名 类型) 指定标识字段：identity(标识种子，标识增量) 指定公式字段：属性名 as表达式例: create table student(no int ide ntity(1,1),n ame char(6),chi smalli nt,mat smalli nt,score as chi+mat)2. 删除表drop table 表名，表名三、select 语句 select */ 表达式表 into 新表from表名，表名 where 条件 group by 属性名 hav ing条件order by 属性名Asc/Desc1. Select 子句 *代表所有属性名 若一个属性名来自多个表，则属性名前须冠以表名，格式为：表名 属性名 设置表达式的别名：表达式As别名 限制查询结果的记录行数：all返回全部记录top n返回前面 n号记录dist inct表示取消重复行说明：top n只能放在关键字select的后面；all 、distinct只能放在关键字select或聚合函数的后面。2. Where 子句 in的格式：属性名in （常量，常量） like 的格式：属性名like 通配字符串通配符有：%表示0个或多个字符-表示1个字符 在Where子句中指定连接：Where 表名1.属性名二表名2.属性名3. Order by 子句order by 属性名 1 Asc/Desc, 属性名 2 Asc/Desc4. 聚合函数 sum（属性名）：纵向求数值型属性之和。 avg（属性名） cou nt（*）返回表的记录行数（含重复行）。count（属性名）返回指定列中取非 NULL值的单元格数目。count（distinct属性名）返回指定列中取非 NULL值、非重复的单元格数目。 max（属性名） min（属性名）5. Group by 子句使用Group by子句时，Select子句只能使用分组项字段和聚合函数 例：以性别为分组项，求每一组的平均年龄。Select ssex, avg（sage） as平均年龄From stude ntGroup by ssex6. Having 子句 Having子句只能跟在Group by子句之后，且只能使用聚合函数和分组项 字段。 where子句放在Group by子句之前，甚至可以没有 Group by子句；且不 能包含聚合函数。例：以系别为分组项，查询学生平均年龄大于19岁的系的系名，平均年龄。Select sdept,avg（sage） as 平均年龄From stude ntGroup by sdeptHaving avg（sdept）197. into 子句功能：将查询结果保存到新的基表中。一、查询的分类一 单表查询连接查询- 嵌套查询1. 连接查询：在 where子句中指定连接where表名1.属性名二表名2.属性名2. 嵌套查询 嵌套查询的特点每级查询的from子句一般只包含一个表名。一个嵌套查询总可以分解为若干个单表查询，总可以改写成连接查询。若查询结果显示的属性名来自一个表，才可以写成嵌套查询。子查询不能使用 order by子句，order by只能用于最顶层的查询。 在where子句中指定子查询where 属性名not in（子查询）：子查询返回一列多行。where 属性名二（子查询）：子查询返回一列一行。where not exists（子查询）：子查询返回多列多行。五、数据操纵1. insert 语句（1）每次插入一条记录insert into表名（属性名表）values（ 表达式表）（2）插入子查询的结果insert into表名（属性名表）子查询例: insert into studentselect * from stude nt12. update 语句update表名set 属性名二值，属性名 二值where 条件缺省where子句，默认为更新全部记录。3. delete 语句 delete from 表名where 条件T-SQL程序设计基础一、常量：字符型：由单引号括住，例：chi na整型I实型I日期型：由单引号括住的具有日期或时间意义的序列，格式为:yyyy-mm-dd ， hh:mm:ss二、变量：局部变量：由用户定义和赋值，以幵头。全局变量：由系统定义和赋值，以干头。1. 声明局部变量Declare 变量名类型例: declare a int,b char(5)注：不要把局部变量声明为text、ntext、image2. 给局部变量赋值 使用set语句：Set 变量名二表达式 使用select 语句Select 变量名二表达式，变量名二表达式from 表名若表达式中含有属性名，则必须使用from子句。例：declare a char(5),b char(6)Select a=95001 ,b=王名或: select a=sno,b=snameFrom stude nt3. 输出表达式的值： 使用print语句Pri nt 表达式 使用select语句Select 表达式，表达式from 表名例：A) select a,bB）select sno,snameFrom student C）select sno as学号 ,sname as 姓名From student 等价于： select 学号 =sno, 姓名 =snameFrom student 4局部变量的作用域： 只能在声明它的批处理中使用。 例： Use studGo Declare a int Set a=5 Print a Go Declare a char（5） Set a= 张三 Print a三、运算符：算术运算符（*、/、% T +、-）关系运算符（ 、 =、 、 =、 =、 ）逻辑运算符（not T and 宀 or ）批处理 1什么叫批处理？一个脚本由一个或多个批处理组成，批处理以GO乍为结束标志。2批处理是脚本的编译单位，当一个批处理中的某个语句出现编译错误，则批 处理中的任何语句均无法执行。3当一个批处理中的某个语句出现运行错误，则批处理中当前语句和它之后的 语句将无法执行。流程控制语句一、beg in end 语句 Begin 语句 1语句 nEnd二、if-else 语句1. 格式： if 逻辑表达式语句 1else 语句 2 2当逻辑表达式包含子查询时，子查询必须用括号括住。子查询返回一列多行 子查询返回一列一行 子查询返回多列多行属性名 in （ 子查询 ） 属性名 = （ 子查询 ） Exists（ 子查询 ）三、case 表达式CaseWhen 逻辑表达式 1 then 结果 1When 逻辑表达式 2 then 结果 2 else 结果 nEnd说明： case 表达式不是语句，不能单独执行例：use studSelect 姓名二sname,系另U =CaseWhen sdept= CS then 计算机科学系 When sdept= IS then 信息系统系 When sdept= MA then 数学系 End四、while 语句1格式：While 逻辑表达式Begin语句组End 2专用于循环体的语句：Break强制退出 while 语句，执行其后续语句Continue 返回 while 语句的入口。Break 、 Continue 必须放在循环体内，并常与 if-else 语句结合使用。二、 其它语句1 Return 语句格式： Return （ 整数值 ） 功能：用于存储过程或批处理中，功能是退出所在的存储过程或批处理。 说明：当用于存储过程时，若没有为 Return 指定整数值，则默认为 0。2存储过程 创建存储过程Create procedure 存储过程名 形参名 类型 As SQL 语句序列 执行存储过程Exec 存储过程名 常量 | 变量名 ： 删除存储过程Drop procedure 存储过程名 说明：存储过程不返回值，或者只能返回整数值；而函数可返回任意类型的 值。3 Waitfor 语句函数1创建函数create function函数名 （形参名 类型 ） returns 类型asbegin函数体End 注： 函数体最后一条语句必须是return语句。 两类函数：标量函数、内嵌表值函数2执行函数Exec 变量名 1=函数名 常量| 变量名Print 函数名 （ 常量| 变量名 ）3删除函数Drop function 函数名附加练习题 9一、定义表：1 SQL server 中建立一个数据库 stu 。2使用企业管理器中创建如下三个表。Snosn amessexsagesdept95001李勇男20CS95002刘晨女19IS95003王名女18MA95004张立男18CSenocn amecredit1数据库原理42操作系统33Java程序设计34汇编语言2student 表Course 表Sc表SnoCnograde9500118595001288950013829500219095002380、查询表:1查询全体学生的姓名及出生年份。2. 查询年龄小于19岁的男学生的学号，姓名。3. 查询选修了课程的学生人数。提示：select count(distinct sno) from sc4. 查询所有选修过课的学生的学号、姓名。5. 查询数学系(MA、计算机科学系(CS的学生的姓名和性别6. 查询年龄最大的2名学生情况7. 查询姓“刘”的学生情况。8. 以性别为分类项，查询每一类的平均年龄。9. 查询选修了课程名为“操作系统”的学生的学号、姓名。10. 查询年龄低于平均年龄的所有学生的姓名、系别三、操纵表：1.在企业管理器中创建一个新表student1 :Snosn amessexsagesdept95005王虹女19CS95006万亮男20MA2.将student1表插入到student表的后面。提示：insert into student select * from student1 表名2 on 条件表名2 on 条件CS )条件3. 将（95020，陈冬，男，18, IS）插入到student表中。4. 删除student1表中的所有记录，使之成为空表。5. 把student表中所有男生记录复制到空表student1中。6. 把student表中所有女生记录复制到新表student2中。7. 将所有学生的年龄增加 1岁。8. 将计算机科学系全体学生的成绩置0。 提示：update sc set grade=0where sno in （select sno from stude nt where sdept=9. 删除学号为95020的学生记录。10. 删除计算机科学系所有学生的选课记录。T-SQL高级应用一、查询的分类 厂单表查询y 连接查询嵌套查询一、连接查询：在 where子句中指定连接1. 内连接：where表名1.属性名二表名2.属性名2. 自身连接：给一个表取两个别名，where子句的格式为：where别名1.属性名二别名2.属性名3. 左外连接：where表名1.属性名*=表名2.属性名意义：查询结果包含了表 1的全部记录和表2满足条件的记录4. 右外连接：where表名1.属性名=*表名2.属性名意义：查询结果包含了表 2的全部记录和表1满足条件的记录三、连接查询：在from子句中指定连接1. 内连接：from 表名1 inn er join表名2 on 条件例：select *from stude nt,scwhere stude nt.s no 二sc.s no等价于select *from stude nt inner join sc on stude nt.s no 二sc.s no2. 左外连接：from 表名 1 left outer join3. 右外连接:from 表名 1 right outer join4. 完全外连接：from 表名1 full outer join表名2 on5. 交叉连接 四、嵌套查询事务处理1什么叫事务？事务是用户定义的一组操作序列。 事务是并发控制的基本单位。 一个事务包含的诸操作要么都执行，要么都不执行。2. 事务的属性 原子性：指事务中包含的诸操作要么都执行，要么都不执行。V致性：事务必须使数据库从一个一致性状态变到另一个一致性状态。隔离性：一个事务的执行不能被其他事务干扰。I持久性3. 显式定义事务beg in tra nsacti on 事务名commit/rollback transaction事务名当未显式指定事务，则一个 SQL语句就是一个事务。4. 在事务内设置保存点beg in tra nsacti onsave tran sacti on保存点名rollback tran sacti on保存点名功能：将保存点到rollback子句之间的SQL操作回滚掉。数据的锁定一、并发操作与数据不一致性1. 数据不一致性包括三类丢失修改：指事务1与事务2从数据库中读入同一数据并修改，事务2的提交结果破坏事务1提交的结果，导致事务1的修改被丢失。不可重复读：指事务1读取数据后，事务2执行更新操作，使事务1无法 再现前一次读取结果。读脏数据：指事务1修改某一数据后，事务 2读取该数据，事务1由于某 种原因被撤销，这时数据又恢复到原值，事务2读到的数据与数据库中的数据不一致，称为“脏”数据。2. 产生数据不一致性的原因并发操作破坏了事务的隔离性。二、并发控制的目标、方法1. 目标：确保DB中的数据一致性。2. 并发事务正确性的原则几个事务的并发执行是正确的，当且仅当其结果与任何一个串行执行的结果 相同。3. 并发控制的方法DBMW般采用“封锁”技术，保证并发操作的可串行化。二、圭寸锁（Locking）1. 什么叫封锁？SQL Server自动强制圭寸锁，并且会将圭寸锁粒度控制在合适的级别，用户不必考 虑封锁问题。2. 封锁类型排它锁（X锁）：事务T对数据A加X锁，其它事务不能再对 A加锁，即其 J它事务不能读取和修改 Ao共享锁（S锁）：事务T对数据A加S锁，其它事务只能再对 A加S锁，即 其它事务只能读A，不能修改Ao3. 封锁粒度封锁对象可以是属性列、元组、关系、整个数据库。封锁对象的大小称为 封锁粒度。封锁粒度越小，并发度越高，但并发控制的幵销越大。4. 封锁协议 事务T在修改数据A之前，必须对其加X锁，直到事务结束才释放。 事务T在读取数据A之前，必须对其加S锁，直到事务结束才释放。 遵循封锁协议，可以解决三种数据不一致性问题：丢失修改 不可重复读读“脏”数据四、死锁和活锁封锁技术可以解决并发操作的不一致性问题，但也带来新的问题，即死锁 和活锁。1. 死锁： 定义：两个事务已经各自锁定一个数据，但是又要访问被对方锁定的数 据，造成了循环等待，称为死锁。 避免死锁的方法：顺序封锁法：若规定封锁顺序为 A, B,则T1, T2只能先封锁A，再封锁B2. 活锁： 定义：若多个事务请求封锁同一个数据时，其中的某个事务总处于等待 状态，则称为活锁。 避免活锁的方法：先来先服务使用游标一、游标的概念1. 每个表均有一个游标，它可以指向表的任意一条记录。2. 移动游标的方法： 在触发器或存储过程中，使用SQL语句定义和使用游标。 在前台应用程序中，使用主语言实现对游标的移动。二、Transact-SQL游标的使用：1. 声明游标Declare 游标名 cursorforward_o nly / scroll global /localread_o nly for select 语句j forward_only（只进游标）：只能进行next操作，缺省为forward_only 。-scroll（滚动游标）global /local:缺省为：global2 打幵游标ope n 游标名打幵游标时，游标指向查询结果集的第一条记录之前。3 .提取游标fetch next / prior / first /last from游标名into局部变量名表 缺省into子句：移动游标，并显示当前记录的内容。 含into子句：移动游标，并将当前记录各属性值依次赋给局部变量。 缺省游标移动方式，则为next 04. 关闭游标close 游标名5. 释放游标declaredeclare 游标名例：-打印student表中全体男生的平均年龄。（不得使用聚合函数。）set nocount ondeclare x int, s in t, n decimal（4,1）select n=0, s=0declare pm cursorscrollfor select sage from stude nt where ssex=男ope n pmfetch next from pm in to xwhile FETCH_STATUS=0beg inselect n=n+1, s=s+xfetch next from pm in to xendprint 全体男生的平均年龄为：+str（s/ n, 4,1）close pmdeallocate pm三、有关全局变量1. FETCH_STATU返回上一个Fetch语句的执行状态，若 Fetch语句成功执 行，则返回0,否则返回-1 O2. rowcount 返回受上一个语句（包括 select、insert、delete、update） 所影响的行数。3. set nocount on :关闭影响行数信息。索引一、索引的概念： 如果把数据表比作一本书，那么表的索引就是这本书的目录。可见，索 引使用户能快速访问数据表的特定信息。 索引包括两项：索引字段值、原记录号 索引必须依附于某个基本表，不能单独存在。二、索引的类型：（-聚集索引：影响数据表的记录顺序 非聚集索引：不会影响数据表的记录顺序注：一个表只能建立一个聚集索引，但可以建立若干个非聚集索引。三、创建索引1. 自动创建索引：。如果在数据表的某个属性设置主键约束或唯一约束，则系统将在这些属性上自动创建唯一索引。自动创建的索引随约束的存在而存在，随约束的消失而消失。2. 使用SQL语句创建索引Create uniq ue clustered/non clustered in dex索弓丨名On 表名（属性名asc/desc, 属性名asc/desc）注：若未指定clustered，则创建非聚集索引； 若未指定排序方式，则为 ASC text ,n text类型的字段不能指定为索引字段。四、删除索引：Drop index 索引名，索引名思考题：创建主键时，如果使主键字段值不影响数据表的记录顺序？视图、视图的特点: 视图只有结构，没有记录，是虚表； 一个视图总对应着一个 select语句； 对视图的查询、更新，实际上是对基本表的查询、更新。二、定义视图：1创建视图：Create view 视图名（属性名，属性名）As 子查询with check optio n说明：视图的属性个数必须与子查询中select子句的表达式个数相同。2. 删除视图：Drop view 视图名，视图名三、查询视图：select */ 表达式表from 视图名，,视图名where 条件group by 属性名order by 属性名Asc/Desc四、操纵视图：1向视图插入一条记录insert into视图名（属性名表）values（表达式表）2. 修改视图中的数据update视图名set属性名二值，属性名二值where 条件缺省where子句，默认为更新全部记录。3. 删除视图中的记录delete from 视图名where 条件数据库完整性一、在创建表时指定约束1主键约束constraint约束名Primary key Clustered/Nonclustered（属性名，属性名）说明： 每个约束都有一个约束名，约束名通常由系统自动给出。 列级约束：只牵涉到一个属性的约束，它放在相关属性的后面，且省略 属性名表。表级约束：牵涉到多个属性的约束。clustered ）或非聚集 创建主键约束、唯一性约束时可以指定聚集(non clustered)。 主键约束默认为聚集的，唯一性约束默认为非聚集的 一个表最多只能创建一个约束是聚集的，聚集约束会影响数据表的记录 号顺序。2. 外键约束constraint约束名Foreign key( 属性名，属性名)References 主键表名(属性名，属性名)例：创建