SQL联合查询

MySQL系列：

初识MySQL，MySQL常用数据类型和表的操作，增删改查(CRUD)操作(总),数据库约束数据库设计

#班级表
drop table if exists class;
create table class(
id bigint primary key auto_increment,
name varchar(20)
);
#学生,课程与成绩的多对多关系
drop table if exists student;
create table student(
id bigint primary key auto_increment,
name varchar(20)not null,
sno varchar(10) not null,
age int default 18,
gender tinyint(1),
enroll_date date,
class_id bigint,
foreign key(class_id) references class(id)
);
#课程表
drop table if exists course;
create table course(
id bigint primary key auto_increment,
name varchar(20)
);
#成绩表
drop table if exists score;
create table score(
id bigint primary key auto_increment,
score float,
student_id bigint,
course_id bigint,
foreign key (student_id) references student(id),
foreign key (course_id) references course(id)
);
  
# 课程表
insert into course (name) values ('Java'), ('C++'), ('MySQL'), ('操作系统'), ('计算机网络'), ('数据结构');
# 班级表
insert into class(name) values ('Java001班'), ('C++001班'), ('前端001班');
# 学生表
insert into student (name, sno, age, gender, enroll_date, class_id) values 
('唐三藏', '100001', 18, 1, '1986-09-01', 1),
('孙悟空', '100002', 18, 1, '1986-09-01', 1),
('猪悟能', '100003', 18, 1, '1986-09-01', 1),
('沙悟净', '100004', 18, 1, '1986-09-01', 1),
('宋江', '200001', 18, 1, '2000-09-01', 2),
('武松', '200002', 18, 1, '2000-09-01', 2),
('李逹', '200003', 18, 1, '2000-09-01', 2),
('不想毕业', '200004', 18, 1, '2000-09-01', 2);

# 成绩表
insert into score (score, student_id, course_id) values
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
(60, 2, 1),(59.5, 2, 5),
(33, 3, 1),(68, 3, 3),(99, 3, 5),
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
(81, 5, 1),(37, 5, 5),
(56, 6, 2),(43, 6, 4),(79, 6, 6),
(80, 7, 2),(92, 7, 6);

1.内连接

语法:
1 select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;
2 select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 其他条件;

查询所有学生的考试成绩:

select s.name,c.name 课程,sc.score 分数 from score sc,student s,course c
    where c.id=sc.course_id
     and s.id=sc.student_id;

select s.name,c.name as course,sc.score 
from student s
join score sc on s.id=sc.student_id
join course c on sc.course_id=c.id;

2.外连接

外连接分为左外连接、右外连接和全外连接三种类型，MySQL不⽀持全外连接。
• 左外连接：返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对
应字段会显⽰为NULL。
• 右外连接：与左外连接相反，返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记
录，则结果集中对应字段会显⽰为NULL。
• 全外连接：结合了左外连接和右外连接的特点，返回左右表中的所有记录。如果某⼀边表中没有匹
配的记录，则结果集中对应字段会显⽰为NULL。

SELECT c.name AS course,COUNT(sc.student_id) AS student_count
FROM score sc
RIGHT JOIN course c ON sc.course_id=c.id
GROUP BY c.name;

SELECT s.name,COUNT(sc.course_id) AS course_count
FROM student s
LEFT JOIN score sc ON s.id=sc.student_id
GROUP BY s.name;

左外连接:

select s.name,sc.score from student s left join score sc on sc.student_id=s.id;

统计所有学生的选课情况（含未选课学生）

右连接不含未选课的学生：

select s.name,sc.score from student s right join score sc on sc.student_id=s.id;

3.自连接

⾃连接是⾃⼰与⾃⼰取笛卡尔积，可以把⾏转化成列，在查询的时候可以使⽤where条件对结果进⾏过滤，或者说实现⾏与⾏之间的⽐较。在做表连接时为表起不同的别名

再查询成绩表中，JAVA成绩⽐MySQL成绩好的信息：

select st.name,c.name 班级,s1.score MySQL成绩,s2.score JAVA成绩 from score s1,score s2,course c1,course c2,student st, class c
          where s1.student_id=s2.student_id
          and s2.course_id=c2.id
          and s1.course_id=c1.id
          and c1.name='MySQL'
          and c2.name='java'
     and st.id=s1.student_id
     and st.class_id=c.id
     and s1.score>s2.score;

4.子查询

单⾏⼦查询：
查询唐三藏的同班同学

 select * from student where class_id=(select class_id from student s where s.name='唐三藏');

多行子查询:
查询"MySQL"或"Java"课程的成绩信息

select * from score where course_id in(select c.id from course c where c.name='MySQl' or c.name='JAVA');

多列子查询

insert into score(score,student_id,course_id) values(70.5,1,1),(98.5,1,3),(33,1,5);

 select * from score where(score,student_id,course_id)in(select score ,student_id ,course_id from score  group by score,student_id,course_id having count(*)>1);

在from⼦句中使⽤⼦查询

     select * from score sc1,(select avg(sc2.score)score from score sc2, student s,class c 
     where sc2.student_id=s.id
     and s.class_id=c.id
     and c.name='java001班')temp 
     where sc1.score>temp.score;

5.合并查询

   create table student1 like student;
     insert into student1(name,sno,age,gender,enroll_date,class_id) values ('张三',100008,18,1,'2001-09-01',2),('孙悟空', '100002', 18, 1, '1986-09-01', 1),('李四',100009,18 ,1,'2001-09-01',2),('王五',100010,18,1,'2001-09-01',3);

Union:
该操作符⽤于取得两个结果集的并集。当使⽤该操作符时，会⾃动去掉结果集中的重复⾏。

Union all
该操作符⽤于取得两个结果集的并集。当使⽤该操作符时，不会去掉结果集中的重复⾏

6.插入查询

语法:INSERT INTO table_name [(column [, column ...])] SELECT ..

将student表中C++001班的学⽣复制到student1表中:

insert into student1 (name,sno,age,gender,enroll_date,class_id) select s.name,s.sno, s.age,s.gender,s.enroll_date,s.class_id from student s,class c where  s.class_id=c.id
 and c.name='C++001班';