MySQL——company、dep、person SQL查询

题目

数据库：company 表：dep、person
–教育部
INSERT INTO person VALUES (‘1’, ‘alex’, ‘28’, ‘人妖’, ‘53000.00’, ‘2010-06-21’, ‘1’);
INSERT INTO person VALUES (‘2’, ‘wupeiqi’, ‘23’, ‘男’, ‘8000.00’, ‘2011-02-21’, ‘1’);
INSERT INTO person VALUES (‘3’, ‘egon’, ‘30’, ‘男’, ‘6500.00’, ‘2015-06-21’, ‘1’);
INSERT INTO person VALUES (‘4’, ‘jingnvshen’, ‘18’, ‘女’, ‘6680.00’, ‘2014-06-21’, ‘1’);

– 销售部
INSERT INTO person VALUES (‘5’, ‘歪歪’, ‘20’, ‘女’, ‘3000.00’, ‘2015-02-21’, ‘2’);
INSERT INTO person VALUES (‘6’, ‘星星’, ‘20’, ‘女’, ‘2000.00’, ‘2018-01-30’, ‘2’);
INSERT INTO person VALUES (‘7’, ‘格格’, ‘20’, ‘女’, ‘2000.00’, ‘2018-02-27’, ‘2’);
INSERT INTO person VALUES (‘8’, ‘周周’, ‘20’, ‘女’, ‘2000.00’, ‘2015-06-21’, ‘2’);

– 市场部
INSERT INTO person VALUES (‘9’, ‘月月’, ‘21’, ‘女’, ‘4000.00’, ‘2014-07-21’, ‘3’);
INSERT INTO person VALUES (‘10’, ‘安琪’, ‘22’, ‘女’, ‘4000.00’, ‘2015-07-15’, ‘3’);

– 人事部
INSERT INTO person VALUES (‘11’, ‘周明月’, ‘17’, ‘女’, ‘5000.00’, ‘2014-06-21’, ‘4’);

– 鼓励部
INSERT INTO person VALUES (‘12’, ‘苍老师’, ‘33’, ‘女’, ‘1000000.00’, ‘2018-02-21’, null);

菲菲
练习题
1，查询人员和部门所有信息
2，查询出 教学部 年龄大于20岁,并且工资小于40000的员工,按工资倒序排列.(要求:分别使用多表联合查询和内连接查询)
3，查询每个部门中最高工资和最低工资是多少,显示部门名称
4，求最大工资那个人的姓名和薪水
5，求工资高于所有人员平均工资的人员
6，查询平均年龄在20岁以上的部门名
7，查询教育部 下的员工信息
8，查询大于所有人平均工资的人员的姓名与年龄
9, 查询高于本部门平均工资的人员
10 根据工资高低,将人员划分为两个级别,分别为 高端人群和低端人群。显示效果:姓名,年龄,性别,工资,级别

答案

-- 创建库
CREATE DATABASE company;
-- 创建一个部门表  部门编号  部门名称
CREATE TABLE dep(
id BIGINT  PRIMARY KEY, -- 主键 唯一 且不为空
dname VARCHAR(50)  -- 最后这个列 不需要有”,“
)DEFAULT CHARSET = utf8;

-- 添加部门
INSERT INTO dep VALUES(1,'教育部');
INSERT INTO dep VALUES(2,'销售部');
INSERT INTO dep VALUES(3,'市场部');
INSERT INTO dep VALUES(4,'人事部');
INSERT INTO dep VALUES(5,'鼓励部');
-- 创建员工表  id ,age,sex, salary工资,hire_date入职时间,dept_id部门号
CREATE TABLE person(
id VARCHAR(50) PRIMARY KEY, -- primary key表示主键
pname VARCHAR(50),-- 姓名
age TINYINT(3) NOT NULL,   -- 年龄 not null 不为空
sex ENUM('男','女','人妖') DEFAULT '男',  -- enum 表示枚举  default表示默认值为男
salary DECIMAL(9,2),-- 工资 总共7位 小数是2位
hire_date DATE, -- 入职日期
dept_id BIGINT, -- 部门号 因为下面有外键约束 所有 这个列的数据类型 必须和主表的id列数据类型一致
FOREIGN KEY (dept_id) REFERENCES dep(id) -- 外键约束 创建表时直接创建
)DEFAULT CHARSET = utf8;

