MySQL 查询语句练习2
创建表
/* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50719 Source Host : localhost:3306 Source Database : oldbo Target Server Type : MYSQL Target Server Version : 50719 File Encoding : 65001 Date: 2017-07-26 15:46:16 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for class -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` varchar(32) NOT NULL, PRIMARY KEY (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of class -- ---------------------------- INSERT INTO `class` VALUES ('1', '三年二班'); INSERT INTO `class` VALUES ('2', '三年三班'); INSERT INTO `class` VALUES ('3', '一年二班'); INSERT INTO `class` VALUES ('4', '二年九班'); -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(32) NOT NULL, `teacher_id` int(11) NOT NULL, PRIMARY KEY (`cid`), KEY `fk_course_teacher` (`teacher_id`), CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES ('1', '生物', '1'); INSERT INTO `course` VALUES ('2', '物理', '2'); INSERT INTO `course` VALUES ('3', '体育', '3'); INSERT INTO `course` VALUES ('4', '美术', '2'); -- ---------------------------- -- Table structure for score -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `course_id` int(11) NOT NULL, `num` int(11) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_score_student` (`student_id`), KEY `fk_score_course` (`course_id`), CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES ('1', '1', '1', '10'); INSERT INTO `score` VALUES ('2', '1', '2', '9'); INSERT INTO `score` VALUES ('5', '1', '4', '66'); INSERT INTO `score` VALUES ('6', '2', '1', '8'); INSERT INTO `score` VALUES ('8', '2', '3', '68'); INSERT INTO `score` VALUES ('9', '2', '4', '99'); INSERT INTO `score` VALUES ('10', '3', '1', '77'); INSERT INTO `score` VALUES ('11', '3', '2', '66'); INSERT INTO `score` VALUES ('12', '3', '3', '87'); INSERT INTO `score` VALUES ('13', '3', '4', '99'); INSERT INTO `score` VALUES ('14', '4', '1', '79'); INSERT INTO `score` VALUES ('15', '4', '2', '11'); INSERT INTO `score` VALUES ('16', '4', '3', '67'); INSERT INTO `score` VALUES ('17', '4', '4', '100'); INSERT INTO `score` VALUES ('18', '5', '1', '79'); INSERT INTO `score` VALUES ('19', '5', '2', '11'); INSERT INTO `score` VALUES ('20', '5', '3', '67'); INSERT INTO `score` VALUES ('21', '5', '4', '100'); INSERT INTO `score` VALUES ('22', '6', '1', '9'); INSERT INTO `score` VALUES ('23', '6', '2', '100'); INSERT INTO `score` VALUES ('24', '6', '3', '67'); INSERT INTO `score` VALUES ('25', '6', '4', '100'); INSERT INTO `score` VALUES ('26', '7', '1', '9'); INSERT INTO `score` VALUES ('27', '7', '2', '100'); INSERT INTO `score` VALUES ('28', '7', '3', '67'); INSERT INTO `score` VALUES ('29', '7', '4', '88'); INSERT INTO `score` VALUES ('30', '8', '1', '9'); INSERT INTO `score` VALUES ('31', '8', '2', '100'); INSERT INTO `score` VALUES ('32', '8', '3', '67'); INSERT INTO `score` VALUES ('33', '8', '4', '88'); INSERT INTO `score` VALUES ('34', '9', '1', '91'); INSERT INTO `score` VALUES ('35', '9', '2', '88'); INSERT INTO `score` VALUES ('36', '9', '3', '67'); INSERT INTO `score` VALUES ('37', '9', '4', '22'); INSERT INTO `score` VALUES ('38', '10', '1', '90'); INSERT INTO `score` VALUES ('39', '10', '2', '77'); INSERT INTO `score` VALUES ('40', '10', '3', '43'); INSERT INTO `score` VALUES ('41', '10', '4', '87'); INSERT INTO `score` VALUES ('42', '11', '1', '90'); INSERT INTO `score` VALUES ('43', '11', '2', '77'); INSERT INTO `score` VALUES ('44', '11', '3', '43'); INSERT INTO `score` VALUES ('45', '11', '4', '87'); INSERT INTO `score` VALUES ('46', '12', '1', '90'); INSERT INTO `score` VALUES ('47', '12', '2', '77'); INSERT INTO `score` VALUES ('48', '12', '3', '43'); INSERT INTO `score` VALUES ('49', '12', '4', '87'); INSERT INTO `score` VALUES ('52', '13', '3', '87'); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `gender` char(1) NOT NULL, `class_id` int(11) NOT NULL, `sname` varchar(32) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_class` (`class_id`), CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES ('1', '男', '1', '理解'); INSERT INTO `student` VALUES ('2', '女', '1', '钢蛋'); INSERT INTO `student` VALUES ('3', '男', '1', '张三'); INSERT INTO `student` VALUES ('4', '男', '1', '张一'); INSERT INTO `student` VALUES ('5', '女', '1', '张二'); INSERT INTO `student` VALUES ('6', '男', '1', '张四'); INSERT INTO `student` VALUES ('7', '女', '2', '铁锤'); INSERT INTO `student` VALUES ('8', '男', '2', '李三'); INSERT INTO `student` VALUES ('9', '男', '2', '李一'); INSERT INTO `student` VALUES ('10', '女', '2', '李二'); INSERT INTO `student` VALUES ('11', '男', '2', '李四'); INSERT INTO `student` VALUES ('12', '女', '3', '如花'); INSERT INTO `student` VALUES ('13', '男', '3', '刘三'); INSERT INTO `student` VALUES ('14', '男', '3', '刘一'); INSERT INTO `student` VALUES ('15', '女', '3', '刘二'); INSERT INTO `student` VALUES ('16', '男', '3', '刘四'); -- ---------------------------- -- Table structure for teacher -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` varchar(32) NOT NULL, PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES ('1', '张磊老师'); INSERT INTO `teacher` VALUES ('2', '李平老师'); INSERT INTO `teacher` VALUES ('3', '刘海燕老师'); INSERT INTO `teacher` VALUES ('4', '朱云海老师'); INSERT INTO `teacher` VALUES ('5', '李杰老师'); SET FOREIGN_KEY_CHECKS=1;
1、将所有的课程的名称以及对应的任课老师姓名打印出来，如下：
mysql> SELECT cname,tname FROM course LEFT JOIN teacher ON course.`teacher_id`=teacher.`tid`; +--------+-----------------+ | cname | tname | +--------+-----------------+ | 生物 | 张磊老师 | | 物理 | 李平老师 | | 体育 | 刘海燕老师 | | 美术 | 李平老师 | +--------+-----------------+ 4 rows in set (0.00 sec)
2、查询学生表中男女生各有多少人？ 如下：
mysql> SELECT gender,COUNT(sid) FROM student GROUP BY gender; +--------+------------+ | gender | COUNT(sid) | +--------+------------+ | 女 | 6 | | 男 | 10 | +--------+------------+ 2 rows in set (0.00 sec)
3、查询物理成绩等于100的学生的姓名？如下：
mysql> SELECT sname FROM student WHERE sid IN(SELECT student_id FROM score WHERE course_id=2 AND num=100); +--------+ | sname | +--------+ | 张四 | | 铁锤 | | 李三 | +--------+ 3 rows in set (0.00 sec)
4、查询平均成绩大于八十分的同学的姓名和平均成绩，如下：
mysql> SELECT sname AS '姓名',avgnum AS '平均分' FROM student INNER JOIN (SELECT student_id,AVG(num)AS avgnum FROM score GROUP BY student_id HAVING AVG(num)>80)AS id_avgnum ON student.`sid`=id_avgnum.student_id; +--------+-----------+ | 姓名 | 平均分 | +--------+-----------+ | 张三 | 82.2500 | | 刘三 | 87.0000 | +--------+-----------+ 2 rows in set (0.00 sec) mysql> SELECT sname,AVG(num) FROM student INNER JOIN score ON student.`sid`=score.`student_id` GROUP BY student_id HAVING AVG(num)>80; +--------+----------+ | sname | AVG(num) | +--------+----------+ | 张三 | 82.2500 | | 刘三 | 87.0000 | +--------+----------+ 2 rows in set (0.01 sec)
5、查询所有学生的学号，姓名，选课数，总成绩
mysql> SELECT student.sid,sname,SUM(num),COUNT(course_id) FROM student INNER JOIN score ON student.`sid`=score.`student_id` GROUP BY student_id; +-----+--------+----------+------------------+ | sid | sname | SUM(num) | COUNT(course_id) | +-----+--------+----------+------------------+ | 1 | 理解 | 85 | 3 | | 2 | 钢蛋 | 175 | 3 | | 3 | 张三 | 329 | 4 | | 4 | 张一 | 257 | 4 | | 5 | 张二 | 257 | 4 | | 6 | 张四 | 276 | 4 | | 7 | 铁锤 | 264 | 4 | | 8 | 李三 | 264 | 4 | | 9 | 李一 | 268 | 4 | | 10 | 李二 | 297 | 4 | | 11 | 李四 | 297 | 4 | | 12 | 如花 | 297 | 4 | | 13 | 刘三 | 87 | 1 | +-----+--------+----------+------------------+ 13 rows in set (0.00 sec)
6、查询姓李老师的个数
mysql> SELECT COUNT(tid) FROM teacher WHERE tname LIKE '李%'; +------------+ | COUNT(tid) | +------------+ | 2 | +------------+ 1 row in set (0.00 sec)
7、查询没有报李平老师课的学生姓名
mysql> SELECT sname FROM student WHERE sid NOT IN(SELECT student_id FROM score WHERE course_id IN(SELECT cid FROM course WHERE teacher_id=(SELECT tid FROM teacher WHERE tname='李平老师'))); +--------+ | sname | +--------+ | 刘三 | | 刘一 | | 刘二 | | 刘四 | +--------+ 4 rows in set (0.00 sec)
8、查询物理课程比生物课程高的学生的学号
SELECT A.student_id,A.num,b.num FROM (SELECT * FROM score WHERE course_id=(SELECT cid FROM course WHERE cname="物理"))as A INNER JOIN (SELECT * FROM score WHERE course_id=(SELECT cid FROM course WHERE cname="生物"))as B ON A.student_id=B.student_id WHERE A.num>B.num
9、查询没有同时选修物理课程和体育课程的学生姓名
SELECT sname FROM student WHERE sid NOT in (SELECT student_id FROM score WHERE course_id in (SELECT cid FROM course WHERE cname="物理" OR cname="体育") GROUP BY student_id HAVING COUNT(sid)=2)
10、查询挂科超过两门(包括两门)的学生姓名和班级
SELECT sname,caption FROM student LEFT JOIN class ON student.class_id=class.cid where sid in (SELECT student_id FROM score WHERE num<60 GROUP BY student_id HAVING COUNT(sid)>1)
11 、查询选修了所有课程的学生姓名
SELECT sname FROM student WHERE sid in (SELECT student_id FROM score GROUP BY student_id HAVING COUNT(sid)!=(SELECT COUNT(cid) FROM course))
12、查询李平老师教的课程的所有成绩记录
SELECT * FROM score WHERE course_id in (SELECT cid FROM course LEFT JOIN teacher ON teacher.tid=course.teacher_id WHERE tname="李平老师")
13、查询全部学生都选修了的课程号和课程名
无
14、查询每门课程被选修的次数
mysql> SELECT course.cname,COUNT(score.course_id) FROM course INNER JOIN score ON course.cid=score.course_id GROUP BY score.course_id; +--------+------------------------+ | cname | COUNT(score.course_id) | +--------+------------------------+ | 生物 | 12 | | 物理 | 11 | | 体育 | 12 | | 美术 | 12 | +--------+------------------------+ 4 rows in set (0.00 sec)
15、查询之选修了一门课程的学生姓名和学号
mysql> SELECT student_id,student.sname,COUNT(course_id) FROM student INNER JOIN score ON student.`sid`=score.`student_id` GROUP BY student_id HAVING COUNT(course_id)=1; +------------+--------+------------------+ | student_id | sname | COUNT(course_id) | +------------+--------+------------------+ | 13 | 刘三 | 1 | +------------+--------+------------------+ 1 row in set (0.00 sec)
16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
mysql> select distinct num from score order by num desc; +-----+ | num | +-----+ | 100 | | 99 | | 91 | | 90 | | 88 | | 87 | | 79 | | 77 | | 68 | | 67 | | 66 | | 43 | | 22 | | 11 | | 10 | | 9 | | 8 | +-----+ 17 rows in set (0.00 sec)
17、查询平均成绩大于85的学生姓名和平均成绩
mysql> SELECT sname,AVG(num) FROM student INNER JOIN score ON student.`sid`=score.`student_id` GROUP BY student_id HAVING AVG(num)>85; +--------+----------+ | sname | AVG(num) | +--------+----------+ | 刘三 | 87.0000 | +--------+----------+ 1 row in set (0.00 sec)
18、查询生物成绩不及格的学生姓名和对应生物分数
mysql> SELECT student.`sname`,A.num FROM student INNER JOIN (SELECT student_id,num FROM score WHERE num<60 AND course_id=(SELECT cid FROM course WHERE cname='生物'))AS A ON student.sid=A.student_id; +--------+-----+ | sname | num | +--------+-----+ | 理解 | 10 | | 钢蛋 | 8 | | 张四 | 9 | | 铁锤 | 9 | | 李三 | 9 | +--------+-----+ 5 rows in set (0.00 sec)
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
mysql> SELECT sname FROM student INNER JOIN (SELECT student_id,SUM(num) FROM (SELECT * FROM score WHERE course_id IN(SELECT cid FROM course WHERE teacher_id=(SELECT tid FROM teacher WHERE tname='李平老师')))AS aa GROUP BY student_id DESC LIMIT 1)AS bb ON student.sid=bb.student_id; +--------+ | sname | +--------+ | 如花 | +--------+ 1 row in set (0.00 sec)
20、查询每门课程成绩最好的前两名学生姓名(答案非准确)
mysql> SELECT student.`sname`,ee.course_id,ee.num FROM student INNER JOIN -> (SELECT * FROM (SELECT student_id,course_id,num FROM score WHERE course_id=1 ORDER BY num DESC LIMIT 2)AS aa -> UNION ALL SELECT * FROM (SELECT student_id,course_id,num FROM score WHERE course_id=2 ORDER BY num DESC LIMIT 2)AS bb -> UNION ALL SELECT * FROM (SELECT student_id,course_id,num FROM score WHERE course_id=3 ORDER BY num DESC LIMIT 2)AS cc -> UNION ALL SELECT * FROM (SELECT student_id,course_id,num FROM score WHERE course_id=4 ORDER BY num DESC LIMIT 2)AS dd)AS ee -> ON student.sid=ee.student_id; +--------+-----------+-----+ | sname | course_id | num | +--------+-----------+-----+ | 李一 | 1 | 91 | | 如花 | 1 | 90 | | 张四 | 2 | 100 | | 铁锤 | 2 | 100 | | 张三 | 3 | 87 | | 刘三 | 3 | 87 | | 张一 | 4 | 100 | | 张二 | 4 | 100 | +--------+-----------+-----+ 8 rows in set (0.01 sec)
