SQL中 create 组合用法全是干货

1. 创建数据库

create database my_database;

功能：此语句用于创建一个名为 my_database 的新数据库。
详细解释：
- CREATE DATABASE 是 SQL 里用于创建数据库的关键字。
- my_database 为新数据库的名称，你可根据需求进行修改。

2. 创建表

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null,
    age int,
    department varchar(50),
    created_at timestamp default current_timestamp
) engine=innodb;

功能：创建一个名为 employees 的表，用于存储员工信息。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，它作为主键，并且会自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，且该列不允许为空。
- age int：age 列的数据类型是整数，允许为空。
- department varchar(50)：department 列的数据类型为可变长度字符串，最大长度 50 个字符，允许为空。
- created_at timestamp default current_timestamp：created_at 列的数据类型为时间戳，默认值是当前时间。
- engine=innodb：指定表的存储引擎为 InnoDB。

3. 创建索引

create index idx_name on employees(name);

功能：在 employees 表的 name 列上创建一个名为 idx_name 的普通索引。
详细解释：
- CREATE INDEX 是用于创建索引的关键字。
- idx_name 是索引的名称。
- employees(name) 表示在 employees 表的 name 列上创建索引。

4. 创建唯一索引

create unique index idx_email on employees(email);

功能：在 employees 表的 email 列上创建一个名为 idx_email 的唯一索引。
详细解释：
- CREATE UNIQUE INDEX 用于创建唯一索引。
- idx_email 是索引的名称。
- employees(email) 表示在 employees 表的 email 列上创建索引，唯一索引确保该列的值具有唯一性。

5. 创建视图

create view hr_employees as
select name, age from employees where department = 'hr';

功能：创建一个名为 hr_employees 的视图，该视图从 employees 表中筛选出部门为 'hr' 的员工的姓名和年龄。
详细解释：
- CREATE VIEW hr_employees：创建名为 hr_employees 的视图。
- AS 用于指定视图的查询语句。
- SELECT name, age FROM employees WHERE department = 'hr'：从 employees 表中选择 name 和 age 列，筛选条件是 department 为 'hr'。

6. 创建存储过程

delimiter //
create procedure get_employees()
begin
    select * from employees;
end //
delimiter ;

功能：创建一个名为 get_employees 的存储过程，该过程用于查询 employees 表的所有记录。
详细解释：
- DELIMITER //：将语句分隔符从默认的 ; 改为 //，这是因为存储过程内部会用到 ;，为避免冲突需要更改分隔符。
- CREATE PROCEDURE get_employees()：创建名为 get_employees 的存储过程，该过程没有参数。
- BEGIN 和 END：用于定义存储过程的主体。
- SELECT * FROM employees：查询 employees 表的所有记录。
- DELIMITER ;：将语句分隔符改回默认的 ;。

7. 创建函数

delimiter //
create function get_employee_age(name varchar(50))
returns int
deterministic
begin
    declare age int;
    select age into age from employees where name = name;
    return age;
end //
delimiter ;

功能：创建一个名为 get_employee_age 的函数，该函数根据员工姓名返回其年龄。
详细解释：
- DELIMITER //：更改语句分隔符。
- CREATE FUNCTION get_employee_age(name varchar(50))：创建名为 get_employee_age 的函数，该函数接受一个 varchar(50) 类型的参数 name。
- RETURNS int：指定函数的返回值类型为整数。
- DETERMINISTIC：表示该函数对于相同的输入参数总是返回相同的结果。
- DECLARE age int：声明一个名为 age 的整数变量。
- SELECT age INTO age FROM employees WHERE name = name：从 employees 表中查询与输入姓名匹配的员工的年龄，并将结果存储到 age 变量中。
- RETURN age：返回 age 变量的值。
- DELIMITER ;：恢复语句分隔符。

8. 创建触发器

delimiter //
create trigger before_employee_insert
before insert on employees
for each row
begin
    set new.created_at = now();
end //
delimiter ;

功能：创建一个名为 before_employee_insert 的触发器，在向 employees 表插入记录之前，将 created_at 列的值设置为当前时间。
详细解释：
- DELIMITER //：更改语句分隔符。
- CREATE TRIGGER before_employee_insert：创建名为 before_employee_insert 的触发器。
- BEFORE INSERT ON employees：指定触发器在向 employees 表插入记录之前触发。
- FOR EACH ROW：表示对每一行记录都执行触发器操作。
- SET new.created_at = now()：将新插入记录的 created_at 列的值设置为当前时间。
- DELIMITER ;：恢复语句分隔符。

9. 创建用户

create user 'new_user'@'localhost' identified by 'password';

功能：创建一个名为 new_user 的用户，该用户只能从 localhost 连接，密码为 password。
详细解释：
- CREATE USER 是用于创建用户的关键字。
- 'new_user'@'localhost'：指定用户名为 new_user，该用户只能从 localhost 连接。
- IDENTIFIED BY 'password'：指定用户的密码为 password。

