MySQL联结查询和组合查询
联结查询
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 | +------+--------+--------+------------+--------+--------------+------+------------+-------------+---------------+---------+---------------+
总结
- 查两表关联列相等的数据用内连接
- table1是table2的子集时用右外连接
- table1是table2的子集时用左外连接
- table1和table2彼此有交集但彼此互不为子集时候用全外
- 求差操作的时候用联合查询
- 多个表查询的时候，这些不同的连接类型可以写到一块
组合查询
UNION---执行多个查询，也就是多条select语句，并将结果作为单个查询结果集返回，称为并或符合查询。
使用情况
- 单个查询中从不同的表返回类似结构的数据
- 单个表执行多个查询，按单个查询返回数据
使用规则
- 必须由两条及以上的select组成，语句间用UNION
- UNION中每个查询包含相同的列，表达式，或聚集函数
- 数据类型兼容，不必完全相同，DBMS可以隐士转换，如：不同日期类型或不同数值类型
总结
- UNION从查询结果集中自动除去了重复的行，这是默认的若果需要可以改变，用UNION ALL而不是UNION
- 只能使用一条order by，必须出现在最后一条select之后，不存在对结果集一部分排序一部分不排序
