-- 表结构：
-- Student学生表（学号、姓名、性别、年龄、编辑）
-- Course课程表（编号、课程名称）
-- sc选课表（选课编号、学号、课程编号、成绩）
-- （1）写一个SQL语句，查询选修了“计算机原理”的学生学号和姓名
-- （2）写一个SQL语句，查询“小明”同学选修的课程名称
#（3）写一个SQL语句，查询选修了5门课程的学生学号和姓名
-----------------------------------------------------------------------------------------------------------------------
建表语句：
drop table student ;
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');
#rop 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 sc ;
select * from student ;
select * from course ;
------------------------------------------------------------------------------------------------------
表的内容：sc选修表
student学生表
course课程表
三表合表图：
-----------------------------------------------------------------------------------------------------
三表：
格式一：select * from a, b, c where a.id=c.id and b.no=c.no
例题：
select a.stu_no,a.stu_name from student a,course b,sc c where a.stu_no=c.stu_no and b.c_no=c.c_no and c_name='计算机原理';
格式二：select * from a，b where a.id=b.id and b.no =(SELECT c_no from c where 条件);
例题：先找一个1表的条件，在合并2，3表用1表的结果做条件
select student.stu_no,stu_name from student,sc where student.stu_no=sc.stu_no and sc.c_no =(SELECT c_no from course where c_name='计算机原理');
格式三：select * from a inner join b on a.id=b.id inner Join c on b.no=c.no ;
格式四：select * from a left join b on a.id=b.id left Join c on b.no=c.no ;
格式五：select * from a right join b on a.id=b.id right Join c on b.no=c.no ;
-----------------------------------------------------------------------------------------
练习：
-- （1）写一个SQL语句，查询选修了“计算机原理”的学生学号和姓名
-- （2）写一个SQL语句，查询“小明”同学选修的课程名称
#（3）写一个SQL语句，查询选修了5门课程的学生学号和姓名
第一题：
方法一：
select s.stu_no ,s.stu_name from student s ,sc,course c where s.stu_no=sc.stu_no and sc.c_no=c.c_no and c.c_name='计算机原理';
方法二：
select s.stu_no,s.stu_name from student s ,sc where s.stu_no=sc.stu_no and sc.c_no=(select c_no from course where c_name='计算机原理');
方法三：
select stu_no, stu_name from student where stu_no in (select stu_no from sc where c_no=(select c_no from course where c_name='计算机原理'));
方法四：
select stu_no,stu_name from student where stu_no in(select stu_no from course inner join sc on course.c_no=sc.c_no where c_name="计算机原理");
方法五： innere join
select student.stu_no,stu_name from student inner join sc on student.stu_no=sc.stu_no INNER JOIN course on sc.c_no=course.c_no where c_name='计算机原理';
方法六：left join
select student.stu_no,stu_name from student left join sc on student.stu_no=sc.stu_no left JOIN course on sc.c_no=course.c_no where c_name='计算机原理';
方法七： right join
select student.stu_no,stu_name from student right join sc on student.stu_no=sc.stu_no right JOIN course on sc.c_no=course.c_no where c_name='计算机原理';
第二题：
方法一：
select course.c_name from course ,sc where course.c_no=sc.c_no and sc.stu_no=(select stu_no from student where stu_name="小明" );
方法二：
select w.c_name from (select stu_name,c_name from student,sc,course where student.stu_no=sc.stu_no and sc.c_no=course.c_no) w where w.stu_name='小明';
方法三：
select c_name from course where c_no in (select c_no from student inner join sc on student.stu_no=sc.stu_no where stu_name="小明");
方法四：
select c.c_name from student s ,sc, course c where s.stu_no=sc.stu_no and sc.c_no=c.c_no and stu_name='小明';
方法五：
select c_name from course where c_no in(select c_no from sc where stu_no=(select stu_no from student where stu_name ='小明'));
备注：c_no是选修课程1,2,3,4,5 表示5门，2表示stu_no 学生号；
第三题：
方法一：
select s.stu_no ,s.stu_name from student s ,sc, course c where s.stu_no=sc.stu_no and c.c_no=sc.c_no group by stu_name having count(stu_name)=5;
方法二：
select w.stu_no,w.stu_name from (select student.stu_no,stu_name from student,sc,course where student.stu_no=sc.stu_no and sc.c_no=course.c_no) w group by w.stu_name having count(w.stu_name)=5;
方法三：
select student.stu_no,student.stu_name from student ,
(select s1.stu_no,count(s1.stu_no) c from sc s1 right join student s2 on s1.stu_no=s2.stu_no
group by s1.stu_no having count(s1.stu_no)=5)h where
student.stu_no=h.stu_no;
