八：SQL之DQL数据查询语言单表操作

前言：

　　DQL数据库查询语言是我们在开发中最常使用的SQL，这一章总结了单表操作部分的常用查询方式

　　主要操作有：查询所有字段、查询指定字段、查询指定记录、带IN的关键字查询，范围查询，陪查询、查询空值

　　带AND的多条件查询、带OR的多条件查询，关键字DISTINCT，查询结果排序，分组查询。分页查询等

准备好数据测试表　

 mysql> select * from student;
 +------+----------+-------+----------+-------------+
 | s_id | s_name   | s_age | s_gender | s_major     |
 +------+----------+-------+----------+-------------+
 |    1 | 马化腾   |    54 | 男       | CEO         |
 |    2 | 雷军     |    48 | 男       | CEO         |
 |    3 | 丁磊     |    46 | 男       | CEO         |
 |    4 | 马云     |    51 | 男       | CEO         |
 |    5 | 刘强东   |    45 | 男       | CEO         |
 |    6 | 王健林   |    65 | 男       | CEO         |
 |    7 | 周鸿祎   |    46 | 男       | CEO         |
 |    8 | 章泽天   |    25 | 女       | NULL        |
 |    9 | 章子怡   |    39 | 女       | NULL        |
 |   10 | 张一山   |    27 | 男       | 演员        |
 |   11 | 冯小刚   |    66 | 男       | 导演        |
 |   12 | 张艺谋   |    68 | 男       | 导演        |
 |   13 | 刘亦菲   |    32 | 女       | 演员        |
 |   14 | 莫言     |    70 | 男       | 作家        |
 |   15 | 科比     |    35 | 男       | 运动员      |
 |   16 | 梅西     |    31 | 男       | 运动员      |
 |   17 | 约翰塞纳 |    42 | 男       | 运动员      |
 |   18 | 凤姐     |    36 | 女       | NULL        |
 |   19 | 詹姆斯   |    31 | 男       | 运动员      |
 |   20 | 内马尔   |    25 | 男       | 运动员      |
 |   21 | 胡歌     |    35 | 男       | 演员        |
 |   22 | 许家印   |    67 | 男       | CEO         |
 |   23 | 周杰伦   |    35 | 男       | 歌手        |
 |   24 | 鹿晗     |    27 | 男       | 偶像明星    |
 |   25 | P-Gone   |    20 | 男       | 歌手        |
 |   26 | 李安     |    55 | 男       | 导演        |
 |   27 | 斯图尔特 |    28 | 女       | 演员        |
 |   29 | 犀利哥   |    45 | 男       | 乞丐        |
 |   31 | 冯提莫   |    26 | 女       | 主播        |
 |   32 | 卢本伟   |    25 | 男       | 主播        |
 |   33 | 小狗     |    21 | 男       | LOL职业选手 |
 |   34 | Gogoing  |    26 | 男       | LOL职业选手 |
 |   35 | 仙凡     |    27 | 男       | 主播        |
 |   36 | 黑人毅   |    29 | 男       | 主播        |
 |   37 | 旭旭宝宝 |    28 | 男       | 主播        |
 |   38 | 大龙猫   |    27 | 男       | 主播        |
 |   39 | 奥巴马   |    57 | 男       | 前美国总统  |
 |   40 | 克林顿   |    20 | 男       | 前美国总统  |
 |   42 | 林俊杰   |    37 | 男       | 歌手        |
 |   43 | 张韶涵   |    35 | 女       | 歌手        |
 |   44 | 杨紫     |    27 | 女       | 演员        |
 |   45 | 马东     |    49 | 男       | 主持人      |
 +------+----------+-------+----------+-------------+
 42 rows in set (0.00 sec)

练习的数据库表

一：查询所有

　　1.1：查询所有记录

　　　　　　select * from student;

　　1.2：查询指定字段

　　　　　　select s_id,s_name,s_major from student;

　　1.3：查询指定记录（带条件）

