【MySQL】 MySQL的增删改查(进阶)--贰（二）

-- 学生表、成绩表、课程表3张表关联查询
SELECT
 stu.id,
 stu.sn,
 stu.NAME,
 stu.qq_mail,
 sco.score,
 sco.course_id,
 cou.NAME
 FROM
 student stu
 JOIN score sco ON stu.id = sco.student_id
 JOIN course cou ON sco.course_id = cou.id
 ORDER BY
 stu.id;

-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

select * from student stu left join score sco on stu.id=sco.student_id;

select * from score sco right join student stu on stu.id=sco.student_id;

SELECT
 stu.id,
 stu.sn,
 stu.NAME,
 stu.qq_mail,
 sco.score,
 sco.course_id,
 cou.NAME
 FROM
 student stu
 LEFT JOIN score sco ON stu.id = sco.student_id
 LEFT JOIN course cou ON sco.course_id = cou.id
 ORDER BY
 stu.id;

-- 先查询“计算机原理”和“Java”课程的id
select id,name from course where name='Java' or name='计算机原理';
SELECT
 s1.*
 FROM
 score s1
 JOIN score s2 ON s1.student_id = s2.student_id
 AND s1.score < s2.score
 AND s1.course_id = 1
 AND s2.course_id = 3;

SELECT
 stu.*,
 s1.score Java,
 s2.score 计算机原理
 FROM
 score s1
 JOIN score s2 ON s1.student_id = s2.student_id
 JOIN student stu ON s1.student_id = stu.id
 JOIN course c1 ON s1.course_id = c1.id
 JOIN course c2 ON s2.course_id = c2.id
 AND s1.score < s2.score
 AND c1.NAME = 'Java'
 AND c2.NAME = '计算机原理';

select * from student where classes_id=(select classes_id from student where
name='不想毕业');

-- 使用IN
select * from score where course_id in (select id from course where
 name='语文' or name='英文');
-- 使用 NOT IN
select * from score where course_id not in (select id from course where
 name!='语文' and name!='英文');

-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou
 where (name='语文' or name='英文') and cou.id = sco.course_id);
-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou
 where (name!='语文' and name!='英文') and cou.id = sco.course_id);

-- 获取“中文系2019级3班”的平均分，将其看作临时表
 SELECT
 avg( sco.score ) score
 FROM
 score sco
 JOIN student stu ON sco.student_id = stu.id
 JOIN classes cls ON stu.classes_id = cls.id
 WHERE
 cls.NAME = '中文系2019级3班';

SELECT
 *
 FROM
 score sco,
 (
 SELECT
 avg( sco.score ) score
 FROM
 score sco
 JOIN student stu ON sco.student_id = stu.id
 JOIN classes cls ON stu.classes_id = cls.id
 WHERE
  cls.NAME = '中文系2019级3班'
 ) tmp
 WHERE
 sco.score > tmp.score;

select * from course where id<3
 union
 select * from course where name='英文';
-- 或者使用or来实现
select * from course where id<3 or name='英文';

-- 可以看到结果集中出现重复数据Java
 select * from course where id<3
 union all
 select * from course where name='英文';

INSERT INTO table_name [(column [, column ...])] SELECT ...

select ... from 表1，表2 where 条件
-- inner可以缺省
select ... from 表1 join 表2 on 条件 where 其他条件

select ... from 表1 left/right join 表2 on 条件 where 其他条件

select ... from 表1，表2 where 条件
-- inner可以缺省
select ... from 表1 join 表2 on 条件 where 其他条件

select ... from 表1 left/right join 表2 on 条件 where 其他条件

select ... from 表1，表1 where 条件
select ... from 表1 join 表1 on 条件

-- 单行子查询
select ... from 表1 where 字段1 = (select ... from ...);
-- [NOT] IN
select ... from 表1 where 字段1 in (select ... from ...);
-- [NOT] EXISTS
select ... from 表1 where exists (select ... from ... where 条件);
-- 临时表：form子句中的子查询
select ... from 表1， (select ... from ...) as tmp where 条件

-- UNION：去除重复数据
select ... from ... where 条件
 union
select ... from ... where 条件
-- UNION ALL：不去重
select ... from ... where 条件
 union all
select ... from ... where 条件
-- 使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致