工作记录------常用SQL

本是古典何须时尚 2024-03-25 06:02 112阅读 0赞

## 工作记录------常用SQL ##

### 给表新增字段以及注释 ###

ALTER TABLE 表名 ADD 字段名 字段类型 DEFAULT NULL comment ‘注释’;

> ALTER TABLE t\_user ADD user\_id VARCHAR(64) DEFAULT NULL comment ‘userId（关联字典表）’;

### 给表字段以及注释，修改表字段 ###

> alter table t\_user user\_id VARCHAR(32) NULL DEFAULT NULL COMMENT ‘用户id’ COLLATE ‘utf8\_general\_ci’;

### 导出表结构 ###

> SELECT  
> COLUMN\_NAME 列名,  
> COLUMN\_TYPE 数据类型,  
> DATA\_TYPE 字段类型,  
> CHARACTER\_MAXIMUM\_LENGTH 长度,  
> IS\_NULLABLE 是否为空,  
> COLUMN\_DEFAULT 默认值,  
> COLUMN\_COMMENT 备注,  
> table\_name 表名  
> FROM  
> INFORMATION\_SCHEMA.COLUMNS  
> WHERE  
> – 填写要导出表结构的数据库名称即可  
> table\_schema = ‘test’  
> – order by table\_name  
> – 如果不写的话，默认查询所有表中的数据  
> AND table\_name = ‘table\_1’;

### 建表语句 ###