-- 教育部
INSERT INTO `person` VALUES ('1', 'alex', '28', '人妖', '53000.00', '2010-06-21', '1');
INSERT INTO `person` VALUES ('2', 'wupeiqi', '23', '男', '8000.00', '2011-02-21', '1');
INSERT INTO `person` VALUES ('3', 'egon', '30', '男', '6500.00', '2015-06-21', '1');
INSERT INTO `person` VALUES ('4', 'jingnvshen', '18', '女', '6680.00', '2014-06-21', '1');

-- 销售部
INSERT INTO `person` VALUES ('5', '歪歪', '20', '女', '3000.00', '2015-02-21', '2');
INSERT INTO `person` VALUES ('6', '星星', '20', '女', '2000.00', '2018-01-30', '2');
INSERT INTO `person` VALUES ('7', '格格', '20', '女', '2000.00', '2018-02-27', '2');
INSERT INTO `person` VALUES ('8', '周周', '20', '女', '2000.00', '2015-06-21', '2');

-- 市场部
INSERT INTO `person` VALUES ('9', '月月', '21', '女', '4000.00', '2014-07-21', '3');
INSERT INTO `person` VALUES ('10', '安琪', '22', '女', '4000.00', '2015-07-15', '3');

-- 人事部
INSERT INTO `person` VALUES ('11', '周明月', '17', '女', '5000.00', '2014-06-21', '4');

-- 鼓励部
INSERT INTO `person` VALUES ('12', '苍老师', '33', '女', '1000000.00', '2018-02-21', NULL);

-- 1.查询人员和部门所有的信息
SELECT * FROM dep;
SELECT * FROM person;

-- 2.查询出 教育部 年龄大于20岁,并且工资小于40000的员工,按工资倒序排列.(要求:分别使用多表联合查询和内连接查询)
-- （1）多表联合查询
SELECT * FROM person p,dep d WHERE p.salary<4000 AND d.id=1;
-- （2）内连接查询
SELECT * FROM person p INNER JOIN dep d ON p.salary<4000 AND d.id=1;

-- 3.查询每个部门中最高工资和最低工资是多少,显示部门名称
SELECT dep.dname,MAX(salary) AS 最高工资,MIN(salary) AS 最低工资 FROM person 
LEFT JOIN dep ON dep.id=person.dept_id GROUP BY person.dept_id;

-- 4.求最大工资那个人的姓名和薪水
SELECT pname,salary FROM person WHERE salary=(SELECT MAX(salary) FROM person);

-- 5.求工资高于所有人员平均工资的人员
SELECT pname FROM person WHERE salary > (SELECT AVG(salary) FROM person);

-- 6.查询平均年龄在20岁以上的部门名
SELECT dname FROM dep WHERE id IN (SELECT dept_id FROM person GROUP BY dept_id HAVING AVG(age)>20);

-- 7.查询教育部下的员工信息
SELECT * FROM person WHERE  dept_id=1;

-- 8.查询大于所有人平均工资的人员的姓名与年龄
SELECT pname,age FROM person WHERE salary > (SELECT AVG(salary) FROM person);

-- 9.查询高于本部门平均工资的人员
SELECT p.* FROM (SELECT dept_id,AVG(salary) AS average FROM person GROUP BY dept_id)aa 
INNER JOIN person p ON p.dept_id=aa.dept_id AND p.salary>aa.average; 

-- 10.根据工资是否高于平均工资,将人员划分为两个级别,分别为 高端人群和低端人群。显示效果:姓名,年龄,性别,工资,级别
SELECT pname,age,sex,salary ,IF(salary >(SELECT AVG(salary) FROM person),'高端人群','低端人群') AS 级别 FROM person;