mysql 多表联查80分_（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);