MySQL常用sql语句

1、指定字段后插入新字段

ALTER TABLE enn_clue ADD remark_type VARCHAR (1) COMMENT '备注类型' after buz_type,
ADD remark VARCHAR (255) COMMENT '备注' after remark_type

 2、修改列类型

ALTER TABLE enn_contract MODIFY COLUMN short_code VARCHAR (255),
 MODIFY COLUMN full_code VARCHAR (255)

 3.not in的注意问题

　　not in 在字段值有null的情况下会导致所有的数据查询不到。

 4.删除重复的数据只保留一条

DELETE
FROM
    enn_trade_back_payment
WHERE
    transaction_code IN (
        SELECT
            a.transaction_code
        FROM
            (
                SELECT
                    transaction_code
                FROM
                    `enn_trade_back_payment`
                WHERE
                    `data_source` LIKE '%网银导入%'
                GROUP BY
                    transaction_code
                HAVING
                    count(transaction_code) > 1
            ) a
    )
AND id NOT IN (
    SELECT
        b.id
    FROM
        (
            SELECT
                min(id) AS id
            FROM
                `enn_trade_back_payment`
            WHERE
                `data_source` LIKE '%网银导入%'
            GROUP BY
                transaction_code
            HAVING
                count(transaction_code) > 1
        ) b
)