MySQL练习题

 1 SELECT
 2   score.sid,
 3   score.student_id,
 4   student.sname,
 5   score.course_id,
 6   course.cname,
 7   score.number
 8 FROM score
 9   LEFT JOIN student
10     ON student.sid = score.student_id
11   LEFT JOIN course
12     ON course.cid = score.course_id

1 SELECT student_id,SUM(number)/COUNT(student_id) AS avg_number FROM score GROUP BY student_id HAVING avg_number>60;

1 SELECT student.sid,student.sname,COUNT(score.student_id),SUM(number) FROM student,score WHERE student.sid=score.student_id GROUP BY score.student_id

1 SELECT COUNT(*) FROM teacher WHERE tname LIKE "李%";

1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id AND score.course_id = (SELECT tid FROM teacher WHERE tname=‘李平老师‘);

1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id AND score.course_id IN (1,2) GROUP BY student.sname HAVING COUNT(*)=2;

 1 SELECT
 2   sid,
 3   sname
 4 FROM student
 5 WHERE sid NOT IN(SELECT
 6                    student.sid
 7                  FROM score,
 8                    student
 9                  WHERE student.sid = score.student_id
10                      AND score.course_id IN(SELECT
11                                               tid
12                                             FROM teacher
13                                             WHERE tname = ‘李平老师‘));

1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id AND number<60 GROUP BY student.sname;

1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id GROUP BY student.sid HAVING COUNT(*)<(SELECT COUNT(*) FROM course);

  1 /*
  2  Navicat Premium Data Transfer
  3 
  4  Source Server         : localhost
  5  Source Server Type    : MySQL
  6  Source Server Version : 50624
  7  Source Host           : localhost
  8  Source Database       : sqlexam
  9 
 10  Target Server Type    : MySQL
 11  Target Server Version : 50624
 12  File Encoding         : utf-8
 13 
 14  Date: 10/21/2016 06:46:46 AM
 15 */
 16 
 17 SET NAMES utf8;
 18 SET FOREIGN_KEY_CHECKS = 0;
 19 
 20 -- ----------------------------
 21 --  Table structure for `class`
 22 -- ----------------------------
 23 DROP TABLE IF EXISTS `class`;
 24 CREATE TABLE `class` (
 25   `cid` int(11) NOT NULL AUTO_INCREMENT,
 26   `caption` varchar(32) NOT NULL,
 27   PRIMARY KEY (`cid`)
 28 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
 29 
 30 -- ----------------------------
 31 --  Records of `class`
 32 -- ----------------------------
 33 BEGIN;
 34 INSERT INTO `class` VALUES (‘1‘, ‘三年二班‘), (‘2‘, ‘三年三班‘), (‘3‘, ‘一年二班‘), (‘4‘, ‘二年九班‘);
 35 COMMIT;
 36 
 37 -- ----------------------------
 38 --  Table structure for `course`
 39 -- ----------------------------
 40 DROP TABLE IF EXISTS `course`;
 41 CREATE TABLE `course` (
 42   `cid` int(11) NOT NULL AUTO_INCREMENT,
 43   `cname` varchar(32) NOT NULL,
 44   `teacher_id` int(11) NOT NULL,
 45   PRIMARY KEY (`cid`),
 46   KEY `fk_course_teacher` (`teacher_id`),
 47   CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
 48 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
 49 
 50 -- ----------------------------
 51 --  Records of `course`
 52 -- ----------------------------
 53 BEGIN;
 54 INSERT INTO `course` VALUES (‘1‘, ‘生物‘, ‘1‘), (‘2‘, ‘物理‘, ‘2‘), (‘3‘, ‘体育‘, ‘3‘), (‘4‘, ‘美术‘, ‘2‘);
 55 COMMIT;
 56 
 57 -- ----------------------------
 58 --  Table structure for `score`
 59 -- ----------------------------
 60 DROP TABLE IF EXISTS `score`;
 61 CREATE TABLE `score` (
 62   `sid` int(11) NOT NULL AUTO_INCREMENT,
 63   `student_id` int(11) NOT NULL,
 64   `course_id` int(11) NOT NULL,
 65   `num` int(11) NOT NULL,
 66   PRIMARY KEY (`sid`),
 67   KEY `fk_score_student` (`student_id`),
 68   KEY `fk_score_course` (`course_id`),
 69   CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
 70   CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
 71 ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
 72 
 73 -- ----------------------------
 74 --  Records of `score`
 75 -- ----------------------------
 76 BEGIN;
 77 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‘);
 78 COMMIT;
 79 
 80 -- ----------------------------
 81 --  Table structure for `student`
 82 -- ----------------------------
 83 DROP TABLE IF EXISTS `student`;
 84 CREATE TABLE `student` (
 85   `sid` int(11) NOT NULL AUTO_INCREMENT,
 86   `gender` char(1) NOT NULL,
 87   `class_id` int(11) NOT NULL,
 88   `sname` varchar(32) NOT NULL,
 89   PRIMARY KEY (`sid`),
 90   KEY `fk_class` (`class_id`),
 91   CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
 92 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
 93 
 94 -- ----------------------------
 95 --  Records of `student`
 96 -- ----------------------------
 97 BEGIN;
 98 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‘, ‘刘四‘);
 99 COMMIT;
100 
101 -- ----------------------------
102 --  Table structure for `teacher`
103 -- ----------------------------
104 DROP TABLE IF EXISTS `teacher`;
105 CREATE TABLE `teacher` (
106   `tid` int(11) NOT NULL AUTO_INCREMENT,
107   `tname` varchar(32) NOT NULL,
108   PRIMARY KEY (`tid`)
109 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
110 
111 -- ----------------------------
112 --  Records of `teacher`
113 -- ----------------------------
114 BEGIN;
115 INSERT INTO `teacher` VALUES (‘1‘, ‘张磊老师‘), (‘2‘, ‘李平老师‘), (‘3‘, ‘刘海燕老师‘), (‘4‘, ‘朱云海老师‘), (‘5‘, ‘李杰老师‘);
116 COMMIT;
117 
118 SET FOREIGN_KEY_CHECKS = 1;