MySQL查询

-- 1. 使用两种方式查询所有员工(EMP)信息
SELECT * from emp;

-- 2. 查询(EMP)员工编号、员工姓名、员工职位、员工月薪、工作部门编号。
select EMPNO,ENAME,JOB from emp; 

-- 1. 员工转正后，月薪上调20%，请查询出所有员工转正后的月薪。sal+sal*0.2=>sal(1+0.2) 
select sal*1.2 from emp;

-- 2. 员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的年薪所得（不考虑奖金部分,年薪的试用期6个月的月薪+转正后6个月的月薪)
select  ename,sal,comm,  sal*6+sal*1.2*6 from emp;

-- 1. 员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入，要求显示列标题为员工姓名，工资收入，奖金收入，总收入。
select  ename,sal,comm,  sal*6+sal*1.2*6 from emp;

-- 1.查询员工表中一共有哪几种岗位类型
select distinct job from emp  ;

-- 2.分别查看员工表、部门表、薪资等级表的表结构。
desc emp;



-- 1.查询职位为SALESMAN的员工编号、职位、入职日期。
select empno,job,hiredate  from emp  where job = 'SALESMAN';

-- 2.查询1985年12月31日之前入职的员工姓名及入职日期。
select ename,hiredate  from emp  where hiredate < '1985-12-31';

-- 3.查询部门编号不在10部门的员工姓名、部门编号。
select ename,deptno  from emp  where not deptno =10;



-- 1.查询入职日期在82年至85年的员工姓名，入职日期。
select ename,hiredate  from emp  where hiredate between '1982-01-01' and '1987-12-31';  

-- 2.查询月薪在3000到5000的员工姓名，月薪。
select ename,sal  from emp  where sal between 3000 and 5000;

--  3.查询部门编号为10或者20的员工姓名，部门编号。
select ename,deptno  from emp  where deptno in (10,20);  -- where deptno =10 or deptno = 20;

-- 4.查询经理编号为7902, 7566, 7788的员工姓名，经理编号。
select ename,mgr  from emp  where mgr in (7902,7566,7788);  

-- 5.查询员工姓名以W开头的员工姓名。
select ename  from emp  where ename like 'W%';

-- 6.查询员工姓名倒数第2个字符为T的员工姓名。
select ename  from emp  where ename like '%T_';

-- 7.查询奖金为空的员工姓名，奖金。
select ename,comm  from emp  where comm is null;

 
 
-- 1.查询工资超过2000并且职位是MANAGER,或者职位是SALESMAN的员工姓名、职位、工资
select ename,job,sal  from emp  where (sal>2000 and job ='MANAGER') or job = 'SALESMAN';

-- 2.查询工资超过2000并且职位是 MANAGER或SALESMAN的员工姓名、职位、工资。
select ename,job,sal  from emp  where sal>2000 and (job ='MANAGER' and job ='SALSMAN');  

-- 3.查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。
select ename,deptno,sal  from emp  where deptno in (10,20) and sal between 3000 and 5000;  

-- 4.查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。
select ename,hiredate,job  from emp  where  hiredate between '1981-01-01' and '1981-12-31'  and job not like 'SALES%';  

-- 5.查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。
select ename,job,deptno  from emp  where job = 'SALESMAN' or job='MANAGER' and deptno in(10,20) and ename like '%A%'; 
 
 
 
-- 1.查询部门在20或30的员工姓名，部门编号，并按照工资升序排序。
select ename,deptno  from emp  where deptno in(10,20)  order by sal;

-- 2.查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。
select ename,deptno,sal  from emp  where sal in(2000,3000) and not deptno =10  order by deptno,sal desc;

-- 3.查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排。
select ename,hiredate,job  from emp  where (hiredate between '1981-1-1' and '1983-12-31') and (job like 'SALES%'or job like 'MAN%')  order by hiredate desc; 

-- 1. 查询入职日期最早的前5名员工姓名，入职日期。
select ename,hiredate from emp order by hiredate limit 0,5;

-- 2. 查询20号部门下入职日期最早的前2名员工姓名，入职日期。
select ename,hiredate,deptno from emp where deptno = 20 order by hiredate limit 0,2;

-- 3. 按照每页显示5条记录，分别查询第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门编号。
select ename,hiredate,deptno from emp limit 0,5;
select ename,hiredate,deptno from emp limit 5,5;
select ename,hiredate,deptno from emp limit 10,5;


-- 1.查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。
select ename,hiredate,job  from emp  where hiredate>'1982-7-9' and not job ='SALESMAN';

