Comparison of Oracle, mysql, sqlserver, and postgresql statements, postgresql and oracle
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 ConditionLimit (Current page number * Page capacity-1),Page size 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