MySQL

安装MySQL

MySQL官网 https://www.mysql.com。

点击第二行的DOWNLOADS。

向下滑，点击MySQL Community (GPL) Downloads »。

点击第一列第四个MySQL Community Server。

Operating system选windows 。

点击下面other downlaods部分的 Windows (x86, 64-bit), ZIP Archive右边的download。

点击最下面的No thanks, just start my download.。

解压后放到任一存储目录。

然后在系统变量中Path的路径下新建一个路径 路径写数据库根目录下的bin文件夹。

然后以管理员身份运行cmd。

先输入mysqld --initialize 目的是初始化MySQL。

然后输入mysqld --install安装MySQL，即可安装成功。

初始密码设置

找到MySQL根目录下的data文件夹，找到xxx.err文件，搜索里面的A temporary password is，后面的就是初始密码。

之后使用mysql -u root -p，输入密码后成功登录MySQL。

若提示“ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost:3333' (10061)，则是MySQL服务为启动，要先启动MySQL服务再尝试连接MySQL。

然后执行下面的语句。

set password for root @localhost = newpwd;  -- newpwd指新密码，需要是字符串

若用户名为root，则修改的是管理员密码。

至此数据库安装完成。

数据库基础概念

数据库是按照数据结构组织、存储和管理数据的仓库，是存储在一起的相关数据的集合。其优点主要体现在以下一些方面。

1. 减少数据的冗余度，节省数据的存储空间。
2. 具有较高的数据独立性和易扩充性。
3. 实现数据资源的充分共享。

数据库的基础概念

数据库系统

数据库系统是采用数据库技术的计算机系统，是由数据库（数据）、数据库管理系统（软件）、数据库管理员（人员）、硬件平台（硬件）和软件平台（软件）5个部分组成的运行实体。其中，数据库管理员是对数据库进行规划、设计、维护和监视等的专业管理人员，在数据库系统中起着非常重要的作用。

数据库管理系统

数据库管理系统是数据库系统的一个重要组成部分，是位于用户和操作之间的一层数据管理软件，负责数据库的数据组织、数据操纵、数据维护和数据服务等，主要有以下功能。

1. 数据存储的物理构建：为数据模式的物理存取与构建提供有效的存取方法及手段。

2. 数据操纵功能：为用户使用数据库的数据提供方便，如查询、插入、修改、删除等以及简单的算术运算和统计。

3. 数据定义功能：用户可以通过数据库管理系统提供的数据定义语言方便地对数据库中对象进行定义。

4. 数据库的运行管理：数据库的管理系统统一管理数据库的运行和维护，以保障数据的安全性、完整性、并发性和故障的系统恢复性。

5. 数据库的建立和维护功能：数据库管理系统能完成初始数据的输入和转换、数据库的转储和恢复、数据库的性能监视和分析等任务。

关系数据库

关系数据库是支持关系模型的数据库。关系模型由关系数据结构、关系操作集合和完整性约束3个部分组成。

1. 关系数据结构：在关系模型中数据结构单一，现实世界的实体以及实体间的联系均用关系来表示，实际上关系模型中数据结构就是一个二维表。
2. 关系操作集合：关系操作分为关系代数、关系演算、具有关系代数和关系演算双重特点的语言（SQL）。
3. 完整性约束：包括实体完整性、参照完整性和用户定义完整性。

数据库常用对象

在MySQL数据库中，表、字段、索引、视图和存储过程等具体存储数据或对数据进行操作的实体都叫做数据库对象，下面介绍几种常用的数据库对象。

1. 表 表是包含数据库中所有数据的数据库对象，由行和列组成，用于组织和存储数据。
2. 字段 表中每列成为一个字段，字段具有自己的属性，如字段类型、大小。其中字段类型是最重要的属性，它决定了该字段能存储什么类型的数据。
3. 索引 索引是一个单独的、物理的数据库结构。它是依赖于表建立的，有了它，数据库无须对整个表进行扫描就能找到想要的数据。
4. 视图 视图是从一张表或多张表中导出的表（也叫虚拟表），是用户查看数据表中数据的一种方式。表中包括几个被定义的数据列与数据行，其结构和数据建立在对表的查询基础之上。
5. 存储过程 存储过程是一组为了完成特定功能的SQL语句集合（包含查询、插入、删除、更新等操作），经编译后以名称的形式存储在MySQL服务器端的数据库中，由用户通过指定存储过程的名字来执行。当这个存储过程被调用执行时，这些操作也会同时执行。

本节介绍MySQL中的数据类型。

数字类型

MySQL支持所有的ANSI/ISO 92 数字类型，包括准确数字的数字类型以及近似数字的数字类型。

数据类型主要可以分为整型和浮点型两种数据类型，如下：

1. 整型

2. 浮点型

其中DECIMAL是精度确定的小数，比如想要指定总位数为8、小数位数为2的数就可以使用DECICAL(8,2)，其中8就表示总共有8位数，2表示其中有2位小数。

FLOAT和DOUBLE存在误差问题，尽量避免浮点数比较，若要使用货币等高精度的数字则应该使用DECIMAL数据类型。

字符串类型

字符串类型可以分为3类：普通的文本类型（CHAR和VARCHAR）、可变类型（BLOB和TEXT）、和特殊类型（SET和ENUM）。

1. 普通文本类型：即CHAR和VARCHAR类型，CHAR列的长度被固定为创建表声明的长度，取值在1-255；VARCHAR列的值是变长的字符串，取值和CHAR一样。

存储字符串长度相同的全部使用CHAR类型；字符长度不相同的使用VARCHAR类型，不预先分配存储空间，长度不要超过255。

2. 可变类型：大小可以改变，TEXT类型适合存储长文本，BLOB适合存储二进制数据，支持任何数据，如声音、文本和图像等。

3. 特殊类型：ENUM和SET类型。

误区警示：BLOB、TEXT、ENUM和SET字段类型在MySQL中的检索速度不高，很难使用索引进行优化。如果必须使用这些类型，一般采用特殊的结构设计，或者与程序相结合使用其他的字段类型替代。例如，SET类型可以使用整型（0、1、2、3、…）注释功能和程序的检查功能集合替代。

若速度优先、要选择固定的列，则选择CHAR类型；若节省空间、使用动态的列，则使用VARCHAR类型；要将列的内容限制在一种选择则选择ENUM类型；允许一个列有多于一个的条目则选择SET，若字符串不区分大小写则选择TEXT类型，若区分大小写则选择BLOB类型。

日期和时间类型

日期和时间类型包括DATE、DATETIME、TIME、TIMESTAMP和YEAR。其中的每种类型都有其取值范围，如赋予它一个不合法的值，将会被“0”替代。

在MySQL中，日期的顺序是严格按照标准的ANSI SQL格式输出的。

查询MySQL中支持的中文引擎

查询支持的全部中文引擎

MySQL中的数据使用不同的技术存储在文件中（或者内存中），每一种技术都使用不同的存储机制，索引技巧，锁定水平并且最终提供应用广泛的、不同的功能和能力。通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体功能。

在MySQL中，可以使用SHOW ENGINE语句查询MySQL中支持的存储引擎，查询语句如下：

SHOW ENGINES\g

注：使用\g与使用;效果是一样的，使用\G可以使结果更美观。

SHOW ENGINES\G

同时，使用如下的命令可以查看默认的存储引擎：

SHOW VARIABLES LIKE 'storage_engine%';

常见的存储引擎

InnoDB存储引擎

优点 特别适合处理多重处理的更新请求；是唯一支持事务的标准MySQL存储引擎，这是管理敏感数据（如金融信息和用户注册信息）所必需的；与其他存储引擎不同，InnoDB表能自动从灾难中恢复，虽然MyISAM表也能在灾难后修复，但是过程要长得多。

MyISAM存储引擎

1. 文件类型

   .frm 存储表的结构

   .MYD 存储数据，是MYData的缩写

   .MYI 存储索引，是MYIndex的缩写

2. 存储格式

   • MyISAM静态
   • MyISAM动态
   • MyISAM压缩

优点 占用空间小，处理速度快

缺点 不支持事务的完整性和并发性

MEMORY存储引擎

1. 文件存储形式

   每个基于MEMORY存储引擎的表是一个文件。

2. 索引类型

   默认使用哈希（HASH）索引，其速度要比B树（BTREE）索引快。

3. 存储周期

   存储在内存上，一旦重启机器或者关机，表中的所有数据都会消失。

4. 优缺点

   缺点 表的大小有限制；不支持VARCHAR、BLOB、TEXT数据类型。

5. 什么时候选择此存储引擎

   暂时 目标数据只是临时需要，在其生命周期中必须立即可用

   相对无关 存储在MEMORY表中的数据如果突然丢失，不会对应用服务产生负面影响，不会对数据完整性有长期影响。

选择存储引擎

InnoDB 用于事务处理应用程序，具有众多特性，包括支持ACID事务、外键、崩溃修复能力和并发控制。如果对事务的完整性要求比较高，要求实现并发控制，那么选择此引擎有很大优势。如果需要进行频繁更新，删除数据库的操作，也可以选择此引擎，因为此引擎可以实现事务的实现和回滚。

MyISAM 管理非事务表，它提供高速存储和检索，以及全文搜索能力。此引擎插入数据快，空间和内存使用比较低。如果表主要是插入新记录和读出记录，那么选择此引擎可以实现处理的高效率，如果应用的完整性、并发性很低，也可以选择此引擎。

MEMORY引擎 此引擎提供“内存中”的表，其所有数据都在内存中，数据的处理速度快，但安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMORY存储引擎。MEMORY存储引擎对表的大小有要求，不能建太大的表。所以，这类数据库只使用较小的数据库表。

系统数据库

系统数据库是指在安装完MySQL服务器后系统自动创建的一些服务器后，会附带一些数据库，例如，在默认安装MySQL数据库之后，会默认创建如下数据库，它们就被称为系统数据库，这些数据库存储了必须的系统信息，用户不能直接修改。

information_schema数据库：主要存储MySQL服务器所有数据库的信息，如数据库的名，数据库的表，访问权限，数据库表的存储类型，数据库索引信息等。

mysql数据库：是MySQL的核心数据库，主要负责存储数据库的用户，权限设置，关键字等MySQL自己需要使用的控制和管理信息。

performance_schema数据库：主要收集数据库主要性能参数，可用于监控数据库服务器在一个较低级别的运行过程中的资源消耗，资源等待等状况。

