MySQL第二天（MySQL索引值，MySQL儲存引擎）

標籤：646

day02
一、MySQL索引值（key） ##設定在欄位上，約束如何給欄位賦值。

普通索引 index
唯一索引 unique
主鍵 primary key
外鍵 foreign key
全文索引 fulltext

+++++++++++++++++++++++++++++++++++
1.1普通索引 index 的使用
1.1.1什麼是索引？
相當於"書的目錄"
1.1.2索引的優點與缺點
優點:加快查詢記錄的速度
缺點:占實體儲存體空間，減慢寫的速度（insert update delete）

100頁
1-10目錄頁（記錄目錄資訊）
11-100本文

1.1.3索引使用規則？

1.1.4建立索引欄位
1)建表 建立索引欄位
create table 表名（
欄位列表，
index(欄位名)，
index(欄位名)
);

mysql> create table t10(
-> name char(10) not null,
-> age tinyint(2),
-> sex enum("boy","girl","no"),
-> likes set("it","film","game","music"),
-> index(name),
-> index(sex)
-> );
Query OK, 0 rows affected (0.20 sec)

mysql> desc t10; ##查看t10表結構
+-------+---------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------------+------+-----+---------+-------+
| name | char(10) | NO | MUL | NULL | |
| age | tinyint(2) | YES | | NULL | |
| sex | enum(‘boy‘,‘girl‘,‘no‘) | YES | MUL | NULL | |
| likes | set(‘it‘,‘film‘,‘game‘,‘music‘) | YES | | NULL | |
+-------+---------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show index from t10\G; ##查看t10表索引
1. row
Table: t10
Non_unique: 1
Key_name: 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. row
Table: t10
Non_unique: 1
Key_name: sex
Seq_in_index: 1
Column_name: sex
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)

ERROR:
No query specified

2)把表中已有的欄位設定為索引欄位
create index 索引名稱 on 表名（欄位名）; ##最好索引名 和欄位名一樣

1.1.5查看錶中是否有索引欄位
desc 表面； key ----->MUL
show index from 表名；

mysql> create index name on studentinfo(name);
Query OK, 0 rows affected (0.41 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc studentinfo;
+-------+-----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------------+------+-----+---------+-------+
| name | char(10) | YES | MUL | NULL | |
| sex | enum(‘boy‘,‘girl‘,‘secret‘) | YES | | NULL | |
| age | tinyint(2) unsigned | YES | | 21 | |
| mail | char(30) | YES | | NULL | |
+-------+-----------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show index from studentinfo\G; ##欄位豎著顯示
1. row
Table: studentinfo
Non_unique: 1
Key_name: name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)

ERROR:
No query specified

1.1.6刪除索引

mysql> drop index name on studentinfo;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from studentinfo\G;
Empty set (0.00 sec)

ERROR:
No query specified
+++++++++++++++++++++++++++++++++++++++++++++++++++
主鍵 primary key 的使用 (主鍵的欄位不能重複且不能為null) 主鍵標誌是PRI
1.1 使用規則？
1.1.1
1)鍵表時建立主鍵欄位
第一種方法：
mysql> create table t11(
-> name char(10) primary key,
-> age int(2),
-> sex enum("b","g")
-> );
Query OK, 0 rows affected (0.15 sec)

mysql> desc t11;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| name | char(10) | NO | PRI | NULL | |
| age | int(2) | YES | | NULL | |
| sex | enum(‘b‘,‘g‘) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
第二種方法：
mysql> create table t12(
-> name char(10),
-> sex enum("b","g"),
-> primary key(name)
-> );
Query OK, 0 rows affected (0.33 sec)

mysql> desc t12;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| name | char(10) | NO | PRI | NULL | |
| sex | enum(‘b‘,‘g‘) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
刪除主鍵

alter table 庫.表 drop primary key;

2)在表中已有的欄位中建立主鍵
alter table 庫.表 add primary key(欄位)；

1.1.2建立複合鍵 （複合鍵是兩個主鍵） (當有兩個值可能相同，可以用複合鍵防止兩個值都相同，可以一個值相同)
mysql> create table t14(
-> cip char(15),
-> port smallint(2),
-> status enum("deny","allow"),
-> primary key(cip,port)
-> );
Query OK, 0 rows affected (0.23 sec)

mysql> insert into t13 values("1.1.1.1",21,"deny");
ERROR 1146 (42S02): Table ‘db.t13‘ doesn‘t exist
mysql> insert into t14 values("1.1.1.1",21,"deny");
Query OK, 1 row affected (0.08 sec)

mysql> insert into t14 values("1.1.1.1",22,"deny");
Query OK, 1 row affected (0.02 sec)

mysql> insert into t14 values("1.1.1.2",22,"deny");
Query OK, 1 row affected (0.02 sec)

