MySQL简单查询详解-单表查询-白红宇

MySQL简单查询详解-单表查询

阅读量：6679 次

发布时间：2019-06-25

本文共 18572 字，大约阅读时间需要 61 分钟。

　　　　　　　　　　　　　　　　　　　　　MySQL简单查询详解-单表查询

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

一.查询的执行路径

　　一条SQL查询语句的执行过程大致如下图所示：

　　1>.客户端和服务端通过mysql协议进行通信，mysql服务器通过某种客户端发送来的查询语句的时候，首先会去本地检验缓存是否命中，如果请求的SQL语句之前有本查询过，就会把之前的查询结果直接返回给用户（查询缓存的功能最少要满足三成以上才有意义，如果所有的查询都去用缓存且所有的SQL没有一次被命中的话就毫无意义啦。）；

　　2>.如果查询的SQL没有命中，就会将SQL的请求交给解析器进行解析操作。必要时在预处理器的配合下，解析器会生成一个解析树，这个解析树会有很多条执行路径去查询我们想要得到的SQL结果；

　　3>.如此多的执行路径就会交给查询优化器进行查询操作，查询优化器会选择资源开销最少的路径去执行SQL语句，与此同时，它还可能改写SQL语句，只要改写的结果和用户要查询的结果一致即可，查询优化器选择最优的一条执行路径之后又将这个结果交给查询执行计划进行排队（注意，MySQL的用户同时查询可能不止一个，因此并发的时候需要给它一个队列，哪些查询比较先进的会优先被查询到返回给用户）；

　　4>.以上的所有操作都没有执行的权限，最终执行查询操作的还是查询执行引擎。但是查询引擎并不能直接到磁盘上取数据，查询引擎本事只是把查询请求转换成对应表的存储引擎的API调用；

　　5>.我们知道存储引擎其实是表类型，存储引擎根据查询执行引擎的API调用从磁盘上获取对方所需要的数据并层层返回给用户，在返回的途中，mysql还要考虑是否将查询结果进行缓存操作。

二.选择和投影

1.投影和选择

　　投影其实就是挑选要符合的字段，选择就是挑选符合条件的行。

　　投影：select 字段1，字段2，... from tb_name;

　　　　常用语法格式：selcet * from tb_name;

　　选择：select 字段1，字段2,.... from tb_name where 子句（布尔条件表达式）;

2.布尔条件表达式操作符

　　= 等值比较

　　<=>：跟空值比较不会产生额外信息

　　<>:不等值

　　IS NULL:是否为空

　　IS NOT NULL：是否不空

　　LIKE：支持的通配符%(任意长度的任意字符) _（任意单个字符）

　　RLIKE，REGEXP：支持使用正则表达式作为条件（注意LIKE和RLIKE都是用来做字符比较的，如果用来做数值比较的话性能就相当低了。）

　　IN：判断某行的某一字段的值是否在给定的列表中

　　BETWEEN...AND....:判断指定的值是否位于指定的范围之间（比如，判断一个数值在10和20之间，我们就可以这样写"X BETWEEN 10 AND 20"）

案例展示：

