第六次作业

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

1mysql> create view v_emp_dept_id_1
    -> as select e.emp_name,e.address from emp e left join dept d on e.dept_id = d.dept_id
    -> where e.dept_id = '1';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_emp_dept_id_1;
+----------+--------------------------+

| emp_name | address |
| -------- | ------- |
|          |         |

+----------+--------------------------+

| 张晓红 | 河南省郑州市中原路10号 |
| ------ | ---------------------- |
|        |                        |

| 张静静 | 河南省新乡市平原路38号 |
| ------ | ---------------------- |
|        |                        |

| 王云飞 | 河南省新乡市人民路28号 |
| ------ | ---------------------- |
|        |                        |

| 王大鹏 | 河南省郑州市东风路15号 |
| ------ | ---------------------- |
|        |                        |

| 王鹏飞 | 河南省新乡市东明大道12号 |
| ------ | ------------------------ |
|        |                          |

+----------+--------------------------+
5 rows in set (0.01 sec)

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

mysql> create view v_emp_dept
    -> as
    -> select e.emp_name,e.address,d.dept_name from emp e left join dept d on e.dept_id = d.dept_id
    -> where e.dept_id = '1';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_emp_dept;
+----------+--------------------------+-----------+
| emp_name | address                  | dept_name |
+----------+--------------------------+-----------+
| 张晓红   | 河南省郑州市中原路10号   | 销售部    |
| 张静静   | 河南省新乡市平原路38号   | 销售部    |
| 王云飞   | 河南省新乡市人民路28号   | 销售部    |
| 王大鹏   | 河南省郑州市东风路15号   | 销售部    |
| 王鹏飞   | 河南省新乡市东明大道12号 | 销售部    |
+----------+--------------------------+-----------+
5 rows in set (0.00 sec)

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

mysql>  create view v_emp_dept1
    -> as
    -> select e.emp_name,e.address,d.dept_name from emp e left join dept d on e.dept_id = d.dept_id
    -> where d.dept_name = '销售部';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_emp_dept1;
+----------+--------------------------+-----------+
| emp_name | address                  | dept_name |
+----------+--------------------------+-----------+
| 张晓红   | 河南省郑州市中原路10号   | 销售部    |
| 张静静   | 河南省新乡市平原路38号   | 销售部    |
| 王云飞   | 河南省新乡市人民路28号   | 销售部    |
| 王大鹏   | 河南省郑州市东风路15号   | 销售部    |
| 王鹏飞   | 河南省新乡市东明大道12号 | 销售部    |
+----------+--------------------------+-----------+
5 rows in set (0.00 sec)

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

alter 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 = '销售部';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_emp_dept;
+----------+--------------------------+---------+-----------+

| emp_name | address | salary | dept_name |
| -------- | ------- | ------ | --------- |
|          |         |        |           |

+----------+--------------------------+---------+-----------+

| 张晓红 | 河南省郑州市中原路10号 | 5800.00 | 销售部 |
| ------ | ---------------------- | ------- | ------ |
|        |                        |         |        |

| 张静静 | 河南省新乡市平原路38号 | 5400.00 | 销售部 |
| ------ | ---------------------- | ------- | ------ |
|        |                        |         |        |

| 王云飞 | 河南省新乡市人民路28号 | 5600.00 | 销售部 |
| ------ | ---------------------- | ------- | ------ |
|        |                        |         |        |

| 王大鹏 | 河南省郑州市东风路15号 | 5900.00 | 销售部 |
| ------ | ---------------------- | ------- | ------ |
|        |                        |         |        |

| 王鹏飞 | 河南省新乡市东明大道12号 | 6800.00 | 销售部 |
| ------ | ------------------------ | ------- | ------ |
|        |                          |         |        |

+----------+--------------------------+---------+-----------+
5 rows in set (0.00 sec)

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

mysql> show full tables;
+--------------------+------------+
| Tables_in_格雷拉特 | Table_type |
+--------------------+------------+
| dept               | BASE TABLE |
| emp                | BASE TABLE |
| v_dept_emp_count   | VIEW       |
| v_emp_dept         | VIEW       |
| v_emp_dept1        | VIEW       |
| v_emp_dept_id_1    | VIEW       |
+--------------------+------------+
6 rows in set (0.00 sec)

mysql> desc v_dept_emp_count;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| dept_name | char(20)      | YES  |     | NULL    |       |
| emp_count | bigint        | NO   |     | 0       |       |
| avg_salay | decimal(14,6) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> show create view v_dept_emp_count;
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View             | Create View


               | character_set_client | collation_connection |

+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v_dept_emp_count | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dept_emp_count` (`dept_name`,`emp_count`,`avg_salay`) AS select `d`.`dept_name` AS `dept_name`,count(`e`.`emp_name`) AS `count(e.emp_name)`,avg(`e`.`salary`) AS `avg(e.salary)` from (`emp` `e` join `dept` `d` on((`e`.`dept_id` = `d`.`dept_id`))) group by `d`.`dept_name` | gbk                  | gbk_chinese_ci       |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

6删除以上三个视图。

mysql> drop view  v_dept_emp_count,v_emp_dept,v_emp_dept1, v_emp_dept_id_1;
Query OK, 0 rows affected (0.01 sec)

mysql> show full tables;
+--------------------+------------+

| Tables_in_格雷拉特 | Table_type |
| ------------------ | ---------- |
|                    |            |

+--------------------+------------+

| dept | BASE TABLE |
| ---- | ---------- |
|      |            |

| emp  | BASE TABLE |
| ---- | ---------- |
|      |            |

+--------------------+------------+
2 rows in set (0.00 sec)

存储过程作业：

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

mysql> create procedure p1()
    -> begin
    -> select sum(salary) from emp;
    -> end !!
Query OK, 0 rows affected (0.01 sec)

2、调用存储过程s1

mysql> \d;
mysql> call p1();
+-------------+
| sum(salary) |
+-------------+
|    63700.00 |
+-------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

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

mysql> \d !!
mysql> create procedure p2(in name varchar(40))
    -> begin
    -> select address from emp
    -> where emp_name = name;
    -> end !!
Query OK, 0 rows affected (0.01 sec)

4、调用存储过程s2

mysql> call p2('王大光');
+-------------------------+
| address                 |
+-------------------------+
| 河南省开封市八一路124号 |
+-------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

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

mysql> create procedure avg_sai(
    -> in dept int ,
    -> in a_gender varchar(2),
    -> out a_avg_salary decimal(10,2))
    -> begin select avg(salary) into a_avg_salary from emp
    -> where dept_id = 1 and gender = a_gender;
    -> end !!
Query OK, 0 rows affected (0.01 sec)

6、调用存储过程avg_sai

mysql> CALL avg_sai(1, '男', @avg_salary);
Query OK, 1 row affected (0.01 sec)

mysql> select @avg_salary as avgsalary;
+-----------+
| avgsalary |
+-----------+
|   6100.00 |
+-----------+
1 row in set (0.00 sec)

7、删除以上存储过程

mysql> drop procedure avg_sai;
Query OK, 0 rows affected (0.04 sec)

mysql>  CALL avg_sai(1, '男', @avg_salary);
ERROR 1305 (42000): PROCEDURE 格雷拉特.avg_sai does not exist