【MySQL】2.深入理解MySQL：数据类型、DDL与DML语句全解析

数据类型、DDL（数据定义语言）和DML（数据操纵语言）语句构成了数据管理和操作的核心。从精心选择数据类型以优化存储和查询性能，到运用DDL语句设计和调整数据库结构，再到使用DML语句对数据进行日常的增删改查，每个环节都至关重要。本文将带你深入探索MySQL的这些基础而强大的概念，为你的数据操作之旅提供一份详尽的指南。

一.数据类型

MySQL支持的数据类型很多，每种数据类型都有其特定的使用场景。以下是MySQL支持的一些主要数据类型及其适用场景：

  1. 整型（Integer）：
    TINYINT：适用于存储非常小的整数值，如状态标识（0和1）。
    SMALLINT：适用于存储较小的整数值，如较小范围的ID。
    MEDIUMINT：适用于存储中等大小的整数值。
    INT 或 INTEGER：最常用的整数类型，适用于存储一般大小的整数值，如用户ID。
    BIGINT：适用于存储较大的整数值，如大量的计数或ID。

  2. 浮点数和双精度（Floating-Point and Double）：
    FLOAT：适用于需要处理小数的数值场景，如价格计算。
    DOUBLE 或 DOUBLE PRECISION：适用于需要更高精度的数值场景，如科学计算。

  3. 定点数（Fixed-Point）：
    DECIMAL 或 NUMERIC：适用于需要固定精度的小数值，如货币计算。

  4. 字符串类型：
    CHAR：适用于存储短的、定长的字符串，如性别、国家代码。
    VARCHAR：适用于存储可变长度的字符串，如用户名、文章标题。
    TEXT：适用于存储大量文本，如文章内容、评论。

  5. 二进制字符串：
    BINARY：适用于存储二进制数据的短字符串。
    VARBINARY：适用于存储可变长度的二进制字符串。
    BLOB：适用于存储二进制大对象，如图片、音频文件。

  6. 日期和时间类型：
    DATE：适用于存储日期，如生日、纪念日。
    TIME：适用于存储时间，如预约时间、营业时间。
    DATETIME：适用于存储日期和时间的组合，如事件的开始和结束时间。
    TIMESTAMP：类似于DATETIME，但时间精度到秒，通常用于记录数据的最后修改时间。

  7. 枚举（ENUM）：
    适用于存储预定义集合中的一个值，如状态（‘active’, ‘inactive’, ‘pending’）。

  8. 集合（SET）：
    适用于存储多个预定义值的组合，如用户权限（‘create’, ‘read’, ‘update’, ‘delete’）。

  9. 空间数据类型：
    适用于存储地理空间数据，如地图应用中的点、线、面。

  10. JSON类型：
    适用于存储JSON格式的数据，如配置信息、用户偏好设置。

选择数据类型时，应考虑字段将要存储的数据类型、大小、精度以及如何使用这些数据。正确的数据类型选择可以提高存储效率、查询性能和数据的准确性。

二.DDL语句

在MySQL中，DDL（Data Definition Language，数据定义语言）语句用于定义和更改数据库的结构。以下是一些常用的MySQL DDL语句，包括它们的说明、格式、示例和注释：

1. CREATE 创建

说明：用于创建新的数据库、表、视图等。

格式：

CREATE DATABASE database_name;

CREATE TABLE table_name (
  column1 datatype constraints,
  column2 datatype constraints,
  ...
);

CREATE VIEW view_name AS SELECT column_list FROM table_name WHERE condition;

示例：

CREATE DATABASE mydatabase; -- 创建数据库 mydatabase

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(50) NOT NULL
); -- 创建用户表

CREATE VIEW active_users AS
SELECT id, username
FROM users
WHERE last_login > DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 创建视图显示最近一个月登录的用户

2. ALTER 修改

说明：用于修改现有数据库对象的结构，如添加或删除列、修改数据类型等。

格式：

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

ALTER TABLE table_name DROP COLUMN column_name;

示例：

ALTER TABLE users ADD email VARCHAR(100); -- 在用户表中添加 email 列

ALTER TABLE users MODIFY COLUMN password VARCHAR(100); -- 修改密码列的数据类型

ALTER TABLE users DROP COLUMN email; -- 从用户表中删除 email 列

3. DROP 删除

说明：用于删除数据库对象，如数据库、表、视图等。

格式：

DROP DATABASE IF EXISTS database_name;

DROP TABLE IF EXISTS table_name;

DROP VIEW IF EXISTS view_name;

示例：

DROP DATABASE IF EXISTS olddatabase; -- 如果存在，则删除数据库 olddatabase

