数据库实验报告

1、实验二 数据定义一、实验目的1、掌握使用SQL语句创建和删除数据库；2、掌握使用SQL语句创建和删除数据表，创建各种完整性约束，修改表的结构；3、掌握索引的创建和删除方法。二、预备知识1、数据库的建立与删除可以使用SQL语句建立数据库，语句格式为：CREATE DATABASE <数据库名>其中CREATE DATABASE为关键字，<数据库名>为用户创建的数据库的名称，由用户自定。使用DROP DATABASE 语句可以删除数据库，语句格式为：DROP DATABASE <数据库名>其中DROP DATABASE为关键字，<数据库名>为要删除的

2、数据库的名称。当数据库被删除时，数据库中的所有数据对象也一起被删除掉。2、数据表的创建与删除使用Create Table语句创建数据表，语句格式为：CREATE TABLE <表名>（<列名> <数据类型> 列级完整性约束条件,<列名> <数据类型> 列级完整性约束条件,<表级完整性约束条件>）;其中CREATE TABLE为关键字，<表名>为数据表的名称，由用户确定，<列名>为组成这个表的属性列的名称，由用户确定，<数据类型>为属性列的取值的类型，由用户在具体的DBMS所提供的数据类型

3、中选择。使用Drop Table 语句删除数据表，语句格式为：DROP TABLE <表名>注意：删除一个表，表的定义以及表中的所有数据，以及该表的索引、约束、触发器等将自动删除，并且与表相关联的规则和默认对象也将失去与它的关联关系。3、修改基本表使用ALTER TABLE语句修改已建立好的基本表，该语句可以实现添加、修改、删除基本表中的属性列、约束等操作，下面分别给出语句格式及说明：（1）添加操作在ALTER TABLE语句中使用ADD子句添加新列和新的完整性约束，添加新列的语句格式如下：ALTER TABLE <表名> ADD <新列名> <数据类

4、型>添加新的完整性约束语句格式如下：ALTER TABLE <表名> ADD <完整性约束规则>（2）修改操作在ALTER TABLE语句中使用ALTER COLUMN子句修改原有列的定义，语句格式如下：ALTER TABLE <表名> ALTER COLUMN <列名> <数据类型>（3）删除操作在ALTER TABLE语句中使用DROP子句删除已有的列和完整性约束条件，删除列的语句格式如下：ALTER TABLE <表名> DROP COLUMN <列名>删除完整性约束条件的语句格式为：ALTER T

5、ABLE <表名> DROP <约束名称>关于ALTER TABLE语句更详细的语法格式请参照“联机丛书”，下面通过实例给出ALTER TABLE语句的具体用法，如下： 操作类型实例添加添加新列Alter table s add Sid char(20) 添加新的完整性约束Alter table s add unique(sid)修改修改原有列的定义Alter table s alter column Sid char(30)删除删除已有完整性约束Alter table s drop uq_sid删除已有列Alter table s drop column Sid4、索

6、引的创建与删除使用CREATE INDEX语句创建索引，语句格式为：CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX <索引名称> ON <表名>（ 列名 ASC | DESC ,.n ） 其中UNIQUE、CLUSTERED、NONCLUSTERED 指出所建索引的类型，分别为唯一性索引、聚集索引和非聚集索引。注意：不能在数据类型为text、ntext和image的列上建立索引。使用DROP INDEX语句删除索引，语句格式为：DROP INDEX <表名.索引名>注意：Drop Index语句能够删除Create

7、Index语句所建立的索引，但是它不能删除SQL Sever为Primary Key或Unique约束所建立的索引，这些索引只能通过删除约束或表的方法来删除。三、实验内容（一）数据库的建立使用CREATE DATABASE语句创建名为“gongcheng”的数据库。1.建立数据库，点击新建查询，输入语句create database gongcheng点击刷新，就新建了一个数据库gongcheng（二）数据表操作1建立数据表在创建的“gongcheng”数据库中使用SQL语句建立4个关系（可参考课本P74习题5），如下：供应商表S （Sno,Sname,Status,Ctiy）零件表P（Pno