sys数据库：它所有的数据源都来自performance_schema，目标是把performance_schema的复杂度降低，让DBA能更好地阅读这个库里的内容，让DBA更快地了解DB的运行情况。

创建数据库

在MySQL中，可以使用CREATE DATABASE语句和CREATE SCHEMA语句创建MySQL数据库，语法如下：

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] 数据库名
[
    [DEFAULT] CHARACTER SET [=] 字符集 |
    [DEFAULT] COLLATE [=] 校对规则名称
];

注：在语法中，花括号“{}”表示必选项，中括号“[]”表示可选项，“|”表示分隔符两侧内容为“或”的关系。在上方的语法中，{DATABASE|SCHEMA} 表示要么使用DATABASE关键字或者使用SCHEMA关键字，但不能不使用。

其中，数据库名必须指定，在文件系统中，MySQL的数据存储区将以目录方式表示MySQL数据库，因此这里的数据库名必须符合系统文件夹的命名规则，而在MySQL中是不区分大小写的。

[DEFAULT]：可选项，表示指定默认值。

CHARCTER SET [=] 字符集：用于指定数据库的字符集，若不想指定字符集则可以不添加，字符集可以是UTF-8，GBK，GB2312，BIG5等。

COLLATE [=] 校对规则名称：可选项，用于指定字符集的校对规则，例如utf8_bin或者gbk_chinese_bi。

CREATE DATABASE 创建数据库

create database db_admin;

CREATE SCHEMA 创建数据库

create schema db_admin1;

创建带指定字符集的数据库

create database db_admin2 character set utf8;

创建数据库之前检测是否已经存在同名数据库

create database db_admin if not exists;

创建此数据库之前，由于同名的数据库已经存在，所以并没有创建成功。

查看数据库

成功创建数据库之后，使用show命令查看MySQL服务器中的所有数据库信息，语法如下：

SHOW {DATABASES|SCHEMAS} [LIKE '模式' WHERE '条件'];

其中，LIKE是可选项，用于指定匹配模式。

WHERE也为可选项，用于指定数据库名称查询范围的条件。

查看上方创建的数据库

show databases;

筛选以db开头的数据库

show databases like 'db_%';

选择数据库

在MySQL中，创建数据库后，该数据库并不会自动成为当前数据库，需要使用MySQL提供的USE语句，语法如下：

USE 数据库名;

选择一个数据库

use db_admin;

修改数据库

在MySQL中，创建一个数据库后，还可以对齐修改，如修改编码格式，修改校对规则等，语法·规则如下：

ALTER {DATABASE|SCHEMA} [数据库名]
[DEFAULT] CHARACTER [=] 字符集
[DEFAULT] COLLATE [=] 校对规则民称;

更改db_admin中的编码和校对规则

alter database db_admin default character set gbk default collate gbk_chinese_ci;

删除数据库

在MySQL中，可以使用DROP DATABASES或者DROP SCHEMA删除已经存在的数据库，在删除数据库之后，该数据库之中的所有数据表以及所有数据都会删除且无法撤销，因此在删除之前要多加思考。删除数据库的语法如下：

DROP {DATABASES|SCHEMA} [IF EXISTS] 数据库名;

删除数据库db_admin

drop database db_admin;

拿Windows系统的文件系统进行类比的话，如果说数据库是文件系统中的文件夹，那么数据表就是文件夹中的文件。在MySQL中，数据库里可以存放多张数据表。在对数据表进行操作之前，必须使用USE语句选择数据库，才可以在指定的数据库中对数据表进行操作，如创建数据表，修改表结构，以及重命名、复制或者删除数据表等，否则将无法对数据表进行操作。

创建数据表

创建数据表使用CREATE TABLE语句，语法如下：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 数据表名 [{create_difinition,...}][table_options][select_statement];

其中，TEMPORARY代表若使用此关键字，那么将创建一个临时表。

create_definition是表的列属性部分，MySQL要求在创建表时至少包含一列。

table_options是表的一些特性参数，其中大部分选项涉及的是表数据如何存储以及存储在何处，如ENGINE选项用于定义表的存储引擎。多数情况下，用户不必指定表选项。

select_statement是SELECT语句描述部分，用于快速创建表。

下面介绍列属性create_statement部分，每一列定义的具体格式如下：

col_name type [NOT NULL|NULL] [DEFUALT default_value] [AUTO_INCREAMENT] [PRIMARY_KEY] [reference_definition]

其中，col_name代表字段名。

type代表字段类型。

NOT NULL|NULL代表该列是否允许是空值，系统一般允许是空值，所以若不想允许为空值必须使用NOT NULL。

DEFAULT default_value代表默认值。

AUTO_INCREMENT代表是否自动编号，每个表只能有一个该列，且必须被索引。

PRIMARY_KEY表示是否为主键，一个表只能有一个主键。若表中没有主键，而某些应用程序需要主键，MySQL将返回第一个没有任何NULL列的UNIQUE键作为主键。

reference_definition代表为字段添加注释。

创建数据表的简洁形式

CREATE TABLE table_name (列名1 属性, 列名2 属性);

创建第一个数据表

create table tb_first (id int primary key not null auto_increment,name varchar(4));

第一行表示使用db_admin数据库，第二行表示创建数据表，名字叫做tb_first（tb是table）的缩写，其中id是第一个字段名，int是该字段的数据类型，即整型，primary key表示该字段为主键，not null表示该字段不能为NULL值，auto_increment代表该字段可以自动增长，后面的name为第二个字段名，类型为变长字符串，最长为4个字符。

查看表结构

对于一个创建成功的数据表，可以使用SHOW COLUMNS或者DESCRIBES语句查看指定数据表的表结构。

SHOW COLUMNS

在MySQL中，使用SHOW COLUMNS语句可以查看表结构，基本语法如下：

SHOW [FULL]COLUMNS FROM 数据表名 [FROM 数据库名];

在上面我们创建了数据表tb_first数据表，里面有有两个字段：id和name，下面我们尝试读取这几个字段：

show columns from tb_first;

DESCRIBES

在MySQL中，还可以使用DESCRIBE语句查看数据表结构，基本语法如下：

DESCRIBE 数据表名;

其中，DESCRIBE可以简写成DESC，在查看表结构时，也可以只列出某一列的信息，基本语法如下：

DESCRIBE 数据表名 列名;

使用DESC查看数据表结构

DESC tb_first;

修改表结构

修改表结构指增加或者删除字段、修改字段名或字段类型以及修改表名等，可以使用ALTER TABLE语句实现，语法如下：

ALTER [IGNORE] TABLE 数据表名 alter_spec[,alter_spec]...| table_options;

其中，[IGNORE]是可选项，表示如果出现重复的行，则只执行一行，其他的行将被删除。

alter_spec子句表示定义要修改的内容，基本语法如下：

ADD [COLUMN] create_difinition [FIRST|AFTER column_name]  -- 添加新字段
| ADD INDEX [index_name](index_col_name,...)  -- 添加索引名称
| ADD PRIMARY KEY (index_col_name)  -- 添加主键名称
| ADD UNIQUE [index_name](index_col_name)  -- 添加唯一索引
| ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT}  -- 修改字段默认值
| CHANGE [COLUMN] old_col_name create_definition  -- 修改字段名/类型
| MODIFY [COLUMN] create_definition  -- 修改子句定义字段
| DROP [COLUMN] col_name  -- 删除某一字段
| DROP PRIMARY KEY  -- 删除主键
| DROP INDEX index_name  -- 删除某一索引
| RENAME [AS] new_tbl_name  -- 更改表名
| COMMENT 'comment'  -- 给表添加注释

其中，create_definition用于定义列的数据类型和属性，与CREATE TABLE语句中的语法相同。

[FIRST|AFTER column_name] 用于指定位于哪个字段的前面还是后面，使用FIRST关键字时表示位于指定字段的前面；使用AFTER关键字时表示位于哪个字段的后面；column_name表示字段名。

[index_name] 是可选项，用于指定索引名。

{index_col_name}用于指定索引列名。

{SET DEFAULT literal|DROP DEFAULT}子句表示为字段设置或者删除默认值，其中literal参数要设置的默认值。

old_col_name表示指定要修改的字段名。

new_tbl_name表示指定新的表名。

table_options表示指定表的一些特性参数，其中大多数选项涉及的是表数据如何存储以及存储在何处，如ENGINE选项表示定义表的存储引擎。多数情况下，用户不必指定表选项。

添加新字段和修改字段定义

在MySQL的ALTER TABLE中，可使用ADD [COLUMN] create_definition [FIRST|AFTERT column_name]子句来添加新字段；使用MODIFY [COLUMN] create_definition子句修改已定义的字段的定义，下面通过实例演示如何为一个已有表添加新字段，并修改已有字段的定义。

添加新字段并修改已有字段的定义

首先查看已有的字段以及它们的描述。

DESC tb_first;

下面我们新增加一个字段叫做create_time，意为创建时间，并且将name的类型改为varchar(10)。

alter table tb_first add column (create_time datetime default now() not null),modify column name varchar(10);

最后再查看表的描述。

DESC tb_first;

可以看到name的字段类型已经改为长度为10的变长字符串，且新增添了一个字段create_time。

修改表的名称

使用ALTER语句将tb_first名字修改为table_first。

alter table tb_first rename as table_first;

重命名表

在MySQL中，可以使用RENAME TABLE语句将表重命名，语法如下。

RENAME TABLE 数据表名1 To 数据表名2;

如将table_first命名为tb_first。

rename table table_first to tb_first;

复制表

创建表的CREATE TABLE语句还有另外一种语法结构：在一个已经存在的数据表基础上创建该表的备份，也就是复制表，语法如下。

CREATE TABLE [IF NOT EXISTS] 数据表名 {LIKE 源数据表名|{LIKE 源数据表名}}；

现在创建第二个表，名字叫做tb_second，源数据表就是tb_first。

create table if not exists tb_second like tb_first;

删除表

删除数据表的操作很简单，同删除数据库的操作类似，使用DROP TABLE语句即可实现，语法如下。

DROP TABLE [IF EXISTS] 数据表名;

如我们删除数据表tb_second。

drop table if exists tb_second;

在删除数据表的时候要特别小心，删除数据表之后表中的数据都会被一并删除。

一些操作的语法，可直接套用

为字段添加约束

如为表添加外键约束（外键约束请见《数据完整性约束》一章）。

alter table tb_name add 
(contraint `fri_key_name`  --外键名
    foreign key (col_name) --参照表的字段名
reference tb_name  --被参照表名
    (col_name)  --被参照表的字段名
    [on delete reference_option]
    [on update reference_option]
);

