![img](https://img-blog.csdnimg.cn/0048350e14a24a86842848cc843ace55.gif#pic_center)
MySQL内外连接
1、内连接
内连接实际上就是利用where子句对两种表形成的笛卡尔积进行筛选,我们前面学习的查询都是内连接,也是在开发过程中使用的最多的连接查询。
语法:
select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;
注意:前面学的都是内连接。
案例:显示SMITH的名字和部门名称
- 用前面的写法:
mysql> select ename,dname from EMP,DEPT where EMP.deptno=DEPT.deptno and ename='SMITH'; +-------+----------+ | ename | dname | +-------+----------+ | SMITH | RESEARCH | +-------+----------+ 1 row in set (0.00 sec) mysql>
- 用标准的内连接写法:
mysql> select ename,dname from EMP inner join DEPT on EMP.deptno=DEPT.deptno and ename='SMITH'; +-------+----------+ | ename | dname | +-------+----------+ | SMITH | RESEARCH | +-------+----------+ 1 row in set (0.00 sec) mysql>
2、外连接
2.1、左外连接
如果联合查询,左侧的表完全显示我们就说是左外连接。
语法:
select 字段名 from 表名1 left join 表名2 on 连接条件;
案例:
先建立两张表
create table stu (id int, name varchar(30)); -- 学生表 create table exam (id int, grade int); -- 成绩表
插入数据
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono'); insert into exam values(1, 56),(2,76),(11, 8);
- 查询所有学生的成绩,如果这个学生没有成绩,也要将学生的个人信息显示出来:
mysql> select * from stu left join exam on stu.id=exam.id; +------+------+------+-------+ | id | name | id | grade | +------+------+------+-------+ | 1 | jack | 1 | 56 | | 2 | tom | 2 | 76 | | 3 | kity | NULL | NULL | | 4 | nono | NULL | NULL | +------+------+------+-------+ 4 rows in set (0.00 sec) mysql>
2.2、右外连接
如果联合查询,右侧的表完全显示我们就说是右外连接。
语法:
select 字段 from 表名1 right join 表名2 on 连接条件;
案例:
- 对stu表和exam表联合查询,把所有的成绩都显示出来,即使这个成绩没有学生与它对应,也要显示出来:
mysql> select * from stu right join exam on stu.id=exam.id; +------+------+------+-------+ | id | name | id | grade | +------+------+------+-------+ | 1 | jack | 1 | 56 | | 2 | tom | 2 | 76 | | NULL | NULL | 11 | 8 | +------+------+------+-------+ 3 rows in set (0.00 sec) mysql>
列出部门名称和这些部门的员工信息,同时列出没有员工的部门:
使用左外连接:
mysql> select d.dname,e.* from DEPT d left join EMP e on d.deptno=e.deptno; +------------+-------+--------+-----------+------+------------+---------+--------+--------+ | dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +------------+-------+--------+-----------+------+------------+---------+--------+--------+ | RESEARCH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | SALES | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | SALES | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | RESEARCH | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | SALES | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | ACCOUNTING | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | RESEARCH | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 | | ACCOUNTING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | SALES | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | RESEARCH | 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 | | SALES | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | RESEARCH | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | ACCOUNTING | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | | OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------------+-------+--------+-----------+------+------------+---------+--------+--------+ 14 rows in set (0.00 sec) mysql>
- 使用右外连接:
mysql> select d.dname,e.* from EMP e right join DEPT d on d.deptno=e.deptno; +------------+-------+--------+-----------+------+------------+---------+--------+--------+ | dname | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +------------+-------+--------+-----------+------+------------+---------+--------+--------+ | RESEARCH | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | SALES | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | SALES | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | RESEARCH | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | SALES | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | ACCOUNTING | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | RESEARCH | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 | | ACCOUNTING | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | SALES | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | RESEARCH | 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 | | SALES | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | RESEARCH | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | ACCOUNTING | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | | OPERATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------------+-------+--------+-----------+------+------------+---------+--------+--------+ 14 rows in set (0.00 sec) mysql>
OKOK,MySQL内外连接就到这里,如果你对Linux和C++也感兴趣的话,可以看看我的主页哦。下面是我的github主页,里面记录了我的学习代码和leetcode的一些题的题解,有兴趣的可以看看。