从零开始学习数据库 day1 DDL

无限大6
267 阅读9分钟

在数据库管理中，DDL（数据定义语言）是用于定义和管理数据库结构的关键部分。今天，我们将详细探讨DDL的概念、常用语句及其在MySQL中的应用。

一、DDL是什么？

DDL（Data Definition Language）是用于定义数据库对象（如表、视图、索引等）的语言。它允许用户创建、修改和删除数据库结构，而不直接操作数据本身。DDL操作通常是不可逆的，即一旦执行，数据结构的改变通常无法通过简单的命令撤销。

DDL的主要功能包括：
  • 创建数据库和表：定义数据存储的结构。
  • 修改表结构：添加、删除或修改表中的列。
  • 删除数据库和表：完全移除数据结构和其中的数据。
  • 定义约束：确保数据的完整性和一致性。

二、数据类型

在创建表时，选择合适的数据类型是至关重要的。以下是MySQL中常用的数据类型汇总表：

数据类型描述
整值类型
TINYINT占用1字节，范围为-128到127或0到255。
SMALLINT占用2字节，范围为-32,768到32,767或0到65,535。
MEDIUMINT占用3字节，范围为-8,388,608到8,388,607或0到16,777,215。
INT或INTEGER占用4字节，范围为-2,147,483,648到2,147,483,647或0到4,294,967,295。
BIGINT占用8字节，范围为-9,223,372,036,854,775,808到9,223,372,036,854,775,807。
浮点数类型
FLOAT单精度浮点数，存储4字节。
DOUBLE双精度浮点数，存储8字节。
定点数类型
DEC或DECIMAL用于高精度存储，适合金融计算。
位类型
BIT存储位值，适合布尔类型的数据。
字符串类型
CHAR定长字符串，最大长度为255。
VARCHAR变长字符串，最大长度为65,535。
BINARY存储二进制数据，定长。
VARBINARY存储变长二进制数据。
TEXT用于存储长文本，最大长度为65,535。
BLOB用于存储二进制大对象，最大长度为65,535。
ENUM枚举类型，限制值的取值范围。
SET集合类型，可以包含多个值。
日期类型
DATE存储日期，格式为'YYYY-MM-DD'。
DATETIME存储日期和时间，格式为'YYYY-MM-DD HH:MM:SS'。
TIMESTAMP自动记录时间戳，格式为'YYYY-MM-DD HH:MM:SS'。
TIME存储时间，格式为'HH:MM:SS'。
YEAR存储年份，格式为'YYYY'。

三、字符集详细介绍

  1. 概述
    字符集是数据库中用于表示字符的编码系统。在需要处理文本数据的应用中，选择合适的字符集显得尤为重要。字符集的选择直接影响数据的正确存储和显示，特别是在多语言环境中，错误的字符集可能导致数据损坏或显示异常。因此，掌握字符集的基本概念和选择原则是成功管理数据库的重要一环。

  2. Unicode
    Unicode是一种字符编码标准，旨在为世界上几乎所有的语言和字符提供统一的编码方案。它通过为每个字符分配一个唯一的代码点，使得不同语言的文本可以在计算机中通用。UTF-8是Unicode的常用编码形式，它将字符编码为1到4个字节不等，能够兼容多种语言和符号。由于其灵活性和广泛的支持，UTF-8已成为Web和数据库中最常用的字符编码。

  3. 汉字编码
    在MySQL中，处理汉字时通常使用UTF-8和GBK编码。UTF-8能够支持多种语言，适用于需要国际化的应用，而GBK主要用于简体中文，能够处理大多数汉字字符。选择适当的编码对于中文数据的存储和查询至关重要，因为不正确的编码可能导致字符显示为乱码。

  4. 字符集选择
    在选择字符集时，需考虑应用程序的需求、数据的语言、以及未来可能的扩展性。如果应用需要处理多种语言或在国际化背景下运行，推荐使用UTF-8或其升级版UTF-8-4（utf8mb4）。UTF-8不仅支持常见字符，还能够处理表情符号和各种特殊字符，这使得它在现代应用中成为首选。相反，如果应用仅限于处理特定语言（如仅中文），则可以根据实际情况考虑使用GBK或其他更适合的编码。

  5. MySQL字符集
    MySQL支持多种字符集和排序规则，以下是一些常见的字符集及其特点：

    • utf8mb4：这种字符集是UTF-8的扩展，可以支持所有Unicode字符，包括表情符号和特殊符号。它是现代MySQL应用的推荐选项，尤其适合包含多种语言字符的网页和应用。

    • latin1：这个字符集是一个单字节字符集，适用于西欧和北美语言，字符范围有限，通常只覆盖ASCII字符和一些西欧字符。这个字符集在处理英文文本时效果很好，但在多语言支持方面表现欠佳。

    • utf8：虽然名称包含“UTF-8”，但在MySQL中实际只支持Unicode字符的基本多语言平面（BMP），无法处理某些字符，例如许多表情符号。因此，推荐使用utf8mb4来确保全面的Unicode支持。

    • gbk：主要用于简体中文，适合处理中文字符的存储和检索。GBK对汉字的支持更全面，但相对UTF-8，可能在多语言内容处理方面有所不足。

    在配置MySQL数据库的字符集时，应考虑字符集与应用程序的兼容性，以及数据的持久性和可扩展性。使用合适的字符集可以确保在数据的存储、检索和展示过程中的准确性和一致性。

