mysql（三）

  • ​mysql（三）​
  • ​​数据准备​​
  • ​​筛选条件​​
  • ​​范围查询​​
  • ​模糊查询​
  • ​​排序/去重​​
  • ​​聚合/分组​​
  • ​​限制与分页​​
  • ​​作业​​


数据准备

create table student(
    number int UNIQUE KEY auto_increment,  
    name varchar(20) UNIQUE KEY, 
    klass int not null, 
    age int not null,
    gradName varchar(10)
)auto_increment=201804001;

insert into student(name, klass,age, gradName) value
( '刘一', 19, 16, '二年级'),
( '陈二', 18, 19, '一年级'),
( '张三', 19, 20, '二年级'),
( '李四', 19, 17, '一年级'),
( '王五', 19, 18, '三年级'),
( '赵六', 18, 24, '二年级'),
( '孙七', 19, 22, '三年级'),
( '周八', 19, 21, '二年级'),
( '吴九', 18, 25, '一年级'),
( '郑十', 19, 23, '一年级'),
( '小周周', 18, 20, '二年级'),
( '周周周', 19, 21, '三年级');

筛选条件

  • 范围查询： ​​BETWEEN a AND b​​
  • 间隔返回： ​​in​​
  • 模糊查询： ​​%​​ 匹配任意个任意的字符
  • 模糊查询： ​​_​​ 匹配一个任意字符

~~~mysql

范围查询

select * from student where age between 17 and 20; #找出age在这个范围的字段
select * from student where age in (17, 20); #找出age属于这里面的字段

模糊查询

select * from student where name like ‘周%’; #%匹配任意个字符
select * from student where name like ‘周_’; #匹配一个任意字符
~~~

排序/去重

  • 排序： ​​SELECT columns FROM tb_name ORDER BY ord_col_1 [asc/desc];​​
  • 去重： ​​SELECT DISTINCT columns FROM tb_name;​​ 
select * from student order by age ;    #按照age从小到大排序
select * from student order by age desc;    #按照age从大到小排序

select distinct gradName from student;  #查看有几个年级

聚合/分组

  • 聚合

常用聚合函数

描述

​​COUNT(column)​​

统计个数

​​MAX(column)​​

最大值

​​MIN(column)​​

最小值

​​SUM(column)​​

求和

​​AVG(column)​​

平均值

​​GROUP_CONCAT(column)​​

列出字段全部值

~mysql
select count(*) from student; #统计有几条数据
select max(age) from student; #求年龄最大值
select avg(age) from student; #求平均年龄 对于字符字段求值为0
select group_concat(age) from student; #显示字段所有值
~

  • 分组 ​​group by​​ 在分组的情况下，只能出现聚合列和分组列
select gradName from student group by  gradName;    
#查看总共有几个年级

select gradName, count(name)as count from student group by gradName;    
#查找每个年级有多少人

select gradName, GROUP_CONCAT(name) from student group by gradName;
#查看每个年级有哪些人

select gradName,name from student group by  gradName;   
#出现其他字段，报错

select age, gradName from student group by age,gradName;
#group by可以分组多个字段，

select gradName, count(number) from student group by gradName with rollup;
#with rollup 在最后加一行统计
  • 聚合过滤 ​​having​​ 对聚合出来的数据进行过滤
#聚合过滤 having  
select  gradName, count(number) as count 
from student 
group by  gradName 
having count(gradName)>3 [order by gradName];   
#查看每个年级有多少人，并过滤掉人数小于等于3的， 如果换成where就会报错
  • where和 having的区别和组合使用
  • where 不可以使用别名， having可以
  • where不能操作聚合函数
  • where和having组合使用是先执行where筛选数据，最后用having筛选数据
  • where要写在group by 前面 having要写在group by 后面
#where不可以使用别名， having可以使用
select age from student where age>20;           #查看age>20的字段
select age as new from student where new>20;    #错误 不能使用别名，
select age as new from student where age>20;    #使用原来的名字可以

select age as new from student having new>20;   #having可以使用别名
select age as new from student having age>20;   #也可以使用原来的名字



#where不能操作聚合函数， having可以
select gradName, count(age) from student group by gradName; #查看每个年级有多少人

select gradName, count(age) 
from student 
where count(age)>3 
group by gradName;
#报错， where不能操作聚合函数


select gradName, count(age) 
from student 
group by gradName 
having count(age)>3;
#having可以操作聚合函数



#where和having组合使用
select gradName, count(age) 
from student 
where age>18  group by gradName;
#统计每个年级age>18的有多少人


select gradName, count(age) 
from student 
where age>18  
group by gradName 
having gradName='一年级';  
#统计一年级age>18的有多少人

限制与分页

select * from student limit 5;      #从开始查找五条数据;

select * from student limit 0,5;        #索引从头开始， 取几个数据

n = 1       # 第几页
m = 5       #一页显示五条
select * from student limit (n-1)*m, m;

作业

统计出每个年级分别有多少人
select gradName as 年级,count(*) as 人数
from student
group by gradName;


统计出每个年级age大于18的人数
select gradName as 年级,count(*) as 人数
from student
where age>18
group by gradName;


统计出一年级的人数
select gradName as 年级,count(*) as 人数
from student
where gradName='一年级';


统计出一年级age大于18的人数
select gradName as 年级,count(*) as 人数
from student
where gradName='一年级' and age>18;