mysql note

MySQL是一种数据库管理系统 (DBMS)，是基于客户机-服务器的数据库；

分为两个不同的部分，

服务器软件（MySQL DBMS）是负责所有数据访问和处理的一个文件，这个软件运行在称为数据库服务器的计算机上，与数据文件打交道；

客户机则是与用户打交道的软件，对于用户提出的申请提交给服务器软件，经过处理后，再返回客户机；客户机可以是MySQL提供的工具，也可以是脚本语言、web应用开发语言、程序设计语言

数据模型

属性的特点

  • 属性不可再分
  • 一个实体的属性可以有很多个
  • 用于唯一区分不同实体的属性，称为key
  • 属性取值可以有一定的约束

实体或是属性之间可以具有一定的联系

MySQL是一种关系型数据库，可以通过画ER图，来构建关系

数据库的创建

主键

主键必须非NULL，且不能重复；

可以一起使用多个列作为主键，所有列值的组合必须是唯一的，但是单列值可以不唯一

主键的好习惯

  • 不更新主键列中的值
  • 不重用主键列的值
  • 不在主键列中使用可能会更改的值

image-20230505222127304|500

创建出来的结果

image-20230505221957373|500

现在我们填入表

浅蓝色部分表示未提交的

image-20230505222656023|500

查询

1
# SELECT * FROM study WHERE account = 20221011

image-20230505223000606|500

1
SELECT * FROM study INNER JOIN teach ON study.account = teach.sid WHERE teach.tid = (SELECT tid FROM study WHERE name = 'ALice')

image-20230505223743738|500

设置父表

image-20230505224319470|500

通过图表观察关系

image-20230505224122154|500

数据库设计规范

第一范式（1NF）

第一范式是指数据库的每一列都是不可分割的基本数据项，而下面这样的就存在可分割的情况：

学生（姓名，电话号码）
电话号码实际上包括了家用座机电话和移动电话，因此它可以被拆分为：

学生（姓名，座机号码，手机号码）
满足第一范式是关系型数据库最基本的要求

第二范式（2NF）

第二范式要求表中必须存在主键，且其他的属性必须完全依赖于主键

第三范式（3NF）

在满足第二范式的情况下，所有的属性都不传递依赖于主键，满足第三范式

学生借书情况（借阅编号，学生学号，书籍编号，书籍名称，书籍作者）
实际上书籍编号依赖于借阅编号，而书籍名称和书籍作者依赖于书籍编号，因此存在传递依赖的情况，我们可以将书籍信息进行单独拆分为另一张表：

学生借书情况（借阅编号，学生学号，书籍编号）
书籍（书籍编号，书籍名称，书籍作者）
这样就消除了传递依赖，从而满足第三范式。

BCNF

