mysql表连接，子查询以及if判断

2019独角兽企业重金招聘Python工程师标准>>>

创建表： 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;

转载于:https://my.oschina.net/aptx4869/blog/348727