共計 17322 個字符，預(yù)計需要花費 44 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章給大家分享的是有關(guān) MySQL 之查詢語句的基本操作是什么的內(nèi)容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考。一起跟隨丸趣 TV 小編過來看看吧。

一. 查詢語句的基本操作

1. 查詢語句的基本操作
 - select
 - from
 - where: 約束條件
 - group by: 分組
 - having：過濾
 - distinct：去重
 - order by：排序
 - limit：限制查詢記錄的數(shù)量
 - 聚合函數(shù): count（計數(shù)）max（最大值）min（最小值）avg（平均值）sum(求和)

二. 單表查詢 1、前期表與數(shù)據(jù)準(zhǔn)備

# 創(chuàng)建一張部門表
create table emp(
 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

# 三個部門：教學(xué)，銷售，運營
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(tank , male ,17, 20170301 , 張江第一帥形象代言部門 ,7300.33,401,1), # 以下是教學(xué)部
(egon , male ,78, 20150302 , teacher ,1000000.31,401,1),
(kevin , male ,81, 20130305 , teacher ,8300,401,1),
(jason , male ,73, 20140701 , teacher ,3500,401,1),
(owen , male ,28, 20121101 , teacher ,2100,401,1),
(jerry , female ,18, 20110211 , teacher ,9000,401,1),
(大餅 , male ,18, 19000301 , teacher ,30000,401,1),
(sean , 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 系統(tǒng)中，插入中文字符，select 的結(jié)果為空白，可以將所有字符編碼統(tǒng)一設(shè)置成 gbk

- select * from emp; # 若數(shù)據(jù)比較多，比較凌亂，可以在表后面 + \G
- select * from emp\G

- select * from emp; # 若數(shù)據(jù)比較多，比較凌亂，可以在表后面 + \G 
- select * from emp\G

重點：

 寫 SQL 語句必須遵循兩點:
 - 書寫順序:
# 獲取 id 為 4、5 的兩條記錄
select * from emp where id 3 and id 
 - select
 - from
 - where
 - 執(zhí)行順序:
 比如: 圖書管理員，得先找到是哪一個圖書館 (哪張表),
 再找這本書在圖書館的哪個位置 (哪一條記錄), 最后查找這個本書中某一頁 (哪些字段值);
 select * from emp where id 3 and id 
 - from --- 找到圖書館
 - where --- 找到書的位置
 - select --- 找到書本中的某一頁
 注意: 必須記住 SQL 語句的 書寫順序 與 執(zhí)行順序 (*******);

1.where(約束條件)

# PS: 根據(jù)執(zhí)行順序來書寫 SQL 語句，一步一步來寫;
# 1. 查詢 id 大于等于 3 小于等于 6 的數(shù)據(jù) (and: 與)
select * from emp where id = 3 and id 
mysql SELECT * FROM EMP WHERE ID 3 AND ID 
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| 4 | jason | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
3 rows in set (0.00 sec)

mysql select * from emp where id between 3 and 6;
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | jason | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
4 rows in set (0.00 sec)
# 2. 查詢薪資是 20000 或者 18000 或者 17000 的數(shù)據(jù)
# or: 或者
select * from emp where salary=20000 or salary=18000 or salary=17000;
# in: 在什么里
select * from emp where salary in (20000, 18000, 17000);
mysql select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 17 | 程咬銅 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬鐵 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
3 rows in set (0.00 sec)
# 也可以使用 in(3,4,5,6)
 select * from emp where id in(3,4,5,6);
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | jason | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
4 rows in set (0.00 sec)

# like: 模糊匹配
# %: 匹配 0 個或多個任意字符 (若前后都有 %(%o%), 查所有帶有索引字符的記錄, 若頭有 % 而尾沒有 (%o), 則查所有尾帶有索引字符的記錄, 若尾有 % 而頭沒有 (o%), 則查所有頭帶有索引字符的記錄, 若前后都沒有 %(o), 則只會查詢只有索引字符的字符 )
# _: 匹配一個任意字符
select name, salary from emp where name like %o% 
+-------+------------+
| name | salary |
+-------+------------+
| egon | 1000000.31 |
| jason | 3500.00 |
| owen | 2100.00 |
+-------+------------+
3 rows in set (0.00 sec)

# 4. 查找名字個數(shù)為 3 個的員工 名字 與 薪資
select name, salary from emp where name like ___ 
+-----------+----------+
| name | salary |
+-----------+----------+
| 程咬金 | 20000.00 |
| 程咬銀 | 19000.00 |
| 程咬銅 | 18000.00 |
| 程咬鐵 | 17000.00 |
+-----------+----------+
4 rows in set (0.00 sec)
# 或者 # char_length(name): 計算名字字符的長度
select name, salary from emp where char_length(name) = 4;
mysql select name, salary from emp where char_length(name) = 3;
+-----------+----------+
| name | salary |
+-----------+----------+
| 程咬金 | 20000.00 |
| 程咬銀 | 19000.00 |
| 程咬銅 | 18000.00 |
| 程咬鐵 | 17000.00 |
+-----------+----------+
4 rows in set (0.00 sec)

select * from emp where id not in (3, 4, 5, 6);
select * from emp where id not between 3 and 6;

# 6. 查詢薪資不在 20000，18000，17000 范圍的數(shù)據(jù)
select * from emp where salary not in (20000, 18000, 17000);

select name, post_comment from emp where post_comment is null;
+-----------+--------------+
| name | post_comment |
+-----------+--------------+
| tank | NULL |
| egon | NULL |
| kevin | NULL |
| jason | NULL |
| owen | NULL |
| jerry | NULL |
| 大餅 | NULL |
| sean | NULL |
| 歪歪 | NULL |
| 丫丫 | NULL |
| 丁丁 | NULL |
| 星星 | NULL |
| 格格 | NULL |
| 張野 | NULL |
| 程咬金 | NULL |
| 程咬銀 | NULL |
| 程咬銅 | NULL |
| 程咬鐵 | NULL |
| o | NULL |
+-----------+--------------+
19 rows in set (0.00 sec)

2.group by(分組)

- 書寫順序:
 - select
 - from
 - where
 - group by
 - 執(zhí)行順序:
 - from
 - where
 - group by
 - select
# 什么叫分組 
# 比如: 一張員工表中有性別字段，可以根據(jù)性別分組，一組是男性，一組是女性，或者是根據(jù)部門分組，有教學(xué)部、銷售部等... 
 # 1. 根據(jù)部門分組
 # 非嚴(yán)格模式下可以獲取 分組條件 post 以外的字段數(shù)據(jù)
select post, salary from emp group by post;
# 設(shè)置嚴(yán)格模式:
show variables like %mode% 
# 全局設(shè)置: 永久有效
set global sql_mode= strict_trans_tables,only_full_group_by 

 設(shè)置 sql_mode 為 only_full_group_by，意味著以后但凡分組，只能取到分組的依據(jù)，不應(yīng)該在去取組里面的單個元素的值，那樣的話分組就沒有意義了，并且會報錯, 因為不分組就是對單個元素信息的隨意獲取
mysql select post, salary from emp group by post;
ERROR 1046 (3D000): No database selected
# 可以同聚合函數(shù)，間接獲取其他字段數(shù)據(jù)
 聚合函數(shù):
 count: 計數(shù)
 max: 最大值
 min: 最小值
 avg: 平均值
 sum: 求和
 group_concat(name): 可以將分組后的 所有名字獲取并進(jìn)行拼接
# 指定以: 拼接
select post, group_concat(name) from emp group by post;
select post, group_concat(name, :) from emp group by post;

 select post,max(salary) from emp group by post;
mysql select post,max(salary) from emp group by post;
+-----------------------------------+-------------+
| post | max(salary) |
+-----------------------------------+-------------+
| NULL | NULL |
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 張江第一帥形象代言部門 | 7300.33 |
+-----------------------------------+-------------+
5 rows in set (0.00 sec)
補充: as 別名: 可以給字段 加一個 別名
select post as 部門 , max(salary) as 薪資 
from emp group by post;
# 也可以簡寫, 但是不推薦
select post 部門 , max(salary) 薪資 
from emp group by post;
mysql select post as 部門 , max(salary) as 薪資 
 - from emp group by post;
+-----------------------------------+------------+
| 部門 | 薪資 |
+-----------------------------------+------------+
| NULL | NULL |
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 張江第一帥形象代言部門 | 7300.33 |
+-----------------------------------+------------+
5 rows in set (0.00 sec)

select post, count(salary) from emp group by post;
select post, count(post_comment) from emp group by post;

 查詢男員工與男員工的平均薪資，女員工與女員工的平均薪資
select sex, avg(salary) from emp group by sex;
. 統(tǒng)計各部門年齡在 30 歲以上的員工平均工資:
# 步驟: 先找到表，再找年齡 30 歲以上，再根據(jù)部門分組，最后求平均薪資;
select post, avg(salary) from emp where age 30 group by post;

總結(jié): 聚合函數(shù)，必須跟在 group by 后面 (執(zhí)行順序);

# 聚合函數(shù): 若沒有 group by 分組，默認(rèn)將查出來的數(shù)據(jù)當(dāng)做一個分組, 也能使用;
select max(salary) from emp;

# group_concat(name): 可以將分組后的 所有名字獲取并進(jìn)行拼接
 # 默認(rèn)以, 拼接
select post, group_concat(name) from emp group by post;
 # 指定以: 拼接
select post, group_concat(name, :) from emp group by post;
select post, group_concat(Name: , name) from emp group by post;

mysql select post, group_concat(name) from emp group by post;
+-----------------------------------+------------------------------------------------+
| post | group_concat(name) |
+-----------------------------------+------------------------------------------------+
| NULL | o |
| operation | 程咬鐵, 程咬銅, 程咬銀, 程咬金, 張野 |
| sale | 格格, 星星, 丁丁, 丫丫, 歪歪 |
| teacher | sean, 大餅,jerry,owen,jason,kevin,egon |
| 張江第一帥形象代言部門 | tank |
+-----------------------------------+------------------------------------------------+
5 rows in set (0.00 sec)
# 給每個部門的員工名字前 + NB_
select post, group_concat(NB_ , name) from emp group by post;
# 拼接部門員工名字 + 薪資
select post, group_concat(name, : , salary) from emp group by post;

補充:concat

# 4. 補充 concat（不分組時用）拼接字符串達(dá)到更好的顯示效果 as 語法并用
select concat(Name: , name) as 名字 , concat(Sal: , salary) as 薪資 from emp;

mysql select concat(name: , name) as 名字 ,concat(sal: ,salary) as 薪資 from emp;
+-----------------+-----------------+
| 名字 | 薪資 |
+-----------------+-----------------+
| name: tank | sal: 7300.33 |
| name: egon | sal: 1000000.31 |
| name: kevin | sal: 8300.00 |
| name: jason | sal: 3500.00 |
| name: owen | sal: 2100.00 |
| name: jerry | sal: 9000.00 |
| name: 大餅 | sal: 30000.00 |
| name: sean | sal: 10000.00 |
| name: 歪歪 | sal: 3000.13 |
| name: 丫丫 | sal: 2000.35 |
| name: 丁丁 | sal: 1000.37 |
| name: 星星 | sal: 3000.29 |
| name: 格格 | sal: 4000.33 |
| name: 張野 | sal: 10000.13 |
| name: 程咬金 | sal: 20000.00 |
| name: 程咬銀 | sal: 19000.00 |
| name: 程咬銅 | sal: 18000.00 |
| name: 程咬鐵 | sal: 17000.00 |
| name: o | NULL |
+-----------------+-----------------+
19 rows in set (0.00 sec)

3.having(過濾)

1.having 與 where 語法一樣，只不過 having 必需要在 group by 后使用;
2.where 不能使用聚合函數(shù)，但 having 可以;
 - 書寫順序:
 - select
 - from
 - where
 - group by
 - having
 - 執(zhí)行順序:
 - from
 - where
 - group by
 - having
 - select
 1、統(tǒng)計各 部門 年齡在 30 歲以上的員工平均工資，并且保留平均工資大于 10000 的部門;
select post, avg(salary) from emp where age 30 group by post having avg(salary) 10000;
mysql select post, avg(salary) from emp where age 30 group by post having avg(salary) 10000;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
1 row in set (0.00 sec)

4.distinct(去重)

# distinct: 去重
- 書寫順序:
 - select
 - distinct
 - from
 - where
 - group by
 - having
執(zhí)行順序:
 - from
 - where 
 - group by 
 - having
 - select 
 - distinct
# 注意: 查詢的字段值必須是重復(fù)的才有效，只要有一個字段值是不重復(fù)的就沒有效果。# 若所查字段內(nèi)有不重復(fù)的字段記錄就不會去重
select distinct id, post from emp;
mysql select distinct id, post from emp;
+----+-----------------------------------+
| id | post |
+----+-----------------------------------+
| 1 | 張江第一帥形象代言部門 |
| 2 | teacher |
| 3 | teacher |
| 4 | teacher |
| 5 | teacher |
| 6 | teacher |
| 7 | teacher |
| 8 | teacher |
| 9 | sale |
| 10 | sale |
| 11 | sale |
| 12 | sale |
| 13 | sale |
| 14 | operation |
| 15 | operation |
| 16 | operation |
| 17 | operation |
| 18 | operation |
| 19 | NULL |
+----+-----------------------------------+
19 rows in set (0.00 sec)

+-----------------------------------+
5 rows in set (0.00 sec)

5.order by(排序)

- 書寫順序:
 - select
 - from
 - where
 - group by
 - having
 - order by
執(zhí)行順序:
 - from
 - where 
 - group by 
 - having 
 - select 
 - order by # 通過 select 查出來的數(shù)據(jù)再進(jìn)行排序
# order by 默認(rèn)升序
# asc 升序
# desc 降序
# 1、根據(jù)薪資進(jìn)行升序
select name, salary from emp order by salary; # 默認(rèn)升序
mysql select name, salary from emp order by salary;
+-----------+------------+
| name | salary |
+-----------+------------+
| o | NULL |
| 丁丁 | 1000.37 |
| 丫丫 | 2000.35 |
| owen | 2100.00 |
| 歪歪 | 3000.13 |
| 星星 | 3000.29 |
| jason | 3500.00 |
| 格格 | 4000.33 |
| tank | 7300.33 |
| kevin | 8300.00 |
| jerry | 9000.00 |
| sean | 10000.00 |
| 張野 | 10000.13 |
| 程咬鐵 | 17000.00 |
| 程咬銅 | 18000.00 |
| 程咬銀 | 19000.00 |
| 程咬金 | 20000.00 |
| 大餅 | 30000.00 |
| egon | 1000000.31 |
+-----------+------------+
19 rows in set (0.00 sec)

# 先按照 age 升序，再按照 salary 降序
select age, salary from emp order by age asc, salary desc;

# 統(tǒng)計 各部門 (分組) 年齡在 10 歲以上的員工平均工資，并且保留平均工資大于 1000 的部門，然后對平均工資進(jìn)行升序序
select post, avg(salary) from emp where age 10 group by post having avg(salary) 1000 order by avg(salary);

9.limit(限制查詢記錄的數(shù)量)

 書寫順序:
 - select
 - from
 - order by
 - limit
 執(zhí)行順序:
 - from
 - select
 - order by
 - limit

mysql select * from emp limit 4;
+----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+
| 1 | tank | male | 17 | 2017-03-01 | 張江第一帥形象代言部門 | NULL | 7300.33 | 401 | 1 |
| 2 | egon | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | jason | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
+----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+
4 rows in set (0.00 sec)

# 2、limit 可以有兩個參數(shù), 參數(shù) 1: 是限制的開始位置, 參數(shù) 2: 是從開始位置展示的條數(shù);
select * from emp limit 0, 4;
select * from emp limit 4, 4;

mysql select * from emp limit 4, 4;
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | 大餅 | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | sean | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
4 rows in set (0.00 sec)
# 3、查詢工資最高的人的詳細(xì)信息
select * from emp order by salary limit 1;

mysql select * from emp order by salary limit 1;
+----+------+------+-----+------------+------+--------------+--------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+------+--------------+--------+--------+-----------+
| 19 | o | male | 28 | 0000-00-00 | NULL | NULL | NULL | NULL | NULL |
+----+------+------+-----+------------+------+--------------+--------+--------+-----------+
1 row in set (0.00 sec)

10. 正則

# 在編程中，凡是看到 reg 開頭的，基本上都是跟正則有關(guān)
 # *: 代表 0 或 多個
select * from emp where name regexp ^ 程.*(金 | 銀 | 銅 | 鐵)$ 
mysql select * from emp where name regexp ^ 程.*(金 | 銀 | 銅 | 鐵)$ 
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬銀 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬銅 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬鐵 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
4 rows in set (0.00 sec)

三. 多表查詢

多表查詢

- 關(guān)聯(lián)查詢

- 子查詢

創(chuàng)建表與插入數(shù)據(jù)準(zhǔn)備

# 建表
create table dep2(
id int,
name varchar(20) 
create table emp2(
id int primary key auto_increment,
name varchar(20),
sex enum(male , female) not null default male ,
age int,
dep_id int
#插入數(shù)據(jù)
insert into dep2 values
(200, 技術(shù)),
(201, 人力資源),
(202, 銷售),
(203, 運營 
insert into emp2(name,sex,age,dep_id) values
(tank , male ,17,200),
(egon , female ,48,201),
(kevin , male ,38,201),
(jason , female ,28,202),
(owen , male ,18,200),
(sean , female ,18,204);

# 指到了如何根據(jù)表關(guān)系對字段進(jìn)行拆分，目的是為了更好的管理，表數(shù)據(jù)都存放在硬盤中，存不是目的，目的是為了取，所以我們將數(shù)據(jù)從硬盤讀到內(nèi)存中，接下來我們因應(yīng)該將他們拼成一張表來查詢更加合理;
# 注意: 將拆分的表，再拼接到一起進(jìn)行查詢, 可以通過一張表查另一張表的數(shù)據(jù);

1. 關(guān)聯(lián)查詢

# 左表的一條記錄與右表的一條記錄都對應(yīng)一遍稱之為 -- 笛卡爾積 PS: 百度科普
# 將所有的數(shù)據(jù)都對應(yīng)了一遍，雖然不合理但是其中有合理的數(shù)據(jù)，現(xiàn)在我們需要做的就是找出合理的數(shù)據(jù) 

 舉例: 通過之前準(zhǔn)備的數(shù)據(jù)進(jìn)行多表操作
1、查詢 員工 以及所在 部門 的信息;
# 通過 where 約束條件
select * from emp2, dep2 where emp2.dep_id = dep2.id;
2、查詢 部門 為 技術(shù)部 的 員工 及 部門信息
select * from emp2, dep2 where emp2.dep_id = dep2.id and dep2.name = 技術(shù) 

 聯(lián)表查詢:
 - 內(nèi)鏈接: 只去兩張表有對應(yīng)關(guān)系的記錄
select * from emp2 inner join dep2 on emp2.dep_id = dep2.id;
mysql select * from emp2 inner join dep2 on emp2.dep_id = dep2.id;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | tank | male | 17 | 200 | 200 | 技術(shù) |
| 2 | egon | female | 48 | 201 | 201 | 人力資源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力資源 |
| 4 | jason | female | 28 | 202 | 202 | 銷售 |
| 5 | owen | male | 18 | 200 | 200 | 技術(shù) |
+----+-------+--------+------+--------+------+--------------+
5 rows in set (0.05 sec)
2、left join
# 2、左連接: 在內(nèi)連接的基礎(chǔ)上保留左表沒有對應(yīng)關(guān)系的記錄 (以 null 補全)
select * from emp2 left join dep2 on emp2.dep_id = dep2.id;
mysql select * from emp2 left join dep2 on emp2.dep_id = dep2.id;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | tank | male | 17 | 200 | 200 | 技術(shù) |
| 5 | owen | male | 18 | 200 | 200 | 技術(shù) |
| 2 | egon | female | 48 | 201 | 201 | 人力資源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力資源 |
| 4 | jason | female | 28 | 202 | 202 | 銷售 |
| 6 | sean | female | 18 | 204 | NULL | NULL |
+----+-------+--------+------+--------+------+--------------+

# 3、右連接: 在內(nèi)連接的基礎(chǔ)上保留右表沒有對應(yīng)關(guān)系的記錄
select * from emp2 right join dep2 on emp2.dep_id = dep2.id;

mysql select * from emp2 right join dep2 on emp2.dep_id = dep2.id;
+------+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+-------+--------+------+--------+------+--------------+
| 1 | tank | male | 17 | 200 | 200 | 技術(shù) |
| 2 | egon | female | 48 | 201 | 201 | 人力資源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力資源 |
| 4 | jason | female | 28 | 202 | 202 | 銷售 |
| 5 | owen | male | 18 | 200 | 200 | 技術(shù) |
| NULL | NULL | NULL | NULL | NULL | 203 | 運營 |
+------+-------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)

# 4、全連接: 在內(nèi)連接的基礎(chǔ)上 保留左、右表沒有對應(yīng)關(guān)系的記錄
select * from emp2 left join dep2 on emp2.dep_id = dep2.id 
union 
select * from emp2 right join dep2 on emp2.dep_id = dep2.id;

2. 子查詢

# 子查詢就是將一個查詢語句的結(jié)果用括號括起來，當(dāng)做另一個查詢語句的條件去用
# 查詢部門是技術(shù)或者人力資源的員工信息
先獲取技術(shù)部和人力資源的 id 號，再去員工表里根據(jù)前面的 id 篩選出符合要求的員工信息;
select * from emp2 where dep_id in (select id from dep2 where name= 技術(shù) or name= 人力資源 
 # 2. 每個部門最新入職的員工 思路：先查每個部門最新入職的員工，再按部門對應(yīng)上聯(lián)表查詢
 # 查第一張 emp 表
# 第四步
select t1.id, t1.name, t1.hire_date, t2.*
# 第二步
# 拼接了 t1（emp）與 t2（各部門最新入職的員工數(shù)據(jù)虛擬表）表的數(shù)據(jù)
emp as t1
inner join
# 第一步
# 子查詢獲取 emp 表中的 部門名稱與最新入職的時間字段值 生成一張?zhí)摂M表
(select post, max(hire_date) as max_date from emp group by post) as t2
# 若 t1.post 的值與 t2.post 條件成立，則拼接對應(yīng)的記錄;
t1.post = t2.post
# 第三步
where t1.hire_date = t2.max_date;

感謝各位的閱讀！關(guān)于 MySQL 之查詢語句的基本操作是什么就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！

向 AI 問一下細(xì)節(jié)

丸趣 TV 網(wǎng) – 提供最優(yōu)質(zhì)的資源集合！