算术运算符

算术运算符是MySQL中最常用的一种运算符。MySQL中支持的运算符如下表。

加减乘运算符可以同时运算多个操作数。除号和求余运算符也可以同时运算多个操作数，但不建议使用。DIV和MOD这两个运算符只有两个参数，进行除法和求余的运算时，若x2为空值则会返回NULL。

使用加减乘除和求余计算两个操作数的结果

我们首先创建一个表，然后创建两列，第一个操作数为7，第二个操作数为2，然后计算，查看结果。

（下面代码中insert语句和select语句后面会讲到）

create table demo (num1 int,num2 int);
insert into demo (num1,num2) values (7,2);
select num1,num2,num1+num2,num1-num2,num1*num2,num1/num2,num1%num2 from demo;

比较运算符

比较运算符在查询数据时常用的一种运算符，SELECT语句中经常用到比较运算符。MySQL中常用的比较运算符如下表。

下面对常用的比较运算符进行详解。

在进行演示前，我们先创建如下的数据表，并且输入如图的数据。

1. 运算符“=”

它用来判断数字、字符串和表达式是否相等。如果相等返回1；否则返回0。如：

select * from tb_demo where id = 1;

2. 运算符“<>”和“!=”

这两个不等于符号可以用来判断数字、字符串和表达式等是否不相等，如果不相等则返回1；否则返回0。如：

select * from tb_demo where id != 1;

3. 运算符“>”

大于号可以用来判断左边的操作数是否大于右边的操作数。如：

select * from tb_demo where id > 2;

“<”、“>=”、“<=”等的用法与“>”基本相同，这里不再赘述。

4. 运算符 IS NULL

IS NULL用于判断操作是否是空值，操作数为空值时结果返回1，否则返回0。如：

select * from tb_demo where username is NULL;

5. 运算符 BETWEEN AND

BETWEEN AND运算符用于判断数据是否在某个取值范围内，表达式如下：

x BETWEEN x1 AND x2

若x大于x1小于x2则返回1，否则返回0。如：

select * from tb_demo where id between 1 and 3;

6. 运算符IN

IN用于判断数据是否存在于某个集合中，表达式如下：

x IN (x1,x2,...,xn)

若x的值等于序列中的任意一个则返回1，否则返回0。如

select * from tb_demo where username in ('ab','abc','abcd');

7. 运算符 LIKE

LIKE可用于匹配字符串，表达式如下：

x LIKE s

若x与s匹配则返回1，否则返回0。如：

select * from tb_demo where password like '%bcde';

8. 运算符REGEXP

REGEXP同样用于匹配字符串，但其使用的是正则表达式，表达式如下：

x REGEXP s

若x满足s表示的匹配方式则返回1，否则返回0。如：

select * from tb_demo where password regexp '^a';

逻辑运算符

逻辑运算符用来判断表达式的真假，若表达式为真则返回1，否则返回0。MySQL中支持四种逻辑运算符：与（&&或AND）、非（!或NOT）、或（||或OR）、异或（XOR）。下面分别介绍这四种运算符。

1. 与运算

“&&”和AND是与运算的两种表达方式。如果所有数据不为0且不为空值NULL，结果返回1；如果存在任何一个数据为0，结果返回0；如果有一个数据为NULL且没有数据为0时返回NULL。可以有多个操作数同时进行与运算。

2. 或运算

“||”或者OR表示或运算。所有数据中存在任何一个数据为非0的数字时返回1；如果数据中不包含非0的数字但包含NULL，结果返回NULL；如果数据中有结果返回0。可以有多个操作数同时进行或运算。

3. 非运算

“!”或者NOT表示非运算。通过非运算，表达式会返回一个与操作数据相反的结果。若数据为非0的数字则结果返回0；若数据为0则结果返回1；若操作数是NULL则返回NULL。

4. 异或运算

XOR表示异或运算，只要其中任何一个操作数为NULL则结果返回NULL；若两个操作数都为非0值则结果返回0；若两个操作数有一个不为0则结果返回1。

如：

位运算符

MySQL中的位运算符如下：

如将4和6分别进行按位与、按位或、按位异或运算，将4进行按位取反运算。

运算符优先级

运算符优先级如下表。

添加数据

使用INSERT...VALUES语句插入新记录

语法：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] 数据表名 [(字段名,...)]
    VALUES ({值 | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE 字段名=表达式, ... ]

1. 插入一条记录

首先来看下表的结构，如下图。

代码如下。

insert into demo_insert values(1,'张三','zhangsan','2000-01-01 12:00:00');

2. 插入部分语句

为没有自动增长或默认值等字段添加记录，代码如下。

insert into demo_insert (user,password,createtime) values ('李四','lisi','2014-01-01 12:00:00');

3. 插入多条记录

代码如下（输入此代码之前删除了之前的记录）。

insert into demo_insert
(user,password,createtime)
values
('张三','zhangsan','2015-01-01 12:00:00'),
('李四','lisi','2014-01-01 12:00:00'),
('王五','wangwu','2014-01-02 00:00:00');

使用INSERT ... SET插入数据

语法：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] 数据表名
    SET 字段名={值 | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE 字段名=表达式,...]

该语法相比于INSERT ... INTO，在于它直接设置字段的值，出错率更小。

如添加上面实例1的数据。

INSERT INTO demo_insert set id=1,user='张三',password='zhangsan',createtime='2000-01-01 12:00:00';

使用INSERT ... SELECT添加数据

