题目

1、 查询“生物”课程比“物理”课程成绩高的所有学生的学号；
2、 查询平均成绩大于60分的同学的学号和平均成绩；
3、 查询所有同学的学号、姓名、选课数、总成绩；
4、 查询姓“李”的老师的个数；
5、 查询没学过“叶平”老师课的同学的学号、姓名
6、 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
7、 查询学过“叶平”老师所教的所有课的同学的学号、姓名
8、 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
9、 查询有课程成绩小于60分的同学的学号、姓名
10、 查询没有学全所有课的同学的学号、姓名
11、 查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
12、 查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名；
13、 查询各个课程及相应的选修人数

数据库表结构

数据库文件

/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 8.0.15 : Database - study
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
USE `study`;

/*Table structure for table `class` */

DROP TABLE IF EXISTS `class`;

CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `class` */

insert  into `class`(`cid`,`caption`) values (1,'三年二班'),(2,'一年三班'),(3,'三年一班');

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(255) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `course` */

insert  into `course`(`cid`,`cname`,`teacher_id`) values (1,'生物',1),(2,'体育',1),(3,'物理',2);

/*Table structure for table `score` */

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE,
  KEY `student_score_FK` (`course_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

/*Data for the table `score` */

insert  into `score`(`sid`,`student_id`,`course_id`,`number`) values (1,1,1,60),(2,1,2,59),(3,2,2,100),(4,3,3,50);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

/*Data for the table `student` */

insert  into `student`(`sid`,`sname`,`gender`,`class_id`) values (1,'钢蛋','女',1),(2,'铁锤','女',1),(3,'山炮','男',2),(4,'张三','男',2);

/*Table structure for table `teacher` */

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `teacher` */

insert  into `teacher`(`tid`,`tname`) values (1,'波多'),(2,'苍空'),(3,'饭岛'),(4,'叶平');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

练习

-- 1、	查询“生物”课程比“物理”课程成绩高的所有学生的学号；

-- 第一步 拿到所有有生物成绩的学员信息
SELECT 
	* 
FROM 
	score a 
LEFT JOIN 
	course b ON a.course_id = b.cid 
WHERE b.cname='生物'   

-- 第二步拿到所有有物理成绩的学员信息
SELECT * FROM score a LEFT JOIN course b ON a.course_id = b.cid WHERE b.cname='物理'   

-- 第三步使用子查询拿到生物成绩比物理成绩高的学生信息
SELECT 
	c.student_id,c.number,d.number 
FROM 
	(SELECT a.student_id,a.number,b.cname FROM score a LEFT JOIN course b ON a.course_id = b.cid WHERE b.cname='生物')AS c  
LEFT JOIN
	(SELECT * FROM score a LEFT JOIN course b ON a.course_id = b.cid WHERE b.cname='物理' )  d 
ON 
	c.student_id = d.student_id  
WHERE 
	c.number > IFNULL(d.number,0)
	

-- 2、	查询平均成绩大于60分的同学的学号和平均成绩；
SELECT
	student_id,AVG(number)
FROM
	score
GROUP BY 
	student_id
HAVING 
	AVG(number)>60


-- 3、	查询所有同学的学号、姓名、选课数、总成绩；
SELECT SUM(sc.number),sc.student_id
FROM score sc,student s
WHERE s.sid=sc.student_id
GROUP BY sc.student_id


-- 4、	查询姓“李”的老师的个数；
SELECT COUNT(*)
FROM teacher
WHERE tname LIKE '李%'


-- 5、	查询没学过“叶平”老师课的同学的学号、姓名

-- 步骤一：拿到叫叶平的老师的tid
SELECT tid FROM teacher WHERE tname LIKE '叶平'

-- 步骤二：连接course和student
SELECT co.cid 课程编号,s.sname 姓名,s.sid 学号,co.teacher_id 老师编号
FROM course co,student s
WHERE s.class_id = co.cid

-- 步骤三：（ifnull(expr1,expr2)如果expr1为null则返回expr2，若不是null则直接返回expr1）
SELECT co.cid 课程编号,s.sname 姓名,s.sid 学号,co.teacher_id 老师编号
FROM course co,student s
WHERE s.class_id = co.cid
AND co.teacher_id!=IFNULL(SELECT tid FROM teacher WHERE tname LIKE '叶平',0)


-- 6、	查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

-- 步骤一：分别拿到学过课程号1和2的学生id
SELECT student_id FROM score WHERE course_id=1
SELECT student_id FROM score WHERE course_id=2

-- 步骤二：将步骤一两表连接拿到既学过1号又学过2号课程的学生id
SELECT t1.student_id sid
FROM 
	(SELECT student_id FROM score WHERE course_id=1) t1,
	(SELECT student_id FROM score WHERE course_id=2) t2
WHERE t1.student_id=t2.student_id

-- 步骤三:连接步骤二的表和student表
SELECT s.sid 学号,s.sname 姓名
FROM 
(SELECT t1.student_id sid
FROM 
	(SELECT student_id FROM score WHERE course_id=1) t1,
	(SELECT student_id FROM score WHERE course_id=2) t2
WHERE t1.student_id=t2.student_id) t3,
student s
WHERE t3.sid =s.sid