mysql20170404代码实现

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) SumScore
FROM tblstudent s1;

SELECT s.`StuId`, s.`StuName`,COUNT(courseId) SelCourses,SUM(score) SumScore
FROM 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 tblStudent
WHERE StuId NOT IN
(
SELECT StuId FROM tblScore sc
INNER JOIN tblCourse cu ON sc.`CourseId`=cu.`CourseId`
INNER JOIN tblTeacher tc ON cu.`TeaId`=tc.`TeaId`
WHERE tc.`TeaName`='叶平'
);