MySQL整理（二）

標籤：img   嵌套   show   文檔   索引   group by   mysq   檢索   設定   

　　一、MySQL動作表的約束

　　　　MySQL提供了一系列機制來檢查資料庫表中的資料是否滿足規定條件，以此來保證資料庫表中資料的準確性和一致性，這種機制就是約束。

　　　　（1）設定非空約束（NOT NULL），唯一約束（UNIQUE），欄位預設值（DEFAULT）　　

create table 表名(    uid int unique,（唯一性限制式，UK）    name varchar(5) not null,（非空約束，NK）    age varchar(3) default ‘12‘（設定欄位預設值）);

　　　　（2）主鍵約束（相當於非空約束加上唯一約束）

create table 表名(    uid int primary key,（主鍵約束）    name varchar(5) not null,    age varchar(3) default ‘12‘);

　　　　（3）多欄位主鍵的設定使用constraint關鍵字

create table 表名(    id int,    name varchar(5) not null,    age varchar(3) default ‘12‘,    constraint pk_id_name primary key(id,name));

　　　　（4）欄位值自動增加約束的關鍵字auto_increment

create table 表名(    id int primary key auto_increment,    name varchar(5) not null,    age varchar(3) default ‘12‘);

　　　　（5）外鍵約束的設定constraint，foreign key，references（設定FK約束時，設定FK的欄位必須依賴於資料庫中已經存在的父表的主鍵，同時外鍵可以為NULL）

create table 表名(    id int,    name varchar(5) not null,    age varchar(3) default ‘12‘,    constraint fk_id foreign key(id) references 主鍵所在表(id));　　　　

　　二、索引的操作

　　　　（1）為什麼使用索引：

　　　　資料庫物件索引與書的目錄相似，主要是為了提供從表中檢索資料的速度，索引可以由表的一個或多個欄位產生的鍵組成，通過MySQL尋找時可以有效找到與鍵相關聯的欄位，就類似從目錄直接找到我們需要的內容，而不必去全文檢索搜尋。

　　　　索引的建立有利有弊，建立索引可以提高檢索速度，但過多的索引會增加讀磁碟的壓力。

　　　　根據MySQL的查詢文檔發現，索引分為：普通索引，唯一索引，全文索引，單列索引，多列索引和空間索引。

　　　　（2）索引建立的情境：

　　　　經常被查詢的欄位，例如where或on語句後跟隨的欄位

　　　　需要分組查詢的欄位，例如group by後跟隨的欄位

　　　　存在依賴關係的父表與字表之間的聯集查詢，如主鍵和外鍵

　　　　設定唯一完整性條件約束的欄位

　　　　註：查詢中很少使用的欄位，或者欄位上有許多重複值的欄位，不適合添加索引

　　　　（3）建立普通索引：index 索引名 （欄位名）　　　　

create table fuck(    id int,    name varchar(5),    age varchar(3) default ‘12‘,    index index_id(id));

　　　　（4）查看索引方法：

　　　　show create table 表名 \G 

　　　　

　　　　explain select * from 表名 where 索引欄位名=1\G

　　　　

　　　　（5）在已經存在的表上建立索引：

　　　　create index 索引名 on 表名(索引欄位名);

　　　　create index index_name on fuck(name);

　　　　alter table 表名 add index 索引名(索引欄位名);

 　　　  alter table fuck add index index_age(age); 

　　　　（6）建立唯一索引（unique），全文索引（fulltext）與普通索引的文法相同，只是在index關鍵字前加上 unique或fulltext

　　　　（7）建立多列索引：alter table 表名 add index 索引名(索引欄位名1，索引欄位名2，...);

　　　　 註：多列索引，是指建立索引時，關聯了多個欄位，雖然可以通過所關聯的欄位查詢，但只有查詢條件中使用了所關聯欄位中的第一個欄位，多列索引才會起作用。

　　　　（8）刪除索引：drop index 索引名 on 表名

　　三、視圖的操作

　　　　（1）為什麼使用視圖？

　　　　為了提高複雜SQL的複用性和表操作的安全性，MySQL提供了視圖特性，所謂視圖，實際上是一種虛擬表，其內容與真實的表相似，其並不在資料庫中以儲存的資料值存在，行和列的資料都來自其引用的基本表。

　　　　使用視圖使程式員只需關心他們所需要的欄位，並且可以屏蔽一些敏感欄位，如工資等欄位。

　　　　視圖的特點如下：

　　　　a)視圖是由基本表產生的虛擬表

　　　　b)視圖的列可以來自不同的表

　　　　c)視圖的建立和刪除不影響基本表

　　　　d)對視圖內容的更新直接影響基本表

　　　　e)當視圖來自多個基本表時，不允許添加和刪除資料

　　　　（2）建立視圖的文法

　　　　create view 視圖名 as select id ,age from 表名：create view fuck_view as select id ,age from fuck;

　　　　（3）刪除視圖

　　　　drop view 視圖名

　　　　（4）修改視圖

　　　　方式一（先刪除後建立）：drop view 視圖名；create view 視圖名 as select...

　　　　方式二（直接修改）：create or replace view 視圖名 as select...

　　　　方式三（alter語句）：alter view 視圖名 as select...

　　　　註：對視圖的增刪查改操作和基本表一致。

　　 四、觸發器介紹

　　　  （1）為什麼使用觸發器？

　　　　觸發器（TRIGGER）是MySQL的對象之一，需要先聲明後執行，但觸發器的執行得由事件來觸發，即在表發生更改時，自動進行一些處理。

　　　　MySQL提供了在觸發delete,update,insert語句時，就會自動執行所設定的操作。觸發器可以當做是攔截器的作用。

　　　　使用樣本：單一條件觸發器

　　　　建立名字為tri_employee的觸發器，在每次往fuck表中添加資料時就會觸發事件，即往employee表中也添加一條資料。

create trigger tri_employee before insert on fuck for each row     insert into employee(empno,empname,salary,hiredate,deptno) values(33,‘haha‘,688,NULL,4);

 　　　　使用樣本：多條件觸發器

　　　　 與單個條件的觸發器不同的是，觸發執行的多個語句嵌套在begin,end中間，用分號分隔，同時MySQL一般以分號作為結束符號，但在建立觸發器時，需要以分號來分隔，為瞭解決該問題，可以使用關鍵字delimiter語句，來設定結束符號，如下面的語句，就將結束符號設定為$$

delimiter $$create trigger tri_employee before insert on fuck for each row     begin    insert into employee(empno,empname,salary,hiredate,deptno) values(33,‘haha‘,688,NULL,4);    insert into employee(empno,empname,salary,hiredate,deptno) values(44,‘hehe‘,886,NULL,5);    end    $$delimiter;

　　　　查看觸發器：

　　　　（1）show triggers;

　　　　（2）進入系統資料表，查看系統資料表中儲存的觸發器：use information_schema；select * from triggers \G

　　　　（3）系統資料表中查看指定觸發器：select * from triggers where trigger_name=‘xxx‘ \G

　　　　刪除觸發器：drop trigger 觸發器的名稱;

MySQL整理（二）