MySQL 資料庫基本知識點

• 什麼是關系型資料庫
  • 關系型資料庫，是指采用了關系模型來組織資料的資料庫，其以行和列的形式存儲資料，以便于使用者了解，關系型資料庫這一系列的行和列被稱為表，一組表組成了資料庫
  • 主流的關系型資料庫有 Oracle、DB2、MySQL、SQL Server 等　　

• 什麼是非關系資料庫
  • 非關系型資料庫的名字叫 Not Only Sql　
  • 非關系型資料庫通常指資料以對象的形式存儲在資料庫中，而對象之間的關系通過每個對象自身的屬性來決定。　
  • 主流的非關系型資料庫有 hbase(列模型)、redis(鍵值對模型)、mongodb(文檔類模型)

• MySQL 與 Oracle 資料庫的差別
  • 本質的差別　　
    • Oracle 資料庫是一個對象關系資料庫管理系統（ORDBMS）。它通常被稱為 Oracle RDBMS 或簡稱為 Oracle，是一個收費的資料庫。　　　　
    • MySQL 是一個開源的關系資料庫管理系統（RDBMS）。它是世界上使用最多的 RDBMS，作為伺服器運作，提供對多個資料庫的多使用者通路。它是一個開源、免費的資料庫。　　　　
  • 資料庫安全性　　
    • MySQL 使用三個參數來驗證使用者，即使用者名，密碼和位置；Oracle 使用了許多安全功能，如使用者名，密碼，配置檔案，本地身份驗證，外部身份驗證，進階安全增強功能等。　　　　
  • SQL 文法的差別　　
    • Oracle 的 SQL 文法與 MySQL 有很大不同。Oracle 為稱為 PL / SQL 的程式設計語言提供了更大的靈活性。Oracle 的 SQL * Plus 工具提供了比 MySQL 更多的指令，用于生成報表輸出和變量定義。　　　　
  • 存儲上的差別：　　
    • 與 Oracle 相比，MySQL 沒有表空間，角色管理，快照，同義詞和包以及自動存儲管理。　　
  • 對象名稱的差別：　　　　
    • 雖然某些模式對象名稱在 Oracle 和 MySQL 中都不區分大小寫，例如列，存儲過程，索引等。但在某些情況下，兩個資料庫之間的區分大小寫是不同的。
    • Oracle 對所有對象名稱都不區分大小寫；而某些 MySQL 對象名稱（如資料庫和表）區分大小寫（取決于底層作業系統）。
  • 運作程式和外部程式支援：
    • Oracle 資料庫支援從資料庫内部編寫，編譯和執行的幾種程式設計語言。此外，為了傳輸資料，Oracle 資料庫使用 XML。
    • MySQL 不支援在系統内執行其他語言，也不支援 XML。
  • MySQL 和 Oracle 的字元資料類型比較：
    • 兩個資料庫中支援的字元類型存在一些差異。對于字元類型，MySQL 具有 CHAR 和 VARCHAR，最大長度允許為 65535 位元組（CHAR 最多可以為 255 位元組，VARCHAR 為 65535 位元組）。
    • 而 Oracle 支援四種字元類型，即 CHAR，NCHAR，VARCHAR2 和 NVARCHAR2；所有四種字元類型都需要至少 1 個位元組長；CHAR 和 NCHAR 最大可以是 2000 個位元組，NVARCHAR2 和 VARCHAR2的最大限制是4000個位元組。可能會在最新版本中進行擴充。

• MySQL 常用的資料類型

int	4 bytes	(-2 147483648，2147483647)	大整數值
bigint	8 bytes	(-9223372036854775808，9223372036854775807)	極大整數值
float		浮點值
char	0-255	定長字元串
varchar	0-65535	不定長字元串
date	3bytes	1000-01-01/9999-12-31	YYYY-MM-DD
datetime	8bytes	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS

• MySQL 中的主鍵限制
  • 主鍵就是主關鍵字(primary key)：表中的一個或多個字段，它的值用于唯一地辨別表中的某一條記錄。

• MySQL 中的外鍵限制
  • 如果表 A 的主關鍵字是表 B 中的字段，則該字段稱為表 B 的外鍵，表 A 稱為主表，表 B 稱為從表。外鍵是用來實作參照完整性的，不同的外鍵限制方式将可以使兩張表緊密的結合起來，特别是修改或者删除的級聯操作将使得日常的維護工作更加輕松。這裡以 MySQL 為例，總結一下 3 種外鍵限制方式的差別和聯系。
  • 這裡以使用者表和使用者組表為例，這是一個典型的多對一關系，多個使用者對應于一個使用者組。
  • 首先建立使用者組表：

create table t_group (
　　id int not null,
　　name varchar(30),
　　primary key (id)
　　);

//并插入兩條記錄：
　　insert into t_group values (1, 'Group1');
　　insert into t_group values (2, 'Group2');      