BCNF作为第三范式的补充，假设仓库管理关系表为StorehouseManage(仓库ID, 存储物品ID, 管理员ID, 数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：

(仓库ID, 存储物品ID) →(管理员ID, 数量)

(管理员ID, 存储物品ID) → (仓库ID, 数量)

所以，(仓库ID, 存储物品ID)和(管理员ID, 存储物品ID)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：

(仓库ID) → (管理员ID)

(管理员ID) → (仓库ID)

SQL语句

结构化查询语言（Structured Query Language）简称SQL，这是一种特殊的语言，它专门用于数据库的操作。每一种数据库都支持SQL，但是他们之间会存在一些细微的差异，因此不同的数据库都存在自己的“方言”

SQL语句由子句(clause)构成，一个子句通常有一个关键字和所提供的数据构成；有些子句是必须的，有些则是可选的

SQL语句不区分大小写（关键字推荐使用大写），它支持多行，并且需要使用;进行结尾

image-20230506153928576|500

SQL也支持注释，通过使用--或是#来编写注释内容，也可以使用/*来进行多行注释

CRUD 增删改查

  • 数据查询语言（Data Query Language, DQL）基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块。
  • 数据操纵语言（Data Manipulation Language, DML）是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除，是开发以数据为中心的应用程序必定会使用到的指令。
  • 数据库定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。
  • DCL（Data Control Language）是数据库控制语言。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

SQL数据类型

字符串存储

  1. char() 可以存储任意长度的字符串，固定长度，不满则用空格填充
  2. varchar() 长度不固定，但是不能超过n

存储数字

  1. smallint用于存储小的整数，范围在 (-32768，32767)
  2. int用于存储一般的整数，范围在 (-2147483648，2147483647)
  3. bigint用于存储大型整数，范围在 (-9,223,372,036,854,775,808，9,223,372,036,854,775,807)
  4. float用于存储单精度小数
  5. double用于存储双精度的小数

存储时间

  1. date存储日期
  2. time存储时间
  3. year存储年份
  4. datetime用于混合存储日期+时间

二进制数据类型

数据库定义语言 DDL

列级约束条件

主键Primary key、外键foreign key 、唯一 unique、检查 check （MySQL不支持）、默认default 、非空/空值 not null/ null

表级约束条件

主键、外键、唯一、检查

例：设置外键

1
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

注：实在不会的话，建议在应用内操作，他会给你自动生成代码，自己再打一遍就好了

1
2
3
4
mysql> CREATE TABLE teach(
    -> tid int NOT NULL,
    -> CONSTRAINT f_tid FOREIGN KEY (tid) REFERENCES teacher(tid)
    -> );

数据库操作

创建数据库

+设定编码格式

1
CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

image-20230506155118944|500

此时刷新数据库，会发现构造成功

|500

删除数据库

1
DROP DATABASE 数据库名

表

创建

1
2
3
4
create table 表名(列名 数据类型[列级约束条件],
             列名 数据类型[列级约束条件],
             ...
             [,表级约束条件])

image-20230506160642012|500

此时，就已经创建成功了

image-20230506160808943|500

修改表

1
2
3
ALTER TABLE 表名[ADD 新列名 数据类型[列级约束条件]]
							 [DROP COLUMN 列名[restrict|cascade]]
							 [ALTER COLUMN 列名 新数据类型]

例

1
2
mysql> ALTER TABLE teacher add
    -> sex enum ('male', 'female') NOT NULL DEFAULT 'male';

删除表

1
DROP TABLE  表名;

其中restrict和cascade上面的效果一致

数据库操纵语言 DML

插入数据 INSERT

1
INSERT INTO 表名 VALUES (, , );

例

1
2
3
4
mysql> INSERT INTO student values(
    -> 26221011,
    -> 'Alice',
    -> 'female');

如果插入的数据与列一一对应，那么可以省略列名，如果希望向指定列上插入数据，则需要指定列名

1
INSERT INTO 表名(列名1，列名2) VALUES (值1，值2);

也可以一次性插入多个数据 

1
INSERT INTO 表名(列名1，列名2) VALUES (值1，值2)，(值1，值2)，(值1，值2);

修改数据 UPDATE

如果忘记添加where，那么将修改整张表的数据

1
UPDATE 表名 SET 列名 = 值, ... WHERE 条件 ;

image-20230506165558457|500

删除数据 DELETE

1
DELETE FROM 表名 WHERE 条件;

数据库查询语言DQL

单表查询

1
2
3
4
5
6
7
8
-- 指定查询某一列数据
SELECT 列名[,列名] FROM 表名
-- 会以别名显示此列
SELECT 列名 别名 FROM 表名
-- 查询所有的列数据
SELECT * FROM 表名
-- 只查询不重复的值
SELECT DISTINCT 列名 FROM 表名

例

image-20230506170303430 
1
SELECT * FROM 表名 WHERE 条件 ;

常用查询条件

  • 一般的比较运算符，包括=、>、<、>=、<=、!=等。

  • 是否在集合中：in、not in

  • 通配符：like，not like， %(表示任何字符出现任意次数)，_(只匹配单个字符)

    使用通配符的技巧

    1. 不要过度使用
    2. 不要把他们用在 搜索模式的开始处，这样搜索起来是最慢的
    3. 注意放置位置

  • 逻辑操作符：and、or、not

  • BETWEEN AND 和 IS NULL

1
2
3
4
5
SELECT * FROM student WHERE sid like %03; // 以03结尾的
SELECT * FROM student WHERE name not in('Alice','Bob');
SELECT * FROM student WHERE name not in('Alice','Bob') and sid like %03;
SELECT * FROM student WHERE name IS NULL;
SELECT * FROM student WHERE sid BETWEEN 26221010 AND 26221014;

排序查询

ASC表示正序、 DESC表示降序，默认升序；关键字只应用到直接位于前面的列名

ORDER BY 应该位于 WHERE 的后面

1
SELECT * FROM 表名 WHERE 条件 ORDER BY 列名1 ASC| DESC, 列名2 ASC|DESC;

分组和分页查询

对查询结果进行分组，通常需要结合聚合函数一起使用

1
SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名 ;
image-20230506172322532

通过添加having，来增加约束条件

1
SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 约束条件

限制结果

通过limit来限制查询的数量，只取前n个结果：

注：检索出来的第一行为行0

1
2
3
LIMIT 开始行, 行数;
LIMIT 行数 OFFSET 开始行; #MySQL 5 开始支持
SELECT * FROM 表名 LIMIT 数量

分页查询

1
SELECT * FROM 表名 LIMIT 起始位置,数量

多表查询

多表查询会通过连接转换成单表查询

直接查询会得到两张表的笛卡尔积，也就是每一项数据都和另一张表的数据结合一次，因此产生庞大的数据

如果两个表中都带有一样的属性，需要添加前缀来表明是哪一张表的数据

1
SELECT * FROM 表1, 表2 WHERE 条件

自身连接查询

自身连接查询，就是将表本身和表进行笛卡尔积计算，得到结果，但是由于表名相同，所以要先起一个别名

1
SELECT * FROM 表名 别名1, 表名 别名2

自身连接查询和前面是一样的， 只不过连接对象变成了自己和自己

外连接查询

外连接就是专门用于联合查询情景的，比如我们希望把两张表结合起来查看完整的数据，就可以通过使用外连接来进行查询，外连接有三种方式

inner join 进行内连接，只会返回两张表的交集部分

通过使用left join进行左连接，不仅会返回两个表满足条件的交集部分，也会返回左边表中的全部数据，而在右表中缺失的数据会使用null来代替（右连接right join同理）

image-20230506175638573

嵌套查询

可以将查询结果作为另一个查询的条件

1
2
3
SELECT * FROM 表名 WHERE 列名 = (SELECT 列名 FROM 表名 WHERE 条件)

mysql> SELECT * FROM student WHERE sid = (SELECT sid FROM teach WHERE tid = (SELECT tid FROM teacher WHERE name = 'Allay'));

数据库控制语言DCL

查看用户

1
2
USE mysql;
SELECT user FROM user;

创建用户

1
2
CREATE USER 用户名 IDENTIDIED BY 'PASSWORD';
CREATE USER 用户名 IDENTIDIED BY RANDOM PASSWORD;

示例

1
2
3
4
5
6
7
8
9
10
mysql> CREATE USER Robot IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE USER REST IDENTIFIED BY RANDOM PASSWORD;
+------+------+----------------------+-------------+
| user | host | generated password   | auth_factor |
+------+------+----------------------+-------------+
| REST | %    | Ou&]*FQdgw[ZHQb&DTo< |           1 |
+------+------+----------------------+-------------+
1 row in set (0.04 sec)

登录用户

首先需要添加一个MySQL Server 的环境变量，然后通过cmd进行登录

1
C:\Users\lenovo>mysql -u Robot -p

访问数据库

1
show databases;

用户授权

访问控制的目的不仅是防止用户的恶意企图，数据梦魇更为常见的是无意识错误的结果；不要轻易使用root

可以通过使用grant来为数据库用户进行授权

1
2
3
grant all|权限1,权限2...(列1,...) on 数据库.表 to 用户 [with grant option];
grant all on need.* to Robot;
mysql> grant select, update(name) on need.student to Robot;

其中all代表授予整个服务器所有权限，当数据库和表为*，代表为所有的数据库和表都授权。如果在最后添加了with grant option，那么被授权的用户还能将已获得的授权继续授权给其他用户

我们可以使用revoke来收回一个权限：

1
revoke all|权限1,权限2...(列1,...) on 数据库.表 from 用户
权限

其他操作

重命名

1
RENAME USER ben TO bforta;

删除用户和相关权限；旧版本则需要revoke+drop一起，因为旧版本的drop仅删除账号

1
DROP USER bforta;

查看用户权限：USAGE表示根本没权限

1
SHOW GRANTS FOR bforta;

更改密码

1
SET PASSWORD FOR user = Password('134t'); #若不指定用户名，则修改当前用户

计算字段

字段 field

基本上与列的意思相同，经常互换使用；但是数据库列一般称为列，而术语字段通常用在计算字段的连接上；

只有数据库能区分SELECT语句中哪些是实际的表列，哪些列是计算字段；从客户机（应用程序）的角度来看，计算字段的数据和其他列的数据是以相同的方式返回的

拼接 concatenate

在MySQL中的select语句，可以使用Concat来拼接两列

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT Concat(Rtrim(name), '( ',Rtrim(sex),')')
    -> FROM student
    -> ORDER BY name;
+------------------------------------------+
| Concat(Rtrim(name), '( ',Rtrim(sex),')') |
+------------------------------------------+
| Allay( female)                           |
| Bob( male)                               |
| Casey( female)                           |
| David( male)                             |
| Elien( female)                           |
| fairy( female)                           |
+------------------------------------------+

Trim() 函数

MySQL除了支持前面使用到的Rtrim()以外，还有Trim() , Ltrim()，分别是去掉串左右两边的空格，去掉串右边的空格

别名 （alias）

是一个字段或者值的替换名，用AS 赋予；有时也称为导出列

任何客户机引用都可以按名引用这个列，就像他是一个实际的表列一样

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT price,
    -> number,
    -> price * number AS expanded_price
    -> FROM purchase;
+-------+--------+----------------+
| price | number | expanded_price |
+-------+--------+----------------+
|     2 |    100 |            200 |
|     3 |     23 |             69 |
|    12 |     76 |            912 |
+-------+--------+----------------+
3 rows in set (0.00 sec)

其他用途

在实际的表列包含不符合规定的字符（如空格）时重命名；在原来的名字容易混淆时扩充它；

函数

函数的可移植性没有SQL强，所以要确保做注释

大多数SQL支持以下类型的函数

  1. 用于处理文本串的文本函数
  2. 用于在数值数据上进行算术操作的数值函数
  3. 用于处理日期和时间值并从这些值中提取特定成分的日期和时间函数
  4. 返回DBMS正在使用的特殊信息的系统函数

文本处理函数

实例

1
2
3
4
#注意如果使用的是UTF-8编码格式，那么一个汉字占3字节，数字和字母占一个字节）
SELECT LENGTH(`name`) FROM student 
#获取第二个字
SELECT SUBSTRING(name, 2, 2) FROM student

关于SOUNDEX

是一个将任何文本串转换为描述其语音表示的字母数字模式的算法，它考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较

1
2
3
SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

日期和时间处理函数

日期和时间处理函数采用相应的数据类型和特殊的格式存储，以便能快速有效地排序或者过滤，并且节省物理存储空间

它总是被用来读取、统计和处理这些值，在MySQL当中有着重要的意义

数据经常需要用日期进行过滤，首先需要注意的时MySQL的日期格式均为yyyy-mm-dd ，排除了多义性

1
2
3
SELECT cust_id, order_num 
FROM orders
WHERE order_date = '2023-05-07';

但是这种会出问题，因为date的类型为datetime ，它同时存储日期与时间值，因此我们就需要用到Date()函数，仅仅提取日期部分

1
2
3
SELECT cust_id, order_num 
FROM orders
WHERE Date(order_date) = '2023-05-07';

如果想要提取一个自然月的订单：

第一种方法就是使用 BETWEEN 

1
WHERE Date(order_date) BETWEEN '2023-04-01' AND '2023-04-30';

第二种方法：分别提取比较

1
WHERE Year(order_date) = 2023 AND Month(order_date) = 9;

其他实例

单位有：year（年）、month（月）、day（日）、hour（小时）、minute（分钟）、second（秒）

1
2
3
4
#延后5天
SELECT DATE_ADD('2022-1-1',INTERVAL 5 day)
#向前一年
SELECT DATE_ADD('2022-1-1',INTERVAL -1 year)

数值处理函数

在主要DBMS的函数中，数值函数是最统一最一致的函数

补充：ceiling(x) x向上取整；floor(x) x向下取整；round(x, 精度) x取四舍五入，遵循小数点精度 ；log(x) x的对数；power(x, n) x的n次方

类型转换函数

1
2
cast(数据   as 数据类型)
SELECT CAST(pi() AS SIGNED)

数据类型有以下几种：

  • BINARY[(N)] ：二进制字符串，转换后长度小于N个字节
  • CHAR[(N)] ：字符串，转换后长度小于N个字符
  • DATE ：日期
  • DATETIME ：日期时间
  • DECIMAL[(M[,N])] ：浮点数，M为数字总位数(包括整数部分和小数部分)，N为小数点后的位数
  • SIGNED [INTEGER] ：有符号整数
  • TIME ：时间
  • UNSIGNED [INTEGER] ：无符号整数

流程控制函数

MySQL还为我们提供了很多的逻辑判断函数，比如：

  • if(条件表达式, 结果1, 结果2) 与Java中的三目运算符一致 a > b ? “AAA” : “BBB”
  • ifnull(值1, 值2) 如果值1为NULL则返回值2，否则返回值1
  • nullif(值1, 值2) 如果值1与值2相等，那么返回NULL
  • isnull(值) 判断值是否为NULL

除了IF条件判断，我们还可以使用类似Switch一样的语句完成多分支结构：

1
2
3
4
5
6
7
SELECT 
CASE 2
	WHEN 1 THEN
		10
	ELSE
		5
END;

我们也可以将自定义的判断条件放入When之后，它类似于else-if：

1
2
3
4
5
6
7
8
9
SELECT 
CASE
	WHEN 3>5 THEN
		10
	WHEN 0<1 THEN
		11
	ELSE
		5
END;

还有一个类似于Java中的Thread.sleep的函数，以秒为单位：

1
SELECT sleep(10);

自定义函数

函数定义后不能修改

基本语法

可以添加参数和返回值，可以通过CREATE FUNCTION创建函数：

1
2
3
4
CREATE FUNCTION test() RETURNS INT
BEGIN
RETURN 666;
END

定义函数的格式

  • create function 函数名称([参数列表]) returns 返回值类型
  • begin 和 end 之间写函数的其他逻辑，begin和end就相当于Java中的花括号{ ... }
  • return后紧跟返回的结果

添加参数注意类型需要写在参数名称后面：

1
2
3
4
CREATE FUNCTION test(i INT) RETURNS INT
BEGIN
RETURN i * i;
END

局部变量

我们可以在BEGIN和RETURN之间编写一些其他的逻辑，比如我们想要定义一个局部变量，并为其赋值：

1
2
3
4
5
BEGIN
DECLARE a INT;
SET a = 10;
RETURN i * i * a;
END

定义局部变量的格式为：

  • declare 变量名称 变量类型 [, …]
  • declare 变量名称 变量类型 default 默认值

为变量赋值的格式为：

  • set 变量名称 = 值

我们还可以在函数内部使用select语句，它可以直接从表中读取数据，并可以结合into关键字将查询结果赋值给变量：

1
2
3
4
5
6
BEGIN
DECLARE a INT;
-- select into from 语句
SELECT COUNT(*) INTO a FROM student;
RETURN a;
END

全局变量

某些情况下，可以直接在一次会话中直接定义变量并使用，这时它并不是位于函数内的，这就是全局变量，它无需预先定义

1
set @x = 10;

可以将全局变量作为参数传递给函数：

1
select test(@x);

除了我们自己定义的全部变量以外，系统默认也有很多的变量

自己定义的变量称为用户变量，系统默认变量称为系统变量。查看系统变量的命令为：

1
show GLOBAL VARIABLES

聚集函数 aggregate function

聚集函数用来汇总数据，这些函数是高效设计的，返回结果一般比在自己的客户机应用程序中计算的要快得多

包括：

  • count([distinct]*)统计所有的行数（distinct表示去重再统计，下同）
  • count([distinct]列名)统计某列的值总和
  • sum([distinct]列名)求一列的和（注意必须是数字类型的）
  • avg([distinct]列名)求一列的平均值（注意必须是数字类型）
  • max([distinct]列名)求一列的最大值
  • min([distinct]列名)求一列的最小值
1
2
3
SELECT count(distinct 列名) FROM 表名 AS 别名 WHERE 条件 ;

SELECT COUNT(DISTINCT name) FROM student; //注意中间没有空格

image-20230506172032039|500

DISTINCT

DISTINCT关键字应用于所有列，而不仅是他的前置列，除非指定的两个列相同，否则所有行都会被检测出来

如果指定列名，DISTINCT只能用于Count()而不是Count(*)，也就是不允许使用Count(DISTINCT)；

DISTINCT不能用于计算或表达式，必须使用列名

分组

创建分组 GROUP BY

创建

分组是在SELECT语句中的GROUP BY子句中创建的

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT sid,
    -> COUNT(*) AS num
    -> FROM teach
    -> GROUP BY sid;
+----------+-----+
| sid      | num |
+----------+-----+
| 26221011 |   1 |
| 26221012 |   1 |
| 26221014 |   1 |
|        2 |   2 |
+----------+-----+
4 rows in set (0.00 sec)

规则

  1. 可以包含任意数目的列，这使得能对分组进行嵌套，为数据分组提供更细致的控制
  2. 在嵌套分组，数据在最后规定的分组上进行汇总；也就是说，在建立分组时，指定的所有列一起计算
  3. 子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数），如果在SELECT中使用表达式则必须在子句中指定相同的表达式，不能使用别名
  4. 除聚集计算语句外，SELECT语句中的每个列都必须在子句中给出
  5. 如果分组列中含有NULL值，则也会作为一个分组返回，列中有多行NULL值，则将他们分为一组
  6. 必须出现在WHERE子句的后面，ORDER BY语句之前

WITH ROLLUP

使用ROLLUP使用 WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组的值）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT sid,
    -> COUNT(*) AS num
    -> FROM teach
    -> GROUP BY sid WITH ROLLUP;
+----------+-----+
| sid      | num |
+----------+-----+
|        2 |   2 |
| 26221011 |   1 |
| 26221012 |   1 |
| 26221014 |   1 |
|     NULL |   5 |
+----------+-----+
5 rows in set (0.00 sec)

过滤分组

HAVING

WHERE过滤行，HAVING过滤分组

这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT sid,
    -> COUNT(*) AS num
    -> FROM teach
    -> GROUP BY sid
    -> HAVING COUNT(*) > 1;
+-----+-----+
| sid | num |
+-----+-----+
|   2 |   2 |
+-----+-----+
1 row in set (0.00 sec)

同时使用WHERE HAVING

例：返回过去十二月内有两个以上订单的顾客

1
2
3
4
5
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

子查询

用子查询测试和调试查询很有技巧性，特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立（和测试）查询的最可靠的方法是逐渐进行，这与MySQL处理它们的方法非常相同。

首先，建立和测试最内层的查询然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。这时，再次测试它。对于要增加的每个查询，重复这些步骤

这样做仅给构造查询增加了一点点时间，但节省了以后（找出查询为什么不正常）的大量时间，并且极大地提高了查询一开始就正常工作的可能性。

子查询最常见的使用是在WHERE子句的IN操作符中，以及用来填充计算列

1
2
3
4
5
6
mysql> SELECT sid
    -> FROM student
    -> WHERE name IN (SELECT tid
    -> FROM teach
    -> WHERE sid > 1);
Empty set, 6 warnings (0.00 sec)

在SELECT语句中，子查询总是从内向外处理

在使用子查询语句时，要注意分解为多行并且适当的缩进；能极大的简化子查询的使用

作为计算字段使用子查询

使用子查询的另外一种方式是创建计算字段

1
2
3
4
5
SELECT cust_name,
	cust_state, (SELECT COUNT(*)
                FROM orders
                WHERE orders.cust_id = customers.cust_id) AS orders FROM customers
                ORDER BY cust_name;

相关子查询 correlated subquery

涉及外部查询的子查询

任何时候只要列名可能有多义性就必须使用该语法（表名和列名由点号隔开），如果不完全限定列名，会出现歧义；所以必须限定有歧义性的列名

联结

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表，联结是利用SQL的SELECT能执行的最重要的操作

关系表

假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。对于每种物品要存储的信息包括产品描述和价格，以及生产该产品的供应商信息。
现在，假如有由同一供应商生产的多种物品，那么在何处存储供应商信息（如，供应商名、地址、联系方法等）呢？将这些数据与产品信息分开存储的理由如下。
❑ 因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间。
❑ 如果供应商信息改变（例如，供应商搬家或电话号码变动），只需改动一次即可。
❑ 如果有重复数据（即每种产品都存储供应商信息），很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用。
关键是，相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（relational））互相关联。
在这个例子中，可建立两个表，一个存储供应商信息，另一个存储产品信息。vendors表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键（primary key），可以是供应商ID或任何其他唯一值。
products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。vendors表的主键又叫作products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。
外键（foreign key） 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
这样做的好处如下：
❑ 供应商信息不重复，从而不浪费时间和空间；
❑ 如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动；
❑ 由于数据无重复，显然数据是一致的，这使得处理数据更简单
总之，关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好
可伸缩性（scale） 能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好（scale well）

为什么要使用联结

分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性
如果数据存储在多个表中，怎样用单条SELECT语句检索出数据？
答案是使用联结。简单地说，联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

维护引用完整性

联结不是物理实体。换句话说，它在实际的数据库表中不存在。联结由MySQL根据需要建立，它存在于查询的执行当中。
在使用关系表时，仅在关系列中插入合法的数据非常重要。回到这里的例子，如果在products表中插入拥有非法供应商ID（即没有在vendors表中出现）的供应商生产的产品，则这些产品是不可访问的，因为它们没有关联到某个供应商
为防止这种情况发生，可指示MySQL只允许在products表的供应商ID列中出现合法值（即出现在vendors表中的供应商）。这就是维护引用完整性，它是通过在表的定义中指定主键和外键来实现的

使用

1
2
3
4
SELECT tid,sid
       FROM teacher,student
       WHERE tid = sid
       ORDER BY tid,sid;
image-20230508212652526

在联结两个表的时候，我们实际上是在将第一个表中的每一行与第二个表中的每一行进行配对，所以WHERE语句就显得尤其关键，这样才能显示出符合条件的列

笛卡尔积：由没有联结条件的表关系返回的结果为笛卡尔积，检索出的行的数目是行数之积；

内部联结

目前为止用的所有联结称为等值联结（equijoin），基于两个表之间的相等测试，这种联结也称为内部联结

以INNER JOIN指定，在使用这种语法的时候要使用ON子句而不是WHERE，传递给ON的实际条件与传递给WHERE的相同

1
2
3
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

联结多个表

SQL对一条SELECT语句可以联结的表的数目没有限制，创建联结的基本规则也相同，首先列出所有的表然后定义关系

创建高级联结

使用表别名

别名除了用于列名和计算字段之外，SQL还允许给表名起别名，这样做有两个理由

  1. 缩短SQL语句
  2. 允许在单条SELECT语句中多次使用相同的表

例

1
2
3
4
5
SELECT cust.name, cust.contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'TNT2';

注意：表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户机

自联接

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然结果一样但是处理联结比处理子查询快的多

子查询 vs 联结

子查询 ：并不是执行复杂SELECT操作的最有效的方法

1
2
3
4
5
6
7
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                 FROM orders
                 WHERE order_num IN (SELECT order_num
                                    FROM orderitems
                                    WHERE prod_id = 'TNT2'));

