常用SQL

 

-- 增加字段
alter table table1 add column age int(3) DEFAULT NULL COMMENT '年龄';

-- 修改字段
ALTER TABLE table1 
MODIFY COLUMN name varchar(200) DEFAULT NULL COMMENT '名称';

 -- 查询表

select table_name from information_schema.tables where table_schema='csdb' and table_type='base table';

-- 查询表字段
select t.*
from information_schema.columns t
where t.TABLE_SCHEMA = 'test'
and LOWER(t.TABLE_NAME) = 't_class'
order by t.TABLE_NAME, t.ORDINAL_POSITION;

-- 查询存储过程包含

select * from mysql.proc
where db = 'xdd_tst' and type = 'PROCEDURE'
and body like '%sale_client_goods%'

 

下划线转驼峰

CREATE FUNCTION `underlineToCamel`(paramString VARCHAR(200)) RETURNS varchar(200) CHARSET utf8
BEGIN
    set paramString = replace(paramString, '_a', 'A');
    set paramString = replace(paramString, '_b', 'B');
    set paramString = replace(paramString, '_c', 'C');
    set paramString = replace(paramString, '_d', 'D');
    set paramString = replace(paramString, '_e', 'E');
    set paramString = replace(paramString, '_f', 'F');
    set paramString = replace(paramString, '_g', 'G');
    set paramString = replace(paramString, '_h', 'H');
    set paramString = replace(paramString, '_i', 'I');
    set paramString = replace(paramString, '_j', 'J');
    set paramString = replace(paramString, '_k', 'K');
    set paramString = replace(paramString, '_l', 'L');
    set paramString = replace(paramString, '_m', 'M');
    set paramString = replace(paramString, '_n', 'N');
    set paramString = replace(paramString, '_o', 'O');
    set paramString = replace(paramString, '_p', 'P');
    set paramString = replace(paramString, '_q', 'Q');
    set paramString = replace(paramString, '_r', 'R');
    set paramString = replace(paramString, '_s', 'S');
    set paramString = replace(paramString, '_t', 'T');
    set paramString = replace(paramString, '_u', 'U');
    set paramString = replace(paramString, '_v', 'V');
    set paramString = replace(paramString, '_w', 'W');
    set paramString = replace(paramString, '_x', 'X');
    set paramString = replace(paramString, '_y', 'Y');
    set paramString = replace(paramString, '_z', 'Z');
    set paramString = replace(paramString, '_', '');
    SET paramString = REPLACE(paramString, 'applycd', 'applyCd');
    RETURN paramString;
END

 

数据库设计文档

SELECT 
        t.COLUMN_NAME '字段',
        t.COLUMN_COMMENT '字段描述',
        t.COLUMN_TYPE '数据类型',
        IF(t.IS_NULLABLE = 'NO', '√', '') '非空',
        t.COLUMN_DEFAULT '默认值'

FROM information_schema.columns t
INNER JOIN (SELECT @i:=0) seq
WHERE 1=1
AND t.TABLE_SCHEMA = 'carservice'
AND TABLE_NAME = 'c_notice_record';

 

生成文档：

select 
        @name:= underlineToCamel(t.COLUMN_NAME) '列名',
        @type := case 
            when t.DATA_TYPE in ('varchar','char','text','longtext','mediumtext','mediumblob') then 'String'
            when t.DATA_TYPE in ('int','tinyint','integer','smallint','bigint') then 'Integer'
            when t.DATA_TYPE in ('date','datetime','timestamp') then 'Date'
            when t.DATA_TYPE in ('double','decimal') then 'BigDecimal'
        else '' end as 'java类型',
        @memo:= t.COLUMN_COMMENT '列描述',
        CONCAT('/** ', t.COLUMN_COMMENT ,' */ private ', @type, ' ', t.COLUMN_NAME, '; ' ) as 'java类',
        @type2 := case 
            when @type = 'String' then 'VARCHAR'
            when @type = 'Integer' then 'INTEGER'
            when @type = 'Date' then 'TIMESTAMP'
            when @type = 'BigDecimal' then 'DECIMAL'
            else ''
        end as 'mybatis类型',
        CASE 
            WHEN t.COLUMN_KEY = 'PRI' THEN CONCAT('<id column="', t.COLUMN_NAME ,'" property="', @name, '" jdbcType="', @type2, '" />' )
            ELSE CONCAT('<result column="', t.COLUMN_NAME ,'" property="', @name, '" jdbcType="', @type2, '" />' )
        END as 'BaseResultMap',
        CONCAT(t.COLUMN_NAME, " AS ", @name, ",") as 'SELECT',
        CONCAT('#{', @name, '},') AS 'VALUES',
        CONCAT( t.COLUMN_NAME ,' = #{', @name, '},')    as 'SET',
        CONCAT( '<if test="', @name, ' !=null and ', @name, " !=''", '" >  \n        and ', t.COLUMN_NAME,' = #{',@name,'}\n</if>') AS 'IF SET',
        @type3 := case 
            when @type = 'String' then 'StringValue'
            when @type = 'Integer' then 'Int32Value'
            when @type = 'Date' then 'Timestamp'
            when @type = 'BigDecimal' then 'DoubleValue'
            else ''
        end as 'proto类型',
        CONCAT('google.protobuf.', @type3, ' ', @name, ' = ', (@i:=@i+1), ';') as 'proto',
        @len:= REPLACE(REPLACE(SUBSTR(t.COLUMN_TYPE, LOCATE('(', t.COLUMN_TYPE)),'(', ''), ')', '') AS '长度',
        @nullflg := if(t.IS_NULLABLE = 'YES', 'N', 'Y') as '是否为NUll',
        CONCAT('| ',@name, 
                ' | ',  @memo,
                ' | ',  @type,
                ' | ',  @nullflg,
                ' | ',  @len,
                ' | |' ) 'api文档'

from information_schema.columns t
INNER JOIN (SELECT @i:=0) seq
where 1=1
and t.TABLE_SCHEMA = 'dhcs'
and table_name = 'd_collection_summary';

生成字典：

 SELECT CONCAT('/** ', classname, '-', itemcontent ,' */','public final static Integer CLASSCD_', classcd, "__ITEMVALUE_", itemvalue, " = ", itemvalue, ';') FROM `m_code_vtms`;

SELECT classcd, GROUP_CONCAT( CONCAT(itemvalue, ":", itemcontent, "") ) FROM `m_code_vtms` GROUP BY classcd;