Mysql小练习

\1. 以自己的姓名创建一个数据库。

create database if not exists pyh;

\2. 在此数据库下创建如下3表，数据类型，宽度，是否为空等，根据实际情况自己定义。

A． 雇员表（employee）：

雇员编号（empid），

姓名（name），

性别（gender），

职称（title），

出生日期（birthday），

所在部门编号（depid）；

其中雇员编号为主键；

B． 部门表（department）：

部门编号（depid），

部门名称（depname）；

其中部门编号为主键。

C． 工资表（salary）：

雇员编号（empid），

基本工资（base_salary），

职务工资（title_salary），

扣除（deduction）。

其中雇员编号为主键。

create table employee
(empid int not null primary key,
name char(11),
gender enum('男', '女'),
title char(11),
birthday date,
depid int
)default charset utf8mb4;
​
create table department
(depid int,
depname char(11))default charset utf8mb4;
​
create table salary
(empid int not null primary key,
base_salary decimal(6, 2),
title_salary decimal(6, 2),
deduction decimal(6, 2))default charset utf8mb4;

\3. 修改表结构，在部门表中添加一个”部门简介”字段。

alter table department add column introduction varchar(256);

\4. 在上面的3个表中各输入若干条记录，内容如下。

雇员表：

雇员编号	姓名	性别	职称	出生日期	所在部门编号
1001	张三	男	高级程师	1975-1-1	111
1002	李四	女	助工	1985-1-1	111
1003	王五	男	工程师	1978-11-11	222
1004	赵六	男	工程师	1979-1-1	222

部门表：

部门编号	部门名称	部门简介
111	生产部	Null
222	销售部	Null
333	人事部	Null

工资表：

雇员编号	基本工资	职务工资	扣除
1001	2200	1100	200
1002	1200	200	100
1003	1900	700	200
1004	1950	700	150

insert into employee
values
(1001, '张三', 1, '高级工程师', '1975-1-1', 111),
(1002, '李四', 2, '助工', '1985-1-1', 111),
(1003, '王五', 1, '工程师', '1978-11-11', 222),
(1004, '赵六', 1, '工程师', '1979-1-1', 222);
​
insert into department
values
(111, '生产部', null),
(222, '销售部', null),
(333, '人事部', null);
​
insert into salary
values
(1001, 2200, 1100, 200),
(1002, 1200, 200, 100),
(1003, 1900, 700, 200),
(1004, 1950, 700, 150);

\5. 将李四的职称改为“工程师”，并将她的基本工资改为5700元，职务工资为600。

update employee set title = '工程师' where name = '李四';
update salary set base_salary = 5700, title_salary = 600 where empid = (select empid from employee where name = '李四');

\6. 查询出每个雇员的雇员编号，姓名，职称，所在部门，实发工资和应发工资。

select e.empid as 雇员编号, e.name as 雇员姓名, e.title as 职称, d.depname as 所在部门, (s.base_salary + s.title_salary - s.deduction) as 实发工资, (s.base_salary + s.title_salary) as 应发工资 from employee e inner join department d on d.depid = e.depid inner join salary s on s.empid = e.empid where e.depid = d.depid;

\7. 查询姓“张”且年龄小于40岁的员工的记录。

# +0将日期值转换为数值型
select * from employee where (year(curdate())-year(birthday)) < 40 and name like '张%';

\8. 查询销售部所有雇员的雇员编号，姓名，职称，部门名称，实发工资。

select e.empid as 雇员编号, e.name as 姓名, e.title as 职称, d.depname as 部门名称, (s.base_salary + s.title_salary - s.deduction) as 实发工资 from employee e inner join department d on d.depid = e.depid inner join salary s on s.empid = e.empid where e.depid = d.depid and e.depid = 222;

\9. 统计各类职称的人数。

select distinct title as 职称, count(title) as 人数 from employee group by title;

\10. 统计各部门的部门名称，实发工资总和，平均工资。

select d.depname as 部门名称, sum(s.base_salary + s.title_salary - s.deduction) as 实发工资总和, avg(s.base_salary + s.title_salary - s.deduction) as 平均工资 from employee e inner join department d on d.depid = e.depid inner join salary s on s.empid = e.empid group by d.depname;