mysql常用命令-實例-史上最全

MySQL命令大全(2018.11.22阿星歸檔) 

mysql

登錄MySQL方法

1.單實例MySQL登錄的方法

mysql    #剛裝完系統(tǒng)無密碼情況登錄方式，不需要密碼mysql -u root  #剛裝完系統(tǒng)無密碼的情況登錄mysql -uroot -p   #這里標(biāo)準(zhǔn)的dba命令行登錄mysql -uroot -poldboy  #非腳本里一般不這樣用，密碼明文會泄露密碼

2.適合多實例防止密碼泄露的方法 
設(shè)置變量

HISTCONTROL=ignorespacemysql -uroot -poldboy -S /data/3306/mysql.sock前面加空格將不記錄登錄信息

給啟動腳本以及備份腳本等加700權(quán)限，用戶和組改為root

chmod 700 /data/3306/mysqlchmod 700 /server/scripts/bak.sh

刪除命令行記錄

history -d 歷史命令序號history -c 清除所有清除之后在root家目錄里面還會有記錄cat ~/.bash.history

多實例MySQL本地登錄

mysql -uroot -p -S /data/3306/mysql.sockmysql -uroot -p -S /data/3307/mysql.sock提示： 多實例通過mysql的-S 命令指定不同的sock文件登錄不同的服務(wù)中

注意：多實例的遠(yuǎn)程連接無需指定sock路徑

mysql -uroot -p -h 127.0.0.1 -P3307-h 指定IP地址，-P 指定端口號

登錄后默認(rèn)提示符是：mysql >這個提示符可以更改，就像linux命令行提示符一樣

mysql

為了防止誤操作，可以把提示符標(biāo)記為測試環(huán)境，也可以寫入配置永久生效

■ 在命令行修改登錄提示

mysql> prompt \u@abcdocker \r:\m:\s->PROMPT set to '\u@abcdocker \r:\m:\s->'root@abcdocker 05:03:09->root@abcdocker 05:03:11->root@abcdocker 05:03:12->

配置文件修改登錄提示符

在my.cnf配置中【mysql】模塊下添加如下內(nèi)容（注意，不是【mysqld】）保存后，無需重啟mysql，退出當(dāng)前session，重新登錄即可

[mysql]prompt=\\u@oldboy \\r:\\m:\\s->MySQL help幫助Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> SHOW DATABASES LIKE '%MY%';  模糊匹配mysql> show databases like 'd3306';mysql> show databases like '%3306';mysql> show databases like '%33%';更多參數(shù)help show

■ 強(qiáng)制關(guān)閉數(shù)據(jù)庫的方法（慎用）

killall mysqldpkill mysqldkillall -9 mysqldmysqld: no process killedkill -9 pid

■ 最好使用優(yōu)雅停止

mysqladmin -uroot -poldboy123 shutdown

■ 野蠻粗魯殺死數(shù)據(jù)庫導(dǎo)致故障企業(yè)案例：

http://oldboy.blog.51cto.com/2561410/1431161http://oldboy.blog.51cto.com/2561410/1431172

企業(yè)實戰(zhàn)題7：

開發(fā)mysql多實例啟動腳本：已知mysql多實例啟動命令為：mysqld_safe–defaults-file=/data/3306/my.cnf&停止命令為：mysqladmin-u root -poldboy123 -S /data/3306/mysql.sockshutdown請完成mysql多實例啟動啟動腳本的編寫要求：用函數(shù)，case語句、if語句等實現(xiàn)。

相關(guān)地址： 
開發(fā)MySQL多實例啟動腳本~ 

老男孩Shell企業(yè)面試題30道 [答案] 

MySQL數(shù)據(jù)庫安全策略介紹

為root設(shè)置比較復(fù)雜的密碼刪除無用的mysql庫內(nèi)的用戶賬號，只留root@localhost刪除默認(rèn)的test數(shù)據(jù)庫刪除用戶的時候，授權(quán)的權(quán)限盡量最小，允許訪問的主機(jī)范圍最小化針對mysql數(shù)據(jù)庫的用戶處理，還有更嚴(yán)格的做法，例如刪除root用戶，添加新的管理員用戶

truncalt table test和delete from test；區(qū)別

■ truncate table test；速度更快。直接清空對應(yīng)數(shù)據(jù)的物理文件。 
■ delete from test；速度慢，邏輯清除，按行刪

為管理員root用戶設(shè)置密碼方法

mysqladmin -u root password ‘oldboy’  #沒有密碼的情況下mysqladmin -uroot -p oldboy password oldboy123 -S /data/3306/mysql.sock

適合多實例更改密碼，強(qiáng)調(diào)，以上命令的是命令行執(zhí)行，而不是進(jìn)入root

■ 修改管理員root密碼法一： 
linux命令修改法

mysqladmin -uroot -p oldboy password ‘oldboy123’   #原密碼，新密碼mysqladmin -uroot -p oldboy password oldboy456 -S /data/3306/mysql.sock適合多實例

■ 修改管理員root密碼法二：****sql 
語句修改法

mysql> update mysql.user set password=oldboy456 where user='root' and host='localhost'; 這樣設(shè)置是不可以登錄的

這樣設(shè)置的密碼不可以使用，需要加密

mysql> update mysql.user set password=password('oldboy456') where user='root' and host='localhost';

結(jié)果：如果不使用password這個變量 下面的密碼將會是明文，明文我們無法登錄

