1. ORACLE
SELECT * FROM TABLE1 WHERE ROWNUM<=N
2. INFORMIX
SELECT FIRST N * FROM TABLE1
3. DB2
SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<=N
或者
SELECT COLUMN FROM TABLE FETCH FIRST N ROWS ONLY
4. SQL SERVER
SELECT TOP N * FROM TABLE1
5. SYBASE
SET ROWCOUNT N GOSELECT * FROM TABLE1
6. MYSQL
SELECT * FROM TABLE1 LIMIT N
7. FOXPRO
SELECT * TOP N FROM TABLE ORDER BY COLUMN
having 和 where 的區(qū)別：
- where 子句中不能有聚組函數(shù)(sum,count,avg,max)
- 一般group by 之前用where 然想， 之后用having莺奔。
插入特殊符號，如單引號变泄，單引號會 斷掉sql 語句
sql = insert into yourTable(f1,f2) values(100,'abc')
這時(shí)一般用兩個(gè)單引號
replace(sql,"'","''")
& 是 chr(38)
union and union all usage:
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
it will show the two tables ** column_name values ** (column_name should be the same) without
duplicate.
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
union all will show the duplicate data
http://coolshell.cn/articles/1846.html
2017.2.21 --- left join 優(yōu)化思考
processing order of the select statement
(Select statement 執(zhí)行的順序)：
1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP BY
6.WITH CUBE or WITH ROLLUP
7.HAVING
8.SELECT
9.DISTINCT
10.ORDER BY
11.TOP
on 在 where 之后執(zhí)行令哟，說明join 的時(shí)候在 on 過濾比在where過濾效率高
select * from a left join b on a.name = b.name and a.group = 1
select * from a left join b on a.name = b.name where a.group = 1
