MySQL常用命令

Jev Tse ? ? Linux干貨, 數據庫運維

本文大綱

MySQL命令             （0%）

交互式CLI工具    
服務端命令
mysqld服務器程序
數據類型

DDL語句                   （40%）

數據庫操作
表操作

DML語句                  （60%）

插入：INSERT    
刪除：DELETE    
更新：UPDATE
檢索：SELECT    
單表查詢
多表查詢
子查詢

 

MySQL命令

 

1、mysql：交互式CLI工具；命令化運行；

2、客戶端命令：于客戶端執行；

3、服務端命令：SQL語句，需要一次性完整地發往服務端；語句必須有結束符；

 

交互式CLI工具

 

交互式CLI工具：mysql [options] db_name

-h host_name，–host=host_name

服務端地址，默認為localhost；

-u user_name，–user=user_name

用戶名，默認為root；

-p[password]，–password[=password]

用戶密碼，默認為空密碼；

-P port_num ，–port=port_num

服務端端口，默認為3306；

–protocol={TCP|SOCKET|PIPE|MEMORY}

本地通信：基于本地回環地址進行請求，將基于本地通信    協議；Linux：SOCKET；Windows：PIPE，MEMORY

非本地通信：使用非本地回環地址進行的請求 TCP協議；

-S path，–socket=path

SOCKET路徑

-D db_name ，–database=db_name

指明連接到服務器后，默認進入的數據庫

-C，–compress

數據壓縮傳輸

-e statement，–execute=statement

非交互模式執行SQL語句；

-E，–vertical

查詢結果縱向顯示；

 

服務端命令

 

1、服務端命令：SQL語句，需要一次性完整地發往服務端；語句必須有結束符；

服務端命令：SQL語句，需要一次性完整地發往服務端；語句必須有結束符

?                (\?) 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.

note                (\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.

2、mysql命令的使用幫助：

# man mysql
# mysql  --help  --verbose

3、sql腳本運行：

mysql [options] [DATABASE] < /PATH/FROM/SOME_SQL_SCRIPT

mysqld服務器程序

 

mysqld服務器程序：工作特性的定義方式

1、服務器參數/變量：設定MySQL的運行特性；

mysql> SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]；

2、狀態（統計）參數/變量：保存MySQL運行過程中的統計數據或狀態數據；通常不能修改；

mysql> SHOW [GLOBAL | SESSION] STATUS [like_or_where];

3、顯示單個變量設定值的方法：

mysql> SELECT @@[global.|session.]system_var_name

%：匹配任意長度的任意字符；

_：匹配任意單個字符；

4、變量/參數級別：

全局：global，為所有會話設定默認；

會話：session，跟單個會話相關；會話建立會從全局繼承；

5服務器變量的調整方式：

運行時修改：

global：僅對修改后新建立的會話有效；

session：僅對當前會話有效，且立即生效；

通過配置文件修改，重啟后生效；

6、運行時修改服務器變量值操作方法：

mysql> HELP SET
SET [GLOBAL | SESSION] system_var_name = expr
SET [@@global. | @@session. | @@]system_var_name = expr

7、安裝完成后的安全初始化：

#mysql_secure_installation

8、運行前常修改的參數：

innodb_file_per_table=ON    修改InnoDB為獨立表空間模式
skip_name_resolve=ON       禁止MySQL對外部連接進行DNS解析
sql_safe_updates=ON         阻止不帶條件更新操作

 

數據類型

 

MySQL的數據類型：字符型、數值型、日期時間型、內建類型

SQL中的字符類型：  通過mysql> help 'Data Types'命令可獲取幫助

字

符

型

定長字符型：

