MySQL 單表查詢

雲崖先生發表於2020-09-01

準備資料

　　以下操作將在該表中進行

create table student (
        id int unsigned primary key auto_increment,
        name char(12) not null,
        gender enum("male","famale") default "male",
        age tinyint unsigned not null,
        hoc_group char(12) not null,
        html tinyint unsigned not null,
        css tinyint unsigned not null,
        js tinyint unsigned not null,
        sanction enum("大處分","小處分","無")
);

insert into student(name,gender,age,hoc_group,html,css,js,sanction) values
        ("Yunya","male",18,"first",88,93,76,"無"),
        ("Jack","male",17,"second",92,81,88,"無"),
        ("Bella","famale",17,"first",72,68,91,"小處分"),
        ("Dairis","famale",18,"third",89,54,43,"大處分"),
        ("Kyle","famale",19,"fifth",31,24,60,"大處分"),
        ("Alice","famale",16,"second",49,23,58,"無"),
        ("Ken","male",16,"third",33,62,17,"大處分"),
        ("Jason","male",21,"fourth",91,92,90,"無"),
        ("Tom","male",20,"fifth",88,72,91,"無"),
        ("Fiona","famale",19,"fourth",60,71,45,"無");

查詢語法

SELECT DISTINCT(欄位名1,欄位名2...) FROM 表名
                  WHERE 條件
                  GROUP BY 欄位名
                  HAVING 篩選
                  ORDER BY 欄位名 asc/desc
                  LIMIT 限制條數;

執行順序

　　雖然查詢的書寫語法是上面那樣的，但是其內部執行順序卻有些不太一樣。

　　 1.通過from找到將要查詢的表

　　 2.where規定查詢條件，在表記錄中逐行進行查詢並篩選出符合規則的記錄

　　 3.將查到的記錄進行欄位分組group by，如果沒有進行分組，則預設為一組

　　 4.將分組得到的結果進行having篩選，可使用聚和函式（where時不可使用聚合函式）

　　 5.執行select準備列印

　　 6.執行distinct對列印結果進行去重

　　 7.執行ordery by對結果進行排序

　　 8.執行limit對列印結果的條數進行限制

select

　　 select主要復負責列印相關的工作

全部查詢

　　使用select * from 表名可拿到該表下全部的資料

　　以下示例將展示使用全部查詢拿到student表中所有記錄

 select * from student;
 
+----+--------+--------+-----+-----------+------+-----+----+-----------+
| id | name   | gender | age | hoc_group | html | css | js | sanction  |
+----+--------+--------+-----+-----------+------+-----+----+-----------+
|  1 | Yunya  | male   |  18 | first     |   88 |  93 | 76 | 無        |
|  2 | Jack   | male   |  17 | second    |   92 |  81 | 88 | 無        |
|  3 | Bella  | famale |  17 | first     |   72 |  68 | 91 | 小處分    |
|  4 | Dairis | famale |  18 | third     |   89 |  54 | 43 | 大處分    |
|  5 | Kyle   | famale |  19 | fifth     |   31 |  24 | 60 | 大處分    |
|  6 | Alice  | famale |  16 | second    |   49 |  23 | 58 | 無        |
|  7 | Ken    | male   |  16 | third     |   33 |  62 | 17 | 大處分    |
|  8 | Jason  | male   |  21 | fourth    |   91 |  92 | 90 | 無        |
|  9 | Tom    | male   |  20 | fifth     |   88 |  72 | 91 | 無        |
| 10 | Fiona  | famale |  19 | fourth    |   60 |  71 | 45 | 無        |
+----+--------+--------+-----+-----------+------+-----+----+-----------+

欄位查詢

　　使用select 欄位名1，欄位名2 from 表名可拿到特定欄位下相應的資料

　　以下示例將展示使用欄位查詢拿到每個學生的HTML\CSS\JS成績

select name,html,css,js from student;

+--------+------+-----+----+
| name   | html | css | js |
+--------+------+-----+----+
| Yunya  |   88 |  93 | 76 |
| Jack   |   92 |  81 | 88 |
| Bella  |   72 |  68 | 91 |
| Dairis |   89 |  54 | 43 |
| Kyle   |   31 |  24 | 60 |
| Alice  |   49 |  23 | 58 |
| Ken    |   33 |  62 | 17 |
| Jason  |   91 |  92 | 90 |
| Tom    |   88 |  72 | 91 |
| Fiona  |   60 |  71 | 45 |
+--------+------+-----+----+

