3.MySQL数据定义语句一览表

Table of Contents

1.用户

简述	语句
创建新角色	`CREATE ROLE 角色名;`
删除角色	`DROP ROLE 角色名;`
通过指定其授予的哪些角色处于活动状态，修改当前用户在当前会话中的有效权限。	`SET ROLE 角色名;`
将当前会话中的活动角色设置为当前用户默认角色。	`SET ROLE DEFAULT;`
定义在用户会话中默认激活哪些用户角色。	`SET DEFAULT ROLE 角色名 TO 用户;`
查看已创建用户的CREATE USER定义语句	`SHOW CREATE USER 用户名;`
创建新用户	`CREATE USER '用户名'@'localhost' IDENTIFIED BY '密码';`
更改用户	`ALTER USER 用户名;`
更改用户名	`RENAME USER 老用户名 TO 新用户名;`
删除用户	`DROP USER 用户名;`
更改密码(已存在用户)	`ALTER USER 'root'@'localhost' IDENTIFIED BY 'Password-4-Root';`
查看所有MySQL server支持的系统权限列表	`SHOW PRIVILEGES;`
查看属于指定用户或角色的权限	`SHOW GRANTS FOR 用户名或角色名;`
授予权限	`GRANT 权限类型 ON 权限对象 TO 用户名或角色名;`
授予角色给用户	`GRANT 角色 TO 用户;`
取消权限	`REVOKE 权限类型 ON 权限对象 FROM 用户名或角色名;`

2.数据库

简述	语句
查看所有数据库	`SHOW DATABASES;`
查看已创建的数据库的CREATE DATABASE定义语句	`SHOW CREATE DATABASE 数据库名;`
创建新数据库	`CREATE DATABASE 数据库名;`
更改数据库	`ALTER DATABASE 数据库名;`
删除数据库中的所有表并删除数据库	`DROP DATABASE 数据库名;`
选择后续语句的默认数据库	`USE 数据库名;`
查看连接到了哪个数据库	`SELECT DATABASE();`
查看(所有)字符集	`SHOW CHARACTER SET;`
查看(所有)字符集排序规则	`SHOW COLLATION;`
配置字符集	`SET {CHARACTER SET \| CHARSET} {'字符集名' \| DEFAULT};`
配置排序	`SET NAMES {'字符集名' [COLLATE '排序名'] \| DEFAULT};`
查看数据目录内的文件	`shell> sudo ls -lhtr /var/lib/mysql`
查看当前数据库文件的存放目录	`SHOW VARIABLES LIKE 'datadir';`
查看所有存储引擎的状态信息	`SHOW ENGINES;`
查看警告信息	`SHOW WARNINGS;`

3.表

简述	语句
查看[给定数据库的]所有非临时表	`SHOW TABLES [FROM 数据库名];`
查看[给定数据库的]所有非临时表(更多表信息)	`SHOW TABLE STATUS [FROM 数据库名];`
查看已创建的表的CREATE TABLE定义语句	`SHOW CREATE TABLE 表名;`
创建[临时]新表	`CREATE [TEMPORARY] TABLE 表名();`
根据另一个表的定义创建一个新的空[临时]表 (包括在老表中定义的任何列属性和索引)	`CREATE [TEMPORARY] TABLE 新表名 LIKE 老表名;`
从另一个表查询数据复制到一个新[临时]表	`CREATE [TEMPORARY] TABLE 新表名 AS SELECT * FROM 老表名;`
更改表	`ALTER TABLE 表名;`
更改表名	语法1：`RENAME TABLE 老表名 TO 新表名;` 语法2：`ALTER TABLE 老表名 RENAME TO 新表名;`
删除[临时]表	`DROP [TEMPORARY] TABLE 表名;`
删除表(只删除表内的数据，但并不删除表本身)	`TRUNCATE TABLE 表名;`

4.列

