数据库原理实验指导书

i 《数据库原理与技术》 实验指导 目 录 实验一： 数据库管理系统软件的使用 ..................................................................................................1 实验二: 数据库的建立和维护 ...............................................................................................................8 实验三：数据库的嵌套查询实验 ..........................................................................................................14 实验四：数据库的分组查询和统计查询 ..............................................................................................16 实验五：数据库视图的定义及使用 ......................................................................................................18 实验六：数据完整性实验 ......................................................................................................................20 实验七*：数据库备份和恢复实验 .........................................................................................................27 实验八：数据库课程设计 ......................................................................................................................33 说明： 1. 带*号的为选做； 2. 实验环境为 Sql Server。 但除实验七有较大差别外，其余的所有实验在 Access、 Oracle、MySql甚至 VFP中都类似。除在 Sql Server中进行实验外，建议也在 Access中操作一 下。 1 实验一： 数据库管理系统软件的使用 一、实验目的 (1)认识几种常见的数据库管理系统，熟悉它们的使用界面; (2)熟练掌握建立数据库和表，向数据库输入数据、修改数据和删除数据的操作。 二、实验内容 分别在 Access和 SQL SERVER2000中建立数据库并设计各表，输入多条实际数据，并实现 数据的增、删、改操作。 三、实验步骤： 创建用于学生管理数据库，数据库名为 XSGL，包含学生的基本信息，课程信息和选课信息。 数据库 XSGL包含下列 3个表： (l） student：学生基本信息。 (2）course：课程信息表。 (3）sc：学生选课表。 各表的结构分别如表 1、表 2和表 3所示。 表 1 学生信息表：student 列名 数据类型 长度 是否允许为空值 sno 字符（文本）型 10 否 sname 字符（文本）型 10 否 ssex 字符（文本）型 2 否 sage 整数（数值）型 是 sdept 字符型 4 否 表 2 课程信息表：course 列名 数据类型 长度 是否允许为空值 cno 字符（文本）型 3 否 cname 字符（文本）型 30 否 credit 整数（数值）型 是 pcno 字符（文本）型 3 是 表 3 学生选课表：sc 列名 数据类型 长度 是否允许为空值 sno 字符（文本）型 10 否 cno 字符（文本）型 30 否 grade 整数（数值）型 是 1． 用可视化界面在 Access中建立数据库和表： （a）在 Access 中创建 xsgl.mdb 数据库，使用表设计视图创建如表 1、表 2、表 3 所示结构的 3 个表。 提示：(1)启动Access2000或Access2003，选择文件->新建->空数据库，输入数据库文件名 xsgl.mdb， 2 图 1 Access数据库设计界面 进入（2）； （2）启动如图 1的设计界面后，选择表对象和使用使用设计器创建表，选择新建，进入（3）； （3）生成如图 2界面，选择设计视图,点击确定按钮，进入（4）； （4）在图 3表设计界面下，分别创建 student表，course表和 sc表的结构； 图 2 表设计器 图 3 表结构设计界面 3 （5）输入表中的记录： 分别在 student表、course表和 sc表中输入如下表中的记录： 在数据库视图下，选择表对象中的相应表选择打开，在浏览视图下输入如下表记录： sno sname ssex sage sdept 95001 李勇 男 20 CS 95002 刘晨 女 19 IS 95003 王敏 女 18 MA 95004 张立 男 19 IS 95005 刘云 女 18 CS （b）对表中的记录进行浏览、修改、删除操作。 2．在 SQLSERVER中用企业管理器新建数据库和表： （1）建立 xsgl数据库： ①启动 SQL Server2000企业管理器，界面如下： 图 4 SQL Server2000企业管理器界面 cno cname credit pcno 1 数据库 4 5 2 数学 6 3 信息系统 3 1 4 操作系统 4 6 5 数据结构 4 7 6 数据处理 3 7 PASCAL语言 4 6 sno cno grade 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80 95003 2 85 95004 1 58 95004 2 85 4 ②选择树形菜单数据库，点击鼠标右键，出现如下弹出式菜单，选择新建数据库 ③在图中，按图分别设置数据库 xsgl的属性，点击确定按钮，完成了数据库的创建。 图 5 数据库名称属性设置 5 图 6 数据库数据文件属性设置 图 7 数据库日志文件属性设置 选择确定按钮，则创建 xsgl数据库。 （2）建立 student、course、sc表： ①选择 xsgl数据库树形菜单，选择表，在图 8表逻辑对象中点击鼠标的右键，在弹出式菜单中选 择新建，启动表设计器。 6 图 8表逻辑对象界面 ②选择新建，启动如图 9的表设计器，建立表结构，保存为 student。 图 9 student的表结构 ③分别按表 2、3建立 course表和 sc表。 （3）在表中添加记录：选择 student表，在快捷菜单下选择打开表->返回所有行，进入图 11，输 7 入学生表中的记录。 图 10 图 11 student表记录录入 依次按相同方法建立 course表和 sc 表，并输入其中的记录。 (4)修改表中的数据: (5）删除表中的记录 思考： 1. Access数据库主要有哪些对象组成？ 2. SQL SERVER2000数据库主要由哪些逻辑对象组成？物理数据库文件包括哪些文件？ 8 实验二: 数据库的建立和维护 实验目的 熟练掌握建立数据库和表，向数据库输入数据、修改数据和删除数据的操作。 实验内容 建立数据库并设计各表，输入多条实际数据，并实现数据的增、删、改操作。 实验步骤： 创建用于学生管理数据库，数据库名为 XSGL，包含学生的基本信息，课程信息和选课信息。数 据库 XSGL包含下列 3个表： (l） student：学生基本信息。 (2）course：课程信息表。 (3）sc：学生选课表。 各表的结构分别如表 1、表 2和表 3所示。 表 1 学生信息表：student 列名 数据类型 长度 是否允许为空值 sno 字符型 10 否 sname 字符型 10 否 ssex 字符型 2 否 sage 整数型 是 sdept 字符型 4 否 表 2 课程信息表：course 列名 数据类型 长度 是否允许为空值 cno 字符型 3 否 cname 字符型 30 否 credit 整数型 是 pcno 字符型 3 是 表 3 学生选课表：sc 列名 数据类型 长度 是否允许为空值 sno 字符型 10 否 cno 字符型 30 否 grade 整数型 是 一. 数据库的建立： 2． 用可视化界面建立： （a） 在 SQLSERVER中用企业管理器-新建数据库； 3． 命令方式建立： （a） 在 SQLSERVER中，在查询器中使用 T-SQL语句： CREATE DATABASE XSGL ON (NAME='XSGL_DATA', FILENAME='E:\XSGL.MDF', 9 SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=5%) LOG ON (NAME='XSGL_Log', FILENAME='e:\XSGL_Log.ldf', SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=1MB) 二. 表的建立： 1． 用可视化界面建立： 在 SQL SERVER中用企业管理器—数据库—XSCJ—表—右键—新建表; 2． 命令方式建立： 在 SQL SERVER中查询分析器的编辑窗口中用下列 SQL语句: use XSGL Create table student(sno CHAR(5),sname CHAR(10),ssex CHAR(2),sage int,sdept CHAR(4)) go Create table course(cno CHAR(2),cname CHAR(30),credit INT,pcno CHAR(2) NULL) go Create table sc(sno CHAR(5),cno CHAR(2),grade INT NULL) go 三. 表数据的添加: 1. 用可视化方法: I: 在 SQL SERVER中用企业管理器—数据库—XSCJ—表－表名—右键－打开表－返回所有行； 输入下列数据： sno sname ssex sage sdept 95001 李勇 男 20 CS 95002 刘晨 女 19 IS 95003 王敏 女 18 MA 95004 张立 男 19 IS 95005 刘云 女 18 CS cno cname credit pcno 1 数据库 4 5 2 数学 6 3 信息系统 3 1 4 操作系统 4 6 5 数据结构 4 7 6 数据处理 3 7 PASCAL语言 4 6 10 sno cno grade 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80 95003 2 85 95004 1 58 95004 2 85 2. 在 SQL SERVER查询分析器的编辑窗口中使用下列 SQL 语句插入数据: insert into student(sno,sname, ssex,sage,sdept) values('95001', '李勇', '男', 20, 'CS') insert into student(sno,sname, ssex,sage,sdept) values('95002', '刘晨', '女', 19, 'IS') insert into student(sno,sname, ssex,sage,sdept) values('95003', '王敏', '女', 18, 'MA') insert into student(sno,sname, ssex,sage,sdept) values('95004', '张立', '男', 19, 'IS') insert into student(sno,sname, ssex,sage,sdept) values('95005', '刘云', '女', 18, 'CS ') insert into course(cno, cname,credit,pcno) values('1', '数据库', 4, '5') insert into course(cno, cname,credit,pcno) values('2', '数学', 6, null) insert into course(cno, cname,credit,pcno) values('3', '信息系统', 3, '1') insert into course(cno, cname,credit,pcno) values('4', '操作系统', 4, '6') insert into course(cno, cname,credit,pcno) values('5', '数据结构', 4, '7') insert into course(cno, cname,credit,pcno) values('6', '数据处理', 3, null) insert into course(cno, cname,credit,pcno) values('7', 'PASCAL语言', 4, '6') insert into sc(sno,cno,grade) values('95001', '1' ,92) insert into sc(sno,cno,grade) values('95001', '2' ,85) insert into sc(sno,cno,grade) values('95001', '3' ,88) insert into sc(sno,cno,grade) values('95002', '2' ,90) insert into sc(sno,cno,grade) values('95002', '3' ,80) insert into sc(sno,cno,grade) values('95003', '2' ,85) insert into sc(sno,cno,grade) values('95004', '1' ,58) insert into sc(sno,cno,grade) values('95004', '2' ,85) 四. 表数据的修改: 1. 用可视化方法: 在 SQL SERVER中用企业管理器—数据库—XSCJ—表－表名—右键－打开表－返回所有行； 在表格中将相应的数据修改即可。 2．命令方法： 将所有学生的年龄增加一岁： update student set sage=sage+1 将 4号课程的学分改为 4: update course set credit=4 where cno=4 设置 7号课程没有先行课: update course set pcno=null where cno=7 将 95001号学生的 1号课程的成绩增加 3分: update sc set grade=grade+3 where sno=’95001’ and cno=’1’ 11 五. 表数据的删除: 1. 用可视化方法: 在SQL SERVER中用企业管理器—数据库—XSCJ—表－表名—右键－打开表－返回所有行； 单击左边的行标记, 选定某一行, 或单击后拖动选择相邻的多行, 再右击鼠标选择弹出式菜单 中的删除。 2．命令方法： 删除学号为 95005的学生的记录：delete from student where sno＝’95005’ 删除所有的课程记录：delete from course 删除成绩为不及格（少于 60分）的学生的选课记录：delete from sc where grade<60 思考： 比较用可视化界面与命令方式在数据的插入、修改、删除方面的优缺点。 12 实验二：数据库的简单查询和连接查询 实验目的： 掌握简单表的数据查询、数据排序和数据联结查询的操作方法。 实验内容： 简单查询操作和连接查询操作。 实验步骤： 一. 单表查询： 1. 查询全体学生的学号和姓名: select sno, sname from student 2. 查询全体学生的所有信息: select * from student 或者 select sno, sname, ssex,sage, sdept from student 3. 查询全体学生的姓名, 出生年份,和所在系, 并用小写字母表示所有系名: select sname, '出生年份为: ', year(getdate()) - sage, lower(sdept) from student 4. 给上例的结果集指定列名: select sname, '出生年份为: ' 出生, year(getdate())- sage 年份, lower(sdept) 系名 from student 5. 查询选修了课程的学生的学号: select distinct sno from sc 比较: select sno from sc 6. 查询年龄在 20岁以下的学生的姓名及其年龄: select sname, sage from student where sage<20 7. 查询考试成绩有不及格的学生的学号: select distinct sno from sc where grade<60 比较: select sno from sc where grade<60 8. 查询年龄在 20-30岁直接的学生的姓名, 姓名, 所在系: select sname, ssex, sdept from student where sage between 20 and 30 9. 查询 IS,CS,MA系的所有学生的姓名和性别: select sname, ssex from student where sdept in ('IS', 'MA','CS') 10. 查找所有姓’李’的学生的姓名, 学号和性别: select sname, sno, ssex from student where sname like '李%' 比较: 将学生表中的’95001’号学生的姓名’李勇’改为’李勇勇’, 再执行: select sname, sno, ssex from student where sname like '李_' 11. 查询没有先行课的课程的课程号 cno和课程名 cname: select cno, cname from course where pcno is null 二. 查询结果排序 12. 查询选修了 3号课程的学生的学号和成绩, 并按分数降序排列: select sno, grade from sc where cno='3' order by grade DESC 23. 查询全体学生的情况,查询结果按所在系号升序排列, 同一系中的学生按年龄降序排列: select * from student order by sdept ASC, sage DESC 13 三. 连接查询: 14. 查询每个学生及其选修课程的情况: select student.*, sc.* from student, sc where student.sno=sc.sno 比较: 笛卡尔集: select student.*, sc.* from student, sc 自然连接 : select student.sno, sname, ssex, sdept, cno, grade from student, sc where student.sno=sc.sno 15. 查询每一门课程的间接先行课(只求两层即先行课的先行课): select First.cno, Second.pcno 间接先行课 from course First, course Second where First.pcno=Second.cno 比较: select First.cno, Second.pcno 间 接先 行课 from course First, course Second where First.pcno=Second.cno and Second.pcno is not null 16. 列出所有学生的基本情况和选课情况, 若没有选课,则只列出基本情况信息: SQL Server 中: select s.sno, sname, ssex,sdept, cno, grade from student s, sc sc where s.sno*=sc.sno 17. 查询每个学生的学号, 姓名, 选修的课程名和成绩: select S.sno, sname, cname, grade from student S, course C, sc SC where S.sno=SC.sno and C.cno=SC.cno 思考: 如何求出不及格学生的学号, 姓名, 不及格的课程名以及成绩。 14 实验三：数据库的嵌套查询实验 实验目的： 加深对嵌套查询语句的理解。 实验内容： 使用 IN、比较符、ANY或 ALL和 EXISTS操作符进行嵌套查询操作。 实验步骤： 一. 使用带 IN谓词的子查询 1. 查询与’刘晨’在同一个系学习的学生的信息: select * from student where sdept in (select sdept from student where sname='刘晨') 比较: select * from student where sdept = (select sdept from student where sname='刘晨') 的异同 比较: select * from student where sdept = (select sdept from student where sname='刘晨') andsname<>'刘晨 V 比较: select S1.* from student S1, student S2 where S1.sdept=S2.sdept and S2.sname='刘晨' 2. 查询选修了课程名为’信息系统’ 的学生的学号和姓名: SQL Server中: select sno, sname from student where sno in (select sno from sc where cno in (select cno from course where cname='信息系统')) 3. 查询选修了课程’1’和课程’2’的学生的学号: select sno from student where sno in (selectsnofrom sc where cno='1') and sno in (select sno from sc where cno='2') 比较: 查询选修了课程’1’或课程’2’的学生的 sno: select sno from sc where cno='1' or cno='2' 比较连接查询: select A.sno from sc A, sc B where A.sno=B.sno and A.cno='1' and B.cno='2' 二. 使用带比较运算的子查询 4. 查询比’刘晨’年龄小的所有学生的信息: select * from student where sage< (select sage from student where sname='刘晨') 三. 使用带 Any, All谓词的子查询 5. 查询其他系中比信息系(IS)某一学生年龄小的学生姓名和年龄; select sname, sage from student where sage 'IS' 6. 查询其他系中比信息系(IS)学生年龄都小的学生姓名和年龄: select sname, sage from student where sage 'IS' 7. 查询与计算机系(CS)系所有学生的年龄均不同的学生学号, 姓名和年龄: select sno,sname,sage from student where sage<>all (select sage from student where sdept='CS') 四. 使用带 Exists谓词的子查询和相关子查询 8. 查询与其他所有学生年龄均不同的学生学号, 姓名和年龄: select sno,sname,sage from student A where not exists (select * from student B where A.sage=B.sage and A.sno<>B.sno) 9. 查询所有选修了 1号课程的学生姓名: select sname from student where exists (select * from sc where sno=student.sno and cno='1') 10. 查询没有选修了 1号课程的学生姓名: select sname from student where not exists (select * from sc where sno=student.sno and cno='1') 11. 查询选修了全部课程的学生姓名: select sname from student where not exists (select * from course where not exists ( select * from sc where sno=student.sno and cno=course.cno)) 11. 查询至少选修了学生 95002选修的全部课程的学生的学号: select distinct sno from sc A where not exists (select * from sc B where sno='95002'and not exists (select * from sc C where sno=A.sno and cno=B.cno)) 12. 求没有人选修的课程号 cno和 cnamecname: select cno,cname from course C where not exists (select * from sc where sc.cno=C.cno ) 13*. 查询满足条件的(sno,cno)对, 其中该学号的学生没有选修该课程号 cno的课程 select sno,cno from student,course where not exists (select * from sc where cno=course.cno and sno=student.sno) 14*. 查询每个学生的课程成绩最高的成绩信息(sno,cno,grade): select * from sc A where grade= (select max(grade) from sc where sno=A.sno ) 思考: 如何查询所有学生都选修了的课程的课程号 cno? 16 实验四：数据库的分组查询和统计查询 实验目的： 熟练掌握数据查询中的分组、统计、计算和集合的操作方法。 实验内容： 使用聚集函数查询、分组计算查询、集合查询。 实验步骤： 一. 使用聚集函数： 1． 查询学生总人数： Select Count(*) as 学生总数 from student 2. 查询选修了课程的学生总数： select count(distinct sno) as 选课学生总数 from sc 3. 查询所有课程的总学分数和平均学分数,以及最高学分和最低学分： select sum(credit) as 总 credit,avg(credit) as 课程平均学分,max(credit) as 最高学分, min(credit) as 最低学分 from course 4. 计算 1号课程的学生的平均成绩, 最高分和最低分: select avg(grade) as 平均成绩,max(grade) as 最高分, min(grade) as 最低分 from scwhere cno='1' 5. 查询’信息系’(IS)学生”数据结构”课程的平均成绩: select avg(grade) from student, course, sc where student.sno=sc.sno and course.cno=sc.cno and sdept='IS' and cname='数据结构' 6*. 查询每个学生的课程成绩最高的成绩信息(sno,cno,grade): select * from grade A where grade= (select max(grade) from sc where sno=A.sno ) 7*. 求成绩低于该门课程平均成绩的学生的成绩信息(sno,cno,grade) select * from grade A where grade= (select avg(grade) from sc where cno=A.cno ) 二. 分组查询 8. 查询各系的学生的人数并按人数从多到少排序 : selectsdept, Count(*) as 人数 from student group by sdept order by 人数 desc 9. 查询各系的男女生学生总数, 并按系别,升序排列, 女生排在前: select sdept,ssex,Count(*) as 人数 from student group by sdept, ssex order by sdept,ssex desc 10. 查询选修了 3门课程已上的学生的学号和姓名: select sno, sname from student where sno in (select sno from sc group by (sno) having count(*)>3) 11. 查询每个学生所选课程的平均成绩, 最高分, 最低分,和选课门数: select sno, avg(grade) as 平均成绩,max(grade) as 最高分, min(grade) as 最低分, count(*) as 选课门数 from sc group by sno 12. 查询至少选修了 2门课程的学生的平均成绩: 17 select sno, avg(grade) as 平均成绩, from sc group by sno having count(*)>=2 13. 查询平均分超过 80分的学生的学号和平均分: Select sno, avg(grade) as 平均成绩 from sc group by sno having avg(*)>=80 比较: 求各学生的 60分以上课程的平均分: select sno, avg(grade) as 平均成绩 from sc where grade>=60 group by sno 14. 查询”信息系”(IS)中选修了 5门课程以上的学生的学号: select sno from sc where sno in (select sno from student where sdept='IS') group by sno having count(*)>=2 三. 集合查询 15. 查询数学系和信息系的学生的信息; select * from student where sdept=’MA’ union select * from student where sdept='IS' 16. 查询选修了 1号课程或 2号课程的学生的学号: select sno from sc where cno='1' Union select sno from sc where cno='2' 比较实验三之 3. 思考: 1. 用两种方法查询平均成绩少于 70分的学生的学号。 2*. 求各系的”大学英语”课程的成绩最高的学生的姓名和成绩。 18 实验五：数据库视图的定义及使用 实验目的： 掌握视图的用法，加深对视图作用的理解。 实验内容： 1、创建、修改和删除视图。 2、利用视图进行查询。 实验步骤： 说明：对视图的创建、修改和删除均可在可视化界面下操作。 在 SQL Server企业管理器中，依次选择数据库――XSGL――视图――鼠标右击――新建 视图。然后通过单击按钮 或右击鼠标第一格的空白区域，选择弹出菜单中的“添加表”。然 后拖拽主键到外键，建立关联，点击字段左边的方框选择输出字段。 图 5-1 仅以命令方式来操作： 一、 视图的创建： 1．创建信息系学生信息的视图： create view IS_Student as select sno, sname, sage from student where sdept='IS' 2. 创建信息系选修了 1号课程的学生的视图: create view IS_S1 as select student.sno, cno, grade from student, sc where student.sno=sc.sno and sdept='IS' and cno='1' 3. 建立信息系选修了 1号课程且成绩在 90分以上的学生的视图: 19 create view as IS_S2 as select * from IS_S1 where grade>=90 4. 创建一个反映学生出生年份的视图: create view BT_S(sno, sname, 出生年份) as select sno, sname, year(date())- sage from student 5. 将所有女生的记录定义为一个视图: create view F_student as select * from student where ssex='女' 6. 将所有学生的学号和他的平均成绩定义为一个视图: create view S_G(sno, avg_grade) as select sno,avg(grade) from scgroup by sno 二、 视图结构的修改： 6. 将视图 F_student修改为信息系的所有女士的视图 alter view F_student as select * from student where ssex='女' and sdept='IS' 说明: 视图结构的修改的 AS 后的 Select 语句与创建视图的完全一致, 引入结构修改的目的是为 了避免与视图相关的数据库对象的变换, 如触发器, 关联等 三、查询视图 7. 在信息系的学生视图中查询年龄小于 20岁的学生: select * from IS_Student where sage<20 8. 查询信息系选修了 1号课程的学生: select sc.sno, sname from IS_Student, sc where IS_Student.sno=sc.sno and cno= '1' 9. 在视图 S_G中查询平均成绩在 90分以上的学生的学号和平均成绩: Select * from S_G where avg_grade >=90 四、更新视图： 10．将信息系学生视图 IS_Student中学号为”95002”的学生姓名改为”刘辰”: update IS_Student set sname='刘辰' where sno='95002' 比较: update IS_Student set sname=’刘辰’ wheresno=’95003’ 此语句不能实现数据的更新. 11. 向信息系学生视图 IS_Student中插入一个新的学生记录, 学号为 95029,姓名为”赵新”, 年龄为 20岁: insert into IS_Student values('95029', '赵新',20) 12. 删除信息系学生视图 IS_Student中学号为 95004的学生的记录: delete from IS_Student where sno='95029' 五、删除视图： 13. 删除视图 IS_S1: drop view IS_S1 思考: 1. 创建所有学生的基本信息和选课信息的视图 2. 基于上述视图查询各系学生各门功课的平均成绩. 20 实验六：数据完整性实验 实验目的： 加深对数据完整性的理解。 实验内容： 数据库的完整性设置。 实验步骤： 可视化界面的操作方法： 一、实体完整性 1．将 student表的“sno”字段设为主键：在表设计界面中，单击左边的行选定块，选定“sno” 字段，单击工具按钮 设置主键。如图 6-1所示： 图 6-1 2． 将“sc”表的“sno”和“cno”设置为主键：在表设计界面中，单击并拖动左边的行选 定块，选定 sno和 cno字段，单击工具按钮 设置主键。如图 6-2所示： 21 图 6-2 二、域完整性 3. 将“ssex”字段设置为只能取“男”，“女”两值：在表设计界面，点击图 4－3箭头所指按钮， 出现属性(Property)对话框，选择新建（New）按钮，然后在约束表达式（Constraint expression） 框中输入“ ssex in ('男','女') ”。如图 6－3所示。 22 图 6－3 三、参照完整性 4. 将“student”表和“sc”表中的“sno”字段设为参照: 打开“sc”表的设计界面, 点击工具栏 按钮 , 在弹出的属性(properties)对话框中点击“新建”按钮，在“主键表(Primary key table)” 下拉框中选择“student”表，在其下的字段选择框中选择“sno”，在“外键表(Foreign key table)” 下拉框中选择“sc”表，在其下的字段选择框中选择“sno”，单击关闭即可。见图 6.4。 23 图 6－4 命令方式操作方法： 一、实体完整性 1．将“student”表的“sno”字段设为主键： 当“student”表已存在则执行： alter table student add constraint pk_sno primary key (sno) 当“student”表不存在则执行: Create table student(sno CHAR(5) primary key , sname CHAR(10),ssex CHAR(2), sage int,sdept CHAR(4)) 注:可用命令“drop table student”删除“student”表 24 2. 添加一身份证号字段,设置其惟一性.(注: 操作前应删除表中的所有记录) Alter table student add id char(18) unique (id) 3. 将“sc”表的“sno”和“cno”设置为主键： 当“sc”表已存在则执行： alter table sc add constraint PK_SnoCno primary key (sno,cno) 当“sc”表不存在则执行： Create table sc(sno CHAR(5),cno CHAR(2), grade INT NULL, constraint PK_SnoCno primary key (sno,cno)) 二、域完整性 4. 将“ssex”字段设置为只能取“男”，“女”两值： 当“student”表已存在则执行： alter table student add constraint CK_Sex check (ssex in ('男' ,'女')) 当“student”表不存在则执行： Create table student(sno CHAR(5) primary key , sname CHAR(10), ssex CHAR(2) check (ssex in ('男' ,'女')) , sage int, sdept CHAR(4)) 5. 设置学号字段只能输入数字: alter table student add constraint CK_Sno_Format check (sno like '[0-9][0-9][0-9][0-9][0-9]') 6. 设置身份证号的输入格式: alter table student add constraint CK_ID_Format check ((id like '[0-9][0-9][0-9][0-9][0-9][0-9][1-2][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]_') OR (id like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]')) 7. 设置 18位身份证号的第 7位到第 10位为合法的年份(1900-2050) alter table student add constraint CK_ID_Format2 check ( not len(id)=18 or ( (convert(smallint,substring(id,7,4) )>=1900) and(convert(smallint,substring(id,7,4) )<=2050)) ) 三、参照完整性 9. 设置男生的年龄必须大于 22, 女生的年龄必须大于 20. Alter table student add constraint CK_age check (sex='男' and sage>=22 or sex='女' and sage>=20 ) 10. 将“student”表和“sc”表中的“sno”字段设为参照: 当“sc”表已存在则执行： alter table sc add constraint FP_sno foreign key (sno) references student(sno ) 当“sc”表不存在则执行： Create table sc(sno CHAR(5) constraint FP_sno foreign key references student(sno), cno CHAR(2),grade INT NULL, constraint PK_SnoCno primary key (sno,cno) ) 四、完整性验证 1． 实体完整性： 在“student”表数据浏览可视化界面中输入学号相同的两条记录将会出现错误 如下图所示： 25 图 6－5 或者在命令窗口输入下面两条命令也会出现错误提示： insert into student values('95001','张三','男',20,'CS') insert into student values('95001','李四','女',18,'CS') 图 6－6 下面的语句用来验证“sc”表中的实体完整性： insert into sc values('95002', '10',65) insert into sc values('95002', '10',90) 26 图 6－7 2. 域完整性: 使用下面的语句验证“ssex”字段的域完整性： insert into student values('95009','张匀','大',20,'CS') 3．参照完整性： 使用下面的语句“验证”sc表中的“sno”字段的域完整性(假设 student表中没有学号为“95998” 的学生记录)