一些简单的SQL语句总计

--------检测数据库是否存在
use master
go
if exists(select * from sysdatabases where name='DBName')
drop database DBName

-----创建数据库
create database DBName
on
(
 /*数据文件的具体描述*/
 name='DBName_data', -----主数据文件的逻辑名称
 filename='D:/project/DBName_data.mdf',   --主数据文件的物理名称
 size=5mb,   --主数据文件的初始大小
 maxsize=100mb,   --主数据文件的最大值
 filegrowth=15%  --主数据文件的增长率
)
log on
(
 /*日志文件的具体描述，各参数含义同上*/
 name='DBName_log',
 filename='D:/project/DBName_data.mdf',
 size=2mb,
 filegrowth=1mb
)
go

------------删除数据库
drop database DBName

 

--------检测表是否存在
use DBName
go

if exists(select * from sysobjects where name='tableName')
drop table tableName

-------创建表
use DBName
go
create table tableName
(
 clName int identity(1,1) not null,  ---标识列，自动增长
 clName2 varchar(50) not null
)
go

--------删除表
drop table tableName

------创建约束

alter table tableName add
constraint PK_clName primary key(clName),     ---主键约束
constraint UQ_clName unique (clName),    ----唯一约束
constraint CK_clName check(clName between 0 and 100),     ---检查约束
constraint DF_clName default(100) for clName,     ----默认约束
constraint FK_clName foreign key(clName) references tbName(clName)   ---外键约束，后面是主表主键

 

----------删除约束
alter table tableName
 drop constraint PK_clName

-------添加Windows登陆账户
exec sp_grantlogin 'jbtraining/s26301' -----------jbtraining表示域，s26301表示域账户

-------添加SQL登录账户
exec sp_addlogin 'zhangsan','1234'  -----------账户名为zhangsan,密码为1234

-------创建数据库用户
exec sp_grantdbaccess 'zhangsan'    ---------登录账户为zhangsan,数据库用户默认和登录账户同名

------为数据库用户授权
grant select,insert,update on tableName to zhangsan   ----为用户分配对表tableName的插入，查询，更新权限
grant create table to zhangsan --------为用户分配创建表的权限

---------删除权限
revoke select on tableName to zhangsan       -----删除用户对表的查询权限

--------声明局部变量和赋值
declare @name varchar(10)   ----声明
declare @id int
set @name='123'   ----使用set赋值
select @name=clName from tableName where clName2=@name   -----使用select赋值

 

------全局变量
@@error    ---最后一个T-SQL错误的错误号
@@identity   ----最后一次插入的标识值
@@language   -----当前使用的语言的名称
@@max_connections   ----可以创建的同时连接的最大数目
@@rowcount    ----受上一个SQL语句影响的行数
@@servername    ----本地服务器的名称
@@servicename    ----该计算机上的SQL服务的名称
@@timeticks      ------当前计算机上每刻度的微秒数
@@trancount     -----当前连接打开的事务数
@@version        -----SQL Server的版本信息

------输出语句
print '服务器名'+@@servicename
select @@servicename as '服务器名'
-----数据类型转换
print '当前错误号'+convert(varchar(5),@@error)

------if-else语句
if(1=1)
 begin
  print '对'
 end
else
 begin
  print '错'
 end

-----while语句
declare @id int
declare @num int
set @id=1
while(1=1)
 begin
  print '对'
  set @num=@id+1
  if(@num>1)
   begin
    break
    print '结束'
   end
 end
 
-------case语句
declare @id int
declare @num int
set @id=3
set @num=case
   when @id=1 then 1
   when @id=0 then 0
   else 2
  end
print convert(varchar(2),@num)

----子查询
select * from tableName where clName=(select clName from tableName where clName2='123')

-----检查子查询结果是否为空
if exists(select clName from tableName where clName2='123')   ---如果不为空返回true，为空返回false

----事务
declare @rows int    ---插入前的总数据条数
declare @num int     ---插入后的总数据条数
select @rows=count(*) from tableName
begin transaction     ----开始事务
insert tableName values('123')
select @num=count(*) from tableName
if(@num>@rows)
begin
 commit transaction    ----提交事务
end
else
begin
 rollback transaction    ---回滚事务
end
go

----索引
use bank
go
----检查索引是否存在
if exists(select name from sysindexes where name='ix_clName2')
drop index tableName.ix_clName2

----创建索引
create clustered index ix_clName2     ----列中没有重复数据的创建聚集索引
on tableName(clName2)        ------指定表名和列名
with fillfactor=30   ----指定填充因子
go

create nonclustered index ix_clName2    ----列中有重复数据的创建非聚集索引
on tableName(clName2)        ------指定表名和列名
with fillfactor=30   ----指定填充因子
go

----按指定索引查询
select * from tableName with (index=ix_clName2) where clName2='123'

-----视图
use bank
go
----检查视图是否存在
if exists(select * from sysobjects where name='view_name')
drop view view_name

----创建视图
create view view_name
as
select 编号=clName,名称=tb1.clName2,学分=clName3
from tb1 left join tb2 on tb1.clName2=tb2.clName2
  
----使用视图
select * from view_name

--------存储过程
use bank
go
if exists(select * from sysobjects where name='proc_DB')
drop proc proc_DB

---创建带输入参数的存储过程
create proc proc_DB
 @clName int,
 @clName2 varchar(10)
as
 insert tableName values(@clName,@clName2)
go

------创建带输出参数的存储过程
create proc proc_DB
 @clName int output,    ----输出参数
 @clName2 varchar(10)
as
 select @clName=clName from tableName where clName2=@clName2
go