MySQL学习笔记（1）基础操作

数据库简介

什么是数据库：

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，

每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS即关系数据库管理系统(Relational Database Management System)的特点：

1. 数据以表格的形式出现
2. 每行为各种记录名称
3. 每列为记录名称所对应的数据域
4. 许多的行和列组成一张表单
5. 若干的表单组成database

RDBMS术语：

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
• 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
• 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• 外键：外键用于关联两个表。
• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQL数据库简介：

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

• MySQL 是开源的，所以你不需要支付额外的费用。
• MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
• MySQL 使用标准的SQL数据语言形式。
• MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
• MySQL 对PHP有很好的支持，PHP是目前最流行的Web开发语言。
• MySQL 支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
• MySQL 是可以定制的，采用了GPL（General Public License）协议，你可以修改源码来开发自己的 MySQL 系统。

MySQL管理

启动和关闭MySQL服务器：

在windows中，启动mysql服务器的命令为：net start mysql

在Linux中，启动mysql服务器的命令为：service mysql start/stop/restart

用户管理：

创建用户：

命令：

create user 'username'@'host' identified by 'password';

另一种创建用户的方法：

grant select, insert, update, delete, create,drop
    on tutorials.*
    to 'hive'@'localhost'
    identified by 'hive123';

说明：

• username：创建的用户名
• host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%。
• password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
• 注意: MySQL 的SQL语句以分号 (;) 作为结束标识。

例如：

create user 'hive'@'localhost' identified by 'hive123';

授权：

命令：

grant privileges on databasename.tablename to 'username'@'host';

说明:

• privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
• databasename：数据库名
• tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

所有的权限为：

​ Alter 　　修改表和索引

　　Create 　创建数据库和表

　　Delete 　删除表中已有的记录

　　Drop 　　 抛弃（删除）数据库和表

　　INDEX 　创建或抛弃索引

　　Insert 　 向表中插入新行

　　REFERENCE 　　未用

　　Select　　　　 检索表中的记录

　　Update 　　　　 修改现存表记录

　　FILE 　　　　　　读或写服务器上的文件

　　PROCESS 　　 查看服务器中执行的线程信息或杀死线程

　　RELOAD 　　　　重载授权表或清空日志、主机缓存或表缓存。

　　SHUTDOWN　　 关闭服务器

　　ALL 　　　　　　所有；ALL PRIVILEGES同义词

　　USAGE 　　　　特殊的“无权限”权限

例如：

grant ALL on *.* to 'hive'@'localhost';

注意：

用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:

> GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
>

设置与更改用户密码：

命令：

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

如果是当前登陆用户用:

SET PASSWORD = PASSWORD("newpassword");

例子:

set password for 'hive'@'localhost'=password('hive');

撤销用户权限：

命令：

revoke privilege on databasename.tablename FROM 'username'@'host';

例如；

revoke all on *.* from 'hive'@'localhost';

删除用户：

drop user 'hive'@'localhost';

注意：在创建对用户管理后，必须执行flush privileges 命令， 这个命令执行后会重新载入授权表。如果你不使用该命令，你就无法使用新创建的用户来连接mysql服务器，除非你重启mysql服务器。

管理MySQL的命令：

切换数据库视图：

use database_name选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

mysql> use mysql;
Database changed

列出所有的数据库：

show databases; 列出 MySQL 数据库管理系统的数据库列表。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

列出所有的表：

show tables; 显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

显示数据表信息：

show columns from tablename 显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。

mysql> show columns from db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(32)      | NO   | PRI |         |       |
......

显示数据表的索引信息：

show index from tablename 显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

显示数数库管理系统的性能及统计信息：

show table status from dababases; 该命令将输出Mysql数据库管理系统的性能及统计信息。

mysql> SHOW TABLE STATUS  FROM RUNOOB;   # 显示数据库 RUNOOB 中所有表的信息

mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%';     # 表名以runoob开头的表的信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G;   # 加上 \G，查询结果按列打印

数据库常用基本操作

创建数据库：

使用create命令创建数据库：

mysql> create database test_db;
Query OK, 1 row affected (0.01 sec)

用 mysqladmin 创建数据库

root用户拥有最高权限，可以使用 mysql mysqladmin 命令来创建数据库。

C:\Users\曹世宏>mysqladmin -u root -p create testdb
Enter password: ******

删除数据库：

使用drop命令删除数据库：

mysql> drop database test_db;
Query OK, 0 rows affected (0.00 sec)

使用mysqladmin删除数据库：

C:\Users\曹世宏>mysqladmin -u root -p drop testdb
Enter password: ******
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'testdb;' database [y/N] y
Database "testdb" dropped

