DQL语言（二）

内连接和外连接

关键词：join

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）inner join
SELECT s.`studentno`, studentname,subjectno,studentresult
FROM student s
INNER JOIN result r
ON s.`studentno` = r.`subjectno`

-- -- 查询参加了考试的同学（学号，姓名，科目编号，分数）right join
SELECT s.`studentno`, studentname,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON s.`studentno` = r.`studentno`

-- -- 查询参加了考试的同学（学号，姓名，科目编号，分数）left join
SELECT s.`studentno`, studentname,subjectno,studentresult
LEFT JOIN result r
ON s.`studentno` = r.`studentno`

操作	描述
inner join	两个都存在返回行
right join	从右表返回所有的值，即使左表没有匹配
left join	从左表返回所有的值，即使右表没有匹配

-- 查询参加了考试的同学的信息（学号，姓名，科目名，分数）
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r 
ON r.`studentno` = s.`studentno`
INNER JOIN `subject` j
ON r.`subjectno`=j.subjectno

 

 

自连接

自己的表和自己连接，一张表拆为两张一样的表

测试数据：

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  AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; 

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

-- ===============自连接================
SELECT c.categoryname '父栏目' ,a.categoryname AS '子栏目    '
FROM category c ,category a
WHERE c.`pid`=a.`categoryid`

 

 

分页和排序

-- ==============成绩降序排列===================
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` j
ON r.subjectno = j.subjectno
WHERE subjectname = '流体力学'
ORDER BY studentresult DESC -- 升序是ASC

为什么要分页

缓解数据库压力，给人更好的体验，瀑布流应用（适用于图片）

-- ==============分页limit (n-1)*pageSize,pageSize===================
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` j
ON r.subjectno = j.subjectno
WHERE subjectname = '流体力学'
ORDER BY studentresult DESC
LIMIT 0,2 -- 0是起始下标，2是页面大小

 

子查询

-- 查询数据结构的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：连接查询
SELECT studentno,r.subjectno,studentresult
FROM result r
INNER JOIN `subject` j
ON r.subjectno = j.subjectno 
WHERE j.subjectname='数据结构'
ORDER BY studentresult DESC
-- 方式二 子查询，由里及外
SELECT studentno,r.subjectno,studentresult
FROM result r
WHERE subjectno=(
    SELECT subjectno 
    FROM `subject`
    WHERE subjectname='数据结构'
)
ORDER BY studentresult DESC

-- 查询分数不小于80分的学生的学号和姓名并且科目是网络安全
-- 方式一 连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` j
ON r.subjectno = j.subjectno
WHERE r.studentresult >= 80 AND j.subjectname='网络安全'
-- 方式二 子查询
SELECT studentno,studentname
FROM student
WHERE studentno IN (
    SELECT studentno
    FROM result
    WHERE studentresult>=80
    AND subjectno=(
        SELECT subjectno 
        FROM `subject`
        WHERE subjectname='网络安全' 
    )
)

 

分组和过滤

-- 查询不同课程的平均分，最高分，最低分
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(studentresult),MIN(studentresult)
FROM result r
INNER JOIN `subject` j
ON r.subjectno = j.subjectno
GROUP BY r.subjectno -- 通过什么来分组
HAVING 平均分>=80 -- 过滤分组后需要满足的条件