SQL语句常⽤命令整理——多表查询
多表查询之关连查询
多表数据连接查询，简称连接查询。本篇我们来⼀同学习多表连接查询的相关⽤法，主要內容有：
1. 内连接查询
2. 外连接查詢
3. 查询结果合并
4. ⼦查询
⾸先第⼀张表还是我们之前⽤到t_employee，我们在另外新建⼀个表t_dept（部门表）建表命令如下：
drop table if exists t_dept;
create table t_dept(
t_id int primary key,
deptno int(11),
dname varchar(20),
loc varchar(50));
插⼊数据：openstack门户可以绕过keystone
insert into t_dept
(t_id,deptno,dname,loc)
values
('1','10','ACCOUNTING','NEW YORK'),
('2','20','RESEARCH','DALLAS'),
('3','30','SALES','CHICAGO'),
('4','40','OPERATIONS','BOSTON');
笛卡尔积
select * from t_employee,t_dept;
结果如下：
empno ename job mgr hiredate sal comm deptno t_id deptno dname loc
7369SMITH CLERK79022011-03-12800null20110ACCOUNTING NEW YORK 7369SMITH CLERK79022011-03-12800null20220RESEARCH DALLAS 7369SMITH CLERK79022011-03-12800null20330SALES CHICAGO 7369SMITH CLERK79022011-03-12800null20440OPERATIONS BOSTON 7499ALLEN SALESMAN76982012-03-12160030030110ACCOUNTING NEW YORK 7499ALLEN SALESMAN76982012-03-12160030030220RESEARCH DALLAS 7499ALLEN SALESMAN76982012-03-12160030030330SALES CHICAGO 7499ALLEN SALESMAN76982012-03-12160030030440OPERATIONS BOSTON
从结果可以看出，两张表中的每条记录都进⾏了重组，导致最终查询结果的条数为两张表中的记录数的乘积，这样的结果积在数学上称为笛卡尔积。简单⽐喻下就是所有可能的组合都进⾏了组合。
内连接（INNER JOIN）
使⽤⽐较运算符（包括=、>、<、<>、>=、<=、!>和!<）进⾏表间的⽐较操作，查询与连接条件相匹配的数据。
无符号数的原码反码补码特殊：⾃连接：查询每个员⼯的姓名、职位和领导姓名
⽅法1：
手机网页游戏大全网站
ame,t1.ame
from
t_employee t1,
t_employee t2
where
< = t2.empno;
⽅法2：
ame,t1.ame
from t_employee t1
inner join t_employee t2 = t2.empno;
查询每个员⼯的编号、姓名、职位、部门名称和部门所在位置
ame,t1.job,t2.dname,t2.loc
from t_employee t1
inner join t_dept t2 on t1. deptno = t2.deptno;
---------------------------------- or ---------------------------------------------
select  t1.ame,t1.job,t2.dname,t2.loc
from t_employee t1,t_dept t2
where t1.deptno = t2.deptno;
查询每个员⼯的编号、姓名、基本⼯资、职位、领导姓名、部门名称和部门所在位置
ame,t1.sal,t1.ame as mgr_name,t3.dname,t3.loc
from t_employee t1
inner join t_employee t2 = t2.empno
inner join t_dept t3 on t1.deptno = t3.deptno;
---------------------------------- or ---------------------------------------------
ame,t1.sal,t1.ame as mgr_name,t3.dname,t3.loc
from t_employee t1,t_employee t2,t_dept t3
= t2.empno and t1.deptno = t3.deptno;
外连接
外连接分为左连接（LEFT JOIN）或左外连接（LEFT OUTER JOIN）、右连接（RIGHT JOIN）或右外连接（RIGHT OUTER JOIN）、全连接（FULL JOIN）或全外连接（FULL OUTER JOIN）。我们就简单的叫：左连接、右连接和全连接。
左外连接(left join)
返回左表中的所有⾏，如果左表中⾏在右表中没有匹配⾏，则结果中右表中的列返回空值。
查询每个员⼯的姓名、职位和领导姓名
ame,t1.ame as mgr_name
from t_employee t1
left join t_employee t2 = t2.empno;
结果：
ename job mgr_name
SCOTT ANALYST JONES
KING PRESIDENT null
可以看到查询结果的第⼆⾏，t2表⽆对应匹配⾏，直接显⽰为null，t1表所有值均显⽰完全。专业⼀点说，即 左外连接 = 内连接 + 左边表内连接之后剩余的其他记录，右边补NULL。
右外连接(right join)
恰与左连接相反，返回右表中的所有⾏，如果右表中⾏在左表中没有匹配⾏，则结果中左表中的列返回空值。
右外连接：查询每个员⼯的编号、姓名、职位和部门名称和部门位置
ame,t1.job,t2.dname,t2.loc
from t_employee t1
right join t_dept t2 on t1.deptno = t2.deptno;
结果：
empno ename job dname loc
7900JAMES CLERK SALES CHICAGO
null null null OPERATIONS BOSTON
全连接(full join)
返回左表和右表中的所有⾏。当某⾏在另⼀表中没有匹配⾏，则另⼀表中的列返回空值
这⾥为了测试效果，插⼊⼀个不在在部门表搜索不到的员⼯记录。如下：
transformer copyinsert into t_employee
values
('7935','TEST','CLERK','7782','2011-03-12','1300',NULL,'50');
查询每个员⼯的编号、姓名、职位和部门名称和部门位置
ame,t1.job,t2.dname,t2.loc
from t_employee t1
full join t_dept t2 on t1.deptno = t2.deptno;
.....运⾏了⼀下发现MySQL并不⽀持全连接，但是可以通过其他途径实现全连接的查询结果。接下来我们学习的合并查询记录就可以实现我们想要的结果。
合并查询数据记录
合并查询数据使⽤UNION或UNION ALL，思考⼀下全连接 = 左连接 UNION 右连接，赶快来试试
ame,t1.job,t2.dname,t2.loc
from t_employee t1
left join t_dept t2 on t1.deptno = t2.deptno
UNION
ame,t1.job,t2.dname,t2.loc
from t_employee t1
right join t_dept t2 on t1.deptno = t2.deptno;
结果：
empno ename job dname loc
7935TEST CLERK NULL NULL
NULL NULL NULL OPERATIONS BOSTON 看下查询结果，即是全连接的效果了。满⾜了，我们再来看看UNION和UNION ALL的区别：
ame,t1.job,t2.dname,t2.loc
from t_employee t1
left join t_dept t2 on t1.deptno = t2.deptno
UNION ALL
ame,t1.job,t2.dname,t2.loc
from t_employee t1
right join t_dept t2 on t1.deptno = t2.deptno;
⼤家⾃⾏看查询结果吧，区别还是很显⽽易见的，即：
UNION执⾏了去重的操作，⽽UNION ALL并没有去重。
⼦查询
⼦查询说通俗⼀点，即是嵌套查询。看⼀下查询语句，⼤家⼀下就明⽩了。
查询⼯资⽐SMITH还要⾼的全部员⼯的信息
select *
from t_employee t
where t.sal > (
select sal
from t_employee
where ename = 'SMITH');
是不是已经明⽩了，即是在上⼀次查询结果的基础上再做⼀次查询。再练习⼀下。
查询⼯资和职位都与WARD⼀样，并且不是WARD本⼈的员⼯的信息
SELECT
*
FROM
t_employee
WHERE
(sal, job) = (
SELECT
sal,job
FROM
t_employee
WHERE
ename = 'WARD'
)
AND ename != 'WARD';
查询员⼯表中的员⼯信息，要求员⼯的编号都在部门表中有记录
SELECT
*
FROM
t_employee
WHERE
deptno IN (SELECT deptno FROM t_dept);
查询员⼯表中的员⼯信息，要求员⼯的编号不在部门表中有记录
select *
from t_employee
where deptno NOT IN (select deptno from t_dept);
以上就是整理常见查询命令的使⽤⽅法，这⾥最后给⼤家留⼀道⾯试遇到的⼀道SQL题。题⽬如下：mysql语句多表查询
_id name value
1a1
2b2
3a1
4b2
5b2
执⾏⼀条SQL语句，删除表内的重复⾏。如下结果(*表⽰值不限定)：
_id name value
while循环列表操作
*a1
*b2
⼤家可能会想到的解决办法如下：
delete from t_test
where `_id` NOT IN (
select min(id)
from t_test
group by `name`,`value`
having count(*) > 1);
但是这样会报这样⼀个错误You can't specify target table 't_test' for update in FROM clause，查了Google这是因为MySQL不⽀持select 语句与delete、update语句同时出现在同⼀个表中。在⽹上到了解决办法，⾮常巧妙的解决～将语句改成如下：
DELETE FROM
t_test
WHERE
`_id` NOT IN (
select MIN(_id)
from (SELECT * from t_test) t
GROUP BY t.name,t.value
HAVING count(*) > 1
);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。