MySQL——索引與事務(wù)，存儲(chǔ)引擎MyLSAM和InnoDB

索引的概念

1、數(shù)據(jù)庫(kù)中的索引與書籍的目錄類似

●在一本書中，無(wú)須閱讀整本書，利用目錄就可以快速查找所需信息
●書中的目錄是一個(gè)詞語(yǔ)列表，其中注明了包含各個(gè)詞的頁(yè)碼

2、數(shù)據(jù)庫(kù)索引

●在數(shù)據(jù)庫(kù)中，索引使數(shù)據(jù)庫(kù)程序無(wú)須對(duì)整個(gè)表進(jìn)行掃描，就可以在其中找到所需數(shù)據(jù)
●數(shù)據(jù)庫(kù)中的索引是某個(gè)表中一列或者若干列值的集合，以及物理標(biāo)識(shí)這些值的數(shù)據(jù)頁(yè)的邏輯指針清單

索引的作用

1、設(shè)置了合適的索引之后，數(shù)據(jù)庫(kù)利用各種快速的定位技術(shù)，能夠大大加快查詢速率

2、特別是當(dāng)表很大時(shí)，或者查詢涉及到多個(gè)表時(shí)，使用索引可使查詢加快成干倍

3、可以降低數(shù)據(jù)庫(kù)的10成本，并且索引還可以降低數(shù)據(jù)庫(kù)的排序成本

4、通過(guò)創(chuàng)建唯一性索引保證數(shù)據(jù)表數(shù)據(jù)的唯一性

5、可以加快表與表之間的連接

6、在使用分組和排序時(shí)，可大大減少分組和排序時(shí)間

索引的分類

1、普通索引

●這是最基本的索引類型，而且它沒(méi)有唯一性之 類的限制

2、唯一性索引

●這種索引和前面的“普通索引”基本相同，但有一個(gè)區(qū)別:索引列的所有值都只能出現(xiàn)一次，即必須唯一

3、主鍵

●主鍵是一種唯一性索引， 但它必須指定為"PRIMARY KEY"

4、全文索引

●MySQL從3.23.23版開始支持全文索引|和全文檢索。在MySQL中全文索引|的索引|類型為FULLTEXT,全文索引可以在VARCHAR或者TEXT類型的列上創(chuàng)建

5、單列索引與多列索引

●索引可以是單列上創(chuàng)建的索引，也可以是在多列上創(chuàng)建的索引

創(chuàng)建索引的原則依據(jù)

1、表的主鍵、外鍵必須有索引

2、數(shù)據(jù)量超過(guò)300行的表應(yīng)該有索引

3、經(jīng)常與其他表進(jìn)行連接的表，在連接字段.上應(yīng)該建立索引

4、唯一性太差的字段不適合建立索引

5、更新太頻繁地字段不適合創(chuàng)建索引

6、經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段,應(yīng)該建立索引

7、索引應(yīng)該建在選擇性高的字段上

8、索引應(yīng)該建在小字段上，對(duì)于大的文本字段甚至超長(zhǎng)字段，不要建索引

創(chuàng)建索引的方法

1、根據(jù)企業(yè)需求選擇了合適的索引之后，可使用CREATE INDEX創(chuàng)建索引

2、CREATE INDEX加上各個(gè)索引關(guān)鍵字便可創(chuàng)建各個(gè)類型的索引

■（1）創(chuàng)建普通索引
CREATE INDEX <索引的名字> ON tablename (列的列表);
■普通索引例子
CREATE INDEX salary index ON IT salary(薪資);

■（2）創(chuàng)建唯一性索引
CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
■創(chuàng)建唯一性索引例子
CREATE UNIQUE INDEX salary_unique_index ON IT_salary(姓名);

■（3）創(chuàng)建主鍵索引
CREATE TABLE tablename ( [..], PRIMARY KEY (列的列表) );
ALTER TABLE tablename ADD PRIMARY KEY (列的列表);
■主鍵索引例子
ALTER TABLE IT_ salary ADD PRIMARY KEY (員工ID);

