mysql

#向部门表新增一个部门，部门编号为50，部门名称为HR，工作地点为SY。

INSERT INTO dept SET(50,'HR','SY');

#向部门表新增一个部门，部门编号为60，部门名称为MARKET

INSERT INTO dept SET(60,'MARKET');

#向员工表中新增一个员工，员工编号为8888，姓名为BOB，岗位为CLERK，经理为号7788，入职日期为1985-03-03，薪资3000，奖金和部门为空。

INSERT INTO emp SET(8888,'BOB','CLERK',7788,'1985-03-03',3000,NULL,NULL);

#使用CREATE TABLE emp_back as

        SELECT * FROM EMP WHERE 1=0，创建emp_back表,拷贝下来即可。

CREATE TABLE emp_back as

        SELECT * FROM EMP WHERE 1=0

#把emp表中入职日期大于1982年1月1日之前的员工信息复制到emp_back表中

CREATE TABLE emp_back as SELECT * FROM emp WHERE HIREDATE>'1982-01-01';

#修改部门20的员工信息，把82年之后入职的员工入职日期向后调整10天

UPDATE dept SET hiredate=DATE_ADD(hiredate,INTERVAL 10 DAY)

WHERE deptno=20 AND hiredate>'1982-01-01'

#修改奖金为null的员工，奖金设置为0

UPDATE emp SET comm=0

WHERE comm IS null;

#修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500

UPDATE emp SET sal=sal+500 WHERE deptno=(SELECT DEPTNO FROM dept WHERE loc='NEW YORK''CHICAGO')

.删除经理编号为7566的员工记录

DELETE FROM emp WHERE MGR=7566;

2.删除工作在NEW YORK的员工记录

DELETE FROM EMP

WHERE deptno=(SELECT deptno FROM dept WHERE loc='NEW YORK' );

SELECT * FROM dept

3.删除工资大于所在部门平均工资的员

select deptno,avg(sal)

from emp

group by deptno

select empno

from emp e,(select deptno,avg(sal) c from emp group by deptno) b

where e.deptno=b.deptno and e.sal>b.c

delete from emp_back

where empno in(select empno

from emp e,(select deptno,avg(sal) c

from emp

group by deptno) b

where e.deptno=b.deptno and e.sal>b.c)