11春数据库实验3 表的创建、管理和数据

实验三 表的创建、管理和数据操纵(4学时)

【实验目的】

了解SQL语言的使用，进一步理解关系运算，巩固数据库的基础知识。

【实验要求】

利用SQL语言进行数据库表的各种操作： 1．数据库表的创建、修改和删除操作。

2．向表中进行数据的插入、删除和修改操作。 【实验内容】

1. 利用数据定义语句在实验一创建的stu_DB库中建立学生管理系统的三个表：Student、Course、SC。

2．利用INSERT、UPDATE和DELETE语句对上述三张表的数据进行插入、更新和删除操作。

3．在stu_DB库中添加Teacher和TC表，对已建立的学生管理系统中的表添加充足的数据(每个表不少于20条)，以便完成本实验的后继操作。 【实验步骤】 一、数据定义

(一)表的创建

在实验一创建的数据库stu_DB中分别用企业管理器和查询分析器，按下面的表结构创建学生管理系统的表。 表3-1 Student表结构

列名

Sno Sname Ssex Sage Sdept

说明

学号 姓名 性别 年龄 所在系

数据类型

字符串，长度为8 字符串，长度为10 字符串，长度为2

整数

字符串，长度为20

约束

主码 非空 ’男’或 ’女’

15~45

默认为’计算机系’

表3-2 Course表结构 create table Course (

Cno char(8) primary key, Cname CHAR(20)not null, Cpno CHAR(8),

Ccredit int check (Ccredit between 1 and 6) , Semster int check (Semster between 1 and 8), Period int check (Period >=0), )

列名

Cno Cname Cpno Ccredit

说明

课程号 课程名 先修课程号 学分

数据类型

字符串，长度为8 字符串，长度为20 字符串，长度为8

整数

约束

主码 非空 取值：1~6

1

Semster Period

学期 学时

整数 整数

取值：1~8 取值：≥0

表3-3 SC表结构 Create table SC1 ( Sno char(8) , Cno char(8), Grade int check (Grade between 0 and 100), )

列名

Sno Cno Grade

说明

学号 课程号 成绩

数据类型

字符串，长度为8 字符串，长度为8

整数

约束

主码，引用student的外码 主码，引用course的外码

取值：0~100

1．利用企业管理器创建表 （1）打开企业管理器。

（2）选中实验一创建好的数据库stu_DB，单击数据库中的表对象，然后右击窗口右侧选择新建表，弹出如图所示窗体。

图3-1 新建表窗体

（3）在这个窗体中，列名列就示表的字段名，可以在这个窗体中为字段选择数据类型和长度以及是否可以为空值。

（4）在此处可以为表选择文件组，在表中单击右键，选择属性，如果已经为数据库创建了一个次要组，可以在属性窗体的表文件组中为此表选择文件组。应该把一些竞争使用的表放于不同的文件组中，并且让文件组属于不同磁盘，这样可以在表竞争读写时提高并发性能。

（5）创建完成后，单击磁盘图标，并为表取一个名字。注意，应该为表取一个有意义的名字。

2．用Transact_SQL语句创建表

2

语法为：

CREATE TABLE

[ database_name.[owner].] table_name

( {column_name data_type [DEFAULT ?default_value?]| [CONSTRAINT CONSTRAINT_name]

}, […n] [IDENTITY [(seed, increment )]] )

[ON { filegroup | DEFAULT }]

[TEXTIMAGE_ON {filegroup | DEFAULT }]

【例1】生成一个表名为student的表 create table student (

sno CHAR(8) primary key, sname CHAR(10) not null,

ssex CHAR (2) check (ssex='男'or ssex='女'), sage smallint,

sdept CHAR (20) default ('计算机系'), )

图3-2查询分析器窗体——创建表

【例2】 创建一张名为Ta1的表，此表中有3列，第一列Pid定义为主键，并且自动增长。第二列Name默认值为Unknown，第三列定义一个约束(日期不能大于输入当天的日期)，插入一条记录并进行查询，显示如下图。

