数据定义与操作

信息工程学院1

数据库原理与应用(SQL Server 2008) 实验/实习报告

一、实验目的

1、掌握使用SQL语句创建和删除数据库；

2、掌握使用SQL语句创建和删除数据表，创建各种完整性约束，修改表的结构；

3、掌握索引的创建和删除方法。

4、掌握查询语句的使用方法，重点掌握连接查询和嵌套查询，理解查询的执行过程。

5、掌握数据更新语句的使用；

6、掌握视图操作的基本方法和应用，理解基于视图的查询和数据更新操作的过程。

二、预备知识

1、数据库的建立与删除

可以使用SQL语句建立数据库，语句格式为：

CREATE DATABASE <数据库名>

其中CREATE DATABASE为关键字，<数据库名>为用户创建的数据库的名称，由用户自定。

使用DROP DATABASE 语句可以删除数据库，语句格式为：

DROP DATABASE <数据库名>

其中DROP DATABASE为关键字，<数据库名>为要删除的数据库的名称。当数据库被删除时，数据库中的所有数据对象也一起被删除掉。

2、数据表的创建与删除

使用Create Table语句创建数据表，语句格式为：

CREATE TABLE <表名>（<列名> <数据类型> [列级完整性约束条件]

[,<列名> <数据类型> [列级完整性约束条件]]

…

[,<表级完整性约束条件>]）;

其中CREATE TABLE为关键字，<表名>为数据表的名称，由用户确定，<列名>为组成这个表的属性列的名称，由用户确定，<数据类型>为属性列的取值的类型，由用户在具体的DBMS所提供的数据类型中选择。

使用Drop Table 语句删除数据表，语句格式为：

注意：删除一个表，表的定义以及表中的所有数据，以及该表的索引、约束、触发器等将自动删除，并且与表相关联的规则和默认对象也将失去与它的关联关系。

3、修改基本表

使用ALTER TABLE语句修改已建立好的基本表，该语句可以实现添加、修改、删除基本表中的属性列、约束等操作，下面分别给出语句格式及说明：

（1）添加操作

在ALTER TABLE语句中使用ADD子句添加新列和新的完整性约束，添加新列的语句格式如下：

ALTER TABLE <表名> ADD <新列名> <数据类型>

添加新的完整性约束语句格式如下：

ALTER TABLE <表名> ADD <完整性约束规则>

（2）修改操作

在ALTER TABLE语句中使用ALTER COLUMN子句修改原有列的定义，语句格式如下：

ALTER TABLE <表名> ALTER COLUMN <列名> <数据类型>（3）删除操作

在ALTER TABLE语句中使用DROP子句删除已有的列和完整性约束条件，删除列的语句格式如下：

ALTER TABLE <表名> DROP COLUMN <列名>

删除完整性约束条件的语句格式为：

ALTER TABLE <表名> DROP <约束名称>

关于ALTER TABLE语句更详细的语法格式请参照“联机丛书”，下面通过实例给出ALTER TABLE语句的具体用法，如下：

操作类型实例

添加

添加新列A lter table s add Sid char(20) 添加新的完整性约束A lter table s add unique(sid)

修改修改原有列的定义A lter table s alter column Sid char(30)

删除删除已有完整性约束A lter table s drop uq_sid 删除已有列A lter table s drop column Sid

4、索引的创建与删除

使用CREATE INDEX语句创建索引，语句格式为：

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX <索引名称>

ON <表名>（列名[ ASC | DESC ] [ ,...n ] ）

其中UNIQUE、CLUSTERED、NONCLUSTERED 指出所建索引的类型，分别为唯一性索引、聚集索引和非聚集索引。

注意：不能在数据类型为text、ntext和image的列上建立索引。

使用DROP INDEX语句删除索引，语句格式为：

注意：Drop Index语句能够删除Create Index语句所建立的索引，但是它不能删除SQL Sever 为Primary Key或Unique约束所建立的索引，这些索引只能通过删除约束或表的方法来删除。5、查询语句的基本格式如下：

SELECT <目标列名序列> --需要哪些列

FROM <数据源> --来自于哪些表

[WHERE <检索条件>] --根据什么条件

[GROUP BY <分组依据列>] --分组

[HAVING <组提取条件>] --筛选

[ORDER BY <排序依据列>] --排序

6、数据更新基本语句及其格式

（1）插入操作

SQL的数据插入语句INSERT有两种形式：

①插入单个元组，使用命令：

INSERT INTO 基本表名(列名表)

VALUES (元组)

②插入子查询的结果：

INSERT INTO 基本表名（列表名）

查询语句

（2）修改操作

当需要修改指定关系中元组的值时，可使用下列语句实现：

