等值连接+外连接+内连接
程序员文章站
2024-02-27 17:25:09
...
广义笛卡尔积–>进行条件筛选->等值连接
广义笛卡尔积将两张或多张表格 进行无条件的拼接
在拼接后的一张大表中的基础上进行了where的筛选
没有任何关系的两张表格都可以进行拼接
##用作演示的两张表格
mysql> select * from emp ;##emp 14行 8 列
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredata | sal | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)
##dept表 4行 3 列
mysql> select *from dept ;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
##s使用广义笛卡尔积 连接
#竖着的列数是两个表格列数的和
#横着的行数是两个表格行数的的乘积
#现在的两张表格是列数是56行11列
#这样直接拼接的 广义笛卡尔积 可以和两张完全不同的表格进行拼接
#语句 select 要显示的列 表名1，表名2 ;
mysql> select *from emp,dept ;
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
| empno | ename | job | mgr | hiredata | sal | comm | deptno | deptno | dname | loc |
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+————————
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | 20 | RESEARCH | DALLAS | emp表中的一行数据与dept表的三行数据 匹配一次 以此类推
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | 30 | SALES | CHICAGO |————————
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 30 | SALES | CHICAGO |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 40 | OPERATIONS | BOSTON |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 20 | RESEARCH | DALLAS |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 40 | OPERATIONS | BOSTON |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 30 | SALES | CHICAGO |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 40 | OPERATIONS | BOSTON |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 10 | ACCOUNTING | NEW YORK |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 20 | RESEARCH | DALLAS |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 40 | OPERATIONS | BOSTON |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 30 | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 40 | OPERATIONS | BOSTON |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 | 30 | SALES | CHICAGO |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | 30 | SALES | CHICAGO |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | 40 | OPERATIONS | BOSTON |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 20 | RESEARCH | DALLAS |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 40 | OPERATIONS | BOSTON |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 10 | ACCOUNTING | NEW YORK |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 30 | SALES | CHICAGO |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 40 | OPERATIONS | BOSTON |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 30 | SALES | CHICAGO |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 | 30 | SALES | CHICAGO |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
56 rows in set (0.01 sec)
```sql
#等值连接
#在广义笛卡尔积连接的后面 使用where 条件
#列还是一样没有发生改变 但是行数是按照 满足条件的数据显示的
#语句 select 要显示的列 from 表格1,表格2 where 条件 ##也可以加上排序
mysql> select * from emp,dept where emp.deptno = dept.deptno ;
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
| empno | ename | job | mgr | hiredata | sal | comm | deptno | deptno | dname | loc | #一个人的信息值匹配一个部门的信息
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 30 | SALES | CHICAGO |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 30 | SALES | CHICAGO |
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
14 rows in set (0.03 sec)
#外连接 注意 在外连接中 条件关键字只能使用 on 条件
#语句 seelct 要显示的列 from 表1 left\right outer join 表2 on 条件 ###outer可以不写
#注意 不写on 语句不好用 报错
#表格1在左边显示表1的数据 后显示表格2的数据 那个表格在前先显示按个表格的数据
mysql> select * from emp left outer join dept on emp.deptno = dept.deptno ;
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
| empno | ename | job | mgr | hiredata | sal | comm | deptno | deptno | dname | loc | ##刚刚新增的语句
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 30 | SALES | CHICAGO |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 30 | SALES | CHICAGO |
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
14 rows in set (0.01 sec)
## left 和right 是用来控制哪一格表格的数据作为基准的
作为基准的表格数据会全部显示出来
非基准的表格按照on条件与之拼接
若找到条件拼接 则正常显示 若找不到满足条件的则null ;
#向emP表格中新增一条语句
mysql> insert into emp(empno,ename) values(1111,'AAA');
Query OK, 1 row affected (0.00 sec)
mysql> select * from emp ;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredata | sal | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 1111 | AAA | NULL | NULL | NULL | NULL | NULL | NULL |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
15 rows in set (0.00 sec)
##执行刚刚一样的语句 emp表是在前面的 我们刚刚新增的语句 条件不满足 显示出来 后面的值null
##dept 表中的40 部门没有条件满足的就不显示出来
mysql> select * from emp left outer join dept on emp.deptno = dept.deptno ;
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
| empno | ename | job | mgr | hiredata | sal | comm | deptno | deptno | dname | loc |
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 30 | SALES | CHICAGO |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 30 | SALES | CHICAGO |
| 1111 | AAA | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
15 rows in set (0.00 sec)
##将 emp表格和dept表格的为位置换一下 dept在前面 dept的40 部门显示出来了 后面的值还是null 的
#emp的1111 不显示出来
mysql> select * from dept left outer join emp on emp.deptno = dept.deptno ;
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
| deptno | dname | loc | empno | ename | job | mgr | hiredata | sal | comm | deptno |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
| 10 | ACCOUNTING | NEW YORK | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
| 20 | RESEARCH | DALLAS | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 30 | SALES | CHICAGO | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 30 | SALES | CHICAGO | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 30 | SALES | CHICAGO | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 30 | SALES | CHICAGO | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 30 | SALES | CHICAGO | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 30 | SALES | CHICAGO | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 40 | OPERATIONS | BOSTON | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+--------+------------+----------+-------+--------+-----------+------+------------+------+------+--------+
15 rows in set (0.00 sec)
#内连接
#这两张表格来演示
mysql> select * from salgrade ;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
mysql> select * from emp ;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredata | sal | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 1111 | AAA | NULL | NULL | NULL | NULL | NULL | NULL |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
15 rows in set (0.00 sec)
#内连接 与等值链接 类似 内连接在性能上面比等值连接要高
#语句 selest 要显示的列 from 表格1 inner join 表格2 on 条件
mysql> select * from emp as e inner join salgrade as s on e.sal between s.LOSAL and s.HISAL ;
+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
| empno | ename | job | mgr | hiredata | sal | comm | deptno | GRADE | LOSAL | HISAL |
+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | 1 | 700 | 1200 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 3 | 1401 | 2000 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 2 | 1201 | 1400 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 4 | 2001 | 3000 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 2 | 1201 | 1400 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 4 | 2001 | 3000 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 4 | 2001 | 3000 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 | 4 | 2001 | 3000 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | 5 | 3001 | 9999 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 3 | 1401 | 2000 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 | 1 | 700 | 1200 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 1 | 700 | 1200 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 4 | 2001 | 3000 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 | 2 | 1201 | 1400 |
+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
14 rows in set (0.00 sec)
#在当前表格中再次查询当前表格的信息
#如当员工的上级是谁
mysql> select e1.empno,e1.ename,e2.mgr,e2.ename from emp as e1 inner join emp as e2 on e1.mgr = e2.empno ;
+-------+--------+------+-------+
| empno | ename | mgr | ename |
+-------+--------+------+-------+
| 7369 | SMITH | 7566 | FORD |
| 7499 | ALLEN | 7839 | BLAKE |
| 7521 | WARD | 7839 | BLAKE |
| 7566 | JONES | NULL | KING |
| 7654 | MARTIN | 7839 | BLAKE |
| 7698 | BLAKE | NULL | KING |
| 7782 | CLARK | NULL | KING |
| 7788 | SCOTT | 7839 | JONES |
| 7844 | TURNER | 7839 | BLAKE |
| 7876 | ADAMS | 7566 | SCOTT |
| 7900 | JAMES | 7839 | BLAKE |
| 7902 | FORD | 7839 | JONES |
| 7934 | MILLER | 7839 | CLARK |
+-------+--------+------+-------+
13 rows in set (0.00 sec)