使用mysql语句进行内连接

使用mysql语句进行内连接

1 由来

因为我们不可能会把所有数据都放在一个表里头,因为这样不仅检索效率低下,而且阅读性不好

我们通常会把所有数据分别存放在不同的表里头,表与表之间通过外键连接

那么此时我们想要获取到多个表的数据,就必须使用多表联查的技术了

这里我们先和大家讲解一下内连接(多表联查技术的一种)的几种用法

2 作用

获取多个表中想要的字段信息(会剔除含有null的字段)

3 如何用

3.1 等值连接

3.1.1 本质

就是拿两表中共有的相同字段作为连接条件,把两个表的信息关联起来,然后获取两个表中想要的字段

3.1.2 语法

SELECT 别名1.字段1...别名1.字段n,别名2.字段2...别名2.字段n
FROM 表1 别名1
INNER JOIN
表2 别名2
ON 别名1.共有字段=别名2.共有字段;
/*
通常是把表名的第一个字母作为表的别名来使用
*/

3.1.3 示例sql语句

SELECT e.ename,d.dname
FROM emp e
INNER JOIN
dept d
ON e.deptno=d.deptno;
/*
查询每个员工所对应的部门名字
需要查询的字段有: 员工名(emp表),部门名称(dept表)
*/

3.1.4 分析过程

emp表

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17	800.00	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-02-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-04-02	2975.00	NULL	20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850.00	NULL	30
7782	CLARK	MANAGER	7839	1981-06-09	2450.00	NULL	10
7788	SCOTT	ANALYST	7566	1987-04-19	3000.00	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000.00	NULL	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-05-23	1100.00	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950.00	NULL	30
7902	FORD	ANALYST	7566	1981-12-03	3000.00	NULL	20
7934	MILLER	CLERK	7782	1982-01-23	1300.00	NULL	10

dept表

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

具体分析过程(只讲解2个,后面的依此类推)

第一条数据分析

当读取到emp表的第一条数据(员工姓名是SMITH)时,它的部门编号为30,此时部门表的对应的信息为

20 RESEARCH DALLAS,我们会把SMITH这个员工的所有信息和对应部门信息拼接成一条数据

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	DEPTNO(dept)	DNAME	LOC
7369	SMITH	CLERK	7902	1980-12-17	800.00	NULL	20	20	RESEARCH	DALLAS

第二条数据分析

当读取到emp表的第二条数据(员工姓名是ALLEN)时,它的部门编号为20,此时部门表的对应的信息为

30 SALES CHICAGO,我们会把ALLEN这个员工的所有信息和对应部门信息拼接成一条数据

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	DEPTNO(dept)	DNAME	LOC
7499	ALLEN	SALESMAN	7698	1981-02-20	1600.00	300.00	30	30	SALES	CHICAGO

后面的数据匹配就按照前面所讲的两条数据来分析就行了,是一样的逻辑的

最后再根据需求展示想要展示的字段就行

3.1.5 示例sql语句运行截图

3.2 非等值连接

3.1.1 本质

两表之间的连接是通过一个表的一个字段在另外一个表的两个字段的范围内查找,然后再返回一个结果(这个结果是这个表的另外一个字段),这就是非等值连接

通俗的来说,只要不是通过两表之间的公共字段相等的这种方式进行内连接的话,那么该种方式就是非等值连接.

3.1.2 语法

SELECT 别名1.字段1,别名1.字段2,别名2.字段1
FROM 表1 别名1
INNER JOIN
表2 别名2
ON 别名1.字段2 BETWEEN 别名2.字段2 AND 别名2.字段3;

3.1.3 示例sql语句

SELECT ename,sal,grade
FROM emp e
INNER JOIN
salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;
/*
查找员工薪水对应的工资等级
需要查询的字段有 员工姓名(emp)、薪水(emp)、工资等级(salgrade)
因为s.hisal是数值型,所以其也能取到,如果是字符串的话就取不到了
*/

3.1.4 分析过程

a 核心

拿着员工表(emp)里面的工资去工资等级表(salgrade)去进行逐行匹配,若在区间内,就返回该行的grade字段的值

b 具体分析过程

b.1 emp表

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17	800.00	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-02-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-04-02	2975.00	NULL	20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850.00	NULL	30
7782	CLARK	MANAGER	7839	1981-06-09	2450.00	NULL	10
7788	SCOTT	ANALYST	7566	1987-04-19	3000.00	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000.00	NULL	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-05-23	1100.00	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950.00	NULL	30
7902	FORD	ANALYST	7566	1981-12-03	3000.00	NULL	20
7934	MILLER	CLERK	7782	1982-01-23	1300.00	NULL	10

b.2 salgrade表

GRADE	LOSAL	HISAL
1	700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3001	9999