三種類型簡(jiǎn)單概述：

1.創(chuàng)建表 直接定義

2.create index 索引名稱 on 表名 （列表1，列表2）；

3.alter table 表名 add index 索引名稱 （列表）；

事務(wù)的概念

1、事務(wù)是一 種機(jī)制、 一個(gè)操作序列，包含了一組數(shù)據(jù)庫(kù)操作命令，并且把所有的命令作為一個(gè)整體一起向系統(tǒng)提交或撤銷操作請(qǐng)求，即這一組數(shù)據(jù)庫(kù)命令要么都執(zhí)行 ,要么都不執(zhí)行

2、事務(wù)是一個(gè)不可分割的工作邏輯單元，在數(shù)據(jù)庫(kù)系統(tǒng)上執(zhí)行并發(fā)操作時(shí)，事務(wù)是最小的控制單元

3、適用于多用戶同時(shí)操作的數(shù)據(jù)庫(kù)系統(tǒng)的場(chǎng)景，如銀行、保險(xiǎn)公司及證券交易系統(tǒng)等等

4、通過(guò)事務(wù)的整體性以保證數(shù)據(jù)的一致性

事務(wù)的ACID特點(diǎn)

1、原子性(Atomicity)

●事務(wù)是一個(gè)完整的操作，事務(wù)的各元素是不可分的(原子的)
●事務(wù)中的所有元素必須作為一個(gè)整體提交或回滾
●如果事務(wù)中的任何元素失敗，則整個(gè)事務(wù)將失敗

2、一致性(Consistency)

●當(dāng)事務(wù)完成時(shí)，數(shù)據(jù)必須處于一致?tīng)顟B(tài):在事務(wù)開始之前，數(shù)據(jù)庫(kù)中存儲(chǔ)的數(shù)據(jù)處于一致?tīng)顟B(tài);在正在進(jìn)行的事務(wù)中，數(shù)據(jù)可能處于不一致的狀態(tài);當(dāng)事務(wù)成功完成時(shí)，數(shù)據(jù)必須再次回到已知的一致?tīng)顟B(tài)

3、隔離性. (Isolation)

●對(duì)數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的，這表明事務(wù)必須是獨(dú)立的，它不應(yīng)以任何方式依賴于或影響其他事務(wù)
●修改數(shù)據(jù)的事務(wù)可以在另一個(gè)使用相同數(shù)據(jù)的事務(wù)開始之前訪問(wèn)這些數(shù)據(jù)，或者在另一個(gè)使用相同數(shù)據(jù)的事務(wù)結(jié)束之后訪問(wèn)這些數(shù)據(jù)

4、持久性(Durability)

●事務(wù)持久性指不管系統(tǒng)是否發(fā)生故障，事務(wù)處理的結(jié)果都是永久的
●一旦事務(wù)被提交，事務(wù)的效果會(huì)被永久地保留在數(shù)據(jù)庫(kù)中

事務(wù)的操作

1、默認(rèn)情況下MySQL的事務(wù)是自動(dòng)提交的，當(dāng)sq|語(yǔ)句提交時(shí)事務(wù)便自動(dòng)提交

2、手動(dòng)對(duì)事務(wù)進(jìn)行控制的方法
●事務(wù)處理命令控制
●使用set設(shè)置事務(wù)處理方式

重點(diǎn)：

1、事務(wù)處理命令控制事務(wù)
●begin:開始一個(gè)事務(wù)
●commit:提交一個(gè)事務(wù)
●rollback:回滾一個(gè)事務(wù)

2、使用set命令進(jìn)行控制
●set autocommit=0:禁止自動(dòng)提交
●set autocommit=1:開啟自動(dòng)提交

savepoint 打標(biāo)記/存檔

存儲(chǔ)引擎概念介紹

1、MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲(chǔ)在文件中，每種技術(shù)都使用不同的存儲(chǔ)機(jī)制、索引技巧、鎖定水平并最終提供不同的功能和能力，這些不同的技術(shù)以及配套的功能在MySQL 中稱為存儲(chǔ)引擎

