KWAN的解忧杂货铺

# 一.库级别

# 1.显示所有数据库

show  databases;
1

# 2.创建数据库

# 创建数据库
create database name;

# 创建数据库
CREATE DATABASE IF NOT EXISTS `test`;
1
2
3
4
5

# 3.显示数据库创建语句

show create database kwan;
1

# 4.选用数据库

# 使用databasenaem数据库
use databasenaem;
1
2

# 5.修改数据库

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

# 6.删除数据库

drop database if exists dbname;
1

# 7.获取库下的表

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

方式一

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

方式二

SELECT * FROM information_schema.`columns`;

desc information_schema.`columns`;
1
2
3

image-20220908165929538

# 8.查询表的数据量

SELECT
    table_schema as `database`,
    table_name as `table`,
    round(sum(data_length + index_length) / (1024 * 1024), 2) as size_in_mb,
    round(sum(data_length + index_length) / (1024 * 1024 * 1024), 2) as size_in_gb
FROM information_schema.tables
GROUP BY table_schema, table_name
ORDER BY size_in_mb DESC;
1
2
3
4
5
6
7
8

# 二.表级别

# 1.查看所有表

# 查看当前所有表
show tables;
1
2

# 2.建表语句

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

基础建表

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
1
2
3
4
5
6

带描述建表

-- 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='聊天机器人记录表';
1
2
3
4
5
6
7
8
9

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购物车项目';
1
2
3
4
5
6
7
8
9
10

多字段建表:

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='表之间关联信息表';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 3.查看表字段

# 查看表字段信息
desc table_name;

# 查看表字段信息
describe tablename;
1
2
3
4
5

image-20231114011723446

# 4.删除表

# 删除表
DROP TABLE if  EXISTS `table_name`;
1
2

# 5.清空表

# 清空表
TRUNCATE TABLE `ddl_task_record`;
1
2

# 6.修改表名

ALTER TABLE dim_bl_week_info_2 RENAME  ads_dim_financial_year_week_info;
1

# 7.修改表描述

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

# 8.修改表的字符集

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

# 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
2
3
4

# 三.字段信息

# 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;
1
2
3
4
5

# 2.修改字段

将字段user_id修改为employee_number字段

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

# 3.更新字段

ALTER TABLE csdn_red_package MODIFY update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间';
1
#修改注释
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 '错误日志';
1
2
3
4
5
6
7

# 4.自动更新字段

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

# 5.删除字段

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

# 6.字段排序

SELECT * from kwan.chatbot order by id desc;
1

# 7.时间字段注解

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

# 8.移动字段位置

# 将 view_count 移动到 msg 之后
ALTER TABLE `Application`
    MODIFY COLUMN `updatedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) AFTER `createdAt`;
1
2
3

# 9.字符集

5.7 使用

`user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名',
1

8.0 使用

`content_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '内容 id',
1

# 四.行数据处理

# 1.新增行数据

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

# 2.删除行数据

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

# 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;
1
2
3
4
5
6
7
8
# 更新多个字段
UPDATE test_end_of_quarter_sell_out_rate_01
SET product_year_name=2022
  , season_name      = '春'
WHERE 1 = 1
;
1
2
3
4
5
6

# 4.相同的数据删一条

DELETE FROM table_c WHERE day_time  = '2023-01-01' LIMIT 1;
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);
1
2
3
4
5
6

# 2.删除索引

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

# 3.新建约束

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

# 4.主键约束

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

添加主键约束：

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    ...
);
1
2
3
4

# 5.唯一约束

添加唯一约束：

CREATE TABLE my_table (
    email VARCHAR(100) UNIQUE,
    ...
);
1
2
3
4

# 6.外键约束

添加外键约束：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    ...
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
1
2
3
4
5
6

# 7.非空约束

添加非空约束：

CREATE TABLE my_table (
    name VARCHAR(50) NOT NULL,
    ...
);
1
2
3
4

# 8.默认值约束

添加默认约束：

CREATE TABLE my_table (
    status VARCHAR(20) DEFAULT 'active',
    ...
);
1
2
3
4

# 9.检查约束

添加检查约束：

CREATE TABLE my_table (
    age INT CHECK (age >= 18),
    ...
);
1
2
3
4

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

如果card_secret字段需要包含数字和可能的小写字母，并且每 4 位后跟一个'-'，那么正则表达式需要稍作调整以包含小写字母。以下是相应的 SQL 语句，用于添加这样的约束：

ALTER TABLE card_secrets
ADD CONSTRAINT chk_card_secret CHECK (
    card_secret REGEXP '^[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{3}$'
);
1
2
3
4

这里的正则表达式'^[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{3}$'解释如下：

  • ^ 表示字符串的开始。
  • [a-z0-9]{4} 表示 4 位数字或小写字母。
  • - 表示一个连字符。
  • $ 表示字符串的结束。

这个正则表达式确保了card_secret字段的值以 4 位数字或小写字母开始，后面跟着一个连字符，然后是另外三组 4 位数字或小写字母和连字符，最后以 3 位数字或小写字母结束，总共 19 位。

请注意，MySQL 的正则表达式功能可能因版本而异，并且性能可能不如其他类型的约束。如果你的 MySQL 版本不支持CHECK约束或者你遇到了性能问题，你可能需要考虑使用触发器或者在应用层进行数据验证。

# 10.联合索引

要将唯一键 idx_desc 修改为联合唯一索引 desc 和 tenant_id，你需要删除原有的唯一键并重新创建一个新的联合唯一键。可以使用以下步骤完成操作：

ALTER TABLE your_table_name
DROP INDEX idx_desc;

ALTER TABLE your_table_name
ADD UNIQUE KEY `idx_desc_tenant` (`desc`, `tenant_id`);
1
2
3
4
5

将 your_table_name 替换为你的实际表名。执行后，desc 和 tenant_id 将共同构成一个联合唯一索引，确保这两个字段组合的值在表中是唯一的。

# 六.服务端配置

# 1.启动 mysql

# 启动mysql
systemctl start mysqld

# 重启mysql
systemctl restart mysqld

# 检查 mysql 服务器状态
systemctl status mysqld
1
2
3
4
5
6
7
8

# 2.登录方式

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

# 3.查看客户端连接

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

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

# 删除连接
kill 连接id;

# 退出
quit
1
2
3
4
5
6
7
8
9
10
11

# 4.执行 sql 文件

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

# 登录客户端后执行sql文件
source /home/jumpserver_dev/data/sql/rv_0321.sql
1
2
3
4
5

# 5.查看服务端编码

show variables like 'character_set_server';
1

# 6.获取连接 id

SELECT CONNECTION_ID()
1
上次更新: 11/26/2024, 10:01:04 PM

← 03-MySQL语句 →