最简单的查询语句,有两个子句:分别是select和from
-- 练习1:查询员工表中的所有记录 *表示表中的所有字段
select * from emp;
-- 练习2: 查询员工表中的员工姓名,职位,入职日期,所在部门编号
select ename,job,hiredate,deptno from emp;
-- 别名的用法: 可以给表起别名,也可以给列起别名
select ename 姓名, job '职位', hiredate as "入职日期" ,deptno 部门编号 from emp as 员工表;
select emp.* from emp;
select a.* from emp a;
select emp.* from emp a; -- 命名了别名,就必须使用别名
where 子句的应用
-- 练习1: 查询工资大于2000的员工编号,姓名,职位,工资
select empno,ename,job,sal from emp where sal > 2000;
-- 练习2: 查询20号部门,工资大于2000的员工姓名,职位,工资,部门编号
select ename,job,sal,deptno from emp where deptno = 20 and sal > 2000;
-- 练习3:查询不是20号部门,工资在1000~2000范围内的员工信息
select * from emp where deptno <> 20 and sal >=1000 and sal <=2000;
select * from emp where deptno <> 20 and sal between 1000 and 2000;
-- 练习4:查询10 和 20 号部门的员工编号大于7500的员工信息
select * from emp where deptno =10 or deptno =20 and empno >7500;
select * from emp where deptno in (10,20) and empno >7500;
-- 练习5:查询1981年入职的员工信息
select * from emp where hiredate >= '1981-1-1' and hiredate < '1982-1-1';
select * from emp where YEAR(hiredate) = 1981;
-- 练习6:查询职位不是 ANALYST、SALESMAN、MANAGER的员工信息
select * from emp where job != 'ANALYST' and job !='SALESMAN' and job != 'MANAGER';
select * from emp where job not in ('ANALYST','SALESMAN','MANAGER');
-- 练习7:查询员工姓名是A开头的员工编号,姓名,职位
select empno,ename,job from emp where ename like 'A%';
-- 练习8:查询员工姓名为5个长度的员工编号,姓名,职位
select empno,ename,job from emp where ename like '_____';
-- 练习9: 查询员工姓名中第二个字母是A的,职位不是CLERK的员工编号,姓名,职位
select empno,ename,job from emp where ename like '_A%' and job !='CLERK';
group by 子句的应用
应用场景:对表中的记录进行分组,比如按照部门、职位、地区、年龄、性别、季度等字段进行分组,
对每一组中的数据进行统计,统计总条数,和,平均值,最大值,最小值等
一般情况下,每一组统计完,都是一条记录。
比如: 表中的数据可以按照部门分组,分三组,最终统计的结果,就是三行记录。
语法: group by 分组字段1[,分组字段2....]
注意事项:
1. select子句中 除了用于统计的函数外,只能放分组字段,如果放入其他字段没有意义。
2. 函数(聚合函数,分组函数):
count(*|常量|字段名): 统计该组中的所有记录总数
sum(常量|字段名): 如果是常量,则统计的是该组中所有记录个常量的和
avg(常量|字段名):同上,只不过是求平均值。 使用总和除以总条数
max(字段名): 返回该组中的指定字段里的最大值
min(字段名): 返回该组中的指定字段里的最小值
3. null值处理:
当在聚合函数中使用字段名时,如果该组中的这个字段在某些行上可能存在null。 默认情况下
null值所在行不会被统计在内。因此可能会影响最终结果。
此时,需要使用ifnull函数对null值进行处理,
语法:ifnull(字段名,defaultValue):
逻辑:if(字段值==null) defaultValue else 字段值
一般情况下: avg要使用的。
-- 练习1:统计每个部门的人数
select deptno,count(*) from emp group by deptno;
select deptno,count(1) from emp group by deptno;
-- 练习2:统计每年入职的人数
select year(hiredate),count(*) from emp group by year(hiredate);
-- 练习3:统计每种职位的人数
select job,count(*) from emp group by job;
-- 练习4:统计每个部门不同职位的人数
select deptno,job,count(*) from emp group by deptno,job;
-- 练习5:统计每个部门的人数,工资总和,平均工资,最高工资,最低工资。
select
deptno,
count(*) 总人数,
sum(sal) 工资之和,
avg(ifnull(sal,0)) 平均工资,
max(sal) 最高工资,
min(sal) 最低工资
from emp group by deptno;
-- 练习6:统计每个部门的每种职位的人数、工资之和、平均工资
SELECT
deptno,
job,
count( 1 ),
sum( sal ),
avg( ifnull( sal, 0 ) )
FROM
emp
GROUP BY
deptno,
job;
-- 练习7:统计每个部门的人数,奖金之和,平均奖金,最低奖金,最高奖金。
SELECT deptno, COUNT(1)
, SUM(ifnull(comm, 0))
, AVG(ifnull(comm, 0))
, MIN(ifnull(comm, 0))
, MAX(if(comm, 0))
FROM emp
GROUP BY deptno;
-- 练习8: 查询部门表中的总人数,最高工资,最低工资,平均奖金,奖金之和。
-- 如果没有使用group by子句,而在select子句中使用聚合函数,那就表示整张表为一组,进行统计。
select count(1),max(sal),min(sal),avg(ifnull(comm,0)),sum(comm) from emp;
-- 练习9: 使用select 计算1和2 的和。
select 1+2;
select 10-2;
select 10*3;
select 10/3;
select 10%3;
-- 练习10: 基于表,在select子句中使用常量。 则会给每一行记录分配一个常量
select 100,a.* from emp a;
-- 比如: 统计表中的记录总数
select sum(1),count(0),count(*) from emp;
having 子句的应用
应用场景: 分组后,继续过滤和筛选.
位置: 位于group by子句后
语法: having condition
-- 练习1:查询部门平均工资大于1600部门号,平均工资。
select deptno,avg(ifnull(sal,0)) avg_sal from emp group by deptno having avg_sal >1600;
-- 练习2:查询每种职位的最高工资大于1500的职位、最高工资,平均工资,平均奖金。
select job,max(sal),avg(ifnull(sal,0)),avg(ifnull(comm,0)) from emp group by job having max(sal)>1500;
order by 子句的应用
应用场景: 对查询出来的数据,进行排序。 按照某些字段升序或者降序
语法: order by colName [asc|desc] [,colName [asc|desc]]
asc: 升序, 默认情况就是升序,可以省略不写
desc: 降序
执行顺序:位于select子句后
-- 练习1:查询员工表中的所有员工信息,按照工资降序排序
select * from emp order by sal desc;
-- 练习2:查询员工表中的所有员工信息,按照工资降序排序,如果相同,再按照奖金降序排序
select * from emp order by sal desc,comm desc;
distinct关键字
应用场景: 当查询出来的记录(整行内容)有重复的, 而我们不需要重复数据,就可以使用distinct
位置: 紧随select关键字之后
-- 练习1:查询有哪些部门编号
select distinct deptno from emp;
-- 练习2:查询有哪些职位名称
select distinct job from emp;
-- 练习3:查询有哪些部门以及部门中有哪些职位
select distinct deptno,job from emp order by deptno;
limit关键字
应用场景: 当一页的数据量过大时,我们可以进行关键字limit进行分页显示操作。
注意:分页查询时,一般都要进行先排序,再分页。
语法:limit m[,n];
m 表示从index处开始查询,
n 表示要查询的记录数目。
注意:
1. mysql的记录索引index从0开始。
2. 只有一个参数时,比如limit n 表示从第一行开始查询n条记录
-- 练习1: 按照工资降序,查询前五条记录
select * from emp order by sal desc limit 5;
select * from emp order by sal desc limit 0,5;
-- 练习2: 按照工资降序,查询第二个五条记录
select * from emp order by sal desc limit 5,5;
-- 练习3: 按照工资降序,查询第3条到第10条记录
select * from emp order by sal desc limit 2,8;
-- 练习4: 分页查询,工资降序,规定每页5条记录,查询第三页的数据。
select * from emp order by sal desc limit 10,5;
-- 练习5: 分页查询,工资降序,规定每页3条记录,查询第1页的数据。
select * from emp order by sal desc limit 0,3;
-- 练习6: 分页查询,工资降序,规定每页3条记录,查询第2页的数据。
select * from emp order by sal desc limit 3,3;
-- 练习7: 分页查询,工资降序,规定每页3条记录,查询第3页的数据。
select * from emp order by sal desc limit 6,3;
-- 练习8: 分页查询,工资降序,规定每页pageSize条记录,查询第page页的数据。
select * from emp order by sal desc limit (page-1)*pageSize,pageSize;