mysql 笔记

1.生成数据字典

SELECT
    COLUMN_NAME AS '字段名',
    COLUMN_TYPE AS '字段类型',
    ( CASE WHEN IS_NULLABLE = 'YES' THEN '是' ELSE '否' END ) AS '是否可空',
    ( CASE WHEN COLUMN_KEY = 'PRI' THEN '是' ELSE '否' END ) AS '是否主键',
		COLUMN_DEFAULT AS '默认值',
    COLUMN_COMMENT AS '注释' 
FROM
    INFORMATION_SCHEMA.COLUMNS 
WHERE
    TABLE_SCHEMA = '数据库' 
    AND TABLE_NAME = '表';

2.导入数据量大的文件

LOAD DATA LOCAL INFILE "E:\\test/CBS_CMSCARDKIND_F_20220823.dat" INTO TABLE ods_cbs_cmscardkind fields terminated by ',';

3.批量替换实例

 UPDATE fa_goods SET content = replace(content,'https://qn094.unitewx.com','https://xcx.juxinlegou.com') WHERE content like "%https://qn094.unitewx.com%"

4.查询重复字段子查询

select id,nickname,mobile,createtime from fa_user where mobile in(
 select mobile from fa_user WHERE status ='normal' GROUP BY mobile HAVING count(mobile)>1
) and mobile <> '' and status ='normal' ORDER BY id desc