MySQL學習進階

標籤：比較   檔案   div   位置   特殊   data   https   .com   中繼   

儲存引擎MyISAMMySQL 5.0 之前的預設資料庫引擎，最為常用。擁有較高的插入，查詢速度，但不支援事務InnoDB事務型資料庫的首選引擎，支援ACID事務，支援行級鎖定, MySQL 5.5 起成為預設資料庫引擎　　——MyISAM表中在插入式整個表被鎖定.InnoDB只鎖定該記錄.所有如果執行大量的update和insert,應該使用InnoDB;如果主要用來執行select,應該是MyISAM. BDB源 自 Berkeley DB，事務型資料庫的另一種選擇，支援Commit 和Rollback 等其他事務特性Memory所有資料置於記憶體的儲存引擎，擁有極高的插入，更新和查詢效率。但是會佔用和資料量成正比的記憶體空間。並且其內容會在 MySQL 重新啟動時丟失Merge將一定數量的 MyISAM 表聯合而成一個整體，在超大規模資料存放區時很有用Archive非常適合儲存大量的獨立的，作為記錄的資料。因為它們不經常被讀取。Archive 擁有高效的插入速度，但其對查詢的支援相對較差Federated將不同的 MySQL 伺服器聯合起來，邏輯上組成一個完整的資料庫。非常適合分布式應用Cluster/NDB高冗餘的儲存引擎，用多台資料機器聯合提供服務以提高整體效能和安全性。適合資料量大，安全和效能要求高的應用CSV： 邏輯上由逗號分割資料的儲存引擎。它會在資料庫子目錄裡為每個資料表建立一個 .csv 檔案。這是一種普通文字檔，每個資料行佔用一個文本行。CSV 儲存引擎不支援索引。BlackHole：黑洞引擎，寫入的任何資料都會消失，一般用於記錄 binlog 做複製的中繼EXAMPLE 儲存引擎是一個不做任何事情的存根引擎。它的目的是作為 MySQL 原始碼中的一個例子，用來示範如何開始編寫一個新儲存引擎。同樣，它的主要興趣是對開發人員。EXAMPLE 儲存引擎不支援編索引。mysql資料類型

(1) 數實值型別 
 

(2)字串類型 
 

(3)日期和時間型 

sql語言

約束

約束類型：	主鍵	預設值	唯一	外鍵	非空
關鍵字：	PRIMARY KEY	DEFAULT	UNIQUE	FOREIGN KEY	NOT NULL

主鍵(PRIMARY KEY)是用於約束表中的一行，作為這一行的標識符，在一張表中通過主鍵就能準確定位到一行，因此主鍵十分重要。主鍵要求這一行的資料不能有重複且不可為空。

還有一種特殊的主鍵——複合主鍵。主鍵不僅可以是表中的一列，也可以由表中的兩列或多列來共同標識

預設值約束(DEFAULT)規定，當有DEFAULT約束的列，插入資料為空白時該怎麼辦。

DEFAULT約束只會在使用INSERT語句（上一實驗介紹過）時體現出來，INSERT語句中，如果被DEFAULT約束的位置沒有值，那麼這個位置將會被DEFAULT的值填充

唯一約束(UNIQUE)比較簡單，它規定一張表中指定的一列的值必須不能有重複值，即這一列每個值都是唯一的。

當INSERT語句新插入的資料和已有資料重複的時候，如果有UNIQUE約束，則INSERT失敗.

外鍵(FOREIGN KEY)既能確保資料完整性，也能表現表之間的關係。

一個表可以有多個外鍵，每個外鍵必須REFERENCES(參考)另一個表的主鍵，被外鍵約束的列，取值必須在它參考的列中有對應值。

在INSERT時，如果被外鍵約束的值沒有在參考列中有對應，比如以下命令，參考列(department表的dpt_name)中沒有dpt3，則INSERT失敗

 非空約束(NOT NULL),聽名字就能理解，被非空約束的列，在插入值時必須非空。

在MySQL中違反非空約束，不會報錯，只會有警告.

