开发必备-常用SQL

[TFdream]

1.建库

1.语法

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification [, create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name

2.示例

CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

utf8mb4：

CREATE DATABASE `test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

2.建表

1.语法

2.示例

CREATE TABLE `operation_user_investment_detail` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`activity_id` bigint(20) NOT NULL COMMENT '活动id',
`user_id` bigint(20) unsigned NOT NULL COMMENT '用户id',
`name` varchar(40) NOT NULL COMMENT '用户真实姓名',
`buy_amount` decimal(12,2) NOT NULL COMMENT '购买理财计划金额',
`buy_record_detail_id` bigint(20) unsigned NOT NULL COMMENT '购买理财计划详情id',
`buy_time` datetime DEFAULT NULL COMMENT '购买时间',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_buy_record_id` (`buy_record_detail_id`, `activity_id`),
KEY `idx_activity_id_user_id` (`activity_id`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='活动后台用户投资记录表';

utf8mb4：

CREATE TABLE `operation_user_investment_detail` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`activity_id` bigint(20) NOT NULL COMMENT '活动id',
`user_id` bigint(20) unsigned NOT NULL COMMENT '用户id',
`name` varchar(40) NOT NULL COMMENT '用户真实姓名',
`buy_amount` decimal(12,2) NOT NULL COMMENT '购买理财计划金额',
`buy_record_detail_id` bigint(20) unsigned NOT NULL COMMENT '购买理财计划详情id',
`buy_time` datetime DEFAULT NULL COMMENT '购买时间',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_buy_record_id` (`buy_record_detail_id`, `activity_id`),
KEY `idx_activity_id_user_id` (`activity_id`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='活动后台用户投资记录表';

3.添加字段

1. 语法

ALTER TABLE table_name ADD COLUMN new_column_name varchar(20) not null;

ALTER TABLE table_name ADD COLUMN new_column_name varchar(20) not null AFTER column_name;

2. 示例

增加一列：

ALTER TABLE `t_user` ADD COLUMN age varchar(20) not null;

增加指定的一列：

ALTER TABLE `t_user` ADD COLUMN age varchar(20) not null AFTER gender;

4.添加索引

1.语法

//普通索引
ALTER TABLE `table_name` ADD INDEX index_name(`column1`,`column2`,`column3`)
//唯一索引
ALTER TABLE `table_name` ADD UNIQUE INDEX index_name(`column1`,`column2`,`column3`)

2.示例

普通索引：

ALTER TABLE `operation_user_coin` ADD INDEX idx_activity_id (`activity_id`) ;

唯一索引：

ALTER TABLE `operation_user_coin` ADD UNIQUE INDEX uniq_activity_user(`coin_id`,`user_id`);

5.删除索引

1.语法

DROP INDEX index_name ON table_name;

ALTER TABLE table_name DROP index index_name;

2.示例

ALTER TABLE `wb_blog` DROP INDEX idx_user;