mysql用if判断关联的表_mysql表连接，子查询以及if判断

创建表：

CREATE TABLE emp (

ename varchar(10) DEFAULT NULL,

hiredate date DEFAULT NULL,

sal decimal(10,2) DEFAULT NULL,

deptno int(2) DEFAULT NULL,

age int(3) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE dept (

deptno int(2) DEFAULT NULL,

deptname varchar(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

表连接：当需要同时显示多个表中的字段时，使用表连接

内连接：仅选出两张表中互相匹配的记录

外连接：既选出匹配的记录，也选出不匹配的记录

左连接：包含所有的左表中的记录，即使右表中没有和它相匹配的记录

例如，用户表中有部门id，使用左连接查询右表，得到相应的部门名称，对于用户表中的id在右表中找不到对应的记录时结果为NULL

select ename,hiredate,sal,emp.deptno,age,deptname from emp left join dept on emp.deptno=dept.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 |

+-------+------------+------+--------+------+----------+

右连接：包含所有的右表中的记录，即使左表中没有和它相匹配的记录

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 |

+-------+--------+----------+

子查询：进行查询时，当需要的条件是另外一个查询的结果是，使用子查询

查询所有部门中的用户

select * from emp where deptno in (select deptno from dept);

+-------+------------+------+--------+------+

| ename | hiredate | sal | deptno | age |

+-------+------------+------+--------+------+

| zzxl | 2000-01-01 | 2000 | 1 | NULL |

| lisa | 2003-01-01 | 3000 | 2 | 20 |

| dony | NULL | 2000 | 5 | NULL |

+-------+------------+------+--------+------+

如果子查询结果唯一，可以用=代替in

select * from emp where deptno = (select deptno from dept limit 1);

+-------+----------+------+--------+------+

| ename | hiredate | sal | deptno | age |

+-------+----------+------+--------+------+

| dony | NULL | 2000 | 5 | NULL |

+-------+----------+------+--------+------+

注意：表连接用于优化子查询

记录联合：将多次查询结果合并显示

select deptno from emp union select deptno from dept;//去除重复数据

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判断

select *,if(salary>2000,'high','low') as statu from salary;

+--------+--------+-------+

| userid | salary | statu |

+--------+--------+-------+

| 1 | 1000 | low |

| 2 | 2000 | low |

| 3 | 3000 | high |

| 4 | 4000 | high |

| 1 | NULL | low |

+--------+--------+-------+

ifnull判断

select ifnull(salary,0) from salary;

+------------------+

| ifnull(salary,0) |

+------------------+

| 1000 |

| 2000 |

| 3000 |

| 4000 |

| 0 |

+------------------+

case when

select case when salary<=2000 then 'low' else 'high' end from salary;

select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;