1.去重
示例表内容参考此文章
有些 MySQL 数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时候我们也需要删除这些重复的数据。
例如：去重显示岗位信息：
mysql> select distinct job from emp; +-----------+ | job | +-----------+ | CLERK | | SALESMAN | | MANAGER | | ANALYST | | PRESIDENT | +-----------+ 5 rows in set (0.02 sec)
另一个示例：联合去重，查找部门和岗位的独有信息：
mysql> select distinct job,deptno from emp; +-----------+--------+ | job | deptno | +-----------+--------+ | CLERK | 20 | | SALESMAN | 30 | | MANAGER | 20 | | MANAGER | 30 | | MANAGER | 10 | | ANALYST | 20 | | PRESIDENT | 10 | | CLERK | 30 | | CLERK | 10 | +-----------+--------+ 9 rows in set (0.00 sec)
另一个示例：现在我们想统计一下工作岗位的数量，结合使用count函数：
mysql> select count(distinct job) from emp; +---------------------+ | count(distinct job) | +---------------------+ | 5 | +---------------------+ 1 row in set (0.00 sec)
2.连接查询
我们已经学会了如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。
JOIN 按照功能大致分为如下三类：
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
多表连接的机制是：从其中一个表中取出每一条数据，从另一个表中的数据行进行匹配🎈这就涉及到了效率控制问题
使用where进行多表连接查询
现在我们来演示一个例子：取出每个员工的名字和部门名字：
mysql> select ename,dname -> from emp,dept -> where emp.deptno = dept.deptno; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | RESEARCH | | ALLEN | SALES | | WARD | SALES | | JONES | RESEARCH | | MARTIN | SALES | | BLAKE | SALES | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | SALES | | ADAMS | RESEARCH | | JAMES | SALES | | FORD | RESEARCH | | MILLER | ACCOUNTING | +--------+------------+ 14 rows in set (0.00 sec)
上面的sql语句实际上效率很低，我们尝试进行优化（给表起别名）：（sql92语法）
mysql> select e.ename,d.dname -> from emp e,dept d -> where e.deptno = d.deptno; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | RESEARCH | | ALLEN | SALES | | WARD | SALES | | JONES | RESEARCH | | MARTIN | SALES | | BLAKE | SALES | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | SALES | | ADAMS | RESEARCH | | JAMES | SALES | | FORD | RESEARCH | | MILLER | ACCOUNTING | +--------+------------+ 14 rows in set (0.00 sec)
注意：表的连接次数越多，效率越低，请尽量减少表的连接次数！❌
内连接 - 等值连接
还是上面的例子，取出每个员工的名字和部门名字：（sql99语法）
内连接，我们使用inner
mysql> select e.ename,d.dname -> from emp e -> inner join -> dept d -> on -> e.deptno = d.deptno; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | RESEARCH | | ALLEN | SALES | | WARD | SALES | | JONES | RESEARCH | | MARTIN | SALES | | BLAKE | SALES | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | SALES | | ADAMS | RESEARCH | | JAMES | SALES | | FORD | RESEARCH | | MILLER | ACCOUNTING | +--------+------------+ 14 rows in set (0.00 sec)
sql99的优点是：表的连接是独立的，不占用where的位置。使sql语句整体更加清晰😀
内连接 - 非等值连接
案例：找出每个员工的薪资等级，要求显示员工名，薪资，薪资等级
mysql> select -> e.ename,e.sal,s.grade -> from -> emp e -> inner join -> salgrade s -> on -> e.sal between s.losal and s.hisal; +--------+---------+-------+ | ename | sal | grade | +--------+---------+-------+ | SMITH | 800.00 | 1 | | ALLEN | 1600.00 | 3 | | WARD | 1250.00 | 2 | | JONES | 2975.00 | 4 | | MARTIN | 1250.00 | 2 | | BLAKE | 2850.00 | 4 | | CLARK | 2450.00 | 4 | | SCOTT | 3000.00 | 4 | | KING | 5000.00 | 5 | | TURNER | 1500.00 | 3 | | ADAMS | 1100.00 | 1 | | JAMES | 950.00 | 1 | | FORD | 3000.00 | 4 | | MILLER | 1300.00 | 2 | +--------+---------+-------+ 14 rows in set (0.01 sec)
内连接 - 自连接
案例：查询员工的上级领导，要求显示员工名和对应的领导名
我们可以发现，员工和领导的关系在一张表中，此时需要用到自连接（技巧：一张表看成两张表）
mysql> select -> a.ename as '员工名',b.ename as '领导名' -> from emp a -> join emp b -> on -> a.mgr = b.empno; +-----------+-----------+ | 员工名 | 领导名 | +-----------+-----------+ | SMITH | FORD | | ALLEN | BLAKE | | WARD | BLAKE | | JONES | KING | | MARTIN | BLAKE | | BLAKE | KING | | CLARK | KING | | SCOTT | JONES | | TURNER | BLAKE | | ADAMS | SCOTT | | JAMES | BLAKE | | FORD | JONES | | MILLER | CLARK | +-----------+-----------+ 13 rows in set (0.00 sec)
外连接 - 左右外连接👈👉
外连接与内连接的区别是，外连接没有匹配成功的某一个表的记录也会被取出
案例：查找员工的部门信息。要求部门即使没有员工也要查出
mysql> select -> e.ename,d.dname -> from emp e -> right join dept d -> on -> e.deptno = d.deptno; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | RESEARCH | | ALLEN | SALES | | WARD | SALES | | JONES | RESEARCH | | MARTIN | SALES | | BLAKE | SALES | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | SALES | | ADAMS | RESEARCH | | JAMES | SALES | | FORD | RESEARCH | | MILLER | ACCOUNTING | | NULL | OPERATIONS | +--------+------------+ 15 rows in set (0.00 sec)
同样的，如果是左外连接，将查询出左表的全部数据，使用left join关键字即可
外连接的查询结果条数一定是 >= 内连接的查询结果条数🧨
三表连接
更为复杂的情况是，群表连接🔗
我们来看一个案例：
找出每个员工的部门名称及工资等级。要求显示员工名，部门名，薪资，薪资等级
mysql> select -> e.ename,e.sal,d.dname,s.grade -> from emp e -> join dept d -> on e.deptno = d.deptno -> join salgrade s -> on e.sal between s.losal and s.hisal; +--------+---------+------------+-------+ | ename | sal | dname | grade | +--------+---------+------------+-------+ | SMITH | 800.00 | RESEARCH | 1 | | ALLEN | 1600.00 | SALES | 3 | | WARD | 1250.00 | SALES | 2 | | JONES | 2975.00 | RESEARCH | 4 | | MARTIN | 1250.00 | SALES | 2 | | BLAKE | 2850.00 | SALES | 4 | | CLARK | 2450.00 | ACCOUNTING | 4 | | SCOTT | 3000.00 | RESEARCH | 4 | | KING | 5000.00 | ACCOUNTING | 5 | | TURNER | 1500.00 | SALES | 3 | | ADAMS | 1100.00 | RESEARCH | 1 | | JAMES | 950.00 | SALES | 1 | | FORD | 3000.00 | RESEARCH | 4 | | MILLER | 1300.00 | ACCOUNTING | 2 | +--------+---------+------------+-------+ 14 rows in set (0.00 sec)
再来看一个更复杂的情况：
找出每个员工的部门名称及工资等级及领导名称。要求显示员工名，部门名，领导名，薪资，薪资等级
mysql> select -> e.ename,e.sal,d.dname,s.grade,l.ename -> from emp e -> join dept d -> on e.deptno = d.deptno -> join salgrade s -> on e.sal between s.losal and s.hisal -> left join -> emp l -> on e.mgr = l.empno; +--------+---------+------------+-------+-------+ | ename | sal | dname | grade | ename | +--------+---------+------------+-------+-------+ | SMITH | 800.00 | RESEARCH | 1 | FORD | | ALLEN | 1600.00 | SALES | 3 | BLAKE | | WARD | 1250.00 | SALES | 2 | BLAKE | | JONES | 2975.00 | RESEARCH | 4 | KING | | MARTIN | 1250.00 | SALES | 2 | BLAKE | | BLAKE | 2850.00 | SALES | 4 | KING | | CLARK | 2450.00 | ACCOUNTING | 4 | KING | | SCOTT | 3000.00 | RESEARCH | 4 | JONES | | KING | 5000.00 | ACCOUNTING | 5 | NULL | | TURNER | 1500.00 | SALES | 3 | BLAKE | | ADAMS | 1100.00 | RESEARCH | 1 | SCOTT | | JAMES | 950.00 | SALES | 1 | BLAKE | | FORD | 3000.00 | RESEARCH | 4 | JONES | | MILLER | 1300.00 | ACCOUNTING | 2 | CLARK | +--------+---------+------------+-------+-------+ 14 rows in set (0.00 sec)
