常用sql语句

use master

if exists(select * from master.dbo.sysdatabases where name='mytback')
drop database mytback
go
create database mytback
go
use mytback
go
if exists(select * from sysobjects where name='tt')
drop table tt

go
create table tt(id    int    primary key not null,
  name  varchar(20)  not null)
go

declare  @i int
set @i=1
while(@i<5)
begin
insert into tt values(1001+@i,'zhangsan'+convert(char,@i))
set @i=@i+1
end

drop table tt

go

if exists(select * from sysdatabases where name='skyBusiness')
begin
drop database skyBusiness
print 'skyBusiness已经存在，已被删除@'
end
else
begin
create database skyBusiness
on primary
(
 name=skyBusiness_mdf,
 filename='c:\a\skyBusiness.mdf',
 size=10mb,
 maxsize=50mb,
 filegrowth=25%

)
log on
(
 name=skyBusiness_ldf,
 filename='c:\a\skyBusiness.ldf',
 size=10mb,
 maxsize=50mb,
 filegrowth=25%
 
)
print 'skyBusiness数据库已经创建'
end

--建聚集索引
create clustered index index_yexinwinners
on tablename(column_name)
with fillfactor=10

--建非聚集索引
create nonclustered index index_yexinwinners
on tablename(column_name)
with fillfactor=10
--建视图
create view view_name
as
select * from pubs.titles
--建检查视图
create view vies_name
as
select * from pubs.titles
with check option
--建加密视图
create view view_name
with encryption
as
select * from pubs.titles

--建表
create table tablename
(
 stuid int not null identity(1,1) primary key,
 stuname varchar(30) not null,
 stuage char(3) not null
)
--添加check约束
alter table tablename
add check(stuage>0 and stuage<100)
--添加外键
alter table tablename
add foreign key(stuname)
references xtablename(stuname)
--添加唯一约束
alter table tablename
add unique(stuid)
--建事务
begin tran
update xtable set Money=Money+10000 where id=1
update xtable set Money=Money-10000 where id=3
if(@@error<>0)
begin
print '转账失败'
rollback tran
end
else
begin

print '转账成功！'
commit tran
end

--建游标
declare cursor_name cursor
for select * from northwind.product
---建更新游标
declare cursor_name cursor
for select * from northwind.product for update

--建随意移动游标
declare cursor_name cursor
for select * from northwind.product
--使用游标
open cursor_name
close cursor_name
--删除游标
deallocate cursor_name
--移动游标
fetch next--下一条记录
fetch last--最后一条记录
fetch first--第一条记录
fetch prior--上一条
fetch absolute n --绝对位置

 

转载于:https://www.cnblogs.com/chjun2000/archive/2010/01/09/1643172.html