CREATE DATABASE标准SQL

1
2
3
4
5

SET NAMES UTF8;

CREATE DATABASE example_db DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; #collate建议使用utf8-bin

CREATE TABLE标准SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

SET NAMES UTF8;

DROP TABLE IF EXISTS example_db.example;

CREATE TABLE example_db.example(   #常规字段为 int,bigint,varchar,text,tinyint

`id` int(10) UNSIGNED NOT NULL COMMENT 'comment' AUTO_INCREMENT,

`account_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'comment',
                         
`telephone` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'comment',

`card_amount` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'comment',          #涉及到金额的，请使用bigint，单位为分
                 
`name` varchar(5) NOT NULL DEFAULT '' COMMENT 'comment',                         #varchar字段，建议使用长度5,30,50,100,255,500,1000,5000

`desc` varchar(30) NOT NULL DEFAULT '' COMMENT 'comment',

`info` varchar(50) NOT NULL DEFAULT '' COMMENT 'comment',

`addr` varchar(100) NOT NULL DEFAULT '' COMMENT 'comment',

`json` text NOT NULL COMMENT 'comment',                                          #存储json格式字段

`status` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT 'comment',               #1为正常数据，0为删除数据

`create_time`  datetime default now() comment '创建时间',

`update_time`  datetime default now() comment '更新时间',

PRIMARY KEY `idx_id` (`id`),                                                     #主键，请合理选择

KEY `idx_user_name` (`name`)                                                     #请加必要的业务相关索引，命名规则为idx_field1_field2

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT 'comment';

INSERT&UPDATE标准SQL

1
2
3
4
5
6
7
8

SET NAMES UTF8;

INSERT INTO example (iIntID,sVar0,sVar1,sVar2) VALUES (1,'中国','上海','浦东');

UPDATE example SET iStatus =  0, type = 1 WHERE iAutoID = 1;


修改表结构标准SQL

1
2
3
4
5
6
7
8

ALTER TABLE example_db.example

ADD COLUMN sMobilePhone VARCHAR(30) NOT NULL DEFAULT '' COMMENT '手机号码' AFTER create_time;     #建议指定新加column的位置

ALTER TABLE example_db.example

CHANGE COLUMN smobilePhone sMobilePhone VARCHAR(50) NOT NULL DEFAULT '' COMMENT '手机号码';

增加索引

1
ALTER TABLE example_db.example ADD INDEX idx_name(name);

删除表字段

1
ALTER TABLE products DROP COLUMN description;