sql语句方法

最新推荐文章于 2023-03-14 13:13:55 发布

原创最新推荐文章于 2023-03-14 13:13:55 发布 · 218 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

未来可期同时被 2 个专栏收录

5 篇文章

订阅专栏

工具类

5 篇文章

订阅专栏

本文介绍了几种常用的SQL命令来管理表数据，包括清空表数据的不同方法及其适用场景，如DELETE、TRUNCATE和DROP的区别；并通过实例展示了如何利用CASE WHEN结合GROUP BY实现数据的行列转换，还提供了一种高效的大规模数据删除策略。

清空表数据方法

delete ----逐行清楚，不适合大量数据

Delete from tablename where 1=1

truncate ----删除所有数据，保留表结构，不能撤销还原

TRUNCATE TABLE tablename

drop ----删除表，数据和表结构一起删除，最快速

## 重新创建
DROP TABLE IF EXISTS `docker2`;
CREATE TABLE `docker2` (
  `name` varchar(255) NOT NULL,
  `path` varchar(500) DEFAULT NULL,
  `docker1_name` varchar(255) NOT NULL,
  `tag1` varchar(255) DEFAULT NULL,
  `tag2` varchar(255) DEFAULT NULL,
  `tag3` varchar(255) DEFAULT NULL,
  `tag4` varchar(255) DEFAULT NULL,
  `tag5` varchar(255) DEFAULT NULL,
  `tag6` varchar(255) DEFAULT NULL,
  `tag7` varchar(255) DEFAULT NULL,
  `tag8` varchar(255) DEFAULT NULL,
  `tag9` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`docker1_name`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

sql数据《行列转换》

max(case  when ... then ... else ... end)   
需要与 **group by**一起使用。
case when :实现行列转换时会出现多条记录，若不聚合直接进行group by分组，则会检索第一条数据，若使用聚合max()函数再尽心group by 分组，就会检索最大值然后进行分组

eg：多表关联实现行列转换

 <sql id="listUnitRecentReportSQL">
        SELECT
            NU.UUID,
            NU.CODE,
            NU.NAME,
            NU.UNIT_NATURE,
            dict.DICT_LABEL as UNIT_NATURE_NAME,
            NU.SYS_CODE,
            AA.LIAISONS,
            AA.TELEPHONE,
            AA.MOBILEPHONE,
            NUF.YEAR,
            NUF.M1,
            NUF.M2,
            NUF.M3,
            NUF.M4,
            NUF.M5,
            NUF.M6,
            NUF.M7,
            NUF.M8,
            NUF.M9,
            NUF.M10,
            NUF.M11,
            NUF.M12
        FROM
            APP_NET_UNIT NU
        LEFT JOIN
            VIEW_NET_UNIT_CONTACT_PERSON AA
        ON
            NU.UUID = AA.UUID
        LEFT JOIN
        (
            SELECT NET_UNIT_UUID,
            YEAR,
            MAX(CASE  WHEN  MONTH = 1 THEN CREATE_DATE END) AS M1,
            MAX(CASE  WHEN  MONTH = 2 THEN CREATE_DATE END) AS M2,
            MAX(CASE  WHEN  MONTH = 3 THEN CREATE_DATE END) AS M3,
            MAX(CASE  WHEN  MONTH = 4 THEN CREATE_DATE END) AS M4,
            MAX(CASE  WHEN  MONTH = 5 THEN CREATE_DATE END) AS M5,
            MAX(CASE  WHEN  MONTH = 6 THEN CREATE_DATE END) AS M6,
            MAX(CASE  WHEN  MONTH = 7 THEN CREATE_DATE END) AS M7,
            MAX(CASE  WHEN  MONTH = 8 THEN CREATE_DATE END) AS M8,
            MAX(CASE  WHEN  MONTH = 9 THEN CREATE_DATE END) AS M9,
            MAX(CASE  WHEN  MONTH = 10 THEN CREATE_DATE END) AS M10,
            MAX(CASE  WHEN  MONTH = 11 THEN CREATE_DATE END) AS M11,
            MAX(CASE  WHEN  MONTH = 12 THEN CREATE_DATE END) AS M12
            from(
                SELECT
                NET_UNIT_UUID,
                YEAR(CREATE_DATE) AS YEAR,
                MONTH(CREATE_DATE) AS MONTH,
                MAX(CREATE_DATE) CREATE_DATE
                FROM
                APP_NET_UNIT_UPLOAD_FILE
                where 1 = 1
                <if test="unitRecentReport.year != null" >
                    and YEAR(CREATE_DATE) = #{unitRecentReport.year}
                </if>
                GROUP BY NET_UNIT_UUID, YEAR(CREATE_DATE),MONTH(CREATE_DATE)
            ) AS t
            group by NET_UNIT_UUID,YEAR
        ) NUF
        ON
            NUF.NET_UNIT_UUID=NU.UUID
        LEFT JOIN
          (SELECT DICT_VALUE, DICT_LABEL FROM SYS_DICT_DATA WHERE DICT_TYPE='unit_nature') dict
        on
          NU.UNIT_NATURE = dict.DICT_VALUE
        <if test="userUuid != null" >
        LEFT JOIN
            APP_NET_UNIT_MANAGER num
        on
            num.NET_UNIT_UUID = nu.uuid
        </if>
        where 1=1
        <if test="unitRecentReport.name != null" >
            and nu.name like concat('%', #{unitRecentReport.name}, '%')
        </if>
        <if test="unitRecentReport.sysCode != null" >
            and nu.sys_code like concat(#{unitRecentReport.sysCode}, '%')
        </if>
        <if test="userUuid != null" >
            and num.USER_UUID = #{userUuid}
        </if>
        order by nuf.year, nu.sys_code
    </sql>

做大量删除数据

1.//把要删除大量数据的表中不需要删除的数据转存到另外一张表中
INSERT INTO 新建的表 SELECT * FROM 表 WHERE ...

2.//同时重明明两张表，间接实现数据删除操作  
RENAME TABLE t TO t_old, 新建的表 TO t;

3.将那个没用的表删除掉 drop table t_old

eg:
INSERT INTO 新建的表 SELECT * FROM 表 WHERE 部门标号 !=20  //将不删除的表转存
RENAME TABLE 表 TO t_old, 新建的表 TO 表;   //重新定义表名
删除不要的表 drop table t_old