CRUD MySQL数据库必会的增删查改操作( 四 )
逻辑运算符：and(&&), or(||), not(!) 。
例如查询数学不及格同学名单：
mysql> select * from exam_score where math < 60;+------+-----------+---------+------+---------+----------+| id| name| chinese | math | english | computer |+------+-----------+---------+------+---------+----------+|2 | 懒羊羊|58.5 | 32.5 |24.0 |66.5 ||8 | 黑大帅|10.0 | 11.0 |9.0 |20.0 |+------+-----------+---------+------+---------+----------+2 rows in set (0.00 sec)查询英语成绩大于计算机综合的同学：
mysql> select name, english, computer from exam_score where english > computer;+-----------+---------+----------+| name| english | computer |+-----------+---------+----------+| 美羊羊|98.0 |82.0 || 暖羊羊|98.0 |76.0 |+-----------+---------+----------+2 rows in set (0.00 sec)查询总分在300分以下的同学：
mysql> select name, chinese+math+english+computer as total from exam_score where chinese+math+english+computer < 300;+-----------+-------+| name| total |+-----------+-------+| 懒羊羊| 181.5 || 灰太狼| 234.5 || 黑大帅|50.0 |+-----------+-------+3 rows in set (0.00 sec)注意使用别名时 ， 条件表达式不能使用别名判断 ， 否则会报错：
mysql> select name, chinese+math+english+computer as total from exam_score where total < 300;ERROR 1054 (42S22): Unknown column 'total' in 'where clause'查询语文数学均大于等于90分的同学：
mysql> select name, chinese, math from exam_score where chinese >= 90 and math >= 90;+-----------+---------+------+| name| chinese | math |+-----------+---------+------+| 美羊羊|99.5 | 90.5 || 喜羊羊|92.0 | 98.0 || 暖羊羊|90.0 | 91.0 |+-----------+---------+------+3 rows in set (0.00 sec)查询语数英中有大于等于95分的同学：
mysql> select name, chinese, math, english from exam_score where chinese >= 95 or math >= 95 or english >= 95;+-----------+---------+------+---------+| name| chinese | math | english |+-----------+---------+------+---------+| 美羊羊|99.5 | 90.5 |98.0 || 喜羊羊|92.0 | 98.0 |88.0 || 暖羊羊|90.0 | 91.0 |98.0 |+-----------+---------+------+---------+3 rows in set (0.00 sec)查询语文数学总分大于等于180分或者计算机综合与英语大于85分的同学：
mysql> select name, chinese+math as cmtotal, english, computer from exam_score where chinese+math >= 180 or computer > 85 and english > 85;+-----------+---------+---------+----------+| name| cmtotal | english | computer |+-----------+---------+---------+----------+| 美羊羊|190.0 |98.0 |82.0 || 喜羊羊|190.0 |88.0 |100.0 || 暖羊羊|181.0 |98.0 |76.0 |+-----------+---------+---------+----------+3 rows in set (0.00 sec)从上述代码我们能够看出来and的优先级大于or ， 如果需要打破这个优先级需要使用() 。
查询计算机综合在[80, 90]之间的同学：
mysql> select name, computer from exam_score where computer between 80 and 90;+-----------+----------+| name| computer |+-----------+----------+| 美羊羊|82.0 || 沸羊羊|81.0 || 小灰灰|88.0 |+-----------+----------+-- 等价于mysql> select name, computer from exam_score where computer >= 80 and computer <= 90;+-----------+----------+| name| computer |+-----------+----------+| 美羊羊|82.0 || 沸羊羊|81.0 || 小灰灰|88.0 |+-----------+----------+3 rows in set (0.00 sec)查询计算机综合成绩为82,88,92,98的同学：
mysql> select name, computer from exam_score where computer in(82,88,92,98);+-----------+----------+| name| computer |+-----------+----------+| 美羊羊|82.0 || 小灰灰|88.0 |+-----------+----------+2 rows in set (0.00 sec)-- 等价于mysql> select name, computer from exam_score where computer=82 or computer=88 or computer=92 or computer=98;+-----------+----------+| name| computer |+-----------+----------+| 美羊羊|82.0 || 小灰灰|88.0 |+-----------+----------+2 rows in set (0.00 sec)查找名字含有羊的同学：
mysql> select * from exam_score where name like '%羊';+------+-----------+---------+------+---------+----------+| id| name| chinese | math | english | computer |+------+-----------+---------+------+---------+----------+|1 | 美羊羊|99.5 | 90.5 |98.0 |82.0 ||2 | 懒羊羊|58.5 | 32.5 |24.0 |66.5 ||3 | 喜羊羊|92.0 | 98.0 |88.0 |100.0 ||4 | 沸羊羊|78.0 | 72.0 |74.5 |81.0 ||5 | 暖羊羊|90.0 | 91.0 |98.0 |76.0 |+------+-----------+---------+------+---------+----------+5 rows in set (0.00 sec)这里如果使用
推荐阅读
- 从RDS算不算国产数据库谈起
- SpringBoot WebFlux整合R2DBC实现数据库反应式编程
- mirna(mirna数据库)
- js调用数据库数据 js数据库信息交互
- mysql数据库建表的完整步骤 sqlserver导入excel表格命令
- 更好的MySQL客户端推荐 mysql客户端哪个好用
- 建立数据库的步骤 mysql数据库系统需求分析
- 阿里巴巴|阿里的分布式数据库OceanBase：帮公司省了几百个亿
- 数据库|领航国产分布式数据库！OceanBase3.2版发布：数据分析性能提升6倍
- 微软|微软Azure数据库出现漏洞：波及多家国际品牌 自家服务也难逃