MySQL管理和语法

3.1 SQL 语言概述

一、SQL 具有四个主要的功能

SQL 有两种使用方法 :

• 交互式 SQL: 一种与用户交互的方式，适合非计算机专业人员。
• 宿主型 SQL: 一种作为子语言嵌入到其它程序设计语言中，适合于程序设计人员
  用高级语言编写应用程序并与数据库打交道 , 嵌入到主语言中使用。

主要功能:

1. 数据定义（ DDL）:主要用于创建、修改、删除数据库及其中的对象。如：表、视图、索引等；
2. 数据操纵（ DML）:主要用于对关系中的数据进行增、删、改和更新等操作；
3. 数据查询：主要用于对数据进行检索查询等；
4. 数据控制（ DCL）:主要用于对数据库的访问权限的授予或撤消、完整性规则描述、事务控制等操作

二、语言的特点

1. 非过程化语言:一次处理一条记录 , 对数据提供自动导航。
2. 统一的语言:对所有用户都适用。提供的命令有：查询数据；在表中插入、修改和删除记录；建立、 修改和删除数据对象；控制对数据和数据对象的存取；保证数据库一致性和完整性等。
3. 是所有关系数据库的公共语言

3.2 基本数据类型

主要数据类型:数值型、字符型、日期型、二进制型、位型和双字节型

1.数值数据类型:用于存储数值型数据

• 整数型：包括 Bitint 、Int 、Smallint 、Tinyint, 一般使用 Int 型。

对象	年龄区间	类型	字节	表示范围
人	150 岁之内	tinyint unsigned	1	无符号值：0 到 255
龟	数百岁	smallint unsigned	2	无符号值：0 到 65535
恐龙化石	数千万年	int unsigned	4	无符号值：0 到约 42.9 亿
太阳	约 50 亿年	bigint unsigned	8	无符号值：0 到约 10 的 19 次方

• 精确数值：用于存储带小数位的数据，包括 Decimal 、Numeric ，一般使用后者。
• 浮点数值：（ Float,Real) 存储非常大带小数的数据。
• 货币型：带四位小数的浮点数。

2.字符型：用于存储字符型数据

• Char 型：长度固定；
• Varchar 型：长度不固定；
• Text 型：存储字符型数据非常大， 长度超过 8000 个字符时使用， 最大可存储 2G 的数据。

3.日期时间型：存储日期和时间的组合数据。

• Datetime: 1753 年 1 月 1 日 至 9999 年 12 月 31 日，占 8 个字符的存储空间。
• Small Datetime: 1900 年 1 月 1 日 至 2079 年 6 月 6 日，占 4 个字符的存储空间。

4.二进制:存储图形图像、 WORD文档等。

• 包括:Image 、Binary 、var binary 。其中，Image 用于存储照片、音频和视频等文件。

5.位型：Bit 逻辑数据，用于存储 1或 0，只有一位字节

6.特殊数据类型： 用于存储特殊用途的数据

3.3 数据定义语言(DDL)

一、数据库

1.数据库的概念:对象的集合，主要包括表、索引、视图、存储过程等。数据库在磁盘上以文件形式存储，由数据文件和日志文件组成。先建立数据库，然后才能建立对象 ,如: 表、索引等。

2.数据库的定义：

CREATE DATABASE <数据库名 >

功能: 建立一个空数据库。该语句执行后，可以使用“ USE <数据库 >” 命令把已建
立的一个数据库置为当前数据库。此后所有操作,包括建立、修改、删除基本表等操作均对当前数据库进行操作。
例： Create DATABASE 教学管理库

3.删除数据库 :

DROP DATABASE <数据库名 >

功能: 删除（撤消）数据库；同时将删除数据库中所有对象。
例： DROP DATABASE 教学管理库

二、表

1.表的概念:表是数据库的操作对象之一，是数据库最基本、最重要的对象。其他对象，如查询、窗体和报表都是在表的基础上建立和使用的。表由行（记录）和列（字段）组成。

学号	课程号	成绩
001	K01	60
001	K02	70
002	K01	90
002	K02	80

2.数据表具有的属性：

• 表名:表的唯一标识；
• 记录:表中的每行为一条记录
• 字段:表的列为字段，每个字段需定义字段名称、字段类型、宽度等；
• 主关键字:能够唯一地标识表的每条记录的字段或字段组合。
  • 如:学生表的主关键字为“学号”，选课表的“学号”和“课程号”的组合为关键字。
