数据库系统原理及应用教程 第4版 教学课件 ppt 作者 刘瑞新 第6章-1

教学目的： 熟练掌握使用SQL Server 2008管理平台和T-SQL创建数据库、表和约束的方法，学会利用管理平台查看或修改数据库属性和表结构。学会用T-SQL数据查询操作 。,第6章 数据库的建立与管理,6.1 数据库的定义和维护 6.2 基本表的定义和维护 6.3 数据维护操作 6.4 数据查询操作,1. 使用SQL Server管理平台创建数据库,建立学生课程库，库中表结构为： 学生（学号，姓名，年龄，性别，所在系）； 课程（课程号，课程名，先行课）； 选课（学号，课程号，成绩）. 具体任务要求： （1）确定数据库文件及参数，建立数据库。 （2）为每个属性选择数据类型和长度；建表。 （3）确定主码和约束，并定义。,任务一：,1.确定数据库属性(准备),6.1 数据库的定义和维护,完成任务1（建库），可以通过2种方法，,确定数据库的数据文件和日志文件的逻辑文件名、物理文件、初始大小和文件组等参数。,实例：,2.使用SQL Server管理平台创建数据库 （建库方法1）,（1）进入SQL Server管理平台,（2）右击数据库文件夹，从弹出的快捷菜单中选择新建数据库选项，出现对话框。,（3）改变增长和路径属性,（4）修改其他属性和文件组属性,（5）查看数据库属性和删除数据库,（1）数据库定义语言 CREATE DATABASE数据库名 ON PRIMARY(NAME =逻辑数据文件名， FILENAME= '操作数据文件路径和文件名' ，SIZE=文件长度 ，MAXSIZE=最大长度 ，FILEROWTH=文件增长率 )，n LOG ON (NAME=逻辑日志文件名， FILENAME= '操作日志文件路径和文件名' ，SIZE=文件长度 )，n FOR RESTORE 句中：PRIMARY指明主文件；SIZE说明文件的大小，默认为3MB；FILEROWTH说明文件增长率，默认为10%。FOR RESTORE子句说明重建数据库（用于数据恢复）。,3.使用T-SQL语言定义数据库（建库方法2）,实例：,CREATE DATABASE 学生课程 ON PRIMARY (NAME =学生课程_ data, FILENAME=D:SQLlx学生课程_data.mdf', SIZE = 15MB, MAXSIZE = 30MB, FILEGROWTH=20%) LOG ON (NAME =学生课程_ log, FILENAME = D:SQLlx 学生课程_log.ldf', SIZE = 3MB, MAXSIZE = 10MB, FILEGROWTH = 1MB),(2)调出查询窗口，输入T-SQL语句，击执行键,6.2 基本表的定义和维护 6.2.1 确定数据表结构及约束,1.系统数据类型（数字型）,1.系统数据类型（字符和其他类型）,2.表的完整性约束,主键约束：即主键各列不能为空且主键作为行的惟一标识 外键约束：确定外码、相关的主码表和主码 默认值、规则、值域等,3.设计表时需要确定的内容,表中需要的列以及每一列的类型（必要时还要有长度） 列是否可以为空 是否需要在列上使用约束、默认值和规则 需要使用什么样的索引 哪些列作为主键,实例：确定数据表结构及约束,6.2.2使用SQL Server管理平台创建基本表,1.选择新建表，输入列名,2.输入数据类型，改变数据长度,3.关闭后输入表名,4.修改表结构或查看表结构,查看表结构,6.2.3 用SQL 定义基本表,CREATE TABLE 库名 表名( 列名数据类型|AS表达式 字段约束 ， 记录约束) ，,1. 定义表,(1) 记录约束： CONSTRAINT 1) PRIMARY KEY CLUSTERED| NON CLUSTERED(列名组) 2) FOREIGN KEY() REFERENCES () 3) CHECK(条件表达式)：定义记录应满足的条件。 4) UNIQUE(列组)：定义不允许重复值的字段组。,(2) 字段约束 1) NOT NULL|NULL：不允许或允许字段值为空。 2) PRIMARY KEY CLUSTERED|NON CLUSTERED 3) REFERENCE ()：定义参照完整条件。 4) DEFAULT缺省值：定义字段的缺省值。 5) CHECK(条件)：定义字段应满足的条件表达式。 6) IDENTITY(，)：定义字段初始值和步长值。,USE 学生课程 GO CREATE TABLE 学生(学号 CHAR(5) NOT NULL UNIQUE， 姓名 VARCHAR(8) NOT NULL， 年龄 SMALLINT DEFAULT 20 ， 性别 CHAR(2) CHECK ( 性别 IN (男，女) ) ， 所在系 VARCHAR (20) ) GO CREATE TABLE 课程(课程号 CHAR(5) PRIMARY KEY CLUSTERED， 课程名 VARCHAR (20)， 先行课 CHAR(5) GO,【例】,建立基本表： 学生(学号，姓名，年龄，性别，所在系)； 课程(课程号，课程名，先行课)； 选课(学号，课程号，成绩).,CREATE TABLE 选课(学号 CHAR(5)， 课程号 CHAR(5)， 成绩 SMALLINT， CONSTRAINT C1 CHECK(成绩 BETWEEN 0 AND 100)， CONSTRAINT C2 PRIMARY KEY(学号，课程号)， CONSTRAINT C3 FOREIGN KEY(学号) REFERENCES 学生(学号)， CONSTRAINT C4 FOREIGN KEY(课程号) REFERENCES 课程(课程号) GO,【例】,建立基本表： 学生(学号，姓名，年龄，性别，所在系)； 课程(课程号，课程名，先行课)； 选课(学号，课程号，成绩).,2. 基本表的维护,(1)修改基本表 1) 修改字段的定义。 ALTER TABLE表名 ALTER COLUMN列名新类型 NULL|NOT NULL约束定义 2) 增加字段和表约束规则。 ALTER TABLE表名ADD列定义|表约束定义 3) 删除字段或约束规则。 ALTER TABLE表名DROPCONSTRAINT约束名|COLUMN列名 4) 使约束有效或无效。 ALTER TABLE表名CHECK|NOCHECK CONSTRAINT ALL|约束名组 (2) 删除基本表: DROP TABLE表名,3.调出查询窗口，输入T-SQL语句，击执行键,6.2.4 数据约束的定义,索引：提高数据库的查找速度，保证列的惟一性 聚集索引（Clustered）：根据键的值对行进行排序，所以每个表只能有一个聚集索引 非聚集索引：不根据键值排序，索引数据结构与数据行是分开的。 惟一索引：保证索引中不含有相同的键值，聚集索引和非聚集索引都可以是惟一索引。 全文（full-text）索引：上Microsoft全文引擎（full-text engine），支持快速的字符串中单词的查找。 XML索引：支持XML数据列中的XML二进制对象。,创建原则及注意事项,适合建立索引： 经常被查询搜索的列，如经常在where子句中出现的列。 在ORDER BY子句使用的列。 外键或主键列。 值惟一的列。 不适合建立索引。 在查询中很少被引用的列。 包含太多重复值的列。 数据类型为bit、text、image 等的列不能建立索引。,(1) 打开表,数据约束定义步骤,(2)设置主键和索引,(3) 建立检查约束,6.3 数据维护操作 6.3.1 T-SQL数据更新语句,1 数据插入语句 (1) 使用常量插入单个元组 . 格式为： INSERT INTO表名(属性列1，属性列2) VALUES (常量1，常量2),【例】插入一条选课记录 (学号：'98011'，课程号：'C10'，成绩不详)。 INSERT INTO 选课 (学号，课程号) VALUES ('98011'，'C10'),(2) 在表中插入子查询的结果集,INSERT INTO表名(属性列1，属性列2) 子查询,【例】求每个系学生的平均年龄，把结果存入数据库中。 CREATE TABLE 系平均年龄 (系名称 VarCHAR(20)， 平均年龄 SMALLINT) GO INSERT INTO 系平均年龄 SELECT 所在系，AVG (ALL 年龄) FROM 学生 GROUP BY 所在系 GO,2 数据修改语句,UPDATE表名 SET列名=表达式，列名=表达式，n WHERE条件,【例】将学生表中全部学生的年龄加上2岁。 UPDATE 学生 SET 年龄=年龄+2 【例】将选课表中的数据库课程的成绩乘以1.2。 UPDATE 选课 SET 成绩= 成绩*1.2 WHERE 课程号= (SELECT 课程号 FROM 课程 WHERE 课程名= '数据库' ),3 数据删除语句,【例】删除艺术系的学生记录及选课记录。 DELETE FROM 选课 WHERE 学号 IN (SELECT 学号 FROM 学生 WHERE 所在系=艺术系) GO DELETE FROM 学生 WHERE 所在系='艺术系',DELETE FROM表名 WHERE条件,6.3.2 SQL Server 数据更新方法,6.3.2 SQL Server 数据更新方法,