简述	语句
查看给定表的所有列信息	`SHOW COLUMNS FROM 表名;`
查看给定表的所有列信息(简写)	`{EXPLAIN \| DESCRIBE \| DESC} 表名;`
创建列(新[临时]表)	`CREATE [TEMPORARY] TABLE 表名 ( 列名1 数据类型, 列名2 数据类型, 列名3 数据类型 );`
创建计算列 VIRTUAL：虚拟列(默认)。 STORED：存储列。	`CREATE [TEMPORARY] TABLE 表名 ( 列名1 数据类型, 列名2 数据类型, 列名3 数据类型 GENERATED ALWAYS AS (表达式) [VIRTUAL \| STORED] );`
添加列(已存在表) FIRST：添加到第一列。 AFTER 指定列：添加到指定列之后。	`ALTER TABLE 表名 ADD COLUMN 列名数据类型 [FIRST \| AFTER 指定列];`
添加计算列(已存在表) VIRTUAL：虚拟列(默认)。 STORED：存储列。	`ALTER TABLE 表名 ADD COLUMN 列名数据类型 GENERATED ALWAYS AS (表达式) [VIRTUAL \| STORED];`
更改列名(已存在表)	`ALTER TABLE 表名 RENAME COLUMN 老列名 TO 新列名;`
更改列定义(已存在表) FIRST：添加到第一列。 AFTER 指定列：添加到指定列之后。	`ALTER TABLE 表名 MODIFY COLUMN 列名新数据类型 [FIRST \| AFTER 指定列];`
更改列名和列定义(已存在表)	`ALTER TABLE 表名 CHANGE COLUMN 老列名新列名新数据类型;`
删除列(已存在表)	`ALTER TABLE 表名 DROP COLUMN 列名;`

5.索引

索引支持单列索引和多列组合索引。

简述	语句
查看给定表的索引信息	`SHOW INDEX FROM 表名;`
创建普通索引(新表)	`CREATE TABLE 表名 ( 列名1 数据类型, 列名2 数据类型, INDEX (列名1, 列名2) );`
创建索引(新表) FULLTEXT：全文索引。 SPATIAL：空间索引。 UNIQUE：唯一索引。	`CREATE TABLE 表名 ( 列名1 数据类型, 列名2 数据类型, {FULLTEXT \| SPATIAL \| UNIQUE} INDEX (列名1, 列名2) );`
添加普通索引(已存在表)	语法1：`CREATE INDEX 索引名 ON 表名 (列名);` 语法2：`ALTER 表名 ADD INDEX (列名1, 列名2);`
添加索引(已存在表) FULLTEXT：全文索引。 SPATIAL：空间索引。 UNIQUE：唯一索引。	语法1：`CREATE {FULLTEXT \| SPATIAL \| UNIQUE} INDEX 索引名 ON 表名 (列名);` 语法2：`ALTER 表名 ADD {FULLTEXT \| SPATIAL \| UNIQUE} INDEX (列名1, 列名2);`
更改索引名(已存在表)	`ALTER TABLE 表名 RENAME INDEX 老索引名 TO 新索引名;`
删除索引	语法1：`DROP INDEX 索引名 ON 表名;` 语法2：`ALTER TABLE 表名 DROP INDEX 索引名;`

6.约束

约束名的作用是当出现约束错误时，系统会以约束名报错，方便排查故障。

6.1NOT NULL

简述	语句
创建NOT NULL约束(新表)	`CREATE TABLE 表名 ( 列名1 数据类型 NOT NULL, 列名2 数据类型 NOT NULL, 列名3 数据类型 );`
添加NOT NULL约束(已存在表)	`ALTER TABLE 表名 MODIFY COLUMN 列名 NOT NULL;`
删除NOT NULL约束(已存在表)	`ALTER TABLE 表名 MODIFY COLUMN 列名 NULL;`

6.2UNIQUE

UNIQUE 是UNIQUE KEY的简写。

简述	语句
创建[带约束名的]UNIQUE约束(新表)	`CREATE TABLE 表名 ( 列名1 数据类型 NOT NULL, 列名2 数据类型 NOT NULL, 列名3 数据类型, [CONSTRAINT 约束名] UNIQUE (列名1, 列名2) );`
添加[带约束名的]UNIQUE约束(已存在表)	`ALTER TABLE 表名 ADD [CONSTRAINT 约束名] UNIQUE (列名1, 列名2);`
删除UNIQUE约束	`ALTER TABLE 表名 DROP INDEX 约束名;`

6.3DEFAULT

