MySql知识点合集1

一、SQL语言分类

数据查询语言（DQL）：是由SELECT子句，FROM子句，WHERE子句组成的查询块

数据操纵语言（DML）: 用于操作数据库中的数据。SELECT(查询)、 INSERT(插入)、 UPDATE(更新) 、DELETE(删除）

数据定义语言（DDL）：用于定义和管理数据库结构。CREATE(创建数据库或表或索引）、ALTER(修改表或者数据库）、DROP(删除表或索引）、TRUNCATE：清空表

数据控制语言（DCL）：用于定义数据库用户的访问权限和安全级别。GRANT(赋予用户权限）、 REVOKE(收回权限） 、DENY(禁止权限)

事务控制语言（TCL）：用于管理事务处理。SAVEPOINT (设置保存点）、ROLLBACK (回滚)、 COMMIT(提交)

二、授予和撤销权限

已知数据库学生成绩及学生表，现授予用户USER1在学生表上的SELECT权限

授予grant to ，回收revoke from

A   USE 学生成绩 GO GRANT SELECT ON 学生表 TO USER1

授权语法结构： USE 数据库名 GO GRANT 权限 ON 表名 TO 用户名

B   USE 学生成绩 GO REVOKE SELECT ON 学生表 TO USER1

收回权限语法结构： USE 数据库名 GO REVOKE 权限 ON 表名 FROM 用户名

三、GROUP BY

在 SQL 中，GROUP BY 子句用于对查询结果按照指定的列进行分组，并对每个组应用聚合函数（如 SUM, COUNT, AVG 等）。在 GROUP BY 子句中指定的列名就是聚合键（或分组键）。

聚合键（Grouping Key）：在 GROUP BY 子句中指定的列名或表达式。SQL 根据这些列的值将查询结果分成多个组。每个组中具有相同的聚合键值的行被分为一组。

例如，假设有一个 orders 表，其中包含订单信息，包括 customer_id, order_date, amount 等列。如果要按照 customer_id 分组，并计算每个客户的订单总金额，可以这样使用 GROUP BY：

SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id;

在这个例子中，customer_id 就是聚合键。GROUP BY customer_id 意味着数据库将所有行按照 customer_id 的值进行分组，并对每个组应用 SUM(amount) 聚合函数来计算总金额。

 四、用户定义函数UDF

（1）用户定义函数不用于执行修改数据库状态的操作，

（2）用户定义函数属于数据库，只能在该数据库下调用，

（3）与系统函数一样，用户定义函数可以从查询中调用，其中用户定义函数可以在SQL查询语句的各个部分（如SELECT、WHERE、GROUP BY、ORDER BY等）中被调用和使用。这使得可以在查询中利用函数的功能来执行特定的计算、转换或逻辑操作。系统函数是数据库系统提供的预定义函数，如SUM()、COUNT()、DATE_FORMAT()等。这些函数可以直接用于查询中，例如计算总和、统计行数、格式化日期等操作。

（4）标量函数和存储过程一样，可以使用EXECUTE语句执行。其中标量函数是一种用户定义函数，通常用于执行简单的计算或转换，并返回单个值（标量）。例如，可以计算两个数的和、格式化日期或者执行简单的逻辑运算。存储过程是一段预先编译并存储在数据库中的程序代码，可以执行复杂的逻辑操作、包含控制流程和条件逻辑，并可以接受输入参数和返回输出参数。

五、REGEXP: 正则表达式匹配运算符

SELECT * FROM Person Address REGEXP '^[^CO]';

表示筛查出所有匹配[^CO]的项，而[^CO]表示匹配任何不包含C、O的字符串

 第一个^: 正则表达式中的锚定符，表示行的开头

[^CO]: 方括号内的 ^ 表示取反，[^CO] 表示不以 'C' 或 'O' 开头的字符。

将从表 Person 中选择所有行，这些行的 Address 列值不以 'C' 或 'O' 开头。 

六、where和having的区别 

WHERE不能接聚合函数（MAX、MIN、COUNT、SUM、AVG等）;HAVING后可以接聚合函数；

WHERE用在GROUP BY前，先过滤后分组；

HAVING用在GROUP BY之后，先分组后过滤，且使用HAVING一定要用到GRUOP BY，但用到GROUP BY 不一定有HAVING。

七、不同的SELECT @@（）表示

@@ERROR：返回执行的上一个 Transact-SQLTransact-SQL 语句的错误号。

@@IDENTITY：返回自增id。

@@ROWCOUNT：返回受上一个SQL语句影响的行数。

@@MAX_CONNECTIONS：返回最大用户连接数。

八、不同的存储过程

sp_helpindex: 这个存储过程用于返回指定表或视图的索引信息，包括索引名称、索引描述、键列等信息。

 sp_help:报告有关数据库对象的信息，语法及应用错误。查看有关数据库对象的摘要信息，

sp_helpdb:返回指定数据库或者全部数据库的信息

sp_helptext:这个存储过程用于返回指定对象（如存储过程、视图、触发器等）的定义文本。

九、隐式事务和显式事务 

隐式事务 是指每条单独的 DML 语句（如 SELECT、INSERT、UPDATE、DELETE）都被 SQL Server 自动地作为一个独立的事务处理。如果该语句成功执行，则事务自动提交。如果执行失败，则事务自动回滚。

UPDATE books SET shelf = shelf - 2 WHERE type = 'tool';

上面的 UPDATE 语句会被 SQL Server 自动地作为一个事务处理。如果更新成功，SQL Server 会自动提交该事务。如果更新失败，SQL Server 会自动回滚该事务。

显式事务 是指使用 BEGIN TRANSACTION 语句明确地开始一个事务，并且需要显式地使用 COMMIT 或 ROLLBACK 来结束该事务。

BEGIN TRANSACTION;
UPDATE books SET shelf = shelf - 2 WHERE type = 'tool';
 
-- 检查是否成功
IF @@ERROR = 0
BEGIN
    COMMIT TRANSACTION;
END
ELSE
BEGIN
    ROLLBACK TRANSACTION;
END

在上面的代码中，事务从 BEGIN TRANSACTION 开始。如果 UPDATE 语句执行成功，则提交事务（COMMIT TRANSACTION），如果执行失败，则回滚事务（ROLLBACK TRANSACTION）。

• 隐式事务：每个 DML 语句都是一个独立的事务，自动提交或回滚。
• 显式事务：由 BEGIN TRANSACTION 明确开始，需要手动提交或回滚事务。