mysql表串連，子查詢以及if判斷

標籤：io   ar   使用   java   資料   on   ef   as   sql   

建立表：
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 |+-------+------------+------+--------+------+如果子查詢結果唯一，可以用=代替inselect * 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;

mysql表串連，子查詢以及if判斷