简单的查询(DQL)
语法格式:
select 字段名1,字段名2,字段名3,...from 表名;
提示:
1.任何一条sql语句以“;”结尾。
2.sql语句不区分大小写。
-
查询emp表的员工的名字
-
查询emp表的员工编号和员工名字
-
大写、小写、大小写混合
大写
小写
大小写混合
-
其他格式写法
-
查询员工的年薪(字段可以参与数学运算)
sal是月薪 select ename,sal * 12 from emp;
-
给查询结果的列重命名
把sal改为yearsal select ename,sal * 12 as yearsal from emp; 注意:as可以省略 select ename,sal * 12 yearsal from emp;
-
中文
把sal改为年薪 select ename,sal * 12 as 年薪 from emp;//错误!!! select ename,sal * 12 as '年薪' from emp;//正确 注意:标准sql语句要求字符串使用单引号括起来!虽然mysql支持双引号,但尽量别用。
-
查询所有字段
select * from emp; 实际开发中不建议使用*,效率较低
条件查询
语法格式:
select
字段,字段...
from
表名
where
条件;
执行顺序:先from,然后where,最后select
查询工资等于5000的员工姓名
select ename from emp where sal = 5000;
查询SMITH的工资
select sal from emp where ename = 'SMITH';
找出工资大于3000,包括3000的员工
select enam,sal from emp where sal >= 3000;
找出工资不等于3000的员工
select ename,sal from emp where sal <> 3000;
!=也可以
select ename,sal from emp where sal != 3000;
between…and…
找出工资在1100和3000之间的员工,包括1100和3000
select ename,sal from emp where sal >= 1100 and sal <= 3000;
另外一种写法如下:
包含:between...and...是闭区间[1100 ~ 3000]
select ename,sal from emp where sal between 1100 and 3000;
注意:between and 在使用的时候必须左小右大
is null 和 is not null
在数据库当中NULL不是一个值,代表什么也没有,为空。空不是一个值,不能用等号衡量,必须使用is null或者is not null。
找出哪些人津贴为null
select ename,sal,comm from emp where comm is null;
错误写法:select ename,sal,comm from emp where comm = null;
找出哪些人津贴不为null
select ename,sal,comm from emp where comm is not null;
找出哪些人没有津贴
select ename,sal,comm from emp where comm is null or comm = 0;
and (并且)和 or(或者)
找出工作岗位是MANAGER和SALESMAN的员工
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
and和or联合起来使用:找出薪资大于1000的并且部门编号是20或30部门的员工
//错误的,这样会变成找出薪资大于1000并且部门编号是20的员工和部门编号是30的员工。
select ename,sal,deptno from emp where sal > 1000 and deptno = 20 or deptno = 30;
//正确的
select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30);
注意:当运算符的优先级不确定的时候加小括号。
in
in等同于or:找出工作岗位是MANAGER和SALESMAN的员工
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
可以改用in写:效果一样
select ename,job from emp where job in('MANAGER','SALESMAN');
select ename,job from emp where sal in(800,5000);
注意:这里找出的是工资是800 和 5000的员工,in后面的值不是区间,是具体的值
找出工资不是800和5000的员工
select ename,job from emp where sal no in(800,5000);
模糊查询like
在模糊查询中,必须掌握两个特殊的符号,一个是%,一个是_
%表示任意多个字符,_代表任意1个字符。
找出名字当中含有O的
select ename from emp where ename like '%O';
找出名字第二个字母是A的
select ename from emp where ename like '_A%';
找出名字第三个字母是A的
select ename from emp where like '__A%';
找出名字中有下划线的
select ename from emp where ename like '%\_%';
找出名字中最后一个字母是T的
select ename from emp where ename like '%T';
排序(升序、降序)
按照工资升序,找出员工名和薪资
select
ename,sal
from
emp
order by
sal;
//结果:
+-------=+---------+
| ename | sal |
+-------=+---------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
+--------+---------+
注意:默认是升序,如何指定升序或者降序呢?asc表示升序,desc表示降序。
select ename,sal from emp order by sal;//升序
select ename,sal from emp order by sal asc;//升序
select ename,sal from emp order by sal desc;//降序
按照工资的降序排列,当工资相同的时候再按照名字的升序排列
select ename,sal from emp order by sal desc,ename asc;
注意:越靠前的字段越能起到主导的作用,只有当前面的字段无法完成排序的时候,才会启用后面的字段
找出工作岗位是SALESMAN的员工,并且要求按照薪资的降序排列
select select
ename,job,sal 字段 3
from from
emp 表名 1
where where
job = 'SALESMAN' 条件 2
order by order by
sal desc; ... 4
//结果:
+-------=+----------+---------+
| ename | job | sal |
+--------+----------+---------+
| ALLEN | SALESMAN | 1600.00 |
| TURNER | SALESMAN | 1500.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
+--------+----------+---------+
order by 是最后执行的。
分组函数
- count 计数
- sum 求和
- avg 平均值
- max 最大值
- min 最小值
记住:所有的分组函数都是对“某一组”数据进行操作的。
找出员工的工资总和
select sum(sal) from emp;
//结果:
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
找出最高工资、最低工资、平均工资、总人数
最高
select max(sal) from emp;
最低
select min(sal) from emp;
平均工资
select avg(sal) from emp;
总人数
select count(*) from emp;
select count(ename) from emp;
分组函数一共5个,分组函数还有另一个名字,叫多行处理函数。多行处理函数的特点:输入多行,最终输出的结果是一行
分组函数自动忽略NULL!!!
select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
select * from emp;
+----------+--------+-----------+---------+---------+
| EMPNO | ENAME | JOB | SAL | COMM |
+----------+--------+-----------+---------+---------+
| 7369 | SMIT | CLERK | 800.00 | NULL |
| 7399 | ALLEN | SALESMAN | 1600.00 | 300.00 |
| 7521 | WARD | SALESMAN | 1250.00 | 500.00 |
| 7566 | JONES | MANAGER | 2975.00 | NULL |
| 7654 | MARTIN | SALESMAN | 1250.00 | 1400.00 |
| 7698 | BLAKE | MANAGER | 2450.00 | NULL |
| ... | ... | ... | ... | ... |
| 7844 | TURNER | SALESMAN | 1500.00 | 0.00 |
+----------+--------+-----------+---------+---------+
count(*) 和 count(具体的某个字段)的区别
count(*):不是统计某个字段中数据的个数,而是统计总记录条数。(和某个字段无关)
count(comm):表示统计comm字段中不为NULL的数据总数量
分组函数也能组合起来用
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
+----------+----------+-------------+----------+----------+
| count(*) | sum(sal) | avg(sal) | max(sal) | min(sal) |
| 14 | 29025.00 | 2073.214286 | 5000.00 | 800.00 |
+----------+----------+-------------+----------+----------+
单行处理函数
输入一行,输出一行
计算每个员工的年薪
select ename,(sal + comm) * 12 as yearsal from emp;
注意:这里comm(补助)若为空,计算出来的年薪会为空,所有的数据库都是这样规定的:只要有NULL参与的运算结果一定是NULL
ifnull(可能为NULl的数据,被当做什么处理),属于单行处理函数
select ename,ifnull(comm,0) as comm from emp;
+--------+---------+
| ENAME | COMM |
+--------+---------+
| SMIT | 0.00 |
| ALLEN | 300.00 |
| WARD | 500.00 |
| JONES | 0.00 |
| MARTIN | 1400.00 |
| BLAKE | 0.00 |
| ... | ... |
| TURNER | 0.00 |
+--------+---------+
使用ifnull函数计算每个员工的年薪
select ename,(sal * ifnull(comm,0)) * 12 as yearsal from emp;
+--------+----------+
| ENAME | yearsal |
+--------+----------+
| SMIT | 9500.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| ... | ... |
| TURNER | 18000.00 |
+--------+----------+
找出工资高于平均工资的员工
select avg(sal) from emp;//平均工资
//ERROR 1111 (HY000) : Invalid use of group function(无效地使用了分组函数)
select ename,sal from emp where sal > avg(sal);
原因:SQL语句当中有一个规则,分组函数不可直接使用在where子句当中。
解析:因为 group by 是在 where 执行之后才会执行的。这里还没分组(还没 group by)不能使用分组函数,尴尬的是 where 执行完后才能 group by ,所以报错。
执行顺序如下:
select 5
...
from 1
...
where 2
...
group by 3
...
having 4
...
order by 6
...
找出工资高于平均工资的员工,分两步
第1步:找出平均工资
select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
第2步:找出高于平均工资的员工
select ename,sal from emp where sal > 2073.214286;
联合起来写如下:
select ename,sal from emp where sal > (select avg(sal) from emp);
//结果:
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
分组查询
group by 和 having
group by :按照某个字段或者某些字段进行分组
having :having是对分组之后的数据进行再次过滤
group by
找出每个工作岗位的最高薪资
select max(sal),job from emp group by job;
+----------+-----------+
| max(sal) | job |
+----------+-----------+
| 3000.00 | ANALYST |
| 1300.00 | CLERK |
| 2957.00 | MANAGER |
| 5000.00 | PRESIDENT |
| 1600.00 | SALESMAN |
+----------+-----------+
(先 from 再 group by 最后 select)
注意:
1.分组函数一般都会和 group by 联合使用,这也是为什么它被称为分组函数的原因,并且任何一个分组函数(count、sum、avg、max、min)都是在 group by 语句执行结束之后才会执行的。
2.当一条SQL语句没有 group by 的话,整张表的数据会自成一组。
多字段分组查询
select ename,max(sal),job from emp group by job;
以上在MySQL当中查询有结果,但结果是没有意义的,在Oracle数据库当中会报错,语法错误,记住一个规则:当一条语句中有 group by 的话,select 后面只能跟分组函数和参与分组的字段。
每个工作岗位的平均薪资
select job,avg(sal) from emp group by job;
多个字段联合起来分组
找出每个部门不同工作岗位的最高薪资,数据如下
deptno:部门编号
+--------+-----------+---------+
| deptno | job | sal |
+--------+-----------+---------+
| 10 | CLERK | 1300.00 |
| 10 | PRESIDENT | 5000.00 |
| 10 | MANEGER | 2450.00 |
| 10 | MANEGER | 2000.00 |
| 20 | ANALYST | 3000.00 |
| 20 | ANALYST | 2000.00 |
| 20 | ANALYST | 1000.00 |
| 20 | CLERK | 1100.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1250.00 |
| 30 | SALESMAN | 1500.00 |
| 30 | SALESMAN | 1400.00 |
| 30 | CLERK | 950.00 |
+--------+-----------+---------+
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 |
| 30 | CLERK | 950.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1500.00 |
+--------+-----------+----------+
having
having就是为了过滤分组后的数据而存在的,不可以单独的出现
找出每个部门的最高薪资,要求显示薪资大于2900的薪资
第一步:找出每个部门的最高薪资
select max(sal),deptno from emp group by deptno;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
| 5000.00 | 10 |
| 3000.00 | 20 |
| 2850.00 | 30 |
+----------+--------+
第二步:找出薪资大于2900
select max(sal),deptno from emp group by deptno having max(sal) > 29000;//这种方式效率低
+----------+--------+
| max(sal) | deptno |
+----------+--------|
| 5000.00 | 10 |
| 3000.00 | 20 |
+----------+--------+
select max(sal),deptno from emp where sal > 2900 group by deptno;//效率较高,这里的max的数据是原来的数据,不用计算也是这个值。
注意:建议能够使用where过滤的尽量使用where
找出每个部门的平均薪资,要求薪资大于2000的数据
第一步:找出每个部门的平均薪资
select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
第二步:要求显示薪资大于2000的数据
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
+--------+-------------+
select deptno,avg(sal) from where avg(sal) > 2000 emp group by deptno;//错误 ,where后面不能使用分组函数,这里的avg是算出来的,原来数据没有的,必须算出来的数据。
总结DQL语句
一个完整的DQL语句
select
...
from
...
where
...
group by
...
having
...
order by
...
执行顺序:from ---> where ---> group by ---> having ---> select ---> order by