SQL数据库编程基础以及实例

实验须知

⒈实验前，请认真阅读本次实验的全部内容，明确实验目的和实验任务，做好必要的实验准备。遇到较为复杂的程序应事先设计算法，可能的话，应写出程序代码。

⒉实验前，应注意收集平时学习中遇到的疑点，留作实验中验证、解决。

⒊提前五分钟以上进入实验室，不得迟到。

⒋实验中积极思考、及时记录，有新的发现、新的收获或新的疑问的，及时记录，争取当堂完成实验报告。

⒌珍惜实验时间，不开小差、不做与实验无关的事，更不得打游戏、或上网聊天；如需要上网查阅资料的，应事先征得实验老师的同意。

⒍保持实验室安静和清洁卫生，不得大声喧哗；不得在实验室吃东西。

⒎实验结束时，整理好台桌和凳子，关闭电脑及显示器，安排值日同学，打扫实验室卫生。

⒏实验报告应字迹工整，标点符号准确无误；书写程序时应使用缩进格式，必要时还可以添加适当的注释，增强程序的可读性。

⒐如实认真填写问题解决与实验收获两部分，如果问题还未解决，也应将问题记下，留作今后解决。

目录

实验须知.............................................................................................................................. I 目录...................................................................................................................................... I 实验一、SQLServer2005的认识.. (1)

实验二数据库创建 (8)

实验三数据表结构的建立与维护 (11)

实验四数据查询 (13)

实验五数据表的操作 (17)

实验六视图 (19)

实验七索引、约束、规则 (24)

实验八Transact-SQL语句的使用 (27)

实验九存储过程和触发器 (30)

实验一、SQLServer2005的认识实验日期：______年______月______日成绩：_____________ 一、实验目的

1．了解SQL Server 2005特点

2．了解SQL Server 2005的安装

3．学习配置SQL Server服务器

二、实验内容

1、SQL Server 2005对硬件环境的要求

（1）处理器、内存要求

32 位平台上需要 PentiumⅢ兼容处理器或更高速度的处理器，最低处理器速度为600MHZ(推荐1GB或者更快)，最小内存 512MB(推荐 1GB或者更大)。

64 位平台，IA64 的需要 Itanium或更高级的处理器，最低处理器速度 1GB 或者更快，最小内存 512MB(推荐 1GB 或者更大)。X64需要AMD Opteron、 AMD Athlon64、支持 Intel EM64T 技术的 Intel Xenon或支持 EM64T 技术的 Interl PentiumⅣ处理器，最小处理器速度1GHz或者更大，最小内存 512MB(推荐 1GB 或者更大)。

（2）磁盘空间要求

在安装 SQL Server 2005 的过程中， Windows Installer 将在系统驱动器上创建临时文件。在运行安装程序以安装或升级到 SQL Server 2005 前，请验证系统的驱动器上有 1.6GB 的可用磁盘空间来容纳这些文件，即使将 SQL Server组件安装到非默认驱动器上，此要求同样适用。

2、对操作系统的要求

3、对Internet的要求

4、基本安装过程

(1) 将 SQL Server 2005 安装光盘插入光驱后，将出现如图 5-1 所示的安装启动对话框。

(2) 单击“安装”下的“服务器组件、工具、联机丛书和示例”，出现如图 5-2 所示的对话框，选择“我接受许可条款和条件”选项。

(3) 单击“下一步”按钮，出现如图5-3所示的对话框。在对话框中列出了安装SQL Server 2005 前必先安装的 4 个组件(如果已经安装则不会显示)。单击“安装”按钮，开始安装和

配置这 4 个组件。

(4) 全部安装成功后，会出现如图 5-4 所示的对话框。

(5) 单击“下一步”按钮，打开如图 5-5 所示的对话框。

(6) 单击“下一步”按钮，在如图 5-6 所示的对话框中，将检查系统中是否有潜在的安装问题(例如检查操作系统、计算机的硬件配置等是否满足安装的最低要求)，并对不满足安装要求的软硬件配置发出警告。如果符合要求就显示“成功”；若不满足要求则显示“警告”，但是不影响继续安装；若显示“错误”，对话框中的“下一步”按钮不能单击，也就是无法安装数据库系统。

