MySQL中的sql语句

DML、 DDL、 DCL

DML(Data Manipulation Language)，用于对数据库中的数据进行操作，包括插入、查询、更新和删除数据等操作。常见的 DML 命令包括 SELECT（查询）、INSERT（插入）、UPDATE（更新）和 DELETE（删除）等。

DDL(Data Definition Language)， 用于定义数据库的结构和组件，包括创建表、修改表结构、删除表等操作。常见的 DDL 命令包括 CREATE（创建）、ALTER（修改）和 DROP（删除）等。

DCL(Data Control Language)，用于控制数据库用户的访问权限和安全性，包括授权用户访问数据库对象、撤销用户权限等操作。常见的 DCL 命令包括 GRANT（授权）、REVOKE（撤销权限）等。

数据库

创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name		# 表示如果存在则不执行
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name			# 指定字符集
  | COLLATE [=] collation_name					# 指定校对规则
  | ENCRYPTION [=] {'Y' | 'N'}					# 指定是否需要加密
}

删除数据库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

创建的时候用的是schema，删除的时候用的是database，彼此不分，说明两者一样

更改数据库

ALTER {DATABASE | SCHEMA} [db_name]
    alter_option ...

alter_option: {
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
  | READ ONLY [=] {DEFAULT | 0 | 1}
}

数据表

创建数据表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]  
    
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition: {
    col_name column_definition	# 定义一个列，包括列名和列的数据类型以及其他属性
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...			# 定义一个索引，可以指定索引的名称、列和其他选项
  | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
      [index_option] ...		# 定义全文索引或空间索引，可以指定名称、列和其他选项
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...)
      [index_option] ... 		# 定义主键约束，可以指定主键的名称、类型、包含的列和其他选项
  | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (key_part,...)
      [index_option] ...		# 定义唯一约束，可以指定唯一约束的名称、类型、包含的列和其他选项
  | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name,...)
      reference_definition	# 定义外键约束，包括外键名称、包含的列以及参考的表和列
  | check_constraint_definition # 定义检查约束，用于限制列中的值必须满足特定条件
}

column_definition: {
    data_type 											# 指定类型
    	[NOT NULL | NULL] 						# 是否可以包含null值
    	[DEFAULT {literal | (expr)} ] #	指定默认值
      [VISIBLE | INVISIBLE]					# 指定列是否可见，默认为可见
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY] # 自增长，适用于整型列
      [COMMENT 'string']						# 注释
      [COLLATE collation_name]			# 指定排序规则
      [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}] # 列的存储格式
      [ENGINE_ATTRIBUTE [=] 'string'] # 指定引擎相关的属性
      [SECONDARY_ENGINE_ATTRIBUTE [=] 'string'] # 指定引擎相关的属性
      [STORAGE {DISK | MEMORY}]			# 指定存储引擎为磁盘还是内存
      [reference_definition]				# 定义外键约束
      [check_constraint_definition] # 定义检查约束
}

key_part: {col_name [(length)] | (expr)} [ASC | DESC] # 定义排序，可以指定列名的长度限制，也可以是表达式

index_type:
    USING {BTREE | HASH} # 索引类型，b+树和哈希索引

index_option: {
    KEY_BLOCK_SIZE [=] value	# 指定索引块的大小
  | index_type								# 索引类型
  | WITH PARSER parser_name		# 指定解析器名称
  | COMMENT 'string'					# 注释
  | {VISIBLE | INVISIBLE}			# 是否可见
  |ENGINE_ATTRIBUTE [=] 'string'	# 指定引擎相关的属性
  |SECONDARY_ENGINE_ATTRIBUTE [=] 'string' # 指定引擎相关的属性
}

check_constraint_definition:
    [CONSTRAINT [symbol]] 		# 指定约束的名称，symbol为可选项
    CHECK (expr) 							# 指定要检查的条件表达式，只有当条件表达式返回TRUE时才会通过约束
    [[NOT] ENFORCED]					# 指定是否启用该检查约束，默认为ENFORCED

reference_definition:
    REFERENCES tbl_name (key_part,...) # 指定被引用表的名称以及与参考键相关联的列
      [MATCH FULL 			# 指定了匹配规则，决定了在执行关联操作时如何处理不完全匹配的情况
       | MATCH PARTIAL  # 要求所有引用键的值都在被引用表中有相应的匹配值
       | MATCH SIMPLE]	# 允许其中一部分引用键的值没有相应的匹配值
      [ON DELETE reference_option] # 指定了在删除被引用行时采取的操作
      [ON UPDATE reference_option] # 指定了在更新被引用行时采取的操作

