MySQL数据库以及表的管理-白红宇

MySQL数据库以及表的管理

阅读量：6388 次

发布时间：2019-06-23

本文共 26822 字，大约阅读时间需要 89 分钟。

　　　　　　　　　　　　　　　　　　　　　　　　　　MySQL数据库以及表的管理

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

　　今天我们探讨的话题就是如何使用MySQL做开发，我们运维的主要工作不是去开发SQL的，但尽管如此，我们有可能需要对别人写出来的复杂SQL语句做性能评估，去分析他们写的SQL语句是不是足够高效，如果不是的话，我们还要能够达到对对方的SQL语句做改写的能力。所以，我们无需自行去开发SQL，但是我们一定要有一个看懂别人写的SQL的能力哟！

一.DBA的职责

1.开发DBA：

　　负责数据库设计（E-R关系图）、sql开发、内置函数、存储历程（存储过程和存储函数）、触发器、时间调度器（event scheduler）

2.管理DBA：

　　负责安装、升级、备份、恢复、用户管理、权限管理、监控、分析、基准测试，语句优化（SQL语句）、数据字典，按需要配置服务器（服务器变量：MyISAM，InnoDB，缓存，日志）

二.SQL语言组成部分

1.DDL：

　　全称Data Defination,我们叫它数据定义语言，典型命令指令有CREAT/ALTER/DROP

2.DML：

　　全称Data Manipulation,我们叫它数据操作语言，典型命令有INSERT/DELETE/SELECT/UPDATE

3.完整性定义语言，DDL的一部分功能

　　主键约束、外键约束、唯一键约束、条件约束、非空约束、事务约束

4.视图定义：即虚表，它是存储下来的select语句

5.事务控制：

　　例如Transactions（在mysql交互界面执行“HELP content”可以查看相关信息。）

6.嵌入式SQL和动态SQL：

7.DCL：

　　我们叫它数据控制语言，如实现授权和权限收回的典型命令有GRANT/REVOKE.

三.数据类型的功用

MySQL的数据类型请参考：

1.存储的值类型;

2.占据的存储空间大小;

3.定长，变长;

4.如何被索引及排序;

5.是否能够被索引;

四.数据字典：依赖系统编目（花名册）（system catalog）

　　对于关系型数据库来讲，它的数据字典也是另外找个地方存起来的。对于MySQL数据库来讲，这个位置就是名称为mysql的数据库。我们在第一次启动MySQL时，它第一步工作就是初始化系统表，所谓初始化系统表就是用来创建mysql这个数据库的。我们也可以称这个mysql数据库叫做MySQL的数据字典。数据字典是用来保存数据库服务器上的元数据。那么什么是元数据呢？我总结有以下几点：

1>.保存关系（表）的名字

2>.保存每个关系（表）的各字段的名字

3>.保存各字段的数据类型和长度

4>.保存约束条件

5>.保存每个关系（表）上的视图的名字及视图的定义

6>.保存授权用户（user表）的名字

7>.保存用户的授权和账户信息等

8>.统计类的数据，如每个关系字段的个数，每个关系中行数，每个关系的存储方法

9>.保存元数据的数据库（即：information_schema，mysql， performance_schema）

五.MySQL内部组件

　　如下图所示，连接器（Connectors）和连接池他们之间是建立连接关系的。连接池（Connection Pool）的所有SQL语句都得发送给SQL接口（SQL Interface）进行接收，然后再由分析器（Parser）进行分析，由优化器（Optimizer）进行优化处理，最终我们有可能在缓存（Caches&Buffers）中获取数据，实在再不行在交由存储引擎（Pluggable Storage Engines）去执行SQL语句。

六.MySQL中字符大小写情况说明

1.SQL关键字及函数不区分大小写;

2.数据库、表及视图名称的大小写区分与否取决于底层OS(操作系统)及FS（文件系统）;

