掌握SQL以及MySQL的基本操作（二）

小厂永远得不到的男人
121 阅读6分钟

关于DDL和SQL命名规定的详细知识文章

1. DDL概述

DDL（Data Definition Language，数据定义语言）是SQL的一部分，专门用于定义和管理数据库的结构。DDL主要关注数据库的结构和元数据（容器），而不涉及具体的数据操作。通过DDL，用户可以创建、修改和删除数据库对象，如数据库、表、索引和视图等。

1.2 DDL关键字

DDL提供了几个关键的SQL命令来管理数据库结构：

  • CREATE: 用于创建数据库、表、索引、视图等。

    sql
浅色版本
-- 创建数据库
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 创建表
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

-- 创建索引
CREATE INDEX index_name ON table_name (column_name);

-- 创建视图
CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

  • ALTER: 用于修改数据库对象的结构，如修改表结构、添加列、删除列等。

    sql
浅色版本
-- 添加列
ALTER TABLE table_name ADD column_name datatype;

-- 修改列的数据类型
ALTER TABLE table_name MODIFY column_name new_datatype;

-- 修改列的名称
ALTER TABLE table_name CHANGE old_column_name new_column_name new_datatype;

-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;

-- 添加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

-- 添加外键
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES referenced_table_name (referenced_column_name);

-- 删除外键
ALTER TABLE table_name DROP FOREIGN KEY fk_name;

-- 添加唯一约束
ALTER TABLE table_name ADD UNIQUE (column_name);

-- 添加默认值
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;

-- 删除默认值
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

  • DROP: 用于删除数据库对象，如删除表、索引等。

    sql
浅色版本
-- 删除数据库
DROP DATABASE database_name;

-- 删除表
DROP TABLE table_name;

-- 删除索引
DROP INDEX index_name ON table_name;

-- 删除视图
DROP VIEW view_name;

2. SQL命名规定和规范

为了确保数据库的一致性和可维护性，SQL命名需要遵循一定的规定和规范。

规定（硬指标）

  1. 长度限制：

    • 数据库名、表名不得超过30个字符。
    • 变量名限制为29个字符。

  2. 字符集：

    • 名称只能包含A-Z、a-z、0-9和下划线（_），共63个字符。
    • 名称不能以数字开头。

  3. 空格：

    • 数据库名、表名、字段名等对象名中间不能包含空格。

  4. 唯一性：

    • 同一个MySQL软件中，数据库不能同名。
    • 同一个库中，表不能重名。
    • 同一个表中，字段不能重名。

  5. 保留字：

    • 字段名不能与SQL保留字、数据库系统或常用方法冲突。如果必须使用保留字，请在SQL语句中使用单引号（`）引起来。

      sql
浅色版本
SELECT `order` FROM table_name;
规范（建议）

  1. 命名一致性：

    • 使用有意义的名称，避免使用缩写或无意义的名称。
    • 表名和字段名尽量使用小写字母，提高可读性。

  2. 前缀和后缀：

    • 表名可以使用前缀表示所属模块或业务领域，例如 user_ 表示用户相关表。
    • 字段名可以使用后缀表示数据类型，例如 id_int 表示整数类型的ID字段。

  3. 注释：

    • 在创建表和字段时添加注释，提高代码的可维护性。

      sql
浅色版本
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    name VARCHAR(100) NOT NULL COMMENT '用户名',
    email VARCHAR(100) NOT NULL COMMENT '用户邮箱',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id)
) COMMENT='用户表';

  4. 命名风格：

    • 使用蛇形命名法（snake_case）或驼峰命名法（camelCase）保持一致。

      • 蛇形命名法：user_profile
      • 驼峰命名法：UserProfile

  5. 避免使用特殊字符：

    • 避免在名称中使用特殊字符，如 @, #, $ 等，以防止潜在的解析问题。

  6. 避免使用保留字：

    • 尽量避免使用SQL保留字作为表名或字段名，以防止语法冲突。

3. 数据库定义语言之库的管理

在MySQL中，数据库的创建和管理是非常基础的操作。以下是几个常见的操作示例：

  • 创建数据库：

    sql
浅色版本
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

  • 选择数据库：

    sql
浅色版本
USE mydatabase;

  • 查看数据库列表：

    sql
浅色版本
SHOW DATABASES;

  • 查看数据库的详细信息：

    sql
浅色版本
SHOW CREATE DATABASE mydatabase;

  • 删除数据库：

    sql
浅色版本
DROP DATABASE mydatabase;

  • 修改数据库字符集和校对规则：

    sql
浅色版本
ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

4. 数据表定义语言之表的管理

在MySQL中，表的创建和管理同样非常重要。以下是几个常见的操作示例：

  • 创建表：

    sql
浅色版本
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

  • 查看表结构：

    sql
浅色版本
DESC users;

  • 查看表的详细信息：

    sql
浅色版本
SHOW CREATE TABLE users;

  • 修改表结构：

    sql
浅色版本
-- 添加列
ALTER TABLE users ADD COLUMN age INT;

-- 修改列的数据类型
ALTER TABLE users MODIFY COLUMN name VARCHAR(200);

-- 修改列的名称
ALTER TABLE users CHANGE COLUMN old_name new_name new_datatype;

-- 删除列
ALTER TABLE users DROP COLUMN age;

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 添加外键
ALTER TABLE users ADD CONSTRAINT fk_user_role FOREIGN KEY (role_id) REFERENCES roles (id);

-- 删除外键
ALTER TABLE users DROP FOREIGN KEY fk_user_role;

-- 添加唯一约束
ALTER TABLE users ADD UNIQUE (email);

-- 添加默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- 删除默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

  • 删除表：

    sql
浅色版本
DROP TABLE users;

  • 重命名表：

    sql
浅色版本
RENAME TABLE old_table_name TO new_table_name;

5. 索引管理

索引是提高查询性能的重要手段。以下是几个常见的索引管理操作：

  • 创建索引：

    sql
浅色版本
CREATE INDEX idx_email ON users (email);

  • 查看索引：

    sql
浅色版本
SHOW INDEX FROM users;

  • 删除索引：

    sql
浅色版本
DROP INDEX idx_email ON users;

  • 创建唯一索引：

    sql
浅色版本
CREATE UNIQUE INDEX idx_unique_email ON users (email);

  • 创建全文索引：

    sql
浅色版本
CREATE FULLTEXT INDEX idx_fulltext_search ON articles (content);

6. 视图管理

视图是虚拟表，基于SQL查询结果创建。以下是几个常见的视图管理操作：

  • 创建视图：

    sql
浅色版本
CREATE VIEW user_info AS SELECT id, name, email FROM users;

  • 查看视图：

    sql
浅色版本
SELECT * FROM user_info;

  • 删除视图：

    sql
浅色版本
DROP VIEW user_info;

  • 修改视图：

    sql
浅色版本
CREATE OR REPLACE VIEW user_info AS SELECT id, name, email, created_at FROM users;

通过以上操作，用户可以有效地管理和维护MySQL数据库中的库和表。遵循命名规定和规范不仅有助于保持数据库的一致性和可维护性，还能提高团队协作的效率。希望这篇文章能够帮助读者更好地理解和应用DDL和SQL命名规范。

avatar
文章
阅读
粉丝