MySQL学习笔记

最新推荐文章于 2021-10-14 19:43:31 发布

我已经无了

最新推荐文章于 2021-10-14 19:43:31 发布

阅读量231

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/qq_42247231/article/details/103212082

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

3 数据库基本操作

3.1 数据库和数据库对象

数据库是存储数据库对象的容器。数据库分为系统数据库和用户数据库。数据库对象是指存储、管理和使用数据的不同结构形式，主要包含表、视图、存储过程、函数、触发器、事件等。

3.2 数据库相关操作——创建数据库

若已存在该数据库，则创建不成功，提示数据库已存在。

create database database_name;

3.3 数据库相关操作——查看和选择数据库

3.3.1 查看数据库

查看所有数据库

show databases;

3.3.2 选择数据库

选择指定数据库

use database_name;

3.4 数据库相关操作——删除数据库

3.4.1 删除数据库

删除指定数据库

drop database database_name;

4 存储引擎和数据类型

4.1 认识存储引擎

存储引擎是MySQL数据库管理系统的一个重要特征，在实际开发时，通常根据实际需要来选择存储引擎。存储引擎指定了表的类型，即如何存储和索引数据、是否支持事务等，同时存储引擎也决定了表在计算机中的存储方式。

4.1.1 查看存储引擎

查看当前版本的数据库管理系统所支持的存储引擎

show engines;

show variables like 'have%';

4.1.2 操作默认存储引擎

查询默认存储引擎

show variables like 'storage_engine%';

修改默认存储引擎

# 打开 my.ini 配置文件
default-storage-engine = INNODB    # 修改成新的存储引擎即可

4.2 数据类型

MySQL 数据库管理系统提供了整数类型、浮点数类型、定点数类型、位类型、日期和时间类型、字符串类型。

4.2.1 整数类型

整数类型	字节
TINYINT	1
SMALLINT	2
MEDIUMINT	3
INT（INTEGER）	4
BIGINT	8

例： INT 类型使用演示

