mysql-210712-01
-
去除重复记录(distinct)
-
连接查询
- 笛卡尔积现象
- 内连接–等值连接
- 内连接–非等值连接
- 内连接–自连接
- 外连接
- 全连接
去除重复记录(distinct)
案例1
// 关键字 distinct
mysql> select distinct job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
// 两个字段联合去重 job deptno
mysql> select distinct job,deptno from emp order by deptno;
+-----------+--------+
| job | deptno |
+-----------+--------+
| CLERK | 10 |
| MANAGER | 10 |
| PRESIDENT | 10 |
| ANALYST | 20 |
| CLERK | 20 |
| MANAGER | 20 |
| CLERK | 30 |
| MANAGER | 30 |
| SALESMAN | 30 |
+-----------+--------+
mysql> select ename, distinct job from emp; // 错误
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct job from emp' at line 1
mysql>
distinct 关键字只能出现在字段的最前方
案例2
// 统计岗位的数量
mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
| 5 |
+---------------------+
连接查询
什么是连接查询
在实际开发中,大部分分情况下都不是从单表(一张表)中查询数据,一般都是多张表联合查询取出最终的结果。
在实际开发中,一般一个业务都会对应多张表
如果所有信息都存在一张表,数据会存在大量重复,导致数据冗余
连接查询分类
1. 根据语法出现年代划分
SQL92 (一些老的DBA还在使用。DBA DataBase Administrator ,数据库管理员)
SQL99 (新的)
2. 根据连接方式划分
1)内连接:
等值连接
非等值连接
自连接
2)外连接:
左外连接(左连接)
右外连接(右连接)
3)全连接
笛卡尔积现象
笛卡尔积现象(笛卡尔乘积现象)
当两张表进行连接查询的时候,没有任何条件进行限制,
最终查询结果条数是两张表条数的乘积。
给表起别名
select e.ename , d.dname from emp e,dept d;
起别名好处:
1)执行效率高
2)可读性好
如何避免笛卡尔积现象???
加条件进行过滤
避免了笛卡尔积现象不会减少记录的匹配次数,,,它还是56次,
只不过显示的是有效记录。
案例1
// 找出每个员工的部门名称,要求显示员工名和部门名
// 出现笛卡尔积现象
// 得出56条记录 是两张表数量的乘积
mysql> select ename,dname from emp,dept;
// 给表起别名
mysql> select e.ename , d.dname from emp e,dept d;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
| SMITH | OPERATIONS |
| ALLEN | ACCOUNTING |
| ALLEN | RESEARCH |
| ...... |
| ...... |
| ...... |
| MILLER | ACCOUNTING |
| MILLER | RESEARCH |
| MILLER | SALES |
| MILLER | OPERATIONS |
+--------+------------+
56 rows in set (0.00 sec)
// 解决 笛卡尔积现象
// 这个是 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)
内连接—等值连接
特点:
条件是等量关系。
案例1(join…on…)
join...on... SQL99语法:
select
...
from
表A
inner join
表B
on
连接条件
where
...
inner可以省略,带上去更加明了一些,一看就知道是内连接。
SQL99语法更清晰一些,表的连接条件和后来的where的条件分离了。
// 查询每个员工的部门名称,要求显示员工名和部门名
// SQL92
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno = d.deptno;
// 查询每个员工的部门名称,要求显示员工名和部门名
// SQL99
// join...on...
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
内连接—非等值连接
特点:
连接条件中的关系是非等量关系
案例1
// 找出每个员工的工资等级,要求显示员工名,工资,工资等级
mysql> select
-> e.ename,e.sal,s.grade
-> from
-> emp e
-> join
-> salgrade s
-> on
-> e.sal between s.losal and hisal
-> order by s.grade asc;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| WARD | 1250.00 | 2 |
| MARTIN | 1250.00 | 2 |
| MILLER | 1300.00 | 2 |
| ALLEN | 1600.00 | 3 |
| TURNER | 1500.00 | 3 |
| JONES | 2975.00 | 4 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| FORD | 3000.00 | 4 |
| KING | 5000.00 | 5 |
+--------+---------+-------+
14 rows in set (0.00 sec)
内连接—自连接
特点:
一张表看做两张表,自己连接自己。
案例1
// 找出每个员工的上级领导,要求显示员工名和对应的领导名
// 丢了KING这条记录
// 只有13条记录
mysql> select
-> yuangong.ename as '员工',yuangong.mgr as '员工领导'
-> from
-> emp yuangong
-> join
-> emp lingdao
-> on
-> yuangong.mgr = lingdao.empno;
+--------+--------------+
| 员工 | 员工领导 |
+--------+--------------+
| SMITH | 7902 |
| ALLEN | 7698 |
| WARD | 7698 |
| JONES | 7839 |
| MARTIN | 7698 |
| BLAKE | 7839 |
| CLARK | 7839 |
| SCOTT | 7566 |
| TURNER | 7698 |
| ADAMS | 7788 |
| JAMES | 7698 |
| FORD | 7566 |
| MILLER | 7782 |
+--------+--------------+
13 rows in set (0.00 sec)
外连接
什么是外连接,和内连接的区别???
内连接:
假设A和B表进行连接,使用内连接的话,凡是A表和B表能匹配上的记录查询出来
这就是内连接
A,B表没有主副之分,两张表平等
外连接:
假设A和B表进行连接,使用外连接的话,
A,B两张表中有一张是主表,一张是副表,
主要查询主表中的数据,捎带着查询副表,
当副表上的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配。
外连接特点
特点:
主表的数据无条件的全部查询出来
外连接分类
左外连接(左连接):
表示左边这张表是主表
右外连接(右连接)
表示右边这张表是主表
左连接有右连接的写法,右连接也会有对应的左连接的写法。
外连接语法
select
...
from
表1
left/right outer join
表2
on
连接条件
outer 可以省略,,,只需要靠 left、right 就可以区分外连接和内连接了,
加上outer可读性较强。
案例1(左连接)
// 找出每个员工的上级领导
// 左边是主表,即emp yuangong表,无论如何也要查到
// outer 可以省略
mysql> select
-> yuangong.ename '员工',lingdao.ename '领导'
-> from
-> emp yuangong
-> left outer join
-> emp lingdao
-> on
-> yuangong.mgr = lingdao.empno;
+--------+--------+
| 员工 | 领导 |
+--------+--------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+--------+
14 rows in set (0.00 sec)
案例2(右连接)
// 找出每个员工的上级领导
// 右边是主表,即emp yuangong表,无论如何也要查到
// outer 可以省略
mysql> select
-> yuangong.ename '员工',lingdao.ename '领导'
-> from
-> emp lingdao
-> right join
-> emp yuangong
-> on
-> yuangong.mgr = lingdao.empno;
+--------+--------+
| 员工 | 领导 |
+--------+--------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+--------+
14 rows in set (0.00 sec)
案例3
// 找出那个部门没有员工
mysql> select
-> e.*,d.*
-> from
-> dept d
-> left outer join
-> emp e
-> on
-> d.deptno = e.deptno
-> where
-> e.deptno is null;
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
1 row in set (0.00 sec)
mysql> select
-> d.*
-> from
-> dept d
-> left outer join
-> emp e
-> on
-> d.deptno = e.deptno
-> where
-> e.deptno is null;
+--------+------------+--------+
| DEPTNO | DNAME | LOC |
+--------+------------+--------+
| 40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.00 sec)