第六次作业:视图和存储

一、视图操作

数据准备

首先，创建两张表：部门（dept）和员工（emp），并插入数据。

CREATE TABLE dept(
    dept_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门编号',
    dept_name CHAR(20) COMMENT '部门名称'
);
INSERT INTO dept(dept_name) VALUES ('销售部'), ('财务部'), ('生产部'), ('人事部');

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 DATETIME NOT NULL DEFAULT '1990-1-1' COMMENT '出生日期',
    salary DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '工资',
    address VARCHAR(200) NOT NULL DEFAULT '' COMMENT '通讯地址',
    dept_id INT COMMENT '部门编号'
);

CREATE INDEX idx_name ON emp(emp_name); 
CREATE INDEX idx_birth ON emp(birth); 
CREATE INDEX idx_deptid_name ON emp(dept_id, emp_name);


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

由于mobaXterm 不支持中文字符,建议转成cmd.

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

CREATE VIEW v_emp_dept_id_1 AS
SELECT emp_name, address
FROM emp
WHERE dept_id = 1;

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

CREATE VIEW v_emp_dept AS
SELECT emp.emp_name, emp.address, dept.dept_name
FROM emp
JOIN dept ON emp.dept_id = dept.dept_id
WHERE emp.dept_id = 1;

3. 创建视图 v_dept_emp_count，统计每个部门人数并计算平均工资

CREATE VIEW v_dept_emp_count (dept_name, emp_count, avg_salary) AS
SELECT dept.dept_name, COUNT(emp.emp_id) AS emp_count, AVG(emp.salary) AS avg_salary
FROM emp
JOIN dept ON emp.dept_id = dept.dept_id
GROUP BY dept.dept_name;

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

CREATE OR REPLACE VIEW v_emp_dept AS
SELECT emp.emp_name, emp.address, emp.salary, dept.dept_name
FROM emp
JOIN dept ON emp.dept_id = dept.dept_id
WHERE emp.dept_id = 1;

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

• 查看视图名称：

SHOW FULL TABLES IN bank WHERE TABLE_TYPE LIKE 'VIEW';       

• 查看视图结构：

DESCRIBE v_emp_dept;

• 查看创建视图语句：

SHOW CREATE VIEW v_emp_dept;

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_param CHAR(20))
BEGIN
    SELECT address FROM emp WHERE emp_name = emp_name_param;
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 gender CHAR(2), OUT avg_salary DECIMAL(10,2))
BEGIN
    SELECT AVG(salary) INTO avg_salary
    FROM emp
    WHERE dept_id = dept AND gender = gender;
END //
DELIMITER ;

6. 调用存储过程 avg_sai

CALL avg_sai(1, '男', @avg_salary);
SELECT @avg_salary;

7. 删除以上存储过程

DROP PROCEDURE IF EXISTS s1;
DROP PROCEDURE IF EXISTS s2;
DROP PROCEDURE IF EXISTS avg_sai;