MySQL 视图、函数、存储过程、触发器、事件(了解即可)
2023-02-18 16:34:36 时间
1.视图:view
-
视图就是一张虚拟的表。表是真正存数据的,视图只是显示查询结果。
-
视图的作用:隐藏表的结构、简化sql嵌套查询操作
注意:视图就是你要查询数据的一个中间结果集,我们一般只用来做数据查询的
创建视图:create view view_name as 查询语句
例如:
mysql> create view v_name_course_socre as select st.name,co.course,sc.score from students st inner join scores sc on st.StuID=sc.StuID inner join courses co on sc.courseid=co.courseid;
Query OK, 0 rows affected (1.63 sec)
mysql> show tables;
+---------------------+
| Tables_in_hellodb |
+---------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
| v_name_course_socre |
+---------------------+
8 rows in set (0.00 sec)
mysql> select * from v_name_course_socre;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 63 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Taiji Quan | 57 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
15 rows in set (1.69 sec)
删除视图:drop view view_name
mysql> show tables;
+---------------------+
| Tables_in_hellodb |
+---------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
| v_name_course_socre |
| v_student |
+---------------------+
9 rows in set (0.00 sec)
mysql> drop view v_student;
Query OK, 0 rows affected (0.00 sec)
查看创建视图时候用的SQL语句
-
SHOW CREATE VIEW view_name #只能看视图定义
-
SHOW CREATE TABLE view_name # 可以查看表和视图
查看视图属性信息:show table status like 'xxx'
mysql> show table status like 'v_%'\G
*************************** 1. row ***************************
Name: v_name_course_socre
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec)
修改视图:
-
视图只是一个虚拟的表,本身不存放数据,只是某个select语句的执行结果。
-
修改视图实际上就是修改后台对应表的数据。
mysql> create view v_student as select * from students;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from v_studet;
ERROR 1146 (42S02): Table 'hellodb.v_studet' doesn't exist
mysql> select * from v_student;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
24 rows in set (0.00 sec)
mysql> delete from v_student where StuID=24;
Query OK, 1 row affected (0.11 sec)
mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.00 sec)
2.MySQL函数:FUNCTION
MySQL的函数分为内置函数和自定义函数。
内置函数查看:
https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html
https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html
自定义函数:
create function fun_name((parameter_name data_type,...,parameter_name data_type);
注意:
-
begin-end用于定义一组语句块
-
delimiter:mysql的分隔符,mysql客户端中默认是分号(;)。告诉mysql解释器,该段命令是否已经结束了,mysql是否可以执行了
例如:
mysql> DELIMITER //
mysql> CREATE FUNCTION deleteById(id SMALLINT UNSIGNED) RETURNS VARCHAR(20)
-> BEGIN
-> DELETE FROM students WHERE stuid = id;
-> RETURN (SELECT COUNT(*) FROM students);
-> END//
ERROR 1304 (42000): FUNCTION deleteById already exists
mysql> DELIMITER ;
mysql> select deleteById(23);
+----------------+
| deleteById(23) |
+----------------+
| 22 |
+----------------+
1 row in set (0.01 sec)
RETURNS VARCHAR(20) :定义返回值的,定义函数的输出数据的类型
3.PROCEDURE 存储过程
作用:和函数的功能差不多,但是函数不能单独作为一个命令来执行。存储过程可以单独作为一个命令来进行执行。
格式:call 需要调用的存储过程
例如:
mysql> delimiter //
mysql> CREATE PROCEDURE selectById(IN id SMALLINT UNSIGNED)
-> BEGIN
-> SELECT * FROM students WHERE stuid = id;
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
+-------+---------------+-----+--------+---------+-----------+
22 rows in set (0.00 sec)
mysql> call selectById(2);
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
+-------+------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
4.TRIGGER 触发器:监控某件事满足条件以后自动执行一些事情
主要是针对数据库据表里的增删改操作,当执行这些操作的时候就触发一个行为。
5.Event 事件
类似于linux里面的计划任务,再某个时间点或者周期执行对应的操作。
注意:事件默认没有开启
相关文章
- 面试官终极拷打-虾皮篇
- edictor调用某些函数界面卡死的问题
- h5页面实现扫码功能
- 面试官终极拷打-滴滴篇
- PC端如何实现页面引导
- 面试官终极拷打-阿里篇
- 面试官终极拷打-腾讯篇
- Halo添加自定义页面
- 用桌面图标模拟FlappyBird游戏
- vivo 故障定位平台的探索与实践
- vivo 实时计算平台建设实践
- vivo 服务端监控体系建设实践
- vivo 游戏中心低代码平台的提效秘诀
- vivo 低代码平台【后羿】的探索与实践
- 分支路径图调度框架在 vivo 效果广告业务的落地实践
- vivo 云原生容器探索和落地实践
- 云原生时代数据库运维体系演进
- 异构混排在vivo互联网的技术实践
- 规则引擎Drools在贷后催收业务中的应用
- vivo大数据日志采集Agent设计实践