【免费】数仓项目实战三！里的sql修改时需要的命令资源-CSDN下载

共2个文件

sql：2个

需积分: 0 160 浏览量 2024-09-27 10:24:32 上传评论收藏 433KB 7Z 举报

在数仓项目中，SQL（Structured Query Language）是不可或缺的工具，用于数据查询、更新、管理和控制。当需要修改数据库中的数据或结构时，掌握正确的SQL命令至关重要。以下是一些常见的SQL修改命令及其使用场景的详细解释。 1. INSERT INTO 使用INSERT INTO命令可以向数据库的表中添加新的数据行。在数仓项目中，经常需要插入新的记录，以补充数据源或更新数据仓库中的维度表。其基本语法如下： ```sql INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); ``` 在这里，table_name代表目标表，columns是要插入数据的列名，values则是对应的数据值。 2. UPDATE UPDATE命令用于修改表中的现有记录。在数仓项目中，随着数据源的变化，可能需要更新事实表或维度表中的数据。UPDATE语句的基本格式是： ```sql UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; ``` 这里，table_name是目标表名，set后的部分指定了要更新的列和新值，where子句用来指定哪些记录需要被更新。 3. DELETE DELETE命令用于从表中删除记录。在数仓项目中，应谨慎使用DELETE命令，因为它会导致数据的永久丢失。其基本语法如下： ```sql DELETE FROM table_name WHERE condition; ``` table_name是要操作的表名，condition定义了要删除的记录的条件。 4. ALTER TABLE ALTER TABLE命令用于添加、删除或修改表的结构。在数仓项目中，随着业务需求的变化，可能需要对表结构进行调整。以下是一些ALTER TABLE的常用操作： ```sql -- 添加列 ALTER TABLE table_name ADD column_name column_type; -- 删除列 ALTER TABLE table_name DROP COLUMN column_name; -- 修改列数据类型 ALTER TABLE table_name MODIFY column_name new_data_type; ``` 在这里，table_name是目标表名，column_name是要添加、删除或修改的列名，column_type是该列的数据类型，new_data_type是新的数据类型。 5. CREATE INDEX CREATE INDEX命令用于创建索引，可以提高查询的效率。在数仓项目中，对表进行查询优化时，可能会创建索引。其基本语法如下： ```sql CREATE INDEX index_name ON table_name (column_name); ``` index_name是索引的名称，table_name是目标表名，column_name是需要索引的列名。 6. TRUNCATE TABLE TRUNCATE TABLE命令用于删除表中的所有记录，同时释放表占用的空间。在数仓项目中，当需要清空表数据且不保留数据时，可以使用此命令。其基本语法如下： ```sql TRUNCATE TABLE table_name; ``` table_name是目标表名。 7. DROP TABLE DROP TABLE命令用于删除整个表及其结构。在数仓项目中，如果确定不再需要某个表时，可以使用此命令彻底删除。其基本语法如下： ```sql DROP TABLE table_name; ``` table_name是目标表名。了解和熟练使用这些SQL修改命令对于数仓项目中的数据维护和更新是基础。在实际操作过程中，应结合数仓的数据模型和业务逻辑，合理地使用这些命令，以确保数据的一致性和准确性。

资源推荐

资源详情

资源评论

收起资源包目录

sql修改.7z （2个子文件）

sql修改

repay_plan_item.sql 6.36MB

repay_plan_item_his.sql 2.36MB

