五、DQL查询数据2

1. 自连接查询

自连接查询，意思就是自己与自己做联表查询，听起来比较抽象，我们看个例子。

首先准备点数据

create table tdb_cates(
	id smallint primary key auto_increment,
	cate_name varchar(20) not null,
	parent_id smallint not null
);

insert into tdb_cates(cate_name, parent_id) values('数码产品', 0);
insert into tdb_cates(cate_name, parent_id) values('家用产品', 0);
insert into tdb_cates(cate_name, parent_id) values('笔记本', 1);
insert into tdb_cates(cate_name, parent_id) values('智能手机', 1);
insert into tdb_cates(cate_name, parent_id) values('电器', 2);
insert into tdb_cates(cate_name, parent_id) values('家具', 2);
insert into tdb_cates(cate_name, parent_id) values('冰箱', 5);
insert into tdb_cates(cate_name, parent_id) values('洗衣机', 5);
insert into tdb_cates(cate_name, parent_id) values('汽车品牌', 0);
insert into tdb_cates(cate_name, parent_id) values('别克', 9);
insert into tdb_cates(cate_name, parent_id) values('宝马', 9);
insert into tdb_cates(cate_name, parent_id) values('雪佛兰', 9);
insert into tdb_cates(cate_name, parent_id) values('家纺', 0);

插入数据后的结果是这样：

这是我们想要查询出一级类目和二级类目的对应关系，比如数码产品下有笔记本，家用产品下有电器...

这是就需要将现在的表想象成两张表，一张上面是父级科目信息，另一张上是二级的科目信息，通过id将他们关联起来。

SELECT t1.cate_name, t2.cate_name
FROM tdb_cates AS t1 INNER JOIN tdb_cates AS t2
ON t1.parent_id = t2.id;

查询的结果如下：

2. 子查询

在四、DQL数据查询1中的student表和score表，如果我们查询语文成绩大于80分的学生的姓名。使用联表查询可以这么查：

SELECT `name`
FROM `student` s1 INNER JOIN `score` s2
ON s1.id=s2.studentId AND s2.subject='语文' AND s2.goal>80;

在这里我们同样可以使用子查询，先冲score表中查询出语文分数>80的学生id，在从student表中查询id匹配的学生姓名：

SELECT `name` FROM `student`
WHERE id IN (
	SELECT `studentId`
	FROM `score`
	WHERE `subject`='语文' AND `goal`>80
);

这两种方法都可以查询出李四和王二。

3. 分页、排序和分组

分页和排序

将查询出的结果分页使用LIMIT关键字，对查询出的结果排序使用ORDER BY关键字。

直接看例子：

-- 查询数学成绩第一名的学生id
SELECT `studentId`,`subject`,`goal` FROM `score`
WHERE `subject`='数学'
ORDER BY `goal` DESC
LIMIT 1;

其中 ORDER BY 后面的DESC表示的是降序排列，也可以使用ASC升序排列。

LIMIT是对查询的结果做分页，这里的limit 1指的是取结果中的前一个。

分组

分组是对查询的结果按照某个列的值进行分组，结果如下：

-- 按照科目分组查询平均分
SELECT `subject`,AVG(goal) FROM `score` GROUP BY `subject`;

4. 聚合函数

聚合函数是在查询中十分常用的函数，下面是一些高频使用的：

1. COUNT() 统计数量
2. SUM() 求和
3. AVG() 求平均数
4. MAX() 最大值
5. MIN() 最小值
6. ...

-- 查询id为1的学生的成绩平均值
SELECT AVG(goal) FROM `score` WHERE `studentId`=1;
-- 查询语文最高分·
SELECT MAX(goal) FROM `score` WHERE `subject`='语文';
-- 查询学生表中学生数量
SELECT COUNT(id) FROM `student`;