实验二 数据表的创建和管理.pdf

《实验二 数据表的创建和管理.pdf》由会员分享，可在线阅读，更多相关《实验二 数据表的创建和管理.pdf（12页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、实验二实验二 数据表的创建和管理数据表的创建和管理【实验目的】【实验目的】（1）掌握数据表的相关理论知识（2）掌握利用 SQL 语句创建数据表的方法（3）掌握利用对象资源管理器创建数据表方法（4）掌握利用 SQL 语句操作数据表数据的方法（5）掌握利用对象资源管理器操作数据表数据的方法【实验要求】【实验要求】（1）复习所需理论知识（2）按照实验步骤的要求完成验证题目（3）按照自主设计题目要求完成数据表的创建（4）按照自主设计题目要求完成数据表的数据操作（5）以班级姓名文件夹形式提交完成的两种实验题目数据文件和日志文件（6）以班级姓名文件夹形式提交数据表创建和数据操作的SQL 语句【实验环境】【

2、实验环境】Windows XP 操作系统或 windows 2000/2003 操作系统，SQL Server 2005 数据库管理系统【实验步骤】【实验步骤】一、利用对象资源管理器（1）按照验证性实验题目的要求完成数据表的创建（2）按照验证性实验题目的要求完成对数据表数据的操作（2）按照自主设计实验题目的要求完成数据表的创建（4）按照自主设计实验题目的要求完成对数据表数据的操作（5）对自主设计实验题目和验证性实验题目创建相应的数据库关系图二、利用 SQL 语句（1）按照验证性实验题目的要求完成数据表的创建（2）按照验证性实验题目的要求完成数据表数据的操作（3）按照自主设计实验题目的要求完成数

3、据表的创建（4）按照自主设计实验题目的要求完成数据表的数据操作注意用到的SQL语句要分别保存， 并命名为shiyan2_1.sql，shiyan2_2.sql， shiyan2_3.sql，shiyan2_4.sql，shiyan2_5.sql 依此类推即可（只要是用到的SQL 语句就要保存）1【验证性实验题目】【验证性实验题目】实验所用素材：实验所用素材：student（学生表）列名SnoSnameSsexSageSdept列名cnocnameCpnoCcreditSc（选课）列名SnoCnoGrade数据类型CharCharDecimal长度886,2是否可空否否是主码是是否含义学号课号成

4、绩Course（课程表）数据类型CharCharCharInt长度8208是否可空否否是是主码是否否否含义课号课程名先行课号学分数据类型CharCharCharIntChar长度810420是否可空否否是是是主码是否否否否含义学号姓名性别年龄系别数据表中参考数据： （也可根据需要自己设计）Student 表中数据：Sno20110101201101022011010320110104201102012011020220110203201103012011030220110303Course 表中数据：Cno86050101860501028605020186050202860502038605

5、0204Sname孙晓赵孟州孙迪刘艳伟李地张亚楠褚云伟刘亮于小倩宋楠CnameSsex女男男女女女男男女女Sage19201918212019211918CpnoNULLNULLNULL860502018605020486050202SdeptCSCSCSCSISISISMSMSMSCcredit443444高等数学大学英语计算机基础C 语言数据库数据结构2860502058605020686050207Sc 表中数据：Sno操作系统数据处理信息系统Cno860502068605020186050203Grade87969887547644420110101201101012011010120

6、1101022011010220110103860501018605010286050201860502018605020286050202一、创建数据表并设置主、外键一、创建数据表并设置主、外键1 1、使用对象管理器、使用对象管理器【步骤 1】参考实验一，将 stud 数据库附加到相应的服务器中 （如已经在服务数据库结点中，则此步可以省略） 。【步骤 2】选择 stud 数据库结点下的表结点，在此结点上单击鼠标右键，出现弹出式菜单，在弹出式菜单中选择“新建表” ，出现如图 1 所示的设计表视图。图 1设计表界面【步骤 3】在图 1 所示的表设计视图中，按照 student 表的列名和具体列数

7、据类型和允许为空的要求添加 student 数据表的相应列。并在相仿相应的列属性最下面的“说明”中，注明列的含义。如图 2 所示。参照第一列的方法按照数据表要求添加所有列并设置相应 “说明” 。【步骤 4】 创建好 student 数据表后， 将光标停在 sno 列上， 然后选择 “对象资源管理器”上方的“设置主键”按钮（钥匙形态的按钮） ，这时在 sno 列前方就出现了小钥匙形态的图形，说明该列为主键列。如图3 所示。然后将表设计器关闭，会出现提示保存的界面，将表3的名称改为 student，然后保存即可。图 2设计表中某列的说明图 3设计表中某列为主键【步骤 5】参照 student 表的