(7) 单击“下一步”按钮，安装程序进行一些必要的准备工作后，出现如图 5-7 所示的对话框，填写注册信息。

(8) 单击“下一步”按钮，出现如图 5-8 所示的对话框，在此选择要安装的组件。

(9) 单击“下一步”按钮，出现如图 5-9 所示的对话框，用于设置服务器的实例名，一般情况下选择“默认实例”，但也可以选择“命名实例”，表示为服务器命名。

(10) 单击“下一步”按钮，出现如图5-10 所示的对话框。可以为每个服务定义不同的账户，也可以为所有的账户定义统一的登录账户；可以使用系统内置的账户，也可以使用域用户账户。

(11) 单击“下一步”按钮，出现如图 5-11 所示的对话框。

(12) 在排序规则设置时，指定 SQL Server实例的排序方式。一般采用默认设置，单击“下一步”按钮，打开如图5-12 所示的对话框，取消选择复选框将禁止向 Microsoft发送错误报告，或者任意选择一个。

(13) 单击“下一步”按钮，打开如图 5-13 所示的对话框，列出用户选择的安装组件。

(14) 单击“安装”按钮，打开如图 5-14 所示的对话框，开始安装SQL Server 2005的选定组件。安装过程可能持续比较长的时间。

(15) 单击“下一步”按钮，打开如图 5-15 所示的对话框。这时单击“完成”按钮就成功安装了 SQL Server 2005。

5、启动 SQL Server Management Studio

操作步骤如下：

(1) 选择“开始”菜单上的“程序” | Microsoft SQL Server 2005 | SQL Server Management Studio 命令，弹出“连接到服务器”对话框，如图5-23 所示。

(2) SQL Server Management Studio 提供数据库引擎、Analysis Services、Reporting Services、SQL Server Mobile和Integration Services五种服务器类型，这里选择服务器类型为“数据库引擎”。

(3) 如果是第一次启动SQL Server Management Studio，需要选择

登录账户。单击“服务器名称”文本框后面的下拉箭头，选择“浏览更多”命令，弹出“查找服务器”对话框。如图 5-24 所示，在“本地服务器”选项卡中，选择“数据库引擎”选项的登录账户COMPUTER(本地计算机名称 COMPUTER)，或打开“网络服务器”选项卡查找网络上服务器并进行选择。

(4) 选择“身份验证”方式，Windows身份验证或 SQL Server身份验证，输入登录名和密码。

(5) 单击“连接”按钮，进入 Microsoft SQL Server Management Studio 窗口，如图 5-25所示。

三、实验思考

安装完SQL Serve后，系统创建哪两个系统管理员登录账户？

安装完SQL Serve后，会自动创建两个系统管理员登录账户：一个是

基于Windows认证模式的账户BUILTIN\Administrators，另一个是基于SQL Server 认证模式的账户sa，sa 拥有最高的管理权限，可以执行服务器范围内的所有操作。sa 也把全部权限赋给BUILTIN\Administrators 账户，使BUILTIN\Administrators 与sa 具有同等权限。

四、实验小结

实验二数据库创建

实验日期：______年______月______日成绩：_____________

一、实验目的

1.掌握修改数据库的逻辑名和物理名

2.掌握向数据库中增加文件、删除文件

3.掌握把数据库文件备份和还原

二、实验内容

1、创建学生信息数据库

要求创建学生信息数据库。数据库选项采取默认设置。

CREATE DATABASE student

ON

(NAME =student_data,

FILENAME ='c:\student_data.mdf'

)

LOG ON

(NAME =student_log,

FILENAME ='c:\student_data.ldf')

2、创建职工信息数据库

要求创建职工信息数据库，并把数据库文件和事务日志文件放在指定目录下。

CREATE DATABASE employee

ON

(NAME =employee_data,

FILENAME ='d:\mssql\data\employee_data.mdf'

)

LOG ON

(NAME ='employee_log',

FILENAME ='d:\mssql\data\employee_data.ldf')