　　　　　　select * from student where s_age > 50;

二：关键字IN

　　2.1：in(xx,yy,mm,..)满足条件的都会被查出来

　　

三：带BETWEEN AND 关键字

　　3.1：between xx and yy指定了范围[xx yy]前后都包括在内

　　

四：带LIKE的模糊查询

　　4.1：字段  LIKE  '值'--表示全匹配，LIKE相当于=　

　　

　　4.2：字段 LIKE '%值'；匹配最后一个

　　4.3：字段 LIKE '值%'；匹配第一个

　　4.4：字段 LIKE '%值%'；全匹配

　　

　　4.5：字段 LIKE '_值';  _表示一个字符占位符

　　4.6：字段 LIKE '值_';

　　4.7：字段 LIKE '_值'_;　

 mysql> select * from student where s_name like '__马';
 +------+--------+-------+----------+------------+
 | s_id | s_name | s_age | s_gender | s_major    |
 +------+--------+-------+----------+------------+
 |   39 | 奥巴马 |    57 | 男       | 前美国总统 |
 +------+--------+-------+----------+------------+
 1 row in set (0.00 sec)

 mysql> select * from student where s_name like '马_';
 +------+--------+-------+----------+---------+
 | s_id | s_name | s_age | s_gender | s_major |
 +------+--------+-------+----------+---------+
 |    4 | 马云   |    51 | 男       | CEO     |
 |   45 | 马东   |    49 | 男       | 主持人  |
 +------+--------+-------+----------+---------+
 2 rows in set (0.00 sec)

 mysql> select * from student where s_name like '马__';
 +------+--------+-------+----------+---------+
 | s_id | s_name | s_age | s_gender | s_major |
 +------+--------+-------+----------+---------+
 |    1 | 马化腾 |    54 | 男       | CEO     |
 +------+--------+-------+----------+---------+
 1 row in set (0.00 sec)

 mysql> select * from student where s_name like '_马_';
 +------+--------+-------+----------+---------+
 | s_id | s_name | s_age | s_gender | s_major |
 +------+--------+-------+----------+---------+
 |   20 | 内马尔 |    25 | 男       | 运动员  |
 +------+--------+-------+----------+---------+
 1 row in set (0.00 sec)

五：空值查询

　　5.1：空值是指null并不是“"一般表示数据未知，或者在以后添加数据时，其字段默认为null　　

 mysql> select * from student where isnull(s_major);
 +------+--------+-------+----------+---------+
 | s_id | s_name | s_age | s_gender | s_major |
 +------+--------+-------+----------+---------+
 |    8 | 章泽天 |    25 | 女       | NULL    |
 |    9 | 章子怡 |    39 | 女       | NULL    |
 |   18 | 凤姐   |    36 | 女       | NULL    |
 +------+--------+-------+----------+---------+
 3 rows in set (0.00 sec)

 mysql> select * from student where s_major is null;
 +------+--------+-------+----------+---------+
 | s_id | s_name | s_age | s_gender | s_major |
 +------+--------+-------+----------+---------+
 |    8 | 章泽天 |    25 | 女       | NULL    |
 |    9 | 章子怡 |    39 | 女       | NULL    |
 |   18 | 凤姐   |    36 | 女       | NULL    |
 +------+--------+-------+----------+---------+
 3 rows in set (0.00 sec)

　　5.2：非空值查询 IS NOT NULL;　　　　

　　 select * from student where s_major is not null;

