【趙渝強老師】MySQL中的數據庫對象

在這里插入圖片描述

MySQL數據庫中包含各自數據庫對象，常見的數據庫對象有：表、索引、視圖、事件、存儲過程和存儲函數等等。

視頻講解如下
【趙渝強老師】MySQL中的數據庫對象

一、創建與管理表

表是一種非常重要的數據庫對象，MySQL數據庫的數據都是存儲在表中。MySQL的表是一種二維結構，由行和列組成。表有列組成，列有列的數據類型。下面通過具體的步驟來演示如何操作MySQL的表。這些操作包括創建表、查看表、修改表和刪除表。

（1）創建一張新的表test5

mysql> create table test5(id int,name varchar(32),age int);

（2）查看創建表的語句。

mysql> show create table test5 \G;# 輸出的信息如下：
*************************** 1. row ***************************Table: test5
Create Table: CREATE TABLE `test5` (`id` int DEFAULT NULL,`name` varchar(32) DEFAULT NULL,`age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

（3）查看表的結構

mysql> desc test5;# 輸出的信息如下：
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+# 提示：這里也可以使用下面語句查看表的結構。
mysql> show columns from test5;

（4）在表中增加一個字段

mysql> alter table test5 add gender varchar(1) default 'M';# 提示：這里增加了一個gender字段用于表示性別，默認是“M”。

（5）修改表將gender字段的長度改為10個字符，并且默認值改為“Female”

mysql> alter table test5 modify gender varchar(10) default 'Female';

（6）修改字段的順序，將gender字段放在id字段的后面

mysql> alter table test5 modify gender varchar(10) after id;

（7）重新查看表的結構

mysql> desc test5;# 輸出的信息如下：
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | NULL    |       |
| name   | varchar(32) | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

（8）刪除gender字段

mysql> alter table test5 drop column gender;

（9）刪除表test5

mysql> drop table test5;

《MySQL數據庫從零開始》

二、在查詢時使用索引

MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數據的數據結構。索引是一種數據結構。MySQL默認的索引類型是B+樹索引。下面以普通索引為例來介紹如何使用MySQL的索引。

普通索引是最基本的索引，它沒有任何限制，用于加速查詢。

（1）基于員工表創建一張新的表。

mysql> create table indextable1 as select * from emp;# 提示：通過子查詢創建表，只會復制表中的數據，不會復制索引。

（2）在員工姓名ename上創建普通索引。

mysql> create index index1 on indextable1(ename);# 提示：索引頁可以在創建表的時候，同時創建創建索引。例如：
mysql> create table mytable1(id   int,name varchar(10),index index_mytable1_name(name));

（3）查看表indextable1上的索引信息。

mysql> show indexes from indextable1 \G;# 輸出的信息如下：
*************************** 1. row ***************************Table: indextable1Non_unique: 1Key_name: index1Seq_in_index: 1Column_name: enameCollation: ACardinality: 15Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: 
Index_comment: Visible: YESExpression: NULL
1 row in set (0.01 sec)

（4）查詢名叫KING的員工信息，使用explain查看SQL的執行計劃。

mysql> explain select * from indextable1 where ename='KING' \G;# 輸出的信息如下：
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: indextable1partitions: NULLtype: ref
possible_keys: index1key: index1key_len: 33ref: constrows: 1filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.00 sec)

三、使用視圖簡化查詢語句

當SQL的查詢語句比較復雜并且需要反復執行，如果每次都重新書寫該SQL語句顯然不是很方便。因此MySQL數據庫提供了視圖用于簡化復雜的SQL語句。視圖（view）是一種虛表，其本身并不包含數據。它將作為一個select語句保存在數據字典中的。視圖依賴的表叫做基表。通過視圖可以展現基表的部分數據；視圖數據來自定義視圖的查詢中使用的基表。在了解的視圖的作用后，下面通過具體的步驟來演示如何使用視圖。
（1）查看創建視圖的語法

mysql> help create view;# 輸出的信息如下：
Name: 'CREATE VIEW'
Description:
Syntax:
CREATE[OR REPLACE][ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}][DEFINER = user][SQL SECURITY { DEFINER | INVOKER }]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]

（2）基于員工表emp創建視圖。

mysql> create or replace view view1asselect * from emp where deptno=10;# 提示：視圖也可以基于多表進行創建，例如：
mysql> create or replace view view2asselect emp.ename,emp.sal,dept.dnamefrom emp,deptwhere emp.deptno=dept.deptno;

（3）使用show create view語句查看視圖信息。

mysql> show create view view1;

（4）從視圖中查詢數據。

mysql> select * from view1;# 輸出的信息如下：
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981/6/9   | 2450 | NULL |     10 |
|  7839 | KING   | PRESIDENT |   -1 | 1981/11/17 | 5000 | NULL |     10 |
|  7934 | MILLER | CLERK     | 7782 | 1982/1/23  | 1300 | NULL |     10 |
+-------+--------+-----------+------+------------+------+------+--------+

（5）通過視圖執行DML操作，例如：給10號部門員工漲100塊錢工資。

mysql> update view1 set sal=sal+100;# 提示：并不是所有的視圖都可以執行DML操作。在視圖定義時含義以下內容，視圖則不能執行DML操作：
1、查詢子句中包含distinct和組函數
2、查詢語句中包含group by子句和order by子句
3、查詢語句中包含union 、union all等集合運算符
4、where子句中包含相關子查詢
5、from子句中包含多個表
6、如果視圖中有計算列，則不能執行update操作
7、如果基表中有某個具有非空約束的列未出現在視圖定義中，則不能做insert操作

（6）創建視圖時使用WITH CHECK OPTION約束。

mysql> create or replace view view2asselect * from emp where sal<1000with check option;# 提示：WITH CHECK OPTION表示對視圖所做的DML操作，不能違反視圖的WHERE條件的限制。

（7）在view2上執行update操作。

mysql> update view2 set sal=2000;# 此時將出現下面的錯誤信息：
ERROR 1369 (HY000): CHECK OPTION failed 'demo1.view2'

《MySQL數據庫從零開始》

四、 MySQL的事件

事件（Event）是MySQL數據庫中的時間觸發器，類似Linux的Crontab定時間的功能。在某一特定的時間點，Event會自動有MySQL調用從而觸發相關的SQL語句或存儲過程。要使用MySQL的事件，需要將參數“event_scheduler”設置為“ON”。

mysql> show variables like 'event_scheduler';# 輸出的信息如下：
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

創建事件的語法格式如下：

CREATE[DEFINER = user]EVENT[IF NOT EXISTS]event_nameON SCHEDULE schedule[ON COMPLETION [NOT] PRESERVE][ENABLE | DISABLE | DISABLE ON SLAVE][COMMENT 'string']DO event_body;# 其中：
ON SCHEDULE：用于設定Event的觸發時間。可以使用”AT 時間戳“來完成單次的計劃任務；或者使用”EVERY 時間的數量時間單位“來完成重復的計劃任務。
ON COMPLETION [NOT] PRESERVE：表示“當這個事件不會再發生的時候”；PRESERVE的作用是使Event在執行完畢后不會被刪除。

下面通過一個示例來說明如何使用MySQL的事件。
（1）創建一張表用于保存當前是時間。

mysql> create table testevent(currenttime timestamp);

（2）創建事件每隔3秒往表testevent插入當前的時間戳。

mysql> create event if not exists insert_timestamp_eventon schedule every 3 secondon completion preserveenabledoinsert into testevent values(current_timestamp());# 提示：這里的Event調用的是insert語句往表testevent插入當前的時間戳。
# Event也可以調用存儲過程來完成更加復雜的業務邏輯。

（3）查看數據庫中已有的事件

mysql> show events \G;# 輸出的信息如下：
*************************** 1. row ***************************Db: demo1Name: insert_timestamp_eventDefiner: root@localhostTime zone: SYSTEMType: RECURRINGExecute at: NULLInterval value: 3Interval field: SECONDStarts: 2025-07-24 20:28:53Ends: NULLStatus: ENABLEDOriginator: 1
character_set_client: utf8
collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci

（4）查看testevent表中的數據。

mysql> select * from testevent;# 輸出的信息如下：
+---------------------+
| currenttime         |
+---------------------+
| 2025-07-24 20:28:53 |
| 2025-07-24 20:28:56 |
| 2025-07-24 20:28:59 |
| 2025-07-24 20:29:02 |
| 2025-07-24 20:29:05 |
+---------------------+# 提示：從表testevent中的數據可以看出，Event每隔3秒往表中插入了當前的時間。

（5）刪除事件。

mysql> drop event insert_timestamp_event;

《MySQL數據庫從零開始》