1、準備數據
以下操作將在該表中進行
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,"無");
2、查詢語法
SELECT DISTINCT(字段名1,字段名2...) FROM 表名
WHERE 條件
GROUP BY 字段名
HAVING 篩選
ORDER BY 字段名 asc/desc
LIMIT 限制條數;
3、執行順序
雖然查詢的書寫語法是上面那樣的，但是其內部執行順序卻有些不太一樣。
1.通過from找到將要查詢的表
2.where規定查詢條件，在表記錄中逐行進行查詢并篩選出符合規則的記錄
3.將查到的記錄進行字段分組group by，如果沒有進行分組，則默認為一組
4.將分組得到的結果進行having篩選，可使用聚和函數（where時不可使用聚合函數）
5.執行select準備打印
6.執行distinct對打印結果進行去重
7.執行ordery by對結果進行排序
8.執行limit對打印結果的條數進行限制
4、select
select主要復負責打印相關的工作
4.1 全部查詢
使用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 | 無 |
+----+--------+--------+-----+-----------+------+-----+----+-----------+
4.2 字段查詢
使用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 |
+--------+------+-----+----+
4.3 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 |
+--------+--------+--------+
4.4 distinct
使用select distinct(字段名1, 字段名2) from 表名可將查詢到的記錄做一個取消重復的操作
以下示例將展示使用去重功能來看有多少個小組
select distinct(hoc_group) from student;
+-----------+
| hoc_group |
+-----------+
| first |
| second |
| third |
| fifth |
| fourth |
+-----------+
4.5 四則運算
查詢結果可進行四則運算，以下示例將展示拿到每個同學三科總分的操作
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 |
+--------+-----------+
4.6 顯示格式
使用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 |
+----------------------------------+
5、where
where條件是查詢的第一道坎，能有效過濾出我們想要的任意數據
5.1、比較運算
使用比較運算符> >= = !=進行查詢
以下示例將展示使用where過濾出js成績大于80分的同學
select name, js from student where js > 80;
+-------+----+
| name | js |
+-------+----+
| Jack | 88 |
| Bella | 91 |
| Jason | 90 |
| Tom | 91 |
+-------+----+
5.2、邏輯運算
使用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 |
+-------+------+-----+----+
5.3、成員運算
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 |
+-------+--------+-----+-----------+
5.4、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 |
+-------+----+
5.5、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 |
+------+
5.6、正則匹配
使用RegExp可進行正則匹配，以下示例將展示使用正則匹配出名字中帶有k的所有同學姓名
select name from student where name REGEXP "k+";
+------+
| name |
+------+
| Jack |
| Kyle |
| Ken |
+------+
6、group by
分組行為發生在where條件之后，我們可以將查詢到的記錄按照某個相同字段進行歸類，一般分組都會配合聚合函數進行使用。
需要注意的是select語句是排在group by條件之后的，因此聚合函數也能在select語句中使用。
6.1、基本使用
以下示例將展示對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 |
+-----------+
6.2、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 |
+--------+--------------------+
6.3、分組模式
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';
6.4、聚合函數
聚合函數可以在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 |
+--------+-----------+
7、having
having也可用于過濾操作
7.1、區別差異
執行優先級從高到低：where> group by > having
where發生在分組group by之前，因而where中可以有任意字段，但是絕對不能使用聚合函數。
having發生在分組group by之后，因而having中可以使用分組的字段，無法直接取到其他字段，可以使用聚合函數
7.2、示例演示
以下示例將展示使用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 |
+-----------+--------------------+
8、ordery by
ordery by用于對查詢結果進行排序
默認的排序是按照主鍵進行排序的
8.1 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 |
+----+--------+-----+-----------+
8.2、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 |
+----+--------+-----+-----------+
9、limit
limit用于控制顯示的條數
9.1、示例演示
按照總成績進行降序排序，只打印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單表查詢的文章就介紹到這了,更多相關MySQL單表查詢內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- 深入講解MongoDB的慢日志查詢（profile）
- MySQL之select in 子查詢優化的實現
- 實例詳解mysql子查詢
- MySQL 參數相關概念及查詢更改方法
- MySQL百萬級數據量分頁查詢方法及其優化建議
- MySQL如何優化查詢速度
- mysql如何查詢日期與時間
- MySQL數據庫高級查詢和多表查詢
- 詳解mysql慢日志查詢