8、创建方法，创建 course 表和 sc 表。注意 sc 表为双属性构成码，在选择两个属性列时需要使用shift 键和鼠标选择配合。三个表都创建好后（设置号主键） ，就可以设置相应的外键（外码）关系了。外键的设置主要是针对 sc 表和 course 与 student 表的关系而创建的， 它是为了保证表的参照完整性关系。【步骤 6】在数据库stud 中下打开“表”结点视图，在其中的dbo.sc 表上右击鼠标，在弹出式菜单中选择“修改表”打开表设计视图， 然后在“对象资源管理器” 上方选择“关系”按钮（ “主键”按钮右侧的按钮） ，出现如图 4 所示的外键关系界面。图 4设计表的外键关系【步骤

9、7】单击“添加”按钮，出现如图5 所示界面，在此界面单击“表和列规范”后的浏览按钮，打开如图 6 所示界面。选择主键表“student” ，并在其下方选择好“sno”属性，然后外键表中将“cno”属性去掉即可。如果需要改变主外键关系名， 直接从上方的关系名编辑框中修改即可， 这里就是用默认值了。然后单击“确定”按钮。这时student 表和 sc 表的主外键关系就建好了。4图 5单击添加按钮图 6选择表和列规范【步骤 8】 回到图 5 所示界面， 按照 【步骤 7】 接着添加 course 表和 sc 表的主外键关系，都设置好后，单击“关闭”按钮即可。2 2、使用、使用 SQLSQL 语言创建

