MySQL 8.0 命令手册

MySQL 8.0 命令手册

官方帮助文档链接：https://dev.mysql.com/doc/refman/8.0/en/

系统管理

MySQL服务器管理

systemctl start mysqld			#mysql启动
systemctl stop mysqld			#mysql停止
systemctl restart mysqld		#mysql重启
systemctl status mysqld			#mysql状态查看

MySQL命令行登陆

mysql -h 192.168.1.2 -P 3306 -u root -p			# 命令行登陆MySQL

选项解释
-h MySQL IP
-P MySQL端口 默认3306
-u MySQL用户
-p 用户密码

MySQL用户管理

mysql> 	CREATE USER 'username'@'%' IDENTIFIED BY 'password';				#实际密码替换password
mysql>	alter user 'username'@'%' identified with mysql_native_password by 'password';
mysql>	grant all privileges on *.* to 'username'@'%' with grant option;

选项：
'username'@'%' 	username:自定义用户名，%：代表0.0.0.0 所有远端client可以访问，%也可以替换为具体的IP和IP段；
all privileges	代表所有权限，可以替换为具体的CREATE,UPDATE等等；
*.*				第一个* 代表所有的数据库，第二个* 代表所有的表；可以替换为单个数据库和单个表；

用户创建

该CREATE USER语句创建新的 MySQL 帐户。它支持为新帐户建立身份验证、角色、SSL/TLS、资源限制、密码管理、注释和属性属性。它还控制帐户最初是锁定还是解锁。

CREATE USER 'jeffrey'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'new_password'
  PASSWORD EXPIRE INTERVAL 180 DAY
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;

用户修改

该ALTER USER语句修改 MySQL 帐户。它允许为现有帐户修改身份验证、角色、SSL/TLS、资源限制、密码管理、注释和属性属性。它还可用于锁定和解锁帐户。

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED WITH mysql_native_password BY 'new_password'
  PASSWORD EXPIRE INTERVAL 180 DAY
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;

该RENAME USER语句重命名现有 MySQL 帐户。不存在的旧帐户或已存在的新帐户会发生错误。

RENAME USER old_user TO new_user
    [, old_user TO new_user] ...
    
例子：   
RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';    

用户删除

该DROP USER语句删除一个或多个 MySQL 帐户及其权限。

DROP USER [IF EXISTS] user [, user] ...

DROP USER 'jeffrey'@'localhost';

用户配置角色

SET DEFAULT ROLE
    {NONE | ALL | role [, role ] ...}
    TO user [, user ] ...
    
例子：    
SET DEFAULT ROLE 'admin', 'developer' TO 'joe'@'10.0.0.1';

角色创建

CREATE ROLE [IF NOT EXISTS] role [, role ] ...

CREATE ROLE 'admin', 'developer';
CREATE ROLE 'webapp'@'localhost';

角色激活

SET ROLE {
    DEFAULT
  | NONE
  | ALL
  | ALL EXCEPT role [, role ] ...
  | role [, role ] ...
}
例子：
SET ROLE DEFAULT;
SET ROLE 'role1', 'role2';
SET ROLE ALL;
SET ROLE ALL EXCEPT 'role1', 'role2';

角色删除

DROP ROLE [IF EXISTS] role [, role ] ...

DROP ROLE 'admin', 'developer';
DROP ROLE 'webapp'@'localhost';

权限管理

授权 GRANT

该GRANT语句将权限和角色分配给 MySQL 用户帐户和角色；

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]
    [AS user
        [WITH ROLE
            DEFAULT
          | NONE
          | ALL
          | ALL EXCEPT role [, role ] ...
          | role [, role ] ...
        ]
    ]
}

例子：
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
GRANT SELECT ON world.* TO 'role3';

刷新 FLUSH PRIVILEGES

 FLUSH PRIVILEGES			#刷新后权限生效

撤销 REVOKE

该REVOKE语句使系统管理员能够撤销权限和角色，这些权限和角色可以从用户帐户和角色中撤销。

REVOKE [IF EXISTS]
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...
    [IGNORE UNKNOWN USER]
    
例子：    
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost';
REVOKE SELECT ON world.* FROM 'role3';

MySQL参数配置

DDL命令

DDL（data definition language）是数据定义语言：主要的命令有CREATE、ALTER、DROP等，DDL操作对象包括databases,tables,tablespaces,indexs,truncat table；主要是用在定义或改变数据库、表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

创建 CREATE

CREATE命令：支持数据库、表、事件、函数、索引、、等

CREATE DATABASE

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
  | ENCRYPTION [=] {'Y' | 'N'}
}

选项解释：
- CHARACTER SET 指定数据库字符集
- COLLATE		指定数据库排序规则
- ENCRYPTION	在MySQL 8.0.16中引入，定义数据库是否加密，默认为‘Y’

eg:
CREATE DATABASE IF NOT EXISTS db_yin CHARACTER SET 'utf8mb4' collate 'utf8mb4_bin' ENCRYPTION 'Y';

MySQL创建数据库后，数据目录会自动创建一个文件目录（不支持用户手动在数据目录 datadir 下通过mkdir创建目录），MySQL没有数据库数量限制，但是服务器文件系统可能会存在文件目录限制。

CREATE TABLE

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) }
    
选项：
tbl_name	表名
TEMPORARY	临时表，会话关闭自动删除；
LIKE		表复制，用于CREATE TABLE ... LIKE根据另一个表的定义创建一个空表，包括在原始表中定义的任何列属性和索引：
AS			表克隆，要从另一个表创建一个表，CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;
IGNORE REPLACE	在使用语句 REPLACE 复制表时如何处理重复唯一键值的行 

