sqlserver操作整理

1.检索数据

单列：select a from table where...

多列：select a,b from table where...

所有列:select * from table where...

不同值:select distinct a from table where...

限制结果:select top 5 a from table where...

按名称排序检索:select a,b from table where... order by a,b

按位置排序检索:select a,b from table where... order by 2,1

降序检索：select a,b from table where... order by a desc,b (如果多列降序，必须每列都跟关键字)

操作符：=(等于)，<>（不等于），!=(不等于)，<=(小于等于),！（不小于）相当于大于等于。

范围检索:select a from table where a between num1 and num2

空值检查:select a from table where a is null

where高级过滤操作符:and(逻辑与)，or(逻辑或)，in(逐一匹配)，not(否定跟在其后的条件)

%通配符：select a from table where a like 'fish%'      %代表任意字符出现任意次数

-通配符:匹配一个字符

[]通配符:符合字符集中的单一字符

拼接:select a + ' (' + b + ') ' from table ...

        select a || ' (' || b || ') ' from table ...

       select a + ' (' + RTRIM(b) + ') ' from table...

计算算术拼接:select a,b,a*b from table...

数据处理函数:LEFT(),LENGTH(),LOWER(),LTRIM(),RTRIM(),RIGHT(),SOUNDEX(),UPPER(),DATEPART()

聚合函数:COUNT(),AVG(),MIN(),MAX(),SUM().

分组数据:select count(*) as count,a from table where... group by a

过滤分组:select count(*) as count,a from table where... group by a having count(*)>2

2.插入数据

insert into table values(a,b,c,d) 插入完整行

insert into table(a,b,c,d) values(a,b,c,d) 完整写法

insert into table(a,b,c) values(a,b,c) 插入部分行

insert into table(a,b,c,d) select a,b,c,d from table1 插入检索数据

select * from table into table1 创建table1,而insert select是table已经存在

3.更新和删除

update table set a=... where...

delete from table where a=...

4.表相关

create table a (a1 int 1,a2 varchar(20) null,a3 int not null...) 创建表

alter table a add a1 varchar(20) 添加列

alter table a drop column a1 删除列