1 mysql> create table stars(SID int unsigned auto_increment not null unique key,Name char(50) not null,Age tinyint unsigned not null,Gender Enum('boy','girl') not null, Tearch char(50));  2 Query OK, 0 rows affected (0.01 sec)  3   4 mysql>  5 mysql> desc stars;  6 +--------+---------------------+------+-----+---------+----------------+  7 | Field  | Type                | Null | Key | Default | Extra          |  8 +--------+---------------------+------+-----+---------+----------------+  9 | SID    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment | 10 | Name   | char(50)            | NO   |     | NULL    |                | 11 | Age    | tinyint(3) unsigned | NO   |     | NULL    |                | 12 | Gender | enum('boy','girl')  | NO   |     | NULL    |                | 13 | Tearch | char(50)            | YES  |     | NULL    |                | 14 +--------+---------------------+------+-----+---------+----------------+ 15 5 rows in set (0.00 sec) 16  17 mysql>  18 mysql> insert into stars values (1,"sun wukong",27,'boy','putilaozu'),(2,'yinzhengjie',18,'boy','himslef'),(3,'liufei',21,'boy','jia baoyu'); 19 Query OK, 3 rows affected (0.00 sec) 20 Records: 3  Duplicates: 0  Warnings: 0 21  22 mysql>  23 mysql> insert into stars values (4,"han senyu",17,'boy','cang laoshi'),(5,'jia shanpeng',16,'boy','ji laosi'),(6,'wu zhiguang',21,'boy','ma laoshi'); 24 Query OK, 3 rows affected (0.00 sec) 25 Records: 3  Duplicates: 0  Warnings: 0 26  27 mysql>  28 mysql> select * from stars; 29 +-----+--------------+-----+--------+-------------+ 30 | SID | Name         | Age | Gender | Tearch      | 31 +-----+--------------+-----+--------+-------------+ 32 |   1 | sun wukong   |  27 | boy    | putilaozu   | 33 |   2 | yinzhengjie  |  18 | boy    | himslef     | 34 |   3 | liufei       |  21 | boy    | jia baoyu   | 35 |   4 | han senyu    |  17 | boy    | cang laoshi | 36 |   5 | jia shanpeng |  16 | boy    | ji laosi    | 37 |   6 | wu zhiguang  |  21 | boy    | ma laoshi   | 38 +-----+--------------+-----+--------+-------------+ 39 6 rows in set (0.00 sec) 40 mysql> select * from stars where Age between 16 and 20; 41 +-----+--------------+-----+--------+-------------+ 42 | SID | Name         | Age | Gender | Tearch      | 43 +-----+--------------+-----+--------+-------------+ 44 |   2 | yinzhengjie  |  18 | boy    | himslef     | 45 |   4 | han senyu    |  17 | boy    | cang laoshi | 46 |   5 | jia shanpeng |  16 | boy    | ji laosi    | 47 +-----+--------------+-----+--------+-------------+ 48 3 rows in set (0.00 sec) 49  50 mysql>  51 mysql> select Name,Age from stars where Age in (18,21); 52 +-------------+-----+ 53 | Name        | Age | 54 +-------------+-----+ 55 | yinzhengjie |  18 | 56 | liufei      |  21 | 57 | wu zhiguang |  21 | 58 +-------------+-----+ 59 3 rows in set (0.00 sec) 60  61 mysql>  62 mysql> select Name,Age from stars where Name rlike '^y.*'; 63 +-------------+-----+ 64 | Name        | Age | 65 +-------------+-----+ 66 | yinzhengjie |  18 | 67 +-------------+-----+ 68 1 row in set (0.00 sec) 69  70 mysql>  71 mysql> insert into stars values(7,'jenny',11,'girl','Li ming'),(8,'danny',14,'boy',NULL); 72 Query OK, 2 rows affected (0.00 sec) 73 Records: 2  Duplicates: 0  Warnings: 0 74  75 mysql> select * from stars; 76 +-----+--------------+-----+--------+-------------+ 77 | SID | Name         | Age | Gender | Tearch      | 78 +-----+--------------+-----+--------+-------------+ 79 |   1 | sun wukong   |  27 | boy    | putilaozu   | 80 |   2 | yinzhengjie  |  18 | boy    | himslef     | 81 |   3 | liufei       |  21 | boy    | jia baoyu   | 82 |   4 | han senyu    |  17 | boy    | cang laoshi | 83 |   5 | jia shanpeng |  16 | boy    | ji laosi    | 84 |   6 | wu zhiguang  |  21 | boy    | ma laoshi   | 85 |   7 | jenny        |  11 | girl   | Li ming     | 86 |   8 | danny        |  14 | boy    | NULL        | 87 +-----+--------------+-----+--------+-------------+ 88 8 rows in set (0.00 sec) 89  90 mysql>  91 mysql> select Name,Tearch from stars where Tearch is NULL; 92 +-------+--------+ 93 | Name  | Tearch | 94 +-------+--------+ 95 | danny | NULL   | 96 +-------+--------+ 97 1 row in set (0.00 sec) 98  99 mysql> select Name,Tearch from stars where Tearch is NOT NULL;100 +--------------+-------------+101 | Name         | Tearch      |102 +--------------+-------------+103 | sun wukong   | putilaozu   |104 | yinzhengjie  | himslef     |105 | liufei       | jia baoyu   |106 | han senyu    | cang laoshi |107 | jia shanpeng | ji laosi    |108 | wu zhiguang  | ma laoshi   |109 | jenny        | Li ming     |110 +--------------+-------------+111 7 rows in set (0.00 sec)112 113 mysql>