选择数据库：

mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

mysql> use test_db;
Database changed
mysql>

执行以上命令后，就已经成功选择了 test_db 数据库，在后续的操作中都会在 该数据库中执行。

注意:所有的数据库名，表名，表字段都是区分大小写的。

数据类型：

MySQL中定义数据字段的类型对数据库的优化是非常重要的。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型：

• MySQL支持所有标准SQL数值数据类型。
• 这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
• 关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
• BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
• 作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT（tinyint）	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT(smallint)	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT(mediumint)	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL(decimal)	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型：

• 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
• 每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。

型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型：

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

创建数据表：

创建MySQL数据表需要以下信息：

• 表名
• 表字段名
• 定义每个表字段

语法：以下为创建MySQL数据表的SQL通用语法：

CREATE TABLE table_name (column_name column_type);

例如：创建一个student表，id为主键，有name，sex，age。

create table if not exists `student` (
	`id` int unsigned auto_increment,
    `name` varchar(20) not null,
    `sex` varchar(5) not null,
    `age` int not null,
	primary key (`id`)
	)engine=InnoDB default charset=utf8;

• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
• PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
• ENGINE 设置存储引擎，CHARSET 设置编码。

删除数据表：

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| student           |
| teacher           |
+-------------------+
2 rows in set (0.00 sec)

mysql> drop TABLE `teacher`;
Query OK, 0 rows affected (0.02 sec)

插入数据：

MySQL 表中使用 INSERT INTO SQL语句来插入数据。

语法:以下为向MySQL数据表插入数据通用的INSERT INTO SQL语法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

如果数据是字符型，必须使用单引号或者双引号，如：”value”。

例如：

mysql> INSERT INTO student
    -> (name, sex, age)
    -> VALUES
    -> ("张三", "男", 19);
Query OK, 1 row affected (0.01 sec)

读取数据表：

mysql> select * from student;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  1 | 张三 | 男  |  19 |
|  2 | 李四 | 男  |  20 |
|  3 | 王五 | 女  |  22 |
+----+------+-----+-----+
5 rows in set (0.00 sec)

查询数据：

MySQL 数据库使用SQL SELECT语句来查询数据。

语法：以下为在MySQL数据库中查询数据通用的 SELECT 语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

• 查询语句中可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
• SELECT 命令可以读取一条或者多条记录。
• 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
• 你可以使用 WHERE 语句来包含任何条件。
• 你可以使用 LIMIT 属性来设定返回的记录数。
• 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

查询示例：

mysql> select * from student;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  1 | 张三 | 男  |  19 |
|  2 | 李四 | 男  |  20 |
|  3 | 王五 | 女  |  22 |
+----+------+-----+-----+
3 rows in set (0.00 sec)

mysql> select name from student ;
+------+
| name |
+------+
| 张三 |
| 李四 |
| 王五 |
+------+
3 rows in set (0.00 sec)

MySQL WHERE 子句：

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

语法：以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

• 查询语句中可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
• 可以在 WHERE 子句中指定任何条件。
• 可以使用 AND 或者 OR 指定一个或多个条件。
• WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
• WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

以下为操作符列表，可用于 WHERE 子句中。

下表中实例假定 A 为 10, B 为 20

操作符	描述	实例
=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。

如果我们想再 MySQL 数据表中读取指定的数据，WHERE 子句是非常有用的。

使用主键来作为 WHERE 子句的条件查询是非常快速的。

如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

使用示例:

mysql> select * from student where id=2;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  2 | 李四 | 男  |  20 |
+----+------+-----+-----+
1 row in set (0.00 sec)

mysql> select * from student where name="张三";
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  1 | 张三 | 男  |  19 |
+----+------+-----+-----+
1 row in set (0.00 sec)
mysql> select * from student where age>=20;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  2 | 李四 | 男  |  20 |
|  3 | 王五 | 女  |  22 |
+----+------+-----+-----+
2 rows in set (0.00 sec)

MySQL UPDATE查询：

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。

语法：以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

• 可以同时更新一个或多个字段。
• 可以在 WHERE 子句中指定任何条件。
• 可以在一个单独表中同时更新数据。

当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

使用示例：

mysql> update student set age=25 where name="李四";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  1 | 张三 | 男  |  19 |
|  2 | 李四 | 男  |  25 |
|  3 | 王五 | 女  |  22 |
+----+------+-----+-----+
3 rows in set (0.00 sec)

MySQL DELETE 语句：

可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。

语法：以下是 SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法：

