春数据库实验表的创建管理和数据操纵样本.doc

实验三 表创立、管理和数据操纵(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学号字符串，长度为8主码Sname姓名字符串，长度为10非空Ssex性别字符串，长度为2男或 女Sage年龄整数1545Sdept所在系字符串，长度为20默以为计算机系表3-2 Course表构造列名阐明数据类型约束Cno课程号字符串，长度为8主码Cname课程名字符串，长度为20非空Cpno先修课程号字符串，长度为8Ccredit学分整数取值：16Semster学期整数取值：18Period学时整数取值：0表3-3 SC表构造列名阐明数据类型约束Sno学号字符串，长度为8主码，引用student外码Cno课程号字符串，长度为8主码，引用course外码Grade成绩整数取值：01001运用公司管理器创立表 （1）打开公司管理器。（2）选中实验一创立好数据库stu_DB，单击数据库中表对象，然后右击窗口右侧选取新建表，弹出如图所示窗体。图3-1 新建表窗体（3）在这个窗体中，列名列就示表字段名，可以在这个窗体中为字段选取数据类型和长度以及与否可觉得空值。（4）在此处可觉得表选取文献组，在表中单击右键，选取属性，如果已经为数据库创立了一种次要组，可以在属性窗体表文献组中为此表选取文献组。应当把某些竞争使用表放于不同文献组中，并且让文献组属于不同磁盘，这样可以在表竞争读写时提高并发性能。（5）创立完毕后，单击磁盘图标，并为表取一种名字。注意，应当为表取一种故意义名字。2用Transact_SQL语句创立表 语法为： 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 getdate() )注意该列有什么特殊性？图3-3 查询分析器窗体创立表 请参照例1和例2，完毕如下操作：(1)按前面所给表构造创立表Course和SC。(2)自行设计表构造，用SQL语句建立第二章习题5中4个表。 (二)表修改1 用公司管理器修改表 右键点击所要修改学生表，选取“设计表”，会弹出如图3-4所示窗体，在这个窗体中可以更改数据表字段。在空白处点右键，选取“属性”，弹出另一窗体，在此可以更改约束，也可以增长约束。图3-4 修改表窗体（2）运用Transact-SQL语句修改表l 向表中添加新字段：在学生表中添加一种“班级”字段，数据类型为字符型。ALTER TABLE student ADD class CHAR(6) l 删除表中旧列：将学生表中“Sdept”字段删除。ALTER TABLE student DROP COLUMN Sdeptl 更改表以添加具备约束列。给学生表增长“grade”字段并加上CHECK约束，让其不可以不不大于100。ALTER TABLE student ADD grade int CONSTRAINT ch_grade CHECK(grade100)EXEC sp_help ch_grade给学生表中添加“birthday”字段，并且这个日期不能在录入当天日期之后。ALTER TABLE student ADD birthday DATETIME NULLCONSTRAINT ch_birthday CHECK(birthdaygetdate()l 添加具备默认值可为空列：在学生表中加入“matriculationday”字段，并且这一字段默认值为录入当天日期。ALTER TABLE student ADD matriculationday smalldatetime NULLCONSTRAINT adddateflt DEFAULT getdate() 请参照上面所给例子：(1) 自行设计表构造修改操作（不少于8个语句），并进行实验验证。(2) 修改表student，成果如表3-4。(3) 创立表3-5、表3-6，为后续实验建立基本。表3-4 更新后student表构造 列名阐明数据类型约束Sno学号字符串，长度为8主码Sname姓名字符串，长度为10非空Ssex性别字符串，长度为2男或 女Sdept所在系字符串，长度为20默以为计算机系Sbirth出生日期日期自定义sclass班级字符串，长度为20sremark评语变长字符串，长度为100saddress家庭住址变长字符串，长度为40szipcode邮编字符串，长度为6sphone 电话字符串，长度为15semail电子邮箱变长字符串，长度为40表3-5 Teacher表构造 列名阐明数据类型约束tno工号字符串，长度为8主码tname姓名字符串，长度为10非空tsex性别字符串，长度为2男或 女tdept所在系字符串，长度为20默以为计算机系tbirth出生日期日期自定义tposition职称字符串，长度为12tsalary工资整数取值：010000texperience简历变长字符串，长度为200tamount工作量整数取值：01000tphone 电话字符串，长度为15temail电子邮箱变长字符串，长度为40taddress家庭住址变长字符串，长度为40表3-6 TC表构造列名阐明数据类型约束Tno工号字符串，长度为8主码，引用Teacher外码Cno课程号字符串，长度为8主码，引用Course外码Classroom教室变长字符串，长度为30(三)表删除可以在公司管理器中选取要删除表直接删除，也可以通过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_DBEXEC sp_addtype newtype2，CHAR(6)，not null3.命名顾客自定义数据类型 使用系统存储过程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和DELETEINSERT语句向student表中插入一行数据，详细数据如下：学号：04265005，姓名：刘辉，性别：男,年龄：21，系别：计算机系USE stu_DBINSERT 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个语句），并进行实验验证，验证规定如下：l 不满足唯一性约束数据插入。l 不满足顾客自定义约束数据插入。l 不满足外键约束数据插入。l 运用缺省值数据插入。l 不合理数据与否能进入数据库。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表：SnosnameSbirthdaySdept04261001王冬梅1982.02.07计算机系04261002王田田1982.03.04计算机系04261003吴畏1982.06.06计算机系04261004马丽1982.04.03计算机系04261005杨乾坤1982.03.23计算机系0426刘乐1983.01.16计算机系0426夏天1983.02.08计算机系0426杨致远1982.07.22计算机系04561001陶然1982.01.12数学系04563001姚远1982.03.21数学系course表：CnoCnameCpnoCreditC001数据库原理C0053C002高等数学4C003管理信息系统C0012C004操作系统C0063C005数据构造C0073C006计算机文化基本2C007C语言程序设计C0063score表：SnoCnoScore04261001C0019004261002C0018504261003C0017304261004C0019804261005C001960426C001970426C001830426C0018504561001C0079204563001C00797teacher表：TnoTnameTsexDepartmentT001石云丹女计算机系T002罗莉女计算机系T003王国强男计算机系T004吴栋男计算机系T005高鸿轩男数学系T006张怀良男数学系T007刘晓伟男数学系T108马莉莲女物电学院TC表：TnocnoclassroomT001C00522号楼405T002C0077号楼406T003C00117号楼504T004C00617号楼401T005C0048号楼201T006C0028号楼202【思考题】1.表Student 、Course 、SC应按何种顺序删除？为什么？2.正在被表或其她数据库对象使用顾客定义类型能不能删除？为什么？