MySQL資料庫基礎學習筆記（二）

一、資料庫表的約束

1、分類

2、詳細使用

非空約束

• 創建表時，添加非空約束

create table 表名(
  欄位名 欄位型別 not null -- 對該欄位添加了非空約束
);

• 創建后，如何添加非空約束

alter table 表名 modify 欄位名 欄位型別 not null;

• 創建后，如何洗掉非空約束

alter table 表名 modify 欄位名 欄位型別;

唯一約束

• 創建表時，添加唯一約束

create table 表名(
  欄位名 欄位型別 unique
);

• 創建后，如何添加唯一約束

alter table 表名 modify 欄位名 欄位型別 unique;

• 創建后，如何洗掉唯一約束

alter table 表名 drop index 欄位名;

主鍵約束

注意：
1、一張表只能有一個欄位為主鍵，
2、主鍵就是表中記錄的唯一標識，
3、自增長只能用在主鍵上

• 創建表時，添加主鍵約束

create table 表名(
  欄位名 欄位型別 primary key
);

• 創建后，如何添加主鍵約束

alter table 表名 modify 欄位名 欄位型別 primary key;

• 創建后，如何洗掉主鍵約束

alter table 表名 drop primary key;

• 自動增長
  如果某一列是數值型別的，使用 auto_increment 可以來完成值得自動增長
  • 創建表時，添加主鍵約束，并且完成主鍵自增長

  create table 表名(
    欄位名 欄位型別 primary key auto_increment
  );
  

  • 創建后，如何添加自動增長

  alter table 表名 modify 欄位名 欄位型別 auto_increment;
  

  • 創建后，如何洗掉自動增長

  alter table 表名 modify 欄位名 欄位型別;
  

設定默認值

create table 表名(
  欄位名 欄位型別 default 默認值
);

外鍵約束

• 簡單例子

-- 部門表 和 員工表 （1 對 多的關系）
-- 部門表（主表：一方，用來約束別人的表）
create table department(
  id int primary key auto_increment,
  dep_name varchar(20),
  dep_location varchar(20)
);

-- 員工表(從表：多方，被別人約束的表)
create table employee(
  id int primary key auto_increment,
  name varchar(20),
  age int,
  dep_id int, -- 外鍵對應主表的主鍵
  -- 創建外鍵約束
  foreign key (dep_id) references department(id)
)

• 創建表時，添加外鍵約束

create table 從表名(
  外鍵欄位名 欄位型別, -- 外鍵對應主表的主鍵
  [constraint] [外鍵名稱] foreign key (外鍵列名稱)
  references 主表名稱(主表列名稱)
);

• 創建后，如何添加外鍵約束

alter table 從表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵欄位名稱) REFERENCES 主表名稱(主表列名稱);

• 創建后，如何洗掉外鍵約束

alter table 表名 drop foreign key 外鍵名稱;

• 級聯操作
  在修改和洗掉主表的主鍵時，同時更新或洗掉副表的外鍵值，稱為級聯操作

注意：只能是創建表的時候創建級聯關系，

-- 員工表
create table employee4(
  id int primary key auto_increment,
  name varchar(20),
  age int,
  dep_id int, -- 外鍵對應主表的主鍵
  -- 創建外鍵約束
  constraint emp_depid_fk2 foreign key (dep_id) references department(id) on update cascade on delete cascade
)

二、資料庫的設計

1、表與表之間的關系

表與表之間的三種關系：

• 1、一對多：最常用的關系 部門和員工
  • 鍵表原則：在從表(多方)創建一個欄位,欄位作為外鍵指向主表(一方)的主鍵
• 2、多對多：學生選課表 和 學生表， 一門課程可以有多個學生選擇，一個學生選擇多門課程
  • 鍵表原則：需要創建第三張表，中間表中至少兩個欄位，這兩個欄位分別作為外鍵指向各自一方的主鍵，
• 3、一對一：相對使用比較少，可以創建成一張表，公民表 護照表
  • 建表原則：可以在任意一方添加唯一外鍵指向另一方的主鍵

2、資料規范化（三大范式）

• 設計資料庫時，需要遵循的一些規范，要遵循后邊的范式要求，必須先遵循前邊的所有范式要求
• 目前關系資料庫有六種范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又稱完美范式），

三、資料庫的備份和還原

• 命令列方式：

  • 備份： mysqldump -u用戶名 -p密碼 需要備份的資料庫名稱 > 保存的路徑
  • 還原：登錄-->創建-->使用（use 資料庫名稱）--->執行檔案（ source 檔案路徑）

• 圖形化工具

四、多表查詢

• 笛卡爾積
  • 有兩個集合A,B 取這兩個集合的所有組成情況
  • 要完成多表查詢，需要消除無用的資料

1、多表查詢的分類

隱式內連接

看不到 join 關鍵字，條件使用 where 指定

• 語法

select 欄位名 from 表1, 表2 where 條件;

顯式內連接

使用 inner join ... on 陳述句, 可以省略 inner

• 語法

