Some comparisons of oracle,mysql,sqlserver,postgresql statements

1. Paging

Oracle: SELECT * FROM (select A.*, ROWNUM RN from (select t.* to Sj_receiptinfo T WHERE t.taxno like CONCAT ('% ', CONCAT (?, '% ')) ORDER BY t.id Desc) A WHERE ROWNUM <=?) WHERE RN >?

MySQL: SELECT * from TableName where conditions Limit( Current page number*page Capacity-1) , page Capacity pagesize

SQL Server : Select W2.N, w1.* from article W1, (select TOP 1030 row_number () Up (ORDER by year DESC, id DESC) n, id from article) W2 WHERE w1.id = w2.id and W2.N > ORDER by W2.N ASC

PostgreSQL: select * from xxx limit pagesize offset offsetnum;

The pagesize is fixed, that is, how many records are displayed per page. offsetnum= (Current page-1) *pagesize

2. Query Header 5 Records

oracle: select * &NBSP; from Persons where ROWNUM <= 5

MySQL:LIMIT 5

SQL Server  :  TOP 2 * from Persons
PostgreSQL:  SELECT * from Persons LIMIT 5; like MySQL.
3. Fuzzy query
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 ('% ',? , '% ')

Sql server:
Select t.* from Sj_receiptinfo T WHERE t.taxno like '% '
PostgreSQL:  SELECT *, (T. Category Code | | '-' | | T. Invoice number) as number from the account where username ~* ' Baidu ';
Find information about all user names in the data table account that contain Baidu and are case-insensitive
4. Determine null
Oracle: SELECT productname,unitprice* (unitsinstock+NVL(unitsonorder,0))  from Products
MySQL: SELECT productname,unitprice* (unitsinstock+ifnull(unitsonorder,0))  from Products
SQL Server: SELECT productname,unitprice* (unitsinstock+ISNULL(unitsonorder,0))  from Products
PostgreSQL: SELECT productname,unitprice* (unitsinstock+coalesce(unitsonorder,0))  from Products
5. Inserting data
Oracle: INSERT INTO Company (ID,NAME,PARENTID,CCDM) VALUES (company_seq. Nextval,?,?,?) Sequence
MySQL:insert into person (fullname,companyname) VALUES (?,?) because the ID is autogrow auto_increment
6. Query the maximum ID
Oracle:SELECTNVL (MAX(ID), 0) +1 fromRepot_bs

Some comparisons of oracle,mysql,sqlserver,postgresql statements