mysql> select user,host,password from mysql.user;+------+-----------+-------------------------------------------+| user | host      | password                                  |+------+-----------+-------------------------------------------+| root | localhost | *7DB922C59F217871B8165D72BEC8ED731A0EFFA1 || root | db01      |                                           || root | 127.0.0.1 |                                           || root | ::1       |                                           ||      | localhost |                                           ||      | db01      |                                           |+------+-----------+-------------------------------------------+6 rows in set (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0需要查看是否成功，出現(xiàn)Changed代表成功

設(shè)置完成之后我們需要刷新才可以登錄

mysql> flush privileges;

修改管理員root（所有）密碼法三

set password=password('oldboy123');

MySQL密碼丟失如何找回？

■ 單實例

a、/etc/init.d/mysqld stopb、mysqld_safe --skip-grant-tables --user=mysql &c、mysqld、修改完密碼重啟e、/etc/init.d/mysqld restart

■ 多實例

/data/3306/mysql    stop  無法停止killall mysqldmysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables --user=mysql &update mysql.user set password=password('oldboy456') where user='root' and host='localhost';flush privileges;mysql 登錄mysqladmin -uroot -poldboy shutdown/etc/init.d/mysqld start

SQL結(jié)構(gòu)化查詢語言

什么是SQL？

SQL，英文全稱Structured Query Language，中文意思是結(jié)構(gòu)化查詢語言，它是一種對關(guān)系型數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行定義和操作的語言方法，是大多數(shù)關(guān)系數(shù)據(jù)庫管理系統(tǒng)所支持的工業(yè)標(biāo)準(zhǔn)語言。

結(jié)構(gòu)化查詢語言SQL是一種數(shù)據(jù)庫查詢和程序設(shè)計語言，用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫系統(tǒng)，同時用sql作為MySQL邏輯備份文件的擴(kuò)展名。結(jié)構(gòu)化查詢語言是高級的非過程化編程語言，允許用戶在高層數(shù)據(jù)結(jié)構(gòu)上工作。他不要求用戶指定對數(shù)據(jù)存放方法，也不需要用戶了解具體的數(shù)據(jù)存放方式。

小結(jié)：SQL語句最常見的分類一般就是3類

DDL（DataDefinition Language）——數(shù)據(jù)定義語言（CREATE，ALTER，DROP）管理基礎(chǔ)數(shù)據(jù)，例如：庫，表DCL （DataControl Language）——數(shù)據(jù)控制語言（GRANT，REVOKE，COMMIT，ROLLBACK）用戶授權(quán)，權(quán)限回收，數(shù)據(jù)提交回滾等DML（DataManipulation Language）——數(shù)據(jù)操作語言（SELECT，INSERT，DELETE,UPDATE）針對數(shù)據(jù)庫里的表里的數(shù)據(jù)進(jìn)行操作，記錄

命令講解

■ 創(chuàng)建數(shù)據(jù)庫

mysql> create database abcdocker;

■ 查看創(chuàng)建庫的語句

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

編譯的時候指定了utf8 所以這里顯示utf8

1.網(wǎng)站程序字符集2.客戶端的字符集3.服務(wù)器端字符集4.linux客戶端字符集5.以上都要統(tǒng)一，否則會出現(xiàn)中文亂碼

■ 創(chuàng)建不同字符集格式的數(shù)據(jù)庫命令

mysql> create database abcdocker;          #默認(rèn)數(shù)據(jù)庫配置，相當(dāng)于創(chuàng)建拉丁字符集數(shù)據(jù)庫mysql> create database abcdocker_gbk character set gbk collate gbk_chinese_ci;創(chuàng)建gbk格式文件mysql> help create databasemysql> show character set;           #查看字符集mysql> show create database oldboy_gbk;+------------+--------------------------------------------------------------------+| Database| Create Database|+------------+--------------------------------------------------------------------+| abcdocker_gbk | CREATE DATABASE `oldboy_gbk` /*!40100 DEFAULT CHARACTER SET gbk */ |+------------+--------------------------------------------------------------------+1 row in set (0.00 sec)

如果編譯的時候指定了特定的字符集，則以后創(chuàng)建對應(yīng)字符集的數(shù)據(jù)庫就不需要指定字符集

-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \提示：二進(jìn)制軟件包，安裝的數(shù)據(jù)庫字符集默認(rèn)latinl

■ 查看數(shù)據(jù)庫

show databases;select database();     #相當(dāng)于pwdselect user();         #查看當(dāng)前用戶select version();   #查看當(dāng)前版本()可以說是函數(shù)

■ 當(dāng)前數(shù)據(jù)庫包含的表信息

use oldboy      #相當(dāng)于cdshow tables     #查看表orshow tables from wordpress      #查看wordpress庫下的表文件

■ 刪除用戶

drop user 'root'@'::1';如果drop刪除不了（一般reshuffle符號或大寫）可以用下面方式刪除（以root，用戶為例）delete from mysql.user where user=’root’ and host=’oldboy’;flush privileges;

創(chuàng)建MySQL用戶及賦予用戶權(quán)限

1.通過在mysql中輸入helpe grant得到如下信息

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';GRANT ALL ON db1.* TO 'jeffrey'@'localhost';GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

2.通過grant命令創(chuàng)建用戶并授權(quán)

grant命令簡單語法如下：grant all privileges on dbname.* to username@localhost identified by ‘passwd’;

說明：上述命令是授權(quán)l(xiāng)ocalhost主機(jī)上通過用戶username管理dbname數(shù)據(jù)庫的所有權(quán)限，密碼為passwd，其中username，dbname，passwd可根據(jù)業(yè)務(wù)的情況修改

　　對于web連接用戶授權(quán)盡量采用最小化原則，很多開源軟件都是web界面安裝，因此在安裝期間除了select,insert,update,delete 4個權(quán)限外，還需要create,drop等比較危險的權(quán)限。

grant select,insert,update,create,drop on blog.* to blog@localhost identified by ‘123’

　　常規(guī)情況下授權(quán)select,insert,update,delete 4個權(quán)限即可，有的源軟件，例如discuz,bbs還需要create,drop等比較危險的權(quán)限。生成數(shù)據(jù)庫表后，要收回create，drop授權(quán)

生產(chǎn)環(huán)境針對主庫（寫入主讀為輔）用戶的授權(quán)；

■ 普通環(huán)境：

本機(jī)：lnmp，lamp環(huán)境數(shù)據(jù)庫授權(quán)grant all privileges ON blog.* to blog@localhost identified by ‘123456’應(yīng)用服務(wù)器和數(shù)據(jù)庫服務(wù)器不在一個主機(jī)上授權(quán)；grant all privileges ON blog.* to blog@10.0.0.% identified by ‘123’嚴(yán)格的授權(quán)：重視安全，忽略了方便；grant select,insert,update,delete ON blog.* to blog@10.0.0.% identified by ‘123’生產(chǎn)環(huán)境從庫（只讀）用戶的授權(quán)；grant select ON blog.* to blog@10.0.0.% identified by ‘123’查看授權(quán)用戶oldboy的具體的授權(quán)權(quán)限show grants for ‘oldboy’@’localhost’;

案例1.創(chuàng)建abcdocker用戶，對test庫具備所有權(quán)限，允許從localhost主機(jī)登錄，密碼是abcdocker123

grant all on test.* to abcdocker@localhost identified by 'abcdocker123';

■ 第一種：授權(quán)用戶

grant all on test.* to oldboy@127.0.0.% identified by ‘oldboy123’show grants for oldboy@’127.0.0.%’;           查看授權(quán)用戶+-------------------------------------------------------------------------------------------------------------+| Grants for root@127.0.0.1|+-------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' || GRANT ALL PRIVILEGES ON `test`.* TO 'root'@'127.0.0.1'                                                     |+-------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

■ 第二種：授權(quán)方法

create user bbs@'172.16.1.1/255.255.255.0' identified by '123456'；先授權(quán)可以登錄的mysql> show grants for bbs@'172.16.1.1/255.255.255.0';mysql> grant select on wordpress.* to bbs@'172.16.1.1/255.255.255.0';

授權(quán)局域網(wǎng)主機(jī)連接遠(yuǎn)程數(shù)據(jù)庫

a.一條命令百分號匹配法

grant all on *.* to‘test@10.0.0.%’identified by ‘test123’;

b、一條命令子網(wǎng)掩碼配置法

grant all on *.* to test@’10.0.0.0/255.255.255.0’ identified by ‘test123’;

c、兩條命令實現(xiàn) 
先創(chuàng)建用戶并設(shè)置密碼；

create user test@’10.0.0.%’ identified by ‘test123’;再對用戶授權(quán)指定權(quán)限和管理庫表grant all on *.* to test@10.0.0.0/255.255.255.0

最后記得上述每條grant命令都要刷新權(quán)限

flush privileges

數(shù)據(jù)庫遠(yuǎn)程登錄

mysql -uwordpress -poldboy123 -h 172.16.1.51 -P3306-h指定IP地址，-P指定服務(wù)端口號

創(chuàng)建類似于root系列的管理員用戶，可以創(chuàng)建下級用戶的用戶

grant all privileges on *.* to root@'127.0.0.1' identified by 'oldboy123' with grant option;只需要在最后輸入with grant option

回收用戶權(quán)限

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

顯示庫的信息

mysql> show create database oldboy\G

MySQL包含關(guān)系 
數(shù)據(jù)庫服務(wù)器>數(shù)據(jù)庫（多個實例）>多個庫>多個表>多個字段行列（數(shù)據(jù)）

創(chuàng)建表

建表的基本命令語法：

create table <表名>（<字段名1><類型n>);提示：其中create table是關(guān)鍵字，不能更改，但是大小寫可以變化

