MySQL经典案例

最新推荐文章于 2024-07-24 16:11:19 发布

Day-yong

最新推荐文章于 2024-07-24 16:11:19 发布

阅读量1w

点赞数 10

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Daycym/article/details/79432118

版权

MySQL 专栏收录该内容

2 篇文章 1 订阅

订阅专栏

MySQL经典案例

数据表以及数据（案例用到的表以及数据，在一下案例前，需在数据库中创建的表以及需插入的数据）

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
    DNAME VARCHAR(14) ,
    LOC VARCHAR(13),
    primary key (DEPTNO)
    );
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INT(4),
    HIREDATE DATE  DEFAULT NULL,
    SAL DOUBLE(7,2),
    COMM DOUBLE(7,2),
    primary key (EMPNO),
    DEPTNO INT(2) 
    )
    ;

CREATE TABLE SALGRADE
      ( GRADE INT,
    LOSAL INT,
    HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;

INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

1. 取得每个部门最高薪水的人员名称

步骤:

第一步：求出每个部门的最高薪水

select
    e.deptno,max(e.sal) as maxsal
from
    emp e
group by
    e.deptno;

这里写图片描述

第二步：将以上查询的结果当成一个临时表t(deptno,maxsal)

select
    e.deptno,e.ename,t.maxsal,e.sal
from 
    (select
        e.deptno,max(e.sal) as maxsal
    from
        emp e
    group by
        e.deptno) t
join
    emp e
on
    t.deptno = e.deptno
where
    t.maxsal = e.sal
order by 
    e.deptno;

这里写图片描述

2. 哪些人的薪水在部门平均薪水之上

步骤

第一步：求出每个部门的平均薪水

select
    e.deptno,avg(e.sal) as avgsal
from
    emp e
group by
    e.deptno;

这里写图片描述

第二步：将以上查询的结果当成临时表t(deptno,avgsal)

select
    t.deptno,e.ename
from 
    (select
        e.deptno,avg(e.sal) as avgsal
    from
        emp e
    group by
        e.deptno) t
join
    emp e
on
    t.deptno = e.deptno
where
    e.sal > t.avgsal;

这里写图片描述

3. 取得部门中（所有人的）平均薪水等级

3.1 取得部门中所有人的平均薪水的等级

步骤:

第一步：求出每个部门的平均薪水

select
    e.deptno,avg(e.sal) as avgsal
from
    emp e
group by
    e.deptno;

这里写图片描述

select * from salgrade;

这里写图片描述

第二步：将以上查询的结果当成一个临时表t(deptno,avgsal)

select
    t.deptno,t.avgsal,s.grade
from 
    (select
        e.deptno,avg(e.sal) as avgsal
    from
        emp e
    group by
        e.deptno) t
join
    salgrade s
on
    t.avgsal between s.losal and s.hisal;

这里写图片描述

3.2 取得部门中所有人的平均的薪水等级

步骤:

第一步：求出每个人的薪水等级

select
    e.deptno,e.ename,s.grade
from
    emp e
join
    salgrade s
on
    e.sal between s.losal and s.hisal;

这里写图片描述

第二步：将以上查询的结果当成一个临时表t(deptno,ename,grade)

select
    t.deptno,avg(t.grade) as avgGrade
from 
    (select
        e.deptno,e.ename,s.grade
    from
        emp e
    join
        salgrade s
    on
        e.sal between s.losal and s.hisal) t
group by 
    t.deptno;

这里写图片描述

4. 不准用组函数（MAX），取得最高薪水（给出两种方案）

4.1 方案1

select sal from emp order by sal desc limit 1;

这里写图片描述

4.2 方案2

select
    distinct a.sal
from
    emp a
join
    emp b
on
    a.sal < b.sal;

这里写图片描述

select 
    sal 
from 
    emp 
where 
    sal not in(select
        distinct a.sal
    from
        emp a
    join
        emp b
    on
        a.sal < b.sal);

这里写图片描述

5. 取得平均薪水最高的部门的部门编号

步骤:

第一步：求出每个部门平均薪水

select
    e.deptno,avg(e.sal) as avgsal
from
    emp e
group by
    e.deptno;

这里写图片描述

第二步：将以上查询结果当成临时表t(deptno,avgsal)

select 
    max(t.avgsal) as maxAvgsal
from
    (select
        e.deptno,avg(e.sal) as avgsal
    from
        emp e
    group by
        e.deptno) t;

这里写图片描述

第三步

select
    e.deptno,avg(e.sal) as avgsal
from
    emp e
group by
    e.deptno
having
    avgsal = (select 
                max(t.avgsal) as maxAvgsal
              from
                (select
                    e.deptno,avg(e.sal) as avgsal
                from
                    emp e
                group by
                    e.deptno) t);

这里写图片描述

6. 取得平均薪水最高的部门的名称

select
        e.deptno,d.dname,avg(e.sal) as avgsal
    from
        emp e
    join
        dept d
    on
        e.deptno = d.deptno
    group by
        e.deptno,d.dname
    having
        avgsal = (select 
                    max(t.avgsal) as maxAvgsal
                  from
                    (select
                        e.deptno,avg(e.sal) as avgsal
                    from
                        emp e
                    group by
                        e.deptno) t);

这里写图片描述

7. 求平均薪水的等级最低的部门的部门名称

第一步：部门的平均薪水

select
    e.deptno,d.dname,avg(e.sal) as avgsal
from
    emp e
join
    dept d
on
    e.deptno = d.deptno
group by
    e.deptno,d.dname;

这里写图片描述

第二步：将以上查询结果当成临时表t(deptno,avgsal)与salgrade表进行表连接：t.avgsal between s.losal and s.hisal;

select
    t.deptno,t.dname,s.grade
from
    (select
        e.deptno,d.dname,avg(e.sal) as avgsal
    from
        emp e
    join
        dept d
    on
        e.deptno = d.deptno
    group by
        e.deptno,d.dname) t
join
    salgrade s
on
    t.avgsal between s.losal and s.hisal;

这里写图片描述

第三步：将以上查询结果当成一张临时表t

select 
    min(t.grade) as minGrade
from (select
        t.deptno,t.dname,s.grade
      from
        (select
            e.deptno,d.dname,avg(e.sal) as avgsal
        from
            emp e
        join
            dept d
        on
            e.deptno = d.deptno
        group by
            e.deptno,d.dname) t
        join
            salgrade s
        on
            t.avgsal between s.losal and s.hisal) t;

这里写图片描述

第四步

select
        t.deptno,t.dname,s.grade
from
    (select
        e.deptno,d.dname,avg(e.sal) as avgsal
    from
        emp e
    join
        dept d
    on
        e.deptno = d.deptno
    group by
        e.deptno,d.dname) t
join
    salgrade s
on
    t.avgsal between s.losal and s.hisal
where
    s.grade = 3;

这里写图片描述

8. 取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名

第一步：找出普通员工（员工代码没有在mgr上出现的）

//1.1 先找出mgr有哪些人
select distinct mgr from emp;

这里写图片描述

//1.2
select * 
from 
    emp 
where
    empno not in(select distinct mgr from emp);
//not in 不会自动忽略空值
//in 会自动忽略空值
select * 
from 
    emp 
where
    empno in(select distinct mgr from emp);

这里写图片描述

//手动忽略空值
select * 
from 
    emp 
where
    empno not in(select distinct mgr from emp where mgr is not null);

这里写图片描述

select 
    max(sal) as maxsal 
from 
    emp 
where
    empno not in(select distinct mgr from emp where mgr is not null);

这里写图片描述

select 
    ename 
from 
    emp 
where 
    sal > (select 
            max(sal) as maxsal 
          from 
            emp 
          where
            empno not in(select distinct mgr from emp where mgr is not null));

这里写图片描述

9. 取得薪水最高的前五名员工

select
    *
from
    emp
order by
    sal desc
limit 0,5;

这里写图片描述

10. 取得薪水最高的第六到第十的员工

select
    *
from
    emp
order by
    sal desc
limit 5,5;

这里写图片描述

11. 取得最后入职的5名员工

select
    *
from
    emp
order by
    hiredate desc
limit 5;

这里写图片描述

12. 取得每个薪水等级有多少员工

第一步：查询每个员工的薪水等级

select
    e.ename,s.grade
from
    emp e
join
    salgrade s
on
    e.sal between s.losal and s.hisal
order by
    s.grade;

这里写图片描述

第二步：将以上结果当成临时表t(ename,grade)

select
    t.grade,count(t.ename) as totalEmp
from
    (select
        e.ename,s.grade
    from
        emp e
    join
        salgrade s
    on
        e.sal between s.losal and s.hisal) t
group by
    t.grade;

这里写图片描述

13.

有三个表s（学生表）、c（课程表）、sc（学生选课表）

S（SNO,SNAME）代表（学号，姓名）
C（CNO,CNAME,CTEACHER）代表（课号，课名，教师）
SC（SNO,CNO,SCFRADE）代表（学号，课号，成绩）

问题

找出没选过“黎明”老师的所有学生姓名

列出2门以上（含两门）不及格学生姓名及平均成绩

即学过1号课程又学过2号课程所有学生的姓名

create table s(
    sno int(4) primary key auto_increment,
    sname varchar(32)
);

insert into s(sname) values ('zhangsan');
insert into s(sname) values ('lisi');
insert into s(sname) values ('wangwu');
insert into s(sname) values ('zhaoliu');

create table c(
    cno int(4) primary key auto_increment,
    cname varchar(32),
    cteacher varchar(32)
);

insert into c(cname,cteacher) values ('Java','吴老师');
insert into c(cname,cteacher) values ('C++','王老师');
insert into c(cname,cteacher) values ('C##','张老师');
insert into c(cname,cteacher) values ('MySQL','郭老师');
insert into c(cname,cteacher) values ('Oracle','黎明');

create table sc(
    sno int(4),
    cno int(4),
    scgrade double(3,1),
    constraint sc_sno_cno_pk primary key(sno,cno),
    constraint sc_sno_fk foreign key(sno) references s(sno),
    constraint sc_cno_fk foreign key(cno) references c(cno)
);

insert into sc(sno,cno,scgrade) values (1,1,30);
insert into sc(sno,cno,scgrade) values (1,2,50);
insert into sc(sno,cno,scgrade) values (1,3,80);
insert into sc(sno,cno,scgrade) values (1,4,90);
insert into sc(sno,cno,scgrade) values (1,5,70);

insert into sc(sno,cno,scgrade) values (2,2,80);
insert into sc(sno,cno,scgrade) values (2,3,50);
insert into sc(sno,cno,scgrade) values (2,4,70);
insert into sc(sno,cno,scgrade) values (2,5,80);

insert into sc(sno,cno,scgrade) values (3,1,60);
insert into sc(sno,cno,scgrade) values (3,2,70);
insert into sc(sno,cno,scgrade) values (3,3,80);

insert into sc(sno,cno,scgrade) values (1,3,50);
insert into sc(sno,cno,scgrade) values (1,4,80);

这里写图片描述

13.1 找出没选过“黎明”老师的所有学生姓名

先找出选过黎明老师的学生编号–> 黎明老师授课的编号

select cno from c where cteacher = '黎明';

这里写图片描述

select sno from sc where cno = (select cno from c where cteacher = '黎明');

这里写图片描述

select * from s where sno not in(select sno from sc where cno = (select cno from c where cteacher = '黎明'));

这里写图片描述

13.2 列出2门以上（含两门）不及格学生姓名及平均成绩

//t1
select 
    sc.sno,s.sname,count(*) as studentNum
from 
    sc
join 
    s
on
    sc.sno = s.sno
where
    scgrade < 60
group by 
    sc.sno,s.sname
having
    studentNum >= 2;

这里写图片描述

//t2
select
    sc.sno,avg(sc.scgrade) as avgscgrade
from
    sc
group by
    sc.sno;

这里写图片描述

select
    t1.sname,t2.avgscgrade
from
    (select 
        sc.sno,s.sname,count(*) as studentNum
    from 
        sc
    join 
        s
    on
        sc.sno = s.sno
    where
        scgrade < 60
    group by 
        sc.sno,s.sname
    having
        studentNum >= 2) t1
join
    (select
        sc.sno,avg(sc.scgrade) as avgscgrade
    from
        sc
    group by
        sc.sno) t2
on
    t1.sno = t2.sno;

这里写图片描述

13.3 即学过1号课程又学过2号课程所有学生的姓名

select sno from sc where cno = 1;
select sno from sc where cno = 2;

select 
    s.sname
from 
    sc 
join
    s
on
    sc.sno = s.sno
where 
    cno = 1 and sc.sno in (select sno from sc where cno = 2);

这里写图片描述

14. 列出所有员工及领导的名字

select
    e.ename,b.ename as leadername
from
    emp e
left join
    emp b
on
    e.mgr = b.empno;

这里写图片描述

15. 列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称

select
    d.dname,e.empno,e.ename
from
    emp e
join
    emp b
on
    e.mgr = b.empno
join
    dept d
on  
    e.deptno = d.deptno
where
    e.hiredate < b.hiredate;

这里写图片描述

16. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select
    d.dname,e.*
from
    emp e
right join
    dept d
on
    e.deptno = d.deptno;

这里写图片描述

17. 列出至少有5个员工的所有部门

select 
    e.deptno,count(e.ename) as totalEmp
from
    emp e
group by
    e.deptno
having
    totalEmp >= 5;

这里写图片描述

18. 列出薪水比’SMITH’多的所有员工信息

select
    sal
from
    emp
where
 ename = 'SMITH';

这里写图片描述

select
    *
from
    emp
where
    sal > (select
                sal
            from
                emp
            where
                ename = 'SMITH'
        );

这里写图片描述

19. 列出所有’CLERK’（办事员）的姓名及其部门名称，部门人数

//t1
select 
    d.deptno,d.dname,e.ename
from
    emp e
join
    dept d
on
    e.deptno = d.deptno
where
    e.job = 'CLERK';

这里写图片描述

求出每个部门的员工数量

//t2
select
    e.deptno,count(e.ename) as totalEmp
from
    emp e
group by
    e.deptno;

这里写图片描述

select
    t1.ename,t1.dname,t2.totalEmp
from
    (select 
        d.deptno,d.dname,e.ename
    from
        emp e
    join
        dept d
    on
        e.deptno = d.deptno
    where
        e.job = 'CLERK') t1
join
    (select
        e.deptno,count(e.ename) as totalEmp
    from
        emp e
    group by
        e.deptno) t2
on
    t1.deptno = t2.deptno;

这里写图片描述

20. 列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数

求出每种工作岗位的最低薪水

select
    e.job,min(e.sal) as minsal
from
    emp e
group by
    e.job;

这里写图片描述

select
    e.job,min(e.sal) as minsal,count(e.ename) as totalEmp
from
    emp e
group by
    e.job
having
    minsal > 1500;

这里写图片描述

21. 列出在部门’SALES’<销售部>工作的员工姓名，假定不知道销售部门的部门编号

select 
    deptno
from
    dept
where
    dname = 'SALES';

这里写图片描述

select
    ename
from
    emp
where
    deptno = (select 
                deptno
            from
                dept
            where
                dname = 'SALES');

这里写图片描述

22. 列出薪金高于公司平均薪水的所有员工，所在部门，上级领导，雇员的工资等级

第一步：求出公司的平均薪水

select
    avg(sal) as avgsal
from
    emp;

这里写图片描述

select
    d.dname,e.ename,b.ename as leadername,s.grade
from
    emp e
join
    dept d
on
    e.deptno = d.deptno
left join
    emp b
on
    e.mgr = b.empno
join
    salgrade s
on
    e.sal between s.losal and s.hisal
where
    e.sal > (select
                avg(sal) as avgsal
            from
                emp);

这里写图片描述

23. 列出与’SCOTT’从事相同工作的所有员工及部门名称

查询出’SCOTT’的工作岗位

select job from emp where ename = 'SCOTT';

这里写图片描述

select
    d.dname,e.*
from
    emp e
join
    dept d
on
    e.deptno = d.deptno
where
    e.job = (select job from emp where ename = 'SCOTT');

这里写图片描述

24. 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

select sal from emp where deptno = 30;

这里写图片描述

select distinct sal from emp where deptno = 30;

这里写图片描述

select ename,sal from where sal in(select distinct sal from emp where deptno = 30) and deptno <> 30;

25. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称

先找出部门30的最高薪水

select max(sal) as maxsal from emp where deptno = 30;

这里写图片描述

select
    d.dname,e.ename,e.sal
from
    emp e
join
    dept d
on
    e.deptno = d.deptno
where
    e.sal > (select max(sal) as maxsal from emp where deptno = 30);

这里写图片描述

26. 列出在每个部门工作的员工数量、平均工资和平均服务期限

to_days(日期类型) ->获取天数
获取数据库的系统当前时间的函数是：now()

//从公元0年到现在所经历的天数
select to_days(now());

这里写图片描述

select ename,(to_days(now()) - to_days(hiredate))/365 as serveryear from emp;

这里写图片描述

select avg(to_days(now()) - to_days(hiredate))/365 as avgserveryear from emp;

这里写图片描述

select
    e.deptno,
    count(e.ename) as totalEmp,
    avg(e.sal) as avgsal,
    avg(to_days(now()) - to_days(hiredate))/365 as serveryear
from
    emp e
group by
    e.deptno;

这里写图片描述

27. 列出所有员工的姓名、部门名称和工资

select
    d.dname,
    e.ename,
    e.sal
from
    emp e
right join
    dept d
on
    e.deptno = d.deptno;

这里写图片描述

28. 列出所有部门的详细信息和人数

select
    d.deptno,d.dname,d.loc,count(e.ename) as totalEmp
from
    emp e
right join
    dept d
on
    e.deptno = d.deptno
group by
    d.deptno,d.dname,d.loc;

这里写图片描述

29. 列出各工作的最低工资及从事此工作的雇员姓名

select
    e.job,min(sal) as minsal
from
    emp e
group by
    e.job;

这里写图片描述

将以上查询结果当成临时表t(job,minsal)

select
    e.ename
from
    emp e
join
    (select
        e.job,min(sal) as minsal
    from
        emp e
    group by
        e.job) t
on
    e.job = t.job
where
    e.sal = t.minsal;

这里写图片描述

30. 列出各个部门MANAGER的最低薪金

select 
    e.deptno,min(e.sal) as minsal
from
    emp e
where
    e.job = 'MANAGER'
group by
    e.deptno;

这里写图片描述

31. 列出所有员工的年工资，按年薪从低到高排序

select ename,(sal+ifnull(comm,0))*12 as yearsal from emp group by yearsal;

这里写图片描述

32. 求出员工领导的薪水超过3000的员工名称和领导名称

select
    e.ename,b.ename as leadername
from
    emp e
join
    emp b
on
    e.mgr = b.empno
where
    b.sal > 3000;

这里写图片描述

33. 求部门名称中带’S’字符的部门员工的工资合计、部门人数

select
    d.dname,
    sum(e.sal) as sumsal,
    count(e.ename) as totalEmp
from 
    emp e
join
    dept d
on
    e.deptno = d.deptno
where
    d.dname like '%s%'
group by
    d.dname;

这里写图片描述

34. 给任职日期超过30年的员工加薪10%

create table emp_bak as select * from emp;
select * from emp_bak;

这里写图片描述

update emp_bak set sal = sal * 1.1 where (to_days(now()) - to_days(hiredate))/365 > 30;
select * from emp_bak;

这里写图片描述

Day-yong

关注

10
点赞
踩
52

收藏

觉得还不错? 一键收藏
0
评论
MySQL经典案例

MySQL经典案例数据表以及数据（案例用到的表以及数据，在一下案例前，需在数据库中创建的表以及需插入的数据）DROP TABLE IF EXISTS EMP;DROP TABLE IF EXISTS DEPT;DROP TABLE IF EXISTS SALGRADE;CREATE TABLE DEPT (DEPTNO int(2) not null , ...
复制链接

扫一扫

专栏目录