数据库原理与应用(数据定义).doc

数据库原理与应用(数据定义) 作者： 日期：13 个人收集整理 勿做商业用途数据库原理及应用电子实验报告题 目：SQL语言(1)数据定义日期2012.10。9姓 名党存寿学号20102111225Q Q1045874070实验环境： Windows XP，Sql Server2005实验目的：1 熟悉Sql Server2005环境2 掌握基本表的定义、删除和修改3 掌握索引的建立和删除实验内容：1. Sql Server2005的启动 Sql Server2005的启动方法： 单击“开始”按钮“所用程序” “Microsoft SOL Server 2005” “SQL Server Management Studio”。之后将会出现如图1所示的“连接到服务器”的窗口。 图1 “连接到服务器”窗口注意：服务器类型选“数据库引擎”身份验证选“Windows 身份验证”服务器名称的选择：点击服务器名称的下拉菜单，然后点击“浏览更多”,之后将会出现如图2所示的“查找服务器”窗口。然后双击数据库引擎，在数据库引擎下就能找到服务器的名称，然后选择该名称并单击“确定”按钮退回到“连接到服务器”窗口.然后单击“连接按钮进入SQL环境. 图2 “查找服务器”窗口2。 定义基本表的语句格式1）create语句SQL 语言使用CREATE TABLE语句定义基本表，其基本格式如下：CREATE TABLE 表名 （列名 列级完整性约束条件 ，列名 数据类型 列级完整性约束条件 ，： 所要定义的基本表的名字 列名： 组成该表的各个属性（列） : 涉及相应属性列的完整性约束条件 表级完整性约束条件: 涉及一个或多个属性列的完整性约束条件 2）drop语句当某个基本表不再需要时，可以使用DROP TABLE语句删除它。其一般格式为：DROP TABLE 表名 RESTRICT | CASCADE；注意:基本表删除后：数据、表上的索引都删除 表上的视图往往仍然保留，但无法引用 删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述。3）alter语句SQL语言用ALTER TABLE语句修改基本表，其一般格式为：ALTER TABLE 表名 ADD 新列名 数据类型 完整性约束 DROP 完整性约束名 MODIFY 列名 数据类型 ；注：表名：要修改的基本表 ADD子句：增加新列和新的完整性约束条件 DROP子句：删除指定的完整性约束条件 MODIFY子句：用于修改列名和数据类型3.基本表的定义、删除和修改1）基本表的定义 【例1】 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空,值是唯一的,并且姓名取值也唯一（如图3-1所示）。 键入如下命令:CREATE TABLE Student（Sno CHAR（9） PRIMARY KEY, Sname CHAR（20) UNIQUE, Ssex CHAR(2）， Sage SMALLINT， Sdept CHAR（20）)；附：常用完整性约束主码约束:PRIMARY KEY唯一性约束:UNIQUE非空值约束：NOT NULL参照完整性约束 图31 学生表【例2】建立一个“课程”表Course，它由课程号Cno、课程名Cname，先行课Cpno,学分Ccredit组成，其中（Cno）为主码(如图3-2所示)。键入如下命令: CREATE TABLE Course（Cno CHAR(4) PRIMARY KEY,Cname CHAR（40），Cpno CHAR（4)，Ccredit SMALLINT，FOREIGN KEY （Cpno） REFERENCES Course(Cno）)；因为先行课Cpno是参照课程号Cno的，所以定义约束： FOREIGN KEY （Cpno） REFERENCES Course(Cno)【例3】建立一个“学生选课表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中（Sno， Cno）为主码（如图33所示）。键入如下命令：CREATE TABLE SC（Sno CHAR(9),Cno CHAR（4)，Grade SMALLINT,PRIMARY KEY（Sno，Cno)，FOREIGN KEY(Sno) REFERENCES Student(Sno）,FOREIGN KEY(Cno) REFERENCES Course（Cno）)； 学生选课SC表中的学号Sno和课程号Cno分别参照学生表Student和课程表Course,所以定义约束：l FOREIGN KEY(Sno） REFERENCES Student（Sno）,l FOREIGN KEY(Cno） REFERENCES Course（Cno） 图3-2 课程表course 图33 学生选课表2）基本表的删除【例4】 删除Student表 DROP TABLEStudent ；【例5】删除Course表 DROP TABLE Course;【例6】删除学生选课SC表 DROP TABLE SC；3）基本表的修改【例7】 向Student表增加“入学时间”列，其数据类型为日期时间型。 ALTER TABLE Student ADD Scome DATETIME； 注：不论基本表中原来是否已有数据，新增加的列一律为空值（如图4所示）。 图4 向Student表增加“入学时间(Scome)”列【例8】将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数,运行结果如图5所示。ALTER TABLE Course ALTER COLUMN Sage INT；【例9】 增加课程名称必须取唯一值的约束条件, 运行结果如图6所示. ALTER TABLE Course ADD UNIQUE（Cname)；【例10】直接删除属性列 ALTER TABLE Student Drop Scome； 图5 将年龄的数据类型由字符型改为整数 图 6 增加课程名称必须取唯一值的约束条件4 定义索引的语句1） create语句 语句格式： CREATE UNIQUE CLUSTER INDEX ON (指定索引值的排列次序，升序:ASC，降序：DESC。缺省值：ASC UNIQUE表明此索引的每一个索引值只对应唯一的数据记录 CLUSTER表示要建立的索引是聚簇索引2） Drop语句 语句格式: DROP INDEX ； 删除索引时，系统会从数据字典中删去有关该索引的描述。5 索引的建立和删除1)索引的建立唯一索引和聚簇索引的区别l 唯一值索引：对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束l 聚簇索引:建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致【例11】 为学生课程数据库中的Student,Course，SC三个表（如图7所示）建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。 Student学 号Sno姓 名Sname性 别Ssex年 龄Sage所 在 系Sdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19IS (a) SC学 号Sno课 程 号Cno成绩Grade9500119295001285950013889500295002239080 （b） Course课 程 号Cno课 程 名Cname先 行 课Cpno学 分Ccredit1数据库542数学23信息系统144567操作系统数据结构数据处理PASCAL语言6763424 （c） 图7 学生课程数据库CREATE UNIQUE INDEX Stusno ON Student(Sno)；CREATE UNIQUE INDEX Coucno ON Course（Cno）;CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC); 命令及运行结果如图8所示： 图8 建立Student、Course、SC表的索引l 【例12】在Student表的Sname（姓名）列上建立一个聚簇索引。CREATE CLUSTER INDEX Stusname ON Student(Sname）；在Student表的Sname（姓名）列上建立一个聚簇索引,而且Student表中的记录将按照Sname值的升序存放 在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询,可以提高查询效率聚簇索引的适用范围 很少对基表进行增删操作 很少对其中的变长列进行修改操作 2）索引的删除【例13】 删除Student表的Stusname索引。 DROP INDEX Stusname； 【例14】删除Student、Course、SC表的Stusno、Coucno、SCno索引.DROP INDEX Stusno ON Student;DROP INDEX Coucno ON Course； DROP INDEX SCno ON SC;完成情况: 1 完成了对学生表Student。课程表Course、学生选课SC表的创建、删除、修改。 2 完成了对学生表Student。课程表Course、学生选课SC表索引的创建和删除。出现的问题：（小四宋体)1 在完成表的索引创建任务时，遇到了消息159,级别15，状态1，第1 行必须为DROP INDEX 语句指定表和索引名称.消息159，级别15,状态1，第2 行必须为DROP INDEX 语句指定表和索引名称。消息159，级别15，状态1,第3 行必须为DROP INDEX 语句指定表和索引名称。问题,解决思路是给每一条删除语句加上了表名。及将语句DROP INDEX Stusno ；DROP INDEX Coucno ; DROP INDEX SCno ;改为:DROP INDEX Stusno ON Student;DROP INDEX Coucno ON Course； DROP INDEX SCno ON SC；