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

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

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

[sql]  view plain  copy

 print ?

1. use master  
2. go  
3. --启用高级选项  
4. exec sp_configure 'show advanced options',1  
5. go  
6. --重新配置  
7. reconfigure  
8. go  
9. --启用xp_cmdshell存储过程  
10. exec sp_configure 'xp_cmdshell',1  
11. go  
12. reconfigure  
13. go  
14. --使用dos命令创建SQL数据库存放目录 ,我的位于F:\2010SummerWork(因个人电脑而异)  
15. exec xp_cmdshell 'mkdir F:\2010SummerWork'  
16. go  
17.   
18. --创建HR数据库  
19. if exists(select name from sysdatabases where name='HR')  
20. drop database HR  
21. go  
22. Create database HR  
23. on Primary  
24. (  
25. name='HR_data',  
26. fileName='F:\2010SummerWork\HR_data.mdf',  
27. size=5MB,  
28. filegrowth=5%,  
29. maxSize=50MB  
30. )  
31. log on  
32. (  
33. name='HR_log',  
34. fileName='F:\2010SummerWork\HR_log.ldf',  
35. size=5MB,  
36. filegrowth=5%,  
37. maxSize=20MB  
38. )  
39.   
40. --创建数据库表  
41. use HR  
42. go  
43. if exists(select * from sysobjects where name='Employee')  
44. drop table Employee  
45. go  
46. create table Employee  
47. (  
48. id nvarchar(100) primary key not null,  
49. name nvarchar(20) not null,  
50. sex nvarchar(5) not null,  
51. age int not null,  
52. phone nvarchar(50),  
53. DepartNo nvarchar(50) not null  
54. )  
55. --添加约束  
56. alter table Employee  
57. add  
58. constraint DF_Sex default('男') for sex,  
59. constraint CK_id check(id like 'GS_%')  
60.   
61. if exists(select * from sysobjects where name='Emp_work' and type='U')  
62. drop table Emp_work  
63. go  
64. create table Emp_work  
65. (  
66. id nvarchar(100)  primary key not null,  
67. startTime date not null,  
68. endTime date not null,  
69. work_order nvarchar(50) not null  
70. )  
71. alter table Emp_work  
72. add  
73. constraint FK_id_id foreign key(id) references Employee(id)  
74.   
75. --为了方便后面题目,此处我多插入了一条数据  
76. insert into Employee values('GS_001','张三','男',28,'0712-232323','D_001')  
77. insert into Employee values('GS_002','李四','女',33,'0712-242424','D_002')  
78. insert into Employee values('GS_003','王五','男',38,'0712-342323','D_001')  
79. insert into Employee values('GS_004','王6','男',30,'0712-452323','D_001')  
80. --测试数据,测试的id不符合默认约束  
81. insert into Employee values('GX_003','王五','男',38,'0712-342323','D_001')  
82.   
83.   
84. --年龄比GS_001大,且和GS_003是同一部门的员工信息  
85. select * from Employee where id in(select id from Employee where age>(select age from Employee where id='GS_001') and  
86.   
87. DepartNo=(select DepartNo from Employee where id='GS_003') and id <> 'GS_003')  
88. --此句结果中去掉了GS_003(我的理解是和GS_003是同一部门的员工信息应该不包括他本人,当然你也可以把and id <>  
89.   
90. 'GS_003'这句去掉)  
91.   
92. --每5岁为一个级别,25--40 注:可以使用else用于其中任何一个级别  
93. select id 员工编号,name 姓名,age 年龄,  
94. case  when (age between 25 and 30)then '一级'  
95.          when (age between 31 and 35)then '二级'  
96.          when (age between 36 and 40)then '三级' end as 级别  
97. from Employee  
98. go  
99.   
100. --运行后的结果完全符合题目的要求.  
101. --而以前我们使用case when向来是  
102. case when   then    (else) end ,  
103. case when   then    (else) end ,  
104. case when   then    (else) end ......  
105.   
106. --原始做法  
107. select id 员工编号,name 姓名,age 年龄,  
108. case when (age between 25 and 30)then '一级' end,  
109. case when (age between 31 and 35)then '二级' end,  
110. case when (age between 36 and 40)then '三级' end  
111. from Employee  
112. go  

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

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

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

[sql]  view plain  copy

 print ?

1. --建立一个标量值函数,要求返回一个员工编号,假设已有员工编号为GS_001,那么下一个是GS_002  
2. create function AddEmpNo() returns nvarchar  
3. as  
4. begin  
5. DECLARE @lastEmpNo nvarchar(100)  
6. DECLARE @lastEmpNo1 int  
7. select @lastEmpNo=id from Employee  
8. set @lastEmpNo1=substring(@lastEmpNo,4,10)  
9. set @lastEmpNo1=@lastEmpNo1+1  
10. if len(@lastEmpNo1)=1  
11. set @lastEmpNo='GS_00'+convert(nvarchar(100),@lastEmpNo1)  
12. else if len(@lastEmpNo1)=2  
13. set @lastEmpNo='GS_0'+convert(nvarchar(100),@lastEmpNo1)  
14. else if len(@lastEmpNo1)>=3  
15. set @lastEmpNo='GS_'+convert(nvarchar(100),@lastEmpNo1)  
16. print @lastEmpNo  
17. 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触发器.略有基础的人都可以做出来.

[sql]  view plain  copy

 print ?

1. --创建一个存储过程,调用上面的函数,返回员工编号  
2. if exists(select * from sys.objects where name='Add_EmployeeNo' and type='p')  
3. drop procedure Add_EmployeeNo  
4. go  
5. create procedure Add_EmployeeNo  
6. (  
7. @EmpNo nvarchar(20) output  
8. )  
9. as   
10. begin  
11. set @EmpNo= dbo.AddEmpNo()  
12. end  
13.   
14. use HR  
15. go  
16. DECLARE @EmpNo1 nvarchar(20)   
17. exec dbo.Add_EmployeeNo @EmpNo1 output  
18. print @EmpNo1  
19.   
20. --创建一个删除员工的存储过程(参数为员工编号),删除员工的同时删除员工加工记录,为保证完整性请使用事务  
21. if exists(select * from sys.objects where name='Del_EmployeeNo' and type='p')  
22. drop procedure Del_EmployeeNo  
23. go  
24. create procedure Del_EmployeeNo  
25. (  
26. @EmpNo nvarchar(20)  
27. )  
28. as   
29. begin  
30. begin transaction  
31. DECLARE @err int  
32. delete from Emp_work where id=@EmpNo  
33. set @err+=@@ERROR  
34. delete from Employee where id=@EmpNo  
35. set @err+=@@ERROR  
36. if(@err<>0)  
37. begin  
38. print '删除失败'  
39. rollback tran  
40. end  
41. else  
42. begin  
43. print '删除成功'  
44. commit tran  
45. end  
46. end  
47.   
48.   
49. --为员工加工记录添加DML触发器,如果要删除加工记录则拒绝删除  
50. if exists(select * from sys.objects where name ='DEL_Employee_work' and type='tr')  
51. drop trigger DEL_Employee_work  
52. go  
53. create trigger DEL_Employee_work  
54. on Emp_work for delete  
55. as  
56. begin  
57. begin tran  
58. print '拒绝删除员工加工记录'  
59. rollback tran  
60. end  

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

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

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

[sql]  view plain  copy

 print ?

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

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

[sql]  view plain  copy

 print ?

1. if exists(select * from sysobjects where name='Emp_work' and type='U')  
2. drop table Emp_work  
3. 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脚本导出建库代码)