肖sir__mysql之三表__008
mysql之三表
create table student(
stu_no int,
stu_name varchar(10),
sex char(1),
age int(3),
edit varchar(20) )
DEFAULT charset=utf8;
insert into student values
(1,‘wang’,‘男’,21,‘hello’),
(2,‘小明’,‘女’,22,‘haha2’),
(3,‘hu’,‘女’,23,‘haha3’),
(4,‘li’,‘男’,25,‘haha4’);
create table course(
c_no int,
c_name varchar(10))
DEFAULT charset=utf8;
insert into course values
(1,‘计算机原理’),
(2,‘java’),
(3,‘c’),
(4,‘php’),
(5,‘py’);
#drop table sc;
create table sc(
sc_no int,
stu_no int,
c_no int,
score int(3))
DEFAULT charset=utf8;
insert into sc values
(1,1,1,80),
(2,2,2,90),
(3,2,1,85),
(4,2,3,70),
(5,2,4,95),
(6,2,5,89);
select * from student;
select * from course;
select * from sc;
Student学生表（学号、姓名、性别、年龄、编辑）
Course课程表（编号、课程名称）
sc选课表（选课编号、学号、课程编号、成绩）
（1）写一个SQL语句，查询选修了“计算机原理”的学生学号和姓名
结果：学生学号(stu_no)和姓名(stu_name)
条件：选修了“计算机原理” c_name=‘“计算机原理” ’
思路：合表 and 条件（c_name=’“计算机原理” '） 显示学生学号(stu_no)和姓名(stu_name)
方法1：
select t.stu_no ‘学号’,t.stu_name ‘姓名’ from student t,course c,sc s where t.stu_no = s.stu_no and c.c_no = s.c_no and c.c_name = ‘计算机原理’;
方法2：
select student.stu_no,student.stu_name from student LEFT JOIN sc on sc.stu_no=student.stu_no LEFT JOIN course on course.c_no=sc.c_no where course.c_name=‘计算机原理’
方法3：
select stu_no,stu_name from student where stu_no in(SELECT stu_no from sc c where c_no=(select c_no from course b where c_name=‘计算机原理’))
（2）写一个SQL语句，查询“小明”同学选修的课程名称
结果：课程名称 c_name
条件：“小明”同学 stu_name= ‘ 小明’
思路：合表 接条件 stu_name= ‘ 小明’
方法1·：select c.c_name ‘课程名称’ from student t,course c,sc s where
t.stu_no = s.stu_no and c.c_no = s.c_no and t.stu_name = ‘小明’;
（3）写一个SQL语句，查询选修了5门课程的学生学号和姓名
结果：学生学号、姓名
条件：选修了5门课程 count（c_name）=5
思路：合表 ，函数count =5
方法1：（错误）
select stu_name,a.stu_no from student a inner join sc b on a.stu_no=b.stu_no inner join course c on b.c_no=c.c_no group by stu_name having count(stu_name)=5 ;
方法2：（错误）
select student.stu_no,student.stu_name from student LEFT JOIN sc on sc.stu_no=student.stu_no LEFT JOIN course on course.c_no=sc.c_no GROUP BY sc.stu_no HAVING count(sc.stu_no)=5
方法3：
SELECT stu_no,stu_name from student where stu_no=(
select a.stu_no from student a inner join sc b on a.stu_no=b.stu_no inner join course c on b.c_no=c.c_no group by a.stu_no having count(a.stu_no)=5 );
总结格式：
1、三表隐藏内连接
格式：
select * FROM 表1 ,表2,表3 where 表1.关联字段=表3.关联字段 and 表2.关联字段=表3.关联字段 ;
案例：select * FROM student a,sc b,course c where a.stu_no=b.stu_no and b.c_no=c.c_no ;
2、三表普通内连接
格式：select * FROM 表1 inner join 表3 on 表1.关联字段=表3.关联字段 inner join 表2 on 表3.关联字段=表3.关联字段 ;
案例：select * FROM student a INNER JOIN sc b on a.stu_no=b.stu_no INNER JOIN course c on b.c_no=c.c_no ;
3、三表左连接
格式：select * FROM 表1 left join 表2 on 表1.关联字段=表3.关联字段 left join 表2 on 表2.关联字段=表3.关联字段 ;
案例：select * FROM student a left JOIN sc b on a.stu_no=b.stu_no left JOIN course c on b.c_no=c.c_no ;
4、三表右连接
格式：select * FROM 表1 right join 表2 on 表1.关联字段=表3.关联字段 right join 表2 on 表2.关联字段=表3.关联字段 ;
案例：select * FROM student a right JOIN sc b on a.stu_no=b.stu_no right JOIN course c on b.c_no=c.c_no ;
5、先合两个表，在合另一张表
格式：select * from （select 字段名 FROM 表1 right join 表2 on 表1.关联字段=表3.关联字段 ）别名，表2 where
合表.关联字段=表3.关联字段
select * from (select a.stu_no,stu_name,sex ,age ,edit ,sc_no ,c_no ,score FROM student a right JOIN sc b on a.stu_no=b.stu_no)s ,course c where s.c_no=c.c_no ;
6、把三表单成单表理解
(SELECT b.,c. FROM sc a,student b,course c WHERE a.stu_no=b.stu_no AND a.c_no=c.c_no)s 当成单表
select * from (SELECT b.,c. FROM sc a,student b,course c WHERE a.stu_no=b.stu_no AND a.c_no=c.c_no)s