Mysql基础操作到进阶

Daphar

已于 2022-12-27 17:25:31 修改

阅读量248

点赞数

分类专栏：数据分析文章标签： mysql 数据库

于 2021-09-06 22:21:00 首次发布

本文链接：https://blog.csdn.net/qd_ltf/article/details/120146385

版权

数据分析同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

数据库

1 篇文章 0 订阅

订阅专栏

一、数据库、表基本操作

1、数据库操作

1.1、显示数据库

SHOW DATABASES;

1.2、创建数据库

CREATE DATABASE create_test CHARSET = 'utf8';   #  CHARSET='编码格式'

1.3、使用数据库

USE create_test;

1.4、查看当前数据库

SELECT DATABASE();  # 使用 `SELECT DATABASE()` 查看当前使用的数据库。

1.5、删除数据库

DROP DATABASE create_test;

2、数据表操作

2.1、创建表

语法格式：

CREATE TABLE [IF NOT EXISTS] `表名`
(
`字段名` 列类型 [属性] [索引] [注释], … , `字段名` 列类型 [属性] [索引] [注释]
)
[表类型] [字符集设置] [注释]）

使用下面的语句创建示例中的 one_piece 表。

CREATE TABLE IF NOT EXISTS one_piece
    (
        id CHAR(10) NOT NULL COMMENT '海贼团id',
        pirates CHAR(10) NOT NULL COMMENT '海贼团名称',
        name CHAR(10) NOT NULL COMMENT '海贼名',
        age INT(11) NOT NULL COMMENT '海贼年龄',
        post VARCHAR(10) NULL COMMENT '海贼团职位'
    );

注意：创建表时，指定的表名必须不存在，否则会出错。可以加下，if not exists

2.2、更新表

2.2.1 添加列

在刚才创建的 one_piece 表中添加一列 bounty (赏金)。

ALTER TABLE one_piece ADD bounty INT(15);

2.2.2 删除列

删除 bounty 列。

ALTER TABLE one_piece DROP COLUMN bounty;

2.3、查看表结构

DESC one_piece;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | char(10)    | NO   |     | NULL    |       |
| pirates | char(10)    | NO   |     | NULL    |       |
| name    | char(10)    | NO   |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| post    | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

2.4、查看表详细信息

SHOW TABLE STATUS LIKE 'one_piece'

           Name: one_piece
         Engine: InnoDB
        Version: 10
     Row\_format: Dynamic
           Rows: 0
 Avg\_row\_length: 0
    Data\_length: 16384
Max\_data\_length: 0
   Index\_length: 0
      Data\_free: 0
 Auto\_increment: NULL
    Create\_time: 2021-09-03 17:53:58
    Update\_time: NULL
     Check\_time: NULL
      Collation: utf8mb4\_0900\_ai\_ci
       Checksum: NULL
 Create\_options:
        Comment:
1 row in set (0.39 sec)

2.5、重命名表

两种方法：

ALTER TABLE 表名 RENAME [TO | AS] 新表名;
RENAME TABLE 表名 TO 新表名;

-- 方法一
ALTER TABLE one_piece RENAME TO new_one_piece;
-- 方法二
RENAME TABLE new_one_piece TO one_piece;

2.6、删除表

DROP TABLE 表名

DROP TABLE one_piece;

注意：在该表与其他表有关联时，Mysql 会阻止该表的删除。

二、数据表增、删、改、查

查询数据

1、查询多列

同时输出name, age 列。

SELECT name, age FROM one_piece;

2、检索唯一值

使用 DISTINCT 关键字，查询字段 age 的唯一值。

SELECT DISTINCT age  FROM one_piece;

3、限制输出

在 Mysql 中使用 LIMIT 关键字限制输出的数据。 LIMIT 有两种常见用法：

SELECT * FROM table  LIMIT [offset], rows            -- LIMIT 单独使用 
SELECT * FROM table  LIMIT rows OFFSET [offset]      -- 配合 OFFSET 使用    
# offset：行开始的行的索引。0 表示从第 1 行 开始显示（包括第 1 行），以此类推。
# rows：数据显示的条数。

# 示例：
SELECT * FROM one_piece LIMIT 5;    -- 检索前5条数据
--相当于
SELECT * from one_piece LIMIT 0,5;    -- 从第0行开始检索5条数据
--相当于
SELECT * FROM one_piece LIMIT 5 OFFSET 0;    -- 从第0行开始检索5条数据，注意这里的LIMIT的5指代的是数量

注：如果表中数据不足，即 LIMIT 设定的数过大，则只会检索到最后一行。

4、注释

三种注释方式

– 单行注释
# 单行注释
/* 多行注释 */

5、ORDER BY 排序

5.1 单列排序

使用 ORDER BY 子句。 ORDER BY 子句取一个或多个列的名字，据此对输出进行排序（默认升序）。

SELECT name, age
    FROM one_piece
    ORDER BY age;

注意：在指定一条 ORDER BY 子句时，应该保证它是 SELECT 语句中最后一条子句。

5.2 多列排序

SELECT A, B
    FROM test
    ORDER BY A, B;

在按多列排序时，仅在多个行具有相同的 A 值时才按 B 进行排序。如果 A 列中所有的值都是 唯一的，则不会按 B 排序。

5.3 指定排序方向

ORDER 默认升序（从 A 到 Z）排序。指定 DESC 关键字进行降序（从 Z 到 A）排序。

SELECT age
    FROM one_piece
    ORDER BY age DESC;

