MySQL 关系数据库

mysql> \h
For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.

print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.

For server side help, type 'help contents'

mysql> \h show
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS

SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where:
    LIKE 'pattern'
  | WHERE expr

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.

Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See

http://dev.mysql.com/doc/refman/5.7/en/extended-show.html.

URL: http://dev.mysql.com/doc/refman/5.7/en/show.html


mysql> \h show create table
Name: 'SHOW CREATE TABLE'
Description:
Syntax:
SHOW CREATE TABLE tbl_name

Shows the CREATE TABLE statement that creates the named table. To use
this statement, you must have some privilege for the table. This
statement also works with views.
SHOW CREATE TABLE quotes table and column names according to the value
of the sql_quote_show_create option. See
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html.

For information about how CREATE TABLE statements are stored by MySQL,
see
http://dev.mysql.com/doc/refman/5.7/en/create-table-statement-retention
.html.

URL: http://dev.mysql.com/doc/refman/5.7/en/show-create-table.html

Examples:
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s` char(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> \h alter table
Name: 'ALTER TABLE'
Description:
Syntax:
ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (col_name,...)
        reference_definition
  | ALGORITHM [=] {DEFAULT|INPLACE|COPY}

  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | {DISABLE|ENABLE} KEYS
  | {DISCARD|IMPORT} TABLESPACE
  | DROP [COLUMN] col_name
  | DROP {INDEX|KEY} index_name
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | FORCE
  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ORDER BY col_name [, col_name] ...
  | RENAME {INDEX|KEY} old_index_name TO new_index_name
  | RENAME [TO|AS] new_tbl_name
  | {WITHOUT|WITH} VALIDATION
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | DISCARD PARTITION {partition_names | ALL} TABLESPACE
  | IMPORT PARTITION {partition_names | ALL} TABLESPACE
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number

  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | REMOVE PARTITIONING
  | UPGRADE PARTITIONING

key_part:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

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

table_option:
    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
  | 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}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)

partition_options:
    (see CREATE TABLE options)

ALTER TABLE changes the structure of a table. For example, you can add
or delete columns, create or destroy indexes, change the type of
existing columns, or rename columns or the table itself. You can also
change characteristics such as the storage engine used for the table or
the table comment.

o To use ALTER TABLE, you need ALTER, CREATE, and INSERT privileges for

  the table. Renaming a table requires ALTER and DROP on the old table,
  ALTER, CREATE, and INSERT on the new table.

o Following the table name, specify the alterations to be made. If none
  are given, ALTER TABLE does nothing.

o The syntax for many of the permissible alterations is similar to
  clauses of the CREATE TABLE statement. column_definition clauses use
  the same syntax for ADD and CHANGE as for CREATE TABLE. For more
  information, see [HELP CREATE TABLE].

o The word COLUMN is optional and can be omitted.

o Multiple ADD, ALTER, DROP, and CHANGE clauses are permitted in a
  single ALTER TABLE statement, separated by commas. This is a MySQL
  extension to standard SQL, which permits only one of each clause per
  ALTER TABLE statement. For example, to drop multiple columns in a
  single statement, do this:

ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

o If a storage engine does not support an attempted ALTER TABLE
  operation, a warning may result. Such warnings can be displayed with

  SHOW WARNINGS. See [HELP SHOW WARNINGS]. For information on
  troubleshooting ALTER TABLE, see
  http://dev.mysql.com/doc/refman/5.7/en/alter-table-problems.html.

o For information about generated columns, see
  http://dev.mysql.com/doc/refman/5.7/en/alter-table-generated-columns.
  html.

o For usage examples, see
  http://dev.mysql.com/doc/refman/5.7/en/alter-table-examples.html.

o With the mysql_info() C API function, you can find out how many rows
  were copied by ALTER TABLE. See
  http://dev.mysql.com/doc/refman/5.7/en/mysql-info.html.

URL: http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

mysql >

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.23, for osx10.12 (x86_64) using  EditLine wrapper

Connection id:    2
Current database: sellsys
Current user:   root@172.17.0.1
SSL:      Cipher in use is DHE-RSA-AES256-SHA
Current pager:    less
Using outfile:    ''
Using delimiter:  ;
Server version:   5.7.26 MySQL Community Server (GPL)
Protocol version: 10
Connection:   127.0.0.1 via TCP/IP
Server characterset:  latin1
Db     characterset:  latin1
Client characterset:  utf8
Conn.  characterset:  utf8
TCP port:   3306
Uptime:     18 min 40 sec

Threads: 2  Questions: 82  Slow queries: 0  Opens: 139  Flush tables: 1  Open tables: 130  Queries per second avg: 0.073
--------------

mysql> SELECT database();
+------------+
| database() |
+------------+
| sellsys    |
+------------+
1 row in set (0.01 sec)

mysql> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2019-07-05 20:26:38 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT user();
+-----------------------+
| user()                |
+-----------------------+
| selladmin@192.168.1.5 |
+-----------------------+
1 row in set (0.01 sec)

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.7.25    |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW PROCESSLIST; -- 查看哪些线程正在运行
+-----+-----------+-------------------+---------+---------+------+----------+------------------+
| Id  | User      | Host              | db      | Command | Time | State    | Info             |
+-----+-----------+-------------------+---------+---------+------+----------+------------------+
| 900 | selladmin | 192.168.1.5:52231 | sellsys | Query   |    0 | starting | SHOW PROCESSLIST |
+-----+-----------+-------------------+---------+---------+------+----------+------------------+
1 row in set (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sellsys            |
+--------------------+
2 rows in set (0.01 sec)

mysql> SHOW CREATE DATABASE sellsys;
+----------+---------------------------------------------------------------------+
| Database | Create Database                                                     |
+----------+---------------------------------------------------------------------+
| sellsys  | CREATE DATABASE `sellsys` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES;
+-------------------+
| Tables_in_sellsys |
+-------------------+
| customers         |
| order_items       |
| orders            |
| product_notes     |
| products          |
| vendors           |
+-------------------+
6 rows in set (0.02 sec)

CREATE DATABASE [IF NOT EXISTS] dbname
  DEFAULT CHARACTER SET utf8mb4;

DROP DATABASE [IF EXISTS] <dbname>;

-- 查看所有字符集编码项
SHOW VARIABLES LIKE 'character_set_%';
-- character_set_client        客户端向服务器发送数据时使用的编码
-- character_set_results       服务器端将结果返回给客户端所使用的编码
-- character_set_connection    连接层编码

-- 设置变量值
SET variable_name = value;

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
SET NAMES utf8; -- 相当于上述三个设置

-- 校对集, 校对集用以排序
-- 查看所有字符集
SHOW CHARACTER SET [LIKE 'pattern']/SHOW CHARSET [LIKE 'pattern'];
-- 查看所有校对集
SHOW COLLATION [LIKE 'pattern'];
-- 设置字符集编码
CHARSET charset_name;
-- 设置校对集编码
COLLATE collate_name;

mysql> insert cust (name) values('函数');
ERROR 1366 (HY000): Incorrect string value: '\xE5\x87\xBD\xE6\x95\xB0' for column 'name' at row 1
mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.23, for osx10.12 (x86_64) using  EditLine wrapper

Connection id:      2
Current database:   sellsys
Current user:       root@172.17.0.1
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:      less
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.26 MySQL Community Server (GPL)
Protocol version:   10
Connection:     127.0.0.1 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         18 min 40 sec

Threads: 2  Questions: 82  Slow queries: 0  Opens: 139  Flush tables: 1  Open tables: 130  Queries per second avg: 0.073
--------------

mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

mysql> set character_set_database = utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set character_set_server = utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.23, for osx10.12 (x86_64) using  EditLine wrapper

Connection id:      2
Current database:   sellsys
Current user:       root@172.17.0.1
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:      less
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.26 MySQL Community Server (GPL)
Protocol version:   10
Connection:     127.0.0.1 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         20 min 50 sec

Threads: 2  Questions: 91  Slow queries: 0  Opens: 140  Flush tables: 1  Open tables: 131  Queries per second avg: 0.072
--------------

mysql> drop database sellsys;
Query OK, 1 row affected (0.02 sec)

mysql> create database aa;
Query OK, 1 row affected (0.00 sec)

mysql> show create database aa;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| aa       | CREATE DATABASE `aa` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.23, for osx10.12 (x86_64) using  EditLine wrapper

Connection id:      2
Current database:
Current user:       root@172.17.0.1
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:      less
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.26 MySQL Community Server (GPL)
Protocol version:   10
Connection:     127.0.0.1 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         22 min 9 sec

Threads: 2  Questions: 99  Slow queries: 0  Opens: 140  Flush tables: 1  Open tables: 130  Queries per second avg: 0.074
--------------

mysql> use aa;
Database changed
mysql> create table cust(id int primary key auto_increment, name varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql> show create table cust\G
*************************** 1. row ***************************
       Table: cust
Create Table: CREATE TABLE `cust` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert cust (name) values('函数');
Query OK, 1 row affected (0.01 sec)

mysql> select * from cust;
+----+--------+
| id | name   |
+----+--------+
|  1 | 函数   |
+----+--------+
1 row in set (0.01 sec)

mysql>

CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [dbname.]tabname (
  -- columns defines
  colname dbtype [NOT NULL | NULL] [DEFAULT DEFAULT_VALUE]
    [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'STRING']
) [others];

-- example
CREATE TABLE `customers` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `cust_name` char(50) NOT NULL,
  `cust_address` char(50) DEFAULT NULL,
  `cust_city` char(50) DEFAULT NULL,
  `cust_state` char(5) DEFAULT NULL,
  `cust_zip` char(10) DEFAULT NULL,
  `cust_country` char(50) DEFAULT NULL,
  `cust_contact` char(50) DEFAULT NULL,
  `cust_email` char(255) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SHOW CREATE TABLE tabname;
DESC/DESCRIBE/EXPLAIN tabname;
SHOW COLUMNS FROM tabname [LIKE 'PATTERN'];
SHOW TABLE STATUS [FROM dbname] [LIKE 'pattern'];

mysql> SHOW CREATE TABLE customers\G
*************************** 1. row ***************************
       Table: customers
Create Table: CREATE TABLE `customers` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `cust_name` char(50) NOT NULL,
  `cust_address` char(50) DEFAULT NULL,
  `cust_city` char(50) DEFAULT NULL,
  `cust_state` char(5) DEFAULT NULL,
  `cust_zip` char(10) DEFAULT NULL,
  `cust_country` char(50) DEFAULT NULL,
  `cust_contact` char(50) DEFAULT NULL,
  `cust_email` char(255) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> DESC customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

mysql> EXPLAIN customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

mysql> SHOW COLUMNS FROM customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> SHOW TABLE STATUS FROM sellsys LIKE 'cust%'\G
*************************** 1. row ***************************
           Name: customers
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 5
 Avg_row_length: 3276
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 10006
    Create_time: 2019-06-30 00:27:31
    Update_time: 2019-06-30 00:30:49
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

mysql>

ALTER TABLE tabname
  ADD[ COLUMN] colname                       -- 增加字段
      AFTER colname                          -- 表示增加在该字段名后面
      FIRST                                  -- 表示增加在第一个
  ADD PRIMARY KEY(colname)                   -- 创建主键
  ADD UNIQUE [idxname] (colnam)              -- 创建唯一索引
  ADD INDEX [idxname] (colname)              -- 创建普通索引
  ADD/DROP[ COLUMN] colname                  -- 删除字段
  MODIFY[ COLUMN] colname dbtype             -- 支持对字段属性进行修改，不能修改字段名(所有原有属性也需写上)
  CHANGE[ COLUMN] colname new_colname dbtype -- 支持对字段名修改
  DROP PRIMARY KEY                           -- 删除主键(删除主键前需删除其 AUTO_INCREMENT 属性)
  DROP INDEX idxname                         -- 删除索引
  DROP FOREIGN KEY fkname                    -- 删除外键

mysql> show create table orders\G
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `order_num` int(11) NOT NULL AUTO_INCREMENT,
  `order_date` datetime NOT NULL,
  `cust_id` int(11) NOT NULL,
  PRIMARY KEY (`order_num`),
  KEY `fk_orders_customers` (`cust_id`),
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20010 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> alter table orders add column note varchar(256) comment 'Order Notes';
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table orders\G
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `order_num` int(11) NOT NULL AUTO_INCREMENT,
  `order_date` datetime NOT NULL,
  `cust_id` int(11) NOT NULL,
  `note` varchar(256) DEFAULT NULL COMMENT 'Order Notes',
  PRIMARY KEY (`order_num`),
  KEY `fk_orders_customers` (`cust_id`),
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20010 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

-- 删除表
DROP TABLE [IF EXISTS] tabname;
-- 清空表数据
TRUNCATE [TABLE] tabname;

-- 复制表结构
CREATE TABLE tabname LIKE src_tabname;
-- 复制表结构和数据
CREATE TABLE tabname [AS] SELECT * FROM src_tabname;

-- 检查表是否有错误
CHECK TABLE tabname [, tabname] ... [option] ...
-- 优化表
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tabname [, tabname] ...
-- 修复表
REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tabname [, tabname] ... [QUICK] [EXTENDED] [USE_FRM]
-- 分析表
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tabname [, tabname] ...

INSERT [INTO] tabname [(colname1, colname2, ...)]
  VALUES (value1, value2, ...) [, (value1, value2, ...), ...];

INSERT [INTO] tabname SET colname1=value1[, colname2=value2, ...];

DELETE FROM tabname[ WHERE condition] [ORDER BY ...] [LIMIT row_count];

UPDATE tabname SET colname1=value1[, colname2=value2] [WHERE condition];

SELECT colname1, colname2, ... FROM tabname [WHERE condition];

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

drop table author;

create table author (
  id int auto_increment primary key,
  name varchar(128) not null,
  age int,
  gender varchar(32),
  a int,
  b int,
  c int
);

insert into author(name, age, gender, a, b, c) values ('aaa', 11, 'M', 10, 21, 34);
insert into author(name, age, gender, a, b, c) values ('abc', 12, 'M', 10, 22, 33);
insert into author(name, age, gender, a, b, c) values ('bbb', 10, 'F', 10, 23, 31);
insert into author(name, age, gender, a, b, c) values ('baa', 40, 'M', 11, 21, 31);
insert into author(name, age, gender, a, b, c) values ('bac', 22, 'F', 11, 22, 32);
insert into author(name, age, gender, a, b, c) values ('caa', 70, 'M', 11, 23, 33);
insert into author(name, age, gender, a, b, c) values ('bca', 30, 'M', 11, 20, 30);

create table author (
  id int auto_increment primary key,
  name varchar(128) not null,
  age int,
  gender varchar(32),
  a int,
  b int,
  c int
);

delete from author;

insert into author(name, age, gender, a, b, c) values ('aaa', 11, 'M', 10, 21, 34);
insert into author(name, age, gender, a, b, c) values ('abc', 12, 'M', 10, 22, 33);
insert into author(name, age, gender, a, b, c) values ('bbb', 10, 'F', 10, 23, 31);
insert into author(name, age, gender, a, b, c) values ('baa', 40, 'M', 11, 21, 31);
insert into author(name, age, gender, a, b, c) values ('bac', 22, 'F', 11, 22, 32);
insert into author(name, age, gender, a, b, c) values ('caa', 70, 'M', 11, 23, 33);
insert into author(name, age, gender, a, b, c) values ('bca', 30, 'M', 11, 20, 30);

-- 指定名称创建
alter table author add unique key uk_author_nag (name, age, gender);
create index ix_author_abc on author(a, b, c);

-- 删除唯一键和索引
alter table author drop key uk_author_nag;
alter table author drop index ix_author_abc;

-- 匿名创建
alter table author add unique key (name, age, gender);
alter table author add index (a,b,c);

-- 查看索引
show create table author\G
show index from author;

SELECT ... UNION [ALL|DISTINCT] SELECT ...

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)] AS select_statement

SHOW CREATE VIEW view_name

DROP VIEW [IF EXISTS] view_name ...

ALTER VIEW view_name [(column_list)] AS select_statement

START TRANSACTION;
-- 或者
BEGIN;

COMMIT;

ROLLBACK;

SAVEPOINT spname             -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT spname -- 回滚到保存点
RELEASE SAVEPOINT spname     -- 删除保存点

-- 0 表示关闭自动提交，1表示开启自动提交。
SET autocommit = 0|1;

CREATE TRIGGER tgname tgtime tgevent ON tbname FOR EACH ROW tgstmt;

DROP TRIGGER [scmname.]tgname;

DELIMITER newsep
  --- <SQL 语句>
自定义结束符号

DELIMITER ;        -- 修改回原来的分号

CREATE USER username@hostname IDENTIFIED BY 'password_string';

RENAME USER old_user TO new_user;

SET PASSWORD = PASSWORD('密码');              -- 为当前用户设置密码
SET PASSWORD FOR username = PASSWORD('密码'); -- 为指定用户设置密码

DROP USER username;

GRANT prvlist ON scmname.tabname TO username [IDENTIFIED BY [PASSWORD] 'password'];

SHOW GRANTS FOR username;

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

REVOKE prvlist ON scmname.tabname FROM username;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM username;   -- 撤销所有权限

-- 全局层级: 只授予和撤销全局权限。
GRANT/REVOKE ALL ON *.*;
-- 数据库层级: 只授予和撤销数据库权限。
GRANT/REVOKE ALL ON dbname.*;
-- 表层级: 只授予和撤销表权限。
GRANT/REVOKE ALL ON db_name.tabname;
-- 当使用 REVOKE 时，您必须指定与被授权列相同的列。
GRANT/REVOKE ALL ON db_name.tabname(colname1, colname2, ...);

ALL [PRIVILEGES]        -- 设置除 GRANT OPTION 之外的所有简单权限
ALTER                   -- 允许使用 ALTER TABLE
ALTER ROUTINE           -- 更改或取消已存储的子程序
CREATE                  -- 允许使用 CREATE TABLE
CREATE ROUTINE          -- 创建已存储的子程序
CREATE TEMPORARY TABLES -- 允许使用 CREATE TEMPORARY TABLE
CREATE USER             -- 允许使用 CREATE USER, DROP USER, RENAME USER 和
                        -- REVOKE ALL PRIVILEGES。
CREATE VIEW             -- 允许使用 CREATE VIEW
DELETE                  -- 允许使用 DELETE
DROP                    -- 允许使用 DROP TABLE
EXECUTE                 -- 允许用户运行已存储的子程序
FILE                    -- 允许使用 SELECT...INTO OUTFILE 和 LOAD DATA INFILE
INDEX                   -- 允许使用 CREATE INDEX 和 DROP INDEX
INSERT                  -- 允许使用 INSERT
LOCK TABLES             -- 允许对您拥有 SELECT 权限的表使用 LOCK TABLES
PROCESS                 -- 允许使用 SHOW FULL PROCESSLIST
REFERENCES              -- 未被实施
RELOAD                  -- 允许使用 FLUSH
REPLICATION CLIENT      -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE       -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT                  -- 允许使用 SELECT
SHOW DATABASES          -- 显示所有数据库
SHOW VIEW               -- 允许使用 SHOW CREATE VIEW
SHUTDOWN                -- 允许使用 mysqladmin shutdown
SUPER                   -- 允许使用 CHANGE MASTER, KILL, PURGE MASTER LOGS 和
                        -- SET GLOBAL 语句，mysqladmin debug 命令；允许您连接
                        -- （一次），即使已达到 max_connections。
UPDATE                  -- 允许使用 UPDATE
USAGE                   -- "无权限" 的同义词
GRANT OPTION            -- 允许授予权限

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...

CHECK TABLE tabname [, tabname] ... [option] ...

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tabname [, tabname] ...

-- 导出表数据
SELECT * INTO OUTFILE filename [export_option] FROM tabname;

-- 导入数据
LOAD DATA [LOCAL] INFILE filename [REPLACE|IGNORE] INTO TABLE tabname [import_option];

select a, b, a+b
  into outfile '/tmp/result.csv'
       fields terminated by ','
       optionally enclosed by '"'
       lines terminated by '\n'
  from test_table;

export MYSQL_PS1="\u@\h [\d]> "

[mysqld]
log-bin             = binlog
server-id           = 1

log-output          = TABLE
general-log         = 1
#general-log-file    = /usr/local/var/mysql/general.log
slow-query-log      = 1
#slow-query-log-file = /usr/local/var/mysql/slow.log
long-query-time     = 1

[mysqld]
################################################################################
# CPU
################################################################################
innodb_thread_concurrency = 32

################################################################################
# Memory
################################################################################

# mysql> show variables like 'innodb_buffer_%';
# +-------------------------------------+----------------+
# | Variable_name                       | Value          |
# +-------------------------------------+----------------+
# | innodb_buffer_pool_chunk_size       | 134217728      |
# | innodb_buffer_pool_dump_at_shutdown | ON             |
# | innodb_buffer_pool_dump_now         | OFF            |
# | innodb_buffer_pool_dump_pct         | 25             |
# | innodb_buffer_pool_filename         | ib_buffer_pool |
# | innodb_buffer_pool_instances        | 1              |
# | innodb_buffer_pool_load_abort       | OFF            |
# | innodb_buffer_pool_load_at_startup  | ON             |
# | innodb_buffer_pool_load_now         | OFF            |
# | innodb_buffer_pool_size             | 134217728      |
# +-------------------------------------+----------------+
innodb_buffer_pool_size = 24576M

# mysql> show variables like '%buffer_size';
# +-------------------------+----------+
# | Variable_name           | Value    |
# +-------------------------+----------+
# | bulk_insert_buffer_size | 8388608  |
# | innodb_log_buffer_size  | 16777216 |
# | innodb_sort_buffer_size | 1048576  |
# | join_buffer_size        | 262144   |
# | key_buffer_size         | 8388608  |
# | myisam_sort_buffer_size | 8388608  |
# | preload_buffer_size     | 32768    |
# | read_buffer_size        | 131072   |
# | read_rnd_buffer_size    | 262144   |
# | sort_buffer_size        | 262144   |
# +-------------------------+----------+
key_buffer_size         = 1024M
sort_buffer_size        = 1024M
join_buffer_size        = 1024M
read_buffer_size        = 1024M
read_rnd_buffer_size    = 1024M

create database test01 default character set utf8mb4 collate utf8mb4_general_ci;

create user 'user01'@'%' identified by 'user01';
grant all privileges on test01.* to 'user01'@'%';
flush privileges;

use mysql;
set password = password('root');
update user set host = '%' where user = 'root';
flush privileges;

set password for 'test01'@'%' = password('1234');
flush privileges;