联结

1
2
3
4
5
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
  AND orderitems.order_num = orders.order_num
  AND prod_id = 'TNT2';

自然联结

自然联结排除多次出现，使每个列只返回一次；只能选择那些唯一的列，一般是通过对表使用通配符，对所有其他表的列使用明确的子集来完成的

事实上，我们所建立的每个内部联结都是自然联结，且大概率用不到不是自然联结的内部联结

1
2
3
4
5
SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'FB';

在这个例子中，通配符只对第一个表使用，所有其他列明确列出，所以没有重复的列被检索出来

外部联结 OUTER JOIN

许多联结需要将一个表中的行与另一个表中的行相关联，但有时候也需要包含没有关联行的

定义：联结中包含了在相关表中没有关联行的行

在使用 OUTER JOIN语法的时候，必须使用 RIGHT或者LEFT 关键字指定包括其所有行的表

外部联结的类型

存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定

1
2
3
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

使用带聚集函数的联结

1
2
3
4
SELECT customers.cust_name, customers_id, COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

要点

  1. 注意所使用的联结类型，一般使用内部联结
  2. 保证使用正确的联结条件
  3. 总是提供联结条件
  4. 一个联结中可以包含多个表，甚至对于每个联结都可以采用不同的联结类型，虽然合法，且有用，但应该在测试他们之前分别测试每一个联结，方便排除故障