DELETE FROM table_name [WHERE Clause]

• 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
• 可以在 WHERE 子句中指定任何条件
• 可以在单个表中一次性删除记录。

当想删除数据表中指定的记录时 WHERE 子句是非常有用的。

使用示例：

mysql> delete from student where id=3;
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  1 | 张三 | 男  |  19 |
|  2 | 李四 | 男  |  25 |
+----+------+-----+-----+
2 rows in set (0.00 sec)

MySQL LIKE 子句：

当要获取包含某字符串的记录是，可以使用like子句。

SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

语法：以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• 可以在 WHERE 子句中指定任何条件。
• 可以在 WHERE 子句中使用LIKE子句。
• 可以使用LIKE子句代替等号 =。
• LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
• 可以使用 AND 或者 OR 指定一个或多个条件。
• 可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

使用示例：

mysql> select * from student where name like '%三' ;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  1 | 张三 | 男  |  19 |
+----+------+-----+-----+
1 row in set (0.00 sec)
mysql> select * from student where age like "%5";
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  2 | 李四 | 男  |  25 |
+----+------+-----+-----+
1 row in set (0.00 sec)

MySQL ALTER 命令：

当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

删除，添加或修改表字段：

使用ALTER命令及DROP子句来删除表中的列字段；操作如下：

如果数据表中只剩余一个字段则无法使用DROP来删除字段。

mysql> show columns from student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | NO   |     | NULL    |                |
| sex   | varchar(5)       | NO   |     | NULL    |                |
| age   | int(11)          | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> alter TABLE student Drop sex;
#删除表的sex列。
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | NO   |     | NULL    |                |
| age   | int(11)          | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 student中添加 class字段，并定义数据类型:

添加的列会自动添加到数据表字段得末尾。如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

FIRST 和 AFTER 关键字只占用于 ADD 子句，所以如果想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

mysql> alter table student add class varchar(30);
#添加class列。
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)      | NO   |     | NULL    |                |
| age   | int(11)          | NO   |     | NULL    |                |
| class | varchar(30)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> alter table student ADD number int after id;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| number | int(11)          | YES  |     | NULL    |                |
| name   | varchar(20)      | NO   |     | NULL    |                |
| age    | int(11)          | NO   |     | NULL    |                |
| class  | varchar(30)      | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

修改字段类型及名称：

如果需要修改字段类型及名称, 可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

mysql> alter table student modify class char(20);
#修改class的字段类型为char（20）
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table student change number num int;
#修改number名称为num；
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| num   | int(11)          | YES  |     | NULL    |                |
| name  | varchar(20)      | NO   |     | NULL    |                |
| age   | int(11)          | NO   |     | NULL    |                |
| class | char(20)         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

使用change子句是，在CHARGE关键字之后，紧跟着要修改的字段名，然后指定字段名及类型。

ALTER TABLE对Null值和默认值的影响：

当修改字段时，可以指定是否包含值或者是否设置默认值。

ysql> alter table student modify num INT NULL DEFAULT 100;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| num   | int(11)          | YES  |     | 100     |                |
| name  | varchar(20)      | NO   |     | NULL    |                |
| age   | int(11)          | NO   |     | NULL    |                |
| class | varchar(20)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

如果不设置默认值，MySQL会自动设置该字段默认为 NULL。

修改字段默认值：

可以使用 ALTER 来修改字段的默认值，尝试以下实例：

mysql> alter table student alter num set default 200;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| num   | int(11)          | YES  |     | 200     |                |
| name  | varchar(20)      | NO   |     | NULL    |                |
| age   | int(11)          | NO   |     | NULL    |                |
| class | varchar(20)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

也可以使用 ALTER 命令及 DROP子句来删除字段的默认值，如下实例：

mysql> alter table student alter num drop default;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改数据表类型，可以使用 ALTER 命令及 TYPE 子句来完成。尝试以下实例，我们将表 student 的类型修改为 MYISAM ：

注意：查看数据表类型可以使用 SHOW TABLE STATUS 语句。

mysql> alter table student engine=myisam;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> show table status like 'student'\G;
*************************** 1. row ***************************
           Name: student
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 24
    Data_length: 48
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: 6
    Create_time: 2018-07-08 23:04:34
    Update_time: 2018-07-08 23:04:34
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

ERROR:
No query specified

修改表名：

如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。

mysql> alter table student rename to students;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| students          |
+-------------------+
1 row in set (0.00 sec)

ALTER 命令还可以用来创建及删除MySQL数据表的索等。

---

学习MySQL参考资料来源自：http://www.runoob.com/mysql/mysql-tutorial.html