b.3 当读到emp表的第一行数据(SMITH)的sal工资为800,就会去salgrade工资等级表里面去找对应的等级

发现800是大于工资登记表的第一行的losal(700),就会再与hisal(1200)比较,发现此时符合losal<800<hisal的条件,那么就返回grade字段的值也就是1出去

b.4 当读到emp表的第二行数据(ALLEN)的sal工资为800,就会去salgrade工资等级表里面去找对应的等级

b.4.1 发现1600是大于工资登记表的第一行的losal(700),就会再与hisal(1200)比较,发现大于hisal,此时不符合条件

b.4.2 就会继续往工资等级表的第二行进行比较,此时losal=1201<1600,hisal=1400<1600,那么此时依然是不符合条件,

b.4.3 那么就会继续往工资等级表的第三行进行比较,发现此时losal=1401<1600<=hisal=1600,因此就会返回第三行的等级grade字段的值出去

b.5 emp表后面的数据行的逐行匹配是和b.3、b.4的规律是一样的,就只要依次类推就行,这里就不赘述了

b.6 然后把得到的结果与名字、工资相匹配展示出来,就能得到我们想要的结果了

3.1.5 示例sql语句运行截图

3.3 自连接

3.1.1 本质

就是把一张表作为两张表来使用,取了两个别名,就可以当成两个不同的表的了,只是说操作的数据是相同的罢了

3.1.2 语法

SELECT 别名1.字段1,别名2.字段1
FROM 表1 别名1
INNER JOIN
表2 别名2
ON 别名1.字段2 运算符(通常是=) 别名2.字段2;
/*
这个SELECT子句中的两个字段可以是相同的字段,不同别名的表的相同字段实际上还是不同的
*/

3.1.3 示例sql语句

SELECT e.ename,m.ename
FROM emp e
INNER JOIN
emp m
ON e.mgr=m.empno;
/*
别名为m的表就可以看成是领导表,是专门通过员工表的mgr字段去匹配相应的领导的
内连接要求一定要匹配成功,匹配不成功的数据就会丢弃掉
*/

3.1.4 分析过程

第一步 emp 表别名为e------->作为员工表

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17	800.00	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-02-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-04-02	2975.00	NULL	20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850.00	NULL	30
7782	CLARK	MANAGER	7839	1981-06-09	2450.00	NULL	10
7788	SCOTT	ANALYST	7566	1987-04-19	3000.00	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000.00	NULL	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-05-23	1100.00	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950.00	NULL	30
7902	FORD	ANALYST	7566	1981-12-03	3000.00	NULL	20
7934	MILLER	CLERK	7782	1982-01-23	1300.00	NULL	10

第二步 emp 表,别名为m----->作为领导表

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17	800.00	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-02-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-04-02	2975.00	NULL	20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850.00	NULL	30
7782	CLARK	MANAGER	7839	1981-06-09	2450.00	NULL	10
7788	SCOTT	ANALYST	7566	1987-04-19	3000.00	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000.00	NULL	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-05-23	1100.00	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950.00	NULL	30
7902	FORD	ANALYST	7566	1981-12-03	3000.00	NULL	20
7934	MILLER	CLERK	7782	1982-01-23	1300.00	NULL	10

第三步 拿e表中的第一条数据(SMITH)mgr字段的值和m表的中的每一行的empno字段进行内容的比较

mgr=7902,m表第一行的empno=7369 不符合,继续往下找

mgr=7902,m表第二行的empno=7499 不符合,继续往下找

mgr=7902,m表第三行的empno=7521 不符合,继续往下找

mgr=7902,m表第四行的empno=7566 不符合,继续往下找

mgr=7902,m表第五行的empno=7654 不符合,继续往下找

mgr=7902,m表第六行的empno=7698 不符合,继续往下找

mgr=7902,m表第七行的empno=7782 不符合,继续往下找

mgr=7902,m表第八行的empno=7788 不符合,继续往下找

mgr=7902,m表第九行的empno=7839 不符合,继续往下找

mgr=7902,m表第十行的empno=7844 不符合,继续往下找

mgr=7902,m表第十一行的empno=7876 不符合,继续往下找

mgr=7902,m表第十二行的empno=7900 不符合,继续往下找

mgr=7902,m表第十三行的empno=7902 符合条件,那么就会把e表中的ename和m表中的ename值作为一个整体放入结果集中

第四步 后面进行逐行匹配的逻辑与第三步一致,就依次类推就行了,不用再赘述了

第五步 最后再把所有的结果集显示出来即可

注意:(每次进行mgr字段和empno字段值的比较时,不相等就会继续往下找,若到达m表中的最后一行都没有符合条件,那么该行数据会丢失)

3.1.5 示例sql语句运行截图