文章目录
1.部门与员工
部门表dept
员工表staff
– 部门表
CREATE TABLE dept(
id INT PRIMARY KEY,
dept_name VARCHAR(10),
dept_desc VARCHAR(10),
dept_date DATE
)
– 员工表
CREATE TABLE staff(
staff_id INT PRIMARY KEY,
staff_name VARCHAR(5),
staff_gender VARCHAR(1),
staff_age INT,
staff_money INT,
staff_deptid INT
)
– 1.查询所有员工信息,信息内容包括(id,员工姓名,性别,年龄,工资,部门名称)
ALTER TABLE staff ADD CONSTRAINT staff_deptid_fk FOREIGN KEY(staff_deptid) REFERENCES dept(id)
SELECT staff_name,staff_gender,staff_age,staff_money,dept_name FROM staff INNER JOIN dept ON staff.`staff_deptid`=dept.`id`
– 2.查询出工资最高的员工,并显示员工信息
SELECT staff_name,staff_gender,staff_age,staff_money,dept_name FROM staff INNER JOIN dept ON staff.`staff_deptid`=dept.`id`
WHERE staff_money=(SELECT MAX(staff_money) FROM staff)
– 3.查询出每个部门工资最高的员工信息
SELECT staff_name,staff_gender,staff_age,staff_money,dept_name FROM staff s INNER JOIN dept d ON s.staff_deptid=d.id
WHERE staff_money IN(SELECT MAX(staff_money) FROM staff GROUP BY staff_deptid)
– 4.查询所有男性员工工资总和,平均工资,最高工资,最低工资
SELECT SUM(staff_money) FROM staff WHERE staff_gender="男"
SELECT AVG(staff_money) FROM staff WHERE staff_gender="男"
SELECT MAX(staff_money) FROM staff WHERE staff_gender="男"
SELECT MIN(staff_money) FROM staff WHERE staff_gender="男"
– 5.查询工资大于平均工资的员工信息
SELECT staff_name,staff_gender,staff_age,dept_name FROM staff INNER JOIN dept ON staff.`staff_deptid`=dept.`id` WHERE staff_money>(SELECT AVG(staff_money) FROM staff)
– 6.查询所有姓王员工的姓名和性别
SELECT staff_name,staff_gender FROM staff WHERE staff_name LIKE "王%"
– 7.查询年龄最大的前3个员工的姓名和年龄
SELECT staff_name,staff_age FROM staff ORDER BY staff_age DESC LIMIT 0,3
– 8.统计每个部门的工资总和,显示信息:部门名称,工资总和
SELECT dept_name 部门名称,SUM(staff_money) 工资总和 FROM staff INNER JOIN dept ON dept.`id`=staff.`staff_deptid` GROUP BY staff_deptid
– 9.统计每个部门的总人数,显示信息:部门名称,部门人数
SELECT dept_name 部门名称,COUNT(staff_deptid) 部门人数 FROM staff INNER JOIN dept ON dept.`id`=staff.`staff_deptid` GROUP BY staff_deptid