溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章給大家分享的是有關MySQL之查詢語句的基本操作是什么的內容。小編覺得挺實用的，因此分享給大家做個參考。一起跟隨小編過來看看吧。
1.查詢語句的基本操作
- select
- from
- where:約束條件
- group by:分組
- having：過濾
- distinct：去重
- order by：排序
- limit：限制查詢記錄的數量
- 聚合函數: count（計數）
max（最大值）
min（最小值）
avg（平均值）
sum(求和)# 創建一張部門表
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
);
# 插入記錄
# 三個部門：教學，銷售，運營
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('tank','male',17,'20170301','張江第一帥形象代言部門',7300.33,401,1), # 以下是教學部
('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系統中，插入中文字符，select的結果為空白，可以將所有字符編碼統一設置成gbk
- select * from emp; # 若數據比較多，比較凌亂，可以在表后面+ \G
- select * from emp\G- select * from emp; # 若數據比較多，比較凌亂，可以在表后面+ \G - select * from emp\G
寫SQL語句必須遵循兩點:
- 書寫順序:
# 獲取id為 4、5的兩條記錄
select * from emp where id > 3 and id < 6;
- select
- from
- where
- 執行順序:
比如: 圖書管理員，得先找到是哪一個圖書館(哪張表),
再找這本書在圖書館的哪個位置(哪一條記錄), 最后查找這個本書中某一頁(哪些字段值);
select * from emp where id > 3 and id < 6;
- from ---> 找到圖書館
- where ---> 找到書的位置
- select ---> 找到書本中的某一頁
注意: 必須記住SQL語句的 書寫順序 與 執行順序(*******);# PS: 根據執行順序來書寫 SQL語句，一步一步來寫;
# 1.查詢id大于等于3小于等于6的數據(and:與)
select * from emp where id >= 3 and id <= 6;
mysql> SELECT * FROM EMP WHERE ID > 3 AND ID <= 6;
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| 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)
#可以使用between()and() :兩者之間
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的數據
# 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)
# 3.查詢員工姓名中包含o字母 的 員工姓名和薪資
# 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)
mysql> select name, salary from emp where name like "o";
+------+--------+
| name | salary |
+------+--------+
| o | NULL |
+------+--------+
1 row in set (0.00 sec)
# 4.查找名字個數為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)
# 5.查詢id小于3或者大于6的數據
# not in: 不再什么什么中
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范圍的數據
select * from emp where salary not in (20000, 18000, 17000);
# 7.查詢崗位描述為空的 員工名 與 崗位名 post_comment
# 用等于號無法判定空字段
select name, post from emp where post_comment = null;
select * from emp where post_comment = null;
Empty set (0.00 sec)
# 注意: 針對null的值 需要使用 is
select name, post from emp where post_comment is null;
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) - 書寫順序:
- select
- from
- where
- group by
- 執行順序:
- from
- where
- group by
- select
# 什么叫分組
# 比如: 一張員工表中有性別字段，可以根據性別分組，一組是男性，一組是女性，或者是根據部門分組，有教學部、銷售部等...
# 1.根據部門分組
# 非嚴格模式下可以獲取 分組條件post 以外的字段數據
select post, salary from emp group by post;
# 設置嚴格模式:
show variables like "%mode%";
# 全局設置: 永久有效
set global sql_mode="strict_trans_tables,only_full_group_by";
"""
設置sql_mode為only_full_group_by，意味著以后但凡分組，只能取到分組的依據，
不應該在去取組里面的單個元素的值，那樣的話分組就沒有意義了，并且會報錯,因為不分組就是對單個元素信息的隨意獲取
"""
mysql> select post, salary from emp group by post;
ERROR 1046 (3D000): No database selected
# 可以同聚合函數，間接獲取其他字段數據
聚合函數:
count: 計數
max: 最大值
min: 最小值
avg: 平均值
sum: 求和
group_concat(name): 可以將分組后的 所有名字獲取并進行拼接
# 指定以:拼接
select post, group_concat(name) from emp group by post;
select post, group_concat(name, ':') from emp group by post;
2.獲取每個 部門 的最高工資
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)
3.每個部門的最低工資
select post, min(salary) from emp group by post;
4.每個部門的平均工資
select post, avg(salary) from emp group by post;
5.每個部門的工資總和
select post, sum(salary) from emp group by post;
6.每個部門的員工個數
# count(): 括號中可以填任意非空值
select post, count(salary) from emp group by post;
select post, count(post_comment) from emp group by post;
查詢崗位名以及各崗位內包含的員工個數
select post, count(id) from emp group by post;
查詢公司內男員工和女員工的個數
select sex, count(*) from emp group by sex;
查詢男員工與男員工的平均薪資，女員工與女員工的平均薪資
select sex, avg(salary) from emp group by sex;
.統計各部門年齡在30歲以上的員工平均工資:
# 步驟: 先找到表，再找年齡30歲以上，再根據部門分組，最后求平均薪資;
select post, avg(salary) from emp where age > 30 group by post;總結: 聚合函數，必須跟在group by 后面(執行順序);
# 聚合函數: 若沒有group by 分組，默認將查出來的數據當做一個分組, 也能使用; select max(salary) from emp;
# group_concat(name): 可以將分組后的 所有名字獲取并進行拼接
# 默認以, 拼接
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;
select post, group_coucat(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（不分組時用）拼接字符串達到更好的顯示效果 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)1.having與where語法一樣，只不過having必需要在group by后使用;
2.where 不能使用聚合函數，但having可以;
- 書寫順序:
- select
- from
- where
- group by
- having
- 執行順序:
- from
- where
- group by
- having
- select
1、統計各 部門 年齡在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)# distinct: 去重
- 書寫順序:
- select
- distinct
- from
- where
- group by
- having
執行順序:
- from
- where
- group by
- having
- select
- distinct
# 注意: 查詢的字段值必須是重復的才有效，只要有一個字段值是不重復的就沒有效果。
# 若所查字段內有不重復的字段記錄就不會去重
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)
select distinct post from emp;
mysql> select distinct post from emp;
+-----------------------------------+
| post |
+-----------------------------------+
| 張江第一帥形象代言部門 |
| teacher |
| sale |
| operation |
| NULL |
+-----------------------------------+
5 rows in set (0.00 sec)- 書寫順序:
- select
- from
- where
- group by
- having
- order by
執行順序:
- from
- where
- group by
- having
- select
- order by # 通過select 查出來的數據再進行排序
# order by 默認升序
# asc升序
# desc降序
# 1、根據薪資進行升序
select name, salary from emp order by salary; # 默認升序
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)
2.select * from emp order by salary asc; # 指定升序
3.select name, salary from emp order by salary desc; # 指定降序
mysql> select name, salary from emp order by salary desc;
+-----------+------------+
| name | salary |
+-----------+------------+
| egon | 1000000.31 |
| 大餅 | 30000.00 |
| 程咬金 | 20000.00 |
| 程咬銀 | 19000.00 |
| 程咬銅 | 18000.00 |
| 程咬鐵 | 17000.00 |
| 張野 | 10000.13 |
| sean | 10000.00 |
| jerry | 9000.00 |
| kevin | 8300.00 |
| tank | 7300.33 |
| 格格 | 4000.33 |
| jason | 3500.00 |
| 星星 | 3000.29 |
| 歪歪 | 3000.13 |
| owen | 2100.00 |
| 丫丫 | 2000.35 |
| 丁丁 | 1000.37 |
| o | NULL |
+-----------+------------+
19 rows in set (0.00 sec)
# 先按照age升序，再按照salary降序
select age, salary from emp order by age asc, salary desc;
# 統計 各部門(分組) 年齡在10歲以上的員工平均工資，并且保留平均工資大于1000的部門，然后對平均工資進行升序序
select post, avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary); 書寫順序:
- select
- from
- order by
- limit
執行順序:
- from
- select
- order by
- limit
# 1、從第一條開始，獲取4條記錄;
select * from emp limit 4;
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可以有兩個參數, 參數1:是限制的開始位置, 參數2:是從開始位置展示的條數;
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、查詢工資最高的人的詳細信息
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)# 在編程中，凡是看到reg開頭的，基本上都是跟正則有關 # *: 代表 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)
多表查詢
-關聯查詢
-子查詢
創建表與插入數據準備
#建表
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
);
#插入數據
insert into dep2 values
(200,'技術'),
(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);
mysql> select * from dep2;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技術 |
| 201 | 人力資源 |
| 202 | 銷售 |
| 203 | 運營 |
+------+--------------+
4 rows in set (0.00 sec)
mysql> select * from emp2;
+----+-------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+-------+--------+------+--------+
| 1 | tank | male | 17 | 200 |
| 2 | egon | female | 48 | 201 |
| 3 | kevin | male | 38 | 201 |
| 4 | jason | female | 28 | 202 |
| 5 | owen | male | 18 | 200 |
| 6 | sean | female | 18 | 204 |
+----+-------+--------+------+--------+
# 指到了如何根據表關系對字段進行拆分，目的是為了更好的管理，表數據都存放在硬盤中，存不是目的，目的是為了取，所以我們將數據從硬盤讀到內存中，接下來我們因應該將他們拼成一張表來查詢更加合理;
# 注意: 將拆分的表，再拼接到一起進行查詢, 可以通過一張表查另一張表的數據;# 左表的一條記錄與右表的一條記錄都對應一遍稱之為 --> "笛卡爾積" PS: 百度科普 # 將所有的數據都對應了一遍，雖然不合理但是其中有合理的數據，現在我們需要做的就是找出合理的數據
舉例:通過之前準備的數據進行多表操作
1、查詢 員工 以及所在 部門 的信息;
# 通過where 約束條件
select * from emp2, dep2 where emp2.dep_id = dep2.id;
2、查詢 部門 為 技術部 的 員工 及 部門信息
select * from emp2, dep2 where emp2.dep_id = dep2.id and dep2.name = '技術';聯表查詢: -內鏈接:只去兩張表有對應關系的記錄 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 | 技術 | | 2 | egon | female | 48 | 201 | 201 | 人力資源 | | 3 | kevin | male | 38 | 201 | 201 | 人力資源 | | 4 | jason | female | 28 | 202 | 202 | 銷售 | | 5 | owen | male | 18 | 200 | 200 | 技術 | +----+-------+--------+------+--------+------+--------------+ 5 rows in set (0.05 sec) 2、left join # 2、左連接: 在內連接的基礎上保留左表沒有對應關系的記錄(以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 | 技術 | | 5 | owen | male | 18 | 200 | 200 | 技術 | | 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、右連接: 在內連接的基礎上保留右表沒有對應關系的記錄 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 | 技術 | | 2 | egon | female | 48 | 201 | 201 | 人力資源 | | 3 | kevin | male | 38 | 201 | 201 | 人力資源 | | 4 | jason | female | 28 | 202 | 202 | 銷售 | | 5 | owen | male | 18 | 200 | 200 | 技術 | | NULL | NULL | NULL | NULL | NULL | 203 | 運營 | +------+-------+--------+------+--------+------+--------------+ 6 rows in set (0.00 sec) - 全連接: union # 4、全連接: 在內連接的基礎上 保留左、右表沒有對應關系的記錄 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;
# 子查詢就是將一個查詢語句的結果用括號括起來，當做另一個查詢語句的條件去用 # 查詢部門是技術或者人力資源的員工信息 ''' 先獲取技術部和人力資源的id號，再去員工表里根據前面的id篩選出符合要求的員工信息; ''' select * from emp2 where dep_id in (select id from dep2 where name='技術' or name='人力資源'); # 2.每個部門最新入職的員工 思路：先查每個部門最新入職的員工，再按部門對應上聯表查詢 # 查第一張emp表 # 第四步 select t1.id, t1.name, t1.hire_date, t2.* from # 第二步 # 拼接了 t1（emp） 與 t2（各部門最新入職的員工數據虛擬表）表的數據 emp as t1 inner join # 第一步 # 子查詢獲取emp表中的 部門名稱與最新入職的時間字段值 生成一張虛擬表 (select post, max(hire_date) as max_date from emp group by post) as t2 on # 若 t1.post 的值與 t2.post 條件成立，則拼接對應的記錄; t1.post = t2.post # 第三步 where t1.hire_date = t2.max_date;
感謝各位的閱讀！關于MySQL之查詢語句的基本操作是什么就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
