【MySQL】20-MySQL如何创建和管理表超详细汇总

已于 2022-11-13 20:20:51 修改
阅读量1.2k 收藏 2
点赞数 1
分类专栏： # MySQL 文章标签： mysql 数据库 sql 大数据
于 2022-07-21 13:22:44 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Sihang_Xie/article/details/125910097
版权

目录


1. 创建表


创建表有两种方式。需要用户具备创建表的权限。


1.方式一

CREATE TABLE IF NOT EXISTS 表名(
字段1名称 字段1数据类型,
字段2名称 字段2数据类型,
...
);

举个例子：

要在数据库 atguigudb 下，创建名为 myemp1 的表。包含三个字段，分别是 INT 类型的 id ，VARCHAR 类型的 emp_name (占15个长度) ，以及 DATE 类型的 hire_date 。

CREATE TABLE IF NOT EXISTS myemp1(
id INT,
emp_name VARCHAR(15),
hire_date DATE
);

可以通过下列语句查看表结构：

DESC myemp1;

输出：

SHOW CREATE TABLE myemp1;

输出：


【注意】

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


2.方式二

方式二是基于现有的表去创建新的表，同时导入数据。

CREATE TABLE 表名
AS
SELECT 现有的表的字段
FROM 现有的表;

举个栗子 ：

我要从已有的表 employees 选择 employee_id 、last_name 和 salary 三个字段，创建一个新的表 myemp2 。

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

查看一下表的结构：

DESC myemp2;

输出：

用此方法创建的新表，其字段的名称、数据类型、属性等与原表是一样的。


注意，此方法创建的新表中，是保留原表的数据的。

SELECT * FROM myemp2;

查询结果：


这意味着，我们可以结合前面章节所讲的 SELECT 丰富的查询语句操作，基于现有的很多表，灵活地创建出不同的新表。


举个例子：

使用多表查询员工表 employees 和部门表 depatments ，创建新表：

CREATE TABLE myemp3
AS
SELECT e.employee_id emp_id, e.last_name l_name, d.department_name dept_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

可以看到，SELECT 字段后使用了别名，那么新表中的字段就会以别名为新名称。

查看新表 myemp3 ：

SELECT * FROM myemp3;

查询结果：


【例子1】创建一个表 employees_copy ，实现对 employees 表的复制，包括表数据。

CREATE TABLE employees_copy
AS
SELECT *
FROM employees;

查询新表 employees_copy ：

SELECT * FROM employees_copy;

查询结果：

把整个表的字段结构、数据内容都复制过来了。对此表的增删改查不影响原来的 employees 表。


【例子2】创建一个表 employees_blank ，实现对 employees 表的复制，不包括表数据。

这题看起来很难，其实新表中的数据是复制 SELECT 查询语句的结果集。那么要想只复制字段，不复制内容，只需要在 SELECT 语句上的 WHERE 过滤条件写上绝对是 FALSE 的条件 (如 1=2 ) ，让查询结果返回空即可。

CREATE TABLE employees_blank
AS
SELECT *
FROM employees
WHERE 1 = 2;

查询新表 employees_blank ：

SELECT * FROM employees_blank;

查询结果：

DESC employees_blank;

查询结果：


2. 修改表


修改表主要靠关键字 ALTER 实现。下面以 myemp1 这张表为例讲解。

DESC myemp1;

2.1 添加字段


添加字段的语句为：

ALTER TABLE 表名
ADD 要添加的字段名 字段类型;

举个栗子：

往表 myemp1 中添加一个工资 salary 字段。

ALTER TABLE myemp1
ADD salary DOUBLE(10, 2);

第2行代码，我们声明 salary 为一个 DOUBLE 类型，10 代表该 DOUBLE 类型长度一共为10位，2 代表小数点后保留 2 位有效数字，因此一共有8位整数。


查看字段是否添加成功：

DESC myemp1;

查询结果：


可以看到，默认是添加到表 myemp1 中的最后一个字段。

那如果我想添加到任意一个位置，应该如何操作呢？


要在任意位置添加字段，通过关键字 FIRST 和 AFTER… 来操作。


【例子1】把电话号码 phone_number 添加到表 myemp1 的第一个字段。

ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;

查看表 myemp1 的字段结构：