10. 创建角色

create role admin_role;

功能：创建一个名为 admin_role 的角色。
详细解释：
- CREATE ROLE 是用于创建角色的关键字。
- admin_role 是角色的名称。

11. 创建表空间

create tablespace user_data
add datafile 'user_data.ibd'
engine=innodb;

功能：创建一个名为 user_data 的表空间，并添加一个数据文件 user_data.ibd，使用 InnoDB 存储引擎。
详细解释：
- CREATE TABLESPACE user_data：创建名为 user_data 的表空间。
- ADD DATAFILE 'user_data.ibd'：为表空间添加一个数据文件 user_data.ibd。
- ENGINE=innodb：指定表空间使用 InnoDB 存储引擎。

12. 创建临时表

create temporary table temp_employees (
    id int primary key,
    name varchar(50)
);

功能：创建一个名为 temp_employees 的临时表，该表在当前会话结束时会自动删除。
详细解释：
- CREATE TEMPORARY TABLE 用于创建临时表。
- temp_employees 是临时表的名称。
- id int primary key：id 列的数据类型是整数，作为主键。
- name varchar(50)：name 列的数据类型为可变长度字符串，最大长度 50 个字符。

13. 创建表并复制结构

create table new_employees like employees;

功能：创建一个名为 new_employees 的表，该表的结构与 employees 表相同。
详细解释：
- CREATE TABLE new_employees：创建名为 new_employees 的表。
- LIKE employees：指定新表的结构与 employees 表相同。

14. 创建表并复制数据

create table new_employees as
select * from employees where 1=0;

功能：创建一个名为 new_employees 的表，该表的结构与 employees 表相同，但不复制任何数据。
详细解释：
- CREATE TABLE new_employees：创建名为 new_employees 的表。
- AS 用于指定表的结构和数据来源。
- SELECT * FROM employees WHERE 1=0：查询 employees 表的所有记录，但 WHERE 1=0 条件始终为假，因此不会返回任何数据，新表只复制结构。

15. 创建表并指定存储引擎

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null
) engine=myisam;

功能：创建一个名为 employees 的表，并指定存储引擎为 MyISAM。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- ENGINE=myisam：指定表的存储引擎为 MyISAM。

16. 创建表并指定字符集

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null
) charset=utf8mb4;

功能：创建一个名为 employees 的表，并指定字符集为 utf8mb4。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- CHARSET=utf8mb4：指定表使用 utf8mb4 字符集。

17. 创建表并指定排序规则

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null
) collate=utf8mb4_unicode_ci;

功能：创建一个名为 employees 的表，并指定排序规则为 utf8mb4_unicode_ci。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- COLLATE=utf8mb4_unicode_ci：指定表使用 utf8mb4_unicode_ci 排序规则。

18. 创建表并指定行格式

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null
) row_format=dynamic;

功能：创建一个名为 employees 的表，并指定行格式为 dynamic。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- ROW_FORMAT=dynamic：指定表使用 dynamic 行格式。

19. 创建表并指定数据目录

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null
) data_directory='/var/lib/mysql-files/';

功能：创建一个名为 employees 的表，并指定数据文件的存储目录为 /var/lib/mysql-files/。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- DATA_DIRECTORY='/var/lib/mysql-files/'：指定表的数据文件存储在 /var/lib/mysql-files/ 目录下。

20. 创建表并指定索引目录

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null
) index_directory='/var/lib/mysql-indexes/';

功能：创建一个名为 employees 的表，并指定索引文件的存储目录为 /var/lib/mysql-indexes/。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- INDEX_DIRECTORY='/var/lib/mysql-indexes/'：指定表的索引文件存储在 /var/lib/mysql-indexes/ 目录下。

21. 创建表并指定表空间

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null
) tablespace user_data;

功能：创建一个名为 employees 的表，并指定该表使用 user_data 表空间。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- TABLESPACE user_data：指定表使用 user_data 表空间。

22. 创建表并指定分区

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null,
    created_at date
)
partition by range (year(created_at)) (
    partition p0 values less than (2020),
    partition p1 values less than (2021),
    partition p2 values less than (2022)
);

功能：创建一个名为 employees 的表，并根据 created_at 列的年份进行范围分区。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- created_at date：created_at 列的数据类型为日期。
- PARTITION BY RANGE (YEAR(created_at))：根据 created_at 列的年份进行范围分区。
- PARTITION p0 VALUES LESS THAN (2020)：创建一个名为 p0 的分区，该分区包含 created_at 年份小于 2020 的记录。
- PARTITION p1 VALUES LESS THAN (2021)：创建一个名为 p1 的分区，该分区包含 created_at 年份小于 2021 的记录。
- PARTITION p2 VALUES LESS THAN (2022)：创建一个名为 p2 的分区，该分区包含 created_at 年份小于 2022 的记录。

