03-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;