2、存儲(chǔ)引擎就是MySQL將數(shù)據(jù)存儲(chǔ)在文件系統(tǒng)中的存儲(chǔ)方式或者存儲(chǔ)格式

3、目前MySQL常用的兩種存儲(chǔ)引擎
MyISAM（輕量級(jí)）
InnoDB

4、MySQL存儲(chǔ)引擎是MySQL數(shù)據(jù)庫(kù)服務(wù)器中的組件，負(fù)責(zé)為數(shù)據(jù)庫(kù)執(zhí)行實(shí)際的數(shù)據(jù)1/0操作

5、使用特殊存儲(chǔ)引擎的主要優(yōu)點(diǎn)之一在于，僅需提供特殊應(yīng)用所需的特性，數(shù)據(jù)庫(kù)中的系統(tǒng)開銷較小，具有更有效和更高的數(shù)據(jù)庫(kù)性能

6、MySQL系統(tǒng)中，存儲(chǔ)引擎處于文件系統(tǒng)之上，在數(shù)據(jù)保存到數(shù)據(jù)文件之前會(huì)傳輸?shù)酱鎯?chǔ)引擎，之后按照各個(gè)存儲(chǔ)引擎的存儲(chǔ)格式進(jìn)行存儲(chǔ)

MyISAM的介紹

1、MyISAM存儲(chǔ)引擎是MySQL關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)5.5版本之前默認(rèn)的存儲(chǔ)引擎，前身是ISAM

2、ISAM是一個(gè)定義明確且歷經(jīng)時(shí)間考驗(yàn)的數(shù)據(jù)表格管理方法，在設(shè)計(jì)之時(shí)就考慮到數(shù)據(jù)庫(kù)被查詢的次數(shù)要遠(yuǎn)大于更新的次數(shù)

3、ISAM的特點(diǎn)

●ISAM執(zhí)行讀取操作的速度很快,
●它不支持事務(wù)處理
●而且不占用大量的內(nèi)存和存儲(chǔ)資源
●不能夠容錯(cuò)

4、數(shù)據(jù)存儲(chǔ)位置：
表定義文件
表數(shù)據(jù)存儲(chǔ)文件
表索引文件

MyISAM的特點(diǎn)

1、不支持事務(wù)

2、表級(jí)鎖定形式，數(shù)據(jù)在更新時(shí)鎖定整個(gè)表

3、數(shù)據(jù)庫(kù)在讀寫過(guò)程中相互阻塞

●會(huì)在數(shù)據(jù)寫入的過(guò)程阻塞用戶數(shù)據(jù)的讀取
●也會(huì)在數(shù)據(jù)讀取的過(guò)程中阻塞用戶的數(shù)據(jù)寫入

4、可通過(guò)key_buffer_size來(lái)設(shè)置緩存索引，提高訪問(wèn)性能，減少磁盤IO的壓力

●但緩存只會(huì)緩存索引文件，不會(huì)緩存數(shù)據(jù)

5、采用MyISAM存儲(chǔ)引擎數(shù)據(jù)單獨(dú)寫入或讀取，速度過(guò)程較快且占用資源相對(duì)少

6、MyISAM存儲(chǔ)引擎它不支持外鍵約束，只支持全文索引

7、每個(gè)MyISAM在磁盤.上存儲(chǔ)成三個(gè)文件，每- -個(gè)文件的名字以表的名字開始，擴(kuò)展名指出文件類型

8、MyISAM在磁盤.上存儲(chǔ)的文件

●.frm文件存儲(chǔ)表定義
●數(shù)據(jù)文件的擴(kuò)展名為.MYD (MYData)
●索引文件的擴(kuò)展名是.MYI (MYIndex)

MyISAM適用的生產(chǎn)場(chǎng)景舉例

1、公司業(yè)務(wù)不需要事務(wù)的支持

2、一般單方面讀取數(shù)據(jù)比較多的業(yè)務(wù)，或單方面寫入數(shù)據(jù)比較多的業(yè)務(wù)