3．修改学生信息数据库

在上次创建学生信息数据库时，除了数据库名需要指定外，其他均采用SQL Sever 2000默认设置，其数据文件属性默认设置为按10%增长，现在要将其修改为按1MB进行增长。

ALTER DATABASE student

MODIFY FILE(NAME='student_data',

filegrowth=1mb)

GO

4．修改职工信息数据库

修改上次创建的数据库employee，添加一个次数据库文件。次数据库文件名为“employee_Data2.NDF”。该文件的扩充增容模式采取按10%增长，即采取默认设置。

USE MASTER

GO

ALTER DATABASE Employee

ADD FILE

(NAME=employee2_Data2,

FILENAME='D:\MSSQL\Data\employee2_Data2.ndf',

FILEGROWTH=10%)

GO

5．Drop语句删除数据库（Drop database数据库名）

删除Student1数据库

Drop database Student1

三、实验思考

数据文件分为哪几类？分别有何作用？

数据文件分为下述3 类。

(1) 主要数据文件(.MDF)：主要存放数据库的启动信息，并用于存储数据。每个数据库有且仅有一个主要数据文件。

(2) 次要数据文件(.NDF)：次要数据文件也是用来存储数据的，它含有不能置于主要数据文件中的所有数据。如果主要数据文件可以包含数据库中的所有数据，那么数据库就不需要次要数据文件。如果数据库很大，

主要数据文件的容量超过了系统的限制，就需要设置一个或多个次要数据文件，并将它们放在不同的磁盘上。

(3) 事务日志文件(.LDF)：主要包含用于恢复数据库的所有日志信息，这使得数据库出现故障或崩溃时可以把它恢复到最近状态，从而最大限度的减少由此带来的损失。每个数据库中必须至少包含一个日志文件，也可以有多个日志文件。

四、实验小结

实验三数据表结构的建立与维护

实验日期：______年______月______日成绩：_____________ 一、实验目的

1．熟悉SQL SERVER 中的数据类型。

2．掌握表结构的建立和维护方法。

3．掌握索引的建立与删除

二、实验内容

1、利用SQL语句完成创建“stu_info”（学生基本信息表）。

CREATE TABLE stu_info

