SQL使用DOS命令建库。建表，添加约束，标量值函数，存储过程，触发器,游标

这些代码是很久以前写的，不知怎么找出来了。贴在这里吧。主要是使用DOS建数据库。建表，添加约束，标量值函数，存储过程，触发器。

代码建库:
为了复习在SQL中使用DOS命令,开头便启用了高级选项,详细请看代码.

use master
go
--启用高级选项
exec sp_configure 'show advanced options',1
go
--重新配置
reconfigure
go
--启用xp_cmdshell存储过程
exec sp_configure 'xp_cmdshell',1
go
reconfigure
go
--使用dos命令创建SQL数据库存放目录 ,我的位于F:\2010SummerWork(因个人电脑而异)
exec xp_cmdshell 'mkdir F:\2010SummerWork'
go

--创建HR数据库
if exists(select name from sysdatabases where name='HR')
drop database HR
go
Create database HR
on Primary
(
name='HR_data',
fileName='F:\2010SummerWork\HR_data.mdf',
size=5MB,
filegrowth=5%,
maxSize=50MB
)
log on
(
name='HR_log',
fileName='F:\2010SummerWork\HR_log.ldf',
size=5MB,
filegrowth=5%,
maxSize=20MB
)

--创建数据库表
use HR
go
if exists(select * from sysobjects where name='Employee')
drop table Employee
go
create table Employee
(
id nvarchar(100) primary key not null,
name nvarchar(20) not null,
sex nvarchar(5) not null,
age int not null,
phone nvarchar(50),
DepartNo nvarchar(50) not null
)
--添加约束
alter table Employee
add
constraint DF_Sex default('男') for sex,
constraint CK_id check(id like 'GS_%')

if exists(select * from sysobjects where name='Emp_work' and type='U')
drop table Emp_work
go
create table Emp_work
(
id nvarchar(100)  primary key not null,
startTime date not null,
endTime date not null,
work_order nvarchar(50) not null
)
alter table Emp_work
add
constraint FK_id_id foreign key(id) references Employee(id)

--为了方便后面题目,此处我多插入了一条数据
insert into Employee values('GS_001','张三','男',28,'0712-232323','D_001')
insert into Employee values('GS_002','李四','女',33,'0712-242424','D_002')
insert into Employee values('GS_003','王五','男',38,'0712-342323','D_001')
insert into Employee values('GS_004','王6','男',30,'0712-452323','D_001')
--测试数据,测试的id不符合默认约束
insert into Employee values('GX_003','王五','男',38,'0712-342323','D_001')


--年龄比GS_001大,且和GS_003是同一部门的员工信息
select * from Employee where id in(select id from Employee where age>(select age from Employee where id='GS_001') and

DepartNo=(select DepartNo from Employee where id='GS_003') and id <> 'GS_003')
--此句结果中去掉了GS_003(我的理解是和GS_003是同一部门的员工信息应该不包括他本人,当然你也可以把and id <>

'GS_003'这句去掉)

--每5岁为一个级别,25--40 注:可以使用else用于其中任何一个级别
select id 员工编号,name 姓名,age 年龄,
case  when (age between 25 and 30)then '一级'
         when (age between 31 and 35)then '二级'
         when (age between 36 and 40)then '三级' end as 级别
from Employee
go

--运行后的结果完全符合题目的要求.
--而以前我们使用case when向来是
case when   then    (else) end ,
case when   then    (else) end ,
case when   then    (else) end ......

--原始做法
select id 员工编号,name 姓名,age 年龄,
case when (age between 25 and 30)then '一级' end,
case when (age between 31 and 35)then '二级' end,
case when (age between 36 and 40)then '三级' end
from Employee
go

而这样不可能达到作业的要求.我本人是不擅长SQL和HTML的.此处为了达到作业的要求,不得不绞尽脑汁.发现在我们习

已为常的用惯了的case when语句中尽然可以有几个when同时使用,而只使用一个case和end.可见kiss(case)的力量是多么的

伟大.    ^_~   嘿嘿(还是有女朋友好,没有的快去找,要不就卡这题这了).

--建立一个标量值函数,要求返回一个员工编号,假设已有员工编号为GS_001,那么下一个是GS_002
create function AddEmpNo() returns nvarchar
as
begin
DECLARE @lastEmpNo nvarchar(100)
DECLARE @lastEmpNo1 int
select @lastEmpNo=id from Employee
set @lastEmpNo1=substring(@lastEmpNo,4,10)
set @lastEmpNo1=@lastEmpNo1+1
if len(@lastEmpNo1)=1
set @lastEmpNo='GS_00'+convert(nvarchar(100),@lastEmpNo1)
else if len(@lastEmpNo1)=2
set @lastEmpNo='GS_0'+convert(nvarchar(100),@lastEmpNo1)
else if len(@lastEmpNo1)>=3
set @lastEmpNo='GS_'+convert(nvarchar(100),@lastEmpNo1)
print @lastEmpNo
end

