mysql-select语句小结

首先先创建一个班学生的信息，并插入一些数据来解释用法：

create table student(
    id int not null,
    name varchar(20) not null,
    chinese float(5,2) not null default 0.0,
    math float(5,2) not null default 0.0,
    english float(5,2) not null default 0.0
);

insert into student values(1, '科比', 50, 60, 80);
insert into student values(2, '詹姆斯', 10, 60 30);
insert into student values(3, '库里', 57, 49, 99);
insert into student values(4, '杜兰特', 68, 48, 20);
insert into student values(5, '汤神', 74, 70, 60);
insert into student values(6, '格林', 48, 73, 59);

---

查询：

1、指定查询列，例如id，姓名，语文成绩

mysql> select id, name, chinese from student;
+----+-----------+---------+
| id | name      | chinese |
+----+-----------+---------+
|  1 | 科比      |   50.00 |
|  2 | 詹姆斯    |   10.00 |
|  3 | 库里      |   57.00 |
|  4 | 杜兰特    |   68.00 |
|  5 | 汤神      |   74.00 |
|  6 | 格林      |   48.00 |
+----+-----------+---------+

---

2、distinct可去除结果中完全相同的行，通过下面的例子就可以看出本来是有两个60，现在只剩下一个了：

mysql> select distinct math from student;
+-------+
| math  |
+-------+
| 60.00 |
| 49.00 |
| 48.00 |
| 70.00 |
| 73.00 |
+-------+

---

3、as起别名：在select语句中可以使用as给查看出的信息赋予别名：

mysql> select math as 数学 from student;
+--------+
| 数学   |
+--------+
|  60.00 |
|  60.00 |
|  49.00 |
|  48.00 |
|  70.00 |
|  73.00 |
+--------+

---

where条件过滤：

在select语句查询过程中，可以使用where进行条件的限定：

还是用之前的数据进行演示：

1、查询数学成绩在50~70之间的学生的姓名：

mysql> select id, name, math from student where math between 50 and 70;
+----+-----------+-------+
| id | name      | math  |
+----+-----------+-------+
|  1 | 科比      | 60.00 |
|  2 | 詹姆斯    | 60.00 |
|  5 | 汤神      | 70.00 |
+----+-----------+-------+

---

2、查询姓杜的学生：

在进行模糊查询时要分清’_’和’%’的使用场景（下划线适用于某一位随意匹配）：

mysql> select id, name from student where name like '杜%';
+----+-----------+
| id | name      |
+----+-----------+
|  4 | 杜兰特    |
+----+-----------+

---

查询总分大于100，且名字第二个字为神的学生：

mysql> select id, name from student where chinese+math+english > 100 and name like '_神';
+----+--------+
| id | name   |
+----+--------+
|  5 | 汤神   |
+----+--------+

---

order by 排序子句：

在select的结尾处添加order by指定排序的顺序；by asc表示升序（默认），by desc表示降序；

例如：对所有学生的总分进行从高到低的排序：

mysql> select id, name, chinese+math+english as total from student order by total desc;
+----+-----------+--------+
| id | name      | total  |
+----+-----------+--------+
|  3 | 库里      | 205.00 |
|  5 | 汤神      | 204.00 |
|  1 | 科比      | 190.00 |
|  6 | 格林      | 180.00 |
|  4 | 杜兰特    | 136.00 |
|  2 | 詹姆斯    | 100.00 |
+----+-----------+--------+

---

count(列名)：返回某一列行的总数

在使用中可以用于统计满足该条件的有多少行；例如统计有多少个学生数学成绩超过了50：

mysql> select count(*) from student where math > 50;
+----------+
| count(*) |
+----------+
|        4 |
+----------+

---

sum，avg

sum函数返回满足where条件限定的行的和；

avg函数返回满足where条件的一列的平均值；

例如：求一个班的语文总分和数学平均分：

mysql> select sum(chinese), avg(math) from student;
+--------------+-----------+
| sum(chinese) | avg(math) |
+--------------+-----------+
|       307.00 | 60.000000 |
+--------------+-----------+

---

max，min

返回满足where条件的一列的最大/最小值；例如找出一个班中的数学的最高和最低分的学生：

mysql> select max(math), min(math) from student;
+-----------+-----------+
| max(math) | min(math) |
+-----------+-----------+
|     73.00 |     48.00 |
+-----------+-----------+

---

group by子句：

首先将我们之前的表结构修改一下，增添一列班级，并给每个学生选定一个班级：

create table student(
    id int not null,
    name varchar(20) not null,
    chinese float(5,2) not null default 0.0,
    math float(5,2) not null default 0.0,
    english float(5,2) not null default 0.0,
    class tinyint not null default 1
);

insert into student values(1, '科比', 50, 60, 80, 1);
insert into student values(2, '詹姆斯', 10, 66, 30, 2);
insert into student values(3, '库里', 57, 49, 99, 1);
insert into student values(4, '杜兰特', 68, 48, 20, 2);
insert into student values(5, '汤神', 74, 70, 60, 1);
insert into student values(6, '格林', 48, 73, 59, 1);

接下来使用group by语句进行试验：

例：显示每个班数学的平均分和最高分：

mysql> select class, max(math), avg(math) from student group by class;
+-------+-----------+-----------+
| class | max(math) | avg(math) |
+-------+-----------+-----------+
|     1 |     73.00 | 63.000000 |
|     2 |     66.00 | 57.000000 |
+-------+-----------+-----------+

---

还需要注意的是：在之前的条件限制中，经常使用where语句，但是对于group by往往和having配合使用，对group by结果进行过滤。

例如找出数学平均分大于40分的班级：

mysql> select class, avg(math) as avgmath from student group by class having avgmath > 40;
+-------+-----------+
| class | avgmath   |
+-------+-----------+
|     1 | 63.000000 |
|     2 | 57.000000 |
+-------+-----------+

其实having和where最主要的区别在于having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。