教学电子课件myschool数据库设计优化

1、数据库的实现第二章回顾与作业点评简述设计数据库的步骤举例说明实体间有哪些映射关系？请简要说明三大范式的含义简述创建数据库的步骤什么是T-SQL？其作用是什么？预习检查使用T-SQL语句创建数据库的语法是什么？写出创建数据库表的SQL语句写出删除数据库表的SQL语句创建数据库表约束的语句是什么？本章任务使用 T-SQL 语句：创建数据库MySchool创建学生信息表Student创建课程表Subject创建成绩表Result创建年级表Grade为表Student、 Subject和Result添加约束本章目标 掌握建库的SQL语句 掌握建表的SQL语句 掌握添加约束的SQL语句回顾数据库的基础知

2、识SQLServer数据库有哪几种文件组成？ 主数据文件：*.mdf次要数据文件：*.ndf日志文件：*.ldf创建SQLServer数据库时，需要指定哪些属性？ 文件存放位置，分配的初始空间，属于哪个文件组文件增长：可以按百分比或实际大小指定增长速度文件容量设置：可以指定文件增长的最大值或不受限创建数据库每个数据库至少有两个文件（一个主文件和一个日志文件）和一个文件组 为便于分配和管理，可以将数据库对象和文件一起分成文件组 日志文件不包括在文件组内。日志空间与数据空间分开管理CREATE DATABASE 数据库名 ON PRIMARY ( ，n ) LOG ON ( ，n )主文件组，可选

3、参数，默认创建数据库的语法创建数据库示例 1使用CREATE DATABASE语句创建数据库MySchool，要求：该数据库具有一个数据文件和一个日志文件文件保存在D盘已有的文件夹Project下数据文件的大小初始为5MB，最大值为100MB，文件增长率是15%日志文件的大小初始为2MB，增长量是1MBCREATE DATABASE MySchool ON PRIMARY -默认就属于PRIMARY主文件组，可省略( NAME= MySchool_data, -主数据文件的逻辑名 FILENAME=D:projectMySchool_data.mdf, -主数据文件的物理名 SIZE=5MB,

4、 -主数据文件初始大小 MAXSIZE=100MB, -主数据文件增长的最大值 FILEGROWTH=15% -主数据文件的增长率)LOG ON( NAME=MySchool_log, FILENAME=D:projectMySchool_log.ldf, SIZE=2MB, FILEGROWTH=1MB)GO 数据文件的具体描述日志文件的具体描述演示示例1：创建MySchool数据库创建数据库示例 2如何使用CREATE DATABASE语句创建数据库Employee，要求：该数据库具有二个数据文件和二个日志文件文件保存在D:Project文件下第一个主数据文件以employee1命名，初始

5、大小是10MB，增长量是10%第二个次要数据文件以employee2命名，初始大小是20MB，最大值是100MB，增长量是1MB第一个日志文件以employeelog1命名，初始为10MB，增长量是1MB第二个日志文件以employeelog2命名，初始大小和增长量与第一个日志文件相同，但最大值是50MBCREATE DATABASE Employee ON ( -主数据文件的具体描述 NAME = employee1, FILENAME = D:projectemployee1.mdf , SIZE = 10, FILEGROWTH = 10% ), ( -次要数据文件的具体描述 NAME

6、= employee2, FILENAME = D:projectemployee2.ndf , SIZE = 20, MAXSIZE = 100, FILEGROWTH = 1 ) LOG ON ( -日志文件1的具体描述 NAME = employeelog1, FILENAME = D:projectemployeelog1_Log.ldf , SIZE = 10, FILEGROWTH = 1 ), ( -日志文件2的具体描述- NAME = employeelog2, FILENAME = D:projectemployeelog2_Log.ldf , SIZE = 10, MAXS

7、IZE = 50, FILEGROWTH = 1 )GO 演示示例2：创建Employee数据库多个数据文件多个日志文件主数据文件次数据文件删除数据库2-1如果SQL Server中已存在数据库MySchool，运行下列语句，会出现什么问题？CREATE DATABASE MySchoolON ( )LOG ON( )GO演示示例3：查询已创建数据库记录USE master -设置当前数据库为master，以便访问sysdatabases表GOIF EXISTS (SELECT * FROM sysdatabases WHERE name =MySchool) DROP DATABASE My

