MySQL数据库与表的创建、修改及数据操作指南-EW帮帮网

精选专栏链接 🔗

MySQL技术笔记专栏
Redis技术笔记专栏
大模型搭建专栏
Python学习笔记专栏
深度学习算法专栏

欢迎订阅，点赞＋关注，每日精进1%，与百万开发者共攀技术珠峰

更多内容持续更新中！希望能给大家带来帮助~ 😀😀😀

1，数据库的创建和管理

1.1，创建数据库

创建数据库的三种常用方式如下，推荐使用方式三：

方式一：创建数据库

CREATE DATABASE 数据库名;

方式二：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

方式三：判断数据库是否已经存在，不存在则创建数据库（推荐）

# 如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。
CREATE DATABASE IF NOT EXISTS 数据库名;

注意：

DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的；
如果创建表时没有指明使用的字符集，则默认使用表所在的数据库的字符集；

1.2，使用数据库

① 查看当前所有的数据库：

SHOW DATABASES;

② 查看当前正在使用的数据库

SELECT DATABASE();  #使用的一个 mysql 中的全局函数

③ 查看指定库下所有的表

SHOW TABLES FROM 数据库名;

④ 查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
或者：
SHOW CREATE DATABASE 数据库名\G

⑤ 使用/切换数据库

USE 数据库名;

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作。

1.3，修改数据库

更改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集;  #比如：'gbk'、'utf8'等

1.4，删除指定数据库

方式一：

DROP DATABASE 数据库名;

方式二：（推荐）

DROP DATABASE IF EXISTS 数据库名;

2，表的创建和管理

2.1，表的创建

创建表方式一：从零创建

语法格式：

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

SQL示例：

CREATE TABLE IF NOT EXISTS myemp1(   #需要用户具备创建表的权限。
id INT,
emp_name VARCHAR(15), #使用VARCHAR来定义字符串，必须在使用VARCHAR时指明其长度。
hire_date DATE
);

创建完成后可以使用如下SQL查看表结构：

DESC myemp1;

运行结果如下：

在这里插入图片描述

表的创建方式二：基于子查询创建新表

语法格式：

CREATE TABLE 表名 AS 子查询

SQL示例：

CREATE TABLE emp1 AS SELECT * FROM employees;

CREATE TABLE myemp3
AS
# 说明：查询语句中字段的别名，可以作为新创建的表的字段的名称
SELECT e.employee_id emp_id,e.last_name lname,d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

注意：

查询语句中字段的别名，可以作为新创建的表的字段的名称
此时的查询语句可以结构比较丰富，使用前面章节讲过的各种SELECT，具体可以看文章开头的MySQL技术笔记专栏

练习：

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

CREATE TABLE employees_copy
AS
SELECT *
FROM employees;

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

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

2.2，查看数据表结构

在MySQL中创建好数据表之后，可以查看数据表的结构。MySQL支持使用 DESCRIBE 或 DESC 语句查看数据表结构，也支持使用SHOW CREATE TABLE语句查看数据表结构。

语法格式如下：

SHOW CREATE TABLE 表名\G

使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

2.3，修改表

2.3.1，添加字段

先查看myemp1的结构：

DESC myemp1;

在这里插入图片描述
给myemp1表添加字段salary：

ALTER TABLE myemp1
# (10,2)表示一共有10位（包括小数点），2表示小数点后保留2位
ADD salary DOUBLE(10,2);

添加后继续通过DESC myemp1;查看表结构：

在这里插入图片描述
注意：添加字段时，默认添加到表中的最后一个字段的位置。如果不想添加到最后一个字段位置可以显式指定。比如：

需求：添加phone_number字段到表中的第一个字段位置：

ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;

需求：添加email字段到表中的emp_name字段后面

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

在这里插入图片描述

2.3.2，修改字段

修改字段使用ALTER关键字

需求：修改emp_name字段的最大长度位25（原本是15）

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) ;

需求：修改emp_name字段的最大长度位35，并修改默认值为 ’aaa‘

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';

通过DESC myemp1;查看表结构：

在这里插入图片描述

