Mysql学习------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 |
+-------------+ 
| 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)

比较运算符

运算符	语法	说明
=	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运算符时指定的值是离散的数据，不是连续值

1. select * from tb_student where age in(18,28,15)含义是 age=18 or age=28 or age=15
2. select * from tb_student where age not in(18,28,15)含义是 age!=18 and age!=28 and age!=15
3. 查询孙权、黄盖、张三以及李四同学的成绩值

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

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; 

-- 写法2 
select * 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 tb_student where name not like '张%' 1

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 null ）

select * 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; 
+----+--------+------+------+--------+
| 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;

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

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

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作为主键，此时主键冲突

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

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

5类聚集函数

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

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

计数

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

获取总学生数

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 号课程的学生人数

select count(*) from tb_choice where cid=1;

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

学生编号	课程编号	成绩
1	3	45
1	3	70

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

select count(distinct sid) from tb_choice where cid=1;

统计值

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

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

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) |
+----------+ 
| 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)

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

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 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值则放入不同的组中
• 平均值就是聚集函数，针对一个集合中的数据进行处理

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)

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

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

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

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

select sex,avg(age) from tb_student group by sex having count(1)>2;

查询选修了 3 门以上课程的学生学号

• 按照学生学号分组
• 只处理分组中数据行数大于3的分组，如果分组中数据行数不足3则不处理

select sid from tb_choice group by sid having count(*)>3; 

查询有 3 门课程是 90 分以上的学生的学号及 90 分以上的课程数

• 需要分组处理的数据必须是90分以上的数据
• 针对学号进行分组
• 分组中课程数应该大于等于3门

select sid, count(*) from tb_choice where 
        score>=90 group by sid having count(cid) >=3;

having 和 where 之间的区别

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

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

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

排序处理

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

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

• 排序过程中首先按照列名称1进行排序，如果列1对应值相等时，才按照列名称2对应列值进行排序
• 注意默认按照指定列的自然序排序，如果需要倒序则使用关键字desc，asc是正序

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

select * from tb_student order by age desc;

练习

定义 学生 ( 学生编号，姓名、性别 ) ，课程（课程编号、课程名称），选修 ( 学生编号，课程编号，成绩 )

create table if not exists tb_student( 
       id bigint primary key auto_increment, 
       name varchar(32) not null, 
       sex boolean default 1 
)engine=innodb default charset utf8; 

create table if not exists tb_course( 
       id bigint primary key auto_increment, 
       title varchar(32) not null 
)engine=innodb default charset utf8; 

create table if not exists tb_choice( 
       sid bigint not null, -- 如果需要进行数据等值判定时，最好数据类型一致 
       cid bigint not null, 
       score numeric(4,1), 
       primary key(sid,cid) 
)engine=innodb default charset utf8;

数据比较

如果一个表中的数据和另外一个表中的数据相等时，最好保证数据类型一致

mysql> select '1'=1; -- 进行比较时，如果参与比较的一方是数值类型时，字符串会自动转换为 
数值进行比较，所以'1a'=1返回为true 
+-------+ 
| '1'=1 | 
+-------+ 
| 1 | 
+-------+ 
1 row in set (0.00 sec) 

mysql> select 'a'='A'; -- 进行字符串比较时，不区分大小写 
+---------+ 
| 'a'='A' | 
+---------+ 
| 1 | 
+---------+ 
1 row in set (0.00 sec)