Mysql练习

练习：

create database bigdata;
use bigdata;

–dept部门表(deptno部门编号/dname部门名称/loc地点)

create table dept (
    deptno numeric(2),
    dname varchar(14),
    loc varchar(13)
);

insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept values (20, 'RESEARCH', 'DALLAS');
insert into dept values (30, 'SALES', 'CHICAGO');
insert into dept values (40, 'OPERATIONS', 'BOSTON');

– salgrade工资等级表(grade 等级/losal此等级的最低/hisal此等级的最高)

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

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

–emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
工资 ＝ 薪金 ＋ 佣金

create table emp (
    empno numeric(4) not null,
    ename varchar(10),
    job varchar(9),
    mgr numeric(4),
    hiredate datetime,
    sal numeric(7, 2),
    comm numeric(7, 2),
    deptno numeric(2)
);

insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, null, 20);
insert into emp values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into emp values (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, null, 20);
insert into emp values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

做题思路：
  用了什么表- -table
  分没分组：各种，按照要求有分组的- -维度
  查询什么数据- -指标
  怎么查- -语法：where…

1.查询出部门编号为30的所有员工的编号和姓名
 table：emp
  查什么：
  1.维度：group by
  2.指标: 聚合函数
  3.普普通通的字段：编号和姓名
  怎么查：
  where：部门编号为30

select empno,ename,deptno from emp where deptno=30
查询   编号，姓名,部门编号   从  员工表 哪个 部门编号 = 30

2.找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。

第一种方法：
select * from emp where deptno=10 and job='MANAGER'
union
select * from emp where deptno=20 and job='SALESMAN'
--查询  全部 从 员工表 哪个 部门编号 = 10 并且 工作=经理
--合并(去重)
--查询  全部 从 员工表 哪个 部门编号 = 20 并且 工作=销售
第二种方法：
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='SALESMAN')
--查询  全部 从 员工表 哪个 （部门编号 = 10 并且 工作=经理）或者（部门编号 = 20 并且 工作=销售）
sql中的or语法一般用于多个条件的查询，上面的语法查询的相当于：两个sql查询出来的数据集合。
or两边的条件一定要加（）。

3.查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序。
 指标：工资= sal+ comm
 排序：工资降序排序、入职日期升序排序

select (sal+comm) as earning,hiredate from emp order by earning desc ,hiredate asc;
--查询 （薪金+佣金）别名 工资，入职日期    从 员工表 排序通过   工资  降序，入职日期  升序
  报错：因为佣金可能为0
第一种方法：
select (sal+comm_alias) as earning,hiredate from (select sal,ifnull(comm,0) as comm_alias,hiredate from emp) as a order by earning desc ,hiredate asc;
--查询 （薪金+佣金别名） 别名  工资， 入职日期   从 （查询 佣金，如果佣金为空，则取0,别名 佣金别名，入职日期 从 员工表）别名 a 排序通过  工资   降序， 入职日期  升序）
第二种方法：
select ifnull((sal+comm),sal) as earning,hiredate from emp order by earning desc ,hiredate asc;
--查询 如果薪金+佣金为空，则取薪金 别名 工资， 入职日期   从 员工表 排序通过  工资    降序， 入职日期  升序

4.列出薪金大于1500的各种工作及从事此工作的员工人数。
  emp
  维度：工作
  指标：员工人数
  where : 薪金大于1500

select job,count(2) as cnt from emp where sal > 1500 group by job
--查询 工作，人数（写撒都行）别名cnt从员工表哪个薪金 > 1500 分组通过 工作

5.列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
  colunm: ename
  where
    销售部 deptno没有
  dept :
    deptno
    deptname
  主表 ： emp
    业务主线
  从表： 。。。dept
  辅助查询业务

第一种方法：
select ename from emp left join dept on emp.deptno = dept.deptno where dname='SALES'
--查询 员工姓名 从 员工表 左连接 部门表 在 员工表的部门编号 = 部门表的部门编号 哪个 部门的名字=销售
第二种方法;
select ename from emp where deptno in(select deptno from dept where dname='SALES')
--查询 员工姓名 从 员工表 哪个 部门编号 在 （查询 部门编号 从 部门表 哪个 部门的名字=销售）

6.查询姓名以S开头的\以S结尾\包含S字符\第二个字母为L __

select ename from emp where ename like "S%" OR ename like "%S" OR  ename like "%S%" OR ename like "_L%";
--查询 员工姓名 从 员工表 哪个 员工姓名 模糊查询 开头为s 或者 员工姓名 模糊查询 结尾为s 或者 员工姓名 模糊查询 包含s 或者 员工姓名 模糊查询 第二个字母为L

7.查询每种工作的最高工资、最低工资、人数

select job,max(ifnull((sal+comm),sal)) as max_earing,min(ifnull((sal+comm),sal)) as min_earing,count(*) as cnt from emp group by job
--查询  工作，最高（如果薪金+佣金为空，则取薪金）别名最高工资,最低（如果薪金+佣金为空，则取薪金）别名最低工资，人数 从 员工表 分组通过 工作

8.列出薪金 高于 公司平均薪金的所有员工号，员工姓名，所在部门名称，上级领导，工资，工资等级
 table:emp、dept
 维度： no
 指标： 员工号 员工姓名，
    部门名称 =》 join dept dname on deptno – done
    上级领导 =》 编号 / 名字 =》join emp => ename on mgr=empno
    工资 => ifnull
    工资等级 => 工资 vs salgrade ? => grade
 where :薪金 高于 公司平均薪金 ?

