mysql建表，修改表，增删改查基本操作

创建表

CREATE TABLE `table_name` (
 	`id` int(10) unsigned not null primary key auto_increment comment "主键id",
 	`oid` int(10) unsigned not null comment "订单id",
 	`price` decimal(10,2) unsigned not null default 0 comment "订单价格，最大值99999999.99，共10位，两位小数",
 	`title` char(50) not null default '' comment "标题",
 	`info` varchar(255) not null default '' comment "短介绍",
 	`description` text comment "描述信息",
 	`sort` tinyint(3) unsigned NOT NULL DEFAULT 255 COMMENT '排序',
 	`birthday` date DEFAULT NULL COMMENT '生日',
 	`is_delete` tinyint(1) not null default 1 comment "是否删除，1=>否，2=>是",
 	`createTime` int(10) unsigned not null default 0 comment "创建时间",
 	KEY `oid` (`oid`)
) ENGINE=InnoDB default CHARSET=utf8 comment="测试数据表";
   

• 增加一条数据

insert into `table_name` (oid, price, title, info, description, sort, birthday, is_delete) values (1234567891, 
1234.57, '标题2', '标题2介绍', '描述信息一大串', 1, '1998-04-08', 1);

• 增加多条数据

insert into `table_name` (oid, price, title, info, description, sort, birthday, is_delete) values (1234567891, 
1234.57, '标题2', '标题2介绍', '描述信息一大串', 1, '1998-04-08', 1), (1234567892, 
1234.58, '标题3', '标题3介绍', '描述信息一大串', 1, '1998-02-08', 1);

• 删除一条数据

delete from `table_name` where id = 1;

• 删除多条数据

delete from `table_name` where id < 3;

• 修改一条数据

update `table_name` set `oid` = 1234567894 where id = 6;

• 修改多条数据

update `table_name` set `oid` = 1234567894 where id < 7;

• 查找一条数据

select `id`, `oid`, `title`, `info`, `description`, `sort`, `birthday` from `table_name` where id = 8;

• 查找多条数据

select `id`, `oid`, `title`, `info`, `description`, `sort`, `birthday` from `table_name` where id < 10;

修改表结构

• 查看索引
  SHOW INDEX FROM table_name;

• 增加索引

ALTER TABLE `table_name` ADD INDEX `sort` (`sort`);

• 添加关联索引

ALTER TABLE `table_name` ADD UNIQUE INDEX `id&oid&sort` (`id`, `oid`, `sort`) USING BTREE COMMENT '关联索引';

• 删除索引

alter table `table_name` drop index `sort`;

• 添加字段

ALTER TABLE `table_name`
ADD COLUMN `type` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '类型' AFTER `sort`;

• 删除字段

ALTER TABLE `table_name` drop `test`;

• 修改字段

ALTER TABLE `table_name` change `type` `type` int(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '类型';
ALTER TABLE `table_name` modify `types` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '类型';

• 重命名列

ALTER TABLE `table_name` change `type` `types` int(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '类型';