组合查询 UNION

MySQL允许执行多个查询（SELECT），并将结果作为单个查询结果集返回，这些组合查询通常称为并（union）和符合查询（compound query）

有两种情况需要使用组合查询

在单个查询中从不同的表返回类似结构的数据

对单个表执行多个查询按单个查询返回数据

组合查询和多个WHERE条件 多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出，在以下段落中可以看到这一点。这两种技术在不同的查询中性能也不同。因此，应该试一下这两种技术，以确定对特定的查询哪一种性能更好。

UNION 可以极大的简化复杂的WHERE子句、简化从多个表中检索数据的工作

创建

1
2
3
4
5
6
7
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5;
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);

对于更复杂的过滤条件，或者从多个表中检索数据的情形，使用UNION可能会使处理更加简单

规则

  1. 必须是两条及以上的SELECT语句
  2. 每个查询必须包含相同的列、表达式或聚集函数
  3. 列数据类型必须兼容，类型不必完全相同，但必须是DBMS可以隐式转换的类型

注意

UNION从查询结果中自动去重，如果需要返回所有匹配行，可使用UNION ALL,这个时候WHERE也取代不了它的工作了

组合查询可以应用于不同的表

全文本搜索

InnoDB引擎在MySQL5.7之前不支持全文本搜索

