个体工商户能做网站吗,门户网站建设工作室,张家港市网站建设,网站空间一般有多大联合查询 0.前言1.联合查询在MySQL里面的原理2.练习一个完整的联合查询2.1.构造练习案例数据2.2 案例#xff1a;⼀个完整的联合查询的过程2.2.1. 确定参与查询的表#xff0c;学⽣表和班级表2.2.2. 确定连接条件#xff0c;student表中的class_id与class表中id列的值相等2.… 联合查询 0.前言1.联合查询在MySQL里面的原理2.练习一个完整的联合查询2.1.构造练习案例数据2.2 案例⼀个完整的联合查询的过程2.2.1. 确定参与查询的表学⽣表和班级表2.2.2. 确定连接条件student表中的class_id与class表中id列的值相等2.2.3. 加⼊查询条件2.2.4. 精减查询结果字段2.2.5. 可以为表名指定别名 2.3联合查询总结 3.内连接3.1 语法3.2 示例3.2.1. 查询唐三藏同学的成绩3.2.2.查询所有同学的总成绩及同学的个⼈信息3.2.3.查询所有同学的总成绩及同学的个⼈信息 4. 外连接4.1.语法4.2.查询没有参加考试的同学信息4.3.查询没有学⽣的班级 5. ⾃连接5.1.应⽤场景5.2.显⽰所有MySQL成绩⽐JAVA成绩⾼的成绩信息5.3 表连接练习5.3.1.显⽰所有MySQL成绩⽐JAVA成绩⾼的学⽣信息和班级以及成绩信息 0.前言 前言为什么要联合查询什么是联合查询呢? 1在学过范式之后我们会对一张表的数据按照范式的标准拆分为多张表这样便于降低数据的冗杂但是我们如果查询表的数据我们又如何一次性查询所有的数据呢这时候就用到了联合查询 2联合查询就是多个表的组合查询一次查询涉及两张或则两张以上的表成为联合查询 1.联合查询在MySQL里面的原理
参与查询的所有表笛卡尔积结果集在临时表中实例:创建两张表一张student 表一张class表 插入数据 表如下 参与查询的所有表取笛卡尔积结果集在临时表中
• 观察哪些记录是有效数据根据两个表的关联关系过滤掉⽆效数据 如果联合查询表的个数越多表中的数据量越⼤临时表就会越⼤所以根据实际情况确定联合查询表的个数
2.练习一个完整的联合查询
2.1.构造练习案例数据
# 课程表
insert into course (name) values (Java), (C), (MySQL), (操作系统), (计
算机⽹络), (数据结构);
# 班级表
insert into class(name) values (Java001班), (C001班), (前端001班);
# 学⽣表
insert into student (name, sno, age, gender, enroll_date, class_id) values
(唐三藏, 100001, 18, 1, 1986-09-01, 1),
(孙悟空, 100002, 18, 1, 1986-09-01, 1),
(猪悟能, 100003, 18, 1, 1986-09-01, 1),
(沙悟净, 100004, 18, 1, 1986-09-01, 1),
(宋江, 200001, 18, 1, 2000-09-01, 2),
(武松, 200002, 18, 1, 2000-09-01, 2),
(李逹, 200003, 18, 1, 2000-09-01, 2),
(不想毕业, 200004, 18, 1, 2000-09-01, 2);
# 成绩表
insert into score (score, student_id, course_id) values
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
(60, 2, 1),(59.5, 2, 5),
(33, 3, 1),(68, 3, 3),(99, 3, 5),
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
(81, 5, 1),(37, 5, 5),
(56, 6, 2),(43, 6, 4),(79, 6, 6),
(80, 7, 2),(92, 7, 6);2.2 案例⼀个完整的联合查询的过程
• 查询学⽣姓名为宋江的详细信息包括学⽣个⼈信息和班级信息
2.2.1. 确定参与查询的表学⽣表和班级表
# 在from后同时写所有参与查询的表并⽤逗号隔开(取笛卡尔积)
select *from student,class;通过观察两张表联合后的结果集中有些是无效的数据如何过滤这些无用的数据呢
2.2.2. 确定连接条件student表中的class_id与class表中id列的值相等
连接条件student表中的class_id与class表中id列的值相等
# 在where⼦句中加⼊连接条件
mysql select * from student, class where student.class_id class.id;这里有个新的知识点class_id在student表但是两张表都有id列不指定表名时MySQL无法分清要使用那张表的id列。这时候用表名.列名就可以区分。
2.2.3. 加⼊查询条件 上面查询产生了一个错误因为我们无法知道这个name是指代的是那张表的name。正确的写法如下
2.2.4. 精减查询结果字段
selectstudent.id, student.name, student.sno, student.age, student.gender, student.enroll_date, class.name
from student, class
wherestudent.class_id class.id
andstudent.name 宋江;2.2.5. 可以为表名指定别名 2.3联合查询总结
确定几张表参与查询根据表与表之间的关系确定过滤条件通过WHERE条件对整个结果集进行过滤精减字段得到想要的结果通过为表起列名精减SQL语句
3.内连接
3.1 语法
1 select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;简写
2 select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 其他条件;规范写法3.2 示例
3.2.1. 查询唐三藏同学的成绩
确定几张表两张学生表和成绩表取两张表的笛卡尔积
select * from student,score;根据表与表之间的关系确定过滤条件
select * from student,score where student.idscore.student_id ;添加where条件 精减查询字段
select student.name,score.score from student,score where student.idscore.student_id and student.name唐三藏;通过起别名精减整个sql语句
select s.name,sc.score from student s,score sc where s.idsc.student_id and s.name唐三藏;用规范的写法写
select s.name, sc.score from student s join score sc on sc.student_id
s.id where s.name 唐三藏;总结一下
3.2.2.查询所有同学的总成绩及同学的个⼈信息
select s.name,sum(sc.score) from student s join score sc on s.idsc.student_id group by s.id;这里注意一下这个分组
分组之后列的值在组内是相同的即使该列不是分组列没有在group by指定依然可以写在查询列表中。
3.2.3.查询所有同学的总成绩及同学的个⼈信息
1.select * from student s join score sc on s.idsc.student_id join course c on c.idsc.course_id;
2.select * from student s,score sc,course c where c.idsc.course_id and s.idsc.student_id;切记关联查询表不要超过三张在以后真正工作的时候不要做大表的表关联查询。
4. 外连接
• 外连接分为左外连接、右外连接和全外连接三种类型MySQL不⽀持全外连接。 • 左外连接返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录则结果集中对应字段会显⽰为NULL。 • 右外连接与左外连接相反返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记录则结果集中对应字段会显⽰为NULL。 • 全外连接结合了左外连接和右外连接的特点返回左右表中的所有记录。如果某⼀边表中没有匹配的记录则结果集中对应字段会显⽰为NULL。
4.1.语法
-- 左外连接表1完全显⽰
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接表2完全显⽰
select 字段 from 表名1 right join 表名2 on 连接条件;4.2.查询没有参加考试的同学信息
# 左连接以JOIN左边的表为基准左表显⽰全部记录右表中没有匹配的记录⽤NULL填充
select * from student s left join score sc on s.idsc.student_id; 左表中显示所有记录右边中没有与左表匹配的记录用NULL填充。 添加where 条件
4.3.查询没有学⽣的班级
# 右连接以JOIN右边的表为基准右表显⽰全部记录左表中没有匹配的记录⽤NULL填充
select * from student s right join class c on c.ids.class_id; 添加where条件过滤
5. ⾃连接
5.1.应⽤场景
⾃连接是⾃⼰与⾃⼰取笛卡尔积可以把⾏转化成列在查询的时候可以使⽤where条件对结果进⾏过滤或者说实现⾏与⾏之间的⽐较。在做表连接时为表起不同的别名。
# 不为表指定别名
mysql select * from score, score;
ERROR 1066 (42000): Not unique table/alias: score
# 指定别名
mysql select * from score s1, score s2;5.2.显⽰所有MySQL成绩⽐JAVA成绩⾼的成绩信息
首先分为两步进行先要找到课程的idjava是1MySQL是3 确定连接条件 两个表中的student_id 必须相等 确定符合题目的要求 要么sc1表中的course_id 1 且 sc2表中的course_id 3 要么sc1表中的course_id 3 且 sc2表中的course_id 1
select * from score sc1,score sc2 where sc1.student_idsc2.student_id and sc1.course_id1 and sc2.course_id3 ;加入结果集的过滤条件分数比较
select * from score sc1,score sc2 where sc1.student_idsc2.student_id and sc1.course_id1 and sc2.course_id3 and sc1.scoresc2.score;精减查询结果用别名表示表头
5.3 表连接练习
5.3.1.显⽰所有MySQL成绩⽐JAVA成绩⾼的学⽣信息和班级以及成绩信息
