内外连接

# 内连接

SELECT ... FROM t1 INNER JOIN t2 ON 连接条件 [INNER JOIN t3 ON 连接条件] ... AND 其他条件;

显示 SMITH 的名字和部门名称

方式 1

mysql> select emp.ename, dept.dname from emp, dept where emp.deptno=dept.deptno and emp.ename='SMITH';
+-------+----------+
| ename | dname    |
+-------+----------+
| SMITH | RESEARCH |
+-------+----------+

1
2
3
4
5
6

方式 2

mysql> select emp.ename, dept.dname from emp inner join dept on emp.deptno=dept.deptno and emp.ename='SMITH';
+-------+----------+
| ename | dname    |
+-------+----------+
| SMITH | RESEARCH |
+-------+----------+

1
2
3
4
5
6

# 外连接

# 左外连接

左外连接的条件通过连接条件指明，用户的其他筛选条件通过其他条件指明:

SELECT ... FROM t1 LEFT JOIN t2 ON 连接条件 [LEFT JOIN t3 ON 连接条件] ... AND 其他条件;

# 右外连接

右外连接的条件通过连接条件指明，用户的其他筛选条件通过其他条件指明：

SELECT ... FROM t1 RIGHT JOIN t2 ON 连接条件 [RIGHT JOIN t3 ON 连接条件] ... AND 其他条件;

列出部门名称和这些部门的员工信息，同时列出没有员工的部门

mysql> select dept.deptno, emp.ename, emp.empno, dept.dname from dept left join emp on dept.deptno=emp.deptno order by dept.deptno as
sc;
+--------+--------+--------+------------+
| deptno | ename  | empno  | dname      |
+--------+--------+--------+------------+
|     10 | MILLER | 007934 | ACCOUNTING |
|     10 | KING   | 007839 | ACCOUNTING |
|     10 | CLARK  | 007782 | ACCOUNTING |
|     20 | FORD   | 007902 | RESEARCH   |
|     20 | ADAMS  | 007876 | RESEARCH   |
|     20 | SCOTT  | 007788 | RESEARCH   |
|     20 | JONES  | 007566 | RESEARCH   |
|     20 | SMITH  | 007369 | RESEARCH   |
|     30 | JAMES  | 007900 | SALES      |
|     30 | TURNER | 007844 | SALES      |
|     30 | BLAKE  | 007698 | SALES      |
|     30 | MARTIN | 007654 | SALES      |
|     30 | WARD   | 007521 | SALES      |
|     30 | ALLEN  | 007499 | SALES      |
|     40 | NULL   |   NULL | OPERATIONS |
+--------+--------+--------+------------+
15 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 视图

视图是一个虚拟表，其内容由查询定义，视图中的数据并不会单独存储在数据库中，其数据来自定义视图时查询所引用的表（基表），在每次引用视图时动态生成。由于视图和基表用的本质是同一份数据，因此对视图的修改会影响到基表，对基表的修改也会影响到视图。

# 创建视图

create view view_name as select...;

mysql> create view myview as select dept.deptno,dname,ename,empno,comm from dept,emp where emp.deptno=dept.deptno;
Query OK, 0 rows affected (0.14 sec)

mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| myview          |
| salgrade        |
+-----------------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

# 删除视图

drop view view_name;

上次更新: 2025/11/11, 22:03:54

← 复合查询索引特性→