数据库DQL

最简单的查询语句，有两个子句：分别是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;