13.DDl数据表管理

1. 表的概念

在数据库中，表是用来存储数据的。MySQL数据库中，一个数据库下可以包含多张表。

表的操作主要包括表的创建、表的修改、表的删除、表的重命名等。

一个表由多个列组成，每个列又有自己的数据类型，因此，我们先来看一下MySQL的数据类型。

2. 数据类型

MySQL包含3大类数据类型，分别是数值类型、日期和时间类型、字符串类型。

我们经常使用的数据类型包括如下5种

数据类型	描述	说明
INT	整型	普通大小的整数类型，INTEGER的同义词。 表数范围：带符号是-2147483648～2147483647，无符号是0～4294967295。
DECIMAL(M,D)	定点数类型	M为精度，表示总长度；D为标度，表示小数点后⾯的长度。 例：DECIMAL(7,4)可存储的数据范围为-999.9999~999.9999。MySQL保存值时进⾏四舍五⼊，如果插⼊999.00009，则结果为999.0001。
CHAR(M)	固定长度字符串型	M为固定且最大存储长度。 例：CHAR(2)所代表的含义为： 1、表示最大的存储长度为2个字符，如果存储超过2个字符，会提示错误。 2、当输入字符长度为1个字符时，实际存储也是占用2个字符。
VARCHAR(M)	可变长度字符串型	M为最大可存储长度。 例：VARCHAR(2)所代表的含义为： 1、表示最大的存储长度为2个字符，如果存储超过2个字符，会提示错误。 2、当输入字符长度为1个字符时，实际存储就占用1个字符。
DATE	日期型	格式为：YYYY-MM-DD 表数范围：1000-01-01到9999-12-3

如下是MySQL数据库中其他数据类型，仅供了解。

数值类型：

数据类型	描述
BIT	位字段类型，取值范围是1~64， 默认为1
TINYINT	很小的整数类型。带符号的范围是-128～127，无符号的范围是0～255
BOOL，BOOLEAN	布尔类型，是TINYINT(1)的同义词。zero值被视为假。非zero值被视为真
SMALLINT	小的整数类型，带符号的范围是-32768～32767。无符号的范围是0～65535
MEDIUMINT	中等大小的整数类型。带符号的范围是-8388608～8388607，无符号的范围是0～16777215
INT	普通大小的整数类型。带符号的范围是-2147483648～2147483647，无符号的范围是0～4294967295
INTEGER	与INT的含义相同
BIGINT	大整数类型，带符号的范围是-9223372036854775808～9223372036854775807。无符号的范围是0～18446744073709551615
FLOAT	单精度浮点类型
DOUBLE	双精度浮点类型
DECIMAL	定点数类型
DEC	与DECIMAL的含义相同

日期和时间类型

数据类型	描述
DATE	日期类型，例如'2012-01-01'
DATETIME	日期和时间类型，例如'2012-01-01 12:00:00'
TIMESTAMP	时间戳类型，TIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间
TIME	时间类型
YEAR	两位或四位的年份类型，默认为四位年份类型

字符串类型

数据类型	描述
CHAR(M)	固定长度字符串，M为存储长度
VARCHAR(M)	可变长度的字符串，M为最大存储长度，实际存储长度为输入字符的实际长度
BINARY(M)	BINARY类型类似于CHAR类型，但保存二进制字节字符串而不是非二进制字符串。M为存储长度
VARBINARY(M)	VARBINARY类型类似于VARCHAR类型，但保存二进制字节字符串而不是非二进制字符串。M为存储长度
BLOB	二进制大对象，包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 等4种BLOB类型
TEXT	大文本类型，包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT等4种TEXT类型
ENUM	枚举类型
SET	集合类型

3. 创建表

创建表的语法如下：

CREATE TABLE 表名(

列名1 数据类型 ,

列名2 数据类型 ,

…

)

其中表名及列名不能同关键字相同，且要做到见名知意。

例：创建一个学生表，包含学号，姓名，性别，出生日期4列。

CREATE TABLE student(
  sno int,
  sname varchar(10),
  gender char(1),
  birthday date
)

4. 查看表结构

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

SHOW TABLES;

2、查看表结构

DESC 表名;

3、查看创建表的SQL语句

SHOW CREATE TABLE 表名;
 

5. 复制表

复制一个表的方法有两种，分别是：

1、仅复制表结构，语法如下：

CREATE TABLE 新表名 LIKE 旧表名;

例：复制一个与EMP表结构相同的表EMP_COPY

2、同时复制表结构和数据，语法如下：

CREATE TABLE 新表名 查询语句;

例：复制一个与DEPT表结构相同的表DEPT_COPY，包含复制DEPT表中的数据。

6. 删除表

1、直接删除表，语法如下：

DROP TABLE 表名;

例：删除表DEPT_COPY

2、判断表是否存在并删除表，语法如下：

DROP TABLE IF EXISTS 表名;

例：判断表DEPT_COPY是否存在，如果存在则删除。

该语法即使要删除的表不存在，也不会给出错误提示，通常在备份/恢复数据库时使用。

7. 修改表结构

修改表结构主要用在表已经存在，但根据需求的变化，可能会修改表的结构，这时就需要我会修改表结构的SQL。在使用可视化插件的前提下，SQL语句修改表结构的情况较少，所以如下SQL仅供大家了解。

1、为已经存在的表添加新列。

ALTER TABLE 表名 ADD 列名 类型;

例：为student表添加一个爱好列hobby，类型为VARCHAR(100)

ALTER TABLE student ADD hobby VARCHAR(100);

2、修改已经存在的列的数据类型

ALTER TABLE 表名 MODIFY 列名 新的类型;
例：将student表中的爱好列的数据类型扩充到VARCHAR(200)

ALTER TABLE student MODIFY hobby VARCHAR(200);  

3、修改列名

ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
例：将student表中的hobby列改成interest，类型VARCHAR(100)

ALTER TABLE student CHANGE hobby interest VARCHAR(100);

4、删除列

ALTER TABLE 表名 DROP 列名;

例：删除student表中的列interest

ALTER TABLE student DROP interest;

5、修改表名

RENAME TABLE 表名 TO 新表名;

例：将学生表student改名成student2

RENAME TABLE student TO student2;

6、修改字符集

ALTER TABLE 表名 character set 字符集;

例：将sutden2表的编码修改成gbk

ALTER TABLE student2 CHARACTER SET gbk;

8. 截断表

截断表的作用是把原来的表摧毁，重新创建一个结构和原来一模一样的新表，语法如下：

TRUNCATE TABLE table;

TRUNCATE table student2

TRUNCATE和DELETE区别

1、TRUNCATE是DDL命令，使用ROLLBACK不可以回滚。而DELETE是DML命令，使用ROLLBACK可以回滚。

2、DELETE可以通过指定删除条件实现部分删除，TRUNCATE不能指定条件。