语法：

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] 数据表名 [(字段名,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE 字段名=表达式, ... ]

其中，SELECT子句表示快速从一个或多个表中快速取出数据，并将这些数据作为行数据插入目标数据表。SELECT子句返回的结果集中的字段数和字段类型，必须和目标数据表完全一致。

例：先查询源表中数据。

select * from demo_table;

添加。

insert into demo_insert select * from demo_table;

修改数据

使用UPDATE语句可以修改数据，语法如下。

UPDATE [LOW_PRIORITY][IGNORE] 数据表名
SET 字段1=值1[,字段2=值2...]
[WHERE 条件表达式]
[ORDER BY...]
[LIMIT 行数];

需要特别注意，WHERE千万不能出错，否则会破坏所有已经更改的数据。

如将下图表中id为1的用户名改为王六。

 update demo_update set user='王六' where id=1;

删除数据

通过DELETE语句删除数据

语法如下：

DELETE [LOW_PRIORITY][QUICK][IGNORE] FROM 表名
[WHERE 条件表达式]
[ORDER BY ...]
[LIMIT 行数];

如删除如下数据中id为3的数据行。

delete from demo_delete where id=3;

使用TRUNCATE TABLE语句删除数据

如果要删除所有表中的行，可通过该语句删除数据。语法如下：

TRUNCATE [TABLE] 数据表名;

例：

基本查询语句

SELECT语句是最常用的查询语句，它的使用方式有点复杂，但是功能非常强大。语法如下。

select selection_list
from 数据表名
where primary_columns
group by grouping_columns
order by sorting_columns
having secondary_constraint
limit count;

其中的子句将在后面介绍。

使用SELECT语句查询一个数据表

使用具体的字段名查询该字段，或者用*号查询全字段。如：

select * from tb1;

查询表中部分数据

select c1,c3 from tb1;

查询多个表中的数据

select c1,c2,c3,c4,c5 from tb1,tb2;

单表查询

查询所有字段

语法：

SELECT * FROM 表名;

如：

select * from tb1;

查询指定字段

语法：

SELECT 字段名 FROM 表名;

如：

select c1,c3 from tb1;

查询指定数据

可以通过使用WHERE子句来设定查询条件。如：

select * from tb1 where c1>1;

带关键字IN的查询

关键字IN可以判断某个字段的值是否在某一集合中，语法：

SELECT * FROM 表名 WHERE 条件 [NOT] IN (元素1,元素2,...);

如：

select * from tb1 where c1 in (0,1,2);

带关键字BETWEEN AND的范围查询

关键字BETWEEN AND可以判断某个字段的值是否在指定的范围之内，语法：

SELECT * FROM 表名 WHERE 条件 [NOT] BETWEEN 取值1 AND 取值2;

如：

select * from tb1 where c1 between 1 and 3;

带LIKE的字符匹配查询

LIKE是比较常用的比较运算符，可以实现模糊查询，它有“%”、“_”两种通配符。

“%”表示匹配一个或者多个字段，可以代表任意长度的字符串，长度可以为0。

“_”表示只匹配一个字符。

如下面的语句代表查找字段以bc为结尾的数据行：

select * from tb3 where name like '%bc';

用IS NULL查找空值

IS NULL关键字可以判断字段的值是否为空值，语法如下：

IS [NOT] NULL

如查找表中不是NULL的字段：

select * from tb3 where name is not null;

带关键字AND的多条件查询

语法：

SELECT * FROM 数据表名 WHERE 条件1 AND 条件2 [...AND 条件表达式n];

如：

select * from tb1 where id>0 and id<2;

带OR的多条件查询

语法：

SELECT * FROM 数据表名 WHERE 条件1 OR 条件2[...OR 条件表达式n];

如：

select * from tb1 where c1>2 or c2<'b';

用DISTINCT关键字去除结果中的重复行

语法：

SELECT * DISTINCT 字段名 FROM 表名;

如：

去重前：

使用DISTINCT去重后：

使用ORDER BY关键字对查询结果排序

语法：

ORDER BY [ASC|DESC]

其中，ASC表示按照升序排序，DESC表示按照降序排序。

如：

select * from d order by a asc;

select * from d order by a desc;

用GROUP BY关键字分组查询

1. 单独使用GROUP BY关键字分组

如：

select a,count(*) from d group by a;

2. GROUP BY关键字和GROUP_CONCAT()函数一起使用

通常情况下，GROUP BY关键字和聚合函数一起使用。

如：

select a,group_concat(a) from d group by a;

3. 按多个字段分组

若第一个字段有相同值时再按照第二个字段分组。

select * from d group by b,a;

用LIMIT限制输出结果的数量

如：

select * from d limit 3;

聚合函数查询

COUNT()函数

COUNT()的参数若为非“*”的参数，返回所选集合中非NULL值的行的数目。对于参数“*”，返回集合中所有行的数目，包含NULL值的行。没有WHERE子句的count(*)是经过内部优化的，能够快速返回表中所有数据的总数。

如：

select count(*) from d;

SUM()函数

SUM()函数可以求出表中某个数值类型字段取值的总和。

如：

select *,sum(a) from d;

AVG函数

AVG()函数可以返回某个字段的平均值。

如：

select AVG(a) from d;

MAX()函数

MAX()函数可以返回某个字段的最大值。

如：

select max(a) from d;

MIN()函数

MIN()函数可以返回某个字段的最小值。

select min(a) from d;

连接查询

连接是把不同的表的记录连接到一起，MySQL从一开始就很好地支持连接操作。

内连接查询

内连接是普遍的连接类型，而且是最匀称的，它们要求构成连接的每个表的共有列匹配，不匹配的行将被删除。

select book_list.bookid,book_list.bookname,book_list.author,book_borrow.borrowtime,book_borrow.borrower from book_list,book_borrow where id=bookid;

上面代码中，设置了若book_list中的书的“bookid”和book_borrow中借书的“id”相等才连接。

外连接查询

外连接就是使用OUT JOIN关键字连接两个表。选取一个主表，然后在选取一个副表，将主表和副表连接起来。语法如下：

SELECT 字段名称 FROM 表名1 LEFT|RIGHT JOIN 表名2 ON 表名1.字段名1=表名2.属性名2;

• 使用左外连接：

左外连接是将左表中所有数据分别都与右表中每条数据相连接组合，返回的结果除了左连接的数据以外，还包括左表中不符合条件的数据，并在右表中不符合条件的位置添加NULL。

select book_list.bookid,book_list.bookname,book_list.author,book_borrow.borrowtime,book_borrow.borrower from book_borrow left join book_list on bookid=id;

针对这里的图书信息表和借阅表，内连接和左外连接得到的结果一样。这是因为左表（借阅表）中的数据在右表 你 （图书信息表）中一定有之相对应的数据。如果将图书信息表作为左表，借阅表作为右表，就会得到如下图的数据。

• 使用右外连接

右外连接是指将右表中的所有数据分别与左表中的每条数据进行连接组合，返回的结果除内连接的数据以外，还包括右表中不符合条件的数据，并在左表的相应列中添加NULL。

select book_list.bookid,book_list.bookname,book_list.author,book_borrow.borrowtime,
book_borrow.borrower from book_borrow right join book_list on bookid=id;

• 外连接的列顺序还是根据select后面的字段顺序保持一致，也就是说左外连接和右外连接不会改变字段的顺序。
• 若为左连接，那么左表是LEFT JOIN左边的表，也是主表，若为右连接，那么右表是RIGHT JOIN右边的表，也是主表。

复合条件连接查询

在连接查询时，也可以添加其他的查询条件，使查找结果更加准确。

select book_list.bookid,book_list.bookname,book_list.author,book_borrow.borrowtime,book_borrow.borrower from book_list,book_borrow where id=bookid and isback=0;

子查询

子查询就是一个SELECT语句查询另一个SELECT语句的附属。MySQL中支持嵌套多个查询，在里面查询的结果可以作为外面查询的条件。

带关键字IN的子查询

只有在子查询返回至少一个结果时才可以使用带IN关键字的子查询。IN关键字用于检测结果集中是否存在某个特定的值，若检测成功则执行外部查询。

在下面的例子中，我们每次可以执行查询book_borrow表中id字段的所有值，然后再执行查询book_list表中bookid字段含有id字段的可能取值来查询被借阅的书的信息，但是book_borrow中字段的值随时会发生改变，因此我们可以一次使用子查询简化操作。

select * from book_list where bookid in (select id from book_borrow);

带比较运算符的子查询

如下方代码代表查询张三借阅的书的信息：

select * from book_list where book_list.bookid=(select id from book_borrow where borrower='张三');

带EXISTS关键字的子查询

此情况子查询不返回查询的记录而是返回一个真假值。当内层查询语句查询到满足条件的记录就返回真值（true），否则返回假值（false）；当值为真值时外层查询语句开始查询，所以查询的条件一般在内查询实现。

如下面的语句查询李四是否借阅了且没有归还的书，若有就输出：

select * from book_list where exists (select * from book_borrow where borrower='李四' and isback='0' and book_list.bookid=book_borrow.id);

NOT EXISTS关键字与EXISTS关键字相反，当返回值：为true时外查询不查询。

带ANY关键字的子查询

通常与比较运算符一起使用，它代表若内查询中的条件有一个满足就执行外查询，语法如下：

列名 比较运算符 ANY(内查询语句)

如果比较运算符是“<”，则表示小于子查询结果集中的某一个值，如果是“>”，则表示至少大于子查询结果集中的任何一个值。

如下面查找比一年三班最低分高的全部学生信息：

select * from tb_student where score>any(select score from tb_student where class='1.3');

带ALL关键字的子查询

表示满足所有条件。语法如下

列名 比较运算符 ALL(子查询)

如下面查找是否有比一年三班最高分高的全部学生信息：

select * from tb_student where score>all(select score from tb_student where class='1.3');

合并查询结果

使用UNION关键字

使用该关键字可以将多个结果合并在一起并且去除重复数据。

如下面实现同事查找两本书架上的书，先查询各自的书。

现在查询两本书架上所有的书，代码如下：

select bookname from book_list union select * from new_book_list;

结果就是【西游记、三国演义、红楼梦、水浒传、神雕侠侣、悟空传、蛮荒记】这几本书。

使用UNION ALL关键字

该关键字和UNION唯一的区别就是该关键字不会去除相同的记录。

select bookname from book_list union all select * from new_book_list;

这步结果就是西游记会显示两次，因为两个表都有

为表和字段取别名

若表或者字段的名称特别长，可以给表或者字段取别名，采用这种方式可以使代码更加简洁易读，能使查询更加方便。

为表取别名

下面的代码就将book_list表取别名为book，将book_borrow表取别名为borrow。

select * from book_list as book left join book_borrow as borrow on book.bookid=borrow.id;

为字段取别名

为字段取别名就是在输出时让该列的名称用别名替代。

select id,count(id) as borrow_number from book_borrow;

使用正则表达式查询

使用正则表达式比使用通配符更加强大，使用关键字REGEXP关键字匹配查询正则表达式，语法如下：

字段名 REGEXP '匹配方式'

正则表达式请在其他地方学习，这里不过多阐述。

如下面的正则表达式表达式表示匹配以三个数字加一个小写字母开头的字符串。

select d from demo where d regexp '^(\\d{3}[a-z])';

MySQL函数就是MySQL数据库提供的内置函数，可以帮助用户更好地的处理数据表中的数据。下表列举了MySQL内置函数的类别和这些函数的使用范围以及作用。

数学函数

数学函数是MySQL中常用的一类函数，主要用于处理数字，包括整型和浮点型数等。常用的数学函数如下表：

字符串函数

常用的字符串函数如下表：

日期时间函数

常用的日期时间函数如下：

条件判断函数

条件判断函数如下：

系统信息函数

常用的系统信息函数如下：

其他函数

MySQL中的其他函数如下：

在MySQL中，常见的过程式SQL语句可以用在一个存储过程体中，其中就包含IF语句、CASE语句、WHILE语句、LOOP语句、REPEAT语句、LEAVE语句和ITERATE语句，它们可以进行流程控制。

定义一个存储过程体的模板是：

delimiter 结束符号
create procedure 存储过程体名(in/out 变量名 变量类型)
begin
存储过程体
end
结束符号

其中，结束符号一般使用“//”，它作为调用此存储过程体的结束标志。

存储过程体名要是MySQL中有效的标识符，in/out表示调用此存储过程体时输入还是输出。

IF语句

IF语句可以用来进行条件判断，根据条件的真假执行不同的操作。

IF语句的表示形式如下：

IF condition THEN
...
[ELSEIF condition THEN]
...
[ELSE]
...
END IF

其中，condition表示判断条件。如：

delimiter //
create procedure example_if(in x int)
begin
if x=1 then select 1;
elseif x=2 then select 2;
else select 'other';
end if;
end
//

CASE 语句

CASE语句为多分支语句，该语句首先从WHEN后的VALUE中查找与CASE后的VALUE相等的值，如果查找到则执行该分支的语句，否则执行ELSE后面的内容。

CASE语句的语法如下：

CASE value
WHEN value THEN...
[WHEN value THEN ...]
[ELSE ...]
END CASE

其中，value表示条件判断的变量，WHEN后面的value表示变量的取值。如：

delimiter //
create procedure example_case(in x int)
begin
case x
when 1 then select 1;
when 2 then select 2;
else select 'other';
end case;
end
//

WHILE语句

WHILE语句执行时首先判断condition条件是否为真，如果为真则执行循环，否则退出循环。

WHILE语句的语法如下：

WHILE condition DO
...
END WHILE;

其中condition表示判断的条件。如

delimiter //
create procedure example_while(in x int)
begin
while x<=5 do
select x;
set x = x+1;
end while;
end
//

LOOP 语句

LOOP语句没有循环条件，但是可以通过LEAVE语句退出循环。

LOOP语句的语法如下：

label:loop
...
end loop;

LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造，中间省略的部分是要重复执行的语句，退出循环用LEAVE语句，语法为LEAVE label。

如：

delimiter //
create procedure example_loop(in x int)
begin
label:loop
if x>5 then leave label;
end if;
select x; 
set x = x+1;
end loop;
end
//

REPEAT语句

REPEAT语句是先执行一个循环体，之后判断condition条件是否为真，为真则退出循环，否则继续执行循环。

REPEAT语句的语法如下：

REPEAT
...
UNTIL condition
END REPEAT

如：

delimiter //
create procedure example_repeat(in x int)
begin
repeat
select x;
set x = x+1;
until x=5
end repeat;
end
//

在一个循环中继续循环

使用关键字ITERATE跳过本次循环，在LOOP，REPEAT和WHILE语句中很常用。使用ITERATE label跳过本次循环。

如：

delimiter //
create procedure example_iterate(in x int)
begin
label:while x<5 do
set x = x+1;
select x;
if x=3 then iterate label;
end if;
end while;
end //

终止循环：使用关键字LEAVE，语法是leave label，其中label表示为循环指定的标签。

索引是一种特殊的数据库结构，是提高数据库性能的重要方式，可以用来快速查询数据库表中的特定记录，MySQL中所有的数据类型都可以被索引。

我们来看一个数据表有索引和无索引时检索相同数据的时间。

首先看一下表的结构：

我们为num1添加了索引，num2没有。

然后我们查看里面的数据。

在此表中添加了两个字段在这两个字段中num1添加了索引，num2没有添加索引，还添加了2432358条数据行，0~2432357，我们分别使用where语句设置查询条件为字段1和字段2的某个值，分别看一下查询速度。

可以看到使用索引后查询几乎可以秒出，但是不使用索引就会遍历整个表，非常耗费时间。

索引概述

索引分类

• 普通索引

普通索引不应用任何限制条件的索引，该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或唯一。在某数据表的某一字段中创建该类型索引后，用户便可通过索引进行查询。

• 唯一索引

使用UNIQUE参数可以设置索引为唯一索引。创建该索引时，索引的值必须唯一，通过唯一索引，用户可以快速定位某条记录，主键是一种特殊的唯一索引。

• 全文索引

使用FULLTEXT参数可以设置索引为全文索引。全文索引只能在CHAR、VARCHAR、TEXT类型的字段上创建。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。如，查询带有回复内容的字段，可以应用全文索引方式。需要注意的是，在默认情况下，应用全文搜索大小写不敏感。如果索引的列使用二进制排序，可以使用对大小写敏感的全文索引。

• 单列索引

顾名思义，单列索引即只对一个字段生效的索引，其可以包含上述3种索引方式。应用该索引时必须保证该索引的值对应一个字段。在添加单列索引时，若被索引的列为varchar、text等类型，则还可以为索引添加长度。

• 多列索引

多列索引实在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询。要想应用该索引，用户必须使用这些字段的第一个字段。多列索引在使用联合查询时会起到优化作用，但是它不能优化单独使用被索引的列满足特定条件的查询。多列建索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。

• 空间索引

使用SPATIAL参数可以设置该索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间型数据的速度。MySQL中只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。

创建索引

在创建数据表时创建索引

语法如下：

create table table_name(
属性名 数据类型[约束条件],
...,
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
[别名] （属性1[(长度)][ASC|DESC]
);

INDEX和KEY参数用于指定字段索引，选择其中一个即可。

创建普通索引

create table score(
    id int(11) not null auto_increment primary key,
    name varchar(50) not null ,
    math int(5) not null ,
    chinese int(5) not null ,
    english int(5) not null ,
    index(id)
);

在最后一行可以看到，id已经被索引。

创建唯一索引

create table address(
id int(11) auto_increment primary key not null,
    name varchar(50),
    detail_address varchar(200),
    unique index address(id)
);

在数据完整性约束里，若为字段设置主键约束，则MySQL会自动为该字段添加主键索引；若为字段设置候选键约束，则MySQL会自动为该字段设置唯一索引。

设置主键约束的部分语法为constraint index_col_name primary key (col_name)，其中constaint关键字表示下面的语句代表添加完整性约束，index_col_name代表主键的名称，col_name代表字段，如下面为表a中的字段a添加主键约束，由于MySQL会自动为该字段添加主键索引，因而运行结果如下图。

下面是为字段b添加候选键约束的演示。

创建全文索引

create table cards(
id int(11) auto_increment primary key not null,
    name varchar(50),
    number bigint(11),
    info varchar(50),
    fulltext key card_info(info)
)

创建单列索引

create table telephone(
id int(11) primary key auto_increment not null,
    name varchar(50) not null,
    tel varchar(50) not null,
    index tel_num(tel(20))
);

创建多列索引

create table information(
id int(11) auto_increment primary key not null,
    name varchar(50) not null,
    sex varchar(5) not null,
    birthday varchar(50) not null,
    index info(name,sex)
);

创建空间索引

create table list(
id int(11) primary key auto_increment not null,
    goods geometry not null,
    spatial index listinfo(goods)
);

在已建立的数据表上创建索引

语法结构如下：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(属性 [(length)])[ASC|DESC]);

