[两万字教程]MYSQL必知必会一站式完全教程
技术分享 数据分析 mysql
数据分析
发布日期:   2023-07-14
作者:   Bevis23
文章字数:   22k
阅读时长:   82 分
阅读次数:  

引言

此文是针对《MYSQL必知必会》一书学习的笔记与总结，注意要点的提示，文末会放上总的思维导图，该文除了对整书的脉络与知识点整理外，还有一些个人的总结见解，比如基本概念-mysql语句的法则与执行顺序，是通过阅读借鉴与实践再经过个人总结，后续且会不断更新本文，为写出更好的mysql语句 ^_^

基本概念

什么是数据库？

一个以某种有组织的方式存储的数据集合，数据库（database） 保存有组织的数据的容器

数据库由表组成，行/列数据，数据类型；主键（primary key）一一列（或一组列），其值能够唯一区分表中每个行

数据库术语

  • 数据库（database） - 保存有组织的数据的容器（通常是一个文件或一组文件）。
  • 数据表（table） - 某种特定类型数据的结构化清单。
  • 模式（schema） - 关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。数据库和表都有模式。
  • 列（column） - 表中的一个字段。所有表都是由一个或多个列组成的。
  • 行（row） - 表中的一个记录。
  • 主键（primary key） - 一列（或一组列），其值能够唯一标识表中每一行

什么是SQL？

SQL是结构化查询语言（Structured Query Language）的缩写。SQL是一种专门用来与数据库通信的语言

语法结构：

1分号结尾、2不区分大小写、3字符要加单引号、4字段或参数间用逗号分割

  • SQL 支持三种注释

    ## 注释1
    – 注释2
    / 注释3 /

  • 子句 - 是语句和查询的组成成分。（在某些情况下，这些都是可选的。）

  • 表达式 - 可以产生任何标量值，或由列和行的数据库表

  • 谓词 - 给需要评估的 SQL 三值逻辑（3VL）（true/false/unknown）或布尔真值指定条件，并限制语句和查询的效果，或改变程序流程。

  • 查询 - 基于特定条件检索数据。这是 SQL 的一个重要组成部分。

  • 语句 - 可以持久地影响纲要和数据，也可以控制数据库事务、程序流程、连接、会话或诊断。

SQL分类

数据定义语言（DDL）

数据定义语言（Data Definition Language，DDL）是 SQL 语言集中负责数据结构定义与数据库对象定义的语言。

DDL 的主要功能是定义数据库对象。

DDL 的核心指令是 CREATE、ALTER、DROP。

数据操纵语言（DML）

数据操纵语言（Data Manipulation Language, DML）是用于数据库操作，对数据库其中的对象和数据运行访问工作的编程语句。

DML 的主要功能是 访问数据，因此其语法都是以读写数据库为主。

DML 的核心指令是 INSERT、UPDATE、DELETE、SELECT。这四个指令合称 CRUD(Create, Read, Update, Delete)，即增删改查。

事务控制语言（TCL）

事务控制语言 (Transaction Control Language, TCL) 用于管理数据库中的事务。这些用于管理由 DML 语句所做的更改。它还允许将语句分组为逻辑事务。

TCL 的核心指令是 COMMIT、ROLLBACK。

数据控制语言（DCL）

数据控制语言 (Data Control Language, DCL) 是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。

DCL 的核心指令是 GRANT、REVOKE。

DCL 以控制用户的访问权限为主，因此其指令做法并不复杂，可利用 DCL 控制的权限有：CONNECT、SELECT、INSERT、UPDATE、DELETE、EXECUTE、USAGE、REFERENCES。

根据不同的 DBMS 以及不同的安全性实体，其支持的权限控制也有所不同。

什么是MYSQL？

MySQL是一种开源的关系型数据库管理系统（RDBMS），它是最流行和广泛使用的数据库之一。MySQL由瑞典公司MySQL AB开发，并在2008年被甲骨文公司收购。它支持多用户、多线程和多个存储引擎，可以在各种操作系统上运行。

MySQL具有以下特点：

  1. 可靠性：MySQL以其稳定性和可靠性而闻名。它经过广泛测试和验证，被许多大型组织和网站用于处理大量数据。

  2. 性能：MySQL被设计为高性能数据库。它采用了各种优化技术，如索引、查询缓存和内存管理，以提供快速的数据访问和处理能力。

  3. 可扩展性：MySQL支持水平和垂直扩展。水平扩展通过分布式架构和数据分片来增加系统的容量和吞吐量。垂直扩展通过增加硬件资源（如CPU、内存）来提高单个服务器的性能。

  4. 简单易用：MySQL提供了简单而直观的命令和语法，使得数据库的管理和查询变得容易。它还提供了丰富的图形界面工具和API，方便开发人员进行数据库操作。

  5. 兼容性：MySQL遵循SQL标准，并提供了广泛的功能和特性，包括事务支持、触发器、存储过程和视图等。它还支持多种编程语言和开发框架。

MYSQL工具

  1. MySQL命令实用工具： 通过mysql的基础命令操作使用数据库
  2. phpMyAdmin： phpMyAdmin是一个基于Web的MySQL数据库管理工具，它提供了一个易于使用的界面来管理数据库。通过phpMyAdmin，你可以执行SQL查询、导入和导出数据、创建和修改表结构、用户管理等。它支持多语言和跨平台，并且具有良好的扩展性，可以通过插件来增加额外的功能。phpMyAdmin是一个开源工具，广泛应用于各种Web开发项目中。
  3. Navicat for MySQL：Navicat for MySQL是一款强大的数据库管理和开发工具，适用于MySQL和MariaDB。它提供了直观的用户界面，使得用户可以轻松地进行数据库设计、数据导入导出、SQL查询和脚本编写等操作。Navicat for MySQL支持多个平台，并且具有高级的数据可视化和报表生成功能。此外，它还提供了团队协作和版本控制的特性，方便多人共同开发和管理数据库。

使用MYSQL

连接

主机名（计算机名）——如果连接到本地MySQL服务器，为localhost；

端口（如果使用默认端口3306之外的端口）；

一个合法的用户名；

用户口令（如果需要）；

选择与了解

show database;    #查看数据库
use database；    #使用数据库
show tables;    #查看表
show status;    #查看广泛的服务器状态信息
show grants;    #显示授予用户的安全权限
show error;    show warnings;    #显示服务器错误或警告消息

help show；    #查看show的使用帮助

什么是自动增量？ 某些表列需要唯一值。例如，订单编号\雇员ID或（如上面例子中所示的顾客ID。在每个行添加到表 中时，MySQL可以自动地为每个行分配下一个可用编号，不用在添加一行时手动分配唯一值（这样做必须记住最后一次使 用的值）这个功能就是所谓的自动增量。如果需要它，则必须在用CREATE语句创建表时把它作为表定义的组成部分。

MYSQL语句的法则！！

书写顺序与执行顺序！！

(8) SELECT (9)DISTINCT<Select_list>
(1) FROM <left_table> (3) <join_type>JOIN<right_table>
(2) ON<join_condition>
(4) WHERE<where_condition>
(5) GROUP BY<group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING<having_condtion>
(10) ORDER BY<order_by_list>
(11) LIMIT<limit_number>

书写顺序为此排列，执行顺序为（1）~（11）

SELECT子句是必选的，其它子句如WHERE子句、GROUP BY子句等是可选的。一个select语句中，子句的顺序是固定的。必须严格按照上述的顺序书写

法则：

SELECT col,col,col 找什么？ FROM table 从哪找？ WHERE col 条件 条件是啥？

这个法则教你怎么一步步写一个查询SQL，可以当做一种写SQL的思维

简言之，写任何一个查询语句，都分解成3步：

第一步：思考FROM后面的（也即是表）

第二步：思考WHERE条件（查手册就可以）

第三步：完善SELECT后面的（也就是老板要显示的数据）

条件：数字(where)

当查找条件col是数字

select * from table where col = 1;

Operator Condition SQL Example 解释
=, !=, < ,<=, >, >= Standard numerical operators col != 4 等于 大于 小于
BETWEEN … AND … Number is within range of two values (inclusive) col BETWEEN 1.5 AND 10.5 在 X 和 X之间
NOT BETWEEN … AND … Number is not within range of two values (inclusive) co NOT BETWEEN 1 AND10 不在 X 和 X之间
IN (…) Number exists in a list col IN (2, 4, 6) 在 X 集合
NOT IN (…) Number does not exist in a list col NOT IN (1, 3, 5) 不在 X 集合

条件：文本(where)

当查找条件col是文本

select * from table where col like '%jin';

Operator Condition SQL Example 解释
= Case sensitive exact string comparison (notice the single equals) col = “abc” 等于
!= or <> Case sensitive exact string inequality comparison col != “abcd” 不等于
LIKE Case insensitive exact string comparison col LIKE “ABC” 等于
NOT LIKE Case insensitive exact string inequality comparison col NOT LIKE “ABCD” 不等于
% Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) col LIKE “%AT%” (matches “AT”, “ATTIC”, “CAT” or even “BATS”) 模糊匹配
_ Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) col LIKE “AN_” (matches “AND”, but not “AN”) 模糊匹配单字符
IN (…) String exists in a list col IN (“A”, “B”, “C”) 在集合
NOT IN (…) String does not exist in a list co NOT IN (“D”, “E”, “F”) 不在集合

排序(rows)

需要对结果rows排序和筛选部分rows
select * from table where col > 1 order by col asc limit 2 offset 2
Operator Condition SQL Example 解释
ORDER BY . ORDER BY col ASC/DESC 按col排序
ASC . ORDER BY col ASC/DESC 升序
DESC . ORDER BY col ASC/DESC 降序
LIMIT OFFSET . LIMIT num_limit OFFSET num_offset 从offset取limit
ORDER BY . ORDER BY col1 ASC,col2 DESC 多列排序

join:连表(table)