在使用全文本搜索的时候，MySQL不需要分别查看每个行，不需要分别分析和处理每个词，MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行，从而高效判断

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的更新不断地重新索引，在对表列进行适当设计后，MySQL会自动进行所有索引和重新索引

1
2
3
4
5
6
CREATE TABLE test (
note_id int NOT NULL
...
note_text text NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text)) ENGINE = MyISAM;

不要在导入数据时使用FULLTEXT 更新索引要花时间，虽然不是很多，但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。应该首先导入所有数据，然后再修改表，定义FULLTEXT。这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）

在索引之后，使用两个函数Match() 和 Against() 指定要使用的搜索表达式

1
2
3
SELECT note_text
FROM productionnotes
WHERE Match(note_text) Against('rabbit'); #WHERE note_text) LIKE %rabbit%;

使用完整的Match() 传递给match的值必须于fulltext()定义中相同，如果指定多个列，则必须依次列出

搜索除非使用BINARY否则不区分大小写

查询拓展

1
2
3
SELECT note_text
FROM productionnotes
WHERE Match(note_text) Against('rabbit' WITH QUERY EXPANSION);

布尔文本搜索

  1. 提供的细节：
  2. 要匹配的词；
  3. 要排斥的词；
  4. 排列提示（指定某些词更加重要）；排列但不降序
  5. 表达式分组
  6. 另外一些内容
1
2
3
SELECT note_text
FROM productionnotes
WHERE Match(note_text) Against('rabbit' IN BOOLEAN MODE);
1
2
3
SELECT note_text
FROM productionnotes
WHERE Match(note_text) Against('rabbit -rope*' WITH QUERY EXPANSION); #指示排除包含rope的

全文本布尔操作符|500

1
2
3
SELECT note_text
FROM productionnotes
WHERE Match(note_text) Against('rabbit rope' WITH QUERY EXPANSION); #两者有一个就行

总结

  1. 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）
  2. MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表
  3. 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE
  4. 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）
  5. 忽略词中的单引号 例如，don’t索引为dont
  6. 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果
  7. 如前所述，仅在MyISAM数据库引擎中支持全文本搜索
  8. 没有邻近操作符 邻近搜索是许多全文本搜索支持的一个特性，它能搜索相邻的词（在相同的句子中、相同的段落中或者在特定数目的词的部分中，等等）

视图

视图本质就是一个查询的结果，不过我们每次都可以通过打开视图来按照我们想要的样子查看数据。既然视图本质就是一个查询的结果，那么它本身就是一个虚表，并不是真实存在的，数据实际上还是存放在原来的表中

为什么使用视图

我们已经看到了视图应用的一个例子。下面是视图的一些常见应用

  1. 重用SQL语句。
  2. 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
  3. 使用表的组成部分而不是整个表。
  4. 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
  5. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

在视图创建之后，可以用与表基本相同的方式利用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至能添加和更新数据（添加和更新数据存在某些限制）

视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据

性能问题 因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

视图的规则和限制

  1. 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）
  2. 对于可以创建的视图数目没有限制
  3. 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予
  4. 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图
  5. ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖
  6. 视图不能索引，也不能有关联的触发器或默认值。
  7. 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

创建视图

1
2
CREATE VIEW 视图名称(列名) as 子查询语句 [WITH CHECK OPTION];
mysql> CREATE VIEW need as SELECT * FROM student WHERE sex = 'male';

WITH CHECK OPTION是指当创建后，如果更新视图中的数据，是否要满足子查询中的条件表达式，不满足将无法插入，创建后，我们就可以使用select语句来直接查询视图上的数据了，因此，还能在视图的基础上，导出其他的视图。

  1. 若视图是由两个以上基本表导出的，则此视图不允许更新。
  2. 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。
  3. 若视图的字段来自集函数，则此视图不允许更新。
  4. 若视图定义中含有GROUP BY子句，则此视图不允许更新。
  5. 若视图定义中含有DISTINCT短语，则此视图不允许更新。
  6. 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。例如将成绩在平均成绩之上的元组定义成一个视图GOOD_SC： CREATE VIEW GOOD_SC AS SELECT Sno, Cno, Grade FROM SC WHERE Grade > (SELECT AVG(Grade) FROM SC); 　　导出视图GOOD_SC的基本表是SC，内层查询中涉及的表也是SC，所以视图GOOD_SC是不允许更新的。
  7. 一个不允许更新的视图上定义的视图也不允许更新

image-20230506190656954|500

删除视图

1
drop view 名字;

存储过程

存储过程简单来说就是为以后使用而保存的一条或多条MySQL语句的集合。可将其视为批文件。虽然作用不仅限于批处理

为什么要使用

  1. 通过处理封装在容易使用的单元中，简化复杂的操作
  2. 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。这一点的延申就是防止错误，需要执行的步骤越多，出错的可能性就越大
  3. 简化对变动的管理。如果有所变动，只需要更改存储过程的代码，使用它的人员甚至都不需要知道；这一点的延申就是安全性，通过存储过程限制对基础数据的访问减少了数据讹传
  4. 提高性能。因为使用存储过程比使用单独的SQL语句要快。
  5. 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码（在下一章的例子中可以看到。）

换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能。显然，它们都很重要。不过，在将SQL代码转换为存储过程前，也必须知道它的一些缺陷

  1. 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
  2. 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。
    尽管有这些缺陷，存储过程还是非常有用的，并且应该尽可能地使用。

使用

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数

存储过程可以显示结果，也可以不显示结果

1
2
3
CALL productpricing(@pricelow, #执行名为productpricing的存储过程
				   @pricehigh,
				   @priceaverage);

创建 +删除

1
2
3
4
5
6
 #此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来
CREATE PROCEDURE productpricing()
BEGIN  #BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句
	SELECT Avg(prod_price) AS priceaverage 
	FROM products;
END;

MySQL处理这段代码时，它创建一个新的存储过程product-pricing。没有返回数据，因为这段代码并未调用存储过程；这只是为了以后使用创造他

执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后需要有()符号（即使不传递参数也需要）

1
CALL productpricing()；

这条语句删除刚创建的存储过程。请注意没有使用后面的()，只给出存储过程名

1
DROP PROCEDURE productpri

如果指定的过程不存在，则DROP PROCEDURE将产生一个错误。当过程存在想删除它时（如果过程不存在也不产生错误）可使用DROP PROCEDURE IF EXISTS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE PROCEDURE productprcing(
	OUT pl DECIMAL(8,2), #a指定指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38；
#b指定小数点右边可以存储的十进制数字的最大个数，小数位数必须是从0到a之间的值，默认小数位数是0
	OUT ph DECIMAL(8,2),
	OUT pa DECIMAL(8,2) #OUT（从存储过程传出）
)
#存储过程的代码位于BEGIN和END语句内
BEGIN
	SELECT Min(prod_price)
	INTO pl #保存到相应的变量（通过指定INTO关键字
	FROM products;
	SELECT Max(prod_price)
	INTO ph
	FROM products;
	SELECT Avg(prod_price)
	INTO p2
	FROM products;
