#查询秦建兴是哪个班的多表连接查询
select stu_name,class_name from student,class
where stu_name='秦建兴' and
student.class_no=class.class_no
#使用连接子句完成的查询
select stu_name,class_name
from student join class on student.class_no=class.class_no
where stu_name='秦建兴';
#查询男同学的姓名及其所在系部
select stu_name,stu_sex,dep_name from student,class
where stu_sex='男' and
student.class_no=class.class_no;
#两个表的等值连接,有同名列存在,不希望同名列存在,需要做自然连接
select * from student,class
where student.class_no=class.class_no;
#改成自然连接,选取其中一张表的同名列,用表名,字段名进行选取,其他列任选
select student.class_no,stu_name,class_name,dep_name from student,class
where student.class_no=class.class_no;
select student.*,class_name,dep_name from student,class
where student.class_no=class.class_no;
#查询所有学生的姓名,课程名及成绩
select Stu_name,Course_name,Score from student,course,score
where student.Stu_no=score.Stu_no and score.Course_no=course.Course_no;
#查询男学生的姓名,课程名及成绩
select Stu_name,Course_name,Score from student,course,score
where student.Stu_no=score.Stu_no and score.Course_no=course.Course_no and stu_sex='男';
delete from score where course_no='010002';
#inner join(默认连接),left join(留下左表中没有与右表等值的信息时,依然要把左表中的数据显示出来),right join,full join
select course_name,score from course left join score on course.course_no=score.course_no;