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运算符时指定的值是离散的数据，不是连续值
  • select * from tb_student where age in(18,28,15)含义是 age=18 or age=28 or age=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;

-- 写法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 '张%'

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,'z1',18,1,'软工'),(null,'z2',16,0,'计科'),(null,'z3',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 '%方%';
  • 查询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;

试图取消软工 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;
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可以用于日期类型比较，最新的日期最大
select * from tb_student;
select max(age) from tb_student;
select min(age) from tb_student;
select avg(age) from tb_student; -- null值不参与计算，除非使用空值处理函数 将其转换为确定数值才可以
 select sum(age) from tb_student;

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

select max(score),avg(score) from tb_choice where cid=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值则放入不同的组中
  • 平均值就是聚集函数，针对一个集合中的数据进行处理
select sex,avg(age) from tb_student group by sex;

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

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

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

  • 按照性别分组
  • 要统计的组必须人数多余2人，小于等于2人的分组不进行显示处理
select sex,avg(age) from tb_student group by sex having count(1)>2;

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

  • 按照学生学号分组
  • 只处理分组中数据行数大于3的分组，如果分组中数据行数不足3则不处理
select sid from tb_choice group by sid having count(*)>3;

练习题2：查询有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;
练习
select * from tb_student order by age desc;
select * from tb_student order by age;
select * from tb_student order by age desc,id 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)