1、關系數據的各種操作
並（union）
把相同字段數目和字段類型的表合並到一起
笛卡爾積（cartesian product）
內連接 inner jion
所謂連接就是在表關系的笛卡爾積數據記錄中，按照相應字段值的比較條件進行選擇生成一個新的關系。連接又分為內連接，外連接，交叉連接。
內連接：在表關系的笛卡爾積數據記錄中，保留表關系中所有匹配的數據記錄，捨棄不匹配的數據記錄。
按照匹配的條件可以分成自然連接，等值連接，和不等連接
自然連接：
表關系的笛卡爾積中，首先根據表關系中相同名稱的字段自動進行記錄匹配，然後去掉重復的字段。
等值連接
表關系的笛卡爾積中，選擇所匹配字段值相等(=符號)的數據記錄。
不等連接
表關系的笛卡爾積中，選擇所匹配字段值(!=)的數據記錄。
外連接 outer jion
在表關系的笛卡爾積數據記錄中，不僅保留表關系中所有匹配的數據記錄，而且還會保留部分不匹配的數據記錄。
按照保留不匹配條件數據記錄來源可以分為：left outer union，right outer union ，full outer jion
左連接：
表關系的笛卡爾積中，除了選擇相匹配的數據記錄，還包含關聯左邊表中不匹配的數據記錄。
右連接：
表關系的笛卡爾積中，除了選擇相匹配（相等）的數據記錄，還包含右邊表中不匹配的數據記錄。
全連接：
表關系的笛卡爾積中，除了選擇相匹配（相等）的數據記錄，還包含左右表中不匹配的數據記錄。
交叉連接 cross jion
2、內連接查詢
1、在from子句裡利用逗號(,)區分多個表，在where子句裡通過邏輯表達式來實現匹配條件，從而實現表的連接
2、ANSI連接語法形式，在from子句中使用"jion...on"關鍵字，而連接條件寫在關鍵字on子句裡，這是推薦使用的連接語法。
select field1,...,fieldn
from join_tablename1
inner join join_tablename2 [inner join join_tablenamen]
on join_condition
*等值連接
自連接：一種特殊的等值連接，自身與自身連接
為表取別名機制
select field1,...,fieldn [AS] otherfieldn
from tablename1 [as] othertablename1,...,tablenamen [AS] othertablenamen
*不等連接
可以使用的關系運算符包含">" ">=" "<" "<=" "!="
3、外連接查詢
select field1,...,fieldn
from join_tablename1 left|right|full [outer] join join_tablename2
on join_condition
左外連接
新關系中執行匹配條件時，以關鍵字left join左邊的表為參考表
select e.ename empolyee,e.job,l.ename leader
from t_emplyee e left outer join t_employee l
on e.mgr=l.ename;
+--------------+-----------+--------+
| employe | job | leader |
+--------------+-----------+--------+
| smith | clerk | ford |
| alen | salesman | black |
| ward | salesman | black |
| jones | manager | king |
| martin | salesman | black |
| ford | analyst | jones |
| black | manager | ford |
| king | president | NULL |
+--------------+-----------+--------+
8 rows in set (0.00 sec)
select e.ename empolyee,e.job,l.ename leader
from t_emplyee e inner join t_employee l
on e.mgr=l.ename;
+--------------+----------+--------+
| employee | job | leader |
+--------------+----------+--------+
| ford | analyst | jones |
| smith | clerk | ford |
| black | manager | ford |
| alen | salesman | black |
| ward | salesman | black |
| martin | salesman | black |
| jones | manager | king |
+--------------+----------+--------+
7 rows in set (0.00 sec)
觀察發現：雖然等值連接sql語句也顯示出雇員的相應信息，但是沒有顯示出雇員為king的信息。
右外連接
新關系中執行匹配條件時，以關鍵字right join右邊的表為參考表
4、合並查詢數據記錄
select field1 field2,...,fieldn
from tablename1
union |union all
select field1 field2 ... fieldn
from tablename2
union | union all
select field1 field2 ... fieldn
from tablename3
...
通過union來實現並操作，即可通過其將多個select語句的查詢結果合並在一起組成新的關系。
例：
mysql> create table t_cstudent(
-> name varchar(20),
-> sex varchar(5));
Query OK, 0 rows affected (0.09 sec)
mysql> create table t_mstudent(
-> name varchar(20),
-> sex varchar(5));
Query OK, 0 rows affected (0.07 sec)
創建兩張表，t_cstudent,t_mstudent
select * from t_cstudent;
+----------+-------+
| name | sex |
+----------+-------+
| ccjgong1 | man |
| ccjgong2 | woman |
| ccjgong3 | man |
| ccjgong4 | woman |
| ccjgong5 | woman |
| cmcjgong | man |
+----------+-------+
select * from t_mstudent;
+----------+-------+
| name | sex |
+----------+-------+
| cmcjgong | man |
| mcjgong1 | women |
| mcjgong2 | man |
| mcjgong3 | man |
| mcjgong4 | woman |
| mcjgong5 | woman |
+----------+-------+
select * from t_cstudent
union
select * from t_mstudent
+----------+-------+
| name | sex |
+----------+-------+
| ccjgong1 | man |
| ccjgong2 | woman |
| ccjgong3 | man |
| ccjgong4 | woman |
| ccjgong5 | woman |
********| cmcjgong | man |*******
| mcjgong1 | women |
| mcjgong2 | man |
| mcjgong3 | man |
| mcjgong4 | woman |
| mcjgong5 | woman |
+----------+-------+
執行結果顯示出合並後的數據記錄，同時去除了重復數據記錄，使新關系裡沒有任何重復的數據記錄。
通過union all 把查詢結果集直接合並在一起。
select * from t_cstudent
union all
select * from t_mstudent
+----------+-------+
| name | sex |
+----------+-------+
| ccjgong1 | man |
| ccjgong2 | woman |
| ccjgong3 | man |
| ccjgong4 | woman |
| ccjgong5 | woman |
********| cmcjgong | man |********
********| cmcjgong | man |********
| mcjgong1 | women |
| mcjgong2 | man |
| mcjgong3 | man |
| mcjgong4 | woman |
| mcjgong5 | woman |
+----------+-------+
與union相比，執行結果成功顯示出合並後的數據記錄，但是沒有去掉了重復數據記錄，即新關系裡存在重復的數據記錄
5、子查詢
在mysql中雖然可以通過連接查詢實現多表查詢數據記錄，但是不建議使用。這是因為連接查詢的性能很差。因此出現了連接查詢的替代者子查詢。在具體應用中，mysql軟件推薦使用子查詢來實現多表查詢數據記錄。
兩個表執行查詢時，會對表先進行笛卡爾積，然後再選取符合匹配條件的數據記錄。進行笛卡爾積操作時，會生成兩個數表數據記錄的乘積條數據記錄，如果這兩張表的數據記錄比較大，則在進行笛卡爾積操作時就會造成死機。
對於有經驗的用戶，首先會通過count()函數來統計操作表笛卡爾積後的數據記錄數，然後才會進行多表查詢，因此多表查詢一般會經過如下步驟：
1、通過統計函數查詢所關聯表笛卡爾積後的數據記錄數：
select count(*) from t_dept,t_employee
2、如果查詢到的數據記錄數mysql軟件可以接受，然後進行多表連接查詢，否則就應該考慮通過其他方式來實現。
3、如果數據記錄數mysql不能接受，則使用子查詢來實現多表查詢。
子查詢：就是在一個查詢之中嵌套了其他的若干查詢，即在select查詢語句的where或from子句中包含另一個select查詢語句。在查詢語句中，外層的select查詢語句成為主查詢，where子句中的select查詢語句被稱為子查詢，也被稱為嵌套查詢。
通過子查詢可以實現多表查詢，該查詢語句中可能包含in，any，all，exist等關鍵字，除此之外還可能包含比較運算符。理論上子查詢可以出現在查詢語句的任意位置，但是在實際開發中，子查詢經常出現在where和from中
where:該位置的子查詢一般返回單行單列，多行單列，單行多列數據記錄
from：該位置的子查詢一般返回多行多列數據記錄，可以當做一張臨時表
+-------+--------+-----------+------+---------------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sale | comm | deptno |
+-------+--------+-----------+------+---------------------+---------+--------+--------+
| 7369 | smith | clerk | 7902 | 1981-03-12 00:00:00 | 800.00 | NULL | 20 |
| 7499 | alen | salesman | 7698 | 1982-03-12 00:00:00 | 1600.00 | 300.00 | 30 |
| 7521 | ward | salesman | 7698 | 1983-03-12 00:00:00 | 1250.00 | 500.00 | 30 |
| 7566 | jones | manager | 7839 | 1981-03-12 00:00:00 | 2893.00 | NULL | 20 |
| 7654 | martin | salesman | 7698 | 1981-03-12 00:00:00 | 1250.00 | NULL | 30 |
| 7902 | ford | analyst | 7566 | 0000-00-00 00:00:00 | 3000.00 | NULL | 30 |
| 7698 | black | manager | 7902 | 1981-03-02 00:00:00 | 2850.00 | NULL | 30 |
| 7839 | king | president | NULL | 1981-03-12 00:00:00 | 5000.00 | NULL | 10 |
+-------+--------+-----------+------+---------------------+---------+--------+--------+
a、返回結果為單行單列和單行多列子查詢
1、返回結果為單行單列子查詢
select sale from t_employee where ename='smith';
select * from t_employee
where sale>(
select sale
from t_employee
where ename='smith');
+-------+--------+-----------+------+---------------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sale | comm | deptno |
+-------+--------+-----------+------+---------------------+---------+--------+--------+
| 7499 | alen | salesman | 7698 | 1982-03-12 00:00:00 | 1600.00 | 300.00 | 30 |
| 7521 | ward | salesman | 7698 | 1983-03-12 00:00:00 | 1250.00 | 500.00 | 30 |
| 7566 | jones | manager | 7839 | 1981-03-12 00:00:00 | 2893.00 | NULL | 20 |
| 7654 | martin | salesman | 7698 | 1981-03-12 00:00:00 | 1250.00 | NULL | 30 |
| 7902 | ford | analyst | 7566 | 0000-00-00 00:00:00 | 3000.00 | NULL | 30 |
| 7698 | black | manager | 7902 | 1981-03-02 00:00:00 | 2850.00 | NULL | 30 |
| 7839 | king | president | NULL | 1981-03-12 00:00:00 | 5000.00 | NULL | 10 |
+-------+--------+-----------+------+---------------------+---------+--------+--------+
2、單行多列子查詢
where子句中的子查詢除了是返回單行單列的數據記錄外，還可以返回單行多列的數據記錄，不過這種子查詢很少出現
mysql> select ename,sale,job
-> from t_employee
-> where (sale,job)=(
-> select sale,job
-> from t_employee
-> where ename='smith');
+-------+--------+-------+
| ename | sale | job |
+-------+--------+-------+
| smith | 800.00 | clerk |
+-------+--------+-------+
b、返回結果為多行單列子查詢
當子查詢的返回結果為多行單列數據記錄時，該子查詢語句一般會在主查詢語句的where子句裡出現，通常會包含in，any，all，exist等關鍵字
1、帶有關鍵字in的子查詢
mysql> select * from t_employee;
+-------+--------+-----------+------+---------------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sale | comm | deptno |
+-------+--------+-----------+------+---------------------+---------+--------+--------+
| 7369 | smith | clerk | 7902 | 1981-03-12 00:00:00 | 800.00 | NULL | 20 |
| 7499 | alen | salesman | 7698 | 1982-03-12 00:00:00 | 1600.00 | 300.00 | 30 |
| 7521 | ward | salesman | 7698 | 1983-03-12 00:00:00 | 1250.00 | 500.00 | 30 |
| 7566 | jones | manager | 7839 | 1981-03-12 00:00:00 | 2893.00 | NULL | 20 |
| 7654 | martin | salesman | 7698 | 1981-03-12 00:00:00 | 1250.00 | NULL | 30 |
| 7902 | ford | analyst | 7566 | 0000-00-00 00:00:00 | 3000.00 | NULL | 30 |
| 7698 | black | manager | 7902 | 1981-03-02 00:00:00 | 2850.00 | NULL | 30 |
| 7839 | king | president | NULL | 1981-03-12 00:00:00 | 5000.00 | NULL | 10 |
| 7676 | sandy | manager | 7839 | 1981-03-12 00:00:00 | 3500.00 | NULL | 50 |
| 7678 | edy | manager | 7839 | 1981-03-12 00:00:00 | 3500.00 | NULL | 60 |
+-------+--------+-----------+------+---------------------+---------+--------+--------+
當主查詢的條件在子查詢的查詢結果裡時，就可以通過關鍵字in來進行判斷。相反，可以使用not in
select ename,depto
from t_employee
where deptno in (
select deptno from t_dept);
+--------+--------+
| ename | deptno |
+--------+--------+
| smith | 20 |
| alen | 30 |
| ward | 30 |
| jones | 20 |
| martin | 30 |
| ford | 30 |
| black | 30 |
| king | 10 |
+--------+--------+
可以發現sandy和edy並沒有打印出來
select ename,depto
from t_employee
where deptno not in (
select deptno from t_dept);
+-------+--------+
| ename | deptno |
+-------+--------+
| sandy | 50 |
| edy | 60 |
+-------+--------+
2、帶有關鍵字any的子查詢
關鍵字any用來表示主查詢的條件為滿足子查詢返回查詢結果中任意一條數據記錄，該關鍵字有三種匹配方式：
=ANY：其功能與關鍵字IN一樣
>ANY(>=ANY):比子查詢中返回數據記錄中最小的還要大於（大於等於）數據記錄
<ANY(<=ANY):比子查詢中返回數據記錄中最大的還要小於（小於等於）數據記錄
例：
select sale from t_employee where job='manager';
+---------+
| sale |
+---------+
| 2893.00 |
| 2850.00 |
| 3500.00 |
| 3500.00 |
+---------+
select ename,job,sale from t_employee where sale < ANY (select sale from t_employee where job='manager');
+--------+----------+---------+
| ename | job | sale |
+--------+----------+---------+
| smith | clerk | 800.00 |
| alen | salesman | 1600.00 |
| ward | salesman | 1250.00 |
| jones | manager | 2893.00 |*****
| martin | salesman | 1250.00 |
| ford | analyst | 3000.00 |***
| black | manager | 2850.00 |
+--------+----------+---------+
通過例子得知，any的意思滿足子查詢的任意一條記錄，而不是所有的記錄。
3、帶有ALL的子查詢
關鍵字ALL用來表示主查詢的條件為滿足子查詢返回結果中所有數據記錄，該關鍵字有兩種匹配方式，分別為：
>ALL(>=ALL):比子查詢中返回數據記錄中最大的還要大於（大於等於）數據記錄
<ALL(<=ALL):比子查詢中返回數據記錄中最小的還要小於（小於等於）數據記錄
select ename,job,sale from t_employee where sale < ALL (select sale from t_employee where job='manager');
+--------+----------+---------+
| ename | job | sale |
+--------+----------+---------+
| smith | clerk | 800.00 |
| alen | salesman | 1600.00 |
| ward | salesman | 1250.00 |
| martin | salesman | 1250.00 |
+--------+----------+---------+
與any進行比較。
4、帶有關鍵字exist的子查詢
EXISTS用於檢查子查詢是否至少會返回一行數據，該子查詢實際上並不返回任何數據，而是返回值True或False。
c、返回結果為多行多列子查詢
當子查詢的返回結果為多行多列數據記錄時，該子查詢語句一般會在主查詢語句的from子句裡，被當做一張臨時表的方式來處理。
例：執行sql語句select，於數據庫company中，查詢雇員表t_employee中各部門的部門號，部門名稱，部門地址，雇員人數和平均工資。
select * from t_employee;
+-------+--------+-----------+------+---------------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sale | comm | deptno |
+-------+--------+-----------+------+---------------------+---------+--------+--------+
| 7369 | smith | clerk | 7902 | 1981-03-12 00:00:00 | 800.00 | NULL | 20 |
| 7499 | alen | salesman | 7698 | 1982-03-12 00:00:00 | 1600.00 | 300.00 | 30 |
| 7521 | ward | salesman | 7698 | 1983-03-12 00:00:00 | 1250.00 | 500.00 | 30 |
| 7566 | jones | manager | 7839 | 1981-03-12 00:00:00 | 2893.00 | NULL | 20 |
| 7654 | martin | salesman | 7698 | 1981-03-12 00:00:00 | 1250.00 | NULL | 30 |
| 7902 | ford | analyst | 7566 | 0000-00-00 00:00:00 | 3000.00 | NULL | 30 |
| 7698 | black | manager | 7902 | 1981-03-02 00:00:00 | 2850.00 | NULL | 30 |
| 7839 | king | president | NULL | 1981-03-12 00:00:00 | 5000.00 | NULL | 10 |
| 7676 | sandy | manager | 7839 | 1981-03-12 00:00:00 | 3500.00 | NULL | 50 |
| 7678 | edy | manager | 7839 | 1981-03-12 00:00:00 | 3500.00 | NULL | 60 |
+-------+--------+-----------+------+---------------------+---------+--------+--------+
select * from t_dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | accounting | new york |
| 20 | researcher | dalls |
| 30 | sales | chicago |
| 40 | operation | boston |
+--------+------------+----------+
t_dept和t_employee的結構和數據如上所示。
解法一：內連接法
select d.deptno,d.dname,d.loc,count(e.ename) number,avg(e.sale) average
from t_dept d inner join t_employee e on d.deptno=e.deptno
group by d.deptno;
+--------+------------+----------+--------+-------------+
| deptno | dname | loc | number | average |
+--------+------------+----------+--------+-------------+
| 10 | accounting | new york | 1 | 5000.000000 |
| 20 | researcher | dalls | 2 | 1846.500000 |
| 30 | sales | chicago | 5 | 1990.000000 |
+--------+------------+----------+--------+-------------+
解法二：外連接法
select d.deptno,d.dname,d.loc,count(e.ename) number,avg(e.sale) average
from t_dept d left outer join t_employee e on d.deptno=e.deptno
group by d.deptno;
+--------+------------+----------+--------+-------------+
| deptno | dname | loc | number | average |
+--------+------------+----------+--------+-------------+
| 10 | accounting | new york | 1 | 5000.000000 |
| 20 | researcher | dalls | 2 | 1846.500000 |
| 30 | sales | chicago | 5 | 1990.000000 |
| 40 | operation | boston | 0 | NULL |
+--------+------------+----------+--------+-------------+
解法三：子查詢
mysql> select d.deptno,d.dname,d.loc,number,average
-> from t_dept d inner join
-> (select deptno dno,count(empno) number,avg(sale) average from t_employee group by deptno desc) employee
-> on d.deptno=employee.dno;
+--------+------------+----------+--------+-------------+
| deptno | dname | loc | number | average |
+--------+------------+----------+--------+-------------+
| 30 | sales | chicago | 5 | 1990.000000 |
| 20 | researcher | dalls | 2 | 1846.500000 |
| 10 | accounting | new york | 1 | 5000.000000 |
+--------+------------+----------+--------+-------------+
