Select
七个查询命令
SELECT 字段
FROM `表名`
WHERE 条件语句
GROUP BY 字段
HAVING 条件语句
ORDER BY 字段
LIMIT 索引,数量
FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT
简单查询语句
select * from emp; //查询emp表所有数据
WHERE应用
SELECT DEPTNO, ENAME,JOB,SAL FROM EMP WHERE DEPTNO=20;
WHERE 判断条件格式
- 关系表达式:=, !=, >, >=, <, <=
- 逻辑表达式:and, or
特殊运算符:
- in ();
//查询 所有项目经理,销售,办事员信息
select * from emp where job in ('manager','salesman','clerk');
- not in ();
//查询不是项目经理,也不是销售,更不是办事员
select * from emp where job not in ('manager','salesman','clerk');
- between…and…
//查询 工资在1000到2000之间职员信息
select ename, sal from emp where sal between 1000 and 2000;
- is null
//查询 补助为null的人
select ename, comm from emp where comm is null;
- is not null
//查询 补助不为null的人
select ename, comm from emp where comm is not null;
- any, all, some
- like
//前置模糊查询条件:查询名字以S开头的职员
select ename from emp where ename like 'S%'; //%为通配符
//查询名字第二个字符为A的职员
select ename from emp where ename like '_A%'; //_表示一个任意字符
聚合函数
- max(字段):返回字段下最大值
//部门30下销售的最高工资
select max(sal) from emp where deptno=30 and job='salesman';
- min(字段):返回字段下最小值
//办事员最低工资
select min(sal) from emp where job='clerk';
- avg(字段):返回字段下平均值
//部门10下销售的平均工资
select avg(sal) from emp where deptno=10 and job='salesman';
- sum(字段):返回字段下所有数字相加之和
//老板每个月支出的工资
select sum(sal) from emp;
- count(字段):返回字段下内容不为null的数据个数
//补助不为null的人数
select count(comm) from emp;
//公司的人数
select count(*) from emp;
GROUP BY 分组查询
//查询部门20下各个职位名称及其职位人数
select deptno, job, count(*) from emp where deptno=20 group by job;
group by 使用
//查询部门20下各个职位名称及其职位人数
select job, count(*) from emp where deptno=20 group by job;
//查询各个职位最低工资
select job, min(sal) from emp group by job;
//查询各个部门的人数,平均工资
select deptno, count(*), avg(sal) from emp group by deptno;
group by 多字段分组
//查询各个部门职位名称和职位人数(多字段分组)
//分组字段声明顺序对查询结果没有影响
select deptno, job, count(*) from emp group by deptno,job;
HAVING
having命令不能独立出现在查询语句中,只能声明在group by后面
//查询部门30下职位人数在2人以上的职位信息
select deptno, job, count(*) from emp where deptno=30 group by job having count(*) >=2;
having 使用
//查询部门人数在5人以上的部门
select deptno, count(*)
from emp
group by deptno
having count(*)>=5;
//查询部门30下职位人数在2人以上的职位信息
select deptno, job, count(*)
from emp
where deptno=30
group by job
having count(*)>=2;
//查询各个部门下各个职位中人数在2人以上职位信息
select deptno, job, count(*)
from emp
group by deptno, job
having count(*)>=2;
//查询平均工资在1600以上部门
select deptno, avg(sal)
where emp
group by deptno
having avg(sal)>=1600;
ORDER BY
//查询部门30下平局工资在1000元以上的职位信息,要求按照职位平均工资降序排列
select deptno, job, count(*), avg(sal)
from emp
where deptno=30
group by job
having avg(sal)>=1000
order by avg(sal) desc;
LIMIT
//查询部门30下工资由高到低的前三名员工的信息
select deptno, sal from emp where deptno=30 order by sal limit 1,3;