自连接

CREATE TABLE `category` (

​    `categoryid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',

​    `pid` INT(11) COMMENT '父id',

​    `categoryname` VARCHAR(50) COMMENT '主题名字',

​    PRIMARY KEY (`categoryid`)

) ENGINE = INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

TRUNCATE `category`;

INSERT INTO `category` (`categoryid`,`pid`,`categoryname`);

VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

-- 自连接，核心：一张表拆为两张一样的表

-- 查询父子信息:把一张表看作两张一摸一样的表

SELECT a.categoryname,b.categoryname
FROM category a,category b
WHERE a.categoryid = b.pid;

-- 查询学生所属的年级

SELECT studentno,studentname,gradename
FROM student s
INNER JOIN grade g
ON s.gradeid = g.gradeid;

-- 查询参加了 数据库结构-1 考试的同学信息：学号，姓名，科目，成绩

SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '数据库结构-1';