3.存储过程、存储函数及事件调度器的名字不区分大小写，但触发器区分大小写;

4.表别名区分大小写;

5.对字段中的数据，如果字段类型为binary类型，则区分大小写，非binary不区分大小写;

七.SQL指令详解-数据库操作

1.数据库的创建

a>.查看创建库时的帮助信息

1 mysql> help create database             2 Name: 'CREATE DATABASE' 3 Description: 4 Syntax: 5 CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name 6     [create_specification] ... 7  8 create_specification: 9     [DEFAULT] CHARACTER SET [=] charset_name            　　 #设置字符集10   | [DEFAULT] COLLATE [=] collation_name                    #设置排序方式11 12 CREATE DATABASE creates a database with the given name. To use this13 statement, you need the CREATE privilege for the database. CREATE14 SCHEMA is a synonym for CREATE DATABASE.15 16 URL: http://dev.mysql.com/doc/refman/5.1/en/create-database.html17 18 19 mysql>

b>.创建不存在的数据库

1 mysql> show databases; 2 +--------------------+ 3 | Database           | 4 +--------------------+ 5 | information_schema | 6 | mysql              | 7 | test               | 8 +--------------------+ 9 3 rows in set (0.00 sec)10 11 mysql> 12 mysql> create database yinzhengjie;13 Query OK, 1 row affected (0.01 sec)14 15 mysql> show databases;16 +--------------------+17 | Database           |18 +--------------------+19 | information_schema |20 | mysql              |21 | test               |22 | yinzhengjie        |23 +--------------------+24 4 rows in set (0.00 sec)25 26 mysql>

c>.已经存在数据库的如何使用创建命令

1 mysql> show databases; 2 +--------------------+ 3 | Database           | 4 +--------------------+ 5 | information_schema | 6 | mysql              | 7 | test               | 8 | yinzhengjie        | 9 +--------------------+10 4 rows in set (0.00 sec)11 12 mysql> create database if not exists yinzhengjie;13 Query OK, 1 row affected, 1 warning (0.00 sec)14 15 mysql> show databases;16 +--------------------+17 | Database           |18 +--------------------+19 | information_schema |20 | mysql              |21 | test               |22 | yinzhengjie        |23 +--------------------+24 4 rows in set (0.00 sec)25 26 mysql>

2.数据库的删除

a>.查看删除数据库时的帮助信息

1 mysql> help drop database; 2 Name: 'DROP DATABASE' 3 Description: 4 Syntax: 5 DROP {DATABASE | SCHEMA} [IF EXISTS] db_name 6  7 DROP DATABASE drops all tables in the database and deletes the 8 database. Be very careful with this statement! To use DROP DATABASE, 9 you need the DROP privilege on the database. DROP SCHEMA is a synonym10 for DROP DATABASE.11 12 *Important*: When a database is dropped, user privileges on the13 database are not automatically dropped. See [HELP GRANT].14 15 IF EXISTS is used to prevent an error from occurring if the database16 does not exist.17 18 URL: http://dev.mysql.com/doc/refman/5.1/en/drop-database.html19 20 21 mysql>

b>.删除数据库操作

1 mysql> show databases; 2 +--------------------+ 3 | Database           | 4 +--------------------+ 5 | information_schema | 6 | mysql              | 7 | test               | 8 | yinzhengjie        | 9 +--------------------+10 4 rows in set (0.00 sec)11 12 mysql> drop database if exists yinzhengjie;13 Query OK, 0 rows affected (0.00 sec)14 15 mysql> show databases;16 +--------------------+17 | Database           |18 +--------------------+19 | information_schema |20 | mysql              |21 | test               |22 +--------------------+23 3 rows in set (0.00 sec)24 25 mysql>

3.修改数据库的字符集和排序字符以及数据字典

