数据库中一些常用SQL使用方法记录

开发出身的程序员们，因为本职工作主要是做一些编码的工作，因此对于一些特殊场景的SQL就不是天天用了，所以在此做以记录，方便后续的查询与参考

• DDL(Data Definition Language)
• DML(Data Manipulation Language)
• DCL(Data Control Language)
• TCL(transaction Control Language)
• PROCEDURE

DDL

1. 命令行创建一个表并创建用户, 再授权访问

# Mysql
create databse short_url_db DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
create user 'short_url_user'@'%' identified by 'welcome';
grant all on short_url_db.* to 'short_url_user'@'%';

2. 以一个已经存在的表作为模板, 创建一个新表

# Mysql
create table <table_name_new> like <table_name_old>
# Postgres
create table <table_name_new> (like <table_name_old> including all)

3. 修改一个库的字符编码和字符集

# Mysql
##1.先查询库(schema)的编码和字符集
ELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = <some_database> ;
##2.进行修改
ALTER DATABASE <some_database> character set utf8mb4 collate utf8mb4_unicode_ci;
# Postgres

4. 修改一张表的字符编码和字符集

# Mysql
alter table <some_table> convert to character set utf8mb4 collate utf8mb4_unicode_ci;
# Postgres

DML

1. 批量修改某张表里面的一些数据

# 将某一个字段内容的前缀进行统一修改
UPDATE <table_name> tanm
INNER JOIN (
    SELECT
        NO_MODE_ID,
        CASE
    WHEN GEN_PATTERN LIKE 'f:T%' THEN
        CONCAT(
            'f:D',
            substring(GEN_PATTERN, 4)
        )
    ELSE
        CONCAT(
            'f:D',
            substring(GEN_PATTERN, 3)
        )
    END AS GEN_PATTERN
    FROM
        <table_name>
) v ON tanm.NO_MODE_ID= v.NO_MODE_ID
SET tanm.GEN_PATTERN = v.GEN_PATTERN

PROCEDURE

1. 使用存储过程批量修改数据

1. 一个表中包含一个业务sort字段, 由于一些原因导致出现了多个记录具有相同sort的错误数据，现在就需要重新设置sort字段，保证不重复;

DROP PROCEDURE IF EXISTS resetSort;
CREATE PROCEDURE resetSort()
BEGIN
	DECLARE _id bigint default 0;
	DECLARE i int default 0;
	
	DECLARE cnt bigint default 0;
	DECLARE ids cursor for SELECT id FROM health_product_catalog WHERE is_delete = 0 ORDER BY ctl_sort ASC;
	
	SELECT COUNT(1) INTO cnt FROM health_product_catalog WHERE is_delete = 0;
	
	OPEN IDS;
		WHILE i<=cnt DO
			FETCH ids INTO _id;
			SET i = i + 1;
			UPDATE health_product_catalog SET ctl_sort = i WHERE id = _id;
		END WHILE;
	CLOSE IDS;
END;
CALL resetSort();

业务技巧

1. 列表排序的前移, 后移, 置顶, 置底;

UPDATE health_product_catalog x,
        health_product_catalog y,
        (
        SELECT
        a.id, ctl_sort
        FROM
        health_product_catalog a
        WHERE
        a.is_delete = 0
        and ctl_parent_id = #{ctlParentId, jdbcType=VARCHAR}
        <choose>
            <when test="move=='top'">
                ORDER BY a.ctl_sort ASC
            </when>
            <when test="move=='up'">
                <![CDATA[ and a.ctl_sort < #{currentOrder, jdbcType=INTEGER} ]]>
                ORDER BY a.ctl_sort DESC
            </when>
            <when test="move=='down'">
                <![CDATA[ and a.ctl_sort > #{currentOrder, jdbcType=INTEGER} ]]>
                ORDER BY a.ctl_sort ASC
            </when>
            <when test="move=='bottom'">
                ORDER BY a.ctl_sort DESC
            </when>
        </choose>
        LIMIT 1
        ) v
        SET x.ctl_sort = v.ctl_sort,
        y.ctl_sort = #{currentOrder, jdbcType=INTEGER}
        WHERE x.id = #{id,jdbcType=BIGINT}
        AND y.id = v.id