文章目录
第2章 数据查询语言DDL
DQL:数据查询语言:select、from、where
2.1 基础查询
(1)数据的导入
source 路径\文件名.sql
(2)查看表的结构
desc 表名;
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int(4) | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int(4) | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int(2) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
(3)简单查询DQL
1、查询一个字段
select 字段名 from 表名;
2、查询多个字段:使用逗号隔开
select name1,name2 from table_name;
3、查询所有字段
## 方法一:把每个字段写上,逗号隔开
select a,b,c,d,e from tablename;
## 方法二:
select * from tablename;
方法二缺点:效率低,可读性差;实际开发中不建议使用,后面的学习为了方便用这个
(4)给查询的列起别名
## 方式一
select 字段名 as 字段别名 from 表名;
## 方式二:省略as
select 字段名 字段别名 from 表名;
例:要是起别名有空格,或者别名是中文
mysql> select deptno,dname 'dept name' from dept;
+--------+------------+
| deptno | dept name |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)
注意:在所有的数据库当中,字符串统一使用单引号括起来。
案例:计算员工年薪
1.查员工名字和工资:sal * 12
mysql> select ename, sal as monthsal, (sal*12) as yearsal from emp;
2.起中文别名加单引号
mysql> select ename, sal '月薪', (sal*12) '年薪' from emp;
2.2 where
1. 大于、小于、等于、不等于
## 等于
select name from table_name where name='张三'
## 不等于
select empno, ename, sal from emp where sal!=800;
select empno, ename, sal from emp where sal<>800; //两句查询的结果都一样
## 大于
select empno, ename, sal from emp where sal>2000;
## 大于等于
select empno, ename, sal from emp where sal>=3000;
2. between … and
等同于 >= and <=,闭区间
案例:查询薪资在2450和3000之间的员工信息?包括2450和3000
mysql> select empno, ename, sal from emp where sal>=2450 and sal<=3000;
mysql> select empno, ename, sal from emp where sal between 2450 and 3000; //两句功能一样
3. is null (is not null )
案例:查询哪些员工的津贴/补助为null
mysql> select empno, ename, sal from emp where comm is null;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
+-------+--------+---------+
10 rows in set (0.00 sec)
mysql> select empno, ename, sal from emp where comm is not null;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7654 | MARTIN | 1250.00 |
| 7844 | TURNER | 1500.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)
注意:在数据库当中null不能使用等号进行衡量。需要使用is null因为数据库中的null代表什么也没有,它不是一个值。
4. and、or
案例:查询工作岗位是MANAGER**并且(或者)**工资大于2500的员工信息
mysql> select empno, ename, job, sal from emp where job='MANAGER' and sal>2500;
+-------+-------+---------+---------+
| empno | ename | job | sal |
+-------+-------+---------+---------+
| 7566 | JONES | MANAGER | 2975.00 |
| 7698 | BLAKE | MANAGER | 2850.00 |
+-------+-------+---------+---------+
2 rows in set (0.00 sec)
mysql> select empno, ename, job, sal from emp where job='MANAGER' or sal>2500;
+-------+-------+-----------+---------+
| empno | ename | job | sal |
+-------+-------+-----------+---------+
| 7566 | JONES | MANAGER | 2975.00 |
| 7698 | BLAKE | MANAGER | 2850.00 |
| 7782 | CLARK | MANAGER | 2450.00 |
| 7788 | SCOTT | ANALYST | 3000.00 |
| 7839 | KING | PRESIDENT | 5000.00 |
| 7902 | FORD | ANALYST | 3000.00 |
+-------+-------+-----------+---------+
6 rows in set (0.00 sec)
注意:and优先级比or高。
select * from emp where sal > 2500 and deptno = 10 or deptno = 20; //两语句功能一样
select * from emp where (sal > 2500 and deptno = 10) or deptno = 20;
select * from emp where sal > 2500 and (deptno = 10 or deptno = 20); //这句与上面不一样
5. in、not in
案例:查询工作岗位是MANAGER或SALESMAN的员工;查询薪资是800和5000的员工信息
mysql> select empno,ename,job from emp where job in('MANAGER', 'SALESMAN');
mysql> select * from emp where sal in (800,5000);
案例:查询薪资不是800,5000,3000的员工信息
mysql> select * from emp where sal not in (800,5000,3000);
6. like
称为模糊查询,支持%或下划线匹配
%
:匹配任意多个字符_
:任意一个字符
1. 找出名字中含有O的
mysql> select ename from emp where ename like '%O%';
2.找出名字以T结尾的
mysql> select ename from emp where ename like '%T';
3.找出名字以K开始的
mysql> select ename from emp where ename like 'K%';
4.找出第二个字每是A的
mysql> select ename from emp where ename like '_A%';
5.找出名字中有“ _”的,转义字符实现
mysql> select name from t_student where name like '%\_%'; // \转义字符。
+----------+
| name |
+----------+
| jack_son |
+----------+
2.3 order by
(1)升序:asc
查询所有员工薪资,排序。升序
select ename,sal from emp order by sal; //两句语句一样,默认就为升序
select ename,sal from emp order by sal asc;
(2)降序:desc
select ename,sal from emp order by sal desc;
(3)多个字段的排序
案例:查询员工名字和薪资,要求按照薪资升序,如果薪资一样的话,再按照名字升序排列。
案例分析:分主次。sal在前,起主导,只有sal相等的时候,才会考虑启用ename排序。
mysql> select ename,sal from emp order by sal asc, ename asc;
(4)按字段位置排序
了解一下,不建议这样排序。因为不健壮,很容易列顺序发生改变。
select ename,sal from emp order by 2; //按照第二列排序 索引从1开始
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
2.4 单行处理函数
即数据处理函数。当然还有多行处理函数
单行处理函数的特点:一个输入对应一个输出
多行处理函数的特点:多个输入对应一个输出
(1)lower()
select lower(ename) as ename from emp;
(2)upper()
select upper(ename) as ename from emp;
(3)substr()
注意:起始索引从1开始
mysql> select substr(ename,1,1) as ename from emp;
+-------+
| ename |
+-------+
| S |
| A |
| W |
| J |
| M |
| B |
| C |
| S |
| K |
| T |
| A |
| J |
| F |
| M |
+-------+
14 rows in set (0.01 sec)
案例:挑选首字母为A的人名
mysql> select ename from emp where ename like 'A%';
mysql> select ename from emp where substr(ename,1,1)='A'; //两句功能一样
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.00 sec)
(4)length()
案例:获取名字的长度
mysql> select ename, length(ename) enameLength from emp;
+--------+-------------+
| ename | enameLength |
+--------+-------------+
| SMITH | 5 |
| ALLEN | 5 |
| WARD | 4 |
| JONES | 5 |
| MILLER | 6 |
+--------+-------------+
14 rows in set (0.01 sec)
(5)concat()
案例:将empno字段和ename字段拼接起来
mysql> select concat(empno,ename) from emp;
+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH |
| 7499ALLEN |
| 7521WARD |
| 7934MILLER |
+---------------------+
14 rows in set (0.00 sec)
(6)trim()
mysql> select * from emp where ename=' KING';
Empty set (0.00 sec)
mysql> select * from emp where ename=trim(' KING');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)
(7)case…when…then…when…then…else…end
案例:当员工的工作岗位是MANAGER的时候,工资上调10%,当工作岗位是SALESMAN的时候,工资上调50%,其它正常。
mysql> select ename,job,sal oldsal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;
+--------+-----------+---------+---------+
| ename | job | oldsal | newsal |
+--------+-----------+---------+---------+
| SMITH | CLERK | 800.00 | 800.00 |
| ALLEN | SALESMAN | 1600.00 | 2400.00 |
| WARD | SALESMAN | 1250.00 | 1875.00 |
| JONES | MANAGER | 2975.00 | 3272.50 |
| MARTIN | SALESMAN | 1250.00 | 1875.00 |
| BLAKE | MANAGER | 2850.00 | 3135.00 |
| CLARK | MANAGER | 2450.00 | 2695.00 |
| SCOTT | ANALYST | 3000.00 | 3000.00 |
| KING | PRESIDENT | 5000.00 | 5000.00 |
| TURNER | SALESMAN | 1500.00 | 2250.00 |
| ADAMS | CLERK | 1100.00 | 1100.00 |
| JAMES | CLERK | 950.00 | 950.00 |
| FORD | ANALYST | 3000.00 | 3000.00 |
| MILLER | CLERK | 1300.00 | 1300.00 |
+--------+-----------+---------+---------+
14 rows in set (0.00 sec)
(8)round()
案例:保留整数
mysql> select round(123.456, 0) as result from emp limit 5; //保留整数
+--------+
| result |
+--------+
| 123 |
| 123 |
| 123 |
| 123 |
| 123 |
+--------+
5 rows in set (0.00 sec)
mysql> select round(123.456, 1) as result from emp limit 5; //四舍五入保留一位小数
+--------+
| result |
+--------+
| 123.5 |
| 123.5 |
| 123.5 |
| 123.5 |
| 123.5 |
+--------+
5 rows in set (0.00 sec)
mysql> select round(123.456, -2) as result from emp limit 5; //保留到百分位
+--------+
| result |
+--------+
| 100 |
| 100 |
| 100 |
| 100 |
| 100 |
+--------+
5 rows in set (0.00 sec)
(9)rand()
案例:生成1以内的随机数
mysql> select rand() from emp limit 5;
+----------------------+
| rand() |
+----------------------+
| 0.3314896806436495 |
| 0.41996393764388185 |
| 0.10535067702210571 |
| 0.2668616029125281 |
| 0.018256014229968205 |
+----------------------+
5 rows in set (0.00 sec)
案例:生成100以内的随机数,round和rand组合使用
mysql> select round(rand()*100,0) from emp limit 10;
+---------------------+
| round(rand()*100,0) |
+---------------------+
| 22 |
| 4 |
| 55 |
| 57 |
+---------------------+
10 rows in set (0.00 sec)
(10)ifnull()
ifnull是空处理函数。专门处理空的。
在所有数据库当中,只要有NULL参与的数学运算,最终结果就是NULL。
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.00 sec)
注意:NULL只要参与运算,最终结果一定是NULL。为了避免这个现象,需要使用ifnull函数。
ifnull函数用法:ifnull(数据, NULL赋予其他值)
例:补助为NULL的时候,将补助当做0
mysql> select ename, (sal+ ifnull(comm,0))*12 as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.01 sec)
2.5 多行处理函数
即分组函数
注意:分组函数在使用时必须先分组,然后才能用。如果你有对数据进行分组,整张表默认为一组。
1. 五个分组函数
count()
:计数sum()
:求和avg()
:平均值max()
:最大值min
():最小值
mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
| 800.00 |
+----------+
1 row in set (0.00 sec)
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)
mysql> select count(sal) from emp;
+------------+
| count(sal) |
+------------+
| 14 |
+------------+
1 row in set (0.00 sec)
2. 使用时注意事项
第一点:分组函数自动忽略NULL,不需要提前对NULL进行处理。
例如:求sum时不会把NULL加进去。
第二点:分组函数中count(*)和count(具体字段)有什么区别?
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
1 row in set (0.00 sec)
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)
- count(具体字段):表示统计该字段下所有不为NULL的元素的总数。
- count(*):统计表当中的总行数。
第三点:分组函数不能够直接使用在where子句中
例:找出比最低工资高的员工信息
mysql> select ename,sal from emp where sal>min(sal);
ERROR 1111 (HY000): Invalid use of group function
第四点:所有的分组函数可以组合起来一起用
mysql> select sum(sal),max(sal),min(sal),avg(sal),count(*) from emp;
+----------+----------+----------+-------------+----------+
| sum(sal) | max(sal) | min(sal) | avg(sal) | count(*) |
+----------+----------+----------+-------------+----------+
| 29025.00 | 5000.00 | 800.00 | 2073.214286 | 14 |
+----------+----------+----------+-------------+----------+
1 row in set (0.00 sec)
2.6 group by
什么是分组查询?
在实际的应用中,可能有这样的需求,需要先进行分组,然后对每一组的数据进行操作。
如:
计算每个部门的工资和
计算每个工作岗位的平均薪资
找出每个工作岗位的最高薪资
关键字的执行顺序
select
...
from
...
where
...
group by
...
order by
...
结论:select语句中,如果有group by语句,select后面只能跟参加分组的字段,以及分组函数,其它的一律不能跟。
例:找出每个工作岗位的工资和
mysql> select job, sum(sal) from emp group by job order by sal;
+-----------+----------+
| job | sum(sal) |
+-----------+----------+
| CLERK | 4150.00 |
| SALESMAN | 5600.00 |
| MANAGER | 8275.00 |
| ANALYST | 6000.00 |
| PRESIDENT | 5000.00 |
+-----------+----------+
5 rows in set (0.00 sec)
mysql> select job, sum(sal) from emp group by job order by sum(sal);
+-----------+----------+
| job | sum(sal) |
+-----------+----------+
| CLERK | 4150.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 5600.00 |
| ANALYST | 6000.00 |
| MANAGER | 8275.00 |
+-----------+----------+
5 rows in set (0.00 sec)
例:找出每个部门的最高薪资
mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
+--------+----------+
3 rows in set (0.00 sec)
实例:找出“每个部门,不同工作岗位”的最高薪资
mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 10 | CLERK | 1300.00 |
| 10 | MANAGER | 2450.00 |
| 10 | PRESIDENT | 5000.00 |
| 20 | ANALYST | 3000.00 |
| 20 | CLERK | 1100.00 |
| 20 | MANAGER | 2975.00 |
| 30 | CLERK | 950.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1600.00 |
+--------+-----------+----------+
9 rows in set (0.00 sec)
1. having
having可以对分完组之后的数据进一步过滤;having必须和group by联合使用。
having不能单独使用,having不能代替where
优化策略:where和having,优先选择where,where实在完成不了了,再选择having。
实例:找出每个部门最高薪资,要求显示最高薪资大于3000的
mysql> select deptno,max(sal) from emp group by deptno having max(sal)>3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
1 row in set (0.00 sec)
无法使用的where的情况:找出每个部门平均薪资,要求显示平均薪资高于2500的。
mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)
2. 总结
select
...
from
...
where
...
group by
...
having
...
order by
...
执行顺序?
- from
- where
- group by
- having
- select
- order by
实例:找出每个岗位的平均薪资,要求显示平均薪资大于1500的,除MANAGER岗位之外,要求按照平均薪资降序排。
mysql> select job,avg(sal) from emp where job <> 'MANAGER' group by job having avg(sal) >1500 order by avg(sal) desc;
mysql> select job,avg(sal) from emp where job not in ('MANAGER') group by job having avg(sal) >1500 order by avg(sal) desc;
mysql> select job,avg(sal) from emp where job != 'MANAGER' group by job having avg(sal)>1500 order by avg(sal) desc; //三句功能一样
+-----------+-------------+
| job | avg(sal) |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST | 3000.000000 |
+-----------+-------------+
2. distinct
注意:原表数据不会被修改,只是查询结果去重
mysql> select distinct job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)
这样编写是错误的,语法错误:select ename, distinct job from emp;
注意:distinct只能出现在所有字段的最前方
例:distinct出现在job,deptno两个字段之前,表示两个字段联合起来去重
mysql> select job, deptno from emp;
+-----------+--------+
| job | deptno |
+-----------+--------+
| CLERK | 20 |
| SALESMAN | 30 |
| SALESMAN | 30 |
| MANAGER | 20 |
| SALESMAN | 30 |
| MANAGER | 30 |
| MANAGER | 10 |
| ANALYST | 20 |
| PRESIDENT | 10 |
| SALESMAN | 30 |
| CLERK | 20 |
| CLERK | 30 |
| ANALYST | 20 |
| CLERK | 10 |
+-----------+--------+
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 |
+-----------+--------+
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.7 连接查询
1. 简介
(1)什么是连接查询
从一张表中单独查询,称为单表查询。
emp表和dept表联合起来查询数据,从emp表中取员工名字,从dept表中取部门名字;这种跨表查询,多张表联合起来查询数据,被称为连接查询。
(2)连接查询的分类
根据语法年代的分类:
SQL92:1992年的时候出现的语法
SQL99:1999年的时候出现的语法
我们这里重点学习SQL99.(这个过程中简单演示一个SQL92的例子)
根据表连接的方式分类:
内连接:等值连接、非等值连接、自连接
外连接:左外连接(左连接)、右外连接(右连接)、全连接(不讲)
(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 |
| 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)
最终查询结果条数,是两张表条数的乘积,这种现象被称为:笛卡尔积现象。
(4)怎么避免笛卡尔积现象
连接时加条件,满足这个条件的记录被筛选出来
select
emp.ename, dept.dname
from
emp, dept
where
emp.deptno = dept.deptno;
代码如下:
mysql> select emp.ename, dept.dname from emp, dept where emp.deptno = dept.deptno;
mysql> select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno; //两个结果一样
思考:最终查询的结果条数是14条,但是匹配的过程中,匹配的次数减少了吗?
还是56次,只不过进行了四选一。次数没有减少。
注意:表的连接次数越多效率越低,所以尽量避免表的连接次数。
2. 内连接:inner
(1)等值连接
案例:查询每个员工所在部门名称,显示员工名和部门名
SQL92语法:
select
e.ename,d.dname
from
emp e, dept d
where
e.deptno = d.deptno;
sql92的缺点:结构不清晰,表的连接条件,和后期进一步筛选的条件,都放到了where后面。
SQL99语法:
inner可以省略(带着inner可读性更好)
mysql> select
-> e.ename,d.dname
-> from
-> emp e
-> inner join //inner可以省略
-> dept d
-> on
-> e.deptno = d.deptno; // 条件是等量关系,所以被称为等值连接。
sql99优点:表连接的条件是独立的,连接之后,如果还需要进一步筛选,再往后继续添加where
(2)非等值连接
案例:找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级
select
e.ename, e.sal, s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal; // 条件不是一个等量关系,称为非等值连接。
(3)自连接
实例:查询员工的上级领导,要求显示员工名和对应的领导名
mysql> select empno, ename, mgr from emp limit 5;
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
+-------+--------+------+
5 rows in set (0.00 sec)
mysql> select a.ename as '员工名', b.ename as '领导名' from emp a join emp b on a.mgr = b.empno;
+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| 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 |
+--------+--------+
13 rows in set (0.00 sec)
3. 外连接
内连接的特点:完成能够匹配上这个条件的数据查询出来。
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 |
+--------+------------+
14 rows in set (0.00 sec)
mysql> select e.ename,d.dname from emp e right 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 |
| NULL | OPERATIONS |
+--------+------------+
15 rows in set (0.00 sec)
right代表什么:表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表。在外连接当中,两张表连接,产生了主次关系。
(1)左外连接
outer是可以省略的
select
e.ename,d.dname
from
dept d
left outer join //outer可以省略
emp e
on
e.deptno = d.deptno;
注意:
- 带有right的是右外连接,又叫做右连接。
- 带有left的是左外连接,又叫做左连接。
- 任何一个右连接都有左连接的写法。
- 任何一个左连接都有右连接的写法。
思考:外连接的查询结果条数一定是 >= 内连接的查询结果条数的嘛? 正确
实例:查询每个员工的上级领导,要求显示所有员工的名字和领导名
mysql> select a.ename, b.ename from emp a left join emp b on a.mgr=b.empno;
+--------+-------+
| ename | ename |
+--------+-------+
| 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)
4. 多张表连接
select
...
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
right join
d
on
a和d的连接条件
一条SQL中内连接和外连接可以混合。都可以出现!
5. 子查询
select语句中嵌套select语句,被嵌套的select语句称为子查询。
子查询都可以出现在哪里呢?
select
..(select).
from
..(select).
where
..(select).
(1)where子句中的子查询
案例:找出比最低工资高的员工姓名和工资
mysql> select ename,sal from emp where sal>min(sal);
ERROR 1111 (HY000): Invalid use of group function
错误原因:where子句中不能直接使用分组函数。
mysql> select ename,sal from emp where sal>(select min(sal) from emp);
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.00 sec)
(2)from子句中的子查询
注意:from后面的子查询,可以将子查询的查询结果当做一张临时表。(技巧)
案例:找出每个岗位的平均工资的薪资等级。
mysql> select t.*, s.grade
-> from (select job,avg(sal) as avgsal from emp group by job) t
-> join salgrade s
-> on t.avgsal between s.losal and s.hisal;
+-----------+-------------+-------+
| job | avgsal | grade |
+-----------+-------------+-------+
| CLERK | 1037.500000 | 1 |
| SALESMAN | 1400.000000 | 2 |
| ANALYST | 3000.000000 | 4 |
| MANAGER | 2758.333333 | 4 |
| PRESIDENT | 5000.000000 | 5 |
+-----------+-------------+-------+
5 rows in set (0.00 sec)
(3)select后面出现的子查询(这个内容不需要掌握,了解即可!!!)
实例:找出每个员工的部门名称,要求显示员工名,部门名
mysql> select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;
+--------+------------+
| 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)
6. union
案例:查询工作岗位是MANAGER和SALESMAN的员工
mysql> select ename, job from emp where job='MANAGER' or job='SALESMAN';
mysql> select ename,job from emp where job in ('MANAGER', 'SALESMAN');
mysql> select ename, job from emp where job='MANAGER'
-> union
-> select ename, job from emp where job='SALESMAN'; //三条语句结果一样
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.01 sec)
union的效率要高一些。对于表连接来说,每连接一次新表,则匹配的次数满足笛卡尔积,成倍的翻…
但是union可以减少匹配的次数。在减少匹配次数的情况下,还可以完成两个结果集的拼接。
比如:
a 连接 b 连接 c
a 10条记录
b 10条记录
c 10条记录
匹配次数是:1000
a 连接 b一个结果:10 * 10 --> 100次
a 连接 c一个结果:10 * 10 --> 100次
使用union的话是:100次 + 100次 = 200次。(union把乘法变成了加法运算)
注意事项:
错误:union在进行结果集合并的时候,要求两个结果集的列数相同。
select ename,job from emp where job = 'MANAGER'
union
select ename from emp where job = 'SALESMAN';
ERROR 1222 (21000): The used SELECT statements have a different number of columns
MYSQL可以,oracle语法严格 ,不可以,报错。 要求:结果集合并时列和列的数据类型也要一致。
mysql> select ename,job from emp where job = 'MANAGER'
-> union
-> select ename,sal from emp where job = 'SALESMAN';
+--------+---------+
| ename | job |
+--------+---------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ALLEN | 1600 |
| WARD | 1250 |
| MARTIN | 1250 |
| TURNER | 1500 |
+--------+---------+
7. limit
作用:显示部分查询结果,通常使用在分页查询当中。
完整用法:limit startIndex, length
实例:按照薪资降序,取出排名在前5名的员工
mysql> select ename,sal from emp order by sal desc limit 5;
mysql> select ename,sal from emp order by sal desc limit 0,5; //第一个参数为起始索引,默认从0开始 第二个参数为长度
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.01 sec)
注意:mysql当中limit在order by之后执行!
实例:取出工资排名在[3-5]名的员工
mysql> select ename,sal from emp order by sal desc limit 2,3; //下标2开始,长度为3
+-------+---------+
| ename | sal |
+-------+---------+
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
3 rows in set (0.00 sec)
分页
每页显示3条记录
第1页:limit 0,3
第2页:limit 3,3
第3页:limit 6,3
第4页:limit 9,3
第pageNo页:limit (pageNo - 1) * pageSize , pageSize
2.8 DQL语句的总结
select
...
from
...
where
...
group by
...
having
...
order by
...
limit
...