一、数据库管理
MySQL安装并初始化之后，默认会有几个数据库（mysql、information_schema、performance_schema）都是初始化产生的数据库，MySQL 5.7新增了sys库，每个库的具体作用后面介绍。然后首先需要自定义数据库，这是使用MySQL各种功能的前提，下面介绍数据的基本操作，主要内容包括：创建数据库、删除数据库以及更改数据库。
CREATE（创建数据库）语法
|
1 |
CREATE DATABASE [IF NOT EXISTS] db_name [character set 'utf8'] [collate 'utf8_general_ci'] |
示例:
|
1 2 3 4 5 6 7 8 9 |
# 创建数据库db1; Mysql> CREATE DATABASE db1; # 数据库db2如果不存在就创建，如果存在就不创建; Mysql> CREATE DATABASE IF NOT EXISTS db2; # 创建数据库db3时指定字符集和排序规则(保存数据目录下db.opt文件中); Mysql> CREATE DATABASE db3 CHARCTER SET 'utf8' COLLATE 'utf8_general_ci'; Mysql> CREATE DATABASE db4 CHARSET utf8mb4; |
ALTER（修改数据库）语法
|
1 |
ALTER DATABASE db_name [character set 'utf8'] [collate 'utf8_general_ci'] |
示例：
|
1 2 |
# 修改数据库db3的字符集; Mysql> ALTER DATABASE blog3 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; |
修改数据库字符集并不会改变已经定义的表的字符集，只有新创建的表才会继承更改后的库的字符集。
DROP（删除数据库）语法
|
1 |
DROP DATABASE [IF EXISTS] db_name |
示例:
|
1 2 |
# 删除数据库db3; Mysql> DROP DATABASE db3; |
SHOW语法
|
1 2 |
# 查看所有数据库; Mysql> SHOW DATABASES; |
USE语法
|
1 2 |
# 设置默认数据库为db3; Mysql> USE db3; |
二、表约束
- 数据类型约束（TYPE）
就是数据类型的约束，在定义表结构时都会对于每一个字段指定对应的数据类型，如INT、CHAR等。当一个字段指定了特定的数据类型之后，如果在插入数据时没有按照指定的数据类型插入数据，系统就会报错。
- 主键约束（PRIMARY KEY）
主键约束就是表中的一个属性，在一个表中最多可以有一个主键，一个主键可以定义在一个或多个字段上，主键使一个或多个字段的值必须唯一且不为空，这样做可以通过该字段或该组字段中的值唯一地标识表中的一条记录。也可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。
作用:
1.主键可以保证记录的唯一性。
2.主键用于唯一标识表中的行数据，一个主键值对应一行数据。
3.主键由一个或多个字段组成，其值具有唯一性而且不允许取空值null一个表只能有一个主键而可以有多个备用键。
4.键可用做索引属于特殊的索引(有特殊限定不允许相同)，都是B+Tree索引。
- 外键约束（FOREIGN KEY）
一个关系数据库通常都包含多个表，通过外键（foreign key）可以使这些表之间关联起来，主要作用是保证数据引用的完整性。外键约束针对的两个表，如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表，但要注意，必须要计算机要知道你是这种关系(外键只支持支持事物的存储引擎)。另外定义外键后，不允许删除在另一个表中具有关联关系的行，要保证数据的一致性。例如，部门表tb_dept的主键是id，在员工表tb_emp5中有一个键deptld与这个id关联。
- 非空约束（NOT NULL）
非空约束指此字段的值不能为空，对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。
- 唯一键约束（unique key）
一个表中只能有一个主键属性，为了方表用户，提出唯一约束；唯一约束可以定义在一个或多个字段上；唯一约束使该字段或该组字段中的值唯一，可以为空，但是不能重复。
- 检查性约束
数据的定义不能违反阈值，比如使用NOT NULL属性后就不能有空值，又叫NOT NULL约束；缺省属性又叫缺省约束等等；他们的本意是实现自动化，也就是让计算机做这判断并处理件事。
建立主键和唯一键约束的时候，就会自动的创建索引。而且是唯一索引，如果看过索引的作用就会知道了。像主键约束、唯一约束、非空约束、外键约束、核查约束和缺省约束这些操作都是使表具有某些特性，所以在这里也可以称它们都是表的属性。
三、表管理
创建完数据库之后，接下来就是创建表了。所谓创建数据表，指的是在以及创建好的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性等）约束的过程。下面将介绍创建数据表的语法形式、如何添加主键约束、外键约束、非空约束等。
CREATE（创建表）语法
|
1 2 3 4 5 |
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name [(collumn_name data_type [column_definition],...)] [table_options] [partition_options] [IGNORE | REPLACE] [AS] SELECT ... |
或者
|
1 2 |
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name { LIKE old_tbl_name | (LIKE old_tbl_name) } |
示例:
创建表tn1，并定义表结构，每个字段都使用了COMMENT注释：
|
1 2 3 4 5 |
CREATE TABLE tn1(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '定义ID字段,数据类型为INT,不允许为空,设置自动增长,设置主键;', NAME CHAR(8) NOT NULL COMMENT '定义NAME字段,数据类型为CHAR,不允许为空;', AGE INT NOT NULL COMMENT '定义AGE字段,数据类型为TINYINT,不允许为空;', ADDRESS VARCHAR(20) COMMENT '定义ADDRESS字段,数据类型为VARCHAR;', SALARY DECIMAL(10,2) NOT NULL COMMENT '定义SALARY字段,数据类型为DECIMAL,不允许为空;') ENGINE=innodb DEFAULT CHARSET=utf8; |
下面是创表时所会用到的数据类型(data_type)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
BIT[(length)] TINYINT[(length)] [UNSIGNED] [ZEROFILL] SMALLINT[(length)] [UNSIGNED] [ZEROFILL] MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] INT[(length)] [UNSIGNED] [ZEROFILL] INTEGER[(length)] [UNSIGNED] [ZEROFILL] BIGINT[(length)] [UNSIGNED] [ZEROFILL] REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL] NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL] DATE TIME TIMESTAMP DATETIME YEAR CHAR[(length)] [CHARACTER SET charset_name] [COLLATE collation_name] VARCHAR(length) [CHARACTER SET charset_name] [COLLATE collation_name] BINARY[(length)] VARBINARY(length) TINYBLOB BLOB MEDIUMBLOB LONGBLOB TINYTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] TEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] MEDIUMTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] LONGTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] ENUM(value1,value2,value3,...) [CHARACTER SET charset_name] [COLLATE collation_name] SET(value1,value2,value3,...) [CHARACTER SET charset_name] [COLLATE collation_name] JSON |
PS：字符类型详细解释看MySQL数据类型章节。
下面是创表时常用字段属性（column_definition）
|
1 2 3 4 5 6 7 8 9 10 11 |
UNSIGNED #不允许该字段有为0或负数的值; NOT NULL | NULL #定义该字段是否可以为空(默认是NULL); DEFAULT default_value #定义该字段设置默认值; PRIMARY KEY [index_col_name] #该字段定义为主键; AUTO_INCREMENT #自动增加（该字段必须为主键且数据类型为整型）,初始值为1,每次新增一条记录字段值增加1; FOREIGN KEY REPERENCES table(column) #外键(只支持事物的存储引擎); UNIQUE [KEY] #该字段定义为唯一键约束（会创建唯一索引）; INDEX [index_name] [index_type] (index_col_name,...) #普通索引; UNIQUE INDEX [index_name] [index_type] (index_col_name,...) #唯一索引; COMMENT 'string' #字段注释; COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT} #字段格式; |
index_col_name:
|
1 |
col_name [(length)] [ASC | DESC] |
index_type:
|
1 |
USING {BTREE | HASH} |
index_option:
|
1 |
KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' |
下面是创表时常用选项（table_options）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
ENGINE [=] engine_name #定义表默认存储引擎(默认继承库); AUTO_INCREMENT [=] value #定义AUTO_INCREMENT从哪个值开始; AVG_ROW_LENGTH [=] value #定义平均ROW长度; [DEFAULT] CHARACTER SET [=] charset_name #定义默认表字符集(默认继承库); CHECKSUM [=] {0 | 1} #是否启用字段校验和; [DEFAULT] COLLATE [=] collation_name #定义排序规则; COMMENT [=] 'string' #定义注释信息; CONNECTION [=] 'connect_string' #定义连接字符; DATA DIRECTORY [=] 'absolute path to directory' #指定数据字典的绝对路径位置; DELAY_KEY_WRITE [=] {0 | 1} #是否延迟键写入; INDEX DIRECTORY [=] 'absolute path to directory' #指定索引字典的绝对路径位置; INSERT_METHOD [=] { NO | FIRST | LAST } #插入数据的方法; KEY_BLOCK_SIZE [=] value #键数据块大小; MAX_ROWS [=] value #最大允许存储的行数; MIN_ROWS [=] value #最小允许存储的行数; PACK_KEYS [=] {0 | 1 | DEFAULT} PASSWORD [=] 'string' UNION [=] (tbl_name[,tbl_name]...) ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} |
其他用法：
|
1 2 3 4 5 6 7 8 9 10 11 |
# 查询一个表的结果用来创建另一个新表(表结构会变化); Mysql> CREATE TABLE new_table_name SELECT * from old_table_name where ….; # 仿照另一张表创建一张相同结构的表; Mysql> CREATE TABLE new_table_name LIKE old_table_name; # 复制表1的数据到表2中（表2必须存在且表结构也必须与表1相同）; Mysql> INSERT INTO table_name_2 SELECT * from table_name_1; # 复制表1的数据到表2中（表2不需要存在）; Mysql> SELECT [column1,column2] into table_name_2 from table_name_1; |
ALTER（更改表）语法
|
1 |
ALTER TABLE table_name [ADD|DROP|CHANGE|MODIFY] column_name[COLUMN] (col_name column_definition,...) [table_options]; |
ALTER语法与CREATE TABLE带有的属性和选择基本差不多，下面给出一些基本实例，具体可以HELP ALTER TABLE。
示例:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
# 添加一个字唯一键属性; Mysql> ALTER TABLE tn1 ADD UNIQUE KEY (name); # 添加一个字段; Mysql> ALTER TABLE tn1 ADD name VARCHAR(20); # 添加一个字段存放在指定字段后; Mysql> ALTER TABLE tn1 ADD name VARCHAR(20) AFTER AGE; # 添加一个字段存放在第一个字段; Mysql> ALTER TABLE tn1 ADD name VARCHAR(20) FIRST; # 修改一个字段名称(原有属性必须都跟上); Mysql> ALTER TABLE tn1 CHANGE name Name VARCHAR(20); # 修改一个字段的属性; Mysql> ALTER TABLE tn1 MODIFY name VARCHAR(100) NOT NULL; mysql> ALTER TABLE tn1 MODIFY name VARCHAR(100) comment 'test'; # 修改字符集和排序规则; Mysql> ALTER TABLE tn1 CONVERT TO CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; # 修改表的存储引擎; Mysql> ALTER TABLE tn1 engine=innodb; # 删除一个字段; Mysql> ALTER TABLE tn1 DROP name; |
然而，你在以这种方式修改表格类型的时候需要非常仔细，因为对不支持同样的索引、字段类型或者表大小的一个类型进行修改可能使你丢失数据。如果你指定一个在你的当前的数据库中不存在的一个存储引擎，那么就会创建一个INNODB类型的表。
DROP（删除表）语法
|
1 |
DROP TABLE [ IF EXISTS ] TABLE_NAME |
示例:
|
1 2 |
# 删除指定表; Mysql> DROP TABLE IF EXISTS db_name.table_name; |
RENAME（重命名表）语法
|
1 2 |
# 修改表名称; Mysql> RENAME TABLE old_table_name TO new_table_name; |
SHOW语法
|
1 2 3 4 5 6 7 8 |
# 查看所有表，必须先使用use选择一个默认库; Mysql> SHOW TABLES; # 使用LIKE模糊查询执行表; Mysql> SHOW TABLES LIKE '%tn%'; # 查看某个库下的所有表; Mysql> SHOW TABLES FROM db3; |
DESC语法
|
1 2 |
# 查看表结构; Mysql> DESC tn1; |
