-- where对元素表进行条件判断，having对查询的结果的条件判断
-- 分页
-- limit start, count
-- 限制查询出来的数据个数
select * from students where gender=1 limit 2;
-- 查询前5个数据
select * from students limit 0, 5;
-- 查询id6-10（包含）的书序
select * from students limit 5, 5;
-- 每页显示2个，第1个页面
select * from students limit 0,2;
-- 每页显示2个，第2个页面
select * from students limit 2,2;
-- 每页显示2个，第3个页面
select * from students limit 4,2;
-- 每页显示2个，第4个页面
select * from students limit 6,2; -- -----> limit (第N页-1)*每个的个数, 每页的个数;
-- 每页显示2个，显示第6页的信息, 按照年龄从小到大排序
-- 失败select * from students limit 2*(6-1),2;
-- 失败select * from students limit 10,2 order by age asc;
select * from students order by age asc limit 10,2;
select * from students where gender=2 order by height desc limit 0,2;
-- 连接查询（多表情况下）
--
inner join ... on # 内连接
-- select ... from 表A inner join 表B;
select * from students inner join classes;
-- 查询 有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;
-- 按照要求显示姓名、班级
select students.*, classes.name from students inner join classes on students.cls_id=classes.id;
select students.name, classes.name from students inner join classes on students.cls_id=classes.id;
-- 给数据表起名字
select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;
-- 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
select s.*, c.name from students as s inner join classes as c on s.cls_id=c.id;
-- 在以上的查询中，将班级姓名显示在第1列
select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id;
-- 查询 有能够对应班级的学生以及班级信息, 按照班级进行排序
-- select c.xxx s.xxx from student as s inner join clssses as c on .... order by ....;
select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;
-- 当时同一个班级的时候，按照学生的id进行从小到大排序
select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id;
--
left join on
-- 查询每位学生对应的班级信息
select * from students as s left join classes as c on s.cls_id=c.id;
-- 查询没有对应班级信息的学生
-- select ... from xxx as s left join xxx as c on..... where .....
-- select ... from xxx as s left join xxx as c on..... having .....
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;
select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;
--
right join on
-- 将数据表名字互换位置，用left join完成
-- 自关联
-- 省级联动 url:http://demo.lanrenzhijia.com/2014/city0605/
-- 查询所有省份
select * from areas where pid is null;
-- 查询出山东省有哪些市
select * from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";
select province.atitle, city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";
-- 查询出青岛市有哪些县城
select province.atitle, city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="青岛市";
select * from areas where pid=(select aid from areas where atitle="青岛市")
-- 子查询（一个select中嵌套这另一个select）
-- 标量子查询
-- 查询出高于平均身高的信息
-- 查询最高的男生信息
select * from students where height = 188;
select * from students where height = (select max(height) from students);
-- 列级子查询
-- 查询学生的班级号能够对应的学生信息
-- select * from students where cls_id in (select id from classes);
2.数据库查询
标签：嵌套 int 元素 into 没有 多表 end dem 子查询
