Mysql基础：联表查询（joinON），表的自连接查询，分页和排序

多表查询本质（图解）

 

展开（图解）

     

 联表查询，7种join对比：

1.1 inner/right/left join对比

 

-- 联表查询
-- join 对比
-- 表中数据不够了加了一些
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1001,1,'2013-11-11 16:00:00',85),
(1001,2,'2013-11-12 16:00:00',70),
(1001,3,'2013-11-11 09:00:00',68),
(1001,4,'2013-11-13 16:00:00',98),
(1001,5,'2013-11-14 16:00:00',58),
(1002,1,'2013-11-11 16:00:00',85),
(1002,2,'2013-11-12 16:00:00',70),
(1002,3,'2013-11-11 09:00:00',68),
(1002,4,'2013-11-13 16:00:00',98),
(1002,5,'2013-11-14 16:00:00',58),
(1003,1,'2013-11-11 16:00:00',85),
(1003,2,'2013-11-12 16:00:00',70),
(1003,3,'2013-11-11 09:00:00',68),
(1003,4,'2013-11-13 16:00:00',98),
(1003,5,'2013-11-14 16:00:00',58);





-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM result
SELECT * FROM student

-- 分析
-- 1.分析查询字段来自那些表（连接查询）
-- 确定使用哪种连接查询？ 7种
-- 确定交叉，学生表studentno=成绩表studentno

-- inner join
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno=r.studentno


-- right join
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student AS s
RIGHT JOIN result r -- AS可写可不写
ON s.studentno=r.studentno

-- left join
SELECT s.studentno,studentname,subjectno,studentresult 
FROM student AS s
LEFT JOIN result r -- AS可写可不写
ON s.studentno=r.studentno

操作	描述
inner join	如果表中至少有一个匹配，就会返回值，当两张表有同样的列时，需在语句种确定，返回那张表的值
right join	会从左表中返回所有的值，即使右表中没有匹配
left join	会从右表中返回所有的值，即使左表中没有匹配

1.2多表查询练习

-- 查询缺考的同学
-- UPDATE  result SET studentresult=NULL
-- WHERE studentno=1003 and subjectno=5

SELECT s.studentno,studentname,subjectno,studentresult 
FROM student AS s
LEFT JOIN result r -- AS可写可不写
ON s.studentno=r.studentno
WHERE studentresult is NOT NULL

-- 注意
-- join（连接的表） on （判断条件）连接查询
-- where     等值查询

-- 思考题（查询参加考试的同学信息：学号，学生姓名，科目名，分数），三表查询
SELECT s.studentno,studentname,subjectname,studentresult 
FROM student AS s
RIGHT JOIN result r -- AS可写可不写
ON s.studentno=r.studentno
-- where studentresult is not NULL
INNER JOIN `subject` sub 
ON r.subjectno=sub.subjectno
-- 总结，当有多张表查询是，应先查询两张表然后再慢慢增加

1.3自连接

本质：自己的表和自己的表连接，核心：一张表拆成两张一样的表即可

eg：

         

父类

categoryid	categoryname
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryname
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	美术设计

操作：查询父类对应子类的关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps

-- ================自连接================
-- 创建新表，插入数据
CREATE TABLE category(
    categoryid INT(10) UNSIGNED not NULL auto_increment COMMENT '主题id',
    pid INT(10) NOT NULL COMMENT '父id',
    categoryname VARCHAR(50) NOT NULL COMMENT '主题名字',
    PRIMARY KEY(categoryid)
)ENGINE=INNODB  DEFAULT charset=utf8;

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 AS '父栏目',b.categoryname as '子栏目'
from category AS a, category AS b
WHERE a.categoryid=b.pid

SELECT * FROM category


-- 练习：查询学员所属的年纪（学号，学生姓名，年纪名称）
SELECT studentno,studentname,gradename
from student s
INNER JOIN grade g
ON s.gradeid=g.gradeid

-- 练习：查询科目所属的年级(科目名称，年级名称)
SELECT subjectname,gradename
FROM `subject` 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 sub.subjectno=r.subjectno
WHERE subjectname='高等数学-1'

SELECT * FROM grade
SELECT * FROM `subject`
SELECT * FROM result
SELECT * FROM student

1.4分页（limit）和排序（order by）

-- ===========分页（limit）和排序(order by)========
-- 升序ASC，降序DESC

-- 排序
-- 查询结果根据成绩降序排
SELECT  s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON sub.subjectno=r.subjectno
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC


-- 分页，缓解数据库压力，给人体验更好，瀑布流
-- 分页，每页只显示3条数据
-- 语法：limit 起始值，页面大小
SELECT  s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON sub.subjectno=r.subjectno
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC
LIMIT 3,3
-- 分页总结：【pagesize：页面大小，起始值=（n-1）*pagesize】
-- 【n:当前页】，【页面总数/页面大小=总页数】

 总结题

-- 思考题：查询高等数学-1，课程成绩排在前三的学生，并且分数大于80的学生信息（学号，姓名，课程名称，分数）
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' AND studentresult>=80
ORDER BY studentresult DESC
LIMIT 0,3