DESC myemp1;

查询结果：


【例子2】把邮箱 email 添加到表 myemp1 的 id 字段的后面。

ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER id;

查看表 myemp1 的字段结构：

DESC myemp1;

查询结果：


2.2 修改字段


通过关键字 MODIFY 可以修改字段的数据类型、长度、默认值 (等讲到约束的时候再讲) 。

但在实际开发中，一般不可能修改字段的数据类型，如果用了一段时间之后才发现字段数据类型定义错误，那将是非常大的工作失误。

在实际开发中，修改长度倒是有可能的。比如一开始定义的长度短了，后面我们可以把它修改为长一点的。


【例子】把表 myemp1 的字段 emp_name 的长度改为 25 的。

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25);

查看表 myemp1 的字段结构：

DESC myemp1;

查询结果：

image-20220720155742607


2.3 重命名字段


重命名通过关键字 CHANGE 来实现。


【例子】把表 myemp1 中的 salary 字段改为 monthly_salary 。

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

查看表 myemp1 的字段结构：

DESC myemp1;

查询结果：


改名称的同时，也可以同时把长度也改了。


【例子2】把表 myemp1 中的 email 字段改为 my_email 并将其长度改为50。

ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);

查看表 myemp1 的字段结构：

DESC myemp1;

查询结果：


2.4 删除字段


删除字段使用关键字 DROP COLUMN来实现。

【例子】删除表 myemp1 中的 my_email 字段。

ALTER TABLE myemp1
DROP COLUMN my_email;

查看表 myemp1 的字段结构：

DESC myemp1;

查询结果：


3. 重命名表


重命名表有2种方式，推荐第一种。


  • 方式一 (推荐) ：使用 RENAME TABLE … TO … 关键字。

【例子】把表 myemp1 重命名为 myemp11 。

RENAME TABLE myemp1
TO myemp11;

查看数据库 atguigudb 中的表：

SHOW TABLES;

查询结果：


  • 方式二：使用关键字 ALTER

【例子】把表 myemp2 重命名为 myemp22 。

ALTER TABLE myemp2
RENAME TO myemp22;

查看数据库 atguigudb 中的表：

SHOW TABLES;

查询结果：

image-20220720165241916


4. 删除表


删除表通过关键字 DROP TABLE 来实现。不光删除表结构，同时删除表中的数据，释放表所占据的存储空间。

DROP TABLE IF EXISTS 表名;

【例子】删除数据库 atguigudb 中的表 myemp3 。

DROP TABLE IF EXISTS myemp3;

查看数据库 atguigudb 中的表：

SHOW TABLES;

查询结果：

可以看到，表 myemp3 已经被删除了。

【注意】

删除数据库和删除表一样，是不能撤销和回滚的。删除操作一定要慎重！


5. 清空表


清空表通过关键字 TRUNCATE TABLE 来实现。清空表与删除表不同，清空表不删除表的表结构，仅仅清除表中的数据。

TRUNCATE TABLE 表名;

【例子】清空表 employees_copy 中的数据。

TRUNCATE TABLE employees_copy;

查看表 employees_copy 中的数据：

SELECT * FROM employees_copy;

查询结果：

可以看到表结构还保留，但是表结构还在。


6. DCL中COMMIT和ROLLBACK


那么清空表和删除表中的数据到底有什么区别呢？讲清楚这个问题之前，首先来讲一下DCL操作中的提交 COMMIT 和回滚 ROLLBACK 。

COMMIT : 提交数据。一旦执行COMMIT，则数据就被永久地保存在数据库中，意味着数据不能回滚。

ROLLBACK：回滚数据。一旦执行ROLLBACK，则可以实现数据的回滚。回滚到最近的一次COMMIT之后。


6.1 TRUNCATE和DELETE FROM对比


相同点：都可以对表中所有数据进行删除，同时保留表结构。


不同点：

  • TRUNCATE TABLE：不可回滚的。
  • DELETE FROM：数据可以回滚。

【阿里巴巴MySQL开发规范】

TRUNCATE TABLE 比 DELETE 速度更快，占用资源更少。因为前者不需要记录回滚日志，而后者需要一边删除，一边记录日志以备用户后悔进行回滚。