创建普通索引

在创建普通索引之前，先来看一下student的表的结构。

create index id_index on student(id);

创建唯一索引

语法：

CREATE UNIQUE INDEX 索引名 ON 数据表名称(字段名称);

我们在删除前面创建的索引之后为id添加一个唯一索引（删除索引的语句没有包含在下面的截图中）：

create unique index id_uindex on student(id);

创建全文索引

语法如下：

CREATE FULLTEXT INDEX 索引名 ON 数据表名称(字段名称);

我们在演示之前先创建一个如下的数据表：

create fulltext index info_index on information(info);

创建单列索引

语法如下：

CREATE INDEX 索引名 ON 数据表名称(字段名称(长度));

设置字段名称长度，可以优化查询，提高查询效率。

我们为info中的information添加单列索引（先删除）：

create index info_index on information(info(10));

创建多列索引

语法如下：

CREATE INDEX 索引名 ON 数据表名称(字段1,...);

如我们为数据表student添加一个多列索引：

create index multi_index on student(name,age);

创建空间索引

语法如下：

CREATE SPATIAL INDEX 索引名 ON 数据表名称(字段名称);

删除索引

若索引不使用也不删除，会既占用系统资源也降低表更新的速度。删除索引的语法如下：

DROP INDEX 索引名 ON 数据表名;

我们可以看到demo表中的字段num1存在我们之前创建过的索引，我们删除此索引。

drop index num on demo;

可以看到成功删除了索引。

视图是从一个表或多个表中导出的表，是一种虚拟的表。视图就像一个窗口，通过这个窗口就可以看到系统专门提供的数据。这样，用户就不需要查看整个数据表中的数据，而只关心对自己有用的数据。视图可以使用户的操作更加方便，而且可以保障数据库系统的安全性。

视图概述

视图是一个虚拟表，是从数据库中的一个或多个表中导出来的表，其内容由查询语句定义。同真实的表一样，视图包含一系列的行列数据。但是，数据库中只存放了视图的定义，而没有存放视图中的数据。这些数据还存在原来的表中。使用视图查询数据时，数据库系统会在原来的表中取出对应的数据。因此，视图的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

视图是保存在数据库中的查询SQL语句，它的存在主要出于两个原因：首先是安全原因，视图可以隐藏一些数据，例如，它可以用视图显示员工信息表中的姓名、工龄、地址而不显示社会保险号和工资等；另一个原因是它可使复杂的查询易于理解和使用。

对所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。通过视图进行查询没有任何限制，通过视图修改数据时的限制也很少，视图的作用可以总结为以下几点：

1. 简单性

看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。可以将经常使用的查询定义为视图，这样用户就不必为以后的操作每次都指定全部的条件。

2. 安全性

视图的安全性可以防止未授权用户查看特定的行或列，使有权限的用户只能看到表中特定行的方法如下：

(1) 在表中增加一个标志用户名的列。

(2) 建立视图，使用户只能看到标有自己用户名的行。

(3) 把视图授权给其他用户。

3. 逻辑数据安全性

视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，程序一定是建立在表上的。有了视图之后，程序可以建立在试图之上，从而使程序与数据库表被视图分割开来。视图可以在以下几个方面与数据独立。

(1) 如果应用建立在数据库表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表的变化，从而使应用程序不动。

(2) 如果应用建立在数据库表上，当应用发生变化时，可以在表上创建视图，通过视图屏蔽应用的变化，从而使数据库表不动。

(3) 如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变化，从而使应用程序不动。

(4) 如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变化，从而使应用程序不动。

创建视图

查看创建视图的权限

查询是否具有操作视图的权限由Create View语句和select语句实现，代码如下:

SELECT Selete_priv,Create_view_priv FROM mysql.user WHERE user = '用户名';

其中Selete_priv代表用户是否具有SELECT权限，Y表示具有SELECT权限，N表示没有。

Create_view_priv代表是否具有CREATE VIEW的权限，mysql.user表示查看mysql下的user表。

如我们查看管理员root是否具有这两个权限：

select select_priv,create_view_priv from mysql.user where user = 'root';

结果中select_priv和create_view_priv权限都为Y，表示root具有这两个权限。

创建视图

创建视图的语法如下：

CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(属性清单)] AS SELECT语句 [WITH[CASCADED|LOCAL] CHECK OPTION];

ALGORITHM：表示视图选择的算法。

(属性清单)：可选参数，指定视图中各个属性的名词，默认情况下与SELECT语句中查询的属性相同。

SELECT语句：一个完整的查询语句，表示从某个表中查出满足条件的记录，将这些记录导入视图中。

WITH...：可选参数，表示更新视图时要保证在该视图的权限范围之内。

创建视图的注意事项：

• 运行创建视图的语句需要用户具有CREATE_VIEW的权限
• SELECT语句不能包含FROM字句中的子查询
• 在定义中引用的表或者视图必须存在
• 在定义中不能使用多个temporary表，不能创建temporary视图
• 在视图中允许定义ORDER BY，但是若从特定视图中选择，而该视图中使用了具有自己定义的ORDER BY的语句，它将被忽略

如我们为一个员工信息表创建视图。

在创建视图之前先创建数据表。

第一个数据表为员工信息表，此表中的id代表编号，name代表员工姓名，sex代表员工性别，salary代表员工工资，section代表员工部门，telephone代表员工电话号码，number代表员工编号。

在此数据表中，我们设定工资、部门、员工编号为隐私信息，那么我们就可以创建一个只显示字段id、name、sex、telephone的视图。

create view v_employees (id,name,sex,tele) as select id,name,sex,telephone from employees;

到这里视图创建成功。

视图操作

查看视图

查看视图指查看数据库中存在的视图，需要具有SHOW VIEW权限。查看视图的方法如下。

DESCRIBE 视图名;
DESC 视图名;
SHOW TABLE STATUS LIKE '视图名';
SHOW CREATE VIEW 视图名;

以上四种方法都可以查看已创建的视图，下面都进行演示。

desc v_employees;

show table status like 'v_employees';

show create view v_employees;

修改视图

修改视图指的是修改数据库中已存在的表的定义。当基本的表字段发生改变时，可以通过修改视图来保持视图和表保持一致。修改视图的语法如下：

