MySQL單表查詢操作實例詳解【語法、約束、分組、聚合、過濾、排序等】
更新時間：2019年12月19日 11:15:58 作者：dawn-liu
這篇文章主要介紹了MySQL單表查詢操作,結合實例形式詳細分析了mysql單表查詢的語法、約束、分組、聚合、過濾、排序等相關原理、操作技巧與注意事項,需要的朋友可以參考下
本文實例講述了MySQL單表查詢操作。分享給大家供大家參考，具體如下：
語法
一、單表查詢的語法
SELECT 字段1,字段2... FROM 表名
WHERE 條件
GROUP BY field
HAVING 篩選
ORDER BY field
LIMIT 限制條數(shù)
二、關鍵字的執(zhí)行優(yōu)先級（重點）
重點中的重點：關鍵字的執(zhí)行優(yōu)先級
from
where
group by
having
select
distinct
order by
limit
1.找到表:from
2.拿著where指定的約束條件，去文件/表中取出一條條記錄
3.將取出的一條條記錄進行分組group by，如果沒有group by，則整體作為一組
4.將分組的結果進行having過濾
5.執(zhí)行select
6.去重
7.將結果按條件排序：order by
8.限制結果的顯示條數(shù)
（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、首先明確一點：分組發(fā)生在where之后，即分組是基于where之后得到的記錄而進行的
#2、分組指的是：將所有記錄按照某個相同字段進行歸類，比如針對員工信息表的職位分組，或者按照性別進行分組等
#3、為何要分組呢？
取每個部門的最高工資
取每個部門的員工數(shù)
取男人數(shù)和女人數(shù)小竅門：‘每'這個字后面的字段，就是我們分組的依據(jù)
#4、大前提：
可以按照任意字段分組，但是分組完畢后，比如group by post，只能查看post字段，如果想查看組內(nèi)信息，需要借助于聚合函數(shù)
當執(zhí)行以下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;//現(xiàn)在的情況下就會報錯 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() 求總個數(shù)
#強調(diào)：聚合函數(shù)聚合的是組的內(nèi)容，若是沒有分組，則默認一組 # 每個部門有多少個員工 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不一樣的地方在于
#！??！執(zhí)行優(yōu)先級從高到低：where > group by > having
#1. Where 發(fā)生在分組group by之前，因而Where中可以有任意字段，但是絕對不能使用聚合函數(shù)。#2. Having發(fā)生在分組group by之后，因而Having中可以使用分組的字段，無法直接取到其他字段,可以使用聚合函數(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; ##錯誤，分組后無法直接取到salary字段 ERROR 1054 (42S22): Unknown column 'post' in 'field list'
練習
1. 查詢各崗位內(nèi)包含的員工個數(shù)小于2的崗位名、崗位內(nèi)包含員工名字、個數(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升序排序，如果年紀相同，則按照id降序
SELECT * from employee
ORDER BY age ASC,
id DESC;
（5）limit 限制查詢的記錄數(shù):
示例：
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; #默認初始位置為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條
練習：每次顯示5條
# 第1頁數(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頁數(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頁數(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)
更多關于MySQL相關內(nèi)容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數(shù)據(jù)庫鎖相關技巧匯總》
希望本文所述對大家MySQL數(shù)據(jù)庫計有所幫助。
相關文章
mysql 搜尋附近N公里內(nèi)數(shù)據(jù)的簡單實例
下面小編就為大家?guī)硪黄猰ysql 搜尋附近N公里內(nèi)數(shù)據(jù)的簡單實例。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-04-04
MySQL 9.0作為MySQL數(shù)據(jù)庫管理系統(tǒng)的一個重要更新版本,帶來了多項新特性和改進,下面我們就一起來看看更新了哪些特性,感興趣的可以了解一下2024-08-08
今天，數(shù)據(jù)庫的操作越來越成為整個應用的性能瓶頸了，這點對于Web應用尤其明顯。關于數(shù)據(jù)庫的性能，這并不只是DBA才需要擔心的事，而這更是我們程序員需要去關注的事情2016-04-04
mysql 查詢當天、本周,本月,上一個月的數(shù)據(jù)
這篇文章主要介紹了mysql 查詢當天、本周,本月,上一個月的數(shù)據(jù)的sql代碼，在文中還給大家提到了mysql如何查詢當天信息，具體內(nèi)容詳情大家參考下本文2018-01-01
這篇文章主要介紹了MySQL查看、創(chuàng)建和刪除索引的方法,結合實例形式較為詳細的分析了MySQL中索引的作用,以及查看、創(chuàng)建及刪除索引的相關實現(xiàn)技巧,具有一定參考借鑒價值,需要的朋友可以參考下2015-10-10
這篇文章主要介紹了MySQL聯(lián)合索引功能與用法,結合具體實例形式分析了聯(lián)合索引的概念、功能、具體使用方法與相關注意事項,需要的朋友可以參考下2017-09-09
本文主要介紹了MySQL跨服務器關聯(lián)查詢的實現(xiàn)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-01-01
MYSQL多表聯(lián)查on和where的區(qū)別小結
在使用left join時過濾條件放到on后面還是where后面是有區(qū)別的,如果沒有搞清楚他們的區(qū)別,連表匯總的結果就會變少或者變多,本文就來詳細介紹一下兩者的區(qū)別,感興趣的可以了解一下2023-11-11
第一次做完主從庫同步后正常，但工作過程中發(fā)現(xiàn)有一個庫的數(shù)據(jù)庫沒有同步起來，在另外一個mysql（3307）中2010-06-06
首先把mysql的服務先停掉，更改MySQL配置文件My.ini中的數(shù)據(jù)庫存儲主路徑，將老的數(shù)據(jù)庫存儲主路徑中的數(shù)據(jù)庫文件和文件夾復制到新的存儲主路徑，接下來重啟搞定2013-07-07