3、MyISAM存儲(chǔ)引擎數(shù)據(jù)讀寫都比較頻繁場(chǎng)景不適合使用讀寫并發(fā)訪問(wèn)相對(duì)較低的業(yè)務(wù)

4、數(shù)據(jù)修改相對(duì)較少的業(yè)務(wù)

5、對(duì)數(shù)據(jù)業(yè)務(wù)-致性要求不是非常高的業(yè)務(wù)服務(wù)器硬件資源相對(duì)比較差

InnoDB特點(diǎn)介紹

1、支持事務(wù):支持4個(gè)事務(wù)隔離級(jí)別

2、行級(jí)鎖定，但是全表掃描仍然會(huì)是表級(jí)鎖定讀寫阻塞與事務(wù)隔離級(jí)別相關(guān)

3、具有非常高效的緩存特性:能緩存索引，也能緩存數(shù)據(jù)

4、表與主鍵以簇的方式存儲(chǔ)

5、支持分區(qū)、表空間，類似oracle數(shù)據(jù)庫(kù)

6、支持外鍵約束，5.5以前不支持全文索引，5.5版本以后支持全文索引

7、對(duì)硬件資源要求還是比較高的場(chǎng)合

InnoDB適用生產(chǎn)場(chǎng)景分析

1、業(yè)務(wù)需要事務(wù)的支持

2、行級(jí)鎖定對(duì)高并發(fā)有很好的適應(yīng)能力，但需確保查詢是通過(guò)索引來(lái)完成

3、業(yè)務(wù)數(shù)據(jù)更新較為頻繁的場(chǎng)景，如:論壇,微博等

4、業(yè)務(wù)數(shù)據(jù)一致性要求較高，例如:銀行業(yè)務(wù)

5、硬件設(shè)備內(nèi)存較大，利用Innodb較好的緩存能力來(lái)提高內(nèi)存利用率，減少磁盤I0的壓力

企業(yè)選擇存儲(chǔ)引擎依據(jù)

1、需要考慮每個(gè)存儲(chǔ)引擎提供了哪些不同的核心功能及應(yīng)用場(chǎng)景

2、支持的字段和數(shù)據(jù)類型

●所有引|擎都支持通用的數(shù)據(jù)類型
●但不是所有的引擎都支持其它的字段類型，如二進(jìn)制對(duì)象

3、鎖定類型:不同的存儲(chǔ)引擎支持不同級(jí)別的鎖定

●表鎖定
●行鎖定

配置存儲(chǔ)引擎

1、在企業(yè)中選擇好合適的存儲(chǔ)引擎之后，就可以進(jìn)行修改了

2、修改步驟

查看數(shù)據(jù)庫(kù)可配置的存儲(chǔ)引擎
查看表正在使用的存儲(chǔ)引擎
配置存儲(chǔ)引擎為所選擇的類型

3、使用show engines查看系統(tǒng)支持的存儲(chǔ)引擎

4、查看表使用的存儲(chǔ)引擎

方法1: show table status from 庫(kù)名 where name='表名;
方法2: show create table 表名;

修改存儲(chǔ)引擎

1、方法1: alter table修改;
alter table table_ name engine=引擎;

2、方法2:修改my.cnf,指定默認(rèn)存儲(chǔ)引擎并重啟服務(wù)
default-storage-engine=InnDB

3、方法3: create table創(chuàng)建表時(shí)指定存儲(chǔ)引擎
create table 表名 (字段) engine= 引擎

4、方法4: Mysql convert table format 轉(zhuǎn)化存儲(chǔ)引擎
Mysql convert table format -user=root -password=密碼

• sock=/tmp/mysql.sock - engine =引擎 庫(kù)名 表名

索引實(shí)操

1，進(jìn)入數(shù)據(jù)庫(kù)，創(chuàng)建數(shù)據(jù)庫(kù)，創(chuàng)建表

[root@master2 ~]# mysql -uroot -p     ##進(jìn)入數(shù)據(jù)庫(kù)
Enter password:     ##輸入密碼

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database school;    ##創(chuàng)建數(shù)據(jù)庫(kù)school
Query OK, 1 row affected (0.00 sec)

