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

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

mysql

登錄MySQL方法

1.單實例MySQL登錄的方法

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

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

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

給啟動腳本以及備份腳本等加700權限，用戶和組改為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文件登錄不同的服務中

注意：多實例的遠程連接無需指定sock路徑

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

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

mysql

為了防止誤操作，可以把提示符標記為測試環境，也可以寫入配置永久生效

■ 在命令行修改登錄提示

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】模塊下添加如下內容（注意，不是【mysqld】）保存后，無需重啟mysql，退出當前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%';更多參數help show

■ 強制關閉數據庫的方法（慎用）

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

■ 最好使用優雅停止

mysqladmin -uroot -poldboy123 shutdown

■ 野蠻粗魯殺死數據庫導致故障企業案例：

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

企業實戰題7：

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

相關地址： 
開發MySQL多實例啟動腳本~ 

老男孩Shell企業面試題30道 [答案] 

MySQL數據庫安全策略介紹

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

truncalt table test和delete from test；區別

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

為管理員root用戶設置密碼方法

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

適合多實例更改密碼，強調，以上命令的是命令行執行，而不是進入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'; 這樣設置是不可以登錄的

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

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

結果：如果不使用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需要查看是否成功，出現Changed代表成功

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

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結構化查詢語言

什么是SQL？

SQL，英文全稱Structured Query Language，中文意思是結構化查詢語言，它是一種對關系型數據庫中的數據進行定義和操作的語言方法，是大多數關系數據庫管理系統所支持的工業標準語言。

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

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

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

命令講解

■ 創建數據庫

mysql> create database abcdocker;

■ 查看創建庫的語句

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.網站程序字符集2.客戶端的字符集3.服務器端字符集4.linux客戶端字符集5.以上都要統一，否則會出現中文亂碼

■ 創建不同字符集格式的數據庫命令

mysql> create database abcdocker;          #默認數據庫配置，相當于創建拉丁字符集數據庫mysql> create database abcdocker_gbk character set gbk collate gbk_chinese_ci;創建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)

如果編譯的時候指定了特定的字符集，則以后創建對應字符集的數據庫就不需要指定字符集

-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \提示：二進制軟件包，安裝的數據庫字符集默認latinl

■ 查看數據庫

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

■ 當前數據庫包含的表信息

use oldboy      #相當于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;

創建MySQL用戶及賦予用戶權限

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命令創建用戶并授權

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

說明：上述命令是授權localhost主機上通過用戶username管理dbname數據庫的所有權限，密碼為passwd，其中username，dbname，passwd可根據業務的情況修改

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

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

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

生產環境針對主庫（寫入主讀為輔）用戶的授權；

■ 普通環境：

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

案例1.創建abcdocker用戶，對test庫具備所有權限，允許從localhost主機登錄，密碼是abcdocker123

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

■ 第一種：授權用戶

grant all on test.* to oldboy@127.0.0.% identified by ‘oldboy123’show grants for oldboy@’127.0.0.%’;           查看授權用戶+-------------------------------------------------------------------------------------------------------------+| 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)

■ 第二種：授權方法

create user bbs@'172.16.1.1/255.255.255.0' identified by '123456'；先授權可以登錄的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';

授權局域網主機連接遠程數據庫

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

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

b、一條命令子網掩碼配置法

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

c、兩條命令實現 
先創建用戶并設置密碼；

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

最后記得上述每條grant命令都要刷新權限

flush privileges

數據庫遠程登錄

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

創建類似于root系列的管理員用戶，可以創建下級用戶的用戶

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

回收用戶權限

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

顯示庫的信息

mysql> show create database oldboy\G

MySQL包含關系 
數據庫服務器>數據庫（多個實例）>多個庫>多個表>多個字段行列（數據）

創建表

建表的基本命令語法：

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

建表語句

下面是人工寫法設計的建表語句例子，表名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    );

查看創建表的語句

