数据库第次上机

1、实验6 T-SQL语言实验目的：1、 掌握Transact-SQL语言中的变量、运算符、函数；2、 掌握Transact-SQL语句编写批处理和流程控制程序。3、 掌握游标的创建，打开，数据读取，关闭，销毁的基本方法实验内容和步骤：首先完成数据库和表的创建（具体代码，见“建库.sql”，可用文本编辑器打开）。具体操作：首先在D盘下新建文件夹TestDB，打开“查询分析器”->点击工具栏中的图标->找到“建库.sql”，打开 ->执行。即可得到实验所需数据库和数据。Transact-SQL语言是在SQL Server 2000 中使用的程序设计语言。不但包含了标准SQL语言部分

2、，而且为了满足实际应用中的编程需要，在Transact-SQL语言中还另外增加了一些语言要素，它们包括注释、变量、运算符、函数和流程控制语句等。所有的语句都在查询分析器中执行。1. Transact-SQL语句实验内容1： 返回在程序执行过程中上一条SQL语句影响的记录数（全局变量ROWCOUNT），注意观察结果窗口。use teach-打开teach 数据库GO-查询学生表中的记录SELECT * FROM Student-查询上一条SELECT语句影响的记录数SELECT ROWCOUNT AS 第一次查询返回的记录数GO实验内容2： 查询学生表的人数，将返回的记录数赋给局部变量RowsU