CREATE TABLE ta1 (

pid int identity(1,1) primary key, [name] CHAR(10) default ('unknow'),

birthday datetime CHECK( birthday

3

注意该列有什么特殊性？

图3-3 查询分析器窗体——创建表

请参考例1和例2，完成以下操作：

(1)按前面所给的表结构创建表Course和SC。

(2)自行设计表的结构，用SQL语句建立第二章习题5中的4个表。

(二)表的修改

1． 用企业管理器修改表

⑴ 右键点击所要修改的学生表，选择“设计表”，会弹出如图3-4所示的窗体，在这个窗体中可以更改数据表的字段。在空白处点右键，选择“属性”，弹出另一窗体，在此可以更改约束，也可以增加约束。

图3-4 修改表窗体

（2）利用Transact-SQL语句修改表

4

? 向表中添加新的字段：在学生表中添加一个“班级”字段，数据类型为字符型。

ALTER TABLE student ADD class CHAR(6)

? 删除表中的旧列：将学生表中的“Sdept”字段删除。

ALTER TABLE student DROP COLUMN Sdept

? 更改表以添加具有约束的列。

给学生表的增加“grade”字段并加上CHECK约束，让其不可以大于100。

ALTER TABLE student ADD grade int CONSTRAINT ch_grade CHECK(grade<100) EXEC sp_help ch_grade

给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。 ALTER TABLE student ADD birthday DATETIME NULL CONSTRAINT ch_birthday CHECK(birthday

? 添加具有默认值的可为空的列：

在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。 ALTER TABLE student ADD matriculationday smalldatetime NULL CONSTRAINT adddateflt DEFAULT getdate()

请参考上面所给的例子：

(1) 自行设计表结构修改操作（不少于8个语句），并进行实验验证。 (2) 修改表student，结果如表3-4。

(3) 创建表3-5、表3-6，为后续实验建立基础。 表3-4 更新后的student表结构

列名

Sno Sname Ssex Sdept Sbirth sclass sremark saddress szipcode sphone semail

说明

学号 姓名 性别 所在系 出生日期 班级 评语 家庭住址 邮编 电话 电子邮箱

数据类型

字符串，长度为8 字符串，长度为10 字符串，长度为2 字符串，长度为20

日期

字符串，长度为20 变长字符串，长度为100 变长字符串，长度为40 字符串，长度为6 字符串，长度为15 变长字符串，长度为40

约束

主码 非空 ’男’或 ’女’ 默认为’计算机系’

自定义

表3-5 Teacher表结构

列名

tno tname

说明

工号 姓名

数据类型

字符串，长度为8 字符串，长度为10

5

约束

主码 非空

tsex tdept tbirth tposition tsalary texperience tamount tphone temail taddress

性别 所在系 出生日期 职称 工资 简历 工作量 电话 电子邮箱 家庭住址

字符串，长度为2 字符串，长度为20

日期

字符串，长度为12

整数

变长字符串，长度为200

整数

字符串，长度为15 变长字符串，长度为40 变长字符串，长度为40

’男’或 ’女’ 默认为’计算机系’

自定义

取值：0~10000

取值：0~1000

表3-6 TC表结构

列名

Tno Cno Classroom

说明

工号 课程号 教室

数据类型

字符串，长度为8 字符串，长度为8 变长字符串，长度为30

约束

主码，引用Teacher的外码 主码，引用Course的外码

alter table Student add Sdept char(2) default ('计算机系') alter table Student drop column Sdept

alter table Student add Sbirth datetime ,Sremark char(20), Saddress char(100),Szipcode char(6),Sphone char(15), Semail char(40)

create table Teacher ( Tno char(8) primary key, Tname char(10) not null, Tsex char(2) check (Tsex='男' or Tsex='女'), Tdept char(20) default('计算机系'), Tbirth datetime, Tposition char(12), Tsalary int check(Tsalary between 0 and 10000), Texperience varchar(200), Tamount int check(Tamount between 0 and 1000), Tphone char (15), Temail varchar(40), Taddress varchar(40), )

create table TC ( Tno char(8) , Cno char(8) , Classroom varchar (30),

6

)

CREATE TABLE ta1 (

pid int identity(1,1) primary key, [name] CHAR(10) default ('unknow'),

birthday datetime CHECK( birthday

drop table ta1 (三)表的删除

可以在企业管理器中选择要删除的表直接删除，也可以通过Transact-SQL语句DROP 删除表的定义及表中的所有数据、索引、出发器、约束和权限规范。

如要删除ta1表，语句如下： DROP TABLE ta1 请参考上面的例子完成对表Student 、Course 、SC的删除操作，它们删除的次序如何？并进行实验验证。 二、数据操纵

（一）查看、重命名及删除用户定义的数据类型

1．使用企业管理器创建一个名为newtype1、长度为6、可变长字符、允许为空的自定义数据类型。

进入企业管理器，进入stu_DB数据库，用鼠标右键单击“用户定义的数据类型”后选择“操作”菜单中的“新建用户定义数据类型”或点击“新建”按扭，将弹出下图所示窗体：

图3-4 数据类型定义窗体

输入要定义的数据名称newtype1，选择数据类型varCHAR，输入长度为6，在“允许NULL值”复选框中打个“√”，单击“确定”按钮即可。

2.使用T-SQL语句创建一个名为newtype2，数据长度为6，定长字符型，不允许为空的自定义数据类型。

USE stu_DB

7

EXEC sp_addtype newtype2, ?CHAR(6)?, ?not null? 3.命名用户自定义的数据类型

⑴ 使用系统存储过程sp_rename将自定义数据类型newtype1重新命名为a1。 EXEC sp_rename newtype1, a1

⑵ 使用企业管理器再将自定义数据类型a1重新命名为newtype1。 4．删除用户自定义数据类型

⑴ 使用系统存储过程sp_droptype来删除用户自定义的数据类型。 EXEC sp_droptype newtype1

⑵ 使用企业管理器删除用户自定义数据类型

进入企业管理器，进入stu_DB数据库，单击“用户定义的数据类型”后，右边窗口将出现所有用户定义的数据类型，再用鼠标右键单击newtype2，在弹出的快捷菜单上单击“删除”按钮即可。

注意：正在被表或其他数据库对象使用的用户定义类型能不能删除？请进行实验验证。 （二）数据的更新

1. 利用企业管理器更新数据

打开企业管理器，右键点击所要修改的表，选择“打开表”，单击“返回所有行”，会弹出如图3-5所示的窗体，在该窗体中可以修改表中的数据内容。这里的修改内容是自动存储的，修改完之后，直接关闭该窗体，数据内容即修改成功。

图3-5数据修改窗体

2.利用查询分析器更新数据

在关系数据库中，常见的数据更新的语句有三条：INSERT、UPDATE和DELETE ⑴INSERT语句

向student表中插入一行数据，具体数据如下：

学号：04265005, 姓名：刘辉, 性别：男,年龄：21, 系别：计算机系 USE stu_DB

INSERT INTO student (Sno, Sname, Ssex, Sage, Sdept) VALUES (?04265005?, ?刘辉?, ?男?, 21, ?计算机系?) INSERT INTO student (Sno, Sname, Ssex, Sage) VALUES (?04265006?, ?李慧?, ?女?, 21)

向ta1表中插入数据如下：

INSERT ta1([name],birthday) values (?lan?,?1977-03-02?) INSERT ta1 values (?lan?,?1977-03-02?) INSERT ta1(birthday) values (?1977-03-02?)

请根据上述例子，自行设计不同情况下的数据插入语句（不少于10个语句），并进行实验验证，验证要求如下：

8

? ? ? ? ? 不满足唯一性约束的数据的插入。 不满足用户自定义约束的数据的插入。 不满足外键约束的数据的插入。 利用缺省值的数据的插入。

不合理的数据是否能进入数据库。

⑵UPDATE语句

更改（学生）表数据，将学号为?04265005?的学生的所在系改成?机械工程系?。 UPDATE student SET Sdept=?机械系? WHERE Sno=?04265005?

请根据上述例子，自行设计数据修改语句（不少于10个语句），并进行实验验证，验证要求同INSERT语句。

⑶DELETE语句删除行

删除student表中的学号为?04265005?的记录。 DELETE FROM student WHERE Sno =?04265005?

如果想清除表中的所有数据但不删除这个表，可以使用TRUNCATE TABLE语句。该语句相当于没有条件的DELETE语句，并且该语句不记录日志。

请根据上述例子，自行设计不同情况的数据删除语句（不少于10条语句），并进行实验验证不满足外键约束的数据删除操作。

对数据定义中创建的学生管理系统（Student， Course，SC，Teacher，TC表）的每个表添加充足的数据(每个表不少于20条)，以便完成本实验的后继操作。具体表信息可参考如下：

Student表： Sno sname Sbirthday Sdept 04261001 1982.02.07 王冬梅 计算机系 …… 04261002 1982.03.04 王田田 计算机系 …… 04261003 1982.06.06 吴畏 计算机系 …… 04261004 04261005 04262001 04262002 04262003 04561001 04563001 马丽 杨乾坤 刘乐 夏天 杨致远 陶然 姚远 1982.04.03 1982.03.23 1983.01.16 1983.02.08 1982.07.22 1982.01.12 1982.03.21 …… Cpno C005 C001 C006 C007 9

计算机系 计算机系 计算机系 计算机系 计算机系 数学系 数学系 …… Credit …… …… …… …… …… …… …… …… …… …… …… …… …… …… …… course表： Cno Cname C001 数据库原理 C002 高等数学 C003 管理信息系统 C004 操作系统 C005 数据结构

3 4 2 3 3 C006 计算机文化基础 C007 C语言程序设计 …… …… score表： Sno 04261001 04261002 04261003 04261004 04261005 04262001 04262002 04262003 04561001 04563001 …… teacher表： Tno T002 Tname 罗莉 Tsex 女 女 男 男 男 男 男 C006 …… 2 3 …… …… …… …… Cno C001 C001 C001 C001 C001 C001 C001 C001 C007 C007 …… Score 90 85 73 98 96 97 83 85 92 97 …… Department 计算机系 计算机系 计算机系 计算机系 数学系 数学系 数学系 物电学院 …… …… …… …… …… …… …… …… …… T001 石云丹 T003 王国强 T004 吴栋 T005 高鸿轩 T006 张怀良 T007 刘晓伟 T108 马莉莲 女 …… …… …… TC表： Tno T001 T002 T003 T004 T005 T006 ……

【思考题】

cno C005 C007 C001 C006 C004 C002 …… classroom 22号楼405 7号楼406 17号楼504 17号楼401 8号楼201 8号楼202 ……

1.表Student 、Course 、SC应按何种次序删除？为什么？

2.正在被表或其他数据库对象使用的用户定义类型能不能删除？为什么？

10

本文来源：https://www.bwwdw.com/article/soxx.html