DROP TABLE IF EXISTS users; -- 如果存在，则删除用户表

DROP VIEW IF EXISTS active_users; -- 如果存在，则删除视图 active_users

4. TRUNCATE 清空

说明：用于删除表中的所有行，但保留表结构。

格式：

TRUNCATE TABLE table_name;

示例：

TRUNCATE TABLE users; -- 清空用户表中的所有数据

5. RENAME 重命名

说明：用于修改数据库对象的名称。

格式（MySQL 5.6.1+）：

RENAME TABLE old_table_name TO new_table_name;

ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;

示例：

RENAME TABLE users_old TO users_new; -- 将表 users_old 重命名为 users_new

ALTER TABLE users CHANGE username user_name VARCHAR(50); -- 将列 username 重命名为 user_name

6. INDEX 索引

说明：用于管理表上的索引，以提高查询性能。

格式：

CREATE INDEX index_name ON table_name (column_name);

DROP INDEX index_name ON table_name;

示例：

CREATE INDEX idx_user_name ON users (user_name); -- 在用户表的 user_name 列上创建索引

DROP INDEX idx_user_name ON users; -- 删除用户表上的 idx_user_name 索引

7. PRIMARY KEY 主键

说明：用于定义表的主键，确保列的唯一性。

格式：

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

示例：

ALTER TABLE users ADD PRIMARY KEY (id); -- 将 id 列设为主键

8. FOREIGN KEY 外键

说明：用于定义表的外键约束，维护表之间的数据一致性。

格式：

ALTER TABLE child_table ADD CONSTRAINT fk_name
  FOREIGN KEY (child_column) REFERENCES parent_table (parent_column);

示例：

ALTER TABLE orders ADD CONSTRAINT fk_users
  FOREIGN KEY (user_id) REFERENCES users (id); -- 将订单表的 user_id 列设为外键，引用用户表的 id 列

9. CHECK 检查

说明：用于定义列值的限制条件。

格式（MySQL 8.0.16+）：

ALTER TABLE table_name ADD CONSTRAINT check_name CHECK (expression);

示例：

ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price > 0); -- 确保价格列的值大于0

10. COMMENT 注释

说明：用于为数据库对象添加注释。

格式：

ALTER TABLE table_name ADD COMMENT 'table comment';

ALTER TABLE table_name MODIFY column_name datatype COMMENT 'column comment';

示例：

ALTER TABLE users ADD COMMENT 'This table contains user data'; -- 为用户表添加注释

ALTER TABLE users MODIFY user_name VARCHAR(50) COMMENT 'The name of the user'; -- 为 user_name 列添加注释

注释：DDL操作通常需要数据库的写权限，并且在执行这些操作时要小心，因为它们可能会对数据库的结构和其中的数据产生不可逆的更改。在执行DDL语句之前，建议备份相关数据。

三.DML语句

MySQL中的DML语句主要用于对数据库中的数据执行添加、修改、删除和查询操作。DML语句可以分为单表操作和多表操作两类。

1.单表操作

单表操作涉及对单个数据库表的直接操作。

1. SELECT 查询

说明：从表中检索数据，可以指定条件、排序和限制结果集。

格式：

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 ASC|DESC, ...
LIMIT offset, count;

示例：

SELECT * FROM users WHERE age > 30 ORDER BY last_name DESC LIMIT 10;

注释：SELECT * 表示选择所有列，ASC 表示升序，DESC 表示降序。

2. INSERT 插入

说明：向表中添加新的数据行。

格式：

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

示例：

INSERT INTO users (first_name, last_name, email) VALUES ('Alice', 'Smith', 'alice@example.com');

注释：如果列名未指定，则默认插入所有列，列的顺序和值必须匹配。

3. UPDATE 更新

说明：更新表中的现有数据。

格式：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

示例：

UPDATE users SET email = 'newalice@example.com' WHERE first_name = 'Alice';

注释：WHERE 子句用于指定哪些行将被更新，如果省略，将更新所有行。

4. DELETE 删除

说明：从表中删除数据。

格式：

DELETE FROM table_name WHERE condition;

示例：

DELETE FROM users WHERE last_name = 'Smith';

注释：与UPDATE 一样，WHERE 子句用于指定哪些行将被删除，如果省略，将删除所有行。

2.多表操作

多表操作涉及对两个或更多表的联合操作。

1. SELECT 联合查询

说明：从多个表中检索数据，通常用于执行连接操作。

格式：

SELECT column1, column2, ...
FROM table1
JOIN_TYPE table2 ON join_condition
WHERE condition
ORDER BY column1 ASC|DESC, ...
LIMIT offset, count;

