【Mysql系列】Mysql基础篇

💝💝💝欢迎来到我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。

• 推荐:kwan 的首页,持续学习,不断总结,共同进步,活到老学到老
• 导航
  • 檀越剑指大厂系列:全面总结 java 核心技术点,如集合,jvm,并发编程 redis,kafka,Spring,微服务,***ty 等
  • 常用开发工具系列:罗列常用的开发工具,如 IDEA,Mac,Alfred,electerm,Git,typora,apifox 等
  • 数据库系列:详细总结了常用数据库 mysql 技术点,以及工作中遇到的 mysql 问题等
  • 懒人运维系列:总结好用的命令,解放双手不香吗?能用一个命令完成绝不用两个操作
  • 数据结构与算法系列:总结数据结构和算法,不同类型针对性训练,提升编程思维,剑指大厂

非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。💝💝💝 ✨✨ 欢迎订阅本专栏 ✨✨

一.库级别

1.显示所有数据库

show  databases;

2.创建数据库

#创建数据库
create database name;

#创建数据库
CREATE DATABASE IF NOT EXISTS `test`;

3.显示数据库创建语句

show create database kwan;

4.选用数据库

#使用databasenaem数据库
use databasenaem;

5.修改数据库

alter database <数据库名字> character set <字符集> collate <校对规则名>

6.删除数据库

drop database if exists dbname;

7.获取库下的表

有以下 2 种方式获取数据库的概览,库表列的数据,方式一的方式巨慢,方式二是优化方案.

方式一

 ResultSet tables1 = dbMetaData.getTables(null, null, null, null);

方式二

SELECT * FROM information_schema.`columns`;

desc information_schema.`columns`;

二.表级别

1.查看所有表

#查看当前所有表
show tables;

2.建表语句

#查看当前数据库建表语句
show create table table_name;

基础建表

