[MySQL]表的创建、修改与删除

已于 2022-10-08 23:12:15 修改
阅读量2k 收藏 15
点赞数 1
分类专栏： MySQL学习笔记 文章标签： mysql 数据库 sql
于 2022-10-08 17:58:03 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_53022813/article/details/127207438
版权

在这里插入图片描述

文章目录

1. MySQL中的数据类型

在这里插入图片描述

1.1 常用的数据类型

在这里插入图片描述

2. 创建表

创建表必须具备CREATE TABLE权限和存储空间

2.1 方式一：创建空的新表

2.1.1 新表的创建

语法：

CREATE TABLE [IF NOT EXISTS] 表名( 
	字段1, 数据类型 [约束条件] [默认值], 
	字段2, 数据类型 [约束条件] [默认值], 
	字段3, 数据类型 [约束条件] [默认值], 
	……
	[表约束条件] 
);

加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

  • 必须指定：
    • 表名
    • 列名(或字段名)，数据类型，长度
  • 可选指定：
    • 约束条件
    • 默认值
# 使用 mytest3 数据库
USE mytest3;
SELECT DATABASE();

在这里插入图片描述

CREATE TABLE IF NOT EXISTS myemp1 (
    # id int类型
    id INT,
    # 员工姓名 字符类型，最长15个字符
    # 使用VARCHAR必须指明长度
    emp_name VARCHAR(15),
    # 雇佣时间 日期类型
    hire_date DATE
);

2.1.2 查看表结构

DESC myemp1;

在这里插入图片描述

2.1.3 查询创建表的语句

SHOW CREATE TABLE myemp1;

在这里插入图片描述

如果创建表的时候没有指明使用的字符集，则默认使用表所在的数据库的字符集。

2.2 方式二：基于现有的表创建表

基于现有的表创建新的表，新的表的字段名和数据类型都和基于的现有表一致。

语法：

CREATE TABLE 将要新建的表名
AS
查询现有表的SELECT语句

查询语句查询出来的数据，会放入新建的表中

USE atguigudb;
CREATE TABLE myemp2
AS
SELECT employee_id,
       last_name,
       salary
FROM employees;

SELECT * FROM myemp2;

在这里插入图片描述

DESC myemp2;
DESC employees;

在这里插入图片描述
在这里插入图片描述

如果查询语句中有指定字段的别名，那么新的表的字段名就使用指定的别名，否则使用原表的字段名。

CREATE TABLE myemp3
AS
SELECT employee_id emp_id,
       last_name emp_name,
       salary
FROM employees;
SELECT * FROM myemp3;

在这里插入图片描述

2.3 练习

2.3.1 练习1

创建一个表employees_copy，实现对employees表的复制，包括表数据

# 创建一个表employees_copy，实现对employees表的复制，包括表数据
CREATE TABLE employees_copy
AS
SELECT * FROM employees;

SELECT * FROM employees_copy;

在这里插入图片描述

2.3.2 练习2

创建一个表employees_blank，实现对employees表的复制，不包括表数据

# 创建一个表employees_blank，实现对employees表的复制，不包括表数据
CREATE TABLE employees_blank
AS
# 实现对employees表的复制，不包括表数据
# 只需要查询出来的数据为空即可
SELECT *
FROM employees
# 由于该条件一定为false，所以查询出来的数据一定为空
WHERE 1 = 0;

SELECT * FROM employees_blank;

在这里插入图片描述

3. 修改表

修改表，使用ALTER TABLE语句。

3.1 添加字段

语法：

ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [FIRST|AFTER 字段名];

3.1.1 添加一个字段

给myemp1添加一个字段。

USE mytest3;
DESC myemp1;

ALTER TABLE myemp1
# 添加一个字段，字段名为salary，数据类型为double，
# 且位数为10位，其中小数位数占2位，整数部分10位
# 新字段默认添加到表的最后
ADD COLUMN salary DOUBLE(10, 2);

DESC myemp1;

在这里插入图片描述

3.1.2 添加字段到指定位置

ALTER TABLE myemp1
# 将新的字段添加到emp_name字段后
ADD phone_num VARCHAR(20) AFTER emp_name;

DESC myemp1;

在这里插入图片描述

3.2 修改字段

对表的字段进行修改，可以修改字段的数据类型、长度、默认值、位置等。

一般修改字段不会修改字段的数据类型，修改字段的数据类型很可能影响原本的数据。

语法：

ALTER TABLE 表名 
MODIFY [COLUMN] 字段名1 字段类型 [DEFAULT 默认值] [FIRST|AFTER 字段名 2];

3.2.1 修改字段的长度

ALTER TABLE myemp1
# 将emp_name字段的长度修改为25
MODIFY emp_name VARCHAR(25);

DESC myemp1;

在这里插入图片描述

3.2.2 修改字段的同时设置默认值

ALTER TABLE myemp1
# 将emp_name字段的长度修改为35
# 同时指定默认名字为张三
MODIFY emp_name VARCHAR(35) DEFAULT '张三';

DESC myemp1;

在这里插入图片描述

3.2.3 修改字段的位置

ALTER TABLE myemp1
# 将emp_name字段的长度修改为25
# 同时字段位于phone_num之后
# 不能单独设置字段的位置，即不能只写AFTER phone_num
MODIFY emp_name VARCHAR(25) AFTER phone_num;

DESC myemp1;

在这里插入图片描述

3.3 重命名字段

语法：

ALTER TABLE 表名 CHANGE [column] 列名 新列名 新数据类型;

