本文實(shí)例講述了MySQL單表查詢操作。分享給大家供大家參考，具體如下：
語(yǔ)法
一、單表查詢的語(yǔ)法
SELECT 字段1,字段2... FROM 表名
WHERE 條件
GROUP BY field
HAVING 篩選
ORDER BY field
LIMIT 限制條數(shù)
二、關(guān)鍵字的執(zhí)行優(yōu)先級(jí)（重點(diǎn)）
重點(diǎn)中的重點(diǎn)：關(guān)鍵字的執(zhí)行優(yōu)先級(jí)
from
where
group by
having
select
distinct
order by
limit
1.找到表:from
2.拿著where指定的約束條件，去文件/表中取出一條條記錄
3.將取出的一條條記錄進(jìn)行分組group by，如果沒(méi)有g(shù)roup by，則整體作為一組
4.將分組的結(jié)果進(jìn)行having過(guò)濾
5.執(zhí)行select
6.去重
7.將結(jié)果按條件排序：order by
8.限制結(jié)果的顯示條數(shù)
（1）where 約束
where運(yùn)算符
where子句中可以使用
1.比較運(yùn)算符：>、、>=、=、>、!=
2.between 80 and 100 ：值在80到100之間
3.in(80,90,100)值是10或20或30
4.like 'xiaomagepattern': pattern可以是%或者_(dá)。%小時(shí)任意多字符，_表示一個(gè)字符
5.邏輯運(yùn)算符：在多個(gè)條件直接可以使用邏輯運(yùn)算符 and or not
（2）group by 分組查詢
#1、首先明確一點(diǎn)：分組發(fā)生在where之后，即分組是基于where之后得到的記錄而進(jìn)行的
#2、分組指的是：將所有記錄按照某個(gè)相同字段進(jìn)行歸類，比如針對(duì)員工信息表的職位分組，或者按照性別進(jìn)行分組等
#3、為何要分組呢？
取每個(gè)部門的最高工資
取每個(gè)部門的員工數(shù)
取男人數(shù)和女人數(shù)
小竅門：‘每'這個(gè)字后面的字段，就是我們分組的依據(jù)
#4、大前提：
可以按照任意字段分組，但是分組完畢后，比如group by post，只能查看post字段，如果想查看組內(nèi)信息，需要借助于聚合函數(shù)
當(dāng)執(zhí)行以下sql語(yǔ)句的時(shí)候，沒(méi)有報(bào)錯(cuò)，但本身是沒(méi)有意義的
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)
設(shè)置sql_mode為ONLY_FULL_GROUP_BY，并且退出，再進(jìn)入才會(huì)生效
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)
再次進(jìn)入
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;//現(xiàn)在的情況下就會(huì)報(bào)錯(cuò)
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）聚合函數(shù)
max()求最大值
min()求最小值
avg()求平均值
sum() 求和
count() 求總個(gè)數(shù)
#強(qiáng)調(diào)：聚合函數(shù)聚合的是組的內(nèi)容，若是沒(méi)有分組，則默認(rèn)一組
# 每個(gè)部門有多少個(gè)員工
select post,count(id) from employee group by post;
# 每個(gè)部門的最高薪水
select post,max(salary) from employee group by post;
# 每個(gè)部門的最低薪水
select post,min(salary) from employee group by post;
# 每個(gè)部門的平均薪水
select post,avg(salary) from employee group by post;
# 每個(gè)部門的所有薪水
select post,sum(age) from employee group by post;
（4）HAVING過(guò)濾
HAVING與WHERE不一樣的地方在于
#！??！執(zhí)行優(yōu)先級(jí)從高到低：where > group by > having
#1. Where 發(fā)生在分組group by之前，因而Where中可以有任意字段，但是絕對(duì)不能使用聚合函數(shù)。
#2. Having發(fā)生在分組group by之后，因而Having中可以使用分組的字段，無(wú)法直接取到其他字段,可以使用聚合函數(shù)
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()函數(shù)，將所有的name值連接
mysql> select post,group_concat(name) from emp group by post having salary > 10000; ##錯(cuò)誤，分組后無(wú)法直接取到salary字段
ERROR 1054 (42S22): Unknown column 'post' in 'field list'
練習(xí)
1. 查詢各崗位內(nèi)包含的員工個(gè)數(shù)小于2的崗位名、崗位內(nèi)包含員工名字、個(gè)數(shù)
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升序排序，如果年紀(jì)相同，則按照id降序
SELECT * from employee
ORDER BY age ASC,
id DESC;
（5）limit 限制查詢的記錄數(shù):
示例：
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; #默認(rèn)初始位置為0
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; #從第0開始，即先查詢出第一條，然后包含這一條在內(nèi)往后查5條
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; #從第5開始，即先查詢出第6條，然后包含這一條在內(nèi)往后查5條
練習(xí)：每次顯示5條
# 第1頁(yè)數(shù)據(jù)
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頁(yè)數(shù)據(jù)
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頁(yè)數(shù)據(jù)
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)
更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》
希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。
您可能感興趣的文章:- MySQL中聚合函數(shù)count的使用和性能優(yōu)化技巧
- MySQL常用聚合函數(shù)詳解
- MySql 中聚合函數(shù)增加條件表達(dá)式的方法
- php+mysql開源XNA 聚合程序發(fā)布 下載
- Mysql無(wú)法選取非聚合列的解決方法
- MySQL查詢排序與查詢聚合函數(shù)用法分析
- MySQL使用聚合函數(shù)進(jìn)行單表查詢
- MySQL 分組查詢和聚合函數(shù)
- mysql連續(xù)聚合原理與用法實(shí)例分析
- mysql聚合統(tǒng)計(jì)數(shù)據(jù)查詢緩慢的優(yōu)化方法