1 mysql> help alter database 2 Name: 'ALTER DATABASE' 3 Description: 4 Syntax: 5 ALTER {DATABASE | SCHEMA} [db_name] 6     alter_specification ... 7 ALTER {DATABASE | SCHEMA} db_name 8     UPGRADE DATA DIRECTORY NAME 9 10 alter_specification:11     [DEFAULT] CHARACTER SET [=] charset_name        #设置字符集12   | [DEFAULT] COLLATE [=] collation_name                #修改排序方式13 14 ALTER DATABASE enables you to change the overall characteristics of a15 database. These characteristics are stored in the db.opt file in the16 database directory. To use ALTER DATABASE, you need the ALTER privilege17 on the database. ALTER SCHEMA is a synonym for ALTER DATABASE.18 ....19 mysql>

4.数据库改名操作

　　如果我们在创建数据库的时候把数据库的名字起错了，这个时候你想要讲数据库名字改正过来。其实目前修改数据库名称基本上没有什么很好的办法，一个比较明智的做法就是讲该数据库的数据全部备份出来，然后将该库删除掉，创建你想要的数据库名称然后再把数据导入进去。还有一种非常暴力的做法就是去MySQL数据库目录下将创建错的目录进行改名操作，这种做法虽然是把数据库名称改正过来了，但是对于该库的数据字典并没有修改哟，因此这种做法我是不推荐去这样做的。

八.SQL指令详解-表的基本操作

1.MyISAM和InnoDB存储的区别概要

1 MyISAM表，每个表有三个文件，都位于数据库目录中 2 　　tb_name.frm:表结构定义 3 　　tb_name.MYD:数据文件 4 　　tb_name.MYI:索引文件 5 InnoDB表，有两种存储方式 6 　　第一种(默认方式)：每表有一个独立文件和一个多表共享的文件 7 　　　　tb_name.frm：表结构的定义，位于数据库目录中 8 　　　　ibdata#：共享的表空间文件，默认位于数据目录（datadir指向的目录）中 9 　　第二种（独立的表空间文件，推荐使用这一种方式）：10 　　　　tb_name.frm：每表有一个表结构文件11 　　　　tb_name.ibd：一个独立的表空间文件12 　　应该修改innodb_file_per_table为ON，我们可以在mysql的配置文件中的[msyqld]下的字段修改它的值为NO即可完成永久生效哟。

2.表的第一种方式，即自定义新表格式

1 CREATE [TEMPORARY（临时表，保存在内存中）] TABLE [IF NOT EXISTS] tbl_name 2 (create_definition,...) 3 [table_options] 4 (create_definition,...) 5 字段的定义：字段名、类型和类型修饰符 6 键、索引和约束 7 primary key，unique key，foreign key，check 8 {index|key} 9 [table_options]10 engine [=] engine_name11 AUTO_INCREMENT [=] value 指定AUTO_INCREMENT的起始值12 [DEFAULT] CHARACTER SET [=] charset_name 指定默认字符集13 CHECKSUM [=] {
         0 | 1} 是否使用校验值14 [DEFAULT] COLLATE [=] collation_name 排序规则15 COMMENT [=] 'string' 注释16 DELAY_KEY_WRITE [=] {
         0 | 1} 是否启用键延迟写入17 ROW_FORMAT [=] {DEFAULT（默认）|DYNAMIC（动态）|FIXED（静态）|COMPRESSED（压缩）|REDUNDANT（冗余）|COMPACT（紧致）} 表格式18 TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] 表空间

1 mysql> help create table 2 Name: 'CREATE TABLE' 3 Description: 4 Syntax: 5 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name 6     (create_definition,...) 7     [table_options] 8     [partition_options] 9 ....10 mysql>

查看创建表的帮助信息