• 域:每个字段的取值范围。

3.创建表结构

• 命令格式:CREATE TABLE <基本表名> （<列名 1> <列的类型> <列的完整性> ， <列名 2> <列的类型><列的完整性>, …）

  如: 创建学生信息表
  create table student (
      sno int primary key auto_increment not null,
      sname varchar(10),
      sage int 
  );

• 功能：在当前或给定的数据库中定义一个表结构（关系模式）
• 列定义包括：属性名，数据类型，完整性约束。

  CREATE TABLE IF NOT EXISTS `book`(
     `book_id` INT UNSIGNED AUTO_INCREMENT,
     `book_title` VARCHAR(100) NOT NULL,
     `book_author` VARCHAR(40) NOT NULL,
     `submission_date` DATE,
     PRIMARY KEY ( `runoob_id` )
  )ENGINE=InnoDB DEFAULT CHARSET=utf8;

  实例解析：
• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
• PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
• ENGINE 设置存储引擎，CHARSET 设置编码。

常用的表中数据类型有以下几种：

• char(n): 定长度字符型，如：姓名 char(10) ；
• varchar ：变长度字符型；
• Int ：整型，占 4 个字节；
• Float ：浮点数（实数）占 4 或 8 个字节；
• Date ：日期型 yyyy-mm-dd,(yyyy/mm/dd) 占 4 个或 8 个字节

列级完整性约束 : 有六种

• DEFAULT< 常量表达式 > ：默认值约束。
• NULL/NOT NULL 空值 / 非空值：对于非主属性，若不注明此约束，则隐含为允许空值
  约束。
• PRIMARY KEY：主码约束。
• UNIQUE：单值约束，惟一值约束。
• REFERNCES< 父表名 > (< 主码 >): 外码约束。
• CHECK(< 逻辑表达式 >): 检查约束，取值限制

举例：创建学生基本表
CREATE TABLE 学生 ( 
学生号 char(7) primary key ，
姓名 char(6) ，
性别 char(2) ，
出生年月 datetime, 
专业 char(10), 
年级 int );

表级完整性约束 : 有四种

• PRIMARY KEY（< 列名 >, … ）主码约束 : 注明一个或多个列为主码。
• UNIQUE（< 列名 > , … ）单值约束：注明一个或同时若干个列为单值。
• FOREING KEY < 列名 >, … REFRENCES< 父表名 >(< 主码列名 >) 外码约束：注明一
  个或同时若干个列为外码。
• CHECK(< 逻辑表达式 >) 检查约束：注明每行中一个或若干个列的取值限制

除默认值约束、空值 / 非空值约束外，对于其它约束，若只涉及到一个列时，则可以作列级完整性约束，又可以作表级完整性约束。若涉及到多个列时,则只能作为表级完整性约束