当查找的数据在多张关联table里
select * from table1 left join table2 on table1.id = table2.id where col > 1
Operator Condition SQL Example 解释
JOIN .. ON .. . t1 JOIN t2 ON t1.id = t2.id 按ID连成1个表
INNER JOIN . t1 INNER JOIN t2 ON t1.id = t2.id 只保留id相等的row
LEFT JOIN . t1 LEFT JOIN t2 ON t1.id = t2.id 保留t1的所有row
RIGHT JOIN . t1 RIGHT JOIN t2 ON t1.id = t2.id 保留t2的所有row
IS/IS NOT NULL . col IS/IS NOT NULL col是不是为null

算式(select / where)

当需要对select的col 或 where条件的col 经过一定计算后才能使用
select *,col*2 from table where col/2 > 1
Operator Condition SQL Example 解释
+ - * / % . col1 + col2 col加减乘除
substr . substr(col,0,4) 字符串截取
AS . col * 2 AS col_new col取别名
… 还有很多

统计（select）

对查找的rows需要按col分组统计的情况
select count(*),avg(col),col from table where col > 1 group by col
Operator Condition SQL Example 解释
COUNT(*), COUNT(column) A common function used to counts the number of rows in the group if no column name is specified. Otherwise, count the number of rows in the group with non-NULL values in the specified column. count(col) 计数
MIN(column) Finds the smallest numerical value in the specified column for all rows in the group. min(col) 最小
MAX(column) Finds the largest numerical value in the specified column for all rows in the group. max(col) 最大
AVG(column) Finds the average numerical value in the specified column for all rows in the group. avg(col) 平均
SUM(column) Finds the sum of all numerical values in the specified column for the rows in the group. sum(col) 求和
GROUP BY . group by col,col2 分组
HAVING . HAVING col>100 分组后条件

子表 (table)

一次select的结果rows作为下一次select的临时table才能得到最终结果
select * from (select * from table where col > 1) as tmp where col < 1
Operator Condition SQL Example 解释
（select -）as tmp （select -）as tmp select结果做子表
in（select -） in（select -） select结果做条件
avg（select -） avg（select -） select结果做条件

增

创建和操纵表

创建表

创建表

你可以使用 CREATE TABLE 语句。这个语句定义了表的结构，包括列名、数据类型和约束等信息 

CREATE TABLE 表名 (
    列名1 数据类型1 约束,
    列名2 数据类型2 约束,
    列名3 数据类型3 约束,
    ...
);

使用null值

理解NULL 不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。如果指定’’（两个单引号，其间没有字符），这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空串指定

主键

主键和NULL值 第1章介绍过，主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

AUTO_INCREMENT

一个列属性，用于自动分配唯一的递增值给该列， 常用于为表的主键列分配唯一标识符

1.定义 AUTO_INCREMENT：可以在创建表时或者修改表结构时，为某个列指定 AUTO_INCREMENT 属性。一般情况下，这个列通常作为表的主键列。 

CREATE TABLE 表名 (
    列1 数据类型 AUTO_INCREMENT,
    ...
    PRIMARY KEY (列1)
);

  1. 获取 AUTO_INCREMENT 值：插入数据后，你可以使用 LAST_INSERT_ID() 函数来获取最后插入的 AUTO_INCREMENT 值。

    INSERT INTO 表名 (列1, 列2) VALUES ('value1', 'value2');
SELECT LAST_INSERT_ID();

  2. 自定义起始值和步长：使用 AUTO_INCREMENT = value 语句可以设置 AUTO_INCREMENT 列的起始值，并且可以使用 AUTO_INCREMENT = value, INCREMENT BY value 语句设置步长 

    ALTER TABLE 表名 AUTO_INCREMENT = 100;
ALTER TABLE 表名 AUTO_INCREMENT = 100, INCREMENT BY 5;

更新表之alter、drop

修改表名

ALTER TABLE 旧表名 RENAME TO 新表名;

修改列字段名

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型;

增加列字段

ALTER TABLE 表名 ADD COLUMN 列名 数据类型;

删除列字段

ALTER TABLE 表名 DROP COLUMN 列名;

删除表

DROP TABLE 表名;

重命名表

ALTER TABLE 旧表名 RENAME TO 新表名;

插入数据

语法

要在 MySQL 中插入数据，可以使用 INSERT INTO 语句。以下是插入数据的基本语法： 

INSERT INTO 表名 (列1, 列2, 列3, ...) VALUES (值1, 值2, 值3, ...);

插入多个行

MySQL 中插入多个行数据，可以使用 INSERT INTO 语句，并以 VALUES 子句指定多个值集。每个值集表示一行数据，用括号包围并用逗号分隔。以下是插入多个行数据的基本语法： 

INSERT INTO 表名 (列1, 列2, 列3, ...)
VALUES
    (值1, 值2, 值3, ...),
    (值4, 值5, 值6, ...),
    (值7, 值8, 值9, ...);

使用 INSERT INTO … SELECT 语句：可以通过 SELECT 查询语句获取多行数据，并将其插入到目标表中。例如：

INSERT INTO mytable (column1, column2, column3)
SELECT column1, column2, column3
FROM othertable
WHERE condition;

提高insert性能

MySQL用单条INSERT语句处理多个插入比使用多条INSERT 语句快

使用这些方法，可以减少与数据库之间的通信次数，从而提高插入操作的性能。此外，考虑到性能，还可以考虑以下因素：

  • 批量提交（Batch Commit）：将多条 INSERT 语句或多个值集合并在一个事务中，然后一次性提交。这样可以减少事务处理的开销，提高插入性能。
  • 索引管理：在大批量插入数据之前，暂时禁用索引、触发器等约束，插入完成后再重新启用。这避免了每次插入都触发索引维护，提高了插入性能。

删

更新和删除数据

更新数据之update

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

删除数据之drop、delete、truncate

在 MySQL 中，可以使用 DROP 语句来删除数据库、表或索引。请注意，DROP 操作是不可逆的，并且会永久删除目标对象及其关联的数据。 执行 DROP 语句之前请务必备份重要的数据，以防止数据丢失。

删除数据库

DROP DATABASE 数据库名;

删除表

DROP TABLE 表名;

删除索引

ALTER TABLE 表名
DROP INDEX 索引名;

删除数据

DELETE 语句删除表中的数据。DELETE 语句用于从表中删除满足指定条件的行。以下是 DELETE 命令的基本语法 

DELETE FROM 表名
WHERE 条件;

需要注意的是，如果不提供 WHERE 子句，则 DELETE 语句将删除表中的所有行。因此，请确保在删除之前仔细检查并设置正确的条件，以防止意外删除数据。

如果只是想清空表中的数据而不是删除表本身，可以使用 TRUNCATE TABLE 命令。TRUNCATE TABLE 语句将删除表中的所有数据，但保留表的结构。 

TRUNCATE TABLE 表名;

更新和删除数据的指导原则

下面是许多SQL程序员使用UPDATE或DELETE时所遵循的习惯。

1除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。

2保证每个表都有主键（如果忘记这个内容，请参阅第15章），尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。

3在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。

4 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。

查

检索数据之select

SELECT 命令的基本语法：

在 MySQL 中，可以使用 SELECT 语句来检索数据。SELECT 语句用于从表中选择满足指定条件的数据行，并将其返回给用户

SELECT 列1, 列2, ...
FROM 表名
WHERE 条件;

检索不同的行之distinct

SELECT DISTINCT 用于从表中选择唯一的、不重复的行数据 

SELECT DISTINCT 列1, 列2, ...
FROM 表名
WHERE 条件;

限制结果之limit

要限制 SELECT 语句返回的结果行数，可以使用 LIMIT 子句。LIMIT 子句用于从检索到的数据中选择指定数量的行。以下是 LIMIT 子句的基本语法： 

SELECT 列1, 列2, ...
FROM 表名
WHERE 条件
LIMIT 数量;

LIMIT 子句的完整语法是 LIMIT m, n，其中 m 表示起始行的偏移量，n 表示要返回的行数。通过指定起始行和行数，可以实现分页查询或者一次只返回一部分结果；limit m,n:表示从第m+1条开始，取n条数据

LIMIT 子句可以有两个参数，第一个参数表示要返回的起始行的偏移量，第二个参数表示要返回的行数。例如，LIMIT 5, 10 表示从第6行开始返回 10 行数据。

起始行的偏移量是从 0 开始计数的，而不是从 1 开始。因此，LIMIT 0, 10 将返回前 10 行数据。

使用完全限定的表名

使用完全限定的表名，可以在查询中指定表的完整名称，包括数据库名称和表名称。这在多个数据库或模式中存在同名表时非常有用。以下是在查询中使用完全限定表名的示例： 

SELECT 列1, 列2, ...
FROM 数据库名.模式名.表名
WHERE 条件
LIMIT m, n;

排序检索数据之order by

排序数据

对数据进行排序，可以使用 SQL 中的 ORDER BY 子句。ORDER BY 子句允许您根据一个或多个列对查询结果进行排序。以下是在 SQL 查询中使用 ORDER BY 子句的基本语法： 

SELECT 列1, 列2, ...
FROM 表名
ORDER BY 列名 [ASC|DESC];

按多个列排序

要对多个列进行排序，您可以在 ORDER BY 子句中指定多个列名，并按照优先级从左到右进行排序。例如： 

SELECT 列1, 列2
FROM 表名
ORDER BY 列1 ASC, 列2 DESC;

指定排序方向

数据排序不限于升序排序（从A到Z）。这只是默认的排序顺序，还可以使用ORDER BY子句以降序（从Z到A）顺序排序。为了进行降序排序，必须指定DESC关键字。

SELECT 列1, 列2
FROM 表名
ORDER BY 列名 ASC; -- 按列名升序排序

SELECT 列1, 列2
FROM 表名
ORDER BY 列名 DESC; -- 按列名降序排序

区分大小写和排序顺序 在对文本性的数据进行排序时，A与 a相同吗？a位于B之前还是位于Z之后？这些问题不是理论问题，其答案取决于数据库如何设置。在字典（dictionary）排序顺序中，A被视为与a相同，这是MySQL （和大多数数据库管理系统）的默认行为。但是，许多数据库管理员能够在需要时改变这种行为（如果你的数据库包含大量外语字符，可能必须这样做）。 这里，关键的问题是，如果确实需要改变这种排序顺序，用简单的ORDER BY子句做不到。你必须请求数据库管理员的帮助。