8、SchoolCREATE DATABASE MySchoolON ( )LOG ON( )GO删除数据库2-2DROP DATABASE 数据库名 EXISTS() 语句：检测是否存在MyMySchool数据库。如果存在MySchool数据库，则删除 演示示例4：删除后再次创建MySchool数据库删除数据库的语法练习使用SQL语句创建数据库2-1训练要点：使用SQL语句创建数据库需求说明：创建学生管理信息系统的物理数据库，要求数据库取名为MySchool 物理文件保存在D:project下 数据文件的初始大小为10MB，允许自动增长，文件增长率是20%，不限制文件的最大值日志文件初始大小为3

9、MB，每次自动增量是1MB，文件最大值为20MB讲解需求说明指导使用SQL语句创建数据库2-2实现思路：检查判断数据库MySchool是否存在。若存在，则删除之调用DOS命令创建文件夹EXEC xp_cmdshell mkdir D:project使用SQL语句创建数据库MySchool编译、执行SQL语句，并保存SQL脚本USE masterGOIF EXISTS (SELECT * FROM sysdatabases WHERE name = MySchool) DROP DATABASE MySchoolGOEXEC xp_cmdshell mkdir D:project CREATE

10、DATABASE MySchool ON (NAME = MySchool_data, -主数据文件的逻辑名 FILENAME = D:projectMySchool_data.mdf , -主数据文件的物理名 SIZE = 10 MB, -主数据文件初始大小 FILEGROWTH = 20 % ) -主数据文件的增长率 LOG ON (NAME = MySchool_log, FILENAME = D:projectMySchool_log.ldf , SIZE = 3MB, MAXSIZE = 20MB, FILEGROWTH = 1MB)GO完成时间：25分钟xp_cmdshell：生成

11、 Windows 命令 shell ，并以字符串的形式传递执行 共性问题讲解常见调试问题及解决办法代码规范问题共性问题集中讲解回顾表的基础知识简述创建表的基本步骤确定表中有哪些列确定每列的数据类型给表添加各种约束创建各表之间的关系SQL Server 2008有哪些数据类型？整型数据：int，smallint 浮点数据：float，numeric decimal，numeric 字符数据：固定长度：char(6)可变长度：varchar(10|max) 固定长度：nchar(6)可变长度：nvarchar(10|max) 布尔数据（真/假） bit：1/ 0 货币数据 money日期数据 da

12、tetime字符数据类型数据类型Unicode编码存储特点字符数存储大小（字节）char(n)否固定长度18,000nvarchar(n)否可变长度08,000输入数据的实际长度 + 2varchar(max)否可变长度0231-1输入数据的实际长度 + 2nchar(n)是固定长度14,000 n * 2 字节nvarchar(n)是可变长度04,000输入数据的实际长度 * 2 + 2nvarchar(max)是可变长度0231-1输入数据的实际长度 * 2 + 2创建表建表的语法CREATE TABLE 表名 ( 列名1 数据类型 列的特征， 列名2 数据类型 列的特征， .)列的特征：

13、包括该列是否为空（NULL）、是否是标识列（自动编号）、是否有默认值、是否为主键等 创建表StudentUSE MySchool -将当前数据库设置为MySchoolGOCREATE TABLE Student -创建学生信息表( StudentNo int NOT NULL, -学号，非空（必填） LoginPwd nvarchar(20) NOT NULL, -密码，非空（必填） StudentName nvarchar(20) NOT NULL, -姓名，非空（必填） Sex bit NOT NULL, -性别，非空（必填） GradeId int NOT NULL, -年级号（必填）

14、Phone nvarchar(50) NOT NULL, -电话 BornDate datetime NOT NULL, -出生日期（必填） Address nvarchar(255), -地址 Email nvarchar(50), -邮件帐号 IDENTITYCard varchar(18) -身份证号) GO布尔值 TRUE 和 FALSE 可以转换为以下 bit 值：TRUE 转换为 1，FALSE 转换为 0 演示示例5：创建学生信息表Student创建Student表删除表2-1如果当前数据库中已存在Student表，再次创建时系统将提示出错 。如何解决呢？演示示例6：查询已创建表

