SQL语句记录点滴

vike681

　　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...'