六：带 AND 或 OR 的多条件查询

 mysql> select * from student where s_age >= 60 and s_age < 70;
 +------+--------+-------+----------+---------+
 | s_id | s_name | s_age | s_gender | s_major |
 +------+--------+-------+----------+---------+
 |    6 | 王健林 |    65 | 男       | CEO     |
 |   11 | 冯小刚 |    66 | 男       | 导演    |
 |   12 | 张艺谋 |    68 | 男       | 导演    |
 |   22 | 许家印 |    67 | 男       | CEO     |
 +------+--------+-------+----------+---------+
 4 rows in set (0.00 sec)

 mysql> select * from student where s_age >= 60 or s_age <=20;
 +------+--------+-------+----------+------------+
 | s_id | s_name | s_age | s_gender | s_major    |
 +------+--------+-------+----------+------------+
 |    6 | 王健林 |    65 | 男       | CEO        |
 |   11 | 冯小刚 |    66 | 男       | 导演       |
 |   12 | 张艺谋 |    68 | 男       | 导演       |
 |   14 | 莫言   |    70 | 男       | 作家       |
 |   22 | 许家印 |    67 | 男       | CEO        |
 |   25 | P-Gone |    20 | 男       | 歌手       |
 |   40 | 克林顿 |    20 | 男       | 前美国总统 |
 +------+--------+-------+----------+------------+
 7 rows in set (0.00 sec)

七：关键字DISTINCT，去重复查询

　　去重复查询只是将给定列中的重复的记录去掉　　

　　

八：分组查询 GOUP BY

　　8.1：分组查询就是把值相等的列分到一个组里面

　　8.2：带条件的分组查询where having的情况

　　　　where：在分组前对表数据进行过滤，因此位置在group by前面

　　　　having：在分组后对结果进行过滤，因此位置在group by后面

　　8.3：分组查询可使用count()查询出每一组具体有多少条记录

　　8.4：使用group_ concat(列名)还可以将该组中该列所有值查询出来

　　8.5：还可以将分组查询的结果使用 order by排序

参照以下代码

 mysql> select count(s_name) as 该组总数,s_major from student group by s_major;
 +----------+-------------+
 | 该组总数 | s_major     |
 +----------+-------------+
 |        3 | NULL        |
 |        8 | CEO         |
 |        2 | LOL职业选手 |
 |        1 | 主持人      |
 |        6 | 主播        |
 |        1 | 乞丐        |
 |        1 | 作家        |
 |        1 | 偶像明星    |
 |        2 | 前美国总统  |
 |        3 | 导演        |
 |        4 | 歌手        |
 |        5 | 演员        |
 |        5 | 运动员      |
 +----------+-------------+
 13 rows in set (0.00 sec)

 mysql> select count(s_name) as 该组总数,s_major from student group by s_major having count(s_name) >= 4;
 +----------+---------+
 | 该组总数 | s_major |
 +----------+---------+
 |        8 | CEO     |
 |        6 | 主播    |
 |        4 | 歌手    |
 |        5 | 演员    |
 |        5 | 运动员  |
 +----------+---------+
 5 rows in set (0.00 sec)

 mysql> select count(s_name) as 该组总数,s_major from student where s_age > 30 group by s_major having count(s_name) >= 4;
 +----------+---------+
 | 该组总数 | s_major |
 +----------+---------+
 |        8 | CEO     |
 |        4 | 运动员  |
 +----------+---------+
 2 rows in set (0.00 sec)

 mysql> select count(s_name) as 该组总数,s_major from student where s_age > 30 group by s_major having count(s_name) >= 4 order by s_major;
 +----------+---------+
 | 该组总数 | s_major |
 +----------+---------+
 |        8 | CEO     |
 |        4 | 运动员  |
 +----------+---------+
 2 rows in set (0.00 sec)

 mysql> select count(s_name) as 该组总数,s_major from student where s_age > 30 group by s_major having count(s_name) >= 4 order by s_major desc;
 +----------+---------+
 | 该组总数 | s_major |
 +----------+---------+
 |        4 | 运动员  |
 |        8 | CEO     |
 +----------+---------+
 2 rows in set (0.00 sec)

 mysql> select count(s_name) as 该组总数,s_major,group_concat(s_name) as 该组所有人姓名 from student group by s_major;
 +----------+-------------+---------------------------------------------------+
 | 该组总数 | s_major     | 该组所有人姓名                                    |
 +----------+-------------+---------------------------------------------------+
 |        3 | NULL        | 章泽天,凤姐,章子怡                                |
 |        8 | CEO         | 马化腾,周鸿祎,王健林,刘强东,马云,丁磊,雷军,许家印 |
 |        2 | LOL职业选手 | Gogoing,小狗                                      |
 |        1 | 主持人      | 马东                                              |
 |        6 | 主播        | 冯提莫,卢本伟,仙凡,黑人毅,旭旭宝宝,大龙猫         |
 |        1 | 乞丐        | 犀利哥                                            |
 |        1 | 作家        | 莫言                                              |
 |        1 | 偶像明星    | 鹿晗                                              |
 |        2 | 前美国总统  | 克林顿,奥巴马                                     |
 |        3 | 导演        | 李安,张艺谋,冯小刚                                |
 |        4 | 歌手        | P-Gone,周杰伦,林俊杰,张韶涵                       |
 |        5 | 演员        | 刘亦菲,胡歌,张一山,杨紫,斯图尔特                  |
 |        5 | 运动员      | 梅西,詹姆斯,内马尔,约翰塞纳,科比                  |
 +----------+-------------+---------------------------------------------------+
 13 rows in set (0.00 sec)

