数据库实验指导

1、实验目的

(1)熟悉SQL Server 2000的安装步骤，了解SQL Server 2000的主要组件。

(2)掌握启动和停止SQL Server 服务的方法

(3)掌握注册SQL Server 服务器的方法

(4)掌握创建服务器组的方法

2、实验步骤

(1)检查软硬件配置是否达到SQL Server 2000 的安装要求

(2)选择安装SQL Server 2000的方式

(3)安装前要做的准备工作

(4)安装SQL Server 2000

(5)练习用不同的方法启动SQL Server服务

(6)新建、删除服务器组

(7)注册服务器

(8)断开与恢复同服务器的连接

(9)删除SQL Server 注册

(10)打开查询分析器的方法

实验2：用T-SQL和企业管理器创建表

1、实验目的

(1)熟悉各种数据类型

(2)能够用T-SQL语句和企业管理器正确创建表

(3)了解6种常用的数据完整性约束，学会用企业管理器设置常用的数据完整性约束

2、实验步骤

(1)使用T-SQL语句，创建SM数据库中的学生表Student。

打开查询分析器，输入如下SQL语句。

    CREATE TABLE Student 

    ( Sid INT IDENTITY(1,1),

     SNo CHAR(5) PRIMARY KEY,

     CLno CHAR(6),

     SName VARCHAR(8)NOT NULL,

     SSex CHAR(2) CHECK(SSex IN(‘男’,’女’)),

     SBir DATETIME,

     SAge INT    )

按F5键或单击工具栏上的►图标，可以看到建表语句被成功执行

(2)创建SM数据库中的课程表Course。

打开查询分析器，输入以下SQL语句

    CREATE TABLE Course 

    (CNo CHAR(5)CONSTRAINT pk_CNO PRIMARY KEY,

     CName VARCHA(50)UNIQUE,

     CCredits NUMERIC(2,0)DEFAULT(4),

     CTno CHAR(2),

     CInfo VARCHAR(50),

     CPno CHAR(5),

     CTtime NUMERIC(3,0),

     CTerm MUMERIC(1,0))

(3)创建SM数据库中的选课表SC。

     打开查询分析器，输入以下SQL语句。

     CREATE TABLE SC

     (SNo CHAR(5)FOREIGN KEY(SNo)REFERENCES Student(SNo)ON DELETE CASCADE,

      CNo CHAR(5)FOREIGN KEY(CNo)REFERENCES Course(CNo),

      Score NUMERIC(3,1),

      PRIMARY KEY(SNo,CNo))

(4) 输入以下SQL语句，查看表Student的定义。

     USE SM

     EXEC sp_help Student

(5) 输入以下SQL语句，查看表Student与其他表之间的依赖关系

USE SM

     EXEC sp_depends Student

(6) 输入以下SQL语句，查看表Student上的约束

USE SM

     EXEC sp_helpconstraint Student

(7)利用企业管理器提供的图形界面来创建“进销存”数据库中的客户信息表c，货物表g和订单表o，表结构如下。

c(customerID int,name varchar(10),address varchar(50),city varchar(20),tel varchar(20),company varchar(50),birthday datatime,type tinyint)

g(name varchar(20),storage int, supplie varchar(50),status tinyint,price money)

o(ordered int,goodsname varchar(20),customerID int , quantity int,ordersum money,orderdate datetime)

(8)在表o的goodsname列上创建外键约束

(9)在表o的quantity列上创建检查约束

(10)使用企业管理器，把SM数据库中 选课表SC的主码设置为SNo和CNo。

(11)查看表间的依赖关系

(12)向表中输入数据。

实验3：表的维护

1、实验目的

(1)熟练掌握用企业管理器修改表

(2)能够用T-SQL语句对表结构进行修改

(3)熟练掌握用企业管理器编辑表中的数据

(4)能够运用T-SQL语句对表中的数据进行插入、修改和删除操作

2、实验步骤

(1)使用企业管理器为表Student(结构详见实验2中Student表)增加一列

