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里面的計劃任務，再某個時間點或者周期執行對應的操作，
注意：事件默認沒有開啟
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/506076.html
標籤：其他
上一篇：MySQL第七天