这些代码是很久以前写的,不知怎么找出来了。贴在这里吧。主要是使用DOS建数据库。建表,添加约束,标量值函数,存储过程,触发器。
代码建库:
为了复习在SQL中使用DOS命令,开头便启用了高级选项,详细请看代码.
- use master
- go
-
- exec sp_configure 'show advanced options',1
- go
-
- reconfigure
- go
-
- exec sp_configure 'xp_cmdshell',1
- go
- reconfigure
- go
-
- exec xp_cmdshell 'mkdir F:\2010SummerWork'
- go
-
-
- 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')
-
- insert into Employee values('GX_003','王五','男',38,'0712-342323','D_001')
-
-
-
- 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'这句去掉)
-
-
- 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 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)的力量是多么的
伟大. ^_~ 嘿嘿(还是有女朋友好,没有的快去找,要不就卡这题这了).
-
- 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
-
-
-
- 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脚本导出建库代码)