• 什么是关系型数据库？
• 关系数据库模型
• 层次模型
• 网状模型
• 关系模型
• SQL
• MySQL
• 基础架构及SQL执行过程
• 查询/修改/定义 语句
• PROCEDURE, FUNCTION, TRIGGER
• Trigger
• Procedure and Function
• EVENT
• VIEW
• MySQL索引（Index）
• 什么是索引？
• 索引的优缺点
• 语句
• 索引类型
• 正确使用索引的建议
• MySQL事务（Transaction）
• MySQL锁（Lock）
• WINDOW FUNCTION

什么是关系型数据库？

关系数据库模型

层次模型

网状模型

关系模型

• 关系模型把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表。
• 特性（列）指定了数据类型，并且每条记录（或每行）都包含该特定数据类型的值。关系型数据库中的所有表都有一个称为主键的特性（该特性是行的唯一标识符），并且每行都可以用于使用外键 （对另一个现有表的主键的引用）创建不同表之间的关系。
• 随着时间推移，理解和使用起来最简单的关系模型逐渐占据主流应用。
• 对于一个关系表，除了定义每一列的名称外，还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等
• 当下的主流关系模型数据库
  • 商用数据库，例如：Oracle，SQL Server，DB2等；
  • 开源数据库，例如：MySQL，PostgreSQL等；
  • 桌面数据库，以微软Access为代表，适合桌面应用程序使用；
  • 嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。

SQL

• DDL（Data Definition Language）：DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。
• DML（Data Manipulation Language）：DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。
• DQL（Data Query Language）：DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

MySQL

基础架构及SQL执行过程

• Server层

  主要包括连接器、查询缓存（MySQL8.0移除）、分析器、优化器、执行器等，所有的跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图、函数等，还有一个通用的日志模块binlog。

• 存储引擎

  主要负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB、MyISAM、Memory等多个存储引擎，其中InnnoDB有属于自己的日志模块。现在最常用的存储引擎是InnoDB，它从MySQL5.5.5版本开始被当做默认的存储引擎了。

查询/修改/定义 语句

WHERE LIKE Operator

选取一列 替换/截取/删除/切割 字段名

select

• 替换法 replace(列名, '被替换部分','替换后的结果') — device_id, replace(blog_url,'http:/url/','') as user_name
• 截取法 substr(列名, start_point, length可选参数) — device_id, substr(blog_url,11,length(blog_url)-10) as user_name
• 删除法 trim('被删除字段' from 列名) — device_id, trim('http:/url/' from blog_url) as user_name
• 字段切割法 substring_index(string, '切割标志', n)
  • 当n大于0时取第n个分隔符(n从1开始)之后的全部内容
  • 当n小于0时取倒数第n个分隔符(n从-1开始)之前的全部内容

  — device_id, substring_index(blog_url,'/',-1) as user_name

from user_submit;

• UNION
• DATE函数

PROCEDURE, FUNCTION, TRIGGER

Trigger

• 触发器（TRIGGER）：是由事件来触发某个操作。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。
• 触发事件：INSERT语句、UPDATE语句和DELETE语句。
• 创建触发器
  创建只有一个执行语句的触发器

  CREATE TRIGGER trigger_name trigger_time trigger_event 
  ON tb_name FOR EACH ROW trigger_stmt
  /**
  	trigger_name：触发器的名称
  	tirgger_time：触发时机，为BEFORE或者AFTER
  	trigger_event：触发事件，为INSERT、DELETE或者UPDATE
  	tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
  	trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
  	所以可以说MySQL创建以下六种触发器：
  	BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
  	AFTER INSERT,AFTER DELETE,AFTER UPDATE **/

  创建有多个执行语句的触发器

  DELIMITER ||
  mysql> CREATE TRIGGER demo BEFORE DELETE
      -> ON users FOR EACH ROW
      -> BEGIN
      -> INSERT INTO logs VALUES(NOW());
      -> INSERT INTO logs VALUES(NOW());
      -> END
      -> ||
  Query OK, 0 rows affected (0.06 sec)
  mysql> DELIMITER ;

  其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开。

  tips：一般情况下，mysql默认是以“；”作为结束执行语句，与触发器中需要的分行起冲突。为解决此问题可用DELIMITER，如：DELIMITER“｜｜”，可以将结束符号变成“｜｜”。

  当触发器创建完成后，可以用DELIMITER“；”，来将结束符号变回“；”

Procedure and Function

• 存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。外部程序可以直接调用数据库里面定义好的存储过程，另外数据库内部的触发器(trigger)、或者其他存储过程也可以调用它。
• Function是一种可重用的代码块，可以接受零个或多个参数，并返回一个值。在调用函数时，可以将参数传递给函数，并且函数将返回一个值。函数通常用于执行某些计算或操作，并返回结果给调用方。在函数内部，可以使用SELECT语句查询数据并进行计算。例如，SUM()函数就是一个常见的SQL函数。
• 区别

  Function和Procedure的主要区别在于其返回值和执行方式。Function返回一个值，而Procedure不需要返回值，或者可以返回多个值。Procedure执行一系列的操作和任务，而Function通常用于计算和数据查询。

EVENT

• Event名称：用于标识Event对象的名称。
• Schedule：定义Event执行的时间和间隔。包括开始时间、重复间隔、重复次数等。
• SQL语句或存储过程：定义Event要执行的SQL语句或存储过程。

CREATE EVENT my_event
ON SCHEDULE
EVERY 1 DAY
STARTS '2023-03-22 00:00:00'
DO
BEGIN
   DELETE FROM my_table WHERE timestamp < DATE_SUB(NOW(), INTERVAL 7 DAY);