过滤数据

过滤数据之where

在 SQL 查询中使用 WHERE 子句的基本语法： 

SELECT 列1, 列2, ...
FROM 表名
WHERE 条件;

可以根据需要使用比较运算符（如 =, <, >, <=, >=, <> 等）和逻辑运算符（如 AND, OR, NOT）来构建更复杂的条件。 还有between from null等

数据过滤

组合WHERE子句

使用逻辑运算符（例如 AND、OR 和 NOT）组合多个条件，以创建更复杂的 WHERE 子句。这些逻辑运算符允许您在查询中同时测试多个条件，并根据需要进行组合。

  1. 使用 AND 运算符组合条件：

    SELECT 列1, 列2, ...
FROM 表名
WHERE 条件1 AND 条件2;

  2. 使用 OR 运算符组合条件 ：

    SELECT 列1, 列2, ...
FROM 表名
WHERE 条件1 OR 条件2;

  3. 使用 NOT 运算符否定条件 ：

    SELECT 列1, 列2, ...
FROM 表名
WHERE NOT 条件;

  4. 还可以使用括号 ( ) 来明确指定运算顺序并进一步组合条件。例如： 

    SELECT 列1, 列2, ...
FROM 表名
WHERE (条件1 OR 条件2) AND 条件3;

IN操作符

IN 操作符用于指定一个条件，以便从多个可能的值中选择匹配的行。IN 操作符用于简化多个 OR 条件的写法。

以下是 IN 操作符的基本语法：

SELECT 列1, 列2, ...
FROM 表名
WHERE 列名 IN (值1, 值2, ...);

还可以使用子查询作为 IN 操作符的值列表。例如： 

SELECT 列1, 列2, 列3
FROM 表名
WHERE 列1 IN (SELECT 列
              FROM 表
              WHERE 条件);

NOT操作符

NOT 操作符用于否定一个条件，即取条件的反义。它允许您排除不满足指定条件的行。

以下是 NOT 操作符的基本语法：

SELECT 列1, 列2, ...
FROM 表名
WHERE NOT 条件;

还可以在 NOT 操作符后面使用其他逻辑运算符（如 AND 和 OR）来进一步组合条件。例如： 

SELECT 列1, 列2, ...
FROM 表名
WHERE NOT (条件1 AND 条件2);

通配符过滤

LIKE操作符

LIKE 操作符用于比较字符串列与模式之间的匹配。它通常与通配符一起使用来进行模糊匹配。

以下是 LIKE 操作符的基本语法：

SELECT 列1, 列2, ...
FROM 表名
WHERE 列名 LIKE 模式;

LIKE 操作符支持两种通配符：

  • %：表示匹配任意字符序列（包括空字符序列）。
  • _：表示匹配任意单个字符。

使用通配符的技巧

正如所见，MySQL的通配符很有用。但这种功能是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。

1不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

2 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

3仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

正则表达式

什么是正则表达式

正则表达式（Regular Expression），简称为正则或Regex，是一种用于描述字符串模式的工具。它是一种强大而灵活的文本处理工具，常用于文本匹配、搜索和替换。

正则表达式的使用广泛，几乎所有编程语言都支持正则表达式的操作。它在文本处理、数据验证、数据清理等领域都有很多应用。

以下是一些正则表达式的常见特性和用途：

  1. 字符匹配：可以使用普通字符来匹配与之相等的字符。例如，正则表达式 abc 可以匹配字符串中连续的 “abc”。
  2. 字符类（Character classes）：可以使用字符类来匹配一组字符中的任意一个。例如，[abc] 可以匹配字符 “a”、”b” 或 “c”。
  3. 量词（Quantifiers）：可以使用量词来指定前面的字符或字符类重复出现的次数。例如，a{3} 可以匹配连续的三个 “a”。
  4. 转义字符（Escape characters）：可以使用反斜杠 \ 来转义特殊字符，使其具有普通字符的含义。例如，\. 可以匹配实际的句点字符 “.”。
  5. 边界匹配（Anchors）：可以使用边界匹配符号来指定字符串的开头和结尾。例如，^abc 匹配以 “abc” 开头的字符串。
  6. 分组和捕获（Grouping and capturing）：可以使用括号来分组正则表达式，并将分组作为单个单元进行处理和捕获结果。
  7. 替换操作：可以使用正则表达式来搜索和替换字符串中与模式匹配的部分。

注意！！！！！！ 正则表达式的语法和特性会因具体的编程语言和工具而有所差异，因此在实际使用时，请参考相关的文档或教程以了解具体的用法和语法规则

基本字符匹配regexp

REGEXP 运算符：REGEXP 运算符用于在 WHERE 子句中进行正则表达式匹配。

SELECT * FROM table_name WHERE column_name REGEXP 'pattern'; -- 使用正则表达式匹配

LOCATE 函数：LOCATE 函数用于查找一个子字符串在目标字符串中的位置。 

SELECT LOCATE('sub_string', 'string_value'); -- 查找 'sub_string' 在 'string_value' 中的位置

LIKE 运算符使用通配符进行模式匹配，而不是正则表达式。它支持通配符 % 和 _。

  • % 通配符：表示匹配任意字符（包括空字符）的序列，可以出现零次或多次。

  • _ 通配符：表示匹配任意单个字符。

  • SELECT * FROM table_name WHERE column_name LIKE 'abc%'; -- 匹配以 "abc" 开头的字符串
SELECT * FROM table_name WHERE column_name LIKE '%xyz'; -- 匹配以 "xyz" 结尾的字符串
SELECT * FROM table_name WHERE column_name LIKE '%def%'; -- 匹配包含 "def" 的字符串
SELECT * FROM table_name WHERE column_name LIKE 'a_c'; -- 匹配第一个字符为 "a"，第三个字符为 "c" 的字符串

使用 LIKE 运算符进行模式匹配时，不需要使用特殊的转义字符。

LIKE与REGEXP 在LIKE和REGEXP之间有一个重要的差别

LIKE 只支持简单的通配符模式匹配，而 REGEXP 支持更复杂的正则表达式模式匹配。

，LIKE匹配整个列。如果被匹配的文本在列值 中出现，LIKE将不会找到它，相应的行也不被返回（除非使用 通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在 列值中出现，REGEXP将会找到它，相应的行将被返回。这是一个非常重要的差别

进行OR匹配

为搜索两个串之一（或者为这个串，或者为另一个串），使用|，如下所示：

SELECT * FROM table_name WHERE column_name REGEXP 'pattern1|pattern2';

使用|从功能上类似于在SELECT语句中使用OR语句，多个OR条件可并入单个正则表达式。

两个以上的OR条件 可以给出两个以上的OR条件。例如，’1000 | 2000 | 3000’将匹配1000或2000或3000。

匹配几个字符之一

要在正则表达式中匹配几个字符之一，可以使用字符类（Character Class）来实现。字符类用方括号 [] 包围需要匹配的字符，表示匹配其中任意一个字符。

以下是基本的用法示例：

SELECT * FROM table_name WHERE column_name REGEXP '[abc]';

还可以通过使用连字符 - 来指定一个字符范围。例如： 

SELECT * FROM table_name WHERE column_name REGEXP '[0-9]';

匹配范围

要匹配一个字符的范围，你可以在正则表达式中使用连字符 - 来表示范围 以下是一些示例：

  • [a-z]：匹配任何小写字母。
  • [A-Z]：匹配任何大写字母。
  • [0-9]：匹配任何数字字符。
  • [a-zA-Z]：匹配任何字母（大写或小写）。
  • [a-zA-Z0-9]：匹配任何字母或数字字符。

你也可以结合多个范围来创建更复杂的匹配规则。例如，如果你想要匹配一个介于 1 到 5 之间的数字，你可以使用 [1-5]。

以下是一个示例查询，在名为 table_name 的表中查找列 column_name 包含的值是否匹配指定的范围：

SELECT * FROM table_name WHERE column_name REGEXP '[1-5]';

匹配特殊字符

要在正则表达式中匹配特殊字符，你需要使用反斜杠 \ 进行转义。因为一些字符在正则表达式中有特殊的含义，所以需要用反斜杠来转义它们，以表示字面意义。

以下是一些常见的特殊字符及其转义形式：

  • .：匹配除换行符外的任意字符，要匹配实际的点号，可以使用 \.。
  • *：匹配前面的字符零次或多次，要匹配实际的星号，可以使用 \*。
  • +：匹配前面的字符一次或多次，要匹配实际的加号，可以使用 \+。
  • ?：匹配前面的字符零次或一次，要匹配实际的问号，可以使用 \?。
  • [ 和 ]：用于定义字符类，要匹配实际的方括号，可以使用 \[ 和 \]。
  • { 和 }：用于指定重复次数，要匹配实际的花括号，可以使用 \{ 和 \}。
  • ( 和 )：用于分组，要匹配实际的圆括号，可以使用 \( 和 \)。

例如，如果你想要匹配一个字符串中的点号和星号，可以使用以下正则表达式：

SELECT * FROM table_name WHERE column_name REGEXP '[.*]';

注意，在不同的上下文中，特殊字符的转义方式可能会有所不同，所以要根据具体情况使用适当的转义形式。

匹配字符类

在正则表达式中，字符类（Character Class）用于匹配一组特定的字符。你可以使用方括号 [] 来定义一个字符类，并在其中列出你想要匹配的字符。

以下是一些字符类的示例：

  • [abc]：匹配字符 ‘a’、’b’ 或 ‘c’ 中的任意一个。
  • [aeiou]：匹配任何一个元音字母（’a’、’e’、’i’、’o’、’u’）。
  • [0-9]：匹配任何一个数字字符。
  • [A-Za-z]：匹配任何一个字母（大写或小写）。

你还可以在字符类中结合使用连字符 - 来指定一个字符范围。例如，[a-z] 表示匹配任何一个小写字母。

以下是一个示例查询，在名为 table_name 的表中查找列 column_name 包含的值是否匹配指定的字符类：

SELECT * FROM table_name WHERE column_name REGEXP '[aeiou]';

匹配多个实例