23. 创建表并指定子分区

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null,
    created_at date
)
partition by range (year(created_at))
subpartition by hash (month(created_at)) (
    partition p0 values less than (2020) (
        subpartition s0,
        subpartition s1
    ),
    partition p1 values less than (2021) (
        subpartition s2,
        subpartition s3
    )
);

功能：创建一个名为 employees 的表，先根据 created_at 列的年份进行范围分区，再根据月份进行哈希子分区。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- created_at date：created_at 列的数据类型为日期。
- PARTITION BY RANGE (YEAR(created_at))：根据 created_at 列的年份进行范围分区。
- SUBPARTITION BY HASH (MONTH(created_at))：在每个分区内根据 created_at 列的月份进行哈希子分区。
- PARTITION p0 VALUES LESS THAN (2020) (SUBPARTITION s0, SUBPARTITION s1)：创建一个名为 p0 的分区，该分区包含 created_at 年份小于 2020 的记录，并将其进一步划分为两个子分区 s0 和 s1。
- PARTITION p1 VALUES LESS THAN (2021) (SUBPARTITION s2, SUBPARTITION s3)：创建一个名为 p1 的分区，该分区包含 created_at 年份小于 2021 的记录，并将其进一步划分为两个子分区 s2 和 s3。

24. 创建表并指定外键

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null,
    department_id int,
    foreign key (department_id) references departments(id)
) engine=innodb;

功能：创建一个名为 employees 的表，并在 department_id 列上定义一个外键，关联到 departments 表的 id 列。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- department_id int：department_id 列的数据类型是整数，用于存储员工所属部门的 ID。
- FOREIGN KEY (department_id) REFERENCES departments(id)：在 department_id 列上定义一个外键，关联到 departments 表的 id 列，确保 department_id 列的值必须存在于 departments 表的 id 列中。
- ENGINE=innodb：指定表的存储引擎为 InnoDB，因为只有 InnoDB 支持外键约束。

25. 创建表并指定默认值

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null,
    age int default 0,
    department varchar(50) default 'unknown'
);

功能：创建一个名为 employees 的表，并为 age 和 department 列指定默认值。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- age int default 0：age 列的数据类型是整数，默认值为 0。
- department varchar(50) default 'unknown'：department 列的数据类型为可变长度字符串，最大长度 50 个字符，默认值为 'unknown'。

26. 创建表并指定自动增长

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null
) auto_increment=100;

功能：创建一个名为 employees 的表，并指定 id 列的自动增长起始值为 100。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- AUTO_INCREMENT=100：指定 id 列的自动增长起始值为 100。

27. 创建表并指定评论

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null
) comment='员工信息表';

功能：创建一个名为 employees 的表，并为该表添加评论 '员工信息表'。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- COMMENT='员工信息表'：为表添加评论 '员工信息表'，方便后续维护和理解。

28. 创建表并指定延迟键表

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null
) delay_key_write=1;

功能：创建一个名为 employees 的表，并启用延迟键写入功能。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- DELAY_KEY_WRITE=1：启用延迟键写入功能，这意味着在插入数据时，不会立即更新索引，而是将索引更新操作延迟到表关闭时进行，以提高插入性能。

29. 创建表并指定加密

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null
) encryption='Y';

功能：创建一个名为 employees 的表，并对该表的数据进行加密。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- ENCRYPTION='Y'：指定对表的数据进行加密，保护数据的安全性。

30. 创建表并指定压缩

create table employees (
    id int primary key auto_increment,
    name varchar(50) not null
) row_format=compressed;

功能：创建一个名为 employees 的表，并指定行格式为 compressed，对表的数据进行压缩。
详细解释：
- CREATE TABLE employees：创建名为 employees 的表。
- id int primary key auto_increment：id 列的数据类型是整数，作为主键，并且自动递增。
- name varchar(50) not null：name 列的数据类型为可变长度字符串，最大长度 50 个字符，不允许为空。
- ROW_FORMAT=compressed：指定表使用 compressed 行格式，对表的数据进行压缩，以节省存储空间。

posted @ 2025-04-17 17:57 wei笑面对阅读(497) 评论(0) 收藏举报

刷新页面返回顶部

范.迪塞尔

SQL中 create 组合用法全是干货

1. 创建数据库

2. 创建表

3. 创建索引

4. 创建唯一索引

5. 创建视图

6. 创建存储过程

7. 创建函数

8. 创建触发器

9. 创建用户

10. 创建角色

11. 创建表空间

12. 创建临时表

13. 创建表并复制结构

14. 创建表并复制数据

15. 创建表并指定存储引擎

16. 创建表并指定字符集

17. 创建表并指定排序规则

18. 创建表并指定行格式

19. 创建表并指定数据目录

20. 创建表并指定索引目录

21. 创建表并指定表空间

22. 创建表并指定分区

23. 创建表并指定子分区

24. 创建表并指定外键

25. 创建表并指定默认值

26. 创建表并指定自动增长

27. 创建表并指定评论

28. 创建表并指定延迟键表

29. 创建表并指定加密

30. 创建表并指定压缩

公告