mysql> show create table student;create table student(       #create table表示創建表的固定關鍵字,student為表名id int(4) not null,        #學號列,數字類型，長度為4，不能為空值name char(20) not null,    #名字列，定長字符類型，長度20，不能為空age tinyint(2)  NOT NULL default '0', #年齡列，很小的數字類型，長度為2，不能為空，默認為0值dept varchar(16)  default NULL    #系別列，變長字符類型，長度16，默認為空。ENGINE=lnnoDB DEFAULT CHARSET=latinl  #引擎和字符集，引擎默認為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 
環境的默認建表語句中的引擎的不同，如果希望控制引擎，就要在建表語句里顯示的指定引擎建表；

MySQL5.1以及默認引擎為MyISAM，MySQL5.5以后默認引擎為InnoDB

MySQL表的字段類型

1） 數字類型 

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

3）字符串類型 

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

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

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

有關MySQL字段類型詳細內容，可以參考MySQL手冊

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

2． DOUBLE[M,D] [ZEROFILL]型：正常大小（雙精密）浮點數字類型

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

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

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

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

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

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

小結

■ char定長，不夠的用空格補全，浪費存儲空間，查詢速度快，多數系統表字段都是定長 
■ varchar變長，查詢速度慢

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

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

user表用的是CHAR

生產場景案例： 
某sns產品生產正式建表語句

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代表關閉，1代表正常',PRIMARY KEY  (`subject_comment_manager_id`),KEY `IDX_PRIMARYKEY` (`subject_primary_key`(32)), #<==括號內的32表示對前32個字符做前綴索引。KEY `IDX_SUBJECT_TITLE` (`subject_title`(32))KEY `index_nick_type` (`edit_user_nick`(32),`subject_type`)#<==聯合索引，此行為新加的，用于給大家講解的。實際表語句內沒有此行。) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

為表的字段創建索引* * * * * *

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

創建主鍵索引
查詢數據庫，按主鍵查詢是最快的，每個表只有一個主鍵列，但是可以用多個普通索引列。主鍵列要求列的所有內容必須唯一，而普通索引不要求內容必須唯一
主鍵就類似我們在學校學習時的學號一樣，班級內是唯一的，整個表的每一條記錄的主鍵值在表內都是唯一的，用來唯一標識一條記錄。
首先，無論建立主鍵索引還是普通索引，都要在表的對應列上創建，可以對單列創建索引，也可以對多列創建索引。

為表的字段創建索引*****
數據庫的索引就象書的目錄一樣，如果在字段上建立了索引，那么多以索引列為查詢條件時可以加快查詢數據的速度，這是mysql優化的重要內容之一。

創建主鍵索引      
查詢數據庫，按主鍵查詢是最快的，每個表只能有一個主鍵列，但是可以有多個普通索引列。主鍵列的所有內容必須唯一，而普通索引列不要求內容必須唯一。
主鍵就類似我們在學習學習時的學號一樣，班級內是唯一的，整個表的每一條記錄的主鍵值在表內都是唯一的，用來唯一標識一條記錄。
首先，無論建立主鍵索引還是普通索引，都要在表的對應列上創建，可以對單列創建索引，也可以對多列創建索引。

建立主鍵索引的方法：

（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; 查看剛剛創建的表結構。

+-------+-------------+------+-----+---------+----------------+| 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為主鍵的標示，MUL為普通索引的表示 
auto_increnment 代表數據自增

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

alter table student change id id int primary key auto_increment;

創建的表的時候，可以指定

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字段普通索引優化：在唯一值多的列上建索引查詢效率高還可以自定義自增的長度EBGUBE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

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

mysql> show index from student;

查看索引\G 可以查看的更詳細

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)

查看表結構

desc student；

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

alter table student drop index index_name;

刪除索引

alter table student drop index index_name;

操作實踐：

給name創建索引，也可以按照上述進行指定字節

create index index_name on student(name);

查看索引

mysql> show index from student;

創建語句

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)

為表的多個字段創建聯合索引

　　如果查詢數據的條件是多列時，我們可以為多個查詢的列創建聯合索引，甚至，可以為多個列的前n個字符創建聯合索引，演示如下：

創建聯合索引，和單個索引不沖突

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

聯合索引作用，查詢更快

    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)

創建聯合索引，并指定值大小

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)

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

什么時候創建聯合索引？

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

創建唯一索引（非主鍵）

create unique index uni_ind_name on student(name);

索引小結：

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

索引列的創建及生效條件

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

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

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

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

查看唯一值數量

select count(distinct user) from mysql.user;

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

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

用戶列表，根據上放進行解釋

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)

創建索引的基本知識小結：

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

主鍵索引和唯一索引的區別

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

往表中插入數據

● 命令語法

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)

查詢數據

查詢表的所有數據行

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

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

2.列：查看表test中所有數據

a．進入指定庫后查詢 
如果不進入庫可以使用

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行內容

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)

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

查詢多個條件

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)

排序

什么都不加相當于升序

mysql> select * from test;相當于mysql> select * from test order by id asc;     寫法倒序mysql> select * from test order by id desc;  倒序

例子：創建學生表

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

插入內容

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

插入內容

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

插入內容

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

插入內容

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);

檢查，查看表格式或者表內容

mysql>desc SC; or select * from SC;

優化

sql語句優化

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

創建索引

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   查詢行數，表示當前只查詢了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命令優化SQL語句（select語句）的基本流程

■ 抓慢查詢SQL語法方法

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

■ 分析慢查詢日志

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

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

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

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

★