当前位置：首页 > article >正文

MySQL实用SQL示例

article 2025/1/5 21:14:26

创建数据库

CREATE DATABASE `zq-cloud` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

创建表

CREATE TABLE `regulatory_firm_category`  (
  `category_id` int NOT NULL AUTO_INCREMENT COMMENT '分类id，自增主键',
  `parent_id` int NOT NULL COMMENT '父级id',
  `category_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '分类名称',
    `sort_order` int DEFAULT -1 COMMENT '排序值',
  `create_by` int NOT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
    `update_by` int DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注信息',
  PRIMARY KEY (`category_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '企业分类信息表' ROW_FORMAT = DYNAMIC;

添加字段

ALTER TABLE risk_facility ADD principal BIGINT COMMENT '管控负责人';

修改字段类型

ALTER TABLE hazard_plan_task MODIFY risk_point VARCHAR(255) NOT NULL DEFAULT '-1';

查询库占用内存大小

SELECT table_schema AS 'Database', CONCAT(ROUND(SUM(data_length + index_length) / 1024 / 1024, 2), ' MB') AS 'Size (MB)' FROM information_schema.TABLES
GROUP BY table_schema;