常用SQL语句

 

 Create Table Article
( 
 Id Int Identity(1,1) Not Null,
 Title Varchar(50) Not Null Constraint uq_ArticleTitle Unique,
 Keywords Varchar(50) Not Null,
 Abstract Varchar(500) Not Null,
 Author Varchar(50) Not Null Default '张子阳',
 Type TinyInt Not Null Default 0 Constraint ck_ArticleType
 Check(Type in (0,1,2)), -- 0,原创；1,编译；2,翻译
 IsOnIndex Bit Not Null Default 1, -- 是否显示在首页
 Content Text Not Null,
 SourceCode Varchar(100) Null, -- 程序源码的下载路径
 Source Varchar(50) Not Null Default 'TraceFact', -- 文章出处
 SrcUrl Varchar(150) Null, -- 文章出处的 URL
 PostDate DateTime Not Null Default GetDate(),
 ViewCount Int Not Null Default 0,
 ClassId Int Not Null -- 外键包含的字段，文章类别
 Check(ClassId>0)
 Constraint pk_Article Primary Key(Id) -- 建立主键
) 

 

drop table tableName

 

alter table [dbo].[tableName] 
add columnName nvarchar(20) not null;

 

alter table tableName ADD DEFAULT 0 FOR columnName

 

 

alter table [dbo].SIMRawEdges4RubberFrame 
add ID uniqueidentifier not null default (NEWID());

 

alter table [dbo].[tableName] 
add constraint PK_tableName primary key(columnName);
-- alter table tableName add primary key (columnName);

alter table [dbo].[tableName] 
add constraint UK_tableName UNIQUE (columnName);
--alter table tableName add UNIQUE (columnName);

 

alter table [dbo].[tableName] 
alter column columnName nvarchar(20);

在修改字段类型的时候，还可以在后面指定是否为非空字段，null || not null ，当把一个字段由非空改成可空时，也可以用上面这条语句。

 

alter table tableName [WITH NOCHECK]
ADD CONSTRAINT <约束名> FOREIGN KEY (<字段>)
REFERENCES <引用的表名>(<字段>)
[ON <UPDATE | DELETE<NO ACTION | CASCADE | SET NULL| SET DEFAULT>];
--WITH NOCHECK:如果增加约束时不需要对现有的数据进行约束检查，可选用此项；
-- NO ACTION:当父表中的行被更新（删除）时，则产生错误，并撤销对数据的操作。
-- CASCADE: 当父表中的行被更新（删除）时，同时更新（删除）子表中相依赖的行
-- SET NULL:当父表中的行被更新（删除）时，将子表中相依赖行中相应字段的数据设为空。
-- SET DEFAULT：同上；
[]:可选 <>:必选

 

ALTER TABLE tableName [WITH NOCHECK]
ADD CONSTRAINT <约束名> CHECK (<check_condition>);
--alter table employee WITH NOCHECK 
  ADD CONSTRAINT check_age
  CHECK(age>18);

 

关闭：alter table tableName NOCHECK CONSTRAINT checkName;  
删除：alter table tableName DROP CONSTRAINT checkName;
--修改字段名：
execute sp_rename 'dbo.tableName.columnName_old','columnName_new'

 

alter table tableName
drop column columnName

 

CREATE INDEX<index_name> ON tableName (columnName[,cloumnName1]);

 

DROP INDEX <index_name> ON tableName

 

select name,age,sex=case when sex='F' then '女' when sex='M' then '男' end from PEOPLE_TEST

select name,age,sex=case sex when'F' then '女' when 'M' then '男' end from PEOPLE_TEST


--下面两句，可以看到，select 的字段最终是在end关键字后面定义的
case when (select CAST(B.Cdt as decimal)-CAST(B.Odt as decimal))>0 then left(B.Cdt, 4)+ '-' + SUBSTRING(B.Cdt, 5, 2)+ '-' + SUBSTRING(B.Cdt, 7, 2)+ ' ' + SUBSTRING(B.Cdt, 9, 2)+ ':' + SUBSTRING(B.Cdt, 11, 2)+ ':' + right(B.Cdt, 2) else GETDATE() end as cdt1,

case B.odt when '0' then '0' else left(B.Odt, 4)+ '-' + SUBSTRING(B.Odt, 5, 2)+ '-' + SUBSTRING(B.Odt, 7, 2)+ ' ' + SUBSTRING(B.Odt, 9, 2)+ ':' + SUBSTRING(B.Odt, 11, 2)+ ':' + right(B.Odt, 2) end as odt1, 

 

<create|alter> procedure schemaName.procedure_Name 
[<@参数> <数据类型> [=默认值] [out|output],]
[<@参数1> <数据类型> [=默认值] [out|output]]
AS
  [declare <@变量名> <数据类型>[=默认值];]
  [declare <@变量名1> <数据类型>[=默认值]]
[begin]
    sql_statement
[end]

--例：
create procedure myTest.MatOut
    @matNo varchar(50),
    @Qty numeric,
    @Msg varchar(100) output
as
  --定义变量：物料的库存量
  declare @stockQty numeric;
begin
  --给变量@Msg赋值
  set @Msg='领料出库的业务逻辑';
end;

 

EXEC procName 'para1','para2','para3'

 

--1、DML触发器：（AFTER 触发器不能在视图上使用的）
create trigger triggerName
on <tableName|viewName>
   <instead of|after|for><insert|update|delete>
as 
begin
     sql_statement
end;

--例：
create trigger T_addNum
on 学生信息
for insert
as 
update 班级信息 set 班级人数=班级人数+1
where 班级编号=（select  所在班级 from inserted）

--2、DDL触发器
create trigger triggerName
ON <ALL SERVER|DATABASE>
WITHLNCRYPTION
<FOR|AFTER><drop_table|alter_table|create_table>

--例：
create trigger T_notdelete
on database
 for drop _table,alter_table//drop 和修改(触发事件的类型)
as
print'事物不能被处理，基础数据表不能被修改和删除'
rollback 回调

--删除触发器
drop  trigger triggerName

--禁用触发器
disable trigger triggerName on tableName

--启用触发器
enable trigger triggerName on tableName

--重命名触发器
sp_rename 'triggerName_old','triggerName_new'

 

--创建视图(只能写select语句，并且一个视图只能写一个select语句)
CREATE VIEW viewName as <select statement>;

-- 更新视图（条件：1、视图必须未涉及连接。2、视图必须不包含Group by 子句。3、不能使用distinct子句。4、where 子句不能包含表的嵌套引用）
update viewName set age=age+1 where studentID='080808'

--修改视图
alter view viewName as select_statement

--删除视图
drop view viewName

创建视图时，select语句中要写明查询的字段名称，不能用*号。 

 

--创建临时表：（一个#说明是局部临时表，两个#说明是全局临时表）
create table #temp_stu(    
    name nvarchar(20),
    num nvarchar(20)    
)
--删除临时表：drop table #temp_stu

--创建临时表（2）：temp_stu1，查询people_test中的数据，并将数据插入到temp_stu1中去
select name,age into #temp_stu1 from  PEOPLE_TEST

 

select @@identity

select IDENT_CURRENT("tablename")

--前者没有作用域的问题
--后者仅限于某张表中的自增长ID