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))
阅读全文
0 0
- mysql 多表联查
- mysql多表联查
- mysql 多表联查
- mysql多表联查
- mysql--多表联查
- MySql多表联查
- MySql多表联查
- Mysql多表联查
- Mysql
- MySql
- MYSQL
- MySQL
- mysql
- mysql
- mysql
- mysql
- Mysql
- Mysql
- 简单随机红包生成java实现
- FPGA作业3.2:通过例化设计3-8译码器
- border属性绘制三角形
- Java正则表达式基础
- LintCode_524_左填充
- Mysql多表联查
- 访问localhost报错,HTTP Error 503. The service is unavailable
- 周志华《Machine Learning》 学习笔记系列(1)
- Android studio出现adb不能安装应用
- 【Mybatis学习】Mybatis简单增删改查以及批量操作
- ROS下AR.Drone 2.0相机标定
- 被投票绑架的朋友圈
- Advanced Programming in UNIX Environment Episode 12
- 解决类似nvcc warning : The 'compute_20', 'sm_20', and 'sm_21' architectures are deprecated....等问题