mySql期末复习

use yggl;
Create table employees2(
员工编号 char(6) not null primary key,
姓名 char(10) not null,
学历 char(4) not null,
出生时间 date not null,
性别 char(2) not null,
工作年限 tinyint(1) null,
地址 varchar(20) null,
电话号码 char(12) null,
员工部门号 char(3) null
);

一、修改employees表：
在‘工作年限’字段后面添加一个字段：邮箱（varchar(20) 空）；
修改字段‘性别’的类型为tinyint；
删除字段‘地址’

alter table employees2 
add 邮箱 varchar(20) null after 工作年限;

二、公司新成立一个后勤部，部门编码为7，备注为“筹建中”。

insert into department values (7,'后勤部','筹建中');

三、经过一段时间的工作张乐调到财务部工作；其工资增加2000元

update employees,salary
set 员工部门号=1,收入=收入+2000
where employees.员工编号=salary.员工编号 and 姓名='张乐';

四、王林辞职离开公司，请将与王林相关的数据全部删除。

delete employees,salary
from employees,salary
where employees.员工编号=salary.员工编号 and 姓名='王五';

五、查询employees表中员工的姓名和性别，要求性别值为1时显示为‘男’，为0时显示为‘女’。

select 姓名,
case
    when 性别=1 then '男'
    when 性别=0 then '女'
end as 性别
from employees;

六、查询yggl数据库中，“研发部”所有男性员工的员工编号，姓名，性别，工作年限，电话号码，工作的部门名称，实际收入。

select 姓名,性别,工作年限,电话号码,部门名称,收入-支出 as 实际收入
from employees join department on employees.员工部门号=department.部门编号
               join salary using(员工编号)
where 部门名称='研发部';

七、分别统计男生和女生的总人数。

select 性别,count(*)
from employees
group by 性别;

八、按员工学历分组统计各学历人数

select 学历,count(*)
from employees
group by 学历;

九. 按员工的工作年限分组，统计各个工作年限的人数，并按人数从大到小排序。

select 工作年限,count(*) as 人数
from employees
group by 工作年限
order by 人数 desc;

十查询每个部门的雇员人数超过2人的部门名称与员工数量。

select 部门名称,count(*) as 人数
from employees join department on employees.员工部门号=department.部门编号
group by 部门名称 having 人数>2;

十一、查询财务部、研发部、市场部的员工信息

select employees.*
from employees join department on 员工部门号=部门编号
where 部门名称 in ('财务部','研发部','市场部');

十二、查询“李可”的姓名、工作年限和所工作的部门名称

select 姓名,工作年限,部门名称
from employees join department on 员工部门号=部门编号
where 姓名='李可';

十三、对employees数据表创建视图，并完成相关查询操作
⑴创建视图，包含所有男员工的员工编号、姓名、工作年限和学历

create or replace view emp_view
as 
  select 员工编号,姓名,工作年限,学历
  from employees
  where 性别=1;

⑵从上述视图中查询工作年限在两年以上的员工信息

select * from emp_view where 工作年限>2;

十四、使用Alter Table命令创建索引
⑴对employees数据表的出生日期添加一个唯一索引date_ind，姓名和性别添加一个复合索引na_ind

alter table employees
add unique index date_ind(出生时间),
add index na_ind(姓名,性别);

⑵对department数据表中的员工部门号创建主键索引

alter table department 
add primary key(部门编号);

十五、将salary表中字段员工编号设置为外键，参照于employees表中的员工编号字段，并在更新、删除数据时设置CASCADE级联策略。

alter table salary
add foreign key(员工编号)
references employees(员工编号)
on delete cascade
on update cascade;

十六、创建一个触发器，实现当向employees表插入一行数据时，自动在salary表中增加一条记录，其中收入=5000(1+增加的工作年限0.1)，支出=0。

delimiter $$
create trigger employees_insert after insert on employees for each row 
begin 
    insert into salary values(NEW.员工编号,5000*(1+NEW.工作年限*0.1),0);
end $$
delimiter ;
# 触发事件
insert into employees values('000123','张红','大专','1994-02-11',1,2,'四川成都高新区','12365987485','1');

十七、创建一个存储过程，比较两个员工的实际收入，如果前者比后者高，输出”前者高”，小于则输出”后者高”，相同则输出“相同”。调用该存储过程比较”000001”和”108991”这两个员工的实际收入。

delimiter $$
create procedure compare(in num1 char(16),in num2 char(6),out result char(6))
begin 
    declare sr1 float;
    declare sr2 float;
    select 收入 into sr1 from salary where 员工编号=num1;
    select 收入 into sr2 from salary where 员工编号=num2;
    if sr1<sr2 then set result='后者高';
    else set result='相同';
    end if;
end $$
delimiter ;

#触发事件
call compare('000001','108991',@res)=@res;

评论 (0)