查询_子查询_多表联查

-- 模糊查询 like：好像 _：匹配一个字符 %：匹配任意一个字符
SELECT * FROM student
WHERE `studentName` LIKE "%市%"


-- 如果有北京市的学生,就把地址不详的学生查出来
SELECT * FROM `student` 
WHERE EXISTS(

    SELECT * FROM student
    WHERE `address` LIKE "北京市%"

) AND `address`="地址不详";

-- 如果没有100分的学生,则查出不是100分的学生
SELECT * FROM `result`
WHERE NOT EXISTS(
    SELECT * FROM `result`
    WHERE `studentResult`=100
)AND `studentResult`>100;

-- 查询每门科目的平均分
SELECT `subjectNo` AS "科目", ROUND(AVG(`studentResult`),2)-- 平均分保留两位小数
AS "平均分"
FROM `result`
GROUP BY `subjectNo` DESC; 

-- 分组查询2：多列分组
-- 分别统计每个年纪的男女人数
SELECT  `gradeId` AS 年纪, sex AS 性别, COUNT(1) 人数
FROM `student` 
GROUP BY `gradeId`,sex;

-- having分组后筛选
-- 查询平均分及格的课程和平均分
SELECT `subjectNo` AS "科目", ROUND(AVG(`studentResult`),2)-- 平均分保留两位小数
AS "平均分"
FROM `result`
GROUP BY `subjectNo` 
HAVING 平均分>=70;

-- 两表联查，查：姓名、科目编号、分数
SELECT `studentName` AS 姓名,`subjectNo` AS 科目号,`studentResult` AS 分数
FROM `student`,`result`
WHERE `student`.`studentNo`=`result`.`studentNo`;

-- 简化：给表起别名 AS可省略
SELECT S.studentName  姓名,R.subjectNo  科目号,R.studentResult  分数
FROM student S,result R
WHERE S.studentNo=R.studentNo;

-- 内连接：inner join 表2 on 匹配条件
SELECT S.studentName  姓名,R.subjectNo  科目号,R.studentResult  分数
FROM student S
INNER JOIN result R
ON S.studentNo=R.studentNo;

-- 三表联查,查：姓名、科目名称、分数

SELECT S.studentName  姓名,SB.subjectName 科目名称,R.studentResult 分数
FROM student S
INNER JOIN result R
ON S.studentNo=R.studentNo
-- 用`subjectNo`链接第二第三个表``
INNER JOIN SUBJECT SB
ON SB.subjectNo=R.`subjectNo`;

-- 左连接：左表left join 右表 on 匹配条件

SELECT S.studentName  姓名,R.subjectNo  科目号,R.studentResult  分数
FROM `result` R  LEFT JOIN `student` S
ON S.studentNo=R.studentNo;

-- 自连接
SELECT S2.`subjectName` 父课程, S1.`subjectName` 子课程 
FROM `subject_1` S1,`subject_1` S2
WHERE S2.`subjectId`=S1.`pId`;

-- 自链接 order by
SELECT S1.`subjectName` 父课程, S2.`subjectName` 子课程 
FROM `subject_1` S1,`subject_1` S2
WHERE S1.`subjectId`= S2.`pId`
ORDER BY 父课程;