一.安装 mysql
1.卸载步骤
#查询之前安装的文件
rpm -qa | grep-i mysql
#删除查询到的依赖
rpm -e--nodeps mysql-errmsg-8.0.26-1.module_el8.4.0+915+de215114.x86_64
#查找相关文件
find / -name mysql
#删除文件
rm-rf /etc/selinux/targeted/active/modules/100/mysql
#验证是否删除干净
rpm -qa | grep-i mysql
### 2.安装步骤
创建 mysql 目录,用于存储下载的 mysql 文件
#进入安装目录
cd/usr/local/
#下载 mysql 文件
wgethttps://dev.mysql.com/get/mysql80-community-release-el8-4.noarch.rpm
#安装储存库
rpm-Uvhmysql80-community-release-el8-4.noarch.rpm
#禁用默认 MySQL 模块
yum-ymoduledisablemysql
#安装 mysql-community-server
yum-yinstallmysql-community-server
3.启动 mysql
#启动mysql
systemctl start mysqld
#重启mysql
systemctl restart mysqld
#检查 mysql 服务器状态
systemctl status mysqld
4.登录
#使用密码登录
mysql -u root -p
5.修改密码
#查看临时密码
cat /var/log/mysqld.log | grep password
#使用临时密码登录
mysql -u root -p
#修改密码，需要满足强度要求
alter user 'root'@'localhost' identified by '你的密码';
#新密码登录测试
mysql -u root -p
#带密码登录
mysql -u root -p20191014
#密码策略设置为LOW，表示只验证密码长度，对密码的数字、字母都没要求。
set global validate_password.policy=LOW;
#密码长度设置为自己想要的长度，表示密码最少有几位。
set global validate_password.length=7;
#修改密码，只需要大于设置的长度就可以。
alter user 'root'@'localhost' identified by '你的密码';
#退出mysql
exit;
6.修改访问权限
#访问权限查询
select user,host from mysql.user;
#root用户的localhost表示只允许本机访问，要实现远程连接，可以将用户的host改为%，表示允许任意主机访问，如果需要设置只允许特定ip访问，则应改为对应的ip。
#修改root用户的host字段为%
update mysql.user set host="%" where user="root";
#刷新配置
flush privileges;
#如果是物理服务器：
#查询3306端口是否开放
firewall-cmd --query-port=3306/tcp
#开放
firewall-cmd --zone=public --add-port=3306/tcp --permanent
#重新加载防火墙设置
firewall-cmd --reload
#云服务器直接开放端口号3306
#navicat/dbeaver测试能否连接
7.删除 mysql 缓存
#在服务器上登录
mysql -u root -p
#刷新mysql的缓存
FLUSH HOSTS;
#退出
quit
8.查看客户端连接
#在服务器上登录
mysql -u root -p
#显示当前的客户端连接
SHOW PROCESSLIST;
#删除连接
kill 连接id;
#退出
quit
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
参数详解:
| 参数 | 作用 |
| user | 数据库用户名（用于连接数据库） |
| password | 用户密码（用于连接数据库） |
| useUnicode | 是否使用 Unicode 字符集，如果参数 characterEncoding 设置为 gb2312 或 gbk，本参数值必须设置为 true） |
| characterEncoding | 当 useUnicode 设置为 true 时，指定字符编码。比如可设置为 gb2312 或 gbk |
| autoReconnect | 当数据库连接异常中断时，是否自动重新连接 |
| autoReconnectForPools | 是否使用针对数据库连接池的重连策略 |
| failOverReadOnly | 自动重连成功后，连接是否设置为只读 |
| maxReconnects | autoReconnect 设置为 true 时，重试连接的次数 |
| initialTimeout | autoReconnect 设置为 true 时，两次重连之间的时间间隔，单位：秒 |
| connectTimeout | 和数据库服务器建立 socket 连接时的超时，单位：毫秒。 0 表示永不超时，适用于 JDK 1.4 及更高版本 |
| socketTimeout | socket 操作（读写）超时，单位：毫秒。 0 表示永不超时 |
二.服务端设置
1.执行 sql 文件
#登录服务器
mysql -u root -p
#登录客户端后执行sql文件
source /home/jumpserver_dev/datasense/sql/rv_0321.sql
2.查看服务端编码
show variables like 'character_set_server';
3.查看缓冲池的信息
SELECT * from information_schema.INNODB_BUFFER_POOL_STATS;
information_schema：这是 MySQL 中的一个系统数据库，包含有关数据库系统的元数据，例如表、列、索引、权限等信息。INNODB_BUFFER_POOL_STATS：这是information_schema数据库中特定表的名称。如前所述，该表包含有关 MySQL 中 InnoDB 缓冲池的统计信息。
4.获取连接 id
SELECT CONNECTION_ID()
5.查询死锁
-- 行锁阻塞情况
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;
6.查询主从信息
#查询主服务器状态
show master status\G;
#查询从服务器状态
show slave status\G;
8.查看数据库支持的引擎
#查看引擎
show engines;
9.显示 ddl 频次
在 MySQL 中，"Com" 命令计数是指执行各种类型的 SQL 命令的次数。例如，"Com_select" 表示执行 SELECT 语句的次数，"Com_insert" 表示执行 INSERT 语句的次数，等等。通过这个查询，您可以查看与特定类型的 SQL 命令相关的计数信息。
SHOW GLOBAL STATUS LIKE 'Com_______';
10.查询库表列
#查看库
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;
11.索引监控
#查询冗余索引
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;
12.查看等待锁的语句
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;
三.库级别
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 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 COMMENT='聊天机器人记录表';
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 COMMENT '商品名称',
`goods_img` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '商品图片',
`goods_price` decimal(8,2) DEFAULT NULL COMMENT '商品价格',
`goods_state` varchar(10) DEFAULT NULL COMMENT '商品购买状态',
`goods_count` decimal(8,2) DEFAULT NULL COMMENT '商品价格',
`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 COMMENT='vue购物车项目';
多字段建表:
CREATE TABLE IF NOT EXISTS `biz_table_relationship` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`project_id` bigint(20) NOT NULL COMMENT '项目id',
`source_id` bigint(20) NOT NULL COMMENT '数据源id',
`database` varchar(64) NOT NULL COMMENT '库名',
`relation_id` bigint(20) NOT NULL COMMENT '关系id',
`base_table_name` varchar(255) NOT NULL COMMENT '主表名称',
`relative_table_name` varchar(255) NOT NULL DEFAULT '' COMMENT '副表',
`config` text COMMENT '关系详情',
`sql` text COMMENT 'sql',
`relationship_type` int(2) DEFAULT NULL COMMENT '关联类型,1:(1:1),2:(1:N),3:(N:1)',
`creator` varchar(64) NOT NULL COMMENT '创建人名称',
`memo` varchar(255) DEFAULT NULL COMMENT '描述信息',
`tenant_id` varchar(64) NOT NULL DEFAULT '' COMMENT '租户id',
`is_delete` tinyint(4) DEFAULT '0' COMMENT '是否删除',
`create_by` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建人id',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_by` bigint(20) DEFAULT NULL COMMENT '更新人id',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='表之间关联信息表';
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 COMMENT='聊天机器人记录表';
8.字符集
MySQL 支持多种字符集（Character Set）用于存储和处理不同语言和字符编码的数据。以下是一些常见的 MySQL 字符集：
- utf8mb4：UTF-8 编码，支持存储任意 Unicode 字符，包括表情符号。
- utf8：UTF-8 编码，支持存储大部分 Unicode 字符，但不支持存储一些较新的 Unicode 字符。
- latin1：Latin-1 字符集，也称为 ISO 8859-1。支持大部分西欧语言的字符。
- utf16：UTF-16 编码，支持存储任意 Unicode 字符。
- utf32：UTF-32 编码，支持存储任意 Unicode 字符。
这只是一些常见的字符集示例，MySQL 还支持其他字符集。您可以使用以下命令查看 MySQL 服务器支持的所有字符集：
SHOW CHARACTER SET;
此命令将返回 MySQL 支持的所有字符集的列表，包括其名称和默认排序规则。
9.修改表的字符集
#修改表的字符集
alter table <表名> character set <字符集>;
10.添加约束
下面是创建表时如何添加这些约束的示例：
添加主键约束：
CREATE TABLE my_table (
id INT PRIMARY KEY,
...
);
添加唯一约束：
CREATE TABLE my_table (
email VARCHAR(100) UNIQUE,
...
);
添加外键约束：
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
...
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
添加非空约束：
CREATE TABLE my_table (
name VARCHAR(50) NOT NULL,
...
);
添加默认约束：
CREATE TABLE my_table (
status VARCHAR(20) DEFAULT 'active',
...
);
添加检查约束：
CREATE TABLE my_table (
age INT CHECK (age >= 18),
...
);
这些约束可以单独使用，也可以结合在一起，以满足表数据的复杂限制要求。
五.字段信息
1.新增字段
#新增列
alter table rv.rv_schema_table add column `original_table_name` varchar(100) default null COMMENT '原始视图名' 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) COMMENT '员工编号';
3.更新字段信息
#修改注释
ALTER TABLE `rv`.ddl_task MODIFY create_by VARCHAR (22) NOT NULL COMMENT '创建人';
ALTER TABLE `rv`.ddl_task MODIFY update_by VARCHAR (22) NOT NULL COMMENT '更新人';
ALTER TABLE `rv`.ddl_task_record MODIFY stdout_log LONGTEXT default NULL COMMENT '执行日志';
ALTER TABLE `rv`.ddl_task_record MODIFY stderr_log LONGTEXT default NULL COMMENT '错误日志';
ALTER TABLE `rv`.ddl_task_record MODIFY error LONGTEXT default NULL COMMENT '错误日志';
4.删除字段
#删除字段
ALTER TABLE `rv`.`ddl_task` DROP res_type;
5.新增行数据
#新增行
INSERT INTO table_name ( column1 ,...) VALUES ( value1,...);
6.删除行数据
#删除行数据
DELETE FROM kwan.chatbot WHERE question='hello';
7.更新行数据
#更新数据
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
;
8.时间字段注解
@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
private Date updateTime;
9.字段排序
SELECT * from kwan.chatbot order by id desc;
10.相同的数据删一条
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 一张表中有一个非主键的字段指向了别一张表中的主键，就将该字段叫做外键
