mysql，sql函数，存储过程，事务，权限

╰+哭是因爲堅強的太久メ 2023-07-22 09:05 94阅读 0赞

# 一、通配符 #

通配符也是用在过滤语句中，但它只能用于文本字段。

*  **%** 匹配 >=0 个任意字符；
 *  **\_** 匹配 ==1 个任意字符；
 *  **\[ \]** 可以匹配集合内的字符，例如 \[ab\] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。

使用 Like 来进行通配符匹配。

SELECT *
    FROM mytable
    WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

不要滥用通配符，通配符位于开头处匹配会非常慢。

# 二、计算字段/删除操作 #

计算字段通常需要使用 **AS** 来取别名，否则输出的时候字段名为计算表达式。

SELECT col1 * col2 AS alias
    FROM mytable;

**CONCAT()** 用于连接两个字段。

### delete ###

删除整张表的数据或者部分数据：每次删除一行，都在事务日志中为所删除的每行记录一项。事务提交之后才生效，可以回滚；删除表中数据而不删除表的结构(定义)。

### truncate ###

只能操作表，一次性地从表中删除所有的数据：通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放；操作立即生效,自动提交，不能回滚，操作不触发trigger；删除内容、释放空间但不删除表的结构(定义)。

### drop ###

1、drop语句将删除表的结构，触发器(trigger)：drop也属于DDL语言，立即执行，执行速度最快；删除内容和定义，释放空间。

区别
      1、表和索引所占空间： 
        TRUNCATE操作，将表和索引所占用的空间会恢复到初始大小； 
    　　DELETE操作不会减少表或索引所占用的空间； 
    　　DROP语句将表所占用的空间全释放掉。 
    　　
    2、应用范围： 
    　　TRUNCATE 只能对table； 
    　　DELETE可以是table和view。
    3、执行速度： drop > truncate > delete
    4、delete from删空表后，会保留一个空的页，truncate在表中不会留有任何页。
    6、当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。truncate始终锁定表和页，而不是锁定各行。
    7、如果有identity产生的自增id列，delete from后仍然从上次的数开始增加，即种子不变； 
    使用truncate删除之后，种子会恢复到初始值。

# 三、函数 #

各个 DBMS 的函数都是不相同的，因此不可移植，以下主要是 MySQL 的函数。直接当作已经实现的方法使用就行。

## 汇总 ##

<table> 
 <thead> 
  <tr> 
   <th align="center">函 数</th> 
   <th align="center">说 明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">AVG()</td> 
   <td align="center">返回某列的平均值</td> 
  </tr> 
  <tr> 
   <td align="center">COUNT()</td> 
   <td align="center">返回某列的行数</td> 
  </tr> 
  <tr> 
   <td align="center">MAX()</td> 
   <td align="center">返回某列的最大值</td> 
  </tr> 
  <tr> 
   <td align="center">MIN()</td> 
   <td align="center">返回某列的最小值</td> 
  </tr> 
  <tr> 
   <td align="center">SUM()</td> 
   <td align="center">返回某列值之和</td> 
  </tr> 
 </tbody> 
</table>

AVG() 会忽略 NULL 行。

使用 DISTINCT 可以汇总不同的值。

SELECT AVG(DISTINCT col1) AS avg_col
    FROM mytable;

# 四、分组 #

把具有相同的数据值的行放在同一组中，使用汇总函数进行处理，例如求分组数据的平均值等。

指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。

GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

SELECT col, COUNT(*) AS num
    FROM mytable
    GROUP BY col
    ORDER BY num;

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤，也就是说顺序上面where优先于having

SELECT col, COUNT(*) AS num
    FROM mytable
    WHERE col > 2
    GROUP BY col
    HAVING num >= 2;

分组规定：

*  GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；

# 五、连接 #

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

连接可以替换子查询，并且比子查询的效率一般会更快。

可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。

## 内连接 ##

内连接：又称等值连接，使用 INNER JOIN 关键字；也可以直接使用where。 结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。

SELECT A.value, B.value
    FROM tablea AS A INNER JOIN tableb AS B
    ON A.key = B.key;

## 外连接 ##

左外连接：如果左表的某行在右表中没有匹配行，则将为右表返回空值。右外连接相反。

全外链接：在结果中除了显示满足连接的条件的行外，还显示了join两侧表中所有满足检索条件的行。

# 六、组合查询 #

使用 **UNION** 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。

每个查询必须包含相同的列、表达式和聚集函数。

默认会去除相同行，如果需要保留相同行，使用 UNION ALL。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

SELECT col
    FROM mytable
    WHERE col = 1
    UNION
    SELECT col
    FROM mytable
    WHERE col =2;

# 七、存储过程 #

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。类比面向对象编程的类，可以看成是对一系列 SQL 操作的批处理。

使用存储过程的好处：

*  代码封装，保证了一定的安全性；
 *  代码复用；
 *  由于是预先编译，因此具有很高的性能。

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

包含 in、out 和 inout 三种参数。

给变量赋值都需要用 select into 语句。

每次只能给一个变量赋值，不支持集合的操作。

delimiter //
    
    create procedure myprocedure( out ret int )
        begin
            declare y int;
            select sum(col1)
            from mytable
            into y;
            select y*y into ret;
        end //
    
    delimiter ;

call myprocedure(@ret);
    select @ret;

# 八、触发器 #

触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。

触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

INSERT 触发器包含一个名为 NEW 的虚拟表。

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
    FOR EACH ROW SELECT NEW.col into @result;
    
    SELECT @result; -- 获取结果

DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。

UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

# 九、事务 #

**事物特性**

原子性(A)：事务是最小单位，不可再分
    一致性(C)：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
    隔离性(I)：事务A和事务B之间具有隔离性
    持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

**基本术语**

*  开启事务（Start Transaction）：任何一条DML语句(insert、update、delete)执行，标志事务开启。
 *  回退（rollback）指撤销指定 SQL 语句的过程；
 *  提交（commit）指将未存储的 SQL 语句结果写入数据库表，可改变底层数据库数据。
 *  保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。在事物进行过程中，未结束之前，DML语句是不会更改底层数据，只是将历史操作记录一下，在内存中完成记录。只有在事物结束的时候，而且是成功的结束的时候，才会修改底层硬盘文件中的数据。

如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。

START TRANSACTION
    // ...
    SAVEPOINT delete1
    // ...
    ROLLBACK TO delete1
    // ...
    COMMIT

**事务四大特性之一隔离性(isolation)**

1 事物A和事物B之间具有一定的隔离性
    2 隔离性有隔离级别(4个)
        读未提交：read uncommitted
        读已提交：read committed
        可重复读：repeatable read
        串行化：serializable
    3 事务隔离级别的作用范围分为两种： 
        全局级：对所有的会话有效 
        会话级：只对当前的会话有效

# 十、权限管理 #

MySQL 的账户信息保存在 mysql 这个数据库中。

USE mysql;
    SELECT user FROM user;

**创建账户**

新创建的账户没有任何权限。

CREATE USER myuser IDENTIFIED BY 'mypassword';

**修改账户名**

RENAME USER myuser TO newuser;

**删除账户**

DROP USER myuser;

**查看权限**

SHOW GRANTS FOR myuser;