数据表的创建、删除、索引和变更

创建数据表

-- 1、改变存储特性的表选项
CREATE TABLE t(i INT) ENGINE = ARCHIVE;-- 引擎名字不区分大小写
-- ①、表选项是 engine = engine_name 为表指定要用的存储引擎
-- 要保证 CREATE TABLE 语句没有产生任何警告消息，因为有可能是存储引擎不可用，并使用默认引擎代替。
-- 查看表使用了哪一种存储引擎
SHOW CREATE TABLE t;
-- ②、表选项 MAX_ROWS 和 AVG_ROW_LENGTH 可用于调整 MyISAM 表的大小。
-- 默认情况下，MyISAM 表具有的大小与内部行指针长度一致，即允许表文件增长到 256 TB。
-- ③、修改某个已有表的存储特性
ALTER TABLE t ENGINE = InnoDB;

-- 2、只在表不存在时创建它，注意只看表名字是否存在,不看表结构。
CREATE TABLE IF NOT EXISTS t(i INT,a INT);

-- 3、temporary 临时表——在客户端会话结束时，服务器会自动删除它们
CREATE TEMPORARY TABLE t(i INT) ENGINE = MyISAM;
-- temporary 表的名字可以与某个已有的永久表的相同。
-- 对于当前的客户端来说，当有 temporary 表存在时，那个同名的永久表会隐藏起来（无法访问）

-- 4、从另一个数据表或从一次 SELECT 查询的结果来创建数据表
-- ①、为一个现有的数据表创建一份空白副本，但表是空的
CREATE TABLE new_t LIKE t;
-- 再从原始数据表填充它
INSERT INTO new_t SELECT * FROM t;
-- ②、从一次查询的结果来创建新表
CREATE TABLE t3 SELECT * FROM t1 INNER JOIN t2;
-- 默认情况下，不会复制所有的列属性（如 AUTO_INCREMENT）
-- 也不会把原有表里任何索引复制过去

-- ③、强制转换数据类型,select 部分需要提供一些别名去匹配
CREATE TABLE t1(i INT UNSIGNED,t TIME,d DECIMAL(10,5))
SELECT 1 AS i,
  cast(curtime() AS TIME) AS t,
  cast(pi() AS DECIMAL(10,5)) AS d;
-- 允许强制转换的类型包括：BINARY（二进制串）、CHAR、DATE、DATETIME、
-- TIME、SIGNED、SIGNED INTEGER 和 DECIMAL。