1 mysql> select database(); 2 +------------+ 3 | database() | 4 +------------+ 5 | NULL       | 6 +------------+ 7 1 row in set (0.00 sec) 8  9 mysql> 10 mysql> show databases;11 +--------------------+12 | Database           |13 +--------------------+14 | information_schema |15 | mysql              |16 | test               |17 +--------------------+18 3 rows in set (0.00 sec)19 20 mysql> create database if not exists yinzhengjie;21 Query OK, 1 row affected (0.00 sec)22 23 mysql> use yinzhengjie24 Database changed25 mysql> 26 mysql> select database();27 +-------------+28 | database()  |29 +-------------+30 | yinzhengjie |31 +-------------+32 1 row in set (0.00 sec)33 34 mysql> create table t1 (Name varchar(50) not null,Age tinyint unsigned not null,primary key(Name,Age));            #创建一个表，定义Name字段类型自动变化长度的字符类型（varchar），不能为空，定义一个Age字段类型为微整形（tinyint），也不能为空，定义主键（primary key）是Name和Age两个字段。35 Query OK, 0 rows affected (0.08 sec)36 37 mysql> desc t1;38 +-------+---------------------+------+-----+---------+-------+39 | Field | Type                | Null | Key | Default | Extra |40 +-------+---------------------+------+-----+---------+-------+41 | Name  | varchar(50)         | NO   | PRI | NULL    |       |42 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |43 +-------+---------------------+------+-----+---------+-------+44 2 rows in set (0.00 sec)45 46 mysql>

创建一个表包含主键的表

1 mysql> show table status like 't1'\G;        #查看之前创建表的存储引擎。 2 *************************** 1. row *************************** 3            Name: t1 4          Engine: MyISAM 5         Version: 10 6      Row_format: Dynamic 7            Rows: 0 8  Avg_row_length: 0 9     Data_length: 010 Max_data_length: 28147497671065511    Index_length: 102412       Data_free: 013  Auto_increment: NULL14     Create_time: 2017-11-17 03:10:5515     Update_time: 2017-11-17 03:10:5516      Check_time: NULL17       Collation: latin1_swedish_ci18        Checksum: NULL19  Create_options: 20         Comment: 21 1 row in set (0.00 sec)22 23 ERROR: 24 No query specified25 26 mysql> 27 mysql> drop table t1;28 Query OK, 0 rows affected (0.00 sec)29 30 mysql> create table t1 (Name varchar(50) not null,Age tinyint unsigned not null,primary key(Name,Age)) engine='InnoDB';31 Query OK, 0 rows affected (0.05 sec)32 33 mysql> show table status like 't1'\G;34 *************************** 1. row ***************************35            Name: t136          Engine: InnoDB37         Version: 1038      Row_format: Compact39            Rows: 040  Avg_row_length: 041     Data_length: 1638442 Max_data_length: 043    Index_length: 044       Data_free: 419430445  Auto_increment: NULL46     Create_time: 2017-11-17 03:19:5347     Update_time: NULL48      Check_time: NULL49       Collation: latin1_swedish_ci50        Checksum: NULL51  Create_options: 52         Comment: 53 1 row in set (0.00 sec)54 55 ERROR: 56 No query specified57 58 mysql>

创建表的存储引擎

3.表的第二种创建方式，即复制表数据

1 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name2     [(create_definition,...)]3     [table_options]4     select_statement    #将select的结果来作为字段创建新表的字段，但是可能失去属性定义的。

用法格式