END;
/*
上面的示例创建了一个名为“my_event”的Event对象，它将在每天执行一次，并删除“mytable”表中7天之前的记录。可以看到，我们在Event对象中使用了SQL语句来执行具体的操作。
*/

VIEW

• View是一种虚拟表格，不存储数据，而是根据查询条件从基础表中获取数据。
• View提供了一种方便和安全的方式来访问和处理数据，可以通过View来隐藏基础表的细节、简化复杂查询、限制访问等。
• View可以基于单个表格或多个表格的联接操作创建，可以包含各种复杂的查询语句，可以使用各种函数、运算符和表达式。
• View可以像普通表格一样使用SELECT语句查询数据，也可以用于更新、插入和删除数据，但是需要满足一定的条件和限制。

CREATE VIEW my_view AS
SELECT column1, column2, ...
FROM my_table
WHERE condition;
/*
上面的示例创建了一个名为“my_view”的View对象，
它基于“my_table”表格执行一个SELECT查询，只返回满足特定条件的列。*/

/* “my_view”现在可以像表格一样使用SELECT语句查询数据，例如：*/
SELECT column1, column2, ...
FROM my_view
WHERE condition;

MySQL索引（Index）

什么是索引？

• 索引是一种用于快速查询和检索数据的数据结构，其本质可以看作是一种排好序的数据结构。
• 不使用索引，数据库引擎将遍历整个表。
  • 从表面上看，索引类似于书的目录，在没有目录的情况下，要从书中查找某项内容就必须阅读全文，而有了目录之后，通过页码就可以很快定位到相关内容。
  • 从本质上看，索引是根据表的一个或者多个字段生成的子表，该子表中的数据已经进行了排序。子表除了包含指定字段中的数据，还包含一个 rowid 列，用于存储当前记录在原始表中的位置。用户无法看到索引，它只是用来加快查询速度。
• 索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。
• 索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如gender列，大约一半的记录值是M另一半是F因此，对该列创建索引就没有意义。

索引的优缺点

• 使用索引可以大大加快数据的检索速度（大大减少检索的数据量），这也是创建索引的最主要的原因。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

• 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
• 索引需要使用物理文件存储，也会耗费一定空间。

语句

CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

1. unique | fulltext 为可选参数，分别表示唯一索引、全文索引
2. index和key为同义词，两者作用相同，用来指定创建索引
3. col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
4. index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
5. length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
6. asc或desc指定升序或降序的索引值存储

索引类型

1. 普通索引

   是最基本的索引，它没有任何限制。

   它有以下几种创建方式

   /* 直接创建索引 */
   CREATE INDEX index_name ON table(column(length))
   
   /* 修改表结构的方式添加索引 */
   ALTER TABLE table_name ADD INDEX index_name ON (column(length))
   
   /* 创建表的时候同时创建索引 */
   CREATE TABLE `table` (
       `id` int(11) NOT NULL AUTO_INCREMENT ,
       `title` char(255) CHARACTER NOT NULL ,
       `content` text CHARACTER NULL ,
       `time` int(10) NULL DEFAULT NULL ,
       PRIMARY KEY (`id`),
       INDEX index_name (title(length))
   )

2. 唯一索引

   与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

   它有以下几种创建方式

   /* 创建唯一索引 */
   CREATE UNIQUE INDEX indexName ON table(column(length))
   
   /* 修改表结构 */
   ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
   
   /* 创建表的时候直接指定 */
   CREATE TABLE `table` (
       `id` int(11) NOT NULL AUTO_INCREMENT ,
       `title` char(255) CHARACTER NOT NULL ,
       `content` text CHARACTER NULL ,
       `time` int(10) NULL DEFAULT NULL ,
       UNIQUE indexName (title(length))
   );

3. 主键索引

   是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。

   一般是在建表的时候同时创建主键索引。

   CREATE TABLE `table` (
       `id` int(11) NOT NULL AUTO_INCREMENT ,
       `title` char(255) NOT NULL ,
       PRIMARY KEY (`id`)
   );

   在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

4. 组合索引

   指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。

   使用组合索引时遵循最左前缀集合。

   ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

5. 全文索引

   主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。

   值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

   CREATE FULLTEXT INDEX index_content ON table_name(content)

正确使用索引的建议

MySQL事务（Transaction）

MySQL锁（Lock）

WINDOW FUNCTION

1. Window Function是指一种用于对结果集执行计算操作的函数，它可以在不影响查询结果的情况下对查询结果集中的某些行进行计算和排名。
2. Window Function的主要特点包括：
   • Window Function通过在查询结果集的特定列或窗口中执行计算操作来实现功能。常见的Window Function包括SUM、AVG、MAX、MIN、ROW_NUMBER等。
   • Window Function可以根据查询结果集中的某些列进行分组、排序、分区等操作，并且可以基于分组和排序的结果执行计算操作。
   • Window Function可以通过OVER子句指定窗口的范围和排序方式，可以使用ROWS或RANGE关键字指定窗口的边界，也可以使用ORDER BY子句指定窗口内的排序方式。
   • Window Function可以用于生成报表、计算比率、排名等各种操作，可以让查询更加灵活和高效。
3. More Examples

   SQL Window Functions | Advanced SQL - Mode

   This lesson of the SQL tutorial for data analysis covers SQL windowing functions such as ROW_NUMBER(), NTILE, LAG, and LEAD.

   https://mode.com/sql-tutorial/sql-window-functions/

4. 教学