一、数据表简介
在MySQL中,存储数据的结构就是Table这样的二维数据表,也即是所谓的关系模型,每张表中可以存储一条或多条字段,如下所示:
mysql> select * from student;
+---------+------+------+
| name | age | sex |
+---------+------+------+
| Tom | 16 | M |
| Charlie | 18 | M |
| Emma | 15 | F |
+---------+------+------+
3 rows in set (0.00 sec)
每张表中的第一行是这张表的表头,表示这张表可以存储哪些数据。之下每一行就是一条数据,而每一列就代表一个字段。
使用DESCRIBE或者DESC命令可以查看表的结构和每个字段的数据类型。
mysql> DESCRIBE student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| age | tinyint(255) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
二、约束条件
所谓约束条件,指的是对于字段,除了数据类型和数据长度以外其他的规定,例如非空、自增长等。
NOT NULL(非空)
如果没有指定NOT NULL这个约束条件的话,那么插入一条数据的时候,这个字段是可以不插入值的,这个时候,MySQL会自动将这个字段的值设置为NULL。
而如果设置了NOT NULL约束条件,那么在插入数据的时候,这个字段必须有值,否则会报错。
name VARCHAR(20) NOT NULL;
AUTO_INCREMENT(自增长)
整数类型或浮点数类型,可以设置这个约束条件,每张表中只能设置一个AUTO_INCREMENT字段,且不能设置默认值DEFAULT。AUTO_INCREMENT字段必须为一个KEY。
当你在插入数据的时候,如果给这个字段插入NULL或者0,甚至不插入值时,这个字段会自动找出这个字段中最大的值,并将这个值+1并填充到当前这条数据中。
id INT(11) AUTO_INCREMENT,
DEFAULT(默认值)
指定默认值,如果插入数据时,这个字段没有插入值,那么将会使用默认值来填充此字段。
name VARCHAR(20) DEFAULT 'nobody',
UNIQUE(唯一)
当我们希望某一个字段的值都唯一的情况下,我们可以给这个字段指定UNIQUE约束条件。如果在插入一条新数据时,给一个UNIQUE字段插入一个已经存在的值,那么将会抛出一个异常。
需要注意的是,UNIQUE不会把NULL视为重复的值,也就是说,UNIQUE字段中可以包含多个NULL。
id INT(11) UNIQUE,
如果需要在建表之后再设定某个字段为UNIQUE,可以用这种方式:
ALTER TABLE tb_name ADD UNIQUE KEY(number);
PRIMARY KEY(主键)
主键通常是用来作为一条数据的唯一标识,所以主键字段的值必须是唯一的,且这个字段必须设置为NOT NULL,每一张表中也只能设置一个主键。
当没有设置主键的时候,表中第一个出现的NOT NULL且UNIQUE字段,将会被视为主键。
id INT(11) PRIMARY KEY,
当建表之后需要设置主键时:
ALTER TABLE tb_name ADD PRIMARY KEY (id);
主键还可以被撤销:
ALTER TABLE tb_name DROP PRIMARY KEY;
FOREIGN KEY(外键)
外键可以将表中的某一个字段与另一张表中的一个字段联系起来,从而为两张表建立联系。
CONSTRAINT foreign_name FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
详细使用请查看表关系章节。
三、建表语句(CREATE TABLE)
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tb_name
(create_definition,...)
[table_options]
[partition_options]
关键字:
TEMPORARY - 可以使用此关键字创建临时表,临时表只在当前会话中可见,并且会在会话关闭时自动删除。
IF NOT EXISTS - 当表中不存在此表时,才执行CREATE TABLE语句,以免创建重名表引发异常。
create_definition - 在tb_name字段后方括号中的,是表中定义字段的语句,定义多条字段字段时用逗号分隔。
table_options - 用来优化表的选项。在大多数时候不需要指定。可以在这里指定表的引擎(engine),如果没有指定的话则默认使用InnoDB引擎。
partition_optins - 可以用于创建表的分区。
定义字段
col_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT],
除了需要在这里指定字段的名字、数据类型和约束条件之外,还有一些选项可以在此指定:
COMMENT(注释)
使用COMMENT可以为字段写入注释,最多1024个字符。如果要显示注释的话需要使用SHOW CREATE TABLE或者SHOW FULL COLUMNS语句。
UNSIGNED(无符号)
数值类型可以使用UNSIGNED来指定数据只表示正数,这样的话能使字段表示的最大范围翻倍。
ZEROFILL(用0填充)
当数值类型插入的数据长度比设定的最大长度小时,设定了ZEROFILL选项的字段将自动在数据的左端填充0,将数据补充到设定的最大长度。
四、修改表结构(ALTER TABLE)
可以使用ALTER TABLE语句来更改现有表的结构。 ALTER TABLE语句可用来添加列,删除列,更改列的数据类型,添加主键,重命名表等等。 以下是ALTER TABLE语句语法:
ALTER TABLE tb_name
[alter_specification [, alter_specification] ...]
[partition_options]
需要注意的是多条修改语句间需要使用,分割。
以下是一些ALTER TABLE的使用例子:
重命名表
ALTER TABLE tb_name RENAME new_name;
添加字段
ALTER TABLE tb_name ADD col_name TIMESTAMP;
如果需要指定添加字段的位置,那么可以在后面跟上一个AFTER子句,添加到某个字段下方。
ALTER TABLE tb_name ADD col_name TIMESTAMP AFTER col_name2;
使用AFTER能添加到除了第一行之外的所有位置,那么想添加到第一行的话,需要使用FIRST。
ALTER TABLE tb_name ADD col_name TIMESTAMP FIRST;
删除字段
ALTER TABLE tb_name DROP COLUMN col_name;
重命名并修改字段
ALTER TABLE tb_name CHANGE old_col_name new_col_name data_type;
修改字段
ALTER TABLE tb_name MODIFY col_name data_type;