MySQL基礎day03 儲存引擎和外鍵MySQL 5.6

標籤：des   style   http   java   color   使用   

• MySQL基礎day03_儲存引擎和外鍵-MySQL 5.6

   

  外鍵的條件：

  1，表的儲存引擎為innodb儲存引擎

  2，表中外鍵欄位的類型要與參考表的欄位類型一致

  3，外鍵欄位要是索引類型中的一種

   

  MySQL資料庫的儲存引擎

  儲存引擎：是mysql結構中的組成部分

  mysql體繫結構：

  由8部分組成：串連池、Sql介面、分析器、最佳化器、緩衝和緩衝、儲存引擎、管理工具、實體儲存體裝置

  1.用戶端：mysql 2.連接器： NativeC API 本地C語言介面、JDBCJAVA的資料庫連接器、ODBC開放式資料庫互聯，非常底層 Oracle，SOLServer都支援 3.串連池（conetcionpool）：它的後方是MYSQL的真正功能 4.SQL介面：使用者通過sql用戶端發過來的命令，由sql介面接收（DML資料操作語言：查詢，修改，升級資料等DDL資料定義語言 (Data Definition Language)：建立一個新的資料庫，新的索引，刪除一個使用者等預存程序視圖觸發器） 5.Parser:分析器（查詢 事務 對象許可權） 6.optimizer：（訪問路徑 、 產生執行樹） 7.caches&buffers：緩衝和緩衝 （最佳化裡最重要的是緩衝的最佳化） 8.pluggablestroage engines儲存引擎（外掛程式式）：將邏輯結構轉換為物理結構的程式 9.ManagementServices & Utilites工具：備份，恢複，安全，移植，叢集等，這些工具一般和檔案系統打交道，不需要和mysql-server打交道，它們對應的都是命令。

   

  查看當前mysql伺服器所支援的儲存引擎：show engines；

  01.mysql> show engines;02.+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+03.| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |04.+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+05.| PERFORMANCE_SCHEMA | YES     | PerformanceSchema                                             | NO           | NO   | NO         |06.| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |07.| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |08.| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |09.| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |10.| MEMORY             | YES     | Hash based, stored in memory, useful for temporarytables      | NO           | NO   | NO         |11.| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |12.| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreignkeys     | YES          | YES  | YES        |13.| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |14.+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+15.9 rows in set (0.00 sec)16.mysql>

   

   

   

   

  指定儲存引擎：

  1，建表時指定儲存引擎

  create table 表名(欄位列表)engine=儲存引擎名;

  註：在指定之前需要用showengine；查看所支援的引擎，Support列為是否支援！

  2，建表之後修改使用的儲存引擎，建表時沒有指定則為預設的：

  alter table 表名 engine=儲存引擎名;

   

  查看一個表的儲存引擎：（即查看錶的建立過程）

  show create table 表名;

   

  存放表結構的檔案為/var/lib/mysql/庫名/表名.frm

  存放表資料的檔案為/var/lib/mysql/庫名/表名.ibd

   

  事務：對錶中資料的一次操作，從開始到結束的過程稱作事務;

  交易回復：如果操作沒有成功，則恢複到之前的狀態;

  InnoDB支援交易回復;

   

  鎖機制：（解決多個使用者對資料庫記錄並發訪問問題）

  所得類型：讀鎖、寫鎖

  讀鎖(共用鎖定)：不會引起資料內容的改變;

  寫鎖(互斥鎖/排它鎖)，鎖定的程度為鎖粒度;

  註：寫鎖的優先順序高於讀鎖;

   

  鎖粒度：表鎖、行鎖、頁鎖

  表鎖：myisam儲存引擎支援表鎖

  行鎖：innodb儲存引擎支援行鎖

  頁級鎖定：查詢的資料存放存的儲存單位為頁(單位預設為M)

   

  row-level-locking 行級鎖定(行鎖);

  寫鎖的優先順序高於讀鎖;

   

  myisam儲存引擎：

  特點：

  支援表鎖，不支援行鎖；

  不支援事務；

  獨享資料表空間；（每一個表都有相應的三個相關檔案儲存體）

  多用在查詢、插入操作較多的表。

  相關檔案：表名.frm(表結構檔案)、表名.MYI(存放索引)、表名.MYD(存放表記錄)

   

  InoDB儲存引擎：

  特點：

  支援表鎖、行鎖;

  支援事務、支援外鍵;

  共用資料表空間;()

  相關檔案：

  表名.frm(表結構檔案)、表名.ibd(索引和表記錄檔案)

  ibdata1(支援復原)

  ib_logfile0、ib_logfile1

   

  設定mysql伺服器預設支援的儲存引擎：

  在設定檔/etc/my.cnf

  在mysqld欄位添加：

  default-storage-engine=儲存引擎

  default-storage-engine=myisam（預設的為innodb）

   

  查看當前的鎖狀態：

  即檢查Table_lock開頭的變數，%作為萬用字元；

  show status like‘table_lock%‘;

  01.mysql> show status like ‘table_lock%‘;02.+-----------------------+-------+03.| Variable_name         | Value |04.+-----------------------+-------+05.| Table_locks_immediate | 70    |06.| Table_locks_waited    | 0     |07.+-----------------------+-------+08.2 rows in set (0.00 sec)09.mysql>

   

   

  外鍵的條件：

  1，表的儲存引擎為innodb儲存引擎

  2，表中外鍵欄位的類型要與參考表的欄位類型一致

  3，外鍵欄位要是索引類型中的一種

   

  建立外鍵欄位的文法格式

  foreign key(欄位名) references 參考表的表名(欄位名)

   

  同步更新、同步刪除

  在建表的時候指定同步更新、刪除：

  ON UPDATE CASCADE 同步更新

  ON DELETE CASCADE 同步刪除

   

  樣本過程：

  create database engine; //建立一個名為engine的資料庫   create table engine.yg( //在engine庫中建立yg表 yg_id int(3) primary key auto_increment, name varchar(10) not null )engine=innodb; //指定儲存引擎為innodb   create table engine.gz( //在engine庫中建立yg表 gz_id int(3), gz float(7,2) not null default 0.00, foreign key(gz_id) references engine.yg(yg_id) //指定外鍵欄位為gz_id和yg的ge_id對應 on update cascade on delete cascade //指定同步更新，同步刪除 )engine=innodb;   use engine; //切換到engine資料庫   1：要求： 在yg表中插入名字為jim、tom兩個名字的條目； 再插入一個名字為tom的條目。 desc yg; insert into yg(name)values(‘jim‘); insert into yg(name)values(‘tom‘); insert into yg(name)values(‘tom‘);   2：要求： 在gz表中添加第2和第3個人的工資為10000,和12000； desc gz; insert into gz values(2,10000); insert into gz values(3,12000);   3：要求： 在yg表中添加一個員工，名字為lucy； 在gz表中插入lucy的工資為4000； 刪除yg表中lucy的員工資訊；註：刪除之後gz表中將沒有gz_id=4的工資資訊 使用select * from gz;查看gz中是否還有gz_id為4的條目。 insert into yg(name)values(‘lucy‘); insert into gz vlaues(4,4000); delete from yg where yg_id=4; select * from gz;   4：要求 更新yg表中的yg_id為2的員工資訊； update yg set yd_id=8 where yg_id=2;   5，要求： 刪除gz表中的外鍵； 註：在刪除外鍵的時候，首先使用show create table from gz;來查看一下外鍵的名字（註：在建立外鍵的時候使用的欄位名為gz_id，gz_id並不是外鍵名）。 show create table from gz; //首先查看建表過程尋找froeign key欄位 alter table gz drop foreign key gz_ibfk_1; //刪除外鍵 show create table from gz; //再次查看外鍵已經被刪除

   

  總結：想要建立一個外鍵，必須先瞭解一下MySQL的儲存引擎，只有innodb儲存引擎才支援建立外鍵，在MySQL5.6裡面，預設的儲存引擎為innodb！！！