但是，正是由于 TRUNCATE TABLE 无事务且不触发 TRIGGER ，不可回滚的特性，使得在实际开发中有可能造成事故 (例如最核心的用户数据) 丢失。因此在实际开发中，往往更常用 DELETE 来清空表数据。宁愿多占用一点系统资源，也不愿意冒不可回滚的风险。


6.2 DDL和DML的对比

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

【例子1】使用DELETE FROM删除表 myemp3 中的所有数据，再回滚。

① 先提交一下 COMMIT：

COMMIT;

② 查看一下表 myemp3 中的数据：

SELECT * FROM myemp3;

查询结果：

③ 把自动提交设置为 FALSE：

SET autocommit = FALSE;

④ 使用 DELETE FROM 删除表 myemp3 中的所有数据：

DELETE FROM myemp3;

查询结果：

image-20220720202316607

⑤ 回滚数据：

ROLLBACK;

⑥ 再次查询表 myemp3 中的数据：

SELECT * FROM myemp3;

查询结果：

image-20220720202814599

可以看到，删除操作被成功撤销，回滚到最后一次COMMIT之后的版本。这也进一步说明 DELETE FROM 作为 DML 操作是可以回滚的。


【例子2】使用 TRUNCATE TABEL 删除表 myemp3 中的所有数据，但不能回滚。

① 先提交一下 COMMIT：

COMMIT;

② 查看一下表 myemp3 中的数据：

SELECT * FROM myemp3;

查询结果：

③ 把自动提交设置为 FALSE。虽然此语句对 DDL 操作无效，但为了控制变量，还是执行改语句：

SET autocommit = FALSE;

④ 使用 TRUNCATE TABEL 删除表 myemp3 中的所有数据：

TRUNCATE TABLE myemp3;

执行完TRUNCATE 后改语句就自动COMMIT 了，所以回滚 ROLLBACK 到此 。所以删除数据就无法撤销了。

查询结果：

⑤ 回滚数据：

ROLLBACK;

⑥ 再次查询表 myemp3 中的数据：

SELECT * FROM myemp3;

查询结果：


可以看到，就算执行回滚 ROLLBACK 也无法撤销 TRUNCATE TABLE 的删除操作。

【结论】

DDL 操作是不可撤销的。进行DDL操作时要慎之又慎。


7. 阿里巴巴操作规范


7.1 命名规范

  • 【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

    • 正例：aliyun_admin, rdc_config, level3_name
    • 反例：AliyunAdmin, rdcConfig, level_3_name

  • 【强制】禁用保留字。如 desc, range, match, delayed 等。

  • 【强制】表必备三个字段：id, gmt_create, gmt_modified 。

    • 说明：其中 id 必为主键，类型为 BIGINT UNSIGNED，单表时自增、步长为 1 。gmt_create 、gmt_modified 的数据类型均为 DATETIME 类型，前者的单词为现在时表示主动式创建，而后者单词是过去分词表示被动式更新。

  • 【推荐】表的命名最好遵循 “业务名称_表的作用” 。

    • 正例：alipay_task, force_project, trade_config

  • 【推荐】数据库名与应用名称尽量一致。

  • 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提示检索速度。

    • 正例：无符号值可以避免存储负数，且扩大了表示范围。
    对象年龄区间类型字节表示范围
    人150岁之内TINYINT UNSIGNED1无符号值：0~255
    龟数百岁SMALLINT UNSIGNED2无符号值：0~65535
    恐龙化石数千万年INT UNSIGNED4无符号值：0~43亿
    太阳约50亿年BIGINT UNSIGNED8无符号值：0~ 1.84 × 1 0 19 1.84\times10^{19} 1.84×1019

7.2 清空表和删除表的规范

表删除操作是把表的结构和表中的数据一起删除，并且 MySQL 在执行删除操作时，不会有任何的确认信息提示。因此执行删除操作时应当慎重。在删除表前，最好对表中的数据进行备份。这样当操作失误时可以对数据进行恢复，以免造成无法挽回的后果。

同样的，在使用 ALTER TABLE 进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进行完整的备份。因为数据库的改变是无法撤销的 (不可回滚的) 。如果添加了一个不需要的字段，可以将其删除；相同的，如果删除了一个需要的列，该列下面的所有数据都将丢失。