-- 5、使用分区表
-- MySQL 支持表分区，让表的内容分散存储在不同的物理存储位置。
-- 好处：①、表存储可以分布在多个设备上，这样可以通过 I/O 并行机制来缩短访问时间；
--      ②、优化器可以把本地化搜索限定在某个特定的分区，或者并行搜索多个分区。
-- 分区方式：可以根据范围、列表或散列值来分区
CREATE TABLE log_partition(
  dt DATETIME NOT NULL ,
  info VARCHAR(100) NOT NULL ,
  INDEX(dt) -- 定义一个索引
)
PARTITION BY RANGE (year(dt))(  -- 分区函数 partition，按年分区
  PARTITION p0 VALUES LESS THAN (2011),
  PARTITION p1 VALUES LESS THAN (2012),
  PARTITION p2 VALUES LESS THAN (2013),
  PARTITION p3 VALUES LESS THAN (2014),
  PARTITION pmax VALUES LESS THAN MAXVALUE -- 2015年及以后的行都被分配到 MAXVALUE 区。
);
-- 到了 2015 年时，可以再把这个分区进行划分，从而让所有 2015 年的行都有它们的自己的分区。
ALTER TABLE log_partition REORGANIZE PARTITION pmax
INTO (
  PARTITION p4 VALUES LESS THAN (2015),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 默认情况下，MySQL 会将分区存储在专属于分区表的数据库目录里。
-- 若想将存储分散到其他地方（如放置到不同的物理设备上），
-- 则需要用到分区选项 DATA_DIRECTORY 和 INDEX_DIRECTORY。

删除数据表

-- 删除多个数据表
DROP TABLE IF EXISTS tbl_name1,tbl_name2;
-- 删除临时数据表
DROP TEMPORARY TABLE tal_name;

索引表

存储引擎的索引特性：

• 可以对单个列或多个列建立索引，多列索引也被称作复合索引；
• 索引可以只包含唯一值，也可以包含重复值；
• 可以为同一个表创建多个索引，帮助优化对表的不同类型的查询；
• 对于除 ENUM 和 SET 以外的字符串类型，可以利用列的前缀创建索引。

注：MERGE 跟 MyISAM 具有相似的索引特性；其他的要么不支持索引，要么支持得很有限。

创建索引

• 唯一索引。对于单列索引，不允许有重复值出现；对于多列（复合）索引，不允许出现重复的组合值；
• 常规（非唯一性）索引；
• FULLTEXT 索引。可用于完成全文检索。只适用于 MyISAM（MySQL 5.6.4 以上的版本，可用于 InnoDB 表）；
• SPATIAL 索引。只适用于包含多空间值的 MyISAM 表；
• HASH 索引。MEMORY 表的默认索引类型，不过可以通过创建 BTREE 索引来改写它。

-- 为已有表添加索引：ALTER TABLE 或 CREATE INDEX
ALTER TABLE tbl_name ADD INDEX index_name(index_columns);
ALTER TABLE tbl_name ADD PRIMARY KEY (index_columns);
-- index_name：索引名（可选）
-- index_columns：要进行索引的列（如果多个，用逗号隔开）

-- 如果想要限制某个索引，让它只包含唯一值：PRIMARY KEY 或 UNIQUE 索引
-- 两者区别：
-- ①、每个表只能包含一个 PRIMARY KEY（PRIMARY KEY 的名字总是为 PRIMARY，一个表不允许有两个同名的索引）
--     而一个表里可以放置多个 UNIQUE 索引
-- ②、PRIMARY KEY 不可以包含 NULL 值，而 UNIQUE 索引可以。
CREATE INDEX index_name ON tbl_name(index_columns);
-- CREATE INDEX 语句里的索引名不是可选的，并且不能再一条语句里创建多个索引。

-- MEMORY 表默认的索引类型是 HASH，如果想要进行范围比较，最好创建一个 BTREE 索引来代替它。
CREATE TABLE namelist(
  id INT NOT NULL ,
  name CHAR(100),
  INDEX (id) USING BTREE  -- 创建 BTREE 索引
) ENGINE = MEMORY;

-- 对字符串的前缀建立索引
CREATE TABLE addresslist(
  name CHAR(30) NOT NULL ,
  address BINARY(60) NOT NULL ,
  INDEX (name(10)),   -- name 的前 10 个字符建立索引
  INDEX (address(15)) -- address 的前 15 个字节建立索引
);
-- ①、对于 BLOB 或 TEXT 列，只能创建前缀型索引
-- ②、索引行的长度等于构成该索引的各种的索引部分的长度总和
-- 注：FULLTEXT 索引里的列是以满列值方式进行索引的，不能进行前缀索引。

删除索引

-- 删除该索引
DROP INDEX index_name ON tbl_name;
DROP INDEX 'PRIMARY' ON tbl_name; -- 删除 PRIMARY KEY
-- 等价于
ALTER TABLE tbl_name DROP INDEX index_name;
ALTER TABLE tbl_name DROP PRIMARY KEY ;
-- 当从表里删除索引时，索引也会隐式受到影响

更改表结构

ALTER TABLE 是一条万能型的语句，可以更改存储引擎、创建和删除索引，还可以完成重命名表、添加或删除列、更改列的数据类型等操作。

-- 更改列的数据类型：change 或 modify 子句
ALTER TABLE mytbl MODIFY i MEDIUMINT UNSIGNED;
ALTER TABLE mytbl CHANGE i k MEDIUMINT UNSIGNED; -- 更改数据类型时，重命名列 i->k
-- 更改列的字符集，在列的定义里使用 character set 属性
ALTER TABLE t MODIFY c CHAR(20) CHARACTER SET ucs2;
ALTER TABLE t MODIFY c CHAR(15);
ALTER TABLE t CHANGE c c CHAR(15);
-- 让表改用另一种存储引擎
ALTER TABLE tbl_name ENGINE = innoDB;
-- 重新命名表
ALTER TABLE tbl_name RENAME TO new_tbl_name;
RENAME TABLE tbL_name TO new_tbl_name;
-- 注意：一次对多表进行重命名时，只能使用 RENAME TABLE
RENAME TABLE t1 to tmp,t2 to t1,tmp to t2;
-- 表名前面使用了数据库名进行限定，就可以：把表 t 从 sampdb 数据库移动到 test 数据库
ALTER TABLE sampdb.t RENAME TO test.t;
RENAME TABLE sampdb.t TO test.t;
-- 注意：重命名不能使用已有的名字