联结查询
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之后,不存在对结果集一部分排序一部分不排序