MySQL-CRUD入门2

数据的查询(补充)

这一节接着写, 包括数据的查询(补充), 数据的更新, 数据的删除

条件查询

其实就是根据给定的一些条件, 然后过滤掉不符合实际情况的记录, 把符合条件的记录返还给用户, 执行这些操作是通过一些运算符, 比如说比较运算符, 逻辑运算符, 和Java中的思路的是一样的

运算符	说明
> , >= , < , <=	大于, 大于等于, 小于, 小于等于
=	等于, NULL不安全, 如NULL= NULL的结果就是NULL(参加运算)
<=>	等于, NULL安全, NULL <=> NULL 的结果是1(表示真)
!=, <>	不等于, NULL不安全
betweed m and n	范围匹配, [m, n], 如果m <= value <= n, 返回1(表示真)

小练习(关于NULL) :

-- 进行简单的select操作不需要使用数据库
select NULL > 1;
select NULL = NULL;
select NULL <=> NULL;
select not (NULL = NULL);
select not (NULL <=> NULL);
select NULL <> NULL;-- 执行结果 : NULL, NULL, 1, NULL, 0, NULL
-- 总结就是 : 
关于NULL的数学运算结果都是NULL
关于NULL的含有安全等于的运算是有一定意义的

运算符	说明
in (option,…)	如果是option中的任意一个返回1(表示真)
is null	是NULL
is not null	不是NULL
like	模糊查询, %表示任意个(包括0个)任意字符; _表示任意一个字符

小练习 :

-- 进行简单的select操作不需要使用数据库
select 1 in (1, 2, 3);
select 5 in (1, 2, 3);
select NULL is null;
select NULL is not null;
-- 查询结果是 1, 0, 1, 0-- 关于模糊查询的操作就需要使用数据库了
use sel_test;
select * from exam where name like '%孙%';
-- 上面的查找的是名字中有 '孙' 这个字的信息
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|    6 | 孙权      |    70.0 | 73.0 |    78.5 |
+------+-----------+---------+------+---------+
select * from exam where name like '孙_';
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    6 | 孙权   |    70.0 | 73.0 |    78.5 |
+------+--------+---------+------+---------+-- 之前我们查看当前数据库的字符集的时候用的下面这个指令就是模糊查询(下面一致)
show variables like '%character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8mb4                                                 |
| character_set_connection | utf8mb4                                                 |
| character_set_database   | utf8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8mb4                                                 |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8mb3                                                 |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
show variables like '%charac%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8mb4                                                 |
| character_set_connection | utf8mb4                                                 |
| character_set_database   | utf8mb4                                                 |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8mb4                                                 |
| character_set_server     | utf8mb4                                                 |
| character_set_system     | utf8mb3                                                 |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+

运算符	说明
and(&&)	多个条件必须都为true(1), 结果才是true(1)
or(l l)	任意一个条件为true(1), 结果就是true(1)
not	条件为true(1), 结果为false(0), 类似于Java中!
特殊说明一下, 逻辑运算符的优先级不建议记忆, 建议使用的时候还是通过小括号的方式, 手动的去指定优先级, 使用where条件的语法

select * from 表名 where 列名/表达式 运算符 条件;

首先展示一下我们的数据中的数据(等会测试验证)

select * from exam;
+------+--------------+---------+------+---------+
| id   | name         | chinese | math | english |
+------+--------------+---------+------+---------+
|    1 | 唐三藏       |    67.0 | 98.0 |    56.0 |
|    2 | 孙悟空       |    87.5 | 78.0 |    77.0 |
|    3 | 猪悟能       |    88.0 | 98.0 |    90.0 |
|    4 | 曹孟德       |    82.0 | 84.0 |    67.0 |
|    5 | 刘玄德       |    55.5 | 85.0 |    45.0 |
|    6 | 孙权         |    70.0 | 73.0 |    78.5 |
|    7 | 宋公明       |    75.0 | 65.0 |    30.0 |
|    8 | 齐天大圣     |    87.5 | 78.0 |    77.0 |
|    9 | 孙行者       |    NULL | 84.0 |    83.5 |
|   10 | 宋江         |    76.0 | NULL |    77.0 |
|   11 | 李逵         |    67.0 | 90.0 |    NULL |
+------+--------------+---------+------+---------+

