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