MySQL联结查询和组合查询

时间:2022-09-15 23:07:49

联结查询

  1.关系表

    主键:一列或一组列,能够唯一区分表中的每一行,用来表示一个特定的行

    外键:为某个表中的一列,包含另一个表的主键,定义量表的关系。

  2.创建联结

    规定要连接的表和他们如何关联即可

    在联结两个表时,将第一个表中的每一行与第二个表中的每一行配对,where子句作为过滤条件,只包含那些匹配给定的条件。如果不加where,返回检索数目为两表行数的乘积,也就是笛卡尔积。

内联接

  用典型的联接运算,使用像 =  或 <> 之类的比较运算符,包括相等联接和自然联接。使用:table1 inner join table2 on 条件,on传递联结条件

select user.*,orders.* from user inner join orders on user.u_id=orders.o_buyer_id;
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+
| u_id | u_name | u_pwd  | u_truename | u_role | u_email      | o_id | o_buyer_id | o_seller_id | o_totalprices | o_state | o_information |
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+
|    1 | z3     | aaaaa  | zhang3     | seller | zhang@qq.com |    1 |          1 |           1 |           2.5 | sale    | qianbi        |
|    2 | w5     | aaabbb | wang5      | buyer  | wang@163.com |    2 |          2 |           2 |           3.5 | sale    | xiangpi       |
|    3 | ll     | cccbbb | lili       | buyer  | li@163.com   |    3 |          3 |           3 |             3 | empty   | wenjuhe       |
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+

外联结

  1.左外联结

  LEFT  JOIN或LEFT OUTER JOIN,左向外联接的结果集包括  LEFT OUTER子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。

select user.*,orders.* from user left join orders on user.u_id=orders.o_buyer_id;
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+
| u_id | u_name | u_pwd  | u_truename | u_role | u_email      | o_id | o_buyer_id | o_seller_id | o_totalprices | o_state | o_information |
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+
|    1 | z3     | aaaaa  | zhang3     | seller | zhang@qq.com |    1 |          1 |           1 |           2.5 | sale    | qianbi        |
|    2 | w5     | aaabbb | wang5      | buyer  | wang@163.com |    2 |          2 |           2 |           3.5 | sale    | xiangpi       |
|    3 | ll     | cccbbb | lili       | buyer  | li@163.com   |    3 |          3 |           3 |             3 | empty   | wenjuhe       |
|    4 | xh     | adadad | xiaohong   | seller | hong@163.com | NULL |       NULL |        NULL |          NULL | NULL    | NULL          |
|    5 | xm     | kkkkk  | xiaoming   | seller | ming@qq.com  | NULL |       NULL |        NULL |          NULL | NULL    | NULL          |
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+

  2.右外联结

  RIGHT  JOIN 或 RIGHT  OUTER  JOIN,右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。

user在左orders在右

select user.*,orders.* from user right join orders on user.u_id=orders.o_buyer_id;
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+
| u_id | u_name | u_pwd  | u_truename | u_role | u_email      | o_id | o_buyer_id | o_seller_id | o_totalprices | o_state | o_information |
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+
|    1 | z3     | aaaaa  | zhang3     | seller | zhang@qq.com |    1 |          1 |           1 |           2.5 | sale    | qianbi        |
|    2 | w5     | aaabbb | wang5      | buyer  | wang@163.com |    2 |          2 |           2 |           3.5 | sale    | xiangpi       |
|    3 | ll     | cccbbb | lili       | buyer  | li@163.com   |    3 |          3 |           3 |             3 | empty   | wenjuhe       |
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+

orders在左user在右

select user.*,orders.* from orders right join user on user.u_id=orders.o_buyer_id;
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+
| u_id | u_name | u_pwd  | u_truename | u_role | u_email      | o_id | o_buyer_id | o_seller_id | o_totalprices | o_state | o_information |
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+
|    1 | z3     | aaaaa  | zhang3     | seller | zhang@qq.com |    1 |          1 |           1 |           2.5 | sale    | qianbi        |
|    2 | w5     | aaabbb | wang5      | buyer  | wang@163.com |    2 |          2 |           2 |           3.5 | sale    | xiangpi       |
|    3 | ll     | cccbbb | lili       | buyer  | li@163.com   |    3 |          3 |           3 |             3 | empty   | wenjuhe       |
|    4 | xh     | adadad | xiaohong   | seller | hong@163.com | NULL |       NULL |        NULL |          NULL | NULL    | NULL          |
|    5 | xm     | kkkkk  | xiaoming   | seller | ming@qq.com  | NULL |       NULL |        NULL |          NULL | NULL    | NULL          |
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+