• • 下面建立使用者表，分别以不同的限制方式建立外鍵引用關系：
    • 級聯（cascade）方式

　　create table t_user (
　　id int not null,
　　name varchar(30),
　　groupid int,
　　primary key (id),
　　foreign key (groupid) references t_group(id) on delete cascade on update cascade
　　);

// 參照完整性測試
　　insert into t_user values (1, 'qianxin', 1);  // 可以插入
　　insert into t_user values (2, 'yiyu', 2);  // 可以插入
　　insert into t_user values (3, 'dai', 3);  // 錯誤，無法插入，使用者組3不存在，與參照完整性限制不符

// 限制方式測試
　　insert into t_user values (1, 'qianxin', 1);
　　insert into t_user values (2, 'yiyu', 2);
　　insert into t_user values (3, 'dai', 2);
　　delete from t_group where id=2;  // 導緻 t_user 中的 2、3 記錄級聯删除
　　update t_group set id=2 where id=1;  // 導緻 t_user 中的1記錄的 groupid 級聯修改為 2      

• • • 置空(set null)方式

create table t_user (
　　id int not null,
　　name varchar(30),
　　groupid int,
　　primary key (id),
　　foreign key (groupid) references t_group(id) on delete set null on update set null
　　);

// 參照完整性測試insert into t_user values (1, 'qianxin', 1);  // 可以插入
　　insert into t_user values (2, 'yiyu', 2);  // 可以插入
　　insert into t_user values (3, 'dai', 3);  // 錯誤，無法插入，使用者組 3 不存在，與參照完整性限制不符

// 限制方式測試
　　insert into t_user values (1, 'qianxin', 1);
　　insert into t_user values (2, 'yiyu', 2);
　　insert into t_user values (3, 'dai', 2);
　　delete from t_group where id=2;  // 導緻 t_user 中的 2、3 記錄的 groupid 被設定為 NULL
　　update t_group set id=2 where id=1;  //導緻 t_user 中的 1 記錄的 groupid 被設定為 NULL      

• • 禁止(no action / restrict)方式　　

create table t_user (
　　id int not null,
　　name varchar(30),
　　groupid int,
　　primary key (id),
　　foreign key (groupid) references t_group(id) on delete no action on update no action
　　);

// 參照完整性測試
　　insert into t_user values (1, 'qianxin', 1);  // 可以插入
　　insert into t_user values (2, 'yiyu', 2);  // 可以插入
　　insert into t_user values (3, 'dai', 3);  // 錯誤，無法插入，使用者組 3 不存在，與參照完整性限制不符

// 限制方式測試
　　insert into t_user values (1, 'qianxin', 1);
　　insert into t_user values (2, 'yiyu', 2);
　　insert into t_user values (3, 'dai', 2);
　　delete from t_group where id=2;  // 錯誤，從表中有相關引用，是以主表中無法删除
　　update t_group set id=2 where id=1;  // 錯誤，從表中有相關引用，是以主表中無法修改
// 注：在 MySQL 中，restrict 方式與 no action 方式作用相同      

•  MySQL 中 count(*)、count(1)、count(字段) 的差別

• • 執行效果上：

    • count(*) 

      包括了所有的列，相當于行數，在統計結果的時候，不會忽略列值為 NULL　　　　

    • count(1) 

      包括了忽略所有列，用1代表代碼行，在統計結果的時候，不會忽略列值為 NULL　　　　

    • count(列名) 

      隻包括列名那一列，在統計結果的時候，會忽略列值為空（這裡的空不是隻空字元串或者 0，而是表示 null）的計數，即某個字段值為 NULL 時，不統計。　　　　
  • 執行效率上：　　
    • 列名為主鍵，count(列名) 會比 count(1) 快　　　　
    • 列名不為主鍵，count(1) 會比 count(列名) 快　　　　
    • 如果表多個列并且沒有主鍵，則 count（1） 的執行效率優于 count（*）　　　　
    • 如果有主鍵，則 select count（主鍵）的執行效率是最優的　　　　
    • 如果表隻有一個字段，則 select count（*）最優。　　　　
•  MySQL 中的 DDL 語言
  • 資料庫模式定義語言 DDL(Data Definition Language)，是用于描述資料庫中要存儲的現實世界實體的語言。主要由 create（添加）、alter（修改）、drop（删除）和 truncate（删除） 四個關鍵字完成　　
• MySQL 中的 DML 語言
  • DML 操作是指對資料庫中表記錄的操作，主要包括表記錄的插入（insert）、更新（update）、删除（delete）和查詢（select）。　　

作者：多測師進階講師_鄭sir

微信：ZhengYing8887

出處：https://www.cnblogs.com/ZhengYing0813/

備注：本文版權歸作者所有，歡迎轉載和添加作者微信探讨技術，但未經作者同意必須在文章頁面給出原文連結，否則保留追究法律責任的權利。