CREATE OR REPLACE VIEW [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图[(属性清单)] AS SELECT语句 [WITH [CASCADE|LOCAL] CHECK OPTION];

ALTER [algorithm={undefined|merge|template}] VIEW view_name [(column_list)] AS select_statement [with [cascaded|local] CHECK OPTION];

若在创建视图时使用了WITH CHECK OPTION、WITH ENCRYPTION、WITH SCHEMABING或VIEW_METADATA选项，修改视图时项保存这些选项提供的功能，必须在ALTER VIEW语句中将它们包括进去。

下面分别使用这两个语句修改上文创建的视图，这里我们先使用CREATE OR REPLACE VIEW语句删除字段tele。

create or replace view v_employees (id,name,sex) as select id,name,sex from employees;

再使用ALTER VIEW重新添加字段tele。

alter view v_employees (id,name,sex,tele) as select id,name,sex,telephone from employees;

更新视图

更新视图指对表的更新。但是视图有很多限制条件所以我们一般不更新视图。更新视图的语法如下：

UPDATE view_name SET column_name=value ... [WHERE子句];

下面演示如何通过视图更改表的数据，首先看一下视图和表的数据。

然后我们输入下面的语句，然后再查看视图和表的数据。

update v_employees set tele='30003' where id='3';

可见数据被成功修改了。

但是若有以下几种情况是不能更新视图的：

1. 视图中包含COUNT()、SUM()、MAX()和MIN()等函数；
2. 视图中包含UNION、UNION ALL、DINTINCT、GROUP BY、HAVIG等关键字；
3. 常量视图，如create view view_book as select 'ansheng' as ansheng;；
4. 视图中SELECT包含子查询；
5. 由不可更新的视图导出的视图，如create view view_book as select * from book_view1;；
6. 创建视图时ALGORITHM为TMPLTABLE类型；
7. 视图对应的表存在没有默认值的列，而且该列没有包含在视图里。

删除视图

语法如下：

DROP VIEW IF EXISTS <视图名> [RESTRICT|CASCADE];

该语句从数据字典中删除指定的视图定义；若该视图导出了其他视图，则使用CASCADE级联删除，或者先显式删除导出的视图，再删除该视图；删除基表时，由该基表导出的所有视图必须显式删除。

如我们删除上面创建的v_employees视图。

drop view id exists v_employees;

由于视图是虚拟表，因此查询视图的语法和查询普通表相同，如下图。

数据完整性是指数据的统一性和相容性，是为了防止数据库中存在不符合语义的数据，即防止数据库中存在不正确的数据。一旦定义了完整性约束，MySQL服务器会实时检测处于更新状态的数据库内容是否符合相关的完整性约束，从而保证数据的一致性和正确性。这样既能防止对数据库的意外破坏又能减轻数据库管理人员的维护负担。

定义完整性约束

实体完整性

实体是一个数据对象，是指客观存在并且可以相互区分的事物。如一个教师，一个学生，一个桌子，一个椅子。一个实体在数据中表示为一条记录，通常它必须遵循实体完整性约束。

在MySQL中实体完整性是由主键约束和候选键约束实现的。

主键约束

主键可以是一个字段，也可以是多个字段的组合，叫做复合主键。主键约束必须满足以下规则：

• 每个表只能定义一个主键。
• 唯一性原则：主键的值，也称键值，必须能够唯一标识表中的某一条数据，且不能为NULL。
• 最小化原则：复合主键不能包含不必要的多余字段。
• 一个字段名在复合主键中只能出现一次。

在MySQL中，可以在CREATE TABLE和ALTER TABLE语句中使用PRIMARY KEY关键字创建主键约束，实现方式有以下两种：

第一种，在字段的定义后面声明该字段为主键。如下面将id字段设为主键。

create table tb_user(
id int primary key auto_increment,
user varchar(50) not null,
password varchar(50) not null,
createtime date not null
);

可以看到，此数据表中字段id为主键。

第二种，在最后声明，如将主键设为id和class字段。

create table tb_stu(
    id int not null auto_increment,
    name varchar(50) not null,
    sex enum('男','女') not null,
    class int not null,
    primary key (id,class)
);

可以看到此数据表中字段id和class都为主键，即为复合主键。

候选键约束

若一个属性能完整标识一个元组，那么这个属性就可以设置成候选键，如学号可以唯一地区分不同的学生，那么学号就可以设为候选键。候选键可以是某一列，也可以是多个列组成的一个组合。候选键必须唯一且不能为NULL。候选键使用CREATE TABLE或ALTER TABLE语句创建，其实现方法与主键约束类似，也是作为列的完整性约束和表的完整性约束两种格式。

在MySQL中，候选键与主键有以下两种区别：

• 一个表只能创建一个主键，但是可以创建多个候选键；
• 定义主键约束时，系统会自动创建PRIMARY KEY索引，而定义候选键约束时，系统会自动创建UNIQUE索引。

如我们创建一个员工表，将id和姓名作为候选键。

create table tb_employees (
id int auto_increment unique,
    name varchar(50) not null unique,
    sex enum('男','女'),
    tele varchar(50)
);

参照完整性

参照完整性规则就是定义外码（外键）和主码（主键）之间的引用规则，它是对关系间引用数据的一种限制。

InnoDB引擎定义外键约束的语法如下：

[CONSTRAINT[SYMBOL]] FOREIGN KEY (index_col_name) reference_definition;

reference_definition主要用于定义外键所参照的表、列、参照动作的声明和实施策略四部分内容。它的基本语法如下：

REFERENCES tbl_name [(index_col_name,...)] [MATCH FULL|MATCH PARTIAL| MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option]

index_col_name的语法格式如下：

col_name [(length)] [ASC|DESC]

reference_option的语法格式如下：

RESTRICT|CASCADE|SET FULL|NO ACTION

参数说明如下：

• index_col_name：用于指定被设置为外键的字段。
• tbl_name：用于指定外键所参照的表名。这个表称为被参照表（或父表），而外键所在的表为参照表（或子表）。
• col_name：用于指定被参照的列名，外键可以引用被参照表中的主键或候选键，也可以引用被参照表中某一些字段的组合，但这个组合不能是被参照表中随机的一组列，必须保证该组合的取值在被参照表中是唯一的。外键中所有列值在被参照表中必须全部存在，也就是通过外键来对参照表中某些列的取值进行限定和约束。
• ON DELETE|ON UPDATE指定参照动作相关的SQL语句。可为每个外键指定对应于DELETE语句和UPDATE语句的参照动作.
• reference_option：指定参照完整性约束的实现策略。其中当没有明确指定参照完整性约束的实现策略时，两个参照动作都会使用RESTRICT，具体策略见下表：

如我们定义一个存储学生信息的数据库，在这里面存在两个数据表：tb_stu和tb_class。tb_stu中我们定义五个字段：id、name、sex、classid（班级id）、remark（备注），将id设为主键；tb_class中定义两个字段：id（班级id）、classname，在此数据表中，我们约定班级id一一对应班级名称，那么我们就可以通过将tb_stu中的classid定义为外键，与tb_class中的主键id相连接，这就是参照完整性。在向学生数据表中添加信息时，若我们添加的班级id在班级数据表中不存在则添加失败。

实现上述功能的步骤如下：

创建学生信息表和班级id转换表。

• 班级id转换表

  

• 学生信息表（创建的同时将classid的外键连接到tb_class的主键id上）

create table tb_stu(
id int auto_increment primary key,
name varchar(50) not null,
classid int not null,
remark varchar(50),
foreign key (classid)
references tb_class (id)
on delete restrict
on update restrict
);

注意在创建学生信息表的时候我们指定了**on delete restrict on update restrict**，这个代表在尝试删除参照表也就是学生信息表时若该数据行的classid字段的值等于被参照表班级id参照表中id的某一个值就会弹出删除失败的信息，若尝试添加一个不存在的班级id的学生时也会报出添加失败的信息。如我们先录入几个班级id和班级名称对应的关系：

insert into tb_class (classname) values ('一年一班'),('一年二班'),('一年三班'),('一年四班');

我们尝试添加一个不存在的班级id的学生：

insert into tb_stu (name,classid) values ('张三',0);

可见0不存在于班级id中，下面我们添加一个班级id为1的学生：

insert into tb_stu (name,classid) values ('张三',1);

可见添加成功。

我们再尝试班级id参照表中classid为1的数据行。

delete from tb_class where id=1;

可见由于外键约束，学生信息表中还存在着classid为1的数据行，因此删除失败，解决方式就是先删除学生信息表中classod为1的数据行再删除此数据。

设置外键时，必须遵守如下规则：

1. 被参照表必须是存在的，或者是当前正在创建的表。若为当前正在创建的表，也就是说，被参照表与参照表是一个表，这样的表称为自参照表，这种结构称为自参照完整性。
2. 必须为被参照表定义主键。
3. 必须在被参照表名后指定列名或列名的组合。这个列或者列组合必须是这个被参照表的主键或候选键。
4. 外键中列的数据类型必须与被参照表中主键（或候选键）的数据类型相同。
5. 外键中列的数目必须与被参照表中列的数据相同。
6. 尽管主键不能包含空值，但允许在外键中出现一个空值。这意味着，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

用户定义完整性

用户定义完整性规则是针对某一应用环境的完整性约束条件，它反映了某一具体应用所涉及的数据应满足的要求。

非空约束

若要设置某一字段不为空，可以再CREATE TABLE或者ALTER TABLE语句中添加关键字NOT NULL修饰。如：

可以看到我们将字段c1设为非空，在插入数据行时若c1为空则会报错，而无论c2是否为空都不会报错。

CHECK约束

对列实施CHECK约束

若要对列实施CHECK约束，只需要在该列定义后加一个CHECK(para)即可，其中para表示判断的表达式，如我们设置c列必须大于10且小于20。

如下面我们定义一个字段，在该字段后面加上CHECK约束，使该字段的值大于10且小于20。

create table demo (c int check(c>10 and c<20));

可以看到我们设置CHECK约束之后当c等于1时插入失败，而当c等于15时可以成功插入。

对表实施CHECK约束

若要对表实施完整性约束，可以在CREATE TABLE语句或ALTER TABLE语句中定义一个CHECK语句即可，语法如下：

CREATE TABLE table_name(
col_definitions ...
    CHECK(para)
)

其中para代表检查条件，如CHECK(classid in(2,3))代表classid必须在2和3里面，CHECK(classid in(select id from tb_class))代表classid必须为tb_class表中的某一个值。如：

create table demo(
c int not null,
    check(c>0 and c<100)
)

可以看到我们定义c必须大于0且小于10，在插入的值为-1时插入失败，在插入的值为1时插入成功。

命名完整性约束

在MySQL中也可以对完整性约束进行添加、修改和删除等操作，在进行这些操作之前就需要先将完整性约束命名。命名完整性约束的方式是在定义完整性约束的语句前加CONSTRAINT字句，具体语法如下：

CONSTRAINT <symbol>
[PRIMARY KEY 短语|UNIQUE 短语|FOREIGN KEY 短语|CHECK 短语]

其中symbol就为完整性约束的名字。

如我们在创建员工表的同时为id添加一个主键约束，并且将其命名为pri_key。

create table employees (
id int auto_increment unique,
name varchar(50) not null,
sex enum('男','女') not null ,
salary int not null,
constraint pri_key primary key (id)
);

在定义完整性约束时，最好在定义的同时就为其添加名字，这样方便后期对其进行修改、删除等操作。

更新完整性约束

删除完整性约束

在ALTER TABLE语句中使用DROP字句可以单独地删除一个完整性约束，语句如下：

DROP [FOREIGN KEY|INDEX|<SYMBOL>]|[PRIMARY KEY]

其中，FOREIGN KEY表示删除外键约束。PRIMARY KEY表示删除主键约束，但是需要注意在删除主键约束时必须再次创建一个主键，否则会删除失败。INDEX表示删除候选键约束，symbol表示要删除的约束名称。

如我们删除employees表中字段id的唯一索引，就可以使用下面的语法：

alter table employees drop index id;

在删除这个索引后我们可以再次查看此表的索引，可以看到唯一索引已被删除。

修改完整性约束

MySQL中不存在直接修改完整性约束的方法，需要先删除对应的完整性约束然后再创建一个同名的完整性约束完成修改。

在下面的实例中我们将候选键约束name改为id，具体是先删除候选键约束name，然后在添加值为id的候选键约束。

其中删除候选键约束和重新添加候选键约束的语句如下：

alter table employees drop index name;
alter table employees add unique index id(id);

运行结果如下图，可见执行这两条语句之后候选键约束从name改为了id。

再若我们要给表tb1添加一个如下的外键约束（无运行结果）。

alter table tb1 add (constraint 'fri_key'  -- 该外键名为fri_key
foreign key (col1,col2)  -- 参照表为tb1 添加外键约束的字段为col1和col2
    references tb2 (col3,col4) -- 被参照表为tb2 被参照字段为col3和col4
   on delete set null  -- 若删除被参照表中的字段值，则在参照表中对应的位置设成NULL
    on update restrict  -- 若更新被参照表中的字段值，若参照表有对应的值则不允许此操作
);

• 在设置此外键约束前，被参照表tb2必须具有对应字段为col3和col4的多列索引，创建该索引的部分语法如下：add index (col3,col4)。
• 设置此外键约束后，参照表中若要添加数据，则参照表中同一数据行的字段col1和字段col2必须同时对应被参照表中某一同一数据行的字段col3和col4，如被参照表中有一数据行，该数据行中col3的值为1，col4的值为2，则向参照表中添加数据时，该行数据的字段col1必须为1，col4必须为2，若col1为1col2为3那么就是不同时对应该数据行的每个字段了，若被参照表中其他字段也不满足此规则，则该数据行不能添加。

存储过程和存储函数是指在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和存储函数来执行已经定义好的SQL语句，以避免开发人员重复编写相同的SQL语句，增加开发的效率，减少客户端和服务端的数据传输。

创建存储过程和存储函数

创建存储过程

创建存储过程的语法如下：

CREATE PROCEDURE proc_name([proc_parameter[...]])
[characteristic] routine_body

其中，proc_name代表存储过程的名称；proc_charameter代表存储过程的参数列表；characteristic代表存储过程的特性；routine_body代表SQL代码的内容。proc_parameter参数由三部分组成，分别为输入或输出类型、参数名称和参数类型。其形式为[IN|OUT|INOUT]param_name type。其中IN表示输入参数；OUT表示输出参数；INOUT表示既可作为输入函数又可作为输出函数；param_type表示存储过程参数名称；type参数指存储过程的参数类型，该类型可以为MySQL中的任意数据类型。一个存储过程包含名称、参数列表、还可以包含很多SQL语句集。

注：characteristic部分的语法如下。

{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'

输入参数、输出参数、输入输出参数：

• in 输入参数，意思说你的参数要传到存过过程的过程里面去，在存储过程中修改该参数的值不能被返回

• out 输出参数:该值可在存储过程内部被改变，并向外输出，一般在调用时使用全局变量，即在变量名前加一个“@”符号，此时这个变量就存储起来了

• inout 输入输出参数，既能输入一个值又能传出来一个值

在MySQL中存储过程体以CREATE PROCECURE开始，后面紧跟存储过程的名称和参数。MySQL中的存储过程名称不区分大小写，且存储过程不能与MySQL中的内置函数同名。存储过程体的语句块以begin开始，以end结束，语句体中可以包含变量的声明、控制语句、SQL查询语句等。由于存储过程体内部要以分号结束，所以在定义存储过程体之前都要新定义一个结束符号，并且应降低该字符在语句块中的出现概率。

下面我们先创建一个数据表，该数据表中只包含一个字段用于存储编程语言名称的字段，创建存储过程的语句如下。

delimiter //
create procedure proc_name(in parameter int)
begin
declare variable varchar(50);
if parameter = 1 then
set variable = 'MySQL';
elseif parameter = 2 then
set variable = 'Java';
else set variable = 'Python';
end if;
insert into tb values (variable);
end
//

在此例中，我们将parameter作为输入量，然后通过判断parameter的值来改变变量的值，最后使用INSERT INTO语句实现快速插入数据的操作。

MySQL中默认的语句结束符为分号，存储过程中的SQL语句需要使用分号结束.为了避免冲突，首先用“delimiter //”将MySQL的结束符设置为”//“，再用“delimiter ;”来将结束符设置成分号。

创建存储函数

创建存储函数的语法和创建存储过程的语法类似，语法如下：

CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...]  routine_body

其中sp_name为存储函数的名称，func_parameter为存储函数的参数列表，RETURNS type指定返回值的类型，characteristic指定存储函数的特性，routine_body代表SQL代码内容。

下面仅演示如何创建存储函数。

delimiter //
create function func(i int)
returns varchar(50)
begin
if i=1 then return 'Java';
elseif i=2 then return 'Python';
else return 'MySQL';
end if;
end;
//

若MySQL提示：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) 需要在MySQL的配置文件中找到[mysqld]位置，在该行下面设置log-bin-trust-function-creators=1。

