MySQL 相关知识笔记

于 2024-08-23 17:37:15 发布
阅读量523 收藏 8
点赞数 15
分类专栏： MySQL笔记 文章标签： mysql 笔记 数据库 学习
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_68929783/article/details/141470907
版权

一、字符编码


MySQL 默认的编码是 utf8，仅支持三个字节的存储；utf8mb4 支持四个字节的存储。

二、数据库操作

  1. 查询

    • 查询所有数据库：SHOW DATABASES;
    • 查询当前数据库：SELECT DATABASE();

  2. 创建

    • CREATE DATABASE [IF NOT EXISTS] 数据库名 (DEFAULT CHARSET 字符集) [COLLATE 排序规则];

  3. 删除

    • DROP DATABASE [IF EXISTS] 数据库名;

  4. 使用

    • USE 数据库名;

三、表操作

  1. 查询

    • 查询当前数据库所有表：SHOW TABLES;
    • 查询表结构：DESC 表名;
    • 查询指定表的建表语句：SHOW CREATE TABLE 表名;

  2. 创建

    • CREATE TABLE 表名 ( 字段1 字段1类型 [COMMENT 字段1注释], 字段2 字段2类型 [COMMENT 字段2注释], 字段3 字段3类型 [COMMENT 字段3注释], 字段n 字段n类型 [COMMENT 字段n注释] ) [COMMENT 表注释];
    • 注意：若有多个字段，需在每个字段后面添加 “，”，但最后一个字段后面没有 “，”。Java 中的字符串用 String，MySQL 中的字符串用 varchar (50)。
    • MySQL 中的数据类型包括数值类型、引用类型和日期时间类型。

  3. 修改

    • 添加字段：ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
    • 修改数据类型：ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
    • 修改字段名和字段类型：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
    • 删除字段：ALTER TABLE 表名 DROP 字段名;

  4. 删除

    • 删除表：DROP TABLE [IF EXISTS] 表名;
    • 删除指定表，并重新创建该表：TRUNCATE TABLE 表名;

四、聚合函数


MySQL 聚合函数将一列数据作为一个整体进行纵向计算

常见聚合函数：

  1. 统计数量：count
  2. 最大值：max
  3. 最小值：min
  4. 平均值：avg
  5. 求和：sum

语法：

SELECT 聚合函数(字段列表) FROM 表名;

注意：

使用聚合函数时，所有的 null 值不参与运算。例如，select count(*) from emp;可以查询到所有的数量，而select count(idcard) from emp;若 idcard 中有 null 值则会缺少，因为 null 不参与计算。

五、分组查询


语法：SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

where 和 having 的区别：

  1. 执行时机不同：where 是分组之前进行过滤，不满足 where 条件的不参与分组；而 having 是分组之后对结果进行过滤。
  2. 判断条件不同：where 不能对聚合函数进行判断，而 having 可以。

注意：

  1. 执行顺序：where > 聚合函数 > having。
  2. 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无意义。

六、排序查询


语法：SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

排序方式：

  1. ASC：升序（默认值）。
  2. DESC：降序。

注意：

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

七、函数


函数是一段可以直接被另一段程序调用的程序或代码。

八、约束


约束作用于表中字段上的规则，用于限制存储在表中的数据，目的是保证数据库中数据的正确、有效性和完整性。

约束分类如下：

  1. 非空约束：限制该字段的数据不能为 null，关键字为NOT NULL。
  2. 唯一约束：保证该字段的所有数据都是唯一、不重复的，关键字为UNIQUE。
  3. 主键约束：主键是一行数据的唯一标识，要求非空且唯一，关键字为PRIMARY KEY。
  4. 默认约束：保存数据时，如果未指定该字段的值，则采用默认值，关键字为DEFAULT。
  5. 检查约束（8.0.16 版本之后）：保证字段值满足某一个条件，关键字为CHECK。
  6. 外键约束：用来让两张表的数据之间建立连接，保证数据的一致性和完整性，关键字为FOREIGN KEY。

注意：

  1. 约束是作用于表中字段上的，可以在创建表 / 修改表的时候添加约束。
  2. 多个约束之间使用空格分开。

