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_studentwhere score>=90group 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;

数据比较

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