本篇是继上篇的下篇,如果上篇没有看过小伙伴,可以先看看我的上一篇再来看一下这一篇【MySQL】表的增删查改(CRUD)(上)
条件查询:允许用户在查询语句中指定筛选条件,数据库中会根据这些条件从表中筛选出符合条件的数据,将满足条件的记录返回给用户,不满足的条件的排出在外。
比较运算符:
运算符 | 说明 |
---|---|
>,>=,<,<= | 大于,大于等于,小于,小于等于 |
= | 等于,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) |
示例:
表中数据:
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)
为什么where不能使用别名? 这与MySQL执行SQL语句有关 执行顺序:
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)
范围查询
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)
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)
--查找老开头的名字(%):
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)
--查找英语成绩为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)
概念:
分页查询:用于将大量数据按照一定的大小进行划分,以便逐页获取和展示数据。
语法:
--从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:
--写法一:
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)
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]
示例:
--更新单条数据:
--将编号为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: 0
mysql> 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)
delete from table_name [where...][order by...][limit...];
示例:
--先查询语文成绩小于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)
基本查询差不多就到这里就完结啦🌹🌹🌹