数据库SQL实验第三节

SQL Server 2000实验

实验1 SQL Server的安装及其管理工具的使用

目的与要求

(1)掌握SQL Server服务器的安装。 (2)掌握企业管理器的基本使用方法。 (3)掌握查询分析器的基本使用方法。 (41对数据库及其对象有一个基本了解。 实验准备

(1)了解SQL Server 2000各种版本安装的软硬件要求。 (2)了解SQL Server支持的身份验证模式。 (3)SQL Server各组件的主要功能。

(4)对数据库、表、数据库对象有一个基本了解。 (5)了解在查询分析器中执行SQL语句的方法。 实验内容

1.安装SQLServer2000

根据软硬件环境，选择一个合适版本的SQL Server 2000。安装步骤请参照。 2.利用企业管理器访问系统自带的pubs数据库 (1)启动SQL Server服务管理器。

(2)以系统管理员身份登录到企业管理器并访问pubs数据库。

(3)在企业管理器的目录树中展开文件夹一--“展开”pubs数据库图标，则将列出该数据库的所有对象，如表、视图、存储过程，默认、规则等。

(4)选中pubs下的“表”图标，将列出pubs数据库所有的表(系统表和用户表)，在此以用户表publishers为例，打开该表，查看其内容。

(5)在表的尾部插入记录(9943,zhang,Beijing,null,china)和(1408,1i,shangiaai,nulI．china),会发生什么情况? (6)在表中删除步骤(5)插入的记录。 3．查询分析器的使用

(1)在查询分析嚣对象浏览器的目录树中展开pubs数据库图标，则将列出该数据库的所有对象，如表、视图、存储过程等。

(2)以pubs数据库的用户表authors为例，选中该表的图标，单击鼠标右键，出现一快捷菜单，执行“打开”菜单项，打开该表，查看其内容。 （3）在表的尾部插入一记录。

（4）在查询分析器的输入窗口，输入如下T-SQL语句： use pubs Select * from authors go

看看执行结果是什么?

思考与练习：用查询分析器查询pubs数据库publishers表的所有记录。

实验2创建数据库和表

目的和要求

(1)了解SQL Server数据库的逻辑结构和物理结构。 (2)了解表的结构特点。

(3)了解SQL Server的基本数据类型。 (4)了解空值概念。

(5)学会在企业管理器中创建数据库和表。 (6)学会使用T-SQL语句创建数据库和表。 实验内容 1.实验题目

创建用于企业管理的员工管理数据库，数据库名为YGGL，包含员工的信息、部门信息 以及员工的薪水信息。数据库YGGL包含下列3个表： (1)Employees：员工信息表。 (2)Departments：部门信息表。 (3)Salary：员工薪水情况表。

各表的结构分别如表1、表2和表3所示。 表1 Employees表结构

列名 EmployeeID Name Birthday Sex 数据类型 Char Char Datetime Bit Char Char Char Char Char 长度 6 10 8 1 20 6 12 30 3 是否允许为空值 × × × × √ √ √ √ × 说明 员工编号，主键 姓名 出生日期 性别 地址 邮编 电话号码 电子邮件地址 员工部门号，外键 说明 部门编号，主键 部门名 备注 Address Zip PhoneNumber EmailAddress DepartmentID 列名 DepartmentID DepartmentName Note 表2 Departments表结构 数据类型 字符型(char) 字符型(char) 文本(text) 数据类型 长度 是否允许为空值 3 20 16 × × √ 说 明 表3 Salary表结构 列 名 长 度 是否允许为空值 EmployeeID 字符型(char) InCome OutCome 浮点型(float) 浮点型(float) 6 8 8 × × × 员工编号，主键 收入 支出

2.实验准备

首先要明确，能够创建数据库的用户必须是系统管理员，或是被授权使用CREATE DATABASE语句的用户。

其次创建数据库必须要确定数据库名、所有者(即创建数据库的用户)、数据库大小(最

初的大小、最大的大小、是否允许增长及增长方式)和存储数据库的文件。

然后，确定数据库包含哪些表，以及所包含的各表的结构，还要了解SQL Server的常用数据类型，以创建数据库的表。

此外还要了解两种常用的创建数据库、表的方法，即在企业管理器中创建和使用T-SQL语句创建。 实验步骤

1.在企业管理器中创建数据库YGGL 要求：数据库YGGL初始大小为lO MB，最大大小为50 MB，数据库自动增长，增长方式是按5％比例增长：日志文件初始为2MB，最大可增长到5 MB(默认为不限制)，按1 MB增长(默认是按5％比例增长)。数据库的逻辑文件名和物理文件名均采用默认值，分别为YGGL_data和e:\\sql\\data\\MSSQL\\Data\\YGGL．mdf,其中e：\\sql\\data\\MSSQL为SQL Server的系统安装目录；事务日志的逻辑文件名和物理文件名也均采用默认值，分别为YGGL_LOG和e：\\sql\\data\\MSSQL\\Data\\YGGL_Log.ldf。

以系统管理员Administrator或被授权使用CREATE DATABASE语句的用户登录SQL Server服务器，启动企业管理器→在服务器上单击鼠标右键→新建数据库→输入数据库名“YGGL” →选择“数据文件”选项卡→设置增长方式和增长比例→选择“事务日志”选项卡→设置增长方式和增长比例。 注意：在“数据文件”选项卡和“事务日志”选项卡中，可以分别指定数据库文件和日志文件的物理路径等特性。

2.在企业管理器中删除创建的YGGL数据库

在企业管理器中选择数据库YGGL→在YOGL上单击鼠标右键→删除。 3.使用T-SQL语句创建数据库YGGL 按照上述要求创建数据库YGGL。

启动查询分析器→在“查询”窗口中输入如下T-SQL语句： CREATE DATABASE YGGL ON

(NAME=‘YGGL_Data’，

FILENAME=‘e:\\sql\\data\\MSSQL\\Data\\YGGL.mdf’， SIZE=10MB， MAXSIZE=50MB， FILEGROWTH=5％) LOG ON

(NAME=‘YGGL_Log'，

FILENAME=‘e:\\sql\\data\\MSSQL\\Data\\YGGL_Log.ldf’, SIZE=2MB， MAXSIZE=5MB， FILEGROWTH=1MB) GO

单击快捷工具栏的执行图标执行上述语句，并在企业管理器中查看执行结果。 4.在企业管理器中分别创建表Employees、Departments和Salary

在企业管理器中选择数据库YGGL→在YGGL上单击鼠标右键→新建→表→输入Employees表各字段信息→单击保存图标→输入表名Employees，即创建了表Employees。按同样的操作过程创建表Departments和Salary。

5.在企业管理器中删除创建的Employees、Departments和Salary表

在企业管理器中选择数据库YGGL的表Employees→在Employees上单击鼠标右键-→删除，即删除了表Employees。按同样的操作过程删除表Departments和Salary。 6.使用T-SOL语句创建表Employees、Departments和Salary 启动查询分析器→在“查询”窗口中输入以下T-SQL语句： USE YGGL

CREATE TABLE Employees (EmployeeID char(6) NOT NULL， Name char(10) NOT NULL， Birthday datetime NOT NULL， Sex bit NOT NULL,

Address char(20) NOT NULL， Zip char(6) NULL，

PhoneNumber char(12) NULL， EmailAddress char(20) NULL, DepartmentID char(3) NOT NULL) GO

单击快捷工具栏的执行图标，执行上述语句，即可创建表Employees。按同样的操作过程创建表Departments和Salary，并在企业管理器中查看结果。

实验3 表数据插入、修改和删除

目的和要求

(1)学会在企业管理器中对数据库表进行插入、修改和删除数据操作。 (2)学会使用T-SQL语句对数据库表进行插入、修改和删除数据操作。 (3)了解数据更新操作时要注意数据完整性。

(4)了解T-SQL语句对表数据操作的灵活控制功能。 实验内容 1.实验题目

分别使用企业管理器和T-SQL语句，向在实验2建立的数据库YGGL的3个表Employees、Departments和Salary中插入多行数据记录，然后修改和删除一些记录。使用T-SQL语句进行有限制的修改和删除。 2.实验准备

首先了解对表数据的插入、删除、修改都属于表数据的更新操作。对表数据的操作可以在企业管理器中进行，也可以由T-SQL语句实现。 其次要掌握T-SQL中用于对表数据进行插入、修改和删除的命令分别是INSERT、UPDATE和DELETE。

要特别注意在执行插入、删除、修改等数据更新操作时，必须保证数据完整性。

此外，还要了解使用T-SQL语句在对表数据进行插入、修改及删除时，比在企业管理器中操作表数据更为灵活，功能更强大。 实验步骤

1.在企业管理器中向数据库YGGL表加入数据

(1) 在企业管理器中向表Employees中加入如表4所示的记录。

表4 Employees表记录 编号 姓名 出生日期 性别 1 1 l 0 1 1 0 1 1 1 0 地址 邮编 电话 电子邮件 NULL NULL NULL lili@sina.com NULL zhujun@sina.com zhongmin@sohu.com zhang@china.com NULL liyumin@online.com NULL NULL 部门号 2 1 l l 5 5 3 5 3 4 4 4 000001 王林 1956-1-23 010008 伍容华 1966-3-28 020010 王向容 1972-12-9 020018 李丽 1950-7-30 102201 刘明 1962-10-18 102208 朱俊 1955-09-28 108991 钟敏 1969-08-10 111006 张石兵 1964-10-01 210678 林涛 1967-04-02 302566 李玉珉 1958-09-20 308759 叶凡 1968-11-18 504209 陈林琳 1959-09-03

中山路32-1-508 210003 3355668 北京东路100-2 210001 3321321 四牌楼10-l0-108 210006 3792361 中山东路102-2 210002 3413301 虎距路100-2 210013 3606608 三牌楼巷-3-1806 210004 4700817 中山路10-3-105 210003 3346722 中山北路247-305 210008 3467336 热和路209-3 210018 8765991 北京西路31-502 210001 3308901 汉中路1204-1112 210002 4468158 1 解放路34-9-1-203 210010 4563418 在企业管理器中选择表Employees→在其上单击鼠标右键→选“返回所有行” →逐字段输入各记录值，输入完后，关闭表窗口。

(2)在企业管理器中向表Departments插入如表5所示的记录。 (3)在企业管理器中向表Salary插入如表6所示的记录。 表5 表Departments的记录

编号 部门名称 备注 1 2 3 4 5 财务部 人力资源部 经理办公室 研发部 市场部 NUlL NULL NULL NULL NULL

表6 表Salary的记录 编号 000001 010008 102201 111006 504209 302366 108991 020010 020018 308759 210678 102208 收入 2100.8 1582.62 2569.88 1987.01 2966.15 2980.7 3259.98 2860.0 2347.68 2531.98 2240.0 1980.0 支出 123.09 88.03 185.65 79.58 108.0 210.2 281.52 198.0 180.0 199.08 121.0 100.0

2.在企业管理器中修改数据库YGGL表数据

(1)在企业管理器中删除表Employees的第2、8行和Salary的第2、11行。注意进行删除操作时，作为两表主键的EmployeeID的值，以保持数据完整性。

在企业管理器中选择表Employees→在其上单击鼠标右键→选“返回所有行”→选择要删除的行→单击鼠标右键→删除→关闭表窗口。

(2)在企业管理器中删除表Departments的第2行，同时也要删除表Employees的第2行。操作方法同上。

(3)在企业管理器中将表Employees中编号为020018的记录的部门号改为4。

在企业管理器中选择表Employees→在其上单击鼠标右键→选“返回所有行”→将光标定位至编号为020018的记录的DepartmentID字段，将值1改为4。 3.使用T-SQL命令修改数据库YGGL表数据

(1)使用T-SQL命令分别向YGGL数据库的Employees、Departments和Salary表中插入一行记录。

启动查询分析器→在“查询”窗口中输入如下T-SQL语句： USE YGGL

INSERT INTO Employees VALUES(‘011112’，‘罗林’，‘1973-5-3’，1,‘解放路100号’,210002,4055663,NULL,5) GO

INSERT INTO Departments

VALUES(‘2’,‘人力资源部’,NULL) GO

INSERT INTO Salary

VALUES(‘011112’,1200.09,50) GO

单击快捷工具栏的执行图标，执行上述语句。 注意：在企业管理器中分别打开YGGL数据库的Employees，Departments和Salary表，观察数据变化。

(2)使用T-SQL命令修改表Salary中的某个记录的字段值。 启动查询分析器→在“查询”窗口中输入如下T-SQL语句： USE YGGL

UPDATE Salary SET InCome=2890

WHERE EmployeeID=‘011112’ GO

单击快捷工具栏的执行图标，执行上述语句，将编号为011112的职工收入改为2890。 注意：在企业管理器中打开YGGL数据库Salary表，观察数据变化。

(3)修改表Employees和Departments的记录值，仍要注意完整性。操作过程同上。 (4)使用T-SQL命令修改表Salary中的所有记录的字段值。 启动查询分析器→在“查询”窗口中输入如下T-SQL语句： USE YGGL

UPDATE Salary

SET InCome=InCome+100 GO

单击快捷工具栏的执行图标，执行上述语句，将所有职工的收入增加100。 可见，使用T-SQL语句操作表数据比在企业管理器中操作表数据更为灵活。 注意：输入T-SQL语句SELECT * FROM Salary，观察数据变化。 (5)使用DELETE语句删除表中所有行。

启动查询分析器→在“查询”窗口中输入如下T-SQL语句： USE YGGL

DELETE Salary GO

单击快捷工具栏的执行图标，执行上述语句，将删除Salary表中的所有行。

注意：实验时—般不要轻易执行这个操作，因为后面实验还要用到这些数据。如要试验该命令的效果，可创建一个临时表，输入少量数据后进行。

实验4 数据库的查询

目的与要求

(1)掌握SELECT语句的基本语法。 (2)掌握子查询的表示。 (3)掌握连接查询的表示。 (4)掌握数据汇总的方法。

(5)掌握SELECT语句的GROUP BY子句的作用和使用方法。 (6)掌握SELECT语句的ORDER BY子句的作用和使用方法。 实验准备

(1)了解SELECT语句的基本语法格式。 (2)了解SELECT语句的执行方法。 (3)了解子查询的表示方法。 (4)了解连接查询的表示。 (5)了解数据汇总的方法。

(6)了解SELECT语句的GROUP BY子句的作用和使用方法。 (7)了解SELECT语句的ORDER BY子句的作用。 实验内容

1.SELECT语句的基本使用

(1)对于实验2给出的数据库表结构，查询每个雇员的所有数据。在查询分析器的编辑窗口输入如下语句并执行： USE YGGL SELECT *

FROM Employees GO

思考与练习：用SELECT语句查询Departments和Salary表的所有记录。

(2)查询每个雇员的地址和电话。在查询分析器的编辑窗口输入如下语句并执行： USE YGGL

SELECT Address,PhoneNumber FROM Employees GO

思考与练习：用SELECT语句查询Departments和Salary表的一列或若干列。

(3)查询EmployeeID为000001的雇员的地址和电话。在查询分析器的编辑窗口输入如

下语句并执行： USE YGGL

SELECT Address, PhoneNumber FROM Employees

WHERE EmployeeID=‘000001’ GO

思考与练习：用SELECT语句查询Departments和Salary表中满足指定条件的一列或若干列。

(4)查询Employees表中女雇员的地址和电话，使用AS子句将结果中各列的标题分别指定为地址、电话。在查询分析器的编辑窗口输入如下语句并执行： USE YGGL

SELECT Address AS 地址,PhoneNumber AS 电话 FROM Employees WHERE sex=0 GO

注意：使用AS子句可指定目标列的标题。

(5)计算每个雇员的实际收入。在查询分析器的编辑窗口输入如下语句并执行： USE YGGL

SELECT EmployeeID,实际收入=InCome—OutCome FROM Salary GO

(6)找出所有姓王的雇员的部门号。在查询分析器的编辑窗口输入如下语句并执行： USE YGGL

SELECT DepartmentID FROM Employees

WHERE name LIKE‘王％’ GO

思考与练习：找出所有地址中含有“中山”的雇员的号码及部门号。

(7)找出所有收入在2000-3000元之间的雇员编号。在查询分析器的编辑窗口输入如下语句并执行： USE YGGL

SELECT EmployeeID FROM Salary

WHERE InCome BETWEEN 2000 AND 3000 GO

思考与练习：找出所有在财务部和人力资源部工作的雇员的编号。 注意：在SELECT语句中LIKE、BETWEEN．．．AND、IN、NOT及CONTAIN谓词的作用。 2.子查询的使用

(1)查找在财务部工作的雇员的情况。在查询分析器的编辑窗口输入如下语句并执行： USE YGGL SELECT *

FROM Employees

WHERE DepartmentID= (SELECT DepartmentID

FROM Department

WHERE DepartmentName=‘财务部’) GO

思考与练习：用子查询的方法查找所有收入在2 500元以下的雇员的情况。

(2)查找财务部年龄不低于研发部雇员年龄的雇员的姓名。在查询分析器的编辑窗口输入如下语句并执行： USE YGGL SELECT Name FROM Employees DepartmentID IN

(SELECT DepartmentID FROM Departments

WHERE DepartmentName=‘财务部’) AND

Birthday＞ALL(SELECT Birthday FROM Employees

WHERE DepartmentID IN (SELECT DepartmentID FROM Departments

WHERE DepartmentName=‘研发部’)) GO

思考与练习：用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名。 (3)查找比所有财务部的雇员收入都高的雇员的姓名。在查询分析器的编辑窗口输入如下的语句并执行： USE YGGL SELECT Name FROM Employees

WHERE EmployeeID IN (SELECT EmployeeID FROM Salary WHERE InCome＞ ALL(SELECT InCome FROM Salary

WHERE EmployeeID IN (SELECT EmployeeID FROM Employees

WHERE DepartmentID= (SELECT DepartmentID FROM Departments

WHERE DepartmentName=‘财务部’)))) GO

思考与练习：用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名。 3.连接查询的使用

(1)查询每个雇员的情况及其薪水的情况。在查询分析器的编辑窗口输入如下的语句并

执行：

USE YGGL

SELECT Employees.*，Salary.* FROM Employees，Salary

WHERE Employees．EmployeeID=Salary．EmployeeID GO

思考与练习：查询每个雇员的情况及其工作部门的情况。

(2)查找财务部收入在2 200元以上的雇员姓名及其薪水详情。在查询分析器的编辑窗口输入如下语句并执行： USE YGGL

SELECT Name，InCome，OutCome

FROM Employees，Salary，Departments

WHERE Employees．EmployeeID=Salary．EmployeeID AND Employees．DepartmentID =Departments．DepartmentID AND DepartmentName=‘财务部’AND InCome＞2200 GO

思考与练习：查询研发部在1966年以前出生的雇员姓名及其薪水详情。 4.数据汇总

(1)求财务部雇员的平均收入。在查询分析器的编辑窗口输入如下语句并执行： USE YGGL

SELECT AVG(InCome) AS ‘财务部平均收入’ FROM Salary

WHERE EmployeeID IN (SELECT EmployeeID FROM Employees

WHERE DepartmentID= (SELECT DepartmentID FROM Departments

WHERE DepartmentName＝‘财务部’)) GO

思考与练习；查询财务部雇员的最高和最低收入，

(2)求财务部雇员的平均实际收入。在查询分析器的编辑窗口输入如下的语句并执行： USE YGGL

SELECT AVG(InCome-OutCome) AS ‘财务部平均实际收入’ FROM Salary

WHERE EmployeeID IN (SELECT EmployeeID FROM Employees

WHERE DepartmentID= (SELECT DepartmentID FROM Departments

WHERE DepartmentName=‘财务部’)) GO

思考与练习：查询财务部雇员的最高和最低实际收入。

(3)求财务部雇员的总人数。在查询分析器的编辑窗口输入如下的语句并执行：

END RETURN GO

(3)删除职员记录的存储过程EmployeeDelete。 USE YGGL GO

CREATE PROCEDURE EmployeeDelete (@employeeid char(6)) AS BEGIN

DELETE FROM Employees

WHERE Employeeid=@employeeid

END RETURN GO

4.调用存储过程 USE YGGL

EXEC EmployeeAdd '990230','刘朝','890909',1,'武汉小洪山5号', '', '', '', '3' GO

USE YGGL EXEC EmployeeUpdate '990230','990232','刘平','890909',1,'武汉小洪山5号', '', '', '', '2' GO

USE YGGL

EXEC EmployeeDelete '990232' GO

分析一下此段程序执行时可能出现哪几种情况。 思考与练习：

编写如下T—SQL程序： (1)自定义一数据类型，用于描述YGGL数据库中的DepartmentID字段，然后编写代码，重新定义数据库各表。

(2)对于YGGL数据库，表Employees的EmployeeID列与表salary的EmployeeID列应满足参照完整性规则，请用触发器实现两个表间的参照完整性。

(3)编写对数据库YGGL各表进行插入、修改、删除操作的存储过程，然后编写一段程序调用这些存储过程。

本文来源：https://www.bwwdw.com/article/4mbt.html