as 別名

　　使用select 欄位名1 as 別名1, 欄位名2 as 別名2 from 表名可將查詢到的記錄欄位修改一個別名

　　以下示例將展示修改name欄位為姓名，修改gender欄位為性別，修改age欄位為年齡的操作

select name as "姓名", gender as "性別", age as "年齡" from student;

+--------+--------+--------+
| 姓名   | 性別   | 年齡   |
+--------+--------+--------+
| Yunya  | male   |     18 |
| Jack   | male   |     17 |
| Bella  | famale |     17 |
| Dairis | famale |     18 |
| Kyle   | famale |     19 |
| Alice  | famale |     16 |
| Ken    | male   |     16 |
| Jason  | male   |     21 |
| Tom    | male   |     20 |
| Fiona  | famale |     19 |
+--------+--------+--------+

distinct

　　使用select distinct(欄位名1, 欄位名2) from 表名可將查詢到的記錄做一個取消重複的操作

　　以下示例將展示使用去重功能來看有多少個小組

select distinct(hoc_group) from student;

+-----------+
| hoc_group |
+-----------+
| first     |
| second    |
| third     |
| fifth     |
| fourth    |
+-----------+

四則運算

　　查詢結果可進行四則運算，以下示例將展示拿到每個同學三科總分的操作

select name, html+css+js as 總成績 from student;
+--------+-----------+
| name   | 總成績    |
+--------+-----------+
| Yunya  |       257 |
| Jack   |       261 |
| Bella  |       231 |
| Dairis |       186 |
| Kyle   |       115 |
| Alice  |       130 |
| Ken    |       112 |
| Jason  |       273 |
| Tom    |       251 |
| Fiona  |       176 |
+--------+-----------+

顯示格式

　　使用concat()可將查詢結果與任意字串進行拼接

　　使用concat_ws()可指定連線符進行拼接，第一個引數是連線符

select concat("姓名->",name,"    ","性別->",gender) from student; # 合併成了一個字串，注意用的空格分隔開的，不然會黏在一起

+--------------------------------------------------+
| concat("姓名->",name,"    ","性別->",gender)     |
+--------------------------------------------------+
| 姓名->Yunya    性別->male                        |
| 姓名->Jack    性別->male                         |
| 姓名->Bella    性別->famale                      |
| 姓名->Dairis    性別->famale                     |
| 姓名->Kyle    性別->famale                       |
| 姓名->Alice    性別->famale                      |
| 姓名->Ken    性別->male                          |
| 姓名->Jason    性別->male                        |
| 姓名->Tom    性別->male                          |
| 姓名->Fiona    性別->famale                      |
+--------------------------------------------------+

select concat_ws("|||",name,gender,age) from student;  # 使用|||為每個欄位進行分割

+----------------------------------+
| concat_ws("|||",name,gender,age) |
+----------------------------------+
| Yunya|||male|||18                |
| Jack|||male|||17                 |
| Bella|||famale|||17              |
| Dairis|||famale|||18             |
| Kyle|||famale|||19               |
| Alice|||famale|||16              |
| Ken|||male|||16                  |
| Jason|||male|||21                |
| Tom|||male|||20                  |
| Fiona|||famale|||19              |
+----------------------------------+

where

　　 where條件是查詢的第一道坎，能有效過濾出我們想要的任意資料

比較運算

　　使用比較運算子> < >= <= !=進行查詢

　　以下示例將展示使用where過濾出js成績大於80分的同學

select name, js from student where js > 80;

+-------+----+
| name  | js |
+-------+----+
| Jack  | 88 |
| Bella | 91 |
| Jason | 90 |
| Tom   | 91 |
+-------+----+

邏輯運算

　　使用and or not可進行邏輯運算與多條件查詢

　　以下示例將展示使用where多條件查詢過濾出各科成績都大於80分的同學

select name, html, css, js from student where html > 80 and css > 80 and js > 80;

