SQL 05: 查询语句
以学生系统为例
1. 最简单的查询, 查询所有字段
select * from student;
2. 查询特定的字段
select name,number from student
3. 查询符合特定条件的字段
select * from student where id = 4;
select * from student where age > 10;
select * from student where age > 10 and age <= 18;
4. 查询不连续的数据
select * from student where age=10 or age=18;
select * from student where age in (10,15,18); 选出10,15,18岁的学生
select * from student where age not in (10,15,18); 选出不是10,15,18岁的学生
5. 查询范围
select * from student where age between 10 and 18; 等于
select * from student where age >= 10 and age <= 18;
范围之外
select * from student where age not between 10 and 18;
6. like - 模糊查询
select * from student where name like '小明'; 找出小明
select * from student where name like '小%'; 找出小X, 小XX, 小XXX ...
select * from student where name like '小_'; 找出小X
select * from student where name like '%unity%'; 找出包含unity的结果, 相反地
select * from student where name not like '%unity%';
7. is null - 查询null
select * from student where number is null; 查询学号为null的同学
8. 去重查询
select distinct gender from student; 结果: gender 男 女
select distinct age from student; 结果: age 12 18 15 10
9. 排序查询
selcet * from student order by age; 按照年龄从小到大排序
selcet * from student order by age desc; 按照年龄倒序排序
10. gruop by - 分组查询
select gruop_concat(name) from gruop by class_id; 按照class_id 来分组， 根据name进行组拼
操作前
操作后
select class_id, gruop_concat(name) from student gruop by class_id; 按照class_id 来分组, 根据name进行组拼, 并显示class_id
select count(id) from student gruop by class_id; 按照class_id 来分组, 通过遍历(count)学生的id, 统计每班的人数
select sum(age) from student gruop by class_id; 分别求得各班级年龄和
(3班的人的年龄没有输入, 因此没有被统计)
having - 添加更多的限制条件
select count(id) from student gruop by class_id having count(id)>=2; 只显示班级人数>=2的班级
11. 分页查询
select * from student limit 0,1; 从索引0开始, 查1个数据
显示第pageNo页的数据, 显示pageSize个. 公式:
select * from student limit (pageNo-1)*pageSize, pageSize;
例如，
select * from student limit (2-1)*2, 2; 查询第二页的数据, 第二页显示两条数据
实际需要输入:
select * from student limit 2, 2;
效果:
