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