+-------+------+-----+----+
| name  | html | css | js |
+-------+------+-----+----+
| Jack  |   92 |  81 | 88 |
| Jason |   91 |  92 | 90 |
+-------+------+-----+----+

成員運算

　　 in可以在特定的值中進行獲取，如in(80,90,100)則代表只取80或者90或者100的這幾條記錄。

　　以下示例將展示只取第一組first以及第二組second學生的個人資訊

select name, gender, age, hoc_group from student where hoc_group in ("first","second");

+-------+--------+-----+-----------+
| name  | gender | age | hoc_group |
+-------+--------+-----+-----------+
| Yunya | male   |  18 | first     |
| Jack  | male   |  17 | second    |
| Bella | famale |  17 | first     |
| Alice | famale |  16 | second    |
+-------+--------+-----+-----------+

between and

　　 between and也是取區間的意思，

　　以下示例將展示使用between and過濾出Js成績大於等於60並且小於80的同學

select name, js from student where js between 60 and 80;

+-------+----+
| name  | js |
+-------+----+
| Yunya | 76 |
| Kyle  | 60 |
+-------+----+

like

　　 like是模糊查詢，其中%代表任意多個字元（類似於貪婪匹配的萬用字元.*），_代表任意一個字元（類似於非貪婪匹配的萬用字元.*?）。

　　以下示例將展示使用like/%匹配出姓名以k開頭的所有同學的名字

select name from student where name like "k%";

+------+
| name |
+------+
| Kyle |
| Ken  |
+------+

　　以下示例將展示使用like/_匹配出姓名以k開頭並整體長度為3的同學的名字

select name from student where name like "k__";

+------+
| name |
+------+
| Ken  |
+------+

正則匹配

　　使用RegExp可進行正則匹配，以下示例將展示使用正則匹配出名字中帶有k的所有同學姓名

select name from student where name REGEXP "k+";

+------+
| name |
+------+
| Jack |
| Kyle |
| Ken  |
+------+

group by

　　分組行為發生在where條件之後，我們可以將查詢到的記錄按照某個相同欄位進行歸類，一般分組都會配合聚合函式進行使用。

　　需要注意的是select語句是排在group by條件之後的，因此聚合函式也能在select語句中使用。

基本使用

　　以下示例將展示對hoc_group欄位進行分組。

　　我們按照hoc_group欄位進行分組，那麼select查詢的欄位只能是hoc_group欄位，想要獲取組內的其他欄位相關資訊，需要藉助函式來完成

select hoc_group from student group by hoc_group;

+-----------+
| hoc_group |
+-----------+
| fifth     |
| first     |
| fourth    |
| second    |
| third     |
+-----------+

　　如果不使用分組，則會產生重複的資訊

mysql> select hoc_group from student;
+-----------+
| hoc_group |
+-----------+
| first     |
| second    |
| first     |
| third     |
| fifth     |
| second    |
| third     |
| fourth    |
| fifth     |
| fourth    |
+-----------+

group_concat

　　用什麼欄位名進行分組，在select查詢時就只能查那個用於分組的欄位，查詢別的欄位會丟擲異常，會提示sql_mode異常。

　　我們將Js成績大於80分的同學篩選出來並且按照gender欄位進行分組，此外我們還想檢視其所有滿足條件同學的名字。

　　以下這樣操作會丟擲異常。

mysql> select gender,name from student where js > 80 group by gender;

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.student.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

　　必須藉助group_concat()函式來進行操作才能使我們的需求圓滿完成。

select gender, group_concat(name)  from student where js > 80 group by gender;

+--------+--------------------+
| gender | group_concat(name) |
+--------+--------------------+
| male   | Jack,Jason,Tom     |
| famale | Bella              |
+--------+--------------------+

分組模式

　　 ONLY_FULL_GROUP_BY要求select中的欄位是在與group by中使用的欄位

　　如果group by是主鍵或unique not null時可以在select中列出其他欄位

#檢視MySQL 5.7預設的sql_mode如下：
mysql> select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#設定sql_mole如下操作(我們可以去掉ONLY_FULL_GROUP_BY模式)：
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

聚合函式

　　聚合函式可以在where執行後的所有語句中使用，比如having，select等。

　　聚合函式一般是同分組進行配套使用，以下是常用的聚合函式。

