MySQL basic 02 (query)

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)