SQL SERVER 语句大全

            

l  database数据库的增删改查

l  table表的增删改查和约束

l  column列的增删改查 

l  data数据的增删改查  

l  view视图，case语句，if语句，while语句

l  事务，存储过程 

l 索引，触发器

连接查询

一.  database数据库的增删改查

--增

create database mytest2014b

on primary

(

name='mytest2014b',

size=5mb,

maxsize=20mb,

filename='D:\mytest2014b.mdf'

)

 

--删

drop database mytest2014b

--改

  use apsxDb  --切换数据库

   alter database  mytest2014b modify file (name=mytest2014b,size=10MB) --修改数据库大小，只能往大改，不能往小改

--查

  use apsxDb

SELECT * FROM SYS.DATABASE_FILES          --切换数据库并查询出当前数据库大小等信息

 

二. table表的增删改查和约束

--增

    create table MyFirst(

 Id    int identity(1,1) primary key,

 FirstName nvarchar(20) not null,

 FirstSmileDate datetime not null

)                                   --创建指定的表

   

    select top 0 * into thirdtable from secondtable     --根据已有表创建新表（会有相同的结构（列名，标识列），但约束，主键等不会有）

     select *  into tbscore2 from tblscore               --复制表  包括数据不包括主键等约束

--删

  drop table MyFirst

--改

    --修改表名（调用存储过程，参数为原表名，新表名）     

    exec sp_rename  'myfirst','secondtable'      

--查

  use book_shop

  select * from sys.tables    --查询当前数据库下所有表  