如果你想要匹配多个实例（即重复出现）的特定模式，你可以使用量词（Quantifier）来指定重复的次数。

以下是一些常用的量词：

  • *：匹配前面的模式零次或多次。
  • +：匹配前面的模式一次或多次。
  • ?：匹配前面的模式零次或一次。
  • {n}：精确匹配前面的模式出现 n 次。
  • {n,}：匹配前面的模式至少出现 n 次。
  • {n,m}：匹配前面的模式出现 n 到 m 次。

例如，如果你想要匹配一个字符串中的连续数字（至少一位数字），可以使用以下正则表达式：

SELECT * FROM table_name WHERE column_name REGEXP '[0-9]+';

定位符

在正则表达式中，定位符（Anchors）用于指定模式的位置，而不是字符本身。它们帮助我们匹配字符串的特定位置。

以下是一些常用的定位符：

  • ^：匹配字符串的开头。例如，^abc 可以匹配以 “abc” 开头的字符串。
  • $：匹配字符串的结尾。例如，xyz$ 可以匹配以 “xyz” 结尾的字符串。
  • \b：匹配单词的边界。例如，\bword\b 可以匹配独立的单词 “word”，而不会匹配包含它的单词。
  • \B：匹配非单词的边界。例如，\Bing\B 可以匹配字符串中不是单独单词的 “ing”。

以下是一个示例查询，在名为 table_name 的表中查找列 column_name 中以 “abc” 结尾的值：

SELECT * FROM table_name WHERE column_name REGEXP 'abc$';

汇总数据

聚集函数

聚集函数（Aggregate Functions）是用于对数据库表中的数据进行聚合计算的函数。它们将多个行的值汇总为单个结果，并返回计算后的结果。

以下是一些常见的聚集函数：

  • COUNT()：用于计算指定列中的行数，可以使用 COUNT(*) 计算所有行的数目。
  • SUM()：用于计算指定列中数值类型的总和。
  • AVG()：用于计算指定列中数值类型的平均值。
  • MIN()：用于找到指定列中的最小值。
  • MAX()：用于找到指定列中的最大值。

这些聚集函数可以与 SQL 查询语句中的 SELECT 和 GROUP BY 子句一起使用，以便根据特定条件对数据进行聚合计算。

以下是一个示例查询，使用 SUM() 函数计算名为 sales 的表中的销售总额：

SELECT SUM(amount) as total_sales FROM sales;

聚集不同值

想对数据库表中的某一列进行聚集并计算其不同值的数量，你可以使用 COUNT(DISTINCT) 函数。COUNT(DISTINCT) 函数用于计算指定列中不重复值的数量。

以下是一个示例查询，使用 COUNT(DISTINCT) 函数计算名为 products 的表中不同产品的数量：

SELECT COUNT(DISTINCT product_name) as distinct_products FROM products;

组合聚集函数

组合多个聚合函数来按照不同的方式对数据进行计算和汇总。这样可以得到更详细和灵活的结果。

以下是一些示例组合聚合函数的情况：

  1. 计算销售表中不同产品的总销售额和平均销售额：

    SELECT product_name, SUM(amount) as total_sales, AVG(amount) as average_sales
FROM sales
GROUP BY product_name;

    上述查询将根据产品名称分组，计算每个产品的总销售额和平均销售额。

  2. 找到订单表中销售额最高和最低的产品：

    SELECT MAX(amount) as max_sales, MIN(amount) as min_sales
FROM orders;

    上述查询将计算订单表中的最高销售额和最低销售额。

  3. 计算某一时间范围内销售表中不同产品的总销售额：

    SELECT product_name, SUM(amount) as total_sales
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY product_name;

    上述查询将根据指定的时间范围内的销售数据，计算每个产品的总销售额。

取别名 在指定别名以包含某个聚集函数的结果时，不应该使 用表中实际的列名。虽然这样做并非不合法，但使用唯一的名字会使你的SQL更易于理解和使用（以及将来容易排除故障）。

分组数据

数据分组之group by

数据分组是在 SQL 查询中使用 GROUP BY 子句对数据进行分组的过程。通过数据分组，你可以根据一个或多个列的值将数据划分为不同的组，并对每个组应用聚合函数来计算汇总结果。

以下是一个基本的数据分组示例：

SELECT column1, column2, aggregate_function(column3)
FROM table
GROUP BY column1, column2;

过滤分组之having

HAVING支持所有WHERE操作符

HAVING 子句用于在对数据进行分组后，对分组结果进行过滤。HAVING 子句中的条件是应用于聚合函数的结果。

以下是一些关于 HAVING 子句的使用示例：

  1. 筛选出销售额总和大于 1000 的产品组：

    SELECT product_name, SUM(amount) as total_sales
FROM sales
GROUP BY product_name
HAVING total_sales > 1000;

    上述查询根据产品名称对销售数据进行分组，并计算每个组的销售额总和。HAVING 子句筛选出销售额总和大于 1000 的产品组。

  2. 筛选出订单数量超过平均订单数的销售代表组：

    SELECT sales_rep, COUNT(order_id) as orders_count
FROM orders
GROUP BY sales_rep
HAVING COUNT(order_id) > (SELECT AVG(orders_count) FROM (SELECT COUNT(order_id) as orders_count FROM orders GROUP BY sales_rep) as subquery);

    上述查询根据销售代表对订单进行分组，并计算每个组的订单数量。HAVING 子句使用子查询来比较订单数量是否超过平均订单数。

请注意，HAVING 子句必须在 GROUP BY 子句之后使用，用于对分组后的结果进行过滤。如果需要对原始数据进行过滤，请使用 WHERE 子句。

分组和排序

​ ORDER BY与GROUP BY的差别

ORDER BY GROUP BY
排序产生的输出 分组行。但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用） 只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要 如果与聚集函数一起使用列（或表达式），则必须使用

复杂查询

使用子查询

子查询

子查询是指在 SQL 查询中嵌套使用的内部查询。子查询在外部查询的条件、选择列表或其他子句中使用，允许我们从嵌套的查询中检索数据并将其用作外部查询的一部分

子查询可以用于以下几个方面：

  1. 在 WHERE 子句中使用子查询进行过滤：

    SELECT column1, column2, ...
FROM table
WHERE column1 IN (SELECT column1 FROM another_table WHERE condition);

    上述查询中，子查询 (SELECT column1 FROM another_table WHERE condition) 用于选择满足特定条件的值，然后外部查询根据这些值对数据进行筛选

  2. 在 SELECT 子句中使用子查询获取计算结果：

    SELECT column1, (SELECT COUNT(*) FROM another_table) AS total_rows
FROM table;

    上述查询中，子查询 (SELECT COUNT(*) FROM another_table) 用于获取 another_table 中的行数，并将其作为别名为 total_rows 的计算结果返回到外部查询中。

  3. 在 FROM 子句中使用子查询作为临时表：

    SELECT column1, column2, ...
FROM (SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2) AS temp_table;

    上述查询中，子查询 (SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2) 将两个表的数据组合起来，并将其作为临时表 temp_table 在外部查询中使用。

利用子查询进行过滤

利用子查询进行过滤是一种常见的使用场景，可以根据内部查询的结果来筛选外部查询的数据。下面是一些示例：

  1. 使用子查询筛选符合条件的数据：
SELECT column1, column2, ...
FROM table
WHERE column1 IN (SELECT column1 FROM another_table WHERE condition);

上述查询中，子查询 (SELECT column1 FROM another_table WHERE condition) 返回满足特定条件的值，并将这些值用于外部查询中的 WHERE 子句，以筛选表 table 的数据。

  1. 使用子查询排除符合条件的数据：
SELECT column1, column2, ...
FROM table
WHERE column1 NOT IN (SELECT column1 FROM another_table WHERE condition);

上述查询中，子查询 (SELECT column1 FROM another_table WHERE condition) 返回满足特定条件的值，并将这些值用于外部查询中的 WHERE 子句的 NOT IN 条件，以排除表 table 中包含这些值的数据。

  1. 使用子查询进行范围过滤：
SELECT column1, column2, ...
FROM table
WHERE column1 > (SELECT AVG(column1) FROM another_table);

上述查询中，子查询 (SELECT AVG(column1) FROM another_table) 返回列 column1 的平均值，并将其用于外部查询中的 WHERE 子句，以筛选出表 table 中大于平均值的数据。

使用子查询作为计算字段

使用子查询作为计算字段是一种常见的用法，可以在查询结果中包含根据子查询计算得到的额外信息。下面是一个示例：

SELECT column1, column2, (SELECT COUNT(*) FROM another_table) AS total_rows
FROM table;

上述查询中，子查询 (SELECT COUNT(*) FROM another_table) 用于获取表 another_table 中的行数，并将其作为计算字段 total_rows 包含在查询结果中。这样，查询结果就会返回每一行数据的 column1、column2 字段值，以及额外的计算字段 total_rows。

子查询作为计算字段必须只返回单个值。如果子查询返回了多个值，将会引发错误。

组合查询

组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

创建组合查询union

要创建组合查询，可以使用 UNION、INTERSECT 或 EXCEPT 运算符来合并多个查询的结果集。下面是一个示例：

  1. 使用 UNION 运算符合并查询结果：
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

上述查询将从表 table1 和 table2 中选择 column1 和 column2 字段的结果，并将两个结果集合并在一起。如果有重复的行，将只返回一次。

  1. 使用 INTERSECT 运算符获取查询结果的交集：
SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;

上述查询将返回同时在表 table1 和 table2 中存在的行，且只包含 column1 和 column2 字段。

  1. 使用 EXCEPT 运算符排除一个查询结果中存在的行：
SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;

上述查询将从表 table1 中选择 column1 和 column2 字段的结果，排除在表 table2 中存在的行。

注意，在使用这些运算符时，两个结果集的列数和列的数据类型必须相同。

UNION规则

