摘要：比多一點(diǎn)兒一常用命令這是系列筆記的第一部分，本系列筆記希望能按照筆者自己學(xué)習(xí)技術(shù)的經(jīng)歷來記錄，避免純粹按照內(nèi)容一塊一塊總結(jié)，也就是不同于一般按內(nèi)容分配章節(jié)的書籍的結(jié)構(gòu)，有一個(gè)平滑的閱讀曲線。第一篇的主要內(nèi)容會(huì)是最常用的一些命令。

這是MySQL系列筆記的第一部分，本系列筆記希望能按照筆者自己學(xué)習(xí)MySQL技術(shù)的經(jīng)歷來記錄，避免純粹按照內(nèi)容一塊一塊總結(jié)，也就是不同于一般按內(nèi)容分配章節(jié)的書籍的結(jié)構(gòu)，有一個(gè)平滑的閱讀曲線。內(nèi)容比較豐富的技術(shù)點(diǎn)會(huì)按照專題在多個(gè)學(xué)習(xí)筆記中逐漸深入。文章列表點(diǎn)此。

首先，請(qǐng)如果還完全為接觸過MySQL的讀者先閱讀w3c的SQL教程，再來讀本篇內(nèi)容。

這部分的標(biāo)題叫比CRUD多一丁點(diǎn)兒，比起最基礎(chǔ)的w3c的SQL教程之外，只多一點(diǎn)的擴(kuò)展，滿足應(yīng)付從純粹閱讀入門資料到可以上手完成一個(gè)簡單的工作的需求。

第一篇的主要內(nèi)容會(huì)是最常用的一些mysql命令。因?yàn)殡m然有很多圖形化的工具，但在實(shí)際的工作中因?yàn)樾枰ゾ€上服務(wù)查看數(shù)據(jù)，處理問題，字符界面的命令行工具是必不可少的。

mysql -h $host -P $port -u $user -p$passsword $database_name

例如用戶root使用密碼mydb@xxx用鏈接到地址為192.168.1.99，端口為3306的數(shù)mysql進(jìn)程，并默認(rèn)使用上名為testdb的數(shù)據(jù)庫（即自動(dòng)執(zhí)行use testdb）。

mysql -h 192.168.1.99 -P 3306 -u"root" -p"mydb@xx" testdb

各參數(shù)如果有@,&等bash的關(guān)鍵字，則需要用""引起來。

有時(shí)候需要在命令行執(zhí)行某句SQL，則建議使用 -Bse 參數(shù)。-B參數(shù)的含義是執(zhí)行多條語句(batch)這樣可以執(zhí)行多條，-e即是執(zhí)行(execute)，-s參數(shù)意思是靜默執(zhí)行(silent)可以讓輸出格式精簡一些。

mysql -h $host -P $port -u $user -p$passsword -Bse "command1;command2;....;commandn"

例如，常見的將執(zhí)行結(jié)果導(dǎo)出到文件中方便留存和閱讀。

mysql -h 192.168.1.99 -P 3306 -u"root" -p"mydb@xx" -Bse "select id,name from testdb.Account;" > result.txt

mysqldump是做數(shù)據(jù)導(dǎo)出的命令行工具，也是mysql安裝后默認(rèn)會(huì)帶的。作用是將mysql數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)出出來。

mysql -h $host -P $port -u $user -p$passsword $database_name $table1 $table2 ...

例如

mysqldump -h 192.168.1.99 -P 3306 -u"root" -p"mydb@xx" mydb table1 table2 > result.sql

使用--no-data參數(shù)只導(dǎo)出表結(jié)構(gòu)，

mysqldump -h 192.168.1.99 -P 3306 -u"root" -p"mydb@xx" --no-data mydb  > result.sql

在開發(fā)實(shí)踐中難免會(huì)遇到校驗(yàn)數(shù)據(jù)表結(jié)構(gòu)不同，或者根據(jù)開發(fā)環(huán)境和目標(biāo)環(huán)境的表結(jié)構(gòu)不同來生成對(duì)應(yīng)的表結(jié)構(gòu)修改語句。mysql在5.7版本就提供了一個(gè)自帶的mysqldiff工具。參數(shù)比較多，直接舉例說明生成difftype=sql的ALTER語句的命令寫法，如下：

 mysqldiff --server1=root@host1 --server2=root@host2 
          --show-reverse --difftype=sql 
          db1.table1:dbx.table3

