SQLServer教案第07周-数据库和表的创建

精品文档 1欢迎下载 授课日期授课日期 年 月 日 第 6 周授课形式授课形式讲课授课时数授课时数 4 章节名称章节名称第 06 章 数据库和表的创建 教学目的与要求教学目的与要求 理解数据库中的数据文件和日志文件的概念和作用 理解 SQL Server 中系统数据库与用户数据库的概念 掌握数据库的创建方法以及创建数据库的 Create Database 语句 掌握在数据库中创建表的 Create Table 语句和撤销表的 DropTable 语句 掌握为表添加字段 撤销字段的方法和相关语句 理解约束的概念和类型 以及约束的作用 掌握为表添加约束 撤销约束的方法和相关语句 掌握用 Insert Into 语句为数据表插入新记录的语法 掌握用 Update 语句为数据表更新已经数据的语法 掌握用 Delete From 语句为数据表删除记录的语法 教学重点教学重点 教学难点教学难点 教学方法和手段教学方法和手段讲授法结合课堂实例分析讨论 教学过程与组织教学过程与组织 导入新课导入新课 我们已经学习关系数据库的相关理论 现在 我们以 SQL Server 2005 数据库为例 介绍如何 在 DBMS 中创建数据库和表 讲授新课讲授新课 第第 0707 章章 数据库和表的创建数据库和表的创建 第第 0101 节节 数据库的创建数据库的创建 7 1 1 SQL Server 数据库的结构 1 数据库文件和文件组 SQL Server 2000 用文件来存放数据库 数据库文件有三类 主数据库文件 Primary 存放数据 每个数据库都必须有一个主数据文件 其他数据文件 Secondary 存放数据 一个数据库可以没有也可以有多个 Secondary 文件 事务日志文件 Transaction Log 存放事务日志 每个数据库必须有一个或多个日志文件 一般情况下 一个简单的数据库可以只有一个主数据文件和一个日志文件 如果数据库很大 则可以设置多个 Secondary 文件和日志文件 并将它们放在不同的磁盘上 默认状态下 数据文件名为 数据库名 Data MDF 日志文件名为 数据库名 Log LDF 数据 库的创建者可以在创建时指定其他的路径和文件名 也可以添加 Secondary 文件和更多的日志文件 文件组允许多个数据库文件组成一个组 并对它们整体进行管理 比如 可以将三个数据文件 data1 mdf data2 mdf 和 data3 mdf 分别创建在三个盘上 这三个文件组成文件组 fgroup1 在创建表的时候 就可以指定一个表创建在文件组 fgroup1 上 SQL Server 的数据库文件和文件组必须遵循以下规则 1 一个文件和文件组只能被一个数 据库使用 2 一个文件只能属于一个文件组 3 数据和事务日志不能共存于同一文件或文件组 上 4 日志文件不能属于文件组 2 数据库对象 精品文档 2欢迎下载 SQL Server 2000 数据库中的数据在逻辑上被组织成一系列对象 当一个用户连接到数据库后 他所看到的是这些逻辑对象 而不是物理的数据库文件 SQL Server 中有以下数据库对象 表 Table 视图 View 存储过程 Stored procedures 触发器 Triggers 用户自定义数据类型 User defined data types 用户自定义函数 User defined functions 索引 Indexes 规则 Constraints 默认值 Defaults 等 7 1 2 SQL Server 2005 的系统数据库 1 master 数据库 master 数据库记录了 SQL Server 系统级的信息 包括系统中所有的登录账号 系统配置信息 所有数据库的信息 所有用户数据库的主文件地址等 每个数据库都有属于自己的一组系统表 记 录了每个数据库各自的系统信息 这些表在创建数据库时自动产生 为了与用户创建的表相区别 这些表被称为系统表 表名都以 sys 开头 master 数据库中还有很多系统存储过程和扩展存储 过程 系统存储过程是预先编译好的程序 所有的系统存储过程的名字都以 sp 开头 2 tempdb 数据库 tempdb 数据库用于存放所有连接到系统的用户临时表和临时存储过程以及 SQL Server 产生的 其他临时性的对象 tempdb 是 SQL Server 中负担最重的数据库 因为几乎所有的查询都可能需要 使用它 在 SQL Server 关闭时 tempdb 数据库中的所有对象都被删除 每次启动 SQL Server 时 tempdb 数据库里面总是空的 3 model 数据库 model 数据库是系统所有数据库的模板 这个数据库相当于一个模子 所有在系统中创建的新 数据库的内容 在刚创建时都和 model 数据库完全一样 如果 SQL Server 专门用作一类应用 而这 类应用都需要某个表 甚至在这个表中都要包括同样的数据 那么就可以在 model 数据库中创建这 样的表 并向表中添加那些公共的数据 以后每一个新创建的数据库中都会自动包含这个表和这些 数据 当然 也可以向 model 数据库中增加其他数据库对象 这些对象都能被以后创建的数据库所 继承 4 msdb 数据库 msdb 数据库被 SQL Server 代理 SQL Server Agent 来安排报警 作业 并记录操作员 7 1 3 创建数据库 1 在 Manager Studio 中创建数据库 略 2 使用 T SQL 创建数据库 使用 T SQL 语句创建数据库的语法格式如下 CREATE DATABASE 数据库名 ON NAME 数据文件逻辑名称 FILENAME 存取路径 数据文件名 SIZE 数据文件初始大小 FILEGROWTH 数据文件增长率 LOG ON NAME 日志文件逻辑名称 精品文档 3欢迎下载 FILENAME 存取路径 日志文件名 SIZE 日志文件初始大小 FILEGROWTH 日志文件增长率 注意 在 SQL Server 2005 2008 中 数据文件和日志文件的初始大小至少要为 3M 通过执行以下的语句可以创建 LWZZ 数据库 CREATE DATABASE LWZZ ON NAME LWZZ Data FILENAME C Program Files Microsoft MSSQL data LWZZ Data MDF SIZE 3 FILEGROWTH 10 LOG ON NAME LWZZ Log FILENAME C Program Files Microsoft MSSQL data LWZZ Log LDF SIZE 3 FILEGROWTH 10 我们也可以简单地来创建数据库 CREATE DATABASE mytest 由于没有指定主文件名 在默认的情况下 命名主文件为 mytest mdf 日志文件名为 mytest log log 同时由于按复制 Model 数据库的方式来创建新的数据库 主文件和日志文件的大 小都同 Model 数据库的主文件和日志文件大小一致 并且可以自由增长 7 1 4 修改数据库 修改数据库包括增减数据文件和日志文件 修改文件属性 包括更改文件名和文件大小 修 改数据库选项等 1 在 Manager Studio 中修改数据库 略 2 使用 T SQL 语句修改数据库 1 用 ALTER DATABASE 语句增减数据文件和日志文件 增加数据文件的语法如下 ALTER DATABASE 数据库名 ADD FILE NAME 逻辑数据文件名 FILENAME 存取路径 数据文件名 SIZE 数据文件初始大小 MAXSIZE 文件大小最大值 FILEGROWTH 文件增长率 修改数据文件的大小的语法如下 精品文档 4欢迎下载 ALTER DATABASE 数据库名 MODIFY FILE NAME 逻辑数据文件名 SIZE 数据文件初始大小 MAXSIZE 文件大小最大值 FILEGROWTH 文件增长率 例如 ALTER DATABASE LWZZ ADD FILE NAME LWZZ data2 FILENAME C Program Files Microsoft MSSQL data LWZZ data2 ndf SIZE 5MB MAXSIZE 50MB FILEGROWTH 1MB 其中 ADD FILE 是指增加一个数据文件 还可以是 ADD LOG FILE REMOVE FILE MODIFY FILE 等 分别代表增加日志文件 删除数据库文件和修改文件信息 比如 以下的语句将 LWZZ 数据库 的第二个数据文件 LWZZ data2 的初始大小修改为 20MB ALTER DATABASE LWZZ MODIEY FILE NAME LWZZ data2 SIZE 20MB 关于 ALTER DATABASE 语句的更详细用法可以参考 SQL Server 2000 的在线手册 2 用 ALTER DATABASE 语句修改数据库选项 例如 ALTER DATABASE LWZZ SET AUTO SHRINK ON 3 使用系统存储过程 sp dboption 修改数据库选项 Sp dboption LWZZ autoshrink true 除了设置选项外 Sp dboption 还有很多其他的作用 如运行不带参数的 Sp dboption 可以 得到所有数据库选项列表 如果希望看到某个数据库当前的选项设置情况 可以给 Sp dboption 加上一个数据库名作为参 数 SQL Server 将显示所有在该数据库中设置为 True 的选项 如果想查看某个特定选项的当前设置值 可以输入数据库名和选项名 7 1 5 删除数据库 1 在 Manager Studio 中删除数据库 略 2 使用 T SQL 语句删除数据库 使用 SQL 语句删除数据库需要使用 DROP DATABASE 语句 使用 DROP DATABASE 语句可以一次删 除多个数据库 语法格式为 DROP DATABASE 数据库名列表 例如 DROP DATABASE northwind pubs 精品文档 5欢迎下载 第第 0202 节节 数据表的创建数据表的创建 7 2 1 创建表结构 1 在 Manager Studio 中创建数据表 略 2 使用 T SQL 语句创建数据表 使用 SQL 语句定义表的语句格式为 CREATE TABLE 表名 列名 数据类型 列级完整性约束定义 列名 数据类型 列级完整性约束定义 表级完整性约束定义 SQL 语句中有定义这三类完整性的相关语法成份 完整性约束又分为列级约束和表级约束 1 实体完整性约束 用于定义表中的主码约束 在本列的列名 数据类型之后 写上以下 的英文短语 就是将本列定义为主码 PRIMARY KEY 2 参照完整性约束 用于定义表中的外码约束 在本列的列名 数据类型之后 写上以下 的英文短语 就是将本列定义为外码 FOREIGN KEY 列名 REFERENCES 外表名 外表中被引用的列名 3 惟一性约束 在某一列的列名 数据类型说明之后 写上以下的英文短语 就是将本列定义为惟一性约束 值唯一 无重复 UNIQUE 5 非 空值约束 将某列定义为 非 空值约束的方法是 在某一列的列名 数据类型之后 加上以下成份 NOT NULL 6 CHECK 约束 当需要限制某列的取值范围时 通常使用这一约束 方法是 在某一列的列名 数据类型之后 加上以下成份 CHECK 约束表达式 凡是在某一列的列名 数据类型之后定义的约束 都叫 列级约束 特别地 默认值约束和 非 空值约束只能定义为列级约束 凡是在一个表的全部列定义之后再定义的约束 都叫 表级 约束 多数情况下 一个表中的主码是惟一的列 在本列将它定义为主码约束即可 7 默认值 严格来说 默认值不是约束 将某列定义为默认值约束的方法是 在某一列的列名 数据类型 之后 加上以下成份 DEFAULT 常量 下面给出创建表的实例 系信息表 Department 结构如下 列名说明类型和长度约束 DenoDeno 系编号字符串 6 位主键 精品文档 6欢迎下载 DnameDname 系名称字符串 最长 20 位非空 唯一 TelTel 联系电话字符串 最长 11 位唯一 CREATE TABLE Department DeNo char 6 CONSTRAINT Department DeNo PK Primary Key DName varchar 20 CONSTRAINT Department DName NN Not NULL CONSTRAINT Department DName UK UNIQUE Tel varchar 11 CONSTRAINT Department Tel UK UNIQUE 学生信息表 Student 结构如下 列名说明类型和长度约束 SnoSno 学号字符串 8 位主键 SnameSname 学生姓名字符串 最长 12 位非空 唯一 SsexSsex 性别字符串 2 位取男或女 BirthBirth 出生日期日期时间大于 1900 且小于当前时间 DepartDepart 所属系字符串 6 位外键 默认为 D030001 AddrAddr 联系地址字符串 最长 50 位允许为空 CREATE TABLE Student SNo char 8 CONSTRAINT Student SNo PK Primary Key SName varchar 12 CONSTRAINT Student SName NN Not NULL SSex char 2 CONSTRAINT Student SSex CK Check Ssex 男 OR Ssex 女 Birth Datetime CONSTRAINT Student Birth CK Check Birth 1900 AND Birth 0 And CCredit 0 And Period 0 AND Grade 100 CONSTRAINT SC SNo CNo PK PRIMARY KEY Sno Cno 7 2 2 修改表结构 创建完一个表以后 难免要对表进行修改 1 在 Manager Studio 中修改表结构 略 2 使用 T SQL 语句修改表结构 使用 SQL 语句定义表的语句格式为 1 添加列 向表中增加一列时 应使新增加的列有默认值或允许为空值 SQL Server 将向表中已存在的行 填充新增列的默认值或空值 如果既没有提供默认值也不允许为空值 那么新增列的操作将出错 因为 SQL Server 不知道该怎么处理那些已经存在的行 向表中添加列的语句格式如下 ALTER TABLE 表名 ADD 列名 列的描述 例如 为 SC 表新增一个新列 列名为 ScType 表示选课类别 4 位定长字符串 允许为空 ALTER TABLE SC ADD ScType char 4 NULL 2 删除列 删除一列的语句格式为 ALTER TABLE 表名 DROP COLUMN 列名 例如 删除 SC 表的 ScType 列 ALTER TABLE SC DROP COLUMN ScType 3 修改列定义 表中的每一列都有其定义 包括列名 数据类型 数据长度以及是否允许为空值等 这些值都 可以在表创建好以后修改 修改列定义的语句格式为 ALTER TABLE 表名 ALTER COLUMN 列名 列的描述 例如 将 Student 表的 Addr 列改为最大长度为 60 的 varchar 型数据 ALTER TABLE Student ALTER COLUMN Addr varchar 60 4 修改表约束 添加主码约束 精品文档 8欢迎下载 添加主码约束的 SQL 语句的格式如下 ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY 列名 n 添加外键约束 添加外键约束的 SQL 语句的格式如下 ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY 列名 REFERENCES 被引用表名 被引用列名 添加惟一性约束 添加惟一性约束的 SQL 语句的格式如下 ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE 列名 n 添加 CHECK 约束 添加 CHECK 约束的 SQL 语句的格式如下 ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK 约束条件 禁用约束 将某张表的某个约束暂时禁用 语法如下 ALTER TABLE 表名 NOCHECK CONSTRAINT 约束名 重新启用被禁用的约束 将某张表的某个被禁用的约束再次禁用 语法如下 ALTER TABLE 表名 CHECK CONSTRAINT 约束名 5 添加列的默认值 添加 DEFAULT 默认值的 SQL 语句的格式如下 ALTER TABLE 表名 ADD CONSTRAINT 约束名 DEFAULT 常量 FOR 列名 6 修改表名 修改表名的语句格式为 EXEC sp rename 原表名 新表名 7 2 3 撤销表结构 当不再需要某个表时 可以将其删除 一旦一个表被删除 那么它的数据 结构定义 约束 索引 都将被永久地删除 以前用来存储数据和索引的空间可以用来存储其他的数据库对象了 1 在 Manager Studio 中撤销表结构 略 2 使用 T SQL 语句撤销表结构 使用 SQL 语句撤销表的语句格式为 DROP TABLE 表名 表名 例 删除表名为 Test1 和 Test2 的两张表 DROP TABLE Test1 Test2 第第 0303 节节 数据更新数据更新 6 3 1 数据行的插入 1 在 Manager Studio 中为表插入数据行 略 2 使用 T SQL 语句为表插入数据行 使用 SQL 语句为表插入数据行的语句格式为 INSERT INTO 表名 列名列表 VALUES 值列表 精品文档 9欢迎下载 其中 1 表名 是要插入数据的表名 2 列名列表 是要插入的数据列的各列名称 必须是表中有定义的列名 各列名之间用逗号 隔开 3 值列表 插入各列的实际数据 当语句中存在列名列表时 值列表中数据的个数 顺序 数据类型 要保持严格的一一对应关系 如果要插入空值 可用 NULL 表示 例 将记录 g001 计算机系 插入表 DEPARTMENT 中 INSERT INTO DEPARTMENT DeNO DName Tel VALUES g001 计算机系 NULL 例 将新生记录 20062105 张国立 男 1988 05 16 g002 插入 Student 表中 INSERT INTO STUDENT SNo SName SSex Birth Depart VALUES 20062105 张国立 男 1988 05 16 g002 例 将一个新的学生选课 20062105 C01 插入表 SC 中 INSERT INTO SC SNo CNo VALUES 20062105 C01 例 将记录 20062105 C01 90 插入表 SC 中 INSERT INTO SC SNo CNo GRADE VALUES 20062105 C01 90 6 3 2 数据修改 1 在 Manager Studio 中为表修改数据 略 2 使用 T SQL 语句为表修改数据 使用 SQL 语句为表修改数据的语句格式为 UPDATE 表名 SET 列名 表达式 列名 2 表达式 2 WHERE 条件表达式 如果不使用 WHERE 子句 所以对发表时间表中某一个字段的修改影响到表中的每一行 例 将 Course 表中所有课程的学时都加 2 UPDATE Course SET Period Period 2 例 将 Student 表中的姓名为 王丹凤 的出生日期改为 1985 08 24 UPDATE Student SET Birth 1985 08 24 WHERE SName 王丹凤 6 3 3 数据行的删除 1 在 Manager Studio 中为表删除数据行 略 2 使用 T SQL 语句为表删除数据行 使用 SQL 语句为表删除数据行的语句格式为 DELETE 使用 T SQL 中的 DELETE 语句可以删除数据表中的一个或多个记录 DELETE 语句最简单的形式如下 DELETE 表名 WHERE 条件 其中 表名是要删除数据的表的名字 如果 DELETE 语句中没有 WHERE 子句限制 表中的所有记 录都将被删除 例 删除表 SC 中全部的记录 DELETE FROM SC 例 删除 SC 表中所有不及格学生的选课记录 精品文档 10欢迎下载 DELETE FROM SC WHERE Grade 60 TRUNCATE TABLE 语句 TRUNCATE TABLE 语句提供了一种删除表中所有记录的快速方法 因为 TRUNCATE TABLE 语句不 记录日志 只记录整个数据页的释放操作 而 DELETE 语句对每一行修改都记录日志 所以 TRUNCATE TABLE 语句总