建表語句

下面是人工寫法設(shè)計的建表語句例子，表名student

    create table student(    id int(4) not null,    name char(20) not null,    age tinyint(2)  NOT NULL default '0',    dept varchar(16)  default NULL    );

查看創(chuàng)建表的語句

mysql> show create table student;create table student(       #create table表示創(chuàng)建表的固定關(guān)鍵字,student為表名id int(4) not null,        #學(xué)號列,數(shù)字類型，長度為4，不能為空值name char(20) not null,    #名字列，定長字符類型，長度20，不能為空age tinyint(2)  NOT NULL default '0', #年齡列，很小的數(shù)字類型，長度為2，不能為空，默認(rèn)為0值dept varchar(16)  default NULL    #系別列，變長字符類型，長度16，默認(rèn)為空。ENGINE=lnnoDB DEFAULT CHARSET=latinl  #引擎和字符集，引擎默認(rèn)為InnoDB，字符集，繼承庫的latinl);

student表的直觀顯示，可以用下面表格顯示。 

create table student(id int(4) not null,name char(20) not null,age tinyint(2)  NOT NULL default '0',dept varchar(16)  default NULL)ENGINE=InnoDB DEFAULT CHARSET=latinl;

需要注意的事：MySQL5.1和MySQL5.5 
環(huán)境的默認(rèn)建表語句中的引擎的不同，如果希望控制引擎，就要在建表語句里顯示的指定引擎建表；

MySQL5.1以及默認(rèn)引擎為MyISAM，MySQL5.5以后默認(rèn)引擎為InnoDB

MySQL表的字段類型

1） 數(shù)字類型 

2）日期和時間類型（DATE 日期類型：支持的范圍是1000-01-01到9999-12-31。MySQL以YYYY-MM-DD格式來顯示DATE值，但是允許你使用字符串或數(shù)字把值賦給DATE列）

3）字符串類型 

1、 INT[(M)]型：正常大小整數(shù)類型

2、 CHAR(M)型：定長字符串類型，當(dāng)存儲時，總是用空格填滿右邊到指定的長度

3、 VARCHAR型：變長字符串類型

有關(guān)MySQL字段類型詳細(xì)內(nèi)容，可以參考MySQL手冊

1． INT[(M)]型：正常大小整數(shù)類型

2． DOUBLE[M,D] [ZEROFILL]型：正常大?。p精密）浮點數(shù)字類型

3． DATE 日期類型：支持的范圍是1000-01-01到9999-12-31.MySQL以YYY-MM-DD格式來顯示DATE值，但是允許你使用字符串給數(shù)字把值賦給DATE列

4． CHAR(M)型：定長字符串類型，當(dāng)存儲時，總是是用空格填滿右邊到指定的長度

5． BLOB TEXT類型，最大長度65535（2^16-1）個字符

6． VARCHAR型：變長字符串類型

下面的圖說明了CHAR和VARCHAR之間的差別： 

解釋：例如，VARCHAR(10)列可以容納最大長度為10的字符串。實際存儲需求是字符串（L）的長度，加上一個記錄字符串長度的字節(jié)。對于字符串’abcd’，L是4，存儲需要5個字節(jié)。

小結(jié)

■ char定長，不夠的用空格補(bǔ)全，浪費存儲空間，查詢速度快，多數(shù)系統(tǒng)表字段都是定長 
■ varchar變長，查詢速度慢

例子：mysql.user用的就是定長

`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',

user表用的是CHAR

生產(chǎn)場景案例： 
某sns產(chǎn)品生產(chǎn)正式建表語句

use sns;set names gbk;CREATE TABLE `subject_comment_manager` (`subject_comment_manager_id` bigint(12) NOT NULL auto_increment COMMENT '主鍵',`subject_type` tinyint(2) NOT NULL COMMENT '素材類型',`subject_primary_key` varchar(255) NOT NULL COMMENT '素材的主鍵',`subject_title` varchar(255) NOT NULL COMMENT '素材的名稱',`edit_user_nick` varchar(64) default NULL COMMENT '修改人',`edit_user_time` timestamp NULL default NULL COMMENT '修改時間',`edit_comment` varchar(255) default NULL COMMENT '修改的理由',`state` tinyint(1) NOT NULL default '1' COMMENT '0代表關(guān)閉，1代表正常',PRIMARY KEY  (`subject_comment_manager_id`),KEY `IDX_PRIMARYKEY` (`subject_primary_key`(32)), #<==括號內(nèi)的32表示對前32個字符做前綴索引。KEY `IDX_SUBJECT_TITLE` (`subject_title`(32))KEY `index_nick_type` (`edit_user_nick`(32),`subject_type`)#<==聯(lián)合索引，此行為新加的，用于給大家講解的。實際表語句內(nèi)沒有此行。) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

為表的字段創(chuàng)建索引* * * * * *

索引就象書的目錄一樣，如果在字段上建立了索引，那么以索引為查詢條件時可以加快查詢數(shù)據(jù)的速度，這是mysql優(yōu)化的重要內(nèi)容之一。

創(chuàng)建主鍵索引
查詢數(shù)據(jù)庫，按主鍵查詢是最快的，每個表只有一個主鍵列，但是可以用多個普通索引列。主鍵列要求列的所有內(nèi)容必須唯一，而普通索引不要求內(nèi)容必須唯一
主鍵就類似我們在學(xué)校學(xué)習(xí)時的學(xué)號一樣，班級內(nèi)是唯一的，整個表的每一條記錄的主鍵值在表內(nèi)都是唯一的，用來唯一標(biāo)識一條記錄。
首先，無論建立主鍵索引還是普通索引，都要在表的對應(yīng)列上創(chuàng)建，可以對單列創(chuàng)建索引，也可以對多列創(chuàng)建索引。

為表的字段創(chuàng)建索引*****
數(shù)據(jù)庫的索引就象書的目錄一樣，如果在字段上建立了索引，那么多以索引列為查詢條件時可以加快查詢數(shù)據(jù)的速度，這是mysql優(yōu)化的重要內(nèi)容之一。

創(chuàng)建主鍵索引      
查詢數(shù)據(jù)庫，按主鍵查詢是最快的，每個表只能有一個主鍵列，但是可以有多個普通索引列。主鍵列的所有內(nèi)容必須唯一，而普通索引列不要求內(nèi)容必須唯一。
主鍵就類似我們在學(xué)習(xí)學(xué)習(xí)時的學(xué)號一樣，班級內(nèi)是唯一的，整個表的每一條記錄的主鍵值在表內(nèi)都是唯一的，用來唯一標(biāo)識一條記錄。
首先，無論建立主鍵索引還是普通索引，都要在表的對應(yīng)列上創(chuàng)建，可以對單列創(chuàng)建索引，也可以對多列創(chuàng)建索引。

建立主鍵索引的方法：

（1）在建表示，可以增加建立主鍵索引的句子如下：

drop table student;create table student(id int(4) not null AUTO_INCREMENT,name char(20) not null,age tinyint(2)  NOT NULL default '0',dept varchar(16)  default NULL,primary key(id),KEY index_name(name));

提示： 
■ primary key(id)<==主鍵 
■ KEY index_name(name)<==name字段普通索引

（2）mysql> desc student; 查看剛剛創(chuàng)建的表結(jié)構(gòu)。

+-------+-------------+------+-----+---------+----------------+| Field | Type        | Null | Key | Default | Extra          |+-------+-------------+------+-----+---------+----------------+| id    | int(4)      | NO   | PRI | NULL    | auto_increment || name  | char(20)    | NO   | MUL | NULL    |                || age   | tinyint(2)  | NO   || 0       |                || dept  | varchar(16) | YES  |     | NULL|                |+-------+-------------+------+-----+---------+----------------+

PRL為主鍵的標(biāo)示，MUL為普通索引的表示 
auto_increnment 代表數(shù)據(jù)自增

利用alter命令修改id列為自增主鍵值

alter table student change id id int primary key auto_increment;

創(chuàng)建的表的時候，可以指定

mysql> create table student(id int(4) not null AUTO_INCREMENT,name char(20) not null,age tinyint(2) NOT NULL default '0',dept varchar(16)  default NULL,primary key(id),KEY index_name(name)      );

提示：

KEY index_name(name)<==name字段普通索引優(yōu)化：在唯一值多的列上建索引查詢效率高還可以自定義自增的長度EBGUBE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

查看索引（可能會不清晰）【前提都需要進(jìn)入庫】

mysql> show index from student;

查看索引\G 可以查看的更詳細(xì)

mysql> show index from student\G      *************************** 1. row ***************************  主建  一個表只能有一個            Table: student       Non_unique: 0         Key_name: PRIMARY         Seq_in_index: 1      Column_name: id        Collation: A      Cardinality: 0         Sub_part: NULL           Packed: NULL             Null:       Index_type: BTREE           表類型          Comment:    Index_comment:    *************************** 2. row ***************************  索引 一個表可以有多個            Table: student       Non_unique: 1         Key_name: index_name   名字     Seq_in_index: 1      Column_name: name         列        Collation: A      Cardinality: 0         Sub_part: NULL           Packed: NULL             Null:       Index_type: BTREE                  類型          Comment:    Index_comment:    2 rows in set (0.00 sec)

查看表結(jié)構(gòu)

desc student；

建表后利用alter增加普通索引，刪除建表時創(chuàng)建的index_name索引、

alter table student drop index index_name;

刪除索引

alter table student drop index index_name;

操作實踐：

給name創(chuàng)建索引，也可以按照上述進(jìn)行指定字節(jié)

create index index_name on student(name);

查看索引

mysql> show index from student;

創(chuàng)建語句

create index index_age on student(name(8));

show index from student\G查看   表    位置表明     顯示*************************** 3. row ***************************　　Table: student　　Non_unique: 1     Key_name: index_age　　　 Seq_in_index: 1　　Column_name: age　　Collation: A　　Cardinality: 0     Sub_part: 8    Packed: NULL    Null: YES    Index_type: BTREE    Comment:    Index_comment:    3 rows in set (0.00 sec)

為表的多個字段創(chuàng)建聯(lián)合索引

　　如果查詢數(shù)據(jù)的條件是多列時，我們可以為多個查詢的列創(chuàng)建聯(lián)合索引，甚至，可以為多個列的前n個字符創(chuàng)建聯(lián)合索引，演示如下：

創(chuàng)建聯(lián)合索引，和單個索引不沖突

mysql>  create index ind_name_dept on student(name,dept);

聯(lián)合索引作用，查詢更快

    mysql> show index from student\G    *************************** 1. row ***************************    Table: student    Non_unique: 0    Key_name: PRIMARY     Seq_in_index: 1    Column_name: id    Collation: A    Cardinality: 0    Sub_part: NULL    Packed: NULL    Null:    Index_type: BTREE    Comment:    Index_comment:    *************************** 2. row ***************************    Table: student    Non_unique: 1    Key_name: index_name     Seq_in_index: 1    Column_name: name    Collation: A    Cardinality: 0    Sub_part: NULL    Packed: NULL    Null:    Index_type: BTREE    Comment:    Index_comment:    *************************** 3. row ***************************    Table: student    Non_unique: 1    Key_name: index_age     Seq_in_index: 1    Column_name: name    Collation: A    Cardinality: 0    Sub_part: 8           Packed: NULL    Null:    Index_type: BTREE    Comment:    Index_comment:    *************************** 4. row ***************************    Table: student    Non_unique: 1         Key_name: ind_name_dept     Seq_in_index: 1    Column_name: name    Collation: A    Cardinality: 0    Sub_part: NULL    Packed: NULL    Null:    Index_type: BTREE    Comment:    Index_comment:    *************************** 5. row ***************************    Table: student    Non_unique: 1         Key_name: ind_name_dept     Seq_in_index: 2    Column_name: dept    Collation: A    Cardinality: 0    Sub_part: NULL    Packed: NULL    Null: YES    Index_type: BTREE    Comment:    Index_comment:    5 rows in set (0.00 sec)

創(chuàng)建聯(lián)合索引，并指定值大小

create index ind_name_dept on student(name(8),dept(10));name 前8個字符，dept 前10個字符    *************************** 2. row ***************************    Table: student    Non_unique: 1         Key_name: index_dept     Seq_in_index: 1    Column_name: name    Collation: A    Cardinality: 0         Sub_part: 8    Packed: NULL    Null:    Index_type: BTREE    Comment:    Index_comment:    *************************** 3. row ***************************    Table: student    Non_unique: 1         Key_name: index_dept     Seq_in_index: 2    Column_name: dept    Collation: A    Cardinality: 0         Sub_part: 10    Packed: NULL    Null: YES    Index_type: BTREE    Comment:    Index_comment:    3 rows in set (0.00 sec)

提示：盡量在唯一值多的大表上建立索引。

什么時候創(chuàng)建聯(lián)合索引？

只有程序用這兩個條件查詢，采用聯(lián)合索引，這個主要是看開發(fā)。 
提示：按條件列查詢數(shù)據(jù)時，聯(lián)合索引是由前綴生效特性的 

創(chuàng)建唯一索引（非主鍵）

create unique index uni_ind_name on student(name);

索引小結(jié)：

創(chuàng)建主鍵索引alter table student chage id id int primary key auto_increment;刪除主鍵索引（主鍵列不能自增）alter table student drop primary key;創(chuàng)建普通索引alter table student add index index_dept(dept);根據(jù)的前n個字符創(chuàng)建索引create index index_dept on student(dept(8));根據(jù)多個列創(chuàng)建聯(lián)合索引create index index_name_dept on student(name,dept);創(chuàng)建唯一索引create unique index uni_ind_name on student(name);刪除普通索引與唯一索引alter table student drop index index_dept;drop index index_dept on student;

索引列的創(chuàng)建及生效條件

問題1、既然索引可以加快查詢速度，那么就給所有的列加索引吧？

解答：因為索引不但占用系統(tǒng)空間，而且更新數(shù)據(jù)時還需要維護(hù)索引數(shù)據(jù)的，因此索引是一把雙刃劍，并不是越多越好，例如：數(shù)十到幾百行的小表上無需建立索引，插入更新頻繁，讀取比較少的需要少建立索引

問題2、需要在哪些列上創(chuàng)建索引才能加快查詢速度呢？

select user,host from mysql.user wherepassword=…..,索引一定要創(chuàng)建在where后的條件列上，而不是select后的選擇數(shù)據(jù)的列上。另外，我們要盡量選擇在唯一值多的大表上的列建立索引，例如，男女生性別列唯一值少，不適合建立索引。

查看唯一值數(shù)量

select count(distinct user) from mysql.user;

唯一值就是相同的數(shù)量，例如查詢user那么相同的user就是唯一值

mysql> select count(distinct user) from mysql.user;+----------------------+| count(distinct user) |+----------------------+|                    7 |+----------------------+ 1 row in set (0.07 sec)

用戶列表，根據(jù)上放進(jìn)行解釋

mysql> select user,host from mysql.user;+-----------+---------------------------+| user| host                      |+-----------+---------------------------+| cyh| 10.1.1.%                  || root| 127.0.0.1                 || bbs| 172.16.1.1/255.255.255.0  || wordpress | 192.168.1.%               || oldboy| 192.168.1.%/255.255.255.0 || abc| localhost                 || blog| localhost                 || oldboy| localhost                 || root| localhost                 |+-----------+---------------------------+9 rows in set (0.00 sec)

創(chuàng)建索引的基本知識小結(jié)：

■ 索引類似書籍的目錄，會加快查詢數(shù)據(jù)的速度 
■ 要在表的列（字段）上創(chuàng)建索引 
■ 索引會加快查詢速度，但是也會影響更新的速度，因為更新要在維護(hù)索引數(shù)據(jù) 
■ 索引列并不是越多越好，要在頻繁查詢的表語句where后的條件列上創(chuàng)建索引 
■ 小表或重復(fù)值很多的列上可以不建索引，要在大表以及重復(fù)值少的條件上創(chuàng)建索引 
■ 多個列聯(lián)合索引有前綴生效特性 
■ 當(dāng)字段內(nèi)容前N個字符已經(jīng)接近唯一時，可以對字段的前N個字符創(chuàng)建索引 
■ 索引從工作方式區(qū)別，有主鍵，唯一，普通索引 
■ 索引類型有BTREE(默認(rèn))和hash（適合做緩存（內(nèi)存數(shù)據(jù)庫））等。

主鍵索引和唯一索引的區(qū)別

(1)對于主鍵/unique constraint oracle/sql server/mysql等都會自動建立唯一索引； 
(2)主鍵不一定只包含一個字段，所以如果你在主鍵的其中一個字段建唯一索引還是必要的； 
(3)主健可作外健，唯一索引不可； 
(4)主健不可為空，唯一索引可； 
(5)主健也可是多個字段的組合； 
(6)主鍵與唯一索引不同的是： 
a.有not null屬性； 
b.每個表只能有一個。

往表中插入數(shù)據(jù)

● 命令語法

insert into <表名>[(<字段名1>[..<字段名n>])]values(值1)[，(值n)]

● 建立一個簡單的測試表test

CREATE TABLE `test` (   `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) NOT NULL,  PRIMARY KEY (`id`)) ;mysql> desc test;+-------+----------+------+-----+---------+----------------+| Field | Type     | Null | Key | Default | Extra          |+-------+----------+------+-----+---------+----------------+| id    | int(4)   | NO   | PRI | NULL    | auto_increment || name  | char(20) | NO   |     | NULL|                |+-------+----------+------+-----+---------+----------------+

