MYSQL题目

有两张表：

部门表 dept

id	name
1	研发部
2	财务部
3	市场部

员工表  epmloyee

id	name	gender	age	money	deptid
1	王五	男	25	3000	1
2	李明	男	23	2500	1
3	王二小	男	23	2356	2
4	陈发	男	22	3600	2
5	小明	男	21	3100	3
6	苏奇	男	24	2800	3
7	王丽	女	19	1800	1
8	李芳	女	18	1900

完成以下要求:

-- 创建数据库

 CREATE DATABASE IF NOT EXISTS management CHARSET utf8



-- 创建部门表

CREATE TABLE dept(

id INT PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(10)

)

-- INSERT：插入新数据

INSERT INTO dept( id, NAME)VALUES

(1,'研发部'),

(2,'财务部'),

(3,'市场部');



-- 创建员工表

CREATE TABLE epmloyee(

id INT PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(10),

gender VARCHAR(1),

age INT,

money INT,

dept_id INT,

CONSTRAINT fk_epmloyee_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id)

)



-- INSERT：插入新数据

INSERT INTO epmloyee( id, NAME,gender,age,money,dept_id)VALUES

(1,'王五','男',25,3000,1),

(2,'李明','男',23,2500,1),

(3,'王二小','男',23,2356,2),

(4,'陈发','男',22,3600,2),

(5,'小明','男',21,3100,3),

(6,'苏奇','男',24,2800,3),

(7,'王丽','女',19,1800,1),

(8,'李芳','女',18,1900,NULL);

1. 1查询所有员工信息，信息内容包括(id,员工姓名,性别,年龄,工资,部门名称)

     两张表关联

SELECT e.id,e.name,e.gender,e.age,e.money,d.name 部门

FROM epmloyee e

LEFT JOIN dept d

ON e.dept_id=d.id

1. 2查询出工资最高的员工，并显示员工信息

-- 第一种方法
SELECT d.name 部门,e.name 姓名,e.money 工资

FROM epmloyee e

LEFT JOIN dept d

ON e.dept_id=d.id

WHERE e.money=(SELECT MAX(money) FROM epmloyee)

-- 第二种方法
SELECT d.name,e.name,e.money

FROM (SELECT MAX(money) epm FROM epmloyee) ep, epmloyee e

INNER JOIN dept d

ON e.dept_id=d.id

WHERE e.money>=ep.epm

1. 3查询出每个部门工资最高的员工信息

-- 第一种方法
SELECT d.name 部门,e.name 姓名,e.money 工资

FROM epmloyee e

LEFT JOIN dept d

ON e.dept_id=d.id

WHERE (dept_id,money) IN(

         SELECT dept_id,MAX(money)

         FROM epmloyee

         GROUP BY dept_id

)
-- 第二种方法
SELECT d.name 部门,e.name 姓名,e.money 工资

FROM epmloyee e

LEFT JOIN dept d

ON e.dept_id=d.id

INNER JOIN (SELECT dept_id,MAX(money) tm

	FROM epmloyee

	GROUP BY dept_id) t

ON e.dept_id=t.dept_id AND e.money=t.tm

1. 4查询所有男性员工工资总和,平均工资,最高工资,最低工资

SELECT SUM(e.money) 工资总和,AVG(e.money) 平均工资,MAX(e.money) 最高工资,MIN(e.money) 最低工资

FROM epmloyee e

WHERE e.gender='男'

1. 5查询工资大于平均工资的员工信息

-- 第一种方法
SELECT e.id,e.name,e.gender,e.age,e.money,d.name 部门

FROM epmloyee e

LEFT JOIN dept d

ON e.dept_id=d.id

WHERE e.money>=(SELECT AVG(money) FROM epmloyee )
--第二种方法
SELECT e.id,e.name,e.gender,e.age,e.money,d.name 部门

FROM (SELECT AVG(money) sa FROM epmloyee) s,

	epmloyee e

INNER JOIN dept d

ON e.dept_id=d.id

WHERE e.money>s.sa

1. 6查询所有姓王员工的姓名和性别

SELECT e.name,e.gender

FROM epmloyee e

WHERE e.name LIKE '王%'

1. 7查询年龄最大的前3个员工的姓名和年龄  排序  数量限制

SELECT e.name,MAX(e.age)

FROM epmloyee e

GROUP BY e.name

ORDER BY MAX(e.age) DESC LIMIT 3

1. 8统计每个部门的工资总和,显示信息：部门名称，工资总和

SELECT d.name,SUM(e.money) 工资总和

FROM epmloyee e

INNER JOIN dept d

ON e.dept_id=d.id

GROUP BY e.dept_id

1. 9统计每个部门的总人数,显示信息：部门名称，部门人数

SELECT d.name,COUNT(e.money) 部门人数

FROM epmloyee e

INNER JOIN dept d

ON e.dept_id=d.id

GROUP BY e.dept_id