数据库相关

PHP(Mr. Hamster)

已于 2024-06-12 23:30:44 修改

阅读量2.3k

点赞数

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

于 2023-06-25 22:32:54 首次发布

本文链接：https://blog.csdn.net/lxp199741/article/details/127211567

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

文章详细介绍了SQL的不同语言分类，如DDL用于数据定义，DML用于数据操作，DCL用于权限管理，DQL用于数据查询，以及TCL用于事务控制。同时，讨论了SQL优化策略，包括减少数据访问、利用索引等，并提到了如何创建和管理索引。此外，还涵盖了视图、存储过程、触发器的概念和使用，以及事务的ACID特性、并发读问题和不同隔离级别。

摘要由CSDN通过智能技术生成

理论

结构化查询语言（Structured Query Language）

SQL分类

数据定义语言DDL(Data Definition Language)：create alter drop

数据操作语言DML(Data Manipulation Language):insert delete update

数据控制语言DCL(Data Control Language)：用户管理，权限管理

数据查询语言DQL(Data Query Language):select from where

事务控制语言TCL：

SQL优化方案

减少数据访问：

返回更少的数据：

减少交互次数：

减少服务器CPU开销：

利用更多资源：

分析SQL语句，是否加载不必要的字段/数据

分析SQL执行计划，思考可能的优化点，索引是否命中等

查看SQL设计的表结构，索引信息

如果SQL很复杂，优化SQL结构

按照可能的优化点执行表结构变化，增加索引，SQl，改写等操作

查看优化后的执行时间和执行计划

如果表数据量太大，考虑分表

利用缓存较少查询次数

慢SQl优化

1、WHERE 子句中：where 表之间的连接必须写在其他 Where 条件之前，那些可以过滤掉最大数量记录的条件必须写在 Where 子句的末尾.HAVING 最后。

2、用 EXISTS 替代 IN 、用 NOT EXISTS 替代 NOT IN 。

3、避免在索引列上使用计算

4、避免在索引列上使用 IS NULL 和 IS NOT NULL

5、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by涉及的列上建立索引。

6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

7、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

三范式

第一范式：每列不可再拆分

第二范式：范式一基础上，非主键列完全依赖于主键列，而不能是依赖于主键的一部分

第三范式：范式二基础上，非主键列只依赖于主键，不依赖于其他非主键

反范式：一定程度/情况下需要违反范式规则，否则数据会拆分过细产生过多冗余数据或设计复杂度过深不易理解结构工作时间过长等问题，根据具体情况具体分析。

聚合函数

sum

count

avg

min

max

分支语法

语法一：

CASE WHEN field_name >= * THEN 比较结果
    WHEN  ~
    WHEN  ~
    ELSE  ~
END

语法二：

CASE field_name
    WHEN 条件判断 THEN 输出结果
    WHEN ~
END

定义全局变量

SET @name = value;

索引

B+树结构，三次IO命中节点IO次数少

主键索引：PRIMARY KEY

特殊的唯一索引，不允许有空值

唯一索引：UNIQUE

普通索引：INDEX

全文索引：FULLTEXT

创建索引：

CREATE index index_name on table_name(field_name)

删除索引：

drop index index_name on table_name

创建索引注意事项

不适合创建的场景

索引列包含NULL值

数据量少，修改频率低

列数据经常变化，频繁更新索引

需要计算的字段，索引无效

左侧模糊查询，索引失效，应遵循最右原则

类型为长类型数据字段如：text varchar

适合创建的场景

字段的数值有唯一性限制

频繁作为WHERE查询条件的字段

经常group by/order by的条件列

update/dalete条件列

DISTINCT字段

小数据类型字段列

字符创前缀创建索引

区分度高的列适合作为索引

使用频繁的列放在联合索引的左侧

多字段都需创建索引，联合索引优于单值索引

索引失效情况

like 以%开头索引无效，当 like 以&结尾，索引有效

or 语句前后没有同时使用索引，当且仅当 or 语句查询条件的前后列均为索引时，索引生效

组合索引，使用的不是第一列索引时候，索引失效，即最左匹配规则

数据类型出现隐式转换，如 varchar 不加单引号的时候可能会自动转换为 int 类型，这个时候索引失效。

在索引列上使用 IS NULL 或者 IS NOT NULL 时候，索引失效，因为索引是不索引空值的

在索引字段上使用，NOT、 <>、!= 、时候是不会使用索引的，对于这样的处理只会进行全表扫描

对索引字段进行计算操作，函数操作时不会使用索引

当全表扫描速度比索引速度快的时候不会使用索引。

视图

创建视图：

CREATE VIEW view_name AS sql~

存储过程

创建存储过程：

DELIMITER  //  *这里的符号自定义  但必须上下一致
    CREATE PROCEDURE pro_name(in/out *in输入类型参数  out输出类型参数 name varchar(50) *参数类型)
    BEGIN
    sql~
    sql~
END //

调用存储过程：

CALL pro_name(参数);

查询存储过程:

SHOW PROCEDURE STATUS [LIKE '***']

触发器

创建触发器：

CREATE trigger tri_name after/before insert/delete/update on table_name for each row
begin
    sql~ where field_name=OLD.field_name;
end

*OLD代表触发的对象数据可以在内部使用OLD代表