3、SE teachGODECLARE Rows int-声明局部变量-给局部变量赋值SET Rows=(SELECT COUNT(*) FROM Student)-显示局部变量的值SELECT Rows AS SELECT返回的记录数GO实验内容3： 设置一个局部变量为表，进行插入和查询操作- 定义变量为临时表DECLARE Table_Example TABLE (Col_num int PRIMARY KEY, Col_text char (50)- 向该变量中添加两行内容INSERT INTO Table_Example VALUES (1, 'this is a')INS

4、ERT INTO Table_Example VALUES (2, 'table data type example')- 从临时表中查询SELECT * FROM Table_ExampleGO实验内容4：(练习，自己试着写一下)设置一个局部变量其值为选修了课程的人数，并显示提示： DECLARE Rows int-声明局部变量-给局部变量赋值SET Rows=(SELECT COUNT(distinct sno) FROM SC )-显示局部变量的值SELECT Rows AS 选课人数GO实验内容5：检查sysdatabases中的每一个数据库，使用数据库标识号来确定数据

5、库名称。USE master-打开master 数据库GO-DB_NAME 是一个函数，功能是根据数据库标识号，来返回数据库的名称SELECT dbid , DB_NAME(dbid) AS DB_NAMEFROM sysdatabases ORDER BY dbidGO实验内容6：从数据库中返回学生表的第2列的名称USE teachGO-定义局部变量，用于存放学生表的ID号DECLARE 学生表ID INT-OBJECT_ID是一个函数，功能是返回数据库对象的编号SET 学生表ID= OBJECT_ID('Student')-COL_NAME是一个函数， 返回指定表中指定字段

6、的名称，即列名SELECT COL_NAME(学生表ID,2) AS 学生表中第2列的名称GO实验内容7：简单年、月、日函数SELECT MONTH('03/12/1998'), DAY('03/12/1998'),YEAR('03/12/1998')实验内容8：从系统当前日期中返回系统当前的年份数、月份数和天数。- GETDATE()函数返回系统当前日期SELECT YEAR(GETDATE() AS 当前年份, MONTH(GETDATE() AS 当前月份, DAY(GETDATE() AS 当前天数GO实验内容9：自己编写函数。编写一个函

7、数，名称为“函数_学生选课数”，要求根据输入的学生学号，求此学生的选课数。USE teachGO-通过CREATE FUNCTION语句创建用户的自定义函数“函数_学生选课数”CREATE FUNCTION 函数_学生选课数(xuehao AS char(9) -函数的输入参数)RETURNS int -返回结果类型BEGIN -函数体 DECLARE 选课数 AS int SELECT 选课数 =COUNT(cno) FROM SC WHERE Sno=xuehao RETURN 选课数ENDGO-使用自定义函数，求选课表（SC）中每位学生的选课数SELECT DISTINCT Studen

8、t.Sno,Sname,选课数=dbo. 函数_学生选课数(SC.Sno)FROM Student,SCWHERE Student .Sno=SC.SnoGO实验内容10: (练习，自己试着写一下)练习自定义函数，求每门课程的选课人数提示：USE teachGOCREATE FUNCTION 函数_选课人数(cno AS char(9) )RETURNS int BEGIN DECLARE 选课人数 AS int SELECT 选课人数 =COUNT(sno) FROM SC WHERE cno=cno RETURN 选课人数ENDGO-使用自定义函数，查询每门课的选课人数（注：没有人选的课程

9、，不会出现）SELECT DISTINCT Course.Cno ,Cname ,选课人数=dbo. 函数_选课人数(SC.Cno)FROM Course,SCWHERE Co=SC.Cno GO-使用自定义函数，通过左外连接，保留了左边被舍弃的元组SELECT DISTINCT Course.Cno ,Cname ,选课人数=dbo. 函数_选课人数(SC.Cno)FROM Course left join SC on Co=SC.Cno GO2. 游标的创建和使用游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次以行或者多行前进或向后浏览数据

10、的能力。我们可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。T-SQL游标：该游标是基于Declare Cursor 语法，主要用于Transaction-SQL脚本、存储过程以及触发器中。Transaction-SQL游标在服务器处理由客户端发送到服务器的Transaction-SQL语句。使用游标要遵循以下顺序：声明游标打开游标读取数据关闭游标删除游标。1声明游标实验内容1：以下是一个符合SQL-92标准的游标声明，此游标是只读的，只能从头到尾顺序提取数据。 DECLARE S_CUR1 CURSOR FOR SELECT * FROM STUDE

11、NT FOR READ ONLY实验内容2：以下是一个符合T-SQL扩展的游标声明，此游标是动态游标，可前后滚动，可对姓名列进行修改。 DECLARE S_CUR2 CURSOR SCROLL DYNAMIC FOR SELECT * FROM STUDENT FOR UPDATE OF SName2. 打开游标声明游标之后，要使用游标提取数据，必须先打开游标。使用OPEN语句可以打开游标。打开游标后，可以使用全局变量CURSOR_ROWS查看游标中数据行的数目。实验内容3：打开游标S_CUR1，输出其行数 OPEN S_CUR1 SELECT '游标s_cur1的数据行数'=

12、CURSOR_ROWS注意：通过联机丛书来查询CURSOR_ROWS，来理解其值的含义3读取数据游标打开后，就可以使用FETCH命令从中读取数据。具体语句格式见联机丛书。实验内容4：从游标S_CUR1中提取数据，只能用NEXT提取数据。 FETCH NEXT FROM S_CUR1提示：选中该语句，点击执行，查看结果。再点击执行，查看结果。重复执行。实验内容5：从游标S_CUR2中提取数据：OPEN S_CUR2-读取游标第一行FETCH FIRST FROM S_CUR2-读取下一行FETCH NEXT FROM S_CUR2-读取上一行FETCH PRIOR FROM S_CUR2-读取最

13、后一行FETCH LAST FROM S_CUR2-读取当前行的上二行FETCH RELATIVE-2 FROM S_CUR24关闭游标游标使用完后，要及时关闭游标，以释放当前的结果集并解除定位在该游标记录行上的游标锁定。实验内容6：关闭游标S_CUR1CLOSE S_CUR15删除游标关闭一个游标后，其数据结构仍存储在系统中，需要的时候仍然可以再次使用OPEN语句打开和使用该游标。如果确定以后不再使用该游标，则可以删除游标，将游标占用的系统空间释放出来。实验内容7：删除游标S_CUR1DEALLOCATE S_CUR1实验7 存储过程实验目的：1、 理解存储过程的概念，掌握各种存储过程的创建

14、方法。2、 掌握查看、修改和删除存储过程的方法。3、 掌握执行存储过程的方法。实验内容：存储过程就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，并且这样的语句是放在数据库中的，还可以根据条件执行不同SQL语句。在Sql Server2000中，可以使用三种方法创建存储过程。1）使用创建存储过程向导创建存储过程。2）使用企业管理器创建存储过程。3）使用T-SQL语句中的CREATE PRODCECURE命令创建存储过程。具体步骤：打开查询分析器n 首先创建数据库和表n 创建存储过程n 执行存储过程，查看结果我们在这里掌握第三种方法，其他方法自行掌握。实验内容1：简单的

15、存储过程。功能为：查询student表中的内容 Create procedure query1_s As Select * from student go 执行语句为：exec query1_s实验内容2：带参数的存储过程。带了一个参数s_sno，功能为：查询指定学生的信息Create procedure query2_s s_sno char(9) As Select * from student where sno=s_sno go执行该存储过程的2种不同方法：1）exec query2_s '201010001'2）exec query2_s s_sno ='20

16、1010001'实验内容3：带输入和输出参数的存储过程 既有输入参数也有输出参数。在存储过程中查询后得到的数据可以利用输出参数进行回传。 功能为：查询指定学生的姓名 Create procedure query3_s s_sno char(9) , s_sname char(20) outputAs Select s_sname=sname from student where sno=s_sno go 执行存储过程： DECLARE ss_sno char(9) ,ss_sname char(20) SET ss_sno= '201010001' EXEC query

17、3_s ss_sno, ss_sname output SELECT ss_sno, ss_sname GO实验内容4：使用系统存储过程查看某一存储过程的参数及其数据类型。sp_help query2_s实验内容5：重命名存储过程 sp_rename query1_s, query1实验内容7：删除存储过程 Drop procedure query2_s实验内容8： (练习，自己试着写一下)编写一存储过程在student表中插入一条学生的记录，并执行。提示：IF EXISTS (SELECT name FROM sysobjects WHERE name = 's_insert' AND type = 'P') DROP PROCEDURE s_insertGOCREATE PROCEDURE s_insert no CHAR(9), name CHAR(20),sex CHAR(2),age SMALLINT,dept CHAR(20) AS INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES(no,name,sex,age,dept)GO-执行存储过程EXEC s_insert '