MySQL运算符

MySQL 运算符

MySQL 运算符主要包括 3 大类：比较运算符、算术运算符、逻辑运算符

算术运算符

加 + 、减 - 、乘 * 、除 / 、求余 %

特殊操作

mysql> select 1+2;
+-----+
| 1+2 |
+-----+
| 3 |
+-----+
1 row in set (0.00 sec)
mysql> select 1/2;
+--------+
| 1/2 |
+--------+
| 0.5000 |
+--------+
1 row in set (0.00 sec)
mysql> select 5%2;
+------+
| 5%2 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql> select '5a'+2;
+--------+
| '5a'+2 |
+--------+
| 7 |
+--------+
1 row in set, 1 warning (0.00 sec)
mysql> select 'a5'+2;
+--------+
| 'a5'+2 |
+--------+
| 2 |
+--------+
1 row in set, 1 warning (0.00 sec)
mysql> select 123.45%2.52;
+-------------+
| 123.45%2.52 |

运算符

语法

说明

=

a=b

如果参与计算的两个操作数相等则为 true ，否则 false

!= 或者 <>

a!=b 或者 a<>b

如果两个操作数不相等则 true[1] ，否则 false[0]

<

a<b

如果 a 小于 b 则返回 true ，否则 false

>

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 and

age!=15

查询孙权、黄盖、张三以及李四同学的成绩值

-- 查询张三以及李四同学的成绩

select score from tb_student where name=' 张三 ' or name=' 李四 '

select score from tb_stuent where name in(' 张三 ',' 李四 ') -- in 中的数据个数没有限制

between/and

用于判断数据是否在指定的范围内，连续值

查询成绩及格并且不属于优秀的所有学生信息

+-------------+
| 2.49 |
+-------------+
1 row in set (0.00 sec)
mysql> select -123.45%2.52;
+--------------+
| -123.45%2.52 |
+--------------+
| -2.49 |
+--------------+
1 row in set (0.00 sec)

-- 写法1：使用条件拼接
select * from tb_student where score>=60 and score<=85;
-- 写法2
select * from tb_student where score between 60 and 85;
--如果需要的是不在指定范围内部
select * from tb_student where score not between 50 and 85;

语法

说明

&& 或者

and

a and b 或者

a&&b

逻辑与，如果参与计算的两个操作数为 true ，否则 false

|| 或者 or

a or b 或者 a||b

逻辑或，如果参与计算的双反，只要一方为 false ，则返回

false

not 或者 !

not a 或者 !a

逻辑非，如果操作数为 false 则结果为 true

like/not like

主要针对字符串类型数据进行模糊查询，通配符 _ 和 %

查询不姓张的同学

regexp 是在 mysql 中针对字符串类型进行正则式进行判断， not regexp

<=> 如果两数相同为 true ，即使是 null

is null/is not null

判断是否为空，为空则返回 true

逻辑运算符

练习题

样例数据表

查询全体学员的姓名及其出生年份

查询李姓学员的姓名及其出生年份

查询年龄在 18-23 岁之间的学生姓名、系别和年龄

select * from tb_student where name not like '张%'
1
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;
1
select name, 2022-age as birth_year from tb_student where name like '李%';

1 查询年龄不在 18-23 岁之间的学生姓名、系别和年龄

查询软工、机壳和大数据系的所有学生姓名和性别

查询既不是软工、机壳，也不是大数据系的所有学生姓名和性别

查询张 xx 学生的姓名和年龄

查询名字中有方的学生信息

查询 age 确定的学生信息

注意 =null 或者 !=null 都是错误的，应该使用 is null 或者 is not null

查询性别不确定的学生信息

查询学生所在的系别信息

distinct 自动去除重复值， all 显示所有数据

查询系别和学生年龄信息

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)
1
2
3
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;
1
2
select name,sex from tb_student where dept in('软工','机壳','大数据');
select name,sex from tb_student where dept='软工' or dept='机壳' or dept='大数
据';
1
2
select name,sex from tb_student where dept not in('软工','机壳','大数据');
select name,sex from tb_student where dept!='软工' and dept!='机壳' and
dept!='大数据';
1
2
select name,age from tb_student where name like '张__';
select name,age from tb_student where name like '张%' and length(name)=3; --
length针对中文的处理为获取其中所占字节数，不是字符数
1
2
select * from tb_student where name like '%方%';
1
select * from tb_student where age is not null;
1
select * from tb_student where sex is null;
1
select all dept from tb_student; -- all不会去除重复值，默认all
select distinct dept from tb_student;
1
2
mysql> select * from tb_student;
+----+--------+------+------+--------+
1

2 问题：如果 auto_increment 到达上限时 MySQL 是如何处理的

auto_increment 默认从 1 开始，上限取决于列的数据类型。如果表中 id 列已经有指定的值，则 max(id)+1

如果删除数据后，数据库系统仍旧会记录已经生成过的数据，不会从新开始，而是在以前的基础上继续

