表的操作

了解库操作。

掌握表的创建、查看、删除。

了解表的修改、复制。

库操作

在MySQL中，对于库的操作，主要包含以下4个方面。

• 创建库
• 查看库
• 修改库
• 删除库

创建库

创建表之前，我们一定要先创建用来存储表的“数据库”才行。在 MySQL 中，我们可以使用 create database 语句或 create schema 语句来创建一个库。

CREATE DATABASE database_name
[OPTIONS]
[CHARACTER SET charset_name]
[COLLATE collation_name];

• database_name 是你想要创建的数据库的名称。
• OPTIONS 部分是可选的，用于指定与数据库创建相关的其他选项（在 MySQL 的大多数版本中，这个部分通常被省略或不被广泛使用）。
• CHARACTER SET charset_name 指定了数据库的默认字符集。如果省略此选项，将使用服务器的默认字符集。
• COLLATE collation_name 指定了数据库的默认校对规则。如果省略此选项且指定了字符集，则使用该字符集的默认校对规则。

**示例：**创建一个名为 study 的数据库，指定其字符集为 utf8mb4（这是一个支持表情符号等 4 字节 Unicode 字符的字符集），校对规则为 utf8mb4_general_ci（一个不区分大小写的校对规则）

CREATE DATABASE my_database
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

注意事项

• 在创建数据库之前，确保你有足够的权限。通常，这需要数据库管理员（DBA）的权限。
• 数据库名称在 MySQL 服务器上必须是唯一的。如果尝试创建一个已经存在的数据库，MySQL 将返回一个错误。
• 创建数据库后，你可以使用 USE database_name; 语句来选择并使用这个新创建的数据库。
• 如果不确定服务器的默认字符集和校对规则，可以使用 SHOW VARIABLES LIKE 'character_set_database'; 和 SHOW VARIABLES LIKE 'collation_database'; 命令来查看。然而，这些命令显示的是当前会话的默认值，而不是服务器的全局默认值；对于新创建的数据库，最好还是明确指定字符集和校对规则。

查看库

在MySQL中，我们可以使用show databases语句或show schemas语句来查看当前可用的数据库都有哪些。

show databases;

修改库

在MySQL中，我们可以使用alter database语句或alter schema语句来修改一个库。对于库的修改，主要是修改字符集以及字符集的校对规则。

alter database 库名
default character set = 字符集名
default collate = 校对规则名;

删除库

drop database 库名;

表的操作简介

数据定义语言，主要有以下3种：

语句	说明
create table	创建表
drop table	删除表
alter table	修改表

创建表

创建好数据库之后，我们就可以创建表了。一个数据库中往往会包含多张表。在MySQL中，我们可以使用create table语句来创建一个张表。

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    PRIMARY KEY (column_list),  -- 可选，用于定义主键
    FOREIGN KEY (column_name) REFERENCES another_table(another_column),  -- 可选，用于定义外键
    INDEX (column_name),  -- 可选，用于创建索引
    UNIQUE (column_name),  -- 可选，用于确保某列的值唯一
    CHECK (condition),  -- 可选，用于限制列中的值必须满足的条件（注意：并非所有MySQL版本都支持CHECK约束）
    ...
    -- 还可以包括其他表选项，如ENGINE、CHARSET等
) ENGINE=storage_engine CHARSET=character_set;

• table_name 是你想要创建的表的名称。
• column1, column2, ... 是表中的列，每列都有一个数据类型（如 INT, VARCHAR, DATE 等）和可选的约束条件（如 NOT NULL, DEFAULT 值等）。
• PRIMARY KEY 定义了表的主键，主键的值在表中必须是唯一的，并且不能包含 NULL 值。
• FOREIGN KEY 定义了外键，它用于建立和维护两个表之间的数据完整性。
• INDEX 和 UNIQUE 可以用来提高查询性能或确保数据的唯一性。
• CHECK 约束允许你指定列中的值必须满足的条件，但需要注意的是，并非所有的 MySQL 版本都支持 CHECK 约束。
• ENGINE 指定了表的存储引擎，如 InnoDB 或 MyISAM。
• CHARSET 指定了表的字符集。

**示例：**创建一个 fruits 表，并定义 id 主键以及其他键

CREATE TABLE fruits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    type VARCHAR(50),
    season VARCHAR(20),
    price DECIMAL(10, 2),
    date DATE
);

**示例：**根据以下结构创建表 product

列名	类型	允许****NULL	是否主键	注释
id	int	×	√	商品编号
name	varchar(10)	√	×	商品名称
type	varchar(10)	√	×	商品类型
city	varchar(10)	√	×	来源城市
price	decimal(5, 1)	√	×	出售价格
date	date	√	×	入库时间

CREATE TABLE product (
    id INT NOT NULL AUTO_INCREMENT COMMENT '商品编号',
    name VARCHAR(10) COMMENT '商品名称',
    type VARCHAR(10) COMMENT '商品类型',
    city VARCHAR(10) COMMENT '来源城市',
    price DECIMAL(5, 1) COMMENT '出售价格',
    date DATE COMMENT '入库时间',
    PRIMARY KEY (id)
);

