联表查询JoinON详解（自连接）

发布于：2022-08-05 ⋅ 阅读:(349) ⋅ 点赞:(0)

链表查询

JOIN

 

 

例子：

（1）创建school数据库

CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) 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`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

-- 创建年级表
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;

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
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 AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;

-- 创建成绩表
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;

输出结果：

student

 

result

 

（2）查询参加了考试的同学（学号，姓名，科目编号，分数）

-- ======= 联表查询 join ==============
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result

思路：
1、分析需求，分析查询的字段来自哪些表，（连接查询）
2、确定使用哪种连接查询？--------------------------- 7种

  • 确定交叉点（这两个表中哪个数据是相同的）

  • 判断的条件：学生表中的 studentNo = 成绩表 studentNo

  • join（判断条件） on（连接的表） 连接查询

  •  where 等值查询

  • as是可以省略的，使用ON和where都是一样的

INNER JOIN（内连接）

-- INNER JOIN
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno

输出结果：

 

RIGHT JOIN（右连接）

-- RIGHT JOIN
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno

输出结果：

 

left JOIN（左链接）

-- left JOIN
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno

输出结果：

 

（3）查询缺考的同学

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
WHERE studentresult IS NULL

思考题（查询了参加考试的同学信息：学号，学生姓名，科目名，分数）

  1. 分析需求，分析查询的字段来自哪些表，student、result、subject（连接查询）
  2. 确定使用哪种连接查询？ 7种
  3. 确定交叉点（这两个表中哪个数据是相同的）
  4. 判断的条件：学生表中的 studentNo = 成绩表 studentNo
  5. s.后面不能加`` 其他的都可以加``
-- s.后面不能加`` 其他的都可以加``

SELECT s.studentno,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON r.studentno = s.studentno
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno

输出结果：

操作 描述
Inner join 如果表中至少有一个匹配，就返回行
left join 会从左表中返回所有的值，即使右表中没有匹配
right join 会从右表中返回所有的值，即使左表中没有匹配

总结：

  • 我要查询哪些数据 select....
  • 从那几个表中查 FROM （表 XXX） join （连接表） ON （交叉条件）
  • 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加
  • FROM a left join b 这个就是把a表全部包括，再去找b中的东西，没有就是null（左连接）
  • FROM a right join b 这个就是把b表全部包括，再去找a中的东西，没有就是null（右连接）

自连接及链表查询

自连接

        自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

例子：

 

父类 

        先看pid，去找相同的pid（pid = 1），然后去列出对应的categoryid

categoryid categoryName
2 信息安全
3 软件开发
5 美术设计

子类     

pid categoryid categoryName
3 4 数据库
2 8 办公信息
3 6 web开发
5 7 ps技术

操作：查询父类对应的子类关系

        子类的pid去对应父类的categoryid

父类 子类
软件开发 数据库
信息安全 办公系统
软件开发 web开发
美术设计 ps技术 

-- ==================自连接=======
CREATE TABLE `category`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryname` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET = utf8;

INSERT INTO `category` (`categoryid`, `pid`, `categoryname`) 
VALUES ('2','1','信息技术'),
('3','1','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');

-- 查询父子信息：把一张表看成为两个一摸一样的表
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`

-- 查询学员所属的年级（学号，学生的姓名，年级名称）
SELECT studentno,studentname,`gradename`
FROM student AS s
INNER JOIN `grade` AS g
ON s.`gradeid` = g.`gradeid`

-- 查询科目所属的年级（科目名称，年级名称）
SELECT `subjectname`,`gradename`
FROM `subject` AS sub
INNER JOIN `grade` AS g
ON sub.`gradeid` = g.`gradeid`

-- 查询了参加 数据库结构-1 考试的同学信息：学号，学生姓名，科目名，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON sub.`subjectno` = r.`subjectno`
WHERE `subjectname` = '数据结构-1'

SELECT语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[AS aliasl][, table.field2[AS alias2]][,.....]]}
FROM table_name [AS table_alias]
	[LEFT | RIGHT | ineer JOIN table_name2] -- 联合查询
	[WHERE ....] -- 指定结果需满足的条件
	[GROUP by.....] -- 指定结果按照哪几个字段来分组
	[HAVING] -- 过滤分组的记录必须满足的次要条件
	[ORDER by.....] -- 指定查询记录按一次或多个条件排序
	[LIMIT {[OFFSET,]ROW_COUNT | row_countoffset OFFSET}]
	-- 指定查询的记录从哪条到哪条

注意：

  1. [ ]括号代表可选的
  2. ｛｝括号代表必选的

本文含有隐藏内容，请 开通VIP 后查看

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布