sql 常用语句总结

1、查询表中所有的数据

select * from 表名
select all  id,name from 表名
select id,name  from 表名

select *  from 表A，表B where 表A.id=表B.id
select  *  from 表A inner join  表B on 表A.id=表B.id 

select  distinct *  from 表A
select  distinct(字段) from 表A

select count(*) from 表A
select count(1) from 表A
select count(字段) from 表A
select count(distinct 字段) from 表A

select top n *  from 表A
select top (n) * from c order by id desc

select * from 表A where not exists (select * from  表B where id = 表A.id )
select * from 表A where  exists (select * from  表B where id = 表A.id )
select * from 表A where id in  (select id from  表B)
select * from 表A where id  not  in  (select id from  表B)

select * from 表A where 字段A='' and 字段B=''
select * from 表A where 字段A='' or 字段B=''

select id, name from 表A where 字段 like '%' union select id, 字段 from 表A where id = 4 --并集不重复
select * from 表A where name like '%' union all select * from 表A--并集重复
select * from 表A where 字段 like '%' intersect select * from 表A--交集（相同部分） 
select * from 表A where 字段 like '%' except select * from 表A where 字段 like '%' --除去（相同部分）

9、聚合函数

select max(字段) 表A  
select min(字段) 表A  
select count(字段) 表A 
select avg(字段) 表A 
select sum(字段) 表A 
select var(字段) 表A 

select dateAdd(day, 3, getDate())
select dateAdd(year, 3, getDate())
select dateAdd(hour, 3, getDate())

select dateDiff(day, '2016-09-07', getDate())
select dateDiff(second, '2016-09-07 00:00:00', getDate())
select dateDiff(hour, '2016-09-07 00:00:00', getDate())
select dateName(month, getDate())
select dateName(minute, getDate())
select dateName(weekday, getDate())
select datePart(month, getDate())
select datePart(weekday, getDate())
select datePart(second, getDate())
select day(getDate())
select day('2016-09-07')
select month(getDate())
select month('2016-09-07')
select year(getDate())
select year('2016-09-07')
select getDate()
select getUTCDate()

11、数学函数

select pi()
select rand(100), rand(50), rand(), rand()
select round(rand(), 3), round(rand(100), 5)
select round(123.456, 2), round(254.124, -2)
select round(123.4567, 1, 2)

select len(字符串)
select reverse(字符串)
select left(字符串, 4)
select right(字符串, 4)
select lower('abc'), lower('ABC')
select upper('ABc'), upper('abc')

create database 库名称

drop  database  数据库名称

use master exes sp_addumpdevice '数据库' ,'备份名称','备份地址'

backup  database pubs to '备份名称'

create  table 表名称

drop  table 表名称

alter table 表名称 add column col 列名称

create  view  视图名称

drop  view 视图名称

select  *  into 表B from 表A
select top 0 into 表B from 表A

select *  from 表A  where time between  time1 and time2
select  *  from  表A where id  between  id1  and id2