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

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 表名;
更改表名语法1RENAME TABLE 老表名 TO 新表名;
语法2ALTER 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)
);
添加普通索引(已存在表)语法1CREATE INDEX 索引名 ON 表名 (列名);
语法2ALTER 表名 ADD INDEX (列名1, 列名2);
添加索引(已存在表)
FULLTEXT:全文索引。
SPATIAL:空间索引。
UNIQUE:唯一索引。
语法1CREATE {FULLTEXT | SPATIAL | UNIQUE} INDEX 索引名 ON 表名 (列名);
语法2ALTER 表名 ADD {FULLTEXT | SPATIAL | UNIQUE} INDEX (列名1, 列名2);
更改索引名(已存在表)ALTER TABLE 表名 RENAME INDEX 老索引名 TO 新索引名;
删除索引语法1DROP INDEX 索引名 ON 表名;
语法2ALTER 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参数。要为参数指定其它内容,请在参数名称前使用关键字OUTINOUT

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

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

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

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

简述语句
查看已创建的存储过程的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()

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

函数主体可以是一个简单的语句,例如 SELECTINSERT语句,也可以是使用BEGINEND编写的复合语句。复合语句可以包含声明、循环和其他控制结构语句。在实践中,存储函数倾向于使用复合语句,除非主体由单个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

(1)
huoxiaoqiang的头像huoxiaoqiang
上一篇 2022年9月2日
下一篇 2022年9月4日

相关推荐

  • 2.MySQL常用命令一览表

    1.mysql_secure_installation mysql_secure_installation 用于在生产环境中提高 MySQL Server 安装的安全性,按照提示操作。 2.mysqld mysqld 用于启动 MySQL Server。 mysqld选项 描述 mysqld –help | -? 查…

    MySQL教程 2022年9月2日
    06650
  • 1.MySQL数据类型一览表

    1.Integer(整数精确值) INT 的同义词为 INTEGER 。 类型 存储(字节) 范围 TINYINT[UNSIGNED] 1 有符号 -128(-27) ~ 127(27-1),无符号 0 ~ 255(28-1)。 SMALLINT[UNSIGNED] 2 有符号 -32,768(-215) ~ 32,7…

    MySQL教程 2022年9月1日
    09570
  • 4.MySQL数据操作语句一览表

    1.新增记录 简述 语句 新增记录(按列顺序) INSERT [INTO] 表名 VALUES (值1, 值2, …); 新增记录(按列名称) INSERT [INTO] 表名 (列名1, 列名2, …) VALUES (值1, 值2, …); 复制已存在表1数据到已存在表2(全部列) INSERT [IN…

    MySQL教程 2022年9月4日
    06110

发表回复

登录后才能评论