【MySQL】表的增删查改（CRUD）（下）

个人主页：♡喜欢做梦

欢迎 👍点赞 ➕关注 ❤️收藏 💬评论

本篇是继上篇的下篇，如果上篇没有看过小伙伴，可以先看看我的上一篇再来看一下这一篇【MySQL】表的增删查改（CRUD）（上）

💥条件查询（where）

概念：

条件查询：允许用户在查询语句中指定筛选条件，数据库中会根据这些条件从表中筛选出符合条件的数据，将满足条件的记录返回给用户，不满足的条件的排出在外。

进行条件查询需要用到where，否则在没有写where的情况下，写运算符会发生报错。

运算符

比较运算符：

运算符	说明
>,>=,<,<=	大于，大于等于，小于，小于等于
=	等于，null不安全，例如null=null，结果为null
<=>	等于，null安全，例如null=null，结果为true（1）
!=,<>	不等于
between a and b	匹配范围，[a0,a1],如果a0<=value<=a1,则返回true（1）
in（option1，option2....）	如果是in中任意一个option，则返回true（1）
is null	如果是null，则返回true（1）
is not null	如果不是null，则返回true（1）
like	模糊匹配，%表示任意多个（包括0个）任意字符； _表示任意一个字符

这里的等于就是=，而不是==；

逻辑运算符

运算符	说明
and	多个条件必须都为true（1），结果才为true（1）
or	任意一个条件为true（1），结果才为true（1）
not	条件为true（1），结果为false（0）

and类似于&&；
or类似于||；
not类似于！；
逻辑运算符具有优先级，建议用括号制定。

示例：

表中数据：

mysql> select * from exam;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 张三   |    67.0 | 80.0 |    88.0 |
|    2 | 李四   |    87.0 | 55.0 |    66.0 |
|    3 | 小五   |    91.0 | 90.0 |    96.0 |
|    4 | 小六   |    48.0 | 32.0 |    55.0 |
|    5 | 赵六   |    91.0 | 90.0 |    96.0 |
|    6 | 老王   |    23.0 | 36.0 |    NULL |
|    7 | 老六   |    33.0 | 26.0 |    66.0 |
|    8 | 孙武   |    77.0 | 88.0 |    NULL |
+------+--------+---------+------+---------+
8 rows in set (0.00 sec)

🔥基本查询

--查询语文成绩大于60的同学(>)
mysql>  select * from exam where chinese>60;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 张三   |    67.0 | 80.0 |    88.0 |
|    2 | 李四   |    87.0 | 55.0 |    66.0 |
|    3 | 小五   |    91.0 | 90.0 |    96.0 |
|    5 | 赵六   |    91.0 | 90.0 |    96.0 |
|    8 | 孙武   |    77.0 | 88.0 |    NULL |
+------+--------+---------+------+---------+
5 rows in set (0.00 sec)--查询英语成绩小于60的同学（其结果集会自动过滤null）
mysql> select * from exam where english<60;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    4 | 小六   |    48.0 | 32.0 |    55.0 |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)--查询数学成绩为90的同学（=）
mysql>  select * from exam where math=90;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    3 | 小五   |    91.0 | 90.0 |    96.0 |
|    5 | 赵六   |    91.0 | 90.0 |    96.0 |
+------+--------+---------+------+---------+
2 rows in set (0.00 sec)--查询数学成绩为85的同学（=）
mysql>   select * from exam where math=85;
--不存在数学成绩为85的同学，显示为空
Empty set (0.00 sec)--查询数学成绩不为90的同学（<>或者！=）
mysql>  select * from exam where math<>90;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 张三   |    67.0 | 80.0 |    88.0 |
|    2 | 李四   |    87.0 | 55.0 |    66.0 |
|    4 | 小六   |    48.0 | 32.0 |    55.0 |
|    6 | 老王   |    23.0 | 36.0 |    NULL |
|    7 | 老六   |    33.0 | 26.0 |    66.0 |
|    8 | 孙武   |    77.0 | 88.0 |    NULL |
+------+--------+---------+------+---------+
6 rows in set (0.00 sec)--查询语数英总分小于150的同学
mysql> select *from exam where chinese+english+math<150;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    4 | 小六   |    48.0 | 32.0 |    55.0 |
|    7 | 老六   |    33.0 | 26.0 |    66.0 |
+------+--------+---------+------+---------+
2 rows in set (0.00 sec)--where不能使用别名，否则会报错
mysql> select name,chinese+math+english as total from exam where total<150;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'--进行条件查询的同时可以对数据进行排序
mysql>  select name,chinese+math+english as total from exam where chinese+english+math<150 order by total asc;
+--------+-------+
| name   | total |
+--------+-------+
| 老六   | 125.0 |
| 小六   | 135.0 |
+--------+-------+
2 rows in set (0.00 sec)