当使用 UNION 运算符合并多个查询结果时，需要遵循以下规则：

  1. 结果集的列数必须相同：所有查询语句中选择的列数必须一致。如果列数不匹配，则会导致错误。

  2. 列的数据类型必须兼容：相应位置上的列数据类型必须是兼容的，否则会导致错误。例如，数字列和字符串列不能直接相加，因此它们的数据类型不兼容。

  3. 结果集的列顺序必须一致：每个 SELECT 语句中选择的列的顺序必须相同，否则将导致结果集中的列顺序混乱。

  4. 去除重复行：UNION 运算符默认会去除结果集中的重复行。如果想保留重复行，可以使用 UNION ALL 运算符。

示例：

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

上述查询将从表 table1 和 table2 中选择 column1 和 column2 字段的结果，合并为一个结果集，并去除重复行。

总之，使用 UNION 运算符时，要确保查询语句的列数、列的数据类型和列顺序都一致，以及根据需要决定是否去除重复行。

包含或取消重复的行

要在组合查询中包含或取消重复的行，可以使用 UNION ALL 运算符或 DISTINCT 关键字。

  1. 使用 UNION ALL 运算符：这个运算符会将所有查询的结果集合并在一起，并保留所有的行，包括重复的行。
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

上述查询将从表 table1 和 table2 中选择 column1 和 column2 字段的结果，合并为一个结果集，并保留重复的行。

  1. 使用 DISTINCT 关键字：这个关键字用于消除结果集中的重复行。
SELECT DISTINCT column1, column2 FROM table;

上述查询将从表 table 中选择 column1 和 column2 字段的结果，并且只返回不重复的行。

UNION ALL 可以更快地执行，因为它不涉及去重的操作。而使用 DISTINCT 会对结果进行去重处理，可能会导致一些性能开销。

对组合结果进行排序

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2
ORDER BY column1 ASC/DESC;

计算字段

创建字段

计算字段

计算字段是在 SELECT 语句中使用表达式或函数来计算出的一个新字段。它不是数据库表中的实际列，而是在查询结果中动态生成的。以下是几个常见的计算字段示例：

  1. 使用表达式计算字段：
SELECT column1, column2, column3, (column1 + column2) AS sum FROM table;

上述查询中，(column1 + column2) 表达式计算出了一个名为 sum 的计算字段，表示 column1 和 column2 列的和。

  1. 使用函数计算字段：
SELECT column1, column2, column3, UPPER(column1) AS uppercase FROM table;

上述查询中，UPPER(column1) 函数将 column1 列的值转换为大写，并创建了一个名为 uppercase 的计算字段。

  1. 使用条件判断计算字段：
SELECT column1, column2, column3, CASE WHEN column1 > column2 THEN 'Greater' ELSE 'Less or equal' END AS comparison FROM table;

上述查询中，使用 CASE 表达式根据条件判断创建了一个名为 comparison 的计算字段，如果 column1 大于 column2，则为 ‘Greater’，否则为 ‘Less or equal’。

使用计算字段时，你可以给该字段起一个别名（AS 别名），以便在结果中显示更友好的列名。

拼接字段

在查询中，可以使用 CONCAT 函数来拼接字段。 CONCAT 函数可以将多个字段或字符串连接在一起形成一个新的字符串。

以下是几个示例：

  1. 拼接两个字段：
SELECT column1, column2, CONCAT(column1, column2) AS concatenated FROM table;

上述查询中，CONCAT(column1, column2) 将 column1 和 column2 的值拼接在一起，并创建一个名为 concatenated 的计算字段。

  1. 拼接字段和字符串：
SELECT column1, column2, CONCAT(column1, ' is the value of column1') AS result FROM table;

上述查询中，CONCAT(column1, ' is the value of column1') 将 column1 的值和一个字符串拼接在一起，并创建一个名为 result 的计算字段。

  1. 拼接多个字段和字符串：
SELECT column1, column2, CONCAT(column1, ' - ', column2, ' - ', column3) AS combined FROM table;

上述查询中，CONCAT(column1, ' - ', column2, ' - ', column3) 将 column1、column2 和 column3 的值以及一些字符串连接在一起，并创建一个名为 combined 的计算字段。

执行算术计算

在 SQL 查询中，可以使用内置的数学函数来执行算术计算。下面是几个常用的数学函数示例：

  1. 加法：
SELECT column1, column2, (column1 + column2) AS sum FROM table;

上述查询中，(column1 + column2) 执行了列 column1 和 column2 的加法操作，并创建了一个名为 sum 的计算字段。

  1. 减法：
SELECT column1, column2, (column1 - column2) AS difference FROM table;

上述查询中，(column1 - column2) 执行了列 column1 减去 column2 的操作，并创建了一个名为 difference 的计算字段。

  1. 乘法：
SELECT column1, column2, (column1 * column2) AS product FROM table;

上述查询中，(column1 * column2) 执行了列 column1 和 column2 的乘法操作，并创建了一个名为 product 的计算字段。

  1. 除法：
SELECT column1, column2, (column1 / column2) AS quotient FROM table;

上述查询中，(column1 / column2) 执行了列 column1 除以 column2 的操作，并创建了一个名为 quotient 的计算字段。

使用数据处理函数

函数

与其他大多数计算机语言一样，SQL支持利用函数来处理数据。函数一般是在数据上执行的，它给数据的转换和处理提供了方便。

使用函数

在 SQL 查询中，可以使用各种数据处理函数来处理数据。以下是几个常用的数据处理函数示例：

  1. 字符串处理函数：
  • UPPER：将字符串转换为大写。
  • LOWER：将字符串转换为小写。
  • LENGTH：返回字符串的长度。
  • SUBSTRING：提取字符串的子串。

例如：

SELECT UPPER(column) AS uppercase, LOWER(column) AS lowercase, LENGTH(column) AS length, SUBSTRING(column, 1, 3) AS substring FROM table;
  1. 数值处理函数：
  • ROUND：将数值四舍五入到指定精度。
  • CEILING：将数值向上取整。
  • FLOOR：将数值向下取整。
  • ABS：返回数值的绝对值。

例如：

SELECT ROUND(column, 2) AS rounded, CEILING(column) AS ceiling, FLOOR(column) AS floor, ABS(column) AS absolute FROM table;
  1. 日期和时间处理函数：
  • DATEPART：提取日期或时间部分（年、月、日、小时、分钟等）的值。
  • DATEADD：根据指定的日期或时间间隔添加或减去值。
  • DATEDIFF：计算两个日期之间的差异。

例如：

SELECT DATEPART(year, date_column) AS year_part, DATEADD(month, 1, date_column) AS added_month, DATEDIFF(day, date_column1, date_column2) AS day_difference FROM table;

表联结

联结

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结及其语法是学习SQL的一个极为重要的组成部分。

关系表

关系表（Relational Table）是关系型数据库中的基本组成部分之一，它由行和列组成，用于存储和组织数据。关系表是以二维表格形式呈现的，其中每一列表示一个属性（字段），每一行表示一个记录（元组）。

关系表的设计通常遵循数据库设计的规范，包括主键（用于唯一标识每条记录）、外键（用于与其他表建立关联）等约束。

为什么要使用联结

分解数据为多个表能更有效地存储，更方便地处理，且具有更大的可伸缩性。但这些好处是有代价的；

在关系型数据库中，使用联结（Join）是一种重要的操作，用于将多个关系表中的数据按照特定的关联条件进行合并。下面是一些使用联结的原因：

  1. 数据关联：当数据分布在多个表中，并且这些表之间存在关联关系时，使用联结可以根据关联条件将相关数据连接在一起，从而获得更完整和详细的信息。
  2. 数据查询：通过联结，可以执行更复杂和灵活的查询操作。联结允许同时查询和获取多个关联表中的数据，从而得到更准确和全面的查询结果。
  3. 数据完整性：联结可以确保数据的完整性和一致性。通过将关联表的数据合并在一起，可以避免数据冗余和不一致问题，提高数据库的数据质量。
  4. 数据分析：联结可以帮助进行复杂的数据分析任务。通过将多个表中的数据合并，可以进行更深入和综合的数据分析，发现更多的关联和模式。
  5. 优化性能：在某些情况下，通过联结来优化查询性能。通过联结，可以减少需要多次查询的数据访问次数，从而提高查询效率。

联结操作需要谨慎使用，尤其是对于包含大量数据的表或复杂的联结操作。合理设计数据库的关系、索引和查询语句是确保联结操作高效和准确的关键。

创建联结

在关系型数据库中，可以使用 SQL 语句来创建联结（JOIN），将多个表中的数据按照特定的关联条件进行合并。常见的联结类型包括内联结（INNER JOIN）、左联结（LEFT JOIN）、右联结（RIGHT JOIN）和全联结（FULL JOIN）。

以下是基本的联结语法示例：

  1. 内联结（INNER JOIN）：
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
  1. 左联结（LEFT JOIN）：
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
  1. 右联结（RIGHT JOIN）：
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
  1. 全联结（FULL JOIN）：
SELECT column1, column2, ...
FROM table1
FULL JOIN table2 ON table1.column = table2.column;

在上述示例中，table1 和 table2 是要进行联结的两个表，ON 子句指定了关联条件，用于指定两个表之间要匹配的列。SELECT 子句中列出了想要查询的字段。

创建高级联结

使用表别名

当需要进行更复杂的联结操作时，可以结合表别名使用高级联结。

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

1缩短SQL语句；

2允许在单条SELECT语句中多次使用相同的表。

以下是一个示例，演示如何使用表别名进行内联结和左联结：

SELECT t1.column1, t2.column2
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.id = t2.id
WHERE t1.column3 = 'value';

SELECT t1.column1, t2.column2
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t1.id = t2.id
WHERE t1.column3 = 'value';

在上述示例中，我们使用表别名 “t1” 和 “t2” 分别代替了表 table1 和 table2。通过表别名，我们可以在查询中引用相应的表，并指定它们之间的关联条件。

第一个示例展示了一个内联结，使用 INNER JOIN 关键字将表 t1 和 t2 进行联结，并在 ON 子句中指定了关联条件。这个查询只返回满足 t1.column3 = ‘value’ 条件的结果。

第二个示例展示了一个左联结，使用 LEFT JOIN 关键字将表 t1 和 t2 进行联结，并在 ON 子句中指定了关联条件。这个查询同样只返回满足 t1.column3 = ‘value’ 条件的结果，但保留了 t1 表中没有匹配的记录。

