mysql连接查询、联合查询、子查询原理与用法实例详解

本文实例讲述了mysql连接查询、联合查询、子查询原理与用法。分享给大家供大家参考，具体如下：

本文内容：

• 连接查询
• 联合查询
• 子查询
  • from子查询
  • where子查询
  • exists子查询

首发日期：2018-04-11

 

---

 

连接查询：

• 连接查询就是将多个表联合起来查询，连接查询方式有内连接、外连接、自然连接、交叉连接。连接查询使得可以同时查看多张表中数据。
  • 内连接：有条件连接，多个表之间依据指定条件连接，匹配结果是保留符合匹配结果的记录。
  • 外连接：与内连接不同的是不管匹配符不符合都保留，根据外连接连接方式来决定保留哪张表，比如保留左表的话，那么左表无法匹配右表时，保留左表数据，然后置右表字段数据为null.
  • 自然连接：有条件连接，自动依据“同名字段”连接（多个同名字段就都作为条件）。
  • 交叉连接cross join：无条件连接，将每一条记录与另外一个表的每一条记录连接（笛卡尔积），结果是字段数等于原来字段数之和，记录数等于之前各个表记录数之乘积。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

-- 实验表结构 create table student( id int , name varchar (15), gender varchar (15), cid int ); create table class( cid int , cname varchar (15) ); drop table student,class; -- 实验表数据： insert into student values (1, "lilei" , "male" ,1),(2, "hanmeimei" , "male" ,2),(3, "jack" , "male" ,1),(4, "alice" , "female" ,4); --这里特意创建了一个class中没有的4 insert into class values (1, "linux" ),(2, "python" ),(3, "java" ),(5, "html5" ); --这里特意创建了一个student中没有的5 select * from student; select * from class;

内连接：

• 从左表中取出每一条记录，去右表中与所有的记录进行匹配，保留匹配成功的记录，并将两份记录拼接。
• 语法：select 字段列表 from 左表 [inner] join 右表 on 左表.字段 = 右表.字段;
  • 不使用on条件的时候，结果与交叉连接相同
  ?

  1 2 3 4

  -- 内连接 -- select * from student inner join class; --结果与交叉连接相同 select * from student join class on student.cid = class.cid; select * from student inner join class on student.cid = class.cid;

  

外连接：

• 与内连接不同的是主表记录不管匹配符不符合都保留，方式有左外连接、右外连接,左外连接是保留左表，右外连接是保留右表
• 语法：
  • 左外连接：select 字段列表 from 左表 left join 右表 on 左表.字段 = 右表.字段;
  • 右外连接：select 字段列表 from 左表 right join 右表 on 左表.字段 = 右表.字段;
  ?

  1	select * from student left join class on student.cid = class.cid;

  

?

1	select * from student right join class on student.cid = class.cid;

自然连接：

• 自动匹配连接条件，系统以字段名字作为匹配模式（同名字段就作为条件，多个同名字段就都作为条件）
  • 自然内连接：类似内连接，但不提供连接条件。
  • 自然外连接：类似外连接，但不提供连接条件。
• 语法：
  • 自然内连接：select 字段列表 from 表名 natural join 表名;
  • 自然外连接：select 字段列表 from 表名 natural left\right join 表名;
  ?

  1	select * from student natural join class;

  

?

1	select * from student natural left join class;

交叉连接：

• 将每一条记录与另外一个表的每一条记录连接
• 语法：
  • select 字段列表 from 表名 cross join 表名;
  • select 字段列表 from 表名 ,表名;
  ?

  1 2	select * from student cross join class; select * from student,class;

  

补充：

• 在多个表中，为了区分每个表，以及简便使用，可以使用表别名。 ?

  1 2	select * from student inner join class on student.cid = class.cid; -- 原本结果 select id, name ,gender,c.cid,cname from student as s inner join class as c on s.cid = c.cid; -- 使用表别名
• 可以多重连接。
• 外连接可以模拟自然连接，只需要将连接条件on 左表.字段 = 右表.字段变成"using 字段名"即可。

 

---

 

联合查询：

• 联合查询是将多个查询结果在记录上进行拼接。（相当于将其他表的查询记录结果连接到第一个表的后面）【因为是拼接，所以多个查询结果的字段数必须相同】【拼接不在意数据类型，比如第一个表的第一个字段是int,但后面的表中的varchar依然可以拼接到第一列中】
• 语法：select语句 union select语句…; ?

  1 2 3 4

  select name ,gender from student union select * from class; -- 因为class就两个字段，所以第一个只选出两个字段

  

补充：

• union可以有选项，加在union 后面，all选项是不对相同去重，distinct是去重。
• 联合查询一般用来处理同一表中以不同方法显示不同数据。（比如，想将学生各科成绩表中的两科(这里假设为python 跟linux)同时查询出来）
• union和order by同时使用报错时
  • 如果你想针对联合查询中的某个查询结果排序，需要将这个select语句用括号括起来。【另外，由于联合查询的拼接机制，需要在order by 后面加上limit子句才行，limit的数量可以是一个很大的值。】
  • 如果是针对最终的联合查询结果，就在最后一个select语句中使用order by 【建议给最后一个字段加上括号，再加ordery by,使用情况是有同名字段】

 

---

 

子查询：

• 子查询是嵌套在查询语句中的查询。
• 子查询按照出现的位置可以分为三类：
  • from子查询：子查询跟在from之后的；一般用在“先查出二维表，再处理”的情况。
    • 比如： ?

      1 2	-- 这是一个无意义的例子。仅为举例使用 select cid,cname from ( select * from class where cname= "python" ) as c;

  • where子查询：子查询跟在where条件中；一般用在“先查询出指定条件再查询”的情况
    • 比如： ?

      1	select * from student where cid=( select cid from class where cname= "python" );

  • exist子查询：子查询在exist语句里面；一般用作“存在才做”的情况
    • 比如： ?

      1 2	-- 这是一个如果学生没有选择cid=1的课，那么不输出对应课程信息的例子 select * from class where exists( select * from student where cid=1) and cid=1;

  • 事实上，有些人认为union之后跟着的也是子查询，不过这里不把这些当作子查询，只把上面几个与“查询”非常紧密相关的当作子查询。

补充：

• 事实上，还可以根据结果来分类子查询：
  • 标量子查询，子查询得到的结果是一行一列，情况一般发生于where子查询只查询出一行一列的情况。
  • 列子查询，子查询得到的结果是一列多行，情况一般发生于where子查询查询出一列多行的情况。
  • 行子查询，子查询得到的结果是多列一行（或者多行多列） ，情况一般发生在where子查询查询出多列一行的情况。
  • 表子查询，子查询得到的结果是多行多列 ，情况一般发生在from子查询中
• where子查询中有时候还使用一些其他关键字，如any,all,some，但对=来说，=基本可以实现他们的功能了。

希望本文所述对大家MySQL数据库计有所帮助。

原文链接：https://www.cnblogs.com/progor/p/8796283.html