MySQL資料庫--加強篇

資料限制

• 什麼是資料的限制

對使用者操作表的資料進行限制（限制使用者對資料表的操作）

• 預設值（default）

作用：當使用者對使用預設值的字段 不插入值的時候，就使用預設值。

注意：插入null時，依舊插入null，

• 非空(not null)

作用：限制字段必須插入資料

注意：1.非空字段，不能不指派

2.不能指派null

• 唯一(unique)

作用：對字段的值不能重複

注意：1. 唯一的字段可以插入null值

2.唯一的字段可以插入多個null值

• 主鍵(非空+唯一 primary key)

作用：非空與唯一相結合

注意：1. 通常情況下，每張表都會設定一個主鍵的字段，用于标記表中的每條記錄的唯一性

2.設計主鍵的時候，不要選取表中，包含業務含義的字段，給每張表設定一個沒有業務含義的字段，通常為id字段

• 自增長(auto_increment)

作用：自動的遞增（zerofill）

int(4) zerofill----0001

注意：從0開始，是以插入的第一個數為1

• 外鍵(constraint 名稱(目前表名_參照表名_fk) foreign key(參照表+ID)) references 參考表（參考字段）

  

  image.png

作用：作用于兩張表，解決資料備援高的問題：獨立出一張表

注意：1.在插入資料的時候兩張表沒有必要的聯系，是以使用外鍵使獨立表限制原

2.參考獨立表的id

3.被限制的表稱為副表，限制别人的表成為主表，外鍵設定在主表上，通常作用與id

• 級聯操作（前提有外鍵）

  

on update cascade 修改

on delete cascade 删除

資料庫的設計

• 需求分析

需求分析師--原始需求--抽取業務模型

• 需求設計

概要設計：抽取實體<業務模型-->實體模型>，資料庫設計<業務模型/實體模型-->資料模型>

詳細設計：屬性，方法之類的詳細設計

• 三大範式
  • 設計原則：

    建議設計的表盡量遵守三大範式

    * 第一範式：要求表的每個字段必須是不可分割的獨立單元

    * 第二範式：在第一範式的基礎上，要求每張表隻表達一個意思。表的每個字段都和表的主鍵有依賴

    * 第三範式：在第二範式的基礎上，要求在每張表的除主鍵以外的其他字段，都和主鍵有直接的依賴關系（降低資料備援）---加入外鍵

關聯查詢（多表查詢）

多表查詢規則：

三步：

1.确定查詢哪些表

2.确定查詢哪些字段

3.表與表之間的連接配接關系（規律：連接配接條件數量=表數量-1）

• 交叉連接配接查詢

不推薦： 差生笛卡爾乘積現象，有些是重複記錄

• 内連接配接查詢

  

• 左【外】連接配接

概念：

使用左邊的表的資料去比對右邊表的資料，如果符合連接配接條件的結果，則顯示，如果不符合連接配接條件的結果，則顯示null

注意：左外連接配接；左邊的資料一定會全部顯示

• 右【外】連接配接

使用右邊的表的資料去比對左邊表的資料，如果符合連接配接條件的結果，則顯示，如果不符合連接配接條件的結果，則顯示null

注意：右外連接配接；右邊的資料一定會全部顯示

總結：

左外與右外連接配接其實是一樣的隻是個人喜好；

• 自連接配接查詢（用在分類别）

虛拟一張表

存儲過程

• 什麼是存儲過程

存儲過程，帶有邏輯的sql語句。（即sql的程式設計）

• 存儲過程的特點

執行效率非常快（存儲過程是在資料庫的伺服器端執行的）

移植性很差（不同資料庫的存儲過程是不能移植的）

• 存儲過程的文法

聲明存儲過程的結束符

存儲過程的名稱（參數清單）

開始

sql語句+流程控制

結束 結束符

call 存儲過程名稱（參數清單）

• 帶有輸入參數的存儲過程

• 删除存儲過程

  DROP PROCEDURE 存儲名稱;

MySQL的變量

• 全局變量（内置變量）

MySQL資料庫内置的變量（所有連接配接都起作用）

• 檢視所有全局變量 show variables
• 檢視某個全局變量 select @@變量名
• 修改某個全局變量 set 變量名=新值
• character_set_client : MySQL 伺服器的接受資料的編碼
• character_set_results ： MySQL伺服器輸出資料的編碼

• 會話變量

隻存在于目前用戶端與資料庫伺服器端的一次連接配接當中。如果連接配接斷開，那麼會話變量會全部丢失！

• 定義會話變量：

  set @變量=值

• 檢視會話變量：

  set @變量

• 局部變量

在存儲過程中使用的變量叫局部變量，隻要存儲過程執行完畢，局部變量就丢失

觸發器

• 作用

當操作了某張表時，希望同時觸發一些動作/行為，可以使用觸發器完成

添加：

create trigger 觸發器名稱 after insert on 資料表名 for each row;

insert into 修改器字段名 values("需要添加的資訊（例如：員工添加了一條資訊）")

修改：

create trigger 觸發器名稱 after update on 資料表名 for each row;

insert into 修改器字段名 values("需要添加的資訊（例如：員工修改了一條資訊）")

删除：

create trigger 觸發器名稱 after delete on 資料表名 for each row;

insert into 修改器字段名 values("需要添加的資訊（例如：員工删除了一條資訊）")

MySQL的權限

• 權限賬戶

隻擁有部分權限

例如：隻能操作某個資料庫的某張資料表的資料

• 如何修改MySQL的密碼

password md5加密函數（單向加密）

select password("root");

• 修改密碼

update user set password=password('123456') where user='root'

;

然後重新開機MySQL

• 配置設定權限賬戶

crant 權限 on 資料庫.表 to '帳戶名'@'賬戶類型' identified by '密碼';

crant inseret on 資料庫.表 to '自定義'@'%' identified by '密碼';

• 權限：select insert delete update drop create all

• 賬戶類型：

  %：遠端或本地均可

  localhost：本地

  192.168.1.1：固定的IP位址

MySQL的備份和還原

• 備份：

mysqldump -u root -p 資料庫名 > C:/**.sql

• 還原

mysql -u root -p 資料庫名<位址