● 插入值

insert into test(id,name) values(1,'oldboy');insert into test(id,name) values(1,'oldboy');

● 查詢

mysql> select * from test;+----+--------+| id | name   |+----+--------+|  1 | oldboy |+----+--------+1 row in set (0.00 sec)

● 第二種方法：

id列可以不指定，不指定就自己增長

insert into test(name) values('oldgirl');  mysql> select * from test;+----+---------+| id | name|+----+---------+|  1 | oldboy  ||  2 | oldgirl |+----+---------+2 rows in set (0.00 sec)

● 第三種方法，可以不指定列，后面按照循序插入

mysql> insert into test values(3,'inca');Query OK, 1 row affected (0.00 sec)mysql> select * from test;+----+---------+| id | name|+----+---------+|  1 | oldboy  ||  2 | oldgirl ||  3 | inca|+----+---------+3 rows in set (0.00 sec)

● 批量插入：

mysql> insert into test values(4,'zuma'),(5,'kaka');Query OK, 2 rows affected (0.00 sec)Records: 2Duplicates: 0  Warnings: 0mysql> select * from test;+----+---------+| id | name|+----+---------+|  1 | oldboy  ||  2 | oldgirl ||  3 | inca    ||  4 | zuma||  5 | kaka|+----+---------+5 rows in set (0.00 sec)

