mysql 多表查询练习题（一）数据准备

创建四张表：

学生表：student(编号sid，姓名sname，年龄age，性别sex)

课程表：course(课程编号cid，课程名称cname，教师编号tid)

教师表：teacher(教师编号tid，姓名tname)

成绩表：sc(学生编号sid，课程编号cid，成绩score)

 

1、插入学生数据

学号	姓名	年龄	性别
1001	张宇	12	男
1002	张梅	18	女
1003	王鑫	16	男
1004	马东东	13	男
1005	孙子涵	12	女
1006	钱一	12	男
1007	赵贺	15	男
1008	周雪	16	女

 

2、插入老师数据

教师编号	教师姓名
1	李雷
2	李纯
3	胡悦悦
4	朱清时
5	赛鸿飞
6	宋三东

 

3、插入课程数据

课程编号	课程名	教师编号
001	PHP	1
002	C	1
003	C++	2
004	JAVA	3
005	Python	4
006	R	5
007	HTML	6

 

4、插入成绩数据

学号	课程编号	成绩
1001	001	89
1002	001	80
1003	001	30
1004	001	78
1005	001	68
1006	001	93
1007	001	62
1001	002	67
1002	002	86
1003	002	67
1004	002	77
1005	002	66
1006	002	84
1007	002	72
1001	003	82
1002	003	85
1003	003	32
1004	003	73
1005	003	64
1006	003	87
1007	003	77
1008	003	94
1001	004	39
1002	004	80
1003	004	80
1004	004	88
1005	004	68
1006	004	59
1007	004	42
1008	004	64
1001	005	89
1002	005	70
1003	005	60
1004	005	58
1005	005	38
1006	005	89
1007	005	72
1008	005	64
1001	006	49
1002	006	90
1003	006	70
1004	006	48
1005	006	98
1006	006	59
1007	006	72
1008	006	74
1001	007	49
1002	007	50
1003	007	70
1004	007	88
1005	007	78
1006	007	99
1007	007	82

 建表语句和数据导入

 

