MySQL数据表操作

目录

常用数据类型

数值类型

整型

浮点型

字符串类型

日期类型

数据表的操作

查看表结构

创建表

约束

删除表

修改表

添加列

删除列

修改列的定义

重命名列

重命名表

总结

在学习了数据库操作之后，我们接着来看数据表的相关操作

我们首先来学习 MySQL 中的常用数据类型

常用数据类型

数值类型

数值类型分为整型和浮点型，我们先看整型：

整型

数据类型

大小

说明

对应Java类型

BIT(M)

M指定位数，默认为1

二进制数，M的范围从1到64，存储的数值范围为 0 - 2^M - 1

当 M 取值为1 时，即只能存储 0 和 1，常用 Boolean 对应 BIT(1)

TINYINT

1字节

Byte

SMALLINT

2字节

Short

INT

4字节

Integer

BIGINT

8字节

存储大整数

Long

浮点型

数据类型

大小

说明

对应Java类型

FLOAT(M, D)

4字节

单精度，M指定长度，D指定小数位数。会发生精度丢失

Float

DOUBLE(M, D)

8字节

Double

DECIMAL(M, D)

M/D最大值 + 2

双精度，M指定长度，D指定小数点位数，精确数值

BigDecimal

NUMERIC(M, D)

M/D最大值 + 2

和 DECIMAL 一样

BigDecimal

数值类型可以指定为**无符号（unsigned），**表示不取负数

1 字节（bytes） = 8 bit

对于整型类型的范围：

有符号范围： -2^(类型字节数*8 - 1) ~ 2^(类型字节数*8 - 1)

无符号范围：0 ~ 2^(类型字节数*8) - 1

字符串类型

数据类型

大小

说明

对应Java类型

VARCHAR(SIZE)

0-65 535字节

可变长字符串

String

CHAR

0-255字节

定长字符

String

TEXT

0-65 535字节

长文本数据

String

MEDIUMTEXT

0-16 777 215字节

中等长度文本数据

String

BLOB

0-65 535字节

二进制形式的长文本数据

byte[]

其中，VARCHAR是我们较为常用的类型，SIZE 表示最大字符长度，假设 size 为 1000，表明这一列最多能够存储 1000 个字符，若当前字符串比较短，可能只有2个字符，那么 varchar 会根据存储的内容动态扩容

日期类型

数据类型

大小

说明

对应Java类型

DATETIME

8字节

范围从1000到9999年，不会进行时区的检索和转换

java.util.Date、

java.sql.Timestamp

TIMESTAMP

4字节

范围从1970 到 2038 年，自动检索当前时区并进行转换

java.util.Date、

java.sql.Timestamp

数据表的操作

需要操作数据库中表时，需要先使用该数据库：

use 数据库名;

查看表结构

语法：

desc table_name;

示例：

创建表

语法：

CREATE TABLE table_name (
column1 datatype constraints COMMENT ‘注释’,
column2 datatype constraints,
…
PRIMARY KEY (primary_key_column),
FOREIGN KEY (foreign_key_column) REFERENCES other_table(other_column)
);

column1：列名（字段名）

datatype：字段数据类型

constraints：约束

COMMENT：可选的，可以添加字段说明（注释）

PRIMARY KEY (primary_key_column)：指定表的主键，一张表中只能存在一个主键

FOREIGN KEY (foreign_key_column) REFERENCES other_table(other_column)：指定foreign_key_column 为外键，并与other_table 表中的other_column 相关联

在 MySQL 中会使用到 单引号（'）、双引号（"）以及 反引号（`）

单引号（'）：用于表示字符串文字

双引号（"）：也用于表示字符串文字

反引号（`）：用于引用数据库对象的名称（如 表名、列名），特别是当名称中包含空格或与SQL关键字冲突时

例如，现在需要创建一个 订单表，并将表名命名为 order，但是 order 是关键字，此时，我们就可以使用反引号（`）

create table `order`(

order_id,

nums int

);

接下来，我们通过一个具体的例子来继续学习：

创建一张员工表：