多列指定排序方向时，要使用逗号分隔。

SELECT name, age
    FROM one_piece
    ORDER BY name DESC, age;

6、查询过滤数据

6.1 WHERE 子句操作符

操作符	说明	操作符	说明
=	等于	>	大于
<>、!=	不等于	>=	大于等于
<	小于	!>	不大于
<=	小于等于	BETWEEN	在两值之间 (包含边界)
!<	不小于	IS NULL	是 NULL 值

6.2 WHERE 范围值检查

使用 WHERE 关键字和 BETWEEN AND 进行范围值检查**（前闭后闭）**。

#  查询字段 age 中 `>=5` 并且 `<= 10` 的数据  （前闭后闭）
SELECT age
    FROM one_piece
    WHERE A BETWEEN 5 AND 10;

6.3 WHERE 空值检查

使用 WHERE 关键字和 IS NULL 进行范围值检查。如果没有 NULL 值就不返回数据。

SELECT name
    FROM one_piece
    WHERE name IS NULL;

6.4 WHERE 组合过滤

使用 AND、OR 操作符给 WHERE 子句添加附加条件，要注意各个优先级的影响：
AND 的优先级比 OR 要高，优先级高低 () > AND > OR 。在使用的过程中要注意各个优先级的影响

SELECT name, age
    FROM one_piece
    WHERE(name = '索隆' OR name = '路飞')
    AND age >= 18;

6.5 IN 操作符

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。(与 OR 的功能相同，但OR的速度比 IN 慢)

SELECT name, age
    FROM one_piece
    WHERE name IN ('索隆', '路飞')

6.6 NOT 操作符

WHERE 子句中的 NOT 操作符有且只有一个功能，那就是否定其后所跟的任何条件。

SELECT name
    FROM one_piece
    WHERE name NOT IN ('索隆', '路飞')

6.7 通配符过滤

