MySQL 经典练习题

– 表名和字段
–- 1.学生表
– Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
–- 2.课程表
– Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
–- 3.教师表
– Teacher(t_id,t_name) –教师编号,教师姓名
– –4.成绩表
– Score(s_id,c_id,s_score) –学生编号,课程编号,分数

– 建表
– 学生表
CREATE TABLE Student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT ‘’,
s_birth VARCHAR(20) NOT NULL DEFAULT ‘’,
s_sex VARCHAR(10) NOT NULL DEFAULT ‘’,
PRIMARY KEY(s_id)
);
– 课程表
CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT ‘’,
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);
– 教师表
CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT ‘’,
PRIMARY KEY(t_id)
);
– 成绩表
CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id,c_id)
);
– 插入学生表测试数据
INSERT INTO Student VALUES(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
INSERT INTO Student VALUES(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
INSERT INTO Student VALUES(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’);
INSERT INTO Student VALUES(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’);
INSERT INTO Student VALUES(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
INSERT INTO Student VALUES(‘06’ , ‘吴兰’ , ‘1992-03-01’ , ‘女’);
INSERT INTO Student VALUES(‘07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’);
INSERT INTO Student VALUES(‘08’ , ‘王菊’ , ‘1990-01-20’ , ‘女’);
INSERT INTO Student VALUES(‘09’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
INSERT INTO Student VALUES(‘10’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
INSERT INTO Student VALUES(‘11’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
– 课程表测试数据
INSERT INTO Course VALUES(‘01’ , ‘语文’ , ‘02’);
INSERT INTO Course VALUES(‘02’ , ‘数学’ , ‘01’);
INSERT INTO Course VALUES(‘03’ , ‘英语’ , ‘03’);

– 教师表测试数据
INSERT INTO Teacher VALUES(‘01’ , ‘张三’);
INSERT INTO Teacher VALUES(‘02’ , ‘李四’);
INSERT INTO Teacher VALUES(‘03’ , ‘王五’);

– 成绩表测试数据
INSERT INTO Score VALUES(‘01’ , ‘01’ , 80);
INSERT INTO Score VALUES(‘01’ , ‘02’ , 90);
INSERT INTO Score VALUES(‘01’ , ‘03’ , 99);
INSERT INTO Score VALUES(‘02’ , ‘01’ , 70);
INSERT INTO Score VALUES(‘02’ , ‘02’ , 60);
INSERT INTO Score VALUES(‘02’ , ‘03’ , 80);
INSERT INTO Score VALUES(‘03’ , ‘01’ , 80);
INSERT INTO Score VALUES(‘03’ , ‘02’ , 80);
INSERT INTO Score VALUES(‘03’ , ‘03’ , 80);
INSERT INTO Score VALUES(‘04’ , ‘01’ , 50);
INSERT INTO Score VALUES(‘04’ , ‘02’ , 30);
INSERT INTO Score VALUES(‘04’ , ‘03’ , 20);
INSERT INTO Score VALUES(‘05’ , ‘01’ , 76);
INSERT INTO Score VALUES(‘05’ , ‘02’ , 87);
INSERT INTO Score VALUES(‘06’ , ‘01’ , 31);
INSERT INTO Score VALUES(‘06’ , ‘03’ , 34);
INSERT INTO Score VALUES(‘07’ , ‘02’ , 89);
INSERT INTO Score VALUES(‘07’ , ‘03’ , 98);

---

– 1查询所有学生的课程及分数情况；
SELECT st.s_id,s_name,c.c_id,c.c_name,s_score FROM student st JOIN score s ON st.s_id = s.s_id JOIN course c ON c.c_id=s.c_id;

– 2查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT course.c_id FROM Course WHERE course.c_name=‘数学’;
SELECT st.s_name, s.s_id,s.s_score FROM score s JOIN student st ON st.s_id=s.s_id WHERE s.s_score<60 AND s.c_id=(SELECT course.c_id FROM Course WHERE course.c_name=‘数学’);

– 3查询同名同性学生名单，并统计同名人数
SELECT st1.s_name,st1.s_id FROM student st1 JOIN student st2 ON st1.s_id = st2.s_id WHERE st1.s_name=st2.s_name;
SELECT st1.s_name,COUNT(st1.s_name) counta FROM student st1 GROUP BY st1.s_name HAVING counta > 1;

– 4查询各科成绩前三名的记录
SELECT s.c_id,c.c_name,MAX(s.s_score) FROM score s JOIN course c ON s.c_id=c.c_id GROUP BY s.c_id,c.c_name;

– 5统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT c.c_id,c.c_name,COUNT(c.c_name) FROM Course c JOIN score s ON c.c_id=s.c_id WHERE s.s_score>85 GROUP BY c.c_name,c.c_id;
SELECT c.c_id,c.c_name,COUNT(c.c_name) FROM Course c JOIN score s ON c.c_id=s.c_id WHERE s.s_score<85 AND s.s_score>70 GROUP BY c.c_name,c.c_id;
SELECT c.c_id,c.c_name,COUNT(c.c_name) FROM Course c JOIN score s ON c.c_id=s.c_id WHERE s.s_score<70 AND s.s_score>60 GROUP BY c.c_name,c.c_id;
SELECT c.c_id,c.c_name,COUNT(c.c_name) FROM Course c JOIN score s ON c.c_id=s.c_id WHERE s.s_score<60 GROUP BY c.c_name,c.c_id;

– 6查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT s.s_id,st.s_name,COUNT(s.s_id) counts,AVG(s.s_score) 平均成绩 FROM score s JOIN student st ON s.s_id=st.s_id WHERE s.s_score<60 GROUP BY s.s_id,st.s_name HAVING counts >1 ;

– 7查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT s.c_id FROM score s WHERE s.s_id=‘01’;
SELECT s.s_id FROM score s WHERE s.c_id IN (SELECT s.c_id FROM score s WHERE s.s_id=‘01’) GROUP BY s.c_id

– 8查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
– (包括有成绩的和无成绩的)
SELECT s.s_id,st.s_name,AVG(s.s_score) avga FROM score s JOIN Student st ON s.s_id=st.s_id GROUP BY s.s_id,st.s_name HAVING avga<60;