MySQL开发教程：数据类型、数据库连接、基本数据库和表操作详解

上一章mysql教程中我们讨论了：管理MySQL以及在PHP中使用MySQL，在这一章中我们讨论：mysql数据类型、基本数据库和表操作。

一、数据类型

正确定义表中的字段对于数据库的整体优化非常重要，例如如果你只打算使用2个字符，那么不要定义一个10个字符宽的字段。在你在这些字段中存储的对应数据类型数据之后，这些类型的字段(或列)也称为数据类型。

MySQL使用许多不同的数据类型，主要是分为三类：

• 数字类型
• 日期和时间类型
• 字符串类型

下面详细讨论这些数据类型。

1、数字类型

MySQL使用所有标准的ANSI SQL数值数据类型，所以如果你从不同的数据库系统访问MySQL，这些定义对你来说会很熟悉。

下面的列表显示了常见的数字数据类型及其描述：

• 整型int——一个标准大小的整数，可以是有符号的，也可以是无符号的。有符号范围-2147483648到2147483647，无符号范围0到4294967295，也就是你可以指定最多11位数字的宽度。
• tinyint——一个很小的整数，可以是有符号的，也可以是无符号的。有符号范围-128到127，无符号范围0到255，你可以指定最多4位数字的宽度。
• smallint小整数，可以有符号或无符号的小整数，有符号范围-32768到32767，无符号范围0到65535，你可以指定最多5位数字的宽度。
• mediumint一个中等大小的整数，可以是有符号的，也可以是无符号的，有符号范围-8388608到8388607，无符号范围0到16777215，你可以指定最多9位数字的宽度。
• bingint一个有符号或无符号的大整数，有符号范围-9223372036854775808到9223372036854775807，无符号允许范围是0到18446744073709551615，你可以指定最多20位数字的宽度。
• 浮点数float(M,D)——一个不能无符号的浮点数，你可以定义显示长度(M)和小数位数(D)（可选），默认为10,2，其中2是小数位数，10是数字总数(包括小数)，一个浮点数的十进制精度可以达到24位。
• 双精度浮点数double(M,D)，不能无符，。你可以定义显示长度(M)和小数位数(D)（可选），默认为16,4，其中4是小数位数，双精度精度可以达到53位，REAL是DOUBLE的同义词。
• 十进制decimal(M,D)——一个不能无符号的无包装浮点数。在非压缩小数中，每个十进制对应一个字节,需要定义显示长度(M)和小数位数(D),NUMERIC是DECIMAL的同义词。

2、日期和时间类型

MySQL日期和时间数据类型如下：

• date日期- YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之间，例如1973年12月30日将被存储为1973年12月30日。
• datetime日期时间YYYY-MM-DD HH:MM:SS格式的日期和时间组合，时间范围为1000-01-01 00:00:00和9999-12-31 23:59:59，例如1973年12月30日下午3:30将存储为1973-12-30 15:30:00。
• timestamp时间戳-时间戳从1970年1月1日午夜到2037年某个时候，这看起来像以前的日期时间格式，只是数字之间没有连字符;1973年12月30日下午3:30存储为19731230153000 (YYYYMMDDHHMMSS)。
• time时间-以HH:MM:SS格式存储时间。
• 年year(M) -以2位数或4位数格式存储一年。如果长度指定为2(例如YEAR(2)) YEAR为1970年到2069年(70到69年)，如果长度指定为4，则年份可以是1901到2155，默认长度是4。

3、字符串类型

虽然数字和日期类型很有用，但是你将存储的大多数数据都是字符串格式的，这个列表描述了MySQL中常见的字符串数据类型。

