MySQL 单表查询

准备数据

　　 以下操作将在该表中进行

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 表名可拿到特定字段下相应的数据

　　 以下示例将展示使用字段查询拿到每个学生的HTMLCSSJS成绩

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 |
+----+--------+-----+-----------+