> CREATE TABLE `t_p_ion` (  
> `ID` VARCHAR(32) NOT NULL COMMENT ‘主键’ COLLATE ‘utf8\_general\_ci’,  
> `W_Ordde` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘订单代码’ COLLATE ‘utf8\_general\_ci’,  
> `W_Ce` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘工作代码’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Opde` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘型号编码’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Rime` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘型号名称’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_OItQty` DECIMAL(18,3) NULL DEFAULT NULL COMMENT ‘数量’,  
> `Ws_Oode` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘主资源’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_OStT` DATETIME NULL DEFAULT NULL COMMENT ‘计划开始时间’,  
> `Ws_OpdTim` DATETIME NULL DEFAULT NULL COMMENT ‘计划结束时间’,  
> `Ws_Opime` DECIMAL(18,3) NULL DEFAULT NULL COMMENT ‘节拍’,  
> `Ws_Stus` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘生产状态’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Sn` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘版本’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Bt` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘是否已打印条码’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Plour` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘计划生产小时’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Itepe` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘产品类型’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Bde` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘条码号’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_frode` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘条码前码（除去最后四位流水号的条码）’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Daode` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘日作业合并依据’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Fary` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘工厂’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Oe` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘SAP订单类型’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Unit` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘单位’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Leder` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘子订单’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Rder` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘父订单’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Lerder` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘最末子订单’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Righder` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘最末父订单’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Linme` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘线体名称’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Lide` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘线体编码’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Reme` DATETIME NULL DEFAULT NULL COMMENT ‘实际开始时间’,  
> `Ws_Reme` DATETIME NULL DEFAULT NULL COMMENT ‘实际结束时间’,  
> `Ws_IsT` INT(11) NULL DEFAULT NULL COMMENT ‘是否一次通过 1：是 0：否’,  
> `TisFlag` INT(11) NULL DEFAULT NULL COMMENT ‘是否已时序拉动’,  
> `Insult` INT(11) NULL DEFAULT NULL COMMENT ‘检验结果’,  
> `Ws_Cude` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘客户编码’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_CurInfo` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘流水码后四位’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Cone` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘电话’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Coame` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘联系人’ COLLATE ‘utf8\_general\_ci’,  
> `Ws_Schate` DATETIME NULL DEFAULT NULL COMMENT ‘排产日期’,  
> `Wsr_Traode` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘客户地址’ COLLATE ‘utf8\_general\_ci’,  
> `Wser_Trme` VARCHAR(128) NULL DEFAULT ‘0’ COMMENT ‘客户城市’ COLLATE ‘utf8\_general\_ci’,  
> `Wsser_Cde` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘渠道编码’ COLLATE ‘utf8\_general\_ci’,  
> `Wser_Ce` VARCHAR(128) NULL DEFAULT NULL COMMENT ‘渠道名称’ COLLATE ‘utf8\_general\_ci’,  
> `JFg` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘积放链’ COLLATE ‘utf8\_general\_ci’,  
> `Cg` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘成品标识’ COLLATE ‘utf8\_general\_ci’,  
> `Fg` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘辅料标识’ COLLATE ‘utf8\_general\_ci’,  
> `DFlag` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘报工标识’ COLLATE ‘utf8\_general\_ci’,  
> `T_WS_1` VARCHAR(32) NULL DEFAULT NULL COMMENT ‘T-1计划标识’ COLLATE ‘utf8\_general\_ci’,  
> `LinFlag` VARCHAR(32) NULL DEFAULT NULL COMMENT ‘线边库冲减的标示’ COLLATE ‘utf8\_general\_ci’,  
> `CUER_CODE` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘电商订单号’ COLLATE ‘utf8\_general\_ci’,  
> `BAK` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘电商标识码’ COLLATE ‘utf8\_general\_ci’,  
> `Immp` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘默认值图片’ COLLATE ‘utf8\_general\_ci’,  
> `WStatus` INT(11) NULL DEFAULT NULL COMMENT ‘状态 1：已入库 2：已发货’,  
> `Diex` INT(11) NULL DEFAULT NULL COMMENT ‘显示顺序’,  
> `Rerk` VARCHAR(256) NULL DEFAULT NULL COMMENT ‘描述’ COLLATE ‘utf8\_general\_ci’,  
> `Create_By` VARCHAR(32) NULL DEFAULT NULL COMMENT ‘创建人’ COLLATE ‘utf8\_general\_ci’,  
> `Create_Date` DATETIME NOT NULL COMMENT ‘创建时间’,  
> `Last_Update_By` VARCHAR(32) NULL DEFAULT NULL COMMENT ‘最后更新人’ COLLATE ‘utf8\_general\_ci’,  
> `Last_Update_Date` DATETIME NULL DEFAULT NULL COMMENT ‘最后更新时间’,  
> `Active` SMALLINT(6) NULL DEFAULT NULL COMMENT ‘可用标识’,  
> `DFlage` INT(11) NULL DEFAULT NULL COMMENT ‘数据同步标志’,  
> `code` VARCHAR(32) NULL DEFAULT NULL COMMENT ‘校验码’ COLLATE ‘utf8\_general\_ci’,  
> `oid` VARCHAR(512) NULL DEFAULT NULL COMMENT ‘二维码’ COLLATE ‘utf8\_general\_ci’,  
> `sitd` VARCHAR(64) NULL DEFAULT NULL COLLATE ‘utf8\_general\_ci’,  
> `sitede` VARCHAR(64) NULL DEFAULT NULL COLLATE ‘utf8\_general\_ci’,  
> `WarCode` VARCHAR(255) NULL DEFAULT NULL COMMENT ‘随机条码号’ COLLATE ‘utf8\_general\_ci’,  
> `Exe` VARCHAR(255) NULL DEFAULT NULL COMMENT ‘外销码’ COLLATE ‘utf8\_general\_ci’,  
> `EnRL` VARCHAR(512) NULL DEFAULT NULL COMMENT ‘能耗贴’ COLLATE ‘utf8\_general\_ci’,  
> `encrcode` VARCHAR(128) NULL DEFAULT NULL COMMENT ‘加密条码(有随机码的以随机码为准)’ COLLATE ‘utf8\_general\_ci’,  
> `searcode` VARCHAR(100) NULL DEFAULT NULL COMMENT ‘自制半成品条码’ COLLATE ‘utf8\_general\_ci’,  
> `ip` VARCHAR(1) NULL DEFAULT ‘0’ COMMENT ‘是否已对应成品,0未对应,1已对应’ COLLATE ‘utf8\_general\_ci’,  
> `geide` VARCHAR(255) NULL DEFAULT NULL COMMENT ‘隐藏码和ge码’ COLLATE ‘utf8\_general\_ci’,  
> `Assber` VARCHAR(32) NULL DEFAULT NULL COMMENT ‘资产号’ COLLATE ‘utf8\_general\_ci’,  
> `Wde` VARCHAR(255) NULL DEFAULT NULL COMMENT ‘对应成品订单号’ COLLATE ‘utf8\_general\_ci’,  
> `Cier` VARCHAR(255) NULL DEFAULT NULL COMMENT ‘新改为:校验码’ COLLATE ‘utf8\_general\_ci’,  
> `Entede` VARCHAR(64) NULL DEFAULT NULL COMMENT ‘企业编码’ COLLATE ‘utf8\_general\_ci’,  
> `Enterd` VARCHAR(32) NULL DEFAULT NULL COMMENT ‘企业Id’ COLLATE ‘utf8\_general\_ci’,  
> `WoBarCode` VARCHAR(255) NULL DEFAULT NULL COMMENT ‘压机条码’ COLLATE ‘utf8\_general\_ci’,  
> PRIMARY KEY (`ID`, `Create_Date`) USING BTREE,  
> INDEX `Ws_index` (`WCode`) USING BTREE,  
> INDEX `Wode_index` (`WorkerCode`) USING BTREE,  
> INDEX `WorkmCode_index` (`WoMainItemCode`) USING BTREE,  
> INDEX `WormName_index` (`WorkUtItemName`(255)) USING BTREE,  
> INDEX `WorkUdex` (`Woctory`) USING BTREE,  
> INDEX `Workdex` (`Worame`(255)) USING BTREE,  
> INDEX `Wde_index` (`WoCode`(255)) USING BTREE,  
> INDEX `W_index` (`WStartTime`) USING BTREE,  
> INDEX `W_index` (`WodTime`) USING BTREE,  
> INDEX `W_index` (`WareHtus`) USING BTREE,  
> INDEX `Insndex` (`Insult`) USING BTREE,  
> INDEX `sitex` (`sie`) USING BTREE,  
> INDEX `WT_index` (`WnStartT`) USING BTREE,  
> INDEX `C_index` (`Cr`) USING BTREE  
> )  
> COMMENT=‘Bare表’  
> COLLATE=‘utf8\_general\_ci’