-- 单表查询(虚拟结果集)

-- 5.select *

-- 1.from emp

-- 2.where

-- 3.group by

-- 4.having

-- 6.order by ;

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

select ename,job,sal from emp ;

-- 设置别名：查询每位员工调整后的薪资(基本工资+1000)

select ename,job,sal+1000 薪资 from emp ;

-- 练习：查询每位员工的年薪(基本工资*12):empno,ename,年薪

select ename,empno,sal*12 年薪 from emp ;

-- 查询不重复的数据：查询emp表中有哪些部门

select distinct deptno from emp ;

-- 条件查询

-- 查询基本工资大于等于2000小于等于3000的员工信息

select *

from emp

where sal between 2000 and 3000;

select *

from emp

where sal>=2000 and sal<=3000;

-- 查询10号部门和20号部门中sal低于2000的员工信息

select *

from emp

where sal<2000 and deptno in(10,20);

select *

from emp

where sal<2000 and (deptno=10 or deptno=20); -- 优先级是not、and、 or，用括号改变优先级。

-- 练习：查询salesman的所属部门：姓名,职位,所在部门

select ename,job,deptno

from emp

where job="salesman";

-- 空值查询

-- 查询mgr为空的记录

select *

from emp

where mgr is null;

-- 练习：查询comm不为空的记录

select *

from emp

where comm 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%";

-- 练习：查询员工姓名中不包含s的员工信息

select *

from emp

where ename not like "%s%";

-- 查询结果排序

-- 单字段排序：查询所有员工信息按sal降序(desc)显示

select *

from emp

order by sal desc;

-- 多字段排序：查询所有员工信息按deptno升序(asc)、sal降序显示

select *

from emp

order by deptno asc,sal desc;

select *

from emp

order by sal desc ,deptno asc;

-- 限制查询结果数量

-- 查询基本工资最高的前5位员工

select *

from emp

order by sal desc

limit 5;

select *

from emp

order by sal desc

limit 0,5;

-- 查询基本工资第6到10名的员工（ limit [相对第一行的偏移量], 行数）

select *

from emp

order by sal desc

limit 5,5;

-- 练习：查询最后入职的5位员工

select *

from emp

order by hiredate desc

limit 5;

-- 聚合运算(会自动忽略空值)

-- 查询emp表中员工总数、最高工资、最低工资、平均工资及工资总和

select count(empno) 员工总数,max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资,sum(sal) 工资总和

from emp;

-- 分组查询(分组之后只能显示（select）分组字段、聚合运算以及与分组字段一一对应的字段)

-- 查询各部门的平均工资

select deptno,avg(sal)

from emp

group by deptno;

-- 查询各部门不同职位的平均工资

select deptno,job,avg(sal)

from emp

group by deptno,job

order by deptno asc,avg(sal) desc;

-- 练习：查询各部门的员工数

select deptno,count(empno)

from emp

group by deptno

order by deptno asc;

-- 练习：查询各部门不同职位的人数

select deptno,job, count(job)

from emp

group by deptno,job

order by deptno asc;

-- 分组后筛选(having是对分组后的再次筛选）

-- 查询各部门clerk的平均工资

select deptno,job,avg(sal)

from emp

where job='clerk'

group by deptno,job

order by deptno asc;

select deptno,job,avg(sal)

from emp

group by deptno,job

having job='clerk'

order by deptno asc;

-- 查询平均工资大于2000的部门

select deptno,avg(sal)

from emp

group by deptno

having avg(sal)>2000;

-- 多表连接查询

create table t1(key1 char,v1 int);

create table t2(key2 char,v2 int);

insert into t1 values('a',1),('a',2),('b',3),('c',4),('a',13);

insert into t2 values('b',10),('b',11),('a',12),('a',13),('e',14);

select * from t1;

select * from t2;

-- 内连接(满足条件就连接显示)（其他数据库还有全连接：full join）

-- select 字段1[,…] from 表1 [inner] join 表2 on 表1.key=表2.key;

select *

from t1

join t2

on t1.key1=t2.key2;

select *

from emp

join dept

on emp.deptno=dept.deptno;

-- 左连接(以左边表为主去连接，左边的都会显示，右边没有对应的不显示)

-- select 字段1[,…] from 表1(主表) left join 表2 on 表1.key=表2.key;

select *

from t1

left join t2

on t1.key1=t2.key2;

select *

from emp

left join dept

on emp.deptno=dept.deptno;

-- 右连接(以右边表为主去连接，右边的都会显示，左边没有对应的不显示)

-- select 字段1[,…] from 表1 right join 表2(主表) on 表1.key=表2.key;

select *

from t1

right join t2

on t1.key1=t2.key2;

select *

from emp

right join dept

on emp.deptno=dept.deptno;

-- 合并查询（联合查询）:被合并的结果集的列数、顺序和数据类型必须完全一致

-- union去重:select 字段1[,字段2,…] from 表名 union select 字段1[,字段2,…] from 表名;

select * from t1 union select * from t2;

-- union all不去重:select 字段1[,字段2,…] from 表名 union all select 字段1[,字段2,…] from 表名;

select * from t1 union all select * from t2;

-- 多表查询练习

-- 创建salgrade表

create table salgrade(grade int,losal int,hisal int);

insert into salgrade values(1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

select * from emp;

select * from dept;

select * from salgrade;

-- 查询每位员工的ename,dname,sal

select e.ename,e.sal,d.dname

from emp e,dept d

where e.deptno=d.deptno;

select ename,sal,dname

from emp

left join dept

on emp.deptno=dept.deptno;

-- 查询各地区的员工数（统计每个地区，没有员工计为0）

select loc,count(empno)

from dept

left join emp

on emp.deptno=dept.deptno

group by loc;

-- 查询manager的姓名、所属部门名称和入职日期：ename,dname,job,hiredate(内连接/笛卡尔积连接)

select ename,job,hiredate,dname

from dept join emp on emp.deptno=dept.deptno

where job='manager'; -- 内连接查询(好)

select ename,job,hiredate,dname,emp.deptno

from emp,dept

where emp.deptno=dept.deptno and job='manager'; -- -- 笛卡尔积连接查询

-- 查询每位员工的工资等级；empno,ename,sal,grade(不等值连接)

select empno,ename,sal,grade

from emp,salgrade

where sal between losal and hisal

order by grade asc; -- 笛卡尔积连接查询

select empno,ename,sal,grade

from emp left join salgrade

on sal between losal and hisal

order by grade asc; -- 左连接查询

select * from salgrade;

-- 查询各工资等级的员工数

-- 查询所有员工姓名及其直属领导姓名（自连接：通过别名，将同一张表视为多张表）(e1：员工表，e2：领导表)

select e1.ename 员工姓名,e2.ename 领导姓名

from emp e1 left join emp e2 on e1.mgr=e2.empno;

-- select e1.ename 员工姓名,e2.ename 领导姓名 from emp e1,emp e2 where e1.mgr=e2.empno;(笛卡尔积会忽略mgr为空的员工)

-- 查询入职日期早于其直属领导的员工：empno,ename,dname (e1：员工表，e2：领导表)

select e1.empno,e1.ename,dname

from emp e1 left join emp e2 on e1.mgr=e2.empno

left join dept on e1.deptno=dept.deptno

where e1.hiredate<e2.hiredate;