现有三张表数据表，如下：
员工信息表employee（empno员工编号，ename员工姓名，jobno职位编号，mgr经理编号，hiredate入职时间，deptno部门编号）
部门信息表dept（deptno部门编号，dname部门名称，loc部门所在地）
职位信息表job（jobno职位编号，jobname职位名称，sal工资）
1.员工“张三”已经离职，请编写SQL语句更新数据库
DELETE FROM employee WHERE ename='张三';
2.查询薪水大于2000，且职位是“经理”的员工信息
SELECT * FROM employee e JOIN job j ON e.jobno=j.jobno WHERE sal > 2000 AND jobname = '经理';
3.查询各个部门的每个职位的平均工资，并按照部门编号升序、平均工资降序排列
SELECT d.deptno, j.jobno, AVG(sal) gz FROM job j JOIN employee e ON j.jobno = e.jobno JOIN dept d ON e.deptno = d.deptno GROUP BY d.deptno, j.jobno ORDER BY d.deptno, gz DESC;
4.查询工资低于公司的平均工资的员工信息
SELECT * FROM employee e1 JOIN job j1 ON e1.jobno=j1.jobno WHERE j1.sal > (
SELECT AVG(j.sal) FROM employee e JOIN job j ON e.jobno = j.jobno
);
5.修改职位信息表中的sal属性(INT类型)，使其不允许为空
ALTER TABLE job CHANGE sal sal INT(10) NOT NULL;
6.查询所有员工的姓名、工资、部门编号、部门名称、部门地址
SELECT e.ename, j.sal, d.deptno, d.dname, d.loc FROM employee e JOIN job j ON e.jobno=j.jobno JOIN dept d ON d.deptno=e.deptno;
7.查询员工的入职年限（按一年365天计算，取整，四舍五入）
SELECT empno,ename,hiredate,ROUND(DATEDIFF(CURDATE(), hiredate) / 365) AS work_years FROM employee;
8.查询薪水大于2000，且职位是“经理”的员工信息
SELECT * FROM employee e JOIN job j ON j.jobno=e.jobno WHERE j.sal>2000 AND j.jobname='经理';
9.查询年薪大于30000，且职位不是“经理”、部门号不是10或40的员工信息，要求按照年薪进行倒序排列
SELECT *, sal*12 yearsal FROM employee e JOIN job j ON j.jobno=e.jobno JOIN dept d ON d.deptno=e.deptno WHERE sal*12>30000 AND NOT j.jobname='经理' AND d.deptno NOT IN (10, 40) ORDER BY sal DESC;
10.查询每个部门的平均工资，并按部门编号降序排列
SELECT AVG(sal), d.deptno,d.dname FROM employee e JOIN job j ON e.jobno=j.jobno JOIN dept d ON d.deptno=e.deptno GROUP BY d.deptno ORDER BY d.deptno DESC;
1.订单表(oid 订单id,time下单时间,total 金额,goodName商品名名稱，uid)
2 用戶表(uid 用戶表 ， name 姓名 , city城市 ,性別)
1.計算出男女的人數
SELECT gender, COUNT(*) FROM user GROUP BY USER.gender;
2.計算出每個城市下單的數量，由高到低
SELECT u.city, COUNT(oid) co FROM orderlist o JOIN USER u ON o.uid=u.uid GROUP BY u.city ORDER BY co DESC;
3.計算出每個用戶下單的數量，高到低排序
SELECT u.uid, u.name, COUNT(o.oid) co FROM USER u JOIN orderlist o ON o.uid=u.uid GROUP BY u.uid ORDER BY co DESC;
4.計算出又沒下單的用戶user
SELECT * FROM USER u WHERE u.uid NOT IN ( SELECT o.uid FROM orderlist o );
5.計算出每天下單的數量
SELECT DATE(o.time) date, COUNT(oid) FROM orderlist o GROUP BY DATE ORDER BY date;