-- 2.查询员工姓名的第三个字母是a的员工姓名。
select ename  from emp  where ename like '__a%';

-- 3.查询除了10、20号部门以外的员工姓名、部门编号。
select ename,deptno  from emp  where deptno not  in (10,20);

-- 4.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。
select *  from emp  where deptno =30  order by sal desc,ename;

-- 5.查询没有上级管理的员工(经理号为空)的员工姓名、上级编号。
select ename,mgr  from emp  where mgr is null;

-- 6.查询工资大于等于4500并且部门为10或者20的员工的姓名、工资、部门编号。
select ename,sal,deptno  from emp  where sal>=4500 and deptno in (10,20);


-- 多表查询
-- 1.写一个查询，显示所有员工姓名，部门编号，部门名称。
select ename,emp.deptno,dname  from emp,dept where emp.deptno=dept.deptno;

-- 2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金
select ename,loc,comm 
from emp,dept where emp.deptno = dept.deptno 
and  loc = 'CHICAGO'  and comm  is not null ;

-- 3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点  
select ename,loc  from emp,dept  where emp.deptno = dept.deptno and ename like '%A%';


-- 1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。
select empno,ename,sal,grade,loc  from emp,dept,salgrade  where emp.deptno = dept.deptno and emp.sal between salgrade.losal and salgrade.hisal  order by grade;

-- 1. 查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号  
select a.ename,a.empno,b.ename,b.empno,d.loc 
 from emp a,emp b,dept d where a.mgr=b.empno and a.deptno = d.deptno 
and d.loc in('NEW YORK','CHICAGO');


-- 1.创建一个员工表和部门表的交叉连接。
select * from emp CROSS join dept;

-- 2.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期
select ename,dname,hiredate from emp NATURAL join dept   where  hiredate >'1980-01-05';  
 
-- 5.使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
select a.ename ,b.ename  from emp a , emp b  where a.mgr = b.empno; 
----------------------------  
select a.ename ,b.ename  from emp a left outer join emp b  on a.mgr = b.empno;       

-- 6.使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
select a.ename,b.ename  from emp a right outer join emp b  on a.empno=b.mgr;
 
 
-- 1.显示员工SMITH的姓名，部门名称，直接上级名称
select a.ename,a.deptno,b.ename  from emp a,emp b  where a.mgr=b.empno and a.ename ='SMITH';  

-- 2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。
select ename,dname,sal,grade  from emp,dept,salgrade  where emp.deptno=dept.deptno and sal between losal and hisal and grade>4;

-- 3.显示员工KING和FORD管理的员工姓名及其经理姓名。
select a.ename,b.ename,c.ename  from emp a LEFT JOIN emp b on a.empno=b.mgr LEFT JOIN emp c on a.mgr=c.empno where (a.ename='KING'or a.ename='FORD') ;

-- 4.显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。
select a.ename,a.hiredate,b.ename,b.hiredate  from emp a,emp b  where a.mgr=b.empno and a.hiredate<b.hiredate;
 
 
 
 
 
 
 
-- 高级查询
-- 1.查询部门20的员工，每个月的工资总和及平均工资
select deptno,sum(sal),avg(sal) from emp where deptno = 20 group by deptno

-- 2.查询工作在CHICAGO的员工人数，最高工资及最低工资
SELECT COUNT(m.empno) 员工人数, MAX(sal) 最高工资,MIN(sal) 最低工资 
FROM emp m,dept d
WHERE
m.deptno = d.DEPTNO
AND d.loc = 'CHICAGO';
 
-- 3.查询员工表中一共有几种岗位类型
SELECT COUNT(DISTINCT JOB) FROM emp;


-- 1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
SELECT 
d.DEPTNO 部门编号, 
d.DNAME 部门名称, 
COUNT(*) 部门人数, 
MAX(e.SAL) 最高工资, 
MIN(e.SAL) 最低工资, 
SUM(e.SAL) 工资总和, 
AVG(e.SAL) 平均工资 
FROM emp e, dept d 
WHERE e.DEPTNO = d.DEPTNO 
GROUP BY  e.DEPTNO, d.DNAME; 

-- 2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。
SELECT
d.DEPTNO 部门编号,
d.DNAME 部门名称,
e.JOB 岗位名称,
COUNT(*) 部门人数,
MAX(e.SAL) 最高工资,
MIN(e.SAL) 最低工资,
SUM(e.SAL) 工资总和,
AVG(e.SAL) 平均工资
FROM emp e,dept d
WHERE
e.DEPTNO = d.DEPTNO
GROUP BY
d.DEPTNO,d.DNAME, e.JOB; 