1 mysql> select * from t1; 2 Empty set (0.01 sec) 3  4 mysql>  5 mysql> insert into t1(Name,Age) values ("yinzhengjie",18); 6 Query OK, 1 row affected (0.00 sec) 7  8 mysql>  9 mysql> select * from t1;10 +-------------+-----+11 | Name        | Age |12 +-------------+-----+13 | yinzhengjie |  18 |14 +-------------+-----+15 1 row in set (0.00 sec)16 17 mysql> 18 mysql> create table t2 select * from t1;19 Query OK, 1 row affected (0.01 sec)20 Records: 1  Duplicates: 0  Warnings: 021 22 mysql> select * from t2;23 +-------------+-----+24 | Name        | Age |25 +-------------+-----+26 | yinzhengjie |  18 |27 +-------------+-----+28 1 row in set (0.00 sec)29 30 mysql>  31 mysql> desc t1;32 +-------+---------------------+------+-----+---------+-------+33 | Field | Type                | Null | Key | Default | Extra |34 +-------+---------------------+------+-----+---------+-------+35 | Name  | varchar(50)         | NO   | PRI | NULL    |       |36 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |37 +-------+---------------------+------+-----+---------+-------+38 2 rows in set (0.00 sec)39 40 mysql> desc t2;            #我们可以发现t2的表中的字段属性和t1的并不一致哟！只是数值一致而已。41 +-------+---------------------+------+-----+---------+-------+42 | Field | Type                | Null | Key | Default | Extra |43 +-------+---------------------+------+-----+---------+-------+44 | Name  | varchar(50)         | NO   |     | NULL    |       |45 | Age   | tinyint(3) unsigned | NO   |     | NULL    |       |46 +-------+---------------------+------+-----+---------+-------+47 2 rows in set (0.00 sec)48 49 mysql>

案例展示

4.表的第三种创建方式，即复制表结构

1  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name2     { LIKE old_tbl_name | (LIKE old_tbl_name) }

用法格式

1 mysql> select database(); 2 +-------------+ 3 | database()  | 4 +-------------+ 5 | yinzhengjie | 6 +-------------+ 7 1 row in set (0.00 sec) 8  9 mysql> show tables;10 +-----------------------+11 | Tables_in_yinzhengjie |12 +-----------------------+13 | t1                    |14 | t2                    |15 +-----------------------+16 2 rows in set (0.00 sec)17 18 mysql> create table t3 like t1;19 Query OK, 0 rows affected (0.04 sec)20 21 mysql> select * from t3;22 Empty set (0.01 sec)23 24 mysql> desc t3;25 +-------+---------------------+------+-----+---------+-------+26 | Field | Type                | Null | Key | Default | Extra |27 +-------+---------------------+------+-----+---------+-------+28 | Name  | varchar(50)         | NO   | PRI | NULL    |       |29 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |30 +-------+---------------------+------+-----+---------+-------+31 2 rows in set (0.00 sec)32 33 mysql> desc t1;34 +-------+---------------------+------+-----+---------+-------+35 | Field | Type                | Null | Key | Default | Extra |36 +-------+---------------------+------+-----+---------+-------+37 | Name  | varchar(50)         | NO   | PRI | NULL    |       |38 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |39 +-------+---------------------+------+-----+---------+-------+40 2 rows in set (0.01 sec)41 42 mysql>

用法展示

5.创建一张表的思想

　　我们可以自定义表，重新创建表的结构，当然也可以通过第三种方式找一个符合我们要求的表结构复制出来，然后通过insert语句将数据插入到这个心创建的表中即可。

6.表的删除

1 DROP [TEMPORARY] TABLE [IF EXISTS]2       tbl_name [, tbl_name] ...3       [RESTRICT | CASCADE]

用法格式

1 mysql> select database(); 2 +-------------+ 3 | database()  | 4 +-------------+ 5 | yinzhengjie | 6 +-------------+ 7 1 row in set (0.00 sec) 8  9 mysql> show tables;10 +-----------------------+11 | Tables_in_yinzhengjie |12 +-----------------------+13 | t1                    |14 | t2                    |15 | t3                    |16 +-----------------------+17 3 rows in set (0.00 sec)18 19 mysql> drop table t2,t3;20 Query OK, 0 rows affected (0.07 sec)21 22 mysql> show tables;23 +-----------------------+24 | Tables_in_yinzhengjie |25 +-----------------------+26 | t1                    |27 +-----------------------+28 1 row in set (0.00 sec)29 30 mysql>

用法展示

7.表的修改操作

