Oracle,mysql,sqlserver,postgresql語句幾點比較

Oracle,mysql,sqlserver,postgresql語句幾點比較

1.分頁

Oracle： SELECT * FROM(SELECT A.*, ROWNUM RN FROM (select T.* from sj_receiptinfo t WHERE t.TAXNO LIKE CONCAT('%', CONCAT(?, '%')) order by t.id desc) A WHERE ROWNUM <= ?)WHERE RN > ?

mysql： select * from tableName where 條件 limit （當前頁碼*頁面容量-1） , 頁面容量pagesize

sqlServer： SELECT w2.n, w1.* FROM ARTICLE w1,( 　　SELECT TOP 1030 row_number() OVER (ORDER BY YEAR DESC, ID DESC) n, ID FROM ARTICLE) w2 WHERE w1.ID = w2.ID AND w2.n > 1000 ORDER BY w2.n ASC

postgresql：select * from xxx limit pagesize offset offsetNum ;

pagesize是固定的，即每頁顯示多少記錄。offsetNum＝（當前頁數－1）*pagesize

2.查詢頭5條記錄

Oracle： SELECT * FROM PersonsWHERE ROWNUM <= 5

mysql:		SELECT * FROM Persons LIMIT 5

sqlServer:	SELECT TOP 2 * FROM Persons
postgresql：SELECT * FROM Persons LIMIT 5；跟mysql一樣


3.模糊查詢
Oracle：select T.* from sj_receiptinfo t WHERE t.TAXNO LIKE CONCAT('%', CONCAT(?, '%')) 
mysql：select T.* from sj_receiptinfo t WHERE t.TAXNO LIKE CONCAT('%', ？, '%') 

sqlServer：
select T.* from sj_receiptinfo t WHERE t.TAXNO LIKE ‘%%’
postgresql：select *，( T .類別代碼 || '-' || T .發票號碼 ) AS 號碼 from account where username ~* 'baidu';
找出數據表account中所有用戶名包含baidu且不區分大小寫的用戶的信息
4.判斷null
Oracle：SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))FROM Products
mysql：SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))FROM Products
sqlServer：SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))FROM Products
postgresql：SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))FROM Products
5.插入數據
Oracle：insert into company(id,name,parentid,ccdm) values(COMPANY_SEQ.NEXTVAL,?,?,?)序列
mysql：insert into person(fullName,companyName) values(?,?) 因為ID是自動增長auto_increment
6.查詢最大id
Oracle：SELECT NVL(MAX(ID),0)+1 FROM REPOT_BS