结果集会自动过滤掉null；
where不能使用别名，否则会发生报错；

为什么where不能使用别名？

这与MySQL执行SQL语句有关

执行顺序：

首先确定表，执行from；
查询的时候爸符合条件的数据过滤处理，也就是接下来执行where语句，此时where语句还没有被定义别名；
随后执行select后面指定的列，将指定列加入到最中的结构中；
排序操作，根据order by子句中指定的列名和排序规则进行最后的排序。

🔥and与or

mysql>  select * from exam where math<60 or chinese>40 and english<70;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    2 | 李四   |    87.0 | 55.0 |    66.0 |
|    4 | 小六   |    48.0 | 32.0 |    55.0 |
|    6 | 老王   |    23.0 | 36.0 |    NULL |
|    7 | 老六   |    33.0 | 26.0 |    66.0 |
+------+--------+---------+------+---------+
4 rows in set (0.00 sec)

and与or的优先级比较

优先级顺序：not>and>or;
建议：用的时候还是手动加括号；
范围查询

1.between...and...

--查询英语成绩在60-90的同学（between a and b）
mysql>  select * from exam where english between 60 and 90;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 张三   |    67.0 | 80.0 |    88.0 |
|    2 | 李四   |    87.0 | 55.0 |    66.0 |
|    7 | 老六   |    33.0 | 26.0 |    66.0 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)--也可以使用and
mysql> select * from exam where english >=60 and english <=90;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 张三   |    67.0 | 80.0 |    88.0 |
|    2 | 李四   |    87.0 | 55.0 |    66.0 |
|    7 | 老六   |    33.0 | 26.0 |    66.0 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)

between...and...左右是闭区间；

2. in

--查找语文成绩为33或者87或者66的同学（in）
mysql> select * from exam where chinese in(33,87,66);
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    2 | 李四   |    87.0 | 55.0 |    66.0 |
|    7 | 老六   |    33.0 | 26.0 |    66.0 |
+------+--------+---------+------+---------+
2 rows in set (0.01 sec)--也可以使用or来表示
mysql> select * from exam where chinese=33 or chinese=87 or  chinese=66;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    2 | 李四   |    87.0 | 55.0 |    66.0 |
|    7 | 老六   |    33.0 | 26.0 |    66.0 |
+------+--------+---------+------+---------+
2 rows in set (0.01 sec)

🔥模糊查询：like

--查找老开头的名字（%）：
mysql> select * from exam where name like '老%';
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    6 | 老王      |    23.0 | 36.0 |    NULL |
|    7 | 老六      |    33.0 | 26.0 |    66.0 |
|    8 | 老小四    |    22.0 | 87.0 |    92.0 |
+------+-----------+---------+------+---------+
3 rows in set (0.00 sec)--查找最后一个名字为六的名字：
mysql> select * from exam where name like '%六';
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    4 | 小六   |    48.0 | 32.0 |    55.0 |
|    5 | 赵六   |    91.0 | 90.0 |    96.0 |
|    7 | 老六   |    33.0 | 26.0 |    66.0 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)--查找名字开头为老，且名只有一个的名字：
mysql>  select * from exam where name like '老_';
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    6 | 老王   |    23.0 | 36.0 |    NULL |
|    7 | 老六   |    33.0 | 26.0 |    66.0 |
+------+--------+---------+------+---------+
2 rows in set (0.00 sec)--查找语文成绩为9开头的同学：
mysql>  select * from exam where chinese like '9%';
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    3 | 小五   |    91.0 | 90.0 |    96.0 |
|    5 | 赵六   |    91.0 | 90.0 |    96.0 |
+------+--------+---------+------+---------+
2 rows in set (0.00 sec)

查询时要使用 like 不能使用 = ；
%表示任意多个（包括0个）任意字符；
_表示任意一个字符；
要记得加上单引号，否则报错；

🔥Null查询

--查找英语成绩为null的同学：
mysql>  select * from exam where english is null;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    6 | 老王   |    23.0 | 36.0 |    NULL |
|    8 | 孙武   |    77.0 | 88.0 |    NULL |
+------+--------+---------+------+---------+
2 rows in set (0.00 sec)--查找英语成绩不为null的同学：
mysql> select * from exam where english is  not null;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 张三      |    67.0 | 80.0 |    88.0 |
|    2 | 李四      |    87.0 | 55.0 |    66.0 |
|    3 | 小五      |    91.0 | 90.0 |    96.0 |
|    4 | 小六      |    48.0 | 32.0 |    55.0 |
|    5 | 赵六      |    91.0 | 90.0 |    96.0 |
|    7 | 老六      |    33.0 | 26.0 |    66.0 |
|    8 | 老小四    |    22.0 | 87.0 |    92.0 |
+------+-----------+---------+------+---------+
7 rows in set (0.00 sec)--也可以使用<=>
mysql>  select * from exam where english <=> null;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    6 | 老王   |    23.0 | 36.0 |    NULL |
|    8 | 孙武   |    77.0 | 88.0 |    NULL |
+------+--------+---------+------+---------+
2 rows in set (0.00 sec)