15、记录删除表2-2删除表的语法DROP TABLE 表名USE MySchool -将当前数据库设置为MySchool，以便在MySchool数据库中建表GOIF EXISTS (SELECT * FROM sysobjects WHERE name=Student ) DROP TABLE StudentCREATE TABLE Student -创建学生信息表( ) GO练习使用SQL语句创建表Subject需求说明：检查判断课程表Subject是否已存在。若存在，删之使用SQL语句创建课程表Subject完成时间：10分钟序号列名称列说明类型长度属性备注1SubjectNo课程编号int非

16、空标识列，自增12SubjectName课程名称nvarchar50非空3ClassHour学时 int4GradeID年级编号intUSE MySchoolGOIF EXISTS (SELECT * FROM sysobjects WHERE name=Subject ) DROP TABLE SubjectGOCREATE TABLE Subject -创建课程表( SubjectNo int IDENTITY(1,1) NOT NULL, SubjectName nchar(50) NOT NULL, ClassHour int NOT NULL,GradeId int NOT NULL

17、) GO练习使用SQL语句创建表Result需求说明：检查判断成绩表Result是否已存在。若存在，删之使用SQL语句创建成绩表Result完成时间：10分钟序号列名称列说明类型长度属性备注1StudentNo学号int非空2SubjectNo课程编号int非空3ExamDate考试日期 datetime非空4StudentResult考试成绩int非空USE MySchoolGOIF EXISTS (SELECT * FROM sysobjects WHERE name= Result ) DROP TABLE ResultGOCREATE TABLE Result -创建成绩表( Stud

18、entNo int NOT NULL,SubjectNo int NOT NULL,StudentResult int NOT NULL,ExamDate datetime NOT NULL) GO练习使用SQL脚本创建表需求说明：使用现有的SQL脚本创建学生表Student使用现有的SQL脚本创建年级表Grade提示：选择菜单“文件”-“打开”指定的SQL脚本文件确认数据库是MySchool编译、执行脚本中SQL代码检查表是否创建成功完成时间：10分钟共性问题讲解常见调试问题及解决办法代码规范问题共性问题集中讲解小结简述数据库、数据文件与日志文件的关系比较char与varchar、nchar

19、与nvarchar的异同点已创建的数据库、表可以通过SQL Server的哪些系统表查询到？回顾数据完整性数据完整性有哪几种？各自作用是什么？实体完整性能够唯一标识表中的每一条记录实现方式：主键、唯一键、IDENTITY属性域完整性表中特定列数据的有效性，确保不会输入无效的值实现方式：类型、缺省值、约束、空值引用完整性维护表间数据的有效性、完整性实现方式：建立外键联系另一表的主键自定义完整性根据业务处理流程定义的特定业务规则实现方式：存储过程、触发器、规则回顾SQL Server的约束使用约束的目的是什么？确保表中数据完整性常用的约束类型有哪些？主键约束（Primary Key Constra

20、int）：要求主键列数据唯一，并且不允许为空唯一约束（Unique Constraint）：要求该列数据唯一，允许为空，但只能出现一个空值检查约束（Check Constraint）：某列取值范围限制、格式限制等默认约束（Default Constraint）：某列的默认值外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的哪列主键约束与唯一约束的区别：1.主键约束所在的列不允许有空值，唯一约束所在的列允许空值2.每个表中可以有一个主键，多个唯一键添加约束 添加约束的语法ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具

21、体的约束说明约束名的取名规则推荐采用：约束类型_约束列主键（Primary Key）约束：如 PK_stuNo唯一（Unique Key）约束：如 UQ_stuID默认（Default Key）约束：如 DF_stuAddress检查（Check Key）约束：如 CK_stuBornDate外键（Foreign Key）约束：如 FK_stuNo 添加约束示例2-1如何在表Student中添加下面的约束？主键约束：学号唯一约束：身份证号 默认约束：Address列的默认值是“地址不详”检查约束：出生日期的值为1980年1月1日后外键约束：设置GradeID列为外键，建立Grade表与Stud

