『 MySQL数据库 』表的增删查改(CRUD)之表的数据插入及基本查询（上）https://developer.aliyun.com/article/1424459
📂 Retrieve(查询)
在数据库中查询是一个非常频繁的工作,需要根据不同的场合以及不同的条件进行查找;
+----+---------+---------+------+---------+ | id | name | chinese | math | english | +----+---------+---------+------+---------+ | 1 | Lihua | 67 | 88 | 90 | | 2 | Liming | 57 | 58 | 70 | | 3 | Zhaolao | 66 | 80 | 47 | | 4 | Wu | 76 | 70 | 47 | | 5 | Wuqi | 88 | 43 | 80 | | 6 | Liqiang | 89 | 92 | 90 | | 7 | Qinsu | 90 | 74 | 67 | | 8 | Zhaoli | 54 | 74 | 100 | +----+---------+---------+------+---------+
📌SELECT语句📌
在MySQL中SELECT语句是一个高频语句;
其功能可以根据条件对表数据进行查询;
其查询不单单能对数据,还能对表达式、函数等等…
mysql> select 1+1; +-----+ | 1+1 | +-----+ | 2 | +-----+ 1 row in set (0.00 sec)
全列查询 📨
在MySQL中*符号代表通配符;
SELECT * FROM table_name;
该句指令即表示查询表中所有字段(列);
以该中方式即可以打印出表中的所有字段的数据;
mysql> select * from Point; +----+---------+---------+------+---------+ | id | name | chinese | math | english | +----+---------+---------+------+---------+ | 1 | Lihua | 67 | 88 | 90 | | 2 | Liming | 57 | 58 | 70 | | 3 | Zhaolao | 66 | 80 | 47 | | 4 | Wu | 76 | 70 | 47 | | 5 | Wuqi | 88 | 43 | 80 | | 6 | Liqiang | 89 | 92 | 90 | | 7 | Qinsu | 90 | 74 | 67 | | 8 | Zhaoli | 54 | 74 | 100 | +----+---------+---------+------+---------+
指定列查询 📨
指定列查询,顾名思义就是指定对应的字段进行查询;
语句:SELECT 字段1,字段2... FROM table_name;
示例:
mysql> select name,chinese from Point; +---------+---------+ | name | chinese | +---------+---------+ | Lihua | 67 | | Liming | 57 | | Zhaolao | 66 | | Wu | 76 | | Wuqi | 88 | | Liqiang | 89 | | Qinsu | 90 | | Zhaoli | 54 | +---------+---------+
查询字段为表达式 📨
SELECT不仅能查询表中的字段,也能查询表达式;
示例:
mysql> select name,chinese,6 from Point; +---------+---------+---+ | name | chinese | 6 | +---------+---------+---+ | Lihua | 67 | 6 | | Liming | 57 | 6 | | Zhaolao | 66 | 6 | | Wu | 76 | 6 | | Wuqi | 88 | 6 | | Liqiang | 89 | 6 | | Qinsu | 90 | 6 | | Zhaoli | 54 | 6 | +---------+---------+---+
在该次查询中查询的除了字段以外还查询了一个常量表达式;
查询结果取别名 📨
当一个查询的字段由一个较为复杂的表达式合成时,可以将该表达式使用as将其改名;
需要注意的是,在SELECT查询为结果的字段进行改名时,这个操作一般是靠后的,即将数据处理完后将其打印时才能将其进行改名,所以不能先进行改名再将其进行其他操作;
示例:
mysql> select name,chinese+math+english as '总分' from Point; +---------+--------+ | name | 总分 | +---------+--------+ | Lihua | 245 | | Liming | 185 | | Zhaolao | 193 | | Wu | 193 | | Wuqi | 211 | | Liqiang | 271 | | Qinsu | 231 | | Zhaoli | 228 | +---------+--------+
其中这里的as可以省略不写;
去重 📨
这里的去重指的是对最终的结果在显示前进行去重;
其语法即为SELECT DISTINCT 字段 FROM table_name;
mysql> select math from Point; # 未使用去重 +------+ | math | +------+ | 88 | | 58 | | 80 | | 70 | | 43 | | 92 | | 74 | | 74 | +------+ mysql> select distinct math from Point; # 结果去重 +------+ | math | +------+ | 88 | | 58 | | 80 | | 70 | | 43 | | 92 | | 74 | +------+
📌where条件📌
在大部分的情况下,SELECT在查询数据时应该配合着行的限制与列的限制从而达到筛选数据的效果;
若是不对数据进行筛选而是无脑选择SELECT * FROM table_name;来将数据进行全部显示的话可能会因为数据量过于庞大不便于观察且并没有筛选出需要条件的数据而做出的无用查看;
在MySQL中可以使用where条件来为SELECT查看的数据做出对应的条件限制从而达到能够查看到对应的数据;
同样的接下来的操作将基于上表进行;
📌比较运算符📌
>,>=,<,<=
若是满足条件则显示1,否则显示0;
mysql> select 1>2; +-----+ | 1>2 | +-----+ | 0 | +-----+ 1 row in set (0.00 sec) mysql> select 1<2; +-----+ | 1<2 | +-----+ | 1 | +-----+ 1 row in set (0.00 sec)
- 示例:
显示出math大于90的人的名字与成绩
mysql> select name,math from Point where math>90; +---------+------+ | name | math | +---------+------+ | Liqiang | 92 | +---------+------+
=与<=>
在MySQL中的等于有两种,分别为以上两种;
两种的等于在实质性的使用层面并没有太多的区别;
唯一的区别只是对NULL进行判断;
#-------------------------------------- mysql> select 1 = 1; +-------+ | 1 = 1 | +-------+ | 1 | +-------+ mysql> select 1 = 2; +-------+ | 1 = 2 | +-------+ | 0 | +-------+ mysql> select NULL = NULL; +-------------+ | NULL = NULL | +-------------+ | NULL | +-------------+ #-------------------------------------- mysql> select 1<=>1; +-------+ | 1<=>1 | +-------+ | 1 | +-------+ mysql> select 1<=>2; +-------+ | 1<=>2 | +-------+ | 0 | +-------+ mysql> select NULL<=>NULL; +-------------+ | NULL<=>NULL | +-------------+ | 1 | +-------------+ #--------------------------------------
- 不等于
!=、<>
在MySQL中的不等于为以上两种,但是无论哪种不等于都无法对NULL做比较;
mysql> select NULL <> NULL; +--------------+ | NULL <> NULL | +--------------+ | NULL | +--------------+ 1 row in set (0.00 sec) mysql> select NULL = NULL; +-------------+ | NULL = NULL | +-------------+ | NULL | +-------------+ 1 row in set (0.00 sec)
- 范围匹配
BETWEEN a0 AND a1
范围匹配一般用来判断一个数是否属于该范围内,且该范围属于左闭右闭区间([a0,a1]);
mysql> select 5 between 0 and 10; +--------------------+ | 5 between 0 and 10 | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec)
- 示例:找出math区间为[70,75]的数据:
mysql> select name,math from Point where math between 70 and 75; +--------+------+ | name | math | +--------+------+ | Wu | 70 | | Qinsu | 74 | | Zhaoli | 74 | +--------+------+
- 该数据是否为一组数据中的其中一个
IN (option,...)
该运算符一般判断一个数据是否存在于一组数据中;
mysql> select 12 in (10,12,14); +------------------+ | 12 in (10,12,14) | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec)
- 示例:分别找出english为(70,80,90,100)的数据;
mysql> select name,english from Point where english in (70,80,90,100); +---------+---------+ | name | english | +---------+---------+ | Lihua | 90 | | Liming | 70 | | Wuqi | 80 | | Liqiang | 90 | | Zhaoli | 100 | +---------+---------+ 5 rows in set (0.00 sec)
- 是NULL
IS NULL与 非NULLIS NOT NULL
该运算符一般用来判断一个字段是否为NULL;
mysql> select NULL IS NULL; +--------------+ | NULL IS NULL | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> select NULL IS NOT NULL; +-------------------+ | NULL IS NOT NULL | +-------------------+ | 0 | +-------------------+ 1 row in set (0.00 sec)
- 模糊匹配
LIKE
该运算符一般用来模糊匹配,其中%表示多个(包括0)个任意字符,_表示任意一个字符;
示例:分别找出name为W的字段与W_ 的字段;
mysql> select * from Point where name like 'W%'; +----+------+---------+------+---------+ | id | name | chinese | math | english | +----+------+---------+------+---------+ | 4 | Wu | 76 | 70 | 47 | | 5 | Wuqi | 88 | 43 | 80 | +----+------+---------+------+---------+ mysql> select * from Point where name like 'W_'; +----+------+---------+------+---------+ | id | name | chinese | math | english | +----+------+---------+------+---------+ | 4 | Wu | 76 | 70 | 47 | +----+------+---------+------+---------+
📌逻辑运算符📌
| 运算符 | 说明 |
| AND | 多个条件为TRUE(1)时结果为TRUE(1); |
| OR | 任意一个条件为TRUE(1)时结果为TRUE(1); |
| NOT | 条件为TRUE(1)时结果为FALSE(0); |
📌结果排序📌
结果排序可以将数据再处理完时对其进行排序处理(一般该操作的顺序为最后的操作);
且没有进行ORDER BY子句的查询所返回的结果顺序一般都是未定义的,即不可靠的;
语法:
SELECT ... FROM table_name [ WHERE ... ] ORDER BY column [ASC|DESC],[...]; #其中: #ASC为升序,DESC为降序; #默认为ASC升序;
示例:
显示name与math的字段且math为升序的条件显示;
mysql> select name,math from Point order by math ASC; +---------+------+ | name | math | +---------+------+ | Wuqi | 43 | | Liming | 58 | | Wu | 70 | | Qinsu | 74 | | Zhaoli | 74 | | Zhaolao | 80 | | Lihua | 88 | | Liqiang | 92 | +---------+------+ 8 rows in set (0.00 sec)
📌筛选分页结果📌
在MySQL中经常会因为数据量过大而导致不便于数据的观察;
而在MySQL中有这么一条语句可以便于结果的观察,即为筛选分页结果;
语法:
#分页时的起始下标为0; SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n; -- 从起始下标开始筛选n条结果; SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s,n; -- 从s开始,筛选n条结果 SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s; -- 从s开始,筛选n条结果(该方法的表达更为明确)
示例:
查看name与math字段并使用order by以降序的方式进行排序最终结果每页显示3条;
mysql> SELECT name,math from Point order by math desc limit 3 offset 0; +---------+------+ | name | math | +---------+------+ | Liqiang | 92 | | Lihua | 88 | | Zhaolao | 80 | +---------+------+ 3 rows in set (0.01 sec) mysql> SELECT name,math from Point order by math desc limit 3 offset 3; +--------+------+ | name | math | +--------+------+ | Qinsu | 74 | | Zhaoli | 74 | | Wu | 70 | +--------+------+ 3 rows in set (0.00 sec) mysql> SELECT name,math from Point order by math desc limit 3 offset 6; +--------+------+ | name | math | +--------+------+ | Liming | 58 | | Wuqi | 43 | +--------+------+ 2 rows in set (0.00 sec)