# server1 on host1: ... connected.
# server2 on host2: ... connected.
# Comparing db1.table1 to dbx.table3                               [FAIL]
# Transformation statements:

# --destination=server1:
ALTER TABLE db1.table1
  ADD COLUMN notes char(30) AFTER a,
  CHANGE COLUMN misc misc char(55);

# --destination=server2:
# ALTER TABLE dbx.table3
#   DROP COLUMN notes,
#   CHANGE COLUMN misc misc char(30);

具體看mysql的官方文檔：https://dev.mysql.com/doc/mys...

如果在mysql5.7版本以下就使用mysqldump命令參數(shù)輸出簡潔的表結(jié)構(gòu)，隨后diff文件然后自行編寫ALTER語句吧。

mysqldump --skip-comments --skip-extended-insert -u root -p dbName1>file1.sql
mysqldump --skip-comments --skip-extended-insert -u root -p dbName2>file2.sql
diff file1.sql file2.sql

心急火燎的進(jìn)入mysql終端處理線上問題，這時(shí)候一個(gè)語法拼不對(duì)，還得切出去查手冊(cè)？萬一環(huán)境是內(nèi)網(wǎng)不能上網(wǎng)就更是麻煩，其實(shí)mysql內(nèi)建了幫助手冊(cè)，可以直接在終端查詢。

? 是交互式mysql終端內(nèi)建的幫助命令。可以按照此幫助查閱文檔等。其輸出如下

mysql> ?

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   (
) 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   (
) Disable pager, print to stdout.
notee     (	) 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.

s 可查看當(dāng)前狀態(tài)，版本，客戶端ip，QPS等，!可以在mysql終端中執(zhí)行shell命令。在是很多處理問題的時(shí)候終端界面一進(jìn)入mysql的交互式終端，就找不到ip、端口等在bash中的信息了又需要頻繁切出，這兩個(gè)命令都非常有用。s輸出如下：

mysql> s
--------------
mysql  Ver 14.14 Distrib 5.7.12, for osx10.11 (x86_64) using  EditLine wrapper



Connection id:        2
Current database:    rizhiyi_system
Current user:        root@localhost
SSL:            Not in use
Current pager:        less
Using outfile:        ""
Using delimiter:    ;
Server version:        5.7.12 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:            21 sec

Threads: 1  Questions: 74  Slow queries: 0  Opens: 171  Flush tables: 1  Open tables: 164  Queries per second avg: 3.523
--------------

? contents 可以看內(nèi)建的幫助手冊(cè)

mysql> ? contents
You asked for help about help category: "Contents"
For more information, type "help ", where  is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

然后就可以繼續(xù)查看子菜單幫助手冊(cè)的內(nèi)容，比如? Functions

mysql> ? Functions
You asked for help about help category: "Functions"
For more information, type "help ", where  is one of the following
categories:
   Bit Functions
   Comparison operators
   Control flow functions
   Date and Time Functions
   Encryption Functions
   Information Functions
   Logical operators
   Miscellaneous Functions
   Numeric Functions
   String Functions

? insert 幫助命令還可以直接按關(guān)鍵字進(jìn)行模糊查詢，如：

mysql> ? insert
Name: "INSERT"
Description:
Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

INSERT inserts new rows into an existing table. The INSERT ... VALUES
and INSERT ... SET forms of the statement insert rows based on
explicitly specified values. The INSERT ... SELECT form inserts rows
selected from another table or tables. INSERT ... SELECT is discussed
further in [HELP INSERT SELECT].

When inserting into a partitioned table, you can control which
partitions and subpartitions accept new rows. The PARTITION option
takes a comma-separated list of the names of one or more partitions or
subpartitions (or both) of the table. If any of the rows to be inserted
by a given INSERT statement do not match one of the partitions listed,
the INSERT statement fails with the error Found a row not matching the
given partition set. See
http://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.html, for
more information and examples.

In MySQL 5.7, the DELAYED keyword is accepted but ignored by the
server. See [HELP INSERT DELAYED], for the reasons for this.

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

本文中常用命令的更詳細(xì)內(nèi)容可看MySQL手冊(cè)：
https://dev.mysql.com/doc/refman/5.7/en/programs-overview.html