• 字符CHAR(M)—长度在1到255个字符之间的固定长度字符串(例如CHAR(5))，在存储时用空格右填充到指定长度，不需要定义长度，但是默认值是1。
• 变长字符VARCHAR(M)—长度在1到255个字符之间的可变长度字符串。例如VARCHAR (25)，创建VARCHAR字段时必须定义长度。
• 二进制BLOB或文本text—最大长度为65535个字符的字段。blob是“二进制大对象”，用于存储大量二进制数据，如图像或其他类型的文件。定义为文本的字段也包含大量数据。区别在于对存储数据的排序和比较在blob上区分大小写，而在文本字段中不区分大小写，不使用BLOB或文本指定长度。
• TINYBLOB或TINYTEXT——最大长度为255个字符的BLOB或文本列，不使用TINYBLOB或TINYTEXT指定长度。
• 一个最大长度为16777215个字符的BLOB或TEXT列，不要使用MEDIUMBLOB或MEDIUMTEXT指定长度。
• LONGBLOB或LONGTEXT—最大长度为4294967295个字符的BLOB或文本列，不使用LONGBLOB或LONGTEXT指定长度。
• 枚举enum，是表的别称。在定义ENUM时，你要创建一个项目列表，必须从中选择值(或者可以为NULL)。例如如果希望字段包含“A”、“B”或“C”，那么可以将ENUM定义为ENUM(“A”、“B”、“C”)，只有那些值(或NULL)才能填充该字段。

二、数据库连接

你可以在命令提示符处使用MySQL二进制文件建立MySQL数据库。

下面是一个从命令提示符连接到MySQL服务器的简单示例：

[root@host]# mysql -u root -p
Enter password:******

这将为你提供mysql>命令提示符够执行任何SQL命令，下面是上述命令的结果：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.7.23 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

在上面的例子中我们使用root作为用户，但是你也可以使用任何其他用户，任何用户都可以执行该用户允许执行的所有SQL操作。

你可以在MySQL >提示下使用exit命令随时断开与MySQL数据库的连接。

mysql> exit
Bye

三、数据库操作

1、创建数据库

创建或删除MySQL数据库需要特殊的权限，假设你可以访问根用户，那么你可以使用mysql mysqladmin创建任何数据库。

下面是一个创建名为sample的数据库的简单示例：

[root@host]# mysqladmin -u root -p create sample
Enter password:******

另外的一个命令是：

mysql> create database mywebsite;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+-----------------------+
| Database              |
+-----------------------+
| mywebsite             |
+-----------------------+
7 rows in set (0.00 sec)

2、删除数据库

创建或删除MySQL数据库需要特殊的权限，假设你可以访问根用户可以使用mysql mysqladmin创建任何数据库。删除任何数据库时都要小心，因为你将丢失数据库中所有可用的数据，下面是删除数据库的命令：

[root@host]# mysqladmin -u root -p drop sample
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 'sample' database [y/N] y
Database "sample" dropped

3、选择数据库

连接到MySQL服务器后，需要选择要使用的数据库，这是因为MySQL服务器可能有多个数据库可用。

从mysql>提示中选择一个数据库非常简单，可以使用SQL命令选择数据库。

[root@host]# mysql -u root -p
Enter password:******
mysql> use sample;
Database changed
mysql> 

目前你已经选择了sample数据库，所有后续操作都将在sample数据库上执行。注意所有数据库名、表名、表字段名都是大小写敏感的，因而在给出任何SQL命令时都必须使用专有名称。

四、表操作

1、创建表

首先创建表命令需要以下详细信息：

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

下面是创建MySQL表创建的通用SQL语法：

create table table_name (column_name1 column_type1, column_name2 column_type2);

现在我们在sample数据库中创建以下表：

mysql> create table post(
    -> post_id int not null auto_increment,
    -> post_title varchar(100) not null,
    -> post_author varchar(100) not null,
    -> post_date date,
    -> primary key(post_id)
    -> );
Query OK, 0 rows affected (0.29 sec)

这里有几项需要说明：

• 字段属性NOT NULL被使用，因为我们不希望这个字段为空，NULL值MySQL将会产生一个错误。
• 字段属性AUTO_INCREMENT告诉MySQL继续并将下一个可用的数字添加到id字段。
• 关键字PRIMARY KEY用于将列或字段定义为主键，可以使用逗号分隔的多个列来定义主键。

2、删除表

删除现有的MySQL表非常容易，但是在删除任何现有表时都需要非常小心，因为删除表后丢失的数据将无法恢复。

下面是删除MySQL表的通用SQL语法：

drop table table_name