//建表语句
CREATE TABLE `department` (
`dept_id` INT(11) NOT NULL AUTO_INCREMENT,
`dept_name` VARCHAR(16) NOT NULL,
PRIMARY KEY (`dept_id`)
) ENGINE=MYISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
//插入语句
INSERT INTO `department`(`dept_id`,`dept_name`) VALUES (1,'行政部'),(2,'财务部'),(3,'研发部'),(4,'气象与海洋事业部');
C:/Users/Administrator/AppData/Local/YNote/data/qqC6291E4C3805B7FFE62627719EE86C2D/249ee4d11e1c4b569e6c93f0169ba7f6/wps26a5.tmp.jpeg
//建表语句CREATE TABLE `employee` (
`emp_id` INT(11) NOT NULL AUTO_INCREMENT,
`emp_name` VARCHAR(16) NOT NULL,
`dept_id` INT(11) NOT NULL,
`emp_wage` DOUBLE NOT NULL,
PRIMARY KEY (`emp_id`)
) ENGINE=MYISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
//插入语句
INSERT INTO `employee`(`emp_id`,`emp_name`,`dept_id`,`emp_wage`) VALUES (1,'王晗',1,6300),(4,'郝跃',2,4500),(5,'李策',2,5000),(6,'宋利鹏',2,4500),(7,'刘洋',3,9000),(8,'赵海东',3,9000),(9,'张鹏程',3,10000),(10,'暴晓凯',3,10000),(11,'王海南',3,20000),(12,'秦冲',3,18000),(13,'赵光',4,30000),(14,'李建鹏',4,10000),(15,'程江铁',4,50000),(16,'王世浩',4,60000),(17,'杨磊',4,20000),(18,'保洁阿姨',0,2500);
C:/Users/Administrator/AppData/Local/YNote/data/qqC6291E4C3805B7FFE62627719EE86C2D/9e0772d19cd14d19a4940bfda1226ec9/wps26a6.tmp.jpeg
问题:
-- 列出工资大于5000的员工所属的部门名、员工id和员工工资
SELECT
d.dept_name AS '部门名',
e.emp_id AS '员工ID',
e.emp_name AS '员工姓名',
e.emp_wage AS '员工工资'
FROM employee e LEFT JOIN department d ON e.dept_id = d.dept_id
WHERE e.emp_wage >= 5000
-- 查询部门中的员工
SELECT
d.dept_name AS '部门名',
d.dept_id AS '部门ID',
e.emp_id AS '员工ID',
e.emp_name AS '员工姓名'
FROM department d LEFT JOIN employee e ON d.dept_id = e.dept_id
-- 列出员工大于等于2人的部门编号
SELECT
COUNT(*) AS '人数',
e.dept_id AS '部门编号'
FROM employee e
GROUP BY e.dept_id
HAVING COUNT(*) >= 2
-- 求各部门的平均工资
SELECT
AVG(e.emp_wage) AS '平均工资',
d.dept_name AS '部门名称'
FROM department d LEFT JOIN employee e ON d.dept_id = e.dept_id
GROUP BY e.dept_id
-- 求各部门的员工工资总额
SELECT
SUM(e.emp_wage) AS '工资总额',
d.dept_name AS '部门名称'
FROM department d LEFT JOIN employee e ON d.dept_id = e.dept_id
GROUP BY e.dept_id
-- 列出工资最高的员工姓名
SELECT
e1.emp_name AS '姓名',
e1.emp_wage AS '工资'
FROM employee e1
WHERE e1.emp_wage = (SELECT MAX(e.emp_wage) FROM employee e )
-- 查询每个部门中工资最高的员工信息。
-- 先通过员工表把某一个部门中工资最好的员工找到(只能找到工资和部门ID)，接着去关联员工表，就可以拿到员工姓名，接着关联部门表，就可以查到所属部门
SELECT
e.emp_name AS '员工姓名',
e.emp_wage AS '员工工资',
d.dept_name AS '部门名称'
FROM (
SELECT
MAX(emp_wage) AS 'emp_wage',
dept_id AS 'dept_id'
FROM employee GROUP BY dept_id
) temp LEFT JOIN employee e ON e.dept_id = temp.dept_id AND e.emp_wage = temp.emp_wage
LEFT JOIN department d ON d.dept_id = e.dept_id
|
|
收藏
淘帖
踩