示例：

SELECT users.first_name, orders.order_date
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.order_date > '2023-01-01';

注释：JOIN_TYPE 可以是 INNER JOIN, LEFT JOIN, RIGHT JOIN, 或 FULL OUTER JOIN。

2. UPDATE 多表更新

说明：同时更新多个表中的数据。

格式（MySQL不支持直接的多表更新，但可以使用多个UPDATE语句或存储过程）：

示例：

UPDATE users, orders SET users.last_login = NOW() WHERE users.id = orders.user_id AND orders.order_date > '2023-01-01';

注释：这种写法在MySQL中不推荐使用，因为它可能会导致不可预测的更新。通常应该避免跨表更新。

3. DELETE 多表删除

说明：同时从多个表中删除数据。

格式（同样，MySQL不支持直接的多表删除）：

示例：

DELETE users, orders FROM users INNER JOIN orders ON users.id = orders.user_id WHERE orders.order_date < '2022-01-01';

注释：与多表更新一样，这种写法不推荐使用，因为它可能会导致数据不一致。通常应该在一个事务中使用多个DELETE语句。

在使用DML语句时，务必注意以下几点：

  • 使用WHERE子句精确指定要操作的记录。
  • 在执行可能影响大量数据的操作之前，考虑先对数据进行备份。
  • 使用事务来确保数据的一致性，特别是在执行多表更新或删除操作时。
  • 考虑性能影响，尤其是在涉及大量数据或复杂连接操作的情况下。

3.JOIN_TYPE

SQL中的JOIN操作用于将两个或多个表中的行结合起来，基于相关的列之间的关系。以下是INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL OUTER JOIN的区别及其适用场景：

1. INNER JOIN

说明：内连接，只有两个表中都有匹配的行才会被选取。

适用场景：当你需要查询两个表中都有的、匹配的数据时使用。

示例：

SELECT *
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

2. LEFT JOIN (或 LEFT OUTER JOIN)

说明：左连接，结果集包括左表中的所有行，即使右表中没有匹配的行。右表中没有匹配的行将用NULL填充。

适用场景：当你需要查询左表的所有数据，并且对于关联的右表数据不关心是否完整时。

示例：

SELECT *
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

3. RIGHT JOIN (或 RIGHT OUTER JOIN)

说明：右连接，与左连接相反，结果集包括右表中的所有行，即使左表中没有匹配的行。左表中没有匹配的行将用NULL填充。

适用场景：当你需要查询右表的所有数据，并且对于关联的左表数据不关心是否完整时。

示例：

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

4. FULL OUTER JOIN

说明：全外连接，结果集包括两个表中所有匹配的行加上两个表中不匹配的行。如果某一侧没有匹配，那么该侧的结果将用NULL填充。

适用场景：当你需要查询两个表中所有数据，无论它们是否匹配时。

示例：

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.common_field = table2.common_field;

注意：并非所有数据库系统都支持FULL OUTER JOIN。在MySQL中，可以使用以下的左连接和右连接的组合来模拟全外连接：

(SELECT * FROM table1 LEFT JOIN table2 ON table1.common_field = table2.common_field)
UNION
(SELECT * FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field)
WHERE table1.common_field IS NULL OR table2.common_field IS NULL;

选择使用哪种类型的JOIN取决于你的需求和你想从数据库查询中获得的数据。通常，INNER JOIN是最常用的，因为它只返回两个表中都有的数据，而LEFT JOIN和RIGHT JOIN则可以返回一个表的全部数据，配合另一个表中相关的数据。FULL OUTER JOIN则更为全面，但使用时需要确保它符合你的查询逻辑。

四.结语

通过本文的全面解析，你现在应该对MySQL的数据类型选择、DDL语句的结构变更能力以及DML语句的数据操纵技巧有了深刻的理解。掌握了这些知识，你将能够在数据库设计和操作中做出更明智的决策，无论是进行精细的数据查询、构建高效的数据模型，还是实施数据的增删改操作。记住，每一行SQL代码都可能对数据的完整性和性能产生重大影响，因此，始终以谨慎和专业的态度对待数据库操作是非常重要的。随着你对MySQL的进一步探索，这些基础概念将继续作为你坚实的后盾，助你在数据管理的道路上越走越远。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/594447.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

详解基于 RAG 的 txt2sql 全过程

详解基于 RAG 的 txt2sql 全过程

前文 本文使用通义千问大模型和 ChromaDB 向量数据库来实现一个完整的 text2sql 的项目&#xff0c;并基于实际的业务进行效果的展示。 准备 在进行项目之前需要准备下面主要的内容&#xff1a; python 环境通义千问 qwen-max 模型的 api-keyChromaDB 向量数据库acge_text_…
阅读更多...
一款 NodeJS 版本管理工具 NVM (Windows)

