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

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 = '北京';