一、创建表1.1 建表语句语法CREATE TABLE table_name (column_name column_type);示例CREATETABLEIFNOTEXISTSwno704_tbl(idINTUNSIGNEDAUTO_INCREMENT,titleVARCHAR(100)NOTNULL,authorVARCHAR(40)NOTNULL,dateDATE,PRIMARYKEY(id))ENGINEInnoDBDEFAULTCHARSETutf8 ROW_FORMATDYNAMICCOMMENT人员;实例解析1.如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL 在操作数据库时如果输入该字段的数据为NULL 就会报错。2.AUTO_INCREMENT定义列为自增的属性一般用于主键数值会自动加1。3.PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键列间以逗号分隔。4.ENGINE 设置存储引擎CHARSET 设置编码。二、查看操作2.1 查看表名称show tables;2.2 查看表结构desc tablename;2.3 查看表详细结构语句show create table tablename;三、表字段操作3.1 添加字段语法ALTER TABLE tb_name ADD [ COLUMN ] 新字段名数据类型 [约束条件] [FIRST | AFTER 已有字段名];示例ALTER TABLE wno704_tbl ADD COLUMN status INT FIRST;3.2 修改字段语法ALTER TABLE tb_name CHANGE [COLUMN] 原字段名新字段名数据类型 [约束条件]; ALTER TABLE tb_name ALTER [COLUMN] 字段名 {SET | DROP} DEFAULT; ALTER TABLE tb_name MODIFY [COLUMN] 字段名数据类型 [约束条件] [FIRST | AFTER 已有字段名];示例ALTER TABLE wno704_tbl CHANGE COLUMN status flag INT; ALTER TABLE wno704_tbl ALTER COLUMN status SET DEFAULT 0; ALTER TABLE wno704_tbl MODIFY COLUMN status INT AFTER date;3.3 删除字段语法ALTER TABLE tb_name DROP [COLUMN] 字段名;示例ALTER TABLE wno704_tbl DROP COLUMN flag;四、表操作4.1 重命名表语法ALTER TABLE 原表名 RENAME [TO] 新表名; RENAME TABLE 原表名1 TO 新表名1 [, 原表名2 TO 新表名2] ... ...;示例ALTER TABLE wno704_tbl RENAME TO wno704_tb; RENAME TABLE wno704_tb TO wno704_tbl;4.2 删除表语法DROP TABLE [IF EXISTS] 表1 [, 表2]...;示例DROP TABLE IF EXISTS wno704_tbl;4.3 insert into select语法insert into TABLE 表1 ([COLUMN]) select [COLUMN] from 表2;示例insert into total_index_td(date,orgid,type,num,update_time) select date_format(now(), %Y-%m) date,p.orgid,15 type,0 num,now() update_time from entry_pianquzb_report p where p.attr 2 and p.delflag 0 and not exists (select 1 from total_index_td q where q.orgid p.orgid and q.type 15);4.4 注解分析SELECT TABLE_NAME,COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE , COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA kdptdb and TABLE_NAME entry_pianquzb_report; ALTER TABLE kdptdb.entry_pianquzb_report MODIFY COLUMN latn int COMMENT 地市;4.5 批量注解增加SELECTTABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_COMMENT,FLOOR(RAND()*100)1asrand,CONCAT(ALTER TABLE ,TABLE_NAME, MODIFY COLUMN ,COLUMN_NAME, ,(casewhenCHARACTER_MAXIMUM_LENGTHisnullthenDATA_TYPEelseconcat(DATA_TYPE,(,CHARACTER_MAXIMUM_LENGTH,))end), COMMENT ,(casewhencolumn_nameidthen主键whencolumn_namelike%ids%then序列whencolumn_namelike%id%then编码whencolumn_namelike%name%then名称whencolumn_namelike%create_by%then创建人whencolumn_namelike%create_date%then创建时间whencolumn_namelike%update_by%then更新人whencolumn_namelike%update_date%then更新时间whencolumn_namelike%del_flag%then删除标识whencolumn_namelike%email%then邮件whencolumn_namelike%phone%then电话whencolumn_namelike%remark%then备注whencolumn_namelike%extend%then扩展whencolumn_namelike%time%then时间else1-其他字段end),;)sqllFROMINFORMATION_SCHEMA.COLUMNSWHERETABLE_SCHEMAin(ofm_main_db)andCOLUMN_COMMENT