sql多表联查数据库诀窍,sql多表查询语句命令

2022.08.18 / 小杰

本节主要针对MySQL数据库的多表查询操作常用SQL命令进行总结,主要包括内连接(INNER JOIN),左外部连接(LEFT JOIN),右外部连接(RIGHT JOIN),全连接(FULL JOIN),交叉连接(CROSS JOIN),自然连接(NATURAL JOIN),自连接(SELF JOIN),联合查询(UNION)。

MySQL数据库的多表查询操作常用SQL命令

SQL JOIN

1.内连接-INNER JOIN

INNER JOIN 主要是获取两个或多张表的交集,只返回匹配的行;多张表之间没有主表附表之分。

//INNER JOIN = JOIN//规则1SELECT column_list FROM table1 t1 INNER JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.主键=t2.外键; //常用写法//规则2SELECT column_list FROM table1 t1 JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 JOIN table2 t2 ON t1.主键=t2.外键;//常用写法//规则3SELECT column_list FROM table1 t1 JOIN table2 t2 USING (相同列名);SELECT * FROM table1 t1 JOIN table2 t2 USING (id);//常用写法//规则4SELECT column_list FROM table1 t1, table2 t2 where t1.column_name=t2.column_name;SELECT * FROM table1 t1, table2 t2 where t1.主键=t2.外键;//常用写法//案例SELECT t1.*, t2.* FROM user t1 INNER JOIN role t2 ON t1.id = t2.uid;SELECT t1.*, t2.* FROM user t1 JOIN role t2 ON t1.id = t2.uid;SELECT t1.*, t2.* FROM users1 t1 JOIN users2 t2 USING (id);SELECT t1.*, t2.* FROM user t1, role t2 where t1.id = t2.uid;
MySQL数据库的多表查询操作常用SQL命令

SQL JOIN - INNER JOIN

2.左外部连接-LEFT JOIN

LEFT JOIN 以左表为主表,右表为附表;返回左表的全部行和右表满足ON条件的行;

如果左表的行在右表中没有匹配,那么这一行右表中对应数据用NULL代替;

可以是一主一附(一个左表,一个右表),也可以是一主多附(一个左表,多个右表)

//LEFT JOIN = LEFT OUTER JOIN//规则1SELECT column_list FROM table1 t1 LEFT JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.主键=t2.外键; //常用写法//规则2SELECT column_list FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.主键=t2.外键;//常用写法//案例SELECT t1.*, t2.* FROM user t1 LEFT JOIN role t2 ON t1.id = t2.uid;SELECT t1.*, t2.* FROM user t1 LEFT OUTER JOIN role t2 ON t1.id = t2.uid;//user 是主表,role和resource是附表SELECT t1.*, t2.*,t3.* FROM user t1  LEFT JOIN role t2 ON t1.id = t2.uid LEFT JOIN resource t3 ON t1.id = t3.uid;
MySQL数据库的多表查询操作常用SQL命令

SQL JOIN - LEFT JOIN

3.右外部连接-RIGHT JOIN

RIGHT JOIN 以右表为主表,右表为附表;返回右表的全部行和左表满足ON条件的行;

如果右表的行在左表中没有匹配,那么这一行左表中对应数据用NULL代替;

//RIGHT JOIN = RIGHT OUTER JOIN//规则1SELECT column_list FROM table1 t1 RIGHT JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.主键=t2.外键; //常用写法//规则2SELECT column_list FROM table1 t1 RIGHT OUTER JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 RIGHT OUTER JOIN table2 t2 ON t1.主键=t2.外键;//常用写法//案例SELECT t1.*, t2.* FROM user t1 RIGHT JOIN role t2 ON t1.id = t2.uid;SELECT t1.*, t2.* FROM user t1 RIGHT OUTER JOIN role t2 ON t1.id = t2.uid;
MySQL数据库的多表查询操作常用SQL命令

SQL JOIN - RIGHT JOIN

4.全连接-FULL JOIN

FULL JOIN 会从左表和右表返回符合条件的所有行;

只要左表(table1)和右表(table2)其中一个表中存在匹配,就返回结果;

如果其中一个表的数据行在另一个表中没有匹配的行,那么对面的数据用NULL代替;

FULL JOIN 结合了 LEFT JOIN 和 RIGHT JOIN 的结果;

SQLServer 和Oracle支持FULL JOIN;

MySQL不支持FULL JOIN,可以使用LEFT JOIN + UNION + RIGHT JOIN来达到效果