3.组合条件测试

　　NOT !

　　AND &&

　　OR ||

案例展示：

1 mysql> select database(); 2 +-------------+ 3 | database()  | 4 +-------------+ 5 | yinzhengjie | 6 +-------------+ 7 1 row in set (0.00 sec) 8  9 mysql> show tables;10 +-----------------------+11 | Tables_in_yinzhengjie |12 +-----------------------+13 | stars                 |14 | t1                    |15 +-----------------------+16 2 rows in set (0.01 sec)17 18 mysql> 19 mysql> select * from stars;20 +-----+--------------+-----+--------+-------------+21 | SID | Name         | Age | Gender | Tearch      |22 +-----+--------------+-----+--------+-------------+23 |   1 | sun wukong   |  27 | boy    | putilaozu   |24 |   2 | yinzhengjie  |  18 | boy    | himslef     |25 |   3 | liufei       |  21 | boy    | jia baoyu   |26 |   4 | han senyu    |  17 | boy    | cang laoshi |27 |   5 | jia shanpeng |  16 | boy    | ji laosi    |28 |   6 | wu zhiguang  |  21 | boy    | ma laoshi   |29 |   7 | jenny        |  11 | girl   | Li ming     |30 |   8 | danny        |  14 | boy    | NULL        |31 +-----+--------------+-----+--------+-------------+32 8 rows in set (0.00 sec)33 34 mysql> select Name,Age from stars where Age > 16 and Gender = 'boy';35 +-------------+-----+36 | Name        | Age |37 +-------------+-----+38 | sun wukong  |  27 |39 | yinzhengjie |  18 |40 | liufei      |  21 |41 | han senyu   |  17 |42 | wu zhiguang |  21 |43 +-------------+-----+44 5 rows in set (0.00 sec)45 46 mysql>

4.排序

　　order by ‘排序字段’

　　默认为升序：ASC

　　降序：DESC

案例展示：

1 mysql> select * from stars; 2 +-----+--------------+-----+--------+-------------+ 3 | SID | Name         | Age | Gender | Tearch      | 4 +-----+--------------+-----+--------+-------------+ 5 |   1 | sun wukong   |  27 | boy    | putilaozu   | 6 |   2 | yinzhengjie  |  18 | boy    | himslef     | 7 |   3 | liufei       |  21 | boy    | jia baoyu   | 8 |   4 | han senyu    |  17 | boy    | cang laoshi | 9 |   5 | jia shanpeng |  16 | boy    | ji laosi    |10 |   6 | wu zhiguang  |  21 | boy    | ma laoshi   |11 |   7 | jenny        |  11 | girl   | Li ming     |12 |   8 | danny        |  14 | boy    | NULL        |13 +-----+--------------+-----+--------+-------------+14 8 rows in set (0.00 sec)15 16 mysql> 17 mysql> select Name,Age from stars where Age > 16 and Gender = 'boy' order by Name desc;18 +-------------+-----+19 | Name        | Age |20 +-------------+-----+21 | yinzhengjie |  18 |22 | wu zhiguang |  21 |23 | sun wukong  |  27 |24 | liufei      |  21 |25 | han senyu   |  17 |26 +-------------+-----+27 5 rows in set (0.00 sec)28 29 mysql> select Name,Age from stars where Age > 16 and Gender = 'boy' order by Name asc;30 +-------------+-----+31 | Name        | Age |32 +-------------+-----+33 | han senyu   |  17 |34 | liufei      |  21 |35 | sun wukong  |  27 |36 | wu zhiguang |  21 |37 | yinzhengjie |  18 |38 +-------------+-----+39 5 rows in set (0.00 sec)40 41 mysql>

