《数据库原理》教案高技版

第 1 页，共 26 页第 1 章 数据库系统基础知识1.1 数据库系统概述一、基本概念1数据2数据库（DB）3数据库管理系统（DBMS）Access小型 VFP SQL serverOracle大型 DBMS MySQLDB24数据库系统（DBS）数据库（DB）数据库管理系统（DBMS）开发工具应用系统二、数据库系统的体系结构 单机结构：DBMS、数据库、开发工具、应用系统安装在一台计算机上。 C/S 结构：局域网结构客户机：装开发工具、应用系统服务器：装 DBMS、数据库开发工具：VB、VC+、Delphi、Powerbuilder、Java B/S 结构： Internet 结构用户 用户 用户应用系统开发工具数据库管理系统操作系统数据库数据库管理员第 2 页，共 26 页服务器：装 DBMS、数据库、开发工具、应用系统客户机：装 IE 即可开发工具：ASP、JSP、PHP、ASP.net三、数据管理的三个发展阶段人工管理 文件系统 数据库系统软硬件环境 无磁盘、无 OS 出现磁盘、文件系统出现 DBMS数据能否保存 不能保存 可以保存 可以保存数据由谁管理 由应用程序自己管理数据 由文件系统管理数据 由 DBMS 管理数据的共享程度不共享，一组数据只能对应一个应用程序。共享性差，一个数据文件只能对应一个应用程序。共享性高数据的独立性 不独立，它是应用程序的一部分。 独立性差 数据库与应用系统完全分开四、数据库管理系统的功能和组成1DBMS 的功能：负责对数据库进行统一的管理与控制。 数据定义：对数据库中各对象的结构进行定义。 数据操纵：包括对数据库进行查询、插入、删除、修改等操作。 数据控制：包括安全性控制、完整性控制、并发控制、数据库恢复。2DBMS 的组成：DDL 语言DML 语言DCL 语言实用程序注意： SQL 集 DDL，DML，DCL 功能于一体； SQL 既是宿主型语言，又是嵌入式语言； 所有应用程序通过 SQL 语句才能访问数据库五、开发、管理、使用数据库系统涉及的人员系统分析员：负责应用系统的需求分析和数据库设计程序员：编写代码数据库管理员（DBA）：负责数据库的维护最终用户开发单位使用单位第 3 页，共 26 页1.2 数据模型1.2.1 概念模型一、三个世界1现实世界：即具体事物2信息世界：对现实世界中的具体事物抽象为概念模型（即 E-R 图） ，E-R 图与 DBMS 无关。3机器世界：将 E-R 图转换为某一种数据模型，数据模型与 DBMS 相关。二、实体及属性1实体：客观存在并可相互区别的人、事、物。2属性：3关键字（码、key）：能唯一标识每个实体又不含多余属性的属性组合。一个表的码可以有多个，但主码只能有一个。例：借书表（学号，姓名，书号，书名，作者，定价，借期，还期）规定：学生一次可以借多本书，同一种书只能借一本，但可以多次续借。4实体型：即二维表的结构例 student(sno，sname，ssex，sage，sdept)5实体集：即整个二维表三、两实体集间实体之间的联系1：1 联系：如果对于实体集 A 中的每一个实体，实体集 B 中至多有一个实体与之联系，反之亦然，则称实体集 A 与实体集 B 具有一对一联系。记为 1：1。1：n 联系：如果对于实体集 A 中的每一个实体，实体集 B 中有 n 个实体（n0）与之联系，反之，对于实体集 B 中的每一个实体，实体集 A 中至多只有一个实体与之联系，则称实体集 A与实体 B 有一对多联系。记为 1：n。m：n 联系 ：如果对于实体集 A 中的每一个实体，实体集 B 中有 n 个实体（n0）与之联系，反之，对于实体集 B 中的每一个实体，实体集 A 中也有 m 个实体（m 0）与之联系，则称实体集 A 与实体 B 具有多对多联系。记为 m：n。四、概念模型：常用 E-R 图表示。实体型：属性：联系：说明： E-R 图作为用户与开发人员的中间语言。 E-R 图可以等价转换为层次、网状、关系模型。例：某商业集团管理系统涉及两个实体型，实体型“商店”有商店编号、商店名、地址和电话属性；实体型“顾客”有顾客编号、姓名、性别、出生年月和家庭地址属性。顾客与商店间存在着消费联系。假定一位顾客可去多个商店购物，多位顾客可以前往同一商店购物，必须记下顾第 4 页，共 26 页客每次购物的消费金额。（1）试为此系统设计 E-R 图。（2）将此 E-R 图转换成关系模式，并注明每个关系模式的主码和外码。1.2.2 数据模型一、层次模型：用树型结构表示实体之间的联系。 每个节点代表一个实体型。 只能直接处理一对多（含一对一）的实体关系。 查找层次数据库中的记录，速度较慢。二、网状模型：用图结构表示实体之间的联系。 每个节点代表一个实体型。 可以处理多对多的实体关系。 查找网状数据库中的记录，速度最快。三、关系模型：用二维表表示实体之间的联系。 关系模型是建立在严格的数学概念的基础上的； 关系模型的存取路径对用户透明； 查找关系数据库中的记录，速度最慢。小结：数据有三种模型，DBMS 就有三种类型，DB 亦有三种类型。思考与练习1学校有若干个系，每个系有若干班级和教研室，每个教研室有若干教员，其中有的教授和副教授每人各带若干研究生。每个班有若干学生，每个学生选修若干课程，每门课程有若干学生选修。用 E-R 图画出概念模型。第 2 章 关系数据库系统2.1 关系模型的数据结构 关系：一个关系就是一个二维表；性别电话地址商 店顾 客购买商店编号 商店名 地址 电话地址顾客编号 姓名电话地址出生年月 家庭地址MN消费金额第 5 页，共 26 页 关系模式：在实体型的基础上，注明主码。 元组：二维表的一行，即实体； 码、候选码：一个关系模式可以有多个候选码。 主码：任选候选码中的一个。 主属性：主码中包含的各个属性。 非主属性：不包含在主码中的各个属性。 外码：设 F 是关系 R 的一个属性，不是 R 的主码，但却是另一个关系 S 的主码，则称 F是关系 R 的外码。例：student ( sno, sname, ssex, sage, sdept)Sc ( sno, cno, grade)Sc 的主码为：（sno,cno） ；外码为： sno2.2 关系的完整性1实体完整性：指关系的所有主属性都不能取空值。注意：实体完整性不仅仅是主码整体不能取空值。2参照完整性：指一个关系外码的取值必须是相关关系中主码的有效值或空值。例：班级( 班号,人数)学生(学号,姓名,性别,密码 ,班号)在学生表中，班号的取值必须是班级表“班号”的值或空值。其中班级称为“主键表” ，学生称为“外键表” 。3为了保证参照完整性，应该对数据库的修改加以限制，包括：插入约束删除约束（限制删除、级联删除）更新约束（限制更新、级联更新）2.3 数据库设计（见 P137）2.3.1 数据库设计的步骤一、数据库设计的步骤 需求分析：了解分析用户的需要、要求。 概念结构设计：根据需求分析的结果画总体 E-R 图。 逻辑结构设计：将总体 E-R 图转换为关系模式，并优化。 物理结构设计：即决定为表中的哪些列创建聚集索引还是非聚集索引。 数据库实施：定义数据库结构，组织数据入库，编写应用程序。 数据库运行与恢复二、超类与子类教师教授 副教授 讲师 助师第 6 页，共 26 页超类：包含子类的公共属性；子类：包含超类的主码以及子类本身特有的属性。职工（职工号，姓名，性别，密码，出生年月，身份证号）飞行员（职工号，飞行小时，健康检查，飞机型号）管理员（职工号，职务，职称）工人（职工号，技术等级）2.3.2 概念结构设计一、局部 E-R 图设计 1确定局部范围通常把系统涉及的各个部门或各个主要功能作为局部。2联系的属性：必须包含相关联的各实体型的主码。3联系的主码1：1 联系：可以是相关联的任一实体型的主码。1：n 联系：必须是 n 方实体型的主码。m：n 联系：必须是相关联的各实体型的主码之和。二、合并成总体 E-R 图1消除各局部 E-R 图的冲突问题。2按公共实体名合并，生成初步 E-R 图。3消除冗余的属性和冗余的联系，生成总体 E-R 图。2.3.3 逻辑结构设计1把每个实体型转换为一个关系模式。21：1 联系：可以消化到相关联的任一实体型对应的关系模式中。职工飞行员 管理员 工人班长XH XM NL管理班级BH RSXHBH11第 7 页，共 26 页班长( XH ， XM， NL，BH )班级（BH，RS）31：n 联系：可以消化到 n 方实体型对应的关系模式中。学生（XH，XM，NL，BH）班级（BH，RS） 4m：n 联系：必须转换为一个关系模式，并且不能消化。例：学生(sno，sname， ssex， sage， sdept)课程（cno， cname，credit）选修（sno， cno， grade）5多元联系：不能消化例：学生XH XM NL组成班级BH RSXHBH1n学生snosname ssex sagesdept选修理工课程cno cname creditsnocnogrademn第 8 页，共 26 页2.3.4 物理结构设计任务：在逻辑设计的基础上，为每个关系模式选择合适的存储结构与存储方式。 选择存储结构：即决定每个表的记录顺序。 选择存取方式：即决定为哪些属性建立非聚集索引，以便加快查找速度。思考与练习1现有一个关系模式：借阅（书号，书名，库存数，读者号，借期，还期） ，假如同一本书允许一个读者多次借阅，但不能同时对一种书借多本。则该关系模式的主码是（ ） ，主属性是（ ） 。2某旅行社管理系统涉及的部分信息如下：（1）景点：景点编号、景点名称、地点、景点描述（2）线路：线路编号、线路名称、线路描述（3）导游：工号、姓名、等级（4）团队：团队编号、人数、开始日期、截止日期其中：每条线路由多个景点组成，不同线路的景点存在交叉；每条线路有多名导游，但一名导游只负责一条线路；每条线路可同时存在多个旅游团队，但每个团队只旅游一条线路。（1）建立一个反映上述局部应用的 E-R 图，要求标注联系类型（可省略实体属性） 。（2）将 E-R 图转换为关系模式，要求标注每个关系模式的主键和外键。3假设要为某个工厂的物资管理设计一个数据库。物资管理涉及的实体有：仓库（仓库号，仓库面积，电话号码）零件（零件号，名称，规格，单价，描述）供应商（供应商号，姓名，地址，电话号，账号）项目（项目号，预算，开工日期）职工（职工号，姓名，年龄，职称）这些实体之间的联系如下：一个仓库可以存放多种零件，一种零件可以存放在多个仓库中。一个仓库有多个职工当仓库保管员，一个职工只能在一个仓库工作。职工之间具有领导被领导的关系，即仓库主任领导若干保管员。一个供应商可以供应多种零件，一种零件可以由多个供应商供应。一个项目用到多种零件，而一种零件适用于多个项目。供应商m供应产品 零件nmk第 9 页，共 26 页设想一下如何设计 E-R 图，将其转换成关系模式，并画出数据结构图。第 3 章 SQL Server 数据库应用基础3.1 SQL server2000 常用工具服务管理器企业管理器查询分析器3.2 创建和使用数据库一、数据库文件（P33）1一个数据库至少有一个主要数据文件和一个事务日志文件。如果数据库很大，可以使用一个主要数据文件，多个次要数据文件和多个事务日志文件。2主要数据文件(.mdf )次要数据文件(.ndf ) 事务日志文件(.ldf ) ：用来记录对数据库对象的所有更新操作。3文件组：当多个数据文件组成一个文件组时，就可以对它们整体进行管理。二、创建和使用数据库1创建数据库Create database 数据库名2打开数据库Use 数据库名3删除数据库 Drop database 数据库名三、管理数据库1分离和附加数据库2备份和还原数据库3数据的导入和导出 3.3 创建和使用数据表一、数据类型（P61）1整型取值范围 所占字节数Bigint 8Int 4Smallint -32768 +32767 2Tinyint 0255 1bit 0,1 即逻辑型2实型 精确数值型Decimal(p,s)用来存放数据库的各类对象第 10 页，共 26 页Numeric(p,s)精度 P：表示全部数字的位数（不计小数点和正负号）S：表示小数位数，若缺省 S，则默认为 0P-S：表示整数位数 近似数值型Real 精确到 7 位有效数字 Float 精确到 15 位有效数字3字符型char(n) 定长字符型， n 表示字符数，取值为 18000。若缺省 n，则默认为 1。varchar(n) 变长字符型text 可以存储任意长的字符串标准字符型：每个英文字母、数字算 1 个字符，每个汉字算 2 个字符，每个字符占 1 个字节。4统一码字符型统一码字符型：每个英文字母、数字、汉字算 1 个字符，每个字符占 2 个字节。nchar(n) n 表示字符数，取值为 14000。nvarchar(n)ntext 可以存储任意长的统一码字符串。例：设某表的结构如下：no char(6), name nchar(6)则 no 可以赋予 3 个汉字，name 可以赋予 6 个汉字5二进制型：存放用十六进制数表示的图像、声音数据。binary(n) 定长二进制型，n 表示字节数，取值为 18000。varbinary(n) 变长二进制型image注意：十六进制数以 0x 开头。6日期时间型Datetime、Smalldatetime若省略日期部分，则默认为：1900-1-1若省略时间部分，则默认为：00:00:00日期格式：yyyy-mm-dd时间格式：hh:mm:ss7贷币型Money：占 8 个字节，保留 4 位小数Smallmoney：占 4 个字节，保留 4 位小数字符串常量：由单引号括住的字符序列贷币型常量：可以是一个实型常量二、使用企业管理器创建表第 11 页，共 26 页1在一个表中只能定义一个标识字段。2只有整型和精确数值型（decimal、numeric ）字段才能指定为标识字段。3把某个字段指定为公式字段。三、设置约束1主键约束：每个表中只能有一列或一个组合被指定为主键，主键中的各个列不能为空值。2外键约束：创建外键约束，就是定义两个表的永久关联，这两个表分别称为主键表、外键表。外键表中外键的值只能是主键表中主键的有效值或空值。3唯一性约束：主键约束与唯一性约束的区别是： 在一个表中只能定义一个主键约束，但可定义多个唯一性约束； 指定为主键约束的列不能取空值，但指定为唯一性约束的列允许取空值。4检查约束：用于限制输入到一列或多列的值的范围，保证数据库的数据完整性。5默认值约束：思考与练习1使用企业管理器创建一个名为 data 的数据库，该数据库包含 1 个主要数据文件 data1. mdf，2 个次要数据文件 data2.ndf、data3.ndf，1 个事务日志文件 data4.ldf，三个数据文件分别放在 C、D 、E 盘上，并组成 primary 文件组。 事务日志文件放在 E 盘上。2什么是主键约束？什么是唯一性约束？两者有什么区别？3什么是外键，外键有什么作用？第 4 章 SQL 语言4.1 SQL 语言的特点1SQL 语言集数据定义、数据查询、数据操纵、数据控制的功能于一体。动词数据定义 CREATE、DROP 、 ALTER数据查询 SELECT数据操纵 INSERT、UPDATE、DELETE数据控制 GRANT、REVOKE 、DENY2所有的 DBMS 都支持 SQL 语言。4.2 数据定义1创建表create table 表名（属性名 类型， ，属性名 类型） 指定标识字段：identity( 标识种子，标识增量 )第 12 页，共 26 页 指定公式字段：属性名 as 表达式例：create table student(no int identity(1,1),name char(6),chi smallint,mat smallint,score as chi+mat)2删除表drop table 表名, 表名4.3 数据查询一、select 语句select */表达式表into 新表名from 表名,表名where 条件group by 属性名having 条件order by 属性名Asc/Desc1Select 子句 *代表所有属性名 若一个属性名来自多个表，则属性名前须冠以表名，格式为：表名. 属性名 设置表达式的别名：表达式 As 别名 限制查询结果的记录行数：all 返回全部记录top n 返回前面 n 号记录distinct 表示取消重复行说明：top n 只能放在关键字 select 的后面；all、distinct 只能放在关键字 select 或聚合函数的后面。2Where 子句 in 的格式：属性名 not in (常量，常量) like 的格式：属性名 not like 通配字符串通配符有： % 表示 0 个或多个字符- 表示 1 个字符 在 Where 子句中指定连接：Where 表名 1. 属性名= 表名 2. 属性名3Order by 子句第 13 页，共 26 页order by 属性名 1 Asc/Desc, 属性名 2 Asc/Desc4聚合函数 sum(属性名)：纵向求数值型属性之和。 avg(属性名 ) count(*) 返回表的记录行数（含重复行） 。count(属性名 ) 返回指定列中取非 NULL 值的单元格数目。count(distinct 属性名) 返回指定列中取非 NULL 值、非重复的单元格数目。 max( 属性名 ) min(属性名)5Group by 子句使用 Group by 子句时，Select 子句只能使用分组项字段和聚合函数例：以性别为分组项，求每一组的平均年龄。Select ssex, avg(sage) as 平均年龄From studentGroup by ssex6Having 子句 Having 子句只能跟在 Group by 子句之后，且只能使用聚合函数和分组项字段。 where 子句放在 Group by 子句之前，甚至可以没有 Group by 子句；且不能包含聚合函数。例：以系别为分组项，查询学生平均年龄大于 19 岁的系的系名，平均年龄。Select sdept,avg(sage) as 平均年龄From studentGroup by sdeptHaving avg(sage)197into 子句功能：将查询结果保存到新的基表中。二、连接查询1查询的分类单表查询连接查询嵌套查询2连接查询：在 where 子句中指定连接内连接：where 表名 1.属性名= 表名 2.属性名自身连接：给一个表取两个别名，where 子句的格式为：where 别名 1.属性名=别名 2.属性名左外连接：where 表名 1.属性名*= 表名 2.属性名意义：查询结果包含了表 1 的全部记录和表 2 满足条件的记录。第 14 页，共 26 页右外连接：where 表名 1.属性名=* 表名 2.属性名意义：查询结果包含了表 2 的全部记录和表 1 满足条件的记录。三、嵌套查询 嵌套查询的特点每级查询的 from 子句一般只包含一个表名。一个嵌套查询总可以分解为若干个单表查询，总可以改写成连接查询。若查询结果显示的属性名来自一个表，才可以写成嵌套查询。子查询不能使用 order by 子句， order by 只能用于最顶层的查询。 在 where 子句中指定子查询where 属性名 not in（子查询）：子查询返回一列多行。where 属性名=（子查询）：子查询返回一列一行。where not exists（子查询）：子查询返回多列多行。4.4 数据操纵1insert 语句（1）插入一行记录insert into 表名(属性名表) values(表达式表)（2）插入多行记录insert into 表名(属性名表)子查询例：insert into studentselect * from student12update 语句update 表名 set 属性名=值，属性名=值 where 条件缺省 where 子句，默认为更新全部记录。3delete 语句delete from 表名 where 条件4.5 安全性控制一、相关知识链接1安全性控制：使非法用户不能访问数据库中的数据，合法用户只能访问数据库中授权的数据。2一切权限来自 sa，sa 可以创建普通登录名，并可以把普通登录名指定为一个或多个数据库的用户。3在服务器上创建 SQL Server 登录名 U1、U2、U3 ，并把它们指定为数据库 stu 的用户（不要选 db_owner） 。二、权限的种类第 15 页，共 26 页1系统权限：包括 create database、create table、create view、create procedure2对象权限：指对表、视图、存储过程的操作权限。各种对象的操作权限见下表。对象 操作权限属性列 select、update表和视图 select、insert、 update、delete存储过程 execute三、使用企业管理器设置数据库用户的权限选择要设置权限的数据库，再展开“用户”节点。四、系统权限的授予和回收grant all|系统权限 ,.n to 用户名表 with grant optionrevoke all|系统权限 ,.n from 用户名表 注意：all 代表所有的系统权限。 create database 只能在数据库 master 中执行，且只能是 master 的用户。 with grant option 表示被授权的用户可以将权限授予其它用户。五、对象权限的授予和回收grant all|对象权限 ,.n on 表名|视图名| 存储过程名 to 用户名表 with grant optionrevoke all|对象权限 ,.n on 表名|视图名| 存储过程名 from 用户名表思考与练习一、定义表1SQL server 中建立一个数据库 stu。2使用企业管理器中创建如下三个表。Student 表Course 表cno cname credit1 数据库原理 42 操作系统 33 Java 程序设计 34 汇编语言 2Sc 表Sno Cno gradeSno sname ssex sage sdept95001 李勇 男 20 CS95002 刘晨 女 19 IS95003 王名 女 18 MA95004 张立 男 18 CS第 16 页，共 26 页95001 1 8595001 2 8895001 3 8295002 1 9095002 3 80二、查询表1查询全体学生的姓名及出生年份。2查询年龄小于 19 岁的男学生的学号，姓名。3查询选修了课程的学生人数。 提示：select count(distinct sno) from sc4查询所有选修过课的学生的学号、姓名。5查询数学系（MA） 、计算机科学系（CS ）的学生的姓名和性别。6查询年龄最大的 2 名学生的学号和姓名7查询姓“刘”的学生的学号、姓名、系别。8以性别为分类项，查询每一类的平均年龄。9查询选修了课程名为“操作系统”的学生的学号、姓名。10查询年龄低于平均年龄的所有学生的姓名、系别三、操纵表1在企业管理器中创建一个新表 student1：Sno sname ssex sage sdept95005 王虹 女 19 CS95006 万亮 男 20 MA2将 student1 表插入到 student 表的后面。提示：insert into student select * from student1 3将 (95020，陈冬，男，18 ，IS) 插入到 student 表中。4删除 student1 表中的所有记录，使之成为空表。5把 student 表中所有男生记录复制到空表 student1 中。6把 student 表中所有女生记录复制到新表 student2 中。7将所有学生的年龄增加 1 岁。8将计算机科学系全体学生的成绩置 0。 提示：update sc set grade=0 where sno in (select sno from student where sdept=CS)9删除学号为 95020 的学生记录。10删除计算机科学系所有学生的选课记录。四、安全性控制第 17 页，共 26 页首先在服务器创建登录账户 U1、U2 、U3、U4，并把它们指定为数据库 stu 的用户1把查询 course 表权限授给用户 U1。2把查询、插入 course 表的权限授给用户 U2 和 U3。3把查询 course 表和修改课程号的权限授给用户 U4。4把用户 U1 查询 course 表的权限收回。5把用户 U4 修改 course 表课程号的权限收回。6给用户 U1 授予创建数据库的权限。7把在数据库 stu 创建表、创建视图的权限授给用户 U2，U3。第 5 章 数据库对象管理51 视图一、视图的特点：视图只有结构，没有记录，是虚表；一个视图总对应着一个 select 语句；对视图的查询、更新，实际上是对基本表的查询、更新。二、定义视图：1创建视图：Create view 视图名 (属性名，属性名)As 子查询with check option说明：视图的属性个数必须与子查询中 select 子句的表达式个数相同。2删除视图：Drop view 视图名，视图名三、查询视图：select */表达式表from 视图名,视图名where 条件group by 属性名order by 属性名Asc/Desc四、操纵视图：1向视图插入一条记录insert into 视图名(属性名表) values(表达式表)2修改视图中的数据update 视图名 set 属性名=值，属性名=值 where 条件第 18 页，共 26 页缺省 where 子句，默认为更新全部记录。3删除视图中的记录 delete from 视图名 where 条件52 索引一、索引的概念： 如果把数据表比作一本书，那么表的索引就是这本书的目录。可见，索引使用户能快速访问数据表的特定信息。 索引包括两项：索引字段值、原记录号 索引必须依附于某个基本表，不能单独存在。二、索引的类型：聚集索引：影响数据表的记录顺序非聚集索引：不会影响数据表的记录顺序注：一个表只能建立一个聚集索引，但可以建立若干个非聚集索引。三、创建索引1自动创建索引： 如果在数据表的某个属性设置主键约束或唯一约束，则系统将在这些属性上自动创建唯一索引。 自动创建的索引随约束的存在而存在，随约束的消失而消失。2使用 SQL 语句创建索引Create unique clustered/nonclustered index 索引名On 表名（属性名asc/desc, 属性名asc/desc）注：若未指定 clustered，则创建非聚集索引；若未指定排序方式，则为 ASC；四、删除索引：Drop index 索引名，索引名思考题：创建主键时，如果使主键字段值不影响数据表的记录顺序？53 T-SQL 语言基础一、常量字符型：由单引号括住，例：china整型实型日期型：由单引号括住的具有日期或时间意义的序列，格式为：yyyy-mm-dd， 第 19 页，共 26 页hh:mm:ss二、变量局部变量：由用户定义和赋值，以 开头。全局变量：由系统定义和赋值，以 开头。1 声明局部变量Declare 变量名 类型例：declare a int,b char(5)2 给局部变量赋值 使用 set 语句：Set 变量名=表达式 使用 select 语句Select 变量名= 表达式,，变量名=表达式from 表名若表达式中含有属性名，则必须使用 from 子句。例：declare a char(5),b char(6)Select a=95001 ,b=王名或：select a=sno,b=snameFrom student3. 输出表达式的值： 使用 print 语句Print 表达式 使用 select 语句Select 表达式，表达式from 表名例：select sno as 学号,sname as 姓名From student等价于：select 学号=sno, 姓名=sname From student三、流程控制语句1beginend 语句Begin语句 1 ：语句 n第 20 页，共 26 页End2if-else 语句 格式：if 逻辑表达式语句 1else语句 2 当逻辑表达式包含子查询时，子查询必须用括号括住。属性名 in (子查询) 子查询返回一列多行属性名 = (子查询) 子查询返回一列一行Exists(子查询) 子查询返回多列多行3while 语句 格式：While 逻辑表达式Begin语句组End 专用于循环体的语句：Break 强制退出 while 语句，执行其后续语句。Continue 返回 while 语句的入口。Break、Continue 必须放在循环体内，并常与 if-else 语句结合使用。4Waitfor 语句54 存储过程1什么叫存储过程？将一组 SQL 语句，以一个名称存储在数据库中，就形成存储过程。2创建存储过程Create proc 存储过程名形参名 类型 outputAs SQL 语句序列例：create proc proc1a int,b int,c int outputas set c=a+bgo其中a，b 称为输入参数，c 称为输出参数。输入参数用于接收外界提供的数据，输出参数用于将参数值传递给外界。3执行存储过程Exec 存储过程名 常量| 变量名| 变量名 output： 执行无返回值的存储过程第 21 页，共 26 页Exec 变量名 1=存储过程名 常量| 变量名| 变量名 output ：执行有返回值的存储过程说明： 当实参为带 output 的局部变量时，形参必须为带 output 的局部变量。 使用 return 语句时，一个存储过程只能返回一个整数值，格式为： Return (整数值)例：declare result intexec proc1 50,100,result outputprint result4删除存储过程Drop proc 存储过程名, 存储过程名5查看存储过程exec sp_helptext 存储过程名55 触发器一、维护数据完整性的措施：创建约束 基于一个表创建创建触发器二、触发器类型：1after 触发器： 当对表实施插入、删除或修改操作以后，就自动触发 after insert、after delete 或 after update 触发器，并执行 as 后面的 SQL 语句。 一个表可以建立多个 after insert、after delete、after update 触发器。 after 触发器只能基于表创建。2Instead of 触发器： 当对表实施插入、删除或修改操作之前，就自动触发 Instead of insert、Instead of delete、 Instead of update 触发器，并执行 as 后面的 SQL 语句。 一个表或视图只能建立一个 instead of insert、instead of delete、instead of update 触发器。 instead of 触发器基于表或视图创建。三、创建触发器：Create trigger 触发器名On 表名|视图名For | after | Instead of 触发操作As SQL 语句序列说明：for 或 after，表示创建 after 触发器。触发操作是指 Insert、update、delete 中的一个或多个。第 22 页，共 26 页四、触发器的应用1两个临时表：inserted、deleted当触发器被创建时，将生成两个临时表；当触发器被触发时，将向临时表插入有关记录。 当执行 insert 语句后，新记录插入到 inserted 表中。 当执行 delete 语句后，被删记录插入到 deleted 表中。 当执行 update 语句后，原记录插入到 deleted 表中，新记录插入到 inserted 表中。2在 student 表中创建一个 after 触发器，限制 ssex 只能输入男或女。若输入其它，将回滚插入的记录。CREATE TRIGGER trig1 ON studentafter INSERT AS if exists (select * from inserted where ssex not in(男, 女)beginprint 性别只能输入男或女 rollbackend3在 student 表中创建一个 after 触发器，监控删除的学生记录。若被删的学生在 SC 表有选修课，则回滚被删记录。CREATE TRIGGER trig2 ON studentafter DELETE ASif exists(select * from deleted,sc where deleted.sno=sc.sno)beginprint 该学生已选修过课,不能删除 !rollbackend4在 student 表中创建一个 after 触发器，限制 sage 的变动只能在 1 岁以内。CREATE TRIGGER trig3 on studentafter UPDATE ASif exists( select * from inserted,deletedwhere inserted.sno=deleted.sno and abs(inserted.sage-deleted.sage)1)beginprint 年龄变动不能超过 1 岁 !rollbackend五、删除触发器Drop trigger 触发器名,触发器名第 23 页，共 26 页思考与练习一、使用 T-SQL 语言编程1将 student 表第 1 条记录的各属性值存入局部变量ae 中。2分别用 select 语句与 print 语句 输出局部变量ae 的值。3显示 student 表中年龄最大的学生的姓名、系别。4查询 student 表中是否有学号为 95001 的学生，如果有，则输出该学生的记录，否则，输出“没有学号为 95001 的学生”5 设局部变量a=12，b=100，c=8 ，请编程输出它们的最大者。6求 100 以内的偶数之和。二、已知“图书管理”数据库包括如下 3 个表，请按要求创建存储过程。书籍管理（书籍 ID，书名，出版社，作者姓名，在馆数量）读者管理（读者 ID，读者姓名，联系电话，家庭住址）借阅信息（借阅编号，书籍 ID，读者 ID，借阅日期，还书日期，借书数量）1 创建一个存储过程 proc1，要求它带有三个输入参数，用于接受外界提供的初值，要求输出最大值。试执行一次存储过程。2 创建一个无参存储过程 proc2，功能是：返回读者“ 陈里媚”所借图书的名称、出版社、作者和在馆数量。试执行一次存储过程。3 创建一个带参数的存储过程 proc3，要求：当用户输入一个读者姓名时，若该读者存在，就显示该读者的姓名、联系电话和家庭住址；若该读者不存在，则返回“查无此人”的提示信息。试执行一次存储过程。三、对“图书管理”数据库创建触发器1在“借阅信息”表创建一个 After 触发器，命名为：trig1 。要求插入每本书都要满足“还书日期大于借书日期” ，否则提示“还书日期不能小于借书日期” ，并不允许插入。2在“书籍管理”表中创建一个 After 触发器，命名为：trig2，要求每当向“书籍管理”表中删除一条书籍记录时，就自动删除“借阅信息”表中该书籍的借阅记录。3在“读者管理”表中创建 After 触发器，命名为：trig3 ，要求每当删除“读者管理”表中一条记录时，就检查该读者在“借阅信息”表是否有借阅书籍，若有，则回滚“读者管理”表被删记录，并显示提示信息。第 24 页，共 26 页第 9 章 SQL Server 的权限管理一、两种身份验证模式：仅 windows 模式：用户只能使用 windows 登录名登录 SQL Server混合模式：用户可以使用 windows 登录名或 SQL Server 登录名登录 SQL Server二、