MySQL Database (0) Exercise analysis

1. The ID of the second high wage in the query table, if not, returns NULL. the key to this problem is that if you encounter the empty set, you must return NULL.

(1) Use sub-query.

Select (SelectDISTINCTfromORDERbyDESC11  as Secondhighestsalary;

Select (subquery) as result, if the subquery is empty set, then the result is a null below result.

　　Note: SELECT (subquery) as result and select * FROM (subquery) the difference between as result, the former has only one subquery, and the latter is queried in the result of a subquery in parentheses, there are two queries.

Mysql> Select *  fromemployee;+------+------+--------+-----------+|Id|Name|Salary|ManagerID|+------+------+--------+-----------+|    1 | NULL |     - |      NULL |+------+------+--------+-----------+1Rowinch Set(0.00sec) MySQL> Select *  from(Select *  fromEmployeewhereId= 1) asCA;+------+------+--------+-----------+|Id|Name|Salary|ManagerID|+------+------+--------+-----------+|    1 | NULL |     - |      NULL |+------+------+--------+-----------+1Rowinch Set(0.00sec) MySQL> Select *  from(Select *  fromEmployeewhereId= 2) asCA; EmptySet(0.00sec) MySQL> Select(Select *  fromEmployeewhereId= 1) asCA; ERROR1241(21000): Operand should contain1 column(s) MySQL> Select(SelectSalary fromEmployeewhereId= 1) asCA;+------+|Ca|+------+|   - |+------+1Rowinch Set(0.00sec) MySQL> Select(SelectSalary fromEmployeewhereId= 2) asCA;+------+|Ca|+------+| NULL |+------+1Rowinch Set(0.00sec) MySQL> Select(SelectSalary,name fromEmployeewhereId= 2) asCa,ba; ERROR1241(21000): Operand should contain1 column(s) MySQL> Select(SelectSalary fromEmployeewhereId= 2);+--------------------------------------------+|(SelectSalary fromEmployeewhereId= 2)|+--------------------------------------------+|                                       NULL |+--------------------------------------------+1Rowinch Set(0.00Sec

View Code

(2) Use the Ifnull statement.

Ifnull (value1,value2) returns value1 if value1 is not empty, otherwise value2

Select Ifnull ((selectDISTINCTfromORDERbyDESC11  null as secondhighestsalary;

Understand the role of select:

MySQL>Select (null as shit; + -- ----+ | | + -- ----+ | NULL | + -- ----+ 1 inch Set (0.00 sec)

2.

MySQL Database (0) Exercise analysis