1. 连表查询详解
操作 | 描述 |
inner join | 如果表中至少有一个匹配, 就返回行 |
left join | 会从左表中返回所有的值, 即使右表中没有匹配 |
right join | 会从右表中返回所有的值, 即使左表中没有匹配 |
CREATE DATABASE if NOT EXISTS `school`;
USE DATABASE;
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`GradeName` VARCHAR(50) NOT NULL COMMENT '年纪名称',
PRIMARY KEY (`GradeID`)
) ENGINE = INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO grade(GradeID,GradeName) VALUES(1,'大一');
INSERT INTO grade(GradeID,GradeName) VALUES(2,'大二');
INSERT INTO grade(GradeID,GradeName) VALUES(3,'大三');
INSERT INTO grade(GradeID,GradeName) VALUES(4,'大四');
INSERT INTO grade(GradeID,GradeName) VALUES(5,'预科班');
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`StudentNo` INT(4) NOT NULL COMMENT '学号',
`SubjectNo` INT(4) NOT NULL COMMENT '课程编号',
`ExamDate` DATETIME NOT NULL COMMENT '考试时间',
`StudentResult` INT(4) NOT NULL COMMENT '考试成绩',
KEY `SubjectNo` (`subjectNo`)
)ENGINE=INNODB DEFAULT CHARSET = utf8;
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2023-06-25 09:00:00',85),
(1000,2,'2013-06-26 09:00:00',70),
(1000,3,'2013-06-27 09:00:00',68),
(1000,4,'2013-06-28 16:00:00',98),
(1000,5,'2013-06-29 16:00:00',68),
(1001,6,'2013-06-30 16:00:00',78),
(1002,7,'2013-07-01 16:00:00',88);
DROP TABLE IF EXISTS `student`;
CREATE TABLE student(
`StudentNo` INT(4) NOT NULL COMMENT '学号' ,
`Loginpwd` VARCHAR(20) DEFAULT NULL COMMENT '密码',
`StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`Sex` TINYINT DEFAULT NULL COMMENT '性别,0/1',
`Gradeid` INT(11) DEFAULT NULL COMMENT '年纪编号',
`Phone` VARCHAR(50) NOT NULL COMMENT '联系电话,不允许为空',
`Address` VARCHAR(255) NOT NULL COMMENT '地址,不允许为空',
`BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',
`Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号,不允许为空',
`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`StudentNO`), -- 主键
UNIQUE KEY `IdentityCard` (`IdentityCard`), -- 唯一索引
KEY `Email` (`Email`) -- index索引
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456434334011234'),
(1001,'123456','郭德',0,2,'13832343234','北京朝阳','1980-1-1','text123@qq.com','123456194001011234'),
(1002,'123456','李振',0,2,'13854325234','北京朝阳','1980-1-1','text123@qq.com','123456123001011234'),
(1003,'123456','刘强',0,2,'12130021234','北京朝阳','1980-1-1','text123@qq.com','123456193141011234'),
(1004,'123456','马云',0,2,'13832131234','北京朝阳','1980-1-1','text123@qq.com','123454332001011234'),
(1005,'123456','赵青',0,2,'13545441234','北京朝阳','1980-1-1','text123@qq.com','123456142101011234'),
(1006,'123456','赵强',1,3,'13805445222','广东深圳','1990-1-1','text111@qq.com','123132131301011233');
CREATE TABLE `subject`(
`SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`ClassHour` INT(4) DEFAULT NULL COMMENT '学时',
`GradeID` INT(4) DEFAULT NULL COMMENT '年纪编号',
PRIMARY KEY(`SubjectNo`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
2. 连表查询示例
1. 查询参加了考试的同学 (查询列 : 学号, 姓名, 科目编号, 分数) — 两表
【思路】
- 上述字段分别来自 student 表 (学号,姓名) 和 result 表 (科目编号,分数)
- 因为需要查询参加考试的同学, 而学生表中有些学生是没有参加考试的, 所以以成绩表为基准, 所以使用右连接查询.
【SQL 实现】
-- RIGHT JOIN
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM `student` s
RIGHT JOIN `result` r
ON s.StudentNo = r.StudentNo;
如果使用左查询, 将会把没有参加考试的同学的行也查询出来, 就不符合题目要求 (查询参加考试的同学).
2. 查询缺考的同学 (学号, 姓名, 科目编号, 分数) — 两表
【思路】
2. 确定使用哪种连接查询.
- 上述字段分别来自 student 表 (学号,姓名) 和 result 表 (科目编号, 分数)
- 因为题目需要查询未参加考试的同学, 而成绩表中只有参加了考试的同学, 所以以左表为基准, 所以使用左连接查询.
【SQL 实现】
-- LEFT JOIN
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM `student` s
LEFT JOIN `result` r
ON s.StudentNo = r.StudentNo
WHERE StudentResult IS NULL;
3. 查询参加考试的同学信息(学号, 学生姓名, 科目名称, 分数)– 三表
【思路】
2. 确定使用哪种连接查询.
4. 写判断条件 : 学生表中的 StudentNo = 成绩表中的 StudentNo. 成绩表中的 SubjectNo = 学科表中的 SubjectNo.
5. 根据需求增加 where 等值查询 (可有可无).
- 上述字段分别来自 student 表 (学号,姓名) ,result 表 (分数) 和 subject 表 (科目名称)
- 先查 student , rersult 两张表, 再连 subject 第三张表.
- 对于 student 和 result 表, 要查询参加考试的同学, 使用右连接, 而对于这两张表查询出来的结果和 subject 进行连表查询时, 没有以哪张表为基准, 所以使用 inner join 就可以了.
【SQL 实现】
1. 先查 student 表 和 result 表
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.StudentNo = r.StudentNo;
2. 再拿着查询结果与 subject 表进行连表查询
完整 SQL :
SELECT s.StudentNo '学号',StudentName '姓名',
SubjectName '科目名称',StudentResult '考试成绩'
FROM student s
RIGHT JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON sub.SubjectNo = r.SubjectNo;
【总结】
整体查询思路 :
1. 我要查询哪些数据 -> SELECT …
原文地址:https://blog.csdn.net/xaiobit_hl/article/details/131499185
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如若转载,请注明出处:http://www.7code.cn/show_12293.html
如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱:suwngjj01@126.com进行投诉反馈,一经查实,立即删除!
声明:本站所有文章,如无特殊说明或标注,均为本站原创发布。任何个人或组织,在未征得本站同意时,禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。