常用sql语句

1、创建table标准语句

/*==============================================================*/
/* Table: alarm_info                                            */
/*==============================================================*/
create table if not exists alarm_info 
(
   id                   unsigned bigint                not null auto_increment,
   gmt__create          datetime                       null,
   gmt_modified         datetime                       null,
   alarm_location       varchar(30)                    null,
   alarm_name           varchar(30)                    null,
   alarm_level          unsigned tinyint               DEFAULT 1,
   alarm_status         unsigned tinyint               DEFAULT 1,
   send_status          unsigned tinyint               DEFAULT 1,
   handler              varchar(30)                    null,
   alarm_id             varchar(36)                    null,
   alarm_source         varchar(36)                    null,
   alarm_info           varchar(100)                   null,
   constraint PK_ALARM_INFO primary key clustered (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 2、创建数据库

CREATE DATABASE IF NOT EXISTS testDatabase DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

3、对一张存在的表中添加一个字段

ALTER TABLE contacts ADD email VARCHAR(60);

/*==============================================================*/
/* To insert the new column after a specific column, such as name,
/* use this statement:                                          */
/*==============================================================*/
ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

/*==============================================================*/
/* you want the new column to be first, use this statement:                         */
/*==============================================================*/
ALTER TABLE contacts ADD email VARCHAR(60) FIRST;

 4、修改字段的长度或者类型

ALTER TABLE asset_value
MODIFY node_id VARCHAR(65) NOT NULL;