数据库T-SQL上机实验及代码

时间：2024.4.27

实验5 T-SQL程序、函数、存储过程与触发器

【实验目的与要求】

1、掌握T-SQL程序编写的基本方法；

2、掌握SQLSERVER中自定义函数、存储过程、触发器的使用。

【实验准备】

实验数据：实验2创建的数据表及录入的数据记录

【实验内容与步骤】

1.在数据库中创建函数Distance(@x1 float,@y1 float,@x2 float,@y2 float)用于计算输入两点（@x1,@y1）和(@x2,@y2)的距离； create function Distance(@x1 float,@y1 float,@x2 float,@y2 float)

returns float

begin

declare @distance float

select @distance=sqrt((@x2-@x1)*(@x2-@x1)+(@y2-@y1)*(@y2-@y1))

return @distance

end

2.编写T-SQL程序，检查界点表是否存在距离工作区中心点大于1000m的点，如果有，则显示界址点及其距中心点的距离； declare @x_center float,@y_center float

select @x_center=(min(x)+max(x))/2,

@y_center=(min(y)+max(y))/2 from dbo.界址点

declare @x float,@y float,@Distance float

if exists(select * from dbo.界址点 where dbo.Distance(x,y,@x_center,@y_center)>10 ) begin

select 点号,x,y,dbo.Distance(x,y,@x_center,@y_center) from dbo.界址点

end

else

print '不存在'

3.创建存储过程sp_InsertZD，利用参数向宗地表插入一条记录，并判

断宗地表中是否存在相同宗地号，若存在则返回错误；然后判断街坊号、宗地图号是否合法，若不合法则提示错误；判断完成后，实现数据插入操作。测试存储过程sp_InsertZD。

create proc sp_insertZD @宗地号 int,@宗地图号 int,@所属街坊号 int, @Result varchar(50) output

BEGIN

IF Exists(select * from dbo.宗地 where 宗地号=@宗地号)

set @Result='错误'

ELSE

begin

IF Exists(select * from dbo.宗地 where 宗地图号=@宗地图号 and 所属街坊号=@所属街坊号)

set @Result='错误'

else

insert into dbo.宗地 (宗地号,宗地图号,所属街坊号) values(@宗地号,@宗地图号,@所属街坊号)

set @Result='添加成功'

end

End

4.创建存储过程sp_DeleteCLY，根据输入的测量员工号，判断数据库中是否存在该测量员测量的宗地图，若存在，则不允许删除，并给出提示，否则删除该测量员记录。测试存储过程sp_DeleteCLY。 create procedure sp_DeleteCLY @工号 int,@result varchar(50) output

begin

if exists (select 图号 from dbo.宗地图纸 where 测量员_工号=@工号)

set @result='不允许删除'

else

begin

delete from dbo.测量员 where 工号=@工号

End

declare @test varchar(50)

exec sp_DeleteCLY 43002,@test OUTPUT

PRINT @test

5.修改sp_DeleteCLY存储过程，在其中添加条件：若测量员为高级工程师，则不允许删除。测试存储过程sp_DeleteCLY。

alter proc dbo.sp_DeleteCLY @工号 varchar(20),@result varchar(20)output

begin

declare @职称 varchar (20)

select @职称=(select 职称 from 测量员 where @工号=工号)

if exists(select * from 测量员 where @职称='高级工程师' )

set @result='不允许删除'

end

declare @test varchar(20)

exec sp_DeleteCLY 430002,@test OUTPUT

PRINT @test

6.为界址点创建触发器TG_InsertJZD，对当前插入的记录进行有效检查，若当前界址点距离工作区中心超过1000m，则不允许插入，否则完成插入，并给出相应提示。

create trigger TG_InsertJZD

on 界址点

after insert

begin

declare @x_min float,@x_max float,@y_min float,@y_max float

select @x_min=min(x),@x_max=max(x),@y_min=min(y),@y_max=max(y) from 界址点

declare @x_center float,@y_center float

select @x_center=(@x_min+@x_max)/2,@y_center=(@y_min+@y_max)/2

declare @x float,@y float,@点号 int

if exists(select * from 界址点 dbo.Distance(@x,@y,@x_center,@y_center)>1000)

print ('插入不允许')

else

insert into dbo.界址点 (点号,x,y)

values(@点号,@x,@y)

End

where

7.修改TG_InsertJZD，在其中添加是否超出工作范围的判断，即利用实验四中创建的V_Envelope视图，从当前界址点中计算工作区范围（MBR），并判断当前插入点是否超出MBR，若超出则提示错误且不允许插入，否则完成插入，并给出提示。

