MySQL table joins, subqueries, and if judgments

To create a table:
CREATE TABLE emp (
enamevarchar () DEFAULT NULL,
hiredateDate DEFAULT NULL,
salDecimal (10,2) DEFAULT NULL,
deptnoInt (2) DEFAULT NULL,
ageInt (3) DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8

CREATE TABLE dept (
deptnoInt (2) DEFAULT NULL,
deptnamevarchar (DEFAULT) NULL
) Engine=innodb DEFAULT Charset=utf8

Table joins: Use table joins when you need to display fields from multiple tables at the same time

Internal connection: Select only two tables in the matching record outside the connection: Select the matching record, but also select the unmatched record left connection: Contains all the records in the left table, even if there is no record in the right table and it matches for example, the user table has a department ID, use left connection query right table, get the corresponding department name, The result is null for the ID in the user table when the corresponding record is not found in the right table. Select Ename,hiredate,sal,emp.deptno,age,deptname from EMP LEFT JOIN dept on Emp.deptno=d    Ept.deptno; +-------+------------+------+--------+------+----------+    | ename | HireDate | Sal | Deptno | Age |    Deptname | +-------+------------+------+--------+------+----------+    | ZZXL | 2000-01-01 |      2000 | 1 | NULL |    DEPT1 | | Lisa | 2003-01-01 |      3000 |   2 | 20 |    Dept2 | | Dony | NULL |      2000 | 5 | NULL |    Dept5 | +-------+------------+------+--------+------+----------+ Right Connection: Contains all the records in the right table, even if there is no record in the left table that matches it, select Ename,emp.deptno,    Deptname from EMP Right join dept on Emp.deptno=dept.deptno; +-------+--------+----------+    | ename | Deptno |    Deptname | +-------+--------+----------+    |      Dony | 5 |    Dept5 | | NULL | NULL |    DEPT6 | |      ZZXL | 1 |    DEPT1 | | Lisa |     2 |    Dept2 | +-------+--------+----------+

Subquery: When you make a query, when you need a condition that is the result of another query, use a subquery
Querying users in all departments

SELECT * from EMP where deptno on (select Deptno from dept) +-------+------------+------+--------+------+| ename | HireDate |   sal  | deptno | Age  |+-------+------------+------+--------+------+| ZZXL  | 2000-01-01 | 2000 |      1 | NULL | | Lisa  | 2003-01-01      | 2 |   20 | | Dony  | NULL       |      5 | NULL |+-------+------------+------+--------+------+ If the subquery result is unique, you can use = instead of Inselect * from emp where deptno = (select Deptno From Dept Limit 1) +-------+----------+------+--------+------+| ename | HireDate | Sal  | deptno | Age  |+-------+----------+------+--------+------+| dony  | NULL     |      5 | NULL |+-------+----------+------+--------+------+

Note: Table joins are used to refine subqueries

Record Federation: Merge multiple query results to display

Select Deptno from EMP Union select Deptno from dept;//de-duplication Select DEPTNO from EMP UNION ALL select Deptno from dept;

CREATE TABLE salary (userid int,salary decimal (10,2));
Insert into salary values (1,1000), (2,2000), (3,3000), (4,4000), (1,null);

If judgment

Select *,if (salary>2000, ' High ', ' low ') as Statu from salary;+--------+--------+-------+| UserID | Salary | Statu |+--------+--------+-------+|      1 | Low   | |      2 | Low |   |      3 | High  | |      4 | 4000 |  |      1 | NULL   | Low   |+--------+--------+-------+

Ifnull judgment

Select Ifnull (salary,0) from salary;+------------------+| Ifnull (salary,0) |+------------------+| | | | 4000 | |             0                |+------------------+

Case

Select Case when salary<=2000 and ' low ' else ' high ' end from Salary;select case salary when the "Low" when 2000 Then ' mid ' else  ' high ' end from salary;

MySQL table joins, subqueries, and if judgments