变量的应用

局部变量

局部变量以关键字DECLARE声明，语法如下：

DECLARE variable_name type [DEFAULT value]

其中variable_name表示局部变量名，type表示类型，DEFAULT value可选，表示为局部变量设置默认值。

如：

delimiter //
create procedure p1()
begin
declare i varchar(10) default 'outer';
begin
declare i varchar(10) default 'inner';
select i;
end;
select i;
end;
//

全局变量

全局变量比较于局部变量，只需要在前面加上一个“@”符号。

delimiter //
create procedure p2()
begin
set @a=1;
begin
set @a=2;
select @a;
end;
select @a;
end;
//

为变量赋值

MySQL中可以使用关键字DECLARE来声明变量，可以使用关键字SET为变量赋值，可以同时为多个变量赋值，各个变量的赋值语句用“,”隔开。

另外，MySQL还支持使用下面的语句结构为变量赋值：

SELECT col_name[,...] INTO var_name[,...] FROM table_name where condition;

其中col_name代表查询的字段名称；var_name代表变量的名称；table_name代表表的名称，如从stu表中查询name为‘张三’的记录，并将记录中的tel赋值为局部变量i，那么代码如下：

select tel into i from stu where name = '张三';

光标的应用

在MySQL中查询记录，其结果可能位多条记录。在存储过程和函数中使用光标可以实现逐条读取结果集中的记录。

光标的使用包括以下步骤：

声明光标

光标只能在存储过程中定义。声明光标使用关键字DECLARE，语法如下。

DECLARE cursor_name CURSOR FOR select_statement;

其中，cursor_name为光标的名称，光标使用和表是同样的规则；select_statement是一个SELECT语句，返回一行或多行数据，该语句也可以在存储过程中定义多个光标，但是必须保证光标的名称不能相同。

打开光标

在声明光标之后，若要使用光标需要先打开光标。打开光标的语法如下。

OPEN cursor_name;

使用光标

顺利打开光标后，可以使用FETCH...INTO语句读取数据，语法如下。

FETCH cursor_name INTO var_name[,var_name]...

关闭光标

使用CLOSE关键字关闭光标，语法如下。

CLOSE cursor_name;

现在举一个实例——更新表中信息：在员工表中将name字段由‘姓名’改成‘id-姓名’：

首先查看表中数据。

然后我们创建如下的存储过程。

delimiter //
create procedure proc()
begin
    declare e_id int;  # 存储字段id
    declare e_name varchar(50);  # 存储字段name
    declare isDone int default 0;  # 存储是否结束的标志
    declare cur_proc cursor for select id as e_id,name as e_id from employees;  # 声明光标
    declare continue handler for not found set isDone = 1;
    # 若光标没有成功查找到数据就将结束表示设为1
    open cur_proc;  # 开启光标
    while not isDone do  # 循环，若结束标志为0则继续循环
        fetch cur_proc into e_id,e_name;  # 使用光标，赋值
        update employees set name = concat(e_id,'-',e_name) where id = e_id; # 连接
        end while;
    close cur_proc; # 关闭光标
end //

然后调用存储过程，最后再查看表的数据。

可以看到表的数据被改动了，说明达到目的了。

调用存储过程和存储函数

调用存储过程

存储过程的调用在前面的示例中已多次使用。使用CALL关键字调用存储过程，然后存储过程会被执行，然后将结果输出给返回值。语法如下。

CALL sp_name([parameter[,...]]);

其中sp_name代表存储过程的名称，parameter是参数，且参数不能省略。

MySQL中，若存储过程的参数是OUT或者INOUT作为输出参数使用时，我们一般在传递参数的时候将全局变量作为参数，如我们定义一个如下的存储过程： “create procedure proc(out i int) begin ... end //” 那么我们在调用的时候这样调用：“call proc(@a) //” 这样调用后就会在MySQL中生成这个全局变量@a，之后我们使用“select @a;” 或者其他的语句就可以使用这个值了。

如我们先定义一个如下的存储过程，然后调用此存储过程。调用存储过程的语句如下：

call count_of_student(@total) //
select @total //

可以看到@total的值为4，说明employees表中存在4条数据行。

调用存储函数

存储函数的调用和MySQL的内置函数基本相同。语法如下：

SELECT function_name([parameter[,...]]);

如我们定义如下的存储函数，然后调用此存储函数。调用存储函数的语法如下：

select search_name(1) //

查看存储过程和存储函数

SHOW STATUS语句

使用SHOW STATUS语句查看存储过程和存储函数的语法如下：

SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern']

