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 | 汤神 |
+----+--------+
mysql> select class, avg(math) as avgmath from student group by class having avgmath > 40;
+-------+-----------+| class | avgmath |
+-------+-----------+
| 1 | 63.000000 |
| 2 | 57.000000 |
+-------+-----------+