/* Navicat Premium Data Transfer Source Server : hadoop11 Source Server Type : MySQL Source Server Version : 80026 (8.0.26) Source Host : hadoop11:3306 Source Schema : jrxd Target Server Type : MySQL Target Server Version : 80026 (8.0.26) File Encoding : 65001 Date: 27/06/2024 12:11:57 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for repay_plan_item -- ---------------------------- DROP TABLE IF EXISTS `repay_plan_item`; CREATE TABLE `repay_plan_item` ( `id` bigint NOT NULL AUTO_INCREMENT, `drawal_apply_id` bigint NULL DEFAULT NULL, `repay_plan_id` bigint NOT NULL, `repay_term` int NOT NULL, `status` int NOT NULL, `due_date` datetime NOT NULL, `dest_principal` bigint NOT NULL, `dest_interest` bigint NOT NULL, `dest_service` bigint NOT NULL, `dest_pty_interest` bigint NOT NULL, `dest_pty_fee` bigint NOT NULL, `dest_pty_late_fee` bigint NOT NULL, `dest_gutee_fee` bigint NULL DEFAULT NULL, `dest_gutee_service_fee` bigint NULL DEFAULT NULL, `dest_other` bigint NOT NULL DEFAULT 0, `paid_principal` bigint NOT NULL, `paid_interest` bigint NOT NULL, `paid_service` bigint NOT NULL, `paid_pty_interest` bigint NOT NULL, `paid_pty_fee` bigint NOT NULL, `paid_pty_late_fee` bigint NOT NULL, `paid_gutee_fee` bigint NULL DEFAULT NULL, `paid_gutee_service_fee` bigint NULL DEFAULT NULL, `paid_other` bigint NOT NULL DEFAULT 0, `reduce_principal` bigint NOT NULL DEFAULT 0, `reduce_interest` bigint NOT NULL DEFAULT 0, `reduce_service` bigint NOT NULL DEFAULT 0, `reduce_pty_interest` bigint NOT NULL DEFAULT 0, `reduce_pty_fee` bigint NOT NULL DEFAULT 0, `reduce_pty_lat_fee` bigint NOT NULL DEFAULT 0, `reduce_gutee_fee` bigint NULL DEFAULT NULL, `reduce_gutee_service_fee` bigint NULL DEFAULT NULL, `reduce_other` bigint NOT NULL DEFAULT 0, `overdue_days` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0', `actual_repay_date` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `total_un_paid_amt` bigint NULL DEFAULT 0, `updated_at` datetime NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 673119152 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of repay_plan_item -- ---------------------------- INSERT INTO `repay_plan_item` VALUES (671038734, 421196032, 67302660, 1, 2, '2020-01-01 00:00:00', 48963, 2513, 3008, 51, 0, 3, 4050, 16, 0, 48963, 2513, 3008, 51, 0, 3, 4050, 16, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '1', '0000-00-00 00:00:00', 0, '2020-06-13 05:04:00', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038735, 421196032, 67302660, 2, 2, '2020-02-01 00:00:00', 49373, 2103, 3008, 257, 0, 15, 4050, 16, 0, 49373, 2103, 3008, 257, 0, 15, 4050, 16, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '5', '0000-00-00 00:00:00', 0, '2020-06-13 05:04:00', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038736, 421196032, 67302660, 3, 1, '2020-03-01 00:00:00', 49787, 1689, 3008, 360, 0, 22, 4050, 16, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '225', '0000-00-00 00:00:00', 58932, '2020-10-12 02:00:19', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038737, 421196032, 67302660, 4, 1, '2020-04-01 00:00:00', 50204, 1272, 3008, 360, 0, 22, 4050, 16, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '194', '0000-00-00 00:00:00', 58932, '2020-10-12 02:00:19', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038738, 421196032, 67302660, 5, 1, '2020-05-01 00:00:00', 50624, 852, 3008, 360, 0, 22, 4050, 16, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '164', '0000-00-00 00:00:00', 58932, '2020-10-12 02:00:19', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038739, 421196032, 67302660, 6, 1, '2020-06-01 00:00:00', 51049, 427, 3008, 360, 0, 22, 4050, 16, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '133', '0000-00-00 00:00:00', 58932, '2020-10-12 02:00:19', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038740, 421196104, 67302661, 1, 2, '2020-01-01 00:00:00', 39782, 4188, 5170, 0, 0, 0, 6750, 27, 0, 39782, 4188, 5170, 0, 0, 0, 6750, 27, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0', '0000-00-00 00:00:00', 0, '2019-12-29 15:16:02', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038741, 421196104, 67302661, 2, 2, '2020-02-01 00:00:00', 40115, 3855, 5170, 0, 0, 0, 6750, 27, 0, 40115, 3855, 5170, 0, 0, 0, 6750, 27, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0', '0000-00-00 00:00:00', 0, '2020-01-31 11:35:00', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038742, 421196104, 67302661, 3, 2, '2020-03-01 00:00:00', 40451, 3519, 5170, 0, 0, 0, 6750, 27, 0, 40451, 3519, 5170, 0, 0, 0, 6750, 27, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0', '0000-00-00 00:00:00', 0, '2020-02-27 21:45:01', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038743, 421196104, 67302661, 4, 2, '2020-04-01 00:00:00', 40790, 3180, 5170, 0, 0, 0, 6750, 27, 0, 40790, 3180, 5170, 0, 0, 0, 6750, 27, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0', '0000-00-00 00:00:00', 0, '2020-03-30 08:30:16', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038744, 421196104, 67302661, 5, 2, '2020-05-01 00:00:00', 41132, 2838, 5170, 0, 0, 0, 6750, 27, 0, 41132, 2838, 5170, 0, 0, 0, 6750, 27, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0', '0000-00-00 00:00:00', 0, '2020-04-27 10:38:10', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038745, 421196104, 67302661, 6, 2, '2020-06-01 00:00:00', 41476, 2494, 5170, 0, 0, 0, 6750, 27, 0, 41476, 2494, 5170, 0, 0, 0, 6750, 27, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0', '0000-00-00 00:00:00', 0, '2020-05-30 07:49:27', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038746, 421196104, 67302661, 7, 2, '2020-07-01 00:00:00', 41823, 2147, 5170, 0, 0, 0, 6750, 27, 0, 41823, 2147, 5170, 0, 0, 0, 6750, 27, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0', '0000-00-00 00:00:00', 0, '2020-07-01 07:01:10', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038747, 421196104, 67302661, 8, 2, '2020-08-01 00:00:00', 42174, 1796, 5170, 0, 0, 0, 6750, 27, 0, 42174, 1796, 5170, 0, 0, 0, 6750, 27, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0', '0000-00-00 00:00:00', 0, '2020-08-01 23:01:51', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038748, 421196104, 67302661, 9, 2, '2020-09-01 00:00:00', 42527, 1443, 5170, 0, 0, 0, 6750, 27, 0, 42527, 1443, 5170, 0, 0, 0, 6750, 27, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0', '0000-00-00 00:00:00', 0, '2020-09-02 00:00:15', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038749, 421196104, 67302661, 10, 2, '2020-10-01 00:00:00', 42883, 1087, 5170, 0, 0, 0, 6750, 27, 0, 42883, 1087, 5170, 0, 0, 0, 6750, 27, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0', '0000-00-00 00:00:00', 0, '2020-10-01 22:00:20', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038750, 421196104, 67302661, 11, 2, '2020-11-01 00:00:00', 43242, 728, 5170, 0, 0, 0, 6750, 27, 0, 43242, 728, 5170, 0, 0, 0, 6750, 27, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0', '0000-00-00 00:00:00', 55917, '2020-10-10 23:00:00', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038751, 421196104, 67302661, 12, 2, '2020-12-01 00:00:00', 43605, 365, 5170, 0, 0, 0, 6750, 27, 0, 43605, 365, 5170, 0, 0, 0, 6750, 27, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0', '0000-00-00 00:00:00', 55917, '2020-10-10 23:00:00', '2019-12-01 07:00:02'); INSERT INTO `repay_plan_item` VALUES (671038752, 421196132, 67302662, 1, 2, '2020-01-01 00:00:00', 48963, 2513, 3008, 0, 0, 0, 4050, 16, 0, 48963, 2513, 3008, 0, 0, 0, 4050, 16, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0', '0000-00-00 00:00:00', 0, '2019-12-16 10:34:43', '2019-12-01 07:39:55'); INSERT INTO `repay_plan

评论收藏

内容反馈