CREATE DATABASE mysql_shiyan;use mysql_shiyan;CREATE TABLE department(  dpt_name   CHAR(20) NOT NULL,  people_num INT(10) DEFAULT ‘10‘,  CONSTRAINT dpt_pk PRIMARY KEY (dpt_name)     ##約束名 dpt_pk 、主鍵約束 );CREATE TABLE employee(  id      INT(10) PRIMARY KEY,  name    CHAR(20),  age     INT(10),  salary  INT(10) NOT NULL,  phone   INT(12) NOT NULL,  in_dpt  CHAR(20) NOT NULL,  UNIQUE  (phone),  CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name)    ##外鍵約束 ); CREATE TABLE project(  proj_num   INT(10) NOT NULL,  proj_name  CHAR(20) NOT NULL,  start_date DATE NOT NULL,  end_date   DATE DEFAULT ‘2017-08-15‘,  of_dpt     CHAR(20) REFERENCES department(dpt_name),  CONSTRAINT proj_pk PRIMARY KEY (proj_num,proj_name) );

樣本表的操作1、建立表 CREATE TABLE 表名 （屬性名稱 資料類型 [完整性條件約束條件]， 屬性名稱 資料類型 [完整性條件約束條件]， 屬性名稱 資料類型 [完整性條件約束條件]）2、 查看錶結構

describe 表名

3、查看錶結構

show create table 表名

show create table 表名 \G

4、 修改表名

alter table 舊錶名 rename [to] 新表名；

5、 修改欄位的資料類型 alter table 表名 modify 屬性名稱 資料類型； 6、修改欄位名

alter table 表名 change 舊屬性名稱 新屬性名稱 [新資料類型]；

7、 增加欄位

alter table 表名 add 屬性名稱1 資料類型 [完整性條件約束條件] [first] after 屬性名稱2；

8、 刪除欄位

alter table 表名 drop 屬性名稱；

9、刪除關聯表

（1）刪除表的外鍵約束
外鍵是一個特殊欄位，其將某一個表與其父表建立關聯關係。在建立表的時候，外鍵約束就已經設定好了。去掉他們之間的關聯關係需要用到下面語句。

 alter table 表名 drop foreign key 外鍵別名；

（2）刪除沒有被關聯的普通表 drop table 表名； （3）刪除被其他表關聯的父表

當刪除有關聯關係的表時，用drop table example1 會報錯，原因是有外鍵依賴於該表

例如建立了一個example4表依賴於example1表，example4表的外鍵stu_id依賴於example1表的主鍵。example1表時example4表的父表。

如果要刪除example4表，必須先去掉這種依賴關係。最簡單的辦法是先刪除子表example4，然後刪除父表example1。但這樣可能會影響子表的其他資料。

另一種方法是先刪除子表的外鍵約束，然後刪除父表。這種方法不會影響子表的其他資料，可以保證資料庫的安全。

比如，example4表的外鍵別名是d_fk，刪除example4的外鍵約束

alter table example4 drop foreign key d_fk;

可以通過show create table example4 \G來查看是否已刪除。
然後再執行  drop table example1;

執行成功則表示操作成功。

協助

\h　　　　　　 　　　　　　　　　　　　　　　　查看協助

\s　　　　 　　　　　　　　　　　　　　　　　　查看當前環境

\c　　 　　　　　　　　　　　　　　　　　　　　廢棄當前輸入命令

建立使用者並授權

CREATE USER ‘username‘@‘host‘ IDENTIFIED BY ‘password‘; 

GRANT {ALL | SELECT | INSERT | UPDATE } ON databasename.tablename TO ‘username‘@‘host‘ 

 工作中使用的樣本：

BEGIN      DECLARE win_id VARCHAR(20);    DECLARE win_count VARCHAR(20);    SELECT id FROM windows_vps_log ORDER BY id DESC LIMIT 1 into win_id;  SELECT count(id) FROM windows_vps_log into win_count;    IF win_count>5000000 THEN        delete from windows_vps_log where id <= win_id-3000000;    END IF;END

預存程序

SELECT    machine_host,    CONCAT(lan_ip, ‘:‘, `port`) AS "問題機",    dial_timeFROM    client_infoWHERE    mac IN (        SELECT            mac        FROM            windows_vps_log        WHERE            invalid = 0        AND TIMESTAMPDIFF(MINUTE, create_time, now()) > 180    )ORDER BY    machine_host desc；

多表查詢

 

MySQL學習進階