#向部门表新增一个部门，部门编号为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)