约束演示：

create table user(
    id int primary key auto_increment comment'主键',
    name varchar(10) not null unique comment'姓名',
    age int check(age > 0 && age <= 120) comment'年龄',
    status char(1) default '1' comment'状态',
    gender char(1) comment'性别'
) comment'用户表';

外键约束：


外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

  • 删除外键：ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除 / 更新行为：

九、索引


索引是帮助 MySQL 高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引向（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

  1. 索引的优点：

    • 提高数据检索的效率，降低数据库的 IO 成本。
    • 通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗。

  2. 索引的缺点：

    • 索引列也是要占用空间的。
    • 索引大大提高了查询效率，同时也降低了更新表的速度，如对表进行 INSERT、UPDATE、DELETE 时，效率降低。

  3. 索引的结构：

    • MySQL 的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，如：
      • B + Tree 索引：最常见的索引类型，大部分引擎都支持 B + 树索引。
      • Hash 索引：底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。
      • R - tree（空间索引）：空间索引是 MylSAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
      • Full - text（全文索引）：是一种通过建立倒排索引快速匹配文档的方式，类似于 Lucene、Solr、ES。

B - Tree（多路平衡查找树）：


以一颗最大度数（max - degree）为 5（5 阶）的 b - tree 为例（每个节点最多存储 4 个 key，5 个指针）：

数据：10, 15, 18, 20, 23, 25, 28, 30, 34, 56, 58, 62, 64, 78, 88, 89, 92, 96, 98
指针：3, 4, 14, 16, 17, 19
知识小贴士：

树的度数指的是一个节点的子节点个数。

B + Tree：


以一颗最大度数（max - degree）为 4（4 阶）的 b + tree 为例：

数据：38, 55, 58, 67, 90, 94
指针：6, 12, 16, 18, 29, 34, 38, 45, 55, 56, 58, 62, 87, 90, 92, 94, 98
注：
  1. B + 树的所有节点都会出现在叶子节点上，上面的节点只起到一个索引的作用。
  2. B + 树的叶子节点之间有一个单项链表。
  3. MySQL 中的 B + 树是 B + 树的优化版。
  4. MySQL 索引数据结构对经典的 B + Tree 进行了优化。在原 B + Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B + Tree，提高区间访问的性能。

Hash索引：


哈希索引是采用一定的 hash 算法，将键值换算成新的 hash 值，映射到对应的槽位上，然后存储在 hash 表中。

注：


如果两个（或多个）键值，映射到同一个槽位上，它们就产生了 hash 冲突（hash 碰撞），可以通过链表来解决。

Hash 索引的特点：
  1. Hash 索引只能用于对等比较（=，in），不支持范围查询。
  2. 无法利用索引完成排序操作（计算的 hash 值是无序的）。
  3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于 B + tree 索引。

为什么 InnoDB 存储引擎选择使用 B + tree 索引结构？


答：相对于二叉树，层级更少，搜索效率高；对于 B - tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；相较 Hash 索引，B + tree 支持范围匹配及排序操作。

索引分类：

在 InnoDB 存储引擎中，根据索引的存储形式，可以分为以下两种：

聚集索引的选取规则：

  1. 如果存在主键，主键索引就是聚集索引。
  2. 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
  3. 如果表没有主键，或没有合适的唯一索引，InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。

思考：


以下 SQL 语句，哪个执行效率高？为什么？

select * from user where id = 10；
select * from user where name = 'Arm'；

id 为主键，name 字段创建的有索引。

答：select * from user where id = 10；更快。

