常用mysql語句_常用MySql語句

新建数据表

drop table if EXISTS ga_game_way;

CREATE TABLE `ga_game_way` (

`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',

PRIMARY KEY (`id`) USING BTREE, -- 主键

UNIQUE INDEX `uq_username`(`username`) USING BTREE, -- 唯一索引

INDEX `index_pid`(`pid`) USING BTREE, -- 普通索引

CONSTRAINT fk_bt_game_id

FOREIGN KEY (game_id)

REFERENCES ga_game_sys(id)

ON DELETE cascade

ON UPDATE cascade

-- 外键索引

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='表注释';

索引

新建索引

-- 唯一索引

alter table ga_user add UNIQUE uq_username(username)

-- 普通索引

alter table ga_user add INDEX index_pid(pid)

删除索引

alter table ga_user drop

启用、禁用外键约束

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0; -- 禁用外键约束

SET FOREIGN_KEY_CHECKS = 1; -- 启用外键约束

优化

-- 查看碎片

SHOW TABLE STATUS LIKE 'ga_admin';

-- 优化表:

OPTIMIZE TABLE ga_admin;