--作用于表的约束（主键，Check,唯一，默认，非空，外键）

    --删除约束（几种约束都一样）

    alter table 表名 drop constraint 约束名

   alter table secondtable drop constraint PK__myfirst__3214EC07D24F39CF

    --添加主键约束

    alter table secondtable add constraint PK_SecondTable_Id primary key(Id)  

   --添加Check约束(用于给列设定条件)   

   alter table secondtable add constraint CK_SecondTable_SecondName check(len(secondname)>2) 

   --添加唯一约束（不允许重复，要求设定列表中该列没有重复）

    alter table secondtable add constraint UQ_SecondTable_secondName Unique(secondName)

   --添加默认约束

    alter table secondtable add constraint Default_SecondTable_secondName default('宾') for secondname

      --添加非空约束（与唯一约束冲突）

   alter table secondtable alter column secondname nvarchar(20) not null

   --添加外键约束   外键表引用主键表的主键ID    对应关系： 主键表1：外键表*   

    alter table 外键表名 add constraint 约束名 foreign key(关系列）references 主键表名（主键列）

    语句后追加 on delete（update）cascade 设置级联关系，删除主键表主键时会关联对应的外键表 

    alter table aspx_news add constraint FK_Aspx_News_Aspx_Type_TypeId  foreign key(NewsTypeId) references Aspx_Type(TypeId)

三.列的增删改查  

   --添加列

   alter table myfirst add Id int identity(1,1) not null

    ---添加列并指定为主键（主键不能有重复）（SET IDENTITY_INSERT 表名 ON  开启后可对主键列进行修改，SET IDENTITY_INSERT 表名 OFF 关闭修改权）

    alter table myfirst add Id int identity(1,1) primary key         

  --删除列

   alter table myfirst drop column Id

   --修改列

   --修改列名（调用存储过程，参数为 表.列名 新列名 ‘column'(对象名)）   

   exec sp_rename 'myfirst.firstname','secondName','column'

   --修改列类型

    alter table secondtable alter column secondname  nvarchar(10) not null

   --查列（查看所有列名）

   select top 0 * from secondtable

四.数据的增删改查  

   --增  

    insert  into secondtable  (secondname,firstsmiledate) values('汪峰',dateadd(day,2,getdate()) )   --插入一条数据

      insert into tblclass (tclassname,tclassdesc) output inserted.tClassId values('高二三班','牛叉班')  --插入数据并获取刚刚插入的数据的id

    insert  into secondtable   values('白亚松',dateadd(day,1,getdate()) )     --插入一条数据（除主键外所有column）

      insert into thirdtable select secondName, FirstSmileDate from secondtable   --从一个表向另一个表导入数据（要求两个表有相同的列且对应类的数据类型可兼容（列名可以不同））

   --删

    delete from secondtable where id=6      --删除一条指定的数据

      delete from secondtable                  --删除表中所有的数据

      truncate table secondtable                --删除表中所有的数据 （比delete更快）

    --改

      update secondtable set secondName='比比', FirstSmileDate=dateadd(day,-10,getdate()) where id=13    --修改一列指定的数据

   --查

     select * from secondtable           --查询表中所有数据

      select top 5 * from secondtable     --查询表中前五条数据

      select * from mystudent where fid between 20 and 100    --查询20-100之间的数据（包含20,100）

      select * from mystudent where fid in(20,100)                 --查询20，100 两条数据

      select top 5 * from secondtable order by NEWID()     --随机获取五条数据

      select tclassdesc from (select * from tblclass1 where tclassname='高一四班')as t       --子查询

      select * from thirdtable where secondname like '%亚%'  --查询表中secondname列包含亚字的数据        --模糊查询，少用

      select * from thirdtable where secondname  not like '%亚%'  --查询表中secondname列不包含亚字的数据

      select * from thirdtable where secondname like '%比'   --查询表中secondname列以比字结尾的数据

      select * from thirdtable where secondname like '白%'   --查询表中secondname列以白字开头的数据

      select * from thirdtable order  by id desc（asc）     --按条件降序（升序查询，把order by 以后返回的数据集合叫“游标”。）

      select  distinct secondname as N'姓名', firstsmiledate as '第一笑点' from thirdtable     --查询表中不重复的数据 （DISTINCT是对查询出的整个结果集进行数据重复处理的，而不是针对某一个列。）

      select count(0) from thirdtable   --查询表中数据总数                     --聚合函数

      max(scores) min(scores) sum(scores) avg(scores)        

      select getdate()  --获取当期时间                                      --时间函数

      select datediff(year,'2012-12-11',getdate())  --获取规定单位的时间差

      select dateadd(year,-12,getdate())   --获取加减单位时间后的时间

      select datepart(year,getdate())   --获取时间的某一部分单位

      select * from thirdtable where secondname is not null              --空值判断

      select * from thirdtable where secondname is null

      select isnull(secondname,'匿名') from thirdtable                 --secondname值为null则用匿名代替，仅在检索时有效

      select cast('123'as int)                                           --类型转换

      select convert(int ,'22')     

    select secondname ,max(id) from thirdtable group by secondname     --分组（没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 （聚合函数中除外）

      select secondName from thirdtable group by secondname having count(secondname)>1      --分组后筛选

    GROUP BY子句必须放到WHERE语句的之后 ,Group By与Order By都是对筛选后的数据进行处理，而Where是用来筛选数据的。Having 是Group By的条件对分组后的数据进行筛选（与Where类似，都是筛选，只不过having是用来筛选分组后的组的。）

   select productid,sum(quantity) as 销售数量 from orders group by productid order by 销售数量 desc  --根据产品总销量降序排列

     select productid,sum(quantity*UnitPrice) as 销售总价 from orders group by productid having sum(quantity*UnitPrice)>40000 order by 销售总价 desc --将总销售价大于40000的根据产品销售总价排行     

    select tsname,tmath from v_tms union all select '总成绩',sum(tmath) from v_tms   --联合查询

五.视图,case语句，if语句，while语句

  创建视图 （视图保存虚拟表，不存储具体的数据，在没有top指定的情况下查询语句不能用orderby）     

   create view v_tms as

   select tsname,tmath from  tblstudent  as t1 inner join tblscore as t2 on t1.tSId=t2.tScoreId

   create view v_test as

   select top 50 percent tmath from tblscore order by tmath de --指定top后查询可用orderby 但是若top 100 percent 时保存检索的视图将不会排序

   create view v_test1 as

   select top 100 percent tmath from tblscore order by tmath desc

    select * from v_test1   =    select tmath from tblscore

   删除视图  

  drop view v_tms

  case语句

  select secondname,               --等值判断

编号=(

case id

when 17 then '十七' 

when 18 then '十八'

else '成年'

end

) from secondtable

   

   select secondname,              --区间（范围）判断

编号=(

case 

when id>18 then '成年' 

when id>35 then '中年'

else '小孩'

end

) from secondtable

select case                        --超出长度转换

 when len(tclassdesc)>1 then left(tclassdesc,1)+'...' 

end from tblclass2

   select teamname,              --综合case,聚合函数，分组   先分组，再确定列，再case,再使用聚合

胜=(

sum(case 

when gameresult= '胜' then 1

when gameresult= '负' then 0

end)

 ),

 负=(sum(case 

 when gameresult='负' then 1

  when gameresult='胜' then 0

 end)

 ) from TeamScore group by teamname

  if语句

   declare @num int 

 set @num=12

 if(@num>10)

 begin

 print('hello')

 end

while语句

 declare @num int 

 set @num=10

while(@num>1)

begin

set @num-=1

print(@num)

end

六.事务，存储过程

事务：

begin tran

declare @num int

 set @num=0

 update bank set balance=balance-2000 where cid='0001'

 set @num+=@@error

 update bank set balance=balance+2000 where cid='0002'

 set @num+=@@error

 if(@num<>0)

 begin

 rollback tran

 end

 else

 begin

 commit tran

 end

分页存储过程：

     create proc usp_Orders

 @pagesize int,  --页大小

 @pageindex int, --当前页数

 @recordcount int output, --总记录数

 @pagecount int output  --总页数

 as

 begin

   set @recordcount=(select count(*) from orders) 

   set @pagecount=ceiling(@recordcount*1.0/@pagesize)

   --分页查询

   select OrderID, CustomerID, EmployeeID, OrderDate, ShipVia, ProductID, UnitPrice, Quantity, Discount

   from(select  OrderID, CustomerID, EmployeeID, OrderDate, ShipVia, ProductID, UnitPrice, Quantity, Discount,

   rn=row_number() over (order by orderid) from Orders )  as t where t.rn between (@pagesize*(@pageindex-1))+1 and @pagesize*@pageindex

 end 

七.索引、触发器

  索引

   

   --建立聚集索引

 create clustered index IXid on myorders(id)

 --建立非聚集索引

 create nonclustered  index IX购买人 on myorders(购买人)

 --删除索引，表名.索引名

 drop index myorders.IXid

触发器

--after(for)触发器(执行之后触发)

create trigger tri_delete1 on tblclass

after delete  --(update,insert)

as

begin

 insert into tblclass1 select tclassname ,tClassDesc from deleted --(inserted)

end

--instead of 触发器（替换触发器）

go

create trigger tri_delete2 on tblclass

instead of delete  --(update,insert)

as

begin

 insert into tblclass1 select tclassname ,tClassDesc from deleted --(inserted)

end

--删除触发器

drop  trigger tri_delete2 

--使用触发器去除多余的重复数据

create trigger tri_getdistinct on tblclass2

after delete  --(update,insert)

as

begin

 insert into tblclass2 select distinct tclassname ,tClassDesc from deleted --(inserted)

end

delete from tblclass2