查看表

在MySQL中，如果想要查看一个数据表，有以下3种方式：

• show tables语句。
• show create table语句。
• describe语句。

show table

在MySQL中，我们可以使用show tables语句来查看当前数据库中都有哪些表。

show tables;

show create table

在MySQL中，我们可以使用show create table语句来查看一张表对应的SQL创建语句是什么。

show create table 表名;

describe

在MySQL中，我们可以使用describe语句来查看一张表的结构是怎样的。

describe 表名;

修改表

修改表，指的是修改数据库中已经存在的表的结构。对于表的修改，主要包括两个方面：（1）修改表名；（2）修改字段。

操作	语句
修改表名	alter table…rename to…
修改字段名	alter table…change…
修改字段	alter table…modify…
添加字段	alter table…add…
删除字段	alter table…drop…

修改表名

在MySQL中，我们可以使用alter table…rename to…语句来修改一个表的名字。

alter table 旧表名
rename to 新表名;

修改字段

在 MySQL 中，对于字段的修改，主要包含以下4个方面：

• 添加字段
• 删除字段
• 修改字段名
• 修改类型

添加字段

在MySQL中，我们可以使用alter table…add…语句来添加一个新字段。

ALTER TABLE table_name
ADD COLUMN column_name column_definition [FIRST | AFTER existing_column];

• table_name 是你要修改的表的名称。
• column_name 是你要添加的新列的名称。
• column_definition 定义了新列的数据类型和其他属性（如是否允许 NULL、默认值等）。
• FIRST 是一个可选参数，用于将新列添加到表的最前面。
• AFTER existing_column 是一个可选参数，用于将新列添加到指定列之后。

**示例：**在 fruits 表中 name 列之后添加一个名为 quality 的新列，数据类型为 VARCHAR(255)，默认值为 'Good'，且不允许为空

ALTER TABLE fruits
ADD COLUMN quality VARCHAR(255) DEFAULT 'Good' NOT NULL AFTER name;

删除字段

在MySQL中，我们可以使用alter table…drop…语句来删除一个字段。

alter table 表名
drop 字段名;

修改字段

在MySQL中，我们可以使用alter table…change…语句来修改一个字段的名字。

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name new_column_definition [FIRST | AFTER existing_column];

• table_name：要修改的表的名称。
• CHANGE COLUMN：指示要修改列的定义并可能更改其名称。
• old_column_name：要修改的现有列的名称。
• new_column_name：新列的名称（如果不需要更改列名，可以与 old_column_name 相同）。
• new_column_definition：新的列定义，包括数据类型、是否允许 NULL、默认值等。
• FIRST：可选参数，将列移动到表的最前面。
• AFTER existing_column：可选参数，将列移动到指定列之后。

**示例：**将 fruits 表的 price 字段更改为 price_describe 并将类型改为 VARCHAR(20)，添加默认值 “便宜”。

ALTER TABLE fruits
CHANGE COLUMN price price_describe VARCHAR(20) DEFAULT '便宜';

修改字段类型

在MySQL中，我们可以使用alter table…modify…语句来修改一个字段的数据类型。

ALTER TABLE table_name
MODIFY COLUMN column_name new_column_definition [FIRST | AFTER existing_column];

• table_name：要修改的表的名称。
• MODIFY COLUMN：指示要修改列的定义。
• column_name：要修改的列的名称。
• new_column_definition：新的列定义，包括数据类型、是否允许 NULL、默认值等。
• FIRST：可选参数，将列移动到表的最前面。
• AFTER existing_column：可选参数，将列移动到指定列之后。

**示例：**更改 fruits 的 price 字段类型为 DECIMAL 以便存储更精确的小数，并使其不允许 NULL 值。

ALTER TABLE fruits
MODIFY COLUMN price DECIMAL(10, 2) NOT NULL;

复制表

在MySQL中，复制表主要有两种方式：1.只复制结构；2.同时复制结构和数据。

只复制结构

在MySQL中，我们可以使用create table…like…语句来将一个已存在的表的“结构”复制到一个新表中。简单来说，就是用已存在的表的结构来创建一个新表。

它将拥有与旧表相同的列、索引、默认值等，但不会包含旧表中的任何数据。

create table 新表名
like 旧表名;

同时复制结构和数据

在MySQL中，我们可以使用create table…as…语句来将一个已存在的表的结构和数据同时复制到一个新表中去。

create table 新表名
as (select * from 旧表名);

**示例：**创建一个新表 high_price_fruits，只包含价格高于 20 的水果。

CREATE TABLE high_price_fruits AS
SELECT id, name, price, season
FROM fruits
WHERE price > 20;

笔记

对于create table...like...来说，它会复制旧表中的完整结构，包括主键、自动递增、索引等。不过create table...like...只能复制结构，不能复制数据。

对于create table...as...来说，它存在一定的局限性，并不会复制旧表中的主键、自动递增、索引等。对于这些属性，我们需要手动去添加。不过create table...as...有一个优势，那就是可以复制数据。

删除表

在MySQL中，我们可以使用drop table语句来删除一个表。

drop table 表名;