【赵渝强老师】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官方对索引的定义为：索引（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: NULL
1 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: ref
possible_keys: index1
          key: index1
      key_len: 33
          ref: const
         rows: 1
     filtered: 100.00
        Extra: 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 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: 1
character_set_client: utf8
collation_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;