mysql> create table tab_int_test (
    -> id INT
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> insert into tab_int_test values
    -> (0),
    -> (-1);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tab_int_test;
+------+
| id   |
+------+
|    0 |
|   -1 |
+------+
2 rows in set (0.00 sec)

mysql>

4.2.2 浮点数类型、定点数类型、位类型

浮点数类型

浮点数类型	字节
FLOAT	4
DOUBLE	8

注：当需要精确到小数点后 10 位以上时就需要选用 DOUBLE 类型。

定点数类型

定点数类型	字节
DEC（M, D）、DECIMAL（M, D）	M+2

注：有效取值范围 M 和 D 决定。M 表示总位数，D 表示小数位数。FLOAT 、 DOUBLE 数据类型存储的是近似值，而 DEC（DECIMAL）存储的是字符串，精度更高。

例：FLOAT 数据类型与 DECIMAL 数据类型的区别

mysql> create table tab_f_test (
    -> a FLOAT(38, 30),
    -> B DECIMAL(38,30)
    -> );
Query OK, 0 rows affected, 1 warning (0.44 sec)

mysql> insert into tab_f_test values
    -> (123450.000000000000000000000000000001,
    ->  123450.000000000000000000000000000001);
Query OK, 1 row affected (0.10 sec)

mysql> select * from tab_f_test;
+---------------------------------------+---------------------------------------+
| a                                     | B                                     |
+---------------------------------------+---------------------------------------+
| 123450.000000000000000000000000000000 | 123450.000000000000000000000000000001 |
+---------------------------------------+---------------------------------------+
1 row in set (0.00 sec)

mysql>

位类型

位类型	字节
BIT（M）	1～8

位类型的字节数是 M， M 的取值范围为 1～8，即该类型的存储空间是根据其精度决定的。

例：BIT 类型的使用方法

mysql> create table tab_bit_test (
    -> id BIT(8)
    -> );
Query OK, 0 rows affected (0.44 sec)

mysql> insert into tab_bit_test values
    -> (11),
    -> (b'11');
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select id+0 from tab_bit_test;
+------+
| id+0 |
+------+
|   11 |
|    3 |
+------+
2 rows in set (0.00 sec)

mysql> select BIN(id+0) from tab_bit_test;
+-----------+
| BIN(id+0) |
+-----------+
| 1011      |
| 11        |
+-----------+
2 rows in set (0.00 sec)

mysql>

4.2.3 日期和时间类型

日期和时间类型	字节	样式
DATE	4	2006-01-02
TIME	3	15:04:05
DATETIME	8	2006-01-01 15:04:05
TIMESTAMP	4	2006-01-01 15:04:05
YEAR	1	2006

例：日期和时间类型的使用方法

mysql> create table tab_d_test (
    -> f_date DATE,
    -> f_time TIME,
    -> f_datetime DATETIME,
    -> f_timestamp TIMESTAMP,
    -> f_year YEAR
    -> );
Query OK, 0 rows affected (0.44 sec)

mysql> # 查看当前各时间以便对比
mysql> select curdate(), curtime(), now(), now(), year(now()) \G
*************************** 1. row ***************************
  curdate(): 2019-11-23
  curtime(): 12:37:43
      now(): 2019-11-23 12:37:43
      now(): 2019-11-23 12:37:43
year(now()): 2019

1 row in set (0.00 sec)

mysql> insert into tab_d_test value (
    -> curdate(), curtime(), now(), now(), year(now())
    -> );
Query OK, 1 row affected, 1 warning (0.08 sec)

mysql> select * from tab_d_test;
+------------+----------+---------------------+---------------------+--------+
| f_date     | f_time   | f_datetime          | f_timestamp         | f_year |
+------------+----------+---------------------+---------------------+--------+
| 2019-11-23 | 12:39:17 | 2019-11-23 12:39:17 | 2019-11-23 12:39:17 |   2019 |
+------------+----------+---------------------+---------------------+--------+
1 row in set (0.00 sec)

mysql>

4.2.4 字符串类型

CHAR 系列字符串类型

类型	字节
CHAR（M）	M（0～255）
VARCHAR（M）	M（0～65535）

注：CHAR 适合存放经常变化的字符串。

TEXT 系列字符串类型

类型	字节
TINYTEXT	0～255
TEXT	0～65535
MEDIUMTEXT	0～167772150
LONGTEXT	0～4294967295

注：存储较长的文章内容的纯文本可以选用 TEXT 系列字符串类型。

BINARY 系列字符串类型

类型	字节
BINARY（M）	M
VARBINARY（M）	M

注：这两种类型与 CHAR 、VARCHAR 十分类似，不同的是，前者可以存储二进制数据（例如图片、音频、视频等），而后者只能存储字符数据。

BILOB 系列字符串类型

类型	字节
TINYBLOB	0~2^8
BLOB	0~2^16
MEDIUMBLOB	0~2^24
LONGBLOB	0～2^32

注：该类型与 TEXT 类型十分类似，不同的是，前者可以存储二进制数据（例如图片、音频、视频等），而后者只能存储字符数据。

例：字符串类型的使用方法

mysql> create table tab_char_test ( 
    -> id INT,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.39 sec)

mysql> insert into tab_char_test values 
    -> (1,'jincheng'),
    -> (1,"jindaxia");
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into tab_char_test values
    -> (2,"123456789123456789123456789");
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> select * from tab_char_test;
+------+----------+
| id   | name     |
+------+----------+
|    1 | jincheng |
|    1 | jindaxia |
+------+----------+
2 rows in set (0.00 sec)

mysql>

若插入的字符超出限制（20），则会报错。

5 表的操作

在 MySQL 中，表是一种很重要的数据库对象，是组成数据库的基本元素，由若干个字段组成，主要用来实现存储数据记录。表的操作包含创建表、查看表、删除表、修改表。

5.1 表的基本概念

表中的数据库对象包含列、索引、触发器。

5.2 创建表

创建一个表

create table table_name (
	属性名, 数据类型, 
	属性名, 数据类型,
	...
	属性名, 数据类型,
);

5.3 查看表结构

查看表的定义

describe table_name;    // 简写: dec table_name;

查看表的详细定义

show cteate table table_name;

例：查看表

mysql> desc tab_char_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table tab_char_test;
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                     |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab_char_test | CREATE TABLE `tab_char_test` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

5.4 删除表

删除表会直接删除表中所有数据以及表的结构，因此操作时应当十分小心。
删除一个表

drop table table_name;

5.5 修改表

5.5.1 修改表名

修改一个数据表的名称（需保证唯一）

alter table old_table_name rename [to] new_table_name;

注：to 可加，也可不加。

例：修改表名

mysql> show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| tab_bit_test      |
| tab_char_test     |
| tab_d_test        |
| tab_f_test        |
| tab_int_test      |
+-------------------+
5 rows in set (0.01 sec)

mysql> alter table tab_int_test rename t_int_test;
Query OK, 0 rows affected (0.31 sec)

mysql> show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| t_int_test        |
| tab_bit_test      |
| tab_char_test     |
| tab_d_test        |
| tab_f_test        |
+-------------------+
5 rows in set (0.01 sec)

mysql> alter table t_int_test rename to tab_int_test;
Query OK, 0 rows affected (0.20 sec)

mysql> show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| tab_bit_test      |
| tab_char_test     |
| tab_d_test        |
| tab_f_test        |
| tab_int_test      |
+-------------------+
5 rows in set (0.00 sec)

mysql>

5.5.2 增加字段

对于表，可以看成是由列和行来构成的，其中“列”经常被称为字段，字段是由字段名和数据类型进行定义的。

在表的最后一个位置增加字段

alter table table_name add 属性名 数据类型;

在表的第一个位置增加字段

aletr table table_name add 属性名 数据类型 first;

在表的指定字段之后增加字段

alter table table_name add 属性名 数据类型 after  属性名;

例：增加字段

mysql> desc tab_char_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> alter table tab_char_test add head INT first;
Query OK, 0 rows affected (1.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tab_char_test add mid INT after id;
Query OK, 0 rows affected (1.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tab_char_test add tail INT;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tab_char_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| head  | int(11)     | YES  |     | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
| mid   | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| tail  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql>

5.5.3 删除字段

删除表的一个字段

alter table table_name drop 属性名;

例：删除字段

mysql> desc tab_char_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| head  | int(11)     | YES  |     | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
| mid   | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| tail  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table tab_char_test drop mid;
Query OK, 0 rows affected (1.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tab_char_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| head  | int(11)     | YES  |     | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| tail  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

5.5.4 修改字段

修改字段的数据类型

alter table table_name modify 属性名 新数据类型；

修改字段名

alter table table_name change 旧属性名 新属性名 旧数据类型;

同时修改字段名及其属性

alter table table_name change 旧属性名 新属性名 新数据类型;

修改字段的顺序
属性名 1 表示要调整顺序的字段名，“first” 表示将其调整到表的第一个位置，“after 属性名 2” 表示将字段调整到属性名 2 的字段之后。

alter table table_name modify 属性名1 first|after 属性名2；

例：修改字段

mysql> desc tab_char_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| head  | int(11)     | YES  |     | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| tail  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table tab_char_test modify head CHAR(20);
Query OK, 2 rows affected (1.22 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table tab_char_test change id identify INT;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table tab_char_test change name name_char TEXT;
Query OK, 2 rows affected (0.96 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table tab_char_test modify tail INT first;
Query OK, 0 rows affected (1.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tab_char_test;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| tail      | int(11)  | YES  |     | NULL    |       |
| head      | char(20) | YES  |     | NULL    |       |
| identify  | int(11)  | YES  |     | NULL    |       |
| name_char | text     | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

5.6 操作表的约束

对于已经创建好的表，虽然字段的数据类型决定了所能存储的数据类型，但是表中存储的数据是否合法并没有进行检查。在使用 MySQL 时，如果想对表中的数据做一些完整性检查操作，可以通过表的约束来完成。

5.6.1 MySQL 支持的完整性约束

所谓完整性约束是指数据的准确性和一致性，而完整性检查是指检查数据的准确性和一致性。MySQL 数据库管理系统提供了一致机制来检查数据库表中的数据是否满足规定的条件，以保证数据库表中的数据的准确性和一致性，这种机制就是约束。约束分为单列月约束和多列约束。

完整性约束

完整性约束关键字	含义
NOT NULL（NK）	约束字段的值不能为空
DEFAULT	设置字段的默认值
UNIQUE KEY（UK）	约束字段的值是唯一的
PRIMARY KEY（PK）	约束字段为表的主键，可以作为该表记录的唯一标识
AUTO_INCREMENT	约束字段的值自动增加
FOREIGN KEY（FK）	约束字段为表的外键

5.6.2 设置非空约束（NOT NULL， NK）

若希望表的某个字段不为空，可以设置 NK 约束。插入数据时，若该字段为空，则会报错。

create table table_name (
	属性名 数据类型 NOT NULL,
	...
);

例：非空约束

mysql> create table tab_nk_test ( 
    -> name VARCHAR(20),
    -> id INT NOT NULL
    -> );
Query OK, 0 rows affected (0.33 sec)

mysql> insert into tab_nk_test values 
    -> ("jincheng", 1),
    -> ("jindaxia", 0);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into tab_nk_test (name,id) values ("jinhaofan",NULL);
ERROR 1048 (23000): Column 'id' cannot be null

mysql> select * from tab_nk_test;
+----------+----+
| name     | id |
+----------+----+
| jincheng |  1 |
| jindaxia |  0 |
+----------+----+
2 rows in set (0.00 sec)

mysql>

5.6.3 设置字段的默认值（DEFAULT）

当在为数据库表插入一条新纪录时，如果没有为这个字段赋值，那么系统会自动为这个字段插入默认值。

create table table_name (
	属性名 数据类型 DEFAULT 默认值,
	...
);

例：默认值

mysql> create table tab_dflt_test (
    -> id INT,
    -> name VARCHAR(20) DEFAULT "JIN"
    -> );
Query OK, 0 rows affected (0.45 sec)

mysql> insert into tab_dflt_test (id, name) values (1, "jincheng");
Query OK, 1 row affected (0.08 sec)

mysql> insert into tab_dflt_test (id) values (2);
Query OK, 1 row affected (0.08 sec)

mysql> select * from tab_dflt_test;
+------+----------+
| id   | name     |
+------+----------+
|    1 | jincheng |
|    2 | JIN      |
+------+----------+
2 rows in set (0.00 sec)

mysql>

5.6.4 设置唯一约束

当表中某个字段上的美容不允许重复时，则可以使用 UK 约束进行设置。当插入的数据在该字段重复时系统会报错。

create table table_name (
	属性名 数据类型 UNIQUE,
	...
);

也可以为约束起一个名字

create table table_name (
	属性名1 数据类型,
	...
	constraint 约束名 unique(属性名1)
）;

例：唯一约束

mysql> create table tab_uniq_test (
    -> id INT UNIQUE
    -> );
Query OK, 0 rows affected (0.57 sec)

mysql> insert into tab_uniq_test values (1), (2);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into tab_uniq_test values (1);
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

mysql> select * from tab_uniq_test;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql>

5.6.5 设置主键约束（PRIMARY KEY， PK）

当想数据库表中的某个字段来唯一标识所有记录时，则可以使用 PK 约束进行设置。之所以在数据库表中设置主键，是为了便于数据库管理系统快速地查找到表中的记录。主键约束相当于非空约束加上唯一约束。主键可以是单字段主键，也可以是多字段主键。

create table table_name (
	属性名 数据类型 primary key,    # 单字段主键
	...
);

create table table_name (
	属性名1 数据类型
	属性名2 数据类型
	...
	constrait 约束名  primary key(属性名1， 属性名2， ...)    # 多字段主键
);

mysql> create table tab_pri_test (
    -> id INT primary key,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.45 sec)

mysql> desc tab_pri_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

5.6.6 设置字段自动增加（AUTO_INCREMENT）

AUTO_INCREMENT 是 MySQL 唯一扩展的完整性约束，当为数据库表插入新纪录时，该字段上的值会自动生成唯一的 ID 。一个表只能有一个字段使用该约束，并且该字段的数据类型必须为整数类型。且设置自动增加的字段必须是一个 key 。

create table table_name (
	id INT auto_increment,
	name VARCHAR(20)
);

例：自动增加

mysql> create table tab_auto_test ( 
    -> id INT primary key auto_increment,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.34 sec)

mysql> insert into tab_auto_test (name) values ("jincheng"), ("jindaxia");
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tab_auto_test;
+----+----------+
| id | name     |
+----+----------+
|  1 | jincheng |
|  2 | jindaxia |
+----+----------+
2 rows in set (0.00 sec)

mysql>

5.6.7 设置外键约束（FOREIGN KEY， FK）

外键约束保证多个表（通常为两个表）之间的参照完整性，即构建于两个表之间的参照关系。设置外键约束的两个表之间具有父子关系，即子表中某字段的取值范围由父表决定。在具体设置外键约束时，设置 FK 约束的字段必须依赖于数据库中已经存在的父表的主键，同时外键可以为 NULL 。

create table table_name (
	属性名 数据类型，
	...
	constrait 约束名 foreign key (本表属性名) references 外表名 9 (外表属性名)
);

6 索引的操作

在 MySQL 数据库中，数据库对象表是存储和操作数据的逻辑结构，而数据库对象索引是一种有效组合数据的方式。通过索引对象，可以快速查询到数据库对象表中的特定记录，是一种提高性能的常用方式。一个索引会包含表中按照一定顺序排序的一列或多列字段。

6.1 为什么使用索引

由于数据存储在数据库表中，所以索引是创建在数据库表对象上的，由表中的一个或多个字段生成的键组成，这些键存储在数据结构（B 树或哈希表）中，通过 MySQL 可以快速有效地查找与键值相关联的字段。索引分为B型树索引和哈希索引。

MySQL 支持 6 种索引：普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引。

下列情况适合创建索引：

经常被查询的字段，即在 where 子句中出现的字段；
在分组的字段，即在 group by 子句中出现的字段；
存在依赖关系的子表和父表之间的联合查询，即主键和外键字段；
设置唯一完整性约束的字段。

6.2 创建和查看索引

所谓创建索引，就是在表的一个或多个字段上建立索引，有三种方式：创建表时创建索引、在已经存在的表上创建索引、通过 alter table 语句创建索引。

6.2.1 创建和查看普通索引

所谓普通索引，就是在创建索引时，不附加任何限制条件（唯一、非空等约束）。该类型的索引可以创建在任何数据类型的字段上。

1. 创建表时创建普通索引

create table table_name (
	属性名 数据类型,
	属性名 数据类型,
	...
	属性名,数据类型,
	index|key 索引名(属性名, (索引长度) asc|desc)
);

其中：索引长度和排序方式（asc：升序，desc：降序）可省略。

例：

mysql> create table tab2 ( 
    -> id INT,
    -> name VARCHAR(20),
    -> index index_id(id)
    -> );
Query OK, 0 rows affected (0.62 sec)

mysql> explain select * from tab2 where id=1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab2
   partitions: NULL
         type: ref
possible_keys: index_id
          key: index_id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql>

可以看到 possible_keys 与 key 均为 index_id，说明索引已经创建成功并已启用。

2. 在已经存在的表上创建普通索引

create index 索引名 on table_name (属性名, (索引长度) asc|desc)

例:

mysql> desc tab3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> create index index_id on tab3 (id);
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tab3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> show create table tab3 \G
*************************** 1. row ***************************
       Table: tab3
Create Table: CREATE TABLE `tab3` (
  `id` int(11) DEFAULT NULL,
  KEY `index_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql>

3. 通过 alter table 语句创建普通索引

alter table table_name add index|key 索引名(属性名, (索引长度) asc|desc)

例子与前述两种类似，略。

6.2.2 创建和查看唯一索引

所谓唯一索引，就是在创建索引时，限制索引的值必须是唯一的。通过该类型的索引可以更快地查询某条记录。在 MySQL 中，根据创建索引地方式，可以分为自动索引和手动索引两种。
自动索引：是指在数据库表里设置完整性约束时，该表会被系统自动创建索引。
手动索引：手动在数据库表上创建索引。

1. 创建表时创建唯一索引

create table table_name (
	属性名, 数据类型,
	属性名, 数据类型,
	...
	属性名, 数据类型,
	unique index|key 索引名(属性名, (索引长度) asc|desc)
);

2. 在已经存在的表上创建唯一索引

create unique index 索引名 on table_name (属性名, (索引长度) asc|desc)

3. 通过 alter table 语句创建普通唯一索引

alter table table_name add unique index|key 索引名(属性名, (索引长度) asc|desc)

6.2.3 创建和查看全文索引

全文索引主要关联在数据类型为 CHAR、VARCHAR 和 TEXT 的字段上，以便能够更快地查询数据量较大的字符串类型的字段。
MySQL 从 3.23.23 版本开始支持全文索引，只能在存储引擎为 MyISAM 的数据库表上创建全文索引。在默认情况下，全文索引的搜索执行方式为不区分大小写，如果全文索引所关联的字段为二进制数据类型时，则以区分大小写的方式进行搜索。

1. 创建表时创建全文索引

create table table_name (
	属性名, 数据类型,
	属性名, 数据类型,
	...
	属性名, 数据类型,
	fulltext index|key 索引名(属性名, (索引长度) asc|desc)
);

2. 在已经存在的表上创建全文索引

create fulltext index 索引名 on table_name (属性名, (索引长度) asc|desc)

3. 通过 alter table 语句创建全文索引

alter table table_name add fulltext index|key 索引名(属性名, (索引长度) asc|desc)

6.2.4 创建和查看多列索引

所谓多列索引，是指在创建索引时，所关联的字段不是一个字段，而是多个字段。虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。

1. 创建表时创建多列索引

create table table_name (
	属性名, 数据类型,
	属性名, 数据类型,
	...
	属性名, 数据类型,
	index|key 索引名( 属性名1, (索引长度) asc|desc, 
	                 属性名2, (索引长度) asc|desc,
	                 ... 
	                 属性名n, (索引长度) asc|desc )
);

2. 在已经存在的表上创建多列索引

create index 索引名 on table_name (属性名1, (索引长度) asc|desc, 
	                              属性名2, (索引长度) asc|desc,
	                              ... 
	                              属性名n, (索引长度) asc|desc )

3. 通过 alter table 语句创建多列索引

alter table table_name add index|key 索引名(属性名1, (索引长度) asc|desc, 
	                                       属性名2, (索引长度) asc|desc,
	                                        ... 
	                                       属性名n, (索引长度) asc|desc )

6.3 删除索引

drop index index_name on table_name;

7. 视图的操作

7.1 为什么使用视图

数据库中关于数据的查询有事非常复杂，编写的语句较多，当这种查询需要重复执行时，很难保证每次都编写正确，从而降低了数据库的实用性。另一方面，有时候要求只能操作表中的部分字段，而非全部字段，这时就需要限制程序员操作的字段。因此，为了提高复杂 SQL 语句的复用性和表操作的安全性，从 5.0.1 版本开始，MySQL 数据库管理系统提供了视图特性。

视图：所谓视图，本质上是一种虚拟表，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库表中以存储数据值的形式存在。行和列数据来自于定义视图的查询所引用的基本表，并且在具体引用视图时动态生成。

视图使程序员只关心感兴趣的某些特定数据和它们所负责的特定任务。这样程序员只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高数据库中数据的安全性。

视图的特点：

视图的列可以来自不同的表，是表的抽象在逻辑意义上建立的新关系。
视图是由基本表（实表）产生的表（虚表）。
视图的建立和删除不影响基本表。
对视图内容的更新（添加、删除和修改）直接影响基本表。
当视图来自于多个基本表时，不允许添加和删除数据。

7.2 创建视图

在创建视图时，首先要确保拥有 create view 的权限，并且确保对创建视图所引用的表也拥有相应的权限。

7.2.1 创建视图的语法形式

虽然视图可以看做是一种虚拟表，但是其在物理上是不存在的，即数据库管理系统没有专门的位置为视图存储数据。根据视图的概念，可以发现其数据来源于查询语句。

create view view_name as 查询语句;

由上可知，视图的功能其实就是封装了复杂的查询语句。如果想在多个地方重复实现该功能，只需查询视图，而不必每次都编写视图所封装的详细查询语句。

例：

mysql> select * from tab1;
+----------+
| name     |
+----------+
| jincheng |
| jindaxia |
+----------+
2 rows in set (0.00 sec)

mysql> select * from v_tab1;
+----------+
| name     |
+----------+
| jincheng |
| jindaxia |
+----------+
2 rows in set (0.01 sec)

mysql>

7.2.2 创建各种视图

1. 封装实现查询常量语句的视图

mysql> create view v_test1 as select 3.14159;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from v_test1;
+---------+
| 3.14159 |
+---------+
| 3.14159 |
+---------+
1 row in set (0.00 sec)

mysql>

2. 封装使用聚合函数（SUM，MIN，MAX，COUNT等）查询语句的视图

mysql> select * from tab1;
+----------+
| name     |
+----------+
| jincheng |
| jindaxia |
+----------+
2 rows in set (0.00 sec)

mysql> create view v_test2 as select count(name) from tab1;
Query OK, 0 rows affected (0.11 sec)

mysql> select * from v_test2;
+-------------+
| count(name) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql>

3. 封装了实现排序功能（ORDER BY）查询语句的视图

mysql> select * from tab4;
+------+----------+
| id   | name     |
+------+----------+
|    1 | jincheng |
|    2 | jindaxia |
|    3 | haofan   |
+------+----------+
3 rows in set (0.00 sec)

mysql> create view v_test3 as select name from tab4 order by id desc;
Query OK, 0 rows affected (0.12 sec)

mysql> select * from v_test3;
+----------+
| name     |
+----------+
| haofan   |
| jindaxia |
| jincheng |
+----------+
3 rows in set (0.00 sec)

mysql>

4. 封装了实现内连接查询语句的视图

mysql> select * from tab3;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from tab4;
+------+----------+
| id   | name     |
+------+----------+
|    1 | jincheng |
|    2 | jindaxia |
|    3 | haofan   |
+------+----------+
3 rows in set (0.00 sec)

mysql> create view v_test4 
    -> as
    -> select s.name 
    -> from tab4 as s, tab3 as g
    -> where s.id=g.id and g.id=2;
Query OK, 0 rows affected (0.12 sec)

mysql> select * from v_test4;
+----------+
| name     |
+----------+
| jindaxia |
+----------+
1 row in set (0.01 sec)

mysql>

5. 封装了实现表外连接（LEFT JOIN、RIGHT JOIN）查询语句的视图

mysql> create view v_test5
    -> as 
    -> select s.name
    -> from tab4 as s left join tab3 as g on s.id=g.id
    -> where g.id=2;
Query OK, 0 rows affected (0.13 sec)

mysql> select * from v_test5;
+----------+
| name     |
+----------+
| jindaxia |
+----------+
1 row in set (0.01 sec)

mysql>

6. 封装了实现查询相关查询语句的视图

mysql> select * from v_test5;
+----------+
| name     |
+----------+
| jindaxia |
+----------+
1 row in set (0.01 sec)

mysql> create view v_test6
    -> as
    -> select s.name
    -> from tab4 as s
    -> where s.id in (select id from tab3);
Query OK, 0 rows affected (0.11 sec)

mysql> select * from v_test6;
+----------+
| name     |
+----------+
| jincheng |
| jindaxia |
| haofan   |
+----------+
3 rows in set (0.00 sec)

mysql>

7. 封装了实现记录联合（UNION、UNION ALL）查询语句的视图

mysql> select * from tab5;
+------+-----------+
| id   | name      |
+------+-----------+
|    4 | jincheng1 |
|    5 | jincheng2 |
+------+-----------+
2 rows in set (0.00 sec)

mysql> select * from tab4;
+------+----------+
| id   | name     |
+------+----------+
|    1 | jincheng |
|    2 | jindaxia |
|    3 | haofan   |
+------+----------+
3 rows in set (0.00 sec)

mysql> create view v_test7
    -> as
    -> select id,name from tab4
    -> union all
    -> select id,name from tab5;
Query OK, 0 rows affected (0.20 sec)

mysql> select * from v_test7;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | jincheng  |
|    2 | jindaxia  |
|    3 | haofan    |
|    4 | jincheng1 |
|    5 | jincheng2 |
+------+-----------+
5 rows in set (0.00 sec)

mysql>

7.3 查看视图

7.3.1 show tables 语句查看视图名

从 MySQL 5.1 版本开始，执行 show tables 不仅会显示表的名字，同时也会实现出视图的名字。

例：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tab1           |
| tab2           |
| tab3           |
| tab4           |
| tab5           |
| v_tab1         |
| v_test         |
| v_test2        |
| v_test3        |
| v_test4        |
| v_test5        |
| v_test6        |
| v_test7        |
+----------------+
13 rows in set (0.00 sec)

mysql>

7.3.2 show table status 语句查看视图详细信息

show table status from database_name like 'xxx';

例：

mysql> show table status from test like 'v_test%';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| v_test  | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:16:32 | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    |
| v_test2 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:19:10 | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    |
| v_test3 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:23:52 | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    |
| v_test4 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:31:39 | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    |
| v_test5 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:35:22 | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    |
| v_test6 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:37:36 | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    |
| v_test7 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:42:37 | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
7 rows in set (0.00 sec)

mysql> show table status from test;
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| tab1    | InnoDB |      10 | Dynamic    |    2 |           8192 |       16384 |               0 |            0 |         0 |           NULL | 2019-11-24 09:53:33 | NULL                | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
| tab2    | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |           NULL | 2019-11-24 20:07:59 | NULL                | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
| tab3    | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |        16384 |         0 |           NULL | 2019-11-24 20:16:52 | 2019-11-27 19:29:28 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
| tab4    | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |            0 |         0 |           NULL | 2019-11-27 19:21:50 | 2019-11-27 19:22:36 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
| tab5    | InnoDB |      10 | Dynamic    |    2 |           8192 |       16384 |               0 |            0 |         0 |           NULL | 2019-11-27 19:40:57 | 2019-11-27 19:41:38 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
| v_tab1  | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:12:48 | NULL                | NULL       | NULL               |     NULL | NULL           | VIEW    |
| v_test  | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:16:32 | NULL                | NULL       | NULL               |     NULL | NULL           | VIEW    |
| v_test2 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:19:10 | NULL                | NULL       | NULL               |     NULL | NULL           | VIEW    |
| v_test3 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:23:52 | NULL                | NULL       | NULL               |     NULL | NULL           | VIEW    |
| v_test4 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:31:39 | NULL                | NULL       | NULL               |     NULL | NULL           | VIEW    |
| v_test5 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:35:22 | NULL                | NULL       | NULL               |     NULL | NULL           | VIEW    |
| v_test6 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:37:36 | NULL                | NULL       | NULL               |     NULL | NULL           | VIEW    |
| v_test7 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:42:37 | NULL                | NULL       | NULL               |     NULL | NULL           | VIEW    |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
13 rows in set (0.26 sec)

mysql> show table status from test like 'v_test5';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| v_test5 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2019-11-27 19:35:22 | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql>

7.3.3 show create view 语句查看视图定义信息

show create view view_name;

例：

mysql> show create view v_test2;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View    | Create View                                                                                                                                           | character_set_client | collation_connection |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v_test2 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test2` AS select count(`tab1`.`name`) AS `count(name)` from `tab1` | utf8mb4              | utf8mb4_0900_ai_ci   |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql>

7.3.4 describe / desc 语句查看视图设计信息

desc view_name;

例：

mysql> desc v_test2;
+-------------+------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| count(name) | bigint(21) | NO   |     | 0       |       |
+-------------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

7.3.5 通过系统表查看视图信息

MySQL 数据库安装成功后，会自动创建系统数据库 information_schema 。该数据库中存在一个包含视图信息的表格 views ，可以通过查看 views 查看所有视图的相关信息。

例：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from views where table_name='v_test7';
+---------------+--------------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION                                                                                                                                                                   | CHECK_OPTION | IS_UPDATABLE | DEFINER        | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
+---------------+--------------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
| def           | test         | v_test7    | select `test`.`tab4`.`id` AS `id`,`test`.`tab4`.`name` AS `name` from `test`.`tab4` union all select `test`.`tab5`.`id` AS `id`,`test`.`tab5`.`name` AS `name` from `test`.`tab5` | NONE         | NO           | root@localhost | DEFINER       | utf8mb4              | utf8mb4_0900_ai_ci   |
+---------------+--------------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
1 row in set (0.01 sec)

mysql>

7.4 删除视图

在删除视图时，首先要确保拥有 drop view 权限。

7.4.1 删除视图的语法形式

通过 drop view 语句可以一次删除一个或多个视图。

drop view view_name1, view_name2, ... ;

例：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tab1           |
| tab2           |
| tab3           |
| tab4           |
| tab5           |
| v_tab1         |
| v_test         |
| v_test2        |
| v_test3        |
| v_test4        |
| v_test5        |
| v_test6        |
| v_test7        |
+----------------+
13 rows in set (0.01 sec)

mysql> drop view v_tab1, v_test;
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tab1           |
| tab2           |
| tab3           |
| tab4           |
| tab5           |
| v_test2        |
| v_test3        |
| v_test4        |
| v_test5        |
| v_test6        |
| v_test7        |
+----------------+
11 rows in set (0.00 sec)

mysql>

7.5 修改视图

7.5.1 create or replace view 语句修改视图

对于拥有大量数据的表而言，修改表的操作是极其必要的，如果我们通过将旧表删除，创建需要的新表的方式修改表，会十分繁琐且易出错。但是视图是“虚表”，我们完全可以这么做。但是 MySQL 提供了更好的方式。

create or repalce view view_name as 查询语句;

例:

mysql> select * from v_test7;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | jincheng  |
|    2 | jindaxia  |
|    3 | haofan    |
|    4 | jincheng1 |
|    5 | jincheng2 |
+------+-----------+
5 rows in set (0.00 sec)

mysql> create or replace view v_test7 as select name from tab4;
Query OK, 0 rows affected (0.16 sec)

mysql> select * from v_test7;
+----------+
| name     |
+----------+
| jincheng |
| jindaxia |
| haofan   |
+----------+
3 rows in set (0.00 sec)

mysql>

7.5.2 alter 语句修改视图

alter view view_name as 查询语句;

例:

mysql> select * from v_test7;
+----------+
| name     |
+----------+
| jincheng |
| jindaxia |
| haofan   |
+----------+
3 rows in set (0.00 sec)

mysql> alter view v_test7 as select * from tab4;
Query OK, 0 rows affected (0.16 sec)

mysql> select * from v_test7;
+------+----------+
| id   | name     |
+------+----------+
|    1 | jincheng |
|    2 | jindaxia |
|    3 | haofan   |
+------+----------+
3 rows in set (0.00 sec)

mysql>

7.6 利用视图操作基本表

7.6.1 检索（查询）数据

通过视图查询数据，与通过表进行查询完全相同，只不过用过视图查询比表更安全、更简单、使用。

例：

mysql> select * from v_test7;
+------+----------+
| id   | name     |
+------+----------+
|    1 | jincheng |
|    2 | jindaxia |
|    3 | haofan   |
+------+----------+
3 rows in set (0.00 sec)

mysql> select * from tab4;
+------+----------+
| id   | name     |
+------+----------+
|    1 | jincheng |
|    2 | jindaxia |
|    3 | haofan   |
+------+----------+
3 rows in set (0.00 sec)

mysql>

7.6.2 利用视图操作基本表数据

由于视图是“虚表”，对视图数据进行的更新操作，实际上是对基本表数据进行更新操作。
注意：

对视图数据进行添加、删除时，直接影响基本表。
视图来自于多个基本表时，不允许添加和删除数据。

例: 添加数据操作

mysql> select * from tab4;
+------+----------+
| id   | name     |
+------+----------+
|    1 | jincheng |
|    2 | jindaxia |
|    3 | haofan   |
+------+----------+
3 rows in set (0.00 sec)

mysql> select * from v_test7;
+------+----------+
| id   | name     |
+------+----------+
|    1 | jincheng |
|    2 | jindaxia |
|    3 | haofan   |
+------+----------+
3 rows in set (0.00 sec)

mysql> insert into v_test7 (id,name) values (4,'JC-jindaxia');
Query OK, 1 row affected (0.06 sec)

mysql> select * from tab4;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | jincheng    |
|    2 | jindaxia    |
|    3 | haofan      |
|    4 | JC-jindaxia |
+------+-------------+
4 rows in set (0.00 sec)

mysql> select * from v_test7;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | jincheng    |
|    2 | jindaxia    |
|    3 | haofan      |
|    4 | JC-jindaxia |
+------+-------------+
4 rows in set (0.00 sec)

mysql>

例: 删除数据操作

mysql> select * from tab4;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | jincheng    |
|    2 | jindaxia    |
|    3 | haofan      |
|    4 | JC-jindaxia |
+------+-------------+
4 rows in set (0.00 sec)

mysql> select * from v_test7;
+------+-------------+
| id   | name        |
+------+-------------+
|    1 | jincheng    |
|    2 | jindaxia    |
|    3 | haofan      |
|    4 | JC-jindaxia |
+------+-------------+
4 rows in set (0.00 sec)

mysql> delete from v_test7 where id=4;
Query OK, 1 row affected (0.07 sec)

mysql> select * from v_test7;
+------+----------+
| id   | name     |
+------+----------+
|    1 | jincheng |
|    2 | jindaxia |
|    3 | haofan   |
+------+----------+
3 rows in set (0.00 sec)

mysql> select * from tab4;
+------+----------+
| id   | name     |
+------+----------+
|    1 | jincheng |
|    2 | jindaxia |
|    3 | haofan   |
+------+----------+
3 rows in set (0.00 sec)

mysql>

8. 触发器的操作

9 数据的操作

MySQL 中关于数据的操作，包括：插入数据记录、查询数据记录、更新数据记录、删除数据记录。

9.1 插入数据记录

通过 insert into 语句实现插入数据记录。

9.1.1 插入完整数据记录

insert into table_name (field1, field2, ... ,fieldn) values (value1, value2, ... , valuen);

其中：field 为全部的字段名，value 为数据记录，二者一一对应。在插入完整数据记录时，field 可以省略，如下所示。

insert into table_name values (value1, value2, ... , valuen);

9.1.2 插入数据记录的一部分

insert into table_name (field1, field2, ... ,fieldn) values (value1, value2, ... , valuen);

其中：field 为部分的字段名，value 为数据记录，二者一一对应。在插入部分数据记录时，field 不可以省略。

9.1.3 插入多条数据记录

可以一次插入一条数据记录，也可以一次插入多条数据记录。

插入多条完整数据记录

insert into table_name (field1, field2, ... ,fieldn) values 
	(value11, value12, ... , value1n),
	(value21, value22, ... , value2n),
	...
	(valuen1, valuen2, ... , valuenn);

其中：field 为全部字段名。同样，field 可以省略。

插入多条部分数据记录

insert into table_name (field1, field2, ... ,fieldn) values 
	(value11, value12, ... , value1n),
	(value21, value22, ... , value2n),
	...
	(valuen1, valuen2, ... , valuenn);

其中：field 为部分字段名。并且field 不可以省略。

9.1.4 插入查询结果

除了将数据值插入表中外，还可以将另一个表中的查询结果插入表中，从而实现数据值的复制功能。

insert into table_name1 (field11, field12, field13, ... , field1n)
	select (field21, field22, field23, ... , field2n) from table_name2 where 条件

其中，两个 field 列表的个数和类型必须一致，where 条件可以不指定。。

例：插入查询结果

mysql> select * from tab_int2;
Empty set (0.00 sec)

mysql> insert into tab_int2 (b) select (a) from tab_int1;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tab_int2;
+------+
| b    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql>

9.2 更新数据记录

通过 update 语句更新数据记录。

9.2.1 更新特定数据记录

更新指定字段的数据

update table_name set field1 = value1, field2 = value2 ... where 条件

where 条件可以不指定，不指定默认更新所有数据记录。

例：

mysql> select * from tab_int1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> update tab_int1 set a = 4 where a <= 2;
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from tab_int1;
+------+
| a    |
+------+
|    4 |
|    4 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql>

mysql> select * from tab_int2;
+------+
| b    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> update tab_int2 set b = 4;
Query OK, 3 rows affected (0.07 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from tab_int2;
+------+
| b    |
+------+
|    4 |
|    4 |
|    4 |
+------+
3 rows in set (0.00 sec)

mysql>

9.2.2 更新所有数据记录

语法与更新特定数据记录相同，只要将 where 条件涵括所有数据记录即可，或者不指定 where 条件。

9.3 删除数据记录

使用 delete 语句实现数据记录的删除。

9.3.1 删除特定数据记录

delete from table_name where 条件;

where 条件可以不指定，不指定默认更新所有数据记录。

例：

mysql> select * from tab_int1;
+------+
| a    |
+------+
|    4 |
|    4 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> delete from tab_int1 where a < 4;
Query OK, 1 row affected (0.09 sec)

mysql> select * from tab_int1;
+------+
| a    |
+------+
|    4 |
|    4 |
+------+
2 rows in set (0.00 sec)

mysql>

mysql> select * from tab_int1;
+------+
| a    |
+------+
|    4 |
|    4 |
+------+
2 rows in set (0.00 sec)

mysql> delete from tab_int1;
Query OK, 2 rows affected (0.13 sec)

mysql> select * from tab_int1;
Empty set (0.00 sec)

mysql>

9.3.2 删除所有数据记录

语法与删除特定数据记录相同，只要将 where 条件涵括所有数据记录即可，或者不指定 where 条件。

10 单表数据记录查询

12 使用运算符

MySQL 运算符包括算术运算符、比较运算符、逻辑运算符、位运算符。

12.1 为什么要使用运算符

对于数据库中的表对象，随着字段的设置，该对象中存储数据的意义已经确定，但是通过运算符可以另外含义的数据。

12.2 使用算术运算符

MySQL 支持的算术运算符

运算符	描述
+	加
-	减
*	乘
/（DIV）	除
%（MOD）	求余

例：算术运算符的使用

mysql> select 6+4 加法,
    -> 6-4 减法,
    -> 6*4 乘法,
    -> 6/4 除法,
    -> 6%4 求余;
+--------+--------+--------+--------+--------+
| 加法   | 减法   | 乘法   | 除法   | 求余   |
+--------+--------+--------+--------+--------+
|     10 |      2 |     24 | 1.5000 |      2 |
+--------+--------+--------+--------+--------+
1 row in set (0.01 sec)

mysql>

例：

mysql> select * from tab_sal;
+-------------+------+
| name        | sal  |
+-------------+------+
| jincheng    | 3000 |
| zhangyaling | 8000 |
+-------------+------+
2 rows in set (0.00 sec)

mysql> select name 雇员, sal 月薪, sal*12 年薪 from tab_sal;
+-------------+--------+--------+
| 雇员        | 月薪   | 年薪   |
+-------------+--------+--------+
| jincheng    |   3000 |  36000 |
| zhangyaling |   8000 |  96000 |
+-------------+--------+--------+
2 rows in set (0.00 sec)

mysql>

12.3 使用比较运算符

12.3.1 常用比较运算符

MySQL 支持的比较运算符

运算符	描述	用法
>	大于	x1 > x2
<	小于	x1 < x2
=（<=>）	等于	x1 = x2、x1 <=> x2
!=（<>）	不等于	x1 != x2、x1 <> x2
>=	大于等于	x1 >= x2
<=	小于等于	x1 <= x2
BETWEEN AND	存在于指定范围	x1 BETWEEN m AND n
IS NULL	为空	x1 IS NULL
IN	存在于指定集合	x1 IN (value1, value2, … , valuen)
LIKE	通配符匹配	x1 LIKE 表达式
REGEXP	正则表达式匹配	x1 REGEXP 正则表达式

12.3.2 特殊功能比较运算符

mysql> select
    -> 2 BETWEEN 1 AND 3 "BETWEEN AND",
    -> 5 IS NULL "IS NULL",
    -> 4 IN (3,4,5) "IN";
+-------------+---------+----+
| BETWEEN AND | IS NULL | IN |
+-------------+---------+----+
|           1 |       0 |  1 |
+-------------+---------+----+
1 row in set (0.00 sec)

mysql>

mysql> select * from tab1;
+----------+
| name     |
+----------+
| jincheng |
| jindaxia |
+----------+
2 rows in set (0.01 sec)

mysql> select * from tab1 where name LIKE "jin%";
+----------+
| name     |
+----------+
| jincheng |
| jindaxia |
+----------+
2 rows in set (0.00 sec)

mysql> select * from tab1 where name LIKE "jind%";
+----------+
| name     |
+----------+
| jindaxia |
+----------+
1 row in set (0.00 sec)

mysql>

REGEXP（正则表达式）比较运算符。所谓正则表达式，就是通过模式去匹配一类字符串。

MySQL 支持的模式字符

模式字符	含义
^	匹配字符串的开始部分
$	匹配字符串的结束部分
.	匹配字符串中的任意一个字符
[字符集合]	匹配字符集合中的任意一个字符
[^字符集合]	匹配字符集合外的任意一个字符
str1 \| str2 \| str3	匹配 str1、str2、str3 中的任意一个字符串
*	匹配字符，包含 0 个和 1 个
+	匹配字符，包含 1 个
字符串 {N}	字符串出现 N 次
字符串 {M, N}	字符串出现至少 M 次，至多 N 次

例：REGEXP

mysql> select 'jincheng' REGEXP '^j' 特定字符开头,
    -> 'jincheng' REGEXP '^jin' 特定字符串开头;
+--------------------+-----------------------+
| 特定字符开头       | 特定字符串开头        |
+--------------------+-----------------------+
|                  1 |                     1 |
+--------------------+-----------------------+
1 row in set (0.42 sec)

mysql>

mysql> select 'jincheng' REGEXP 'g$' 特定字符结尾,
    -> 'jincheng' REGEXP 'cheng$' 特定字符串结尾;
+--------------------+-----------------------+
| 特定字符结尾       | 特定字符串结尾        |
+--------------------+-----------------------+
|                  1 |                     1 |
+--------------------+-----------------------+
1 row in set (0.42 sec)

mysql>

mysql> select 'jincheng' REGEXP '^j......g$' 匹配5个任意字符;
+------------------------+
| 匹配5个任意字符        |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.01 sec)

mysql>

mysql> select 'jincheng' REGEXP '[abc]' 匹配字符集合中任意一个字符,
    -> 'jincheng' REGEXP '[a-zA-Z]' 匹配字符区间的任意一个字符,
    -> 'jincheng' REGEXP '[^abc]' 匹配字符集合外任意一个字符,
    -> 'jincheng' REGEXP '[^a-zA-Z]' 匹配字符区间任意一个字符;

+-----------------------------------------+-----------------------------------------+-----------------------------------------+--------------------------------------+
| 匹配字符集合中任意一个字符            | 匹配字符区间的任意一个字符             | 匹配字符集合外任意一个字              | 匹配字符区间任意一个字符            |
+-----------------------------------------+-----------------------------------------+-----------------------------------------+--------------------------------------+
|                                       1 |                                       1 |                                       1 |                                    0 |
+-----------------------------------------+-----------------------------------------+-----------------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql> select 'jincheng' REGEXP 'inc' 指定单个字符串,
    -> 'jincheng' REGEXP 'jin|nch|hen' 指定多个字符串;
+-----------------------+-----------------------+
| 指定单个字符串        | 指定多个字符串        |
+-----------------------+-----------------------+
|                     1 |                     1 |
+-----------------------+-----------------------+
1 row in set (0.00 sec)

mysql>

a*h 表示 h 之前是否有 a ，包含 0 个和 1 个。
a+h 表示 h 之前是否有 a ，包含 1 个。

mysql> select 'jincheng' REGEXP 'a*h',
    -> 'jincheng' REGEXP 'a+h',
    -> 'jincheng' REGEXP 'i*h',
    -> 'jincheng' REGEXP 'i+h';
+-------------------------+-------------------------+-------------------------+-------------------------+
| 'jincheng' REGEXP 'a*h' | 'jincheng' REGEXP 'a+h' | 'jincheng' REGEXP 'i*h' | 'jincheng' REGEXP 'i+h' |
+-------------------------+-------------------------+-------------------------+-------------------------+
|                       1 |                       0 |                       1 |                       0 |
+-------------------------+-------------------------+-------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

’aa’ REGEXP ‘a{0,1}’ 1？

mysql> select 'aa' REGEXP 'a{1}',
    -> 'aa' REGEXP 'a{2}',
    -> 'aa' REGEXP 'a{3}',
    -> 'aa' REGEXP 'a{1,3}',
    -> 'aa' REGEXP 'a{0,1}';
+--------------------+--------------------+--------------------+----------------------+----------------------+
| 'aa' REGEXP 'a{1}' | 'aa' REGEXP 'a{2}' | 'aa' REGEXP 'a{3}' | 'aa' REGEXP 'a{1,3}' | 'aa' REGEXP 'a{0,1}' |
+--------------------+--------------------+--------------------+----------------------+----------------------+
|                  1 |                  1 |                  0 |                    1 |                    1 |
+--------------------+--------------------+--------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql>

12.4 使用逻辑运算符

运算符	描述
AND（ && ）	与
OR（ \|\| ）	或
NOT（ ! ）	非
XOR	异或

例：

mysql> select 3 AND 4,
    -> 0 && 1,
    -> 2 && NULL,
    -> 0 && NULL;
+---------+--------+-----------+-----------+
| 3 AND 4 | 0 && 1 | 2 && NULL | 0 && NULL |
+---------+--------+-----------+-----------+
|       1 |      0 |      NULL |         0 |
+---------+--------+-----------+-----------+
1 row in set, 3 warnings (0.00 sec)

mysql>

mysql> select 1 || 2,
    -> 0 || 1,
    -> 0 OR NULL,
    -> 2 OR NULL;
+--------+--------+-----------+-----------+
| 1 || 2 | 0 || 1 | 0 OR NULL | 2 OR NULL |
+--------+--------+-----------+-----------+
|      1 |      1 |      NULL |         1 |
+--------+--------+-----------+-----------+
1 row in set, 2 warnings (0.00 sec)

mysql>

mysql> select !0,
    -> NOT 1,
    -> NOT NULL;
+----+-------+----------+
| !0 | NOT 1 | NOT NULL |
+----+-------+----------+
|  1 |     0 |     NULL |
+----+-------+----------+
1 row in set, 1 warning (0.00 sec)

mysql>

mysql> select 1 XOR 2,
    -> 0 XOR 1,
    -> 0 XOR 0,
    -> 1 XOR 1,
    -> 1 XOR NULL,
    -> 0 XOR NULL;
+---------+---------+---------+---------+------------+------------+
| 1 XOR 2 | 0 XOR 1 | 0 XOR 0 | 1 XOR 1 | 1 XOR NULL | 0 XOR NULL |
+---------+---------+---------+---------+------------+------------+
|       0 |       1 |       0 |       0 |       NULL |       NULL |
+---------+---------+---------+---------+------------+------------+
1 row in set (0.00 sec)

mysql>

12.5 使用位运算符

运算符
&	按位与
\|	按位或
~	按位取反
^	按位异或
<<	按位左移
>>	按位右移

例：

mysql> select 5&6,
    -> BIN(5&6),
    -> 4&5&6,
    -> BIN(4&5&6);
+-----+----------+-------+------------+
| 5&6 | BIN(5&6) | 4&5&6 | BIN(4&5&6) |
+-----+----------+-------+------------+
|   4 | 100      |     4 | 100        |
+-----+----------+-------+------------+
1 row in set (0.00 sec)

mysql>

mysql> select 5|6,
    -> BIN(5|6),
    -> 4|5|6,
    -> BIN(4|5|6);
+-----+----------+-------+------------+
| 5|6 | BIN(5|6) | 4|5|6 | BIN(4|5|6) |
+-----+----------+-------+------------+
|   7 | 111      |     7 | 111        |
+-----+----------+-------+------------+
1 row in set (0.00 sec)

mysql>

mysql> select BIN(4),
    -> ~4,
    -> BIN(~4);
+--------+----------------------+------------------------------------------------------------------+
| BIN(4) | ~4                   | BIN(~4)                                                          |
+--------+----------------------+------------------------------------------------------------------+
| 100    | 18446744073709551611 | 1111111111111111111111111111111111111111111111111111111111111011 |
+--------+----------------------+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql> select BIN(4),
    -> BIN(5),
    -> 4^5,
    -> BIN(4^5);
+--------+--------+-----+----------+
| BIN(4) | BIN(5) | 4^5 | BIN(4^5) |
+--------+--------+-----+----------+
| 100    | 101    |   1 | 1        |
+--------+--------+-----+----------+
1 row in set (0.00 sec)

mysql>

mysql> select BIN(6),
    -> 6<<1,
    -> BIN(6<<1),
    -> 6>>1,
    -> BIN(6>>1);
+--------+------+-----------+------+-----------+
| BIN(6) | 6<<1 | BIN(6<<1) | 6>>1 | BIN(6>>1) |
+--------+------+-----------+------+-----------+
| 110    |   12 | 1100      |    3 | 11        |
+--------+------+-----------+------+-----------+
1 row in set (0.00 sec)

mysql>