一款 NodeJS 版本管理工具 NVM (Windows)

一、简介 Node Version Manager&#xff08;NVM&#xff09;是一种用于管理多个 NodeJS 版本的工具。在日常工作中&#xff0c;我们可能同时在进行多个不同的项目开发&#xff0c;每个项目的需求不同&#xff0c;依赖与不同版本的NodeJS 运行环境。这种情况下&#xff0c;维护…
阅读更多...
数据处理学习笔记9

数据处理学习笔记9

一些其他的函数 “Resize”和“Reshape”的区别主要在于它们对数组元素数量和形状的处理方式不同&#xff0c;以下是详细介绍&#xff1a; “Resize”通常会改变数组的元素数量&#xff0c;在放大数组形状时会用0补全新增的元素&#xff0c;而在缩小数组形状时会丢弃多余的元素…
阅读更多...
一款AI工作流项目：phidatahq/phidata

一款AI工作流项目：phidatahq/phidata

一款AI工作流项目&#xff1a;phidatahq/phidata 构建和测试功能强大的 AI 工作流程。该项目提供了一个工作流平台,可以结合大型语言模型(LLM)和各种工具,扩展模型的实用性和应用范围。[1][4][5] 开发各种 AI 助手应用,如客服聊天机器人、数据分析工具、研究助手等。phidata 提…
阅读更多...
Golang | Leetcode Golang题解之第72题编辑距离

Golang | Leetcode Golang题解之第72题编辑距离

题目&#xff1a; 题解&#xff1a; func minDistance(word1 string, word2 string) int {m, n : len(word1), len(word2)dp : make([][]int, m1)for i : range dp {dp[i] make([]int, n1)}for i : 0; i < m1; i {dp[i][0] i // word1[i] 变成 word2[0], 删掉 word1[i], …
阅读更多...
LabVIEW波浪发电平台浮筒取能效率数据采集系统

LabVIEW波浪发电平台浮筒取能效率数据采集系统

LabVIEW波浪发电平台浮筒取能效率数据采集系统 随着化石能源的逐渐减少以及能源价格的上升&#xff0c;寻找可替代的、可再生的、清洁的能源成为了世界各国的共识。波浪能作为一种重要的海洋能源&#xff0c;因其巨大的潜力和清洁性&#xff0c;近年来受到了广泛关注。开发了一…
阅读更多...
32 OpenCV Harris角点检测

32 OpenCV Harris角点检测

文章目录 cornerHarris 算子示例 角点检测 cornerHarris 算子 void cv::cornerHarris ( InputArray src,OutputArray dst,int blockSize,int ksize,double K,int borderType BORDER_DEFAULT) src:待检测Harris角点的输入图像&#xff0c;图像必须是CV 8U或者CV 32F的单通道…
阅读更多...
玩comfyui踩过的坑之使用ComfyUI_Custom_NODES_ALEKPET翻译组件问题

玩comfyui踩过的坑之使用ComfyUI_Custom_NODES_ALEKPET翻译组件问题

环境&#xff1a; 秋叶安装包&#xff0c;安装ComfyUI_Custom_NODES_ALEKPET组件或者直接下载网盘中的包&#xff0c;直接解压包到comfyui根目录/custom_nodes/&#xff0c;重启后&#xff0c;按指导文件操作。 注意&#xff1a;网盘指导包中有配置好的流程json文件&#xff0…
阅读更多...
【源码】 频裂变加群推广强制分享引流源码

【源码】 频裂变加群推广强制分享引流源码

视频裂变加群推广强制分享引流源码&#xff0c;用户达到观看次数后需要分享给好友或者群,好友必须点击推广链接后才会增加观看次数。 引导用户转发QV分享,达到快速裂变引流的效果&#xff01; 视频裂变推广程序&#xff0c;强制分享链接&#xff0c;引导用户转发&#xff0c;…
阅读更多...
prometheus搭建

prometheus搭建

1.prometheus下载 下载地址：Download | Prometheus 请下载LTS稳定版本 本次prometheus搭建使用prometheus-2.37.1.linux-amd64.tar.gz版本 2.上传prometheus-2.37.1.linux-amd64.tar.gz至服务器/opt目录 CentOS7.9 使用命令rz -byE上传 3.解压缩prometheus-2.37.1.linux…
阅读更多...
VscodeC/C++环境配置

VscodeC/C++环境配置

