Query is a big piece, so here I will only write the characteristics of MySQL, as far as I am currently using the situation, MySQL is more support for standard SQL, if it is novice, it is recommended to go to W3school to learn standard SQL.
1.DUAL
Dual is a virtual table that does not exist and makes the statement appear to conform to the SQL specification when used for direct select scalars
--MSSQLSelect 1,'A'--dual is indispensable in OracleSelect 1,'A' fromDUAL--MySQL is supported in 2 different formats:Select 1,'A';Select 1,'A' fromDUAL;
2.LIMIT
Compared to MSSQL, MySQL does not have a top keyword, but it has a limit and is more efficient and flexible.
SELECT * fromUSR LIMIT1;--LIMIT 1 means taking 1 records, and MySQL will no longer operate after 1 records have been taken.SELECT * fromUSR LIMIT0,2;--LIMIT 0,2 means starting with the first line (including the first row) and fetching 2 recordsSELECT * fromUsrWHEREu_idinch(SELECTu_id fromUsrWHEREDept='Information Department'LIMIT1);--when the statement executes, MySQL will error, meaning that you cannot use the limit in a subquery--solution, you would use the Limit statement to set a table, as follows:SELECT * fromUsrWHEREu_idinch(SELECT * from(SELECTu_id fromUsrWHEREDept='Information Department'LIMIT1AA) using top in Ps:mssql, the database is sorted before returning data, so limit is more efficient
3. Update errors for subqueries
UPDATEUsrSETUsr_name='Anonymous' WHEREu_idinch(SELECTu_id fromUsrWHEREDept='Information Department')--statement error, probably means that the modified table cannot use itself--solution, as above, and then set a layer of table canUPDATEUsrSETUsr_name='Anonymous' WHEREu_idinch(SELECT * from(SELECTu_id fromUsrWHEREDept='Information Department') AA)
MySQL basic 02 (query)