举例：创建选课基本表
CREATE TABLE 选课 ( 
学生号 char(7) ，
课程号 char(4), 
成绩 int check( 成绩 >=0 and 成绩 <=100), 
 primary key ( 学生号 , 课程号 ), 
 Foreign key ( 学生号 ) refrences 学生 ( 学生号），
 Foreign key ( 课程号 ) refrences 课程 ( 课程号）
)

4．表的删除

DROP TABLE <表名>

三、索引

1.索引的概念:索引提供一种快速访问数据的途径 ,当数据库很大时，在表中建立若干个索引，会加快查询。索引有以下特点：

• 索引是在表中的某列上建立的数据库对象，它和表分开存储，它的建立或撤消对
  数据的内容没有影响。
• 索引一经创建，就完全由系统自动选择和维护。
• 表中的记录数量越多，越有必要建立索引。
• 索引是为加快查询速度，但其影响表中数据的插入、删除和修改

2. 创建原则

• 表中的记录数量越多，越有必要建立索引。
• 索引是为加快查询速度，但其影响表中数据的插入、删除和修改。
• 索引是建立在基本表列上的对象,通常需要在经常出现在 WHERE、ORDER BY、GROUP BY子句中的列建立索引。

3. 不易创建索引的情况

• 包含太多重复值的列
• 查询中很少被引用的列
• 值特别长的列
• 具有很多 NULL 值的列
• 需要经常更新的列
• 记录较少的表等。

4. 索引的类型

• 单列索引 : 对基本表的某一单独列进行索引，通常是对主关键字建立。
• 唯一索引 : 建立唯一索引后，则不允许在该列上插入相同的值。
• 复合索引 : 对表的两个或多个列上建立索引。
• 聚集索引 : 改变表中记录的物理存储顺序，即表中记录的物理排列顺序不再按插入的顺序排列。
• 非聚集索引 : 不改变表中记录的物理存储顺序，即表中记录的物理顺序和索引顺序不同

5. 聚集索引和非聚集索引对比 :

• 存取速度 : 聚集索引查询速度快 , 但降低更新速度。
• 索引的数据 : 一个表只能建立一个聚集索引, 但可以建立多个非聚集索引。
• 所用空间 : 非聚集索引需要较多的空间。

6.创建索引 : CREATE [UNIQUE] [CLUSTERED] INDEX <索引名 > ON < 表名 > (< 列名 1> < 次 序> …。

• UNIQUE：创建唯一索引；
• CLUSTERED：创建聚集索引；
• 次序：表示排列次序， ASC 表示升序； DESC 表示降序。
  如：

  CREATE INDEX indexName ON mytable(username(length));

  如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

7. 修改表结构(添加索引)

   ALTER table tableName ADD INDEX indexName(columnName)

8. 删除索引：

   DROP INDEX [indexName] ON mytable; ，… 一次可以删除一个或多个索引

   唯一索引

   它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

四、视图

• 视图是建立在基本表上的一个虚表，它的结构和内容都来自基本表，它依据基本表存在而存在。视图的列可以来自一个基本表或多个基本表。

• 视图的建立和删除只影响视图本身，不影响对应的基本表，而对视图的更新（插入、删除和修改）直接影响基本表。对视图做的最多的是查询操作。 当删除表时同时删除在表建立的视图

• 视图能够增强安全性，使数据库中一些保密的数据对无关人员成为不可见的，从而不能随意查询。

创建视图：

CREATE VIEW <视图名 >（<列名 >，… ) AS <SELECT 子句 >；

为当前数据库根据 SELECT 子句查询结果建立视图。

举例： 把计算机系学生的学号和姓名创建为视图：

CREATE VIEW CSStudent AS 
 SELECT StudentNo, StudentName 
 FROM Student 
 WHERE Dept=‘计算机系' ；

可把 CSStudent 看作是一个虚拟的表

删除（撤消）视图：

DROP VIEW <视图名 >；

五、存储过程

1.存储过程 : 存储过程是一组预先编译好的 Transact SQL 代码，是一种可执行的永久存储在数据的SQL 代码中。可以作为一个独立的数据库对象被用户使用。

2.存储过程的作用 : 它可以接收参数 , 并以输出参数的形式返回多个参数给调用它
的过程或批处理，使用存储过程优点：

• 执行速度快 : 存储过程在创建时就经过了语法检查和性能优化，因此在执行时不
  必再重复这些步骤。 存储过程在经过第一次调用后， 就驻留在内存中， 不必再经过编译和优
  化，所以执行速度快。
• 模块化的程序设计 : 可以被多次调用， 用户可以独立于应用程序而对存储过程进
  行修改，增加了数据库应用程序的易修改特征。
• 减少网络流量 : 存储过程可以包含大量的 Transact SQL 语句 , 但在进行调用时 ,
  客户端只需要使用一个语句就可以实现了 , 大大减少了网络上数据的传输量。
• 保证系统的安全性 : 存储过程存放在数据库服务器端 , 对于某些关键数据 , 可以
  设置成只通过存储过程来访问 , 不允许用户直接使用 Transact SQL 语句或企业管理器对
  数据进行访问

3.创建存储过程：

CREATE PROC 存储过程名 AS SQL 语句

SQL语句是利用 Transact SQL 编写的程序

4.执行存储过程 : 如果对存储过程的调用是批处理的第一条语句， 则可直接使用存储
过程的名字调用。如果不是第一条语句，调用的语法： EXEC 存储过程名。

举例 : 创建 USER_PROC1 存储过程

CREATE PROC USER_PROC1 
AS 
SELECT * FROM 学生表
GO

六、触发器

触发器是一个特殊的存储过程 , 它基于一个表的创建 , 但可以针对多个表进行操作 , 主要用来保证数据的完整性。一张表可以创建多年触发器 , 用户可以针对 UPDATE、
DELETE、INSERT 语句分别设置触发器，当用户进行 UPDATE、 DELETE、INSERT等操作后，
这些事先定义好的触发器对象就会被触发，并按事先定义好的规则自动执行。

创建触发器：

CREATE TRIGGER 触发器名 ON 表名 / 视图名 OF [DELETE，INSERT，
UPDATE] AS SQL 语句

主要参数： DELETE、INSERT、UPDATE 指定在表或视图上执行哪些数据修改语句时将激
活触发器的关键字。必须至少指定一个选项。

举例： 当对学生表执行 DELETE 语句时 , 同时删除成绩表中学号相同的记录。

CREATE TRIGGER 删除学号 ON 学生表
 OF DELETE 
 AS 
 DECLARE @Myno char(4) 
 Select @Myno= 学号 FROM DELETED 
 DELETE FROM 成绩表 WHERE 学号 =@Myno 
 RETURN

3.4 数据操纵语言(DML)

1.插入记录命令格式

INSERT [INTO] < 基本表名 >（<列名 >, … ) VALUES ( 列值 , … )

向表中插入完整的记录时 , 可省略列名列表。例如：学生关系（学生号，姓名，性别，专业）

INSERT into 学生（学生号，姓名，性别，专业） VALUES（'001'，'李宏' ，'女' ，'计算机'）

与下面语句功能相同

INSERT into 学生 VALUES（'001'，'李宏' ，'女' ，'计算机'）

2.修改记录命令格式

UPDATE <表名 > SET < 列名 >= < 表达式 >, … WHERE < 逻辑表达式 > ；

功能：按条件修改表中的一些列值。 SET：要修改的列值；WHERE：修改记录条件，省略参数将修改表中所有记录。

例: 将所有计算机系学生的年龄增加 1 岁，并且把系名改为”计算机科学系”

UPDATE Student SET Age=Age+1, Dept='计算机科学系' WHERE Dept= '计算机科学系';

3.删除记录命令格式

DELETE FROM <表名 > WHERE <逻辑表达式 >;

例: 删除学号为 9900203 的学生记录

DELETE FROM Student WHERE 学生号 ='9900203';

3.5 数据查询语言

SQL中最简单的查询就是从某个关系中查找满足某种条件的元组，类似于关系代数中的选择运算

一、查询语言格式

Select 字段列表 [Into]< 基本表 >] 
 From < 源表名 > 
 [Where < 查询条件 > ] 
 [GROP by < 分组列名 1> [Having < 查询条件 >] 
 [Order by < 排序表达式 > [ASC] [DESC]

二、简单查询

假设教学数据库包括： 学生、课程和成绩三个基本表。

1.查询指定的字段：用“，”隔开需要查询的字段
例: 查询学生表所有学生的学号和姓名。

Select 学号 , 姓名 FROM 学生

2.通配符“ ”查询所有字段
*例:** 查询学生表所有学生的所有字段

Select * FROM 学生

3.基于字段的表达式 : 使用字段构成需要查询的表达式，如：

例: 查询所有学生的学号和出生年月。

Select 学号 ,STR(YEAR(出生日期 ),4)+ ‘年’ + STR(MONTH( 出生日期 ),2)+ ‘月’ AS 出生年月 FROM 学生

AS选项定义表达式的列名，若省略 AS 选项，则字段名作为列名使用。

4.ALL/DISTINCT : 默认值为 ALL, 表示允许查询结果中出现内容重复的行；若选择
DISTINCT 则表示不允许出现内容重复的行。
例: 查询学生表中专业数。

Select DISTINCT 专业  FROM 学生

5.WHERE选项 : 定义基本表之间的连接条件和对记录的筛选条件。连接条件通常为等值连
接, 筛选条件是从源表中选择出满足条件的行,筛选条件可以是一般比较式,也可以是通过逻辑运算符连接的逻辑表达式

1)使用 BETWEEN 短语查询。短语格式：

BETWEEN< 开始值 > AND < 结束值 >

例： 查询年龄在 19 岁-21 岁的学生。

select * from 学生 where 年龄 19 and 21;

2)使用 NULL 查询：
例： 查询成绩不为空值的所有记录。

