CREATE DATABASE IF NOT EXISTS school;USE school;CREATE TABLE tblStudent(StuId INT(4) NOT NULL PRIMARY KEY,StuName VARCHAR(20) NOT NULL,StuAge INT(3),StuSex CHAR(2));CREATE TABLE tblCourse(CourseId INT(4) NOT NULL PRIMARY KEY,CourseName VARCHAR(20) NOT NULL,TeaId INT(4));CREATE TABLE tblScore(StuId INT(4) NOT NULL PRIMARY KEY,CourseId INT(4),Score DECIMAL(20));CREATE TABLE tblTeacher(TeaId INT(4)NOT NULL PRIMARY KEY,TeaName VARCHAR(20));DROP TABLE tblTeacher;

 

INSERT INTO tblstudent VALUES(1,'张三',50,'男'),(2,'李四',50,'男'),(3,'王五',50,'男');INSERT INTO tblstudent VALUES(4,'张小红',20,'女'),(5,'李小三',20,'女'),(6,'王小妞',20,'女');INSERT INTO tblcourse VALUES(1,'大学语文',2),(2,'大学英语',1),(3,'芭蕾舞',2);INSERT INTO tblcourse VALUES(4,'大学体育',1),(5,'游泳',3),(6,'马克思主义哲学',2);DELETE FROM tblcourse;INSERT INTO tblteacher VALUES(1,'小葱拌豆腐'),(2,'微冷的雨'),(3,'帅的离谱');DELETE FROM tblteacher;INSERT INTO tblscore VALUES(1,1,100),(2,2,99),(3,3,98),(4,1,100),(5,2,99),(6,3,98);INSERT INTO tblcourse VALUES(4,'大学体育',1),(5,'游泳',3),(6,'马克思主义哲学',2);INSERT INTO tblscore VALUES(1,1,100),(1,2,99),(1,3,92),(1,4,80),(1,5,99),(1,6,61);INSERT INTO tblscore VALUES(2,1,87),(2,2,99),(2,3,60),(2,4,100),(1,5,60),(2,6,98);INSERT INTO tblscore VALUES(3,1,98),(3,2,85),(3,3,66),(3,4,72),(1,5,99),(3,6,98);INSERT INTO tblscore VALUES(4,1,77),(4,2,99),(4,3,98),(4,4,99),(1,5,62),(4,6,88);INSERT INTO tblscore VALUES(5,1,66),(5,2,95),(5,3,94),(5,4,80),(1,5,99),(5,6,98);INSERT INTO tblscore VALUES(6,1,100),(6,2,99),(6,3,98),(6,4,92),(1,5,85),(6,6,70);DELETE FROM tblscore;ALTER TABLE tblcourse MODIFY COLUMN CourseId INT(3) ZEROFILL;

 

-- 1、查询“001”课程比 ”002“ 课程成绩高的所有学生的学号;SELECT StuId FROM tblStudent s1 WHERE(SELECT Score FROM tblScore t1 WHERE t1.`StuId`=s1.`StuId` AND t1.`CourseId`=001)>(SELECT Score FROM tblScore t2 WHERE t2.stuId=s1.`StuId` AND t2.`CourseId`=002);-- 2、查询平均成绩大于60分的同学的学号和平均成绩;SELECT StuId,AVG(Score) AS AvgScore FROM tblScore GROUP BY StuId HAVING AVG(Score)>60;-- 3、查询所有同学的学号、姓名、选课数、总成绩;SELECT StuId,StuName,(SELECT COUNT(CourseId) FROM tblscore t1 WHERE t1.StuId=s1.StuId) SelCourses,(SELECT SUM(Score) FROM tblscore t2 WHERE t2.StuId=S1.StuId) SumScoreFROM tblstudent s1;SELECT s.`StuId`, s.`StuName`,COUNT(courseId) SelCourses,SUM(score) SumScoreFROM tblstudent s, tblscore sc WHERE s.`StuId`=sc.`StuId` GROUP BY s.`StuId`-- 4、查询姓 ”李“ 的老师的个数;SELECT COUNT(*) FROM tblTeacher WHERE TeaName LIKE '李%';-- 5、查询没学过 ”叶平“ 老师课的同学的学号、姓名;SELECT StuId,StuName FROM tblStudentWHERE StuId NOT IN(SELECT StuId FROM tblScore scINNER JOIN tblCourse cu ON sc.`CourseId`=cu.`CourseId`INNER JOIN tblTeacher tc ON cu.`TeaId`=tc.`TeaId`WHERE tc.`TeaName`='叶平');