清空所有值

mysql> truncate table test;Query OK, 0 rows affected (0.00 sec)mysql> select * from test;Empty set (0.00 sec)

一條命令解決以上所有配置

mysql> insert into test values (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');Query OK, 5 rows affected (0.01 sec)Records: 5Duplicates: 0  Warnings: 0mysql> select * from test;+----+---------+| id | name|+----+---------+|  1 | oldboy  ||  2 | oldgirl ||  3 | inca    ||  4 | zuma    ||  5 | kaka    |+----+---------+5 rows in set (0.00 sec)

查詢數(shù)據(jù)

查詢表的所有數(shù)據(jù)行

1.命令句法： 
select<字段1,字段2，…>frin 
<表名>where<表達(dá)式>

其中，select,from,where是不能隨便改的，是關(guān)鍵字，支持大小寫

2.列：查看表test中所有數(shù)據(jù)

a．進(jìn)入指定庫后查詢 
如果不進(jìn)入庫可以使用

mysql> select * from oldboy.test;mysql> select user,host,password from mysql.user;

+-----------+---------------------------+-------------------------------------------+| user| host                      | password|+-----------+---------------------------+-------------------------------------------+| root| localhost                 | *7495041D24E489A0096DCFA036B166446FDDD992 || root| 127.0.0.1                 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || wordpress | 192.168.1.%               | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || cyh| 10.1.1.%                  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || bbs| 172.16.1.1/255.255.255.0  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || blog| localhost                 | *7495041D24E489A0096DCFA036B166446FDDD992 || oldboy| localhost                 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || oldboy| 192.168.1.%/255.255.255.0 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || abc| localhost                 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-----------+---------------------------+-------------------------------------------+

只查詢前2行內(nèi)容

mysql> select * from test limit 2;+----+---------+| id | name    |+----+---------+|  1 | oldboy  ||  2 | oldgirl |+----+---------+2 rows in set (0.00 sec)

從第二條開始查，查找2個

mysql> select * from test limit 1,2;+----+---------+| id | name|+----+---------+|  2 | oldgirl ||  3 | inca    |+----+---------+2 rows in set (0.00 sec)

按照條件查詢

mysql> select * from test where id=1;+----+--------+| id | name|+----+--------+|  1 | oldboy |+----+--------+1 row in set (0.00 sec)

提示：mysql> select * from test where name='abcdocker'; 
<==查詢字符串要加引號

mysql> select * from test where name='abcdocker';+----+--------+| id | name   |+----+--------+|  1 | abcdocker |+----+--------+1 row in set (0.00 sec)

提示：查找字符串類型的條件的值要帶單引號，數(shù)字值不帶引號。

查詢多個條件

mysql> select * from test where name='oldgirl' and id=2;+----+---------+| id | name    |+----+---------+|  2 | oldgirl |+----+---------+1 row in set (0.00 sec)

范圍查詢

mysql> select * from test where id>2;+----+------+| id | name |+----+------+|  3 | inca ||  4 | zuma ||  5 | kaka |+----+------+3 rows in set (0.00 sec)mysql> select * from test where id>2 and id<5;+----+------+| id | name |+----+------+|  3 | inca ||  4 | zuma |+----+------+2 rows in set (0.35 sec)

或者的意思

mysql> select * from test where id>2 or id<5;+----+---------+| id | name    |+----+---------+|  1 | abcdocker  ||  2 | oldgirl ||  3 | inca    ||  4 | zuma    ||  5 | kaka    |+----+---------+5 rows in set (0.34 sec)

排序

什么都不加相當(dāng)于升序

mysql> select * from test;相當(dāng)于mysql> select * from test order by id asc;     寫法倒序mysql> select * from test order by id desc;  倒序

例子：創(chuàng)建學(xué)生表

drop table student;create table student(Sno int(10) NOT NULL COMMENT '學(xué)號',Sname varchar(16) NOT NULL COMMENT '姓名',Ssex char(2) NOT NULL COMMENT '性別',Sage tinyint(2)  NOT NULL default '0' COMMENT '學(xué)生年齡',Sdept varchar(16)  default NULL  COMMENT '學(xué)生所在系別', PRIMARY KEY  (Sno) ,key index_Sname (Sname)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

插入內(nèi)容

create table course(Cno int(10) NOT NULL COMMENT '課程號',Cname varchar(64) NOT NULL COMMENT '課程名',Ccredit tinyint(2) NOT NULL COMMENT '學(xué)分',PRIMARY KEY  (Cno) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

插入內(nèi)容

CREATE TABLE `SC` (   SCid int(12) NOT NULL auto_increment COMMENT '主鍵',  `Cno` int(10) NOT NULL COMMENT '課程號',  `Sno` int(10) NOT NULL COMMENT '學(xué)號',  `Grade` tinyint(2) NOT NULL COMMENT '學(xué)生成績',  PRIMARY KEY  (`SCid`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

插入內(nèi)容

INSERT INTO course values(1001,'Linux中高級運維',3);INSERT INTO course values(1002,'Linux高級架構(gòu)師',5);INSERT INTO course values(1003,'MySQL高級Dba',4);INSERT INTO course values(1004,'Python運維開發(fā)',4);INSERT INTO course values(1005,'Java web開發(fā)',3);

插入內(nèi)容

INSERT INTO SC(Sno,Cno,Grade)  values(0001,1001,4);INSERT INTO SC(Sno,Cno,Grade) values(0001,1002,3);INSERT INTO SC(Sno,Cno,Grade)  values(0001,1003,1);INSERT INTO SC(Sno,Cno,Grade)  values(0001,1004,6);INSERT INTO SC(Sno,Cno,Grade)  values(0002,1001,3);INSERT INTO SC(Sno,Cno,Grade)  values(0002,1002,2);INSERT INTO SC(Sno,Cno,Grade)  values(0002,1003,2);INSERT INTO SC(Sno,Cno,Grade)  values(0002,1004,8);INSERT INTO SC(Sno,Cno,Grade)  values(0003,1001,4);INSERT INTO SC(Sno,Cno,Grade)  values(0003,1002,4);INSERT INTO SC(Sno,Cno,Grade)  values(0003,1003,2);INSERT INTO SC(Sno,Cno,Grade)  values(0003,1004,8);INSERT INTO SC(Sno,Cno,Grade)  values(0004,1001,1);INSERT INTO SC(Sno,Cno,Grade)  values(0004,1002,1);INSERT INTO SC(Sno,Cno,Grade)  values(0004,1003,2);INSERT INTO SC(Sno,Cno,Grade)  values(0004,1004,3);INSERT INTO SC(Sno,Cno,Grade)  values(0005,1001,5);INSERT INTO SC(Sno,Cno,Grade)  values(0005,1002,3);INSERT INTO SC(Sno,Cno,Grade)  values(0005,1003,2);INSERT INTO SC(Sno,Cno,Grade)  values(0005,1004,9);

檢查，查看表格式或者表內(nèi)容

mysql>desc SC; or select * from SC;

優(yōu)化

sql語句優(yōu)化

explain 查看是否含有建立索引的語句mysql> explain select * from test where name='oldboy';在一個語句前面加上explain相當(dāng)于模擬查詢

創(chuàng)建索引

mysql> create index index_name on test(name);| name| char(20) | NO   | MUL | NULL    |              mysql> explain select * from test where name='oldboy'\G*************************** 1. row ***************************           id: 1      select_type: SIMPLE            table: test             type: ref    possible_keys: index_name              key: index_name          key_len: 60              ref: const             rows: 1   查詢行數(shù)，表示當(dāng)前只查詢了1行            Extra: Using where; Using index    1 row in set (0.00 sec)

原圖：

mysql> explain select * from test where name='oldboy'\G    *************************** 1. row ***************************               id: 1      select_type: SIMPLE            table: test             type: ALL    possible_keys: NULL              key: NULL          key_len: NULL              ref: NULL             rows: 5            Extra: Using where    1 row in set (0.00 sec)

幫助

mysql>help explain  包含EXPLAIN的用法

使用explain命令優(yōu)化SQL語句（select語句）的基本流程

■ 抓慢查詢SQL語法方法

每隔2,秒輸入mysql> SHOW FULL PROCESSLIST; 如果出現(xiàn)2次說明是慢查詢mysql> SHOW  FULL PROCESSLIST;

■ 分析慢查詢?nèi)罩?/p>

配置參數(shù)記錄慢查詢語句log_query_time = 2log_queries_not_using_indexeslog-slow-queries = /data/3306/slow.log

■ 對需要建索引的條件列建立索引 
大表不能高峰期建立索引，300萬條記錄（如果訪問已經(jīng)慢了，可以直接創(chuàng)建）

■ 分析慢查詢SQL的工具mysqlala(每天早上發(fā)郵件) 
切割慢查詢?nèi)罩?，去重分析后發(fā)給大家，如果并發(fā)太大可以按小時，去重。

1）mv 然后flush進(jìn)程2）cp復(fù)制，然后利用>清空。3）定時任務(wù)mv /data/3306/slow.log /opt/$(date +%F)_slow.logmysqladmin -uroot -poldboy -S /data/3306/mysql.sock flush-logs

★