mysql基础练习二：

-- 数据准备
DROP TABLE IF EXISTS emp;
CREATE TABLE IF NOT EXISTS emp(
    empno INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(10) NOT NULL,
    job VARCHAR(20),
    salary DOUBLE(8,2),
    mgr INT(10),
    bonus DOUBLE(6,2),
    hiredate DATE,
    deptno INT(10)
);

CREATE TABLE `department` (
  `id` INT ,
  `dep_name` VARCHAR(20) DEFAULT NULL,
  `dep_location` VARCHAR(20) DEFAULT NULL
);

INSERT  INTO `department`(`id`,`dep_name`,`dep_location`) VALUE 
(10,'销售部','广州'),
(20,'财务部','深圳'),
(30,'管理部','深圳'),
(40,'研发部','深圳'),
(50,'业务部','广州');

INSERT INTO emp VALUES(NULL,'mary','sales',7000,6,1000,'2014-1-1',10);
INSERT INTO emp VALUES(NULL,'lily','sales',6000,1,800,'2014-5-1',10);
INSERT INTO emp VALUES(NULL,'tom','sales',5000,1,4000,'2014-3-1',10);
INSERT INTO emp VALUES(NULL,'james','account',8000,6,NULL,'2014-2-1',20);
INSERT INTO emp VALUES(NULL,'scott','teaching',8000,6,3000,'2014-1-20',30);
INSERT INTO emp VALUES(NULL,'tom','BOSS',38000,NULL,NULL,'2013-1-20',30);
INSERT INTO emp VALUES(NULL,'kitty','teaching',7000,5,700,'2014-5-20',30);
INSERT INTO emp VALUES(NULL,'kitty','teaching',6000,5,500,'2014-6-20',30);
INSERT INTO emp VALUES(NULL,'green','analyst',15000,6,1000,'2014-2-20',40);
INSERT INTO emp VALUES(NULL,'brown','analyst',12000,9,7000,'2014-4-20',40);
INSERT INTO emp VALUES(NULL,'danis','department',3000,6,800,'2014-3-4',50);
INSERT INTO emp VALUES(NULL,'brown','department',1800,11,600,'2014-4-20',50);
INSERT INTO emp VALUES(NULL,'smith','department',1200,11,500,'2014-5-20',50);

-- 1：查询EMP表的全部职工的EMPNO、ENAME和JOB。
SELECT empno,ename,job
FROM emp
WHERE job!='BOSS';

-- 2：查询EMP表的全部职工的EMPNO、ENAME和JOB，按salary升序次序排列。
-- 按照某个字段进行排序，不需要将其显示，一定要确认查询的表中存在这个字段
SELECT empno,ename,job
FROM emp
WHERE job!='BOSS'
ORDER BY salary;

-- 3：查询EMP表的全部列，列的次序为：JOB，SALARY，ENAME，EMPNO，MGR，HIREDATE，BONUS，DEPTNO，查询结果按年薪降序。
SELECT job,salary,ename,empno,mgr,hiredate,bonus,deptno
FROM emp
ORDER BY (salary*12+IFNULL(bonus,0)) DESC;

-- 4：列出EMP表中的不同的JOB名称，
SELECT DISTINCT job
FROM emp
WHERE job!='BOSS';

-- 5：查询在部门10中工作，其工资高于6000的职工信息。
SELECT *
FROM emp
WHERE deptno=10
AND salary>6000;

-- 6：列出其JOB为MANAGER或ANALYST的职工名及职工号
SELECT empno,ename
FROM emp
WHERE job IN
('MANAGER','ANALYST');

-- 7：列出工资在5500至20000之间的职工名字、职工号。使用两种方式实现
-- 第一种
SELECT empno,ename
FROM emp
WHERE salary BETWEEN 5500 AND 20000;

-- 第二种
SELECT empno,ename
FROM emp
WHERE salary >= 5500 AND salary<=20000;

-- 8：查出工资为6k的人员所在的部门名称和地址。
SELECT b.`dep_name`,b.`dep_location`
FROM emp a,department b
WHERE a.`deptno`=b.`id`
AND a.salary=6000;

-- 9：查出以W开头或以S结尾的职工名的职工信息。
SELECT *
FROM emp
WHERE ename LIKE "w%" 
OR ename LIKE "%s";

-- 10：查出以k开头或以y结尾的职工信息和部门信息。
SELECT a.*,b.`dep_name`,b.`dep_location`
FROM emp a,department b
WHERE a.`deptno`=b.`id`
AND 
(ename LIKE "k%" OR ename LIKE "%y");

-- 11：列出没有奖金的职工信息。
SELECT *
FROM emp
WHERE empno = IFNULL(bonus,empno);

-- 12：查询工资最高的人的信息以及部门信息
SELECT a.*,b.`dep_name`,b.`dep_location`
FROM emp a,department b
WHERE a.`deptno`=b.`id`
ORDER BY salary
LIMIT 1;

-- 13：查询每个部门的平均工资，部门名称，部门编号，并按照平均工资倒序排列
SELECT ROUND(AVG(a.salary),2) 平均工资,b.`dep_name`,b.`id`
FROM emp a
JOIN department b
ON a.`deptno`=b.`id`
GROUP BY deptno
ORDER BY 平均工资 DESC;

-- 14：查询每个部门的平均工资，按照部门编号，部门名称，平均薪资顺序显示，
-- 低于5000的不要求显示，并按照平均工资倒序排列
SELECT b.`id`,b.`dep_name`,ROUND(AVG(a.salary),2) 平均工资
FROM emp a
LEFT JOIN department b
ON a.`deptno`=b.`id`
GROUP BY deptno;

-- 15：查询‘lily’的领导信息
SELECT a.`ename` 员工,b.`ename` 领导
FROM emp a
JOIN emp b
ON a.`mgr`=b.`empno`
AND a.`ename`='lily';

-- 16：查詢在上海办公的人员信息
SELECT a.*
FROM emp a
JOIN department b
ON a.`deptno`=b.`id`
AND b.`dep_location`='上海';

-- 17：列出所有部门的详细信息和部门人数。
SELECT b.*,COUNT(1) 人数
FROM emp a
RIGHT JOIN department b
ON a.`deptno`=b.`id`
GROUP BY b.`id`;

-- 18：查询出所有薪水在'james'之上的所有人员信息，并按照工资降序排列
SELECT *
FROM emp
WHERE salary >
    (SELECT salary 
    FROM emp
    WHERE ename='james')
ORDER BY salary DESC;

-- 19：查询出所有有奖金的人员信息，并按照奖金的多少进行降序排列
SELECT *
FROM emp
WHERE bonus IS NOT NULL
ORDER BY bonus DESC;

-- 20：查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。
SELECT MAX(a.salary) , MIN(a.salary),b.*
FROM emp a
JOIN department b
ON a.`deptno`=b.`id`
AND b.`id`!=10
GROUP BY deptno;

-- 21：查询所有mary下属的信息和工作地点，并且按照他们的工资降序排列
SELECT t1.*, t2.`dep_location`
FROM 
    (SELECT * 
     FROM emp
     WHERE mgr=
        (SELECT empno
         FROM emp
         WHERE ename='mary')) t1
JOIN department t2
ON t1.`deptno`=t2.`id`
ORDER BY t1.salary DESC;