MYSQL表内连和外连(6)

学程序的羊

已于 2024-10-02 23:08:05 修改

阅读量45

点赞数 2

文章标签： mysql sql 学习数据库

于 2024-10-02 23:06:32 首次发布

本文链接：https://blog.csdn.net/weixin_72426758/article/details/141036983

版权

MYSQL表内连和外连

各期链接

内连接

语法

select 字段 from 表1 join 表2 on 连接条件 and 其他条件;

注:前面学习的都是内连接

案例

-- 之前的写法
select * from dept,emp where dept.deptno=emp.deptno;
-- 按照内连接的语法
select * from dept join emp on dept.deptno=emp.deptno;

mysql> -- 之前的写法
mysql> select * from dept,emp where dept.deptno=emp.deptno;
+--------+------------+----------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| deptno | dname      | loc      | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+------------+----------+--------+--------+-----------+------+---------------------+---------+---------+--------+
|     20 | RESEARCH   | DALLAS   | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
|     30 | SALES      | CHICAGO  | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|     30 | SALES      | CHICAGO  | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|     20 | RESEARCH   | DALLAS   | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|     30 | SALES      | CHICAGO  | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|     30 | SALES      | CHICAGO  | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
|     10 | ACCOUNTING | NEW YORK | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
|     20 | RESEARCH   | DALLAS   | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
|     10 | ACCOUNTING | NEW YORK | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|     30 | SALES      | CHICAGO  | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
|     20 | RESEARCH   | DALLAS   | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
|     30 | SALES      | CHICAGO  | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
|     20 | RESEARCH   | DALLAS   | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
|     10 | ACCOUNTING | NEW YORK | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+------------+----------+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> -- 按照内连接的语法
mysql> select * from dept join emp on dept.deptno=emp.deptno;
+--------+------------+----------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| deptno | dname      | loc      | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+------------+----------+--------+--------+-----------+------+---------------------+---------+---------+--------+
|     20 | RESEARCH   | DALLAS   | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
|     30 | SALES      | CHICAGO  | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|     30 | SALES      | CHICAGO  | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|     20 | RESEARCH   | DALLAS   | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|     30 | SALES      | CHICAGO  | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|     30 | SALES      | CHICAGO  | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
|     10 | ACCOUNTING | NEW YORK | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
|     20 | RESEARCH   | DALLAS   | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
|     10 | ACCOUNTING | NEW YORK | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|     30 | SALES      | CHICAGO  | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
|     20 | RESEARCH   | DALLAS   | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
|     30 | SALES      | CHICAGO  | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
|     20 | RESEARCH   | DALLAS   | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
|     10 | ACCOUNTING | NEW YORK | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+------------+----------+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

外连接

语法

-- 左外连接
select 字段 from 表1 left join 表2 on 连接条件 and 其他条件;
-- 右外连接
select 字段 from 表1 right join 表2 on 连接条件 and 其他条件;

左外连接会返回左表的所有内容
右外连接会返回右表的所有内容

案例

-- 创建学生表
create table stu(
id int primary key auto_increment,
name varchar(10)
);
-- 创建成绩表
create table exam(
id int primary key auto_increment,
grade int
);
-- 给两表插入数据
insert into stu values(1,'zhan'),(2,'li'),(3,'wang');
insert into exam values(1,80),(2,78),(4,67);

-- 左外连接
select stu.id,stu.name,exam.grade from stu left join exam on stu.id=exam.id;
-- 右外连接
select stu.id,stu.name,exam.grade from stu right join exam on stu.id=exam.id;

mysql> -- 左外连接
mysql> select stu.id,stu.name,exam.grade from stu left join exam on stu.id=exam.id;
+----+------+-------+
| id | name | grade |
+----+------+-------+
|  1 | zhan |    80 |
|  2 | li   |    78 |
|  3 | wang |  NULL |
+----+------+-------+
3 rows in set (0.03 sec)

mysql> -- 右外连接
mysql> select stu.id,stu.name,exam.grade from stu right join exam on stu.id=exam.id;
+------+------+-------+
| id   | name | grade |
+------+------+-------+
|    1 | zhan |    80 |
|    2 | li   |    78 |
| NULL | NULL |    67 |
+------+------+-------+
3 rows in set (0.00 sec)