/*
Navicat MySQL Data Transfer

Source Server         : ibeifeng
Source Server Version : 50717
Source Host           : localhost:3306
Source Database       : anli1

Target Server Type    : MYSQL
Target Server Version : 50717
File Encoding         : 65001

Date: 2019-01-06 10:19:07
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` varchar(50) NOT NULL,
  `cname` varchar(50) NOT NULL,
  `tid` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('001', 'PHP', '1');
INSERT INTO `course` VALUES ('002', 'C', '1');
INSERT INTO `course` VALUES ('003', 'C++', '2');
INSERT INTO `course` VALUES ('004', 'JAVA', '3');
INSERT INTO `course` VALUES ('005', 'python', '4');
INSERT INTO `course` VALUES ('006', 'R', '5');
INSERT INTO `course` VALUES ('007', 'HTML', '6');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `sid` int(10) NOT NULL,
  `cid` varchar(50) NOT NULL,
  `score` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('1001', '001', '89');
INSERT INTO `sc` VALUES ('1002', '001', '80');
INSERT INTO `sc` VALUES ('1003', '001', '30');
INSERT INTO `sc` VALUES ('1004', '001', '78');
INSERT INTO `sc` VALUES ('1005', '001', '68');
INSERT INTO `sc` VALUES ('1006', '001', '93');
INSERT INTO `sc` VALUES ('1007', '001', '62');
INSERT INTO `sc` VALUES ('1001', '002', '67');
INSERT INTO `sc` VALUES ('1002', '002', '86');
INSERT INTO `sc` VALUES ('1003', '002', '67');
INSERT INTO `sc` VALUES ('1004', '002', '77');
INSERT INTO `sc` VALUES ('1006', '002', '84');
INSERT INTO `sc` VALUES ('1007', '002', '72');
INSERT INTO `sc` VALUES ('1001', '003', '82');
INSERT INTO `sc` VALUES ('1002', '003', '85');
INSERT INTO `sc` VALUES ('1003', '003', '32');
INSERT INTO `sc` VALUES ('1004', '003', '73');
INSERT INTO `sc` VALUES ('1005', '003', '64');
INSERT INTO `sc` VALUES ('1006', '003', '87');
INSERT INTO `sc` VALUES ('1007', '003', '77');
INSERT INTO `sc` VALUES ('1001', '004', '39');
INSERT INTO `sc` VALUES ('1003', '004', '80');
INSERT INTO `sc` VALUES ('1004', '004', '88');
INSERT INTO `sc` VALUES ('1005', '004', '68');
INSERT INTO `sc` VALUES ('1006', '004', '59');
INSERT INTO `sc` VALUES ('1007', '004', '42');
INSERT INTO `sc` VALUES ('1008', '004', '64');
INSERT INTO `sc` VALUES ('1001', '005', '89');
INSERT INTO `sc` VALUES ('1002', '005', '70');
INSERT INTO `sc` VALUES ('1003', '005', '60');
INSERT INTO `sc` VALUES ('1004', '005', '58');
INSERT INTO `sc` VALUES ('1005', '005', '38');
INSERT INTO `sc` VALUES ('1006', '005', '89');
INSERT INTO `sc` VALUES ('1007', '005', '72');
INSERT INTO `sc` VALUES ('1008', '005', '64');
INSERT INTO `sc` VALUES ('1001', '006', '49');
INSERT INTO `sc` VALUES ('1002', '006', '90');
INSERT INTO `sc` VALUES ('1003', '006', '70');
INSERT INTO `sc` VALUES ('1004', '006', '48');
INSERT INTO `sc` VALUES ('1005', '006', '98');
INSERT INTO `sc` VALUES ('1006', '006', '59');
INSERT INTO `sc` VALUES ('1007', '006', '72');
INSERT INTO `sc` VALUES ('1008', '006', '74');
INSERT INTO `sc` VALUES ('1001', '007', '49');
INSERT INTO `sc` VALUES ('1002', '007', '50');
INSERT INTO `sc` VALUES ('1003', '007', '70');
INSERT INTO `sc` VALUES ('1004', '007', '88');
INSERT INTO `sc` VALUES ('1005', '007', '78');
INSERT INTO `sc` VALUES ('1006', '007', '99');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(10) NOT NULL,
  `sname` varchar(50) NOT NULL,
  `age` int(10) NOT NULL,
  `sex` varchar(10) NOT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1001', '张宇', '12', '男');
INSERT INTO `student` VALUES ('1002', '张梅', '18', '女');
INSERT INTO `student` VALUES ('1003', '王鑫', '16', '男');
INSERT INTO `student` VALUES ('1004', '马东东', '13', '女');
INSERT INTO `student` VALUES ('1005', '孙子涵', '12', '男');
INSERT INTO `student` VALUES ('1006', '钱一', '12', '男');
INSERT INTO `student` VALUES ('1007', '赵贺', '15', '男');
INSERT INTO `student` VALUES ('1008', '周雪', '16', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(10) NOT NULL,
  `tname` varchar(50) NOT NULL
) ENGINE=InnoDB 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', '赛鸿飞');
INSERT INTO `teacher` VALUES ('6', '宋三东');

 

问题：

1. 查询平均成绩大于70分的同学的学号和平均成绩
2. 查询所有同学的学号、姓名、选课数、总成绩
3. 查询姓“李”的老师的个数
4. 查询学过“李纯”老师课的同学的学号、姓名
5. 查询没学过“李雷”老师课的同学的学号、姓名
6. 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
7. 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
8. 查询学过“李雷”老师所教的所有课的同学的学号、姓名
9. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，课程名最高分，最低分
10. 查询没有学全所有课的同学的学号、姓名
11. 查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名
12. 按各科平均成绩从低到高和及格率的百分数从高到低顺序。补充：concat() 方法用于连接两个或多个数组，百分数表示方法 CONCAT(值1/值2*100,'%')
13. 删除学习“李纯”老师课的SC表记录

 

答案见下一篇，点击跳转