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)