MySQL数据管理: 联表查询 [DQL语言]

最新推荐文章于 2024-03-09 06:00:00 发布

qq_15907907

最新推荐文章于 2024-03-09 06:00:00 发布

阅读量130

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/qq_15907907/article/details/115370764

版权

MySQL 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

MySQL数据管理: 联表查询 [DQL语言]

-- 创建一个school数据库
create database if not exists `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;


-- 插入科目数据
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行
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','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 插入成绩数据  这里仅插入了一组，其余自行添加
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- 插入年级数据
insert into `grade` (`gradeid`,`gradename`) values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

联表查询

联表模糊查询

-- 联表模糊查询
SELECT r.studentno,r.studentresult,s.* 
FROM `student` AS s,`result` AS r
WHERE 
r.studentno=s.studentno
&& r.studentno=1000
&& s.`studentname` LIKE '%张%'

JOIN 对比

七种join理论

1.左连接（左表的全部，右表不满足补NULL）

SELECT * 
FROM tbl_dep d 
LEFT JOIN tbl_emp e 
ON d.id=e.deptId;

2.特殊的左连接，（显示为左表的独有的数据）

说明：查询tbl_dep 表中跟tbl_emp 表无关联关系的数据，即tbl_dep 独占，且tbl_emp 表的显示列补

NULL；
SELECT * 
FROM tbl_dep d 
LEFT JOIN tbl_emp e 
ON d.id=e.deptId 
WHERE e.deptId IS NULL;

3.内连接(两表的共有部分)

SELECT * 
FROM tbl_dep d 
INNER JOIN tbl_emp e 
ON d.id=e.deptId;

4.右连接（右表的全部，左表不满足的补NULL）

SELECT * 
FROM tbl_dep d 
RIGHT JOIN tbl_emp e 
ON d.id=e.deptId;

5.特殊的右连接（显示为右表的独有的数据）

说明：查询tbl_emp 表中跟tbl_dep 表无关联关系的数据，即tbl_emp 独占，且tbl_dep 表的显示列补

NULL；
SELECT * 
FROM tbl_dep d 
RIGHT JOIN tbl_emp e 
ON d.id=e.deptId 
WHERE d.id IS NULL;

6.全连接（显示全部数据）（mysql 不支持 full outer join）

UNION ：有去重的功能。

SELECT * 
FROM tbl_dep d 
RIGHT JOIN tbl_emp e 
ON d.id=e.deptId;

SELECT * 
FROM tbl_dep d 
LEFT JOIN tbl_emp e 
ON d.id=e.deptId UNION;

7.显示两表的独有的数据

UNION ：有去重的功能。

SELECT * 
FROM tbl_dep d 
RIGHT JOIN tbl_emp e 
ON d.id=e.deptId 
WHERE d.id IS NULL;

SELECT * 
FROM tbl_dep d 
LEFT JOIN tbl_emp e 
ON d.id=e.deptId 
WHERE e.deptId IS NULL UNION

======================联表查询 join ==============================

SELECT * FROM  `result`,`student`
/*
分析需求 分析查询的字段来自哪些表
确定使用哪种链接查询
确定交叉点(这两个表中哪个数据是相同的)
判断的条件 :学生表中的 studentno=成绩表 studentno
*/
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
 FROM `student` AS s
 INNER JOIN `result` AS r
 WHERE s.`studentno`=r.`studentno`



-- Right join
-- join(连接表名) on(判断条件) 连接查询
-- where 等值查询
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno` = r.`studentno` 
WHERE s.`studentno` IS NOT NULL



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



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

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


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

/*
 分析查询哪些表 student result subject
 确定使用哪种连接查询
 确定交叉点
 判断条件 学生表中的studentno= studentno
 */
SELECT s.`studentno`,s.`studentname`,su.`subjectname`,r.`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON r.`studentno`=s.`studentno`
INNER JOIN `subject` AS su
ON r.`subjectno`=su.`subjectno`
WHERE s.`studentno` IS NOT NULL



-- 我要查询哪些数据 select (字段名)
-- 从哪里查询       from (表名)  (连接方式) join (连接表名) on (连接条件)
-- 假设存在一种多张表查询，慢慢来 先查询两张表慢慢增加

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

自连接联表查询

自连接建表

CREATE TABLE `school`.`category`( 
`categoryid` INT(3) NOT NULL COMMENT 'id'
, `pid` INT(3) NOT NULL COMMENT '父id 没有父则为1'
, `categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字'
, PRIMARY KEY (`categoryid`) 
) ENGINE=INNODB 
CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
insert into `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values ('3', '1', '软件开发');
insert into `school`.`category` (`categoryid`, `PId`, `categoryname`) values ('5', '1', '美术设计');
insert iNTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES ('4', '3', '数据库'); 
insert into `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) values ('8', '2', '办公信息');
insert into `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) values ('6', '3', 'web开发'); 
inserT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) valueS ('7', '5', 'ps技术');

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

categoryid	categoryname
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryname
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	美术设计

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

父类	子类
2	信息技术
3	软件开发
5	美术设计

查询父子信息

-- 查询父子信息
SELECT a.`categoryname` AS `父栏目`,b.`categoryname` AS `子栏目` 
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`

qq_15907907

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

MySQL数据管理: 联表查询 [DQL语言]

MySQL数据管理: 联表查询 [DQL语言]

联表模糊查询

JOIN 对比

1.左连接（左表的全部，右表不满足补NULL）

2.特殊的左连接，（显示为左表的独有的数据）

3.内连接(两表的共有部分)

4.右连接（右表的全部，左表不满足的补NULL）

5.特殊的右连接（显示为右表的独有的数据 ）

6.全连接（显示全部数据）（mysql 不支持 full outer join）

7.显示两表的独有的数据

自连接联表查询

5.特殊的右连接（显示为右表的独有的数据）