mysql入门--04数据库定义

DDL（数据库定义）

数据库的创建、修改和删除

• 创建数据库的可选项：

  • 字符集和校对规则：可以指定数据库的字符集和校对规则，以确保数据存储和比较的准确性。例如，创建一个使用UTF-8字符集和校对规则的数据库：
  • 其他选项：根据需要，你还可以指定数据库的其他选项，如最大连接数、存储引擎等。
  • CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci;

• 修改数据库的可选项：

  • 修改表结构：使用ALTER TABLE语句可以修改表的结构，如添加、修改或删除列、索引等。
  • 修改数据：使用UPDATE语句可以修改表中的数据，如更新特定行的值。

• 删除数据库的可选项：

  • IF EXISTS：可以使用IF EXISTS选项来避免删除不存在的数据库时出现错误。如果数据库不存在，该选项将使MySQL忽略删除操作。

  • DROP DATABASE IF EXISTS mydatabase;

  • RESTRICT和CASCADE：在删除数据库时，可以使用RESTRICT和CASCADE选项来控制删除操作的行为。RESTRICT将拒绝删除数据库，如果有其他对象引用该数据库，而CASCADE将删除引用该数据库的对象。

  • DROP DATABASE mydatabase RESTRICT;

创建表

语法结构

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

数据类型

• INT(size): 整数类型，可以指定大小。
• VARCHAR(size): 可变长度字符串，可以指定最大长度。
• TEXT: 用于存储文本数据。
• DATE: 日期类型。
• DATETIME: 日期和时间类型。
• DECIMAL(precision, scale): 用于存储精确的小数值。
• ENUM(value1, value2, …): 枚举类型，只能存储指定的值。

列约束

• NOT NULL: 禁止列的值为 NULL。
• UNIQUE: 确保列中的所有值都是唯一的。
• DEFAULT value: 为列指定默认值。
• AUTO_INCREMENT: 用于自动递增整数值，通常与主键一起使用。

表约束

• PRIMARY KEY (column): 定义主键，确保列中的值是唯一的。
• FOREIGN KEY (column) REFERENCES table_name(column): 定义外键，确保列中的值在另一个表中存在。
• CHECK (condition): 检查约束，确保列中的值符合指定条件。
• INDEX (column): 为列创建索引，加快查询速度。

比较完整的实例

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category ENUM('Electronics', 'Clothing', 'Books'),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (name),
    INDEX (category)
);

修改，重命名，删除，清空表

修改表：

-- 增加新列
ALTER TABLE table_name ADD column_name datatype;

-- 修改列
ALTER TABLE table_name MODIFY column_name datatype;

-- 删除列
ALTER TABLE table_name DROP column_name;

-- 将column_name作为table_name表的主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

-- 在table_name表中的column_name列上创建一个名为fk_name的外键约束，该约束将引用other_table表中的other_column列。
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES other_table (other_column);

-- 在table_name表的column_name列创立名为index_name的索引
ALTER TABLE table_name ADD INDEX index_name (column_name);

重命名表：

RENAME TABLE current_table_name TO new_table_name;

-- 重命名多个表
RENAME TABLE current_table1 TO new_table1, current_table2 TO new_table2;

删除表：

• 一旦执行，表数据全部清除，同时保留表的结构，数据可以回滚

DROP TABLE table_name;

-- 先备份table_name表再将原来的表删除
CREATE TABLE backup_table_name AS SELECT * FROM table_name;
DROP TABLE table_name;

清空表：

• 一旦执行，表数据全部清除，同时保留表的结构，数据不可回滚

TRUNCATE TABLE table_name;

--清空表并自增id
TRUNCATE TABLE table_name;
ALTER TABLE table_name AUTO_INCREMENT = 1;