7.3 MySQL8新特性：DDL的原子化


【原子化】是什么意思？

答：原子化的概念取自化学中原子是组成物质的最小元素的概念，不可分割 (但其实原子可以进一步分割成夸克，没关系，只是借用一下概念而已) 。关键点在于 ”不可分割“ 。在MySQL中，一个 ”事务“ 常常是由多条操作语句组成的。原子化要求：这一个事务中的所有操作语句，要么全部执行成功，要么如果有一条执行报错，就要回滚到初始状态 (即啥也没发生) 。不允许一部分执行成功，剩下的部分执行失败的情况，这就像原子一样是最小单位不可分割一样。故称为原子化。

在MySQL8之前，DDL 语句是不支持原子化的。而MySQL8之后就新增了 DDL 的原子化。


举个例子：分别在 MySQL5.7 和 MySQL8.0 中创建数据库 mytest ，并创建表 book1 ，然后再执行删除表操作，删除表 book1 和 book2 ，但表 book2 是不存在的，因此该删除语句必定失败报错。我们重点观察在执行失败的情况下，表 book1 是否被删除。

先分别执行创建数据库和表的语句：

CREATE DATABASE mytest;

USE mytest;

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

SHOW TABLES;

然后分别观察执行删除表操作的结果：


1.MySQL5.7版本

DROP TABLE book1, book2;

输出：

错误代码： 1051
Unknown table 'mytest.book2'

再次查看数据库中的所有表：

SHOW TABLES;

输出：

image-20220721124657227


2.MySQL8.0版本

DROP TABLE book1, book2;

输出：

错误代码： 1051
Unknown table 'mytest.book2'

再次查看数据库中的所有表：

SHOW TABLES;

输出：


可以看到在MySQL8.0中，事务一旦执行失败，就会回滚。这就是8.0的新特性：DDL 操作的原子化。

