mysql 基础

#ON UPDATE CURRENT_TIMESTAMP属性，修改数据时，这行数据的时间发生改变
drop table if EXISTS order_today;
CREATE TABLE order_today(
    id varchar(32) not NULL COMMENT '主键',
    merchant_id varchar(32) CHARACTER set utf8 COLLATE utf8_general_ci not null COMMENT '商家编号',
    amount decimal(15,2) not null COMMENT '订单金额',
    pay_success_time datetime not null COMMENT '支付成功时间',
    order_status varchar(10) character set utf8 collate utf8_general_ci not null comment '支付状态 S:支付成功 F：支付失败',
    remark varchar(100) character set utf8 collate utf8_general_ci not null comment '备注',
    create_time timestamp not null default CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time timestamp not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT  '修改时间--修改时自动更新',
    primary key (id) USING BTREE,
    key idx_merchant_id(merchant_id) USING BTREE COMMENT '商户编号'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

insert into order_today(id,merchant_id,amount,pay_success_time,order_status,remark,CREATE_time)
VALUES
(1,1,150,CURRENT_TIMESTAMP,'S','电饭锅',CURRENT_TIMESTAMP)

SELECT CURRENT_DATE; #2021-04-21
SELECT CURRENT_TIME; #10:13:16
SELECT CURRENT_TIMESTAMP; #2021-04-21 10:13:16
SELECT CURRENT_USER; #root@localhost

drop TABLE if EXISTS order_table;
create table order_table(
id varchar(32) not NULL comment '编号',
wares_id varchar(32) character set utf8 COLLATE utf8_general_ci not null comment '产品编号',
pay_success_time datetime not null comment '支付成功时间',
order_status varchar(10) CHARACTER set utf8 COLLATE utf8_general_ci not null COMMENT '支付状态 S or F',
remark varchar(100) character set utf8 collate utf8_general_ci not null COMMENT '备注',
CREATE_Time TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY key (id) USING BTREE,
KEY idx_wares_id(wares_id) USING BTREE COMMENT '产品编号'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

DROP TABLE if EXISTS order_user;
create table order_user(
id int(10) not null auto_increment COMMENT '编号',
wares_id varchar(32) not null COMMENT '产品编号',
pay_success_time datetime not null comment '支付成功时间',
order_status varchar(10) CHARACTER set utf8 COLLATE utf8_general_ci not null COMMENT '支付状态',
remark varchar(100) not null COMMENT '备注',
update_time TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY key (id) USING BTREE,
KEY idx_wares_id(wares_id) COMMENT '产品编号'
)ENGINE = INNODB auto_increment= 100 CHARSET=UTF8
#修改字段数据类型
alter table order_user change id id int(10);
#查看字段信息
desc order_user;
#查看创建语言
show create table order_user;

DROP TABLE IF EXISTS order_user;
CREATE TABLE `order_user` (
  `id` int(10) NOT NULL,
  `wares_id` varchar(32) NOT NULL COMMENT '产品编号',
  `pay_success_time` datetime NOT NULL COMMENT '支付成功时间',
  `order_status` varchar(10) NOT NULL COMMENT '支付状态',
  `remark` varchar(100) NOT NULL COMMENT '备注',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_wares_id` (`wares_id`) COMMENT '产品编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#FORCE INDEX 根据索引进行查询
insert into order_user
SELECT * from order_user
FORCE INDEX(idx_wares_id);

#删除主键索引
ALTER TABLE order_user drop PRIMARY key