MYSQL/子查詢/關聯查詢/ANY關鍵字/ALL關鍵字/SOME關鍵稚/IN關鍵字

最新推荐文章于 2023-11-21 14:19:11 发布

082200598

最新推荐文章于 2023-11-21 14:19:11 发布

阅读量81

点赞数

分类专栏：數據庫 MYSQL數據庫

本文链接：https://blog.csdn.net/menpizzer/article/details/90723738

版权

數據庫同时被 2 个专栏收录

20 篇文章 0 订阅

订阅专栏

MYSQL數據庫

17 篇文章 0 订阅

订阅专栏

點我查看自連結跟自然連結查詢

#子查詢

emp:
+----+--------+-----------+------+------------+-------+------+--------+
| id | name   | job       | mgr  | hiredate   | sal   | comm | deptno |
+----+--------+-----------+------+------------+-------+------+--------+
|  1 | 伊澤   | 經理      | NULL | 2011-06-17 | 52000 | 2000 |      1 |
|  2 | 大澤   | 副理      |    1 | 2011-06-17 | 42000 | 1000 |      1 |
|  3 | 吉澤   | 職員      |    2 | 2011-06-17 | 22000 |  100 |      1 |
|  4 | 未央   | 職員      |    2 | 2013-06-02 | 21000 |   50 |      2 |
|  5 | 蔡八   | 實習生    |    3 | 2018-06-02 | 11000 |    0 |      3 |
|  6 | 閃哥   | 實習生    |    3 | 2018-08-02 | 11000 |    0 |      4 |
+----+--------+-----------+------+------------+-------+------+--------+
dept:
+--------+-----------+--------+
| deptno | dname     | loc    |
+--------+-----------+--------+
|      1 | 人力部    | 北京   |
|      2 | 市場部    | 上海   |
|      3 | 銷售部    | 廣州   |
|      7 | 行銷部    | 四川   |
+--------+-----------+--------+
#查詢誰的sal>'閃哥'的sal
SELECT *FROM emp WHERE sal >(SELECT sal FROM emp WHERE name='閃哥');
>>>
+----+--------+--------+------+------------+-------+------+--------+
| id | name   | job    | mgr  | hiredate   | sal   | comm | deptno |
+----+--------+--------+------+------------+-------+------+--------+
|  1 | 伊澤   | 經理   | NULL | 2011-06-17 | 52000 | 2000 |      1 |
|  2 | 大澤   | 副理   |    1 | 2011-06-17 | 42000 | 1000 |      1 |
|  3 | 吉澤   | 職員   |    2 | 2011-06-17 | 22000 |  100 |      1 |
|  4 | 未央   | 職員   |    2 | 2013-06-02 | 21000 |   50 |      2 |
+----+--------+--------+------+------------+-------+------+--------+
#查詢有誰是人力部的
SELECT *FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='人力部');
>>>
+----+--------+--------+------+------------+-------+------+--------+
| id | name   | job    | mgr  | hiredate   | sal   | comm | deptno |
+----+--------+--------+------+------------+-------+------+--------+
|  1 | 伊澤   | 經理   | NULL | 2011-06-17 | 52000 | 2000 |      1 |
|  2 | 大澤   | 副理   |    1 | 2011-06-17 | 42000 | 1000 |      1 |
|  3 | 吉澤   | 職員   |    2 | 2011-06-17 | 22000 |  100 |      1 |
+----+--------+--------+------+------------+-------+------+--------+

#關聯查詢

#查找 emp命為e,dept命為d 判斷 e中的deptno 是否等於 d中的deptno 並且為 d中的dname名為人力部的對象
SELECT *FROM emp e,dept d WHERE e.deptno = d.deptno and d.dname='人力部';
>>>
+----+--------+--------+------+------------+-------+------+--------+
| id | name   | job    | mgr  | hiredate   | sal   | comm | deptno |
+----+--------+--------+------+------------+-------+------+--------+
|  1 | 伊澤   | 經理   | NULL | 2011-06-17 | 52000 | 2000 |      1 |
|  2 | 大澤   | 副理   |    1 | 2011-06-17 | 42000 | 1000 |      1 |
|  3 | 吉澤   | 職員   |    2 | 2011-06-17 | 22000 |  100 |      1 |
+----+--------+--------+------+------------+-------+------+--------+