引言 vscode是一款非常好用的编辑器&#xff0c;集成了大量的插件&#xff0c;具有很高的自由度&#xff0c;因此广受大家的喜爱。但是他本身是不带编译器的&#xff0c;因此如果要使用vscode来编译C/C程序的话&#xff0c;我们需要额外安装编译器并且为vscode配上环境。 编译…
阅读更多...
Docker 入门与实践：从零开始构建容器化应用环境

Docker 入门与实践：从零开始构建容器化应用环境

Docker 一、docker常用命令docker ps 格式化输出Linux设置命令别名 二、数据卷相关命令挂载到默认目录&#xff08;/var/lib/docker&#xff09;挂载到本地目录 三、自定义镜像Dockerfile构建镜像的命令 四、网络自定义网络 五、DockerCompose相关命令 一、docker常用命令 dock…
阅读更多...
Python | Leetcode Python题解之第71题简化路径

Python | Leetcode Python题解之第71题简化路径

题目&#xff1a; 题解&#xff1a; class Solution:def simplifyPath(self, path: str) -> str:names path.split("/")stack list()for name in names:if name "..":if stack:stack.pop()elif name and name ! ".":stack.append(name)re…
阅读更多...
ThreeJS:光线投射与3D场景交互

ThreeJS:光线投射与3D场景交互

光线投射Raycaster 光线投射详细介绍可参考&#xff1a;https://en.wikipedia.org/wiki/Ray_casting&#xff0c; ThreeJS中&#xff0c;提供了Raycaster类&#xff0c;用于进行鼠标拾取&#xff0c;即&#xff1a;当三维场景中鼠标移动时&#xff0c;利用光线投射&#xff0c;…
阅读更多...
点亮一个LED

点亮一个LED

新建项目 #include <REGX52.H>void main() {P2 0xFE;while(1){} }调整字体大小 在编译之前要勾选一个东西,不然scp读取不了 去stc-isp中烧录进51单片机 两个地方要勾选,一个是单片机型号,一个是串口号,我的单片机型号不是江科大视频里面那个型号,所以不能按视频里面来选…
阅读更多...
【数据结构（邓俊辉）学习笔记】列表04——排序器

【数据结构（邓俊辉）学习笔记】列表04——排序器

文章目录 0. 统一入口1. 选择排序1.1 构思1.2 实例1.3 实现1.4 复杂度 2. 插入排序2.1 构思2.2 实例2.3 实现2.4 复杂度分析2.5 性能分析 3. 归并排序3.1 二路归并算法3.1.1 二路归并算法原理3.1.2 二路归并算法实现3.1.3 归并时间 3.2 分治策略3.2.1 实现3.2.2 排序时间 4. 总…
阅读更多...
学习笔记：【QC】Android Q - IMS 模块

学习笔记：【QC】Android Q - IMS 模块

一、IMS init 流程图 二、IMS turnon 流程图 三、分析说明 1、nv702870 不创建ims apn pdp 2、nv702811 nv702811的时候才创建ims pdp&#xff1a; ims pdp 由ims库发起&#xff0c;高通没有开放这部分代码&#xff1a; 10-10 11:45:53.027 943 943 E Diag_Lib: [IMS_D…
阅读更多...
只用语音能训练出AI大模型吗？就像训练会说话但不识字的人一样

只用语音能训练出AI大模型吗？就像训练会说话但不识字的人一样

AI语音对话技术通常是基于语音识别和自然语言处理&#xff08;NLP&#xff09;的。在这个过程中&#xff0c;语音首先被识别成文字&#xff0c;然后NLP技术对这些文字进行处理&#xff0c;生成回应。然而&#xff0c;我们是否可以直接训练一个“文盲”大模型&#xff0c;即只用…
阅读更多...
45. UE5 RPG 增加角色受击反馈

45. UE5 RPG 增加角色受击反馈

在前面的文章中&#xff0c;我们实现了对敌人的属性的初始化&#xff0c;现在敌人也拥有的自己的属性值&#xff0c;技能击中敌人后&#xff0c;也能够实现血量的减少。 现在还需要的就是在技能击中敌人后&#xff0c;需要敌人进行一些击中反馈&#xff0c;比如敌人被技能击中后…
阅读更多...
深度学习中的注意力机制二（Pytorch 16）

深度学习中的注意力机制二（Pytorch 16）

一 Bahdanau 注意力 通过设计一个 基于两个循环神经网络的编码器‐解码器架构&#xff0c;用于序列到序列学习。具体来说&#xff0c;循环神经网络编码器将长度可变的序列转换为固定形状的上下文变量&#xff0c;然后循环神经网络 解码器根据生成的词元和上下文变量按词元生成…
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站