四、表类型选择

  1. 存储引擎概述：存储引擎是MySQL中用于管理数据存储和检索的组件。不同的存储引擎具有不同的特性和适用场景。

  2. 各种存储引擎基本特性：

    • InnoDB：
      • 支持事务处理，确保数据的一致性。
      • 支持外键，适合复杂的数据关系。
      • 提供行级锁定，适合高并发应用。
    • MyISAM：
      • 不支持事务，适合读操作频繁的应用。
      • 提供表级锁定，适合低并发的场景。
      • 支持全文索引，适合搜索引擎。
    • MEMORY：
      • 将数据存储在内存中，速度快，但数据在重启后丢失。
      • 适合临时数据存储和快速访问。
    • CSV：
      • 将数据存储为CSV格式，便于数据导入导出。
      • 不支持索引，适合简单的数据存储需求。

  3. 如何选择存储引擎：选择存储引擎时，应根据应用需求考虑以下因素：

    • 事务支持：如果应用需要事务处理，选择InnoDB。
    • 并发处理能力：对于高并发应用，InnoDB是更好的选择。
    • 数据安全性：考虑数据的持久性和安全性，InnoDB提供更强的数据保护。
    • 性能需求：根据读写操作的比例选择合适的存储引擎。

五、约束

约束用于确保数据的完整性和准确性，常见的约束包括：

  1. 主键约束：

    id INT PRIMARY KEY AUTO_INCREMENT
    • 主键字段唯一且不为空，通常用于标识记录。

  2. 外键约束：

    FOREIGN KEY (student_id) REFERENCES students(id)
    • 用于建立表之间的关系，确保引用的完整性。

  3. 唯一约束：

    email VARCHAR(100) UNIQUE
    • 确保字段值唯一，不允许重复。

  4. 检查约束：

    CHECK (age >= 0)
    • 用于限制字段值的范围或条件。

  5. 默认值：

    status VARCHAR(10) DEFAULT 'active'
    • 为字段提供默认值。

  6. 非空约束：

    name VARCHAR(100) NOT NULL
    • 确保字段不能为空。

  7. 查看约束：

    SHOW CREATE TABLE students;
    • 查看表的创建语句及约束信息。

六、DDL的主要语句及实例

以下是DDL的主要命令，结合实例和图表进行分析：

  1. CREATE：创建数据库或表。

    -- 创建数据库
CREATE DATABASE school;

-- 创建表
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    grade VARCHAR(10)
);

    图示：

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

在这里插入图片描述

分析：上述代码首先创建了一个名为school的数据库，然后在该数据库中创建了一个名为students的表，表中包括id、name、age和grade四个字段。

  1. ALTER：修改已有表的结构。

    -- 添加新列
ALTER TABLE students ADD COLUMN email VARCHAR(100);

-- 修改列的数据类型
ALTER TABLE students MODIFY COLUMN age TINYINT;

-- 删除列
ALTER TABLE students DROP COLUMN grade;

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

    分析：通过ALTER TABLE语句，我们可以在students表中添加、修改或删除列。上面的例子展示了如何添加email列，修改age列的数据类型，以及删除grade列。

  2. DROP：删除数据库或表。

    -- 删除表
DROP TABLE students;

-- 删除数据库
DROP DATABASE school;

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

    在这里插入图片描述

    分析：DROP语句用于删除数据库或表。执行DROP TABLE students将彻底删除students表及其所有数据，而DROP DATABASE school将删除整个数据库。

  3. 数据类型和约束：在创建表时，可以为字段定义数据类型和约束条件。

    CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL UNIQUE,
    credits INT CHECK (credits > 0)
);

    图示：

    在这里插入图片描述

    分析：在courses表中，course_name字段被定义为唯一，确保没有重复的课程名称，而credits字段使用了CHECK约束，确保其值大于0。

七、总结

DDL是数据库管理中不可或缺的一部分，理解其基本操作和语法是学习数据库的基础。通过今天的学习，我们深入探讨了DDL的概念、主要语句及其在MySQL中的应用实例。掌握这些知识后，将能够创建和管理数据库结构，为后续的数据操作打下坚实的基础。

接下来，我们将继续深入学习DML（数据操作语言），以便更好地管理和操作数据库中的数据。

avatar
文章
阅读
粉丝