MySQL 多表查询。

MySQL 多表查询。

---

---

表的加法 ~ union。

CREATE SCHEMA `mysql_geek` DEFAULT CHARACTER SET utf8 ;

CREATE TABLE `mysql_geek`.`course` (
  `c_id` INT NOT NULL COMMENT '课程 id。',
  `c_name` VARCHAR(45) NOT NULL COMMENT '课程名。',
  `t_id` VARCHAR(45) NOT NULL COMMENT '教师id。',
  PRIMARY KEY (`c_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE `mysql_geek`.`course2` (
  `c_id` INT NOT NULL COMMENT '课程 id。',
  `c_name` VARCHAR(45) NOT NULL COMMENT '课程名。',
  `t_id` VARCHAR(45) NOT NULL COMMENT '教师id。',
  PRIMARY KEY (`c_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

INSERT INTO `mysql_geek`.`course` (`c_id`, `c_name`, `t_id`) VALUES ('001', '语文', '0002');
INSERT INTO `mysql_geek`.`course` (`c_id`, `c_name`, `t_id`) VALUES ('002', '数学', '0001');
INSERT INTO `mysql_geek`.`course` (`c_id`, `c_name`, `t_id`) VALUES ('003', '英语', '0003');

INSERT INTO `mysql_geek`.`course2` (`c_id`, `c_name`, `t_id`) VALUES ('001', '语文', '0002');
INSERT INTO `mysql_geek`.`course2` (`c_id`, `c_name`, `t_id`) VALUES ('004', '计算机', '0004');
INSERT INTO `mysql_geek`.`course2` (`c_id`, `c_name`, `t_id`) VALUES ('005', '数据库', '0005');

加法 union。

SELECT 
    c_id, c_name
FROM
    course 
UNION SELECT 
    c_id, c_name
FROM
    course2;

重复的数据没有了。

如果要显示重复数据，使用 union all。

SELECT 
    c_id, c_name
FROM
    course 
UNION ALL SELECT 
    c_id, c_name
FROM
    course2;

---

表的联结。

交叉连接（笛卡尔积）/ 等值连接。

eg. 扑克牌。

---

内连接 ~ (inner) join。

select st.s_id, st.s_name, sc.c_id
from student st
inner join score sc
on st.s_id = sc.s_id;

---

左外连接 ~ left (outer) join。

select st.s_id, st.s_name, sc.s_score
from student st left join score sc
on st.s_id = sc.s_id;

select st.s_id, st.s_name, sc.c_id
from student st left join score sc
on st.s_id = sc.s_id
where sc.s_id is null;

---

右外连接 ~ right (outer) join。

select st.s_id, st.s_name, sc.s_score
from student st right join score sc
on st.s_id = sc.s_id

select st.s_id, st.s_name, sc.s_score
from student st right join score sc
on st.s_id = sc.s_id
where sc.s_id is null;

---

全连接 ~ full join。

MySQL 不支持全连接。

---

联结应用案例。

-- 查询所有学生的学号、姓名、选课数、总成绩。
SELECT 
    st.s_id, st.s_name, COUNT(sc.s_score), SUM(sc.s_score)
FROM
    student st
        LEFT JOIN
    score sc ON st.s_id = sc.s_id
GROUP BY st.s_id;

-- 查询平均成绩大于 85 的所有学生的学号、姓名和平均成绩。
SELECT 
    st.s_id, st.s_name, AVG(sc.s_score) average_score
FROM
    student st
        LEFT JOIN
    score sc ON st.s_id = sc.s_id
GROUP BY s_id
HAVING average_score > 85;

-- 查询学生选课情况。
SELECT 
    st.s_id, st.s_name, c.c_id, c.c_name
FROM
    student st
        INNER JOIN
    score sc ON st.s_id = sc.s_id
        INNER JOIN
    course c ON sc.c_id = c.c_id;

---

CASE 表达式。

SELECT 
    s_id,
    c_id,
    s_score,
    (CASE
        WHEN s_score >= 60 THEN '及格'
        WHEN s_score < 60 THEN '不及格'
        ELSE NULL
    END) AS '是否及格'
FROM
    score;

-- 查询每门课程的及格人数和不及格人数。
-- 先查出每门课程的人数。
SELECT 
    c_id, COUNT(s_id) AS counts
FROM
    score
GROUP BY c_id;
-- 将 COUNT(s_id) AS counts 改为及格人数和不及格人数即可。
SELECT 
    c_id,
    SUM(CASE
        WHEN s_score >= 60 THEN 1
        ELSE 0
    END) AS count_ok,
    SUM(CASE
        WHEN s_score < 60 THEN 1
        ELSE 0
    END) AS count_no
FROM
    score
GROUP BY c_id;

-- 使用分段 [100 ~ 85], [85 ~ 70], [70 ~ 60], [<60] 来统计各科成绩。
SELECT 
    sc.c_id,
    c.c_name,
    SUM(CASE
        WHEN sc.s_score BETWEEN 85 AND 100 THEN 1
        ELSE 0
    END) AS '[100 ~ 85]',
    SUM(CASE
        WHEN sc.s_score BETWEEN 70 AND 85 THEN 1
        ELSE 0
    END) AS '[85 ~ 70]',
    SUM(CASE
        WHEN sc.s_score BETWEEN 60 AND 60 THEN 1
        ELSE 0
    END) AS '[70 ~ 60]',
    SUM(CASE
        WHEN sc.s_score < 60 THEN 1
        ELSE 0
    END) AS '[<60]'
FROM
    score sc
        RIGHT OUTER JOIN
    course c ON sc.c_id = c.c_id
GROUP BY sc.c_id , c.c_name;
-- select 中的列名必须是 group by 中的列名。