第一种方法：
-- 1.etl--取出用到的字段 并进行数据清洗
-- 2.公司平均薪金
select avg(sal) as avg_sal from emp
--查询 平均薪资 别名 平均薪资 从 员工表

--答案
select e.empno,e.ename,dname,e1.ename as mgr_name,earning,grade
--查询 临时e表里的员工号 临时表e里面的员工姓名 部门名称 临时表e1里面的员工姓名 别名 上级领导 工资 工资等级
--当需要依据条件的取值而决定某值的选择时，可以使用 case-when-end 语句
case--薪资等级
--开始
    when earning> 700 and earning<=1200 then 1
--当 工资 >  700 并且 工资 <= 1200 然后 取1
    when earning>1200 and earning<=1400 then 2
--当 工资 > 1200 并且 工资 <= 1400 然后 取2
    when earning>1400 and earning<=2000 then 3
--当 工资 > 2000 并且 工资 <= 3000 然后 取3
    when earning>2000 and earning<=3000 then 4
--当 工资 > 1400 并且 工资 <= 2000 然后 取4
    when earning>3000 and earning<=9999 then 5
--当 工资 > 3000 并且 工资 <= 9999 然后 取5
end as grade
--结束 别名 等级
from (
--从 （
    select empno,ename,deptno,mgr,
--查询 员工编号，员工名字，部门编号，主管编号，
    ifnull((sal + comm),sal) as earning
--如果 （薪金+佣金为空，则取薪金） 别名 工资
    from emp 
--从 员工表 
    where sal > (
--哪个 薪金 > （
        select avg(sal) as sal_avg
--查询 平均工资 别名 平均工资
        from emp
--从 员工表
    )
--） 
) as e 
--）别名 e（生成的临时表起个别名）
left join(
--左连接（
    select deptno,dname
--查询 部门编号，部门名字
    from dept 
--从 部门表
) as d
--）别名 d
on e.deptno=d.deptno
--关联条件 e表里的部门编号=d表里的部门编号
left join (
--左连接（
    select empno,ename
--查询 员工编号，员工名字
    from emp 
--从 员工表
)as e1
--）别名 e1
on e.mgr=e1.empno
--关联条件 e表里的员工编号=e1表里的员工编号

第二种方法：
select e.empno, e.ename,d.dname,e1.ename,ifnull(e.sal+e.comm,e.sal) as earning,s.grade
--查询 e表里的员工编号，e表里的员工姓名，d表里的部门编号，e1表里的员工姓名，如果 （薪金+佣金为空，则取薪金） 别名 工资，s表里的等级
from emp e 
--从 员工表 别名 e 
join dept d on e.deptno=d.deptno 
--连接 部门表 别名 d 关联条件 e表里的部门编号=d表里的部门编号
left join emp e1 on e.mgr=e1.empno 
--左连接 员工表 别名 e1 关联条件 e表里的主管编号=e1表里的员工编号
cross join salgrade s
--交叉连接(笛卡尔集) 薪金等级 别名 S
where e.sal>(
--哪个 e表的薪金（
    select avg(sal)
--查询 平均薪金
    from emp
--从 员工表
) and ifnull(e.sal+e.comm,e.sal) between s.losal and s.hisal;
--）并且 如果 （e表里的薪金+e表里的佣金为空，则取e表里的薪金）在 s表里的最低薪金 到 s表里的最高薪金之间

 1.cross join:笛卡尔集（不用，算法时可用）
 2.between and 范围
 3.给表起别名可以不写as 直接空格
 4.连接表，然后在查询连接表里的数据，就可以显现，on 关联条件

9.列出薪金 高于 在各自部门工作的员工的平均薪金的 员工姓名和薪金、部门名称。
 table：emp
 维度： 部门
 指标：平均薪金 员工姓名和薪金、部门名称
 where 薪金 高于各自部门工作的员工的平均薪金

-- 1.各自部门工作的员工的平均薪金
--分组后平均工资就会变成部门里的
select deptno,avg(sal) as sal_avg
--查询 部门编号，平均薪金 别名 平均薪金
from emp
--从 员工表
group by deptno
--分组通过 员工编号

-- emp:员工姓名和薪金、部门名称 平均薪金
--部门名称 => join dept => dname

-----答案
select ename,sal,a.deptno,dname,round(sal_avg,2) as sal_avg_alias
--查询 员工姓名，薪金，a表里的部门编号，部门名称，小数点选择（平均薪金，后两位） 别名平均薪金别名
from(
--从 （
    select ename,sal,a.deptno,b.dname
--查询 员工姓名，薪金，部门编号，部门名称
    from(-- 主表
--从 （ 
        select ename,sal,deptno
--查询 员工姓名，薪金，部门编号
        from emp
--从 员工表
      ) as a
--）别名 a
      left join dept b on a.deptno = b.deptno
--左连接 部门表 别名b 关联条件 a表里的部门编号=b表里的部门编号
  ) as a
--）别名 a
  left join (
--左连接 （ 
    select deptno,avg(sal) as sal_avg
--查询 部门编号，平均薪金 别名 平均薪金
    from emp
--从 员工表
    group by deptno
--分组通过 部门编号
  ) as b on a.deptno = b.deptno
--）别名 b 关联条件 a表里的部门编号=b表里的部门编号
  where sal >  round(sal_avg,2);
--哪个 薪金 > 小数点选择（平均薪金，后两位）

在mysql中，round函数用于数据的四舍五入，它有两种形式：
 1、round(x,d) ，x指要处理的数，d是指保留几位小数
  这里有个值得注意的地方是，d可以是负数，这时是指定小数点左边的d位整数位为0,同时小数位均为0；
 2、round(x) ,其实就是round(x,0),也就是默认d为0；