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里面的计划任务，再某个时间点或者周期执行对应的操作。
注意：事件默认没有开启
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/288586.html