函式名	作用
COUNT()	對組內成員某一欄位求個數
MAX()	對組內成員某一欄位求最大值
MIN()	對組內成員某一欄位求最小值
AVG()	對組內成員某一欄位求平均值
SUM()	對組內成員某一欄位求和
注意：不使用分組，則預設為一組

　　以下示例將展示求每組的成績總和

select hoc_group, sum(js+html+css) from student group by hoc_group;

+-----------+-------------------+
| hoc_group | sum(js+html+css) |
+-----------+-------------------+
| fifth     |               366 |
| first     |               488 |
| fourth    |               449 |
| second    |               391 |
| third     |               298 |
+-----------+-------------------+

　　以下示例將展示整個班級的平均成績及總成績（round()用於四捨五入操作）

select round(avg(html+js+css)) as 平均分 ,sum(html+js+css) as 總分 from student;

+-----------+--------+
| 平均分    | 總分   |
+-----------+--------+
|       199 |   1992 |
+-----------+--------+

　　以下示例將展示列印出總科成績最高分數

select max(js+css+html) from student;

+------------------+
| max(js+css+html) |
+------------------+
|              273 |
+------------------+

　　以下示例將展示檢視本班有多少男生，多少女生

select gender, count(id) from student group by gender;

+--------+-----------+
| gender | count(id) |
+--------+-----------+
| male   |         5 |
| famale |         5 |
+--------+-----------+

having

　　 having也可用於過濾操作

區別差異

　　執行優先順序從高到低：where> group by > having

　　 where發生在分組group by之前，因而where中可以有任意欄位，但是絕對不能使用聚合函式。

　　 having發生在分組group by之後，因而having中可以使用分組的欄位，無法直接取到其他欄位，可以使用聚合函式

示例演示

　　以下示例將展示使用having過濾取出每組總分數大於400的小組

select hoc_group, sum(html+css+js) from student group by hoc_group having sum(html+css+js) > 400;

+-----------+------------------+
| hoc_group | sum(html+css+js) |
+-----------+------------------+
| first     |              488 |
| fourth    |              449 |
+-----------+------------------+

　　以下示例將展示使用having過濾取出有處分的同學。（可以使用分組的欄位，但不能使用其他欄位）

select sanction, group_concat(name) from student group by sanction having sanction != "無";

+-----------+--------------------+
| sanction  | group_concat(name) |
+-----------+--------------------+
| 大處分    | Dairis,Kyle,Ken    |
| 小處分    | Bella              |
+-----------+--------------------+

ordery by

　　 ordery by用於對查詢結果進行排序

　　預設的排序是按照主鍵進行排序的

asc

　　 asc用於升序排列，以下示例將展示按照每位同學的年齡進行升序排列，如果年齡相同則依照總成績進行升序排列。

select id, name, age, html+css+js as 總成績 from student order by age, html+css+js asc;

+----+--------+-----+-----------+
| id | name   | age | 總成績    |
+----+--------+-----+-----------+
|  7 | Ken    |  16 |       112 |
|  6 | Alice  |  16 |       130 |
|  3 | Bella  |  17 |       231 |
|  2 | Jack   |  17 |       261 |
|  4 | Dairis |  18 |       186 |
|  1 | Yunya  |  18 |       257 |
|  5 | Kyle   |  19 |       115 |
| 10 | Fiona  |  19 |       176 |
|  9 | Tom    |  20 |       251 |
|  8 | Jason  |  21 |       273 |
+----+--------+-----+-----------+

desc

　　 desc用於降序排列，以下示例將展示按照每位同學的年齡進行降序排列。

select id, name, age, html+css+js as 總成績 from student order by age desc;

+----+--------+-----+-----------+
| id | name   | age | 總成績    |
+----+--------+-----+-----------+
|  8 | Jason  |  21 |       273 |
|  9 | Tom    |  20 |       251 |
|  5 | Kyle   |  19 |       115 |
| 10 | Fiona  |  19 |       176 |
|  1 | Yunya  |  18 |       257 |
|  4 | Dairis |  18 |       186 |
|  2 | Jack   |  17 |       261 |
|  3 | Bella  |  17 |       231 |
|  6 | Alice  |  16 |       130 |
|  7 | Ken    |  16 |       112 |
+----+--------+-----+-----------+