使用不同类型的联结

当处理一张表内的数据关联时，可以使用自联结（self-join），而当需要联结两个具有相同结构的表时，可以使用自然联结（natural join）。此外，当需要获取包括不符合联结条件的行时，可以使用外部联结（outer join）。

下面是使用不同类型的联结的示例：

  1. 自联结（Self-Join）：
SELECT t1.column1, t2.column2
FROM table AS t1
JOIN table AS t2 ON t1.id = t2.related_id;

在自联结中，我们使用相同的表别名 “t1” 和 “t2” 来引用同一张表，并指定它们之间的关联条件。这可以用于将表中的某一行与同一表中的其他行进行比较和关联。

  1. 自然联结（Natural Join）：
SELECT *
FROM table1
NATURAL JOIN table2;

自然联结会自动根据两个表中列名相同的列进行关联。该操作省略了使用 ON 子句指定关联条件的步骤。

  1. 外部联结（Outer Join）：
SELECT t1.column1, t2.column2
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t1.id = t2.id;

外部联结可以分为左外部联结（LEFT OUTER JOIN）和右外部联结（RIGHT OUTER JOIN）。上述示例演示了左外部联结，返回左表（table1）中的所有行，以及与右表（table2）中符合关联条件的匹配行。如果没有匹配行，则右表中的列将用 NULL 值表示。

使用带聚集函数的联结

可以在联结操作中使用聚合函数来对联结后的数据进行聚合计算。以下是使用带有聚合函数的联结的示例：

SELECT t1.column, COUNT(t2.column)
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.id = t2.id
GROUP BY t1.column;

上述示例中，使用了内联结（INNER JOIN）将表1（table1）和表2（table2）进行联结，并使用关联条件 t1.id = t2.id 进行匹配。然后，通过聚合函数 COUNT(t2.column) 对联结后的数据进行计数。使用 GROUP BY 子句按照 t1.column 进行分组，以便为每个不同的 t1.column 值计算对应的 COUNT(t2.column) 值。

可以根据需要使用其他聚集函数，例如 SUM、AVG、MIN、MAX 等等来对联结后的数据进行聚合计算。

使用联结和联结条件

1注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。

2保证使用正确的联结条件，否则将返回不正确的数据。

3应该总是提供联结条件，否则会得出笛卡儿积。

4在一个联结中可以包含多个表，甚至对于每个联结可以采用不同 的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。

使用视图

视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询

为什么使用视图

使用视图有以下几个常见的优点和用途：

  1. 简化复杂的查询：视图可以将多个表关联和筛选操作封装在一个视图中，从而简化复杂查询的编写过程。用户只需使用视图进行查询，而不必关心底层表的结构和关系。
  2. 数据安全性和权限控制：通过视图，可以限制用户对底层表的直接访问，从而提高数据的安全性。你可以定义一个仅包含所需的列和行的视图，然后将该视图授予特定的用户或角色。这样，用户只能通过视图访问被授权的数据，而无法直接访问表。
  3. 数据抽象和逻辑分离：视图可以提供数据的抽象层，将数据库的物理结构与逻辑操作进行分离。这有助于简化应用程序开发过程，使得应用程序可以基于视图进行操作，而不必直接处理底层表的复杂性。
  4. 维护和变更管理：如果数据库的底层表结构发生变化，例如添加或删除列，但视图的定义保持不变，那么应用程序也不需要进行大量的更改。视图提供了一种独立于底层数据结构的方式来访问数据，从而减少了维护工作和代码的重构。
  5. 提供数据重用：通过创建视图，可以将常用的查询逻辑封装起来并重复使用。这样可以提高代码的可维护性和代码重用性，减少冗余代码的编写。

视图的规则和限制

视图在使用时有一些规则和限制，下面是常见的一些规则和限制：

  1. 视图必须基于一个或多个已存在的表或其他视图。视图无法直接基于其他视图。

  2. 视图只能查询数据，不能修改数据。这意味着你不能通过视图进行插入、更新或删除操作。如果想要修改数据，需要直接操作底层的表。

  3. 视图的列名可以是自定义的，不一定与底层表的列名相同。但是在查询时，仍然需要使用视图中定义的列名。

  4. 视图的定义可以包含筛选条件，用于限制结果集中的行。这可以通过在创建视图时使用 WHERE 子句来实现。

  5. 视图的定义可以包含连接操作，用于关联多个表。这可以通过在创建视图时使用 JOIN 子句来实现。

  6. 视图可以嵌套，即一个视图可以基于另一个视图进行定义。但是要注意，嵌套的视图可能会影响查询性能，因此需要谨慎使用。

  7. 视图可以具有授权和权限限制。只有具有足够权限的用户才能够查询和使用视图。

  8. 视图的性能可能会受到影响。复杂的视图查询可能会导致性能下降，特别是当涉及到大量数据和多个表时。在设计视图时应考虑性能方面的问题。

使用视图

视图用CREATE VIEW语句来创建

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

使用SHOW CREATE VIEW viewname；来查看创建视图的语句

SHOW CREATE VIEW view_name;

用DROP删除视图，其语法为DROP VIEW viewname

DROP VIEW view_name;

更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

利用视图简化复杂的联结

使用视图可以简化复杂的联结操作。通过创建视图，将多个表的联结逻辑封装起来，并且在需要时直接使用视图进行查询，而无需编写繁琐的联结语句。

以下是一个示例，展示如何使用视图简化联结操作：

假设你有两个表：orders 和 customers，它们之间通过 customer_id 列进行关联。你想要查询订单信息以及对应的顾客信息。首先，你可以创建一个名为 order_details 的视图，该视图包含订单信息和对应的顾客信息。创建视图的语句如下：

CREATE VIEW order_details AS
SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

在上述语句中，我们通过联结 orders 表和 customers 表来获取订单信息和对应的顾客信息，并将结果保存为 order_details 视图。

一旦视图创建完成，你可以直接使用该视图进行查询，而无需编写联结语句。例如，你可以执行以下查询命令来获取订单信息以及对应的顾客信息：

SELECT * FROM order_details;

这样，你就可以轻松地获取到订单信息和对应的顾客信息，而无需手动编写联结语句。

通过使用视图，你可以将复杂的联结操作封装为一个可重复使用的对象，并提高查询的简洁性和可读性。

用视图重新格式化检索出的数据

使用视图可以重新格式化检索出的数据。在创建视图时，通过查询语句对数据进行适当的格式化操作，以满足需求。

以下是一个示例，展示如何使用视图重新格式化检索出的数据：

假设你有一个名为 employees 的表，其中包含员工的姓名和薪资信息。你想要将这些数据按照一定的格式进行检索，例如将姓名的首字母大写，并显示薪资的千位分隔符。

首先，你可以创建一个名为 formatted_employees 的视图，该视图重新格式化了员工表中的数据。创建视图的语句如下：

CREATE VIEW formatted_employees AS
SELECT 
  CONCAT(UCASE(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))) AS formatted_name,
  FORMAT(salary, 0) AS formatted_salary
FROM employees;

在上述语句中，我们使用了字符串函数 CONCAT 和 UCASE 来将姓名的首字母大写，并使用了字符串函数 LOWER 和 SUBSTRING 来保持其余部分小写。我们还使用了函数 FORMAT 来添加薪资的千位分隔符。

一旦视图创建完成，你可以直接使用该视图进行查询，以获取已经重新格式化的数据。例如，你可以执行以下查询命令来获取重新格式化后的员工数据：

SELECT * FROM formatted_employees;

这样，你就可以获得按照特定格式重新处理过的员工数据。通过使用视图，你可以在数据库中创建一个虚拟表，该表包含经过格式化的数据，以便于查询和使用。

用视图过滤不想要的数据

使用视图可以方便地过滤掉你不想要的数据，只返回符合指定条件的数据。你可以在创建视图时，通过添加筛选条件来过滤数据。

以下是一个示例，展示如何使用视图过滤不想要的数据：

假设你有一个名为 products 的表，其中包含产品的名称、价格和库存信息。你想要创建一个视图，只包含价格高于100的产品信息。

你可以使用以下语句创建一个名为 high_priced_products 的视图：

CREATE VIEW high_priced_products AS
SELECT *
FROM products
WHERE price > 100;

在上述语句中，我们在查询语句中包含了一个筛选条件 WHERE price > 100，它将仅返回价格高于100的产品信息。

创建完成后，你可以直接使用该视图进行查询，以获取满足条件的产品信息。例如，你可以执行以下查询命令来获取价格高于100的产品：

SELECT * FROM high_priced_products;

这样，你就可以获得符合条件的产品信息，而不包括价格低于或等于100的产品。通过使用视图进行数据过滤，你可以创建一个过滤后的虚拟表，使得查询结果更加精确和符合特定需求。

使用视图与计算字段

使用视图和计算字段可以在数据库中创建一个虚拟表，其中包含经过计算的字段。视图可以简化复杂的计算操作，并使其更易于查询和使用。

以下是一个示例，展示如何在视图中使用计算字段：

假设你有一个名为 sales 的表，其中包含销售订单的信息，包括产品名称、单价和销售数量。你想要创建一个视图，包含订单的总价和利润率。

你可以使用以下语句创建一个名为 sales_summary 的视图：

CREATE VIEW sales_summary AS
SELECT 
  product_name,
  unit_price,
  quantity,
  unit_price * quantity AS total_price,
  (unit_price * quantity - cost_price * quantity) / (unit_price * quantity) AS profit_margin
FROM sales;

在上述语句中，我们在查询语句中定义了两个计算字段：total_price 和 profit_margin。total_price 计算了订单的总价，通过将单价与销售数量相乘；profit_margin 计算了订单的利润率，通过使用销售利润与总价的比率来计算。

创建完成后，你可以直接使用该视图进行查询，以获取包含计算字段的信息。例如，你可以执行以下查询命令来获取销售订单的摘要信息：

SELECT * FROM sales_summary;

这样，你就可以获得包含了订单总价和利润率的摘要信息。

通过使用视图和计算字段，你可以在数据库中创建一个方便查询的虚拟表，其中包含经过计算的字段，使得数据分析和报表生成更加便捷。

