T-SQL 语法

----创建数据库----
use master
go
if exists (select * from sysdatabases where name='库名')
drop database 库名--删除数据库
create database 库名
on primary
(
name='库名_data',
filename='路径:\库名_data.mdf',
size=5mb,
maxsize=100mb,
filegrowth=15%
)
log on
(
name='库名_log',
filename='路径:\库名_log.ldf',
size=2mb,
filegrowth=10%
)
go

----创建表----
use 库名
go
if exists (select * from sysobjects where name='表名')
drop table 表名--删除表
create table 表名
(
列名 数据类型(大小) <是否为空>,
stuNo int identity (1,1), --座号,自动标识列
--数据类型
int,numeric小数,text大量字符串,image图像,money钱,datetime日期

----创建约束----
alter table stuInfo add
constraint pk_stuNo primary key (stuNo)---主键
constraint uq_stuID unique (stuID)---唯一
constraint df_stuAddress default ('不详') for (stuAddress)---默认
constraint ck_stuAge check(stuAge between 1 and 100)---检查约束
constraint fk_stuNo foreign key(外键) references 主表(主键)---主外关系
alter table 表 drop constraint 约束名---删除约束

----高级查询<子查询>----
--查询3张表的信息
--用联接
SELECT a.*,b.*,c.*
FROM a表 as a JOIN b表 as b ON a.Id=b.Id
JOIN c表 as c ON c.id=b.Id
WHERE 条件
--子查询--常用
SELECT b.1 b.2 c.1 a.1 a.2
FROM a表 a , b表 b , c表 c
WHERE 条件 and a.Id=b.Id and a.Id=c.Id <group by>

--创建登录账户
exec sp_grantlogin 'windows 账户'---Windows登录账户
exec sp_addlogin '账户','密码'---SQL登录账户
--创建数据库用户
exec sp_grantdbaccess '登录账户','数据库用户'
--用户授权
grant 权限<select,insert,update,delete> on 表 to 数据库用户

----变量----
declare @变量名 数据类型 ---声明
select/set @变量名=值 ---赋值
--全局变量
@@error ---最后一个T-SQL错误的错误号
@@identity ---最后一次插入的标识值
@@servicename ---SQL服务的名称
@@version ---SQL的版本信息
convert(varchar(5),@@error) ---装成文本格式

----逻辑控制语句----
--if-else条件语句
if(条件)
begin
end
else
--while循环
while(条件)
循环体
[break]
--case多分支语句
case
when 条件 then 结果
[else 结果]
end
select 列 from 表 where 列 is null ---查询空行
select top 2 列 from 表 where desc ---排序
select count(*) as 别名 ---查询次数

----事物----
begin transaction ---开始事物
commit transaction ---提交事物
rollback transaction ---撤销事物
--事物示例
begin transaction ---开始事物
declare @errorSum int
set @errorSum=0
update bank set Money=Money-1000 where Name='张三'
set @errorSum=@errorSum+@@error --累计是否有错误
update bank set Money=Money+1000 where Name='李四'
set @errorSum=@errorSum+@@error --累计是否有错误

if @errorSum<>0 ---有错误
begin
print '交易失败'
rollback transaction ---撤销事物
end
else
begin
print '交易成功'
commit transaction ---提交事物
end
go

----索引----
--创建(非聚集)索引
use 库
go
if exists (select name from sysindexes where name='IX_表_列')
drop index 表.IX_表_列 ---删除索引
create nonclustered index IX_表_列 on 表(列)
go
--创建视图
if exists (select * from sysobjects where name='view_表_列')
drop view view_表_列
go
create view view_表_列
as
select 别名=列名
from 表 <left join stuMarks on stuInfo.stuNo=suMarks.stuNo>--左联接
go
----存储过程----
--系统存储过程
--例 exec sp_databases ---列出当前系统中的数据库
sp_databases---列出服务器上所有数据库
sp_helpdb---报告有关指定数据库或所有数据库的信息
sp_renamedb---更改数据库的名称
sp_tables---返回当前环境下可查询的对象的列表
sp_columns---返回某个表列的信息
sp_help---查看某个表的所有信息
sp_helpconstraint---查看某个表的约束
sp_helpindex---查看某个表的索引
sp_stored_procedures---列出当前环境中的所有存储过程
sp_password---添加活修改登录账户的密码
sp_helptext---现实默认值,存储过程,触发器或视图的实际文本
exec xp_cmdshell dos命令 <[no_output]---是否返回结果>
--创建不带参数的存储过程
use 库
go
if exists (select * from sysobjects where name='proc_名')
drop procedure proc_名
go
create procedure proc_名 as
declare @变量声明
select @变量赋值 from 表
go
exec proc_名---调用执行存储过程
--创建不带参数的存储过程
......
create procedure 名
@参数[=默认值] [output]
--同无参
exec proc_名 a,b---调用
--处理错误
raiserror ('发生错误')

---事物示例
use stuDB
go
--恢复原来的数据
--update bank set curretMoney=currentMoney-1000 where customerName='李四'
set nocount on--不显示受影响的行数影响
print '查看转账事物前的余额'
select * from bank
go
--开始事物
begin transaction
--定义变量，用于累计事物执行过程中的错误
declare @errorSum int
set @errorSum=0
--转账
update bank set currentMoney=currentMoney-1000 where customerName='张三'
set @errorSum=@errorSum+@@error--累计是否有错误
update bank set currentMoney=currentMoney+1000 where customerName='李四'
set @errorSum=@errorSum+@@error--累计是否有错误
print '查看转账事物过程中的余额'
select * from bank
----根据是否有错误，确认事物是提交还是撤销
if @errorSum<>0
begin
print '交易失败，回滚事物'
rollback transaction
end
else
begin
print '交易成功，提交事物'
commit transaction
end
go
print '查看转账事物后的余额'
select * from bank
go
----索引示例
use stuDB
go
--检测索引是否存在
if exists (select name from sysindexes where name ='IX_stuMarks_writtenExam')
drop index stuMarks.IX_stuMarks_writtenExam--删除已存在
--笔试列创建非聚集索引，填充因子30%
create nonclustered index IX_stuMarks_writtenExam
on stuMarks(writtenExam)
with fillfactor=30
go
--按索引查询
select * from stuMarks (索引名) where writtenExam between 60 and 90
----创建视图示例
use stuDB
go
if exists (select * from sysobjects where name='view_stuInfo_stuMarks')
drop view view_stuInfo_stuMarks
go
--创建视图
create view view_stuInfo_stuMarks
as
select 姓名=stuName,学号=stuInfo.stuNo,笔试成绩=writtenExam,机试成绩= labExam,平均分=(writtenExam+labExam)/2
from stuInfo left join stuMarks on stuInfo.stuNo=stuMarks.stuNo
go
--使用视图查询
select * from view_stuInfo_stuMarks
----创建不带参数的存储过程
use stuDB
GO
if exists (select * from sysobjects where name='pric_stu')
drop procedure proc_stu
go
create procedure proc_stu
as
declare @writtenAvg float,@labAvg float --笔试，机试平均分变量
select @writtenAvg=avg(writtenExam),@labAvg(labExam)
from stuMarks
print '笔试平均分:'+convert(varchar(5),@writtenAvg)
print '机试平均分:'+convert(varchar(5),@labAvg)
if(@writtenAvg>70 and @labAvg>70)
print '优秀'
else
print '较差'
print '没有通过的学员'
select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo
inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo
where writtenExam<60 or labExam<60
go
--调用
exec proc_stu
----带输入参数的存储过程
use stuDB
go
if exists (select * from sysobjects where name='pric_stu')
drop procedure proc_stu
go
create procedure proc_stu
@writtenPass int, (=60)--默认值
@labPass int (=60)--默认值
as
print '没有通过的学员'
select stuName,stuInfo.stuNo,writenExam,labExam from stuInfo
inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo
where writtenExam<@writtenPass or labExam<@labPass
go
exec proc_stu 60,55
go
----创建带输出参数的proc
use stuDB
go
if exists (select * from sysobjects where name='pric_stu')
drop procedure proc_stu
go
create procedure proc_stu
@notpassSum int output,
@writtenPass int =60,
@labPass int =60
as
print '没有通过的学员'
select stuName,stuInfo.stuNo,writenExam,labExam from stuInfo
inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo
where writtenExam<@writtenPass or labExam<@labPass
--统计并返回没有通过的人数
select @notpassSum=count(stuNo) from stuMarks
where writtenExam<@writtenPass or labExam<@labPass
go
declare @sum int
exec proc_stu @sum output,60
if @sum>=3
print '未通过人数：'+convert(varchar(5),@sum),'及格线下调'
else
print '未通过人数:'+convert(varchar(5),@sum),'及格线合适'
go

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/coolpig86/archive/2010/03/08/5358250.aspx