mysql多表格连接查询例题二

MySql 多个表格链接查询

三个表格连接查询，首先要有一个表格里面要有另外两个表格的参数，像这样：

这样的题就有先把三个表格建起来，下面的图是建立表格，并且通过sql语句把数据添加进去，如图：

-- 学生表
create table S(
		sno int,
		sname VARCHAR(255),
		age int,
		sex VARCHAR(255)
)
INSERT INTO `S`(`sno`, `sname`, `age`, `sex`) VALUES (1, '张三', 18, '男');
INSERT INTO `S`(`sno`, `sname`, `age`, `sex`) VALUES (2, '李四', 20, '女');
INSERT INTO `S`(`sno`, `sname`, `age`, `sex`) VALUES (3, '王五', 23, '男');
INSERT INTO `S`(`sno`, `sname`, `age`, `sex`) VALUES (4, '赵六', 22, '女');

-- 课程信息表
create table C(
		cno int,
		cname VARCHAR(255),
		cteacher VARCHAR(255)
)
INSERT INTO `C`(`cno`, `cname`, `cteacher`) VALUES (1,'java','何昊');
INSERT INTO `C`(`cno`, `cname`, `cteacher`) VALUES (2, 'php','李美君');
INSERT INTO `C`(`cno`, `cname`, `cteacher`) VALUES (3, 'android','王超');

-- 选课信息表
create table SC(
		sno int,
		cno int,
		scgrade int
)
INSERT INTO `SC`(`sno`, `cno`, `scgrade`) VALUES (1,1,50);
INSERT INTO `SC`(`sno`, `cno`, `scgrade`) VALUES (2,1,66);
INSERT INTO `SC`(`sno`, `cno`, `scgrade`) VALUES (3,1,90);
INSERT INTO `SC`(`sno`, `cno`, `scgrade`) VALUES (1,2,55);
INSERT INTO `SC`(`sno`, `cno`, `scgrade`) VALUES (2,2,68);
INSERT INTO `SC`(`sno`, `cno`, `scgrade`) VALUES (3,2,61);
INSERT INTO `SC`(`sno`, `cno`, `scgrade`) VALUES (4,2,44);
INSERT INTO `SC`(`sno`, `cno`, `scgrade`) VALUES (1,3,90);
INSERT INTO `SC`(`sno`, `cno`, `scgrade`) VALUES (2,3,78);
INSERT INTO `SC`(`sno`, `cno`, `scgrade`) VALUES (3,3,22);
INSERT INTO `SC`(`sno`, `cno`, `scgrade`) VALUES (4,3,55);

然后看下面的题，一个一个来；
1，-- 查询SC表中对应何昊老师所授课程女生的信息

select S.*,C.Cteacher from SC inner join S on SC.sno=S.sno inner join C on SC.cno=C.Cno where S.sex='女'and C.Cteacher ="何昊";

2，-- 找出没有选修过何老师的课程的所有学生

select s.sname,c.cteacher  from sc INNER JOIN s on sc.sno = s.sno INNER JOIN c on sc.cno = c.cno where c.cteacher !='何昊';

3，.列出有不及格课程（成绩小于60）的学生的姓名

select DISTINCT S.sname from S inner join SC on S.sno = SC.sno where SC.scgrade<60;
select s.sname from s where s.sno not in(select sno from sc where cno =1);