更新视图

视图的数据能否更新？答案视情况而定

通常，视图是可更新的（即，可以对它们使用INSERT、UPDATE和DELETE）。更新一个视图将更新其基表（可以回忆一下，视图本身没有数据）。如果你对视图增加或删除行，实际上是对其基表增加或删除行

并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：

 分组（使用GROUP BY和HAVING）；

 联结；

 子查询；

 并；

 聚集函数（Min()、Count()、Sum()等）

 DISTINCT；

 导出（计算）列

将视图用于检索 一般，应该将视图用于检索（SELECT语句）

而不用于更新（INSERT、UPDATE和DELETE）

其他功能

使用存储过程

存储过程

存储过程是一组预定义的SQL语句集合，它们被存储在数据库中，并可以作为一个单元来执行。存储过程具有以下优点：

  1. 代码封装：存储过程将一组相关的SQL语句封装在一个单元中，使得代码更易于维护和管理。

  2. 提高性能：存储过程在数据库中预编译，因此执行速度较快。此外，存储过程还可以减少与数据库服务器的通信次数，提高整体性能。

  3. 安全性：通过存储过程，可以限制用户直接访问表，而只允许通过存储过程来执行特定的操作。这样可以增强数据库的安全性。

  4. 代码重用：存储过程可以在不同的应用程序中重用，从而减少了代码的编写量，提高了开发效率。

下面是一个简单的存储过程示例，展示如何创建和执行存储过程：

CREATE PROCEDURE GetProductCount()
BEGIN
    SELECT COUNT(*) AS total_products FROM products;
END;

上述存储过程名为 GetProductCount，它使用SELECT COUNT(*)语句来获取表 products 的总行数，并将结果存储在一个名为 total_products 的列中。

要执行该存储过程，可以使用以下语句：

CALL GetProductCount();

执行存储过程后，将返回一个结果集，其中包含产品总数。

为什么要使用存储过程

使用存储过程有多种好处和用途，以下是一些常见的原因和情景：

  1. 提高性能：存储过程在数据库中预编译，并且可以进行优化。这意味着存储过程的执行速度通常比简单的SQL查询更快。此外，存储过程可以减少与数据库服务器的通信次数，从而提高整体性能。

  2. 代码重用：存储过程可以在不同的应用程序中重复使用。通过将常用的业务逻辑封装在存储过程中，可以避免在每个应用程序中重复编写相同的代码，从而提高开发效率和代码的可维护性。

  3. 安全性控制：存储过程可以用于限制用户对数据库的直接访问，并只允许他们通过存储过程执行特定的操作。这样可以增强数据库的安全性，同时更好地控制数据的访问和修改权限。

  4. 简化复杂的业务逻辑：存储过程可以容纳包含多个SQL语句和逻辑的复杂业务操作。通过将这些操作封装在一个存储过程中，可以简化应用程序中的代码，并使其更易于理解和维护。

  5. 数据一致性和完整性：存储过程可以确保特定的业务规则和约束得到遵守。通过在存储过程中定义相应的数据验证、更新和插入规则，可以确保数据的一致性和完整性。

  6. 数据库层面的错误处理：存储过程允许在数据库层面上进行错误处理和异常处理。可以使用条件语句和异常处理机制来捕获和处理数据库操作中的错误，从而提供更加可靠和稳定的应用程序。

使用存储过程

  1. 执行存储过程：

CALL 存储过程名称;

通过使用 CALL 关键字，可以执行指定名称的存储过程。

  1. 创建存储过程：

CREATE PROCEDURE 存储过程名称(参数列表)
BEGIN
    存储过程体
END;

使用 CREATE PROCEDURE 关键字和合适的语法，可以创建一个新的存储过程。参数列表和存储过程体都是可选的，根据需要进行定义。

  1. 删除存储过程：

DROP PROCEDURE IF EXISTS 存储过程名称;

使用 DROP PROCEDURE 关键字可以删除指定名称的存储过程。IF EXISTS 部分用于检查存储过程是否存在，避免出现错误。

  1. 使用参数：

    存储过程可以接收输入参数、输出参数或输入输出参数，用于动态地处理数据。在创建存储过程时，可以在参数列表中定义这些参数，并在存储过程体中使用它们。

    以下是一个示例，展示如何在存储过程中使用参数：

    CREATE PROCEDURE ProcessData(IN input_param INT, OUT output_param INT)
BEGIN
    -- 在存储过程中使用输入参数
    SELECT column1, column2 
    FROM table
    WHERE condition = input_param;

    -- 在存储过程中使用输出参数
    SET output_param = input_param * 2;
END;

    上述示例中的存储过程 ProcessData 接收一个输入参数 input_param 和一个输出参数 output_param。具体的实现逻辑包括：

    • 使用 IN 关键字声明输入参数 input_param 的类型。
    • 在存储过程体中，可以使用输入参数 input_param 来进行条件查询，根据不同的条件动态处理数据。
    • 使用 OUT 关键字声明输出参数 output_param 的类型。
    • 在存储过程体中，可以对输出参数 output_param 进行赋值操作，以便将结果传递给调用者。

  1. 建立智能存储过程：

    智能存储过程是指具有逻辑判断和条件处理能力的存储过程。您可以在存储过程的主体中添加条件、循环和控制语句来实现更复杂的业务逻辑。

    下面是一个示例，展示如何在存储过程中使用条件语句和控制流程语句：

    CREATE PROCEDURE IntelligentProcedure()
BEGIN
    DECLARE total_sales DECIMAL(10, 2);
    DECLARE bonus DECIMAL(10, 2);

    -- 计算总销售额
    SELECT SUM(sales_amount) INTO total_sales FROM sales;

    -- 根据销售额计算奖金
    IF total_sales > 100000 THEN
        SET bonus = total_sales * 0.1;
    ELSEIF total_sales > 50000 THEN
        SET bonus = total_sales * 0.05;
    ELSE
        SET bonus = 0;
    END IF;

    -- 输出结果
    SELECT bonus AS 'Bonus Amount';
END;

    上述示例中的存储过程 IntelligentProcedure 实现了一个简单的逻辑：根据总销售额的不同范围，计算出相应的奖金金额。具体的实现逻辑包括：

    • 定义两个变量 total_sales 和 bonus 来保存总销售额和奖金金额。
    • 使用 SELECT...INTO 语句将总销售额查询结果赋值给 total_sales 变量。
    • 使用条件语句 IF...ELSEIF...ELSE 根据不同的销售额范围计算奖金金额，并将结果赋值给 bonus 变量。
    • 最后，使用 SELECT 语句输出奖金金额。

    通过这种方式，您可以根据实际需求，在存储过程中添加适当的条件、循环和控制流程语句，以实现更智能的业务逻辑。

  2. 检查存储过程：

SHOW CREATE PROCEDURE 存储过程名称;

使用 SHOW CREATE PROCEDURE 命令可以查看指定存储过程的创建语句。这对于检查存储过程的定义和结构非常有用。

使 用 游 标

游标（Cursor）是一种用于在存储过程或函数中遍历结果集的机制。通过使用游标，您可以逐行处理查询结果，并对每一行执行特定的操作。

以下是一个示例，展示如何在存储过程中使用游标：

CREATE PROCEDURE ProcessData()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE column1 INT;
    DECLARE column2 VARCHAR(50);

    -- 声明游标
    DECLARE cur CURSOR FOR SELECT column1, column2 FROM table;

    -- 定义异常处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN cur;

    -- 循环遍历结果集
    read_loop: LOOP
        -- 读取下一行数据
        FETCH cur INTO column1, column2;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 在此处对每一行数据执行特定操作
        -- 示例：输出列值
        SELECT column1, column2;

    END LOOP;

    -- 关闭游标
    CLOSE cur;
END;

上述示例中的存储过程 ProcessData 使用游标来遍历查询结果集并对每一行数据执行特定的操作。具体的实现逻辑包括：

  • 声明变量 done 来表示游标是否已经遍历完所有行。
  • 声明与查询结果集对应的变量，例如 column1 和 column2。
  • 声明游标 cur 并将查询结果集赋值给游标。
  • 定义异常处理程序，用于在遍历结果集时捕获异常（例如结果集为空）。
  • 打开游标以准备遍历结果集。
  • 使用循环语句 LOOP 和条件判断 IF done THEN LEAVE read_loop; END IF; 来循环遍历结果集，并在每次迭代中读取下一行数据。
  • 在循环体中，您可以对每一行数据执行特定的操作，例如输出列值。
  • 最后，关闭游标以释放资源。

使用触发器

触发器（Trigger）是数据库对象，可以在指定的数据库操作（例如插入、更新、删除）发生时自动执行一段代码。通过使用触发器，您可以在特定的数据变动事件上执行自定义逻辑，实现数据的约束、验证、派生等操作。

以下是一个示例，展示如何创建触发器：

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    -- 在此处编写触发器的逻辑
    -- 示例：自动生成创建时间
    SET NEW.create_time = NOW();
END;

上述示例中，我们创建了一个名为 trigger_name 的触发器，并指定它在表 table_name 上的插入操作之前执行。关键的配置包括：

  • 使用 CREATE TRIGGER 语句创建触发器，并为触发器指定一个名称。
  • 使用 BEFORE INSERT ON table_name 指定触发器在表 table_name 上的插入操作之前执行。您可以根据需要选择不同的触发时机，如 AFTER INSERT、BEFORE UPDATE、AFTER DELETE 等。
  • 使用 FOR EACH ROW 指定触发器对每一行数据都执行。
  • 在 BEGIN 和 END 之间编写触发器的逻辑。在示例中，我们将新插入的行的 create_time 列设置为当前时间。

管理事务处理

在数据库中，事务（Transaction）是一组数据库操作的逻辑单元，要么全部成功执行，要么全部回滚（撤销）。事务处理可以确保数据库在并发操作时维持数据的一致性和完整性。

以下是管理事务处理的示例：

-- 启动事务
START TRANSACTION;

-- 在此处执行数据库操作
-- 示例：插入数据
INSERT INTO table_name (column1, column2) VALUES (value1, value2);