mysql> insert into t14 values("1.1.1.2",22,"allow");
ERROR 1062 (23000): Duplicate entry ‘1.1.1.2-22‘ for key ‘PRIMARY‘

mysql> desc t14; ##key中有PRI 表示主鍵
+--------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| cip | char(15) | NO | PRI | NULL | |
| port | smallint(2) | NO | PRI | NULL | |
| status | enum(‘deny‘,‘allow‘) | YES | | NULL | |
+--------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from t14;
+---------+------+--------+
| cip | port | status |
+---------+------+--------+
| 1.1.1.1 | 21 | deny |
| 1.1.1.1 | 22 | deny |
| 1.1.1.2 | 22 | deny |
+---------+------+--------+
3 rows in set (0.00 sec)

刪除複合鍵
mysql> alter table t14 drop primary key(port); ##不能單獨刪除一個鍵
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(port)‘ at line 1

mysql> alter table t14 drop primary key; ##複合鍵一起建立且一起刪除
Query OK, 3 rows affected (0.50 sec)
Records: 3 Duplicates: 0 Warnings: 0

primary key 通常與auto_increment連用(欄位值自動+1)

mysql> create table t18(
-> id int(2) zerofill primary key auto_increment,
-> name char(10),
-> age tinyint(2) unsigned,
-> sex enum("boy","girl"),
-> likes set("film","book"),
-> index(name));
Query OK, 0 rows affected (0.17 sec)

mysql> desc t18;
+-------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+----------------+
| id | int(2) unsigned zerofill | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | MUL | NULL | |
| age | tinyint(2) unsigned | YES | | NULL | |
| sex | enum(‘boy‘,‘girl‘) | YES | | NULL | |
| likes | set(‘film‘,‘book‘) | YES | | NULL | |
+-------+--------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> insert into t18(name,age,sex,likes) values("tom",21,"boy","film,book");
Query OK, 1 row affected (0.03 sec)

mysql> select * from t18;
+----+------+------+------+-----------+
| id | name | age | sex | likes |
+----+------+------+------+-----------+
| 01 | tom | 21 | boy | film,book |
+----+------+------+------+-----------+
1 row in set (0.00 sec)

mysql> insert into t18(name,age,sex,likes) values("jim",21,"boy","film,book");
Query OK, 1 row affected (0.05 sec)

mysql> insert into t18(name,age,sex,likes) values("harry",21,"boy","film,book");
Query OK, 1 row affected (0.05 sec)

mysql> select * from t18;
+----+-------+------+------+-----------+
| id | name | age | sex | likes |
+----+-------+------+------+-----------+
| 01 | tom | 21 | boy | film,book |
| 02 | jim | 21 | boy | film,book |
| 03 | harry | 21 | boy | film,book |
+----+-------+------+------+-----------+
3 rows in set (0.00 sec)

mysql> insert into t18 values(13,"harry",21,"boy","film,book");
Query OK, 1 row affected (0.03 sec)

mysql> select * from t18;
+----+-------+------+------+-----------+
| id | name | age | sex | likes |
+----+-------+------+------+-----------+
| 01 | tom | 21 | boy | film,book |
| 02 | jim | 21 | boy | film,book |
| 03 | harry | 21 | boy | film,book |
| 13 | harry | 21 | boy | film,book |
+----+-------+------+------+-----------+
4 rows in set (0.00 sec)

尋找自己想要的記錄(索引尋找)
mysql> select * from t18 where id=03;
+----+-------+------+------+-----------+
| id | name | age | sex | likes |
+----+-------+------+------+-----------+
| 03 | harry | 21 | boy | film,book |
+----+-------+------+------+-----------+
1 row in set (0.00 sec)

外鍵 foreign 可以的使用
外鍵使用規則？

建立外鍵命令格式
create table 庫.表1(
欄位列表，
foreign key(欄位名) references 表2(欄位名)
on update cascade on delete cascade
)engine=innodb; ##同步刪除同步更新參考 表2

財務 100
姓名 學號 pay
bob nsd171108 2w

參考表為cwb
外鍵表為bjb

mysql> create table cwb( ##建立參考表cwb
-> cw_id int(2) primary key auto_increment,
-> name char(10),
-> pay float(7,2)
-> )engine=innodb;
Query OK, 0 rows affected (0.18 sec)

mysql> desc cwb; ##查看參考表cwb結構
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| cw_id | int(2) | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | | NULL | |
| pay | float(7,2) | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> insert into cwb(name,pay) values("bob",20000),("lucy",18000),("jack",16000); ##插入參考表cwb的記錄
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from cwb;
+-------+------+----------+
| cw_id | name | pay |
+-------+------+----------+
| 1 | bob | 20000.00 |
| 2 | lucy | 18000.00 |
| 3 | jack | 16000.00 |
+-------+------+----------+
3 rows in set (0.00 sec)

