深入浅出MySQL(三)

表结构相关操作

添加字段和删除字段

添加字段

ALTER TABLE tbl_name ADD 字段名称 字段属性 [完整性约束条件] [FIRST | AFTER]

删除字段

ALTER TABLE tbl_name DROP 字段名称

测试用例:

-- 测试添加和删除字段

CREATE TABLE IF NOT EXISTS user1(
    id INT UNSIGNED AUTO_INCREMENT KEY
);

-- 添加用户名字段
ALTER TABLE user1
ADD username VARCHAR(20);

-- 添加密码字段
ALTER TABLE user1
ADD password CHAR(32) NOT NULL;

-- 添加邮箱字段
ALTER TABLE user1
ADD email VARCHAR(50) NOT NULL UNIQUE AFTER username;

-- 删除邮箱字段
ALTER TABLE user1
DROP email;

-- 添加(删除)多个字段 新增年龄,地址字段;删除用户名，密码字段
ALTER TABLE user1
ADD age TINYINT UNSIGNED NOT NULL DEFAULT 18,
ADD addr VARCHAR(100) NOT NULL,
DROP id,
DROP username;

添加和删除默认值

给字段添加默认值

ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT  默认值

给字段删除默认值

ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT

测试用例：

-- 测试添加删除默认值操作
CREATE TABLE user2(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL,
    age TINYINT UNSIGNED NOT NULL DEFAULT 18,
    email VARCHAR(50) NOT NULL
);

-- 给email字段添加默认值
ALTER TABLE user2
ALTER email SET DEFAULT '1@qq.com';

-- 给age字段删除默认值

ALTER TABLE user2
ALTER age DROP DEFAULT;

MODIFY和CHANGE关键字

MODIFTY的作用是修改字段类型和字段属性

ALTER TABLE tbl_name MODIFY 字段名称 字段类型 [字段属性][FIRST | AFTER 字段名称]

CHANGE关键字的作用为修改字段名称，字段类型和字段属性

ALTER TABLE tbl_name CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST | AFTER 字段名称]

测试用例：

-- 测试修改字段名称，字段类型，字段属性
CREATE TABLE user3(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(5) NOT NULL UNIQUE,
    password CHAR(32) NOT NULL,
    email VARCHAR(10) NOT NULL
);

-- 将用户名字段的类型改为VARCHAR(20)
ALTER TABLE user3
MODIFY username VARCHAR(20) NOT NULL;

-- 将密码字段长度改为40
ALTER TABLE user3
MODIFY password CHAR(40) NOT NULL;

-- 将email字段改为VARCHAR(50)并且放在表的首位
ALTER TABLE user3
MODIFY email VARCHAR(50) NOT NULL FIRST;

-- 修改字段名称，将username字段名称变为 name
ALTER TABLE user3
CHANGE username name VARCHAR(20) NOT NULL;
-- 修改字段名称，将password字段名称变为 pwd
ALTER TABLE user3
CHANGE password pwd CHAR(40) NOT NULL;

-- 将email 改成userEmail字段 类型变为VARCHAR(100) 可以为空 DEFAULT '1@qq.com'
ALTER TABLE user3
CHANGE email userEmail VARCHAR(100) DEFAULT '1@qq.com';

添加和删除主键

添加主键

ALTER TABLE tbl_name ADD PRIMARY KEY(字段名称)

删除主键

ALTER TABLE tbl_name DROP PRIMARY KEY

测试用例如下：

CREATE TABLE user4(
    id INT UNSIGNED,
    username VARCHAR(20) NOT NULL
);

-- 添加主键
ALTER TABLE user4
ADD PRIMARY KEY(id);

-- 删除主键
ALTER TABLE user4
DROP PRIMARY KEY;

-- 在表中有AUTO_INCREMENT约束的时候,需要先删除AUTO_INCREMENT
CREATE TABLE IF NOT EXISTS user5(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL
);

ALTER TABLE user5
MODIFY id INT UNSIGNED; 
ALTER TABLE user5
DROP PRIMARY KEY;

添加和删除唯一索引

添加索引

ALTER TABLE tbl_name ADD UNIQUE KEY | INDEX [index_name](字段名称)

删除索引

ALTER TABLE tbl_name DROP  INDEX index_name

测试用例如下：

