本文實例講述了MySQL單表查詢操作。分享給大家供大家參考，具體如下：
語法
一、單表查詢的語法
SELECT 字段1,字段2... FROM 表名
WHERE 條件
GROUP BY field
HAVING 篩選
ORDER BY field
LIMIT 限制條數
二、關鍵字的執行優先級（重點）
重點中的重點：關鍵字的執行優先級
from
where
group by
having
select
distinct
order by
limit
1.找到表:from
2.拿著where指定的約束條件，去文件/表中取出一條條記錄
3.將取出的一條條記錄進行分組group by，如果沒有group by，則整體作為一組
4.將分組的結果進行having過濾
5.執行select
6.去重
7.將結果按條件排序：order by
8.限制結果的顯示條數
（1）where 約束
where運算符
where子句中可以使用
1.比較運算符：>、、>=、=、>、!=
2.between 80 and 100 ：值在80到100之間
3.in(80,90,100)值是10或20或30
4.like 'xiaomagepattern': pattern可以是%或者_。%小時任意多字符，_表示一個字符
5.邏輯運算符：在多個條件直接可以使用邏輯運算符 and or not
（2）group by 分組查詢
#1、首先明確一點：分組發生在where之后，即分組是基于where之后得到的記錄而進行的
#2、分組指的是：將所有記錄按照某個相同字段進行歸類，比如針對員工信息表的職位分組，或者按照性別進行分組等
#3、為何要分組呢？
取每個部門的最高工資
取每個部門的員工數
取男人數和女人數
小竅門：‘每'這個字后面的字段，就是我們分組的依據
#4、大前提：
可以按照任意字段分組，但是分組完畢后，比如group by post，只能查看post字段，如果想查看組內信息，需要借助于聚合函數
當執行以下sql語句的時候，沒有報錯，但本身是沒有意義的
mysql> select * from employee group by post;
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 14 | 張野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
| 1 | egon | male | 18 | 2017-03-01 | 老男孩駐沙河辦事處外交大使 | NULL | 7300.33 | 401 | 1 |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
4 rows in set (0.00 sec)
設置sql_mode為ONLY_FULL_GROUP_BY，并且退出，再進入才會生效
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
再次進入
mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------
mysql> select * from emp group by post;//現在的情況下就會報錯
ERROR 1054 (42S22): Unknown column 'post' in 'group statement'
mysql> select * from employee group by post;
ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
mysql> select post from employee group by post;
+-----------------------------------------+
| post |
+-----------------------------------------+
| operation |
| sale |
| teacher |
| 老男孩駐沙河辦事處外交大使 |
+-----------------------------------------+
4 rows in set (0.00 sec)
或者如下使用
mysql> select name,post from employee group by post,name;
+------------+-----------------------------------------+
| name | post |
+------------+-----------------------------------------+
| 張野 | operation |
| 程咬金 | operation |
| 程咬鐵 | operation |
| 程咬銅 | operation |
| 程咬銀 | operation |
| 丁丁 | sale |
| 丫丫 | sale |
| 星星 | sale |
| 格格 | sale |
| 歪歪 | sale |
| alex | teacher |
| jingliyang | teacher |
| jinxin | teacher |
| liwenzhou | teacher |
| wupeiqi | teacher |
| xiaomage | teacher |
| yuanhao | teacher |
| egon | 老男孩駐沙河辦事處外交大使 |
+------------+-----------------------------------------+
18 rows in set (0.00 sec)
mysql> select post,count(id) from employee group by post;
+-----------------------------------------+-----------+
| post | count(id) |
+-----------------------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩駐沙河辦事處外交大使 | 1 |
+-----------------------------------------+-----------+
4 rows in set (0.00 sec)
（3）聚合函數
max()求最大值
min()求最小值
avg()求平均值
sum() 求和
count() 求總個數
#強調：聚合函數聚合的是組的內容，若是沒有分組，則默認一組
# 每個部門有多少個員工
select post,count(id) from employee group by post;
# 每個部門的最高薪水
select post,max(salary) from employee group by post;
# 每個部門的最低薪水
select post,min(salary) from employee group by post;
# 每個部門的平均薪水
select post,avg(salary) from employee group by post;
# 每個部門的所有薪水
select post,sum(age) from employee group by post;
（4）HAVING過濾
HAVING與WHERE不一樣的地方在于
#！！！執行優先級從高到低：where > group by > having
#1. Where 發生在分組group by之前，因而Where中可以有任意字段，但是絕對不能使用聚合函數。
#2. Having發生在分組group by之后，因而Having中可以使用分組的字段，無法直接取到其他字段,可以使用聚合函數
mysql> select * from employee where salary>1000000;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)
mysql> select * from employee having salary>1000000;
ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause
# 必須使用group by才能使用group_concat()函數，將所有的name值連接
mysql> select post,group_concat(name) from emp group by post having salary > 10000; ##錯誤，分組后無法直接取到salary字段
ERROR 1054 (42S22): Unknown column 'post' in 'field list'
練習
1. 查詢各崗位內包含的員工個數小于2的崗位名、崗位內包含員工名字、個數
2. 查詢各崗位平均薪資大于10000的崗位名、平均工資
3. 查詢各崗位平均薪資大于10000且小于20000的崗位名、平均工資
答案
mysql> select post,group_concat(name),count(id) from employee group by post;
+-----------------------------------------+-----------------------------------------------------------+-----------+
| post | group_concat(name) | count(id) |
+-----------------------------------------+-----------------------------------------------------------+-----------+
| operation | 程咬鐵,程咬銅,程咬銀,程咬金,張野 | 5 |
| sale | 格格,星星,丁丁,丫丫,歪歪 | 5 |
| teacher | xiaomage,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | 7 |
| 老男孩駐沙河辦事處外交大使 | egon | 1 |
+-----------------------------------------+-----------------------------------------------------------+-----------+
4 rows in set (0.00 sec)
mysql> select post,group_concat(name),count(id) from employee group by post having count(id)2;
+-----------------------------------------+--------------------+-----------+
| post | group_concat(name) | count(id) |
+-----------------------------------------+--------------------+-----------+
| 老男孩駐沙河辦事處外交大使 | egon | 1 |
+-----------------------------------------+--------------------+-----------+
1 row in set (0.00 sec)
#題2：
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------+---------------+
2 rows in set (0.00 sec)
#題3：
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) 20000;
+-----------+--------------+
| post | avg(salary) |
+-----------+--------------+
| operation | 16800.026000 |
+-----------+--------------+
1 row in set (0.00 sec)
（5）order by 查詢排序
按單列排序
SELECT * FROM employee ORDER BY age;
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age DESC;
按多列排序:先按照age升序排序，如果年紀相同，則按照id降序
SELECT * from employee
ORDER BY age ASC,
id DESC;
（5）limit 限制查詢的記錄數:
示例：
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; #默認初始位置為0
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; #從第0開始，即先查詢出第一條，然后包含這一條在內往后查5條
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; #從第5開始，即先查詢出第6條，然后包含這一條在內往后查5條
練習：每次顯示5條
# 第1頁數據
mysql> select * from employee limit 0,5;
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩駐沙河辦事處外交大使 | NULL | 7300.33 | 401 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
5 rows in set (0.00 sec)
# 第2頁數據
mysql> select * from employee limit 5,5;
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | xiaomage | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)
# 第3頁數據
mysql> select * from employee limit 10,5;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 張野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)
更多關于MySQL相關內容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL常用函數大匯總》、《MySQL日志操作技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數據庫鎖相關技巧匯總》
希望本文所述對大家MySQL數據庫計有所幫助。
您可能感興趣的文章:- MySQL中聚合函數count的使用和性能優化技巧
- MySQL常用聚合函數詳解
- MySql 中聚合函數增加條件表達式的方法
- php+mysql開源XNA 聚合程序發布 下載
- Mysql無法選取非聚合列的解決方法
- MySQL查詢排序與查詢聚合函數用法分析
- MySQL使用聚合函數進行單表查詢
- MySQL 分組查詢和聚合函數
- mysql連續聚合原理與用法實例分析
- mysql聚合統計數據查詢緩慢的優化方法