(

stu_id CHAR(7) NOT NULL PRIMARY KEY,

name VARCHAR(8) NOT NULL,

sex CHAR(2) CHECK (sex='男' or sex='女'),

borndate SMALLDATETIME,

peop_id VARCHAR(18) NULL UNIQUE,

class_id CHAR(5) NOT NULL）

2、利用SQL语句完成创建学生成绩表（result_info），

CREATE TABLE result_info

(

term_no CHAR(8) NOT NULL,

stu_id CHAR(7) NOT NULL,

course_no CHAR(3) NOT NULL,

result SMALLINT CHECK (result between -1 and 100) DEFAULT -1,

PRIMARY KEY (term_no,stu_id,course_no)，

FOREIGN KEY (stu_id) REFERENCES stu_info(stu_id) )

3、向stu_info数据表中添加“comedate”（入学日期）字段。

ALTER TABLE stu_info ADD comedate SMALLDATETIME NULL

4、将stu_info数据表中的“name”字段的数据类型修改为varchar （10），完整性约束修改为“允许为空”。

ALTER TABLE stu_info ALTER COLUMN name VARCHAR(10) NULL

5、将stu_info数据表中的“comedate”字段删除。

ALTER TABLE stu_info DROP COLUMN comedate

6、删除stu_info表

DROP TABLE stu_info

7、为学生基本信息表（stu_info）建立索引，要求按学号升序建立唯一索引。

CREATE UNIQUE INDEX stu_in ON stu_info(stu_id)

8、为学生成绩表（result_info）建立索引，要求按学期编号升序、课程编号和学号降序建立唯一索引。

CREATE UNIQUE INDEX res_in ON result_info

(term_no ASC, course_no DESC, stu_id DESC)

9、删除学生成绩表（result_info）中的索引“res_in”。

DROP INDEX result_info.res_in

三、实验思考

1、唯一性约束有什么什么作用？与主键约束有何区别？

唯一性约束指定表的一列或多列的组合的值在表中具有唯一性，以防止在列中输入重复的值。主键约束与唯一性约束的区别是：

(1) 在一个表中只能定义一个主键约束，但可定义多个唯一性约束。

(2) 指定为主键约束的列不能取空值，但指定为唯一性约束的列允许取空值。

2、CHECK约束有什么作用？

CHECK 约束用于限制输入到一列或多列的值的范围，保证数据库的数据完整性。

四、实验小结

实验四数据查询

实验日期：______年______月______日成绩：_____________

一、实验目的

1.掌握SELECT语句的参数的含义

2.掌握SELECT语句的各参数的设置

3.掌握SELECT数据查询的使用

二、实验内容

1、显示“stu_info”表中的“stu_id”、“class_id”和“name”字段。

SELECT stu_id,class_id,name FROM stu_info

2、显示“stu_info”表中的所有字段。

SELECT * FROM stu_info

3、显示“stu_info”表中的“stu_id”、“name”和“sex”字段。字段名称分别指定为“学号”、“姓名”和“性别”。

SELECT stu_id AS 学号,name 姓名,性别=sex FROM stu_info

4、显示“stu_info”表中的所有学生的姓名和年龄。

SELECT name AS 姓名,year(getdate())-year(borndate) as 年龄from stu_info

5、显示“stu_info”表中的所有学生的姓名和身份证号。

SELECT name AS 姓名,'身份证号：',peop_id AS 身份证号

FROM stu_info

6、显示“stu_info”表中的所有学生的班级编号。

SELECT ALL class_id FROM stu_info

（或者） SELECT class_id FROM stu_info

去掉重复记录的语句为：

SELECT DISTINCT class_id FROM stu_info

只显示前三个班级的编号的语句：

SELECT TOP 3 class_id FROM stu_info

7、显示“stu_info”表中所有男生的记录。

SELECT * FROM stu_info WHERE sex='男'

8、显示“stu_info”表中所有班级编号不是“07001”的学生记录。

SELECT * FROM stu_info WHERE class_id<>'07001'

9、显示“stu_info”表中所有1988年出生的男生记录。

SELECT * FROM stu_info WHERE sex='男' and

year(borndate)=1988

10、显示“stu_info”表中班级编号为“07001”或者性别为女生的学生记录。

SELECT * FROM stu_info WHERE sex='女' OR

class_id='07001'

11、显示“stu_info”表中户籍不是天津市的学生记录。

SELECT * FROM stu_info WHERE NOT

substring(peop_id,1,3)='120'

12、显示“stu_info”表中所有姓“张”的学生记录。

SELECT * FROM stu_info WHERE name LIKE '张%'

13、显示“stu_info”表中所有姓“张”和姓“李”的学生记录。

SELECT * FROM stu_info WHERE name like '张%' or name like '李%'

（或者）SELECT * FROM stu_info WHERE name like '[张,李]%'

14、显示“stu_info”表中年龄在18至20岁之间的学生的姓名和性别。

SELECT name,sex FROM stu_info

WHERE year(getdate())-year(borndate) BETWEEN 18 AND 20

15、显示“stu_info”表中身份证号码内容为空的学生记录。

SELECT * FROM stu_info WHERE peop_id IS NULL

16、显示“stu_info”表中班级编号为“07001”和“07002”的学生记录。

SELECT * FROM stu_info WHERE class_id='07001' OR class_id='07002'

（或者）SELECT * FROM stu_info WHERE class_id IN (07001,07002)

17、显示“stu_info”表中班级编号不是“07001”和“07002”的学生记录。

SELECT * FROM stu_info WHERE class_id NOT IN (07001,07002)

（或者） SELECT * FROM stu_info WHERE NOT class_id IN (07001,07002)

18、显示“stu_info”表中的学生记录，查询结果按姓名进行升序排序。

SELECT * FROM stu_info ORDER BY name

19、显示“stu_info”表中的学生记录，查询结果按班级编号进行升序排序，同一班级的学生按照学号降序排序。

SELECT * FROM stu_info ORDER BY class_id,stu_id DESC

20、统计“stu_info”表中各班的人数，在查询结果中显示班级编号和该班人数。

SELECT class_id as 班级编号,count(class_id) as 人数

FROM stu_info GROUP BY class_id

21、将“stu_info”表中总人数大于或等于2人的班级编号和人数显示出来。

SELECT class_id as 班级编号,count(class_id) as 人数

FROM stu_info GROUP BY class_id HAVING

count(class_id)>=2

22、查询每个学生的基本情况和每门课程的成绩。

SELECT stu_info.*,result_info.*

FROM stu_info,result_info

WHERE stu_info.stu_id=result_info.stu_id

23、查询每个学生每门课程的成绩，结果表中要显示“course_info”（课程信息表）中的课程名称等内容。

SELECT stu_info.*,course_info.*,result_info.*

FROM stu_info,course_info,result_info

WHERE stu_info.stu_id=result_info.stu_id AND

result_info.course_no=course_info.course_no

24、查询每个学生每门课程的成绩，结果表中只显示学号、姓名、学

期编号、课程编号和成绩几个字段。

SELECT stu_info.stu_id,name, term_no,course_no,result

FROM stu_info,result_info

WHERE stu_info.stu_id=result_info.stu_id

25、查询选修了课程编号为“001”的课程的学生的基本信息。

SELECT * FROM stu_info

WHERE stu_id IN

(SELECT stu_id FROM result_info

WHERE course_no='001')

26、查询与“张三”同班的学生信息。

SELECT * FROM stu_info

WHERE class_id=(SELECT class_id FROM stu_info WHERE

name='张三')

三、实验思考

嵌套查询有何特点？

(1) 每级查询的from 子句一般只包含一个表名。

(2) 一个嵌套查询总可以分解为若干个单表查询，总可以改写成连接查询。

(3) 若查询结果显示的属性名来自一个表，才可以写成嵌套查询。

(4) 子查询不能使用order by 子句，order by 只能用于最顶层的查询。

四、实验小结

实验五数据表的操作

实验日期：______年______月______日成绩：_____________

一、实验目的

1、掌握记录插入的方法。

2、掌握记录修改的方法。

3、掌握记录删除的方法。

二、实验内容

1、在 student 表中插入一行新记录，学号为95006，姓名为李光荣，性别为男，年龄为21，系为CS。

INSERT INTO student VALUES('95006','李光荣','男','21','CS')

2、95006 号学生选修了4 号课程，但尚无成绩。在sc 表中插入该信息。

INSERT INTO sc(sno,cno) VALUES('95006','4')

3、建立一个表student1，其结构与student 表相同。将表student 表中CS 系的学生信息复制到student1 表中。

INSERT INTO student1 SELECT * FROM student WHERE sdept='CS'

4、将表student 中IS 系的学生的学号、姓名和系别复制到student1 表中。

INSERT INTO student1(sno,sname,sdept)

5、将 student 表中所有学生的年龄增加1 岁。

UPDATE student SET sage=sage+1

6、将 student 表中所有系别CS 改为“计算机科学系”

UPDATE student SET sdept='计算机系 'WHERE sdept='CS'

7、将计算机科学系全体学生的成绩置0。

UPDATE sc SET grade=0

WHERE sno in(SELECT sno FROM student WHERE sdept='CS')

8、删除表student 中所计算机系的学生信息。

DELETE FROM student WHERE sdept='计算机系'

9、删除计算机科学系所有学生的选课记录。

DELETE FROM sc

WHERE sno in(SELECT sno FROM student WHERE sdept='CS')

三、实验思考

1、INSERT 语句的作用是什么？

INSERT 语句用于向已经存在的表中插入新的数据，可以直接在INSERT 语句中指定插入的数据，一次只能插入一行数据；也可以插入子查询的结果，一次可以插入若干行数据。

2、DELETE语句和DROP语句有何区别？

DELETE语句操作是针对表记录的，DROP语句操作对象是数据库、表、视图、存储过程、触发器等。

四、实验小结