eg:
CREATE TABLE t1 (
    c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c2 VARCHAR(100),
    c3 VARCHAR(100) )
ENGINE=InnoDB
COMMENT="this is eg";

CREATE EVENT

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;

schedule: {
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
}

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如下：

关键字CREATE EVENT加上事件名称，它在数据库模式中唯一标识事件。

一个ON SCHEDULE子句，它确定事件执行的时间和频率。

子句，其中DO包含要由事件执行的 SQL 语句。

这是一个最小CREATE EVENT语句的示例：
 CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

修改 ALTER

ALTER命令：支持数据库、表、事件、函数、索引、、等

ALTER 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}
}

eg:
ALTER DATABASE mydb READ ONLY = 0 DEFAULT COLLATE utf8mb4_bin;

不支持修改数据库名称；

ALTER TABLE

添加一个字段，默认添加再最后一个。
ALTER TABLE table_name ADD [COLUMN] col_name 数据类型 [first|after 字段名];
修改一个字段：数据类型、长度、默认值
ALTER TABLE table_name MODIFY col_name 数据类型;
重命名一个字段
ALTER TABLE table_name CHANGE col_name new_col_name 数据类型;
删除一个字段
ALTER TABLE table_name DROP COLUMN 字段名

重命名表名
RENAME TABLE old_table TO new_table;
或者
ALTER TABLE old_table RENAME new_table;

ALTER EVENT

支持修改事件的名称、执行日期、启用/暂停等参数配置；

ALTER
    [DEFINER = user]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    [DO event_body]
    
eg:
ALTER EVENT myevent
    ON SCHEDULE
      EVERY 12 HOUR
    STARTS CURRENT_TIMESTAMP + INTERVAL 4 HOUR;

删除 DROP

删除DROP命令支持数据库、表、视图、函数、事件、触发器、索引等对象；

DROP DATABASE

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

删除库会同步删除库及库中所有的表数据；drop schema 等同于drop database;

DROP TABLE

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

删除表可以同步删除一张或者多张表；删除表会删除表定义和表数据；

TRUNCATE TABLE

TRUNCATE [TABLE] tbl_name

TRUNCATE TABLE等于delete，SQL语句执行后会清空表中所有数据且不可恢复，删除效率高；

DROP EVENT

DROP EVENT [IF EXISTS] event_name

DML命令

DML（data manipulation language）是数据操纵语言：它们是UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。

插入 INSERT

insert语句用于表中插入数据；

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {   SELECT ... 
      | TABLE table_name 
      | VALUES row_constructor_list
    }
    [ON DUPLICATE KEY UPDATE assignment_list]


value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

assignment:
    col_name = 
          value
        | [row_alias.]col_name
        | [tbl_name.]col_name
        | [row_alias.]col_alias

assignment_list:
    assignment [, assignment] ...
    
eg:
INSERT INTO table_name (clo1,clo2) VALUES (1,2)
INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

更新 UPDATE

update语句用户修改表数据；

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...
    
eg：
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

删除 DELETE

delelte语句用户删除表中的数据；

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

导入 IMPORT

IMPORT TABLE FROM sdi_file [, sdi_file] ...

mysql> IMPORT TABLE FROM
       '/tmp/mysql-files/employees.sdi',
       '/tmp/mysql-files/managers.sdi';

导入 LOAD DATA

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]
        
eg：
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

表查询 TABLE

TABLE table_name [ORDER BY column_name] [LIMIT number [OFFSET number]]
eg:
TABLE t ORDER BY b;

查询 SELECT

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

eg：
SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...

SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
  WHERE t1.name = t2.name;

SELECT t1.name, t2.salary FROM employee t1, info t2
  WHERE t1.name = t2.name;

DCL命令

DCL（DataControlLanguage）是数据库控制语言：是用来设置或更改数据库用户或角色权限的语句，GRANT 授权, REVOKE 取消, COMMIT 提交, ROLLBACK 回滚，LOCK语句。

锁表 LOCK TABLE

MySQL 允许客户端会话显式获取表锁，以便与其他会话协作以访问表，或者防止其他会话在会话需要独占访问表的期间修改表。会话只能为自己获取或释放锁。一个会话不能获取另一个会话的锁或释放另一个会话持有的锁。

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

UNLOCK TABLES

事务提交 START TRANSACTION、COMMIT 和 ROLLBACK 语句

START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

这些语句提供对 事务使用的控制：

• START TRANSACTION或 BEGIN开始新的交易。
• COMMIT提交当前事务，使其更改永久化。
• ROLLBACK回滚当前事务，取消其更改。
• SET autocommit禁用或启用当前会话的默认自动提交模式。

默认情况下，MySQL 在 启用自动提交模式的情况下运行。这意味着，当不在事务中时，每个语句都是原子的，就好像它被START TRANSACTIONand包围一样COMMIT。您不能使用ROLLBACK撤消效果；但是，如果在语句执行期间发生错误，则该语句将回滚。

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

授权 GRANT

该GRANT语句将权限和角色分配给 MySQL 用户帐户和角色；

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]
    [AS user
        [WITH ROLE
            DEFAULT
          | NONE
          | ALL
          | ALL EXCEPT role [, role ] ...
          | role [, role ] ...
        ]
    ]
}

例子：
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
GRANT SELECT ON world.* TO 'role3';

撤销 REVOKE

该REVOKE语句使系统管理员能够撤销权限和角色，这些权限和角色可以从用户帐户和角色中撤销。

REVOKE [IF EXISTS]
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user_or_role [, user_or_role] ...
    [IGNORE UNKNOWN USER]
    
例子：    
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost';
REVOKE SELECT ON world.* FROM 'role3';