简述	语句
创建DEFAULT约束(新表)	`CREATE TABLE 表名 ( 列名1 数据类型 NOT NULL, 列名2 数据类型 NOT NULL, 列名3 数据类型 DEFAULT 默认值 );`
添加DEFAULT约束(已存在表)	`ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT 默认值;`
删除DEFAULT约束	`ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT;`

6.4AUTO_INCREMENT

默认初始值为 1，每条新记录递增 1。

简述	语句
创建AUTO_INCREMENT约束(新表)	`CREATE TABLE 表名 ( 列名1 数据类型 NOT NULL AUTO_INCREMENT, 列名2 数据类型 NOT NULL, 列名3 数据类型 );`
添加AUTO_INCREMENT约束(已存在表)	`ALTER TABLE 表名 MODIFY COLUMN 列名 AUTO_INCREMENT;`

6.5PRIMARY KEY

键区别	是否可以定义表中的多列	是否可为NULL	值是否唯一	是否自动递增
PRIMARY KEY	是(联合主键也必须唯一)	否	是	是
UNIQUE KEY	是	是(但只能有一个记录为空)	是	否
FOREIGN KEY	子表可以存在多列外键，但一个外键只能关联父表中的一个主键	是	是	否

简述	语句
创建[带约束名的]PRIMARY KEY约束(新表)	`CREATE TABLE 表名 ( 列名1 数据类型 NOT NULL, 列名2 数据类型 NOT NULL, 列名3 数据类型, [CONSTRAINT 约束名] PRIMARY KEY (列名1, 列名2) );`
添加[带约束名的]PRIMARY KEY约束(已存在表)	`ALTER TABLE 表名 ADD [CONSTRAINT 约束名] PRIMARY KEY (列名1);`
删除PRIMARY KEY约束	`ALTER TABLE 表名 DROP PRIMARY KEY;`

6.6FOREIGN KEY

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

简述	语句
创建[带约束名的]FOREIGN KEY约束(新表)	`CREATE TABLE 表名 ( 列名1 数据类型 NOT NULL, 列名2 数据类型 NOT NULL, 列名3 数据类型, [CONSTRAINT 约束名] FOREIGN KEY (列名3) REFERENCES 表名1(列名1) );`
添加[带约束名的]FOREIGN KEY约束(已存在表)	`ALTER TABLE 表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (列名3) REFERENCES 表名1(列名1);`
删除FOREIGN KEY约束(注意：删除外键约束并没有删除外键这一列)	`ALTER TABLE 表名 DROP FOREIGN KEY 约束名;`

引用选项	描述
`RESTRICT`	拒绝对父表的删除或更新操作。
`CASCADE`	从父表中删除或更新行，并自动删除或更新子表中匹配的行。
`SET NULL`	从父表中删除或更新行，并将子表中的外键列设置为`NULL`。
`NO ACTION(默认)`	标准 SQL 中的关键字。在 MySQL 中，相当于`RESTRICT`。
`SET DEFAULT`	这个行为被 MySQL 解析器识别。

6.7CHECK

简述	语句
创建[带约束名的]CHECK约束(新表)	`CREATE TABLE 表名 ( 列名1 数据类型 NOT NULL, 列名2 数据类型 NOT NULL, 列名3 数据类型, [CONSTRAINT 约束名] CHECK (列名1>0 AND 列名2 = 'Sandnes') );`
添加[带约束名的]CHECK约束(已存在表)	`ALTER TABLE 表名 ADD [CONSTRAINT 约束名] CHECK (列名1>0 AND 列名2 = 'Sandnes');`
删除CHECK约束	`ALTER TABLE 表名 DROP CHECK 约束名;`

7.表空间(TABLESPACE)

简述	语句
创建表空间	`CREATE [UNDO] TABLESPACE 表空间名;`
更改表空间	`ALTER [UNDO] TABLESPACE 表空间名;`
删除表空间	`DROP [UNDO] TABLESPACE 表空间名;`

8.事件(EVENT)

简述	语句
创建事件	`CREATE EVENT 事件名 ON SCHEDULE 计划安排 DO 事件主体;`
更改事件	`ALTER EVENT 事件名 [ON SCHEDULE 计划安排] [RENAME TO 新事件名];`
删除事件	`DROP EVENT 事件名;`

