实验三数据表的创建与管理

实验三数据表的创建与管理

一、实验目的

要求学生熟练掌握SQL server Management Studio的使用和利用T-SQL语句进行数据表的创建和删除，并对数据表和表中数据进行有效的管理。

二、实验内容

分别使用SQL Server Management Studio和T-SQL语句创建和删除数据表，修改表结构，输入数据、更新数据。

三、实验指导

1. 数据表定义

实验3.1在“JXGL”数据库中，使用SQL Server Management Studio建立S、C和SC 三个表，其结构如表

表1.3.1 学生表S结构

列名描述数据类型允许空值说明

sno 学号char(8) NO 主键

sname 姓名char(8) NO

age 年龄Smallint YES

sex 性别char(2) YES

sdept 所在系Varchar(50) YES

表1.3.2 课程表C结构

列描述数据类型允许空值说明

cno 课程号char(4) NO 主键

cname 课程名char(20) NO

credit 学分Float YES

pcno 先修课char(4) YES

describe 课程描述varchar(100) YES

表1.3.3 选课表SC结构

列描述数据类型允许空值说明

sno 学号char(8) NO

主键（同时都是外键）cno 课程号char(4) NO

grade 成绩Float YES

具体步骤如下：

(1)在SQL Server Management Studio的对象管理器中，单击数据库前面的“+”号，选择“表”并击右键，在弹出的快捷菜单中选择“新建表”菜单项，则进入设计表字段对话框，

如图

图1.3.1设计字段对话框

(2) 在设计表字段对话框中，共有三列参数：列名，数据类型，允许Null值。“列名”就是数据库表的字段名，而“数据类型”是字段值的类型即整型、字符型、日期时间型等类型，“允许Null值”是用来设置该字段中的值能不能为空。

“列属性”显示在表设计器的底部窗格中。主要有：

●常规

展开此项可显示“名称”、“允许空值”、“数据类型”、“默认值或绑定”、“长度”、“精度”和“小数位数”。

名称：显示所选列的名称。

长度：显示基于字符的数据类型所允许的字符数。此属性仅可用于基于字符的数据类型。

默认值或绑定：当没有为此列指定值时显示此列的默认值。此字段的值可以是SQL Server 默认约束的值，也可以是此列被绑定到的全局约束的名称。该下拉列表中包含数据库中定义的所有全局默认值。若要将该列绑定到某个全局默认值，可以从下拉列表中进行选择。另外，若要为该列创建默认约束，可以直接以文本格式键入默认值。

数据类型：显示所选列的数据类型。若要编辑此属性，只需单击该属性的值，展开下拉列表，然后选择其它值。

允许Null值：指示此列是否允许空值。若要编辑此属性，只需在表设计器的顶部窗格中单击与列对应的“允许Null值”复选框。

●表设计器

展开表设计器部分。

标识规范：显示此列是否对其值强制唯一性的相关信息。此属性的值是否为标识列以及是否与子属性“是标识”的值相同。

标识种子：显示在此标识列的创建过程中指定的种子值。默认情况下，会将值 1 赋给该单元格。

标识增量：显示在此标识列的创建过程中指定的增量值。默认情况下，会将值 1 赋给该单元格。

计算列规范：显示计算所得列的相关信息。该属性显示的值与“公式”子属性的值相同，可显示计算所得列的公式。

公式：显示计算所得列的公式。

简洁数据类型：按与SQL CREATE TABLE 语句同样的格式显示有关字段的数据类型的信息。

排序规则：显示当使用列值对查询结果的行进行排序时，SQL Server 默认情况下对列应用的排序规则顺序。

③设计好表的字段后，单击“关闭”按钮（或直接按“保存”按钮），弹出是否要保存更改的提示对话框，如图

图1.3.2 是否要保存更改提示对话框

④单击“是”按钮，弹出选择名称提示对话框，在这里命名为Student，单击“确定”按钮，则建立好了S tudent表。

用同样方法，可以建立起如表

实验3.2在学生管理数据库“EDUC”中，利用T-SQL语句创建数据表。表结构如下：

表1.3.4学生信息表Student_info的结构

列名描述数据类型允许空值说明

sno 学生学号char(8) NO 主键

sname 学生姓名char(8) NO

sex 学生性别char(2) YES

s_native 籍贯varchar(50) YES

birthday 学生出生日期Smalldatetime YES

续表

表1.3.5 课程信息表Course_info的结构

表1.3.6学生成绩信息表SC_info的结构

列名描述数据类型允许空值说明

sno 学生学号char(8) NO

