#建表
create table department(
id int,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入資料
insert into department values
(200,'技術'),
(201,'人力資源'),
(202,'銷售'),
(203,'營運');
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
#檢視表結構和資料
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技術 |
| 201 | 人力資源 |
| 202 | 銷售 |
| 203 | 營運 |
+------+--------------+
mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
#表department與employee
三、多表連接配接查詢
##!!!!重點：外連結文法
SELECT 字段清單
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
1、 交叉連接配接（ 笛卡爾積查詢 ）
不适用任何比對條件,生成笛卡爾積
1）笛卡爾積查詢介紹
#select * from 表一,表二
2）笛卡爾積查詢使用
select * from emp,dep; #将兩張表拼在一起
select * from emp,dep where dep.id = emp.dep_id; #找到兩張表中對應的關系記錄
select emp.name from dep,emp where dep.id = emp.dep_id and dep.name='技術'; #拿到篩選後的技術部門資料中emp.name的資料
#第一步:連表
select * from dep inner join emp on dep.id=emp.dep_id;
#第二步: 過濾
select * from dep inner join emp on dep.id=emp.dep_id where dep.name='技術';
#第三步:找對應字段資料
select emp.name from dep inner join emp on dep.id=emp.dep_id where dep.name='技術';
#找兩張表共有的部分，相當于利用條件從笛卡爾積結果中篩選出了正确的結果
#department沒有204這個部門，因而employee表中關于204這條員工資訊沒有比對出來
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+-----------+------+--------+--------------+
| id | name | age | sex | name |
+----+-----------+------+--------+--------------+
| 1 | egon | 18 | male | 技術 |
| 2 | alex | 48 | female | 人力資源 |
| 3 | wupeiqi | 38 | male | 人力資源 |
| 4 | yuanhao | 28 | female | 銷售 |
| 5 | liwenzhou | 18 | male | 技術 |
+----+-----------+------+--------+--------------+
#上述sql等同于
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
#實列：
select * from dep left join emp on dep.id=emp.dep_id union select * from dep right join emp on dep.id=emp.dep_id;
#全外連接配接：在内連接配接的基礎上增加左邊有右邊沒有的和右邊有左邊沒有的結果
#注意：mysql不支援全外連接配接 full JOIN
#強調：mysql可以使用此種方式間接實作全外連接配接
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;
#檢視結果
+------+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技術 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技術 |
| 2 | alex | female | 48 | 201 | 201 | 人力資源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力資源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 銷售 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 營運 |
+------+------------+--------+------+--------+------+--------------+
#注意 union與union all的差別：union會去掉相同的紀錄
四、符合條件連接配接查詢
#示例1：以内連接配接的方式查詢employee和department表，并且employee表中的age字段值必須大于25,即找出年齡大于25歲的員工以及員工所在的部門
select employee.name,department.name from employee inner join department
on employee.dep_id = department.id
where age > 25;
#示例2：以内連接配接的方式查詢employee和department表，并且以age字段的升序方式顯示
select employee.id,employee.name,employee.age,department.name from employee,department
where employee.dep_id = department.id
and age > 25
order by age asc;
五、子查詢
子查詢是将一個查詢的結果作為另一個查詢的條件
1》#子查詢是将一個查詢語句嵌套在另一個查詢語句中
2》#内層查詢語句的查詢結果，可以為外層查詢語句提供查詢條件。
3》#子查詢中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等關鍵字
4》#還可以包含比較運算符：= 、 !=、> 、<等
#實列：
select name from emp where dep_id = (select id from dep where name = '技術');
1、帶in關鍵字的子查詢
#查詢平均年齡在25歲以上的部門名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#檢視技術部員工姓名
select name from employee
where dep_id in
(select id from department where name='技術');
#檢視不足1人的部門名(子查詢得到的是有人的部門id)
select name from department where id not in (select distinct dep_id from employee);
##!!!!!!注意not in
not in 無法處理null的值，即子查詢中如果存在null的值，not in将無法處理，如下
mysql> select * from emp;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
| 7 | xxx | male | 19 | NULL |
+----+------------+--------+------+--------+
7 rows in set (0.00 sec)
mysql> select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技術 |
| 201 | 人力資源 |
| 202 | 銷售 |
| 203 | 營運 |
+------+--------------+
4 rows in set (0.00 sec)
# 子查詢中存在null
mysql> select * from dep where id not in (select distinct dep_id from emp);
Empty set (0.00 sec)
# 解決方案如下
mysql> select * from dep where id not in (select distinct dep_id from emp where dep_id is not null);
+------+--------+
| id | name |
+------+--------+
| 203 | 營運 |
+------+--------+
1 row in set (0.00 sec)
mysql>
2、帶any關鍵字的子查詢
#在 SQL 中 ANY 和 SOME 是同義詞，SOME 的用法和功能和 ANY 一模一樣。
#ANY 和 IN 運算符不同之處一
ANY 必須和其他的比較運算符共同使用，而且ANY必須将比較運算符放在 ANY 關鍵字之前，所比較的值需要比對子查詢中的任意一個值，這也就是 ANY 在英文中所表示的意義
例如：#使用 IN 和使用 ANY運算符得到的結果是一緻的
select * from employee where salary = any (
select max(salary) from employee group by depart_id);
select * from employee where salary in (
select max(salary) from employee group by depart_id);
結論：也就是說“=ANY”等價于 IN 運算符，而“<>ANY”則等價于 NOT IN 運算符
#ANY和 IN 運算符不同之處二
ANY 運算符不能與固定的集合相比對，比如下面的 SQL 語句是錯誤的
SELECT
*
FROM
T_Book
WHERE
FYearPublished < ANY (2001, 2003, 2005)
3、帶all關鍵字的子查詢
#all同any類似，隻不過all表示的是所有，any表示任一
1>#查詢出那些薪資比所有部門的平均薪資都高的員工=》薪資在所有部門平均線以上的狗币資本家
select * from employee where salary > all (
select avg(salary) from employee group by depart_id);
2>#查詢出那些薪資比所有部門的平均薪資都低的員工=》薪資在所有部門平均線以下的無産階級勞苦大衆
select * from employee where salary < all (
select avg(salary) from employee group by depart_id);
3>#查詢出那些薪資比任意一個部門的平均薪資低的員工=》薪資在任一部門平均線以下的員工select * from employee where salary < any ( select avg(salary) from employee group by depart_id);
4>#查詢出那些薪資比任意一個部門的平均薪資高的員工=》薪資在任一部門平均線以上的員工
select * from employee where salary > any (
select avg(salary) from employee group by depart_id);
4、帶比較運算符的子查詢
#比較運算符：=、!=、>、>=、<、<=、<>
#查詢大于所有人平均年齡的員工名與年齡
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
2 rows in set (0.00 sec)
#查詢大于部門内平均年齡的員工名、年齡
select t1.name,t1.age from emp t1
inner join
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;
5、帶exists關鍵字的子查詢
EXISTS關字鍵字表示存在
在使用EXISTS關鍵字時，内層查詢語句不傳回查詢的記錄
而是傳回一個真假值 : True或False
當傳回True時，外層查詢語句将進行查詢；當傳回值為False時，外層查詢語句不進行查詢
#department表中存在dept_id=203，Ture
mysql> select * from employee
-> where exists
-> (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
#department表中存在dept_id=205，False
mysql> select * from employee
-> where exists
-> (select id from department where id=204);
Empty set (0.00 sec)
1）in與exists
！！！！！！當in和exists在查詢效率上比較時，in查詢的效率快于exists的查詢效率！！！！！！
==============================exists==============================
# exists
exists後面一般都是子查詢，後面的子查詢被稱做相關子查詢（即與主語句相關），當子查詢傳回行數時，exists條件傳回true，
否則傳回false，exists是不傳回清單的值的，exists隻在乎括号裡的資料能不能查找出來，是否存在這樣的記錄。
# 例：
查詢出那些班級裡有學生的班級
select * from class where exists (select * from stu where stu.cid=class.id)
# exists的執行原理為：
1》依次執行外部查詢：即select * from class
2》然後為外部查詢傳回的每一行分别執行一次子查詢：即(select * from stu where stu.cid=class.cid)
3》子查詢如果傳回行，則exists條件成立，條件成立則輸出外部查詢取出的那條記錄
==============================in==============================
# in
in後跟的都是子查詢，in()後面的子查詢 是傳回結果集的
# 例
查詢和所有女生年齡相同的男生
select * from stu where sex='男' and age in(select age from stu where sex='女')
# in的執行原理為：
in()的執行次序和exists()不一樣,in()的子查詢會先産生結果集,
然後主查詢再去結果集裡去找符合要求的字段清單去.符合要求的輸出,反之則不輸出.
2） not in與 not exists
!!!!!!not exists查詢的效率遠遠高與not in查詢的效率。!!!!!!
==========================not in============================
not in()子查詢的執行順序是：
為了證明not in成立，即找不到，需要一條一條地查詢表，符合要求才傳回子查詢的結果集，不符合的就繼續查詢下一條記錄，直到把表中的記錄查詢完，隻能查詢全部記錄才能證明，并沒有用到索引。
=========================not exists==============================
not exists：
如果主查詢表中記錄少，子查詢表中記錄多，并有索引
#例如:查詢那些班級中沒有學生的班級
select * from class
where not exists
(select * from student where student.cid = class.cid)
#not exists的執行順序是：
3）示例：
#資料準備：
#建立庫
create database db13;
use db13
#建表
create table student(
id int primary key auto_increment,
name varchar(16)
);
create table course(
id int primary key auto_increment,
name varchar(16),
comment varchar(20)
);
create table student2course(
id int primary key auto_increment,
sid int,
cid int,
foreign key(sid) references student(id),
foreign key(cid) references course(id)
);
#往表插入資料
insert into student(name) values
("egon"),
("lili"),
("jack"),
("tom");
insert into course(name,comment) values
("資料庫","資料倉庫"),
("數學","根本學不會"),
("英語","鳥語花香");
insert into student2course(sid,cid) values
(1,1),
(1,2),
(1,3),
(2,1),
(2,2),
(3,2);
####################### 實列測試 #########################
1》#查詢選修了所有課程的學生id、name:（即該學生根本就不存在一門他沒有選的課程。）
select * from student s where not exists
(select * from course c where not exists
(select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
select s.name from student as s
inner join student2course as sc
on s.id=sc.sid
group by s.name
having count(sc.id) = (select count(id) from course);
2》#查詢沒有選擇所有課程的學生，即沒有全選的學生。（存在這樣的一個學生，他至少有一門課沒有選）
select * from student s where exists
(select * from course c where not exists
(select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
3》#查詢一門課也沒有選的學生。（不存這樣的一個學生，他至少選修一門課程）
select * from student s where not exists
(select * from course c where exists
(select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
4》#查詢至少選修了一門課程的學生。
select * from student s where exists
(select * from course c where exists
(select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
6、案列
查詢每個部門最新入職的那位員工
############ 準備表和記錄 #########
company.employee
員工id id int
姓名 emp_name varchar
性别 sex enum
年齡 age int
入職日期 hire_date date
崗位 post varchar
職位描述 post_comment varchar
薪水 salary double
辦公室 office int
部門編号 depart_id int
#建立表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一個部門一個屋子
depart_id int
);
#檢視表結構
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
#插入記錄
#三個部門：教學，銷售，營運
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩駐沙河辦事處外交大使',7300.33,401,1), #以下是教學部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龍','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是銷售部門
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('張野','male',28,'20160311','operation',10000.13,403,3), #以下是營運部門
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬銀','female',18,'20130311','operation',19000,403,3),
('程咬銅','male',18,'20150411','operation',18000,403,3),
('程咬鐵','female',18,'20140512','operation',17000,403,3)
;
#ps：如果在windows系統中，插入中文字元，select的結果為空白，可以将所有字元編碼統一設定成gbk
#方式一（連結清單）
SELECT
*
FROM
emp AS t1
INNER JOIN (
SELECT
post,
max(hire_date) max_date
FROM
emp
GROUP BY
post
) AS t2 ON t1.post = t2.post
WHERE
t1.hire_date = t2.max_date;
#方式二（子查詢）
mysql> select (select t2.name from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post;
+---------------------------------------------------------------------------------------+
| (select t2.name from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) |
+---------------------------------------------------------------------------------------+
| 張野 |
| 格格 |
| alex |
| egon |
+---------------------------------------------------------------------------------------+
rows in set (0.00 sec)
mysql> select (select t2.id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post;
+-------------------------------------------------------------------------------------+
| (select t2.id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) |
+-------------------------------------------------------------------------------------+
| 14 |
| 13 |
| 2 |
| 1 |
+-------------------------------------------------------------------------------------+
rows in set (0.00 sec)
#正确答案
mysql> select t3.name,t3.post,t3.hire_date from emp as t3 where id in (select (select id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post);
+--------+-----------------------------------------+------------+
| name | post | hire_date |
+--------+-----------------------------------------+------------+
| egon | 老男孩駐沙河辦事處外交大使 | 2017-03-01 |
| alex | teacher | 2015-03-02 |
| 格格 | sale | 2017-01-27 |
| 張野 | operation | 2016-03-11 |
+--------+-----------------------------------------+------------+
rows in set (0.00 sec)
#方式一為正确答案，方式二中的limit 1有問題（每個部門可能有>1個為同一時間入職的新員工），我隻是想用該例子來說明可以在select後使用子查詢
#可以基于上述方法解決：比如某網站在全國各個市都有站點，每個站點一條資料，想取每個省下最新的那一條市的網站品質資訊
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)