sql中as的用法和一些经典的sql语句http://blog.csdn.net/lange253/article/details/4278832

总结一些工作中用到或碰到的SQL语句&＃xff0c;希望能与大家分享&＃xff0c;同时也希望大家能提供更多的精妙SQL语句.....
1、delete table1 from (select * from table2) as t2 where table1.id&＃61;t2.id
2、truncate table table1 (不在事务日志中做记录&＃xff0c;比delete table快&＃xff0c;但不能激活触发器)
3、update table1 set column&＃61;column&＃43;1 where id&＃61;(select id from table2)
4、update table1 set column&＃61;column&＃43;1 from table1,table2 where table1.id&＃61;table2.id
5、select top n [Percent] * from table1 &＃39;输出百分比记录
6、select id,column1 * column2 as column from table1 &＃39;可算明白as的用法了
7、select * from table1 where column1 like &＃39;SQL#_G_O&＃39; escape &＃39;#&＃39; &＃39;单匹配
8、select table1.id from table1 where not exists (select table2.id from table2 where table1.id&＃61;table2.id) &＃39;这个应该比not in快一些
9、select table1.id from table1,table2 where table1.id<>table2.id &＃39;看复合查询机制
10、select table1.id from table1,table2,(select id from table3) as t3 where table1.id&＃61;table2.id and table2.id&＃61;t3.id &＃39;有些类似[1]了......
11、select * from table1 where column1 like &＃39;[A]%&＃39; or like &＃39;[^B]%&＃39;
12、select &＃64;column1&＃61;column1 from table1;select &＃64;column1 as column1 &＃39;存储到自定义变量
13、select * from table1 where contains(column1,&＃39;char1 or char2*&＃39;) &＃39;全文索引
14、select * from table1 where contains(column1,&＃39;前有 near 中有 near 后有&＃39;)
15、select * from table1 where contains(column1,&＃39;formsof(inflectional,go)&＃39;) &＃39;派生
16、select * from table1 where contains(description,&＃39;isabout(apple weight(.9),boy weight(.8),china weight(.7))&＃39;) &＃39;权重
17、select * from table1 where freetext(column1,&＃39;char&＃39;) &＃39;仅支持文字不支持表达式搜索
18、insert into table1 select column1,count(column1) from table2 group by column1 &＃39;统计

-----------------------------------------------------------------------------------------
1 说明&＃xff1a;复制表(只复制结构,源表名&＃xff1a;a 新表名&＃xff1a;b)
SQL: select * into b from a where 1<>1

2 说明&＃xff1a;拷贝表(拷贝数据,源表名&＃xff1a;a 目标表名&＃xff1a;b)
SQL: insert into b(a, b, c) select d,e,f from b;

3 说明&＃xff1a;显示文章、提交人和最后回复时间
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title&＃61;a.title) b

4 说明&＃xff1a;外连接查询(表名1&＃xff1a;a 表名2&＃xff1a;b)
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a &＃61; b.c

5 说明&＃xff1a;日程安排提前五分钟提醒
SQL:    select * from 日程安排 where datediff(&＃39;minute&＃39;,f开始时间,getdate())>5

6 说明&＃xff1a;两张关联表&＃xff0c;删除主表中已经在副表中没有的信息
SQL:  
delete from info where not exists ( select * from infobz where info.infid&＃61;infobz.infid )

7 说明&＃xff1a;
从数据库中去一年的各单位电话费统计(电话费定额和电话费清单两个表来源&＃xff09;
SQL: 
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, &＃39;yyyy&＃39;) AS telyear,
        SUM(decode(TO_CHAR(a.telfeedate, &＃39;mm&＃39;), &＃39;01&＃39;, a.factration)) AS JAN,
        SUM(decode(TO_CHAR(a.telfeedate, &＃39;mm&＃39;), &＃39;02&＃39;, a.factration)) AS FRI,
        SUM(decode(TO_CHAR(a.telfeedate, &＃39;mm&＃39;), &＃39;03&＃39;, a.factration)) AS MAR,
        SUM(decode(TO_CHAR(a.telfeedate, &＃39;mm&＃39;), &＃39;04&＃39;, a.factration)) AS APR,
        SUM(decode(TO_CHAR(a.telfeedate, &＃39;mm&＃39;), &＃39;05&＃39;, a.factration)) AS MAY,
        SUM(decode(TO_CHAR(a.telfeedate, &＃39;mm&＃39;), &＃39;06&＃39;, a.factration)) AS JUE,
        SUM(decode(TO_CHAR(a.telfeedate, &＃39;mm&＃39;), &＃39;07&＃39;, a.factration)) AS JUL,
        SUM(decode(TO_CHAR(a.telfeedate, &＃39;mm&＃39;), &＃39;08&＃39;, a.factration)) AS AGU,
        SUM(decode(TO_CHAR(a.telfeedate, &＃39;mm&＃39;), &＃39;09&＃39;, a.factration)) AS SEP,
        SUM(decode(TO_CHAR(a.telfeedate, &＃39;mm&＃39;), &＃39;10&＃39;, a.factration)) AS OCT,
        SUM(decode(TO_CHAR(a.telfeedate, &＃39;mm&＃39;), &＃39;11&＃39;, a.factration)) AS NOV,
        SUM(decode(TO_CHAR(a.telfeedate, &＃39;mm&＃39;), &＃39;12&＃39;, a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
          FROM TELFEESTAND a, TELFEE b
          WHERE a.tel &＃61; b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, &＃39;yyyy&＃39;)

8 说明&＃xff1a;四表联查问题&＃xff1a;
SQL: select * from a left inner join b on a.a&＃61;b.b right inner join c on a.a&＃61;c.c    inner join d on a.a&＃61;d.d where .....

9 说明&＃xff1a;得到表中最小的未使用的ID号
SQL:
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID &＃61; 1) THEN MIN(HandleID) &＃43; 1 ELSE 1 END) as HandleID
FROM    Handle
WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)

10 说明&＃xff1a;模糊查询&＃xff0c;单字匹配(短横线代表待匹配内容)

select * from table where field1 like &＃39;A_B_C&＃39;

11 说明&＃xff1a;as的用法

select id,column1 * column2 as column from table1