Mysql学习笔记(七) ----自连接,分页，排序

1. 自连接

自连接就是自己的表和自己的表连接，其实自连接的表本身可以拆分为两张独立的表，只不过这样容易造成数据冗余。
我们在学习数据库的时候，可能都学过一个先修课的例子，也就是修一门功课之前，必须修完另一门功课，这就是自连接的典型例子。
[1] 下面我们先以两张表进行说明
父类表

子类表

举个例子：在父类表中都是最原始的课程，也就是学习它，不需要别的课程做辅助。而在子类表中需要学习了pid对应的课程才能学习。
以办公信息为例它的pid为2,则它的父类为信息技术，依此类推，web开发，数据库的父类都是软件开发。ps技术的父类就是美术技术。
[2] 我们的业务需求就是把上面的两张表合成一张表，并查找出对应的父子类关系
最终的结果为：

[3] 创建相关的表（我们在建表时，把父类的pid设为1，即信息技术，软件开发，美术技术的pid都为1）

- 创建种类表
CREATE TABLE `school`.`category`
( `categoryid` INT(3) NOT NULL COMMENT 'id', 
  `pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', 
  `categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', 
   PRIMARY KEY (`categoryid`) ) 
   ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
INSERT INTO `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) VALUES ('3', '1', '软件开发');
INSERT INTO `school`.`category` (`categoryid`, `PId`, `categoryname`) VALUES ('5', '1', '美术设计');
INSERT INTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VALUES ('4', '3', '数据库');
INSERT INTO `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) VALUES ('8', '2', '办公信息');
INSERT INTO `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) VALUES ('6', '3', 'web开发');
INSERT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('7', '5', 'ps技术');

创建后的表信息为：

查询父子类信息

-- 查询父子类信息,把一张表看成两张一模一样的表
SELECT p.`categoryname` AS '父栏目' ,s.`categoryname` AS '子栏目'
FROM `category` p, `category` s
WHERE s.`pid` = p.`categoryid`

结果:

2. 分页和排序

排序
ASC 升序（默认） DESC 降序

我们现在有三张表，学生表，科目表，年级表。
[1] 学生表 student

[2] 科目表 subject

[3] 年级表 grade

业务需求：我们通过这三张表查找出学生id, name, subName, score, gradeName, 并通过成绩进行排序。
由于查找的结果在每张表中都有，我们考虑连接查询，而这个业务需求我们直接使用内连接，取交集即可
（这三种连接不清楚的可参考多表查询）

-- 排序: 升序 ASC  降序 DESC
-- 查询的结果根据成绩排序
SELECT `id`,`name`,`subName`,`score`,`gradeName`
FROM student s
INNER JOIN `subject` sub
ON s.subNo = sub.subNo
INNER JOIN `grade` g
ON s.gradeid = g.gradeid
ORDER BY score ASC

分页
语法： limit (查询的起始下标，页面大小)

为什么使用分页？
假设有100万条数据，如果一次性查询，数据库的压力很大，给人的体验感也不佳。
（那么我们现在百度浏览的图片是用这种技术吗？其实不是，它是使用了一种瀑布流的方式，往下拉的时候逐渐读取缓存）
我们还是使用上面的例子分页（当然现实这么小的数据量根本不用分页）

-- 语法: limit 起始值,页面的大小
SELECT `id`,`name`,`subName`,`score`,`gradeName`
FROM student s
INNER JOIN `subject` sub
ON s.subNo = sub.subNo
INNER JOIN `grade` g
ON s.gradeid = g.gradeid
ORDER BY score ASC
LIMIT 0,2

原始的数据页：

分页结果:

那么第二页的写法呢？（记住第一个值为起始下标值，上面两条数据为0,1，则第二页的起始下标为2）
把它改为 LIMIT 2,2

小结

上面所描述的自连接，分页，排序，它们的概念都相当比较简单，不过有些业务需求还是经常使用，我们只要记住相关用法即可。