MySQL运算符
MySQL运算符主要包括三大类:比较运算符、算术运算符、逻辑运算符
算术运算符
加+、减-、乘*、除/、求余
mysql> select 1+2;
+-----+
| 1+2 |
+-----+
| 3 |
+-----+
1 row in set (0.01 sec)
mysql> select 1/2;
+--------+
| 1/2 |
+--------+
| 0.5000 |
+--------+
1 row in set (0.01 sec)
mysql> select 5%5;
+------+
| 5%5 |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
mysql> select 5*5;
+-----+
| 5*5 |
+-----+
| 25 |
+-----+
1 row in set (0.00 sec)
mysql> select 10/2;
+--------+
| 10/2 |
+--------+
| 5.0000 |
+--------+
1 row in set (0.00 sec)
特殊操作
mysql> select 'a'+2;
+-------+
| 'a'+2 |
+-------+
| 2 |
+-------+
1 row in set, 1 warning (0.01 sec)
mysql> select '3a'+2;
+--------+
| '3a'+2 |
+--------+
| 5 |
+--------+
1 row in set, 1 warning (0.00 sec)
mysql> select 'a4'+2;
+--------+
| 'a4'+2 |
+--------+
| 2 |
+--------+
1 row in set, 1 warning (0.00 sec)
mysql> select 100.123%50.1;
+--------------+
| 100.123%50.1 |
+--------------+
| 50.023 |
+--------------+
1 row in set (0.01 sec)
mysql> select 112.123%50.1;
+--------------+
| 112.123%50.1 |
+--------------+
| 11.923 |
+--------------+
1 row in set (0.00 sec)
mysql> select -112.123%50.1;
+---------------+
| -112.123%50.1 |
+---------------+
| -11.923 |
+---------------+
1 row in set (0.01 sec)
比较运算符
运算符 | 语法 | 说明 |
---|---|---|
= | a=b | 如果参与计算的两个操作数相等则为true,否则false |
!=或者<> | a!=b或者a<>b | 如果两个操作数不相等则true[1],否则false[0] |
< | a<b | 如果a小于b则返回true,否则false |
> | a>b | 如果a大于b返回true,否则false |
<= | a<=b | 小于等于 |
>= | a>=b | 大于等于 |
in 或者 not in
-
in用于判断某个列的取值是否为指定的值,使用in运算符时指定的值是离散的数据,不是连续值
-
select * from tb_student where age in(18,25,13)
含义是age = 18 or age = 25 or age = 13
-
select * from tb_student where age not in(18,25,13)
含义是age != 18 and age!=25 and age !=13
-
select num from test where num in(1,2,3);
-- 等价于
select num from test wherr num = 1 or num = 2 or num = 3;
-
not in 的用法于in一样,最大的区别就是一个匹配包含的数据一个拒绝包含的数据
-
使用not in时,not in 中不能包含null,否则结果为空,即发生not in失效的情况
-
between/and
用于判断数据是否在指定的范围内,连续值
-
查询成绩及格并且不属于优秀的所有的学生信息
-- 写法1:
select * from tb_student where score >= 60 and score <= 85;
-- 写法二:
select * from tb_student where score between 60 and 85;
like / not like
主要针对字符串类型数据进行模糊查询,通配符_和%
查询不姓张的同学
select * from tb_student where name not like '张%';
is null/is not null
判断是否为空,为空则返回true
逻辑运算符
语法 | 说明 | |
---|---|---|
&& 或者 and | a and b 或者a&&b | 逻辑与,如果参与计算的两个操作数为true,否则为false |
|| 或者 or | a or b 或者a || b | 逻辑或,如果参与的计算双方,只要有一方为true,则返回true |
not 或者 ! | not a 或者 !a | 逻辑非,如果操作数为false则结果返回true |
练习题
样例数据表
create table tb_Student(
id bigint not null auto_increment,
name varchar(20) 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,'李四',20,0,'大数据'),(null,'小白脸',21,0,'人工智能'),(null,'小帅哥',22,1,'软工');
-
查询全体学生的姓名及出生年份
select name,2022-age as bitrh from tb_student;
-
查询所有学生的姓名
select name from tb_student;
-
查询李姓学员的姓名及出生年份
select name, 2022-age from tb_student where name like '李%';
-
查询年龄在18到23岁之间的学生姓名、系别和年龄
select name,dept,age from tb_student where age between 18 and 23;
.......... age>=18 and age<=23;
.......... age in(18,19,20,21,22,23);
-
查询年龄不在18到23岁之间的学生姓名、系别和年龄
select name,dept,age from tb_student where age not between 18 and 23;
age<18 or age>23;
-
查询软工、人工智能和大数据系的所有学生姓名和性别
select name,age from tb_student where dept in('软工','人工智能','大数据');
select name,age from tb_student where dept='软工'or dept='人工智能'or dept='大数据';
-
查询不是软工、人工智能和也不是大数据系的所有学生姓名和性别
select name,age from tb_student where dept not in('软工','人工智能','大数据');
select name,age from tb_student where dept!='软工' and dept!='人工智能' and dept!='大数据';
-
查询Axx学生的姓名和年龄
select name,age from tb_student where name like'A__';
-
查询名字中有a的学生的信息
select * from tb_student name like'%a%';
-
查询age确定的学生信息
-
注意=null或者!=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 from tb_student; -- distinct去除重复值
-
查询系别和学生年龄信息
select dept,age from tb_student;
问题:如果auto_increment到达上限是mysql是如何处理的
auto_increment默认从1开始,上限取决于列的数据类型,如果表中的id列已经有指定的值,则max(id)+1
create table tt(
id int primary key auto_increment,
name varchar(20)
);
insert into tt values(2147483646,'小帅哥');
insert into tt(name) values('小白脸');
mysql> select * from tt;
+------------+-----------+
| id | name |
+------------+-----------+
| 2147483646 | 小帅哥 |
| 2147483647 | 小白脸 |
+------------+-----------+
2 rows in set (0.00 sec)
mysql> insert into tt(name) values('小红');
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY' -- 意思是再次生成了最大的int值2147483647作为之间,此时主键冲突
如果删除数据后,数据库系统仍旧会记录已经生成过的数据,不会重新开始,而是在以前的基础上继续加1
mysql> create table tt(id bigint primary key auto_increment);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into tt values 1;
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 '1' at line 1
mysql> insert into tt values (1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into tt values (2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from tt;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
mysql> delete from tt where id = 2;
Query OK, 1 row affected (0.01 sec)
mysql> insert into tt values (3);
Query OK, 1 row affected (0.01 sec)
mysql> select * from tt;
+----+
| id |
+----+
| 1 |
| 3 |
+----+
2 rows in set (0.00 sec)
如果需要重新开始生成连续整数,只能将表中的所有数据删除
truncate table t1; -- 删除整表数据,不能使用delete from
建议:因为auto_increment生成数据是从1开始,不会出现负数,所以一般建议使用
五种聚类函数
聚集函数用于对于一个集合中的数据进行处理,不是一行一行的数据 count统计行数,sum求和,max最大值,min最小值,avg平均值
计数
语法count ([all/distanct] 列名称/*)
获取总学生数
select count(*) from tb_student; -- 查询学生总人数
select count(1) from tb_student;
select count(distinct dept) from tb_student; -- 获取系的个数
count(*)\count(1)\count(字段)
的区别
执行效果:
count(1) 和 count(*) 效果一样
count(1) 会统计表中的所有记录数,包含字段为null的记录,count(字段) 会统计该字段在表中出现的次数,忽略字段为null的情况,即不统计字段为null的记录
count(*) 包括了所有列,相当于行数,在统计结果的时候,不会忽略列值为null
count(1) 包括了忽略所有列,用1代表代码行,在统计结果的时候,不会忽略列值为null
count(字段)只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空表示的是null)的计数,即某个字段为null时不统计
统计值
用于数据类型的列,不要用于其他类型。max min sum avg
mysql> insert into tb_student values(now());
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from tb_student;
+------------+
| birth |
+------------+
| 2022-04-26 |
+------------+
1 row in set (0.00 sec)
mysql> insert into tb_student values('2022-04-13');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tb_student values('2022-02-12');
Query OK, 1 row affected (0.01 sec)
mysql> select max(birth) from tb_student;
+------------+
| max(birth) |
+------------+
| 2022-04-26 |
+------------+
1 row in set (0.01 sec)
mysql> select avg(birth) from tb_student;
+---------------+
| avg(birth) |
+---------------+
| 20220350.3333 |
+---------------+
1 row in set (0.01 sec)
mysql> select min(birth) from tb_student;
+------------+
| min(birth) |
+------------+
| 2022-02-12 |
+------------+
1 row in set (0.00 sec)
-- null值不参与计算,除非使用空值处理函数将其转换为确定数值才可以
问题
select count(*)、select count(1)和select count(列名称)
-
列名为主键
select count(列名)
会比select count(1)
速度快,如果列名不是主键则相反 -
如果表有多个列而且没有主键,则
select count(1)
会比select count(*)
速度快,如果有主键select count(主键列名)
速度最快,如果表中只有一个列select count(*)
最快 -
select count(1)
会统计表中所有的记录数,包括字段为null的记录;select count(列名)
则列为null时不统计 -
MySQL针对不同的存储引擎进行了优化处理,MyISAM会将表的总行数记录下来,供
select count(*)
查询使用;Innodb则会在扫描表时选择最小的索引成本执行,在Innodb中,select count(*)
和select count(1)
没有区别,而且执行效率一致,只有select 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(*) > 2;
查询选修了三门以上课程的学生的学号
vselect sno from SC group by son having count(*) > 3;
查询有三门课程是90分以上的学生的学号以及90分以上的课程数
-
需要分组处理的数据必须是90分以上的数据
-
针对学号进行分组
-
分组中课程数应该大于等于三门
select sno,count(*) from tb_student
where score>=90
group by sno having count(con) >= 3;
having和where之间的区别
只有满足条件的数据才会输出显示
最大的区别在于:作用的对象不同
-
where用于基表或者试图从中选择满足条件的元组
-
having子句用于分组,从多个分组中选择满足条件的分组
排序处理
SQL中可以针对查询结果进行排序
语法select ... from ... ordor by 列名称1[asc/desc],列名称2[asc/desc],....
-
排序过程中首先按照列名称1进行排序,如果列1对应值相等时,才按照列名称2对应列值进行排序
-
默认按照指定列的自然序排序,如果需要倒叙则使用关键字desc,asc是正序
按照学生年龄从大到小输出学生信息
select * from tb_student order by age desc;
DDL语句建表操作练习
定义 学生(学生编号,姓名,性别),课程(课程编号、课程名称),选修(学生编号,课程编号,成绩)
create table if not exists tb_student(
id bigint primary key auto_increment,
name varchar(20) 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 varcaht(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;
数据比较
如果一个表中的数据和另一个表中的数据相等时,最好保持数据类型一致