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, '三年级');

 　比较运算符 : 

等于	=
大于	>
小于	<
大于等于	>=
小于等于	<=
不等于	!=或<>
是NULL	IS NULL
不是NULL	IS NOT NULL

　　逻辑运算符：

与（且）	AND
或	OR
非（不是）	NOT

• 范围查询： BETWEEN a AND b

• 间隔返回： in

• 模糊查询： % 匹配任意个任意的字符

• 模糊查询： _ 匹配一个任意字符

#范围查询  
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 ;    #按照age从大到小排序

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

聚合/分组

• 聚合

常用聚合函数	描述
COUNT(column)	统计个数
MAX(column)	最大值
MIN(column)	最小值
SUM(column)	求和
AVG(column)	平均值
GROUP_CONCAT(column)	列出字段全部值

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;