(2)用SQL语句修改表Course的列属性，将CName的长度改为40，且不允许空，相关SQL语句如下：

USE SM

ALTER TABLE Course ALTER COLUMN CName VARCHAR(40)NOT NULL

(3)用SQL语句向表Student中增加列Email，且要求输入的电子邮件地址必须包括“@”字符，相关SQL语句如下：

USE SM

ALTER TABLE Student ADD Email VARCHAR(40) NULL CONSTRAINT ck_ea CHECK(Email like’%@%’)

(4) 用SQL语句删除表Student中的列Address, 相关SQL语句如下：

USE SM

ALTER TABLE Student DROP COLUMN Address

(5) 用SQL语句给表Student添加主键约束，相关SQL语句如下：

USE SM

ALTER TABLE Student ADD CONSTRAINT pk_name PRIMARY KEY (SName)

(6)删除SName 列上的主键约束，相关SQL语句如下：

USE SM

ALTER TABLE Student DROP CONSTRAINT pk_name

(7)使用WITH NOCHECK子句，对表Student的Sage列添加约束，使学生的年龄为18~25岁，相关SQL语句如下：

USE SM

ALTER TABLE Student WITH NOCHECK ADD CONSTRAINT ck_age CHECK(Sage>=18 AND Sage<=25)

(8)使约束无效或重新有效，使步骤(3)创建的约束无效，相关SQL语句如下：

USE SM

ALTER TABLE Student NOCHECK CONSTRAINT ck_ea

使这一约束重新有效，相关SQL语句如下：

USE SM

ALTER TABLE Student CHECK CONSTRAINT ck_ea

(9)使用企业管理器编辑表Student中的数据，插入、删除、修改记录

(10)用SQL语句向表中插入数据

将学号为“06001”、姓名为“王立”、性别为“男”的学生记录插入表Student中，相关SQL语句如下：

INSERT INTO Student(SNo,SName,SSex)VALUES(‘06001’,’王立’,’男’)

将一条选课记录插入表SC中

INSERT INTO SC VALUES(‘06001’,’00001’,null)

在表Student中插入一条学生记录，省略INSERT INTO后的列名，查看插入结果。

INSERT INTO Student VALUES(‘060001’,’要强’,’男’,’1986-12-12’)

新建一个表ss，且结构与表Student的结构完全相同，把表Student中学号为“06001”的记录插入到表ss中，再把表Student中所有男生的记录插入到表ss中，查看执行结果。

INSERT INTO ss SELECT*FROM Student WHERE SNo=’06001’

INSERT INTO ss SELECT*FROM Student WHERE SSex=’男’

(11)用SQL语句修改表中的记录

将学生06002的年龄改为20岁

在查询分析器中输入以下SQL语句。

UPDATE Student SET SAge=20 WHERE SNo=’06002’

将所有男生的年龄增加1岁

在查询分析器中输入以下SQL语句。

UPDATE Student SET SAge=Sage+1  WHERE SSex=’男’

将所有选修了数字逻辑课程的学生成绩增加5分

在查询分析器中输入以下SQL语句。

UPDATE SC SET Score=Score+5 WHERE CNo=(SELECT CNo FROM Course WHERE CName=’数字逻辑’)

(12)用SQL语句删除表中的记录

删除学号为06001的记录

在查询分析器中输入以下SQL语句。

DELETE FROM Student WHERE SNo=’06001’

删除所有学生的选课记录

DELETE FROM SC

实验4：常规数据查询

1、实验目的

(1)掌握SELECT子句以及WHERE子句的使用方法

(2)学会应用ORDER BY子句

(3)掌握5种基本的聚合函数

(4)学会分组统计和二次查询

2、实验步骤

启动企业管理器，在SM数据库中新建表Teacher，包括如下数据项。

Teacher(TNo,TName,age,sal,mgr,DNo)

其中，TNo为教职工编号，TName为教职工姓名，age为年龄，sal为月薪，mgr为教职工的上一级领导的工号，DNo为部门号

(1)SELECT子句的应用

查看所有教职工的信息