练习
需求1 : 找到英语成绩不及格的同学(english < 60)

mysql> select * from exam where english < 60;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|    5 | 刘玄德    |    55.5 | 85.0 |    45.0 |
|    7 | 宋公明    |    75.0 | 65.0 |    30.0 |
+------+-----------+---------+------+---------+
因为 NULL < 60 不论什么时候都是不成立的, 所以直接过滤, 留下三条记录

需求2 : 找打数学成绩好于英语成绩的同学(math > english)

mysql> select * from exam where math > english;
+------+--------------+---------+------+---------+
| id   | name         | chinese | math | english |
+------+--------------+---------+------+---------+
|    1 | 唐三藏       |    67.0 | 98.0 |    56.0 |
|    2 | 孙悟空       |    87.5 | 78.0 |    77.0 |
|    3 | 猪悟能       |    88.0 | 98.0 |    90.0 |
|    4 | 曹孟德       |    82.0 | 84.0 |    67.0 |
|    5 | 刘玄德       |    55.5 | 85.0 |    45.0 |
|    7 | 宋公明       |    75.0 | 65.0 |    30.0 |
|    8 | 齐天大圣     |    87.5 | 78.0 |    77.0 |
|    9 | 孙行者       |    NULL | 84.0 |    83.5 |
+------+--------------+---------+------+---------+

需求三 : 查询总分在200以下的同学(结果降序排列)

select id, name, chinese + math + english 
as '总分' from exam where chinese + math + english < 200 
order by '总分' desc;
+------+-----------+--------+
| id   | name      | 总分   |
+------+-----------+--------+
|    5 | 刘玄德    |  185.5 |
|    7 | 宋公明    |  170.0 |
+------+-----------+--------+

思考上面的代码, 如果把where中的chinese + math + english替换为’总分’可以么 ?

关于SQL语句的执行顺序

主要就是下面几个点的执行顺序

1. select 2. from 3. where 4. order by

关于这个问题, 我有一套自己的逻辑, 我们可以把一个数据库类比为一个数据池, 我们想要从中筛选指定的数据, 首先要搬出来数据池, 也就是from, 那么就需要一个滤网去过滤, 也就是where, 筛选出来指定的数据之后就select把数据选到结果集, 最后再次进行排序(order by)
也就是首先是from, 然后是where, 其次是select, 最后是order by

where中的别名问题
由上面的结论可以得到, 如果在where中通过别名进行筛选的话, 就不会筛选出来指定的结果, 但是有一个例外, 就是当别名通过引号进行标注的话, 就可以正常的执行

但是也会生成一个警告

分页查询(LIMIT)

限制结果集中的数据的条数, 在上一节我们说过如果不对数据的条数进行限制, 是一个很危险的操作, 所以就出现了分页查询的操作, 分页查询在实际的项目中应用是十分的频繁的, 只要是多条数据的集合, 一般都是用分页进行查询, 分页查询的优点也是很明显的, 可以有效的控制一次查询出来的结果集记录的条数, 减小数据库的压力, 同时对用户也是十分友好的

基础语法

-- 第一种分页语法(从索引为0的位置开始查询n条记录, 索引的起始位置是0)
select[字段]from[表名]where[查询条件]order by[字段][asc|desc]limit n;
-- 第二种分页语法(从索引为s的位置开始查询n条记录)
select[字段]from[表名]where[查询条件]order by[字段][asc|desc]limit s,n;
-- 第三种分页语法(跟第二种一样但是语法更加严密)
select[字段]from[表名]where[查询条件]order by[字段][asc|desc]limit n offset s;

假设上面的数据库我们要用分页查询法, 每4个是一页, 按照id排序

