常用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 --绝对位置