USE [ff]

/****** Object: Trigger [dbo].[TG_InsertJZD] Script Date: 04/08/2013 18:25:23 ******/ SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER trigger [dbo].[TG_InsertJZD]

on [dbo].[界址点]

after insert

begin

declare @x_min float,@x_max float,@y_min float,@y_max float

select @x_min=min(x),@x_max=max(x),@y_min=min(y),@y_max=max(y) from 界址点

declare @x_center float,@y_center float

select @x_center=(@x_min+@x_max)/2,@y_center=(@y_min+@y_max)/2

declare @x float,@y float,@点号 int

if exists(select * from 界址点 where dbo.Distance(@x,@y,@x_center,@y_center)>1000)

print ('插入不允许')

else

if exists(select *from dbo.V_Envelope where (@x<x1 or @x>x2 or @y<y1 or @y>y2))

print ('插入不允许')

else

insert into dbo.界址点 (点号,x,y)

values(@点号,@x,@y)

end

第二篇：SQL_several_20xx_数据库_实验要求(代码)

要求使用SQL SERVER 2000企业管理器完成下列要求：

1、按要求创建数据库

1）数据库名为MYDB；

2）数据库中包含一个数据文件，逻辑文件名为MY_DB_DATA，操作系统文件名为MY_DB_DATA.MDF，文件的初始容量为5MB，最大容量为15MB，文件容量递增值为2MB；

3）事务日志的逻辑文件名为MY_DB_LOG，操作系统文件名为MY_DB_LOG.LDF，文件的初始容量为3MB，最大容量为10MB，文件容量递增值为1MB。

create database mydb

(

name='my_db_data',

filename='e:\my_db_data.mdf',

size=5mb,

maxsize=15mb,

filegrowth=10%

)

log on

(

name='my_db_log',

filename='e:\my_db_log.ldf',

size=3mb,

maxsize=10mb,

filegrowth=1mb

)

2、按要求对数据库进行修改

1）添加一个数据文件，逻辑文件名为MY_DB_DATA1，操作系统文件名为MY_DB_DATA1.NDF，文件的初始容量为3MB，最大容量为20MB，文件容量递增值为2MB；

2）将事务日志文件的最大容量改为20MB，文件容量递增值改为2MB。

alter database mydb

add file

(

name='MY_DB_DATA1',

filename= 'e:\MY_DB_DATA1.NDF',

size=3MB,

maxsize=20MB,

filegrowth=2MB

)

alter database mydb

modify file

(

name=my_db_log,

maxsize=20mb, filegrowth=2mb )

3、按要求创建、修改和删除基本表 1）创建基本表

SQLseveral20xx数据库实验要求代码

Create table Departments (

depid char(3) not null,

Depname char(10) not null, Memo varchar(60) )

SQLseveral20xx数据库实验要求代码

Create table Employees (

empid char(6) primary key, Name char(10) not null, Depid char(3) not null, Sex char(2) default '男', Age tinyint,

Address varchar(40) )

Create table tmp (

Id uniqueidentifier )

SQLseveral20xx数据库实验要求代码

2）修改表

（1）为Employees表添加一个日期型的出生日期（birthday）字段，添加一个浮点型的工资（salary）字段，删除年龄字段。

Alter table Employees add birthday datetime Alter table Employees add salary float Alter table Employees drop column age

（2）将Departments表的depid字段设为主键。 Alter table Departments add primary key(depid)

（3）将Employees表中的depid字段设为外键，与Departments表中的主键关联，名为FK_EMP_DEP。

Alter table Employees add constraint FK_EMP_DEP foreign key(depid) references Departments(depid)

（4）将Departments表中的depname字段长度改为20。 Alter table Departments alter column depname char(20)

（5）将Employees表中的salary字段的值限定在0至10000之间。 Alter table Employees add check(salary between 0 and 10000) 3）将tmp表删除 Drop table tmp

4、表数据插入、修改和删除

SQLseveral20xx数据库实验要求代码

insert into Departments values('1','开发部',null) insert into Departments values('2','集成部',null) insert into Departments values('3','销售部',null) insert into Departments values('4','服务部',null)

insert into Employees(empid,name,Depid,sex,birthday,address,salary) values('0001','王莉','1','女','1966-01-23','农大1-1-201',2100)

