MySQL数据库入门知识点整理

数据库基本操作

1、创建数据库语句

create Database database_name

数据库名称有以下几点要求

• 不能与已存在的数据库名称相同；
• 由字母、数字、下划线、@、$和#符号组成；
• 不能以数字及$符号开头；
• 标识符不能使用MySQL的保留字；
• 不能包含空格和特殊字符；
• 长度不能超过128位；

2、查看数据库

SHOW DATABASE

此命令会显示MySQL中所有的数据库；

3、选择数据库

USE database_name

4、删除数据库

DROP DATABASE database_name

MySQL数据库存储引擎

存储引擎指定了表的类型，即如何存储和索引数据、是否支持事务等，同时存储引擎也决定了表在计算机中的存储方式。

SHOW ENGINES

此命令可以查看MySQL所支持的存储引擎；

MySQL5.5支持9种存储引擎：FEDERATED、MRG_MYISAM、MyISAM、BLACKHOLE、CSV、MEMORY、ARCHIVE和PERFORMANCE_SCHEMA

MySQL数据库数据类型

MySQL数据库管理系统提供了整数类型、浮点数类型、定点数类型和位类型、日期和时间类型、字符串类型。

整数类型

MySQL支持的整数类型有TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER)、BIGINT；所占字节从左到右依次为1bit、2bit、3bit、4bit、8bit；

浮点数类型、定点数类型和位类型

MySQl数据库管理系统除了支持标准SQL中所有的浮点数类型、定点数类型外，还进行了相应的扩展，扩展后增加了位类型。

浮点数类型：FLOAT、DOUBLE;所占字节依次为4bit、8bit；

在具体使用MySQL数据库时，如果需要存储小数数据，则可以选择FLOAT类型和DOUBLE类型，具体选择两种类型中的哪一个，则需要判断存储小数数据需要精确到的小数位数，当需要精确到小数点后10位以上，就需要选择DOUBLE类型；

定点数类型：DEC和DECIMAL；该数据类型取值范围与DOUBLE类型相同；

在具体使用MySQL数据库时，如果需要存储小数数据，除了可以选择FLOAT和DOUBLE类型外，还可以选择DEC和DECIMAL类型，当要求小数数据精确度非常高时，则可以选择DEC和DECIMAL类型；

FLOAT、DOUBLE数据类型存储数据时存储的是近似值，而DECIMAL存储的是字符串，因此提供了更高的精确度，在需要表示金额等货币类型时优先选择DECIMAL数据类型。

位类型：BIT；

日期和时间类型

MySQL数据库管理系统支持的时间和日期类型：DATE、DATETIME、TIMESTAMP、TIME和YEAR;

在具体应用中，各种日期和时间类型应用场合如下：

• 如果要表示年月日，一般会使用DATE类型；
• 如果要表示年月日时分秒，一般会使用DATETIME类型；
• 如果需要经常插入或更新日期为当前系统时间，一般会使用TIMESTAMP类型；
• 如果要表示时分秒，一般会使用TIME类型；
• 如果要表示年份，一般会使用YEAR类型；

字符串类型

MySQL数据库管理系统支持四种系列的字符串类型的数据，分别是CHAR系列、TEXT系列、BINARY系列及BLOB系列。

CHAR系列有两种：CHAR及VARCHAR。CAHR最大存储长度为255字节，VARCHAR最大存储长度为65535字节。

在具体应用中，如果需要存储少量字符串，则可以选择CHAR和VARCHAR，至于选择两者中的哪一个，则需要判断所存储字符串长度是否经常变化，如果经常发生变化，则可以选择VARCHAR类型，否则选择CHAR类型；

TEXT系列有四种：TINYTEXT、TEXT、MEDIUMTEXT及LONGTEXT。

在具体应用中，如果需要存储大量字符串（存储文章内容的纯文本），则可以选择TEXT系列字符串类型。至于选择这些类型的哪一个，则需要判断所存储字符串长度，根据存储字符串长度来决定是选择允许长度最小的TINYTEXT字符串类型，还是选择允许长度最大的LONGTEXT字符串类型。

BINARY系列有两种：BINARY及VARBINARY。

与CHAR系列字符串类型相似，不同的是BINARY系列可以存储二进制数据（例如图片、音乐或者视频文件）而CHAR系列只能存储字符数据。

在具体应用中，如果需要存储少量二进制数据，则可以选择BINARY系列字符串类型，至于是BINARY系列中的选择哪一个，则需要判断所存储二进制数据长度是否经常变化，如果经常发生变化，则选择VARBINARY类型，否则选择BINARY类型。

BLOB系列有四种：TINYBLOB、BLOB、MEDIUMBLOB及LONGBLOB。

与TEXT系列字符串类型相似，不同的是，BLOB系列可以存储二进制数据（例如图片、音乐或者视频文件），而TEXT系列只能存储字符数据。

在具体应用中，如果需要存储大量的二进制数据（存储电影等视频文件），则可以选择BLOB系列字符串型。至于选择这些类型中的哪一个，则需要判断所存储二进制数据的长度，根据存储二进制数据的长度来决定是选择允许长度最小的TINYBLOB字符串类型，还是选择允许长度最大的LONGBLOB字符串类型。

表的操作

表是包含数据库中所有数据的数据库对象。表中的数据库对象包含列【Columns】、索引[Indexes]和触发器【Triggers】。

