在线免费av观看,亚洲成色777777在线观看影院

主頁 > 知識(shí)庫 > MySQL單表查詢操作實(shí)例詳解【語法、約束、分組、聚合、過濾、排序等】

MySQL單表查詢操作實(shí)例詳解【語法、約束、分組、聚合、過濾、排序等】

本文實(shí)例講述了MySQL單表查詢操作。分享給大家供大家參考，具體如下：

語法

一、單表查詢的語法

   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，如果沒有g(shù)roup by，則整體作為一組

4.將分組的結(jié)果進(jìn)行having過濾

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)行歸類，比如針對員工信息表的職位分組，或者按照性別進(jìn)行分組等

#3、為何要分組呢？
    取每個(gè)部門的最高工資
    取每個(gè)部門的員工數(shù)
    取男人數(shù)和女人數(shù)

小竅門：‘每'這個(gè)字后面的字段，就是我們分組的依據(jù)

#4、大前提：

可以按照任意字段分組，但是分組完畢后，比如group by post，只能查看post字段，如果想查看組內(nèi)信息，需要借助于聚合函數(shù)

當(dāng)執(zhí)行以下sql語句的時(shí)候，沒有報(bào)錯(cuò)，但本身是沒有意義的

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)容，若是沒有分組，則默認(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過濾

HAVING與WHERE不一樣的地方在于

#！?。?zhí)行優(yōu)先級(jí)從高到低：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; ##錯(cuò)誤，分組后無法直接取到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頁數(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)

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過程技巧大全》及《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》

希望本文所述對大家MySQL數(shù)據(jù)庫計(jì)有所幫助。

您可能感興趣的文章:

MySQL中聚合函數(shù)count的使用和性能優(yōu)化技巧
MySQL常用聚合函數(shù)詳解
MySql 中聚合函數(shù)增加條件表達(dá)式的方法
php+mysql開源XNA 聚合程序發(fā)布下載
Mysql無法選取非聚合列的解決方法
MySQL查詢排序與查詢聚合函數(shù)用法分析
MySQL使用聚合函數(shù)進(jìn)行單表查詢
MySQL 分組查詢和聚合函數(shù)
mysql連續(xù)聚合原理與用法實(shí)例分析
mysql聚合統(tǒng)計(jì)數(shù)據(jù)查詢緩慢的優(yōu)化方法

標(biāo)簽：河北來賓陽江鞍山果洛赤峰黃石煙臺(tái)

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL單表查詢操作實(shí)例詳解【語法、約束、分組、聚合、過濾、排序等】》，本文關(guān)鍵詞 MySQL,單表,查詢,操作,實(shí)例,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。