SQL SERVER2005教学大纲.doc

SQLSERVER 2005 教学大纲一份IMB软件工程的培训教材课程名称： SQLSERVER 2005关系数据库讲授时间：14讲，56学时 适用对象：IMB的一期学员，具有c语言的编程基础，没有数据库的相关理论知识。 本大纲特点：5 适用范围广：本大纲适合各种希望学习IMB软件工程师的人群，无论有无计算机基础，并且对数学程度和英语程度不作过高要求。6 方便授课：书课程配有教学目的、知识要点、实验指导等，方便教师授课。课程简介：教学内容：第一讲：数据库基础知识本讲目的：理解数据库、DBMS的作用和意义，了解数据库管理系统的发展过程理解各种数据库模型。理解数据库相关的基本概念本讲内容： 1.1 数据库的发展历史 1.2 数据库的模型(关系数据库，层次数据库和网状数据库) 1.3 关系数据库 1.4 常见的数据库对象知识要点: 1.数据库模型分为: 关系数据库，层次数据库和网状数据库 2.关系数据库: 关系数据库表示实体和实体之间的联系3.数据库系统(DBS)database system：有数据库管理系统和管理数据库系统的人和软件组成。4.数据库管理系统(DBMS)：有数据库和管理数据库的软件组成。5.数据库(database): Sqlserver2005 中的数据库由一个表集合组成.这些表包含数据以及为支持对数据执行的活动而定义的其它对象.如视图,索引,存储过程,用户自定义的函数和触发器(这些表用于存储特定的结构化数据).表中包含行和列,行也称为记录和元组,列称为属性的集合.表中的每一列用于存储某种类型的信息.6.表(table)：记录的集合，有行(记录)和列(属性)组成。列要保持唯一，在一个数据库中，表的名字也是唯一的。7.度：描述属性的个数我们称为度。8.主键(primary key)：主键是表中一列或者多列的组成。保证了实体的完整性。9.外键(foreign key)：表中一列或者多列的组合，保证了引用的完整性。10.视图(view):从表中导出的虚拟的表。11.约束(constraint):实施数据一致性和完整性的方法。12.规则(rule)：规则同约束一样，实施数据一致性和完整性的方法。13.索引(index)提供一种无须扫描整张表就能实现快速对数据访问的方法。 (优化查询的速度)。14.存储过程(stored procedure):经过编译的可以重复使用的transact -sql 的代码的集合。理解为我们的函数。15.触发器(trigger)：特殊的存储过程(经过编译的可以重复使用的transact -sql的代码的集合，有系统自动执行)。16.默认值(default):向表中插入数据时，提供一个默认的数据。参考讲课例题：第二讲：数据库管理 本讲目的：掌握sqlserver2005的系统数据库，理解文件和文件组的概念。掌握创建数据库的多种方式，修改数据库，使用 sp_dboption修改数据库的属性，利用存储过程sp_helpdb,sp_databases,sp_helpfile查看数据库的详细信息和数据库的文件信息。理解数据库分离和附加的作用，使用sp_attach 附加数据库，sp_detatch分离数据库。本讲内容： 1.1 系统数据库 1.2 文件和文件组 1.3 创建数据库 1.4 修改数据库 1.5 修改数据库属性 1.6 浏览数据库(查看数据库详细信息) 1.7 分离和附加数据库 1.8 备份数据库知识要点：1.sqlserver2005 数据库分为系统数据库和用户定义数据库.2.sqlserver2005系统数据库主要由:master,msdb,model,resource,tempdbmaster数据库 : 记录 SQL Server 实例的所有系统级信息。msdb 数据库: 用于 SQL Server 代理计划警报和作业。 model 数据库:用作 SQL Server 实例上创建的所有数据库的模板。model 数据库进行的修改(如数据库大小、排序规则、恢复模式和其他数据库选项)将应用于以后创建的所有数据库。Resource 数据库 :一个只读数据库，包含 SQL Server 2005 包括的系统对象。系统对象在物理上保留在 Resource 数据库中，但在逻辑上显示在每个数据库的 sys 架构中。tempdb 数据库 :一个工作空间，用于保存临时对象或中间结果集。 3.sqlserver2005数据库至少具有两个操作系统的文件:数据文件和日志文件. 数据文件包含数据和对象,例如表,索引,存储过程和视图.日志文件包含对恢复数据库中所有事务所需的信息.为了便于管理可以将数据文件集合起来,存放到文件组中.4.SQL Server 2005 数据库具有三种类型的文件，如下表所示： 文件 说明 主要主要数据文件包含数据库的启动信息，并指向数据库中的其他文件。用户数据和对象可存储在此文件中，也可以存储在次要数据文件中。每个数据库有一个主要数据文件。主要数据文件的建议文件扩展名是 .mdf。 次要次要数据文件是可选的，由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上。另外，如果数据库超过了单个 Windows 文件的最大大小，可以使用次要数据文件，这样数据库就能继续增长。次要数据文件的建议文件扩展名是 .ndf。事务日志事务日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。事务日志的建议文件扩展名是 .ldf。 5. 每个数据库有一个主要文件组。此文件组包含主要数据文件和未放入其他文件组的所有次要文件。可以创建用户定义的文件组，用于将数据文件集合起来，以便于管理、数据分配和放置。事务日志文件不属于任何文件组. 6.数据库的创建: CREATE DATABASE SalesON ( NAME = Sales_dat, FILENAME = + data_path + saledat.mdf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )LOG ON( NAME = Sales_log, FILENAME = + data_path + salelog.ldf, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ); 7.修改数据库: Alter database sales8.查看数据库的信息: Sp_database; Sp_helpdb Sp_helpfilegroup9.修改数据库的属性: 设置数据库为只读: Exec sp_dboption sales,read only,true设置数据库为自动压缩:Exec sp_dboption sales,autoshrink,true 设置数据库为单用户: Exec sp_dboption sales,single user,true 收缩数据库的大小 Dbcc shrinkdatabase (sales,10) 注解:将sales数据库的文件减小,以使userdb中的文件有10%的可用空间.10. 数据库的分离和附加: 分离数据库是指将数据库从 SQL Server 实例中删除，但使数据库在其数据文件和事务日志文件中保持不变。之后，就可以使用这些文件将数据库附加到任何 SQL Server 实例，包括分离该数据库的服务器。 分离: Exec sp_datach_db sales附加:exec sp_attach_db dbname=sales, filename1=c:salessales_data.mdf, filename2=c:salessales_data.ndf11.数据库的备份:(1).添加磁盘存储设备: Exec sp_addumpdevice disk,mydiskdump,c:dumpdump.bak(2)将数据库备份写入备份设备: Backupdatabase sales to mydiskdump(3) 恢复数据库 Restore database sales from mydiskdump. 参考讲课例题： /* 创建没有主数据文件和日志文件的数据库*/use mastergoif db_id( sales) is not nulldrop database salesgocreate database salesgo/* 自定义创建数据库*/use mastergoif db_id(sales) is not nulldrop database salesgocreate database saleson -创建主数据文件( name = sales_dat ,-逻辑名称 filename=d:salessales.mdf,-物理路径 size=10MB, -初始化大小,单位默认为MB maxsize =50, -数据库的最大容量 filegrowth=5MB -文件的增长速度)log on( name= sales_log, filename=d:salessales.ldf, size=5MB, maxsize=25, filegrowth=5)/* 查看数据库的信息,利用系统存储过程. (存储过程分为两类:系统存储过程和用户自定义存储过程) 利用execute(执行) 调用存储过程,系统的存储过程以sp_开头,系统的存储过程 可以省略execute 关键字.*/execute sp_databases -查看系统数据库的信息execute sp_helpdb -查看数据库的详细信息execute sp_helpdb sales -后边增加参数(数据库的名称),可以查看指定 -数据库的详细信息use salesgoexec sp_helpfile -查看指定库的系统文件的信息exec sp_renamedb sales,mysales -修改数据库的名称/* 修改数据库*/alter database mysalesmodify file( name=sales_dat, size=20MB -根据逻辑名称进行存储数据的修改)gosp_helpdb mysalesalter database mysales add file - 增加数据文件( name=sales_ndat, filename=d:salessales.ndf, -要求我们的文件夹路径必须存在 maxsize=50MB, size=10MB, -初始化大小 filegrowth=5MB)sp_helpfile /* 修改数据库的选项*/-1.设置数据库为只读数据库exec sp_dboption mysales,read only,trueexec sp_dboption mysales,read only,false-2.设置数据库为自动压缩exec sp_dboption mysales,autoshrink,true-3.设置数据库为单用户数据库 exec sp_dboption mysales,single user,true exec sp_dboption mysales,single user,false-4. 收缩我们的数据库 dbcc shrinkdatabase (mysales,10) -收缩数据库mysales,留下%的可利用空间./* 数据库的分离和附加*/-分离 exec sp_detach_db mysales-附加 exec sp_attach_db dbname=mysales, -数据库的名称 filename1=c:salessales.mdf, -数据库的文件物理路径 filename2=c:salessales.ndf, filename3=c:salessales.ldf/* 数据库的备份(完全备份)*/-(1)添加备份设备 use master go exec sp_addumpdevice disk,mybackup,c:backupbackup.bat -disk进行磁盘备份 - mybackup ，备份的逻辑名称 -c:backupbackup.bat 备份的物理路径。-(2)将数据库备份写入备份的文件(逻辑名称) backup database mysales to mybackup -(3)还原备份数据库 restore database mysales from mybackup -(4)删除备份设备 sp_dropdevice mybackup第三讲：管理数据库表本讲目的：重新理解数据库表的概念，理解数据库的数据完整性的概念。掌握数据库表实施数据约束的主键，外键，唯一，check和默认值约束。灵活运用创建数据库表，修改数据库表列属性，列和表名称等。掌握在创建表的过程中添加约束。掌握在创建表后添加约束和删除约束。利用系统存储过程查看表名称和表约束。本讲内容： 1.1 主键和外键，默认值，唯一，CHECK(应该讲述同一张表的自引用外键) 1.2 创建表 1.3 修改表删除表 1.4 创建约束和修改约束，删除约束 1.5 查看表和约束知识要点：1.标识符: 数据库对象的名称即为其标识符。标识符分为两类:常规标识符和分割表识符.2.常规标识符: (1)第一个字符必须是下列字符之一： Unicode 标准 3.2 所定义的字母。Unicode 中定义的字母包括拉丁字符 a-z 和 A-Z，以及来自其他语言的字母字符。下划线 (_)、“at”符号 () 或者数字符号 (#)。 在 SQL Server 中，某些位于标识符开头位置的符号具有特殊意义。以“at”符号开头的标识符表示局部变量或参数。以一个数字符号开头的标识符表示临时表或过程。以两个数字符号 (#) 开头的标识符表示全局临时对象。 (2)后续字符可以包括： 如 Unicode 标准 3.2 中所定义的字母。基本拉丁字符或其他国家/地区字符中的十进制数字。“at”符号、美元符号 ($)、数字符号或下划线。(3)标识符不能是 Transact-SQL 保留字。SQL Server 保留其保留字的大写和小写形式。(4)不允许嵌入空格或其他特殊字符。3.分割标识符(限定标识符) 包含在双引号 () 或者方括号 ( ) 内。符合标识符格式规则的标识符可以分隔，也可以不分隔4.数据类型: 在 SQL Server 2005 中，每个列、局部变量、表达式和参数都具有一个相关的数据类型。数据类型是一种属性，用于指定对象可保存的数据的类型：整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等。数据类型分为系统数据类型和用户自定义数据类型:.Sqlserver2005的系统数据类型归纳为以下数据类型.精确数字:bigint decimal int numeric smallint money tinyint smallmoney bit 近似数字float real 日期和时间datetime smalldatetime 字符串char text varchar Unicode 字符串nchar ntext nvarchar 二进制字符串Binary image varbinary 其他数据类型Cursor timestamp sql_variant uniqueidentifier table xml 5.用户自定义的数据类型: (1)执行 sp_addtype 创建别名数据类型，该数据类型可在特定数据库的 sys.types 目录视图中出现。如果所有新的用户定义数据库中都必须具有这一别名数据类型，请将其添加到 model。创建了别名数据类型之后，可以在 CREATE TABLE 或 ALTER TABLE 中使用它，也可以将默认值和规则绑定到别名数据类型 (2)使用sp_droptype删除别名数据类型.6.约束: (1) PRIMARY KEY 约束 一个表只能包含一个 PRIMARY KEY 约束。 每个 PRIMARY KEY 都生成一个索引。 在 PRIMARY KEY 约束内定义的所有列都必须定义为 NOT NULL。如果未指定为空性，加入 PRIMARY KEY 约束的所有列的为空性都将设置为 NOT NULL。 (2)UNIQUE 约束 每个 UNIQUE 约束都生成一个索引。 (3)FOREIGN KEY 约束 如果在 FOREIGN KEY 约束的列中输入非 NULL 值，则此值必须在被引用的列中存在；否则，将返回违反外键约束的错误消息。 FOREIGN KEY 约束可以引用同一表中的其他列，且引用为自我引用。但是，FOREIGN KEY 约束不能用于创建自我引用或循环的 FOREIGN KEY 约束。 create table employee( em_id int primary key, em_name varchar(20), manager_id int foreign key references employee(em_id)列级 FOREIGN KEY 约束的 REFERENCES 子句仅能列出一个引用列。该列必须与定义约束的列具有相同的数据类型。 表级 FOREIGN KEY 约束的 REFERENCES 子句中引用列的数目必须与约束列的列表中的列数相同。每个引用列的数据类型也必须与列的列表中相应列的数据类型相同。 FOREIGN KEY 约束只能引用被引用表的 PRIMARY KEY 或 UNIQUE 约束中的列。FOREIGN KEY 约束不能引用唯一索引。 (4)DEFAULT 定义每列只能有一个 DEFAULT 定义。这可以包含常量值或常量函数。(5)表定义中的为空性规则列的为空性用于确定在该列中是否允许以空值 (NULL) 作为其数据。NULL 不是零或空白。它表示没有输入任何内容，或提供了一个显式 NULL 值，通常表示该值未知或不适用。(6) 在 INSERT、UPDATE 或 DELETE 语句中违反约束时，将终止该语句。 7.创建表: CREATE TABLE table_name ( column_name data_type DEFAULT constant_expression | IDENTITY ( seed , increment ) NULL | NOT NULL | PRIMARY KEY | UNIQUE | REFERENCES ref_table ( ref_column ) ) (1) table_name 新表的名称。表名称必须符合标识符规则。table_name 在数据库中必须是唯一的。table_name 最多可包含 128 个字符。 (2)column_name 表中列的名称。列名称必须符合标识符规则，并且在表中必须是唯一的。 (3) DEFAULT 指定在插入操作中没有显式提供值时为该列提供的值。除了 IDENTITY 属性定义的列之外，DEFAULT 定义可应用于任何列。删除表时将删除 DEFAULT 定义。常量值可用作默认值。 (4)IDENTITY 指示新列是标识列。在为表添加新行时，SQL Server将为列提供唯一的增量值。标识列通常与 PRIMARY KEY 约束结合使用以作为表的唯一行标识符。IDENTITY 属性只能分配给 int 列。每个表只能创建一个标识列。标识列无法使用绑定默认值和 DEFAULT 约束。必须同时指定种子和增量，或者不指定任何值。如果不指定任何值，默认为 (1,1)。 seed 向表中加载第一行时所使用的值。 increment 加到所加载的上一行的标识值上的增量值。 (5) NULL | NOT NULL 用于指定列中是否允许空值的关键字。从严格意义上讲，NULL 不是一个约束，但可以使用指定 NOT NULL 相同的方法来指定。 (6)PRIMARY KEY 通过使用唯一的索引对特定列强制执行实体完整性的约束。对于每个表只能创建一个 PRIMARY KEY 约束。 (7)UNIQUE 通过使用唯一的索引提供特定列的实体完整性的约束。UNIQUE 约束中的列可以为 NULL，但每一列只允许一个 NULL 值。一个表可以包含多个 UNIQUE 约束。 (8)FOREIGN KEY.REFERENCES 为列中的数据提供引用完整性的约束。FOREIGN KEY 约束要求列中的每个值都存在于所引用表的指定的列中。 ref_table FOREIGN KEY 约束所引用的表的名称。 ( ref_column ,. n ) FOREIGN KEY 约束所引用的表中的一列或多列。 注:以上内容须举例详细讲解:8.修改表: 使用alter table table_name更改、添加或删除列和约束.(1) 增加列:(2) 修改列:(3) 修改数据类型(4) 增加约束(paiamry key,foreign key,default,unique,check)(5) 删除约束.注:需要举例讲解: 9.利用存储过程查看和修改表的信息: (1) exec sp_help:查看表的详细信息: (2) exec sp_rename 修改表的名称或者列名称. (3) exec sp_helpconstraint table_name -查看指定表的所有约束(4)exec sp_help table_name - 查看指定表的所有信息(5)exec sp_pkeys table_name - 查看指定表的主键约束(6)exec sp_fkeys table_name - 查看指定表的外键约束 -(外键属于主表，而不是从表)参考讲课例题：/* (1)标志符： 规则标志符： 限定标志符 (2)数据类型 系统数据类型 用户自定义数据类型 (3)用户自定义数据类型 用户自定义数据类型只在所在库内有效 如果希望所有的库内有效，应建立在model库内。*/sp_addtype address,varchar(100),not null -定义sp_droptype address -删除/* 建表。*/if db_id(sales) is not nulldrop database salesgocreate database salesgouse salesgoif exists (select name from sysobjects where type=u and name=goods)drop table goods -删除表gocreate table goods -创建表( goods_id int identity(1000,1) not null, goods_name varchar(50), goods_num int, goods_price money )exec sp_help goods -查看表的详细信息exec sp_rename goods,orders -修改表的名称select name from sysobjects where type=uexec sp_rename orders.goods_id,g_id,column -修改列的名称exec sp_rename orders.goods_name,g_name,column/* 修改表结构1.增加列*/alter table orders add goods_date datetimesp_help orders/* 修改表结构1.修改列*/alter table ordersalter column g_name varchar(100)/* 修改表结构1.删除列*/alter table ordersdrop column goods_datesp_help orders/*/* 创建表的过程是保证数据完整性的过程 (1)实体完整性(primary key,unique) 1.一个表只能由一个主键，但是可以有多个唯一 2.主键唯一且非空，而unique唯一或者非空。 (2)引用完整性。(foreign key) (3)域完整性(not null,check,default )*/if exists (select name from sysobjects where type=u and name=goods)drop table goods -删除表gocreate table goods -创建表( goods_id int identity(1000,1) not null primary key, -主键 goods_name varchar(50) not null unique, -唯一约束 goods_num int check (goods_num10 and goods_num10 and goods_num 2(11) insert into new_table_name(column_list) select (column_list) from table_name .多行的插入,如果源表和目的表已经存在,必须保证他们的结构相同才可以复制数据.3.更新语句: 更改表或者视图中的现有数据。 update table_name set col_name=values where 条件4.删除语句： (1) delete from table_name where 条件 (2) 删除表中的所有行，而不记录单个行删除操作。TRUNCATE TABLE 在 功能上与没有 WHERE 子句的 DELETE 语句相同；但是，TRUNCATE TABLE 速度更 快，使用的系统资源和事务日志资源更少。 (3) 如果表包含标识列，该列的计数器重置为该列定义的种子值。如果未定义 种子，则使用默认值 1。若要保留标识计数器，请使用 DELETE。 参考讲课例题： 1.学生表添加记录。老师表添加记录。班级表添加记录。分数表添加记录，科目表添加记录 2.查找所有的学生85年以前出生的。 4.根据电话号码查找所有西安的老师（029开头） 5.所有西安的老师的工资增加%10。6.所有的日期函数第五讲：t-sql编程 本讲目的：理解批处理的概念。理解t_sql中的数据类型。掌握编写 应用逻辑变量的定义与赋值。灵活运用SQL 逻辑控制语句(if if else while case . when then.else .end)等。掌握系统函数中的日期函数，字符串函数。本讲内容： 1.1 数据类型 1.2 声明变量和变量赋值 1.3 运算符，表达式，通配符 1.4 控制流程(casewhen.then.else.end中应讲述交叉报表) 1.5 系统函数知识要点：1. sqlserver2005中sql语言的组成: 数据定义语言DDL (Data Definition Language)数据操纵语言DML (Data Manipulation Language) 数据控制语言DCL (Data Control Language) 其它语言要素 (Additional Language Elements)2. 回顾讲述sqlserver2005的数据类型系统数据类型和用户自定义的数据类型SP_ADDTYPE 给数据类型起别名SP_DROPTYPE 删除用户自定义的数据类型注:如果希望在所有的系统都可以使用自定义的数据类型,应把该数据类型定义在model数据库中3. 变量:全局变量和局部变量:(1) 局部变量: 局部变量是用户可自定义的变量它的作用范围仅在程序内部在程序中通常用来储存从表中查询到的数据或当作程序执行过程中暂存变量使用局部变量必须以 开头而且必须先用DECLARE 命令说明后才可使用其(2) 变量的声明:DECLARE 变量名 变量类型 变量名 变量类型(3) 变量的赋值:SELECT 局部变量 = 变量值SET 局部变量= 变量值注意:select 语句的赋值功能和查询功能不能混合使用,否则系统会产生错误信息Set 每次只能给一个变量赋值,而select一次可以给多个变量赋值,同时可以在查询中给变量赋值.(4) 全局变量: 全局变量是SQL Server 系统内部使用的变量其作用范围并不局限于某一程序而是任何程序均可随时调用4.注释符: ANSI 标准的注释符- 用于单行注释与C 语言相同的程序注释符号即/*/ 5.运算符(operator): 算术运算符包括 + 加 减乘/ 除% 取余 比较运算符包括 大于= 大于等于= 小于等于不等于!= 不等于! 不大于! ! 不是ANSI 标准的运算符 逻辑运算符包括AND 与OR 或NOT 非 位运算符包括& 按位与| 按位或 按位非 按位异或 连接运算符连接运算符+ 用于连接两个或两个以上的字符或二进制串列名或者串和列的混合体将一个串加入到另一个串的末尾其 6.通配符:% 代表零个或多个字符 ab% ab后可接任意字符串_ 下划线代表一个字符 a_b a与b之间可以有一个字符 表示在某一范围的字符 0-9 0 到9 之间的字符 表示不在某一范围的字符 09 不在0 到9 之间的字符7.流程控制语句: (1) IF ELSE其语法如下:IF ELSE 条件表达式 (2) BEGIN END其语法如下BEGINEND注:BEGIN END 用来设定一个程序块将在BEGIN END 内的所有程序视为一个单元执行 (3) CASECASE 命令有两种语句格式CASE WHEN THEN WHEN THEN ELSE ENDCASEWHEN THEN WHEN THEN ELSE END注: case只运行第一个匹配的子句. 输出学生性别: create table stu( stu_id int identity(100,1) primary key, stu_name varchar(20), stu_sex bit)insert stu(stu_name,stu_sex) select lisi,0 union all select wangwu,1 union all select zhanghua,0 union all select lili,0 select stu_id,stu_name,stu_sex=case when stu_sex=0 then 女 else 男 end from stu 讲解交叉报表: if exists(select name from sysobjects where name=salary and type=u)drop table salarygocreate