//FULL JOIN = FULL OUTER JOIN//规则1SELECT column_list FROM table1 t1 FULL JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 FULL JOIN table2 t2 ON t1.主键=t2.外键; //常用写法//规则2SELECT column_list FROM table1 t1 FULL OUTER JOIN table2 t2 ON t1.column_name=t2.column_name;SELECT * FROM table1 t1 FULL OUTER JOIN table2 t2 ON t1.主键=t2.外键;//常用写法//mysql中LEFT JOIN + UNION + RIGHT JOINSELECT column_list FROM table1 t1 LEFT JOIN table2 t2 ON t1.column_name=t2.column_nameUNIONSELECT column_list FROM table1 t1 RIGHT JOIN table2 t2 ON t1.column_name=t2.column_name;//案例  SQLServer / Oracle下使用SELECT t1.*, t2.* FROM user t1 FULL JOIN role t2 ON t1.id = t2.uid;SELECT t1.*, t2.* FROM user t1 FULL OUTER JOIN role t2 ON t1.id = t2.uid;//案例  MySQL下使用SELECT * FROM user t1 LEFT JOIN role t2 ON  t1.id = t2.uidUNIONSELECT * FROM user t1 RIGHT JOIN role t2 ON  t1.id = t2.uid;
MySQL数据库的多表查询操作常用SQL命令

SQL JOIN - FULL JOIN

5.交叉连接-CROSS JOIN

FULL JOIN 返回左表中的所有行,左表中的每一行与右表中的所有行组合;

交叉联接也称作笛卡尔积;

如果table1有5行记录,table2有6行记录,交叉连接结果为5*6=30条记录;

//规则1  table1和table2两表的笛卡尔积 = 两个表行数的乘积SELECT * FROM table1 CROSS JOIN table2;//规则2  首先生成两表的笛卡尔积,再根据ON条件来过滤SELECT * FROM table1 t1 CROSS JOIN table2 ON t1.column_name=t2.column_name;//规则3 首先生成两表的笛卡尔积,再根据WHERE条件来过滤SELECT * FROM table1 t1 CROSS JOIN table2 WHERE t1.column_name=t2.column_name;//案例SELECT * FROM user CROSS JOIN role;SELECT * FROM user t1 CROSS JOIN role ON t1.id=t2.uid;SELECT * FROM user t1 CROSS JOIN role WHERE t1.id=t2.uid;
MySQL数据库的多表查询操作常用SQL命令

SQL JOIN - CROSS JOIN

6.自然连接-NATURAL JOIN

NATURAL JOIN 等同于INNER JOIN或INNER USING 匹配两个表中相同名称的列;

具有相同名称的关联表的列将仅出现一次;

关联的表具有一对或多对相同名称的列;这些列必须是相同的数据类型;

不要在自然连接中使用ON子句;

//规则SELECT * FROM table1 NATURAL JOIN table2;//案例SELECT * FROM table1 NATURAL JOIN table2;

7.自连接-SELF JOIN

SELF JOIN 是表与自身联接(一元关系)的连接,特别是当表具有引用其自身主键的外键时;

连接表本身意味着表的每一行都与自身以及表的其他每一行结合在一起;

自连接可以看作是同一表的两个副本的连接;

//规则SELECT * FROM table t1, table t2 WHERE t1.column_name=t2.column_name;SELECT * FROM table t1 INNER JOIN table t2 ON t1.column_name=t2.column_name;//案例SELECT * FROM resource t1, resource t2 WHERE t1.id=t2.pid;SELECT * FROM resource t1 INNER JOIN resource t2 ON t1.id=t2.pid;

8.联合查询-UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集;

UNION 内部的每个 SELECT 语句必须拥有相同数量的列,相似的数据类型,相同的列顺序;

//规则1 UNION 操作符默认地选取不同的值SELECT column_list FROM table1 UNION SELECT column_list FROM table2;//规则2 UNION ALL允许选取重复的值SELECT column_list FROM table1 UNION ALL SELECT column_list FROM table2;//案例select id,name,pass,age from user1 union select id,name,pass,age from user2;select id,name,pass,age from user1 union all select id,name,pass,age from user2;

至此MySQL数据库的多表查询操作常用SQL命令总结完毕,编程就这么简单,小伙伴们不妨试试,总结不易,希望大家多点赞收藏转发,在此谢谢!

- END -

150

mysql简单面试题,mysql面试题及答案

篇幅所限本文只写了MySQL25题,像其他的Redis,SSM框架,算法,计网等技术栈的面试题后面会持续更新,个人整...

mysql处理百万条数据,mysql更新百万条数据

mysql 批量更新共有以下四种办法1、.replace into 批量更新 replace into test_tbl (id,dr) values (1,&#39...

mysql in参数可以有多少个,mysql in不走索引的情况

当前找工作,对于一定年限的软件开发者,都会被问到索引的相关问题,最近我发现对于mysql数据库中in关键字走...

linux常见面试题,linux面试官主要问的问题

如果你要去面试一个Linux系统运维工程师的职位,下面这十个最常见的问题一定要会,否则你的面试可能就危险了...

mysql 查询参数,mysql数据库参数配置优化

摘要:Mysql数据库的操作指令:1.启动mysql的命令:systemctl start mysqld.service2.重启mysql的命令:sys...