limit

　　 limit用於控制顯示的條數

示例演示

　　按照總成績進行降序排序，只列印1-5名。

 select id, name, age, html+css+js as 總成績 from student  order by html+css+js desc limit 5;
 
+----+-------+-----+-----------+
| id | name  | age | 總成績    |
+----+-------+-----+-----------+
|  8 | Jason |  21 |       273 |
|  2 | Jack  |  17 |       261 |
|  1 | Yunya |  18 |       257 |
|  9 | Tom   |  20 |       251 |
|  3 | Bella |  17 |       231 |
+----+-------+-----+-----------+

　　按照總成績進行降序排序，只列印6-8名。

select id, name, age, html+css+js as 總成績 from student  order by html+css+js desc limit 5,3; # 從第五名開始，列印三條。 6，7，8

+----+--------+-----+-----------+
| id | name   | age | 總成績    |
+----+--------+-----+-----------+
|  4 | Dairis |  18 |       186 |
| 10 | Fiona  |  19 |       176 |
|  6 | Alice  |  16 |       130 |
+----+--------+-----+-----------+

MySQL單表查詢
2019-07-08
MySql
單表查詢
2018-10-16
mysql鎖表查詢
2021-09-23
MySql
MySQL - 資料查詢 - 簡單查詢
2020-12-27
MySql
MySql 鎖表查詢命令
2016-12-21
MySql
MySQL訂單分庫分表多維度查詢
2016-04-22
MySql
簡單的mysql查詢
2016-04-13
MySql
資料庫基礎查詢--單表查詢
2018-07-15
資料庫
mysql 三表關聯查詢
2020-06-08
MySql
mysql三表關聯查詢
2019-03-21
MySql
MySQL join連表查詢示例
2018-09-28
MySql
MySQL 5.7 查詢InnoDB鎖表
2016-09-05
MySql
Mysql-基本練習(09-刪除單表記錄、查詢指定列資料、列的別名、簡單單表條件查詢、簡單分組查詢)
2018-05-16
MySql
MySQL—-MySQL資料庫入門—-第四章單表查詢
2019-02-20
MySql資料庫
查詢MySQL資料庫，MySQL表的大小
2020-11-03
MySql資料庫
mysql三張表關聯查詢
2018-10-12
MySql
mysql表碎片的查詢自己回收
2017-03-17
MySql
MySQL 查詢大表注意事項
2017-09-30
MySql
Mysql大表查詢注意事項
2016-04-23
MySql
mysql 查詢建表語句sql
2014-05-21
MySql
MySQL 覆蓋索引、回表查詢
2024-04-04
MySql索引
[MySQL] - 聯表查詢，查詢一個不在另一個表的記錄
2017-12-20
MySql
資料庫中單表查詢
2020-10-05
資料庫
mysql查詢快取簡單使用
2021-03-06
MySql快取
MySQL的簡單查詢語句
2019-01-15
MySql
MYSQL A、B表陣列關聯查詢
2019-07-29
MySql陣列
mysql三表關聯查詢練習
2019-03-20
MySql
mysql 跨表查詢、更新、刪除示例
2021-09-09
MySql
mysql查詢表的資料體積
2017-10-15
MySql
優化mysql資料字典表查詢
2013-04-08
優化MySql
SQL單表查詢語句總結
2020-11-10
SQL
SQL面試題三（單表、多表查詢）
2018-05-04
SQL面試題
子查詢-表子查詢
2017-11-14
MySQL之資料的簡單查詢
2017-05-11
MySql
MySQL查詢
2024-04-14
MySql
Mysql 獲取表設計查詢語句
2019-01-19
MySql
mysql查詢表中最小可用id值
2018-10-23
MySql
MySQL 查詢所有表中的記錄數
2017-03-19
MySql

MySQL 單表查詢

準備資料

查詢語法

執行順序

select

全部查詢

欄位查詢

as 別名

distinct

四則運算

顯示格式

where

比較運算

邏輯運算

成員運算

between and

like

正則匹配

group by

基本使用

group_concat

分組模式

聚合函式

having

區別差異

示例演示

ordery by

asc

desc

limit

示例演示

相關文章