数据库原理与应用(数据表的基本操作)

1、数据库原理与应用实验报告题目：实验三：数据表的基本操作学号：1148028姓名：沈宇杰日期：2013/4/11实验预习情况：本章的预习内容主要是学习对于数据表的基本操作。表是由行和列组成的。：表的列名在同一个表中具有唯一性，同一列的数据属于同一种数据类型。：除了用列名和数据类型来指定列的属性外，还可以定义其他的属性，是否为空、默认值、标示符列、全局唯一标示符列等。：SQL 中共有5中约束：主关键字约束( Primary Key Constraint )、外关键字约束( Foreign Key Constraint )、唯一性约束( Unique Constraint )、约束检查( Check

2、 Constraint )、默认约束( Default Constraint )。预习内容：1.使用向导创建数据表先新建一个数据库“myDB”,右击表对象，从弹出的快捷菜单中选择“新建表”命令。定义一个整型列Collnt，该列不能为空，默认值为0。每当在表中在该列插入带空值的行时，显示该列的默认值。下面再定义一个日期型列 ColDataTime,该列不能为空，且默认值为函数 getdate()。再定义一个标示符属性列Collnd，其初值为1，增量也为1。注意：标示符属性列必须以decimal、int、smallint、bigint或tinyint数据类型定义。在定义标示符列时，必须指定种子和增