完全联结

  FULL  JOIN 或 FULL OUTER JOIN完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。mysql不支持

交叉连接 

  将两个表的所有行进行组合,连接后的行数为两个表的乘积数(笛卡尔积),相当于用两表联结时不用where加联结条件,FROM 表名1 CROSS JOIN 表名2

select user.*,orders.* from user cross join orders;
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+
| u_id | u_name | u_pwd  | u_truename | u_role | u_email      | o_id | o_buyer_id | o_seller_id | o_totalprices | o_state | o_information |
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+
|    1 | z3     | aaaaa  | zhang3     | seller | zhang@qq.com |    1 |          1 |           1 |           2.5 | sale    | qianbi        |
|    1 | z3     | aaaaa  | zhang3     | seller | zhang@qq.com |    2 |          2 |           2 |           3.5 | sale    | xiangpi       |
|    1 | z3     | aaaaa  | zhang3     | seller | zhang@qq.com |    3 |          3 |           3 |             3 | empty   | wenjuhe       |
|    2 | w5     | aaabbb | wang5      | buyer  | wang@163.com |    1 |          1 |           1 |           2.5 | sale    | qianbi        |
|    2 | w5     | aaabbb | wang5      | buyer  | wang@163.com |    2 |          2 |           2 |           3.5 | sale    | xiangpi       |
|    2 | w5     | aaabbb | wang5      | buyer  | wang@163.com |    3 |          3 |           3 |             3 | empty   | wenjuhe       |
|    3 | ll     | cccbbb | lili       | buyer  | li@163.com   |    1 |          1 |           1 |           2.5 | sale    | qianbi        |
|    3 | ll     | cccbbb | lili       | buyer  | li@163.com   |    2 |          2 |           2 |           3.5 | sale    | xiangpi       |
|    3 | ll     | cccbbb | lili       | buyer  | li@163.com   |    3 |          3 |           3 |             3 | empty   | wenjuhe       |
|    4 | xh     | adadad | xiaohong   | seller | hong@163.com |    1 |          1 |           1 |           2.5 | sale    | qianbi        |
|    4 | xh     | adadad | xiaohong   | seller | hong@163.com |    2 |          2 |           2 |           3.5 | sale    | xiangpi       |
|    4 | xh     | adadad | xiaohong   | seller | hong@163.com |    3 |          3 |           3 |             3 | empty   | wenjuhe       |
|    5 | xm     | kkkkk  | xiaoming   | seller | ming@qq.com  |    1 |          1 |           1 |           2.5 | sale    | qianbi        |
|    5 | xm     | kkkkk  | xiaoming   | seller | ming@qq.com  |    2 |          2 |           2 |           3.5 | sale    | xiangpi       |
|    5 | xm     | kkkkk  | xiaoming   | seller | ming@qq.com  |    3 |          3 |           3 |             3 | empty   | wenjuhe       |
+------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+

总结

  1. 查两表关联列相等的数据用内连接
  2. table1是table2的子集时用右外连接
  3. table1是table2的子集时用左外连接
  4. table1和table2彼此有交集但彼此互不为子集时候用全外
  5. 求差操作的时候用联合查询
  6. 多个表查询的时候,这些不同的连接类型可以写到一块

组合查询

  UNION---执行多个查询,也就是多条select语句,并将结果作为单个查询结果集返回,称为并或符合查询。

使用情况

  1. 单个查询中从不同的表返回类似结构的数据
  2. 单个表执行多个查询,按单个查询返回数据

使用规则

  1. 必须由两条及以上的select组成,语句间用UNION
  2. UNION中每个查询包含相同的列,表达式,或聚集函数
  3. 数据类型兼容,不必完全相同,DBMS可以隐士转换,如:不同日期类型或不同数值类型

总结

  1. UNION从查询结果集中自动除去了重复的行,这是默认的若果需要可以改变,用UNION ALL而不是UNION
  2. 只能使用一条order by,必须出现在最后一条select之后,不存在对结果集一部分排序一部分不排序