5.常用内置的聚合函数

　　关键字如下：

　　　　1>.SUM（）：运算和

　　　　2>.AVG（）：运算平均值

　　　　3>.MAX（）：运算最大值

　　　　5>.MIN（）：运算最小值

　　　　6>.COUNT（）：运算个数统计

案例展示：

1 mysql> select * from stars; 2 +-----+--------------+-----+--------+-------------+ 3 | SID | Name         | Age | Gender | Tearch      | 4 +-----+--------------+-----+--------+-------------+ 5 |   1 | sun wukong   |  27 | boy    | putilaozu   | 6 |   2 | yinzhengjie  |  18 | boy    | himslef     | 7 |   3 | liufei       |  21 | boy    | jia baoyu   | 8 |   4 | han senyu    |  17 | boy    | cang laoshi | 9 |   5 | jia shanpeng |  16 | boy    | ji laosi    |10 |   6 | wu zhiguang  |  21 | boy    | ma laoshi   |11 |   7 | jenny        |  11 | girl   | Li ming     |12 |   8 | danny        |  14 | boy    | NULL        |13 +-----+--------------+-----+--------+-------------+14 8 rows in set (0.00 sec)15 16 mysql> select SUM(Age) from stars;17 +----------+18 | SUM(Age) |19 +----------+20 |      145 |21 +----------+22 1 row in set (0.00 sec)23 24 mysql> select MAX(Age) from stars;25 +----------+26 | MAX(Age) |27 +----------+28 |       27 |29 +----------+30 1 row in set (0.00 sec)31 32 mysql> select MIN(Age) from stars;33 +----------+34 | MIN(Age) |35 +----------+36 |       11 |37 +----------+38 1 row in set (0.00 sec)39 40 mysql> select COUNT(Age) from stars;41 +------------+42 | COUNT(Age) |43 +------------+44 |          8 |45 +------------+46 1 row in set (0.00 sec)47 48 mysql> select COUNT(*) from stars;            #这种用法不建议，“*”的效率是非常低的，不如加入一个字段进去！49 +----------+50 | COUNT(*) |51 +----------+52 |        8 |53 +----------+54 1 row in set (0.00 sec)55 56 mysql> 57 mysql> select SUM(Age) from stars where Age > 21;58 +----------+59 | SUM(Age) |60 +----------+61 |       27 |62 +----------+63 1 row in set (0.00 sec)64 65 mysql>

6.分组

　　关键字：group by

案例展示：

1 mysql> select * from stars; 2 +-----+--------------+-----+--------+-------------+ 3 | SID | Name         | Age | Gender | Tearch      | 4 +-----+--------------+-----+--------+-------------+ 5 |   1 | sun wukong   |  27 | boy    | putilaozu   | 6 |   2 | yinzhengjie  |  18 | boy    | himslef     | 7 |   3 | liufei       |  21 | boy    | jia baoyu   | 8 |   4 | han senyu    |  17 | boy    | cang laoshi | 9 |   5 | jia shanpeng |  16 | boy    | ji laosi    |10 |   6 | wu zhiguang  |  21 | boy    | ma laoshi   |11 |   7 | jenny        |  11 | girl   | Li ming     |12 |   8 | danny        |  14 | boy    | NULL        |13 +-----+--------------+-----+--------+-------------+14 8 rows in set (0.00 sec)15 16 mysql> 17 mysql> 18 mysql> select Gender,SUM(Age) from stars group by Gender;19 +--------+----------+20 | Gender | SUM(Age) |21 +--------+----------+22 | boy    |      134 |23 | girl   |       11 |24 +--------+----------+25 2 rows in set (0.00 sec)26 27 mysql>

7.对分组的条件过滤

　　关键字：having

案例展示：

