1 create table student( 2 number int UNIQUE KEY auto_increment,  3 name varchar(20) UNIQUE KEY,  4 klass int not null,  5 age int not null, 6 gradName varchar(10) 7 )auto_increment=201804001; 8  9 insert into student(name, klass,age, gradName) value10 ( ‘刘一‘, 19, 16, ‘二年级‘),11 ( ‘陈二‘, 18, 19, ‘一年级‘),12 ( ‘张三‘, 19, 20, ‘二年级‘),13 ( ‘李四‘, 19, 17, ‘一年级‘),14 ( ‘王五‘, 19, 18, ‘三年级‘),15 ( ‘赵六‘, 18, 24, ‘二年级‘),16 ( ‘孙七‘, 19, 22, ‘三年级‘),17 ( ‘周八‘, 19, 21, ‘二年级‘),18 ( ‘吴九‘, 18, 25, ‘一年级‘),19 ( ‘郑十‘, 19, 23, ‘一年级‘),20 ( ‘小周周‘, 18, 20, ‘二年级‘),21 ( ‘周周周‘, 19, 21, ‘三年级‘);

• 范围查询： BETWEEN a AND b

• 间隔返回： in

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

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

1 #范围查询 2 select * from student where age between 17 and 20; #找出age在这个范围的字段3 select * from student where age in (17, 20); #找出age属于这里面的字段4 5 #模糊查询6 select * from student where name like ‘周%‘; #%匹配任意个字符7 select * from student where name like ‘周_‘; #匹配一个任意字符

排序/去重

• 排序：  SELECT columns FROM tb_name ORDER BY ord_col_1 [asc/desc]; 

• 去重：  SELECT DISTINCT columns FROM tb_name; 

1 select * from student order by age ; #按照age从小到大排序2 select * from student order by age ; #按照age从大到小排序3 4 select distinct gradName from student; #查看有几个年级

聚合/分组

• 聚合

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

　　分组 group by 在分组的情况下，只能出现聚合列和分组列

 1 select gradName from student group by gradName;  2 #查看总共有几个年级 3  4 select gradName, count(name)as count from student group by gradName;  5 #查找每个年级有多少人 6  7 select gradName, GROUP_CONCAT(name) from student group by gradName; 8 #查看每个年级有哪些人 9 10 select gradName,name from student group by gradName; 11 #出现其他字段，报错12 13 select age, gradName from student group by age,gradName;14 #group by可以分组多个字段，15 16 select gradName, count(number) from student group by gradName with rollup;17 #with rollup 在最后加一行统计

　　聚合过滤 having 对聚合出来的数据进行过滤

1 #聚合过滤 having 2 select gradName, count(number) as count from student group by gradName having count(gradName)>3 [order by gradName]; 3 #查看每个年级有多少人，并过滤掉人数小于等于3的， 如果换成where就会报错

where和 having的区别和组合使用

• where 不可以使用别名， having可以

• where不能操作聚合函数

• where和having组合使用是先执行where筛选数据，最后用having筛选数据

• where要写在group by 前面 having要写在group by 后面

 1 #where不可以使用别名， having可以使用 2 select age from student where age>20; #查看age>20的字段 3 select age as new from student where new>20; #错误 不能使用别名， 4 select age as new from student where age>20; #使用原来的名字可以 5  6 select age as new from student having new>20; #having可以使用别名 7 select age as new from student having age>20; #也可以使用原来的名字 8  9 10 11 #where不能操作聚合函数， having可以12 select gradName, count(age) from student group by gradName; #查看每个年级有多少人13 select gradName, count(age) from student where count(age)>3 group by gradName;14 #报错， where不能操作聚合函数15 16 select gradName, count(age) from student group by gradName having count(age)>3;17 #having可以操作聚合函数18 19 20 21 22 #where和having组合使用23 select gradName, count(age) from student where age>18 group by gradName;24 #统计每个年级age>18的有多少人25 26 select gradName, count(age) from student where age>18 group by gradName having gradName=‘一年级‘; 27 #统计一年级age>18的有多少人

限制与分页

1 select * from student limit 5; #从开始查找五条数据;2 3 select * from student limit 0,5; #索引从头开始， 取几个数据4 5 n = 1 # 第几页6 m = 5 #一页显示五条7 select * from student limit (n-1)*m, m;