作业1-数据表管理操作-添加、修改、删除表记录

关注

原创 2025-11-05 11:29:27 278 阅读 AI 写同款· GEO 优化 ›

Hive表结构变更实战：添加、修改、删除列的原理与避坑指南在数据仓库和数据分析领域，表结构变更是一项基础且频繁的操作，其核心在于理解元数据与数据文件的分离架构。Hive作为主流的数据仓库工具，通过修改存储在独立数据库中的元数据来实现轻量级的结构变更，例如添加新列通常只更新元数据，而不会重写底层HDFS或对象存储中的数据文件，这体现了其“读时模式”的设计原理。这种机制在提升操作效率的同时，也带来了数据兼容性挑战，尤其是在处理分区表、外部表以及ORC、Parquet等列式存储格式时。从技术价值看，掌握结构变更的正确方法能确保数据管道的稳定性和回溯兼容性，避免下游任务因阅读详情

create database test;
use test;
create table teacher
(
    id       int(4) auto_increment comment '编号',
    num      int(10)     not null comment '教工号',
    name     varchar(20) not null comment '姓名',
    sex      varchar(4)  not null comment '性别',
    birthday datetime    null comment '出生日期',
    address  varchar(50) null comment '家庭住址',
    constraint teacher_pk
        primary key (id)
);
insert into teacher value ('1','1001','张三','男','1984-11-08','北京市昌平区');
insert into teacher value ('2','1002','李四','女','1970-01-21','北京市海淀区');
insert into teacher value (null,'1003','王五','男','1976-10-30','北京市昌平区');
insert into teacher value (null,'1004','赵六','男','1980-06-05','北京市顺义区');

update teacher
set birthday = '1982-11-08'
where id = 1;

update teacher
set address = '北京市朝阳区'
where sex = '男';

delete
from teacher
where num = 1002;

create table food
(
    id      int(10) auto_increment comment '编号'
        primary key,
    name    varchar(20) not null comment '食品名称',
    company varchar(30) not null comment '生产厂商',
    price   float       null comment '价格',
    product_time year comment '生产年份',
    validity_time int(4) comment '保质期',
    address varchar(50) comment '厂址'
);
insert into food value ('1','aa饼干','aa饼干厂','2.5','2018','3','北京');
insert into food value('2','cc牛奶','cc牛奶厂','3.5','2015','1','河北');
insert into food value(null,'ee果冻','ee果冻厂','1.5','2018','2','北京');
insert into food value('null','ff咖啡','ff咖啡厂','20','2016','5','天津');
insert into food value(null,'gg奶糖','gg奶糖','14','2014','3','广东');
update food
set address = '内蒙古'
where name = 'cc牛奶厂';
update food
set price  = '3.2'
where name = 'cc牛奶厂';
update food
set validity_time = '5'
where address = '北京';
delete from food where (2025-food.product_time )> food.validity_time;

delete from food where address = '北京';