3、量值（其默认值均为1）。再定义一个全局唯一标示符属性列ColRow。注：一个表最多只能有一列定义为是RowGuid列，且该列必须定义为uniqueidentifier数据类型。如果应用程序需要生成在整个数据库或世界各地所有网络计算机的全部数据库中均为唯一标示符列，要使用全局唯一标示符列属性。设计好数据表后，单击工具栏上的保存按钮，输入表名并保存。如要查看代码，可以选择编写脚本为àcreate 到à新查询编辑器窗口。2.使用Transact-SQL语句创建数据表 USE myDB GO CREATE TABLE myTable2(Collnt int NOT NULL DEF

4、AULT 0,ColReal real,ColChar char(10),ColVarChar nchar(10),ColDataTime datetime NOT NULL DEFAULT GetDate(),CollInd int IDENTITY(1,1) NOT NULL,ColRow uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT NewID()GO3.使用Transact-SQL语句在数据库myDB中创建学生成绩管理系统数据表，包括三个数据表：课程数据表Course、学生数据表Student、学生成绩数据表SC。 USE myDBCREAT

5、E TABLE Course(Cno char(1) NOT NULL DEFAULT '',Cname varchar(20) DEFAULT'',Cpno char(1),Ccredit int DEFAULT 0,Constraint CoursePK Primary Key(Cno)CREATE TABLE Student(Sno char(5) NOT NULL DEFAULT'',Sname char(6) DEFAULT'',Ssex char(2) DEFAULT'',Sage int DEFAUL

6、T 0,Sdept char(2) DEFAULT'', Constraint StudentPK Primary Key(Sno),Constraint SaggeCK Check(Sage > 0 and Sage < 150)CREATE TABLE SC(Sno char(5) NOT NULL DEFAULT'',Cno char(1) NOT NULL DEFAULT'',Grade int,Constraint SCPK Primary Key(Sno,Cno),Constraint StudentFK foreign

7、Key(Sno) References Student(Sno),Constraint courseFK foreign Key(Cno) References Course(Cno),）GO4.主关键字约束右击要操作的数据表，选择设计命令，选择要设置为主键的字段（要选择多个字段的话请用ctrl键），然后有鼠标右键单击选中的字段，从弹出的快捷菜单中选择“设置主键”按钮。或是点击菜单栏中的长得像钥匙的按钮。5.使用Transact-SQL语句指定主关键字约束Constraint StudentPK Primary Key(Sno),Constraint SCPK Primary Key(Sno,

8、Cno),:此语句包含在表SC中!此语句指定了Sno为主关键字且主关键字名为StudentPK，后一句语句的目的是同时设置Sno和Cno两个主关键字，且关键字名为SCPK，此语句告诉我们可以同时设置多个字段为主关键字。6.外关键字约束，使用向导指定外关键字约束。例如设置SC表中的Sno是一个外关键字，参考Student表中的Sno；设置SC数据表中的Cno是一个外关键字，参考Course表中的Cno。在资源对象管理器中，打开数据表SC的数据表，右击设计选项卡，点击工具栏上的“管理关系”按钮，弹出外键关系对话框，设置SC数据表的Sno是一个外关键字，参考Student表中的Sno。过程：在外键关

9、系选项卡中，点击表和列规范后面的设置按钮，输入关系名StudentFK，在主键表中选择被参考的数据表Student并选择Sno列，同时选择外键表下拉菜单中的数据表SC为外键表，且选择数据表Sno。7.使用Transact-SQL语句指定外关键字约束如：利用Transact-SQL语句设置SC数据表的Sno是一个外关键字，参考Student表中的Sno；设置SC数据表的Cno是一个外关键字，参考Course表中的Cno。Constraint StudentFK foreign Key(Sno) References Student(Sno),Constraint courseFK foreign

10、 Key(Cno) References Course(Cno),:此语句包含在表SC中!其中的第一条语句是设置SC表中Sno为外关键字，参照Student表中的Sno其中的第二条语句是设置SC表中Cno为外关键字，参照Student表中的Cno8.唯一性约束（确保在非主键列或允许为空值的列中输入不重复的值）在对象资源管理器中，用鼠标右键单击要操作的数据表，选择设计命令。单击工具栏上的“管理索引和键”按钮，单击添加按钮，在列一栏中选择要将唯一约束附加的列，单击“关闭”按钮。9. 使用Transact-SQL语句创建唯一性约束如：使用Transact-SQL语句设置Student数据表的Snam

11、e是一个满足唯一性约束的列。Constraint IX_stu Unique (Sname),:此语句包含在表Student中!设置了Student数据表的Sname是一个满足唯一性约束的列。且唯一性约束名为IX_stu10.检查约束（对输入列或整个表中的值设置检查条件，以限制输入值，保证数据库的数据完整性，可以对每个列设置检查条件。）在对象资源管理器中，右击要操作的数据表，选择设计命令，单击工具栏上的管理Check约束按钮，单击添加按钮，在“约束表达式”文本框中输入检查约束的表达式，输入完毕后，在“约束名”文本框中输入约束的名称，单击关闭按钮。如：设置Student数据表中的Sage的值必须

12、在0100之间。11.使用Transact-SQL语句设置Student数据表中的Sage的值必须在0100之间，Ssex的值只能是“男”或“女”。Constraint SaggeCK Check(Sage > 0 and Sage < 150),Constraint SsexCK Check(Ssex in ('男','女'):此语句包含在表Student中! 第一句语句是在Student表中建立了约束SaggeCK用于限定Sage的范围在【0，100】 第二句语句是在Student表中建立了约束SsexCK用于限定Ssex的范围是男或女。 注释：

13、Sage > 0也可以写成 (Sage>(0) AND Sage<(150)。12.默认约束（默认约束DEFAULT通过定义列的缺省值或使用数据库的缺省值对象绑定表的列，用来指定列的缺省值。）在对象资源管理器中，右击要操作的数据表，点击设计命令，选择要设置为默认约束的字段后，在默认一栏中输入该字段的默认值，即可创建默认约束。13. 使用Transact-SQL语句指定默认约束方法一：使用DEFAULT关键字，如：Sno char(5) NOT NULL DEFAULT'', 方法二：使用CREATE DEFAULT语句创建默认对象，然后使用sp_binddef

14、ault系统存储过程将它绑定到列上。 14. 修改数据表（使用向导修改数据表） 若要修改某数据表，必须首先进行数据表的结构设计界面，然后就与新建数据表一样，可以增加、删除数据列，修改某数据列的数据类型、长度等属性，还可以修改数据表的主键、外键、索引、唯一性约束、检查约束等。 如：修改数据表Student。右击Student单击设计。用和创建对象一样的方法修改数据表。 15. 使用Transact-SQL语句修改数据表注释：实际工作中不如首先删除要修改的数据表，然后重新创建数据表。例如：向学生数据表Student中增加一个数据列住址Saddr，字符型20。USE myDBGOALTER TABL

15、E Student ADD Saddr VARCHAR(20) NULLGO注释：如果是用向导新增的列Saddr则操作完毕后需保存！16.使用Transact-SQL语句向表Student中系号列增加一个检查约束，检查其字符数必须为2。USE myDBGOALTER TABLE Student ADD CONSTRAINT CK_SDept CHECK(LEN(RTRIM(Sdept)=2)GO17. 使用Transact-SQL语句删除学生数据表Student中的住址列。USE myDBGOALTER TABLE Student DROP COLUMN SaddrGO18.向表中插入数据：使

16、用向导向表中插入数据。在资源对象管理器中，定位到要插入数据的数据表，右击并单击“编辑前两百行”。此时可以输入你想要的数据。注释：直接输入数据即可，会自己保存的。当然数据得符合定义的约束条件。：使用Transact-SQL语句想表中插入数据USE myDBGOINSERT Student VALUES ('95005','李呵','男','20','cs')GO19.将另一个学生数据表Student 0 中的数据插入到学生数据表Student中。USE myDBGOINSERT StudentSELECT *FROM

17、 Student0 WHERE Ssex ='男'GO20.删除表中的数据：使用向导删除表中的数据。在资源对象管理器中，选择需要删除数据的表，用鼠标右键单击该表，右击选择编辑前200行，选中要删除的记录，右键选择删除命令。：使用Transact-SQL语句删除表中的数据如：删除Student表中关于男生的数据USE myDBGODELETE FROM Student WHERE Ssex ='男'GO如：删除学生数据表Student中的全部数据USE myDBGODELETE FROM StudentGO注释：虽然TRUNCATE TABLE也能清空表中的数据。

18、但是它是清空表中的所有数据，只留下一个表的定义，而且不记录日志文件。21.修改表中的数据：使用SQL Server Management Studio工具修改表中的数据在对象资源管理器中，选择需要删除数据的表，右击选择编辑前两百行，即可对表中的数据进行修改。22. 使用Transact-SQL语句来修改表中的数据USE myDBGOUPDATE SCSET Grade = Grade * 1.05WHERE Sno IN (SELECT Sno FROM Student WHERE Ssex ='男')GO本题实现的功能是把男生的成绩乘以1.05倍。23.删除数据表：再删除数据

19、表时，必须首先删除与此表有外关键字约束的表。如学生数据表Student的Sno是学生成绩SC的外关键字时，删除Student表之前，必须首先删除SC。：使用SQL Server Management Studio 工具删除数据表在资源对象管理器中，右击要删除的表，选择删除命令，出现“删除对象”对话框，该对话框列出了该对象所依赖的对象和依赖于该表的对象，当有对象依赖于该表时，该表则不能被删除。：使用Transact-SQL语句删除学生数据表SCUSE myDBGODROP TABLE SC一些常用操作的总结：-增加一列ALTER TABLE 表名 ADD 列名 VARCHAR(20) NULL-

20、删除一列ALTER TABLE 表名 drop COLUMN 列名-修改一列alter TABLE 表名 ALTER COLUMN 列名 VARCHAR(40) NULL-修改一列的类型alter TABLE 表名 ALTER COLUMN 列名 VARCHAR(40)-添加主键约束alter table 表名add constraint 约束名 primary key (列名)-添加唯一约束alter table 表名add constraint 约束名 unique (列名)-添加默认约束alter table 表名add constraint 约束名 default(内容) for 列名

21、-添加check约束alter table 表名add constraint 约束名 check(内容)-添加外键约束alter table 表名add constraint 约束名 foreign key(列名) references 另一表名(列名)-删除约束alter table 表名drop constraint 约束名-纵向连接两个表select *from stuInfo unionselect *from stuMarks-重命名，表名exec sp_rename '原表名','改后表名'-重命名，列名exec sp_rename '表名.

22、原列名','改后列名','column'注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。实验内容与完成情况： 第一题： 在SQL Server 2008中建立一个数据库，进行实验所要求的各种操作，所有的SQL操作均在此建立的新数据库里进行。CREATE DATABASE LimenON(NAME = Limen_Dat,FILENAME = 'D:SQL_DBSLimen_Data.mdf',SIZE = 10)LOG ON(NAME = Limen_Log,FILENAME = 'D:SQL_DBSLimen_Log.l

23、df')GO此命令建立了名叫Limen的数据库，数据库的大小为10M。第二题：根据以下实验内容的要求认真填写实验报告，记录所有的实验用例。设有如下关系表S：S(NO,NAME,SEX,AGE,CLASS),主关键字是NO。其中NO为学号，char（2），学号不能为空，值是唯一的；NAME为姓名，char（10）,姓名的取值也唯一；SEX为性别，char（2）；AGE为年龄，int；CLASS为班号，char（5）。：创建表SUSE LimenCREATE TABLE S(Number char(2) Primary Key,NAME char(10) Unique,SEX char(2

24、),AGE int,CLASS char(5)：插入一个记录（25，“李明”，“男”，21，“95031”）再插入一个记录（10，“王丽”，“女”，20，“95101”）USE LimenINSERT S VALUES (25,'李明','男',21,'95031')INSERT S VALUES (10,'王丽','女',20,'95101')这两条语句是向S中插入两条记录，其中的记录必须满足定义的约束条件。 ：插入“95031”班学号为30，姓名为郑和的学生记录USE LimenINSERT S

25、 VALUES (30,'郑和','',20,'95031')此句命令是用于测试Ssex项输入为空是的结果。当然值毫无疑问是null，但是如果不输入值的话，那么值到底是什么呢？ ：对表S,按学号升序建立唯一索引（索引名为sno）；对表S，按年龄降序建立索引（索引名为sage） USE LimenGOCREATE UNIQUE INDEX sno ON S(Number)GOUSE LimenGOCREATE UNIQUE INDEX sage ON S(AGE DESC)GO此两句命令分别建立了唯一索引sno和sage，当然了不建立唯一索引，只建

26、立索引也是可以的。：向S表中添加“入学时间（comedate）”列，其数据类型为日期型USE LimenGOALTER TABLE S ADD comedate datetime NULLGO此句命y令添加了列comedate 且类型为datetime，可允许赋空值。：删除S表的sage索引USE LimenGODROP INDEX sage ON SGO通过MSDN查询到的删除索引的方法。：将年龄的数据类型改为smallintUSE LimenGOALTER TABLE S ALTER COLUMN AGE smallint NOT NULL GO：删除学生姓名必须为唯一值的约束USE Li

27、menGOALTER TABLE S DROP CONSTRAINT UQ_S_D9C1FA00023D5A04GO第二题：按照数据库系统概论（第四版）P82中的关系模式及数据，创建STUDENT、COURSE、SC表，并插入相应的数据，每一张表都必须有主键约束，分别试着使用列级完整性约束和表级完整性约束。第一步：建立STUDENT表格（使用列级完整性约束）USE LimenGOCREATE TABLE STUDENT(Sno char(10) primary key,Sname char(4) NOT NULL DEFAULT '',Ssex char(2) NOT NULL

28、 DEFAULT '',Sage smallint NULL,Sdept char(2)GO此命令中按照题设要求使用了列级完整性约束条件。第二步：建立COURSE表格USE LimenGOCREATE TABLE COURSE(Cno smallint Primary Key,Cname char(20),Cpno smallint NULL,Ccredit smallint NOT NULL DEFAULT 0,CONSTRAINT Cname_Check UNIQUE(Cname)GO此命令中使用了表级完整性约束条件Constraint Cname_CK Unique(Cname)第三步：建立SC表格（把Cno和Sno设置为表级完整性约束）USE LimenGOCREATE TABLE SC(Sno char(10),Cno smallint,Grade smallint N