计划安排: {
    AT timestamp [+ INTERVAL 间隔]
  | EVERY interval
    [STARTS timestamp [+ INTERVAL 间隔]]
    [ENDS timestamp [+ INTERVAL 间隔]]
}

间隔:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

//示例
CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

9.存储过程(STORED PROCEDURE)

括号内的参数列表必须始终存在。如果没有参数，则应使用()空参数列表。参数名称不区分大小写。

默认情况下，每个参数都是一个IN参数。要为参数指定其它内容，请在参数名称前使用关键字OUT或INOUT。

IN参数将值传递给过程，该过程可能会修改该值，但当该过程返回时，调用者看不到该修改。

OUT参数将过程中的值传递回调用者，在过程中它的初始值为NULL，当过程返回时，它的值对调用者可见。

INOUT参数由调用者初始化，可由过程修改，过程返回时调用者可以看到过程所做的任何更改。

存储过程主体可以是一个简单的语句，例如 SELECT 或 INSERT语句，也可以是使用BEGIN和END编写的复合语句。复合语句可以包含声明、循环和其他控制结构语句。

简述	语句
查看已创建的存储过程的CREATE PROCEDURE定义语句	`SHOW CREATE PROCEDURE 存储过程名;`
查看命名存储过程的内部实现的表示	`SHOW PROCEDURE CODE 存储过程名;`
查看存储过程的特征，例如数据库、名称、类型、创建者、创建和修改日期以及字符集信息。	`SHOW PROCEDURE STATUS [LIKE '模式' \| WHERE 表达式];`
创建存储过程	`CREATE PROCEDURE 存储过程名([ IN \| OUT \| INOUT ] 参数名称参数类型) 存储过程主体;`
更改存储过程	`ALTER PROCEDURE 存储过程名 [特征];`
删除存储过程	`DROP PROCEDURE 存储过程名;`

mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
       BEGIN
         SELECT COUNT(*) INTO cities FROM world.city
         WHERE CountryCode = country;
       END//

10.函数(FUNCTION)

括号内的参数列表必须始终存在。如果没有参数，则应使用()空参数列表。参数名称不区分大小写。

对于函数，参数始终被视为IN参数。

如果给定关键字AGGREGATE，则表示该函数是聚合（组）函数。聚合函数的工作方式与原生 MySQL 聚合函数完全一样，例如SUM()or COUNT()。

共享类库名是包含实现该函数的代码的共享库文件的基本名称。

函数主体可以是一个简单的语句，例如 SELECT 或 INSERT语句，也可以是使用BEGIN和END编写的复合语句。复合语句可以包含声明、循环和其他控制结构语句。在实践中，存储函数倾向于使用复合语句，除非主体由单个RETURN语句组成。

简述	语句
查看已创建的存储函数的CREATE FUNCTION定义语句	`SHOW CREATE FUNCTION 函数名;`
查看命名存储函数的内部实现的表示	`SHOW FUNCTION CODE 函数名;`
查看存储函数的特征，例如数据库、名称、类型、创建者、创建和修改日期以及字符集信息。	`SHOW FUNCTION STATUS [LIKE '模式' \| WHERE 表达式];`
创建存储函数	`CREATE FUNCTION 函数名(参数名称参数类型) RETURNS 返回类型函数主体;`
创建可加载函数	`CREATE [AGGREGATE] FUNCTION 函数名 RETURNS {STRING\|INTEGER\|REAL\|DECIMAL} SONAME 共享类库名;`
更改存储函数	`ALTER PROCEDURE 存储函数名 [特征];`
删除存储函数或可加载函数	`DROP FUNCTION 函数名;`

11.触发器(TRIGGER)

简述	语句
创建触发器	`CREATE TRIGGER 触发器名 { BEFORE \| AFTER } { INSERT \| UPDATE \| DELETE } ON 表名 FOR EACH ROW [{ FOLLOWS \| PRECEDES } 其它触发器名] 触发器主体;`
删除触发器	`DROP TRIGGER [架构名.]触发器名;`

原创文章，作者：huoxiaoqiang，如若转载，请注明出处：https://www.huoxiaoqiang.com/sql/mysql/17173.html