-- 查询指定列：查询emp表中ename,job,sal
select ename,job,sal from emp;

-- 设置别名：查询每位员工调整后的薪资(基本工资+1000)
select *,sal+1000 from emp;

select *,sal+1000 as 调薪 from emp;
select *,sal+1000 调薪 from emp;-- as关键字可以省略

-- 查询不重复的数据：查询emp表中有哪些部门
select distinct deptno from emp;

select distinct deptno,job from emp;
-- 条件查询
-- 查询基本工资大于等于2000小于等于3000的员工信息
select *
from emp
where sal>=2000 and sal<=3000;

select *
from emp
where sal between 2000 and 3000;

-- 查询10号部门和20号部门中sal低于2000的员工信息
select *
from emp
where (deptno=10 or deptno=20) and sal<2000;

select *
from emp
where deptno in (10,20) and sal<2000;

-- 空值查询
-- 查询mgr为空的记录
select *
from emp
where mgr is null;

select *
from emp
where mgr is not null;

-- 模糊查询
-- 查询姓名以a开头的员工信息
select *
from emp
where ename like \'a%\';

-- 查询姓名中包含a的员工信息
select *
from emp
where ename like \'%a%\';

-- 查询姓名中第二个字符为a的员工信息
select *
from emp
where ename like \'_a%\';

-- 查询结果排序
-- 单字段排序：查询所有员工信息按sal降序显示
select *
from emp
order by sal desc;

-- 多字段排序：查询所有员工信息按deptno升序、sal降序显示
select *
from emp
order by deptno asc,sal desc;

-- 查询基本工资最高的前5位员工
select *
from emp
order by sal desc
limit 5;

-- 查询基本工资第6到10名的员工
select *
from emp
order by sal desc
limit 5,5;

-- 聚合运算
-- 查询emp表中员工总数、最高工资、最低工资、平均工资及工资总和
select count(*) 员工总数,max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资,sum(sal) 工资总和
from emp;

-- 分组查询
-- 查询各部门的平均工资
select deptno,ename,avg(sal)
from emp
group by deptno;

-- 查询各部门不同职位的平均工资
select deptno,job,avg(sal)
from emp
group by deptno,job;

-- 分组后筛选
-- 查询各部门clerk的平均工资
select deptno,job,avg(sal)
from emp
group by deptno,job
having job=\'clerk\';

select deptno,job,avg(sal)
from emp
where job=\'clerk\'
group by deptno;

-- 查询平均工资大于2000的部门
select deptno,avg(sal)
from emp
group by deptno
where avg(sal)>2000;-- where子句位于from后面

select deptno,avg(sal)
from emp
where avg(sal)>2000 -- where子句中不能使用聚合函数
group by deptno;

select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>2000;

-- 内连接
select * from t1 inner join t2 on t1.key1=t2.key2;

select * from t1 join t2 on t1.key1=t2.key2;

-- 左连接
select * from t1 left join t2 on t1.key1=t2.key2;

-- 合并查询
select * from t1
union
select * from t2;

select * from t1
union all
select * from t2;