-- 使用mydb2數(shù)據(jù)庫
use mydb2;
-- 創(chuàng)建員工表
CREATE TABLE emp(
empid int PRIMARY KEY auto_increment,
empname VARCHAR(20),
empjob VARCHAR(30),
mgr int,
salary double,
comm double,
empdepartment int
)auto_increment=1001;
-- 插入員工數(shù)據(jù)
INSERT INTO emp VALUES(NULL,'張華','銷售',0000,4300,2300,21);
INSERT INTO emp VALUES(NULL,'張磊','銷售',0000,4300,2300,21);
INSERT INTO emp VALUES(NULL,'劉海','公關(guān)',0001,5200,2300,22);
INSERT INTO emp VALUES(NULL,'湖流','財務(wù)',0002,7000,2300,23);
INSERT INTO emp VALUES(NULL,'劉備','董事',0003,10000,2300,24);
INSERT INTO emp VALUES(NULL,'司馬懿','主管',0004,6300,2300,25);
INSERT INTO emp VALUES(NULL,'諸葛亮','銷售',0000,4300,2300,21);
INSERT INTO emp VALUES(NULL,'典韋','公關(guān)',0001,5200,2300,22);
INSERT INTO emp VALUES(NULL,'雅典娜','財務(wù)',0002,7000,2300,23);
INSERT INTO emp VALUES(NULL,'上官婉兒','市場',0005,3400,2300,26);
INSERT INTO emp VALUES(NULL,'張飛','市場',0005,3400,2300,26);
-- 按照員工編號升序排列部門序號不為21
SELECT *FROM emp where empdepartment!=21 ORDER BY empdepartment;
-- 查詢姓劉的員工且工資高于1000
SELECT *FROM emp where empname like '劉%'and salary>1000;
-- 求每個部門的平均薪水升序
SELECT empdepartment,avg(salary) AS avg_salary FROM emp GROUP BY empdepartment ORDER BY avg_salary;
-- 求各個部門的最高薪水
SELECT empdepartment,max(salary) as max_salary FROM emp GROUP BY empdepartment ORDER BY max_salary;
-- 查詢每個部門的每個崗位最高薪水
SELECT empdepartment,empjob,max(salary) FROM emp GROUP BY empdepartment,empjob;
-- 求平均薪水大于2000的部門
SELECT empdepartment,avg(salary) as avg_sal FROM emp GROUP BY empdepartment having avg_sal>2000;
-- 將部門平均薪水大于1500的部門列出了卑笨，按照部門平均薪水進行降序
SELECT empdepartment,avg(salary) as avg_sal FROM emp GROUP BY empdepartment HAVING avg_sal>1500 ORDER BY avg_sal DESC;
-- 查詢公司最高薪水和最低薪水之間的差距
SELECT max(salary)-min(salary) FROM emp;