-- 3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。
SELECT
COUNT(*) 管理人数,
m.EMPNO 经理编号,
m.ENAME 经理姓名
FROM
emp w
LEFT OUTER JOIN emp m ON w.MGR = m.EMPNO
GROUP BY
m.EMPNO,m.ENAME 

-- 4.查询部门人数大于2的部门编号，部门名称，部门人数。
SELECT
e.DEPTNO 部门编号,
d.DNAME 部门名称,
COUNT(*) 部门人数
FROM
emp e,
dept d
WHERE
e.DEPTNO = d.DEPTNO
GROUP BY
e.DEPTNO
HAVING
COUNT(*) > 2 

-- 5查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。
SELECT
e.DEPTNO 部门编号,
d.DNAME 部门名称,
COUNT(*) 部门人数,
AVG(e.SAL) 部门平均工资
FROM
emp e,
dept d
WHERE
e.DEPTNO = d.DEPTNO
GROUP BY
e.DEPTNO
HAVING
(COUNT(*) > 2)
AND (AVG(e.SAL) > 2000)
ORDER BY COUNT(*)

-- 1查询入职日期最早的员工姓名，入职日期
SELECT
e.ename,
e.hiredate
FROM
emp e
WHERE
HIREDATE = (SELECT min(hiredate) FROM emp);

 
-- 2查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称 
SELECT e.ename,e.sal,d.dname
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND e.sal > (SELECT sal FROM emp WHERE ename = 'SMITH')
AND d.loc = 'CHICAGO'; 
 
-- 3查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期 
SELECT e.ename,e.hiredate
FROM emp e
WHERE e.hiredate < (
SELECT min(hiredate)
FROM emp WHERE deptno = 20)
 
 
 
 







 . 查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工
SELECT * from emp where HIREDATE>
any(SELECT HIREDATE from emp where deptno=10) 
AND deptno <>10;  


-- 2. 查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工
SELECT * from emp where HIREDATE>
all(SELECT HIREDATE from emp where deptno=10) 
AND deptno <>10;  

-- 3. 查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工
SELECT * from emp where JOB=
any(SELECT JOB from emp where deptno=10) 
AND deptno <>10;

-------------------------------------------------------------------
  -- 1. 查询 部门平均工资在2500元以上的 部门名称及平均工资。
SELECT
    d.dname,
    avg( e.sal ) 
FROM
    emp e,
    dept d 
WHERE
    e.deptno = d.deptno 
GROUP BY
    e.deptno 
HAVING
    avg( e.sal ) > 2500;

-- 2. 查询 员工岗位中不是以“SA”开头 并且 平均工资在2500元以上 的 岗位及平均工资，并按平均工资降序排序。
SELECT
    job,
    avg( sal ) 
FROM
    emp 
WHERE
    job NOT LIKE 'SA%' 
GROUP BY
    job 
HAVING
    avg( sal ) > 2500 
ORDER BY
    avg( sal ) DESC;
    
-- 3. 查询 部门人数在2人以上的 部门名称、最低工资、最高工资。
SELECT
    d.dname,
    min( e.sal ),
    max( e.sal ) 
FROM
    emp e,
    dept d 
WHERE
    e.deptno = d.deptno 
GROUP BY
    d.dname 
HAVING
    count( e.empno ) > 2;
    
-- 4. 查询 岗位不为SALESMAN，工资和大于等于2500的 岗位及每种岗位的工资和。
SELECT
    job,
    sum( sal ) 
FROM
    emp 
WHERE
    job <> 'SALESMAN' 
GROUP BY
    job 
HAVING
    sum( sal ) >= 2500;

-- 5. 显示 经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序
SELECT
    m.empno,
    m.ename,
    min( w.sal ) 
FROM
    emp w
    LEFT JOIN emp m ON w.mgr = m.empno 
GROUP BY
    m.empno,
    m.ename 
HAVING
    min( w.sal )>= 3000 
ORDER BY
    min( w.sal ) DESC;
    
-- 6. 查询 工资高于编号为7782的员工工资 并且 和7369号员工从事相同工作的员工的 编号、姓名及工资。
SELECT
    empno,
    ename,
    sal 
FROM
    emp 
WHERE
    sal > ( SELECT sal FROM emp WHERE empno = 7782 ) 
    AND 
    job = ( SELECT job FROM emp WHERE empno = 7369 );
    
