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

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

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

use mastergo--启用高级选项exec sp_configure 'show advanced options',1go--重新配置reconfigurego--启用xp_cmdshell存储过程exec sp_configure 'xp_cmdshell',1goreconfigurego--使用dos命令创建SQL数据库存放目录 ,我的位于F:\2010SummerWork(因个人电脑而异)exec xp_cmdshell 'mkdir F:\2010SummerWork'go--创建HR数据库if exists(select name from sysdatabases where name='HR')drop database HRgoCreate database HRon 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 HRgoif exists(select * from sysobjects where name='Employee')drop table Employeegocreate 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 Employeeaddconstraint 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_workgocreate 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_workaddconstraint 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') andDepartNo=(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 Employeego--运行后的结果完全符合题目的要求.--而以前我们使用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 '三级' endfrom Employeego

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

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

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

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

--此题没有什么难点,就是用到了两个函数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_EmployeeNogocreate procedure Add_EmployeeNo(@EmpNo nvarchar(20) output)as beginset @EmpNo= dbo.AddEmpNo()enduse HRgoDECLARE @EmpNo1 nvarchar(20) exec dbo.Add_EmployeeNo @EmpNo1 outputprint @EmpNo1--创建一个删除员工的存储过程(参数为员工编号),删除员工的同时删除员工加工记录,为保证完整性请使用事务if exists(select * from sys.objects where name='Del_EmployeeNo' and type='p')drop procedure Del_EmployeeNogocreate procedure Del_EmployeeNo(@EmpNo nvarchar(20))as beginbegin transactionDECLARE @err intdelete from Emp_work where id=@EmpNoset @err+=@@ERRORdelete from Employee where id=@EmpNoset @err+=@@ERRORif(@err<>0)beginprint '删除失败'rollback tranendelsebeginprint '删除成功'commit tranendend--为员工加工记录添加DML触发器,如果要删除加工记录则拒绝删除if exists(select * from sys.objects where name ='DEL_Employee_work' and type='tr')drop trigger DEL_Employee_workgocreate trigger DEL_Employee_workon Emp_work for deleteasbeginbegin tranprint '拒绝删除员工加工记录'rollback tranend

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

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

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

BEGINDECLARE @l int=1DECLARE @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+1FETCH NEXT FROM curObject INTO @drid--继续查询下一条数据END  close curObject  deallocate curObjectEND

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

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

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脚本导出建库代码)