• 列【Colnmns】：也称属性列，在具体创建表时，必须指定列的名字和数据类型。
• 索引【Indexes】：是指根据指定的数据库表列建立起来的顺序，提供了快速访问数据的途径且可监督表的数据，使其索引所指向的列中的数据不重复。
• 触发器【Triggers】：是指用户定义的事务命令的集合，当对一个表中的数据进行插入、更新或删除时这组命令就会自动执行，可以用来确保数据的完整性和安全性。

创建表

语法形式

CREATE TABLE table_name(属性名 数据类型,属性名 数据类型, ...)

表明紧跟在关键字CREATE TABLE之后，表的具体内容定义在圆括号之中，各列之间用逗号（英文）隔开。表名的命名规则与数据库命名规则一样。

查看表

DESCRIBE table_name

SHOW CREATE TABLE table_name

删除表

DROP TABLE table_name

修改表名

ALTER TABLE old_table_name RENAME [TO] new_table_name

增加字段

在表的最后一个位置添加字段

ALTER TABLE table_name ADD 属性名 数据类型

在表的第一个位置增加字段

ALTER TABLE table_name ADD 属性名 数据类型 FIRST

在表的指定字段之后增加字段

ALTER TABLE table_name ADD 属性名 数据类型 AFTER 属性名

删除字段

ALTER TABLE table_name DROP 属性

修改字段

修改字段的数据类型

ALTER TABLE table_name MODIFY 要修改的属性名 修改之后的数据类型

修改字段的名字

ALTER TABLE table_name CHANGE 旧属性名 新属性名 旧数据类型 

同时修改字段的名字和数据类型

ALTER TABLE table_name CHANGE 旧属性名 新属性名 新数据类型

修改字段的顺序

ALTER TABLE table_name MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2

操作表的约束

MySQL支持以下完整性约束

• NOT NULL: 约束字段不能为空；
• DEFAULT: 设置字段的默认值；
• UNQUE KEY【UK】：约束字段的值时唯一；
• PRIMARY KEY【PK】：约束字段为表的主键，可以作为该表的记录的唯一标识；
• AUTO_INCREMENT：约束字段的值为自动增加；
• FOREIGM KEY【FK】：约束字段为表的外键；

设置非空约束

当数据库表中的某个字段上的内容不希望设置为NULL时，则可以使用NK约束进行设置。即NK约束在创建数据库表时为某些字段加上“NOT NULL”约束条件，保证所有记录中该字段都有值。如果用户插入的记录中，该字段为空值，则数据库管理系统会报错。

语法形式如下

CREATE TABLE table_name ( 属性名 数据类型 NOT NULL, ...)

设置字段的默认值

当为数据库表中插入一条新纪录时，如果没有为某个字段赋值，那么数据库系统自动给为这个字段插入默认值。

语法形式如下

CREATE TABLE table_name ( 属性名 数据类型 DEFAULT 默认值, ...)

设置唯一约束

当数据库表中的某个字段上的内容不允许重复时，则可以使用UK约束进行设置。即UK约束在创建数据库表时为某些字段加上“UNIQUE”约束条件，保证所有记录中该字段上的值不重复。

语法形式如下：

CREATE TABLE table_name ( 属性名 数据类型 UNIQUE, ...)

设置主键约束

当想用数据库表中的某个字段来唯一标识所有记录时，则可以使用PK约束进行设置。即PK约束在创建数据库表时为某些字段加上“PRIMARY KEY”约束条件，则该字段可以唯一地标示所有记录。

在数据库表中之所以设置主键，是为了数据库管理系统快速地查找到表中的记录。在具体设置主键约束时，必须要满足主键字段的值是唯一、非空的。由于主键可以是单一字段，也可以是多个字段，因此分为单字段主键和多字段主键。

单字段主键

语法形式如下

CREATE TABLE table_name ( 属性名 数据类型 PRIMARY KEY, ...)

多字段主键

语法形式如下

CREATE TABLE table_name ( 属性名 数据类型, ... CONSTRAINT 约束名 PRIMARY KEY(属性名，...))

设置字段值自动增加

AUTO_INCREMENT是MySQL唯一扩展的完整性约束，当为数据库表中插入新纪录时，字段上的值会自动生成唯一的ID。在具体设置AUTO_INCREMENT约束时，一个数据库表中只能由一个字段使用该越是，该字段的数据类型必须是整数类型。由于设置AUTO_INCREMENT约束后的字段会生成唯一的ID，所以该字段也经常会设置成PK主键。

语法形式如下

CREATE TABLE table_name ( 属性名 数据类型 AUTO_INCREMENT, ...)

设置外键约束

外键约束保证多个表（通常为两个表）之间的参照完整性，即构建于两个表的两个字段之间的参照关系。

设置外键约束的两个表之间会具有父子关系，即子表中国某个字段的取值范围由父表所决定。

在具体设置FK约束时，设置FK约束的字段必须依赖于数据库中已经存在的父表的主键，同时外键可以为NULL。

语法形式如下

CREATE TABLE table_name ( 
属性名 数据类型, 
... 
CONSTRAINT 外键约束名 FOREIGN KEY(属性名【子表中外键的字段名】)
REFERENCES 表名(属性名【父表中设置主键约束的字段名】)
)