卡皮巴拉不躺平
关注
专栏目录
MySQL创建表
qq_29142107的博客
05-03 1万+
创建表 创建学员表 -- 创建maizi数据库 CREATE DATABASE IF NOT EXISTS maizi DEFAULT CHARACTER SET 'UTF8'; USE maizi; #创建学员表（USER） #编号id #用户名username #年龄age #性别sex #邮箱email #地址addr #生日birth #薪水salary #电话tel #是否结婚marr...
mysql面试-高质量mysql面试题汇总-常见mysql面试题
01-17
MySQL是世界上最受欢迎的关系型数据库管理系统之一，尤其在Web应用程序中被广泛应用。对于求职者来说，掌握MySQL的相关知识并在面试中表现出色至关重要。本篇将基于提供的面试题汇总，深入探讨MySQL的一些核心概念、...
mysql自动创建表
06-22
大批数据，根据字段名称创建独立的表。。。。。。。。。。。。。。。。。。。。。。。。。。。
mysql创建表
weixin_46996561的博客
06-14 2万+
mysql创建表列属性参数
【MySQL】表的相关操作
weixin_66672501的博客
09-19 2872
这种做法相当于是把当初创建表时的有效操作给记录下来了，所以是最详细的，包含了说明和字符集等等，不过可以发现的时候上面的写法貌似和当初自己写的不太一样，这是因为mysqld要对命令进行语法和词法分析，并对其按照标准进行修改优化，最后再去执行优化后的命令，也就是上面这样。和库一样，不要轻易对表进行修改和删除，因为数据库是比较接近底层的，上层程序可能有很多地方都要用到这个数据库以及对应的表，如果你把名字改了，那上层都得跟着改，降低了维护性。可以发现，修改是直接覆盖最初的指令，把原来的属性和描述信息直接替换了。
mySQL创建表
WY001的博客
09-28 472
创建一个表格 -- 普通表 CREATE TABLE t1( id int(4), name VARCHAR(100) )charset=utf8; -- 姓名不为空 NOT NULL CREATE TABLE t2( id int(4), name VARCHAR(100) NOT NULL ) -- UNSIGNED,无符号，不能为负数,只能是数字 CREATE ...
数据库
weixin_30354675的博客
05-29 198
show variables like 'character_set_client';#查询字符集show databases;#列出所有的服务器上的数据库altercreate database if not exists test;#创建一个数据库drop database fk;#删除数据库show tables from test;#显示一个数据库中的表use test; cr...
#-ssm-155-mysql-医院病历管理系统-.zip
最新发布
10-31
病历库管理（所有科室的病历汇总，可以统计，管理，删除等） 缴费信息统计（所有科室的患者缴费统计） 医生 个人资料修改 预约管理 患者信息管理 患者病历管理（限于本科室），基本病历信息， X光片信息管理 患者...
MySQL面试题-20道经典面试题详解汇总（很详细）.pdf
10-11
以下是对给定的20道经典MySQL面试题中涉及知识点的详细解释： 1. **事务**： - **事务**是数据库操作的基本单位，确保一组操作要么全部成功，要么全部失败。MySQL中的事务具有ACID特性： - **原子性（Atomicity）...
#-ssm-057-mysql-医院门诊管理系统-.zip
07-22
医院门诊管理系统的设计与实现选用开发语言为JSP/JAVA,并且数据库采用MySQL,旨在实现门诊挂号、开药、收费、信息汇总处理等多个功能。医院信息管理系统可以在临床科以及门诊部或者其他行政后勤部门等系统共同工作,将...
MYSQL 创建表
binbinhxx的博客
12-23 718
1、MySQL创建表 CREATE TABLE `category` ( `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "主题id", `pid` INT(10) NOT NULL COMMENT "父id", `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字', PRIMARY KEY (`categoryid`) )ENGINE=INNODB A
在MySQL中创建表
m0_65503450的博客
03-15 1613
在 StudentInfo 表中增加一列 StuNo,添加到表的第1列，不为空,取值唯一并自动增加，显示StudentInfo 表的基本结构。将 StudentInfo表的 Speciality 列修改为School,将数据类型改为 char,可为空,默认值为“计算机学院”。将 StudentInfo 表的 City 列的类型修改为 varchar(20),并移到列 Name 之后。在数据库storeexpm中，创建Department表，显示Department表的基本结构。（这里建议把启动类型改成。
mysql之创建表
cxl0525的博客
02-16 262
数据表的设计以及ER图 例子： 使用范式检查数据表设计的合理性（我们用范式检查冗余） 注：一个关系就是一张表。 使用客户端创建数据表 使用命令行窗口创建数据表 另外，切换数据库用（use db_name）...
MySQL---创建表
qq_59708493的博客
08-17 524
注意：在数据库demo_db02创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相 应的列类型。name 字符串。id 整形。password 字符串。birthday 日期。
MySQL之创建表的基本操作
热门推荐
zhangsann_6的博客
07-26 9万+
MySQL 前面我分享了关于数据库的基本操作，例如创建和删除等，接下来分享如何在一个数据库中创建表 1、创建一个具有id、name、salary字段的表 create table tb_tmp01 ( id INT(11), name VARCHAR(25), deptId INT(11), salary FLOAT ); 2、显示该表 show tables; 3、删除该表 drop table tb_tmp01; 4、创建新表 create table tb_emp02 ( id
mysql 创建表
weixin_42861564的博客
08-04 726
create table orderfrom( no int not null auto_increment primary key comment '单号', order_date int(11) comment '日期', supplier varchar(50) comment '供应商', storehouse varchar(50) comment '仓库', remark varcha...
mysql建表
weixin_71399018的博客
03-15 2662
4、在 StudentInfo 表中增加一列 StuNo,添加到表的第1列，不为空,取值唯一并自动增加，显示StudentInfo 表的基本结构。将employee1表的sex列修改为gender ，将数据类型改为char，可为空，默认值改为‘女，显示employee1表的 基本结构。在employee表中增加一列eno，添加到表的第一列，不为空，取值唯一并自动增加，显示employee表的基本结构。将employee1表的address列修改为telephone,将数据类型改为char，可为空。
MySQL50-13-习题答案汇总1：多表连接及条件筛选技巧
综合以上两个题目，学习者可以通过练习MySQL50-13-习题及答案汇总1，不仅可以巩固对SQL语句的基本操作，还能够锻炼自己分析问题和编写查询的能力，从而更加熟练地应用MySQL数据库进行数据处理和管理。通过不断地练习...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

卡皮巴拉不躺平

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值