CHAR(#) 不區分字符大小寫

BINARY(#) 區分字符大小寫

變長字符型：

VARCHAR(#) 不區分字符大小寫,需要在字符長度加1,最多65536個字符

VARBINARY(#)：不區分字符大小寫,需要在字符長度加1,最多65536個字符

對象存儲：

TEXT：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT

BLOB：TINYBLOB，BLOB，MEDIUMBLOB，LONGBLOB

數

值

型

精確數值型：

INT(TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT)整型

DECIMAL十進制數據

近似數值型：

 

FLOAT 單精度浮點型

DOUBLE 雙精度浮點型

REAL

BIT

日期時間型

DATE 日期型

TIME 時間型

DATETIME 日期時間型

TIMESTAMP 時間戳

YEAR(2) 2位年數

YEAR(4) 4位年數

內建類型

SET     集合

ENUM   枚舉

類型修飾符

字符型：NOT NULL，NULL，DEFALUT 'STRING'，CHARACET SET 'CHARSET'，COLLATION 'collocation'

整型：NOT NULL， NULL， DEFALUT value，AUTO_INCREMENT，UNSIGNED

日期時間型：NOT NULL， NULL， DEFAULT

 

SQL MODE：定義mysqld對約束等違反時的響應行為等設定；

常用的MODE：

 

TRADITIONAL 

嚴格模式，保證錯誤數據不能插入，報error錯誤。

STRICT_TRANS_TABLES 

如果給定的值非法，則放棄該語句，返回錯誤并繼續執行剩余的行。

STRICT_ALL_TABLES 

如果給定的值非法，則放棄該語句，返回錯誤并忽視剩余的行。

修改方式：

mysql> SET GLOBAL sql_mode='MODE'; 

mysql> SET @@global.sql_mode='MODE'; 

MySQL中字符大小寫

1.SQL關鍵字及函數不區分大小寫

2.數據庫、表及視圖名稱的大小寫區分與否取決于底層OS和FS

3.存儲過程、存儲函數及時間調度器的名字不區分大小寫，但觸發器區分大小寫

4.表別名區分大小寫

5.對字段中的數據，如果字段類型為binary類型，則區分大小寫，非binary不區分大小寫

 

DDL語句

 

DDL：Data Defination Language，數據庫定義語言

mysql> HELP Data Definition
     CREATE, ALTER, DROP
     DATABASE, TABLE
     INDEX, VIEW, USER
     FUNCTION, FUNCTION UDF, PROCEDURE, TABLESPACE, TRIGGER, SERVER

 

數據庫操作

 

新建數據庫：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name CHARACTER SET [=] charset_name COLLATE [=] collation_name；

修改數據庫：

ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name  COLLATE [=] collation_name；

刪除數據庫：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name；

表操作

 

1、創建新表：CREATE

(1)、直接創建表：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options]；
CREATE TABLE [IF NOT EXISTS] tble_name (col_name  data_typ|INDEX|CONSTRAINT)；

(2)、直接創建表，并將查詢語句的結果插入到新創建的表中；

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] select_statement；

(3)、復制某存在的表的結構來創建新的空表；

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }；

2、刪除表：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name]；

3、修改表：

ALTER  TABLE tbl_name [alter_specification [, alter_specification] ...]；

可修改內容：

(1) table_options

(2) 添加定義：字段、字段集合、索引、約束

ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]；

(3) 修改字段：

修改字段名稱，并設置字段的位置：

CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]；

修改字段類型及屬性及位置：

MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]；

(4) 刪除操作：字段、索引、約束

DROP [COLUMN] col_name；

表重命名 

RENAME [TO|AS] new_tbl_name

4、表查看

查看表結構定義：

DESC tbl_name；

查看表定義：

SHOW CREATE TABLE tbl_name

查看表屬性信息：

SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

各參數詳解：

【IF NOT EXISTS】：如果不存在則執行操作，，用于避免mysql報錯；

【IF EXISTS】：如果存在則執行操作，用于避免mysql報錯；

【CHARACTER SET [=] charset_name】：設置默認的字符集；

【COLLATE [=] collation_name】：設置默認的排序方式；

【TEMPORARY】：創建臨時表；

【create_definition】：這是表的列屬性部分，創建表時，表要至少包含一列；

