mysql中的修改语句 mysql修改语句的语法

MySQL数据库、数据表及数据类型

• 一.MySQL数据库

• 1.创建数据库：
• 2.查看数据库：
• 3.修改数据库：
• 4.删除数据库：
• 5.选择数据库：
• 6.存储引擎：

• 二.MySQL表和数据类型

• 1.创建数据表
• 2.修改数据表
• 3.删除数据表
• 4.数据类型简介
• 5.整数类型
• 6.小数类型
• 7.日期和时间类型
• 8.字符串类型
• 9.二进制类型

参考链接：http://www.voidme.com/mysql/mysql-create-database

一.MySQL数据库

1.创建数据库：

create database [if not exists] <数据库名字> 
[[default] character set <字符集名>]
[[default] collate <校对规则名>];

a.数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义，不区分大小写。
b.只有当前创建的数据库名字不存在时才能执行操作。即不能创建同名的数据库，否则将会报错。
c.if not exists:为避免创建同名的数据库时的报错，可以加上if not exists。
d.[default] character set <字符集名>:指定数据库的字符集。避免在数据库中存储的数据出现乱码的情况。
e.[default] collate <校对规则名>：指定字符集的默认校对规则。

2.查看数据库：

show databases [like '数据库名'];

a. like ‘数据库名’：用于匹配指定的数据库名称。可部分匹配，也可完全匹配。
b.查看名字中包含test的数据库：show databases like ‘%test%’;
c.查看名字以db开头的数据库：show databases like ‘db%’;
d.查看名字以db结尾的数据库：show databases like ‘%db’;

3.修改数据库：

alter database [数据库名] {
[default] character set <字符集名> |
[default] collate <校对规则名>}

a.用于更改数据库的全局特性。
b.需要获得数据库alter权限。
c.数据库名称可忽略，此时语句对应于默认数据库。
d.[default] character set <字符集名>：用于更改默认的数据库字符集。

4.删除数据库：

drop database [if exists] <数据库名>;

5.选择数据库：

use <数据库名>;

a.当用create database语句创建数据库后，该数据库不会自动成为当前数据库，需要用use来指定当前数据库。
b.该数据库保持为默认数据库，直到语段的结尾，或者直到遇见一个不同的use语句。

6.存储引擎：

修改数据库临时的默认存储引擎：

set default_storage_engine=<存储引擎名>

a.InnoDB是MySQL系统的默认引擎，支持可靠的事务处理。

b.使用上面的语句可以更改MySQL的默认存储引擎，但重启客户端后，默认存储引擎仍为InnoDB。

c.MySQL的四种存储引擎：

①如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。

②如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。

③如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。

④如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

二.MySQL表和数据类型

1.创建数据表

create table <表名> ([表定义选项]) [表选项] [分区选项];

a.表名：指定要创建表的名称，必须符合标识符命名规则。
表名称被指定为db_name.tbl_name，以便在特定的数据库中创建表。在当前数据库中创建表时，可省略db_name。
b.[表定义选项]的格式：<列名1><类型1>[,…,]<列名n>
<类型n>
由列名、列的定义以及可能的空值说明、完整性约束或表索引组成。
c.数据表中每个列（字段）的名称和数据类型，如果创建多个列，要用逗号隔开。
d.在创建数据表之前，应使用“use 数据库”指定操作在哪个数据库中进行，若没有选择数据库，就会抛出错误。

①查看表：

show tables like '表名';

②查看表结构：
法一：

describe <表名>;
或
desc <表名>;

可查看表的字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等。

法二：

show create table <表名> \G;

可以用来显示创建表时的create table语句。
而且可以查看存储引擎和字符编码。
若不加\G参数，显示的结果可能非常混乱。（备注：navicat中不支持该功能）

2.修改数据表

alter table <表名> [修改选项];

修改选项的语法格式：
{
add column <列名><类型> |
change column <旧列名><新列名><新列类型> |
alter column <列名> {set default <默认值> | drop default} |
modify column <列名><类型> |
drop column <列名> |
rename to <新表名>
}

①添加字段：
一个完整的字段包括字段名、数据类型、完整性约束。

alter table <表名> add [column] <新字段名> <数据类型> [约束条件] [first|after 已存在的字段名];

a.新字段名：需要添加的字段的名称。
b.first：可选参数，将新添加的字段设置为表的第一个字段。
c.after：可选参数，将新添加的字段添加到指定的“已存在的字段名”的后面。
d.若没有first和after这两个参数，则默认添加到数据表的最后列。