设置默认值可以实现：当我们向表中插入数据的时候，没有指定该字段时，会自动使用此默认值。

2.3.3，重命名字段

重命名字段使用CHANGE关键字。

需求：将salary 字段改为monthly_salary

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

运行后，通过DESC myemp1;查看表结构：

在这里插入图片描述
重命名字段的同时还可以修改字段长度，比如：

ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);

2.3.4，删除字段

删除字段使用DROP关键字。

需求：删除my_email字段（或my_email列）

ALTER TABLE myemp1
DROP COLUMN my_email;

2.4，重命名表

重命名表有两种方式，分别是：RENAME方式和ALTER方式

方式一：RENAME方式

RENAME TABLE myemp1
TO myemp11;

方式二：ALTER方式

ALTER TABLE myemp2
RENAME TO myemp12;

2.5，删除表

删除表操作使用DROP关键字，不光将表结构删除掉，同时表中的数据也删除掉，释放表空间。

需求：删除表myemp12

DROP TABLE IF EXISTS myemp12;

2.6，清空表

清空表使用TRUNCATE关键字，表示清空表中的所有数据，但是表结构保留。

需求：清空employees_copy

employees_copy 表中原本有如下数据：

在这里插入图片描述

清空表：

TRUNCATE TABLE employees_copy;

再次查询表中记录：

在这里插入图片描述

3，数据操作

3.1，添加数据

可以使用 INSERT 语句向表中插入数据。有如下两种插入方式：

使用VALUES方式插入数据；
将查询结果插入到表中，可快速地从一个或多个表中向一个表中插入多行；

接下来一一介绍：

3.1.1，VALUES的方式添加

情况1： 没有指明添加的字段。

此时值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段声明时的顺序相同，否则可能报错。语法格式如下：

INSERT INTO 表名
VALUES (value1,value2,....);

emp1表结构如下：

在这里插入图片描述

插入数据SQL实例如下：

INSERT INTO emp1
VALUES (1,'Tom','2000-12-21',3400); #注意：一定要按照声明的字段的先后顺序添加

插入后执行查询：

SELECT *
FROM emp1;

查询结果如下：

在这里插入图片描述

情况2： 指明要添加的字段（推荐）

INSERT INTO emp1(id,hire_date,salary,`name`) # 插入时需要和此处的字段顺序匹配
VALUES(2,'1999-09-09',4000,'Jerry');

插入后，查询结果如下：

在这里插入图片描述
情况3： 同时插入多条记录

INSERT INTO emp1(id,NAME,salary)
VALUES
(4,'Jim',5000),   # 多条记录之间使用逗号隔开
(5,'张俊杰',5500);

插入后，查询结果如下：

在这里插入图片描述

3.1.2，将查询结果插入到表中

将查询结果插入到表中会结合SELECT查询语句。

SQL示例：

INSERT INTO emp1(id,NAME,salary,hire_date)
#查询语句
SELECT employee_id,last_name,salary,hire_date  
FROM employees
WHERE department_id IN (70,60);

注意：

查询的字段一定要与添加到的表的字段一一对应；
上述SQL中，emp1表中要添加的数据的字段的最大长度不能低于employees表中查询的字段的长度。否则会有添加不成功的风险。

如果emp1中的最大字段长度小，employees中的最大字段长度更大，可能出问题；比如：employees表中last_name字段长度为 varchar(25)，emp1表中name字段长度为 varchar(15)，执行上述SQL，如果employees表中某条记录的last_name长为20，则会报错。

3.3，修改数据

使用 UPDATE …SET…语句修改数据。语法如下：

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

需求：修改id为5的员工的hire_date为当前时间

SQL语句如下：

UPDATE emp1
SET hire_date = CURDATE()
WHERE id = 5;

需求：修改4号员工的薪资为6000，hire_date为当前时间

UPDATE emp1
SET hire_date = CURDATE(),salary = 6000
WHERE id = 4;

需求：将表中姓名中包含字符a的提薪20%

UPDATE emp1
SET salary = salary * 1.2
WHERE NAME LIKE '%a%';

注意：

