mysql SQL
Last updated on July 27, 2025 am
🧙 Questions
☄️Ideas
连接mysql
docker exec -it isxcode-mysql bash
mysql -h isxcode -u ispong -pispong2021 -P 30102
show databases;
use data_assets;
drop table ispong_table;
create table user1
(
id varchar(200),
username varchar(200)
);
create table user2
(
id varchar(200),
age int
);
CREATE VIEW users AS select user1.id,username,age from user1 left join user2 on user1.id = user2.id
INSERT INTO data_assets.ispong_table (username, age, birth) VALUES ('lisi', 14, '2021-09-27 14:48:14')
隐藏部分的手机号码
select insert(PHONE, 5, 11, '********')
from USERS
limit 1;
string 转 date
select str_to_date('2020-12-01 12:12:12','%Y-%m-%d %H:%i:%s') from dual
date 转 string
select date_format(now(),'%Y-%m-%d %H:%i:%s') from dual
创建库
···
CREATE DATABASE ispong_demo DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
CREATE DATABASE ispong_assets DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
···
查询表中各字段的字符集
-- SHOW FULL COLUMNS FROM COLUMNS_V2
SHOW FULL COLUMNS FROM ${tableName}
给表加索引
CREATE INDEX idx_${table_name}_${column_id} ON TABLE1 (COLUMN_ID1);
一个月前的时间
select DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
select DATE_SUB(CURDATE(), INTERVAL 1 WEEK);
执行sql脚本
docker cp admin.sql test-link-mysql:/tmp/
docker exec -it test-link-mysql bash
mysql -h localhost -u root -p'123' -P 3306 < /tmp/admin.sql
mysql执行中文乱码
BEGIN;
SET NAMES utf8;
mysql排查表大小
SELECT
table_schema AS '数据库名',
table_name AS '表名',
ROUND(data_length/1024/1024, 2) AS '数据大小(MB)',
ROUND(index_length/1024/1024, 2) AS '索引大小(MB)',
ROUND((data_length + index_length)/1024/1024, 2) AS '总大小(MB)',
table_rows AS '行数'
FROM
information_schema.TABLES
WHERE
table_schema = 'dehoop'
ORDER BY
(data_length + index_length) DESC;
ALTER TABLE das_api_logs RENAME TO das_api_logs_2025_07_19;
create table das_api_logs like das_api_logs_2025_07_19;
insert into execution_flows select * from execution_flows_2025_07_19 where CREATION_DATE > DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
insert into das_api_logs select * from das_api_logs_2025_07_19 where CREATION_DATE > DATE_SUB(CURDATE(), INTERVAL 7 DAY);
🔗 Links
mysql SQL
https://ispong.isxcode.com/db/mysql/mysql SQL/