select * from 成绩 where NOT （成绩 IS NULL）

3)使用日期进行查询：
例： 查询 1985 年 1 月 1 日 后出生的学生。

select * from 学生 出生日期 >'1985-01-01';

4)使用 IN 查询： IN( 常量表 )
例： 查询年龄为 19 岁或 21 岁的记录。

select * from 学生 where 年龄 IN(19,21);

5)使用 LIKE 查询：列名 LIKE 表达式 ( 用%(所有)、_(一个占位符)构成表达式）
例： 查询姓“王” 的所有学生。

select * from 学生 where 姓名 LIKE ‘王 %';

6.ORDER BY < 排序列名 1> ， < 排序列名 2>：将查询结果按排序列进行排序，查询结果首
先按排序列名 1 的值排序， 若该列的值相同， 再按排序列名 2 的值排序。 排序方式有两种，
关键字 DESC 表示降序， ASC 表示升序，若省略表示升序。
例: 从教学库中查询出所有学生的学号和姓名，查询结果按学生号升序排列。

select 学生号 , 姓名 from 学生 order by 学生号 ASC;

7.统计函数的使用：

• COUNT（[ALL/DISTINCT]< 列名 >|* ）：统计指定列的值个数；
• MAX（< 列名 > ）求出对应的数值、字符或日期列的最大值；
• MIN（< 列名 > ）求出对应的数值、字符或日期列的最小值；
• AVG（< 列名 > ）求出对应的数值列的平均值；
• SUM（< 列名 > ）求出对应的数值列的总和。

例: 统计学生人数

Select COUNT (*) AS 学生人数  FROM 学生;

例: 统计每个专业的学生人数。

Select COUNT (DISTINCT 专业 ) AS 专业数 FROM 学生;

8.GROUP BY <分组列名 > ：按分组列名对查询结果中的所有元组进行分组，使得分组列值
相同的为一组，形成结果表中的一个元组。 select选项中只能包括分组列名和统计函数。

例: 查询每个专业的学生数。

SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;

+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+

9.WITH ROLLUP:实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）

SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+

可以使用coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+

三、复杂查询： 涉及多个表的查询

1.连接查询 : 两表按公共字段等值连接查询
例 : 查询学生的学号、姓名、课程号和成绩。查询需要用到两张表（学生，成绩）

SELECT 学号、姓名、课程号，成绩
FROM 学生表，成绩表
WHERE 学生表.学号 =成绩表.学号;

例: 查询出所有计算机专业学生的学号 , 姓名 , 课程号 , 课程名和成绩。 查询条件包括连接条件和选择条件。

SELECT 学号、姓名、课程号，成绩
FROM 学生表，成绩表
WHERE 学生表.学号 =成绩表.学号 AND 专业 ='计算机';

2.JOIN 来联合多表查询。

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

INNER JOIN

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值：

LEFT JOIN

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

该语句会读取左边的数据表 runoob_tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl中 没有对应的 runoob_author 字段值。

RIGHT JOIN

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

该语句会读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 runoob_tbl 中没有对应的runoob_author 字段值。

3.嵌套查询 : 一个 SELECT 查询嵌套在另一个查询语句中。

例 1: 查询所有被女生选出的课程号和课程名

SELECT 课程号 , 课程名 FROM 课程表
WHERE 课程号 IN (SELECT 课程号 FROM 成绩单 WHERE 学号 IN (SELECT 学号 FROM 学生表 WHERE 性别 ='女 '））;

第一步：最内层的查询功能查询出所有女生的学号
第二步：第二层查询功能是从成绩单表中查询出女生选修课程的课程号；
第三步：最外层查询功能是从课程表中查询出被女生选修的所有课程的课程名和课程号。

例 2: 求成绩小于总平均成绩的学生学号、姓名、专业。

SELECT 学号 , 姓名，专业 FROM 学生表
WHERE 学号 IN (SELECT DISTINCT 学号 FROM 成绩单 WHERE 成绩 < (SELECT AVG （成绩） FROM 成绩表 ));

第一步 : 计算全体学生的总平均成绩；
第二步 : 在成绩单中求出所有成绩小于总平成绩的学号；
第三步 : 用 IN 求出所有学号在第二步查出学号集合中的学生学号、姓名和专业。

四、正则表达式查询

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。
.	匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符，请使用象 ‘[.\n]’ 的模式。
[…]	字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^…]	负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1	p2
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，’zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，’o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

实例:
查找name字段中以’st’为开头的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以’ok’为结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含’mar’字符串的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

3.6 数据控制语言(DCL)

1.事务 : 事务是并发控制的基本单位，它反映了现实世界中需要以一个完整的单位提交的
一项工作。 SQL Server 通过事务机制，将逻辑相关的一组操作捆绑在一起，以便服务器保
持数据的完整性

一般来说，事务是必须满足4个条件（ACID:原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性:一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性:在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性:事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

并发事务带来的问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
• 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复度和幻读区别：

不可重复读的重点是修改，幻读的重点在于新增或者删除。

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导 致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假如某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。

事务隔离级别

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看,MySQL 8.0 该命令改为SELECT @@transaction_isolation;

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交

2.事务处理控制语句：

• 开始一个事务： BEGIN TARNSACTION 事务名
• 提交一个事务： COMMIT TRAN 事务名
• 回滚一个事务： ROLLBACK TRAN 事务名

在数据库应用程序设计中 , 事务控制语句一般用法是 :

• 第 1 步: BEGIN TRAN 事务名；
• 第 2 步: 对数据库进行增、删、改等操作；
• 第 3 步：提交事务或回滚。 基本逻辑是，如果没有发生异常情况，则通过 COMMIT 语
  句提交事务，确认第 2 步的操作；否则执行 ROLLBACK 回滚命令，撤消第 2 步的所有操作。

例:

USE 教学管理;
SELECT * INTO 学生表 1 FROM 学生表
BEGIN TRAN 
UPDATE 学生表 1 SET 年龄 =年龄 +1 
DELETE FROM 学生表 1 WHERE 年龄 >20 
COMMIT TRAN 
GO 
DROP TABLE 学生表 1

事务中包括两条语句，对学生表年龄进行修改后，再删除学生表中年龄大于 20 岁的
记录，这两条语句要么都执行，要么都不执行

MySQL ALTER命令

删除，添加或修改表字段

使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段：

ALTER TABLE testalter_tbl  DROP i;

使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:

ALTER TABLE testalter_tbl ADD i INT;

修改字段类型及名称

ALTER命令中使用 MODIFY 或 CHANGE 子句进行修改字段类型及名称

例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

ALTER TABLE testalter_tbl MODIFY c CHAR(10);

使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型

ALTER TABLE testalter_tbl CHANGE i j BIGINT;

ALTER TABLE 对 Null 值和默认值的影响

当你修改字段时，你可以指定是否包含值或者是否设置默认值。如,指定字段 j 为 NOT NULL 且默认值为100 。

ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;

修改字段默认值

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

#ALTER 命令及 DROP子句来删除字段的默认值，
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

修改表名

修改数据表的名称使用ALTER TABLE 语句中使用 RENAME 子句来实现。

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

MySQL 临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

实例

#创建临时表
CREATE TEMPORARY TABLE SalesSummary (
     product_name VARCHAR(50) NOT NULL
    , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);

#添加一条记录
INSERT INTO SalesSummary(product_name, total_sales, avg_unit_price, total_units_sold)
  VALUES
  ('cucumber', 100.25, 90, 2);
# 查询临时表 
SELECT * FROM SalesSummary;

+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+

当你使用 SHOW TABLES命令显示数据表列表时，你将无法看到 SalesSummary表。如果你退出当前MySQL会话，再使用 SELECT命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时该临时表已经被销毁了。

MySQL 复制表

第一、只复制表结构到新表

create table 新表 select * from 旧表 where 1=2

或者

create table 新表 like 旧表

第二、复制表结构及数据到新表

create table 新表 select * from 旧表

MySQL 元数据

命令	描述
SELECT VERSION( )	服务器版本信息
SELECT DATABASE( )	当前数据库名 (或者返回空)
SELECT USER( )	当前用户名
SHOW STATUS	服务器状态
SHOW VARIABLES	服务器配置变量

MySQL 序列使用

MySQL 序列是一组整数：1, 2, 3, …，由于一张数据表只能有一个字段自增主键， 如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。

CREATE TABLE insect
    (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id),
    name VARCHAR(30) NOT NULL, # type of insect
    date DATE NOT NULL, # date collected
    origin VARCHAR(30) NOT NULL # where collected
);

重置序列

可以通过删除自增的列，然后重新添加来实现。

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    ADD PRIMARY KEY (id);

设置序列的开始值

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, 
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

或者你也可以在表创建成功后

mysql> ALTER TABLE t AUTO_INCREMENT = 100;