END;

每个参数必须具有指定的类型，这里使用十进制值

MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入和传出）类型的参数

记录集不是允许的类型，因此，不能通过一个参数返回多个行和列。这就是前面的例子要使用3个参数（和3条SELECT语句）的原因

1
2
3
CALL productpricing(@pricelow, #所有MySQL变量都必须以@开始
				   @pricehigh,
				   @priceaverage);

建立智能存储过程

只有在存储过程内包含业务规则和智能处理时，它们的威力才真正显现出来

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE PROCEDURE ordertotal (
	IN onumber INT, 
    IN taxable BOOLEAN, 
    OUT ototal DECIMAL (8,2)
--它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示
) COMMENT 'Obtain order total, optionally adding tax'

BEGIN
--定义局部变量,DECLARE要求指定变量名和数据类型
DECLARE total DECIMAL (8,2); 
DECLARE taxrate INT DEFAULT 6;

SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order num = onumber
INTO total;
#IF语句还支持ELSEIF和ELSE子句（前者还使用THEN子句，后者不使用）
IF taxable THEN
	SELECT total+(total/100*taxrate) INTO total;
END IF;
	SELECT total INTO ototal;
END;

使用

1
2
SELECT ordertotal(2005,1,@total);
SELECT @total; #只显示这一行

检查存储过程

为了获得包括何时、由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS。
限制过程状态结果 SHOW PROCEDURE STATUS列出所有存储过程。为限制其输出，可使用LIKE指定一个过滤模式，例如：

显示用来创建一个存储过程的CREATE语句

1
SHOW CREATE PROCEDURE ordertotal;

获得包括何时、由谁创建等详细信息的存储过程列表

1
SHOW PROCEDURE STATUS ordertotal;

限制过程状态结果 SHOW PROCEDURE STATUS列出所有存储过程。为限制其输出，可使用LIKE指定一个过滤模式，例如：

1
SHOW PROCEDURE STATUS LIKE 'ordertotal';

游标

是一个存储在MySQL服务器上的数据库查询，不是一条SELECT语句，而是被该语句检索出来的结果集

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改

MySQL中的游标只能用于存储过程和函数

使用规则

  1. 必须声明，这个过程实际上没有检索数据，只是单纯的定义
  2. 一旦声明，必须打开游标以供使用，这个过程用前面定义的SELECT语句把数据实际检索出来
  3. 对于填有数据的游标，根据需要检索
  4. 结束使用时，必须关闭

创建

1
2
3
4
5
6
CREATE PROCEDURE processorders()
BEGINS #存储过程处理完，游标就消失了
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

打开关闭

1
2
OPEN ordernumbers;
CLOSE ordernumbers; #释放游标使用的所有内存和资源

隐式关闭：当END语句之后，会自动关闭

使用

在一个游标被打开之后，可以使用FETCH分别访问每一行

FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）

1
2
3
OPEN ordernumbers;
FETCH ordernumbers INTO o;
CLOSE ordernumbers;

循环检索数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE PROCEDURE processorders()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	
	DECLARE oredernumbers CURSOR
	FOR 
	SELECT order_num FROM orders;
	
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	
	OPEN ordernumbers;
	REPEAT
		FETCH ordernumbers INTO o;
	UNTIL done END REPEAT;
CLOSE ordernumbers;

关于MySQL 8使用的MySQL错误代码列表：MySQL :: MySQL 8.0 Reference Manual :: B Error Messages and Common Problems

DECLARE语句的次序 ：用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义

重复或循环:除这里使用的REPEAT语句外，MySQL还支持循环语句，它可用来重复执行代码，直到使用LEAVE语句手动退出为止。通常REPEAT语句的语法使它更适合于对游标进行循环。
为了把这些内容组织起来，下面给出我们的游标存储过程样例的更进一步修改的版本，这次对取出的数据进行某种实际的处理：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE PROCEDURE processorders()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	DECLARE t DECIMAL(8, 2);
	
	DECLARE oredernumbers CURSOR
	FOR 
	SELECT order_num FROM orders;
	
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	
	CREATE TABLE IF NOT EXISTS ordertotals (
    	order_num INT,
    	total DECIMAL(8, 2));
	
	OPEN ordernumbers;
	REPEAT
		FETCH ordernumbers INTO o;
		CALL ordertotal(o, 1, t); #执行另一个过程
		INSERT INTO ordertotals(order_num, total) VALUES(o, t)
	UNTIL done END REPEAT;
CLOSE ordernumbers;

索引

当数据量变得非常庞大的时候，一个索引能很好的帮助；能够快速定位元素的位置

不能过度使用，会占用资源

单列索引

只针对某一列数据创建索引

类型

  1. NORMAL：普通的索引类型，完完全全相当于一本书的目录
  2. UNIQUE：唯一索引，一旦建立唯一索引，那么整个列中将不允许出现重复数据。每个表的主键列，都有一个特殊的唯一索引，叫做Primary Key，它不仅仅要求不允许出现重复，还要求不能为NULL，它还可以自动递增。每张表可以有多个唯一索引，但是只能有一个Primary索引
  3. SPATIAL：空间索引，空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON
  4. FULLTEXT：全文索引（MySQL 5.6 之后InnoDB才支持），它是模糊匹配的一种更好的解决方案，它的效率要比使用like %更高，并且它还支持多种匹配方式，灵活性也更加强大。只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
1
2
3
4
5
6
7
-- 创建索引
CREATE INDEX 索引名称 ON 表名 (列名)
-- 查看表中的索引
show INDEX FROM student

mysql> CREATE INDEX i ON student(name);
mysql> SHOW INDEX FROM student;

image-20230506192101993|500

删除索引

1
DROP INDEX 索引名称 FROM 表名;

组合索引

组合索引实际上就是将多行捆绑在一起，作为一个索引，它同样支持以上几种索引类型

注意组合索引在进行匹配时，遵循最左原则

可以使用explain语句（它可以用于分析select语句的执行计划，也就是MySQL到底是如何在执行某条select语句的）来分析查询语句到底有没有通过索引进行匹配

1
explain select * from student where name = '小王';

索引底层原理

索引是存储在硬盘上的，跟我们之前使用的HashMap之类的不同，它们都是在内存中的，但是硬盘的读取速度远小于内存的速度

每一次IO操作都会耗费大量的时间，我们也不可能把整个磁盘上的索引全部导入内存，因此我们需要考虑尽可能多的减少IO次数

索引的实现可以依靠两种数据结构，一种是我们在JavaSE阶段已经学习过的Hash表，还有一种就是B-Tree

image-20230511220021842|500

通过对Key进行散列值计算，我们可以直接得到对应数据的存放位置，它的查询效率能够达到O(1)，但是它也存在一定的缺陷：

  • Hash索引仅仅能满足“=”，“in”查询条件，不能使用范围查询。
  • Hash碰撞问题。
  • 不能用部分索引键来搜索，因为组合索引在计算哈希值的时候是一起计算的。

那么，既然要解决这些问题，我们还有一种方案就是使用类似于二叉树那样的数据结构来存储索引，但是这样相比使用Hash索引，会牺牲一定的读取速度。

