MySQL连接查询（头歌答案）_头哥mysql内连接查询

第1关：连接查询

1、多表查询不需要设定表间的联接条件。（B）
A、对
B、错

2、只有满足查询条件的记录才包含在查询结果中，这种连接为___C___。
A、左连接
B、右连接
C、内部连接
D、完全连接

3、目前，MySQL支持的连接形式有__ABC____。
A、内连接
B、左连接
C、右连接
D、全连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

第2关：连接查询-练习

use course;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
    
   select teacher.teacher_id,teacher.teacher_name,department.department_name 
   from teacher inner join department 
   on teacher.department_id=department.department_id;
   
   select student.student_id,student.student_name,classes.class_name,classes.`year`,department.department_name
   from student 
   inner join classes on student.class_id=classes.class_id
   inner join department on student.department_id=department.department_id;
  
  
 /**********End**********/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

第3关：连接查询-练习一

第一种方法

use course;
/****请在此编写代码，操作完毕之后点击评测******/

/**********Begin**********/


select s.student_id, s.student_name, c.course_name, c.term, c.period, c.credit, c.attend_time, c.attend_address from student s
inner join choose ch on s.student_id = ch.student_id
inner join course c on ch.course_id = c.course_id;

select d.department_id, d.department_name
from department d
left join teacher t
on d.department_id = t.department_id
where d.department_id like "2%";
/**********End**********/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

第二种方法

use course;
/****请在此编写代码，操作完毕之后点击评测******/

/**********Begin**********/


select s.student_id, s.student_name, c.course_name, c.term, c.period, c.credit, c.attend_time, c.attend_address from student s
inner join choose ch on s.student_id = ch.student_id
inner join course c on ch.course_id = c.course_id;

select d.department_id, d.department_name
from department d
left join teacher t
on d.department_id = t.department_id where t.teacher_id IS  NULL;
/**********End**********/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

第4关：连接查询-练习二

use course;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select distinct s.student_id,s.student_name,c.term,c.course_name,ch.score from choose ch
  inner join student s on ch.student_id=s.student_id
  inner join course c on c.course_id=ch.course_id
  where c.term="第一学期" and ch.score >= 70;
 /**********End**********/
1
2
3
4
5
6
7
8
9