②修改字段数据类型：
把字段的数据类型转换成另一种数据类型。

alter table <表名> modify [column] <字段名> <数据类型>;

a.表名：要修改数据类型的字段所在表的名称。
b.字段名：需要修改的字段。
c.数据类型：修改后字段的新数据类型。

③删除字段：
将数据表中的某个字段从表中移除。

alter table <表名> drop [column] <字段名>;

字段名：需要从表中删除的字段的名称。

④修改字段名称：

alter table <表名> change [column] <旧字段名> <新字段名> <新数据类型>;

a.旧字段名：修改前的字段名。
b.新字段名：修改后的字段名。
c.新数据类型：修改后的数据类型，若不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。
d.备注：由于不同类型的数据在机器中的存储方式及长度并不相同，修改数据类型可能会影响数据表中已有的数据记录，因此，当数据表中已经有数据时，不要轻易修改数据类型。

⑤修改表名：

alter table <旧表名> rename [to] <新表名>;

修改表名并不修改表的结构。

3.删除数据表

删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。

drop table [if exists] 表名1 [,表名2,表名3,...]

a.可同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。
b.表被删除时，用户在该表上的权限不会自动删除。

4.数据类型简介

（1）数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。
（2）若使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。
（3）在创建表时必须为每个列设置正确的数据类型和长度。
（4）五种类型：
①数值类型：
a.整数类型：
TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。
b.浮点数类型：
FLOAT、DOUBLE。
c.定点数类型：
DECIMAL。

②日期/时间类型：
YEAR、TIME、DATE、DATETIME、TIMESTAMP。

③字符串类型：
CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET等。

④二进制类型：
BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。

5.整数类型

a.其他整型数据类型也可以在定义表结构时指定所需的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值。
b.不同的整数类型有不同的取值范围，并且需要不同的存储空间，因此应根据实际需要选择最合适的类型，这样有利于提高查询的效率和节省存储空间。

6.小数类型

