本代码是通过老男孩的mysql试题进行实际操作，只写了个人认为精华的部分，答案也是个人测试，如有问题或者好的答案可以回复，大家共同进步测试数据是他们完整的数据，链接如下：http://www.cnblogs.com/wupeiqi/articles/5748496.html测试代码如下：
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
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`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1',
'三年二班'), (
'2',
'三年三班'), (
'3',
'一年二班'), (
'4',
'二年九班');
COMMIT;
-- ----------------------------
-- 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`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1',
'生物',
'1'), (
'2',
'物理',
'2'), (
'3',
'体育',
'3'), (
'4',
'美术',
'2');
COMMIT;
-- ----------------------------
-- 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`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1',
'1',
'1',
'10'), (
'2',
'1',
'2',
'9'), (
'5',
'1',
'4',
'66'), (
'6',
'2',
'1',
'8'), (
'8',
'2',
'3',
'68'), (
'9',
'2',
'4',
'99'), (
'10',
'3',
'1',
'77'), (
'11',
'3',
'2',
'66'), (
'12',
'3',
'3',
'87'), (
'13',
'3',
'4',
'99'), (
'14',
'4',
'1',
'79'), (
'15',
'4',
'2',
'11'), (
'16',
'4',
'3',
'67'), (
'17',
'4',
'4',
'100'), (
'18',
'5',
'1',
'79'), (
'19',
'5',
'2',
'11'), (
'20',
'5',
'3',
'67'), (
'21',
'5',
'4',
'100'), (
'22',
'6',
'1',
'9'), (
'23',
'6',
'2',
'100'), (
'24',
'6',
'3',
'67'), (
'25',
'6',
'4',
'100'), (
'26',
'7',
'1',
'9'), (
'27',
'7',
'2',
'100'), (
'28',
'7',
'3',
'67'), (
'29',
'7',
'4',
'88'), (
'30',
'8',
'1',
'9'), (
'31',
'8',
'2',
'100'), (
'32',
'8',
'3',
'67'), (
'33',
'8',
'4',
'88'), (
'34',
'9',
'1',
'91'), (
'35',
'9',
'2',
'88'), (
'36',
'9',
'3',
'67'), (
'37',
'9',
'4',
'22'), (
'38',
'10',
'1',
'90'), (
'39',
'10',
'2',
'77'), (
'40',
'10',
'3',
'43'), (
'41',
'10',
'4',
'87'), (
'42',
'11',
'1',
'90'), (
'43',
'11',
'2',
'77'), (
'44',
'11',
'3',
'43'), (
'45',
'11',
'4',
'87'), (
'46',
'12',
'1',
'90'), (
'47',
'12',
'2',
'77'), (
'48',
'12',
'3',
'43'), (
'49',
'12',
'4',
'87'), (
'52',
'13',
'3',
'87');
COMMIT;
-- ----------------------------
-- 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`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1',
'男',
'1',
'理解'), (
'2',
'女',
'1',
'钢蛋'), (
'3',
'男',
'1',
'张三'), (
'4',
'男',
'1',
'张一'), (
'5',
'女',
'1',
'张二'), (
'6',
'男',
'1',
'张四'), (
'7',
'女',
'2',
'铁锤'), (
'8',
'男',
'2',
'李三'), (
'9',
'男',
'2',
'李一'), (
'10',
'女',
'2',
'李二'), (
'11',
'男',
'2',
'李四'), (
'12',
'女',
'3',
'如花'), (
'13',
'男',
'3',
'刘三'), (
'14',
'男',
'3',
'刘一'), (
'15',
'女',
'3',
'刘二'), (
'16',
'男',
'3',
'刘四');
COMMIT;
-- ----------------------------
-- 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`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1',
'张磊老师'), (
'2',
'李平老师'), (
'3',
'刘海燕老师'), (
'4',
'朱云海老师'), (
'5',
'李杰老师');
COMMIT;
SET FOREIGN_KEY_CHECKS =
1;
View Code
测试题如下：
1、查询“生物”课程比“物理”课程成绩高的所有学生的姓名
SELECT
student.sname
FROM
(
SELECT
W.student_id
FROM
( SELECT * FROM course LEFT JOIN score ON course.cid = score.course_id WHERE course.cid =
1 ) AS S
INNER JOIN ( SELECT * FROM course LEFT JOIN score ON course.cid = score.course_id WHERE course.cid =
2 ) AS W ON S.student_id =
W.student_id
WHERE
S.num >
W.num
) AS E
LEFT JOIN student ON E.student_id =
student.sid;
2、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT student_id,AVG(num) FROM `score` GROUP BY student_id HAVING AVG(num)>
60;
3、查询所有同学的学号、姓名、选课数、总成绩
SELECT
student.sname,
sid,
C.N,
C.CI FROM
(SELECT
student_id,
sum( num ) as N,
COUNT( course_id ) AS CI
FROM
score
GROUP BY
student_id )AS C
LEFT JOIN student ON C.student_id =
student.sid;
4、查询姓“李”的老师的个数；
SELECT COUNT(tid) from teacher WHERE tname like '李%';
5、查询没学过“李平老师“课的同学的学号、姓名；SELECT
student.sid,
student.sname
FROM
(
SELECT
student_id
FROM
score
WHERE
score.student_id NOT IN ( SELECT tid FROM teacher WHERE tname =
'李平老师' )
GROUP BY
student_id) AS T
LEFT JOIN student ON T.student_id =
student.sid;
6、查询学过“
1”并且也学过编号“
2”课程的同学的学号、姓名
SELECT
student_id,
sname
FROM
(
SELECT
student_id,
COUNT( course_id )
FROM
`score`
WHERE
course_id IN ( 1,
2 )
GROUP BY
student_id
HAVING
COUNT( course_id ) >
1
) AS B
LEFT JOIN student ON B.student_id=
student.sid;
7、查询有课程成绩小于60分的同学的学号、姓名
SELECT
student_id,
sname
FROM
( SELECT student_id FROM `score` WHERE num <
60 GROUP BY student_id ) AS B
LEFT JOIN student ON B.student_id =
student.sid;
8、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
SELECT
course_id,
MAX( num ) AS sMAX,
min( num ) AS sMIN,
student_id AS sstudent
FROM
`score`
GROUP BY
course_id
View Code
转载于:https://www.cnblogs.com/tengfei520/p/10205504.html
相关资源：mysql 数据库练习50题
