1、把查询结果去除重复记录
注意:原表数据不会修改,只是查询结果去重
去重需要使用的关键字——distinct
mysql> select distinct job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)
//这样编写是有语法错误的;distinct只能出现在所有字段最前方
mysql> select ename, distinct job from emp;
//distinct出现在job,deptno两个字段之前,表示两个字段联合起来去重
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)
统计一下工作岗位的数量
mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
| 5 |
+---------------------+
1 row in set (0.01 sec)
2、连接查询
2.1、什么是连接查询
从一张表中单独查询,称为单独查询
emp表和dept表联合起来查询数据,从emp表中取员工名字,从dept表中取部门名字。这种跨表查询,多张表联合起来查询数据,被称为连接查询。
2.2、连接查询的分类
根据语法的年代分类:
SQL92:1992年的时候出现的语法
SQL99:1992年的时候出现的语法
根据表连接的方式分类:
内连接: 等值连接
非等值连接
自连接
外连接:左外连接(左连接)
右外连接(右连接)
全连接:
2.3、当两张表进行连接查询时,没有任何条件的限制会发生什么现象?
案例:查询每个员工所在部门名称
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 |
+--------+--------+
14 rows in set (0.00 sec)
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.00 sec)
//两张表连接没有任何限制:
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