mysql练习
一.创建表
1、自行创建测试数据
a.student表
CREATE TABLE `class` ( `cid` int(10) NOT NULL AUTO_INCREMENT, `caption` varchar(32) DEFAULT NULL, PRIMARY KEY (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
b.teahcher
CREATE TABLE `teacher` ( `tid` int(10) NOT NULL AUTO_INCREMENT, `tname` varchar(32) DEFAULT NULL, PRIMARY KEY (`tid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
c.student
CREATE TABLE student( stid int(10) auto_increment NOT NULL, sname varchar(32) DEFAULT NULL, gender enum('男','女') NOT NULL, class_id int(10) DEFAULT NULL, PRIMARY KEY (stid), KEY fk_class (class_id), CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class(cid) )ENGINE=INNODB DEFAULT CHARSET=utf8;
d.course
CREATE TABLE course( crid int(10) auto_increment NOT NULL, cname VARCHAR(32) DEFAULT NULL, teacher_id int(10) DEFAULT NULL, PRIMARY KEY (crid), UNIQUE KEY cname_index (cname), KEY fk_teacher (teacher_id), CONSTRAINT fk_teacher FOREIGN KEY (teacher_id) REFERENCES teacher(tid))ENGINE=INNODB DEFAULT CHARSET=utf8;
e.score
CREATE TABLE score( sid int(10) auto_increment not NULL, student_id int(10) NOT NULL, course_id int(10) NOT NULL, number int(10) DEFAULT NULL, PRIMARY KEY (sid), UNIQUE KEY stu_course (student_id,course_id), KEY fk_student_id (student_id), KEY fk_course_id (course_id), CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES student (stid), CONSTRAINT fk_course_id FOREIGN KEY (course_id) REFERENCES course (crid) )ENGINE=INNODB DEFAULT CHARSET=utf8;
2.随便几条sql
select * from student limit 3 OFFSET 2; SELECT * from score ORDER BY number desc; SELECT count(*),gender from student GROUP BY gender ORDER BY COUNT(*); select * from class LEFT JOIN student on cid=class_id; select * from score LEFT JOIN student ON student_id=stid LEFT JOIN course ON course_id=crid LEFT JOIN class ON class_id=cid LEFT JOIN teacher ON teacher_id=tid ORDER BY caption;
二.练习题
2.查询“历史”课程比“政治”课程成绩高的所有学生的学号；
SELECT * FROM ( SELECT score.student_id, score.number AS num1 FROM ( SELECT * FROM course WHERE course.cname = "历史" ) AS A LEFT JOIN score ON score.course_id = A.crid ) AS C LEFT JOIN ( SELECT score.student_id, score.number AS num2 FROM ( SELECT * FROM course WHERE course.cname = "政治" ) AS B LEFT JOIN score ON score.course_id = B.crid ) AS D ON C.student_id = D.student_id HAVING num1 > num2;
3、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT
c.student_id,
student.sname,
c.b
FROM
( SELECT student_id, AVG( number ) AS b FROM score GROUP BY student_id HAVING b > 60 ) AS c
LEFT JOIN student ON c.student_id = student.stid;
4、查询所有同学的学号、姓名、选课数、总成绩；
SELECT
A.student_id,
student.sname,
A.b,
A.c
FROM
(
SELECT
score.student_id,
count( course_id ) AS b,
sum( score.number ) AS c
FROM
score
GROUP BY
score.student_id
) AS A
LEFT JOIN student ON A.student_id = student.stid;
或者
SELECT
score.student_id,
student.sname,
count( score.course_id ),
sum( score.number )
FROM
score
LEFT JOIN student ON score.student_id = student.stid
GROUP BY
score.student_id;
5、查询姓“李”的老师的个数；
SELECT count(*) from teacher WHERE tname like "李%"
6、查询没学过“王五”老师课的同学的学号、姓名；
SELECT
student.stid,
student.sname
FROM
student
WHERE
student.stid NOT IN (
SELECT
score.student_id
FROM
score
WHERE
score.course_id IN ( SELECT crid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "王五老师" )
);
7.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
SELECT B.student_id, student.sname FROM ( SELECT score.student_id, count( student_id ) AS A FROM score WHERE score.course_id = 1 OR score.course_id = 2 GROUP BY student_id HAVING A > 1 ) AS B LEFT JOIN student ON student.stid = B.student_id; 或者 SELECT student_id, sname FROM ( SELECT student_id, course_id FROM score WHERE course_id = 1 OR course_id = 2 ) AS B LEFT JOIN student ON B.student_id = student.stid GROUP BY student_id HAVING count( student_id ) > 1
8、查询学过“赵六”老师所教的所有课的同学的学号、姓名；
SELECT B.student_id, student.sname FROM ( SELECT score.student_id, count( score.course_id ) AS A FROM score WHERE score.course_id IN ( SELECT course.crid FROM teacher LEFT JOIN course ON teacher.tid = course.teacher_id WHERE tname = "赵六老师" ) GROUP BY score.student_id HAVING A = ( SELECT count( course.crid ) FROM teacher LEFT JOIN course ON teacher.tid = course.teacher_id WHERE tname = "赵六老师" ) ) AS B LEFT JOIN student ON B.student_id = student.stid;
9.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
SELECT * FROM ( SELECT score.student_id, student.sname, score.number AS num1 FROM score LEFT JOIN student ON score.student_id = student.stid WHERE score.course_id = 2 ) AS A LEFT JOIN ( SELECT score.student_id, student.sname, score.number AS num2 FROM score LEFT JOIN student ON score.student_id = student.stid WHERE score.course_id = 1 ) AS B ON A.student_id = B.student_id HAVING num1 < num2;
10、查询有课程成绩小于60分的同学的学号、姓名；
SELECT A.student_id,student.sname FROM (SELECT DISTINCT(score.student_id) from score WHERE score.number<60) as A LEFT JOIN student ON A.student_id=student.stid;
11、查询没有学全所有课的同学的学号、姓名；
SELECT C.student_id, student.sname FROM ( SELECT score.student_id, count( course_id ) AS A FROM score GROUP BY score.student_id HAVING A != ( SELECT count( * ) AS B FROM course ) ) AS C LEFT JOIN student ON C.student_id = student.stid;
或者
SELECT student_id, sname FROM score LEFT JOIN student ON score.student_id = student.stid GROUP BY student_id HAVING count( course_id ) != ( SELECT count( 1 ) FROM course )
12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
SELECT score.student_id, student.sname FROM score LEFT JOIN student ON score.student_id = student.stid WHERE score.student_id != 1 AND score.course_id IN ( SELECT course_id FROM score WHERE student_id = 1 ) GROUP BY student_id;
13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
SELECT score.student_id, student.sname, count( score.student_id ) AS A FROM score LEFT JOIN student ON score.student_id = student.stid WHERE score.student_id != 1 AND score.course_id IN ( SELECT course_id FROM score WHERE student_id = 1 ) GROUP BY score.student_id HAVING A >= ( SELECT count( course_id ) FROM score WHERE student_id = 1 );
14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
SELECT student_id, sname FROM score LEFT JOIN student ON score.student_id = student.stid WHERE student_id IN ( SELECT student_id FROM score WHERE student_id != 1 GROUP BY student_id HAVING count( course_id ) = ( SELECT count( course_id ) FROM score WHERE student_id = 1 ) ) AND course_id IN ( SELECT course_id FROM score WHERE student_id = 1 ) GROUP BY student_id HAVING count( course_id ) = ( SELECT count( course_id ) FROM score WHERE student_id = 1 )
15、删除学习“赵六”老师课的score表记录；
DELETE FROM score WHERE course_id IN ( SELECT course.crid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = "赵六老师" )
16、向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
INSERT INTO score ( student_id, course_id, num ) SELECT stid, 2, ( SELECT avg( num ) FROM score WHERE course_id = 2 ) FROM student WHERE stid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )
17、按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
SELECT score.student_id, ( SELECT score.number FROM score LEFT JOIN course ON score.course_id = course.crid WHERE course.cname = "语文" ) AS yw, (SELECT score.number FROM score LEFT JOIN course ON score.course_id = course.crid WHERE course.cname = "物理" ) AS wl, (SELECT score.number FROM score LEFT JOIN course ON score.course_id = course.crid WHERE course.cname = "政治" ) AS zz, count( score.course_id ), AVG( score.number ) FROM score GROUP BY score.student_id DESC;
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
SELECT score.course_id,max(score.number),min(score.number) FROM score GROUP BY score.course_id;
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
SELECT course_id, avg( number ) AS avgnum, sum( CASE WHEN score.number > 60 THEN 1 ELSE 0 END ) / count( 1 ) * 100 AS percent FROM score GROUP BY course_id ORDER BY avgnum ASC, percent DESC;