#在from後面的子查詢

#查詢結果封裝成emp2虛擬表(emp2並部會被創建,用完即扔)
SELECT *FROM(SELECT id,name,sal FROM emp)emp2;
>>>
+----+--------+-------+
| id | name   | sal   |
+----+--------+-------+
|  1 | 伊澤   | 52000 |
|  2 | 大澤   | 42000 |
|  3 | 吉澤   | 22000 |
|  4 | 未央   | 21000 |
|  5 | 蔡八   | 11000 |
|  6 | 閃哥   | 11000 |
+----+--------+-------+

#ANY關鍵字

#查詢有誰比3號部門的任一薪資都來的高
# 判斷所有在emp表中的sal> (搜尋薪資在emp中且部門為3的人)
SELECT *FROM emp WHERE sal> ANY(SELECT sal FROM emp WHERE deptno=3);
>>>
+----+--------+--------+------+------------+-------+------+--------+
| id | name   | job    | mgr  | hiredate   | sal   | comm | deptno |
+----+--------+--------+------+------------+-------+------+--------+
|  1 | 伊澤   | 經理   | NULL | 2011-06-17 | 52000 | 2000 |      1 |
|  2 | 大澤   | 副理   |    1 | 2011-06-17 | 42000 | 1000 |      1 |
|  3 | 吉澤   | 職員   |    2 | 2011-06-17 | 22000 |  100 |      1 |
|  4 | 未央   | 職員   |    2 | 2013-06-02 | 21000 |   50 |      2 |
+----+--------+--------+------+------------+-------+------+--------+

#ALL關鍵字

SELECT *FROM emp WHERE sal> ALL(SELECT sal FROM emp WHERE deptno=3);

#SOME關鍵稚

同ANY

#IN關鍵字

#IN
SELECT *FROM emp WHERE deptno IN(1,2);
>>>
+----+--------+--------+------+------------+-------+------+--------+
| id | name   | job    | mgr  | hiredate   | sal   | comm | deptno |
+----+--------+--------+------+------------+-------+------+--------+
|  1 | 伊澤   | 經理   | NULL | 2011-06-17 | 52000 | 2000 |      1 |
|  2 | 大澤   | 副理   |    1 | 2011-06-17 | 42000 | 1000 |      1 |
|  3 | 吉澤   | 職員   |    2 | 2011-06-17 | 22000 |  100 |      1 |
|  4 | 未央   | 職員   |    2 | 2013-06-02 | 21000 |   50 |      2 |
+----+--------+--------+------+------------+-------+------+--------+
#NOT IN
SELECT *FROM emp WHERE deptno NOT IN(1,2);
>>>
+----+--------+-----------+------+------------+-------+------+--------+
| id | name   | job       | mgr  | hiredate   | sal   | comm | deptno |
+----+--------+-----------+------+------------+-------+------+--------+
|  5 | 蔡八   | 實習生    |    3 | 2018-06-02 | 11000 |    0 |      3 |
|  6 | 閃哥   | 實習生    |    3 | 2018-08-02 | 11000 |    0 |      4 |
+----+--------+-----------+------+------------+-------+------+--------+
#查找 emp表中的deptno是否符合==(查找dept中的人力部跟市場部返回deptno編號 )
SELECT *FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dname='人力部' or dname ='市場部');
>>>
+----+--------+--------+------+------------+-------+------+--------+
| id | name   | job    | mgr  | hiredate   | sal   | comm | deptno |
+----+--------+--------+------+------------+-------+------+--------+
|  1 | 伊澤   | 經理   | NULL | 2011-06-17 | 52000 | 2000 |      1 |
|  2 | 大澤   | 副理   |    1 | 2011-06-17 | 42000 | 1000 |      1 |
|  3 | 吉澤   | 職員   |    2 | 2011-06-17 | 22000 |  100 |      1 |
|  4 | 未央   | 職員   |    2 | 2013-06-02 | 21000 |   50 |      2 |
+----+--------+--------+------+------------+-------+------+--------+
#也可以使用IN來做批量刪除
DELETE FROM emp WHERE deptno IN(5,1);