mysql - ddl [CREATE DATABASE、CREATE TABLE、DROP、ALTER] 原创

------------------------------------
------------------------------------

-- DDL 语句操作数据库以及表的create[增加], drop[删除], alter[修改]等

------------------------------------
------------------------------------



-- 创建测试数据库
-- 一般创建
create database test;

-- 不确定是否已存在的创建
CREATE DATABASE IF NOT EXISTS test;

-- 指定字符集
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
-- 修改数据库名
-- 第一种：
RENAME DATABASE test TO project; -- 修改数据库test的名字为project 5.1.7到5.1.23版本可以用的，但是官方不推荐，会有丢失数据的危险
-- 第二种方法：
-- 1.创建需要改成新名的数据库。
-- 2.mysqldum 导出要改名的数据库
-- 3.删除原来的旧库（确定是否真的需要）
-- 当然这种方法虽然安全，但是如果数据量大，会比较耗时，大量数据不推荐使用。

-- 第三种方法：
-- 使用一个脚本

-- 删除数据库
DROP DATABASE test;

-- 修改数据库信息
ALTER DATABASE test DEFAULT CHARACTER utf8, DEFAULT COLLATE utf8_general_ci; -- 修改数据库字符集信息


-- 创建表
-- 一般创建
CREATE TABLE users (
    `id` INT(11),
    `username` VARCHAR(20),
    `status` TINYINT(1) DEFAULT 1,
    `create_time` INT(11) DEFAULT 0
 ) DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
 -- 不确定表是否已存在的创建
 CREATE TABLE IF NOT EXISTS users (
    `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(20) NOT NULL UNIQUE DEFAULT '', -- 名称唯一, 不是指唯一索引
    `status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT "1 有效用户 2 禁用用户",
    `create_time` INT(11) NOT NULL DEFAULT 0
 ) DEFAULT CHARSET utf8 COLLATE utf8_general_ci COMMENT "用户表";
 CREATE TABLE IF NOT EXISTS users2 (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(20) NOT NULL DEFAULT '',
    `status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT "1 有效用户 2 禁用用户",
    `create_time` INT(11) NOT NULL DEFAULT 0,
    PRIMARY KEY(id, username),
    INDEX uus(username, status),
    UNIQUE username(username) -- username唯一索引
 ) DEFAULT CHARSET utf8 COLLATE utf8_general_ci COMMENT "用户表";

 -- AUTO_INCREMENT 表示字段的值是自增长的
 -- PRIMARY KEY 指定字段为主键，一张表只能有一个主键，但可以有联合主键[指定多个字段]PRIMARY KEY(id, username)
 -- NOT NULL 设置字段值不能为null
 -- column的DEFAULT设置字段的默认值
 -- COMMENT 设置字段或表备注
 -- INDEX 指定索引, 索引多列为联合索引，单列为普通索引
 -- UNIQUE 索引类型-唯一索引，创建表时同时创建唯一索引[FULLTEXT]
 -- SET @@auto_increment_offset 设置数据库表自增字段起始值[小于步长,不然会失效]
 -- SET @@auto_increment_increment 设置数据库表自增字段每次增长的步长

 SET @@auto_increment_increment = 3; -- 设置数据库表自增字段每次增长的步长为3
 SET @@auto_increment_offset = 2; -- 设置数据库表自增字段起始值为2


 -- 修改表信息
 -- 修改表名称
 ALTER TABLE users RENAME TO test_users;
 ALTER TABLE test_users RENAME TO users;

 -- 修改字段信息
 -- 增加表字段
 ALTER TABLE users ADD COLUMN update_time int(11) NOT NULL DEFAULT 0 COMMENT "修改时间" AFTER create_time;
 -- ALTER TABLE TABLENAME ADD [COLUMN] fieldName fieldType, field2Name, field2Type...;
 -- 修改表增加表字段 可同时增加多个字段，ADD 后的COLUMN可以不写
 -- 修改表字段
 ALTER TABLE users MODIFY username VARCHAR(30) DEFAULT '' COMMENT "用户名";
 ALTER TABLE users MODIFY update_time int(11) DEFAULT 0 NOT NULL;
 -- 修改字段类型
 ALTER TABLE users MODIFY username char(20) DEFAULT '';
 -- 修改字段名[必须要指定新名字段类型]
 ALTER TABLE users CHANGE username user_name varchar(20) DEFAULT '';
 -- 删除表字段
 ALTER TABLE users DROP COLUMN update_time;


-- 索引
-- 修改表结构创建索引 ALTER TABLE tableName ADD INDEX IndexName(fieldName);
-- ALTER TABLE TABLENAME ADD INDExTYPE IndexName(fieldName);
ALTER TABLE users ADD INDEX status(`status`); -- 普通索引
ALTER TABLE users ADD UNIQUE username(user_name); -- 唯一索引,索引名为username
ALTER TABLE users ADD UNIQUE(user_name); -- 唯一索引2,索引名为user_name
ALTER TABLE users ADD INDEX uus(user_name, status); -- 多列索引，联合索引，索引名为uus

-- 创建索引
-- CREATE INDEX IndexName ON TABLENAME(TableField(fieldLength));
CREATE INDEX ts ON users(`status`); -- 创建普通索引
CREATE UNIQUE INDEX tss ON users(`status`); -- 创建唯一索引

-- 删除索引
DROP INDEX tss ON users; -- 删除索引tss
ALTER TABLE users DROP INDEX ts; -- 删除索引ts

-- 外键
-- 创建外键 外键数据类型要和主表的字段数据类型完全一致
-- 创建外键最好是在创建表的时候直接加上外键的创建。
-- 修改表结构增加外键要保证外键的值在外键主表中存在。
-- constraint 给约束起名，如果不加，系统将自动给约束起名
-- ALTER创建外键

-- ALTER TABLE users ADD type_id int(11) NOT NULL COMMENT '用户类型';
-- CREATE TABLE user_types (
--    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
--    title VARCHAR(20) NOT NULL DEFAULT '' COMMENT '类型名称',
--    status TINYINT(1) NOT NULL DEFAULT 0 COMMENT '状态'
-- ) DEFAULT CHARSET utf8 COLLATE utf8_general_ci COMMENT '用户类型表';
ALTER TABLE users ADD CONSTRAINT fk_user_type_id FOREIGN KEY(type_id) REFERENCES user_types(id);
-- 删除外键
ALTER TABLE users DROP FOREIGN KEY fk_user_type_id;

-- 修改线上运行中数据库表结构
-- 1、备份数据库表，在备份表中修改，删除旧表，备份表改名为旧表名 - 数据丢失，复制数据占用io，还有可能会导致死锁，被删除的数据又出来了，旧数据也出来了，新数据丢啦
-- 2、通过程序执行1 [停掉业务运行，修改完之后再运行]

-- 1执行也可以创建一个相同表结构的新表，然后修改新表结构
-- 把旧表数据导入新表
-- 删除旧表，新表改名为旧表的名 - 问题还是一样的

-- 3、通过工具进行操作 pt-online-schema-change
-- pt-online-schema-change 操作流程跟1 一样，但是这个工具进行了优化[加触发器,数据被修改的时候新表的数据同样被触发修改]
-- 原理与注意参考 https://www.jianshu.com/p/ecec3d307ec0/
-- 操作方法与限制参考 https://blog.51cto.com/wujianwei/2487025


-- TRUNCATE 清空表数据
TRUNCATE TABLE users; -- 删除表中所有数据并重置自增值为初始值
-- DROP 直接删除，所有东西都不保留，TRUNCATE保留表结构信息，删除所有数据，重置自增初始值



-- 测试
CREATE TABLE IF NOT EXISTS orders2 (
    `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT "订单主键", -- 订单表主键ID 自增ID
    `user_id` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT "用户主键ID",
    `goods_id` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT "商品主键ID",
    `amount` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT "订单价格[单位：分]",
    `pay_amount` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT "订单实际支付金额[单位：分]",
    `status` TINYINT(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT "订单状态：1 未支付 2 已支付 3 已退款 4 已评价 5 已取消",
    `pay_time` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT "订单支付时间",
    `create_time` INT(11) UNSIGNED  NOT NULL DEFAULT 0 COMMENT "订单创建时间",
    INDEX user_id(`user_id`),
    INDEX `status`(`status`),
    CONSTRAINT fk_order_users_id FOREIGN KEY(user_id) REFERENCES users(id) -- 创建外键 外键数据类型要和主表的字段数据类型完全一致
    ON DELETE CASCADE
) DEFAULT CHARSET utf8 COLLATE utf8_general_ci COMMENT "用户订单表";