但是这里并没有使用二叉树，而是使用了BTree，它是专门为磁盘数据读取设计的一种度为n的查找树：

  • 树中每个结点最多含有m个孩子（m >= 2）

  • 除根结点和叶子结点外，其它每个结点至少有[ceil(m / 2)]个孩子。

  • 若根结点不是叶子结点，则至少有2个孩子。

  • 所有叶子结点都出现在同一层。

  • 每个非终端结点中包含有n个键值信息： (P1，K1，P2，K2，P3，……，Kn，Pn+1)。其中：

    1. Ki (i=1…n)为键值，且键值按顺序升序排序K(i-1)< Ki。
    2. Pi为指向子树根的结点，且指针P(i)指向的子树中所有结点的键值均小于Ki，但都大于K(i-1)。
    3. 键值的个数n必须满足： [ceil(m / 2)-1] <= n <= m-1。

img|500

比如现在要对键值为10的记录进行查找，过程如下：

  1. 读取根节点数据（目前进行了一次IO操作）
  2. 根据根节点数据进行判断得到10<17，因为P1指向的子树中所有值都是小于17的，所以这时我们将P1指向的节点读取（目前进行了两次IO操作）
  3. 再次进行判断，得到8<10<12，因为P2指向的子树中所有的值都是小于12大于8的，所以这时读取P2指向的节点（目前进行了三次IO操作）
  4. 成功找到。

接着来看，虽然BTree能够很好地利用二叉查找树的思想大幅度减少查找次数，但是它的查找效率还是很低，因此它的优化版本B+Tree诞生了，它拥有更稳定的查询效率和更低的IO读取次数：

img|500

可以发现，它和BTree有一定的区别：

  • 有n棵子树的结点中含有n个键值，BTree只有n-1个。
  • 所有的键值信息只在叶子节点中包含，非叶子节点仅仅保存子节点的最小（或最大）值，和指向叶子节点的指针，这样相比BTree每一个节点在硬盘中存放了更少的内容（没有键值信息了）
  • 所有叶子节点都有一个根据大小顺序指向下一个叶子节点的指针Q，本质上数据就是一个链表。

这样，读取IO的时间相比BTree就减少了很多，并且查询任何键值信息都需要完整地走到叶子节点，保证了查询的IO读取次数一致。因此MySQL默认选择B+Tree作为索引的存储数据结构。

这是MyISAM存储引擎下的B+Tree实现：

img

这是InnoDB存储引擎下的B+Tree实现：

img|500

img|500

InnoDB与MyISAM实现的不同之处：

  • 数据本身就是索引的一部分（所以这里建议主键使用自增）
  • 非主键索引的数据实际上存储的是对应记录的主键值（因此InnoDB必须有主键，若没有也会自动查找替代）

触发器

触发器就像其名字一样，在某种条件下会自动触发，在select/update/delete时，会自动执行预先设定的内容，触发器通常用于检查内容的安全性，相比直接添加约束，触发器显得更加灵活。

触发器所依附的表称为基本表，当触发器表上发生select/update/delete等操作时，会自动生成两个临时的表（new表和old表，只能由触发器使用）

比如在insert操作时，新的内容会被插入到new表中；在delete操作时，旧的内容会被移到old表中，我们仍可在old表中拿到被删除的数据；在update操作时，旧的内容会被移到old表中，新的内容会出现在new表中

1
2
CREATE TRIGGER 触发器名称 [BEFORE / AFTER] [INSERT/UPDATE/DELETE] ON 表名/视图名 FOR EACH ROW DELETE FROM student WHERE student.sno = new.sno;
mysql> CREATE TRIGGER t BEFORE DELETE ON student FOR EACH ROW DELETE FROM teach WHERE old.sid = teach.sid;

查看触发器

1
SHOW TRIGGERS;

删除触发器

1
DROP TRIGGER 触发器名称;

注：要保持每个数据库的触发器名称唯一；只有表才支持触发器

INSERT触发器

  1. 可引用一个名为NEW的虚拟表，访问被插入的行
  2. 在BEFORE INSERT触发器当中，NEW的值可以被更新，允许更改被插入的值
  3. 对于AUTO INCREMENT列，NEW 在 INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值

DELETE触发器

在DELETE触发器内部，可以访问一个名为OLD的虚拟表，访问被删除的行；

OLD中的值都只可读，不能更新

UPDATE 触发器

  1. 可以引用一个名为OLD的虚拟表访问UPDATE语句前的值，引用一个名为NEW的虚拟表访问新更新的值
  2. 在BEFORE UPDATE 触发器中，NEW的值可能也被更新（允许更改将要用于UPDATE语句中的值）
  3. OLD中的值都只是可读，不能更新

进一步介绍

  1. 创建触发器可能需要特殊的安全访问权限，但是触发器的执行是自动的，如果语句能执行，则相关触发器也可执行
  2. 应该用触发器来保证数据的一致性；优点是他总是进行这种处理，而且是透明的进行，与客户机应用无关
  3. 非常有意义的使用是创建审计跟踪，使用触发器把更改记录到另一个表非常容易
  4. 不能从触发器调用存储过程，所需存储过程代码需要复制到触发器内（MySQL触发器中不支持CALL语句）

事务

当我们要进行的删除非常多的时候，需要执行大量的SQL语句，这些数据库操作语句就会构成一个事务

只有InnoDB才支持事务

查看引擎

1
SHOW ENGINES;

image-20230506204655495|500

特性

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

使用

通过以下例子来探究事务

1
2
3
4
5
6
7
8
9
begin;   #开始事务
...
rollback;  #回滚事务
savepoint 回滚点;  #添加回滚点
rollback to 回滚点; #回滚到指定回滚点，不能回退CREATE;DROP;SELECT语句
...
commit; #提交事务
-- 一旦提交，就无法再进行回滚了！
SET autocommit = 0; #指示MySQL不自动提交更改，不管有没有COMMIT语句；autocommit标志是针对每个连接而不是服务器

image-20230506205353162|500

注意：此处虽然已经体现添加内容，但实际上在数据库中还是没有添加的，在commit之后，才是真正的添加

全球化和本地化

基础

不同的语言和字符集需要以不同的方式存储和检索，因此MySQL需要适应不同的字符集、排序和检索的方式

字符集：字母和符号的集合

编码：某个字符集成员的内部表示

校对：规定字符如何比较的指令

在MySQL的正常数据库活动中不需要太担心，使用何种字符集和校对的决定在服务器、数据库和表级进行

使用

查找所支持的字符集完整列表；显示可用字符集、描述和默认校对

1
SHOW CHARACTER SET;

查看所支持的校对完整列表；通常出现两次：区分大小写(_cs)、不区分 ( _ci )

1
SHOW COLLATION

指定：不指定就默认

1
2
3
4
5
CREATE TABLE mytable (
	columnn1 INT,
	columnn2 VARCHAR(10)
)DEFAULT CHARACTER SET hebrew
 COLLATE hebrew_general_ci;

还允许给每一个列设置

1
2
3
4
5
CREATE TABLE mytable (
	columnn1 INT,
	columnn2 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci;
)DEFAULT CHARACTER SET hebrew
 COLLATE hebrew_general_ci;

如果绝对需要，串可以在字符集之间转换，用Cast()和Convert()函数

数据库维护

由于MySQL数据库是基于磁盘的文件，普通的备份系统和例程就能备份MySQL的数据。但是，由于这些文件总是处于打开和使用状态，普通的文件副本备份不一定总是有效

解决方案

  1. 转储所有数据库内容到某个外部文件：mysqldump在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件
  2. 从一个数据库复制所有数据：mysqlhotcopy（并非所有数据库引擎都支持这个实用程序）
  3. 转储所有数据到某个外部文件：BACKUP TABLE或SELECT INTO OUTFILE。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。
  4. 复原数据：RESTORE TABLE
  5. 首先刷新未写数据 为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用FLUSH TABLES语句

进行维护

​ ANALYZE TABLE 用来检查表键是否正确