1 ALTER TABLE tbl_name 2       [alter_specification [, alter_specification] ...] 3     修改字段定义： 4        插入新字段： 5          ADD [COLUMN] col_name column_definition 6          [FIRST | AFTER col_name ] 7        删除字段 8          DROP [COLUMN] col_name 9        修改字段10           修改字段名称11           CHANGE [COLUMN] old_col_name new_col_name column_definition12           [FIRST|AFTER col_name]13           修改字段类型及属性等14           MODIFY [COLUMN] col_name column_definition15           [FIRST | AFTER col_name]16        表改名：17           rename to|as new tb_name18        修改存储引擎19            engine = 20        指定排序标准的字段21            ORDER BY col_name [, col_name] ...

用法格式集合

1 mysql> desc t1; 2 +-------+---------------------+------+-----+---------+-------+ 3 | Field | Type                | Null | Key | Default | Extra | 4 +-------+---------------------+------+-----+---------+-------+ 5 | Name  | varchar(50)         | NO   | PRI | NULL    |       | 6 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       | 7 +-------+---------------------+------+-----+---------+-------+ 8 2 rows in set (0.00 sec) 9 10 mysql> alter table t1 add ID tinyint unsigned not null;11 Query OK, 1 row affected (0.02 sec)12 Records: 1  Duplicates: 0  Warnings: 013 14 mysql> desc t1;15 +-------+---------------------+------+-----+---------+-------+16 | Field | Type                | Null | Key | Default | Extra |17 +-------+---------------------+------+-----+---------+-------+18 | Name  | varchar(50)         | NO   | PRI | NULL    |       |19 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |20 | ID    | tinyint(3) unsigned | NO   |     | NULL    |       |21 +-------+---------------------+------+-----+---------+-------+22 3 rows in set (0.00 sec)23 24 mysql>

插入新字段用法展示

1 mysql> desc t1; 2 +-------+---------------------+------+-----+---------+-------+ 3 | Field | Type                | Null | Key | Default | Extra | 4 +-------+---------------------+------+-----+---------+-------+ 5 | Name  | varchar(50)         | NO   | PRI | NULL    |       | 6 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       | 7 | ID    | tinyint(3) unsigned | NO   |     | NULL    |       | 8 +-------+---------------------+------+-----+---------+-------+ 9 3 rows in set (0.00 sec)10 11 mysql> 12 mysql> alter table t1 add Gender enum('boy','girl') not null default 'boy' after Age;    #插入的字段我们用关键字“after”指定在“Age”之后。（注意，如果你使用first则表示插入在第一个字段哟）13 Query OK, 1 row affected (0.05 sec)14 Records: 1  Duplicates: 0  Warnings: 015 16 mysql> desc t1;17 +--------+---------------------+------+-----+---------+-------+18 | Field  | Type                | Null | Key | Default | Extra |19 +--------+---------------------+------+-----+---------+-------+20 | Name   | varchar(50)         | NO   | PRI | NULL    |       |21 | Age    | tinyint(3) unsigned | NO   | PRI | NULL    |       |22 | Gender | enum('boy','girl')  | NO   |     | boy     |       |23 | ID     | tinyint(3) unsigned | NO   |     | NULL    |       |24 +--------+---------------------+------+-----+---------+-------+25 4 rows in set (0.00 sec)26 27 mysql>

插入指定的位置用法展示

