MySQL 数据表

---

前言

数据表，是一个临时保存数据的网格虚拟表(表示内存中数据的一个表)。本博客对数据表的创建、查看、修改和删除进行讲解。

---

一、创建数据表

1.创建简单数据表

首先，创建数据表需要以下信息：表名，表字段名和定义每个表字段，建表语句如下：

CREATE TABLE [IF NOT EXISTS] table_name(
	column_list
)engine=table_type;

选项	解释
[IF NOT EXISTS]	可选部分，创表前检查正在创建的表是否已存在于数据库中
column_list	指定表的列，列用逗号“,"分隔
table_type	指定存储引擎，如果不声明，则MySQL默认使用InnoDB

---

在表中定义列，使用如下语句：

CREATE TABLE
	column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT]

选项	解释
column_name	列的名称，每列具有特定的数据类型和大小，例如VARCHAR(255)
NOT NULL或NULL	表示该列是否接受空值
DEFAULT	指定列的默认值
AUTO_INCREMENT	当将新行插入到表中时，列的值会自动增加。每个表有且只有一个AUTO_INCREMENT

2.为数据表设置主键

如果将表的特定列设置为主键，则使用以下语法：

PRIMARY KEY(col1,col2,......)

下面演示一个例子：

CREATE TABLE IF NOT EXISTS tasks(
	task_id INT(11) NOT NULL AUTO_INCREMENT,
	subject VARCHAR(45) DEFAULT NULL,
	start_date DATE DEFAULT NULL,
	end_date DATE DEFAULT NULL,
	description VARCHAR(200) DEFAULT NULL,
	PRIMARY KEY(task_id)
)ENGINE = InnoDB;

3.为数据表设置外键

外键关系的两个表的列必须是数据类型相似，即可以相互转换类型的列，创建外键的语法如下：

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name](index_col_name,...)
REFERENCES tbl_name(index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]


reference_option:
RESTRICT|CASCADE|SET NULL|NO ACTION 

下面给出一个实例：

CREATE TABLE calss(
	cla_id INT(6) AUTO_INCREMENT PRIMARY KEY,
	cla_name VARCHAR(30) NOT NULL UNIQUE
);

CREATE TABLE students(
	stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,
	stu_name VARCHAR(30) NOT NULL,
	stu_score FLOAT(5,2) DEFAULT 0.0,
	cla_id INT(10),
	CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFERENCES class(cla_id)#添加外键约束
)

被引用的数据表为主键表，引用其他表的数据表为外键表。class为主键表，students为外键表

选项	解释
CASCADE	从主键表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行
SET NULL	从主键表中删除或更新对应的行，同时将外键表中的外键列设为空。外键列只有没有被设为NOT NULL时才有效
NO ACTION	拒绝删除或者更新主键表
RESTRICT	拒绝删除或者更新主键表

外键约束使用最多的两种情况：

• 主键表更新时外键表也更新，主键表删除时如果外键表有匹配的项，删除失败
• 主键表更新时外键表也更新，主键表删除时外键表匹配的项也删除

二、查看数据表

1.查看某数据库中所有的表

使用show tables命令，语句如下：

use database_name;#使用database_name数据库
show tables;

#例子
use bookmanage;
show tables;

#查看某数据表的结构
show create table table_name;

数据表中的字段有很多，如下是字段说明

字段名	含义
Table_catalog	数据表登记目录
Table_schema	数据表所属的数据库名
Table_name	表名称
Table_type	表类型
Engine	使用的数据库引擎
Version	版本，默认值10
Row_format	行格式
Table_rows	表里所存多少行数据
Avg_row_length	平均行长度
Data_length	数据长度
Max_data_length	最大数据长度
Index_length	索引长度
Data_free	空间碎片
Auto_increment	做自增主键的自动增量当前值
Create_time	表的创建时间
Update_time	表的更新时间
Check_time	表的检查时间
Table_collation	表的字符校验编码集
Checksum	校验和
Create_options	创建选项
Table_comment	表的注释、备注

2.查看表中的详细信息

DESC命令可以返回表中所有字段信息，包括每一个字段的字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息

DESC table_name;

---

三、修改数据表

1.列操作

1. 添加列

alter table 表名 add 列名 类型

# 例子,给books表增加一列words
alter table books add words int;

2. 删除列

alter table 表名 drop column 列名

#例子，删除books表中的words列
alter table books drop column words;

3. 修改列

alter table 表名 modify column 列名 类型;

#例子，修改books表中的bookName字段的长度为varchar(20)
alter table books modify column bookName varchar(20)

2.主键操作

1. 添加主键

alter table 表名 add primary key(列名)

#例子，为表t1添加主键id
alter table t1 add primary key(id)

2. 删除主键

alter table 表名 drop primary key;
alter table 表名 modify 列名 类型,drop primary key;

#删除表t1的主键
alter table t1 drop primary key;
#删除表t1的主键id列
alter table t1 modify in int,drop primary key;

如果要删除的主键被其他表引用做外键，是不能删除的

3.外键操作

1. 添加外键

alter table 从表 add constraint 外键名称 foreign key 外键表(外键字段) references 主键表(主键字段)

#Readers表和BorrowRules表之间创建外键约束
alter table Readers add constraint FKReaderRole foreign key(ReaderRole) reference BorrowRules(ReaderRole) on delete restrict on update restrict;

2. 删除外键

alter table 表名 drop foreign key 外键名称;

#删除外键FKReaderRole
alter table students drop foreign ket FKReaderRole;

4.默认值操作

1. 修改默认值

alter table 表名 alter 字段名 set default 默认值;

#修改BorrowRules表中的AllowBorrowDays字段默认值为30
alter table BorrowRules alter AllowBorrowDays set default 30;

2. 删除默认值

alter table 表名 alter 字段名 drop default;

#删除BorrowRules表中的AllowBorrowDays字段默认值
alter table BorrowRules alter AllowBorrowDays drop defaultl;

---

四、删除数据表

drop table table_name;

#删除表merlin
drop table merlin;

MySQL删除表的几种情况：

操作	含义
trancate table table_name	删除表全部数据，保留表结构，立刻释放磁盘空间，不管是InnoDB还是MyISAM
delete from table_name	删除表全部数据，表结构不变，对于MyISAM会立刻释放磁盘空间，InnoDB不会释放磁盘空间
delete from table_name where XXX	带条件的删除，表结构不变，不管是MyISAM还是InnoDB都不会释放磁盘空间
drop table table_name	删除表全部数据和表结构，立刻释放磁盘空间，不管是InnoDB还是My’ISAM