2020-08-19

一 索引
1. 创建联合唯一索引
alter table tablename add unique index UNIQ_SUB_RES_IDX(name, res_id);
2. 创建唯一索引
alter table tablename add unique index(content);                       -- 唯一索引
alter table test_share_log add index idx_question_id(question_id);     -- 普通索引
3. 修改字段大小
alter table imgs modify column url varchar(1000);
alter table idiom modify column img2 varchar(100);
alter table test_share_log modify column question_title varchar(888);
alter table riddle_user modify column head_url varchar(777);
alter table song_user modify column head_url varchar(777);
4. 查看索引
show index from tableName;
5. 备份相关的表
mysqldump -h 10.66.184.161 -P 3306 --set-gtid-purged=OFF -udevelop -p_admin123@456 collection song >; song.sql
mysqldump -h 10.66.184.161 -P 3306 --set-gtid-purged=OFF -udevelop -p_admin123@456 collection idiom >; idiom.sql
mysqldump -h 10.66.184.161 -P 3306 -udevelop -p_admin123@456 -p -t -T /root/coin.txt collection coin --fields-terminated-by=','
6. 删除索引 删除表
drop index idx_group_id_name on user_face_info ;
drop table table_name  -- 删除表
二 字段
1. 修改字段
ALTER TABLE test_question  CHANGE id id bigint(20) UNSIGNED NOT NULL ;         
2. 添加字段
alter table riddle_user_day_detail add `web_ad` bigint(20) DEFAULT NULL;
alter table riddle_user_day_detail add `video_ad` bigint(20) DEFAULT NULL;
3. 删除字段
ALTER TABLE game_difference DROP share_in_count;
ALTER TABLE game_difference DROP share_in_count1;
ALTER TABLE game_difference DROP share_in_count7;
三 创建数据库
CREATE DATABASE IF NOT EXISTS quant
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;
CREATE DATABASE IF NOT EXISTS quant
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;