mysql测试题

本代码是通过老男孩的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