Comparison of Oracle, mysql, sqlserver, and postgresql statements

Comparison of Oracle, mysql, sqlserver, and postgresql statements

1. Paging

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 condition limit (current page number * Page capacity-1), page capacity 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 is fixed, that is, the number of records displayed on each page. OffsetNum = (current page-1) * pagesize

2. query the first five records

Oracle:SELECT * FROM PersonsWHERE ROWNUM <= 5

mysql:SELECT * FROM Persons LIMIT 5

sqlServer:SELECT TOP 2 * FROM Persons
Postgresql:SELECT * FROM Persons LIMIT 5; same as mysql
3. Fuzzy search
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. Category Code | '-' | T. Invoice number) AS number from account where username ~ * 'Baidu ';
Find out the information of all users in the account in the data table that contain baidu and are case insensitive.
4. Judge 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. Insert data
Oracle:Insert into company (id, name, parentid, ccdm) values (COMPANY_SEQ.NEXTVAL ,?,?,?) Sequence
Mysql:Insert into person (fullName, companyName) values (?,?) Because ID is auto-increasing auto_increment
6. query the maximum id
Oracle：SELECT NVL(MAX(ID),0)+1 FROM REPOT_BS