MySQL实训_02

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

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

-- create table employee(
-- 	empid varchar(10) primary key not null,
--     name varchar(10),
--     gender varchar(10),
--     title varchar(20),
--     birthday date,
--     depid varchar(10));
--     
-- create table department(
-- 	depid varchar(10) primary key not null,
--     depname varchar(20));
--     
-- create table salary(
-- 	empid varchar(10) primary key not null,
--     base_salary decimal(8,2),
--     title_salary decimal(8,2),
--     deduction int);

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

-- alter table department add d_intro varchar(64);

 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. 查询出每个雇员的雇员编号，姓名，职称，所在部门，实发工资和应发工资。

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

# employee department salary  涉及三张表  
# 基本工资 + 职务工资  = 应发工资
# 基本工资 + 职务工资 -扣除部分 = 实发工资


-- select employee.empid as 雇员编号,employee.`name` as 姓名,employee.title as 职称,department.depname as 所在部门,(base_salary+title_salary) as 应发工资,(base_salary+title_salary-defuction) as 实发工资 from department INNER JOIN employee on department.depid = employee.depid INNER JOIN salary ON employee.empid = salary.empid;
-- 

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

# 查询姓“张”且年龄小于40岁的员工的记录。
# year() 返回日期所在的年份 
# CURDATE() 返回当前日期 
-- 
-- SELECT * from employee WHERE name like '张%' and (year(CURDATE())-year(birthday))<40;

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

-- select employee.empid as 雇员编号,employee.`name` as 姓名,employee.title as 职称,department.depname as 所在部门,(base_salary+title_salary) as 应发工资,(base_salary+title_salary-defuction) as 实发工资 from department INNER JOIN employee on department.depid = employee.depid INNER JOIN salary ON employee.empid = salary.empid where depname="销售部";

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

-- SELECT title,count(*) as 人数 from employee GROUP BY title;

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

select department.depname as 部门名称 ,sum(base_salary+title_salary) as 应发工资,SUM(base_salary+title_salary-defuction) as 实发工资,AVG(base_salary+title_salary-defuction) as 平均薪资 from department INNER JOIN employee on department.depid = employee.depid INNER JOIN salary ON employee.empid = salary.empid GROUP BY depname;