MySQL（二）创建大学数据库以及多表查询

大学数据库创建==>传送门链接: link.
先把上次遗留下来的问题给解决

//10、创建新表stu_new，该新表中包含学号、课程号和总评成绩。其中总评成绩=daily*0.2+final*0.8,查看该新表的结构
CREATE TABLE IF NOT EXISTS `stu_new`(
   `学号` INT UNSIGNED AUTO_INCREMENT,
   `课程号` VARCHAr(100) NOT NULL,
   `总评成绩` VARCHAr(40) NOT NULL,
   PRIMARY KEY ( `学号` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECt DISTINCT sname 姓名
FROM student stu
INNER JOIN score s
WHERe sex = '女' and s.final > 90;

SELECt DISTINCT studentno
FROM score
INNER JOIN course	
WHERe cname = 'Java语言' AND score.final > 90;

SELECt stu.studentNo 学号, stu.sname 姓名, s.final 期末成绩
FROM student stu
INNER JOIN score s ON stu.studentNo = s.studentno
WHERe s.courseno = 'c05103';

SELECt studentNo 学号, sname 姓名
FROM student
WHERe studentno in(	
	SELECt studentno
	FROM score
	GROUP BY studentno
	HAVINg COUNT(*)> 2
);


SELECt studentno 学号
FROM score
WHERe courseno IN(
	SELECt courseno
	FROM teach_course
	WHERe
		teacherno = (
		SELECt teacherno
		FROM teacher
		WHERe
			tname LIKE "苏%"
	)
)
GROUP BY studentno;


SELECt s.studentno 学号, stu.sname 姓名, TRUNCATE(avg(final), 1) 期末平均成绩
FROM student stu
JOIN score s ON s.studentno = stu.studentNo
GROUP BY s.studentno, stu.sname
ORDER BY 期末平均成绩 DESC;


SELECt stu.studentno 学号, stu.sname 姓名,
	c.cname 课程名, s.final 期末成绩,
	TRUNCATE(c.period/16, 1) 学分
FROM student stu, score s, course c
WHERe s.studentno = stu.studentNo AND c.courseno = s.courseno;


SELECt *
FROM student
WHERe departmentname = (
	SELECt departmentname
	FROM student
	WHERe sname = '王伟'
);


SELECt stu.studentno 学号, stu.sname 姓名
FROM student stu
JOIN score s ON stu.studentNo = s.studentno
WHERe courseno
	= (SELECt courseno FROM course WHERe cname = '数据库原理');


SELECt s.studentno 学号, s.courseno 课程号,
	(daily*0.3 + final*0.7) 总评成绩
FROM score s
WHERe (daily*0.3 + final*0.7) > 85 AND final > 90;


SELECt stu.sname 姓名, s.courseno 课程号, s.final 期末成绩
FROM student stu
INNER JOIN score s ON s.studentno = stu.studentNo
INNER JOIN (SELECt studentno, AVG(final) avg FROM score GROUP BY studentno) f 
	ON s.studentno = f.studentNo
WHERe final < avg;



SELECt stu.studentNo 学号, stu.sname 姓名,
	stu.phone 电话, c.cname 课程名
FROM student stu
	JOIN score s ON s.studentno = stu.studentNo
	JOIN course c ON c.courseno = s.courseno
WHERe final > 90;


SELECt stu.studentNo 学号,stu.sname 姓名,s.final 期末成绩
FROM student stu, score s
WHERe stu.studentNo = s.studentno AND final>
      all(SELECt final FROM score WHERe courseno='c05103');


SELECt c.cname 课程名, c.cpno 先行课, s.cpno 间接先行课
FROM course c
JOIN course s ON s.courseno = c.cpno;
ORDER BY '先行课';



SELECt s.studentno 学号, s.courseno 课程号
FROM score s
INNER JOIN (SELECt studentno, avg(final) avg FROM score GROUP BY studentno) a ON
	s.studentno = a.studentno
WHERe s.final > avg;



SELECt sname 学生姓名, departmentname 所在系,
	TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) 年龄
FROM student
WHERe TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) < (
	SELECt MIN(TIMESTAMPDIFF(YEAR, birthdate, CURDATE()))
	FROM student
	WHERe departmentname = '计数院'
);


SELECt sname
FROM student
WHERe studentNo in(
	SELECt studentno
	FROM score
	GROUP BY studentno
	HAVINg COUNT(*) = (
		SELECt COUNT(*)
		FROM course
		WHERe type = '选修'
	)
);


SELECt studentno 
FROM	score
WHERe
	NOT EXISTS ( SELECt courseno FROM score WHERe a.studentno = studentno AND studentno = 18125111109 ) 
	AND studentno != 18125111109 
GROUP BY studentno;




SELECt t.teacherno 教工号, t.tname 教师姓名,
	c.cname 课程名, tc.teachtime 授课时间
FROM teacher t
INNER JOIN teach_course tc ON t.teacherno = tc.teacherno
INNER JOIN course c ON tc.courseno = c.courseno
WHERe t.tname LIKE '苏%'
	AND YEAR(teachtime) = 2021
	AND MonTH(teachtime) = 3;


SELECt courseno 课程号
FROM course c
WHERe NOT EXISTS(
	SELECt *
	FROM score
	WHERe c.courseno = courseno
);

照旧给几张运行成功的截图



多表查询写起来感觉要比单表查询难得多，反正我是感觉不容易。
这次就先这样，其实还有一道附加题，我找时间做完了再分享给大家吧