如查看db数据库中所有的存储过程：

show procedure status;

查看所有以字母p开头的存储过程名：

show procedure status like 'p%';

SHOW CREATE语句

通过SHOW CREATE语句查看存储过程和存储状态的语法如下：

SHOW CREATE {PROCEDURE|FUNCTION} sp_name;

sp_name代表存储过程或者存储函数的名称。

如查询count_of_student的定义：

show create procedure count_of_student //

查询结果显示存储过程的定义、字符集等信息。

SHOW STATUS语句只能查看存储过程或存储函数所操作的数据库对象，如存储过程或存储函数的名称、类型、定义者、修改时间等信息，并不能查询存储过程或存储函数的具体定义。若要查看详细定义，可以使用SHOW CREATE语句。

修改存储过程和存储函数

使用ALTER关键字修改存储过程或存储函数的语法如下：

ALTER {PROCEDURE|FUNCTION} sp_name [characteristic ...]
characteristic:
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'

删除存储过程和存储函数

使用DROP PROCEDURE来删除存储过程，使用DROP FUNCTION删除存储函数，语法如下：

DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name;

事务机制

事务修改的概念

事务是一组相互依赖的操作单元的集合，用来保存对数据库的正确修改，保证数据的完整性，如果事务的某个单元操作失败，将取消本次事务的全部操作。如银行交易，A给B转账，需要执行两次操作：A的银行资金减少，B的银行资金增加，若B没有转账成功，则取消A的本次扣除，即事务回滚到扣除之前，即A中没少，B中没多，事务的特征如下：

• 原子性：每个事务是一个不可分割的整体，只有所有的操作都执行成功，事务才能够提交，否则此次事务失败，所有成功执行的操作单元都需撤销，数据库回到此次事务之前的状态。

• 一致性：在执行一次事务后，关系数据的完整性和业务逻辑的一致性不能被破坏。假如A和B转账结束后，资金总额是不能改变的。

• 隔离性：在并发环境中，一个事务所做的修改必须与其他事务所做的修改相隔离。例如一个事务查看的数据必须是其他并发事务不能查修改之前或修改完毕的数据，不能是修改中的数据。

• 持久性：事务结束后，对数据的修改是永久保存的，即使数据故障导致数据库系统重启，数据依然是修改后的状态。

事务机制的重要性

银行应用是解释事务必要性的一个经典例子。假设一个银行的数据库中只有一个账户表tb_account，保存着两张借记卡账户A和B，并且要求这两张借记卡都不能透支。下面通过具体的代码演示一下没有事务机制的话会有什么后果。

1. 创建银行数据库bank，在下面创建数据表tb_account，代码如下。

   create database bank;
   use bank;
   create table tb_account (
   id int unsigned not null auto_increment primary key,
       name varchar(50) not null,
       balance float(8,2) unsigned default 0
   );
   

   

2. 向银行数据表中插入两条记录，具体代码如下。

   insert into tb_account (name,balance) values
   ('A',1000),('B',0);
   select * from tb_account;
   

   

3. 创建模拟转账的存储过程，代码如下。

   delimiter //
   create procedure proc_transfer(in name_from varchar(50),in name_to varchar(50),in money int) reads sql data
   begin
   update tb_account set balance = balance + money where name = name_to;
   update tb_account set balance = balance - money where name = name_from;
   end
   //
   

   

4. 调用刚刚创建的存储过程，实现A向B转账七百元，代码如下。

   call proc_transfer('A','B',700);
   select * from tb_account;
   

   

5. 再次尝试转账700元，代码如下。

   call proc_transfer('A','B',700);
   select * from tb_account;
   

   

   可以看到，由于A的账户不足700元，所以扣款失败，但是B账户还是收到了700元，这是因为在扣除A账户的700元时发生了异常，我们应该撤销B账户得到的700元，并没有，这并不符合我们的预期，所以事务就可以解决这一问题。在有了事务之后，我们就可以将扣款和转账的语句看成一个整体，若扣款失败，则目标账户的资金不能增加，或者扣除已经增加不该增加的金额。

关闭MySQL自动提交

MySQL默认开启自动提交，也就是说，MySQL会自动地把每一个语句都看成一个事务，在执行完该语句后执行提交操作。

查看MySQL的自动提交是否开启

查看MySQL的自动提交是否关闭有以下几种方法。

使用SHOW VARIABLES语句，语法如下。

SHOW VARIABLES LIKE 'autocommit';

在Value字段值为ON或1时表示自动提交开启，值为OFF或0时表示自动提交结束。

关闭自动提交有以下两种情况：

1. 在当前连接中，可以通过将AUTOCOMMIT变量设置为0，来禁用自动提交功能，代码如下。

   set autocommit = 0;
   show variables like 'autocommit';
   

   

   系统变量autocommit是会话变量，即在一个命令行窗口将autocommit设置为0，另一个命令行窗口的autocommit的值还是1。

   当autocommit设置为0时，所有的SQL语句都是在一个事务中，直到显式地执行提交或者回滚时该事务才结束，并且同时开启另一个新事务。

2. 当使用START TRANSACTION命令时可以隐式地关闭自动提交功能，该方法不会改变autocommit变量的值。

事务回滚

在上面的转账过程中，我们添加当转账用户的余额不足时撤销目标用户增加金额的操作，即进行事务回滚，步骤如下。

1. 关闭MySQL的自动提交功能。

set autocommit = 0;

2. 接着《事务机制的重要性》的例子，之前的。

   select * from tb_account;
   

   

3. 接着进行一次转账并查看存款。

   call proc_transfer('A','B',700);
   select * from tb_account;
   

   

可以看到在转账之后，A的账户扣款失败，但是B账户仍然多了700元，现在我们重新打开一个命令行窗口，并查看这两个账户的存款。

可见在新命令行窗口中并没有实际改变这两个变量的值，这是因为关闭了MySQL的自动提交后，若不手动提交，那么UPDATE操作将只会影响内存中的临时记录，并没有真正写入数据库文件。所以在当前命令行窗口执行SELECT语句时，获得的是临时记录，并不是实际数据表中的记录。此时的结果取决于接下来执行的操作，若执行ROLLBACK操作，那么就放弃当前的修改，若执行COMMIT，那么做出的修改会永久存到数据表文件中。

之后我们执行事务回滚操作，事务回滚操作的代码如下。

rollback;
select * from tb_account; 

事务提交

当关闭自动提交之后，数据库开发人员可以根据需要提交更新操作，否则更新的结果不会提交到数据库文件中，成为数据库永久的组成部分。

显式提交

关闭自动提交功能之后，可以使用COMMIT命令显式提交更新语句，如上一节最后若把回滚语句换成提交语句COMMIT，就会变成以下结果。

此时该条记录就会永久地保存到数据库文件中，此时若再次打开一个新的命令行窗口并执行查看语句，会得到和上图相同的结果。

隐式提交

关闭自动提交后，若没有手动提交更新操作或者执行回滚操作，则当执行下表的命令时也将执行提交操作。

MySQL中的事务

在MySQL中，应用START TRANSACTION命令来标记一个事务的开始，语法如下。

start transaction;

通常START TRANSACTION后面跟着的是组成事务的SQL语句，并且要在所有语句都成功执行后执行提交事务COMMIT语句，下面通过一个具体实例演示事务的使用。

首先看一下A账户和B账户的账户余额，如下图，实现转账过程并调用的代码步骤如下。

1. 创建存储过程，在存储过程中添加一个事务，实现从一个账户向另一个账户转账的过程，代码如下。

   delimiter //
   create procedure prog_tran_account(in name_from varchar(50),in name_to varchar(50),in money int)
   modifies sql data -- 修改数据库内数据
   begin
       declare exit handler for sqlexception rollback;  -- 若发生数据库异常则回滚
       start transaction;
       update tb_account set balance = balance + money where name = name_to;
       update tb_account set balance = balance - money where name = name_from;
       commit;
   end
   //
   

   

2. 尝试从A账户向B账户转账1200元，代码如下。可以看到转账失败了，但是A和B账户的资金总额没变，因为进行了事务回滚。

   call prog_tran_account('A','B',1200);
   select * from tb_account;
   

   

3. 这次我们尝试从A账户向B账户转账800元，代码如下。可以看到这次转账成功了。

   call prog_tran_account('A','B',800);
   select * from tb_account;
   

   

回退点

默认情况下若回滚事务，所有执行成功的SQL操作都会撤销.若不想全部撤销，而只需要撤回一部分，就可以通过设置回退点，然后退回到回退点实现，创建回退点、退回该点的语法如下。

SAVEPOINT 回退点名;
ROLLBACK TO SAVEPOINT 回退点名;

如我们在转账之前先创建一个C账户，然后从A向B转账1200元（假设A的余额有1000元），代码如下。从结果可以看到，虽然转账失败了，但是C账户成功创建了。

delimiter //
create procedure prog_savepoint_account(in name_from varchar(50),in name_to varchar(50),in money int) modifies sql data
begin
    declare continue handler for sqlexception
        begin
            rollback to savepoint A;
            commit;
        end;
    start transaction;
    start transaction;
    insert into tb_account (name,balance) values ('C',0);
    savepoint A;
    update tb_account set balance = balance + money where name = name_to;
update tb_account set balance = balance - money where name = name_from;
    commit;
end //
delimiter ;
call prog_savepoint_account('A','B',1200);
select * from tb_account;

数据库管理系统采用锁的机制来管理事务。当多个事务同时修改同一数据时，只允许持有锁的事务修改该数据，其他事务只能“排队等待”，直到前一个事务释放其拥有的锁。下面对MySQL中提供的锁机制进行详细介绍。

锁机制

MySQL锁机制的基本知识

在同一时刻，可能会有多个客户端对中同一行操作，例如，有的客户端在读取该行数据，有的则尝试删除它。为了保证数据的一致性，数据库就要对这种并发操作进行控制，因此产生了锁的概念。

锁的类型

1. 读锁

读锁也称为共享锁。它是共享的，或者说相互不阻塞的。多个客户端在同一时间可以同时读取同一资源，互不干扰。

2. 写锁

写锁也成为排他锁。它是排他的，也就是说一个写锁会阻塞其他的写锁和读锁。这是为了确保在给定的时间内，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源，保证安全。

在实际数据库中，随时都在发生锁定。例如，当某个用户修改某一部分数据时，MySQL就会通过锁定防止其他用户读取同一数据。在大多数时候，MySQL锁的内部管理是透明的。

读锁和写锁的区别如下表。