--此题没有什么难点,就是用到了两个函数substring和len,前者用于截取字符串得到后面的数字,由于001接收后变成了1,因

此此处需要使用len判断一下是几位数.如果是1位数(例如1),则前面要加2个0,使用字符串接收后变为001;如果是2位数(例如

10)则前面要加1个0,使用字符串接收后变为010;如果大于3位数(例如100),则不用加0;

substring后面的第3个参数为10,我想一个有10位数的员工的公司到现在还没有吧.10位够了.(注:使用len截取后的一定是数字,否则

用int类型接收时不能隐式转换为数字而出错)

 

后面的3题没有什么难度,一题是创建一个存储过程,一题是创建一个DML触发器.略有基础的人都可以做出来.

--创建一个存储过程,调用上面的函数,返回员工编号
if exists(select * from sys.objects where name='Add_EmployeeNo' and type='p')
drop procedure Add_EmployeeNo
go
create procedure Add_EmployeeNo
(
@EmpNo nvarchar(20) output
)
as 
begin
set @EmpNo= dbo.AddEmpNo()
end

use HR
go
DECLARE @EmpNo1 nvarchar(20) 
exec dbo.Add_EmployeeNo @EmpNo1 output
print @EmpNo1

--创建一个删除员工的存储过程(参数为员工编号),删除员工的同时删除员工加工记录,为保证完整性请使用事务
if exists(select * from sys.objects where name='Del_EmployeeNo' and type='p')
drop procedure Del_EmployeeNo
go
create procedure Del_EmployeeNo
(
@EmpNo nvarchar(20)
)
as 
begin
begin transaction
DECLARE @err int
delete from Emp_work where id=@EmpNo
set @err+=@@ERROR
delete from Employee where id=@EmpNo
set @err+=@@ERROR
if(@err<>0)
begin
print '删除失败'
rollback tran
end
else
begin
print '删除成功'
commit tran
end
end


--为员工加工记录添加DML触发器,如果要删除加工记录则拒绝删除
if exists(select * from sys.objects where name ='DEL_Employee_work' and type='tr')
drop trigger DEL_Employee_work
go
create trigger DEL_Employee_work
on Emp_work for delete
as
begin
begin tran
print '拒绝删除员工加工记录'
rollback tran
end

SQL游标[下面的例子演示了将一个表其中一个字段每次累加1的情况]

如果有30条数据,更新之前reid都是1.

执行之后,reid变为:31,32,33,34,35,36.......61

BEGIN
DECLARE @l int=1
DECLARE @drid VARCHAR(50)
DECLARE curObject CURSOR FOR SELECT drid FROM dbo.dis_war_report--定义游标
OPEN  curObject  
FETCH NEXT FROM curObject INTO @drid--查询下一条数据
WHILE(@@FETCH_STATUS=0) --如果执行成功 
BEGIN      
UPDATE dbo.dis_war_report SET reid+=@l WHERE drid=@drid--更新数据
SET @l=@l+1
FETCH NEXT FROM curObject INTO @drid--继续查询下一条数据
END  
close curObject  
deallocate curObject
END

可能有人看到下面这条语句不明白后面的type是什么,怎么没学过,其实不然,只要你把书上的稍稍扩展一下而已

if exists(select * from sysobjects where name='Emp_work' and type='U')
drop table Emp_work
go

type为sys.objects(即sysobjects二者指向同一张表,可以理解为同义词)表中区别各自的类型,这样就允许不同类型同名
U表示user_table即用户表,凡是用户创建的表在这个表中就一定存在,删除它相当于删除用户创建的表,因为每条删除用户

表的语句都有一个delete触发器,它直接关联到用户创建的表.
P表示procedure(proc),即存储过程.凡是用户创建的存储过程都会出现在这里.
TR表示trigger即触发器
FN表示function即函数.
S即System_table系统表,SQL2008有4张系统表(master,model,msdb,tempdb),方便用户操作.

PK表示primary key主键
F不表示function而表示foreign key即外键
其它的还请各位自己去试试看.

 

编者:很拽的土豆   

郑重声明:版权所有,翻版不究.欢迎指正学习交流,谢谢.
由于我想要复习代码建库,并不是手动建库,此处使用纯代码建库(开发大型数据库时不推荐因为浪费时间,可使用导出

SQL脚本导出建库代码)