1 mysql> select * from stars;  2 +-----+--------------+-----+--------+-------------+  3 | SID | Name         | Age | Gender | Tearch      |  4 +-----+--------------+-----+--------+-------------+  5 |   1 | sun wukong   |  27 | boy    | putilaozu   |  6 |   2 | yinzhengjie  |  18 | boy    | himslef     |  7 |   3 | liufei       |  21 | boy    | jia baoyu   |  8 |   4 | han senyu    |  17 | boy    | cang laoshi |  9 |   5 | jia shanpeng |  16 | boy    | ji laosi    | 10 |   6 | wu zhiguang  |  21 | boy    | ma laoshi   | 11 |   7 | jenny        |  11 | girl   | Li ming     | 12 |   8 | danny        |  14 | boy    | NULL        | 13 +-----+--------------+-----+--------+-------------+ 14 8 rows in set (0.00 sec) 15  16 mysql>  17 mysql> alter table stars add ClassID tinyint unsigned; 18 Query OK, 8 rows affected (0.00 sec) 19 Records: 8  Duplicates: 0  Warnings: 0 20  21 mysql>  22 mysql> select * from stars; 23 +-----+--------------+-----+--------+-------------+---------+ 24 | SID | Name         | Age | Gender | Tearch      | ClassID | 25 +-----+--------------+-----+--------+-------------+---------+ 26 |   1 | sun wukong   |  27 | boy    | putilaozu   |    NULL | 27 |   2 | yinzhengjie  |  18 | boy    | himslef     |    NULL | 28 |   3 | liufei       |  21 | boy    | jia baoyu   |    NULL | 29 |   4 | han senyu    |  17 | boy    | cang laoshi |    NULL | 30 |   5 | jia shanpeng |  16 | boy    | ji laosi    |    NULL | 31 |   6 | wu zhiguang  |  21 | boy    | ma laoshi   |    NULL | 32 |   7 | jenny        |  11 | girl   | Li ming     |    NULL | 33 |   8 | danny        |  14 | boy    | NULL        |    NULL | 34 +-----+--------------+-----+--------+-------------+---------+ 35 8 rows in set (0.00 sec) 36  37 mysql> update stars set ClassID = 1 where SID = 1; 38 Query OK, 1 row affected (0.00 sec) 39 Rows matched: 1  Changed: 1  Warnings: 0 40  41 mysql> update stars set ClassID = 3 where SID = 2; 42 Query OK, 1 row affected (0.00 sec) 43 Rows matched: 1  Changed: 1  Warnings: 0 44  45 mysql> update stars set ClassID = 5 where SID = 3; 46 Query OK, 1 row affected (0.00 sec) 47 Rows matched: 1  Changed: 1  Warnings: 0 48  49 mysql> update stars set ClassID = 2 where SID = 4; 50 Query OK, 1 row affected (0.01 sec) 51 Rows matched: 1  Changed: 1  Warnings: 0 52  53 mysql> update stars set ClassID = 4 where SID = 5; 54 Query OK, 1 row affected (0.00 sec) 55 Rows matched: 1  Changed: 1  Warnings: 0 56  57 mysql> update stars set ClassID = 2 where SID = 6; 58 Query OK, 1 row affected (0.00 sec) 59 Rows matched: 1  Changed: 1  Warnings: 0 60  61 mysql> update stars set ClassID = 3 where SID = 7; 62 Query OK, 1 row affected (0.00 sec) 63 Rows matched: 1  Changed: 1  Warnings: 0 64  65 mysql> update stars set ClassID = 2 where SID = 8; 66 Query OK, 1 row affected (0.00 sec) 67 Rows matched: 1  Changed: 1  Warnings: 0 68  69 mysql>  70 mysql> select * from stars; 71 +-----+--------------+-----+--------+-------------+---------+ 72 | SID | Name         | Age | Gender | Tearch      | ClassID | 73 +-----+--------------+-----+--------+-------------+---------+ 74 |   1 | sun wukong   |  27 | boy    | putilaozu   |       1 | 75 |   2 | yinzhengjie  |  18 | boy    | himslef     |       3 | 76 |   3 | liufei       |  21 | boy    | jia baoyu   |       5 | 77 |   4 | han senyu    |  17 | boy    | cang laoshi |       2 | 78 |   5 | jia shanpeng |  16 | boy    | ji laosi    |       4 | 79 |   6 | wu zhiguang  |  21 | boy    | ma laoshi   |       2 | 80 |   7 | jenny        |  11 | girl   | Li ming     |       3 | 81 |   8 | danny        |  14 | boy    | NULL        |       2 | 82 +-----+--------------+-----+--------+-------------+---------+ 83 8 rows in set (0.00 sec) 84  85 mysql>  86 mysql> select ClassID,Count(Name),sum(Age) from stars group by ClassID; 87 +---------+-------------+----------+ 88 | ClassID | Count(Name) | sum(Age) | 89 +---------+-------------+----------+ 90 |       1 |           1 |       27 | 91 |       2 |           3 |       52 | 92 |       3 |           2 |       29 | 93 |       4 |           1 |       16 | 94 |       5 |           1 |       21 | 95 +---------+-------------+----------+ 96 5 rows in set (0.00 sec) 97  98 mysql>  99 mysql> select ClassID,Count(Name) from stars group by ClassID having Count(Name) >=2;100 +---------+-------------+101 | ClassID | Count(Name) |102 +---------+-------------+103 |       2 |           3 |104 |       3 |           2 |105 +---------+-------------+106 2 rows in set (0.00 sec)107 108 mysql> 109 mysql> select ClassID from stars group by ClassID having sum(Age) >=50;110 +---------+111 | ClassID |112 +---------+113 |       2 |114 +---------+115 1 row in set (0.00 sec)116 117 mysql>

