[学会MySql系列] 第一篇：表的基本操作

0.1 数据库基础

数据库是储存数据的一种数据结构一组文件。数据库几乎存在于任何角落，比如像计算机账户管理，像网址储存在镜像（根）服务器上等等。

0.1.1 什么是表

在数据库就像是记录了很多数据和数据映射关系的笔记本，其中每一页中都储存着某种映射关系下的数据，这一页就等同于表（Table）的概念。按照很多教材上面的书面定义，TABLE是数据库的一组文件中的单个文件，是某种特定类型数据的结构化清单。
比如某个店的进货单：

#prod table
+-----------+----------+------------+-------------+
| prod_name | quantity | item_price | supplier_id |
+-----------+----------+------------+-------------+
|   A       |    100   |    100     |       001   | 
|   B       |     50   |    300     |       001   | 
|   C       |     55   |    100     |       002   | 
+-----------+----------+------------+-------------+

这个就是一张简单的表。储存着prod_name, quantity, item_price, supplier_id这几列。

• 注表名在数据库中应是唯一的。

0.1.2 什么是列

在上表中的prod_name等就是表中的列column, 许多列组成了表，列是表中的基本结构。在列中的数据类型是一样的。

0.1.3 什么是行

在表中的数据按照水平可以分为行row，比如

+-----------+----------+------------+-------------+
|   A       |    100   |    100     |       001   | 
+-----------+----------+------------+-------------+

行是数据库中的一个记录。

0.1.4 什么是主键

每一个行必须有的唯一标注，可以通过这个标注能够唯一的定位到一行数据。可以通过指定某一列，也可以通过指定一组列。条件是这一列或者一组列组合的值应该是唯一的，这就是主键PRIMARY KEY。在上例中prod_name就可以唯一的确定每一行，比如说prod_name为A的，就是：

+-----------+----------+------------+-------------+
|   A       |    100   |    100     |       001   | 
+-----------+----------+------------+-------------+

0.2 创建表

在进行操作时都是需要选定数据库，如果没有数据库可以新建数据库

CREATE DATABASE databasename;

在MySQL中创建一张表的语句如下

CREATE TABLE products
(
prod_id       int       NOT NULL AUTO_INCREMENT,
prod_name     char(50)  NOT NULL,
quantity      int       NOT NULL,
...
PRIMARY KEY (prod_id)
)ENGINE = InnoDB;

关键词CREATE TABLE后面跟着要创建的表的表名，括号内是要创建的表的列，格式是列名+列的数据类型+ 是否允许NULL/是否为自增量/是否有默认值，每一列都用逗号结束。

PRIMARY KEY是指定prod_id为主键列。ENGINE=InnoDB指定建立表的引擎（不同引擎支持不同的表的功能，比如支持全文搜索与否等）

• 注：新建表名一定是与现有表名不重复的，不然会出错。

0.3 删除表或重命名表

删除表很简单，使用DROP TABLE语句加表名即可。

DROP TABLE products;

重命名表也很简单，格式如下

RENAME TABLE productsnew TO products;
#RENAME TABLE newtablename TO oldtalbename

0.4 更改表

如果需要增加表中的列，或者删除表中的列需要用到ALTER TABLE语句，比如

ALTER TABLE products
ADD weight int;
#在products表中添加weight列
ALTER TABLE products
DROP COLUMN weight;
#删除表中的weight

• 增加的列一般顺序是作为最后一列添加的。例如

+---------------+----------+------+-----+---------+----------------+
| Field         | Type     | Null | Key | Default | Extra          |
+---------------+----------+------+-----+---------+----------------+
| supplier_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| numbers       | int(11)  | NO   |     | NULL    |                |
| address       | char(50) | NO   |     | NULL    |                |
+---------------+----------+------+-----+---------+----------------+

添加一列supplier_name，语句如下

ALTER TABLE suppliers
ADD supplier_name  char(50)  NOT NULL;

结果如下

+---------------+----------+------+-----+---------+----------------+
| Field         | Type     | Null | Key | Default | Extra          |
+---------------+----------+------+-----+---------+----------------+
| supplier_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| numbers       | int(11)  | NO   |     | NULL    |                |
| address       | char(50) | NO   |     | NULL    |                |
| supplier_name | char(50) | NO   |     | NULL    |                |   #<----------新增加的列
+---------------+----------+------+-----+---------+----------------+

mysql> SELECT * 
FROM suppliers;
+-------------+---------+--------------------+---------------+
| supplier_id | numbers | address            | supplier_name |
+-------------+---------+--------------------+---------------+
|         101 | 6214520 | x road,x district  | A Company     |
|         102 | 8205520 | y road,y district  | B Company     |
+-------------+---------+--------------------+---------------+
2 rows in set (0.00 sec)

0.5 数据类型

在创建table时必须指定列的数据类型，在SQL中的数据类型分 数值类型、日期和时间类型、字符串类型三种。

0.5.1 数值类型

数值类型内容见下表，有TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL 等

类型	内存大小	范围（有符号）	范围（无符号）	说明
tinyint	1	(-27，27-1)	(0，28-1)
smallint	2	(-215，215-1)	(0，216-1)
mediumint	3	(-223，223-1)	(0，224-1)
int	4	(-231，231-1)	(0，232-1)
bigint	8	(-263，263-1)	(0，264-1)
float	4	/	/	单精度
double	8	/	/	双精度
decimal

0.5.2 日期和时间类型

类型	内存大小	范围	格式	说明
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	YYYY-MM-DD HH:MM:SS

0.5.3 字符串类型

字符串类型分char/varchar，tinyblob/mediumblob/blob/longblob，tinytext/mediumtext/text/longtext等。

类型	空间大小	说明
char	0~255字节	定长字符串
varchar	0~65535字节	可变字符串
tinyblob	0~255字节	二进制字符串
tinytext	0~255字节	短文本字符串
blob	0~65535字节	二进制形式的长文本数据
text	0~65535字节	长文本数据
mediumblob	0-16777215字节	二进制中等长文本
mediumtext	0-16777215字节	中等长文本数据
longblob	0-4294967295字节	二进制极大长文本
longtext	0-4294967295字节	极大长文本数据

• 注：char(20)和varchar(20)差别在于前者如果输入不到20字节，就会通过补充空格补到指定长度，而 后者不进行补充。检索性能char(20)比varchar(20)快，varchar(20)由于适应不同长度的输入，储存会小一点。

《MySQL必知必会》