select 欄位串列 from 表1 [inner] join 表2 on 條件;

左外連接

查詢的是左表所有資料以及其交集部分

• 語法

select 欄位串列 from 表1 left [outer] join 表2 on 條件;

右外連接

查詢的是右表所有資料以及其交集部分

• 語法

select 欄位串列 from 表1 right [outer] join 表2 on 條件;

子查詢

查詢中嵌套查詢，稱嵌套查詢為子查詢，

子查詢的不同情況：

• 1、子查詢的結果是單行單列的時候
  子查詢的結果是一個值的時候，可以作為條件，使用運算子去判斷， 運算子： > >= < <= =

SELECT 查詢欄位 FROM 表 WHERE 欄位=（子查詢）

• 2、子查詢結果是多行單列的時候
  結果集類似于一個陣列，父查詢使用 in 運算子

SELECT 查詢欄位 FROM 表 WHERE 欄位 IN （子查詢）;

• 3、子查詢的結果是多行多列的時候
  子查詢可以作為一張虛擬表參與查詢

SELECT 查詢欄位 FROM （子查詢） 表別名 WHERE 條件;

總結一下：

• 1、子查詢結果是單列，則在 where 后面作為條件
• 2、子查詢結果是多列，則在 from 后面作為表進行二次查詢

五、事務

如果一個包含多個步驟的業務操作，被事務管理，那么這些操作要么同時成功，要么同時失敗，

1、事務提交方式

• 修改事務的默認提交方式

1、查看事務的默認提交方式:
SELECT @@autocommit; -- 1 代表自動提交  0 代表手動提交

2、修改默認提交方式：
set @@autocommit = 0;

• 手動提交事務 （Oracle資料庫默認是手動提交事務）
  • 首先要開啟事務，再提交

• 手動提交事務使用程序

• 自動提交事務（mySQL默認自動提交）
  • 一條DML(增刪改)陳述句會自動提交一次事務，

2、事務的四大特征

• 1. 原子性：是不可分割的最小操作單位，要么同時成功，要么同時失敗，
• 2. 持久性：當事務提交或回滾后，資料庫會持久化的保存資料，
• 3. 隔離性：多個事務之間，相互獨立，
• 4. 一致性：事務操作前后，資料總量不變

3、事務的隔離級別（了解）

多個事務之間隔離的，相互獨立的，但是如果多個事務操作同一批資料，則會引發一些問題，設定不同的隔離級別就可以解決這些問題，

存在問題

• 1. 臟讀：一個事務，讀取到另一個事務中沒有提交的資料
• 2. 不可重復讀(虛讀)：在同一個事務中，兩次讀取到的資料不一樣，
• 3. 幻讀：一個事務操作(DML)資料表中所有記錄，另一個事務添加了一條資料，則第一個事務查詢不到自己的修改，

隔離級別:

• 1. read uncommitted：讀未提交
  • 產生的問題：臟讀、不可重復讀、幻讀
• 2. read committed：讀已提交 （Oracle默認）
  • 產生的問題：不可重復讀、幻讀
• 3. repeatable read：可重復讀 （MySQL默認）
  • 產生的問題：幻讀
• 4. serializable：串行化
  • 可以解決所有的問題

注意：隔離級別從小到大安全性越來越高，但是效率越來越低

• 資料庫查詢隔離級別

select @@tx_isolation;

• 資料庫設定隔離級別

set global transaction isolation level  級別字串;

六、DCL (管理用戶，授權)

1、管理用戶

• 查詢用戶

1、切換到mysql資料庫：use mysql;
2、查詢user表：select * from user;

注：% 表示可以在任意主機使用用戶登錄資料庫

• 添加用戶

create user '用戶名'@'主機名' identified by '密碼';

• 洗掉用戶

drop user '用戶名'@'主機名';

• 修改用戶密碼(2種方式)

update user set password = password('新密碼') where user = '用戶名';

set password for '用戶名'@'主機名' = password('新密碼');

• mysql中忘記了root用戶的密碼？

1、管理員運行該cmd ---> net stop mysql --停止mysql服務
2、使用無驗證方式啟動mysql服務：mysqld --skip-grant-tables
3、打開新的cmd視窗,直接輸入mysql命令，敲回車，就可以登錄成功
4、use mysql
5、update user set password = password('你的新密碼') where user = 'root';
6、關閉兩個視窗
7、打開任務管理器，手動結束mysqld.exe 的行程
8、啟動mysql服務，使用新密碼登錄

2、權限管理

• 查詢權限

show grants for '用戶名'@'主機名';

• 授予權限

grant 權限串列 on 資料庫名.表名 to '用戶名'@'主機名';

-- 給用戶授予所有權限，在任意資料庫的任意表上
GRANT ALL ON *.* TO '用戶名'@'主機名';

• 撤銷權限

revoke 權限串列 on 資料庫名.表名 from '用戶名'@'主機名';

標籤：MySQL

上一篇：SQLite問題

下一篇：[20201204]為什么回傳2行記錄.txt