1
2
3
4
5
6
7
mysql> ANALYZE TABLE student;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| need.student | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.06 sec)

CHECK TABLE 用来针对许多问题对表进行检查。在MyISAM表还对索引进行检查

CHANGED：检查自最后一次检查以来改动过的表

EXTENDED：执行最彻底的检查

FAST：只检查未正常关闭的表

MEDIUM：检查所有被删除的链接并进行键检验

QUICK：只进行快速扫描

如果MyISAM表访问产生不正确和不一致的结果，使用REPAIR TABLE

从表中删除大量数据，则需要使用OPTIMIZE TABLE来收回所用的空间，从而优化性能

诊断启动问题

服务器启动问题通常在对MySQL配置或服务器本身进行更改时出现

MySQL在这个问题发生时报告错误，但由于多数MySQL服务器是作为系统进程或服务自动启动的，这些消息可能看不到

在排除系统启动问题时，首先应该尽量用手动启动服务器；MySQL服务器自身通过在命令行上执行mysqld启动

下面是几个重要的mysqld命令行选项：

  1. –help显示帮助——一个选项列表；
  2. –safe-mode装载减去某些最佳配置的服务器；
  3. –verbose显示全文本消息（为获得更详细的帮助消息与–help联合使用）；
  4. –version显示版本信息然后退出

查看日志文件

MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下几种：

  1. 错误日志：它包含启动和关闭问题以及任意关键错误的细节。通常名为hostname.err，位于data目录。日志名可用--log-error命令行选项更改
  2. 查询日志：它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。通常名为hostname.log，位于data目录。此名字用--log命令行选项更改
  3. 二进制日志：它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为hostname-bin，位于data目录内。此名字可以用--log-bin命令行选项更改。(这个日志文件是MySQL 5中添加的，以前的MySQL版本中使用的是更新日志)
  4. 缓慢查询日志 ：顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log，位于data目录中。此名字可以用--log-slow-queries命令行选项更改

在使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文件

存储引擎

存储引擎就像我们电脑中的CPU，它是整个MySQL最核心的部分，数据库中的数据如何存储，数据库能够支持哪些功能，我们的增删改查请求如何执行，都是由存储引擎来决定的。

大致了解一下以下三种存储引擎：

  • MyISAM：MySQL5.5之前的默认存储引擎，在插入和查询的情况下性能很高，但是它不支持事务，只能添加表级锁。
  • InnoDB：MySQL5.5之后的默认存储引擎，它支持ACID事务、行级锁、外键，但是性能比不过MyISAM，更加消耗资源。
  • Memory：数据都存放在内存中，数据库重启或发生崩溃，表中的数据都将消失。

我们可以使用下面的命令来查看MySQL支持的存储引擎：

1
show engines;

在创建表时，我们也可以为表指定其存储引擎。

我们还可以在配置文件中修改默认的存储引擎，在Windows 11系统下，MySQL的配置文件默认放在C:\ProgramData\MySQL\MySQL Server 8.0中，ProgramData是个隐藏文件夹

锁机制

当对某个方法或是某个代码块加锁后，除非锁的持有者释放当前的锁，否则其他线程无法进入此方法或是代码块，我们可以利用锁机制来保证多线程之间的安全性

在MySQL中很容易出现多线程同时操作表中数据的情况；

为了避免潜在的并发问题，可以使用之前讲解的事务隔离级别来处理，而事务隔离中利用了锁机制

  • 读未提交(Read Uncommitted)：能够读取到其他事务中未提交的内容，存在脏读问题
  • 读已提交(Read Committed RC)：只能读取其他事务已经提交的内容，存在不可重复读问题
  • 可重复读(Repeated Read RR)：在读取某行后不允许其他事务操作此行，直到事务结束，但是依然存在幻读问题
  • 串行读(Serializable)：一个事务的开始必须等待另一个事务的完成

我们可以切换隔离级别分别演示一下：

1
set session transaction isolation level read uncommitted;

在RR级别下，MySQL在一定程度上解决了幻读问题：

  • 在快照读（不加锁）读情况下，mysql通过mvcc来避免幻读
  • 在当前读（加锁）读情况下，mysql通过next-key来避免幻读

**MVCC**，全称 Multi-Version Concurrency Control ：即多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存

读锁和写锁

从对数据的操作类型上来说，锁分为读锁和写锁：

  • 读锁：也叫共享锁，当一个事务添加了读锁后，其他的事务也可以添加读锁或是读取数据，但是不能进行写操作，只能等到所有的读锁全部释放
  • 写锁：也叫排他锁，当一个事务添加了写锁后，其他事务不能读不能写也不能添加任何锁，只能等待当前事务释放锁

全局锁、表锁和行锁

从锁的作用范围上划分，分为全局锁、表锁和行锁：

  • 全局锁：锁作用于全局，整个数据库的所有操作全部受到锁限制。
  • 表锁：锁作用于整个表，所有对表的操作都会收到锁限制。
  • 行锁：锁作用于表中的某一行，只会通过锁限制对某一行的操作（仅InnoDB支持）

全局锁

我们首先来看全局锁，它作用于整个数据库，我们可以使用以下命令来开启读全局锁：

1
flush tables with read lock;

开启后，整个数据库被上读锁，我们只能去读取数据，但是不允许进行写操作（包括更新、插入、删除等）一旦执行写操作，会被阻塞，直到锁被释放，我们可以使用以下命令来解锁：

1
unlock tables;

除了手动释放锁之外，当我们的会话结束后，锁也会被自动释放。

表锁

表锁作用于某一张表，也是MyISAM和InnoDB存储引擎支持的方式，我们可以使用以下命令来为表添加锁：

1
lock table 表名称 read/write;

其他地方是无法访问此表的，一律都被阻塞

行锁

表锁的作用范围太广了，如果我们仅仅只是对某一行进行操作，那么大可不必对整个表进行加锁，因此InnoDB支持了行锁，我们可以使用以下命令来对某一行进行加锁：

1
2
3
4
-- 添加读锁（共享锁）
select * from ... lock in share mode;
-- 添加写锁（排他锁）
select * from ... for update;

InnoDB：在执行更新、删除、插入操作时，数据库也会自动为所涉及的行添加写锁（排他锁），直到事务提交时，才会释放锁，执行普通的查询操作时，不会添加任何锁

MyISAM：在执行更新、删除、插入操作时，数据库会对涉及的表添加写锁，在执行查询操作时，数据库会对涉及的表添加读锁

记录锁、间隙锁和临键锁

我们知道InnoDB支持使用行锁，但是行锁比较复杂，它可以继续分为多个类型

记录锁（Record Locks）

记录锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁

Record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加写锁，这个类似于表锁，但原理上和表锁应该是完全不同的

间隙锁（Gap Locks）

仅仅锁住一个索引区间（开区间，不包括双端端点）。在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。比如在 1、2中，间隙锁的可能值有 (-∞, 1)，(1, 2)，(2, +∞)

间隙锁可用于防止幻读，保证索引间的不会被插入数据

临键锁（Next-Key Locks）

Record lock + Gap lock，左开右闭区间。默认情况下，InnoDB正是使用Next-key Locks来锁定记录（如select … for update语句）它还会根据场景进行灵活变换：

场景 转换
使用唯一索引进行精确匹配，但表中不存在记录 自动转换为 Gap Locks
使用唯一索引进行精确匹配，且表中存在记录 自动转换为 Record Locks
使用非唯一索引进行精确匹配 不转换
使用唯一索引进行范围匹配 不转换，但是只锁上界，不锁下界
2019-2025 Sean Yam