mysql> use school;       ##使用數(shù)據(jù)庫(kù)
Database changed
mysql> create table info(        ##創(chuàng)建表
        -> id int(4) not null primary key auto_increment,   ##設(shè)置主鍵，自動(dòng)增加
        -> name varchar(10) not null,       ##名字類型varchar不為空
        -> address varchar(50) default 'nanjing',    ##默認(rèn)南京
        -> age int(3) not null);
Query OK, 0 rows affected (0.02 sec)

2，向表中插入數(shù)據(jù)

mysql> insert into info (name,address,age) values ('zhangsan','beijing',20),('lisi','shanghai',22); 
##插入數(shù)據(jù)
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from info;   ##查看表內(nèi)容
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)

mysql> desc info;               ##查看表結(jié)構(gòu)
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(4)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10) | NO   |     | NULL    |                |
| address | varchar(50) | YES  |     | nanjing |                |
| age     | int(3)      | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3，使用create，alter，創(chuàng)建表直接定義三種方式創(chuàng)建索引（普通索引，唯一性索引）

mysql> create index index_age on info (age);   ##創(chuàng)建普通索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;   ##查看表中的索引
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY   |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          1 | index_age |            1 | age         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> drop index index_age on info;   ##刪除表中的索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;        ##查看表中的索引         
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

mysql> create unique index unique_name on info (name); ##創(chuàng)建唯一性索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;  ##查看表中索引
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY     |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | unique_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> drop index unique_name on info;  ##刪除表中的索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;          ##查看表中的索引
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> alter table info add unique index index_name (name);   ##使用alter插入表索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;   ##查看表中的索引
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY    |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | index_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

4，創(chuàng)建兩個(gè)表，進(jìn)行關(guān)聯(lián)，多表查詢

mysql> create table user(     ##創(chuàng)建user表
        -> id int(4) not null primary key auto_increment,   ##設(shè)置主鍵和自動(dòng)增加
        -> name varchar(10) not null,
        -> score decimal not null,
        -> hobby int(2) not null default '1',  ##默認(rèn)1
        -> index index_score (score));    ##設(shè)置索引score
Query OK, 0 rows affected (0.01 sec)

mysql> desc user;  ##查看表結(jié)構(gòu)
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| id    | int(4)        | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)   | NO   |     | NULL    |                |
| score | decimal(10,0) | NO   | MUL | NULL    |                |
| hobby | int(2)        | NO   |     | 1       |                |
+-------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into user (name,score,hobby) values ('test01',88,1),('stu01',99,2),('wangwu',77,3); 
##向表中插入數(shù)據(jù)
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user;    ##查看表內(nèi)容
+----+--------+-------+-------+
| id | name   | score | hobby |
+----+--------+-------+-------+
|  1 | test01 |    88 |     1 |
|  2 | stu01  |    99 |     2 |
|  3 | wangwu |    77 |     3 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)

mysql> create table hob(     ##創(chuàng)建hob表
        -> id int(2) not null primary key,
        -> hob_name varchar(10) not null);
Query OK, 0 rows affected (0.00 sec)

mysql> desc hob;   ##查看表結(jié)構(gòu)
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(2)      | NO   | PRI | NULL    |       |
| hob_name | varchar(10) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into hob (id,hob_name) values (1,'看書'),(2,'運(yùn)動(dòng)'),(3,'聽(tīng)歌');   ##插入表數(shù)據(jù)
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from hob;   ##查看表內(nèi)容
+----+----------+
| id | hob_name |
+----+----------+
|  1 | 看書     |
|  2 | 運(yùn)動(dòng)     |
|  3 | 聽(tīng)歌     |
+----+----------+
3 rows in set (0.00 sec)