reference_option:
    RESTRICT 				# 在引用表中存在相关行时，阻止对被引用表中的行进行删除或更新操作。这是默认的选项
    | CASCADE 			# 当对被引用表中的行进行删除或更新操作时，自动级联执行相应操作到引用表中的相关行
    | SET NULL 			# 在被引用表中的行被删除或更新时，将引用表中的相关列的值设置为NULL
    | NO ACTION 		# 在引用表中存在相关行时，阻止对被引用表中的行进行删除或更新操作
    | SET DEFAULT		# 在被引用表中的行被删除或更新时，将引用表中相关列的值设置为默认值

table_options:
    table_option [[,] table_option] ...

table_option: {
    AUTOEXTEND_SIZE [=] value										# 指定表空间自动扩展的大小
  | AUTO_INCREMENT [=] value										# 指定自增列的起始值和增量
  | AVG_ROW_LENGTH [=] value										# 指定每行的平均长度
  | [DEFAULT] CHARACTER SET [=] charset_name		# 指定默认的字符集
  | CHECKSUM [=] {0 | 1}												# 启用或禁用校验和
  | [DEFAULT] COLLATE [=] collation_name				# 指定默认的排序规则
  | COMMENT [=] 'string'												# 为表添加注释
  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}		# 指定表的压缩方式
  | CONNECTION [=] 'connect_string'							# 指定连接字符串
  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'		# 指定数据或索引文件存储的目录
  | DELAY_KEY_WRITE [=] {0 | 1}									# 延迟索引更新
  | ENCRYPTION [=] {'Y' | 'N'}									# 启用或禁用表的加密
  | ENGINE [=] engine_name											# 指定表的存储引擎
  | ENGINE_ATTRIBUTE [=] 'string'								# 指定特定存储引擎的属性
  | INSERT_METHOD [=] { NO | FIRST | LAST }			# 指定插入新记录的位置
  | KEY_BLOCK_SIZE [=] value										# 指定索引块的大小
  | MAX_ROWS [=] value													# 指定表中允许的最大行数
  | MIN_ROWS [=] value													# 指定表中预期的最小行数
  | PACK_KEYS [=] {0 | 1 | DEFAULT}							# 指定索引压缩方式
  | PASSWORD [=] 'string'												# 为表设置密码
  | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT} # 指定行的存储格式
  | START TRANSACTION 													# 在创建或更改表时自动启动事务
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'			# 指定特定辅助引擎的属性
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}			# 启用或禁用统计数据的自动重新计算
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}			# 启用或禁用持久化统计数据
  | STATS_SAMPLE_PAGES [=] value								# 指定用于统计采样的页数
  | tablespace_option														# 指定表空间选项
  | UNION [=] (tbl_name[,tbl_name]...)					# 将多个表合并成一个虚拟表
}

partition_options:
    PARTITION BY																# 指定分区的方式
        { [LINEAR] HASH(expr)										# 按照表达式的哈希值进行分区
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)	# 按照指定列的值进行分区，可以选择使用线性哈希算法
        | RANGE{(expr) | COLUMNS(column_list)}	# 按照表达式的范围或指定列的范围进行分区
        | LIST{(expr) | COLUMNS(column_list)} } # 按照表达式的列表或指定列的列表进行分区
    [PARTITIONS num]														# 指定分区的数量
    [SUBPARTITION BY														# 指定次级分区的方式
        { [LINEAR] HASH(expr)										# 按照表达式的哈希值进行次级分区
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }	# 按照指定列的值进行次级分区
      [SUBPARTITIONS num]												# 指定次级分区的数量
    ]
    [(partition_definition [, partition_definition] ...)]		# 指定每个分区的具体定义

