第08章 用T-SQL语句建库及建表.pptx
课程主讲人：第08章 用T-SQL语句建库及建表数据库设计与应用主讲：张成叔微信（QQ）：7153265 微信公众号：张成叔第 8 章 用T-SQL语句建库及建表第1章 创建和管理数据库 用T-SQL语句建库2 2 用T-SQL语句建表 用T-SQL语句创建约束1 13 3目 录 数据库部署时通常编写比较通用的SQL语句，包括建库、建表、添加约束和插入测试数据等。编写完成后，存入*.sql文件中，最后复制到客户的计算机中，并执行*.sql文件中的SQL语句，从而实现后台数据库的移植。用T-SQL语句创建数据库n用T-SQL语句创建数据库 用T-SQL创建和删除数据库CREATE DATABASE 数据库名 ON PRIMARY (,n) LOG ON (,n) 语法语法n用T-SQL语句创建数据库 u数据文件的具体参数如下用T-SQL创建和删除数据库 NAME=逻辑文件名， FILENAME=物理文件名 ，SIZE=大小 ，MAXSIZE=最大容量|UNLIMITED ，FILEGROWTH=增长量），n说明说明“”表示需要用具体参数代替；表示需要用具体参数代替；“”表示可选项表示可选项n需求说明 u在D盘创建目录project。u数据库的主数据文件逻辑名称为SchoolDB_data，物理文件名称为SchoolDB_data.mdf，初始大小为5MB，最大值为100MB，增长速度为15%。数据库的日志文件逻辑名称为SchoolDB_log，物理文件名称为SchoolDB_log.ldf，初始大小为2MB，增长速度为1MB。u数据库存放在D：project目录下。 【演示1】创建学生数据库n参考代码： 【演示1】创建学生数据库CREATE DATABASE SchoolDB ON PRIMARY( -数据文件的具体描述 NAME=SchoolDB_data, FILENAME=D:projectSchoolDB_data.mdf, SIZE=5MB, MAXSIZE=100MB, FILEGROWTH=15% ) LOG ON ( -日志文件的具体描述日志文件的具体描述 NAME=SchoolDB_log, FILENAME=D:projectSchoolDB_log.ldf, SIZE=2MB, FILEGROWTH=1MB ) GO 成功创建SchoolDB数据库后，还可以在数据库上右击，在弹出的快捷菜单中选则“属性”选项，查看数据库的属性，如下图所示。【演示1】创建学生数据库n技能要点u使用SQL语句创建数据库。n需求说明u数据库取名SchoolDB，文件保存在D:progect下。u数据文件的初始大小为10MB，允许自动增长，文件增长率是20%，文件大小不受限制。u日志文件初始大小为3MB，每次自动增长量是1MB，文件最大为20MB。实训1-用T-SQL创建数据库n关键点分析u检查判断数据库SchoolDB是否存在。若存在则删除。u事先创建文件夹。实训1-用T-SQL创建数据库n用T-SQL语句删除数据库用T-SQL创建和删除数据库DROP DATABASE 数据库名语法语法DROP DATABASE SchoolDB示例示例 master系统数据库的sysdatabases表中存放了用户创建的数据库。 完整的T-SQL语句如下。USE master -设置当前数据库为masterGOIF EXISTS(SELECT * FROM sysdatabases WHERE name=SchoolDB)DROP DATABASE SchoolDBGOCREATE DATABASE SchoolDBON( )LOG ON( )GO用T-SQL创建和删除数据库n技能要点u使用T-SQL语句删除数据库。n需求说明u使用T-SQL语句删除数据库。n关键点分析u删除数据库之前应该先检测该数据库是否存在实训2-用T-SQL删除数据库一起努力 共同提高第1章 创建和管理数据库 用T-SQL语句建库1 1 用T-SQL语句建表 用T-SQL语句创建约束2 23 3目 录n用T-SQL语句创建表 我们先简要回顾一下SQL Server中表的基础知识。 创建数据库表的步骤如下。u确定表中有哪些列。u确定每列的数据类型。u给表添加各种约束。u创建各表之间的关系。 用T-SQL创建和删除数据表n使用T-SQL创建表 其中，“列的特征”包括该列是否为空（NULL）、是否为标识列（自动编号）、是否有默认值及是否为主键等。CREATE TABLE 表名（ 列1 数据类型 列的特征 列2 数据类型 列的特征 ）语法语法用T-SQL创建和删除数据表n需求说明u根据业务需求，使用T-SQL语句创建学生信息表Student。u在创建Student表之前，应该先检测该表是否存在。如果Student表已经存在，则给出如下图提示。【演示2】创建学生信息表n示例代码 【演示2】创建学生信息表USE SchoolDBGOIF EXISTS(SELECT * FROM sysobjects WHERE name=Student) DROP TABLE StudentCREATE TABLE Student( StudentNo nvarchar(50) NOT NULL StudentName nvarchar(20) NOT NULL, Sex char(2) NOT NULL, GradeId int NOT NULL, Phone nvarchar(50) NULL, Address nvarchar(255) NULL, BornDate datetime NOT NULL, Email nvarchar(50) NULL, IdentityCard nvarchar(18) NULL)GOn技能要点u 使用T-SQL语句创建表。 n需求说明u 使用T-SQL语句创建科目表Subject。Subject表的结构如表1所示。 实训3-用T-SQL创建数据表序号序号列名称列名称列说明列说明数据类型数据类型长度长度属性属性备注备注1SubjectId课程编号int非空标识列，自增12SubjectName课程名称nvarchar203ClassHour学时int4GradeId所属学期int表1 科目表的数据结构n使用T-SQL创建成绩表Result。Result表的结构如表2所示。表2 成绩表的数据结构序号序号列名称列名称列说明列说明数据类型数据类型长度长度属性属性备注备注1StudentNo学号nvarchar50非空2SubjectId所考课程编号int非空3StudentResult考试成绩int 4ExamDate考试日期datetime非空实训3-用T-SQL创建数据表n使用T-SQL创建学生信息表Student。Student表的结构如表3所示。表3 学生信息表的数据结构序号序号列名称列名称列说明列说明数据类型数据类型长度长度属性属性备注备注1StudentNo学号nvarchar50非空2StudentName姓名nvarchar50非空3Sex性别char2非空4BornDate出生日期datetime实训3-用T-SQL创建数据表n关键点分析u 在创建各个表之前应先检查、判断各个表是否已经存在。若存在，则删除。u 如果某个类为标识列且自增1，则在这列（如课程表SubjectId列）的写法如下： SubjectId int IDENTITY(1,1) NOT NULL 其中IDENTITY(1,1)表示为标志列，第一个参数1表示编号从1开始，第二个参数表示每次自增1。实训3-用T-SQL创建数据表n使用T-SQL删除表DROP TABLE 表名语法语法用T-SQL创建和删除数据表DROP TABLE Student示例示例说明说明 数据库中的表的清单存放在该数据库的系统表数据库中的表的清单存放在该数据库的系统表sysobectssysobects中，创建表时先到中，创建表时先到系统表中查询是否存在，如存在，则删除。系统表中查询是否存在，如存在，则删除。n技能要点u 使用T-SQL语句删除表。 n需求说明u 将数据库中的学生信息表Student、学期表Grade、科目表Subject和成绩表Result删除。 n关键点分析u 注意删除各表的顺序，先删除从表，再删除主表。u 如果被删除的表被其他表所引用，例如先删除Grade时，则给出如下错误提示。实训4-用T-SQL删除数据表一起努力 共同提高第1章 创建和管理数据库 用T-SQL语句建库1 1 用T-SQL语句建表 用T-SQL语句创建约束2 23 3目 录n常用的约束类型u主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许为空，如学号能唯一确定一名学生。u非空约束（Not Null）：要求不能存在空值，如学生的姓名不能为空。u唯一约束（Unique Constraint）：要求该列的值必须唯一，允许为空，但只能出现一个空值。u检查约束（Check Constraint）：某列的取值范围限制、格式限制等，如有关年龄的约束。用T-SQL创建和删除表约束n常用的约束类型（续）u默认约束（Default Constraint）：某列的默认值，如男性学生较多，性别默认为“男”。u外键约束（Foreign Key Constraint）：用于在两表之间建立关系，需要指定引用主表的哪一列。 在发生插入数据或更新表中数据时，数据库将自动检查更新的列值是否符合约束限制。如果不符合约束要求，则更新操作失败。用T-SQL创建和删除表约束n使用T-SQL语句添加约束 上述的语法表示修改某个表，并在其中添加某个约束。其中，约束名的命名规则推荐采用“约束类型_约束列”的形式。用T-SQL创建和删除表约束ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束说明语法语法n需求说明u 为Grade表添加主键约束。n示例代码【演示3】为Grade表添加约束-添加主键约束（将GradeId作为主键）ALTER TABLE Grade ADD CONSTRAINT PK_GradeId PRIMARY KEY (GradeId)GOn需求说明u主键约束：学号。u唯一约束：身份证号。u默认约束：地址列的默认值是“安徽省合肥市”。u检查约束：性别的值只能是“男”或“女”，电子邮件中必须包含“”字符。u外键约束：设置GradeID列为主键，建立Grade表与Student表的引用关系。【演示4】为Student添加约束n示例代码-添加主键约束（将StudentNo作为主键）ALTER TABLE Student ADD CONSTRAINT PK_StudentNo PRIMARY KEY (StudentNo)-唯一约束（身份证号唯一）ALTER TABLE Student ADD CONSTRAINT UQ_IdentityCard UNIQUE (IdentityCard)-默认约束（地址不详）ALTER TABLE Student ADD CONSTRAINT DF_Address DEFAULT (地址不详地址不详) FOR Address-检查约束（出生日期是自年月日以后）ALTER TABLE Student ADD CONSTRAINT CK_BornDate CHECK(BornDate=1980-1-1)-添加外键约束（主表Student和从表Grade建立关系，关联列为GradeId）ALTER TABLE Student ADD CONSTRAINT FK_Grade FOREIGN KEY(GradeId) REFERENCES Grade(GradeId)GO【演示4】为Student添加约束n技能要点u使用T-SQL语句添加约束。n需求说明uGrade表添加约束如下。 主键约束：学期编号。uStudent表添加约束如下。 主键约束：学号。 唯一约束：身份证号。 默认约束：如果不填写地址，则默认值是“安徽省合肥市”。 检查约束：性别的值只能是“男”或“女”，电子邮件中必须包含“”字符。 外键约束：主表Grade和从表Student通过GradeID列建立引用关系。实训5-为数据表添加约束uSubject表添加约束如下。 主键约束：科目编号。 非空约束：科目名称。 检查约束：学时必须大于等于0。 外键约束：主表Grade和从表Subject通过GradeID列建立引用关系。 实训5-为数据表添加约束uResult表添加约束如下。 主键约束：学号、科目编号和日期构成组合主键。 默认约束：日期为系统当前日期。 检查约束：考试成绩不得大于100分，或小于0分。 外键约束：第一种，主表Student和从表Result通过StudentNo列建立引用关系。第二种，主表Subject和从表Result通过SubjectId列建立引用关系。实训5-为数据表添加约束n关键点分析u 使用ADD CONSTRAINT语句创建约束。u 对于需求4，ALTER TABLE语句中在“PRIMARY KEY”后面以逗号作为分隔符罗列出构成组合主键的多个列的列名。n补充说明 添加约束之前，确认各表为空，没有记录。如果表中已经存在记录，则建议删除或将表中记录导出，待添加约束之后，重新导入这些数据。实训5-为数据表添加约束n用T-SQL语句删除约束创建和删除数据表的约束ALTER TABLE 表名 DROP CONSTRAINT 约束名语法语法ALTER TABLE Student DROP CONSTRAINT DF_Address示例示例n技能要点u 使用T-SQL删除约束。 n需求说明u 将Grade、Subject、Result和Student表中的约束删除。实训6-删除数据表的约束n关键点分析u 在删除约束的时候，要注意删除的顺序。u 先删除从表的约束，再删除主表的约束，即如果删除的约束被其他约束所引用，则会出错。u 例如，先删除Grade表中的外键约束PK_Grade时，会给出如下图所示的提示信息。实训6-删除数据表的约束总结配置MySQL服务器连接MySQL数据库注意操作MySQLMySQL存储引擎MySQL文件类型和系统帮助三大范式内容端口端口设置设置默认默认字符集字符集设置设置将将binbin目录写入环境目录写入环境变量变量rootroot账号密码设置账号密码设置初识MySQL创建数据库、数据表创建数据库、数据表查看数据库、数据表查看数据库、数据表删除数据库、数据表删除数据库、数据表启动服务启动服务写入环境变量写入环境变量mysql hmysql h服务器名服务器名 uu用户名用户名 pp密码密码InnoDB:支持事务、外键，更新、删除多的场合支持事务、外键，更新、删除多的场合MyISAM:占用空间小，查询多的场合占用空间小，查询多的场合43/47n数据库的物理实现一般步骤如下。u创建数据库。u创建表。u添加各种约束。u创建数据库的登录账户并授权。n 创建数据库或表时一般需要预先检测是否存在该对象，SQL Server中的数据库可以从master系统数据库的sysdatabases表中查询，而一个数据库中的表可以从该数据库的系统表sysobjects中查询。总结n使用CREATE DATABASE语句可以创建数据库u使用DROP DATABASE语句可以删除数据库。n使用CREATE TABLE语句可以创建数据库的表结构u使用DROP TABLE语句可以删除表结构。总结n使用ALTER TABLE语句可以创建约束：u主键约束（Primary Key Constraint）。u唯一约束（Unique Constraint）。u检查约束（Check Constraint）。u默认约束（Default Constraint）。u外键约束（Foreign Key Constraint）。ALTER TABLE DROP语句删除约束总结一起努力 共同提高
