开始时间:2021-03-08
查询结果去重
distinct
mysql> select * from 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.01 sec)
mysql> select distinct job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
distinct只能出现在所有字段的最前面
所以下面代码会报错
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> select distinct ename,job from emp;
+--------+-----------+
| ename | job |
+--------+-----------+
| SMITH | CLERK |
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| SCOTT | ANALYST |
| KING | PRESIDENT |
| TURNER | SALESMAN |
| ADAMS | CLERK |
| JAMES | CLERK |
| FORD | ANALYST |
| MILLER | CLERK |
+--------+-----------+
14 rows in set (0.00 sec)
mysql> select distinct job,ename from emp;
+-----------+--------+
| job | ename |
+-----------+--------+
| CLERK | SMITH |
| SALESMAN | ALLEN |
| SALESMAN | WARD |
| MANAGER | JONES |
| SALESMAN | MARTIN |
| MANAGER | BLAKE |
| MANAGER | CLARK |
| ANALYST | SCOTT |
| PRESIDENT | KING |
| SALESMAN | TURNER |
| CLERK | ADAMS |
| CLERK | JAMES |
| ANALYST | FORD |
| CLERK | MILLER |
+-----------+--------+
14 rows in set (0.00 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 |
+-----------+--------+
连接查询
在实际开发中,大部分的情况下都不是从单表中查询数据,一般都是多张表联合查询取出最终的结果。
在实际开发中,一般一个业务都会对应多张表,比如:学生和班级,起码两张表
根据表的连接方式来划分,包括:
- 内连接:
等值连接
非等值连接
自连接 - 外连接:
左外连接(左连接)
右外连接(右连接)
全连接(这个不讲,很少用!)
内连接:
假设A和B表进行连接,使用内连接的话,凡是A表和s表能够匹配上的记录查询出来,匹配不出来的就不显示,这就是内连接。AB两张表没有主副之分,两张表是平等的。
外连接:
假设x和s表进行连接,使用外连接的话,AB两张表中有一张表是主表,一张表是副表,主要查询主表中的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出Null与之匹配。
笛卡尔积现象
笛卡尔积现象:当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表记录条数的乘积。
例如emp表
mysql> select * from 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
要查询每个员工的姓名,以及部门,还有部门对应的编号
mysql> select ename,dname from emp,dept;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
| SMITH | OPERATIONS |
| ALLEN | ACCOUNTING |
| ALLEN | RESEARCH |
| ALLEN | SALES |
| ALLEN | OPERATIONS |
| WARD | ACCOUNTING |
| WARD | RESEARCH |
| WARD | SALES |
| WARD | OPERATIONS |
| JONES | ACCOUNTING |
| JONES | RESEARCH |
| JONES | SALES |
| JONES | OPERATIONS |
| MARTIN | ACCOUNTING |
| MARTIN | RESEARCH |
| MARTIN | SALES |
| MARTIN | OPERATIONS |
| BLAKE | ACCOUNTING |
| BLAKE | RESEARCH |
| BLAKE | SALES |
| BLAKE | OPERATIONS |
| CLARK | ACCOUNTING |
| CLARK | RESEARCH |
| CLARK | SALES |
| CLARK | OPERATIONS |
| SCOTT | ACCOUNTING |
| SCOTT | RESEARCH |
| SCOTT | SALES |
| SCOTT | OPERATIONS |
| KING | ACCOUNTING |
| KING | RESEARCH |
| KING | SALES |
| KING | OPERATIONS |
| TURNER | ACCOUNTING |
| TURNER | RESEARCH |
| TURNER | SALES |
| TURNER | OPERATIONS |
| ADAMS | ACCOUNTING |
| ADAMS | RESEARCH |
| ADAMS | SALES |
| ADAMS | OPERATIONS |
| JAMES | ACCOUNTING |
| JAMES | RESEARCH |
| JAMES | SALES |
| JAMES | OPERATIONS |
| FORD | ACCOUNTING |
| FORD | RESEARCH |
| FORD | SALES |
| FORD | OPERATIONS |
| MILLER | ACCOUNTING |
| MILLER | RESEARCH |
| MILLER | SALES |
| MILLER | OPERATIONS |
+--------+------------+
56 rows in set (0.00 sec)
当没有用where进行条件限制时,得到的结果条数56=14*4
即把A表每一条和B表的每一条都会匹配
给表起别名,可读性好,执行效率高,上面的语句可以做一点修改
mysql> select ename,dname from emp,dept;
//二者等价
mysql> select e.ename,d.dname from emp e,dept d;
我们的目标是两个表中取相同的部分,因此要加上where限制
匹配次数还是没变,不能提高效率,但是显示记录会进行过滤,
mysql> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;//但这种方式过时了,属于sql92
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+------------+
内连接之等值连接
改写为sql99的写法
语法格式:
...
A
inner join//inner可以省略
B
on
连接条件
where
...
mysql> select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+------------+
用99语法的好处在于,可以再添加一个where进行过滤
内连接-非等值连接
连接关系为非等量关系
查询emp表中员工工资对应salgrade表中各工资的等级
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 |
+--------+---------+-------+
内连接之自连接
一张表看做两张表,自己连接自己。
找出每个员工的直系领导名
mysql> select * from 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
如上表所示,MGR的编号对应着boss的编号
这时候可以把一张表命名为两张表,再来定义连接
mysql> select e.ename,m.ename as boss from emp e join emp m on m.empno=e.mgr;
+--------+-------+
| ename | boss |
+--------+-------+
| 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 |
+--------+-------+
外连接
自连接改外连接
匹配不上就用null
mysql> select e.ename,m.ename as boss from emp e left join emp m on m.empno=e.mgr;
//在join旁边加上left outer(outer可以省),表示左连接,即把左表定义为主表
+--------+-------+
| ename | boss |
+--------+-------+
| 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 |
+--------+-------+
mysql> select e.ename,m.ename as boss from emp m right join emp e on m.empno=e.mgr;
+--------+-------+
| ename | boss |
+--------+-------+
| 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 |
+--------+-------+
查出哪个部门没有员工
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
部门40是没有员工的,查出来
mysql> select d.* from dept d left join emp e on d.deptno =e.deptno where e.deptno is null;
//将部门表设为主表,条件是两表的部门号相等,再加上过滤掉部门号不为空的,保留为空的
mysql> select d.* from dept d left join emp e on d.deptno =e.deptno where e.deptno is null;
+--------+------------+--------+
| DEPTNO | DNAME | LOC |
+--------+------------+--------+
| 40 | OPERATIONS | BOSTON |
+--------+------------+--------+
三张表以上的连接查询
案例:找出每一个员工的部门名称及工资等级
单独进行两两匹配
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
连接在一起
即
select XX
from XX
join XX
on XX=XX
join XX
on XX=XX
select e.ename,e.sal,s.grade,d.dname from emp e
join salgrade s on e.sal between s.losal and s.hisal
join dept d on e.deptno=d.deptno;
+--------+---------+-------+------------+
| ename | sal | grade | dname |
+--------+---------+-------+------------+
| SMITH | 800.00 | 1 | RESEARCH |
| ALLEN | 1600.00 | 3 | SALES |
| WARD | 1250.00 | 2 | SALES |
| JONES | 2975.00 | 4 | RESEARCH |
| MARTIN | 1250.00 | 2 | SALES |
| BLAKE | 2850.00 | 4 | SALES |
| CLARK | 2450.00 | 4 | ACCOUNTING |
| SCOTT | 3000.00 | 4 | RESEARCH |
| KING | 5000.00 | 5 | ACCOUNTING |
| TURNER | 1500.00 | 3 | SALES |
| ADAMS | 1100.00 | 1 | RESEARCH |
| JAMES | 950.00 | 1 | SALES |
| FORD | 3000.00 | 4 | RESEARCH |
| MILLER | 1300.00 | 2 | ACCOUNTING |
+--------+---------+-------+------------+
案例:找出每一个员工的部门名称,工资等级以及上级领导
找领导
mysql> select e.ename,m.ename as boss from emp e join emp m on m.empno=e.mgr;
这里有个坑,之前查询不加外连接,顶多是不显示这个king的记录,这里会直接报错
所以一定要用外连接
select e.ename,e.sal,s.grade,d.dname,m.ename as boss from emp e
join salgrade s on e.sal between s.losal and s.hisal
join dept d on e.deptno=d.deptno
left join emp m on m.empno=e.mgr;//left不能少
+--------+---------+-------+------------+-------+
| ename | sal | grade | dname | boss |
+--------+---------+-------+------------+-------+
| SMITH | 800.00 | 1 | RESEARCH | FORD |
| ALLEN | 1600.00 | 3 | SALES | BLAKE |
| WARD | 1250.00 | 2 | SALES | BLAKE |
| JONES | 2975.00 | 4 | RESEARCH | KING |
| MARTIN | 1250.00 | 2 | SALES | BLAKE |
| BLAKE | 2850.00 | 4 | SALES | KING |
| CLARK | 2450.00 | 4 | ACCOUNTING | KING |
| SCOTT | 3000.00 | 4 | RESEARCH | JONES |
| KING | 5000.00 | 5 | ACCOUNTING | NULL |
| TURNER | 1500.00 | 3 | SALES | BLAKE |
| ADAMS | 1100.00 | 1 | RESEARCH | SCOTT |
| JAMES | 950.00 | 1 | SALES | BLAKE |
| FORD | 3000.00 | 4 | RESEARCH | JONES |
| MILLER | 1300.00 | 2 | ACCOUNTING | CLARK |
+--------+---------+-------+------------+-------+
结束时间:2021-03-10