第4讲——MySQL数据表的基本操作3

旺仔不是程序员
177 阅读6分钟

❤️持续创作，加速成长！开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 4 天，点击查看活动详情”

🎨 个人介绍

👉大家好，我是：旺仔不是程序员

👉认真分享技术，记录学习过程的点滴，如果我的分享能为你带来帮助，请支持我奥🍻

👉你的支持，是我每天更新的动力。

👉赞点：👍 留言：✍ 收藏：⭐

👉个人格言：想法一步一步的落实，才是你我前进最佳选择。

my.jpeg

1. 修改表内字段

1. 修改字段的数据类型

  1. 概述：修改字段的数据类型，就是把字段的数据类型转换成另一种数据类型
  2. 语法规则：ALTER TABLE <表名> MODIFY <字段名>　<数据类型>
# 将数据表tb_dept1中name字段的数据类型由VARCHAR(22)修改成VARCHAR(30)。
# 执行修改表名操作之前，使用DESC查看tb_dept表结构，结果如下：
DESC tb_dept1;
ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);

2. 修改字段名

  1. 语法规则：ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
  2. 提示：
    1. CHANGE也可以只修改数据类型，实现和MODIFY同样的效果
      1. 方法：将SQL语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”
    2. 不要轻易修改数据类型。
# 将数据表tb_dept1中的location字段名称改为loc，数据类型保持不变，SQL语句如下：
ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);
# 将数据表tb_dept1中的loc字段名称改为location，同时将数据类型变为VARCHAR(60)，SQL语句如下：
ALTER TABLE tb_dept1CHANGE loc location VARCHAR(60);

3. 添加字段

  1. 概述：
    1. 可能需要在已经存在的表中添加新的字段
    2. 一个完整字段包括字段名、数据类型、完整性约束
  2. 添加字段的语法格式：
    1. ALTER TABLE <表名> ADD <新字段> <数据类型> [约束条件] [FIRST | AFTER 已存在的字段]
  3. 注意：
    1. “FIRST”或“AFTER已存在字段名”用于指定新增字段在表中的位置
    2. 如果SQL语句中没有这两个参数，则默认将新添加的字段设置为数据表的最后列
  4. 添加方式：
    1. 添加无完整性约束条件的字段
    2. 添加有完整性约束条件的字段
    3. 在表的第一列添加一个字段
    4. 在表的指定列之后添加一个字段
  1. 添加无完整性约束条件的字段
# 在数据表tb_dept1中添加一个没有完整性约束的INT类型的字段managerId（部门经理编号），SQL语句如下：
ALTER TABLE tb_dept1 ADD managerId INT(10);
  1. 添加有完整性约束条件的字段
# 在数据表tb_dept1中添加一个不能为空的VARCHAR(12)类型的字段column1，SQL语句如下：
ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) not null;
  1. 在表的第一列添加一个字段
# 在数据表tb_dept1中添加一个INT类型的字段column2，SQL语句如下：
ALTER TABLE tb_dept 1ADD column2 INT(11) FIRST;
  1. 在表的指定列之后添加一个字段
# 在数据表tb_dept1中name列后添加一个INT类型的字段column3，SQL语句如下：
ALTER TABLE tb_dept1 ADD column3 INT(11) AFTER name;

4. 删除字段

  1. 概述：删除字段是将数据表中的某个字段从表中移除
  2. 语法格式：ALTER TABLE <表名> DROP <字段名>;
# 删除数据表tb_dept1表中的column2字段。
ALTER TABLE tb_dept1 DROP column2;
DESC tb_dept1

5. 修改字段的排列位置

  1. 概述：可以通过ALTER TABLE来改变表中字段的相对位置
  2. 语法格式：ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;
# 将数据表tb_dept中的column1字段修改为表的第一个字段，SQL语句如下：
ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;
# 将数据表tb_dept1中的column1字段插入到location字段后面，SQL语句如下：
ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER location;

2. MySQL 8.0的新特性

