常用SQL笔记，经常使用，记得收藏

sql建表
create table name(
clom1 int primary key,--主键
clom2 varchar(20) unique,唯一索引关键字unique
clom3 varchar(20) default '否',--设置默认值
clom4 int check(clom4>100 and clom4<500)

)

创建局部临时表,不能和其他会话共享，会话结束，行和表的定义将被删除
create table #tab(id int)
全局临时表创建，对所有用户都可见，断开服务连接时自动删除！
create table ##tab（id int ）

创建主外键关联表

use 库名
go
create table tb1(
职工号 int primary key,--主键
职工号 varchar(20) unique,
工资 int
)

go 
create table tb2(
订单号 int primary key,
订单号 varchar(20) unique,
职工号 varchar(20) references tb1(职工号),--外键
销售额 int
)

创建含有约束和自动计算的表，
create table tb3(
职工号 int primary key,
职工号 varchar（20）unique，
基本工资 int check(基本工资>1000 and 基本工资<3000 ),--约束这个栏位只能是这两个值之间的数 
加班工资 int，
奖金 int，扣除 int
应发工资 as (基本工资+加班工资+奖金-扣除)--自动计算，不用输入值
)

自动编号自增
create table tb4(
仓库号 int identity(1,1) primary key,
仓库号 varchar(10) unique,
城市 varchar(20) default'深圳'
)

新增表字段栏位
alter table tb4 add 类别 varchar(20)
修改表字段
alter table tb4 column 类别 varchar（200）
删除表字段
alter table tb4 drop column 类别

删除主外键，先找到主外键约束名称
select name
from sys.foreign_key_columns f join sys.objects o on f.constraint_object_id=o.object_id
where f.parent_object_id=object_id('表名')
删除 alter table 表名 drop constraint FK__test2__id --约束名称

sqlserver 判断临时表是否存在
if object_id('tempdb..#tabname')is not null
begin 
drop table #tabname
end;

开始事物 begin transaction
提交事物 commit transaction
回滚事物 rollback transcation

error_number()返回错误号
error_state()返回错误状态号
error_procedure()返回出错的存储名称
error_line()返回错误行号
error_message（）返回完整的错误信息

CREATE TABLE LogTableazf
( 
  ID              int identity(1,1),--错误序号
  ErrorNumber     int,--错误号
  ErrorSeverity   int,--严重性
  ErrorState      int,--错误状态号
  ErrorProducure  varchar(200),--出现错误的存储过程或 触发器的名称
  ErrorLine       int,--导致错误的例程中的行号
  ErrorMessage    varchar(200)--错误消息的完整文本
)

--===============除数不为0的异常捕获=================--
IF EXISTS (SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'getWrongazf') AND xtype='P')
DROP PROC getWrongazf
go
CREATE PROC getWrongazf
AS 
BEGIN
     -----------------制造异常
     BEGIN TRY
     SELECT 1/0;
     -----------------捕获异常
     END TRY
     BEGIN CATCH
        INSERT INTO LogTableazf values(ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(), ERROR_LINE() ,ERROR_MESSAGE())
     END CATCH
END

--执行存储过程
EXEC getWrongazf
--查看日志表
select * from LogTableazf
--查看系统日志表
SELECT * FROM sys.messages WHERE message_id=8134 AND language_id=2052

sqlserver事务与回滚

复制代码
 set  XACT_ABORT  ON     -- -如果不设置该项为ON，在sql中默认为OFF，那么只只回滚产生错误的 Transact-sql 语句；设为ON，回滚整个事务 
 
 begin   tran  t1  -- -启动一个事务 
 
 update   [ water ] . [ dbo ] . [ ErrorInf ] 
 set  ErrorMessage = ' test ' 
 where  ID = 6 

 insert   into   [ water ] . [ dbo ] . [ ErrorInf ] ( [ ID ] ,ErrorMessage, [ Description ] )
 Values ( 1 , ' test1 ' , ' test1 ' )

 commit   tran  t1   -- -提交事务 

复制代码
 
功能：实现begin tran 和commit tran之间的语句，任一如果出现错误，所有都不执

表值函数
create function funname(@aa varchar(10))
return #tbname table(tid int)--定义返回的表结构
as
begin  
sql----
return;
end;

标量值函数
create function funname(@var varchar(10))
returns varchar(100) as
 begin

 return 'aaaaa'
end

± 按Alt键输0177再放开alt键，或直接拼音zhengfu

申明变量
declare @var varchar(20)
给变量赋值
set @var='aaa'

连接查询
内联查询 inner join on 取两表的共有数据
左连接 left join on 查询左表（主表）全部数据，右表（从表）没匹配的数据用null 填充
右连接 right join 查询从表（右表）所有数据，主表（左表）没有匹配数据用null填充
全连接 full join on 查询主从表所有数据，没有数据用null填充
eg: select * from a left join b on a.id=b.id

 对两个结果集进行“union”,"intersecrt","except"运算这两个结果集的列数必须相同.
intersecrt用于取得两个结果集的交集, except用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据。也就是获取第一个表不匹配第二个表的数据
select ename,sal,job from emp where sal>2500 
intersect--except 
select ename,sal,job from emp where job='manager';

用 exists 代替 in 是一个好的选择： 
select num from a where num in(select num from b) 
用下面的语句替换： 
select num from a where exists(select 1 from b where num=a.num)

数据类型转换
cast('值'as 转换类型)  ，convert(类型，值，样式可有可无)//样式用于时间格式转换 CONVERT(varchar(100), GETDATE(),120)

游标
declare 游标名称 cursor for 查询语句
 open 游标名称
fetch next from 游标名称 into 变量名1...
while @@fetch_status=0
begin
sql逻辑

fetch next from 游标名称 into 变量名1...
end 
close 游标名
deallocate  游标名称

@@ROWCOUNT=0 表示返回影响的行数
@@error=0表示执行成功，<>0表示执行失败