mysql SQL

🧙 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;

🔗 Links