1. 默认字符集改为utf8mb4

  1. 概述：在MySQL 8.0版本之前，默认字符集为latin1，utf8字符集指向的是utf8mb3
  2. 出现的问题：
    1. 网站开发人员在数据库设计的时候往往会将编码修改为utf8字符集
    2. 如果遗忘修改默认的编码，就会出现乱码的问题
  3. 解决方法：从MySQL 8.0开始，数据库的默认编码改为utf8mb4，从而避免了上述的乱码问题
SHOW VARIABLES LIKE 'character_set_database'

3.png

2. 自增变量的持久化

  1. 出现问题：
    1. 在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1
    2. 这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题
  2. 出现问题的元婴：出现上述结果的主要原因是自增主键没有持久化
  3. MySQL 5.7系统：
    1. 对于自增主键的分配规则，是由InnoDB数据字典内部一个计数器来决定的
    2. 而该计数器只在内存中维护，并不会持久化到磁盘中
    3. 当数据库重启时，该计数器会通过下面这种方式初始化
  4. MySQL 8.0版本：
    1. MySQL 8.0将自增主键的计数器持久化到重做日志中
    2. 每次计数器发生改变，都会将其写入重做日志中
    3. 如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值
    4. 为了尽量减小对系统性能的影响，计数器写入到重做日志时并不会马上刷新数据库系统
# 自增变量的持久化
CREATE TABLE test1(
id int auto PRIMARY KEY AUTO_INCREMENT
)
# 插入4个空值
INSERT INTO test1 VALUES(0),(0),(0),(0)
# 删除id为4的记录
DELETE FROM test1 where id=4;
# 再次插入一个空值
INSERT INTO test1 values(0);
# 查询此时数据表test1中的数据
SELECT * FROM test1;
# 删除id为5的记录
DELETE FROM test1 where id=5;
# 重启数据库，重新插入一个空值
INSERT INTO test1 values(0);
# 查询此时数据表test1中的数据
SELECT * FROM test1;

3. 实战练习

1. 实战练习——对表的操作

# 创建数据库
CREATE DATABASE company;
# 使用数据库
USE company;
# 创建表 offices 
# 主要对表的一些操作
CREATE TABLE offices 
(
officeCode  INT(10) NOT NULL UNIQUE,
city        VARCHAR(50) NOT NULL,
address     VARCHAR(50) NOT NULL,
country     VARCHAR(50) NOT NULL,
postalCode  VARCHAR(15) NOT NULL,
PRIMARY KEY  (officeCode)
);
# 查看表
SHOW tables;

# 查看表结构
DESC offices;

# 修改表
ALTER TABLE offices RENAME offices_info;
show tables;

# 删除表
DROP TABLE offices;
SHOW tables;

2. 实战练习——对表字段的操作

# 创建表employees。
# 创建表employees的语句如下
# 对表字段的操作
CREATE TABLE employees 
(
employeeNumber  INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
lastName         VARCHAR(50) NOT NULL,
firstName        VARCHAR(50) NOT NULL,
mobile           VARCHAR(25) NOT NULL,
officeCode       INT(10) NOT NULL,
jobTitle         VARCHAR(50) NOT NULL,
birth            DATETIME,
note            VARCHAR(255),
sex             VARCHAR(5),
CONSTRAINT office_fk FOREIGN KEY(officeCode)  REFERENCES offices(officeCode)
);
# 查看表
show tables;
# 查看表结构
DESC employees;

# 修改employees表的mobile字段在officeCode之后
ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;
DESC employees;

# 修改 employees 的birth 字段 为 employee_birth
ALTER TABLE employees CHANGE birth employee_birth DATETIME;
DESC employees;

# 修改 employees 
ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;
DESC employees;

# 删除 employees 的 note 字段 
ALTER TABLE employees DROP note;
DESC employees;

# 删除 employees 的外键 office_fk
ALTER TABLE employees DROP FOREIGN KEY office_fk;

# 修改 employees 的引擎为 MyISAM
ALTER TABLE employees ENGINE=MyISAM;
SHOW CREATE TABLE employees


ALTER TABLE employees RENAME employees_info;
show tables;
avatar
文章
阅读
粉丝