CREATE TABLE employees (
employee_id INT AUTO_INCREMENT COMMENT ‘员工编号’,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
hire_date DATE,
salary DECIMAL(10, 2),
PRIMARY KEY (employee_id)
);

employee_id：自增的整数，作为主键

**first_name、last_name：**不允许为空的字符串

hire_date：日期字段

salary：具有两位小数的十进制数

AUTO_INCREMENT：MySQL 中用于自动生成唯一数字的属性，通常用于主键列，当插入新的记录时，数据库会自动为这个列生成一个递增的数字值，这样，就不需要我们手动指定这个值，它会确保每条记录都有一个唯一的标识符

NOT NULL：MySQL 中的一个列约束，用于确保这一列在插入数据时不能有 NULL 值，也就是在插入或更新记录时，若不提供该列的值，就会报错，这个约束确保了数据的完整性和有效性

那么，什么是 约束 呢？

约束

MySQL 中的约束，相当于限定条件，用于确保数据库中数据的 完整性 和 有效性，主要的约束有：

主键约束（PRIMARY KEY）

确保列的值唯一且非空，每个表只能有一个主键

CREATE TABLE students (
student_idINT PRIMARY KEY,
name VARCHAR(50)
);

唯一约束（UNIQUE）

确保列中的每个值唯一，可以包含 NULL 值

CREATE TABLE users (
user_idINT,
email VARCHAR(255) UNIQUE
);

非空约束（NOT NULL）

确保列不能为 NULL 值

CREATE TABLE products (
product_idINT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL
);

外键约束（FOREIGN KEY）

确保列中的值在另一个表的列中存在，维护表之间的关系

CREATE TABLE orders (
order_idINT PRIMARY KEY,
customer_idINT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

默认值约束（DEFAULT）

提供列的默认值，若插入时没有提供该列的值，则使用默认值

CREATE TABLE products (
product_idINT PRIMARY KEY,
stock INT DEFAULT 0
);

检查约束（CHECK）

确保列中的数据符合特定条件

CREATE TABLE employees (
employee_idINT PRIMARY KEY,
salary DECIMAL(10, 2),
CHECK (salary > 0)
);

删除表

DROP TABLE [IF EXISTS] table_name1, table_name2… ;

示例：

删除名为 employees 的表

DROP TABLE employees;

若 student 表存在，则删除

DROP TABLE IF EXISTSstudent;

一次性删除多张表

DROP TABLE table1, table2, table3;

注意：删除表会同时删除表结构和数据，因此，删除需要谨慎操作

修改表

修改表结构使用 ALTER TABLE 语句，可以对表进行多种修改，如添加列、删除列 或是 修改列

添加列

ALTER TABLE table_name ADD COLUMN column_name column_definition;

示例：

ALTER TABLE employees ADD COLUMN date_of_birth DATE;

删除列

ALTER TABLE table_name DROP COLUMN column_name;

示例：

ALTER TABLE employees DROP COLUMN date_of_birth;

修改列的定义

ALTER TABLE table_name MODIFY COLUMN column_name new_column_definition;

示例：

ALTER TABLE employees MODIFY COLUMN last_name VARCHAR(100);

重命名列

ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name column_definition;

示例：

ALTER TABLE employees CHANGE COLUMN last_name surname VARCHAR(100);

重命名表

ALTER TABLE old_table_name RENAME TO new_table_name;

示例：

ALTER TABLE employees RENAME TO staff;

注意：修改表结构可能会影响数据的完整性，因此，在进行相关操作之前，需要确保已经备份了相关数据

总结

常用数据类型：

INT：整型

DECIMAL(M, D)：浮点型

VARCHAR(SIZE)：字符串类型

TIMESTAMP：日期类型

操作数据表：

查看表结构：

desc 表名;

创建表：

create table 表名(

字段1 类型1,

字段2 类型2,

…

);

删除表：

drop table 表名;

修改表：

添加列：

alter table 表名 add column 列名 类型;

删除列：

alter table 表名 drop column 列名;

修改列的定义：

alter table 表名 modify column 列名 新数据类型;

重命名列：

alter table 表名 change column 旧列名 新列名 数据类型;

重命名表：

alter table 旧表名 rename to 新表名;