a.浮点类型和定点类型都可以用(M,D)来表示，其中M为精度，表示总共的位数；D为标度，表示小数的位数。
b.浮点数类型的取值范围为M(1 ~ 255)和D(1 ~ 30且不能大于M-2），分别表示显示宽度和小数位数。M和D在FLOAT和DOUBLE中是可选的，FLOAT和DOUBLE类型将被保存为硬件所支持的最大精度。DECIMAL的默认D为0、M为10。

a.DECIMAL类型不同于FLOAT和DOUBLE。DOUBLE实际上是以字符串的形式存放的，DECIMAL可能的最大取值范围与DOUBLE相同，但是有效的取值范围由M和D决定。若改变M而固定D，则取值范围随M的变大而变大。
b.FLOAT类型的取值范围：
* 有符号：-3.402823466E+38～-1.175494351E-38
* 无符号：0 和 -1.175494351E-38～-3.402823466E+38
c.DOUBLE类型的取值范围：
* 有符号：-1.7976931348623157E+308～-2.2250738585072014E-308
* 无符号：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308
d.无论定点还是浮点类型，若用户指定的精度超出精度范围，则会四舍五入进行处理。
e.浮点数相对于定点数的优点是：在长度一定的情况下，浮点数能表示更大的范围；缺点是：会引起精度问题。
f.在 MySQL 中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据），使用 DECIMAL 的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

7.日期和时间类型

每一个类型都有合法的取值范围，当指定确定不合法的值时，系统将“零”值插入数据库中。

YEAR类型：
a.单字节类型，用于表示年，存储时只需1个字节。
b.可用各种格式指定YEAR：

• 以4位字符串或者4位数字格式表示的YEAR，范围为’1901’~‘2155’。输入格式为’YYYY’或者YYYY。
• 以2位字符串格式表示的YEAR，范围为’00’~‘99’。‘00’～’69’ 和 ‘70’～’99’ 范围的值分别被转换为 2000～2069 和 1970～1999 范围的 YEAR 值。‘0’ 与 ‘00’ 的作用相同。插入超过取值范围的值将被转换为 2000。
• 以 2 位数字表示的 YEAR，范围为 1～99。1～99 和 70～99 范围的值分别被转换为 2001～2069 和 1970～1999 范围的 YEAR 值。注意，在这里 0 值将被转换为 0000，而不是 2000。

c.备注：两位整数范围与两位字符串范围稍有不同。例如，插入 3000 年，读者可能会使用数字格式的 0 表示 YEAR，实际上，插入数据库的值为 0000，而不是所希望的 3000。只有使用字符串格式的 ‘0’ 或 ‘00’，才可以被正确解释为 3000，非法 YEAR值将被转换为 0000。

TIME类型：
a.用于只需时间信息的值，在存储时需要3个字节。
b.HH：小时；MM：分钟；SS：秒。
c.取值范围： -838：59：59～838：59：59。
d.可用各种格式指定TIME值：

• ‘D HH：MM：SS’ 格式的字符串。还可以使用这些“非严格”的语法：‘HH：MM：SS’、‘HH：MM’、‘D HH’ 或 ‘SS’。这里的 D 表示日，可以取 0～34 之间的值。在插入数据库时，D 被转换为小时保存，格式为 “D*24+HH”。
• ‘HHMMSS’ 格式、没有间隔符的字符串或者 HHMMSS 格式的数值，假定是有意义的时间。例如，‘101112’ 被理解为’10：11：12’，但是 ‘106112’ 是不合法的（它有一个没有意义的分钟部分），在存储时将变为 00：00：00。

e.为 TIME 列分配简写值时应注意：如果没有冒号，MySQL 解释值时，假定最右边的两位表示秒。（MySQL 解释 TIME 值为过去的时间而不是当前的时间）。例如，读者可能认为 ‘1112’ 和 1112 表示 11：12：00（即 11 点过 12 分钟），但MySQL 将它们解释为 00：11：12（即 11 分 12 秒）。同样 ‘12’ 和 12 被解释为00：00：12。相反，TIME 值中如果使用冒号则肯定被看作当天的时间，也就是说，‘11：12’ 表示 11：12：00，而不是 00：11：12。

DATE类型：
a.英语仅需要日期值时，没有时间部分，在存储时需要3个字节。
b.YYYY：年；MM：月；DD：日。
c.给DATE类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATE的日期格式即可：

• 以 ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符中格式表示的日期，取值范围为 ‘1000-01-01’～’9999-12-3’。例如，输入 ‘2015-12-31’ 或者 ‘20151231’，插入数据库的日期为2015-12-31。
• 以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期，在这里YY表示两位的年值。MySQL 解释两位年值的规则：‘00～69’ 范围的年值转换为 ‘2000_{2069’，'70}99’ 范围的年值转换为 ‘1970～1999’。例如，输入 ‘15-12-31’，插入数据库的日期为 2015-12-31；输入 ‘991231’，插入数据库的日期为 1999-12-31。
• 以 YYMMDD 数字格式表示的日期，与前面相似，00~69 范围的年值转换为 2000～2069，80～99 范围的年值转换为 1980～1999。例如，输入 151231，插入数据库的日期为 2015-12-31，输入 991231，插入数据库的日期为 1999-12-31。
• 使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。

d.MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，‘98-11-31’、‘98.11.31’、‘98/11/31’和’98@11@31’ 是等价的，这些值也可以正确地插入数据库。

DATETIME类型：
a.用于需要同时包含日期和时间信息的值，在存储时需要8个字节。
b.YYYY：年；MM：月；DD：日；HH：小时；MM：分钟；SS：秒。
c.可以使用字符串类型或者数字类型的数据插入，只要符合DATETIME的日期格式即可：

• 以 ‘YYYY-MM-DD HH：MM：SS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的日期，取值范围为 ‘1000-01-01 00：00：00’～’9999-12-3 23：59：59’。例如，输入 ‘2014-12-31 05：05：05’ 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。
• 以 ‘YY-MM-DD HH：MM：SS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期，在这里 YY 表示两位的年值。与前面相同，‘00～79’ 范围的年值转换为 ‘2000～2079’，‘80～99’ 范围的年值转换为 ‘1980～1999’。例如，输入 ‘14-12-31 05：05：05’，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。
• 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，输入 20141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 140505050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

d.MySQL 允许“不严格”语法：任何标点符号都可用作日期部分或时间部分之间的间隔符。例如，‘98-12-31 11：30：45’、‘98.12.31 11+30+35’、‘98/12/31 113045’ 和 ‘98@12@31 11³⁰45’ 是等价的，这些值都可以正确地插入数据库。

TIMESTAMP类型：
a.显示格式与DATETIME相同，显示宽度固定在19个字符，存储需4个字节。
b.TIMESTAMP列的取值范围小于DATETIME的取值范围，为’1970-01-01 00：00：01’UTC～’2038-01-19 03：14：07’UTC。在插入数据时，要保证在合法的取值范围内。
c.协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonné）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。
d.TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：

• DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；
• 而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

e.如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 ‘00：00：00’，因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

8.字符串类型

括号中的M表示可以为其指定长度。

a. VARCHAR 和 TEXT 类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸。
b.例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”，L 是 4，而存储要求 5 个字节。

CHAR和VARCHAR：
a.CHAR(M)为固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定长度。M表示列的长度，范围是0~255个字符。
b.VARCHAR(M)为长度可变的字符串，M表示最大列的长度，M的范围是0~65535。VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加1。

TEXT类型：
a.保存非二进制字符串，如文章内容、评论等。
b.当保存或查询TEXT列的值时，不删除尾部空格。
c.TEXT 类型分为 4 种：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。

• TINYTEXT 表示长度为 255（28-1）字符的 TEXT 列。
• TEXT 表示长度为 65535（216-1）字符的 TEXT 列。
• MEDIUMTEXT 表示长度为 16777215（224-1）字符的 TEXT 列。
• LONGTEXT 表示长度为 4294967295 或 4GB（232-1）字符的 TEXT 列。

ENUM类型：
a.是一个字符串对象，值为表创建时列规定中枚举的一列值。
<字段名> ENUM( '值1', '值1', …, '值n' ) 字段名指将要定义的字段，值 n 指枚举列表中第 n 个值。
b.ENUM 类型的字段在取值时，能在指定的枚举列表中获取，而且一次只能取一个。如果创建的成员中有空格，尾部的空格将自动被删除。
c.ENUM 值在内部用整数表示，每个枚举值均有一个索引值；列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号，枚举最多可以有 65535 个元素。
d.ENUM 列总有一个默认值。如果将 ENUM 列声明为 NULL，NULL 值则为该列的一个有效值，并且默认值为 NULL。如果 ENUM 列被声明为 NOT NULL，其默认值为允许的值列表的第 1 个元素。

SET类型：
a.SET 是一个字符串的对象，可以有零或多个值，SET 列最多可以有 64 个成员，值为表创建时规定的一列值。指定包括多个 SET 成员的 SET 列值时，各成员之间用逗号 ,隔开，语法格式如下：
SET( '值1', '值2', …, '值n' ) b.与 ENUM 类型相同，SET 值在内部用整数表示，列表中每个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动删除。
c.但与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。
d.如果插入 SET 字段中的列值有重复，则 MySQL 自动删除重复的值；插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，给出警告。

9.二进制类型

二进制字符串类型有时也直接被称为“二进制类型”。

BIT类型：
a.位字段类型。M 表示每个值的位数，范围为 1～64。如果 M 被省略，默认值为 1。如果为 BIT(M) 列分配的值的长度小于 M 位，在值的左边用 0 填充。例如，为 BIT(6) 列分配一个值 b’101’，其效果与分配 b’000101’ 相同。
b.BIT 数据类型用来保存位字段值，例如以二进制的形式保存数据 13，13 的二进制形式为 1101，在这里需要位数至少为 4 位的 BIT 类型，即可以定义列类型为 BIT(4)。大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。
c.默认情况下，MySQL 不可以插入超出该列允许范围的值，因而插入数据时要确保插入的值在指定的范围内。

BINARY和VARBINARY类型：
a.BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字节字符串。使用的语法格式如下：
列名称 BINARY(M) 或者 VARBINARY(M) b.BINARY 类型的长度是固定的，指定长度后，不足最大长度的，将在它们右边填充 “\0” 补齐，以达到指定长度。例如，指定列数据类型为 BINARY(3)，当插入 a 时，存储的内容实际为 “\a0\0”，当插入 ab 时，实际存储的内容为“ab\0”，无论存储的内容是否达到指定的长度，存储空间均为指定的值 M。
c.VARBINARY 类型的长度是可变的，指定好长度之后，长度可以在 0 到最大值之间。例如，指定列数据类型为 VARBINARY(20)，如果插入的值长度只有 10，则实际存储空间为 10 加 1，实际占用的空间为字符串的实际长度加 1。

BLOB类型：
a.BLOB 是一个二进制的对象，用来存储可变数量的数据。BLOB 类型分为 4 种：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，它们可容纳值的最大长度不同，如下表所示。

b.BLOB 列存储的是二进制字符串（字节字符串），TEXT 列存储的是非进制字符串（字符字符串）。BLOB 列是字符集，并且排序和比较基于列值字节的数值；TEXT 列有一个字符集，并且根据字符集对值进行排序和比较。