加1
| id | name | age | sex | dept |
+----+--------+------+------+--------+
| 1 | 小胖 | 18 | 1 | 软工 |
| 2 | 东方 | 16 | 0 | 机壳 |
| 3 | 仗义 | 19 | 1 | 大数据 |
| 4 | 张骞 | 16 | 1 | 软工 |
| 5 | 张小骞 | 16 | 1 | 软工 |
+----+--------+------+------+--------+
5 rows in set (0.00 sec)
试图取消软工 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;

create table t1(
id int primary key auto_increment,
name varchar(20)
);
insert into t1 values(2147483646,'zhangsan');
insert into t1(name) values('lisi');
mysql> select * from t1;
+------------+----------+
| id | name |
+------------+----------+
| 2147483646 | zhangsan |
| 2147483647 | lisi |
+------------+----------+
2 rows in set (0.00 sec)
insert into t1(name) values('wangwu');
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY' -- 意思是
再次生成了最大的int值2147483647作为主键，此时主键冲突

如果需要重新开始生成连续整数，只能将表中所有数据删除

建议：因为 auto_increment 生成数据是从 1 开始，不会出现负整数，所以一般建议使用 bigint unsigned

5 类聚集函数

聚集函数用于对于一个集合中的数据进行处理，不是一行一行的数据

count 统计行数、 sum 求和、 max 最大值、 min 最小值、 avg 平均值

计数

