MSSQL-基础适用语句集

--创建文件夹
exec xp_cmdshell 'mkdir F:\Student','no_output'
go
--判断数据库是否存在，存在删除之
if exists(select  * from sys.databases where [name]='StuDB')
   drop database StuDB
go
create database StuDB
   on primary
  (
          name='StuDB_mdf',
          filename='F:\Student\StuDB.mdf',
          size=5,
          maxsize=20,
          filegrowth=10%
   )
   ,
   filegroup file1
  (
   name='StuDB_ndf1',
   filename='F:\Student\StuDB_ndf1.ndf',
           size=4,
           filegrowth=10%
   ),
    (
   name='StuDB_ndf2',
   filename='F:\Student\StuDB_ldf2.ndf'
    )
  log on --日志文件
    (
      
   name='StuDB_ldf1',
   filename='F:\Student\StuDB_ldf1.ldf'
    ),
     (
       name='StuDB_ldf2',
  filename='F:\Student\StuDB_ldf2.ldf'
      )
go
--修改表结构，添加约束

alter  table studentinfo --添加一列
  add StuTel varchar(20)
alter  table  Studentinfo --删除一列
  drop column StuTel
alter  table  Studentinfo --修改数据类型
  alter column StuAddress varchar(100)

alter table Subject  --添加主键
  add constraint PK_SubID  primary key (SubID)
alter table  StuMarks --对一个表，多个约束可以一起加
    add constraint  PK_StuId_SubID primary key (stuid,subID),
        constraint DF_Score default 0 for score,
        constraint CK_Score check(score between 0 and 100) ,
        constraint FK_Subid foreign key (subID) references Subject(subID)
        
alter  table  StuMarks  --删除约束
   drop constraint DF_Score
--userInfo表
--自动增长列×××××××××××××
alter table userInfo
add constraint customerID identity
--身份证长度只能是15位或者是18位数字
alter table userInfo
add constraint CK_PID check (len(PID)>15  or len(PID)>18)
--电话格式或者是手机格式
alter table userInfo
add constraint CK_telephone check ((telephone like '____-_______') or ( len(telephone)>13))
--cardInfo表
--卡号的格式(www.52mvc.com)
alter table cardInfo
add constraint CK_cardID check (cardID like '1010 3576%')
--默认为RMB
alter table cardInfo
add constraint DF_curType default ('RMB') for curType
--活期/定活两便/定期
alter table cardInfo
add constraint CK_savingType check (savingType='活期' or savingType='定活两便' or savingType='定期')
--默认为系统当前日期
alter table cardInfo
add constraint DF_openDate default (getdate()) for openDate
--不低于1元
alter table cardInfo
add constraint CK_openMoney check (openMoney>=1)
--不低于1元,否则将销户
--6位数字，开户时默认为6个“8”
alter table cardInfo
add constraint CK_pass check (len(pass)=6),
constraint DF_pass default ('888888') for pass
--是/否值，默认为”否”
alter table cardInfo
add constraint CK_IsReportLoss check (IsReportLoss='是' or IsReportLoss='否'),
constraint DF_IsReportLoss default('否') for IsReportLoss
--外键,表示该卡对应的顾客编号，一位顾客允许办理多张卡号
alter table cardInfo
add constraint FK_customerID foreign key (customerID) references userInfo(customerID)
--translInfo 表
--默认为系统当前日期
alter table transInfo
add constraint DF_transDate default(getdate()) for transDate
--外健，可重复索引 ××××××××××××××× 本文来自:http://www.52mvc.com/showtopic-1153.aspx
alter table transInfo
add constraint FK_cardID foreign key (cardID) references cardInfo(cardID)
--只能是存入/支取
alter table transInfo
add constraint CK_transType check (transType='存入' or transType='支取')
--大于0
alter table transInfo
add constraint CK_transMoney check (transMoney>0)



--添加用户
use  master
go
--windows登录方式    
  exec sp_grantlogin 'MICROSOF-ABDD91\newerwp' --赋权newerwp访问服务器 ，MICROSOF-ABDD91为机器名，newerwp为登录名

--declare声明变量

--高级查询，子查询
--事务过程
declare @..................
set @sum_error=0
begin transaction
set @[email protected]_error+@@error
if(@sum_error<>0)  
    begin
  print '回滚事务'
  rollback transaction
end
else
begin
  print '事务成功'
  commit transaction
end
--创建视图   注意创建视图过程中不能进行排序和分组，只能在查询视图的时候排序和分组
if exists(select*from sysobjects where name='bbsUsers_view')
drop view bbsUsers_view
go
create view bbsUsers_view
as
select UID,Uname,Uclass,Upoint from bbsUsers
go
select*from bbsUsers_view
order by Uclass desc,Upoint desc
--创建索引
if exists(select name from sysindexes where name='IX_Uclass')
drop index bbsUsers.IX_Uclass
go
create index IX_Uclass
on bbsUsers(Uclass)
go
--存储过程
create procedure pro_find
    @count int output,
    @name varchar(20)='liuyu'
as
     查询代码........
go
--调用存储过程
declare @count int,@name varchar(20)
exec pro_find @count,@name

--insert触发器
if exists(select * from sysobjects
where name='trig_employe_insert')
drop trigger trig_employe_insert
go
create trigger trig_employe_insert
on employe
for insert
as
declare @name varchar(10)
select @name=name
from inserted
--插入日志表
insert into sys_log
values('插入员工表','新添加员工名为'+@name,default)
go


--delete 触发器
if exists(select * from sysobjects
where name='trig_employe_delete')
drop trigger trig_employe_delete
go
create trigger trig_employe_delete
on employe
for delete
as
declare @name varchar(10),@sex char(2)
select @name=name,@sex=sex
from deleted
--添加到退休员工表
insert into retiree
values(@name,@sex,default)
--添加日志信息
insert into sys_log
values('删除员工表','删除'+@name+'员工',default)
go
--update 触发器
if exists(select * from sysobjects
where name='trig_employe_update')
drop trigger trig_employe_update
go
create trigger trig_employe_update
on employe
for update
as
declare @old_post varchar(20),
  @new_post varchar(20),
  @name varchar(10)
select @name=name,@old_post=post
from deleted

select @new_post=post
from inserted

--添加日志
insert into sys_log
values('更新员工表','将'+@name+'的职务从'+@old_post+'调整为'+@new_post,default)
go

--update列的触发器
if exists(select * from sysobjects
where name='trig_retiree_update')
drop trigger trig_retiree_update
go
create trigger trig_retiree_update
on retiree
for update
as
if update(RETIREE_TIME)
begin
  raiserror('退休时间不能被修改',16,1)
  rollback transaction
end
go