mysql,SQL标准,多表查询中内连接,外连接,自然连接等详解之查询结果集的笛卡尔积的演化-阿里云开发者社区

开发者社区> 刘洋intsmaze> 正文

mysql,SQL标准,多表查询中内连接,外连接,自然连接等详解之查询结果集的笛卡尔积的演化

简介: 先附上数据 CREATE TABLE `course` ( `cno` int(11) NOT NULL, `cname` char(30) CHARACTER SET utf8 NOT NULL, `ctime` int(11) NOT NULL, `scount` ...
+关注继续查看

先附上数据

CREATE TABLE `course` (
  `cno` int(11) NOT NULL,
  `cname` char(30) CHARACTER SET utf8 NOT NULL,
  `ctime` int(11) NOT NULL,
  `scount` int(11) NOT NULL,
  `ctest` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `course` VALUES ('4', '应用数学基础', '48', '120', '2016-03-10 10:08:29');
INSERT INTO `course` VALUES ('5', '生物工程概论', '32', '80', '2016-03-10 10:09:24');
INSERT INTO `course` VALUES ('1', '计算机软件基础', '32', '70', '2016-03-10 10:09:47');
INSERT INTO `course` VALUES ('2', '计算机硬件基础', '24', '80', '2016-03-10 10:10:28');
INSERT INTO `course` VALUES ('8', '模拟电路设计', '28', '90', '2016-04-06 10:11:02');
INSERT INTO `course` VALUES ('7', '机械设计实践', '48', '68', '2016-03-10 10:11:29');
INSERT INTO `course` VALUES ('3', '生物化学', '32', '40', '2016-03-29 10:11:54');
INSERT INTO `course` VALUES ('9', '数据库设计', '16', '80', '2016-03-10 10:12:14');
INSERT INTO `course` VALUES ('6', '设计理论', '28', '45', '2016-03-10 10:12:33');
INSERT INTO `course` VALUES ('10', '计算机入门', '24', '150', '2016-03-10 10:12:53');
INSERT INTO `course` VALUES ('11', '数字电路设计基础', '30', '125', '2016-03-10 10:13:10');

CREATE TABLE `student` (
  `sno` char(4) CHARACTER SET utf8 DEFAULT NULL,
  `sname` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `dname` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `ssex` char(2) CHARACTER SET utf8 NOT NULL,
  `cno` int(11) NOT NULL,
  `mark` decimal(3,1) NOT NULL,
  `type` char(4) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `student` VALUES ('9701', '刘建国', '管理工程', '', '4', '82.5', '必修');
INSERT INTO `student` VALUES ('9701', '刘建国', '管理工程', '', '10', '70.0', '必修');
INSERT INTO `student` VALUES ('9701', '刘建国', '管理工程', '', '1', '78.5', '选修');
INSERT INTO `student` VALUES ('9702', '李春', '环境工程', '', '5', '63.0', '必修');
INSERT INTO `student` VALUES ('9702', '李春', '环境工程', '', '10', '58.0', '选修');
INSERT INTO `student` VALUES ('9703', '王天', '生物', '', '5', '48.5', '必修');
INSERT INTO `student` VALUES ('9703', '王天', '生物', '', '2', '86.0', '选修');
INSERT INTO `student` VALUES ('9704', '李华', '计算机', '', '4', '76.0', '必修');
INSERT INTO `student` VALUES ('9704', '李华', '计算机', '', '1', '92.0', '必修');
INSERT INTO `student` VALUES ('9704', '李华', '计算机', '', '2', '89.0', '必修');
INSERT INTO `student` VALUES ('9704', '李华', '计算机', '', '9', '80.0', '必修');
INSERT INTO `student` VALUES ('9704', '李华', '计算机', '', '8', '70.0', '选修');
INSERT INTO `student` VALUES ('9705', '孙庆', '电子工程', '', '8', '79.0', '必修');
INSERT INTO `student` VALUES ('9705', '孙庆', '电子工程', '', '1', '59.0', '必修');
INSERT INTO `student` VALUES ('9705', '孙庆', '电子工程', '', '11', '52.0', '必修');
INSERT INTO `student` VALUES ('9705', '孙庆', '电子工程', '', '6', '68.0', '必修');
INSERT INTO `student` VALUES ('9706', '高伟', '机械工程', '', '13', '93.0', '必修');
INSERT INTO `student` VALUES ('9706', '高伟', '机械工程', '', '12', '88.5', '必修');
INSERT INTO `student` VALUES ('9706', '高伟', '机械工程', '', '1', '78.0', '选修');
INSERT INTO `student` VALUES ('9706', '高伟', '机械工程', '', '10', '76.0', '选修');

CREATE TABLE `teacher` (
  `tno` int(11) NOT NULL,
  `tname` varchar(10) CHARACTER SET utf8 NOT NULL,
  `cno` int(11) NOT NULL,
  `sal` int(11) DEFAULT NULL,
  `dname` char(10) CHARACTER SET utf8 NOT NULL,
  `tsex` char(2) CHARACTER SET utf8 NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `teacher` VALUES ('1', '王军', '4', '800', '数学', '', '32');
INSERT INTO `teacher` VALUES ('2', '李丹', '5', '1200', '生物', '', '54');
INSERT INTO `teacher` VALUES ('3', '王永军', '1', '900', '计算机', '', '40');
INSERT INTO `teacher` VALUES ('4', '刘小静', '2', '1200', '计算机', '', '46');
INSERT INTO `teacher` VALUES ('5', '高伟', '8', '2100', '电子工程', '', '39');
INSERT INTO `teacher` VALUES ('6', '李伟', '7', '1200', '机械工程', '', '29');
INSERT INTO `teacher` VALUES ('7', '刘辉', '3', '900', '生物', '', '46');
INSERT INTO `teacher` VALUES ('8', '刘静', '12', '1300', '经济管理', '', '28');
INSERT INTO `teacher` VALUES ('9', '李伟', '9', null, '计算机', '', '43');
INSERT INTO `teacher` VALUES ('10', '刘一凯', '13', null, '计算机', '', '33');

简单的二表连接

SELECT tname,dname,cname,ctest from teacher,course WHERE teacher.cno=course.cno
该语句的执行过程实例可以表示这样:
a,系统首先执行from子句,这里from子句列出有两个表teacher表和course表,DBMS讲计算这两个表的笛卡尔积,列出这两个表中行的所以可能组合,形成一个中间表。中间表中的每条记录包含了两个表中的所有行。
b,然后系统执行where子句,根据teacher.cno=course.cno关系对中间表进行搜索,去除那些不满足该关系的记录。
c,最后系统执行select语句,从执行where子句后得到的中间表的每条记录中,提取tname,dname,cname,ctest4个字段的信息作为结果表。
 
 
需要强调,表的连接所依据的关系是在where子句中定义的。在实际应用中,用户要实现表的连接必然要依据一定的关系。
 
 
如果不指明连接关系,即不使用where子句。
SELECT tname,dname,cname,ctest from teacher,course

        
从结果可以看到,每个教师的信息均与所有课程信息进行了匹配连接。它实际返回连接表中所有数据行的笛卡尔积,其结果集合中的数据行数等于第一个表中符合查询条件的数据行乘以第二个表中符合查询条件的数据行数,即10X11=110条记录。

采用join关键字建立连接

        也可以在from子句中,通过连接关键字实现表的连接,这样有助于将连接操作与where的搜索条件区分开来。
SELECT COLUMN from join_table join_type join_table on (join_condition)
 
join_type为连接类型,可分为4种:自然连接,内连接,外连接和交叉连接。
 

自连接

自连接是指表与其自身进行连接,这需要使用表别名。
查询成绩中存在不及格课程的学生的姓名,所在系,所有的课程及成绩信息
SELECT s.sname,s.dname,s.cno,s.mark
from student s
where s.mark<60
无法得到想要结果
 
 
SELECT s.sname,s.dname,s.cno,s.mark
from student s
where s.sno in(SELECT DISTINCT s.sno from student s where s.mark<60)
得到想要结果
 
 
SELECT DISTINCT s.sname,s.dname,s.cno,s.mark
from student s,student s2
where s.sno=s2.sno
and s2.mark<60
from子句中的两个表实际上都是表student。为了独立地使用它们,采用表别名方法。
 
SELECT s.sname,s.dname,s.cno,s.mark
from student s,student s2
where s.sno=s2.sno
and s2.mark<60
系统首先执行from子句,将student表S1与它自身S2的笛卡尔积,作为中间表。
实际上,该中间表的每一条记录包含两部分信息,一部分是S1的记录,一部分是S2的记录。而后执行where子句,在中间表中,搜索S2中成绩低于60的学生的记录,同时要求记录中S1与S2是同一个学生的记录即学号相同。最后执行select语句,从中间表获取S1中相应的信息作为结果表。
 
当执行where子句,从中间表中逐条搜索S2中成绩低于60的学生的记录时,由于孙庆有两门课程不及格,所以对每门不及格的记录都满足搜索条件,因此导致从S1得到的信息中出现了重复的记录。
 
简单来说,中间表是没有重复记录的,但是S1部分字段是有重复的,而结果集提取的只是S1部分的字段,因此就有可能有重复记录。
 
一般情况,自连接也可以使用子查询的方式实现。
 
 
SELECT DISTINCT s.sname,s.dname,s.cno,s.mark
from student s,student s2
where s.sno=s2.sno
and s.mark<60
 

自然连接

它将表中具有相同名称的列自动进行记录匹配,自然连接不必指定任何同等连接条件。
自然连接自动判断相同名称的列,而后形成匹配。缺点是,虽然可以指定查询结果包括哪些列,但是不能人为地指定哪些列被匹配。另外,自然连接的一个特点是连接后的结果表中匹配的列只有一个。如上,在自然连接后的表中只有一列C。
 
 
从student表和teacher表中查询学生姓名,所在系,所修的本系教师开设的课程的课程号以及开课教师姓名。这时候就采用natural join对两个表进行自然连接。
SELECT sname,dname,cno,tname
from student NATURAL join teacher
等价
SELECT sname,s.dname,s.cno,tname
from student s, teacher t
where s.dname=t.dname
and s.cno=t.cno

 

事实上,使用基于where子句的等值连接要比使用natural join运算符进行自然连接要灵活的多。
正如前面介绍的,使用natural join运算符自动判断出具有相同名称的列,而后形成匹配,不能人为地指定哪些列被匹配。当自然连接student和teacher表时,CNO和dname列同时被匹配,而不能只匹配一列。
 
 
 

外连接

不管是内连接还是带where子句的多表查询,都组合自多个表,并生成结果表。换句话说,如果任何一个源表中的行在另一个源表中没有匹配,DBMS将把该行放在最后的结果表中。
 
而外连接告诉ODBC生成的结果表,不仅包含符合条件的行,而且还包含左表(左外连接时),右表(右外连接时)或两个边接表(全外连接)中所有的数据行。
 
 
SQL的外连接共有三种类型:左外连接,右外连接,全外连接。
 

1,左外连接

左外连接,left outer join ,告诉DBMS生成的结果表中,除了包括匹配行外,还包括join关键字(from子句中)左边表的不匹配行。
左外连接实际可以表示为:
左外连接=内连接+左边表中失配的元组。
其中,缺少的右边表中的属性值用null表示。如下:

SELECT s.sno,sname,s.cno,cname,ctest,mark
from student s LEFT JOIN course c
on s.cno=c.cno
ORDER BY sname
 

右外连接

外连接,right outer join ,告诉DBMS生成的结果表中,除了包括匹配行外,还包括join关键字(from子句中)边表的不匹配行。
外连接实际可以表示为:
外连接=内连接+边表中失配的元组。
其中,缺少的左边表中的属性值用null表示。如下:
SELECT s.sno,sname,s.cno,cname,ctest,mark
from student s RIGHT JOIN course c
on s.cno=c.cno
ORDER BY sname

 

全外连接

全外连接,full outer join,告诉DBMS生成的结果表中,除了包括匹配行外,还包括join关键字(from子句中)左边表和右边表的不匹配行。
可以这样表示:
全外连接=内连接+左边表中失配的元组+右边表中失配的元组
 
SELECT s.sno,sname,s.cno,cname,ctest,mark
from student s full OUTER JOIN course c
on s.cno=c.cno
ORDER BY sname
 
本人使用mysql数据库,因为mysql暂时还不支持全外连接full的功能.
 
 一些语句流程顺序,等我有空回顾在写把。等我。勿急躁。
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
老铁,你的--->推荐,--->关注,--->评论--->是我继续写作的动力。
微信公众号号:Apache技术研究院
由于博主能力有限,文中可能存在描述不正确,欢迎指正、补充!
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL语句查询结果集中的动态修改案例(临时表+游标)
本文转载:http://www.cnblogs.com/Charles2008/archive/2008/03/04/1090314.html 曾经一位朋友问我这样一个问题:怎样在查询出来的结果集中增加一个新列(有规律)? 如:数据库中的结构和数据如下: (tableName : People)...
618 0
mybatis如何直接 执行传入的任意sql语句 并按照顺序取出查询的结果集
mybatis如何直接 执行传入的任意sql语句 并按照顺序取出查询的结果集 需求: 1.直接执行前端传来的任何sql语句,parameterType="String", 2.对于任何sql语句,其返回值类型无法用resultMap在xml文件里配置或者返回具体的bean类型,因此设置resultType="java.util.Map",但是Map并不保证存入取出顺序一致, 因此设置resultType="java.util.LinkedHashMap",为保证查询的字段值有序(存入与取出顺序一致)所以采用LinkedHashMap。
1080 0
JDBC判断数据库查询结果集是否为空
通常来说都是用rs.next()来判断结果集是否为空,但是由于执行rs.next()后指针指向的是结果集中的第一条记录,此时再用while(rs.next())取结果集中的数据就会导致第一条数据无法得到。
797 0
MySQL---数据库从入门走向大神系列(十一)-Java获取数据库/结果集的元信息、将数据表写入excel表格
数据库的元信息: 首先介绍一下数据库的元信息(元数据): 元数据(Metadata)是关于数据的数据。 元数据是描述数据仓库内数据的结构和建立方法的数据。 存储的数据是什么类型,什么驱动等等,这些描述数据的数据,就是元数据! 准备: package cn.
1295 0
MySQL表连接(内连接、交叉连接、外连接、联合查询)
MySQL表连接(内连接、交叉连接、外连接、联合查询) 一、内连接(INNER JOIN)    SELECT * FROM 表1 INNER JOIN 表2   ON   条件    SELECT * FROM 表1 INNER JOIN 表2   WHERE  条件    典型的联接运算,使用像 =  或 之类的比较运算符)。
4254 0
JDBC操作数据库--查询
                           select information by  sex  !                                        
589 0
+关注
刘洋intsmaze
证书:软件设计师,Apache kylin管理员 java服务端开发,分布式开发,大数据开发工程师
45
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载