8、,Pname,Color,Weight）工程项目表J（Jno,Jname,City）供应情况表 SPJ（Sno,Pno,Jno,QTY）要求：（1）创建每个关系的主键，有外键的创建外键。（2）S表中的Sname属性列的取值唯一（3）P表中weight属性列的取值范围在1-50之间（4）J表中的Jname取值不能为空并且是唯一的（5）SPJ表中QTY属性列的数据类型必须为intcreate table S (Sno varchar(15) primary key,Sname varchar(20) unique,Status varchar(10),Ctiy varchar(10)create

9、table P (Pno varchar(15) primary key,Pname varchar(20),Color varchar(10),Weight int check(1<weight and weight<50)create table J (Jno varchar(15) primary key,Jname varchar(20) unique not null,City varchar(10)create table SPJ (Sno varchar(15) references S(Sno) ,Pno varchar(15) references P(Pno)

10、,Jno varchar(15) references J(Jno),QTY int,primary key(Sno,Pno,Jno) )J表：P表：S表：SPJ表：2修改表的结构用SQL语句完成以下操作：（1）给S表增加Sphone和Semail两个属性列，分别用来存放供应商的联系电话和电子信箱。alter table S add Sphone varchar(20);alter table S add Semail varchar(20);（2）删除Jname属性列取值唯一的约束。alter table J drop UQ_J_5A1986B60DAF0CB0;（3）将QTY属性列的数据类

11、型修改为Smallint型。alter table SPJ alter column QTY smallint;（4）删除S表中的属性列Semailalter table S drop column Semail;3创建、删除索引通过SQL语句分别在表S、P、J表中的Sno，Pno，Jno属性列上建立唯一索引（升序）create unique index SSno on S (Sno);create unique index PPno on P(Pno);create unique index JJno on J(Jno);4数据录入操作在EXCEl中录入四个表的数据（具体数据可参照课本P75

12、四个表中的数据），用数据导入功能将EXCEL中的数据分别导入到S，P，J和SPJ表中。在gongcheng上右击鼠标，选择任务中的导入数据服务器名称为你所在计算机的属性名。选择对应的数据表如下图所示如下图，单击完成即可导入数据：导入的数据户如下图所示：5备份gongcheng数据库。把鼠标移至gongcheng，单击鼠标右键，选择任务中的备份，选择你所要备份的路径，点击确定即可完成。（三）实验总结：通过本次试验，掌握了使用SQL语言来建立数据库、建立表、以及学习其他相关的操作，本次实验遇到的问题有：对相关操作不是很熟悉（1） 删除属性的取值的唯一约束不会，正确代码应为：alter table

13、J drop UQ_J_5A1986B61920BF5C，其中UQ_J_5A1986B61920BF5C是此属性的键值。通过查询资料和询问同学，我解决了这个问题（2） 数据导入不太熟悉，通过上网查询资料和老师的耐心讲解问题已解决；（3） 数据恢复后不知道如何寻找之前写入SQLQuery语句，现在已解决。通过此次实习，我对SQL server更进一步的认识，要想学好，今后还得多多练习。实验三 数据查询一、实验目的掌握查询语句的使用方法，重点掌握连接查询和嵌套查询，理解查询的执行过程。二、预备知识查询语句的基本格式如下：SELECT <目标列名序列> -需要哪些列 FROM <数

14、据源> -来自于哪些表 WHERE <检索条件> -根据什么条件 GROUP BY <分组依据列> -分组 HAVING <组提取条件> -筛选 ORDER BY <排序依据列> -排序三、实验内容1、还原实习二中创建的“gongcheng”数据库和实习一中创建的“学生选课管理”数据库。2、在“gongcheng”数据库中完成以下查询：（1）查询所有供应商所在的城市。（2）查询零件重量在10-20之间（包括10和20）的零件名和颜色。（3）查询工程项目的总个数。（4）查询所有零件的平均重量。（5）查询供应商S3供应的零件号。（6）查询各个供

15、应商号及其供应了多少类零件。（7）查询供应了2类以上零件的供应商号。（8）查询零件名以“螺”字开头的零件信息。（9）查询工程项目名中最后一个字为“厂”字的工程项目所在的城市。（10）查询给每个工程供应零件的供应商的个数。（11）查询供应数量在10002000之间（包括1000和2000）的零件名称。（12）完成课本P127页第5题的前七个小题。找出所有供应商的姓名和所在的城市找出所以零件的名称、重量、颜色找出使用供应商S1所供应零件的名称及数量找出工程项目J2使用的各种零件的名称及数量找出上海厂商供应的所有零件号码找出使用上海产的零件的工程名称找出没有使用天津产的零件的工程项目3、在“学生选课

16、管理”数据库中完成以下查询：（1）查询是“选修课”的课程号、课程名。（2）查询学号为“09105103”学生所学课程的课程名与学分。（3）查询选修课程号为“002”或“004”的学生的学号。（4）查询选修了课程号为“002”和“004”的学生的学号。（5）查询学习全部课程的学生姓名。SQLQuery查询语句如下：select jno,cityfrom Jselect Pname,colorfrom Pwhere Weight between 10 and 20select COUNT(Jno)from Jselect AVG(weight)from Pselect Pnofrom SPJwhe

17、re Sno='S3'select distinct Sno ,COUNT(Pno)from SPJgroup by Snoselect Snofrom SPJgroup by Snohaving COUNT(Jno)>2select *from Pwhere Pname like'螺%'select Jname,cityfrom Jwhere Jname like'%厂'select Jno, COUNT(distinct Sno)from SPJ group by Jnoselect pname from P where Pno in

18、( select pno from spj group by pno having sum(qty) between 1000 and 2000) select Sname,S.Ctiy from S select pname,color,weight from P select distinct Jno from SPJ where Sno ='S1' select distinct Pname,weight from P,SPJ where Jno='J2' select distinct Pno from SPJ where Sno IN( select

19、Sno from S where City ='上海') select Jname from J where Jno IN( select Jno from SPJ where Sno IN ( select Sno from s where City='上海') select Jno from J where Jno IN( select Jno from SPJ where Sno IN ( select Sno from s where City!='天津') select cno,cname from C where Property=&

20、#39;选修' select cname,credit from C,SC,swhere SC.Sno=s.sno and C.Cno=SC.Cno and s.sno='09105103' select sno from sc where Cno='002' union select sno from sc where Cno='004' select distinct fir.sno from SC fir,SC secwhere fir.Cno='002' and sec.Cno='004' sele

21、ct sname from s where not exists( select * from C where not exists( select * from C,s,SC where s.Sno=SC.Sno and C.cno=SC.cno)（三）实验总结这次实验是比较纠结的一次，当中也遇到了不少问题，问题主要有： 查询和添加的时候由于粗心很多时候属性名都是大小写不分，所以后来会发现查询没有结果或者出现语法错误； 没有考虑查询结果是否出现重复，所以要视情况添加distinct关键字 最后，尤其关键的是嵌套查询的多层嵌套掌握的还不理想，有时候会看着语句一头雾水，完全不知道是干嘛的。这个还

22、是需要慢慢思考解决吧这次实验应该是工作量比较大的一次，但是，也很有意思，你要解决的是一个个实际的问题，当结果出来的那一刻心情还是很兴奋滴。当然，实习中间自己也暴漏出来不少的知识性的和自身的缺点，这个我会认真反思努力改正的实验四 数据更新与视图一、实验目的1、掌握数据更新语句的使用；2、掌握视图操作的基本方法和应用，理解基于视图的查询和数据更新操作的过程。二、预备知识1数据更新基本语句及其格式（1）插入操作SQL的数据插入语句INSERT有两种形式： 插入单个元组，使用命令：INSERT INTO 基本表名 (列名表) VALUES (元组) 插入子查询的结果：INSERT INTO 基本表名（

23、列表名）查询语句（2）修改操作当需要修改指定关系中元组的值时，可使用下列语句实现：UPDATE 基本表名SET 列名1值表达式1，列名2值表达式2WHERE 条件表达式（3）删除操作删除关系中满足条件的元组语句格式如下:DELETE FROM <表名>Where <条件表达式>三、实验内容1在实习二创建的“gongcheng”数据库中使用SQL语句完成以下操作：（1）现有一供应商，代码为S9、姓名为英特尔、所在城市西安，供应情况如下：供应零件P5给工程J7数量为600，供应零件P4给工程J4数量为500，请将此供应商的信息和供应信息插入数据库（2）请将北京供应商的供应数

24、量加20（3）零件P3已经停产，请将P3的相关信息从数据库中删除。（4）把全部红色零件的颜色改成蓝色。（5）由S5供给J4的零件P6改为由S3供应，请作必要的修改。（6）请将（S2,J6,P4,200）插入供应情况表。（7）创建零件名为螺丝刀的供应情况的视图P_ls，包括供应商名（Sname），零件名（Pname），零件重量（Weight），工程项目代码（Jno），供应数量（QTY）。（8）在视图P_ls中查询供应数量为500的供应商姓名。（9）请为三建工程项目建立一个供应情况的视图，包括sno，pno和qty。针对该视图找出供应商S1的供应情况。2在实习一创建的“学生选课管理”数据库中使用S

25、QL语句完成以下操作：（1）学生孙天要退学，请删除该学生的所有记录。（2）将学号为09105103学生的所有课程成绩加10分。（3）学校开了一门新课，课称号为056课程名为信息系统分析，请将此门课程的信息插入。（4）创建查询每个学生的修课学分的视图，要求列出学生学号及总学分（sum（ccredit）。（5）在创建的视图中查询学生覃祥和韩振兴所修的学分数。SQLQuery查询语句如下：insertinto S(Sno,Sname,city)values('S9','英特尔','西安')insertinto SPJ values('S9

26、9;,'p5','j7',600)insertinto SPJvalues('S9','p4','j4',500)update SPJset QTY=QTY+20where sno in(select Snofrom swhere s.city='北京')delete from SPJwhere Pno='p3'delete from pwhere Pno='p3'update Pset Color='蓝'where Color='红'update SPJset sno='S3'where sno='s5' and Jno='j4' and Pno='p6'insertinto SPJvalues('s2','p4','J6',200)gocreate view P_1sasselect Sname,Pname,Weig