-- 7. 查询 工资最高的 员工姓名和工资。
SELECT
    ename,
    sal 
FROM
    emp 
WHERE
    sal = ( SELECT max( sal ) FROM emp )
    
-- 8. 查询 部门最低工资高于 10号部门最低工资 的部门的编号、名称及部门最低工资。
SELECT
    e.deptno,
    d.dname,
    min( e.sal ) 
FROM
    emp e,
    dept d 
WHERE
    e.deptno = d.deptno 
GROUP BY
    e.deptno,
    d.dname 
HAVING
    min( e.sal ) > ( SELECT min( sal ) FROM emp WHERE deptno = 10 );
    
-- 9. 查询 员工工资为 其部门最低工资的  员工的编号和姓名及工资。
SELECT
    empno,
    ename,
    sal 
FROM
    emp 
WHERE
    ( deptno, sal ) IN ( SELECT deptno, min( sal ) FROM emp GROUP BY deptno );
    
-- 10. 显示 经理是KING的 员工姓名，工资。
SELECT
    ename,
    sal 
FROM
    emp 
WHERE
    mgr = ( SELECT empno FROM emp WHERE ename = 'KING' );

-- 11. 显示 比员工SMITH参加工作时间晚的 员工姓名，工资，参加工作时间。
SELECT
    ename,
    sal,
    hiredate 
FROM
    emp 
WHERE
    hiredate > ( SELECT hiredate FROM emp WHERE ename = 'SMITH' );




 
 
 -- 复制表
-- 1.通过子查询的方式创建一个表dept10,该表保存10号部门的员工数据。
CREATE TABLE dept10 
AS 
SELECT
    * 
FROM
    emp 
WHERE
    deptno = 10;
 
-- 2.创建与dept表相同表结构的表dtest，将dept表中部门编号在40之前的信息插入该表。
CREATE TABLE dtest AS 
SELECT
    * 
FROM
    dept 
WHERE
    deptno < 40;
 
 -- 3.创建与emp表结构相同的表empl，并将其部门编号为前30号的员工信息复制到empl表。
CREATE TABLE empl AS 
SELECT
    * 
FROM
    emp 
WHERE
    deptno < 30;
 



-- 插入表
-- 1.向部门表新增一个部门，部门编号为50，部门名称为HR，工作地点为SY。
insert into dept(deptno,dname,loc) 
values(50,'HR','SY');

-- 2.向部门表新增一个部门，部门编号为60，部门名称为MARKET
insert into dept(deptno,dname)
values (60,'MARKET')

-- 3.向员工表中新增一个员工，员工编号为8888，姓名为BOB，岗位为CLERK，经理为号7788，入职日期为1985-03-03，薪资3000，奖金和部门为空
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values('8888','BOB','CLERK','7788','1985-03-03','3000.00',null,null);

-- 4.使用CREATE TABLE emp_back as SELECT * FROM EMP WHERE 1=0，创建emp_back表,拷贝下来即可
CREATE TABLE emp_back as SELECT * FROM EMP WHERE 1=0;

-- 5.把emp表中入职日期大于1982年1月1日之前的员工信息复制到emp_back表中
insert into emp_back
select *from emp where hiredate<'1982-01-01';

-- 修改表
-- 1. 修改部门20的员工信息，把82年之后入职的员工入职日期向后调整10天
update emp
set hiredate = DATE_ADD(hiredate,INTERVAL 10 day)
where deptno = 20 and hiredate > '1982-12-31';
select * from emp where hiredate > '1982-12-31';

-- 2. 修改奖金为null的员工，奖金设置为0
update emp
set comm=0
where comm is null;

select *from emp
3. 修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500
update 
    emp
set 
    sal=sal+500
where 
    deptno in
(select 
    deptno
from 
  dept
where 
     loc='NEW YORK' or  loc='CHICAGO'
);
select *from emp; 

-- 删除表
1. 删除经理编号为7566的员工记录
delete from emp where mgr=7566;
select *from emp;

-- 2. 删除工作在NEW YORK的员工记录
delete from emp 
where deptno = 
(select deptno from dept where loc = 'NEW YORK');

-- 3. 删除工资大于所在部门平均工资的员工记录
delete from emp
where empno in
(
select 
  empno
from 
    (select * from emp) e 
where 
    e.sal >
    (select 
            avg(sal) 
     from 
            (select *from emp) e2 
     where 
            deptno = e.deptno)
)