ALTER TABLE myemp1
CHANGE salary monthly_salary double(10,2);

DESC myemp1;

在这里插入图片描述

3.4 删除字段

语法：

ALTER TABLE 表名 DROP [COLUMN] 字段名

ALTER TABLE myemp1
DROP phone_num;

DESC myemp1;

在这里插入图片描述

4. 重命名表

4.1 方式一：RENAME

语法：

RENAME TABLE 旧表名 TO 新表名;

RENAME TABLE myemp1 TO emp1;

SHOW TABLES;

在这里插入图片描述

4.2 方式二：ALTER

语法：

ALTER table 要修改的表 RENAME [TO] 新表名;

ALTER TABLE emp1
RENAME myemp1;

SHOW TABLES;

在这里插入图片描述

5. 删除表

语法：

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

USE atguigudb;

DROP TABLE IF EXISTS employees_blank;

SHOW TABLES;

在这里插入图片描述

数据表删除之后，不能撤销删除操作。
不光将表结构删除掉，同时表中的数据也删除掉，释放表空间。即数据和结构都被删除。

6. 清空表

清空表，表示清空表中的所有数据，但是表结构保留。

语法：

TRUNCATE TABLE 表名;

TRUNCATE TABLE employees_copy;

SELECT * FROM employees_copy;

在这里插入图片描述

6.1 TRUNCATE TABLE与DELETE FROM的对比

  • 相同点：
    • 都可以实现对表中所有数据的删除，同时保留表结构。
  • 不同点：
    • TRUNCATE TABLE：一旦执行此操作，表数据全部清除。同时，数据是不可以回滚的。
    • DELETE FROM：一旦执行此操作，表数据可以全部清除（不带WHERE）。同时，数据是可以实现回滚的（COMMIT之前）。

7. DCL中的COMMIT与ROLLBACK

  • COMMIT：
    • 提交数据。
    • 一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。
  • ROLLBACK：
    • 回滚数据。
    • 一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次COMMIT之后。

阿里开发规范：
【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。
说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

8. DDL 与 DML 说明

  • DDL
    • DDL的操作一旦执行，就不可回滚。
    • 指令SET autocommit = FALSE对DDL操作失效。(因为在执行完DDL操作之后，一定会执行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE影响的。)
  • DML
    • DML的操作默认情况，一旦执行，也是不可回滚的。DML执行完成默认会执行一次COMMIT。
    • 如果在执行DML之前，执行了SET autocommit = FALSE，则执行的DML操作就可以实现回滚。

8.1 演示

8.1.1 DML

# 为了避免之前的操作造成影响，先提交一次
COMMIT;

# 查询myemp3中的数据
SELECT * FROM myemp3;

在这里插入图片描述

# 取消DML操作的自动提交
SET AUTOCOMMIT = FALSE;

# 使用DELETE FROM清空myemp3中的数据
DELETE FROM myemp3;

# 再次查询myemp3
SELECT * FROM myemp3;

在这里插入图片描述

# 进行数据的回滚
ROLLBACK;

# 再次查询myemp3
# 由于没有提交清空操作所以可以进行数据的回滚
SELECT * FROM myemp3;

在这里插入图片描述

在执行DML之前，执行了SET autocommit = FALSE，则执行的DML操作就可以实现回滚。

8.1.2 DDL

# 为了避免之前的操作造成影响，先提交一次
COMMIT;

# 查询myemp3中的数据
SELECT * FROM myemp3;

在这里插入图片描述

# 取消DML操作的自动提交
SET AUTOCOMMIT = FALSE;

# 使用TRUNCATE TABLE清空myemp3中的数据
TRUNCATE TABLE myemp3;

# 再次查询myemp3
SELECT * FROM myemp3;

在这里插入图片描述

# 进行数据的回滚
ROLLBACK;

# 再次查询myemp3
# 由于执行的为DDL操作，所以不能进行数据的回滚
SELECT * FROM myemp3;

在这里插入图片描述

DDL的操作一旦执行，就不可回滚。且指令SET autocommit = FALSE对DDL操作无效。

9. 阿里MySQL命名规范

  • 【 强制 】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
    • 正例：aliyun_admin，rdc_config，level3_name
    • 反例：AliyunAdmin，rdcConfig，level_3_name
  • 【 强制 】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。
  • 【 强制 】表必备三字段：id, gmt_create, gmt_modified。
    • 说明：其中 id 必为主键，类型为BIGINT UNSIGNED、单表时自增、步长为 1。gmt_create, gmt_modified 的类型均为 DATETIME 类型，前者现在时表示主动式创建(最开始[第一次]添加数据的时间)，后者过去分词表示被动式更新(后期对数据进行修改的时间)
  • 【 推荐 】表的命名最好是遵循 “业务名称_表的作用”。
    • 正例：alipay_task 、 force_project、 trade_config
  • 【 推荐 】库名与应用名称尽量一致。
  • 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。
    • 正例：无符号值可以避免误存负数，且扩大了表示范围。
    • 在这里插入图片描述

10. MySQL8新特性—DDL的原子化

在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即 DDL操作要么成功要么回滚 。

CREATE DATABASE mytest; 

USE mytest; 

CREATE TABLE book1( 
	book_id INT , 
	book_name VARCHAR(255) 
);

SHOW TABLES;

在这里插入图片描述

由于删除两种表为同一个事务，在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，所以删除第二个表失败时会进行事务的回滚，撤销对表1的删除。因为在删除表2失败时并没有整个事务执行成功，所以会回滚。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值