partition_definition:
    PARTITION partition_name										# 指定分区的名称
        [VALUES																	# 指定分区的取值范围
            {LESS THAN {(expr | value_list) | MAXVALUE}	# 指定分区的上限值，可以是表达式或值列表的形式，也可以使用MAXVALUE表示最大值
            | IN (value_list)}]									# 指定分区的取值列表
        [[STORAGE] ENGINE [=] engine_name]			# 指定存储引擎
        [COMMENT [=] 'string' ]									# 为分区添加注释
        [DATA DIRECTORY [=] 'data_dir']					# 指定数据文件存储的目录
        [INDEX DIRECTORY [=] 'index_dir']				# 指定索引文件存储的目录
        [MAX_ROWS [=] max_number_of_rows]				# 指定分区允许的最大行数
        [MIN_ROWS [=] min_number_of_rows]				# 指定分区预期的最小行数
        [TABLESPACE [=] tablespace_name]				# 指定表空间的名称
        [(subpartition_definition [, subpartition_definition] ...)]	# 指定次级分区的具体定义

subpartition_definition:
    SUBPARTITION logical_name										# 指定次级分区的逻辑名称
        [[STORAGE] ENGINE [=] engine_name]			# 指定存储引擎
        [COMMENT [=] 'string' ]									# 为次级分区添加注释
        [DATA DIRECTORY [=] 'data_dir']					# 指定数据文件存储的目录
        [INDEX DIRECTORY [=] 'index_dir']				# 指定索引文件存储的目录
        [MAX_ROWS [=] max_number_of_rows]				# 指定次级分区允许的最大行数
        [MIN_ROWS [=] min_number_of_rows]				# 指定次级分区预期的最小行数
        [TABLESPACE [=] tablespace_name]				# 指定表空间的名称

tablespace_option:
    TABLESPACE tablespace_name [STORAGE DISK] 	# 将表存储在指定的表空间中，可以选择将存储类型设置为DISK，表示使用磁盘存储
  | [TABLESPACE tablespace_name] STORAGE MEMORY	# 将表存储在指定的表空间中，存储类型设置为MEMORY，表示使用内存存储

克隆数据表

CREATE TABLE new_tbl LIKE orig_tbl;

CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;

修改数据表

# 添加字段
ALTER TABLE table_name DROP COLUMN new_column;
# 修改字段类型
ALTER TABLE table_name MODIFY COLUMN existing_column INT;
# 删除字段
ALTER TABLE table_name DROP COLUMN column_to_delete;
# 修改表名
ALTER TABLE table_name RENAME TO new_table_name;

删除数据表

DROP TABLE table_name;

表空间

在mysql中，只有InnoDB引擎才有表空间的概念，用于存储表数据和索引的物理文件，可以包含一个或者多个数据文件，这些文件可以位于同一个或不同个存储设备上。

创建表空间

CREATE [UNDO] TABLESPACE tablespace_name  	# 创建[undo]表空间

  InnoDB and NDB:														# 支持InnoDB引擎和NDB引擎的选项
    [ADD DATAFILE 'file_name']							# 指定要向Undo表空间添加的数据文件的文件名
    [AUTOEXTEND_SIZE [=] value]							# 指定自动扩展Undo表空间时增加的大小

  InnoDB only:															# 仅支持InnoDB
    [FILE_BLOCK_SIZE = value]								# 指定Undo表空间文件的块大小
    [ENCRYPTION [=] {'Y' | 'N'}]						# 指定是否对Undo表空间进行加密

  NDB only:																	# 仅支持NDB
    USE LOGFILE GROUP logfile_group					# 指定要使用的日志文档
    [EXTENT_SIZE [=] extent_size]						# 指定Undo表空间的扩展大小
    [INITIAL_SIZE [=] initial_size]					# 指定Undo表空间的初始大小
    [MAX_SIZE [=] max_size]									# 指定Undo表空间的最大大小
    [NODEGROUP [=] nodegroup_id]						# 指定Undo表空间的节点组
    [WAIT]																	# 指定在创建Undo表空间时是否等待完成
    [COMMENT [=] 'string']									# 添加对Undo表空间的注释

  InnoDB and NDB:
    [ENGINE [=] engine_name]								# 指定表空间的存储引擎

  Reserved for future use:
    [ENGINE_ATTRIBUTE [=] 'string']					# 这是保留给将来使用的选项，目前没有特定的用途

修改表空间

# 修改表空间的数据文件路径
ALTER TABLESPACE tablespace_name ADD DATAFILE 'new_file_path';

# 修改表空间的自动扩展大小
ALTER TABLESPACE tablespace_name AUTOEXTEND_SIZE = new_size;

# 修改表空间的加密选项（仅适用于InnoDB表空间）
ALTER TABLESPACE tablespace_name ENCRYPTION = 'Y'; -- 开启加密

删除表空间

DROP TABLESPACE tablespace_name;