CREATE TABLE `buy_log` (
  `userid` int unsigned NOT NULL,
  `buy_date` date DEFAULT NULL,
  KEY `userid` (`userid`),
  KEY `userid_2` (`userid`,`buy_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

带描述建表

-- kwan.chatbot definition

CREATE TABLE `chatbot` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `question` text,
  `response` text,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ***MENT='聊天机器人记录表';

decimal字段类型和create_time字段

CREATE TABLE `vue_chat` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL ***MENT '商品名称',
  `goods_img` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL ***MENT '商品图片',
  `goods_price` decimal(8,2) DEFAULT NULL ***MENT '商品价格',
  `goods_state` varchar(10) DEFAULT NULL ***MENT '商品购买状态',
  `goods_count` decimal(8,2) DEFAULT NULL ***MENT '商品价格',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ***MENT='vue购物车项目';

多字段建表:

CREATE TABLE IF NOT EXISTS `biz_table_relationship` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT ***MENT '主键',
  `project_id` bigint(20) NOT NULL ***MENT '项目id',
  `source_id` bigint(20) NOT NULL ***MENT '数据源id',
  `database` varchar(64) NOT NULL ***MENT '库名',
  `relation_id` bigint(20) NOT NULL ***MENT '关系id',
  `base_table_name` varchar(255) NOT NULL ***MENT '主表名称',
  `relative_table_name` varchar(255) NOT NULL DEFAULT '' ***MENT '副表',
  `config` text ***MENT '关系详情',
  `sql` text ***MENT 'sql',
  `relationship_type` int(2) DEFAULT NULL ***MENT '关联类型,1:(1:1),2:(1:N),3:(N:1)',
  `creator` varchar(64) NOT NULL ***MENT '创建人名称',
  `memo` varchar(255) DEFAULT NULL ***MENT '描述信息',
  `tenant_id` varchar(64) NOT NULL DEFAULT '' ***MENT '租户id',
  `is_delete` tinyint(4) DEFAULT '0' ***MENT '是否删除',
  `create_by` bigint(20) NOT NULL DEFAULT '0' ***MENT '创建人id',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ***MENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL ***MENT '更新人id',
  `update_time` datetime DEFAULT NULL ***MENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ***MENT='表之间关联信息表';

3.查看表字段

#查看表字段信息
desc table_name;

#查看表字段信息
describe tablename;

4.删除表

#删除表
DROP TABLE if  EXISTS table_name;

5.清空表

#清空表
TRUNCATE TABLE ddl_task_record;

6.修改表名

ALTER TABLE dim_bl_week_info_2 RENAME  ads_dim_financial_year_week_info;

7.修改表描述

ALTER TABLE kwan.chatbot ***MENT='聊天机器人记录表';

8.修改表的字符集

#修改表的字符集
alter table <表名> character set <字符集>;

9.检查重复数据

SELECT period_sdate, product_key, size_code, managing_city_no
FROM ads_sense_rep.ads_day_city_brand_sku_size_rep
GROUP BY period_sdate, product_key, size_code, managing_city_no
HAVING COUNT(*) > 1;

三.字段信息

1.新增字段

#新增列
alter table rv.rv_schema_table add column `original_table_name` varchar(100) default null ***MENT '原始视图名' after `table_name`;

#新增删除字段
ALTER TABLE table_name ADD COLUMN is_delete TINYINT(1) DEFAULT 0 NOT NULL;

2.修改字段

ALTER TABLE operate_user_menu change user_id employee_number VARCHAR (22) ***MENT '员工编号';

3.更新字段

#修改注释
ALTER TABLE `rv`.ddl_task MODIFY create_by VARCHAR (22) NOT NULL ***MENT '创建人';
ALTER TABLE `rv`.ddl_task MODIFY update_by VARCHAR (22) NOT NULL ***MENT '更新人';

ALTER TABLE `rv`.ddl_task_record MODIFY stdout_log LONGTEXT default NULL ***MENT '执行日志';
ALTER TABLE `rv`.ddl_task_record MODIFY stderr_log LONGTEXT default NULL ***MENT '错误日志';
ALTER TABLE `rv`.ddl_task_record MODIFY error LONGTEXT default NULL ***MENT '错误日志';

4.自动更新字段

ALTER TABLE kwan.csdn_user_info
    ADD COLUMN `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ***MENT '更新时间' AFTER `create_time`;

5.删除字段

#删除字段
ALTER TABLE `rv`.`ddl_task` DROP res_type;

6.字段排序

SELECT * from kwan.chatbot order by id desc;

7.时间字段注解

@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
private Date updateTime;

四.行数据处理

1.新增行数据

#新增行
INSERT INTO table_name ( column1 ,...) VALUES ( value1,...);

2.删除行数据

#删除行数据
DELETE FROM kwan.chatbot WHERE question='hello';

3.更新行数据

#更新数据
UPDATE dct_logs SET operate_type = '更新' WHERE operate_type = 'UPDATE';

UPDATE ddl_task_record SET stdout_log  = '' WHERE length(stdout_log)>100000;

update rv_schema_table_column set column_type=0 where `column_type` ='null';

UPDATE kwan.chatbot SET is_delete=0 WHERE is_delete=1;

#更新多个字段
UPDATE test_end_of_quarter_sell_out_rate_01
SET product_year_name=2022
  , season_name      = '春'
WHERE 1 = 1
;

4.相同的数据删一条

DELETE FROM table_c WHERE day_time  = '2023-01-01' LIMIT 1;

五.索引信息

1.新增索引

#新增索引
create index ddl_task_record_task_id_index
    on ddl_task_record (task_id);

#新增索引
CREATE INDEX ddl_task_record_start_run_time_IDX USING BTREE ON rv.ddl_task_record (start_run_time);

2.删除索引

#删除索引
ALTER TABLE table_name DROP INDEX index_name;

3.新建约束

• 主键约束:primary key
• 唯一约束:unique 唯一索引除了在插入重复数据的时候会报错，还会使 auto_increment 自动增长
• 非空约束:not unll
• 外键约束: foreign key 一张表中有一个非主键的字段指向了别一张表中的主键，就将该字段叫做外键

4.主键约束

下面是创建表时如何添加这些约束的示例：

添加主键约束：

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    ...
);

5.唯一约束

添加唯一约束：

CREATE TABLE my_table (
    email VARCHAR(100) UNIQUE,
    ...
);

6.外键约束

添加外键约束：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    ...
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

7.非空约束

添加非空约束：

CREATE TABLE my_table (
    name VARCHAR(50) NOT NULL,
    ...
);

8.默认值约束

添加默认约束：

CREATE TABLE my_table (
    status VARCHAR(20) DEFAULT 'active',
    ...
);

9.检查约束

添加检查约束：

CREATE TABLE my_table (
    age INT CHECK (age >= 18),
    ...
);

这些约束可以单独使用，也可以结合在一起，以满足表数据的复杂限制要求。

六.服务端配置

1.启动 mysql

#启动mysql
systemctl start mysqld

#重启mysql
systemctl restart mysqld

#检查 mysql 服务器状态
systemctl status mysqld

2.登录方式

#使用密码登录
mysql -u root -p

`

3.查看客户端连接

#在服务器上登录
mysql -u root -p

#显示当前的客户端连接
SHOW PROCESSLIST;

#删除连接
kill 连接id;

#退出
quit

4.执行 sql 文件

#登录服务器
mysql -u root -p

#登录客户端后执行sql文件
source /home/jumpserver_dev/data/sql/rv_0321.sql

5.查看服务端编码

show variables like 'character_set_server';

6.获取连接 id

SELECT CONNECTION_ID()

七.常见问题

1.删除缓存

#在服务器上登录
mysql -u root -p

#刷新mysql的缓存
FLUSH HOSTS;

#退出
quit

2.查看缓冲池的信息

SELECT * from information_schema.INNODB_BUFFER_POOL_STATS;

• information_schema：这是 MySQL 中的一个系统数据库，包含有关数据库系统的元数据，例如表、列、索引、权限等信息。
• INNODB_BUFFER_POOL_STATS：这是information_schema数据库中特定表的名称。如前所述，该表包含有关 MySQL 中 InnoDB 缓冲池的统计信息。

3.显示 ddl 频次

在 MySQL 中，“***” 命令计数是指执行各种类型的 SQL 命令的次数。例如，“***_select” 表示执行 SELECT 语句的次数，“***_insert” 表示执行 INSERT 语句的次数，等等。通过这个查询，您可以查看与特定类型的 SQL 命令相关的计数信息。

SHOW GLOBAL STATUS LIKE '***_______';

4.查询库表列

#查看库
SELECT * FROM information_schema.schemata order by SCHEMA_NAME;

#查询表
select * from information_schema.tables
where  TABLE_SCHEMA ='information_schema'
order by TABLE_SCHEMA;

#查询列
select * from information_schema.columns order by TABLE_SCHEMA;

5.索引监控

#查询冗余索引
select * from sys.schema_redundant_indexes;

#查询未使用过的索引
select * from sys.schema_unused_indexes;

#查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics
where table_schema='kwan' ;

#查询表的访问量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc;

#查询占用bufferpool较多的表
select object_schema,object_name,allocated,data from sys.innodb_buffer_stats_by_table order by allocated limit 10;

#查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db='kwan';

#监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis order by exec_count desc;

#监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query from sys.statements_with_sorting limit 1;

#监控使用了临时表或者磁盘临时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;

#查看消耗磁盘IO的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes
order by avg_read limit 10;

6.查看等待锁的语句

SELECT *
FROM performance_schema.events_statements_history
WHERE thread_id IN (
    SELECT b.`THREAD_ID`
    FROM sys.`innodb_lock_waits` AS a
       , performance_schema.threads AS b
    WHERE a.waiting_pid = b.`PROCESSLIST_ID`)
ORDER BY timer_start ASC;

7.SHOW PROCESSLIST

SHOW PROCESSLIST 是用于查看当前运行在 MySQL 服务器上的所有连接和它们的状态的 SQL 命令。它提供了有关每个连接的详细信息，包括以下参数：

1. Id：连接的唯一标识符。每个连接都有一个唯一的 ID，可以用于识别和操作连接。

2. User：连接的用户名。这是连接到 MySQL 服务器的用户的用户名。

3. Host：连接的主机地址。这是连接到 MySQL 服务器的客户端主机的 IP 地址或主机名。

4. db：当前使用的数据库。连接当前正在使用的数据库，如果没有使用任何数据库，则显示为 NULL。

5. ***mand：客户端执行的命令。这可以是各种命令，如"Sleep"（空闲状态）、“Query”（执行 SQL 查询）、“Connect”（建立连接）等。

6. Time：连接的运行时间（以秒为单位）。表示连接自上次查询或命令执行以来的运行时间。

7. State：连接的当前状态。这个状态字段提供了连接当前正在执行的具体操作的信息。

8. Info：连接正在执行的 SQL 查询或命令。如果连接正在执行 SQL 查询，这里将显示查询的文本。

通过执行 SHOW PROCESSLIST 命令，你可以了解到当前活动的 MySQL 连接以及它们的状态，这对于诊断和监视 MySQL 服务器的性能和运行状况非常有用。如果需要中断某个连接，你可以使用 KILL 命令，并提供连接的 ID 来终止该连接。

8.查询死锁

-- 行锁阻塞情况
select * from sys.innodb_lock_waits;

这个系统视图提供了有关 InnoDB 存储引擎中正在等待锁的事务的信息。

InnoDB 是 MySQL 中常用的存储引擎之一，它支持事务和行级锁定，以提供更好的数据完整性和并发性。在多个事务同时访问同一数据时，可能会出现锁定冲突。当一个事务在等待另一个事务持有的锁时，就会发生锁等待（lock wait）。

sys.innodb_lock_waits视图的目的是帮助您识别当前正在等待锁的事务，以便您能够进行性能监视和故障排除。该视图提供了以下重要的列：

• waiting_trx_id：正在等待锁的事务 ID。
• waiting_thread_id：正在等待锁的线程 ID。
• waiting_query：正在等待锁的查询语句。
• waiting_lock_id：正在等待的锁 ID。
• waiting_lock_mode：正在等待的锁模式，例如共享锁或排他锁。
• blocking_trx_id：正在持有正在等待锁的事务 ID。
• blocking_thread_id：正在持有正在等待锁的线程 ID。
• blocking_query：正在持有正在等待锁的查询语句。
• blocking_lock_id：正在持有的锁 ID。
• blocking_lock_mode：正在持有的锁模式。

通过查询sys.innodb_lock_waits视图，您可以查看当前存在的锁等待情况，并确定哪些事务在阻塞其他事务的进程，从而帮助您诊断和解决潜在的性能问题。

查看锁信息:

# 查看锁信息
SELECT *
FROM information_schema.INNODB_TRX;
SELECT *
FROM `sys`.`innodb_lock_waits`;
SELECT *
FROM performance_schema.data_locks;
SELECT *
FROM performance_schema.data_lock_waits;

9.查询主从信息

#查询主服务器状态
show master status\G;

#查询从服务器状态
show slave status\G;

10.查看数据库支持的引擎

#查看引擎
show engines;

11.字符集

MySQL 支持多种字符集（Character Set）用于存储和处理不同语言和字符编码的数据。以下是一些常见的 MySQL 字符集：

1. utf8mb4：UTF-8 编码，支持存储任意 Unicode 字符，包括表情符号。
2. utf8：UTF-8 编码，支持存储大部分 Unicode 字符，但不支持存储一些较新的 Unicode 字符。
3. latin1：Latin-1 字符集，也称为 ISO 8859-1。支持大部分西欧语言的字符。
4. utf16：UTF-16 编码，支持存储任意 Unicode 字符。
5. utf32：UTF-32 编码，支持存储任意 Unicode 字符。

这只是一些常见的字符集示例，MySQL 还支持其他字符集。您可以使用以下命令查看 MySQL 服务器支持的所有字符集：

SHOW CHARACTER SET;

此命令将返回 MySQL 支持的所有字符集的列表，包括其名称和默认排序规则。

八.Mysql 变量

0.查询变量方法

#模糊查询变量
show variables	like '%cache%';

#查询所有变量
show variables;

1.a 头

下面是 MySQL 参数的表格展示，包括了参数名称和其对应的值以及中文注释：

请注意，有些参数的值为空或未提供具体数值，因此在表格中留空。如果需要更详细的信息或有其他问题，请随时提出。

2.b 头

下面是 MySQL 参数的表格展示，包括了参数名称和其对应的值以及中文注释：

请注意，每个参数的含义和作用都在表格中进行了解释。如果需要更详细的信息或有其他问题，请随时提出。

3.c 头

下面是 MySQL 参数的表格展示，包括了参数名称和其对应的值以及中文注释：

请注意，每个参数的含义和作用都在表格中进行了解释。如果需要更详细的信息或有其他问题，请随时提出。

4.d 头

下面是 MySQL 参数的表格展示，包括了参数名称和其对应的值以及中文注释：

请注意，每个参数的含义和作用都在表格中进行了解释。如果需要更详细的信息或有其他问题，请随时提出。

5.e 头

下面是 MySQL 参数的表格展示，包括了参数名称和其对应的值以及中文注释：

这些参数的含义和作用在表格中进行了解释。如果需要更详细的信息或有其他问题，请随时提出。

6.f 头

下面是 MySQL 参数的表格展示，包括了参数名称和其对应的值以及中文注释：

这些参数的含义和作用在表格中进行了解释。如果需要更详细的信息或有其他问题，请随时提出。

7.g 头

下面是 MySQL 参数的表格展示，包括了参数名称和其对应的值以及中文注释：

这些参数的含义和作用在表格中进行了解释。如果需要更详细的信息或有其他问题，请随时提出。

8.h 头

下面是 MySQL 参数的表格展示，包括了参数名称和其对应的值以及中文注释：

这些参数的含义和作用在表格中进行了解释。如果需要更详细的信息或有其他问题，请随时提出。

9.i 头

下面是 MySQL 参数的表格展示，包括了参数名称和其对应的值以及中文注释：

这些参数的含义和作用在表格中进行了解释。如果需要更详细的信息或有其他问题，请随时提出。

10.j 头

下面是 MySQL 参数join_buffer_size的表格展示，包括了参数名称、参数值以及中文注释：

这个参数控制着用于执行连接操作（JOIN 操作）时的缓冲区大小。当执行连接操作时，MySQL 可以使用这个缓冲区来存储临时数据，以提高查询性能。参数的值表示缓冲区的大小，单位是字节。增大这个值可以提高某些查询的性能，但也会增加内存的消耗。

11.k 头

下面是 MySQL 参数的表格展示，包括了参数名称和其对应的值以及中文注释：

这些参数的含义和作用在表格中进行了解释。如果需要更详细的信息或有其他问题，请随时提出。

12.l 头

下面是 MySQL 参数的表格展示，包括了参数名称、参数值以及中文注释：

这些参数的含义和作用在表格中进行了解释。如果需要更详细的信息或有其他问题，请随时提出。

13.m 头

以下是对 MySQL 参数的解释，并使用表格进行展示：

14.n 头

下面是一个用表格展示并注释的 MySQL 参数列表：

以上是 MySQL 的一些常用参数，它们可以通过修改配置文件来进行调整，以满足特定应用的需求。

15.o 头

以下是 MySQL 参数的表格展示，附带中文注释：

这些参数可以通过修改 MySQL 的配置文件来进行调整，以满足具体应用需求。

16.p 头

下面是 MySQL 参数的表格展示，包括中文注释：

这些参数可通过修改 MySQL 的配置文件以满足特定的应用需求。

17.q 头

下面是 MySQL 参数的表格展示，包括中文注释：

这些参数用于控制 MySQL 服务器中查询操作的内存分配和预分配方式，可以通过修改配置文件来调整以满足特定的性能需求。

18.r 头

下面是 MySQL 参数的表格展示，包括中文注释：

这些参数用于配置 MySQL 复制和服务器性能，可通过修改配置文件来调整以满足特定的需求。

19.s 头

以下是 MySQL 参数的表格展示，附带中文注释：

这些参数用于配置 MySQL 服务器的各种行为和性能特性，可通过修改配置文件来满足特定需求。

20.t 头

以下是 MySQL 参数的表格展示，附带中文注释：

这些参数用于配置 MySQL 服务器的表格和临时表、线程管理、时区和加密等方面的特性，可通过修改配置文件以满足特定需求。

21.u 头

下面是 MySQL 参数的表格展示，包括中文注释：

这些参数用于控制 MySQL 服务器的行为和特性，可以通过配置文件进行调整以满足特定需求。

22.v 头

以下是 MySQL 参数的表格展示，附带中文注释：

这些参数包括密码策略、版本信息和编译配置等，用于控制 MySQL 服务器的密码安全性和描述服务器的版本信息。可通过修改配置文件来调整部分参数以满足特定需求。

23.w 头

以下是 MySQL 参数的表格展示，附带中文注释：

这些参数控制了 MySQL 服务器的等待超时时间、警告计数和窗口函数的高精度使用。可通过配置文件进行相应调整以满足特定需求。超时等待时间默认是 8 小时。

24.x 头

下面是 MySQL 参数的表格展示，包括中文注释：

这个参数控制在 XA 事务的准备阶段是否分离（detach）事务。当设置为 ON 时，在准备阶段会分离事务，否则不会。可通过配置文件进行相应调整以满足特定需求。

9.JDBC 的 URL 详解

jdbc 的格式:

jdbc:mysql://[host][,failoverhost...][:port]/[database] [?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

例如:

jdbc:mysql://10.171.55.193/uba_db?useUnicode=true&characterEncoding=UTF-8

参数详解:

觉得有用的话点个赞 👍🏻 呗。
❤️❤️❤️本人水平有限，如有纰漏，欢迎各位大佬评论批评指正！😄😄😄

💘💘💘如果觉得这篇文对你有帮助的话，也请给个点赞、收藏下吧，非常感谢!👍 👍 👍

🔥🔥🔥Stay Hungry Stay Foolish 道阻且长,行则将至,让我们一起加油吧！🌙🌙🌙