1 mysql> desc t1; 2 +--------+---------------------+------+-----+---------+-------+ 3 | Field  | Type                | Null | Key | Default | Extra | 4 +--------+---------------------+------+-----+---------+-------+ 5 | Name   | varchar(50)         | NO   | PRI | NULL    |       | 6 | Age    | tinyint(3) unsigned | NO   | PRI | NULL    |       | 7 | Gender | enum('boy','girl')  | NO   |     | boy     |       | 8 | ID     | tinyint(3) unsigned | NO   |     | NULL    |       | 9 +--------+---------------------+------+-----+---------+-------+10 4 rows in set (0.00 sec)11 12 mysql> 13 mysql> alter table t1 drop Gender;14 Query OK, 1 row affected (0.02 sec)15 Records: 1  Duplicates: 0  Warnings: 016 17 mysql> desc t1;18 +-------+---------------------+------+-----+---------+-------+19 | Field | Type                | Null | Key | Default | Extra |20 +-------+---------------------+------+-----+---------+-------+21 | Name  | varchar(50)         | NO   | PRI | NULL    |       |22 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |23 | ID    | tinyint(3) unsigned | NO   |     | NULL    |       |24 +-------+---------------------+------+-----+---------+-------+25 3 rows in set (0.00 sec)26 27 mysql>

删除字段案例展示

1 mysql> desc t1; 2 +-------+---------------------+------+-----+---------+-------+ 3 | Field | Type                | Null | Key | Default | Extra | 4 +-------+---------------------+------+-----+---------+-------+ 5 | Name  | varchar(50)         | NO   | PRI | NULL    |       | 6 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       | 7 | ID    | tinyint(3) unsigned | NO   |     | NULL    |       | 8 +-------+---------------------+------+-----+---------+-------+ 9 3 rows in set (0.00 sec)10 11 mysql> alter table t1 modify ID tinyint unsigned not null first;12 Query OK, 1 row affected (0.02 sec)13 Records: 1  Duplicates: 0  Warnings: 014 15 mysql> desc t1;16 +-------+---------------------+------+-----+---------+-------+17 | Field | Type                | Null | Key | Default | Extra |18 +-------+---------------------+------+-----+---------+-------+19 | ID    | tinyint(3) unsigned | NO   |     | NULL    |       |20 | Name  | varchar(50)         | NO   | PRI | NULL    |       |21 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |22 +-------+---------------------+------+-----+---------+-------+23 3 rows in set (0.00 sec)24 25 mysql>

修改字段类型及属性等（改变字段的位置）

1 mysql> desc t1; 2 +-------+---------------------+------+-----+---------+-------+ 3 | Field | Type                | Null | Key | Default | Extra | 4 +-------+---------------------+------+-----+---------+-------+ 5 | ID    | tinyint(3) unsigned | NO   |     | NULL    |       | 6 | Name  | varchar(50)         | NO   | PRI | NULL    |       | 7 | Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       | 8 +-------+---------------------+------+-----+---------+-------+ 9 3 rows in set (0.00 sec)10 11 mysql> alter table t1 change Name StudentName char(50) not null;12 Query OK, 1 row affected (0.03 sec)13 Records: 1  Duplicates: 0  Warnings: 014 15 mysql> desc t1;16 +-------------+---------------------+------+-----+---------+-------+17 | Field       | Type                | Null | Key | Default | Extra |18 +-------------+---------------------+------+-----+---------+-------+19 | ID          | tinyint(3) unsigned | NO   |     | NULL    |       |20 | StudentName | char(50)            | NO   | PRI | NULL    |       |21 | Age         | tinyint(3) unsigned | NO   | PRI | NULL    |       |22 +-------------+---------------------+------+-----+---------+-------+23 3 rows in set (0.00 sec)24 25 mysql>

修改字段名称