mysql> create table bjb(
-> bj_id int(2),
-> name char(10),
-> foreign key(bj_id) references cwb(cw_id)
-> on update cascade on delete cascade ##和cwb表同步更新和同步刪除
-> )engine=innodb;
Query OK, 0 rows affected (0.41 sec)

mysql> show create table bjb; ##顯示建立表格bjb外鍵的命令

mysql> insert into bjb values(1,"bob"),(2,"lucy"); ##插入id 為1和2的記錄
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into bjb values(7,"harry"); ##不成功，因為參考表中沒有id為7
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (db.bjb, CONSTRAINT bjb_ibfk_1 FOREIGN KEY (bj_id) REFERENCES cwb (cw_id) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> select * from bjb;
+-------+------+
| bj_id | name |
+-------+------+
| 1 | bob |
| 2 | lucy |
+-------+------+
2 rows in set (0.00 sec)

mysql> insert into cwb(cw_id,name,pay) values(7,"harry",21000); ##參考表中插入id為7的記錄
Query OK, 1 row affected (0.02 sec)

mysql> select * from cwb; ##查看參考表，記錄有了
+-------+-------+----------+
| cw_id | name | pay |
+-------+-------+----------+
| 1 | bob | 20000.00 |
| 2 | lucy | 18000.00 |
| 3 | jack | 16000.00 |
| 7 | harry | 21000.00 |
+-------+-------+----------+
4 rows in set (0.00 sec)

update cwb set cw_id=8 where cw_id=2; 修改參照表中的記錄

mysql> update cwb set cw_id=8 where cw_id=2; ##把參照表記錄中id為2的改為id為8
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from cwb;
+-------+-------+----------+
| cw_id | name | pay |
+-------+-------+----------+
| 1 | bob | 20000.00 |
| 3 | jack | 16000.00 |
| 7 | harry | 21000.00 |
| 8 | lucy | 18000.00 |
+-------+-------+----------+
4 rows in set (0.00 sec)

delete from cwb where cw_id=3 ##刪除參照表中的記錄

mysql> delete from cwb where cw_id=3; ##刪除參照表id為3的記錄
Query OK, 1 row affected (0.02 sec)

mysql> select * from cwb;
+-------+-------+----------+
| cw_id | name | pay |
+-------+-------+----------+
| 1 | bob | 20000.00 |
| 7 | harry | 21000.00 |
| 8 | lucy | 18000.00 |
+-------+-------+----------+
3 rows in set (0.00 sec)

刪除外鍵
alter table bjb drop foreign key 外鍵名

ysql> alter table bjb drop foreign key bjb_ibfk_1; ##bjb_ibfk_1為外鍵名
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

外鍵名可以 show create table bjb 查詢
如：
mysql> show create table bjb;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bjb | CREATE TABLE bjb (
bj_id int(2) DEFAULT NULL,
name char(10) DEFAULT NULL,
KEY bj_id (bj_id),
CONSTRAINT bjb_ibfk_1 FOREIGN KEY (bj_id) REFERENCES cwb (cw_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在已有的表裡添加外鍵欄位
alter table 表1 add foreign key(欄位名) references 表2(欄位名) on update cascade on delete cascade;

二、MySQL儲存引擎
2.1什麼是儲存引擎
是表的處理器
2.2查看資料庫服務支援哪些儲存引擎
mysql>show engines;

2.3查看錶使用的儲存引擎
mysql>show create table t1;

2.4修改 服務 和表使用的儲存引擎
mysql>alter table 表名 engine=儲存引擎;
改預設儲存引擎
vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam
#]systemctl stop mysqld
#]systemctl start mysqld

2.5生產環境中常用哪種儲存引擎，有什麼特點？

myisam特點（查詢次數的比較多適合這個，支援高並發讀）
不支援事務、外鍵、事物復原
支援表級鎖（一個人訪問就鎖這個表,表鎖的次數少,cpu消耗少）
每個表對應3個表檔案
表名.frm 表名.MYI 表名.MYD
表結構 索引 資料

innodb特點（寫的次數比較多適合這個，高並發寫)
支援事物、外鍵、事物復原
支援行級鎖（一個人訪問這個行就鎖這個行，其他人可以訪問其他行，行鎖的次數多）

每個表對應2個表檔案
表名.frm 表名.ibd
表結構 索引+資料

鎖類型
讀鎖（共用鎖定）select：支援並發讀
寫鎖（排它鎖或互斥鎖）insert update delete：上鎖期間其他線程不能讀表或寫表

事務？建立串連，操作資料，中斷連線的過程

交易回復？一次事務執行過程中，任何一步操作失敗，都會恢複之前的所有操作

交易記錄檔：
ib_logfile0

ib_logfile1

ibdata1

2.6如何決定表使用哪種儲存引擎
select ---->myisam
insert update delete -----> innodb

MySQL第二天（MySQL索引值，MySQL儲存引擎）