数据库 MySQL(二)
文章目录
准备数据
01. 筛选条件
即用于where条件中
比较运算符
=
:等于(注意!不是==
)!=
或<>
:不等于>=
:大于等于>
:大于<=
:小于等于<
:小于is null
:空值is not null
:非空值
逻辑运算符
and
:与mysql> select * from student where class=18 and gradName='first_grad';
or
:或mysql> select * from student where age>20 or gradName='third_grad';
not
:非
其他操作
-
排序(
order by
)select columns from 表名 order by col [asc/desc];
asc
:正序(默认)desc
:倒序
mysql> select * from student order by age; #按age正序进行查找所有数据 mysql> select * from student order by age desc; #按age倒序进行查找
-
限制(
limit
)select columns from 表名 limit start,count;
start
:开始(下标)位置count
:从start开始显示count个数据
数据库中的数据下标是从0开始的
mysql> select * from student limit 0,3;
#查询从第0个数据开始的三条数据
mysql> select * from student limit 2,4;
#查询从第2条数据开始的四条数据
-
去重(
distinct
)select distinct 字段 from 表名;
mysql> select distinct age from student;
mysql> select distinct gradName from student;
-
模糊查询(
like '%'
)%
:任意多个字符_
:任意一个字符
mysql> select * from student where age like '1_'; #查找所有20岁以下的数据
-
范围查询
-
连续范围:
between a and b
:a <= value <= b
mysql> select * from student where age between 20 and 24; #查找age介于[20,24]的所有数据 mysql> select * from student where age not between 20 and 24; #查找age不在[20,24]的所有数据
-
间隔范围(非连续范围):
in
a in (10,20,30,[...])
mysql> select * from student where age in (19,23,25); #查找年龄为19,23,25的所有数据
-
小结
02. 聚合与分组
常用聚合函数
-
count(字段)
:统计字段的个数(不包括null)
不包括null是指字段值为null的不计入统计mysql> select count(number) from student; #统计number的个数(不包括null)
-
max(字段)
:找该字段的最大值mysql> select max(age) from student; #查找年龄最大的数据
-
min(字段)
:找该字段的最小值mysql> select min(age) from student; #查找年龄最小的数据
mysql> select max(age),min(age) from student; #查找年龄最大和最小的数据
-
sum(字段)
:对该字段进行求和mysql> select sum(age) from student; #对age进行求和
-
avg(字段)
:对该字段求平均值mysql> select avg(age) from student;
-
group_concat(字段)
:列出该字段的全部字段值mysql> select group_concat(name) from student; #列出name的所有字段值
分组查询(group by
)
常与聚合函数一起用
PS:在分组的情况下,只能够出现分组字段和聚合字段,其他的字段没有意义,会报错
select 字段[,聚合函数] from 表名 group by 字段;
:按字段进行分组
PS:select后的字段与by后的字段必须是同一个字段,且select后只能跟一个字段,但可以跟多个聚合函数,select后的字段与聚合函数位置可以任意排序mysql> select gradName from student group by gradName; #按年级分组 mysql> select gradName,group_concat(name) from student group by gradName; #和聚合函数一起用,按年级分组 mysql> select max(age),gradName,min(age) from student group by gradName; #求出各组中age的最大值与最小值
mysql> select group_concat(name) from student group by gradName; #直接查看分组后数据
聚合筛选(having
)
having:对聚合出来的数据进行再次筛选
就是层层筛选,最终达到精确查找的目的
select 字段1 from 表名 group by 字段1 having 字段1的条件;
having
是对select 字段1 from 表名 group by 字段1
所筛选出来的数据字段1进行再次筛选,所以having后面的条件必须是对已筛选出来的字段1的数据进行的再次筛选。
mysql> select gradName,group_concat(name) from student froup by gradName having gradName='first_grad';
#在用gradName分组的情况下再筛选出first_grad的数据
PS:加having条件表达式,可以对输出的结果做出限制
假如说
一个查询语句中同时包含了别名(as),聚合函数,where,having
那么他们的执行顺序是
- 先是执行:where
- 然后执行:聚合函数和别名
- 最后执行:having
having与where区别:
having是从前筛选的字段再筛选,而where是从数据表中的字段直接进行的筛选的
- 区别一:
where 是数据从磁盘读入内存时候一条一条判断的
having 是将所有数据读入内存,在分组统计前,根据having的条件再将不符合条件的数据删除 - 区别二:
having 子句可以使用字段别名,where不可以用 - 区别三:
having可以使用统计函数,where不可用
having筛选必须是 根据前面select字段的值 进行筛选
总之一条sql中有where, having, group by的时候,顺序是 where group by having
mysql> select gradName,count(name) from student where age>18 group by gradName;
#计算出每个年级中大于18岁的人数
mysql> select gradName,count(name) from student where age>18 group by gradName having gradName='first_grad';
#计算出一年级中大于18岁的人数
'''因为select筛选出来的是gradName,所以having只能对gradName进行再筛选,而不能对其他字段进行筛选'''
小结
03. 子查询
定义:
将一个查询的结果留下来用于下一次查询(select中嵌套select)
要求:
- 嵌套在查询内部
- 必须始终出现在圆括号内
例:
mysql> select avg(age) from student;
#求出学生的平均年龄
mysql> select * from student where age > 19.7273;
#查找出大于平均年龄的数据
mysql> select * from student where age > (select avg(age) from student);
#将求出的平均年龄的SQL语句用于查找大于平均年龄的语句中
04. 连接查询
内连接(inner join
)
1. 无条件内连接
无条件内连接,又名交叉连接/笛卡尔连接
第一张表中的每一项会和另一张表的每一项依次组合
select * from 原表 inner join 新表
mysql> select * from student inner join scoren;
#student表中的每一项会与scoren表中的每一项依次组合
2. 有条件内连接
在无条件内连接的基础上,加上一个on子句
on 连接条件
当连接的时候,筛选出那些有实际意义的记录来进行组合
select * from 原表 inner join 新表 on 原表.字段1=新表.字段2;
mysql> select * from student inner join scoren on student.number=scoren.id;
#只将student表中number与scoren表中id值相同的数据组合筛选出来
mysql> select student.number,student.name,student.age,scoren.id from student inner join scoren on student.number=scoren.id;
#再上条代码的基础上只打印出student表中的number,name,age字段和scoren表中的id字段
外连接({left|right} join
)
1. 左外连接:(以左表为基准)
两张表做连接的时候,在连接条件不匹配的时候,
留下左表中的数据,而右表中数据以NULL填充
select * from 左表 left join 右表 on 左表.字段1=右表.字段2
select 字段3,字段4[,...] from 左表 left join 右表 on 左表.字段1=右表.字段2
mysql> select * from student left join scoren on student.number=scoren.id;
#student为左表,scoren为右表
mysql> select student.name,scoren.score left join scoren on student.number=scoren.score;
#只显示student中的name和scoren中的score
2. 右外连接:(以右表为基准)
对两张表做连接的时候,在连接条件不匹配的时候,
留下右表中的数据,而左表中的数据以NULL填充
select * from 左表 right join 右表 on 左表.字段1=右表.字段2
select 字段3,字段4[,...] from 左表 right join 右表 on 左表.字段1=右表.字段2
mysql> select * from student left join scoren on student.number=scoren.id;
#student为左表,scoren为右表
mysql> select student.name,scoren.score left join scoren on student.number=scoren.score;
#只显示student中的name和scoren中的score