insert into Employees(empid,name,Depid,sex,birthday,address,salary) values('0002','李红','4','女

','1976-03-28','富苑2-1-301',2300)

insert into Employees(empid,name,Depid,sex,birthday,address,salary) values('0003','王亮','2','男','1982-12-09','富苑3-1-302',4300)

insert into Employees(empid,name,Depid,sex,birthday,address,salary) values('0004','张小阳','1','男','1960-07-30','金色家园11-3-201',null)

insert into Employees(empid,name,Depid,sex,birthday,address,salary) values('0005','刘大伟','3','男','1972-10-18','新苑4-4-102',3210)

insert into Employees(empid,name,Depid,sex,birthday,address,salary) values('0006','赵思嘉','3','女','1965-09-28','绿岛3-2-302',1342)

insert into Employees(empid,name,Depid,sex,birthday,address,salary) values('0007','李福祥','2','男','1979-08-10','明珠3-4-102',null)

insert into Employees(empid,name,Depid,sex,birthday,address,salary) values('0008','王晓荣','4','女','1974-10-01','农大13-4-302',null)

将数据文件“添加的数据.xls”中的数据导入到employees中。

2）修改数据

（1）将工资为空的职员，工资定为600。

Update employees set salary=600 where salary is null

（2）将王晓荣的名字改为王荣，王亮的名字改为王晓亮。

Update employees set name='王晓亮' where name='王亮'

Update employees set name='王荣' where name='王晓荣'

（3）将所有工资低于1500的职员工资涨200元。

Update employees set salary=salary+200 where salary<1500

3）删除数据

删除19xx年以前出生的员工信息。

Delete from employees where birthday<'1970-01-01'

5、查询

1）查询departments表中的所有信息。

Select * from departments

2）查询employees表中的所有数据，要求将标题行用汉语表示

select empid 雇员编号,name 雇员姓名,Depid 部门编号,sex 性别,birthday 出生日期,address 地址,salary 工资

from Employees

3）查询employees表中的部门号和性别，要求消除重复行。

Select distinct depid,sex from employees

4）查询月收入高于2500的员工姓名和所在的部门名。

select name,depname

from employees,Departments

where employees.depid=Departments.depid and salary>2500

5）查询employees表中工资超过1500，并且属于开发部的人员姓名和工资。

select name,salary

from employees,Departments

where employees.depid=Departments.depid and salary>1500 and depname='开发部' 或

select name,salary

from employees

where salary>1500 and depid =(select depid from Departments where depname='开发部')

6）查询employees表中姓王的职工的所有数据。

select *

from employees

where name like '王%'

7）查询employees表中姓名只含有两个字的职工姓名和住址。

select name,address

from employees

where name like '__'

8）查询employees表中所有20世纪70年代出生的职工信息。

select *

from employees

where birthday between '1970-01-01' and '1979-12-31'

9）找出employees表中所有未填写出生日期的职工信息。

select *

from employees

where birthday is null

10）查询所有开发部员工的平均工资。

Select avg(salary) 开发部员工平均工资

From employees

Where depid=(select depid from departments where depname='开发部')

11）显示工资最高的三位员工的信息。

select top 3 *

from employees

order by salary desc

12）查询所有员工中工资最高的，最低的和平均工资。

select max(salary) 最高工资,min(salary) 最低工资,avg(salary) 平均工资

from employees

13）统计各部门的人数

select depid,count(empid) 人数

from employees

group by depid

14）找出各部门中女性超过3名的部门名称。

select depname

from departments,employees

where departments.depid=employees.depid and sex='女'

group by depname

having count(depname)>3

6、视图

1）创建视图employees_view，视图包含字段empid,name,salary别名分别是员工号码，姓名和工资。

create view employees_view(员工号码,姓名,工资)

select empid ,name ,salary

from employees

select *

from employees_view

2）在视图employees_view中查询王晓亮的工资。

select 工资

from employees_view

where 姓名='王晓亮'

3）向视图employees_view中插入一行数据：0099，李小静，4312

Create trigger emp_tri

on employees_view

instead of insert

begin

declare @empid char(6),@name char(10),@salary float,@depid char(3)

set @depid=1

select @empid=员工号码,@name=姓名,@salary=工资

from inserted

insert into employees(empid,name,depid,salary)

values(@empid,@name,@depid,@salary)

end

insert into employees_view values('0099','李小静',4312)

7、索引

为表employees中的name列创建索引，要求索引名为IDX_name，索引类型为非聚集索引。 create index IDX_name on employees(name)

8、查询Employees员工的姓名、住址和收入水平，2000元以下显示为低收入，2000~3000元显示为中等收入，3000元以上显示为高收入。

select name 姓名,address 住址,收入水平=

case

when salary <2000 then '低收入'

when salary between 2000 and 3000 then '中等收入'

when salary >3000 then '高收入'

end

from Employees

9、用存储过程实现，查询指定部门名称的员工信息

create proc emp_info @depname char(10)

begin

select *

from employees

where depid=(select depid from departments where depname=@depname)

end

emp_info '集成部'

10、创建用SQL SERVER身份登录的用户USER1，并给用户授予查询employees表和修改表中salary列的权限。

Sp_addlogin USER1,1,mydb

use mydb

Sp_adduser user1,user1

grant select,update(salary)

on employees

to user1

11、备份数据库，拷贝数据库对应的操作系统文件，保存查询分析器中的代码。

8、设计题

为某超市公司设计一个数据库系统来管理该公司的业务信息。该超市公司的业务管理规则如下：

（1）该超市公司有若干仓库和若干连锁商店，供应若干商品。

（2）每个商店有一个经理和若干收银员，每个收银员只在一个商店工作。

（3）每个商店销售多种商品，每种商品可在不同商店销售。

（4）每个商品编号只有一个商品名称，但不同的商品编号可以有相同的商品名称，每种商品可以有多种销售价格。

（5）超市公司的业务员负责商品的进货业务。

更多相关推荐：

数据库系统及应用上机实验报告: 数据库系统及应用上机实验报告实验1一实验目的理解SQLServer数据库的存储结构掌握SQLServer数据库的建立方法和维护方法二实验内容在SQLServer环境下建立数据库和维护数据库三程序源代码1CREA...
数据库上机实验报告sql server 20xx: 课程代码1010000450数据库Database学分3总学时48实验学时16面向专业信息与计算科学数学与应用数学一实验教学目标数据库是计算机科学与技术专业的专业必修课程课程内容主要包括数据模型关系代数关系数据...
数据库上机实验报告 sql server 20xx: 数据库上机实验报告实验一数据库实验11创建数据库实验目的1使用交互方法创建数据库2使用TransactSQL创建数据库3指定参数创建数据库4查看数据库属性实验内容1交互创建数据库1数据库名称为jxsk2查看数据...
数据库上机实验操作步骤: 数据库系统原理上机实验预备知识一本实验指导书采用的数据库例子见本课程参考用书数据库系统概论第三版P59StudentCourseSC数据库一个学生可以修多门课程一门课程可以被多个学生选修则学生课程之间的ER图如...
数据库上机实验报告: 数据库基础课堂实验报告20xx20xx学年第1学期班级姓名学号上机前准备充分不充分未准备上机考勤全到缺次上机操作认真不认真实验计划完成部分完成未完成实验报告完成情况全部按时完成部分完成基本未提交实验报告撰写质量...
数据库实验报告: 消除冲突和冗余后的E-R图三．逻辑结构设计stuunion(uno,uname,uyear,uplace)student(sno,sname,sage,clno)sjoin(uno,sno,joinyear)c…
SQL Server20xx 上机实验报告: SQLServer数据库设计实验指导实验1第一部分SQLServer管理工具和实用程序一实验目的掌握SQLServer管理工具和实用程序的使用方法及步骤二实验内容1服务器管理器的功能及其使用方法2企业管理器的功...

重邮数据库上机实验7: 实验七综合练习一实验目的熟练掌握SQL的数据定义数据操作查询功能二实验内容1启动SQLserver20xx服务器利用SQLServerManagementStudio管理器中提供的附加功能附加文件夹中提供的数据...
数据库实验报告 2: 数据库原理及系统实验报告数据库系统原理及应用实验报告学生姓名学号指导教师20xx101数据库原理及系统实验报告数据库上机实验报告一实验目标通过数据库系统概论书本知识学习和数据库原理与应用课程教学所授知识对教学管...
数据结构实验报告34354: 合肥师范学院实验报告册20xx20xx学年第2学期系别实验课程专业班级姓名学号指导教师计算机科学与技术系数据库原理计算机软件12级软件1班张志强1210431059潘洁珠实验一数据库基本操作一实验目的1熟悉MS...
数据库实验报告: 数据库实验报告组长：组员：班级：指导教师：主要任务：1.分析题意，画出E-R图，将E-R图转换为关系模式并进行模式优化。2.SQLServer2008环境下编写SQL代码,创建视图、触发器、存储过程和游标。组员…
数据库实验报告(20xx安工大周兵老师): 数据库概论实验报告书安徽工业大学计算机学院专业班级学号姓名指导教师周兵数据库系统概论实验报告书安徽工业大学计算机学院实验一数据定义语言实验日期20xx年12月11日实验目的熟悉Oracle上机环境及Oracle...

热门关注