-- 示例：更新数据
UPDATE table_name SET column1 = new_value WHERE condition;

-- 示例：删除数据
DELETE FROM table_name WHERE condition;

-- 提交事务
COMMIT;

-- 或者回滚事务
ROLLBACK;

上述示例中，我们使用 START TRANSACTION 开始一个事务，并在 COMMIT 处提交事务。如果需要回滚事务则可以使用 ROLLBACK。

在事务内，我们可以执行各种数据库操作，如插入、更新和删除等。这些操作将被视为一个逻辑单元，要么全部成功提交，要么全部回滚。

在实际应用中，为了确保事务的正确性和一致性，还应考虑以下几点：

  • 使用异常处理（例如使用存储过程或触发器）来捕获错误并决定是否回滚事务。
  • 设置适当的隔离级别，以控制并发访问时数据的可见性和一致性。
  • 避免长时间持有事务，以减少数据库锁的冲突和资源占用。

全球化和本地化

全球化（Globalization）和本地化（Localization）是软件开发领域中与跨国界应用程序的适配性和可用性相关的两个概念。字符集和校对顺序在全球化和本地化中起着重要的作用。

  1. 字符集（Character Set）：

    • 字符集定义了一个字符到数字代码的映射规则。不同的字符集支持不同的字符范围，如 ASCII、Unicode 和 UTF-8。
    • 全球化应用程序应该使用支持广泛的字符集，如UTF-8，以确保能够正确处理各种语言的字符和文本。

  2. 校对顺序（Collation Order）：

    • 校对顺序定义了字符排序的规则和算法。它决定了在排序和比较字符串时字符的顺序和优先级。
    • 不同的语言和地区使用不同的校对顺序来满足其特定的语言和文化需求。例如，英文通常使用字典排序，而中文通常使用拼音排序。
    • 在本地化过程中，校对顺序需要根据特定的语言和地区进行配置，以确保正确的排序和比较。

在使用字符集和校对顺序时，需要注意以下几点：

  1. 数据库配置：

    • 在数据库中，选择适当的字符集和校对顺序进行配置。不同的数据库管理系统（如MySQL、Oracle等）提供了相关配置选项。
    • 确保数据库中使用的字符集和校对顺序与应用程序需要处理的语言和文本一致。

  2. 应用程序开发：

    • 在应用程序开发中，使用与目标受众语言和文化相匹配的字符集和校对顺序进行编码和处理文本。
    • 避免硬编码字符集和校对顺序，而是使用配置文件或在运行时动态设置。

  3. 测试和验证：

    • 在全球化和本地化过程中，进行充分的测试和验证以确保字符集和校对顺序的正确性。
    • 包括输入输出测试、排序和比较测试，以及特定语言和文化下的正确性验证。

安 全 管 理

访问控制

  1. 访问控制：
    • MySQL使用权限模型来控制用户对数据库的访问。可以使用GRANT语句为用户分配权限。
    • GRANT语句用于授予用户特定的权限（如SELECT、INSERT、UPDATE、DELETE等）和访问级别（全局、数据库、表或列级别）。
    • 使用REVOKE语句可以撤销已经授权的权限。

管理用户

  1. 管理用户：

    • 创建用户：可以使用CREATE USER语句创建新用户。示例：

      CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

      可以指定用户名、主机名以及密码。

    • 删除用户：使用DROP USER语句删除指定的用户。示例：

      DROP USER 'username'@'localhost';

  • 设置访问权限：使用GRANT语句为用户分配特定权限。示例：

    GRANT SELECT, INSERT ON database.* TO 'username'@'localhost';

  • 更改口令：使用ALTER USER语句或SET PASSWORD语句更改用户的口令。示例：

    ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

数据库维护

数据库维护是确保数据库系统正常运行和保障数据安全性的重要工作。下面是一些常见的数据库维护任务及相应的方法：

备份数据：

  • MySQL提供多种备份方法，包括物理备份和逻辑备份。常用的备份工具有mysqldump和mysqlpump。

  • 使用mysqldump命令可以导出整个数据库或指定表的内容。例如，

    mysqldump -u <username> -p <databasename> > backup.sql

    将整个数据库导出到backup.sql文件中。

  • mysqlpump是MySQL 5.7及更新版本提供的备份工具，具有更高的性能和更多的选项。使用方法与mysqldump类似。

进行数据库维护：

  • 定期优化数据库以提高性能，可以使用OPTIMIZE TABLE命令来优化表。
  • 清理不必要的或过期的数据，可以使用DELETE语句或TRUNCATE TABLE命令。
  • 检查并修复损坏的表，可以使用CHECK TABLE和REPAIR TABLE命令。

诊断启动问题：

  • 如果数据库无法启动，首先可以检查错误日志文件，位于MySQL数据目录下的error.log文件，了解具体的错误信息。
  • 可以使用systemctl命令（适用于使用systemd的系统）或service命令（适用于其他系统）来启动、停止、重启和查看MySQL服务的状态。例如，systemctl start mysql启动MySQL服务。

查看日志文件：

  • MySQL记录了各种事件和错误信息，这些信息可以在错误日志文件中找到。错误日志的路径和文件名在MySQL配置文件中进行配置。
  • 可以使用文本编辑器打开错误日志文件进行查看，或者使用tail命令实时查看最新的日志内容。例如，tail -f /var/log/mysql/error.log。

改 善 性 能

  1. 优化查询：

    • 确保正确使用索引：合理设计表结构和索引，避免全表扫描。可以使用EXPLAIN语句来分析查询的执行计划，以确定是否有效使用了索引。
    • 编写高效的查询语句：避免不必要的连接和子查询，优化复杂的SQL语句，提高查询的效率。
    • 使用适当的数据类型：避免使用过大或不合适的数据类型，减少存储空间和I/O操作。

  2. 调整配置：

    • 配置合适的缓冲区大小：如innodb_buffer_pool_size和key_buffer_size等，根据系统资源和负载情况进行调整，提高内存利用率。
    • 优化连接池设置：增加或减少max_connections参数，根据实际需求合理配置连接数。
    • 调整日志设置：合理配置日志的级别和写入方式，避免过多的日志写入对性能造成影响。

  3. 硬件升级：

    • 提升硬件性能：增加CPU核心数、内存容量和磁盘I/O速度，以提高系统的处理能力和响应速度。
    • 使用SSD存储：将磁盘替换为SSD可以显著提升I/O性能，加快读写速度。

  4. 实施缓存策略：

    • 使用查询缓存：启用适当的查询缓存（如果可用），可减少数据库重复查询的开销。
    • 使用应用层缓存：如Redis或Memcached等，将热门数据缓存在内存中，减少对数据库的访问。

  5. 定期维护和监控：

    • 定期进行数据库优化和碎片整理：使用OPTIMIZE TABLE命令优化表，清理无效数据和索引碎片。
    • 监控数据库性能：使用MySQL自带的性能监控工具或第三方监控工具，及时发现瓶颈并采取相应措施。

MYSQL附录

MYSQL必知必会导图

mysql必知必会

查看帮助文档

  • 在MySQL命令行客户端中，可以通过输入help或\h命令获取基本查询语法的帮助信息。
  • 若要了解特定命令或函数的详细信息，可以使用help命令后面接上相应的命令或函数，例如：help SELECT。
  • 还可以使用man命令（仅限于UNIX/Linux系统），如man mysql或man mysqldump来查看相关的手册页。

注意要点

distinct关键字：作用于所有的列，不仅仅是跟在其后的那一列。eg: select distinct vend_id,prod_price from products;

除非指定的两列完全相同，否则所有的列都会被检索出来。

order by 子句的位置：必须是语句中最后一条的句子，否则会输出错误信息；order by 位于where之后

何时使用引号：单引号用来限制字符串。 将数值与字符串进行比较，就需要限定引号。

in：where子句中用来指定要匹配值的清单关键字，功能与or相同

通配符：

1有一种情况吧通配符放在搜索模式的中间很有用，根据邮件地址的一部分来查找电子邮件eg：WHERE email like ‘b%@forta.com’

2通配符%不可以匹配null值

3 不要过度使用通配符，尽量不要把它们放在搜索模式的开始处（导致搜索慢），要注意位置，否则会报错

计算字段

trim（）：去掉两边的空格 rtrim ltrim 右和左

select concat（x, ‘(‘, y, ‘)’) as z from X order by x; as最好使用它

常用的文本处理函数

soundex ：将任何文本串转化为描述其语音表示的字母数字模式算法

avg函数忽略null 的行

count函数 ：count（*）对行中所有的书进行计算，不管是包含的还是空值 count（）则忽略

distinct不能用于count（*） 必须使用列名 不能用于计算或表达式

别名：在指定别名以包含某个聚集函数的结果时，不应该使用表中的实际列名；表别名只在查询中使用，与列别名不一样，表别名不返回到客户机

结语

感谢你的阅读，祝你在 MySQL 的学习和实践中取得更大的成功！ 让我们在 MySQL 的海洋中探索更多、学习更多^_^

赏

   转载规则


《[两万字教程]MYSQL必知必会一站式完全教程》 由 Bevis23 采用 知识共享署名 4.0 国际许可协议 进行许可。
 上一篇
渗透-主动信息收集 渗透-主动信息收集
主动信息收集什么是主动信息收集主动信息收集指的是主体根据自身需求和目标，通过主动搜集、查询、调查等方式，积极主动地获取所需的信息。在主动信息收集中，个体或组织作为信息的主动者，主动发起搜索和查询行为，以获得所需的有用信息 主动信息收集的原
2023-07-15 网络安全
网络安全 技术分享 渗透测试 OSI
下一篇 
frp内网穿透：打开局域网的远程大门 frp内网穿透：打开局域网的远程大门
引言在现代科技发展的背景下，越来越多的人需要远程访问他们家庭或办公室网络中的设备。然而，由于网络安全限制，我们常常无法直接从外部访问局域网中的设备。这就是为什么内网穿透技术变得如此重要的原因。本文将向您介绍一种强大的内网穿透工具——frp，
2023-07-06 技术分享
工具 网络安全 技术分享 frp
  目录
切换