GROUP BY练习代码

九：LIMIT实现分页查询

　　9.1：LIMIT 位置便宜量，行数；

　　　　位置便宜量：默认值为0，即从第一条记录开始

　　　　行数：指定从给定位置开始需要查询的总记录数

　　　　LIMIT x,y 不能写成 LIMIT(x,y)

 mysql> select * from student limit 6;
 +------+--------+-------+----------+---------+
 | s_id | s_name | s_age | s_gender | s_major |
 +------+--------+-------+----------+---------+
 |    1 | 马化腾 |    54 | 男       | CEO     |
 |    2 | 雷军   |    48 | 男       | CEO     |
 |    3 | 丁磊   |    46 | 男       | CEO     |
 |    4 | 马云   |    51 | 男       | CEO     |
 |    5 | 刘强东 |    45 | 男       | CEO     |
 |    6 | 王健林 |    65 | 男       | CEO     |
 +------+--------+-------+----------+---------+
 6 rows in set (0.00 sec)

 mysql> select * from student limit 2,3;
 +------+--------+-------+----------+---------+
 | s_id | s_name | s_age | s_gender | s_major |
 +------+--------+-------+----------+---------+
 |    3 | 丁磊   |    46 | 男       | CEO     |
 |    4 | 马云   |    51 | 男       | CEO     |
 |    5 | 刘强东 |    45 | 男       | CEO     |
 +------+--------+-------+----------+---------+
 3 rows in set (0.00 sec)

十：组函数（集合函数）查询

　　count(*)--------------计算表中的总行数

　　count(字段名)------计算指定列的总行数，会忽略NULL的值

　　sum(字段名)------计算该列的总和

　　avg(字段名);--------计算该列的平均值

　　max(字段名)--------计算该列的最大值

　　min(字段名)---------计算该列的最小值

参考以下代码：

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|       42 |
+----------+
1 row in set (0.00 sec)

mysql> select count(s_major) from studen
+----------------+
| count(s_major) |
+----------------+
|             39 |
+----------------+
1 row in set (0.00 sec)

mysql> select sum(s_age) from student;
+------------+
| sum(s_age) |
+------------+
|       1628 |
+------------+
1 row in set (0.00 sec)

mysql> select avg(s_age) from student;
+------------+
| avg(s_age) |
+------------+
|    38.7619 |
+------------+
1 row in set (0.00 sec)

mysql> select max(s_age) from student;
+------------+
| max(s_age) |
+------------+
|         70 |
+------------+
1 row in set (0.00 sec)

mysql> select min(s_age) from student;
+------------+
| min(s_age) |
+------------+
|         20 |
+------------+
1 row in set (0.00 sec)