主键（又分别是外键）tcid 上课编号smallint NO

score 学生成绩tinyint YES

表1.3.7 教师信息表Teacher_info的结构

列名描述数据类型允许空值说明

tno 教师编号char(8) NO 主键

tname 教师姓名char(8) NO

sex 教师性别char(2) YES

birthday 教师出生日期smalldate YES

dno 教师所在院系编号char(4) YES 外键

title 教师职称char(14) YES

home 教师家庭住址varchar(50) YES

tel 教师电话char(12) YES

表1.3.8 教师上课信息表TC_info的结构

列名描述数据类型允许空值说明

tcid 上课编号char(2) NO 主键

tno 教师编号char(8) YES 外键

score 学生成绩tinyint YES

classno 班级号char(4) YES 外键

cno 课程编号char(10) NO 外键

semester 学期char(6) YES

schoolyear 学年char(10) YES

classroom 上课地点varchar(50) YES

classtime 上课时间varchar(50) YES

表1.3.9 院系信息表Dept_info的结构

列名描述数据类型允许空值说明

dno 院系编号char(4) NO 主键

dname 院系名称char(16) NO

d_chair 院系领导char(8) YES

d_address 院系地址varchar(50) YES

tel 电话号码char(12) YES

表班级信息表Class_info的结构

列名描述数据类型允许空值说明

classno 班级编号char(4) NO 主键

classname 班级名称char(16) NO

monitor 班长char(8) YES

instructor 辅导员姓名char(8) YES

dno 院系编号char(4) NO 外键

由各数据表之间的联系可以看出，应依次创建院系信息表Dept_info、班级信息表Class_info、学生信息表Student_info、教师信息表Teacher_info、课程信息表Course_info、最后创建学生成绩信息表SC_info和教师上课信息表TC_info。该实验中只给出前三个数据表的代码，其它数据表创建的代码类似。在数据库引擎查询文档中输入如下代码。

①创建院系信息表Dept_info代码：

USE EDUC

GO

CREATE TABLE Dept_info

(

dno char(4) primary key ,

dname char(16) not null,

d_chair char(8),

d_address varchar(50),

tel char(12)

)

GO

②创建班级信息表Class_info代码：

USE EDUC

GO

CREATE TABLE Class_info

(

classno char(4) primary key ,

classname char(16) not null,

monitor char(8),

instructor char(8),

tel char(12),

dno char(4),

foreign key (dno) REFERENCES Dept_info(dno)

)

GO

③创建学生信息表Student_info代码：

USE EDUC

GO

CREATE TABLE Student_info

(

sno char(8) primary key ,

sname char(8) not null,

sex char(2),

s_native varchar(50),

birthday smalldatetime,

dno char(4),

classno char(4),

entime smalldatetime,

home varchar(50),

tel char(12),

foreign key (dno) REFERENCES Dept_info(dno),

foreign key (classno) REFERENCES Class_info(classno)

)

GO

2. 数据输入和更新

(1) 使用SQL Server Management Studio直接输入和修改数据

在“对象资源管理器”中，依次展开“数据库”→“JXGL”→“表”，选择要输入数据的表，单击右键，在弹出的快捷菜单中选择“编辑前200行”菜单项，如图

图1.3.3 SQL Server Management Studio输入和修改数据界面

如果要删除记录，只需选择行头，然后单击右键，在弹出的快捷菜单中选择“删除”命令，弹出删除提示对话框，单击“是”按钮，就可以删除所选择的记录。

如果要修改某条记录，选择该记录所对应的字段项就可以直接修改。

注意：对于有外关键字字段值的输入，需要先输入参照数据表的数据。

(2)使用T-SQL语句向数据表中插入和更新数据

实验3.3使用T-SQL语句向数据表中插入和更新数据。

①向数据表S中插入记录(’S13’,’吕淑霞’,19,’女’,’CS’)，则在数据库引擎查询文档中输入如下代码：

USE JXGL

GO

INSERT INTO S(sno,sname,age,sex,sdept)

VALUES('S13','吕淑霞',19,'女','CS')

GO

在上述代码中，利用插入语句向数据表中插入了一条记录。语句格式为：

INSERT INTO 表名(字段名1，字段名2，…) valuses(字段值1，字段值2，…)

使用插入语句时要注意以下几点：

●字段名的个数要与字段值的个数相同。

●在插入时，字段名与字段值按对应位置进行插入，所以字段值的类型要与相应字段名的数据类型相同。

●如果字段名允许为空，则可以用NULL代替没有填写的项。在这里要注意允许为空的含义，允许为空是指该字段值存在，但现在不知道。

