Mysql多表联查

经典sql语句

1、数据库myschool

CREATE DATABASE myschool;

2、表名student 、course 、teacher 、sc

//学生表student
CREATE TABLE student(
Sid VARCHAR(10),     //学号
Sname NVARCHAR(20), //姓名
Sage DATETIME,      //年龄
Ssex NVARCHAR(10)   //性别
);

//课程表course
CREATE TABLE course(
Cid VARCHAR(10),    //课程号
Cname NVARCHAR(20), //课程名称
Tid VARCHAR(10)     //教师编号
);

//教师表teacher
CREATE TABLE teacher(
Tid VARCHAR(10),   //教师编号
Tname NVARCHAR(10) //教师姓名
);

//分数表score
CREATE TABLE sc(
Sid VARCHAR(10),    //学号
Cid VARCHAR(10),    //课程号
score DECIMAL(18,1) //分数
);

3、插入数据

INSERT INTO student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO student VALUES('03' , '孙风' , '1990-05-20','男');
INSERT INTO student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO student VALUES('08' , '王菊' , '1990-01-20' , '女');


INSERT INTO course VALUES('01' , '语文' , '02');
INSERT INTO course VALUES('02' , '数学' , '01');
INSERT INTO course VALUES('03' , '英语' , '03');


INSERT INTO teacher VALUES('01' , '张三');
INSERT INTO teacher VALUES('02' , '李四');
INSERT INTO teacher VALUES('03' , '王五');

INSERT INTO sc VALUES('01' , '01' , 80);
INSERT INTO sc VALUES('01' , '02' , 90);
INSERT INTO sc VALUES('01' , '03' , 99);
INSERT INTO sc VALUES('02' , '01' , 70);
INSERT INTO sc VALUES('02' , '02' , 60);
INSERT INTO sc VALUES('02' , '03' , 80);
INSERT INTO sc VALUES('03' , '01' , 80);
INSERT INTO sc VALUES('03' , '02' , 80);
INSERT INTO sc VALUES('03' , '03' , 80);
INSERT INTO sc VALUES('04' , '01' , 50);
INSERT INTO sc VALUES('04' , '02' , 30);
INSERT INTO sc VALUES('04' , '03' , 20);
INSERT INTO sc VALUES('05' , '01' , 76);
INSERT INTO sc VALUES('05' , '02' , 87);
INSERT INTO sc VALUES('06' , '01' , 31);
INSERT INTO sc VALUES('06' , '03' , 34);
INSERT INTO sc VALUES('07' , '02' , 89);
INSERT INTO sc VALUES('07' , '03' , 98);

4、多表联查
（1）、查询01课程比02课程成绩低的学生的信息及课程分数

SELECT a.*, b.`score` 课程01的分数, c.`score` 课程02的分数
FROM student a, sc b, sc c
WHERE a.`Sid`=b.`Sid`
AND a.`Sid`=c.`Sid`
AND b.`Cid`='01'
AND c.`Cid`='02'
AND b.`score`<c.`score`;

（2）、查询平均成绩大于等于60分的同学的学生编号和学生姓名

 SELECT a.`Sid`,  a.`Sname`, AVG(b.`score`) 平均成绩
 FROM student a,sc b
 WHERE a.`Sid`=b.`Sid`
 GROUP BY a.`Sid`
 HAVING 平均成绩>60
 ORDER BY a.`Sid`;

（3）、 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT a.`Sid`,a.`Sname`,COUNT(b.`Cid`) 选课总数, SUM(b.`score`) 所有课程的总成绩
 FROM student a,sc b
 WHERE a.`Sid`=b.`Sid`
 GROUP BY a.`Sid`
 ORDER BY a.`Sid`;

（4）、查询李姓老师数量

SELECT COUNT(t.`Tname`) 李姓老师数量
 FROM teacher t
 WHERE t.`Tname` LIKE '李%' ;

（5）、查询学过张三老师授课的同学的信息

 SELECT DISTINCT a.*
 FROM student a,course b,teacher c,sc d
 WHERE a.`Sid`=d.`Sid`
 AND d.`Cid`=b.`Cid`
 AND b.`Tid`=c.`Tid`
 AND c.`Tname`='张三'
 ORDER BY a.`Sid`;

（6）、查询没学过张三老师授课的同学的信息

 SELECT  student.*
 FROM student
 WHERE student.`Sid` NOT IN(
 SELECT DISTINCT a.`Sid`
 FROM student a,course b,teacher c,sc d
 WHERE a.`Sid`=d.`Sid`
 AND d.`Cid`=b.`Cid`
 AND b.`Tid`=c.`Tid`
 AND c.`Tname`='张三')
 ORDER BY student.`Sid`;

