SQL语句记录点滴

1.查看特定表的列名：select name from syscolumns where id=object_id('特定表名')

2.对已有的表添加字段：alter table tablename add colname 字段类型

3.sql 查询出来的结果相加相减：

select a.snum1 + b.snum2 + c.snum3 - d.snum4 - e.snum5 from
(select sum(num1) snum1 from table1)a ,
(select sum(num2) snum2 from table2)b ,
(select sum(num3) snum3 from table3)c ,
(select sum(num4) snum4 from table4)d ,
(select sum(num5) snum5 from table5)e

4.根据指定规则排序：如

select * from [table] order by case [column] when 'xxx' then 1 when 'yyy' then 2 when 'zzz' then 3 end

5.对于

select [column],count(*) as c from [table] group by [column]

分组统计的c值为0不显示的解决办法，使用子查询：

select [column],c=(select count(*) from [table] where [table].[column]=t.[column]) from [table] as t group by t.[column]

  6.子查询中错误：除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

如：

 

select * from (
select * from tab where ID>20 order by userID desc
) as a order by date desc

 会报错，在嵌套子查询视图里面加入: top 100 percent 即可，改为：

 

select * from (
select top 100 percent * from tab where ID>20 order by userID desc
) as a order by date desc

  7.mysql判断内容是数字，用REGEXP：

 

select * from [table] where 1=(SELECT [column] REGEXP '^[0-9]*$');

 8.mysql 查询强制区分大小写：

 

select * from [table] where binary [column] like 'a%';

 也可以在建表时加上binary标识：

 

create table table_name(
     column_name varchar(20) binary
)

 

9. SQL Server 2005+，查看数据库对象的属性：

select * from sys.all_objects where name='[table]or[sp]or...'