mysql练习03-1
苏*
mysql
项目描述
mysql练习03-1
上传时间
2022.06.27
浏览人数
558人
苏*
天津市河东区
Hot:14506
/*
DATA TYPE IN COMMON IN MYSQL5.7:
INT 整型 年龄
BIGINT 长整型 商品编号
TINYINT 微整型 性别/是否已婚
CHAR 手机号(char(11)) 身份证号(char(18)) 12345678988 87886566
VARCHAR 评论内容( varchar(200) )
FLOAT 浮点
DOUBLE 双精度
DECIMAL 十进制 薪资 DECIMAL(7,2)
DATE
DATETIME
YEAR
TEXT [65E3] 文本型
LONGTEXT [42E8] 长文本型
*/
/*table 1:员工表*/
create table employees (
emp_id varchar(6) NOT NULL,
emp_name varchar(10) NOT NULL,
emp_edu varchar(4) NOT NULL comment '学历',
emp_birth date NOT NULL ,
emp_gender varchar(2) NOT NULL,
emp_years_of_service int(11) DEFAULT NULL comment '工作年限',
empp_addr varchar(20) DEFAULT NULL comment '地址',
emp_tel varchar(11) DEFAULT NULL comment '电话',
depart_num varchar(3) DEFAULT NULL comment '部门ID',
PRIMARY KEY (emp_id)
);
/*table 2:薪水表*/
CREATE TABLE salary (
emp_id varchar(6) NOT NULL,
emp_income DECIMAL(8,2) NOT NULL comment '收入',
emp_expend DECIMAL(8,2) NOT NULL comment '支出',
PRIMARY KEY (emp_id)
) ;
/*table 3:部门表*/
CREATE TABLE departments (
depart_num varchar(3) NOT NULL comment '部门ID',
depart_name varchar(20) NOT NULL comment '部门名称',
depart_comment text comment '部门备注信息',
PRIMARY KEY (depart_num)
);
INSERT INTO employees VALUES ('000001', '王林', '大专', '1996-01-23', '1', 8, '中山路32-1-508', '83355668', '2');
INSERT INTO employees VALUES ('010008', '伍容华', '本科', '1986-03-28', '1', 3, '北京东路100-2', '83321321', '1');
INSERT INTO employees VALUES ('020010', '王向荣', '硕士', '1989-12-09', '1', 2, '四牌楼', '83792361', '1');
INSERT INTO employees VALUES ('020018', '李丽', '大专', '1988-07-30', '0', 6, '中山东路102-2', '83413301', '1');
INSERT INTO employees VALUES ('102201', '刘明', '本科', '2001-10-18', '1', 3, '虎距路100-2', '83606608', '5');
INSERT INTO employees VALUES ('102208', '朱俊', '硕士', '1999-09-28', '1', 2, '牌楼巷5-3-106', '84708817', '5');
INSERT INTO employees VALUES ('108991', '钟敏', '硕士', '1989-08-10', '0', 4, '中山路10-3-105', '83346722', '3');
INSERT INTO employees VALUES ('111006', '张石兵', '本科', '1994-10-01', '1', 1, '解放路34-1-203', '84563418', '5');
INSERT INTO employees VALUES ('210678', '林涛', '大专', '2000-04-02', '1', 2, '中山北路', '83467336', '3');
INSERT INTO employees VALUES ('302566', '李玉珉', '本科', '1998-09-20', '1', 3, '热和路209-3', '58765992', '4');
INSERT INTO employees VALUES ('308759', '叶凡', '本科', '1978-11-18', '1', 2, '北京西路3-7-52', '83308901', '4');
INSERT INTO employees VALUES ('504209', '陈林琳', '大专', '1979-09-03', '0', 5, '汉中路120-4-12', '84468158', '4');
INSERT INTO salary VALUES ('000001', 5100.80, 1123.09);
INSERT INTO salary VALUES ('010008', 4582.62, 2288.03);
INSERT INTO salary VALUES ('020010', 6860.00, 1198.00);
INSERT INTO salary VALUES ('020018', 2347.68, 3180.00);
INSERT INTO salary VALUES ('102201', 12500.00, 4185.65);
INSERT INTO salary VALUES ('102208', 4980.00, 3100.00);
INSERT INTO salary VALUES ('108991', 3259.98, 1281.52);
INSERT INTO salary VALUES ('111006', 3987.01, 7079.58);
INSERT INTO salary VALUES ('210678', 12500.00, 1121.00);
INSERT INTO salary VALUES ('302566', 6980.70, 889.20);
INSERT INTO salary VALUES ('308759', 2531.98, 1299.08);
INSERT INTO salary VALUES ('504209', 8066.15, 300.00);
INSERT INTO departments VALUES ('1', '财务部', '周末单休');
INSERT INTO departments VALUES ('2', '人力资源部', '周末单休');
INSERT INTO departments VALUES ('3', '经理办公室', NULL);
INSERT INTO departments VALUES ('4', '研发部', NULL);
INSERT INTO departments VALUES ('5', '市场部', '周末双休');
-- Part 1:
-- （1）查询employees表员工部门号和性别，要求消除重复行。
-- distinct
select es.emp_id,es.emp_gender from employees as es ;
-- （2）计算每个雇员的实际收入（实际收入=收入-支出）。
select sy.emp_id as 员工编号, (sy.emp_income - sy1.emp_expend) as 实际收入 from salary as sy left join salary as sy1 on sy.emp_id = sy1.emp_id ;
-- （3）查询employees表中员工的姓名和性别，要求sex值为1时显示为“男”，为0时显示为“女”
select es.emp_name,
(case when es.emp_gender = 1 then '男'
when es.emp_gender = 0 then '女' end ) as 性别
from employees as es ;
-- （4）查询每个雇员的地址和电话，显示的列标题要求显示“address” “telephone”。
select es.emp_id,es.emp_name,es.empp_addr as address , es.emp_tel as telephone from employees as es ;
-- （5）计算salary表中员工月收入的平均数。
select avg(sy.emp_income) from salary as sy ;
-- （6）计算所有员工的总支出。
select sum(sy.emp_expend) from salary as sy ;
-- （7）显示女雇员的地址和电话。
select es.emp_id,es.emp_name, es.empp_addr , es.emp_tel from employees as es where es.emp_gender = '0';
-- （8）计算员工总数。
select count(1) from employees as es;
-- （9）显示最高收入的人的员工所有信息,以及薪资和部门名称
select * from employees as es
left join departments as ds on es.depart_num = ds.depart_num
left join salary as sy on es.emp_id = sy.emp_id
where sy.emp_income = (
select max(sy.emp_income) from salary as sy );
-- （10）统计00后的员工数量
select count(1) from employees as es where es.emp_birth like '2%';
-- （11）统计40岁以上的员工数量
select count(es.emp_id) as 'age>40' from employees as es left join
(select es.emp_id,year(CURDATE())-mid(es.emp_birth,1,4)-1 as 'age_l' from employees as es where week(concat(year(CURDATE()),'-',mid(es.emp_birth,6,5)), 1) < week(now(),1))
as ag on es.emp_id = ag.emp_id
left join
(select es.emp_id,year(CURDATE())-mid(es.emp_birth,1,4) as 'age_m' from employees as es where week(concat(year(CURDATE()),'-',mid(es.emp_birth,6,5)), 1) > week(now(),1))
as ae on es.emp_id = ae.emp_id
where age_l >40 or age_m >40 ;
-- （12）统计40岁以上,30-40岁,20-30岁三个年龄段的员工数量
select
case when age_l > 40 or age_m >40 then '40岁以上'
when age_l > 30 and age_l <= 40 or age_m > 30 and age_m <= 40 then '30-40岁'
when age_l > 20 and age_l <= 30 or age_m > 20 and age_m <= 30 then '20-30岁'
end as 年龄分段,
count(es.emp_id) as number from employees as es left join
(select es.emp_id,year(CURDATE())-mid(es.emp_birth,1,4)-1 as 'age_l' from employees as es where week(concat(year(CURDATE()),'-',mid(es.emp_birth,6,5)), 1) < week(now(),1))
as ag on es.emp_id = ag.emp_id
left join
(select es.emp_id,year(CURDATE())-mid(es.emp_birth,1,4) as 'age_m' from employees as es where week(concat(year(CURDATE()),'-',mid(es.emp_birth,6,5)), 1) > week(now(),1))
as ae on es.emp_id = ae.emp_id
group by age_l > 40 or age_m >40 ,age_l > 30 and age_l <= 40 or age_m > 30 and age_m <= 40,
age_l > 20 and age_l <= 30 or age_m > 20 and age_m <= 30;
-- （13）统计40岁以上,30-40岁,20-30岁三个年龄段的平均工资
-- ?????
select case when age > 40 then '>40'
when age > 30 and age <=40 then '30-40'
when age >20 and age <=30 then '20-30'
end as age_gp
,avg(b.emp_income) from employees as a join
(select es.emp_name,year(curdate())-mid(es.emp_birth,1,4) as age,sy.emp_income
from employees as es left join salary as sy on es.emp_id = sy.emp_id) as b on a.emp_name = b.emp_name
group by age_gp ;
-- （14）统计工作年限2年以上薪资在4k以下的员工名,部门名,以及薪资
select * from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
where sy.emp_income < 4000 ;
-- （15）统计'地址'列字符数<=4的员工名以及部门名
select * from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
where char_length(es.empp_addr) <= 4 ;
-- （16）统计学历的分布情况 请查出: 学历 人数 平均薪资
select es.emp_edu,count(1),avg(sy.emp_income) from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
group by es.emp_edu;
-- （17）统计双休以及单休的人数
select
case when ds.depart_comment = '周末单休' then '周末单休'
when ds.depart_comment = '周末双休' then '周末双休 '
else '未知' end as 休息,
count(1) from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
group by ds.depart_comment = '周末单休' , ds.depart_comment = '周末双休' ;
-- （18）统计无明确休息时间的员工名,以及部门名
select
count(1) from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
where ds.depart_comment is null;
-- （19）显示月收入高于2000元的员工的员工号,以及部门ID, 部门名。
select * from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
where sy.emp_income > 2000;
-- （20）查找员工号中倒数第二个数字为0的员工的姓名、地址和学历。
-- Tip:员工工号为固定长度的数字,可使用字符串裁剪的方式....
select * from employees as es left join salary as sy on es.emp_id = sy.emp_id
left join departments as ds on es.depart_num = ds.depart_num
where mid(es.emp_id,5,1) = 0;
苏*
