网站开发能从事那些职业,代理服务器地址列表,2016响应式网站模板,网站上的图片带店面是怎么做的数据库的联合查询 简介为什么要使⽤联合查询多表联合查询时MYSQL内部是如何进⾏计算的构造练习案例数据案例#xff1a;⼀个完整的联合查询的过程 内连接语法⽰例 外连接语法 ⽰例⾃连接应⽤场景示例表连接练习 ⼦查询语法单⾏⼦查询多⾏⼦查询多列⼦查询在from⼦句中使⽤⼦查… 数据库的联合查询 简介为什么要使⽤联合查询多表联合查询时MYSQL内部是如何进⾏计算的构造练习案例数据案例⼀个完整的联合查询的过程 内连接语法⽰例 外连接语法 ⽰例⾃连接应⽤场景示例表连接练习 ⼦查询语法单⾏⼦查询多⾏⼦查询多列⼦查询在from⼦句中使⽤⼦查询 合并查询创建新表并初始化数据UnionUnion all 插⼊查询结果语法示例 简介
为什么要使⽤联合查询
在数据设计时由于范式的要求数据被拆分到多个表中那么要查询⼀个条数据的完整信息就 要从多个表中获取数据如下图所⽰要获取学⽣的基本信息和班级信息就要从学⽣表和班级表中获 取这时就需要使⽤联合查询这⾥的联合指的是多个表的组合。
多表联合查询时MYSQL内部是如何进⾏计算的
• 参与查询的所有表取笛卡尔积结果集在临时表中 • 观察哪些记录是有效数据根据两个表的关联关系过滤掉⽆效数据 如果联合查询表的个数越多表中的数据量越⼤临时表就会越⼤所以根据实际情况确定联合查询 表的个数
构造练习案例数据
-- ----------------------------
-- Table structure for class
-- ----------------------------CREATE TABLE class (class_id bigint NOT NULL AUTO_INCREMENT,name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,PRIMARY KEY (class_id) USING BTREE
) ENGINE InnoDB AUTO_INCREMENT 4 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic;-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO class VALUES (1, 计算机系2019级1班);
INSERT INTO class VALUES (2, 中文系2019级3班);
INSERT INTO class VALUES (3, 自动化2019级5班);
-- ----------------------------
-- Table structure for course
-- ----------------------------CREATE TABLE course (course_id bigint NOT NULL AUTO_INCREMENT,name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,PRIMARY KEY (course_id) USING BTREE
) ENGINE InnoDB AUTO_INCREMENT 7 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO course VALUES (1, Java);
INSERT INTO course VALUES (2, 中国传统文化);
INSERT INTO course VALUES (3, 计算机原理);
INSERT INTO course VALUES (4, 语文);
INSERT INTO course VALUES (5, 高阶数学);
INSERT INTO course VALUES (6, 英文);-- ----------------------------
-- Table structure for student
-- ----------------------------CREATE TABLE student (student_id bigint NOT NULL AUTO_INCREMENT,sn varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,mail varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,class_id bigint NULL DEFAULT NULL,PRIMARY KEY (student_id) USING BTREE,UNIQUE INDEX sn(sn ASC) USING BTREE,INDEX class_id(class_id ASC) USING BTREE,CONSTRAINT student_ibfk_1 FOREIGN KEY (class_id) REFERENCES class (class_id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE InnoDB AUTO_INCREMENT 9 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO student VALUES (1, 09982, 黑旋风李逵, xuanfengqq.com, 1);
INSERT INTO student VALUES (2, 00835, 菩提老祖, NULL, 1);
INSERT INTO student VALUES (3, 00391, 白素贞, NULL, 1);
INSERT INTO student VALUES (4, 00031, 许仙, xuxianqq.com, 1);
INSERT INTO student VALUES (5, 00054, 不想毕业, NULL, 1);
INSERT INTO student VALUES (6, 51234, 好好说话, sayqq.com, 2);
INSERT INTO student VALUES (7, 83223, tellme, NULL, 2);
INSERT INTO student VALUES (8, 09527, 老外学中文, foreignerqq.com, 2);-- ----------------------------
-- Table structure for score
-- ----------------------------CREATE TABLE score (score_id bigint NOT NULL AUTO_INCREMENT,student_id bigint NULL DEFAULT NULL,course_id bigint NULL DEFAULT NULL,score decimal(5, 2) NULL DEFAULT NULL,PRIMARY KEY (score_id) USING BTREE,INDEX student_id(student_id ASC) USING BTREE,INDEX course_id(course_id ASC) USING BTREE,CONSTRAINT score_ibfk_1 FOREIGN KEY (student_id) REFERENCES student (student_id) ON DELETE RESTRICT ON UPDATE RESTRICT,CONSTRAINT score_ibfk_2 FOREIGN KEY (course_id) REFERENCES course (course_id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE InnoDB AUTO_INCREMENT 21 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic;-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO score VALUES (1, 1, 1, 70.50);
INSERT INTO score VALUES (2, 1, 3, 98.50);
INSERT INTO score VALUES (3, 1, 5, 33.00);
INSERT INTO score VALUES (4, 1, 6, 98.00);
INSERT INTO score VALUES (5, 2, 1, 60.00);
INSERT INTO score VALUES (6, 2, 5, 59.50);
INSERT INTO score VALUES (7, 3, 1, 33.00);
INSERT INTO score VALUES (8, 3, 3, 68.00);
INSERT INTO score VALUES (9, 3, 5, 99.00);
INSERT INTO score VALUES (10, 4, 1, 67.00);
INSERT INTO score VALUES (11, 4, 3, 23.00);
INSERT INTO score VALUES (12, 4, 5, 56.00);
INSERT INTO score VALUES (13, 4, 6, 72.00);
INSERT INTO score VALUES (14, 5, 1, 81.00);
INSERT INTO score VALUES (15, 5, 5, 37.00);
INSERT INTO score VALUES (16, 6, 2, 56.00);
INSERT INTO score VALUES (17, 6, 4, 43.00);
INSERT INTO score VALUES (18, 6, 6, 79.00);
INSERT INTO score VALUES (19, 7, 2, 80.00);
INSERT INTO score VALUES (20, 7, 6, 92.00);案例⼀个完整的联合查询的过程
• 查询学⽣姓名为许仙的详细信息包括学⽣个⼈信息和班级信息
确定参与查询的表学⽣表和班级表 确定连接条件student表中的class_id与class表中id列的值相等 class id在两张表中都存在MYSQL分不清当前语句中的class id应该取自哪张表 可以通过表名.列名的方式来解决这个问题
# 在where子句中加入连接条件
select * from student, class where student.class_id class.class_id;加⼊查询条件
# 依题意添加where条件
select * from student,class where student.class_id class.class_id and student.name 许仙;精减查询结果字段
select student.student_id,student.name,student.mail,class.name
from student,class
where student.class_id class.class_id
and student.name 许仙; 可以为表名指定别名
select s.student_id,s.name,s.mail,c.name
from student s,class c
where s.class_id c.class_id
and s.name 许仙; 内连接
语法
select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 其他条件;⽰例
• 查询菩提老祖的成绩
# 查询菩提老祖的成绩
select st.name,sc.score from student st, score sc where st.student_id sc.student_id and st.name 菩提老祖;select st.name,sc.score from student st join score sc on st.student_id sc.student_id where st.name 菩提老祖;• 查询所有人的总成绩及每个人的个⼈信息
# 查询所有人的总成绩及每个人的个人信息
select st.student_id,st.name,st.mail,c.name,SUM(sc.score)
from student st,score sc,class c
where st.student_id sc.student_id
and c.class_id st.class_id
group by st.student_id;Group by 使⽤了student.student_id进⾏分组查询表列表中的student.name、student.mail和class.name没有出现在Group by分组中也没有包含在聚合函数中这是因为SQL规定在Group by分组查询时如果查询列表中的列没有出现在GROUP BY⼦句中但这些列的值在每个分组内部是相同的那么它们可以出现在查询结果中。 • 查询所有同学每⻔课的成绩及同学的个⼈信息
# 查询所有同学每门课的成绩及同学的个人信息
select st.student_id,st.name,st.mail,cl.name,cr.name,sc.score
fromstudent st,class cl,score sc,course cr
where st.student_id sc.student_id
and cl.class_id st.class_id
and cr.course_id sc.course_id
order byst.student_id;结果集中没有老外学中文同学的成绩因为score表中没有这位同学的记录
外连接
• 外连接分为左外连接、右外连接和全外连接三种类型MySQL不⽀持全外连接。 • 左外连接返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录则结果集中对 应字段会显⽰为NULL。 • 右外连接与左外连接相反返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记 录则结果集中对应字段会显⽰为NULL。 • 全外连接结合了左外连接和右外连接的特点返回左右表中的所有记录。如果某⼀边表中没有匹 配的记录则结果集中对应字段会显⽰为NULL。
语法
-- 左外连接表1完全显⽰
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接表2完全显⽰
select 字段 from 表名1 right join 表名2 on 连接条件;⽰例
• 查询没有参加考试的同学信息
# 查询没有参加考试的同学信息
# 左连接以JOIN左边的表为基准左表显示全部记录右表中没有匹配的记录用NULL填充
select * from student left join score on student.student_id score.student_id;# 过滤参加了考试的同学
select st.*
from student st
left join score sc
on st.student_id sc.student_id
where sc.score is NULL; • 查询没有学⽣的班级
# 查询没有学生的班级
# 右连接以JOIN右边的表为基准右表显示全部记录左表中没有匹配的记录用NULL填充
select *
fromstudent s
right join class c
ons.class_id c.class_id;# 过滤有学生的班级
selectc.*
from student s
right join class c
on s.class_id c.class_id
where student_id is NULL;⾃连接
应⽤场景
⾃连接是⾃⼰与⾃⼰取笛卡尔积可以把⾏转化成列在查询的时候可以使⽤where条件对结果进⾏ 过滤或者说实现⾏与⾏之间的⽐较。在做表连接时为表起不同的别名。
# 不为表指定别名
select * from score,score;# 指定别名
select * from score s1, score s2;示例
• 显示所有“计算机原理”成绩比“java”成绩高的成绩信息
# 显示所有“计算机原理”成绩比“java”成绩高的成绩信息
# 首先分两步进行先查出计算机原理和java的课程Id分别为1和3
select * from course where name 计算机原理 or name java;# 再查询成绩表中计算机原理成绩比java成绩高的信息
select *
from score s1,score s2
wheres1.student_id s2.student_id
ands1.course_id 3 and s2.course_id 1
and s1.score s2.score;# 结合在一起进行查询
select s.name,s1.*
fromscore s1,score s2,course c1,course c2,student s
where s1.student_id s2.student_id
ands.student_id s1.student_id
ands2.course_id c2.course_id
and c1.name 计算机原理 and c2.name java
and c1.course_id s1.course_id
and s1.score s2.score;表连接练习
• 显示所有“计算机原理”成绩比“java”成绩高的学⽣信息和班级以及成绩信息
select s.name 姓名,c.name 班级,s1.score 计算机原理成绩, s2.score java成绩
fromscore s1,score s2,course c1,course c2,student s,class c
where s1.student_id s2.student_id
and s1.course_id c1.course_id
ands2.course_id c2.course_id
and c1.name 计算机原理 and c2.name java
and s1.score s2.score
ands.student_id s1.student_id
and c.class_id s.class_id;⼦查询
⼦查询是把⼀个SELECT语句的结果当做别⼀个SELECT语句的条件也叫嵌套查询
语法
select * from table1 where col_name1 { | IN} (select col_name1 from table2 where col_name2 { | IN} [(select ...)] ...
)单⾏⼦查询
嵌套的查询中只返回⼀⾏数据 • 示例查询与不想毕业同学的同班同学
# 查询与不想毕业同学的同班同学
select * from student where class_id (select class_id from student where name 不想毕业) and name ! 不想毕业;多⾏⼦查询
嵌套的查询中返回多⾏数据使⽤[NOT] IN关键字 • 示例查询语文或英文课程的成绩信息
# 查询语文或英文课程的成绩信息
select * from score where course_id in (select course_id from course where name 英文 or name 语文);# 使用NOT IN 可以查询除了语文和英文课程的成绩信息
select * from score where course_id not in (select course_id from course where name 英文 or name 语文);多列⼦查询
单⾏⼦查询和多⾏⼦查询都只返回⼀列数据多列⼦查询中可以返回多个列的数据外层查询与嵌套 的内层查询的列要匹配 • 示例查询重复录⼊的分数
# 插入重复的分数score, student_id, course_id列重复
insert into score(score_id,student_id,course_id,score) values (21,1,1,70.5),(22,1,1,98.5),(23,2,1,60);# 子查询中返回多个列
select score_id,student_id,course_id,score
from score
where (student_id,course_id,score)
in (select student_id,course_id,score from score group by student_id,course_id,score having count(*) 1);在from⼦句中使⽤⼦查询
当⼀个查询产⽣结果时MySQL⾃动创建⼀个临时表然后把结果集放在这个临时表中最终返回 给⽤户在from⼦句中也可以使⽤临时表进⾏⼦查询或表连接操作 • 示例查询所有比“中文系2019级3班”平均分高的成绩信息
# 查询所有比“中文系2019级3班”平均分高的成绩信息
# 首先分步进行第一步先查出中文系2019级3班的平均分
select AVG(sc.score)
from class c, score sc, student s
where c.class_id s.class_id
and sc.student_id s.student_id
and c.name 中文系2019级3班 select sc.*
from score sc,(select AVG(sc.score) scorefrom class c, score sc, student s where c.class_id s.class_id and sc.student_id s.student_id and c.name 中文系2019级3班 ) tmp
where sc.score tmp.score;tmp 是临时表的别名
合并查询
在实际应⽤中为了合并多个select操作返回的结果可以使⽤集合操作符 unionunion all
创建新表并初始化数据
create table student1 like student;insert into student1(sn,name,mail,class_id) values(09982,李鬼,xuanfengqq.com,1),(00001,测试用户,null,1),(00391,白素贞,null,1),(00031,许仙,xuxianqq.com,1);Union
该操作符⽤于取得两个结果集的并集。当使⽤该操作符时会⾃动去掉结果集中的重复⾏。
select * from student union select * from student1;Union all
该操作符⽤于取得两个结果集的并集。当使⽤该操作符时不会去掉结果集中的重复⾏
select * from student union all select * from student1;插⼊查询结果
语法
INSERT INTO table_name [(column [, column ...])] SELECT ...示例
• 将student表中‘菩提老祖’和‘不想毕业’的学⽣信息复制到student1表中
insert into student1(sn,name,mail,class_id) (select s.sn,s.name,s.mail,s.class_id from student s where name 菩提老祖 or name 不想毕业);select * from student1;关于数据库的联合查询先了解到这里希望这篇文章对大家有帮助谢谢大家的阅读
