【赵渝强老师】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: test5Create Table: CREATE TABLE `test5` ( `id` int DEFAULT NULL, `name` varchar(32) DEFAULT NULL, `age` int DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 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官方对索引的定义为：索引（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: indextable1 Non_unique: 1 Key_name: index1 Seq_in_index: 1 Column_name: ename Collation: A Cardinality: 15 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL1 row in set (0.01 sec)

（4）查询名叫KING的员工信息，使用explain查看SQL的执行计划。

mysql> explain select * from indextable1 where ename=\'KING\' \\G;# 输出的信息如下：*************************** 1. row ***************************  id: 1 select_type: SIMPLE table: indextable1 partitions: NULL type: refpossible_keys: index1 key: index1 key_len: 33 ref: const rows: 1 filtered: 100.00 Extra: NULL1 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 view1 as select * from emp where deptno=10;# 提示：视图也可以基于多表进行创建，例如：mysql> create or replace view view2 as select emp.ename,emp.sal,dept.dname from emp,dept where 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 view2 as select * from emp where sal<1000 with 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的事件

事件（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_name ON 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_event on schedule every 3 second on completion preserve enable do insert into testevent values(current_timestamp());# 提示：这里的Event调用的是insert语句往表testevent插入当前的时间戳。# Event也可以调用存储过程来完成更加复杂的业务逻辑。

（3）查看数据库中已有的事件

mysql> show events \\G;# 输出的信息如下：*************************** 1. row ***************************  Db: demo1 Name: insert_timestamp_event Definer: root@localhost  Time zone: SYSTEM Type: RECURRING Execute at: NULL Interval value: 3 Interval field: SECOND  Starts: 2025-07-24 20:28:53 Ends: NULL  Status: ENABLED Originator: 1character_set_client: utf8collation_connection: utf8_general_ci Database 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;