8.只返回有用的行

　　关键字：LIMIT（一个数为显示的行数，两个数字为偏移第一个数字行，显示第二个数字）

案例展示：

1 mysql> select * from stars ; 2 +-----+--------------+-----+--------+-------------+---------+ 3 | SID | Name         | Age | Gender | Tearch      | ClassID | 4 +-----+--------------+-----+--------+-------------+---------+ 5 |   1 | sun wukong   |  27 | boy    | putilaozu   |       1 | 6 |   2 | yinzhengjie  |  18 | boy    | himslef     |       3 | 7 |   3 | liufei       |  21 | boy    | jia baoyu   |       5 | 8 |   4 | han senyu    |  17 | boy    | cang laoshi |       2 | 9 |   5 | jia shanpeng |  16 | boy    | ji laosi    |       4 |10 |   6 | wu zhiguang  |  21 | boy    | ma laoshi   |       2 |11 |   7 | jenny        |  11 | girl   | Li ming     |       3 |12 |   8 | danny        |  14 | boy    | NULL        |       2 |13 +-----+--------------+-----+--------+-------------+---------+14 8 rows in set (0.00 sec)15 16 mysql> 17 mysql> select * from stars limit 2;18 +-----+-------------+-----+--------+-----------+---------+19 | SID | Name        | Age | Gender | Tearch    | ClassID |20 +-----+-------------+-----+--------+-----------+---------+21 |   1 | sun wukong  |  27 | boy    | putilaozu |       1 |22 |   2 | yinzhengjie |  18 | boy    | himslef   |       3 |23 +-----+-------------+-----+--------+-----------+---------+24 2 rows in set (0.00 sec)25 26 mysql> select * from stars limit 2,3;27 +-----+--------------+-----+--------+-------------+---------+28 | SID | Name         | Age | Gender | Tearch      | ClassID |29 +-----+--------------+-----+--------+-------------+---------+30 |   3 | liufei       |  21 | boy    | jia baoyu   |       5 |31 |   4 | han senyu    |  17 | boy    | cang laoshi |       2 |32 |   5 | jia shanpeng |  16 | boy    | ji laosi    |       4 |33 +-----+--------------+-----+--------+-------------+---------+34 3 rows in set (0.00 sec)35 36 mysql>

9.select语句的执行流程

　　from clause --> where clause --> group by --> having clause -->order by --> select -->limit

select常用的修饰符：

　　distinct 重复的只显示一次

　　SQL_CACHE 缓存查询结果

　　SQL_NO_CACHE 不缓存查询结果

10.小试牛刀

　　我比较喜欢看日本的一个动漫叫《火影忍者》，如果要存储一些人员信息的话，我们应该如何搞呢？请用多张表将下面表格的信息保存。相信这对大家来说应该都是小case，如果你忘记如何写SQL语句的话，可以参考：