-- 分页的偏移量是 (页数 - 1) * 每页数据量
mysql> select * from exam order by id limit 4 offset 0;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|    3 | 猪悟能    |    88.0 | 98.0 |    90.0 |
|    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
+------+-----------+---------+------+---------+
4 rows in set (0.00 sec)mysql> select * from exam order by id limit 4 offset 4;
+------+--------------+---------+------+---------+
| id   | name         | chinese | math | english |
+------+--------------+---------+------+---------+
|    5 | 刘玄德       |    55.5 | 85.0 |    45.0 |
|    6 | 孙权         |    70.0 | 73.0 |    78.5 |
|    7 | 宋公明       |    75.0 | 65.0 |    30.0 |
|    8 | 齐天大圣     |    87.5 | 78.0 |    77.0 |
+------+--------------+---------+------+---------+
4 rows in set (0.00 sec)mysql> select * from exam order by id limit 4 offset 8;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    9 | 孙行者    |    NULL | 84.0 |    83.5 |
|   10 | 宋江      |    76.0 | NULL |    77.0 |
|   11 | 李逵      |    67.0 | 90.0 |    NULL |
+------+-----------+---------+------+---------+
3 rows in set (0.00 sec)

现在分页查询需求是按照总分进行排序(每一页四条数据)

mysql> select id, name, chinese + math + english as '总分' from exam order by '总分' desc limit 4 offset 0;
+------+-----------+--------+
| id   | name      | 总分   |
+------+-----------+--------+
|    1 | 唐三藏    |  221.0 |
|    2 | 孙悟空    |  242.5 |
|    3 | 猪悟能    |  276.0 |
|    4 | 曹孟德    |  233.0 |
+------+-----------+--------+
4 rows in set (0.00 sec)mysql> select id, name, chinese + math + english as '总分' from exam order by '总分' desc limit 4 offset 4;
+------+--------------+--------+
| id   | name         | 总分   |
+------+--------------+--------+
|    5 | 刘玄德       |  185.5 |
|    6 | 孙权         |  221.5 |
|    7 | 宋公明       |  170.0 |
|    8 | 齐天大圣     |  242.5 |
+------+--------------+--------+
4 rows in set (0.00 sec)mysql> select id, name, chinese + math + english as '总分' from exam order by '总分' desc limit 4 offset 8;
+------+-----------+--------+
| id   | name      | 总分   |
+------+-----------+--------+
|    9 | 孙行者    |   NULL |
|   10 | 宋江      |   NULL |
|   11 | 李逵      |   NULL |
+------+-----------+--------+
3 rows in set (0.00 sec)

思考, 我们的null进行运算完之后数据都是null, 但现在我们的需求是如果是null默认为0, 那就要用到我们ifnull函数了, 关于函数我们会详细讲解, 这里简单说一下

select id, name, ifnull(chinese, 0) + ifnull(math, 0) + ifnull(english, 0) as '总分' from exam order by '总分' desc limit 4 offset 0;select id, name, ifnull(chinese, 0) + ifnull(math, 0) + ifnull(english, 0) as '总分' from exam order by '总分' desc limit 4 offset 4;select id, name, ifnull(chinese, 0) + ifnull(math, 0) + ifnull(english, 0) as '总分' from exam order by '总分' desc limit 4 offset 8;

最后一个SQL语句执行结果就会按照需求进行调整

+------+-----------+--------+
| id   | name      | 总分   |
+------+-----------+--------+
|    9 | 孙行者    |  167.5 |
|   10 | 宋江      |  153.0 |
|   11 | 李逵      |  157.0 |
+------+-----------+--------+

数据的修改

数据修改基础知识

也就是CRUD中的U(update), 更新某一张表中的数据, 这个才是真正意义上的’更改器’
基础语法

update [表名] set [字段1] = [期望值] ... where 筛选条件 order by 排序规则 limit..

需求1 : 将孙悟空同学的数学成绩变为80分

Rows matched 指的是匹配到了多少行, Changed 是指真正修改的数据行数

mysql> select * from exam where name = '孙悟空';
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    2 | 孙悟空    |    87.5 | 80.0 |    77.0 |
+------+-----------+---------+------+---------+

需求2 : 将孙悟空同学的语文成绩加20分

注意这里不可以使用math += 10, mysql不支持这种操作

mysql> select * from exam where name = '孙悟空';
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    2 | 孙悟空    |    97.5 | 80.0 |    77.0 |
+------+-----------+---------+------+---------+

需求3 : 把总分排名倒数前3的人的数学成绩加上10分

 update exam set math = math + 10 where math is not null order by math + chinese + english asc limit 3;