MySQL
运算符主要包括
3
大类:比较运算符、算术运算符、逻辑运算符
算术运算符
加 + 、减 - 、乘 * 、除 / 、求余 %
mysql> select 1+2;
+-----+
| 1+2 |
+-----+
| 3 |
+-----+
mysql> select 1/2;
+--------+
| 1/2 |
+--------+
| 0.5000 |
+--------+
mysql> select 5%2;
+------+
| 5%2 |
+------+
| 1 |
+------+
特殊操作
mysql> select '5a'+2;
+--------+
| '5a'+2 |
+--------+
| 7 |
+--------+
mysql> select 'a5'+2;
+--------+
| 'a5'+2 |
+--------+
| 2 |
+--------+
mysql> select 123.45%2.52;
+-------------+
| 123.45%2.52 |
+-------------+
| 2.49 |
+-------------+
mysql> select -123.45%2.52;
+--------------+
| -123.45%2.52 |
+--------------+
| -2.49 |
+--------------+
比较运算符
运算符 | 语法 | 说明 |
= | a=b |
如果参与计算的两个操作数相等则为
true
,否则
false
|
!=或者<> | a!=b或者a<>b |
如果两个操作数不相等则
true[1]
,否则
false[0]
|
< | a<b |
如果两个操作数不相等则
true[1]
,否则
false[0]
|
> | a>b |
如果
a
大于
b
则
true
|
<= | a<=b |
小于等于
|
>= | a>=b |
大于等于
|
in或者not in
- in用于判断某个列的取值是否为指定的值,使用in运算符时指定的值是离散的数据,不是连续值
- select * from tb_student where age in(18,28,15)含义是 age=18 or age=28 or age=15
-
select * from tb_student where age not in(18,28,15) 含义是 age!=18 and age!=28 andage!=15
-
查询孙权、黄盖、张三以及李四同学的成绩值-- 查询张三以及李四同学的成绩select score from tb_student where name=' 张三 ' or name=' 李四 'select score from tb_stuent where name in(' 张三 ',' 李四 ') -- in 中的数据个数没有限制
between/and
用于判断数据是否在指定的范围内,连续值
- 查询成绩及格并且不属于优秀的所有学生信息
-- 写法1:使用条件拼接
select * from tb_student where score>= 60 and score<= 85 ;-- 写法 2select * from tb_student where score between 60 and 85 ;-- 如果需要的是不在指定范围内部select * from tb_student where score not between 50 and 85 ;
like/not like
主要针对字符串类型数据进行模糊查询,通配符 _ 和 %
查询不姓张的同学
select * from 表名称 where name not like ' 张 %'
regexp是在mysql中针对字符串类型进行正则式进行判断,not regexp
<=> 如果两数相同为true,即使是null
is null/is not null
判断是否为空,为空则返回 true
逻辑运算符
语法
|
说明
| |
&&
或者and
|
a and b
或者 a&&b
|
逻辑与,如果参与计算的两个操作数为
true
,否则
false
|
||
或者
or
|
a or b
或者
a||b
|
逻辑或,如果参与计算的双反,只要一方为
false
,则返回false
|
not
或者
!
|
not a
或者
!a
|
逻辑非,如果操作数为
false
则结果为
true
|
例题:
样例数据表:
create table if not exists tb_student(
id bigint not null auto_increment,
name varchar(32) not null,
age int default 16,
sex boolean default 1,
dept varchar(32),
primary key(id)
) engine=innodb default charset utf8;
-- 插入测试数据
insert into tb_student values(null,'小胖',18,1,'软工'),(null,'东方',16,0,'机
壳'),(null,'仗义',19,1,'大数据');
- 查询全体学员的姓名及其出生年份
select name, 2022-age as birth_year from tb_student;
- 查询李姓学员的姓名及其出生年份
select name, 2022-age as birth_year from tb_student where name like '李%';
-
查询年龄在 18-23 岁之间的学生姓名、系别和年龄
select name,dept,age from tb_student where age>=18 and age<=23 -- 注意两头相等 select name,dept,age from tb_student where age between 18 and 23 --小值在前 select name,dept,age from tb_student where age in(18,23,22,20,21,19)
-
查询年龄不在 18-23 岁之间的学生姓名、系别和年龄
select name,dept,age from tb_student where age<18 or age>23; select name,dept,age from tb_student where age not between 18 and 23;
- 查询软工、机壳和大数据系的所有学生姓名和性别
select name,sex from tb_student where dept in('软工','机壳','大数据'); select name,sex from tb_student where dept='软工' or dept='机壳' or dept='大数 据';
- 查询既不是软工、机壳,也不是大数据系的所有学生姓名和性别
select name,sex from tb_student where dept not in('软工','机壳','大数据'); select name,sex from tb_student where dept!='软工' and dept!='机壳' and dept!='大数据';
- 查询张xx学生的姓名和年龄
select name,age from tb_student where name like '张__'; select name,age from tb_student where name like '张%' and length(name)=3; -- length针对中文的处理为获取其中所占字节数,不是字符数
-
查询名字中有方的学生信息
select * from tb_student where name like '%方%'; 1
-
查询 age 确定的学生信息注意 =null 或者 !=null 都是错误的,应该使用 is null 或者 is not nullselect * from tb_student where age is not null ;
- 查询性别不确定的学生信息
select * from tb_student where sex is null;
-
查询学生所在的系别信息distinct 自动去除重复值, all 显示所有数据
select all dept from tb_student; -- all不会去除重复值,默认all select distinct dept from tb_student;
-
查询系别和学生年龄信息
mysql> select * from tb_student; 试图取消软工 16这条数据的重复 -- 语法错误 mysql> select distinct dept,distinct age from tb_student; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct age from tb_student' at line 1 --正确的写法 select distinct dept,age from tb_student;
如果需要重新开始生成连续整数,只能将表中所有数据删除
truncate table t1; -- 删除整表数据,不能使用 delete from
建议:因为auto_increment生成数据是从1开始,不会出现负整数,所以一般建议使用bigint unsigned