1 mysql> show indexes from t1;                #查看当前索引信息 2 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 3 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | 4 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 5 | t1    |          0 | PRIMARY  |            1 | StudentName | A         |           1 |     NULL | NULL   |      | BTREE      |         | 6 | t1    |          0 | PRIMARY  |            2 | Age         | A         |           1 |     NULL | NULL   |      | BTREE      |         | 7 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 8 2 rows in set (0.00 sec) 9 10 mysql> alter table t1 add index(StudentName);        #创建一个索引11 Query OK, 1 row affected (0.02 sec)12 Records: 1  Duplicates: 0  Warnings: 013 14 mysql> show indexes from t1;15 +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+16 | Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |17 +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+18 | t1    |          0 | PRIMARY     |            1 | StudentName | A         |           1 |     NULL | NULL   |      | BTREE      |         |19 | t1    |          0 | PRIMARY     |            2 | Age         | A         |           1 |     NULL | NULL   |      | BTREE      |         |20 | t1    |          1 | StudentName |            1 | StudentName | A         |           1 |     NULL | NULL   |      | BTREE      |         |21 +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+22 3 rows in set (0.00 sec)23 24 mysql> alter table t1 drop INDEX  StudentName;        #删除索引信息25 Query OK, 1 row affected (0.04 sec)26 Records: 1  Duplicates: 0  Warnings: 027 28 mysql> show indexes from t1;29 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+30 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |31 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+32 | t1    |          0 | PRIMARY  |            1 | StudentName | A         |           1 |     NULL | NULL   |      | BTREE      |         |33 | t1    |          0 | PRIMARY  |            2 | Age         | A         |           1 |     NULL | NULL   |      | BTREE      |         |34 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+35 2 rows in set (0.00 sec)36 37 mysql>

创建索引和删除索引用法展示

1 mysql> select database(); 2 +-------------+ 3 | database()  | 4 +-------------+ 5 | yinzhengjie | 6 +-------------+ 7 1 row in set (0.01 sec) 8  9 mysql> show tables;10 +-----------------------+11 | Tables_in_yinzhengjie |12 +-----------------------+13 | t1                    |14 +-----------------------+15 1 row in set (0.00 sec)16 17 mysql> alter table t1 rename to mysql_test_table;        #可以用alter命令进行修改标明18 Query OK, 0 rows affected (0.00 sec)19 20 mysql> show tables;21 +-----------------------+22 | Tables_in_yinzhengjie |23 +-----------------------+24 | mysql_test_table      |25 +-----------------------+26 1 row in set (0.00 sec)27 28 mysql> 29 mysql> show tables;30 +-----------------------+31 | Tables_in_yinzhengjie |32 +-----------------------+33 | mysql_test_table      |34 +-----------------------+35 1 row in set (0.00 sec)36 37 mysql> rename table mysql_test_table to t1;            #当然我们也可以直接用rename命令进行修改哟~38 Query OK, 0 rows affected (0.01 sec)39 40 mysql> show tables;41 +-----------------------+42 | Tables_in_yinzhengjie |43 +-----------------------+44 | t1                    |45 +-----------------------+46 1 row in set (0.00 sec)47 48 mysql>

修改表名的两种常见的姿势

1 mysql> show table status like 't1'\G 2 *************************** 1. row *************************** 3            Name: t1 4          Engine: InnoDB 5         Version: 10 6      Row_format: Compact 7            Rows: 1 8  Avg_row_length: 16384 9     Data_length: 1638410 Max_data_length: 011    Index_length: 012       Data_free: 419430413  Auto_increment: NULL14     Create_time: 2017-11-17 05:07:5615     Update_time: NULL16      Check_time: NULL17       Collation: latin1_swedish_ci18        Checksum: NULL19  Create_options: 20         Comment: 21 1 row in set (0.00 sec)22 23 mysql> alter table t1 engine=MyISAM;24 Query OK, 1 row affected (0.02 sec)25 Records: 1  Duplicates: 0  Warnings: 026 27 mysql> show table status like 't1'\G28 *************************** 1. row ***************************29            Name: t130          Engine: MyISAM31         Version: 1032      Row_format: Fixed33            Rows: 134  Avg_row_length: 5335     Data_length: 5336 Max_data_length: 1491817376566476737    Index_length: 204838       Data_free: 039  Auto_increment: NULL40     Create_time: 2017-11-17 05:11:5741     Update_time: 2017-11-17 05:11:5742      Check_time: NULL43       Collation: latin1_swedish_ci44        Checksum: NULL45  Create_options: 46         Comment: 47 1 row in set (0.00 sec)48 49 mysql>

修改表选项案例展示