22、ent表的引用关系ALTER TABLE Student ADD CONSTRAINT PK_stuNo PRIMARY KEY (StudentNo)ALTER TABLE Student ADD CONSTRAINT UQ_stuID UNIQUE (IDENTITYCard)ALTER TABLE Student ADD CONSTRAINT DF_stuAddress DEFAULT (地址不详) FOR stuAddressALTER TABLE Student ADD CONSTRAINT CK_stuBornDate CHECK(BornDate 1980-01-01)ALTER

23、 TABLE Grade ADD CONSTRAINT PK_GradeID PRIMARY KEY(GradeID)ALTER TABLE Student ADD CONSTRAINT FK_Grade FOREIGN KEY(GradeID) REFERENCES Grade(GradeID)GO添加主键约束（StudentNo作为主键）添加唯一约束（因为每人的身份证号全国唯一）添加默认约束（如果地址不填，默认为“地址不详”）添加检查check约束，要求出生日期在1980-1-1之后添加外键约束（主表Grade和从表Student建立关系，关联列为GradeID）添加主键约束（GradeI

24、D作为主键）演示示例7：给Student表添加约束添加约束示例2-2操作演示：查看约束结果 删除约束如果错误地添加了约束，我们还可以删除约束 删除约束的语法ALTER TABLE 表名 DROP CONSTRAINT 约束名 例如：删除Student表中地址默认约束ALTER TABLE Student DROP CONSTRAINT DF_stuAddress演示示例8：删除Student表约束指导添加Subject表约束2-1训练要点：使用SQL语句添加约束需求说明：使用T-SQL脚本向Subject表中插入记录，随后，使用ALTER TABLE语句向课程表Subject中添加相关约束 主

25、键约束：课程编号非空约束：课程名称检查约束：学时必须大于等于0外键约束：主表Grade和从表Subject通过GradeID建立引用关系讲解需求说明指导添加Subject表约束2-2实现思路：执行T-SQL脚本向Subject表中插入数据使用T-SQL语句添加表的各种约束通过插入/修改违反约束的数据进行验证难点指导：使用“WITH NoCHECK”解决表中已有数据与添加的约束发生冲突完成时间：25分钟USE MySchoolGOALTER TABLE Subject -主键约束（课程编号） ADD CONSTRAINT PK_Subject PRIMARY KEY (SubjectNo)ALT

26、ER TABLE Subject -非空约束（课程名称） ADD CONSTRAINT CK_SubjectName CHECK (SubjectName is not null)ALTER TABLE Subject WITH NOCHECK -检查约束（学时必须大于等于0） ADD CONSTRAINT CK_ClassHour CHECK (ClassHour=0)ALTER TABLE Subject -外键约束（主表Grade和从表Subject建立引用关系） ADD CONSTRAINT FK_GradeId FOREIGN KEY (GradeId) REFERENCES Gra

27、de (GradeId)GOWITH NOCHECK对表中已有数据不做约束检查，因此不能保证表中数据的正确性建议：创建表结构后应立即添加约束，不要马上插入数据，以避免插入的数据不符合约束要求，保证表中数据满足约束限制练习添加Result表约束需求说明：使用SQL语句创建成绩表Result约束包括：主键约束：学号、课程编号、日期三列构成复合主键默认约束：日期为系统当前日期检查约束：考试成绩不得大于100分，或小于0分外键约束：主表Student和从表Result通过StudentNo列建立引用关系主表Subject和从表Result通过SubjectNo列建立引用关系完成时间：20分钟USE M

28、ySchoolGOALTER TABLE Result -主键约束（学号、科目号、日期） ADD CONSTRAINT PK_Result PRIMARY KEY (StudentNo, SubjectNo, ExamDate)ALTER TABLE Result -默认约束（日期为系统当前日期） ADD CONSTRAINT CK_ExamDate DEFAULT (getdate() FOR ExamDateALTER TABLE Result -检查约束（分数不能大于100，小于0） ADD CONSTRAINT CK_StudentResult CHECK (StudentResult BETWEEN 0 AND 100)ALTER TABLE Result -外键约束（主表Student和从表Result建立关系） ADD CONSTRAINT FK_StudentNo FOREIGN KEY (StudentNo) REFERENCES