UPDATE语句可以一次更新多条数据。如果省略 WHERE 子句，则表中的所有数据都将被更新；

3.2，删除数据

使用 DELETE FROM 删除数据。语法如下：

DELETE FROM table_name [WHERE <condition>];

需求：删除表中id为1的员工信息

SQL语句如下：

DELETE FROM emp1
WHERE id = 1;

3.3，对比TRUNCATE TABLE 和 DELETE FROM

对比二者之前，需要了解数据库中的 COMMIT命令和 ROLLBACK命令：

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

TRUNCATE TABLE 和 DELETE FROM 的相同点：

都可以实现对表中所有数据的删除，同时保留表结构。

TRUNCATE TABLE 和 DELETE FROM 的不同点：

TRUNCATE TABLE： 一旦执行此操作，表数据全部清除。同时，数据是不可以回滚的；
DELETE FROM： 一旦执行此操作，表数据可以全部清除（不带WHERE的DELETE FROM语句）。同时，数据是可以实现回滚的;

原因是：

TRUNCATE TABLE属于DDL数据定义语言，DELETE FROM属于DML数据操作语言；
DDL数据定义语言一旦执行就不可回滚（因此执行DDL操作要慎重）；
DML数据操作语言，默认情况下，一旦执行，也是不可回滚的，但是如果在执行DML之前，执行了SET autocommit = FALSE，则执行的DML操作就可以实现回滚；
SET autocommit = FALSE指令对DDL操作无效。因为在执行完DDL操作之后，一定会执行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE指令影响。

SQL演示：DELETE FROM

① 先提交一次

COMMIT;

② 查看表中数据

SELECT *
FROM emp1;

运行结果如下：

在这里插入图片描述
③ 禁用自动提交

SET autocommit = FALSE;

④ 执行 TRUNCATE TABLE

TRUNCATE TABLE emp1;

查看表中数据发现删除成功：

在这里插入图片描述
⑤ 回滚

ROLLBACK;

回滚后查看表中数据发现数据回滚成功，回滚到最近一次COMMIT之后：

在这里插入图片描述

SQL演示：TRUNCATE TABLE

① 先提交一次

COMMIT;

② 查看表中数据

SELECT *
FROM emp1;

运行结果如下：

在这里插入图片描述
③ 禁用自动提交

SET autocommit = FALSE;

④ 执行DELETE FROM

DELETE FROM emp1;

查看表中数据发现删除成功：

在这里插入图片描述
⑤ 回滚

ROLLBACK;

回滚后查看表中数据发现数据回滚后数据未恢复：

在这里插入图片描述

3.4，MySQL8新特性：计算列

计算列简单来说就是某一列的值是通过别的列计算得来的。

例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE为例进行讲解。

需求：定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算 a+b 的值。

首先创建测试表tb1：

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL # 字段c即为计算列
);

插入一条记录：

# 只指明了字段a和字段b
INSERT INTO tb1(a,b) VALUES (100,200);

查看数据：

SELECT * FROM tb1;

运行结果如下，c字段自动计算出结果：

在这里插入图片描述
更新字段值：

UPDATE tb1 SET a = 500;

查看数据：

SELECT * FROM tb1;

运行结果如下，c字段自动更新结果：

在这里插入图片描述

MySQL数据库与表的创建、修改及数据操作指南

MySQL数据库与表的创建、修改及数据操作指南

1，数据库的创建和管理

1.1，创建数据库

1.2，使用数据库

1.3，修改数据库

1.4，删除指定数据库

2，表的创建和管理

2.1，表的创建

2.2，查看数据表结构

2.3，修改表

2.3.1，添加字段

2.3.2，修改字段

2.3.3，重命名字段

2.3.4，删除字段

2.4，重命名表

2.5，删除表

2.6，清空表

3，数据操作

3.1，添加数据

3.1.1，VALUES的方式添加

3.1.2，将查询结果插入到表中

3.3，修改数据

3.2，删除数据

3.3，对比TRUNCATE TABLE 和 DELETE FROM

3.4，MySQL8新特性：计算列

网站公告

今日签到

热门文章

最新发布