通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。
在使用通配符过滤之前要先了解 LIKE ， LIKE 操作符用于在 WHERE` 子句中搜索列中的指定模式或取值。

6.7.1 % 通配符
% 表示任何字符出现任意次数。例如，为了找出所有以 路 开始的 name。

SELECT name, age
    FROM one_piece
    WHERE name LIKE '路%';  # 路AA、路AABBB，等都能被匹配

6.7.2 _ 通配符

通配符 _ 的用途与 % 一样也是匹配任意字符，但它只匹配单个字符，而不是多个字符。

SELECT name, age
    FROM one_piece
    WHERE name LIKE '乌_兰';  # 乌克兰、乌A兰，等都能被匹配

7、创建计算字段

7.1 拼接字段

下面将 name ， sex 两列进行合并。并通过 AS 关键字进行给新列赋予别名。

SELECT Concat(name, '(', sex, ')') AS new_column
    FROM one_piece;

7.2 执行算数计算

通过 quantity （数量）、 price （价格）来计算 total_price （总价）

SELECT quantity, price,
    quantity * price AS total_price
    FROM test;

8、子查询

1、利用子查询进行过滤

现在查询草帽海贼团的排名信息。

SELECT rank
    FROM rank\_info
    WHERE id IN (SELECT id FROM one_piece WHERE pirates = '草帽海贼团');

注意：

在 SELECT 语句中，子查询总是从内向外处理。
作为子查询的 SELECT 语句只能查询单个列。检索多个列会报错。

2、作为计算字段使用子查询

查询海贼团排名和任务信息，首先从 one_piece 表中根据 id 检索出排名信息，再统计每个冒险团的人数。

SELECT rank,
    ( SELECT COUNT(*) FROM one_piece AS oe WHERE oe.id = ro.id ) AS num_person
    FROM rank_info AS ro
    ORDER BY rank;

注意：上面的例子中使用的是 oe.id 和 ro.id ，而不是直接使用 id ，因为在两个表中都有 id 列，在有可能混淆列名时必须使用这种语法。

增加数据

1、插入完整行

使用 INSERT 插入完整行它要求指定表名和插入到新行中的值。

INSERT INTO one_piece
    VALUES('1', '草帽海贼团','路飞','age','团长','1500000000');

注意：

必须每列提供一个值，空值使用 NULL
各列必须以它们在表定义中出现的次序填充

2、插入部分行

INSERT 推荐的插入方法是明确给出表的列名。这样还可以省略列，即只给某些列提供值，给其他列不提供值。

省略的列必须满足以下某个条件：

该列定义为允许 NULL 值（无值或空值）。
在表定义中给出默认值（如果不给出值，将使用默认值）。

如果表中不允许有 NULL 值或者默认值，这时却省略了表中的值， DBMS 就会产生错误消息，相应的行不能成功插入。

现在同样在 one_piece 表中插入一行。

INSERT INTO one_piece(id, pirates, name)  VALUES('1', '草帽海贼团', '路飞');

不管使用哪种 INSERT 语法，VALUES 的数目都必须正确。如果不提供列名，则必须给每个表列提供一个值；如果提供列名，则必须给列出的每个列一个值。否则，就会产生一条错误消息，相应的行不能成功插入。

3、从一个表复制到另一个表

有一种数据插入不使用 INSERT 语句。要将一个表的内容复制到一个全新的表（运行中创建的表）。

CREATE TABLE one_pieceCopy AS
    SELECT * FROM one_piece;

任何 SELECT 选项和子句都可以使用，包括 WHERE 和 GROUP BY。
可利用联结从多个表插入数据。
不管从多少个表中检索数据，数据都只能插入到一个表中。

主要用途：它是试验新 SQL 语句前进行表复制的很好工具。先进行复制，可在复制的数据上测试 SQL 代码，而不会影响实际的数据。

修改数据

使用 UPDATE 语句，更新（修改）表中的数据。有两种使用 UPDATE 的方式：

更新表中的特定行
更新表中的所有行

使用时要明确是 更新特定行 还是 更新所有行。

UPDATE 语句中可以使用子查询，使得能用 SELECT 语句检索出的数据更新列数据。

1、更新单行单列

将 路飞 的赏金更新为 10000000000

UPDATE one_piece
    SET bounty = 10000000000
    WHERE name = '路飞';

2、更新单行多列

在更新多个列时，只需要使用一条 SET 命令，每个 “列 = 值” 对之间用逗号分隔（最后一列之后不用逗号）。

UPDATE one_piece
    SET bounty = 10000000000, age = '19'
    WHERE name = '路飞';

3、更新所有行

不使用 WHERE 限制条件，即更新表中所有行。

UPDATE one_piece
    SET bounty = 10000000000, age = '19'

4、删除列中的值

假如表定义允许 NULL 值，要删除某个列的值，可设置它为 NULL。（要注意删除列值（保留列结构）和删除列（完全删除）的区别）

UPDATE one_piece
    SET bounty = NULL
    WHERE name = '路飞';

删除数据

使用 DELETE 语句，删除表中的数据。有两种使用 DELETE 的方式：

删除表中的特定行
删除表中的所有行

使用时要明确是 删除特定行 还是 删除所有行。

1、删除单行

删除 one_piece 表中 name 为 路飞 的行。

DELETE FROM one_piece
    WHERE name = '路飞';

2、删除所有行

删除 Customers 中的所有行。不删除表本身。

DELETE FROM one_piece;

如果想从表中删除所有行，推荐使用 TRUNCATE TABLE 语句，它完成相同的工作，而速度更快（因为不记录数据的变动）。

但要注意： TRUNCATE 属于数据定义语言（ DDL ），且 TRUNCATE 命令执行后无法回滚，使用 TRUNCATE 命令之前最好对当前表中的数据做备份。

TRUNCATE TABLE one_piece;

三、表联结

1、自联结

假如现在有人不知道乔巴所属的海贼团, 想要知道乔巴所属海贼团的所有成员名称与赏金。
先看一下子查询的方式：

SELECT name, bounty
    FROM one_piece
    WHERE pirates = (SELECT pirates
                       FROM one_piece
                       WHERE name = '乔巴');

接下来使用自联结的方式：

SELECT c1.name, c1.bounty
    FROM Customers AS c1, Customers AS c2
    WHERE c1.pirates = c2.pirates
     AND c2.name = '乔巴';

通常情况下，自联结的方式比子查询的方式要快很多。

2、等值联结

联结是一种机制，用来在一条 SELECT 语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。联结不是物理实体。换句话说，它在实际的数据库表中并不存在。它只在查询执行期间存在。

两表 table1， table2 中数据如下：

table1                      table2
+------+------+------+      +------+------+------+
| A    | B    | C    |      | C    | D    | E    |
+------+------+------+      +------+------+------+
|    1 |    2 |    3 |      |    2 |    3 |    4 |
|    4 |    5 |    6 |      |    6 |    7 |    8 |
+------+------+------+      +------+------+------+

现在通过表联结，获取两个表中的数据。

SELECT *
    FROM table1 AS t1, table2 AS t2
    WHERE t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
+------+------+------+------+------+------+

注意：上例中WHERE 中限制了联结条件，如果没有条件的话，返回的结果就是两表的笛卡尔积，返回 2 × 4 共 4 条数据

3、内联结

上面的联结准确来说是等值联结，也可以称为内联结，它还有另一种语法。返回的结果以上面相同。

SELECT \*
    FROM table1 AS t1 INNER JOIN table2 AS t2
      ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
+------+------+------+------+------+------+

4、自然联结

自然连接是一种特殊的等值连接，它在两个关系表中自动比较相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。

SELECT *
    FROM table1 AS t1 NATURAL JOIN table2 t2;
+------+------+------+------+------+
| C    | A    | B    | D    | E    |
+------+------+------+------+------+
|    6 |    4 |    5 |    7 |    8 |
+------+------+------+------+------+

5、外联结

5.1 左外联结

左外联结，左表 ( table1 ) 的记录将会全部表示出来，而右表 ( table2 ) 只会显示符合搜索条件的记录。右表记录不足的地方均为 NULL 。

SELECT *
    FROM table1 AS t1 LEFT JOIN table2 AS t2
      ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
|    1 |    2 |    3 | NULL | NULL | NULL |
+------+------+------+------+------+------+

5.2 右外联结

右外联结，右表 ( table2 ) 的记录将会全部表示出来，而右左表 ( table1 ) 只会显示符合搜索条件的记录。左表记录不足的地方均为 NULL 。

SELECT *
    FROM table1 AS t1 RIGHT JOIN table2 AS t2
     ON t1.C = t2.C;
+------+------+------+------+------+------+
| A    | B    | C    | C    | D    | E    |
+------+------+------+------+------+------+
|    4 |    5 |    6 |    6 |    7 |    8 |
| NULL | NULL | NULL |    2 |    3 |    4 |
+------+------+------+------+------+------+

四、内置函数

1、常用文本处理函数

函数	说明
LEFT(str, length)	返回指定长度的字符串的左边部分
RIGHT(str, length)	返回指定长度的字符串右边部分
LTRIM(str)	去掉字符串左边的空格
RTRIM(str)	去掉字符串右边的空格
LOWER(str)	将字符串转换为小写
UPPER(str)	将字符串转换为大写
LENGTH(str)	返回字符串的长度

2、日期和时间处理函数

https://blog.csdn.net/qq_41684621/article/details/123142022

3、数值处理函数

函数	说明
ABS()	返回一个数的绝对值
COS()	返回一个角度的余弦
SIN()	返回一个角度的正弦
TAN()	返回一个角度的正切
PI()	返回圆周率
EXP()	返回一个数的指数值
SQRT()	返回一个数的平方根

4、数据聚集

4.1 聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

4. 2 数据分组

4.2.1 数据分组

使用分组将数据分为多个逻辑组，对每个组进行聚集计算。
例：统计各个海贼团 ( pirates ) 的人数。

SELECT pirates, COUNT(*) AS num_person
    FROM one_piece
    GROUP BY pirates;

group by 注意事项:

GROUP BY 可以嵌套使用。
GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名。
除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。
如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

4.2.2 过滤分组

使用 HAVING 子句在数据分组后进行过滤。

例：查询海贼团人数在 500 人以上的海贼团名称及人数。

SELECT pirates, COUNT(*) AS num_person
    FROM one_piece
    GROUP BY pirates
    HAVING COUNT(*) >= 500;

WHERE 与 HAVING 的主要区别：

WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。

SELECT 子句顺序：

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否

五、存储过程及自定义函数

使用存储过程的优点：简单、安全、高性能

通过把 “某个处理” 封装在一个易用的单元中，可以简化复杂的操作
由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。
简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
因为存储过程通常以编译过的形式存储，所以 DBMS 处理命令所需的工作量少，提高了性能。

1、创建存储过程

我们来看一个简单的存储过程例子，对草帽海贼团人数进行计数。

DELIMITER $$
CREATE PROCEDURE personCount()
    BEGIN
        SELECT COUNT(*) AS num_person
        FROM one_piece
        WHERE pirates = '草帽海贼团';
    END$$

在定义过程时，使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为 $$，使得过程体中使用的分号被直接传递到服务器，而不会被 Mysql 解释。

2、调用存储过程

使用 CALL 存储过程名(参数) 来调用存储过程。

DELIMITER ;
CALL personCount();

3、存储过程体

存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl 语句，if-then-else 和 while-do 语句、声明变量的 declare 语句等。

过程体格式：以 begin 开始，以 end 结束 (可嵌套)

BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

注意：每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的 begin-end 块 (又叫做复合语句 compound statement )，则不需要分号。

4、存储过程参数

存储过程可以有 0 个或多个参数，用于存储过程的定义，3 种参数类型：

IN (输入参数)：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT (输出参数)：表示过程向调用者传出值 (可以返回多个值)（传出值只能是变量）
INOUT (输入输出参数)：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

4.1 IN 输入参数

DELIMITER $$
CREATE PROCEDURE in_test(IN p_in INT)
    BEGIN
        SELECT p_in;    -- 第一次查询
        SET p_in = 2;   -- 修改p_in的值
        SELECT p_in;    -- 第二次查询
    END$$
    
DELIMITER ;
SET @p_in = 1;
CALL in_test(@p_in);

+------+
| p_in |
+------+
|    1 |
+------+

+------+
| p_in |
+------+
|    2 |
+------+

SELECT @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+

虽然 p_in 在存储过程中被修改，但并不影响 @p_in 的值，因为 in_test 只接受输入参数，并不输出参数，所以相当于在函数内改变，但并未将这个值输出给 @p_in 。

4.2 OUT 输入参数

DELIMITER $$
CREATE PROCEDURE out_test(OUT p_out INT)
    BEGIN
        SELECT p_out;    -- 第一次查询
        SET p_out = 2;   -- 修改p_out的值
        SELECT p_out;    -- 第二次查询
    END$$

DELIMITER ;
SET @p_out = 1;
CALL out_test(@p_out);

+-------+
| p_out |
+-------+
|  NULL |
+-------+

+-------+
| p_out |
+-------+
|     2 |
+-------+

SELECT @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+

第一个返回结果为 NULL 是因为 OUT 是向调用者输出参数，不接收输入的参数，所以第一次查询时 p_out 还未赋值，所以是 NULL 。最后 @p_out 变量的值变为 2 是因为调用了 out_test 存储过程，输出参数，改变了 p_out 变量的值。

4.3 INOUT 输入输出参数

DELIMITER $$
CREATE PROCEDURE inout_test(INOUT p_inout INT)
    BEGIN
        SELECT p_inout;    -- 第一次查询
        SET p_inout = 2;   -- 修改p_inout的值
        SELECT p_inout;    -- 第一次查询
    END$$
    
DELIMITER ;
SET @p_inout = 1;
CALL inout_test(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+

+---------+
| p_inout |
+---------+
|       2 |
+---------+

SELECT @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+

调用 inout_test 存储过程，既接受了输入的参数，也输出参数， @p_inout 的值被改变。

5、删除存储过程

使用 DROP PROCEDURE 来删除存储过程。

DROP PROCEDURE in_test;

6、自建函数&变量定义

6.1 函数定义

MySQL的函数定义语法如下：

    CREATE  
        [DEFINER = { user | CURRENT_USER }]
        FUNCTION functionName ( varName varType [, ... ] )
        RETURNS returnVarType
        [characteristic ...] 
        routine_body

functionName：函数名，同MySQL内置函数一样，大小写不敏感
varName: 形参名
varType: 形参类型，其与varName配对使用，形参数量不限
returnVarType: 返回值类型。函数必须有且只能有一个返回值
characteristic：函数特性，下将详述
routine_body：函数体。函数体中必须含有 return 语句，当函数体为复合结构时，需要使用begin … end 语句

characteristic的可用参数值如下：

| 值 | 说明 | | :-: | :-: | | language sql | 指明函数体的语言类型, 目前仅支持sql
| | [not] deterministic |

deterministic 指明函数的结果是确定的，即相同的输入会得到相同的输出；
not deterministic意为结果不确定。默认为 not deterministic

| | { contains sql | no sql | reads sql data | modifies sql data } | 指明函数体使用sql语句的限制。

contains sql意为函数体包含sql语句，但不包含读写数据的sql语句，默认为contains sql
no sql意为函数体不包含sql语句；
reads sql data意为函数体包含读数据sql语句；
modifies sql data意为函数体包含写数据的sql语句。

| | sql security { definer | invoker } | 指明谁有权限执行该函数。

definer意为只有定义者才能执行，默认为definer
invoker意为拥有权限的调用者可以执行。

| | comment ‘message’ | 函数的注释信息，指明函数的功能 |

示例如下：

CREATE DEFINER=`root`@`%` FUNCTION `convert_时间区间`(d datetime) RETURNS varchar(20) CHARSET utf8mb4 COLLATE utf8mb4_0900_as_ci
    DETERMINISTIC  # 此次需要特别注意，在navicat 里测试，没有这条编译不能通过
BEGIN
-- 传入预约日期与统计日期间隔天数，按照间隔天数，转化为T+1,之类
	DECLARE h int;
	set h=HOUR(d);
	IF h<9 THEN
		return "[00,09)";
	ELSEIF h=9 then
    	return CONCAT("[0",h,",",h+1,")");	
	ELSEIF h<19 then
    	return CONCAT("[",h,",",h+1,")");	
	ELSE
		return  "[19,24)";
END IF;
END

6.2 局部变量

定义在sql语句块中的变量，常见于存储过程和函数的 begin … end 中，语句块执行完后局部变量则结束生命周期

声明定义

函数中可以通过declare 声明定义局部变量，其作用域为 begin ... end 的函数体中。默认初值为null，可以通过default指定该语句中所有定义变量的初值

declare var [, ...] varType [defualt initVal];

Note: declare声明定义语句，必须放在begin…end函数体中最前面的位置

= 赋值

-   通过**set**给变量赋值
    set var = expression [, var = expression, ...];
    set var := expression [, var = expression, ...];

sql下的 = 操作符是比较 ( 判定是否相等 ) 操作符，只有在set语句中可作为赋值操作符使用。故在其他语句中，赋值操作应该使用 := 操作符

通过select into给变量赋值

通过select语句将所查询出的字段数据依次赋值到 into 后的变量中。值得一提的是，当select查询结果为空时(即，无记录)，则不对变量进行赋值操作；当select查询的结果不止一条时，MySQL将报错，函数执行失败

select filed1 [, ...] into var1 [, ...] from tableName where conditon

示例

	declare res int;                    # 声明定义1个变量, 初值默认为 null
	declare num1, num2 int  default 27;     # 声明定义多个变量，初值全部为27
	declare data1, data2 int;       # 声明定义多个变量，初值全部默认为 null
	set num2 = 23, res = num1 + num2;       # 使用set语句, = 操作符赋值
	set data1 = 1, data2 = 1;
	select num, price into  data1, data2 from test2 where id = idx;     # 使用 select into 语句
	set res :=  res * (data1 + data2);      # 使用set语句, := 操作符赋值
	return (res);

6.3 用户变量

定义在当前客户端的连接下的变量，其作用域在当前客户端连接下均有效，当当前客户端断开连接后则该变量结束生命周期。其对其他客户端连接不可见

用户变量无需先行声明创建，直接赋值使用即可。赋值时，当前客户端下若无该用户变量，则会自动创建并完成赋值；查看一个不存在的用户变量时(e.g., select @foo)返回null。需要注意的是，用户变量的变量名必须以 @ 开头

通过set 赋值

	set @varName = val;     # 对名为 @varName 用户变量赋值
	set @varName := val;    # 对名为 @varName 用户变量赋值
	select @varName;         # 查看名为 @varName 用户变量的值

通过 select 赋值

通过select语句将所查询出的字段数据赋值到变量中，需要注意的是，只能使用 := 操作符赋值

	select @varName:=field [as field] [, ...] from tableName where condition;
	select @varName:=Val;

7、常用流程控制

7.1 if 语句

    if condition then
        statements;
    [ elseif condition then
        statements; ]
    [ else
        statements; ]
    end if;

7.2 case…end case 语句

	CASE case_value
		WHEN when_value THEN
			statement_list;
		ELSE
			statement_list;
	END CASE;

7.3 while 语句

    [label:] while condition do
        statments;
    end while [label]

7.4 leave、iterate

levae 和 iterate 分别用于循环控制，跳出整个循环 和 跳过循环体的剩余部分并直接进行下一次循环,作用相当于C语言中的 break和 continue。区别在于C语言中的break、continue都是针对所在层循环的控制。而levae 和 iterate是针对label所标注的循环的控制，故其可以实现从最内层循环直接跳出

    levae label;    # 跳出label所标注的循环结构
    iterate label;  # 跳过循环体的剩余部分，直接开始label所标注的下一次循环

8、函数相关的操作

查看函数状态

通过show status 命令查看函数的相关信息。可以在其后面使用 like 语句进行函数名匹配，其中functionName同样支持 % 进行模糊匹配

show function status [like functionName];

查看函数定义

通过show create 命令查看函数的定义内容

show create function functionName;

修改函数特性

通过alter function实现对函数特性characteristic的修改，注意，不是对函数定义内容的修改

alter function functionName [characteristic ...]

删除函数

通过drop function 删除函数。当指定函数不存在时，会报错，可以添加 if exists 避免出现报错

drop function [if exists] functionName

Note

在命令行中其默认将 ; 符号作为结束符来执行语句。所以如果我们在命令行中创建函数，需要使用 delimiter 命令重定义结束符如下图所示，先通过 delimiter $$ 将结束符更改为 $$ ( 可随意更改，一般常用 $$) 再创建函数。函数创建完毕后，通过我们重定义后的结束符结束(即 $$ )。最后不要忘记，通过 delimiter ; 将结束符重新修改为 ;

delimiter $$;
...
begin
... 
end $$
delimiter ;

六、事件管理

在系统管理或者数据库管理中，经常要周期性的执行某一个命令或者SQL语句。这个时候就会用到mysql事件，使用这个功能必须保证是mysql的版本是5.1以上。

DEFINER：定义事件执行的时候检查权限的用户。
ON SCHEDULE：定义执行的时间和时间间隔。
ON COMPLETION [NOT] PRESERVE：定义事件是一次执行还是永久执行，默认为一次执行，即NOT PRESERVE。
ENABLE | DISABLE | DISABLE ON SLAVE：定义事件创建以后是开启还是关闭，以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话，会自动加上DISABLE ON SLAVE。
COMMENT：定义事件的注释。

# 查看事件计划是否开启
SHOW VARIABLES LIKE 'event%';
SHOW VARIABLES LIKE 'event_scheduler';
 
#  开启事件计划
#  重启电脑自动开启事件计划 mysql.ini -> event_scheduler=ON
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

创建事件

# 如果事件存在，先删除
DROP EVENT IF EXISTS `auto_数据清理`;
-- 创建事件
CREATE DEFINER=`root`@`%` EVENT `auto_数据清理` # 创建事件
-- ON SCHEDULE EVERY 1 SECOND  # 设置重复运行规则
ON SCHEDULE EVERY 1 DAY STARTS '2022-07-21 06:00:00'  -- 每天6点执行
ON COMPLETION PRESERVE ENABLE # 创建后并立即开始生效
DO call px_数据清理();  # 是该事件的操作内容

七、管理事务处理

1、事务处理

事务处理（transaction processing）是一种机制，用来管理必须成批执行的SQL 操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态，以此来维护数据库的完整性。

事务处理术语：

事务（transaction）：指一组 SQL 语句；
回退（rollback）：指撤销指定 SQL 语句的过程；
提交（commit）：指将未存储的 SQL 语句结果写入数据库表；
保留点（savepoint）：指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。

事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。不能回退 SELECT 语句（回退 SELECT 语句也没有必要），也不能回退 CREATE 或 DROP 操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。

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

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

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

2、控制事务处理

BEGIN / START TRANSACTION ：显式地开启一个事务；
COMMIT / COMMIT WORK ：提交事务，使已对数据库进行的所有修改成为永久性的；
ROLLBACK / ROLLBACK WORK ：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT ：SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT ：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO ：把事务回滚到标记点；
SET TRANSACTION ：用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有 READ UNCOMMITTED（读未提交）、READ COMMITTED（读已提交，项目中常用）、REPEATABLE READ（可重复读，Mysql 默认隔离级别）和 SERIALIZABLE（串行化）。

简单示例：

use test;
CREATE TABLE transaction_test(id int(5)) ENGINE = INNODB;  # 创建数据表 
SELECT * FROM transaction_test;
Empty set (0.01 sec)

BEGIN;  # 开始事务 
INSERT INTO transaction_test VALUE(1); 
INSERT INTO transaction_test VALUE(2); 
COMMIT; # 提交事务
 
SELECT * FROM transaction_test;
+------+
| id   |
+------+
| 1    |
| 2    |
+------+

BEGIN;    # 开始事务
INSERT INTO transaction_test VALUES(3);
SAVEPOINT first_insert;    # 声明一个保存点
INSERT INTO transaction_test VALUES(4);
SAVEPOINT second_insert;    # 声明一个保存点
INSERT INTO transaction_test VALUES(5);
ROLLBACK TO second_insert;    # 回滚到 second_insert 保存点

SELECT * FROM transaction_test;    # 因为回滚所以数据没有插入
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

ROLLBACK TO first_insert;
SELECT * FROM transaction_test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

COMMIT;    # 执行 COMMIT 或 ROLLBACK 后保存点自动释放

3、事务隔离级别

先看一下如何查看事务隔离级别。

# 查看默认事务隔离级别（session）
select @@transaction_isolation;
# 查看当前会话的隔离级别
select @@session.transaction_isolation;
# 查看全局的事务隔离级别
select @@global.transaction_isolation;

在 Mysql 下事务的隔离级别有四种，由低到高依次为 Read uncommitted 、Read committed 、Repeatable read (默认)、Serializable ，这四个级别中的后三个级别可以逐个解决脏读、不可重复读 、幻读的问题。

3.1 脏读

对于两个事务 T1 与 T2，T1 读取了已经被 T2 更新但是还没有提交的字段之后，若此时 T2 回滚，T1 读取的内容就是临时并且无效的。

示例：

打开两个 Mysql 客户端，分别执行下面操作，查询当前会话的隔离级别（默认 REPEATABLE READ ）。修改当前会话隔离级别为（ READ UNCOMMITTED ）。全局事务隔离级别仍然为 REPEATABLE READ 。

SELECT @@session.transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;    # 修改会话隔离级别

SELECT @@session.transaction_isolation;    # 当前会话隔离级别已修改
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| READ-UNCOMMITTED                |
+---------------------------------+

SELECT @@global.transaction_isolation;    # 全局事务隔离级别未修改
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+

之后黑框用来做更新，白框用来查询。

由于黑框的 ④ 回滚，白色背景的客户端中 ③ 读取的数据就是临时并且无效的。即脏读。

3.2 不可重复读

对于两个事务 T1 和 T2，T1 读取了一个字段，然后 T2 更新了该字段并提交之后，当 T1 再次读取的时候，结果不一致的情况发生。

由于黑框的更新操作，白框出现两次读取的结果不一致。

3.3 幻读

对于两个事务 T1、T2，T1 从表中读取数据，然后 T2 进行了 INSERT 操作并提交，当 T1 再次读取的时候，结果不一致的情况发生。

由于黑框的插入操作，白框出现两次读取的结果不一致。

几种现象在各隔离级别中出现的可能性：

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

八、游标

SQL 检索操作返回结果集，简单地使用 SELECT 语句，没有办法得到第一行、下一行或前 10 行。有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

1、使用游标

游标使用的步骤：

在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句和游标选项。
一旦声明，就必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来。
对于填有数据的游标，根据需要取出（检索）各行。
在结束游标使用时，必须关闭游标，可能的话，释放游标。

声明游标后，可以根据需要频繁地 打开或关闭 游标。在游标打开时，可根据需要频繁地执行取操作。

注意：不像多数 DBMS，MySQL 游标只能用于存储过程（和函数）。

2、创建游标

使用 DECLEAR 来创建游标，DECLARE 命名游标，并定义相应的 SELECT 语句，根据需要带 WHERE 和其他子句。

下面的语句定义了名为 ordernumbers 的游标，使用了可以检索所有订单的 SELECT 语句。

Order 表中的信息：

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

这个存储过程中，使用 DECLARE 语句用来定义和命名游标。存储过程处理完成后，游标就消失（因为它局限于存储过程）。

3、使用游标数据

使用 OPEN 语句来打开游标，CLOSE 语句关闭游标，在一个游标被打开后，可以使用 FETCH 语句分别访问它的每一行。FETCH 指定检索的数据（所需的列），数据存储的位置（定义的变量）。它还向前移动游标中的内部行指针，使下一条 FETCH 语句检索下一行（不重复读取同一行）。

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
    -- 定义局部变量
    DECLARE num INT;
    -- 定义游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    -- 打开游标
    OPEN ordernumbers;
    -- 获取第一行数据
    FETCH ordernumbers INTO num;
    -- 查询结果
    SELECT num;
    -- 关闭游标
    CLOSE ordernumbers;
END;
CALL processorder();

其中 FETCH 用来检索当前行的 order_num 列（将自动从第一行开始）到一个名为 num 的局部变量中，并将查询 num 的结果。由于只检索到第一行，所以 num 的值为 ‘20005’。

下面，循环检索数据，从第一行到最后一行。

DROP PROCEDURE IF EXISTS processorder;
CREATE PROCEDURE processorder()
BEGIN
	-- 定义局部变量
	DECLARE done BOOLEAN DEFAULT false;
	DECLARE num INT;
	-- 定义游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	-- 定义CONTINUE HANDLER
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=true;
	-- 打开游标
	OPEN ordernumbers;
	-- 循环所有行
	REPEAT
		-- 获取第一行数据
		FETCH ordernumbers INTO num;
	-- 结束循环
	UNTIL done END REPEAT;
	-- 查询结果
	SELECT num;
	-- 关闭游标
	CLOSE ordernumbers;
END;
CALL processorder();

循环了结果集的所有行，所以 num 的值是最后一行的数据。

与上一个例子不同之处是，这个例子的 FETCH 是在 REPEAT 内，因此它反复执行直到 done 为真。

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=true;

这条语句定义了一个 CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当 SQLSTATE '02000' 出现时，SET done=true。SQLSTATE '02000'是一个未找到条件，当 REPEAT 由于没有更多的行供循环而不能继续时，出现这个条件。

4、游标的优缺点

优点：游标是面向集合与面向行的设计思想之间的一种桥梁，因为游标是针对行操作的，所以对从数据库中 SELECT 查询得到的每一行可以进行分开的独立的相同或不同的操作，是一种分离的思想。可以满足对某个结果行进行特殊的操作。如基于游标位置的增删改查能力。

缺点：

速度较慢
会产生死锁现象
内存大

九. 约束 & 索引

1、约束

DBMS 通过在数据库表上施加约束来实施引用完整性。大多数约束是在表定义中定义的，用 CREATE TABLE 或是 ALTER TABLE 语句。

2、主键

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。没有主键，要安全地 UPDATE 或 DELETE 特定行而不影响其他行会非常困难。

主键的条件：

任意两行的主键值都不相同。
每行都具有一个主键值（即列中不允许 NULL 值）。

创建表时定义主键。

CREATE TABLE teacher
(
	id INT(11) PRIMARY KEY,
	teacher_name VARCHAR(10)
);

使用 ALTER TABLE 添加主键。

ALTER TABLE teacher
ADD CONSTRAINT PRIMARY KEY(id);

删除主键约束。

ALTER TABLE teacher DROP PRIMARY KEY;

3、外键

外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。

下面新建 student 表并添加外键 teacher_id 与 teacher 表中的主键 id 进行关联。

在创建表的时定义外键。

CREATE TABLE student
(
	stu_id INT(11) PRIMARY KEY,
	teacher_id INT(11) REFERENCES teacher(id),
	stu_name VARCHAR(10)
);

使用 ALTER TABLE 添加外键。

ALTER TABLE student
ADD CONSTRAINT teacher_id_id
FOREIGN KEY (teacher_id) REFERENCES teacher(id);

使用外键可以有效地防止意外删除，比如在上面两表中如果删除 teacher 表中的信息，如果该 id 在 student 表中也有出现，那么 Mysql 会防止删除操作。当然也可以启用级联删除的特性，那么在删除时就会删除所有相关信息。

删除外键。

ALTER TABLE student DROP FOREIGN KEY teacher_id_id;

4、唯一约束

唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。

表可包含多个唯一约束，但每个表只允许一个主键。
唯一约束列可包含 NULL 值。
与主键不一样，唯一约束不能用来定义外键。

在创建表的时定义唯一约束。

CREATE TABLE student
(
	stu_id INT(11) PRIMARY KEY,
	teacher_id INT(11) REFERENCES teacher(id),
	stu_name VARCHAR(10)
);

使用 ALTER TABLE 添加唯一约束。

ALTER TABLE student
ADD CONSTRAINT unique_id UNIQUE(stu_id);

删除唯一性约束。

ALTER TABLE student DROP INDEX unique_id;

5、检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。

常见用途：

检查最小或最大值。
指定范围。
只允许特定的值。

下面创建一个检查约束来限制性别列只能输入男、女。

在创建表的时定义检查约束。

CREATE TABLE student
(    
    stu_id INT(11) PRIMARY KEY,
	gender VARCHAR(1) CHECK(gender IN('男', '女'))
);

使用 ALTER TABLE 添加检查约束。

ALTER TABLE student ADD CONSTRAINT check_gender CHECK(gender in ('男', '女'));

删除检查约束。

ALTER TABLE student DROP CHECK check_gender;

6、索引

索引用来排序数据以加快搜索和排序操作的速度。主键数据总是排序的，因此，按主键检索特定行总是一种快速有效的操作。但是，搜索其他列中的值通常效率不高。这时候我们可以使用索引，在一个或多个列上定义索引，使 DBMS保存其内容的一个排过序的列表。在定义了索引后，DBMS 以使用书的索引类似的方法使用它。DBMS 搜索排过序的索引，找出匹配的位置，然后检索这些行。

索引特点：

索引提高检索的性能，但降低了数据增删改的性能。在执行这些操作时，DBMS 必须动态地更新索引。
索引数据可能要占用大量的存储空间。
并非所有数据都适合做索引。取值不多的数据（如地区）不如具有更多可能值的数据（如姓名），能够更加体现索引的价值。
索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
可以在索引中定义多个列（例如，国家 + 城市）。

6.1、普通索引

在创建表的时创建普通索引。

DROP TABLE IF EXISTS student;
CREATE TABLE student
(    
  	id INT(11),
	stu_name VARCHAR(10)
)

直接创建。

CREATE INDEX stu_id ON student(id);

修改表时创建。

ALTER TABLE student ADD INDEX stu_id(id);

删除索引。

DROP INDEX stu_id ON student;

6.2、唯一索引

唯一索引列值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一。事实上，在许多场合，创建唯一索引的目的往往不是提高访问速度，而是为了避免数据出现重复。

CREATE UNIQUE INDEX stu_id ON student(id);

6.3、全局索引

全文索引只能作用在 CHAR、VARCHAR、TEXT、类型的字段上。创建全文索引需要使用 FULLTEXT 参数进行约束。

CREATE FULLTEXT INDEX s_name ON student(stu_name);

6.4、多列索引

多列索引，即在数据表的多个字段上创建索引。

CREATE TABLE student
(    
  id INT(11),
	stu_name VARCHAR(10),
	email VARCHAR(20),
	INDEX info(stu_name, email)
);

在多列索引中，只有查询条件中使用了这些字段中的第一个字段 (即上面示例中的 stu_name 字段)，索引才会被使用（最左前缀’原则）。如果没有用到第一字段，则索引不起任何作用。

-- 使用索引
SELECT * FROM student WHERE stu_name = '张三';
SELECT * FROM student WHERE stu_name = '李四' AND email = '11111@qq.com';
-- 未使用索引
SELECT * FROM student WHERE email = '11111@qq.com';

Daphar

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql基础操作到进阶

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。本文链接：https://blog.csdn.net/qq_43965708/article/details/120037528文章目录1. 查询1.1 查询多列1.2 检索唯一值1.3 限制输出1.4 注释2. ORDER BY 排序2.1 单列排序3.2 多列排序3.3 指定排序方向4. WHERE 过滤数据4.1 WHERE 子句操作符4.2 范围值检查4.3 空值检查5. 高级数据过滤5.1
复制链接

扫一扫