SELECT * FROM Teacher

查看所有教职工的职工号、姓名

SELECT TNo,TName FROM Teacher

查看所有职工的职工号、姓名，并且将输出结果中的列名显示为“教职工号”、“姓名”

SELECT TNo 教职工号，TName 姓名FROM Teacher

查看所有职工的姓名和年薪，并且将输出结果中的列名显示为“姓名”、“年薪”。

SELECT TName 姓名，sal*12 年薪 FROM Teacher

查看所有职工的姓名和年薪，并将输出结果中的列名显示为“姓名”、“年薪”、“币种单位”，其中币种单位的值为“元”。

SELECT TName 姓名，sal*12 年薪，’元’ 币种单位 FROM Teacher

运行以下两组SQL语句，看结果有何区别。

SELECT SNo FROM SC

SELECT DISTINCT SNo FROM SC

SELECT SNo,Score FROM SC

SELECT DISTINCT SNo,Score FROM SC

(2)WHERE子句的应用

查询所有0002部门职工的信息

SELECT * FROM Teacher WHERE DNo=’0002’

查询1984年和1985年出生的女生的信息

SELECT SName,SBir FROM Student

WHERE SSex=’女’AND SBir BETWEEN ‘1984-01-01’AND ‘1985-12-31’

查询0001部门、0002部门或0003部门的职工的信息

SELECT * 

FROM Teacher

WHERE DNo IN (‘0001’,’0002’,’0003’)

查询学号为03004的同学正在学习的课程

SELECT CNo

FROM SC

WHERE SNo=’03004’AND Score IS NULL

查询姓王的同学的信息

SELECT *

FROM Student

WHERE SName LIKE ‘王%’

(3)ORDER BY 子句的应用

查询所有职工的年龄，并按年龄从小到大的顺序排列

SELECT TNo,age

FROM Teacher

ORDER BY age

查询学生某门课（如00001）的成绩，并按成绩由高到低的顺序输出

SELECT SNo,Score

FROM SC

WHERE CNo=’00001’AND Score IS NOT NULL

ORDER BY Score DESC

查询职工的年薪，并按年薪的升序排列

SELECT TName,sal*12

FROM Teacher

ORDER BY 2

查询所有职工信息，按部门号的降序排列，在部门相同的情况下，按年龄的升序排列

SELECT *

FROM Teacher

ORDER BY DNo,DESC,age

(4)聚合函数的应用

统计职工的工资总额

SELECT SUM(sal) FROM Teacher

统计职工的工资总额，并显示每个职工的工资情况

SELECT TNo,sal

FROM Teacher

ORDER BY TNo COMPUTE SUM(sal)

统计职工的工资总额，显示每个职工的工资情况及各个部门的工资总额

SELECT TNo,sal

FROM Teacher

ORDER BY DNo,TNo COMPUTE SUM(sal) BY DNo

求职工的最高工资、最低工资和平均工资

SELECT MAX(sal),MIN(sal),AVG(sal)

FROM Teacher

求女学生的学生总数

SELECT COUNT(*)

FROM Student

WHERE SSex=’女’

查询有多少名学生的物理课成绩不及格

SELECT COUNT(SNo)

FROM SC,Course

WHERE CName=’物理’AND Course.CNO=SC.CNo AND Score<60

(5)聚合操作与分组统计的应用

求每个班的学生数，希望得到下面的输出

CLno               COUNT(*)

060001                 40

060002                 35

…                     …

SELECT CLno,COUNT(*)

FROM Student

GROUP BY CLno

查询每个学生已获得的学分（成绩及格即取得相应课程的学分）

SELECT SNo,SUM(CCredits)

FROM SC,Course

WHERE Score>60 AND SC.CNo=Course.CNo

GROUP BY SNo

使用HAVING子句，查询学分大于30的学生，输出学生的学号和学分

SELECT SNo,SUM(CCredits)

FROM SC,Course

WHERE Score>60 AND SC.CNo=Course.CNo

GROUP BY SNo

HAVING SUM(CCredits)>30