mysql> select * from user inner join hob on user.hobby=hob.id;   ##關(guān)聯(lián)user和hob兩張表
+----+--------+-------+-------+----+----------+
| id | name   | score | hobby | id | hob_name |
+----+--------+-------+-------+----+----------+
|  1 | test01 |    88 |     1 |  1 | 看書     |
|  2 | stu01  |    99 |     2 |  2 | 運(yùn)動(dòng)     |
|  3 | wangwu |    77 |     3 |  3 | 聽(tīng)歌     |
+----+--------+-------+-------+----+----------+
3 rows in set (0.00 sec)

mysql> select user.name,hob.hob_name from user inner join hob on user.hobby=hob.id;
##去除其他內(nèi)容顯示name和hob_name內(nèi)容
+--------+----------+
| name   | hob_name |
+--------+----------+
| test01 | 看書     |
| stu01  | 運(yùn)動(dòng)     |
| wangwu | 聽(tīng)歌     |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select u.name,h.hob_name from user u inner join hob h on u.hobby=h.id; ##設(shè)置簡(jiǎn)易名稱
+--------+----------+
| name   | hob_name |
+--------+----------+
| test01 | 看書     |
| stu01  | 運(yùn)動(dòng)     |
| wangwu | 聽(tīng)歌     |
+--------+----------+
3 rows in set (0.00 sec)

mysql> create view view_user as select u.name,h.hob_name from user u inner join hob h on u.hobby
##創(chuàng)建視圖
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_user;  ##查看視圖
+--------+----------+
| name   | hob_name |
+--------+----------+
| test01 | 看書     |
| stu01  | 運(yùn)動(dòng)     |
| wangwu | 聽(tīng)歌     |
+--------+----------+
3 rows in set (0.00 sec)

mysql> update user set hobby=3 where name='test01';  ##修改user表中內(nèi)容
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from view_user;   ##查看視圖，即視圖就是表的一個(gè)鏈接
+--------+----------+
| name   | hob_name |
+--------+----------+
| stu01  | 運(yùn)動(dòng)     |
| test01 | 聽(tīng)歌     |
| wangwu | 聽(tīng)歌     |
+--------+----------+
3 rows in set (0.00 sec)

5，全文索引，組合索引

mysql> select * from info;     ##查看表內(nèi)容
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)

mysql> show index from info;    ##查看表的索引
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY    |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | index_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> create fulltext index full_addr on info (address);      ##以address創(chuàng)建全文索引
Query OK, 0 rows affected, 1 warning (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show index from info;  ##查看表索引
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY    |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | index_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          1 | full_addr  |            1 | address     | NULL      |           2 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> select * from user;     ##查看user表內(nèi)容
+----+--------+-------+-------+
| id | name   | score | hobby |
+----+--------+-------+-------+
|  1 | test01 |    88 |     3 |
|  2 | stu01  |    99 |     2 |
|  3 | wangwu |    77 |     3 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)

mysql> create index index_name_score on user (name,score);    ##創(chuàng)建name和score的組合索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from user;   ##查看表索引
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY          |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | index_score      |            1 | score       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | index_name_score |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | index_name_score |            2 | score       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

事務(wù)實(shí)操

1，開啟事務(wù)，往表中插入數(shù)據(jù)

mysql> select * from info;   ##查看表內(nèi)容數(shù)據(jù)
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)

mysql> insert into info (name,address,age) values ('wangwu','hangzhou',30)；##插入數(shù)據(jù)

mysql> begin;   ##開啟事務(wù)
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values ('zhaoliu','hangzhou',31);  ##插入數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)

mysql> savepoint a;   ##設(shè)置保存節(jié)點(diǎn)a
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;   ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

mysql> insert into info (name,address,age) values ('tianqi','hangzhou',32);  ##繼續(xù)插入數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)

mysql> savepoint b;   ##設(shè)置保存節(jié)點(diǎn)b
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values ('heiba','hangzhou',32);      ##繼續(xù)插入數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;  ##查看表內(nèi)容
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
|  5 | tianqi   | hangzhou |  32 |
|  6 | heiba    | hangzhou |  32 |
+----+----------+----------+-----+
6 rows in set (0.00 sec)

2，利用另一個(gè)終端查看是否成功插入

