Mysql联表查询（学生表、教师表、成绩表、课程表）

2024-07-01 14:08| 来源: 网络整理| 查看: 265

文章目录 1 建库语句2 建表语句3 插入测试数据4 常见查询问题

1 建库语句 create database if not exists studentinfo character set utf8mb4; 2 建表语句

在这里插入图片描述

一个学生可以选多门课程，一个课程下也对应多个学生，所以需要一个关系表（student_course）。为了方便，除了将学生id和课程id放入student_course中，还将成绩一起放到关系表中。另外一门课程对应一个老师。

(1) 学生表（student）

create table student( s_id int, sname varchar(20), sage int, sgender varchar(8) )

(2) 课程表（course）

create table course( c_id int, cname varchar(20), t_id int )

(3) 关系表（student_course）—— 学生和课程之间的关系表

create table student_course( s_id int, # 学生id c_id int, # 课程id score int # 成绩 )

(4) 教师表（teacher）

create table teacher( t_id int, #老师id tname varchar(20) # 老师姓名 ) 3 插入测试数据

（1）插入学生数据

insert into student select 1,N'刘一',18,N'男' union all select 2,N'钱二',19,N'女' union all select 3,N'张三',17,N'男' union all select 4,N'李四',18,N'女' union all select 5,N'王五',17,N'男' union all select 6,N'赵六',19,N'女' ;

注意：

N表示后续的字符串是Unicode类型的字符。Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；Union All：对两个结果集进行并集操作，包括重复行，不进行排序；注意使用insert插入时可以使用select（需要保证查询出的字段和插入字段一致就可以了） insert into student values(1,’张三’,1203); insert into test_user(name, email) select name, qq_mail from student;

（2）插入教师数据

insert into teacher select 1,N'叶平' union all select 2,N'贺高' union all select 3,N'杨艳' union all select 4,N'周磊';

（3）插入课程数据

insert into course select 1,N'语文',1 union all select 2,N'数学',2 union all select 3,N'英语',3 union all select 4,N'物理',4;

（4）插入关系表数据

insert into student_course select 1,1,56 union all select 1,2,78 union all select 1,3,67 union all select 1,4,58 union all select 2,1,79 union all select 2,2,81 union all select 2,3,92 union all select 2,4,68 union all select 3,1,91 union all select 3,2,47 union all select 3,3,88 union all select 3,4,56 union all select 4,2,88 union all select 4,3,90 union all select 4,4,93 union all select 5,1,46 union all select 5,3,78 union all select 5,4,53 union all select 6,1,35 union all select 6,2,68 union all select 6,4,71; 4 常见查询问题

(1) 查询id为“001”的课程比“002”成绩高的所有学生的学号；

select a.s_id FROM (select s_id,score from student_course where c_id = '001') a,(select s_id,score from student_course where c_id = '002') b where a.score > b.score and a.s_id = b.s_id; | s_id | +------+ | 3 |

(2) 查询平均成绩大于60分的学号和平均成绩；

select s_id,avg(score) from student_course group by s_id having avg(score)>60; | s_id | avg(score) | +------+------------+ | 1 | 64.7500 | | 2 | 80.0000 | | 3 | 70.5000 | | 4 | 90.3333 |

(3) 查询所有学生的学号、姓名、选课数、总成绩；

select s.s_id,s.sname, count(sc.c_id),sum(sc.score) from student s left join student_course sc on s.s_id = sc.s_id group by s.s_id, s.sname; | s_id | sname | count(student_course.c_id) | sum(score) | +------+--------+----------------------------+------------+ | 1 | 刘一 | 4 | 259 | | 2 | 钱二 | 4 | 320 | | 3 | 张三 | 4 | 282 | | 4 | 李四 | 3 | 271 | | 5 | 王五 | 3 | 177 | | 6 | 赵六 | 3 | 174 |

(4) 查询姓“叶”的老师的个数；

select count(*) from teacher where tname like '叶%'; | count(*) | +----------+ | 1 |

(5) 查询没学过“叶平”老师课的同学的学号、姓名；

select s.s_id, s.sname from student s where s.s_id not in (select sc.s_id from student_course sc, course c, teacher t where sc.c_id = c.c_id and c.t_id=t.t_id and t.tname='叶平'); | s_id | sname | +------+--------+ | 4 | 李四 |

(6) 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

select s.s_id, s.sname from student s,student_course sc where sc.s_id = s.s_id and sc.c_id = '001' and exists (select * from student s1, student_course sc1 where s1.s_id=sc1.s_id and sc1.c_id='002' ); | s_id | sname | +------+--------+ | 1 | 刘一 | | 2 | 钱二 | | 3 | 张三 | | 6 | 赵六

注： exists对应的子查询实际上并不返回任何数据，而是返回值True或False，EXISTS 指定一个子查询，主要用来检测行的存在。

(7) 查询学过“叶平”老师所教的所有课的同学的学号、姓名；

select s.s_id,s.sname from student s where s.s_id in (select sc.s_id from student_course sc,course c, teacher t where sc.c_id = c.c_id and c.t_id = t.t_id and t.tname ='叶平'); | s_id | sname | +------+--------+ | 1 | 刘一 | | 2 | 钱二 | | 3 | 张三 | | 5 | 王五 | | 6 | 赵六 |

(8) 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

select a.s_id,a.sname from (select sc.s_id,sc.score,s.sname from student_course sc,student s where sc.s_id=s.s_id and sc.c_id='001')a, (select sc.s_id,sc.score from student_course sc,student s where sc.s_id=s.s_id and sc.c_id='002') b where a.s_id=b.s_id and a.score>b.score; | s_id | sname | +------+--------+ | 3 | 张三 | +------+--------+

(9) 查询所有课程成绩小于60分的同学的学号、姓名；

select s.s_id,s.sname from student s where s.s_id in (select sc.s_id from student_course sc where sc.score

【本文地址】

公司简介

联系我们