语法 count([all/distinct] 列名称 /*)

获取总学生数

需要统计选修 1 号课程的学生人数

mysql> delete from t1 where name='lisi'; -- 2147483647
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------------+----------+
| id | name |
+------------+----------+
| 2147483646 | zhangsan |
+------------+----------+
1 row in set (0.00 sec)
mysql> insert into t1(name) values('wangwu'); -- 2147483647
Query OK, 1 row affected (0.00 sec)

truncate table t1; -- 删除整表数据，不能使用delete from
1
select count(*) from tb_student;
select count(1) from tb_student;
select count(distinct dept) from tb_student; -- 获取系的个数
mysql> select * from tb_student;
+----+--------+------+------+--------+
| id | name | age | sex | dept |
+----+--------+------+------+--------+
| 1 | 小胖 | 18 | 1 | 软工 |
| 2 | 东方 | 16 | 0 | 机壳 |
| 3 | 仗义 | 19 | 1 | 大数据 |
| 4 | 张骞 | 16 | 1 | 软工 |
| 5 | 张小骞 | 16 | 1 | 软工 |
| 6 | 从来 | 16 | 1 | NULL |
+----+--------+------+------+--------+
6 rows in set (0.00 sec)
select count(dept) from tb_student; -- 进行计数统计时，null不参与统计

学生编号

课程编号

成绩

1 3 451 3 70

假设选择表中需要记录补考成绩，也就是意味着课程会有重复，则问题 1 ： primary key(cid,sid) 就是错误

的，

由于出现重复计数，则选修人数统计出错

统计值

用于数值类型的列，不要用于其他类型。 max min sum avg

max/min 可以用于日期类型比较，最新的日期最大

select count(*) from tb_choice where cid=1;
1
select count(distinct sid) from tb_choice where cid=1;
1
ysql> select * from tb_student;
+----+--------+------+------+--------+
| id | name | age | sex | dept |
+----+--------+------+------+--------+
| 1 | 小胖 | 18 | 1 | 软工 |
| 2 | 东方 | 16 | 0 | 机壳 |
| 3 | 仗义 | 19 | 1 | 大数据 |
| 4 | 张骞 | 16 | 1 | 软工 |
| 5 | 张小骞 | 16 | 1 | 软工 |
| 6 | 从来 | 16 | 1 | NULL |
| 7 | 张展 | 16 | 1 | NULL |
+----+--------+------+------+--------+
7 rows in set (0.00 sec)
mysql> select max(age) from tb_student;
+----------+
| max(age) |
+----------+
| 19 |
+----------+
1 row in set (0.00 sec)
mysql> select min(age) from tb_student;
+----------+
| min(age) |
+----------+
| 16 |
+----------+
1 row in set (0.00 sec)
mysql> select avg(age) from tb_student; -- null值不参与计算，除非使用空值处理函数
将其转换为确定数值才可以
+----------+
| avg(age) |

查询选修 1 号课程的学生最高成绩和平均成绩

问题

select count(*) 、 select count(1) 和 select count( 列名称 )

列名为主键 count( 列名 ) 比 count(1) 速度快；如果列名不是主键，则 count(1) 比 count( 列名 )

快 如果表多个列并且没有主键，则 count(1) 比 count(*) 速度快，如果有主键 `count( 主键列名 ) 速

度最快，如果表中只有一个列 count(*) 速度最快

count(1) 会统计表中的所有记录数，包括字段为 null 的记录； count( 列名称 ) 则列为 null 时不统计

MySQL 针对不同的存储引擎进行了优化处理， MyISAM 会将表的总行数记录下来，供 count(*) 查

询使用； Innodb 则会在扫描表时选择最小的索引成本执行，所以在 Innodb 中 count(*) 和

count(1) 实质上没有区别，而且执行效率一致，只有 count( 列名称 ) 需要进行 null 值列的判断，

所以效率低一些

对查询结果分组

可以使用 group by 子句对查询结果进行分组处理，经常会使用聚集函数 如果不使用分组，聚集函数则用于处理所有查询结果数据 如果使用分组，则分别作用于各个分组查询的结果数据

获取男女生的平均年龄

按照性别进行分组 group by sex ，不同的 sex 值则放入不同的组中

平均值就是聚集函数，针对一个集合中的数据进行处理

+----------+
| 16.7143 |
+----------+
1 row in set (0.00 sec)
mysql> select sum(age) from tb_student;
+----------+
| sum(age) |
+----------+
| 101 |
+----------+
1 row in set (0.00 sec)

select max(score),avg(score) from tb_choice where cid=1;
mysql> select sum(age),max(age),min(age),avg(age) from tb_student;
+----------+----------+----------+----------+
| sum(age) | max(age) | min(age) | avg(age) |
+----------+----------+----------+----------+
| 101 | 19 | 16 | 16.8333 |
+----------+----------+----------+----------+
1 row in set (0.00 sec)

注意：如果在 select 之后不在聚集函数中的列名称一定出现在 group by 之后，否则语法错误

having 可以对分组进行条件选择

需要获取人数多余 2 人的不同性别学生的平均年龄

按照性别分组

要统计的组必须人数多余 2 人，小于等于 2 人的分组不进行显示处理

练习题 1 ：查询选修了 3 门以上课程的学生学号

按照学生学号分组

只处理分组中数据行数大于 3 的分组，如果分组中数据行数不足 3 则不处理

练习题 2 ：查询有 3 门课程是 90 分以上的学生的学号及 90 分以上的课程数

需要分组处理的数据必须是 90 分以上的数据

针对学号进行分组

分组中课程数应该大于等于 3 门

having 和 where 之间的区别

只有满足条件的数据才会输出显示

最大区别在于：作用的对象不同

where 子句用于基表或者视图，从中选择满足条件的元组

having 子句用于分组，从多个分组中选择满足条件的分组

排序处理

SQL 中可以针对查询结果进行排序

语法 select ... from ... order by 列名称 1 [asc/desc] ，列名称 2 [asc/desc],...

mysql> select sex,avg(age) from tb_student group by sex;
+------+----------+
| sex | avg(age) |
+------+----------+
| 1 | 17.0000 |
| 0 | 16.0000 |
| NULL | 16.0000 |
+------+----------+
3 rows in set (0.00 sec)

9
select sex,avg(age) from tb_student group by sex having count(1)>2;
1
select sid from tb_choice group by sid having count(*)>3;
1
select sid, count(*) from tb_choice
where score>=90
group by sid having count(cid) >=3;
1

2

3 排序过程中首先按照列名称 1 进行排序，如果列 1 对应值相等时，才按照列名称 2 对应列值进行排序

注意默认按照指定列的自然序排序，如果需要倒序则使用关键字 desc ， asc 是正序

按照学生年龄从大到小输出学生信息

练习

select * from tb_student order by age desc;
1
mysql> select * from tb_student order by age desc;
+----+--------+------+------+--------+
| id | name | age | sex | dept |
+----+--------+------+------+--------+
| 3 | 仗义 | 19 | 1 | 大数据 |
| 1 | 小胖 | 18 | 1 | 软工 |
| 2 | 东方 | 16 | 0 | 机壳 |
| 4 | 张骞 | 16 | 1 | 软工 |
| 5 | 张小骞 | 16 | 1 | 软工 |
| 6 | 从来 | 16 | 1 | NULL |
| 8 | 张展 | 16 | 0 | NULL |
+----+--------+------+------+--------+
7 rows in set (0.00 sec)
mysql> select * from tb_student order by age;
+----+--------+------+------+--------+
| id | name | age | sex | dept |
+----+--------+------+------+--------+
| 2 | 东方 | 16 | 0 | 机壳 |
| 4 | 张骞 | 16 | 1 | 软工 |
| 5 | 张小骞 | 16 | 1 | 软工 |
| 6 | 从来 | 16 | 1 | NULL |
| 8 | 张展 | 16 | 0 | NULL |
| 1 | 小胖 | 18 | 1 | 软工 |
| 3 | 仗义 | 19 | 1 | 大数据 |
+----+--------+------+------+--------+
7 rows in set (0.00 sec)
mysql> select * from tb_student order by age desc,id desc;
+----+--------+------+------+--------+
| id | name | age | sex | dept |
+----+--------+------+------+--------+
| 3 | 仗义 | 19 | 1 | 大数据 |
| 1 | 小胖 | 18 | 1 | 软工 |
| 8 | 张展 | 16 | 0 | NULL |
| 6 | 从来 | 16 | 1 | NULL |
| 5 | 张小骞 | 16 | 1 | 软工 |
| 4 | 张骞 | 16 | 1 | 软工 |
| 2 | 东方 | 16 | 0 | 机壳 |
+----+--------+------+------+--------+
7 rows in set (0.00 sec)