摘要:比多一點兒一常用命令這是系列筆記的第一部分,本系列筆記希望能按照筆者自己學習技術的經歷來記錄,避免純粹按照內容一塊一塊總結,也就是不同于一般按內容分配章節的書籍的結構,有一個平滑的閱讀曲線。第一篇的主要內容會是最常用的一些命令。
比CRUD多一點兒(一):MySQL常用命令
這是MySQL系列筆記的第一部分,本系列筆記希望能按照筆者自己學習MySQL技術的經歷來記錄,避免純粹按照內容一塊一塊總結,也就是不同于一般按內容分配章節的書籍的結構,有一個平滑的閱讀曲線。內容比較豐富的技術點會按照專題在多個學習筆記中逐漸深入。文章列表點此。
首先,請如果還完全為接觸過MySQL的讀者先閱讀w3c的SQL教程,再來讀本篇內容。
這部分的標題叫比CRUD多一丁點兒,比起最基礎的w3c的SQL教程之外,只多一點的擴展,滿足應付從純粹閱讀入門資料到可以上手完成一個簡單的工作的需求。
第一篇的主要內容會是最常用的一些mysql命令。因為雖然有很多圖形化的工具,但在實際的工作中因為需要去線上服務查看數據,處理問題,字符界面的命令行工具是必不可少的。
客戶端程序mysql: 連接交互式終端mysql -h $host -P $port -u $user -p$passsword $database_name
例如用戶root使用密碼mydb@xxx用鏈接到地址為192.168.1.99,端口為3306的數mysql進程,并默認使用上名為testdb的數據庫(即自動執行use testdb)。
mysql -h 192.168.1.99 -P 3306 -u"root" -p"mydb@xx" testdb
各參數如果有@,&等bash的關鍵字,則需要用""引起來。
非交互式的執行SQL有時候需要在命令行執行某句SQL,則建議使用 -Bse 參數。-B參數的含義是執行多條語句(batch)這樣可以執行多條,-e即是執行(execute),-s參數意思是靜默執行(silent)可以讓輸出格式精簡一些。
mysql -h $host -P $port -u $user -p$passsword -Bse "command1;command2;....;commandn"
例如,常見的將執行結果導出到文件中方便留存和閱讀。
mysql -h 192.168.1.99 -P 3306 -u"root" -p"mydb@xx" -Bse "select id,name from testdb.Account;" > result.txt導出程序mysqldump
mysqldump是做數據導出的命令行工具,也是mysql安裝后默認會帶的。作用是將mysql數據庫中的數據導出出來。
導出特定的表的內容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參數只導出表結構,
mysqldump -h 192.168.1.99 -P 3306 -u"root" -p"mydb@xx" --no-data mydb > result.sql比較表結構mysqldiff
在開發實踐中難免會遇到校驗數據表結構不同,或者根據開發環境和目標環境的表結構不同來生成對應的表結構修改語句。mysql在5.7版本就提供了一個自帶的mysqldiff工具。參數比較多,直接舉例說明生成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命令參數輸出簡潔的表結構,隨后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內建?, s命令
心急火燎的進入mysql終端處理線上問題,這時候一個語法拼不對,還得切出去查手冊?萬一環境是內網不能上網就更是麻煩,其實mysql內建了幫助手冊,可以直接在終端查詢。
? 是交互式mysql終端內建的幫助命令。可以按照此幫助查閱文檔等。其輸出如下
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 可查看當前狀態,版本,客戶端ip,QPS等,!可以在mysql終端中執行shell命令。在是很多處理問題的時候終端界面一進入mysql的交互式終端,就找不到ip、端口等在bash中的信息了又需要頻繁切出,這兩個命令都非常有用。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 可以看內建的幫助手冊
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
然后就可以繼續查看子菜單幫助手冊的內容,比如? 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 幫助命令還可以直接按關鍵字進行模糊查詢,如:
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
本文中常用命令的更詳細內容可看MySQL手冊:
https://dev.mysql.com/doc/refman/5.7/en/programs-overview.html
文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。
轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/17648.html
摘要:語句最基礎的用法不再贅述。而這樣刪掉整張表的語句是不會清空自增值的。 showImg(https://segmentfault.com/img/bV1ybF?w=2216&h=1288); 這是MySQL系列筆記的第一部分,本系列筆記希望能按照筆者自己學習MySQL技術的經歷來記錄,避免純粹按照內容一塊一塊總結,也就是不同于一般按內容分配章節的書籍的結構,會有一個平滑的閱讀曲線。內容比...
閱讀 3785·2023-04-26 02:07
閱讀 3671·2021-10-27 14:14
閱讀 2859·2021-10-14 09:49
閱讀 1624·2019-08-30 15:43
閱讀 2611·2019-08-29 18:33
閱讀 2369·2019-08-29 17:01
閱讀 915·2019-08-29 15:11
閱讀 582·2019-08-29 11:06