-- 测试添加和删除唯一
CREATE TABLE user6(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    password CHAR(32) NOT NULL,
    email VARCHAR(50) NOT NULL UNIQUE
);
-- 删除username字段的唯一索引
ALTER TABLE user6
DROP INDEX username;
-- 删除email字段的唯一索引
ALTER TABLE user6
DROP INDEX email;

-- 添加username字段唯一索引
ALTER TABLE user6
ADD UNIQUE KEY(username);
-- 添加email字段的唯一索引
ALTER TABLE user6
ADD UNIQUE INDEX uni_email(email);

修改数据表名称

有两种方式：
第一种：

ALTER TABLE  tbl_name  RENAME [TO | AS] new_tbl_name

第二种：

RENAME TABLE tbl_name TO new_tbl_name

测试用例：

CREATE TABLE user7(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE
);
-- 修改数据表名称
ALTER TABLE user7
RENAME TO user777;
-- 修改数据表名称
RENAME TABLE user777
TO user7;

MySQL存储引擎

MyISAM存储引擎

-- 测试MyISAM的存储引擎
CREATE TABLE test_myisam(
    a INT UNSIGNED,
    b VARCHAR(20),
    c CHAR(32)
)ENGINE = MyISAM;

在创建表的时候，指定存储引擎为MyISAM即可。
默认的MyISAM的表回再磁盘中产生三个文件：.frm文件为表结构文件；.MYD文件为数据文件;.MYI文件为索引文件。MyISAM存储引擎支持在创建表的时候指定数据文件和索引文件的存储位置，INNODB引擎是不支持的。
指定数据文件的存储位置

DATA DIRECORY [=] 数据保存的绝对路径

指定索引文件的存储位置

INDEX DIRECTORY [=] 索引文件保存的绝对路径

MyISAM单表最大支持的数据量为2的64次方条记录，每个表最多可以建立64个索引；如果是复合索引，每个复合索引最多包含16个列，索引值最大长度是1000B。
MyISAM引擎有三种存储格式：

定长(FIXED)静态
指的是指字段中不包含VARCHAR/TEXT/BLOB
测试程序如下：

CREATE TABLE test_myisam1(
    a CHAR(30),
    id INT
)ENGINE = MyISAM;
SHOW TABLE STATUS LIKE 'test_myisam1'\G

结果如下：

           Name: test_myisam1
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 26740122787512319
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-04-04 18:02:11
    Update_time: 2020-04-04 18:02:11
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

动态(DYNAMIC)
只要字段中包含了VARCHAR/TEXT/BLOB
测试代码如下：

CREATE TABLE test_myisam2(
    a VARCHAR(30),
    id INT
)ENGINE = MyISAM;
SHOW TABLE STATUS LIKE 'test_myisam2'\G

结果如下：

           Name: test_myisam2
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-04-04 18:07:51
    Update_time: 2020-04-04 18:07:51
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

也可以使用ROW_FORMAT指定存储格式

CREATE TABLE test_myisam3(
    a VARCHAR(30),
    id INT
)ENGINE = MyISAM ROW_FORMAT = FIXED;
SHOW TABLE STATUS LIKE 'test_myisam3'\G

结果如下：

          Name: test_myisam3
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 27021597764222975
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-04-04 18:10:23
    Update_time: 2020-04-04 18:10:23
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=FIXED
        Comment:

压缩(COMPRESSED)
myisampack创建

InnoDB存储引擎

InnoDB存储引擎的设计遵循了ACID模型,支持事物，具有从服务崩溃中恢复的能力，能够最大限度保护用户的数据。ACID即：原子性(Atomiocity)，一致性(Consistency)，隔离性(Isolation)，持久性(Durability)。InnoDB支持行级锁，可以提升多用户并发时的读写性能；InnoDB支持外键，保证数据的一致性和完整性；InnoDB拥有自己独立的缓冲池,常用的数据和索引都在缓存中；对于INSERT,UPDATE,DELETE操作，InooDB会使用一种change buffering的机制来自动优化，还可以提供一致性的读，并且还能够缓存变更的数据，减少磁盘I/O，提高性能。
测试程序：

CREATE TABLE test_innodb(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL
);

对于MySQL如果不指定创表的存储引擎时，默认就是InnoDB存储引擎。创建InnoDB表之后会产生两个文件：.frm是表结构文件；.ibd则是数据和索引存储的文件。对于InnoDB存储引擎的表来说，所有的表都应该需要创建主键，而且最好要配合AUTO_INCREMENT使用