查询：分页、连接查询、自关联、子查询

分页

 

limit 开始位置, 查询数目 或者 limit 查询数目

 

       -- 限制查询出来的数据个数

       select * from students where gender=1 limit 2;

 

       -- 查询前5个数据

       select * from students limit 0, 5;

 

       -- 查询id6-10（包含）的书序

       select * from students limit 5, 5;

 

       -- 每页显示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等同于 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完成

 

自关联

对于一些省市信息，如何设计自关联表将他们关联起来呢？

 

对一个省市，我们做如下表示：

id（省市编号）  atitle（省市名称）  pid（直接所属上级城市的编号id）

 

因为省没有所属的省份，所以可以填写为null，城市所属的省份pid，填写省所对应的编号id

 

这就是自关联，表中的某一行，关联了这个表中的另外一行

      

       -- 查询所有省份

       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 * from areas where pid=(select aid from areas where atitle="青岛市")

 

子查询

       -- 标量子查询

       -- 查询出高于平均身高的信息

 

       -- 查询最高的男生信息

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

转载于:https://www.cnblogs.com/yongfuxue/p/10037919.html