今天给大家整理了一些工作中常用到的SQL语句，特别是AS的用法，希望能帮助大家提升SQL查询效率！
一、AS的用法详解
AS关键字在SQL中主要用于为表或列指定别名，让查询结果更清晰易懂：
-- 为列指定别名select id, column1 * column2 as 计算结果 from table1
-- 为表指定别名select a.id, b.name from table1 as a, table2 as b where a.id = b.id
-- 子查询中使用别名delete table1 from (select * from table2) as t2 where table1.id = t2.id
二、实用SQL语句精选
AS关键字在SQL中主要用于为表或列指定别名，让查询结果更清晰易懂：-- 为列指定别名select id, column1 * column2 as 计算结果 from table1
-- 为表指定别名select a.id, b.name from table1 as a, table2 as b where a.id = b.id
-- 子查询中使用别名delete table1 from (select * from table2) as t2 where table1.id = t2.id
二、实用SQL语句精选
1. 数据删除与清空
delete table1 from (select * from table2) as t2 where table1.id = t2.id
truncate table table1
2. 数据更新技巧
update table1 set column = column + 1 where id = (select id from table2)
update table1 set column = column + 1 from table1, table2 where table1.id = table2.id
3. 查询优化技巧
select table1.id from table1 where not exists ( select table2.id from table2 where table1.id = table2.id)
select table1.id from table1, table2, ( select id from table3) as t3 where table1.id = table2.id and table2.id = t3.id
4. 模糊查询高级用法
select * from table1 where column1 like 'A_B_C'
select * from table1 where column1 like 'SQL#_G_O' escape '#'
select * from table1 where column1 like '[A]%' or like '[^B]%'
5. 全文索引搜索
select * from table1 where contains(column1, 'char1 or char2*')
select * from table1 where contains(column1, '前有 near 中有 near 后有')
select * from table1 where contains(description, 'isabout(apple weight(.9), boy weight(.8), china weight(.7))')
6. 常用统计查询
insert into table1 select column1, count(column1) from table2 group by column1
select top 10 * from table1 order by id descselect top 10 percent * from table1
三、经典实用案例
1. 复制表结构
-- 只复制结构，不复制数据select * into b from a where 1<>1
-- 复制数据到已存在的表insert into b(a, b, c) select d, e, f from a
2. 外连接查询
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUTER JOIN b ON a.a = b.c
3. 日程提醒查询
select * from 日程安排 where datediff(minute, f开始时间, getdate()) > 5
4. 删除冗余数据
delete from info where not exists ( select * from infobz where info.infid = infobz.infid)
5. 获取最小未使用ID
SELECT CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END as HandleIDFROM HandleWHERE NOT HandleID IN ( SELECT a.HandleID - 1 FROM Handle a)
四、年度统计汇总（经典案例）
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DECFROM ( SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration FROM TELFEESTAND a, TELFEE b WHERE a.tel = b.telfax) aGROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')
五、性能优化小贴士
NOT EXISTS比NOT IN快：特别是在子查询结果集较大时
使用TRUNCATE代替DELETE：清空大表时速度快得多
合理使用索引：经常查询的字段要建立索引
**避免SELECT ***：只查询需要的字段
温馨提示：以上SQL语句在不同数据库（MySQL、SQL Server、Oracle）中可能略有差异，请根据实际情况调整。
阅读原文：原文链接
该文章在 2026/4/15 18:08:54 编辑过
400 186 1886