原因：
select * from user where id = 10；`这个语句中根据 id = 10 可以有以下步骤：
直接找到 id = 10 的整行数据。

`select * from user where name = 'Arm'；`这个语句根据 name = 'Arm' 有以下步骤：
先找到 Arm 的 id，然后再根据 id 找到整行数据，涉及到回表，因此效率不高。

索引的语法：

  1. 创建索引：CREATE [UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,...);
  2. 查看索引：SHOW INDEX FROM table_name ;
  3. 删除索引：DROP INDEX index_name ON table_name;

十、实际操作示例

  1. 为 name 字段（该字段的值可能会重复）创建索引：CREATE INDEX idx_user_name ON tb_user(name);
  2. 为 phone 手机号字段（该字段的值是非空且唯一的）创建唯一索引：CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
  3. 为 profession、age、status 创建联合索引：CREATE INDEX idx_user_pro_age_sta ON tb_user(profession, age, status);
  4. 为 email 建立合适的索引来提升查询效率：CREATE INDEX idx_email ON tb_user(email);

十一、其他命令

  1. 查看服务器状态信息的命令：

    show [session|global] status like 'Com_______';（注：该命令可查询执行频次，一个下划线代表一个字符）
  2. 慢查询：
    • 查看慢查询的开关开启情况：show variables like ’slow_query_log'
    • 在 MySQL 的配置文件（/etc/my.cnf）中配置：
      • #开启 MySQL 慢日志查询开关
      • slow_query_log = 1
      • 设置慢日志的时间为 2 秒，SQL 语句执行时间超过 2 秒，就会视为慢查询，记录慢查询日志
      • long_query_time = 2
    • 配置结束后重启 MySQL 服务器：systemctl restart mysqld
    • 日志文件存放路径：cd /var/lib/mysql
    • 日志文件：localhost - slow.log
  3. 关于 profile：
    • SELECT @@have_profiling; #查看该数据库是否支持
    • SET profiling = 1； #profiling 默认是关闭的，可以开启，可以通过 session/global 设置开启级别
    • show profiles; #查看 SQL 语句的执行情况
    • show profile for query ${id}; #查看指定 query_id 的 SQL 语句各个阶段的耗时情况

十二、总结


以上是根据 B 站黑马课程学习后的 MySQL 笔记，为了日后复习和给别人参考而整理，希望对大家有所帮助。同时，在实际应用中，还需要根据具体情况合理使用索引、约束等功能，以提高数据库的性能和数据的完整性。

道爷我悟了
关注
  • 15
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql学习笔记（全）
m0_60264772的博客
12-07 2万+
目录 MyISAM和InnoDB 性能下降SQL慢的原因： Mysql执行顺序 SQLJoin 索引 索引的优劣 索引分类 mysql索引结构 那些情况建索引 哪些情况不要建索引 性能分析 Extra 索引优化 索引口诀 索引案例 Order By 索引优化 exsites Mysql慢查询日志命令 函数操作批量插入数据 show Profile分析sql 全局查询日志 Mysql锁 锁命令 表锁：读锁 表锁：写锁 行锁 事务 行锁测试 间隙锁 行
mysql高级篇学习笔记
java
07-28 6974
尚硅谷宋红康康师傅mysql高级篇学习
MySQL的知识点整理（学习笔记）
随心笔记
06-06 7364
MySQL是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。Mysql是开源的，所以你不需要支付额外的费用。Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL知识笔记
zy3256的博客
09-29 1734
MySQL知识笔记
MySQL基础知识笔记
hlt_bk的博客
04-05 3028
MySQL SQL语句分类 数据查询语言：DQL 负责进行查询数据，不会更改数据 基础查询： 查询表中所有列的数据： select * from 表名 查询指定列的数据： select 列名1，列名2 from 表名 (写哪列查哪列) 在当前数据库查看其他数据库中的表 show tables in 数据库名 在当前数据库查看其他数据库表中的数据 select 列名 from 数据库.数据表（用点连接库和表） 数据定义语言: DDL 负责数据结构定义和数据库对象定义 创建表： create table 表
mysql数据库基本知识点整理笔记
aierken2的博客
05-10 3239
mysql数据库基本知识点整理笔记
尚硅谷MySQL学习笔记
热门推荐
IAMLSL的博客
02-11 1万+
MySQL笔记目录一、MySQL数据库基础篇大纲1. 数据库概述与MySQL安装篇第01章：数据库概述第02章：MySQL环境搭建2. SQL之SELECT使用篇第03章：基本的SELECT语句第04章：运算符第05章：排序与分页第06章：多表查询第07章：单行函数第08章：聚合函数第09章：子查询3. SQL之DDL、DML、DCL使用篇第10章：创建和管理表第11章：数据处理之增删改第12章：MySQL数据类型精讲第13章：约束4. 其它数据库对象篇第14章：视图第15章：存储过程与函数第16章：变量、
MySQL知识点笔记
exercise_smile的博客
05-13 1007
Oracle：大型的收费数据库，Oracle公司产品，价格昂贵。MySQL：开源免费的中小型数据库，后来Sun公司收购了MySQL，而Oracle又收购了Sun公司。目前Oracle推出了收费版本的MySQL，也提供了免费的社区版本。SQL ServerDB2不论我们使用的是上面的哪一个关系型数据库，最终在操作时，都是使用SQL语言来进行统一操作，因为我们前面讲到SQL语言，是操作关系型数据库的统一标准。存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。
MYSQL学习过程笔记
zhineng2LizZ_的博客
02-06 415
包括但不限于增删改查基础sql语句 索引 优化 键 多表联查 子查询 left join 锁 触发器 事务等相关知识及同步练习代码。记录了学习mysql数据库以来基础和进阶篇的知识。
[MySQL]知识点
进步*于辰的博客
04-03 1万+
本篇文章是关于MySQL各类知识点的小结，包括：int族数据类型存储数据范围、char族数据类型的选择问题等。 如果文中阐述不全或不对的，多多交流。
MySQL的基础知识笔记
04-26
### MySQL的基础知识笔记 #### 一、背景介绍 MySQL 是一款非常流行的开源关系型数据库管理系统。自1979年创建以来，它已经成为全球最广泛使用的数据库之一。MySQL 的应用场景非常广泛，包括但不限于： - **网站...
MySQL笔记 知识点全
01-02
本笔记将全面覆盖MySQL的基础知识、进阶技巧以及最佳实践。 一、MySQL基础 1. 数据库与表：MySQL中的数据库是存储数据的逻辑容器，而表则是组织数据的基本结构。创建表时，需要定义字段（列）及其数据类型，如INT、...
mysql的相关命令笔记
03-30
### MySQL 相关命令知识点详解 #### 一、MySQL配置与初始化 1. **打开 MySQL 配置环境：** - 在新安装MySQL后，首先需要确保MySQL的配置正确无误。 - 通常，MySQL的配置文件为`my.cnf`（Linux系统）或`my.ini`...
MYSQL知识学习过程笔记
10-27
MYSQL知识学习过程笔记 在学习MYSQL之前，首先需要了解什么是数据库、数据库管理系统和SQL它们之间的关系是什么？数据库英文单词DataBase，简称DB，按照一定格式存储数据的一些文件的组合，顾名思义：存储数据的...
mysql高级知识笔记.pdf
02-03
mysql高级知识笔记.pdf
SpringBoot MySQL BinLog 监听数据变化(多库多表)
最新发布
掐指一算乀缺钱
08-19 592
SpringBoot MySQL BinLog 监听数据变化(多库多表) 库.表,库1.表1,库1.表2
commvault学习（9）：备份mysql（linux平台）
ab149162536的博客
08-19 432
1.1创建实例在centos7上安装完带有mysql组件的cv客户端后，就能在CS上看到客户端的mysql选项右击图中mysql，点击在弹出的配置框中进行如下设置：服务器名：类似任务名，自行设置即可SA用户及密码：就是客户端的mysql的root用户即密码二进制目录：数据库数据文件所在目录。默认为日志数据目录：数据库日志文件所在目录，默认为配置文件：填写数据库Config File所在目录，默认为套接字文件：填写数据库Socket File所在目录，默认为设置完成后，选择存储策略即可。
51 无显式主键时 mysql 增加的 DB_ROW_ID
970655147的专栏
08-19 464
假设有 implicit_id_table_02, 然后增加了 两条记录, 之后再向 implicit_id_table 中增加记录, 得到的 DB_ROW_ID 为 558。创建表的时候, 创建了该 三个系统字段, DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR。row_id 的处理是通过 dict_sys_get_new_row_id 函数来获取的。为 DB_ROW_ID, DB_TRX_ID, DB_POLL_PTR 分配存储空间的地方。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值