查询null值时，不能使用=，！=，否则显示为空；

也可以使用<=>来查找为null的值；

🔥分页查询：limit

概念：

分页查询：用于将大量数据按照一定的大小进行划分，以便逐页获取和展示数据。

语法：

--从0开始筛选n条结果：
select * from table_name [where...][order by...] limit n;
--从s开始筛选n条结果：
select * from table_name [where...][order by...] limit s,n;
--从s开始筛选n条结果（更明确）：
select * from table_name [where...][order by...] limit n offset s;

起始下标是由0开始；
s表示起始位置；
n表示每页显示的记录数；
offset表示起始位置；

示例：

--从第一条开始，记录数为0:
--写法一：
mysql>  select * from exam limit 3;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 张三   |    67.0 | 80.0 |    88.0 |
|    2 | 李四   |    87.0 | 55.0 |    66.0 |
|    3 | 小五   |    91.0 | 90.0 |    96.0 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)--写法二：
mysql> select * from exam limit 0,3;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 张三   |    67.0 | 80.0 |    88.0 |
|    2 | 李四   |    87.0 | 55.0 |    66.0 |
|    3 | 小五   |    91.0 | 90.0 |    96.0 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)--写法三：
mysql>  select * from exam limit 3 offset 0;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 张三   |    67.0 | 80.0 |    88.0 |
|    2 | 李四   |    87.0 | 55.0 |    66.0 |
|    3 | 小五   |    91.0 | 90.0 |    96.0 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)

每一页起始位置的计算公式:s=(当前页号-1)*每页显示的记录数；

3.✨修改（update）

语法：

UPDATE table_name SET column = expr [, column = expr ...][WHERE ...] [ORDER BY ...] [LIMIT ...]

update：表示制定要更新的表；
set：用于指定要更新的列和新值，也可以更新多个列，用逗号隔开；
where：可选句子，用于指定更新的条件。如果不使用where句子，那么将更新所有行。

示例：

--更新单条数据：
--将编号为1的同学姓名更改为王五
mysql> update exam set name = '王五' where id=1;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0--更新多条记录
--将语文成绩小于60的同学成绩加上0.6：
mysql>  update exam set chinese=chinese+0.7 where chinese<60;
Query OK, 4 rows affected (0.06 sec)
Rows matched: 4  Changed: 4  Warnings: 0--不加where：
--将所有同学的数学成绩加上0.5
mysql>  update exam set math=math+0.5 ;
Query OK, 9 rows affected (0.10 sec)
Rows matched: 9  Changed: 9  Warnings: 0mysql>  select * from exam;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 王五      |    67.0 | 80.5 |    88.0 |
|    2 | 李四      |    87.0 | 55.5 |    66.0 |
|    3 | 小五      |    91.0 | 90.5 |    96.0 |
|    4 | 小六      |    48.7 | 32.5 |    55.0 |
|    5 | 赵六      |    91.0 | 90.5 |    96.0 |
|    6 | 老王      |    23.7 | 36.5 |    NULL |
|    7 | 老六      |    33.7 | 26.5 |    66.0 |
|    8 | 孙武      |    77.0 | 88.5 |    NULL |
|    8 | 老小四    |    22.7 | 87.5 |    92.0 |
+------+-----------+---------+------+---------+
9 rows in set (0.00 sec)

4.✨删除（delete）

语法：

delete from table_name [where...][order by...][limit...];

delete from:指定要删除的表；
where：可选句子，用于指定删除的条件。如果不使用where句子，那么将删除所有数据。

示例：

--先查询语文成绩小于60的同学：
mysql> select * from exam where chinese<60;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    4 | 小六      |    48.7 | 32.5 |    55.0 |
|    6 | 老王      |    23.7 | 36.5 |    NULL |
|    7 | 老六      |    33.7 | 26.5 |    66.0 |
|    8 | 老小四    |    22.7 | 87.5 |    92.0 |
+------+-----------+---------+------+---------+
4 rows in set (0.00 sec)--将语文成绩小于60的同学进行排序，删除两条记录：
mysql>  delete from exam where chinese<60 order by chinese asc limit 2;
Query OK, 2 rows affected (0.08 sec)----删除后语文成绩小于60的同学：
mysql> select * from exam where chinese<60;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    4 | 小六   |    48.7 | 32.5 |    55.0 |
|    7 | 老六   |    33.7 | 26.5 |    66.0 |
+------+--------+---------+------+---------+
2 rows in set (0.00 sec)

基本查询差不多就到这里就完结啦🌹🌹🌹