mysql-210712-01

关注

原创最新推荐文章于 2022-11-21 21:12:15 发布 113 阅读 AI 写同款· GEO 优化 ›

mysql-210712-01

去除重复记录（distinct）
连接查询
1. 笛卡尔积现象
2. 内连接–等值连接
3. 内连接–非等值连接
4. 内连接–自连接
5. 外连接
6. 全连接

去除重复记录（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)