Mysql多表联查

来源:互联网 发布:greengene数据库 编辑:程序博客网 时间:2024/03/29 17:22

经典sql语句

1、数据库myschool

CREATE DATABASE myschool;

2、表名student 、course 、teacher 、sc

//学生表studentCREATE TABLE student(Sid VARCHAR(10),     //学号Sname NVARCHAR(20), //姓名Sage DATETIME,      //年龄Ssex NVARCHAR(10)   //性别);//课程表courseCREATE TABLE course(Cid VARCHAR(10),    //课程号Cname NVARCHAR(20), //课程名称Tid VARCHAR(10)     //教师编号);//教师表teacherCREATE TABLE teacher(Tid VARCHAR(10),   //教师编号Tname NVARCHAR(10) //教师姓名);//分数表scoreCREATE 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 cWHERE 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 bWHERE 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 bWHERE 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 bWHERE 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 bWHERE 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 scWHERE 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 bWHERE a.Sid=b.SidAND a.Sid IN(SELECT Sid FROM sc WHERE score<60GROUP BY SidHAVING COUNT(1)>=2)

(13)、检索01课程分数小于60,按分数降序排列的学生信息

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

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

SELECT teacher.`Tid`,teacher.`Tname`,CAST(AVG(sc.`score`) AS DECIMAL(18,2)) avg_scoreFROM course,sc,teacherWHERE 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,scWHERE 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,courseWHERE 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,courseWHERE student.`Sid`=sc.`Sid`AND sc.`Cid`=course.`Cid`

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

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

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

SELECT *FROM studentWHERE Sid IN(SELECT Sid FROM sc GROUP BY SidHAVING COUNT(1)=(SELECT COUNT(1) FROM course))
原创粉丝点击