UPDATE 基本表名

SET 列名1＝值表达式1［，列名2＝值表达式2…］

［WHERE 条件表达式］

（3）删除操作

删除关系中满足条件的元组语句格式如下:

DELETE FROM <表名>

Where <条件表达式>

三、实验内容

（一）数据库的建立

使用CREATE DATABASE语句创建名为“gongcheng”的数据库。

（二）数据表操作

1．建立数据表

在创建的“gongcheng”数据库中使用SQL语句建立4个关系（可参考课本P74习题5），如下：供应商表S （Sno,Sname,Status,Ctiy）

写完这个语句后，我已经按了执行，但是在左上角那没选中“gongcheng”这个数据库，所以出现了以下错误。把代码剪切下来，然后在左边列表把S表删除，再重新粘贴改代码，选中“gongcheng”数据库，这时候执行就没问题了。

S表建成：

零件表P（Pno,Pname,Color,Weight）

P表创建完成：

工程项目表J（Jno,Jname,City）

J表创建完成：

供应情况表SPJ（Sno,Pno,Jno,QTY）

SPJ表创建完成：

要求：

（1）创建每个关系的主键，有外键的创建外键。

（2）S表中的Sname属性列的取值唯一

（3）P表中weight属性列的取值范围在1-50之间

（4）J表中的Jname取值不能为空并且是唯一的

（5）SPJ表中QTY属性列的数据类型必须为int

2．修改表的结构

用SQL语句完成以下操作：

（1）给S表增加Sphone和Semail两个属性列，分别用来存放供应商的联系电话和电子信添加完成：

（2）删除Jname属性列取值唯一的约束。

删除完成：

（3）将QTY属性列的数据类型修改为Smallint型。

修改成功：

（4）删除S表中的属性列Semail

3．创建、删除索引

通过SQL语句分别在表S、P、J表中的Sno，Pno，Jno属性列上建立唯一索引（升序）

索引创建完成：

4．数据录入操作

在EXCEl中录入四个表的数据（具体数据可参照课本P75四个表中的数据），用数据导入功能将EXCEL中的数据分别导入到S，P，J和SPJ表中。

5．备份gongcheng数据库。

6.还原实习二中创建的“gongcheng”数据库和实习一中创建的“学生选课管理”数据库。

7.在“gongcheng”数据库中完成以下查询：

（1）查询所有供应商所在的城市。

select distinct city from S

（2）查询零件重量在10-20之间（包括10和20）的零件名和颜色。

select Pname,color from P where Weight between 10 and 20

（3）查询工程项目的总个数。select COUNT(distinct Jno)from J

（4）查询所有零件的平均重量。select AVG(Weight)from P

（5）查询供应商S3供应的零件号。

select pno from SPJ where Sno='S3'

（6）查询各个供应商号及其供应了多少类零件。select Sno,COUNT(distinct Pno)from SPJ group by Sno

（7）查询供应了2类以上零件的供应商号。

select Sno from SPJ group by Sno having COUNT(*)>2

（8）查询零件名以“螺”字开头的零件信息。

select Pno,Pname,Color,Weight from P where Pname like'螺%'

（9）查询工程项目名中最后一个字为“厂”字的工程项目所在的城市。select Jname,city from J where Jname like'%厂'

（10）查询给每个工程供应零件的供应商的个数。

select Jno,COUNT(distinct Sno)from SPJ group by Jno

（11）查询供应数量在1000—2000之间（包括1000和2000）的零件名称。select Pname from P where Pno in(select Pno from SPJ group by Pno

having sum(QTY)between 1000 and 2000)

（12）完成课本P127页第5题的前七个小题。select Sname,city from S

select Pname,color,weight from P

select Jno from SPJ where Sno='S1'

select Pname,QTY from P,SPJ where P.pno=SPJ.pno and SPJ.Jno='j2'

select distinct Pno from SPJ,S where SPJ.Sno=S.Sno and S.City='上海'

select distinct Jname from J,Spj,S where J.Jno=Spj.Jno and S.Sno=Spj.Sno and S.City='上海'

Select distinct(Jno)from J Where Jno not in(select Jno from SPJ,S Where S.Sno=SPJ.Sno and S.City='天津')

8.在“学生选课管理”数据库中完成以下查询：

（1）查询是“选修课”的课程号、课程名。

Select https://www.360docs.net/doc/674802512.html,o,https://www.360docs.net/doc/674802512.html,ame from c,sc where https://www.360docs.net/doc/674802512.html,o=https://www.360docs.net/doc/674802512.html,o

（2）查询学号为“09105103”学生所学课程的课程名与学分。select Sno,Cname,Credit from S,C where Sno='09105103'