10、数据表并设置主外键关系。语言创建数据表并设置主外键关系。在本次实验附加的 stud 数据库中利用 SQL 语言创建数据表，数据表的基本结构参见素材，注意表名分别为 student1，course1 和 sc1，并设置相应的主外键关系和列完整性（自定义完整性） 。【步骤 1】 单击 stud 数据库结点 （确保当前是在 stud 数据库中进行操作） ， 然后选择 “新建查询” ，在出现的 SQL 语言编辑环境中输入如下语句。CREATE TABLE STUDENT1CREATE TABLE STUDENT1- -创建创建 student1student1 表表(SNO CHAR(8) prima

11、ry key,(SNO CHAR(8) primary key,- -学号为主键学号为主键SNAME CHAR(8) NOT NULL,SNAME CHAR(8) NOT NULL,- -姓名姓名SSEX CHAR (4),SSEX CHAR (4),- -性别性别SAGE SMALLINT,SAGE SMALLINT,- -年龄年龄SDEPT CHAR(10);SDEPT CHAR(10);- -专业专业CREATE TABLE COURSE1CREATE TABLE COURSE1- -创建创建 course1course1 表表(CNO CHAR(8) NOT NULL,(CNO CHA

12、R(8) NOT NULL,- -课程号课程号CNAME CHAR(20) NOT NULL,CNAME CHAR(20) NOT NULL,- -课程名称课程名称CPNO CHAR(8),CPNO CHAR(8),- -先行课号先行课号CCREDIT SMALLINT,CCREDIT SMALLINT,- -学分学分PRIMARY KEY(CNO);PRIMARY KEY(CNO);- -课程号为主键课程号为主键CREATE TABLE SC1CREATE TABLE SC1- -创建创建 sc1sc1 表表(SNO CHAR(8) NOT NULL,(SNO CHAR(8) NOT NUL

13、L,- -学号学号CNO CHAR(8) NOT NULL,CNO CHAR(8) NOT NULL,- -课程号课程号5GRADE INT,GRADE INT,- -成绩成绩PRIMARY KEY(SNO,CNO),PRIMARY KEY(SNO,CNO),- -学号、课程号为主键学号、课程号为主键FOREIGN KEY(SNO) REFERENCES STUDENT(SNO),FOREIGN KEY(SNO) REFERENCES STUDENT(SNO),- -学号为外键学号为外键FOREIGN KEY(CNO) REFERENCES COURSE(CNO),FOREIGN KEY(CN

14、O) REFERENCES COURSE(CNO),- -课程号为外键课程号为外键CHECK(GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100);CHECK(GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100);- -成绩约束条件成绩约束条件【步骤 2】单击“执行”按钮，然后在 stud1 数据库结点上刷新一下，然后在下方表结点中会出现 student1、course1、sc1 表。【步骤 3】将 SQL 语句保存在指定目录下并命名为shiyan2_1.sql。使用 SQL 语句创建数据表应注意以下问题：（1）在 SQL S

15、erver2005中，SQL 语言加注释使用的是两个短横杠“-” ；（2）对于主码的设置，如果是单属性主码，则直接在属性后利用primary key 进行主码说明（属列级完整性） ，如果是多属性码，则需要单独进行主键说明（属表级完整性） ，如SC 表；（3）外键关系需要在外键表中进行说明，利用保留字foreign key 和 references；（4）表的创建语句只能执行一次，否则会出现同名表已经存在的错误，而且如果前面的表没有问题，而后面的表有问题，利用“执行”按钮后，前面的没问题的表会创建成功，而后面的表则创建失败，修改后再执行，则会出现表重名问题，所以建议对于初学者，将创所以建议对于初

16、学者，将创建表的语句分开来写，一个表一个建表的语句分开来写，一个表一个SQLSQL语句。语句。二、建立数据库关系图二、建立数据库关系图数据表创建好后，可以针对数据表建立数据库关系图， 已方便查看表间关系，具体方法如下：【步骤 1】在“对象资源管理器”下方的数据库结点中，找到要创建数据库关系图的数据库，这里选择 stud，然后展开 stud 数据库结点，在其下方找到“数据库关系图”结点，右击此结点，在出现大弹出式菜单中选择“新建数据库关系图” 。出现如图 7 所示界面。图 7 创建数据库关系图添加表图 8创建好的数据库关系图6【步骤 2】在图7 中，利用ctrl 键和鼠标配合将三个表都选上，然后

17、单击“添加”按钮，这时在“对象资源管理器”的右侧的关系图中就出现了如图 8 所示的三个数据库表关系图。然后关闭关系图界面，将关系图保存即可。三、为数据表输入数据三、为数据表输入数据/ /删除数据删除数据注意：注意： 数据表创建好后就可以输入数据了。 数据表输入数据的顺序应该是先输入student（student1）和 course（course1）的数据，最后在输入 sc（sc1）表的数据。因为 sc（sc1）表的数据要参考 student（student1）和 course（course1）表。1 1、使用对象资源管理器法、使用对象资源管理器法【步骤 1】在 stud 数据库结点下找到表结点

18、，如未展开则将其展开，找到 dbo.student表，右击此表，选择“打开表”命令，出现如图9 所示界面。图 9打开表为表输入数据界面【步骤 2】参照素材中所给数据或根据自己需要输入数据。注意：注意：主码不能相同。输入完后，单击“关闭”按钮即可完成保存功能，不会再有提示信息。【步骤 3】参照【步骤 1】和【步骤 2】为 course 和 sc 表输入数据。注意注意: :sc 表中的 sno 和 cno 属性的值在 student 和 course 中必须存在， 否则会不满足参照完整性关系。【步骤 4】如果想删除某一条信息要注意，sc 表中的信息删除一般不会出问题，但是student 和 cou

19、rse 表的删除就会有问题， 如果在 sc 表中有相应的参照信息则student 和 course表中的信息不能随便删除。要想删除信息在要删除信息上单击鼠标右键选择“删除”即可。2 2、使用、使用 SQLSQL 方法方法使用 stud 中的 student1 表为例进行说明。【步骤 1】在相应的数据库 stud 结点上单击，然后选择“新建查询”按钮，在出现的SQL 语句编辑环境中输入如下语句，然后单击“执行”按钮。7insert into student1insert into student1values(20021122,values(20021122,刘晨刘晨,女女,19,CS),19,

20、CS)删除信息使用的命令是：Delete from student1;Delete from student1;- -删除学生表全部信息删除学生表全部信息Delete from student1Delete from student1Where sno=Where sno= 2002112120021121 ; ;- -删除指定学生信息删除指定学生信息如果语句没有问题，在下方相应的消息框中会出现“ 1 行受影响”的字样，如果有错误则出现相应的错误信息。【步骤 2】 参考 【步骤 1】 为 student1 表插入更多数据， 并为 course1 和 sc1 表插入数据。使用 SQL 语句进行插

21、入和删除数据信息时应注意：（1）由于 insert 命令没有条件，所以每次只能插入一条数据，如果想插入多条数据就得使用多个 insert 语句。而 delete 命令删除数据一次可以删多条。（2）在用 insert 命令插入数据时，需要注意，要插入的数据个数与数据类型以及数据的先后顺序必须与表的结构完全一样，否则会出现错误。（3）利用 insert 命令插入数据非常灵活，可以插入一整条数据，也可以插入指定属性数据。如：insert into student1(sno,sname)insert into student1(sno,sname)values(values( 200211232002

22、1123 , , 赵琳赵琳 ) )（4）在插入数据时要确保操作的数据库为指定数据库（在处于SQL 编辑环境时，对象资源管理器上方有数据库选择的下拉钮，可根据需要进行选择） 。四、修改表结构四、修改表结构针对 stud 数据库的 student 表增加标识列，列名为 stuid，数据类型为 int 型。1 1、利用对象资源管理器方法、利用对象资源管理器方法【步骤 1】在数据库 stud 结点的表结点中找到 student 表，然后右击，选择“修改表”命令，打开表设计器。【步骤 2】将光标停在 sno 属性上，右击鼠标，在弹出式菜单中选择“插入列” ，这时会在 sno 属性列上方出现一个新的空列，

23、将新的空列属性名设置为stuid，数据类型为 int，不允许为空。【步骤 3】在下方的列属性中， 将 stuid 设置为标识列。 如图 10 所示，将标识规范展开，在下方的“是否标识”后面选择“是” ，将标识增量和标识种子都设成1。那么 stuid 列就会根据 student 表中元组的条数自动进行增1 操作，第一条的 stuid 为 1。8图 10 为表设置 identity 列（标识）2 2、利用、利用 SQLSQL 方法方法（1）在 stud 数据库的 student1 表中增加 stuid 列，数据类型为整型【步骤】选中相应的数据库 stud 结点，然后“新建查询” ，在 SQL 语言

24、的编辑环境中输入如下语句， 然后执行。 如果需要设置成标识， 再利用对象资源管理器方法进行设置即可。alter table student1 add stuid int identity;alter table student1 add stuid int identity;（2）在 stud 数据库的 course1 表中，将课程名称设置成唯一值的约束条件。【步骤】选中相应的数据库 stud 结点，然后“新建查询” ，在 SQL 语言的编辑环境中输入如下语句，然后执行。alter table course1 add unique(cname);alter table course1 add

25、unique(cname);如果还需要利用语句进行其他设置请参考教材进行实践即可。五、数据表索引的设置五、数据表索引的设置当数据表建立好后，一般就会将主键设置好， 一旦具有主键，主键就是一个聚簇索引和唯一索引，一个表只能有一个聚簇索引， 但可以有多个唯一索引。 索引是为了提高检索效率而设置的，但是在更新时如果表的索引过多则会使更新速度变慢。1 1、利用对象资源管理器为表、利用对象资源管理器为表 studentstudent 的的 snamesname 设置唯一索引。设置唯一索引。【步骤 1】在对象资源管理器下的相应数据库结点上找到表结点，并将其打开，在下面找到具体要设置索引的表，此时选择stu

26、dent，将此表结点展开，在下面找到“索引”选项，然后右击。如图 11 所示。图 11 为指定表创建索引【步骤 2】 在弹出式菜单中选择 “新建索引” ， 这时会打开如图 12 所示的新建索引窗口。9在此窗口中设置索引名称stuname，索引类型为“非聚簇” ，将“唯一”前面的复选框选上。【步骤 3】设置好索引的相关内容后， 需要指定要为哪一列设置索引， 这是需要在图 12中选择“添加”按钮，在打开的学生表相关列中选择sname 即可（如果是多列则选择多列） 。图 12 新建索引窗口【步骤 4】最后单击“确定”按钮，索引创建完毕。2 2、利用、利用 SQLSQL 方法为方法为 courseco

27、urse 的的 cnamecname 列设置唯一索引。列设置唯一索引。【步骤 1】在对象资源管理去中选好数据表所在的数据库结点，然后单击“新建查询”，在 SQL 编辑环境中输入如下命令：create unique index couname on course(cname)create unique index couname on course(cname)【步骤 2】单击“执行”按钮，命令完成后索引就创建好了。找到相应数据库结点的相关表的索引结点可以看到相应的索引信息。如果想为 sc 表建立 sno 升序 cno 降序的唯一索引可采用create unique index scno on

28、sc(sno asc,cno desc)create unique index scno on sc(sno asc,cno desc)3 3、利用对象资源管理器删除和修改索引、利用对象资源管理器删除和修改索引【步骤 1】在对象资源管理器下方的相应数据库结点中，找到相应的表结点，将其展开后找到要删除或修改索引的表，将表结点下的索引展开，找到要删除或修改的索引。【步骤 2】在要修改或删除的索引上单击鼠标右键，在弹出是菜单中选择相应操作，如果是要删除则直接选择删除， 如果是要修改则选择属性， 当修改时除了索引名不能修改， 其他都能改。请同学们自己实践，可以针对自己创建的索引进行修改和删除。4 4、

29、利用、利用 SQLSQL 删除索引删除索引删除索引的命令为drop indexdrop index 索引名。索引名。六、数据表的删除六、数据表的删除1 1、使用对象资源管理器、使用对象资源管理器10数据表删除是找到相应数据库结点后找到相应的数据表， 在要删除的数据表上单击鼠标右键，在弹出式菜单中选择删除即可。2 2、使用、使用 SQLSQL 命令命令找到相应的数据库结点， 然后单击 “新建连接” ， 打开 SQL 编辑器， 然后输入如下命令：Drop tableDrop table 表名表名; ;注意：如果使用命令对数据表的插入、删除、修改、建索引等操作，都必须明确表所在的数据库， 也就是在对

30、象资源管理器中找到相应的数据库结点并选中后， 再单击 “新建查询”打开 SQL 编辑环境，进行语句编辑和执行。当然也可以在打开 SQL 编辑环境后，在对象资源管理器上方的数据库下拉列表中选择相应数据库。【自主设计实验题目】【自主设计实验题目】一、附加实验一创建的数据库ck。二、针对第一次作业中的相应关系模式设计具体的二维表。关系模式为：供应商（供应商号、姓名、地址、电话号码、账号）仓库（仓库号、面积、电话号码）职工（职工号、姓名、年龄、职称、仓库号、是否领导（或领导职务） ）项目（项目号、预算、开工日期）零件（零件号、名称、规格、单价、描述）供应（供应商号、项目号、零件号、供应量）库存（仓库号

31、、零件号、库存量）二维表设计为：（1）gys 表列名GyshGyxmGydzhGydhGyzhh（2）ck 表列名CkhCkmjCkdh（3）zg 表列名数据类型及长度是否主码是否可空说明数据类型及长度Char（10）Decimal（10,2）Char（12）是否主码是否否是否可空否是是说明仓库号面积电话号码数据类型及长度Char（10）Char（10）Char（40）Char（12）Char（20）是否主码是否否否否是否可空否否是是是说明供应商号姓名地址电话号码账号11ZghZgxmZgnlZgzchCkhZgld（4）xm 表列名XmhXmysXmkg（5）lj 表列名LjhLjmchLj

32、ggLjdwLjmsh（6）gy 表列名GyshXmhLjhGyl（7）kc 表列名CkhLjhKclChar（10）Decimal（10,2）SmallintChar（20）Char（10）Char（4）是否否否否否否否否是是是职工号姓名年龄职称仓库号是否领导数据类型及长度Char（10）Decimal（10,2）Date是否主码是否否是否可空否是是说明项目号预算开工日期数据类型及长度Char（10）Char（20）Char（20）Char（4）Varchar（100）是否主码是否否否否是否可空否否是是是说明零件号名称规格单位描述数据类型及长度Char（10）Char（10）Char（10）

33、Int是否主码是是是否是否可空否否否是说明供应商号项目号零件号供应量数据类型及长度Char（10）Char（10）Int是否主码是是否是否可空否否是说明仓库号零件号库存量三、在 SQL Server2005环境中，使用对象资源管理器创建表（1）-（4） ，利用 SQL 语言创建表（5）-（7） （命名规则按照前面的标号继续类推） 。注意：在创建表的过程中要建好相应的主外键。四、为部分数据表输入模拟数据，注意主键、外键限制和数据类型长度限制。五、创建数据库关系图，命名为diagram_ck。六、为表 zg 表添加标识列，并设置标识规范，标识种子为1，增量为 1。七、为 lj 表的 ljmch（零件名称列）创建唯一索引非聚簇索引。12