1、关于查询结果集的去重?
distinct 关键字去除重复记录。
select distinct job from emp;
mysql> select distinct job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)
注意:distinct关键字必须出现在所有字段的最前方。
distinct出现在最前方表示,后面的所有字段联合起来去重,不是只单独去重
案例:统计岗位的数量。
select count(distinct job) from emp;
mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
| 5 |
+---------------------+
1 row in set (0.00 sec)
2、连接查询
2.1 什么是连接查询:
实际开发中,大部分情况不是从单张表来查询数据
一般都是多张表联合查询取出最终的结果。比如:学生和班级
当数据都存入同一张表中时,因为很多的数据都是相同的,会导致数据存在大量的冗余。
2.2 连接查询分分类
根据语法出现的年代来划分,包括:SQL92,SQL99
根据表的连接方式来划分,包括:
内连接,
等值连接
非等值连接
自连接
外连接,
左外连接(左连接)
右外连接(右连接)
全连接(不常用)
2.3 笛卡尔积现象 (笛卡尔乘积现象)
案例:找出每一个员工的部门名称,奥球显示员工名和部门名
/*查询EMP表中的员工姓名与部门编号信息*/
mysql> select ename,deptno from emp;
+--------+--------+
| ename | deptno |
+--------+--------+
| SMITH | 20 |
| ALLEN | 30 |
| WARD | 30 |
| JONES | 20 |
| MARTIN | 30 |
| BLAKE | 30 |
| CLARK | 10 |
| SCOTT | 20 |
| KING | 10 |
| TURNER | 30 |
| ADAMS | 20 |
| JAMES | 30 |
| FORD | 20 |
| MILLER | 10 |
+--------+--------+
/*查询DEPT表中的数据*/
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.06 sec)
/*不做约束,对两个属性进行查找*/
select ename,dname from emp,dept;
mysql> select ename,dname from emp,dept;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
/*....(两两组合) ,一共是emp中14条数据 * demp表中4条数据 变成总共的56条数据*/
56 rows in set (0.00 sec)
笛卡尔积现象:当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表记录条数的乘积。
关于表的别名:
select e.ename, d.dname from emp e, dept d;
表的别名的好处:
1.执行效率高 (这样就对于字段,不需要去多张表去查找)
2.可读性好
2.4 如何避免笛卡尔积现象
加条件进行过滤。
**注意:**避免笛卡尔积现象,不会减少记录的匹配次数,但是会显示有效记录。
案例:找出每一个员工的部门名称,要求显示员工名和部门名
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno; //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 |
+--------+------------+
2.5、内连接中的等值连接
内连接中的等值连接:最大的特点是 条件是等量关系
案例:查询每个员工的部门名称,要求显示员工名称和部门名称
SQL99:(常用的)
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
mysql> select e.ename,d.dname from emp e 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)
/***语法**:
...
A 表1
inner join (inner可以省略)
B 表2
on
连接条件
where
...
*/
SQL99语法结构更加清晰一点,表连接和where条件分开。
2.6、内连接中的非等值连接
内连接中的非等值连接:最大的特点是 条件是非等量关系
案例:找出每个员工的工资等级,要求显示员工名、工资、工资等级。
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
mysql> select e.ename,e.sal,s.grade from emp e 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 |
+--------+---------+-------+
2.7、内连接中的自连接
内连接中的自连接:最大的特点是 一张表看做两张表,自己连接自己。
案例:找出每个员工的上级领导,要求显示员工名和对应的领导名。
select e1.empno,e1.ename,e1.mgr,e2.ename as ‘上司’ from emp e1 join emp e2 on e1.mgr = e2.empno;
mysql> select e1.empno,e1.ename,e1.mgr,e2.ename as '上司' from emp e1 join emp e2 on e1.mgr = e2.empno;
+-------+--------+------+-------+
| empno | ename | mgr | 上司 |
+-------+--------+------+-------+
| 7369 | SMITH | 7902 | FORD |
| 7499 | ALLEN | 7698 | BLAKE |
| 7521 | WARD | 7698 | BLAKE |
| 7566 | JONES | 7839 | KING |
| 7654 | MARTIN | 7698 | BLAKE |
| 7698 | BLAKE | 7839 | KING |
| 7782 | CLARK | 7839 | KING |
| 7788 | SCOTT | 7566 | JONES |
| 7844 | TURNER | 7698 | BLAKE |
| 7876 | ADAMS | 7788 | SCOTT |
| 7900 | JAMES | 7698 | BLAKE |
| 7902 | FORD | 7566 | JONES |
| 7934 | MILLER | 7782 | CLARK |
+-------+--------+------+-------+
13 rows in set (0.00 sec)
2.8、外连接(以后用的多)
什么是外连接,和内连接的区别?
内连接:
假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内 连接,AB两张表没有主副之分,两张表示平等的。
外连接
假设A和B表进行连接,使用外连接的话,AB两张表中有一张是主表,一张是副表,主要查询主 表中的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表会自动模拟 出NULL与之匹配。
外连接的分类?
左外连接(左连接),表示左边的这张表是主表。 left
右外连接(右连接),表示右边的这张表是主表。
左连接有右连接的写法,右连接也会有对应的左连接的写法。
案例:找出每个员工的上级领导
select e1.empno,e1.ename,e1.mgr,e2.ename '上司' from emp e1 left join emp e2 on e1.mgr = e2.empno;
左外连接 | 右外连接 |
---|---|
select | select |
a.ename ‘员工’,b.ename ‘领导’ | a.ename ‘员工’,b.ename ‘领导’ |
from | from |
emp a | emp b |
left join | right join |
emp b | emp a |
on | on |
a.mgr = b.empno; | a.mgr = b.empno; |
外连接中,其实缺省了 outer,完全写法为 left/right outer join。 outer可以省略。
案例:找出那个部门没有员工
select a.*,d.* from dept d left join emp a on a.deptno = d.deptno;
select d.* from dept d left join emp a on a.deptno = d.deptno where a.deptno is null;
mysql> select d.* from dept d left join emp a on a.deptno = d.deptno where a.deptno is null;
+--------+------------+--------+
| DEPTNO | DNAME | LOC |
+--------+------------+--------+
| 40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.00 sec)
2.9、三张表以上连接查询
案例:找出每一个员工的部门名称以及工资等级。
/*...
A
join
B
jion
c
on
... 表示:a表先和b表进行表连接,连接之后继续和c表进行表连接
*/
mysql> select e.ename '姓名', d.dname '部门名称' ,e.sal '工资' ,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;
+--------+------------+---------+----------+
| 姓名 | 部门名称 | 工资 | 工资等级 |
+--------+------------+---------+----------+
| SMITH | RESEARCH | 800.00 | 1 |
| ALLEN | SALES | 1600.00 | 3 |
| WARD | SALES | 1250.00 | 2 |
| JONES | RESEARCH | 2975.00 | 4 |
| MARTIN | SALES | 1250.00 | 2 |
| BLAKE | SALES | 2850.00 | 4 |
| CLARK | ACCOUNTING | 2450.00 | 4 |
| SCOTT | RESEARCH | 3000.00 | 4 |
| KING | ACCOUNTING | 5000.00 | 5 |
| TURNER | SALES | 1500.00 | 3 |
| ADAMS | RESEARCH | 1100.00 | 1 |
| JAMES | SALES | 950.00 | 1 |
| FORD | RESEARCH | 3000.00 | 4 |
| MILLER | ACCOUNTING | 1300.00 | 2 |
+--------+------------+---------+----------+
14 rows in set (0.00 sec)
案例:找出每一个员工的部门名称,工资等级,以及上级领导。
select e1.ename '员工姓名',d.dname '部门名称',s.grade '工资等级',e2.ename '上级领导'
from emp e1 left join emp e2
on e1.mgr = e2.empno
join dept d
on e1.deptno = d.deptno
join salgrade s
on e1.sal between s.losal and s.hisal;
+----------+------------+----------+----------+
| 员工姓名 | 部门名称 | 工资等级 | 上级领导 |
+----------+------------+----------+----------+
| SMITH | RESEARCH | 1 | FORD |
| ALLEN | SALES | 3 | BLAKE |
| WARD | SALES | 2 | BLAKE |
| JONES | RESEARCH | 4 | KING |
| MARTIN | SALES | 2 | BLAKE |
| BLAKE | SALES | 4 | KING |
| CLARK | ACCOUNTING | 4 | KING |
| SCOTT | RESEARCH | 4 | JONES |
| KING | ACCOUNTING | 5 | NULL |
| TURNER | SALES | 3 | BLAKE |
| ADAMS | RESEARCH | 1 | SCOTT |
| JAMES | SALES | 1 | BLAKE |
| FORD | RESEARCH | 4 | JONES |
| MILLER | ACCOUNTING | 2 | CLARK |
+----------+------------+----------+----------+
14 rows in set (0.00 sec)