【(create_definition,…)】參數：定義字段、字段集合、索引、約束等；

 col_name type [NOT NULL|NULL] [DEFAULT] [UNIQUE] [AUTO_INCREMENT] [PRIMARY KEY][FOREIGN KEY] [reference_definition]

    col_name：表示表中字段的名稱

    type：表示字段的數據類型

    NOT NULL|NULL：指出該列是否允許是空值，系統一般默認允許為空值，所以當不允許為空值時，必須使用NOT NULL；

    DEFAULT：為該字段設置默認值

    UNIQUE：表示該字段的值是唯一的

    AUTO_INCREMENT：表示該字段的值是自動編號（自動增加），每個表只能有一個AUTO_INCREMENT列，并且必須被索引；

    PRIMARY KEY：設置該字段為的主鍵，如果沒設定，而某些應用程序需要，MySQL會將第一個沒有任何NULL列的唯一鍵，作為主鍵；

    FOREIGN KEY：表示該字段為該表的外鍵，是與之聯系的某表的主鍵；

    reference_definition：為字段添加注釋；

【table_options】參數：定義表的特性參數

   ENGINE [=] engine_name：定義存儲引擎；從5.5開始默認為InnoDB，版本小于5.5默認為MyISAM；

      查看支持的所有存儲引擎：mysql> SHOW ENGINES;

      查看指定表的存儲引擎：mysql> SHOW TABLE STATUS LIKE clause;

   AUTO_INCREMENT [=] VALUE ：指定AUTO_INCREMENT的起始值

   [DEFAULT] CHARACTER SET [=] charset_name ：指定字符集

   CHECKSUM [=] {0 | 1} ：是否使用校驗值，不建議使用

   [DEFAULT] COLLATE [=] collation_name：指定排序規則

   COMMENT [=] 'string'：表的注釋信息

   DELAY_KEY_WRITE [=] {0 | 1} ：是否啟用間延遲寫入

   TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] 定義表空間

   ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}：定義表的格式

     DEFAULT默認,DYNAMIC動態,FIXED靜態,COMPRESSED壓縮,REDUNDANT冗余,COMPACT緊致

【select_statement】：SELECT語句，用于快速創建表

【AS】：別名

索引：數據結構

創建：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...)；

查看：

SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]；

刪除：

DROP  INDEX index_name ON tbl_name

索引類型：

聚集索引、非聚集索引：索引是否與數據存在一起；

主鍵索引、輔助索引

稠密索引、稀疏索引：是否索引了每一個數據項；

BTREE（B+）、HASH、R Tree、FULLTEXT

BTREE：左前綴；

EXPLAIN：分析查詢語句的執行路徑；

視圖：VIEW

虛表：存儲下來的SELECT語句；

創建：

CREATE  VIEW view_name [(column_list)] AS select_statement；

修改：

ALTER  VIEW view_name [(column_list)] AS select_statement；
刪除：
DROP VIEW [IF EXISTS] view_name [, view_name] ...；

 

DML語句

 

DML: Data Manipulation Language，數據庫操縱語言

查看所有DML語句：

mysql> HELP Data Manipulation
     INSERT/REPLACE，DELETE，UPDATE，SELECT

 

插入：INSERT

 

單行插入

批量插入

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
      [INTO] tbl_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
      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 [(col_name,...)]
      SELECT ...
      [ ON DUPLICATE KEY UPDATE
      col_name=expr
      [, col_name=expr] ... ]

 

刪除：DELETE

DELETE  FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

 

注意：一定要有限制條件，否則將清空整個表；

限制條件：

[WHERE where_condition]
[ORDER BY ...] [LIMIT row_count]

 

更新：UPDATE

UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
      [WHERE where_condition]
      [ORDER BY ...]
      [LIMIT row_count]

注意：一定要有限制條件，否則將修改整個表中指定字段的數據；

限制條件：

[WHERE where_condition]
[ORDER BY ...] [LIMIT row_count]

注意：sql_safe_updates變量可阻止不帶條件更新操作；

 

檢索：SELECT

 

Query Cache：緩存查詢的執行結果；

key：查詢語句的hash值；

value：查詢語句的執行結果；

SQL語句的編寫方式：

