MySQL第六次作业

一、创建部门表

指令：

mysql> CREATE TABLE dept (
    ->  dept_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门编号',
    -> dept_name CHAR(20) COMMENT '部门名称'
    -> );

演示：

二、插入部门数据

指令：

mysql> INSERT INTO dept (dept_name) VALUES
    -> ('销售部'),
    -> ('财务部'),
    -> ('生产部'),
    -> ('人事部');

演示：

三、创建员工表

指令：

mysql> CREATE TABLE emp (
    ->  emp_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工号',
    -> emp_name CHAR(20) NOT NULL DEFAULT '员工姓名' COMMENT '员工姓名',
    -> gender CHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
    -> birth DATE NOT NULL DEFAULT '1990-01-01' COMMENT '出生日期',
    -> salary DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '工资',
    -> address VARCHAR(200) NOT NULL DEFAULT '' COMMENT '通讯地址',
    -> dept_id INT COMMENT '部门编号',
    -> FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
    -> );

演示：

四、创建索引

指令：

mysql> CREATE INDEX idx_name ON emp(emp_name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX idx_birth ON emp(birth);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX idx_deptid_name ON emp(dept_id, emp_name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

演示：

五、插入员工数据

指令：

mysql> INSERT INTO emp (emp_name, gender, birth, salary, address, dept_id) VALUES
    -> ('张晓红', '女', '1980-01-23', 5800, '河南省郑州市中原路10号', 1),
    -> ('张静静', '女', '1987-10-03', 5400, '河南省新乡市平原路38号', 1),
    -> ('王云飞', '男', '1992-11-15', 5600, '河南省新乡市人民路28号', 1),
    -> ('王鹏长', '男', '1987-10-01', 6800, '河南省新乡市东明大道12号', 1),
    -> ('王大鹏', '男', '1989-02-11', 5900, '河南省郑州市东风路15号', 1),
    -> ('王萌萌', '女', '1986-12-30', 5000, '河南省开封市五一路14号', 2),
    -> ('王大光', '男', '1988-11-08', 6200, '河南省开封市八一路124号', 2),
    -> ('王小明', '男', '1998-01-03', 4800, '河南省驻马店市雪松路128号', 2),
    -> ('王娜娜', '女', '1994-03-05', 5200, '河南省驻马店市车站路2号', 2),
    -> ('刘云飞', '男', '1992-08-13', 6800, '河南省南阳市民生路255号', 3),
    -> ('田田', '男', '1991-09-06', 6200, '河南省南阳市张仲景路14号', 3);

演示：

六、视图练习

1、创建视图v_emp_dept_id_1，查询销售部门的员工姓名和家庭住址；

指令：

mysql> create view v_emp_dept_id_1 as
    -> select emp_name, address
    -> from emp
    -> where dept_id = (select dept_id from dept where dept_name = '销售部');

演示：

2、创建视图v_emp_dept，查询销售部门员工姓名和家庭住址及部门名称；

指令：

mysql> create view v_emp_dept as
    -> select e.emp_name, e.address, d.dept_name
    -> from emp e
    -> join dept d on e.dept_id = d.dept_id
    -> where d.dept_name = '销售部';

演示：

3、创建视图v_dept_emp_count(dept_name,emp_count,avg_salay)，统计每个部门人数并计算平均工资；

指令：

mysql> create view v_dept_emp_count as
    -> select d.dept_name, count(e.emp_id) as emp_count, avg(e.salary) as avg_salary
    -> from dept d
    -> left join emp e on d.dept_id = e.dept_id
    -> group by d.dept_name;

演示：

4、修改视图v_emp_dept，查询销售部门员工姓名、家庭住址、工资和部门名称；

首先，我们需要删除现有的视图，然后重新创建它：

drop view if exists v_emp_dept;

指令：

mysql> create view v_emp_dept as
    -> select e.emp_name, e.address, e.salary, d.dept_name
    -> from emp e
    -> join dept d on e.dept_id = d.dept_id
    -> where d.dept_name = '销售部';

演示：

5、查看视图名称； 查看视图结构； 查看创建视图语句；

指令：

查看所有视图名称：

show full tables in homework where table_type like 'view';

查看视图结构：

describe v_emp_dept_id_1;

describe v_emp_dept;

describe v_dept_emp_count;

演示：

查看创建视图语句：

指令：

show create view v_emp_dept_id_1;

show create view v_emp_dept;

show create view v_dept_emp_count;

演示：

6、删除以上三个视图；

指令：

drop view if exists v_emp_dept_id_1;
drop view if exists v_emp_dept;
drop view if exists v_dept_emp_count;

演示：

七、存储过程练习

1、创建一个提取emp表所有员工工资和的存储过程s1；

指令：

delimiter //
create procedure s1()
begin
select sum(salary) as total_salary from emp;
end //
delimiter ;

演示：

2、调用存储过程s1；

指令：

call s1();

演示：

3、创建存储过程s2，实现输入员工姓名后返回员工的家庭住址；

指令：

delimiter //
create procedure s2(in emp_name_input char(20))
begin
select address
from emp
where emp_name = emp_name_input;
end //
delimiter ;

演示：

4、调用存储过程s2；

指令：

 call s2('张晓红');

演示：

5、创建一个存储过程avg_sai，有3个参数，分别是dept，gender，接收平均工资，功能查询emp；表dept_id为1，gender为男的平均工资；

指令：

delimiter //
create procedure avg_sai(in dept int, in gen char(2), out avg_salary decimal(10,2))
begin
select avg(salary)
into avg_salary
from emp
where dept_id = dept and gender = gen;
end //
delimiter ;

演示：

6、调用存储过程avg_sai；

指令：

set @avg_salary = 0;
call avg_sai(1,'男', @avg_salary);
select @avg_salary as average_salary;

演示：

7、删除以上存储过程；

指令：

mysql> drop procedure if exists s1;
mysql> drop procedure if exists s2;
mysql>  drop procedure if exists avg_sai;

演示：