[root@master2 ~]# mysql -uroot -p  ##進(jìn)入數(shù)據(jù)庫(kù)
Enter password:     ##輸入密碼

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use school;   ##使用數(shù)據(jù)庫(kù)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from info;   ##查看表內(nèi)容，此時(shí)并沒(méi)有提交
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
+----+----------+----------+-----+
3 rows in set (0.00 sec)

3，使用回滾，返回保存的節(jié)點(diǎn)

mysql> rollback to b;    ##利用回滾到保存節(jié)點(diǎn)b
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;   ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
|  5 | tianqi   | hangzhou |  32 |
+----+----------+----------+-----+
5 rows in set (0.00 sec)

mysql> rollback to a;  ##回滾到保存節(jié)點(diǎn)a
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;   ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

mysql> rollback;     ##回滾到初始，退出事務(wù)狀態(tài)
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;  ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
+----+----------+----------+-----+
3 rows in set (0.00 sec)

4，使用commit提交事務(wù)

mysql> begin;    ##開啟事務(wù)
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values ('heiba','hangzhou',32);  ##插入數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)

mysql> commit;   ##提交事務(wù)
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;  ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | hangzhou |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

5，使用另一個(gè)終端查看

mysql> select * from info;   ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | hangzhou |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

6，另一種事務(wù)的操作方式

mysql> set autocommit=0;   ##設(shè)置不自動(dòng)提交事務(wù)
Query OK, 0 rows affected (0.00 sec)

mysql> update info set address='beijing' where name='heiba';  ##修改表數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info;   ##查看表信息
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | beijing  |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

##另一個(gè)終端查看
mysql> select * from info;  ##查看表信息，并沒(méi)有修改
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | hangzhou |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

mysql> set autocommit=1;   ##開啟自動(dòng)提交事務(wù)
Query OK, 0 rows affected (0.00 sec)

##另一個(gè)終端查看
mysql> select * from info;   ##查看表數(shù)據(jù)，此時(shí)就已經(jīng)修改
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | beijing  |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

存儲(chǔ)引擎實(shí)操

1，查看系統(tǒng)默認(rèn)存儲(chǔ)引擎

mysql> show engines;   ##查看默認(rèn)存儲(chǔ)引擎innodb
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |

mysql> show create table info;   ##查看創(chuàng)建的表的存儲(chǔ)引擎innodb

| info  | CREATE TABLE "info" (
    "id" int(4) NOT NULL AUTO_INCREMENT,
    "name" varchar(10) NOT NULL,
    "address" varchar(50) DEFAULT 'nanjing',
    "age" int(3) NOT NULL,
    PRIMARY KEY ("id"),
    UNIQUE KEY "index_name" ("name"),
    FULLTEXT KEY "full_addr" ("address")
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

2，修改MySQL配置文件，設(shè)置默認(rèn)的存儲(chǔ)引擎

[root@localhost ~]# vim /etc/my.cnf ##修改配置文件

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysql.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
default-storage-engine=Myisam   ##添加默認(rèn)存儲(chǔ)引擎為Myisam

[root@master2 ~]# systemctl restart mysqld.service ##重啟MySQL服務(wù)

3，進(jìn)入數(shù)據(jù)庫(kù)

[root@master2 ~]# mysql -uroot -p   ##進(jìn)入數(shù)據(jù)庫(kù)
Enter password:    ##輸入密碼

mysql> use school;    ##使用數(shù)據(jù)庫(kù)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table a ( id int );    ##創(chuàng)建一個(gè)a表
Query OK, 0 rows affected (0.00 sec)

mysql> show create table a;   ##查看表默認(rèn)的存儲(chǔ)引擎Myisam
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table                                                                        |
+-------+-------------------------------------------------------------------------------------+
| a     | CREATE TABLE "a" (
    "id" int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table a engine=innodb;  ##修改表的存儲(chǔ)引擎為innodb
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table a;  ##查看表的存儲(chǔ)引擎innodb
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table                                                                        |
+-------+-------------------------------------------------------------------------------------+
| a     | CREATE TABLE "a" (
    "id" int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)