（2）SQL语句总结（mysql多表查询练习）

准备数据：

建议一个一个执行sql语句

然后在架构设计器捋清楚每个表之间的关系

（我已经截图贴在了下面）

 

CREATE TABLE `class` (
  `cid` INT(11) NOT NULL AUTO_INCREMENT,
  `cname` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ;

INSERT INTO class VALUES (NULL,'01班');
INSERT INTO class VALUES (NULL,'02班');
INSERT INTO class VALUES (NULL,'03班');


CREATE TABLE `course` (
  `cid` INT(11) NOT NULL AUTO_INCREMENT,
  `cnmae` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`cid`)
);

INSERT INTO students VALUES (NULL,'张三','男','1900-08-02',1);
INSERT INTO students VALUES (NULL,'李四','女','1992-08-02',1);
INSERT INTO students VALUES (NULL,'王五','男','1900-08-02',1);
INSERT INTO students VALUES (NULL,'赵六','男','1990-08-02',2);
INSERT INTO students VALUES (NULL,'田七','女','1900-08-02',2);
INSERT INTO students VALUES (NULL,'张五','男','1998-08-02',2);
INSERT INTO students VALUES (NULL,'张老七','女','1900-08-02',3);
INSERT INTO students VALUES (NULL,'王老四','男','1900-08-02',3);
INSERT INTO students VALUES (NULL,'李老八','男','1900-08-02',3);
INSERT INTO students VALUES (NULL,'李六','男','1900-08-02',3);

CREATE TABLE `stu_course` (
  `sid` INT(11) NOT NULL AUTO_INCREMENT,
  `sno` INT(11) DEFAULT NULL,
  `cno` INT(11) DEFAULT NULL,
  `score` INT(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `sno` (`sno`),
  KEY `cno` (`cno`),
  CONSTRAINT `stu_course_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cid`),
  CONSTRAINT `stu_course_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `students` (`sid`)
) ;

INSERT INTO course VALUES (NULL,'java');
INSERT INTO course VALUES (NULL,'php');
INSERT INTO course VALUES (NULL,'C++');

CREATE TABLE `students` (
  `sid` INT(11) NOT NULL AUTO_INCREMENT,
  `sname` VARCHAR(20) DEFAULT NULL,
  `sex` VARCHAR(20) DEFAULT NULL,
  `birthday` DATE DEFAULT NULL,
  `cno` INT(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `cno` (`cno`),
  CONSTRAINT `students_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `class` (`cid`)
) ;


INSERT INTO stu_course VALUES (NULL,1,1,66);
INSERT INTO stu_course VALUES (NULL,1,3,77);
INSERT INTO stu_course VALUES (NULL,2,1,33);
INSERT INTO stu_course VALUES (NULL,2,2,55);
INSERT INTO stu_course VALUES (NULL,3,3,99);
INSERT INTO stu_course VALUES (NULL,3,1,87);
INSERT INTO stu_course VALUES (NULL,3,3,59);
INSERT INTO stu_course VALUES (NULL,4,1,88);
INSERT INTO stu_course VALUES (NULL,4,3,77);
INSERT INTO stu_course VALUES (NULL,5,1,78);
INSERT INTO stu_course VALUES (NULL,5,2,100);
INSERT INTO stu_course VALUES (NULL,5,3,66);
INSERT INTO stu_course VALUES (NULL,6,1,55);
INSERT INTO stu_course VALUES (NULL,6,3,99);

 

 

测试题：

-- 1查询班级名称，和班级所在的总人数

-- 2查询学生的姓名和学生所选的总课程平均成绩。
    
-- 3查询学生的姓名和学生的选课总数，显示选课超过2门学生姓名。

-- 4查询平均成绩大于80分的学生的总数。

-- 5查询学生和平均成绩，但是平均成绩大于01班的任何一个学生的评价成绩。

-- 查询班级名称，和班级所在的总人数
-- 方法一
SELECT c.cname,COUNT(*) FROM class c,students s WHERE c.cid = s.cno GROUP BY c.cname;
/*方法二
分析：班级名称class表
	班级所在的总人数students表count(id)
	1,先查询出学生表按照班级分组后的各班总人数和班级编号
	2，把1中的表作为虚拟表（t2）和班级表（t1）联合查询
	3，关联条件是 t1.cid=t2.cno
*/

SELECT
t1.`cname`,-- 班级名称
t2.countid -- 班级人数
FROM
class t1,
(SELECT
cno,
COUNT(sid) countid
FROM
	students
GROUP BY
	cno) t2
WHERE
t1.`cid`=t2.cno;




-- 查询学生的姓名和学生所选的总课程平均成绩。
-- 方法一
SELECT s.`sname`,AVG(sc.`score`)FROM students s,stu_course sc WHERE sc.`sno`=s.sid GROUP BY s.`sname`;
/*方法二
分析：学生的姓名students表
	学生所选的总课程平均成绩stu_course表
	1把stu_course表根据学生编号sno进行分组，并求出分组后的平均成绩
	2把1中的表作为虚拟表和student联合查询
	
*/
SELECT
t1.`sname`,
t2.avgscore
FROM
	students AS t1,
	(SELECT 
	sno,
	AVG(score)AS avgscore
	FROM
	stu_course
	GROUP BY
	sno)AS t2
WHERE
	t1.sid=t2.sno;

	
-- 查询学生的姓名和学生的选课总数，显示选课超过2门学生姓名。
-- 方法一
SELECT s.`sname`,COUNT(sc.`cno`) FROM students s,stu_course sc WHERE sc.`sno`=s.`sid` GROUP BY s.`sname` HAVING COUNT(sc.`cno`)>2;
/*方法二
分析：学生的姓名students表
	选课总数stu_course表
	1先求出选课总数表,做为虚拟表和学生表联合查询
*/
SELECT
t1.`sname`,
t2.countClass
FROM
students t1,
(SELECT 
sno,
COUNT(sid) countClass
FROM
stu_course
GROUP BY
sno HAVING countClass>2)t2
WHERE
t1.`sid`=t2.sno;


-- 查询平均成绩大于80分的学生的总数。
-- 方法一
SELECT COUNT(sid) FROM students s WHERE s.sid IN(SELECT sc.`sno` FROM stu_course sc GROUP BY sc.`sno` HAVING AVG(score)>80);
-- 方法二（借用上一题的答案直接加一个HAVING avgscore>80条件）
SELECT
COUNT(sname) 
FROM
	students AS t1,
	(SELECT 
	sno,
	AVG(score)AS avgscore
	FROM
	stu_course
	GROUP BY
	sno HAVING avgscore>80)AS t2
WHERE
	t1.sid=t2.sno;

-- 查询学生和平均成绩，但是平均成绩大于01班的任何一个学生的平均成绩。
-- 这个有点难
/*分析：1先求出01班平均成绩的最高分
	2求出所有学生的平均成绩只显示大于01班最大平均成绩的学生
*/
SELECT 
s.*,
AVG(sc.`score`)
FROM
students s,
stu_course sc
WHERE
s.`sid`=sc.`sno`
GROUP BY
s.`sname`
HAVING
AVG(sc.`score`) >ANY(SELECT AVG(sc.score) 
FROM
 students s,
 stu_course sc,
 class c 
 WHERE
  s.sid = sc.sno 
  AND s.cno = c.cid
   AND c.cname ='01班' 
   GROUP BY s.sname);