SELECT name FROM tbl2;
select name from tbl2;

查詢執行路徑：

請求–>查詢緩存：命中后返回；

請求–>查詢緩存–>解析器–>預處理器–>優化器–>查詢執行引擎–>存儲引擎–>緩存–>響應

默認不緩存，非確定結果不緩存  大于或小于某范圍不緩存

SELECT語句的執行流程：

FROM  –> WHERE –> Group By –> Having –> Order BY –> SELECT –> Limit

 

單表查詢

 

SELECT
      [ALL | DISTINCT | DISTINCTROW ]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
      select_expr [, select_expr ...]
      [FROM table_references
      [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}]

 

用法：

SELECT col1, col2, ... FROM tble_name；
SELECT col1, col2, ... FROM tble_name WHERE clause；
SELECT col1, col2, ... FROM tble_name [WHERE clause] GROUP BY col_name [HAVING clause]；

分組的目的在于聚合計算：avg, max, min, count, sum,

 

DISTINCT：數據去重；

SQL_CACHE：顯式指定緩存查詢語句的結果；

SQL_NO_CACHE：顯式指定不緩存查詢語句的結果；

 

query_cache_type服務器變量有三個值：

ON：啟用；

SQL_NO_CACHE：不緩存；默認符合緩存條件都緩存；

OFF：關閉；

DEMAND：按需緩存；

SQL_CACHE：緩存；默認不緩存；

字段可以使用別名 ：

col1 AS alias1, col2 AS alias2, ...

 

WHERE子句：指明過濾條件以實現"選擇"功能；

過濾條件：布爾型表達式；

[WHERE where_condition]

WHERE操作符：  [WHERE where_condition]

算術操作符：

"+"、 "-"、 "*"、 "/"、 "%"

比較操作符：

"="、 "<>"、 "!= "、 "<=>"、 ">"、 ">="、 "<"、 "<="

 注："<>"、"!= "均為不等于； "<=>"類似"="，用于與空值比較

邏輯操作符

"AND"；  "OR"；  "NOT"；  "XOR"

IS NULL

是否為空

IS NOT NULL

是否不為空

BETWEEN min AND max

指定區間

IN

給定條件列表

LIKE

模糊比較，"%"、"_"

RLIKE或REGEXP

使用正則匹配

GROUP BY：根據指定的字段把查詢的結果進行"分組"以用于"聚合"運算；

聚合運算：GROUP BY

avg()

平均值

max()

最大值

sum()

求和

min()

最小值

count()

計數

   

ASC

升序

DESC

降序

HAVING

對分組聚合后的結果進行條件過濾

LIMIT：對輸出結果進行數量限制

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

offset：偏移的行數； row_count：顯示行數；

 

多表查詢

 

連接操作：

    交叉連接：笛卡爾積；

   內連接：

            等值連接：讓表之間的字段以等值的方式建立連接；

           不等值連接：

           自然連接

           自連接

      外連接：

             左外連接：

     FROM tb1 LEFT JOIN tb2 ON tb1.col = tb2.col

             右外連接：

     FROM tb1 RIGHT JOIN tb2 ON tb1.col = tb2.col

 

子查詢

 

子查詢：在查詢中嵌套查詢；

 

用于WHERE子句中的子查詢；

(1) 用于比較表達式中的子查詢：子查詢僅能返回單個值；

(2) 用于IN中的子查詢：子查詢可以返回一個列表值；

(3) 用于EXISTS中的子查詢：

用于FROM子句中的子查詢；

SELECT tb_alias.col1, ... FROM (SELECT clause) AS tb_alias WHERE clause;

 

聯合查詢：將多個查詢語句的執行結果相合并；

UNION
     SELECT clause UNION SELECT cluase；

 

 

 

 

本文完【返回頁首】

 

原創文章，作者：Jev Tse，如若轉載，請注明出處：http://www.www58058.com/68929

贊 (0)
Jev TseJev Tse
上一篇 2017-02-16 15:26
下一篇 2017-02-16 17:39

相關推薦

欧美性久久久久