（7）、查询学过编号为01并且也学过编号为02的课程的同学的信息

SELECT DISTINCT a.*
FROM student a,sc b
WHERE a.`Sid`=b.`Sid`
AND b.`Cid`='01'
AND EXISTS(SELECT 1 FROM sc c WHERE c.`Sid`=b.`Sid` AND c.`Cid`='02')
ORDER BY a.`Sid`

（8）、查询学过编号为01但是没学过编号为02的课程的同学的信息

SELECT DISTINCT a.*
FROM student a,sc b
WHERE a.`Sid`=b.`Sid`
AND b.`Cid`='01'
AND NOT EXISTS(SELECT 1 FROM sc c WHERE c.`Sid`=b.`Sid` AND c.`Cid`='02')
ORDER BY a.`Sid`

（9）、查询没有学全所有课程的同学

SELECT a.*
FROM student a,sc b
WHERE a.`Sid`=b.`Sid`
GROUP BY a.`Sid`,a.`Sname`,a.`Sage`,a.`Ssex`
HAVING COUNT(Cid)<(SELECT COUNT(Cid) FROM course);

（10）、查询至少有一门课与学号为01的同学所学相同的同学的信息

SELECT DISTINCT a.*
FROM student a,sc b
WHERE a.`Sid`=b.`Sid`
AND Cid IN (SELECT Cid FROM sc WHERE Sid='01')
AND a.`Sid` <>'01'; 

（11）、查询和01同学学习的课程完全相同的其他同学的信息

SELECT student.*
FROM student 
WHERE Sid IN(
SELECT sc.`Sid` FROM sc
WHERE sc.`Cid` IN(SELECT DISTINCT Cid FROM sc WHERE Sid='01')
AND sc.`Sid` <> '01'
GROUP BY sc.`Sid`
HAVING COUNT(1)=(SELECT COUNT(1) FROM sc WHERE Sid='01')
); 

（12）、查询俩门及其以上不及格的同学的学号，姓名及其平均成绩

SELECT a.Sid,a.Sname,CAST(AVG(b.score) AS DECIMAL(18,2)) 平均成绩
FROM student a,sc b
WHERE a.Sid=b.Sid
AND a.Sid IN(
SELECT Sid FROM sc WHERE score<60
GROUP BY Sid
HAVING COUNT(1)>=2
)

（13）、检索01课程分数小于60，按分数降序排列的学生信息

SELECT a.*,b.`Cid`,b.`score`
FROM student a,sc b
WHERE a.Sid=b.Sid
AND b.score<60
AND b.Cid='01'
ORDER BY b.score DESC

（14）、查询不同老师所教不同课程平均分从高到低显示

SELECT teacher.`Tid`,teacher.`Tname`,CAST(AVG(sc.`score`) AS DECIMAL(18,2)) avg_score
FROM course,sc,teacher
WHERE course.`Tid`=teacher.`Tid`
AND course.`Cid`=sc.`Cid`
GROUP BY teacher.`Tid`,teacher.`Tname`
ORDER BY avg_score DESC

（15）、查询出只有两门课程的全部学生的学号和姓名

SELECT student.`Sid`,student.`Sname`
FROM student,sc
WHERE student.`Sid`=sc.`Sid`
GROUP BY student.`Sid`,student.`Sname`
HAVING COUNT(sc.`Cid`)=2

（16）、查询课程名称为”数学”，且分数低于60的学生姓名和分数

SELECT student.`Sname`,sc.`score`
FROM student,sc,course
WHERE student.`Sid`=sc.`Sid`
AND sc.`Cid`=course.`Cid`
AND course.`Cname`='数学'
AND sc.`score`<60

（17）、查询所有学生的课程及分数情况；

SELECT student.`Sid`,student.`Sname`,course.`Cname`,sc.`score`
FROM student, sc,course
WHERE student.`Sid`=sc.`Sid`
AND sc.`Cid`=course.`Cid`

（18）、检索至少选修两门课程的学生学号

SELECT sc.`Sid`
FROM sc
GROUP BY sc.`Sid`
HAVING COUNT(sc.`Cid`)>=2

（19）、查询选修了全部课程的学生信息

SELECT *
FROM student
WHERE Sid IN(
SELECT Sid FROM sc 
GROUP BY Sid
HAVING COUNT(1)=(SELECT COUNT(1) FROM course))