正确输入后，按下键盘上的“F5”键或“执行”按钮就执行该SQL语句了，这样就可以向数据表插入数据了。

②吕淑霞同学选修了“电子商务”这门课，期未的考试成绩为95分，SQL语句如下：

USE JXGL

GO

INSERT INTO SC(sno,cno,grade) VALUES('S13','C10',95)

GO

其中'S13'、'C10'分别为吕淑霞同学的学号和“电子商务”的课程号。或只给指定属性赋值：

USE JXGL

GO

INSERT INTO SC(sno,cno) VALUES('S13','C9')

GO

③修改吕淑霞同学“电子商务”的成绩为90。

USE JXGL

GO

UPDATE SC SET GRADE=90

WHERE SNO='S13' AND CNO='C10'

GO

④在表SC中删除学号为’S13’和课号’C10’的记录。

USE JXGL

GO

DELETE FROM SC WHERE sno='S13' and cno='C10'

GO

3. 数据表结构的修改

(1)使用SQL Server Management Studio修改表结构

选定要修改的数据表单击右键弹出快捷菜单，选定“设计”菜单，出现图

(2) 使用T-SQL语句修改表结构

先打开表所在的数据库，再使用ALTER语句增加、修改或删除字段的相关信息。

实验3.4修改数据表结构。

①为学生表“S”中年龄“age”字段增加约束，限制年龄在15岁到30岁之间（包括15岁和30岁）。

USE JXGL

GO

ALTER TABLE S

ADD CONSTRAINT CON_age CHECK(age>=15 and age<=30)

GO

②在学生表“S”中增加班级字段“class”为可变字符串类型（varchar），长度为20。

USE JXGL

GO

ALTER TABLE S ADD class varchar(20)

GO

③修改学生表“S”中的“class”字段的长度为50。

USE JXGL

GO

ALTER TABLE S ALTER COLUMN class varchar(50)

GO

④删除学生表“S”中的“class”字段。

USE JXGL

GO

ALTER TABLE S DROP COLUMN class

GO

4．查看数据表信息

利用sp_spaceused和sp_MSforeachtable这两个存储过程，可以方便的统计出用户数据表的大小，包括记录总数和空间占用情况。

实验3.4查看数据表的相关信息

(1) 查看指定表“S”的空间大小等信息。

USE JXGL

GO

EXEC sp_spaceused 'S'

GO

(2) 查看所有用户表的空间表大小等信息。

USE JXGL

GO

EXEC sp_MSforeachtable "exec sp_spaceused '?'"

GO

四、注意事项

(1) 输入数据时要注意数据类型、主键和数据约束的限制。

(2) 数据更改和数据删除时要注意外键约束。

五、思考题

(1)数据库中一般不允许更改主键数据。如果需要更改主键数据时，应怎样处理？

(2)为什么不能随意删除被参照表中的主键。

六、练习题

1．创建数据表。分别使用SQL Server Management Studio和T-SQL命令创建图书管理数据库“TSGL”中的四个数据表的结构：readers（读者信息表）、books(图书信息表)、borrowinf （借阅信息表）、readtype（读者类型表）。各表的结构如下：

readers表结构

列名描述数据类型允许空值说明

ReaderID 读者编号Char(10) NO 主键

Name 读者姓名Char(8) YES

RederType 读者类型Int YES 外键

BorrowedQuantity 已借数量int YES

books 表的结构

列名含义数据类型允许空值说明

BookID 图书编号char(15) NO 主键

Name 图书名称varchar(50) YES

Author 作者char(8) YES

Publisher 出版社varchar(30) YES

PublishedDate 出版日期smalldatetime YES

Price 价格real YES

borrowinf表的结构

列名含义数据类型允许空值说明

ReaderID 读者编号char(10) NO

主键(外键) BookID 图书编号char(15) NO

BorrowedDate 借阅日期datetime NO

ReturnDate 归还日期datetime YES

readtype表的结构

列名含义数据类型允许空值主键

TypeID 类型编号int NO 主键

Name 类型名称Varchar(20) NO

LimitBorrowQuantity 限借数量int YES

BorrowTerm 借阅期限（月）int YES

该表中数据至少包括教师、学生和其他人三种类型。

2．向数据表中输入数据。

利用SQL Server Management Studio和T-SQL向表中输入数据。

3．依照上述实验，完成下列操作：

①用INSERT命令在readers 表中插入两条记录。

②用UPDATE命令将readtype表中教师的限借阅数量修改为30，借阅期限修改为180天。

③用DELETE命令删除书名为“数据结构”的图书信息。