31. 資料庫基礎

hbutmeng發表於2024-10-09

資料庫

1. 資料庫基礎知識

1.1 關係型資料庫與非關係型資料庫

1.2 關係型資料庫的結構

庫 Database

庫，也稱資料庫，用於組織、儲存和管理資料

類比於資料夾

表 Table

表，是資料庫中基本的資料儲存單位，由行（Row）和列（Column）組成

類比於excel檔案

記錄 Record

記錄，是表中的一行的所有資料

例如：在一個訂單表中，每一條記錄可能代表一個訂單所有資訊，包含訂單號、訂單日期、訂單金額等。

表頭 Header

表頭，是表格中列的名稱

可以看作是資料的結構化資訊，幫助使用者理解和運算元據

表單 Form

嚴格來說不直接屬於資料庫結構的一部分，但表單是一種常用的介面元素，用於收集並展示資料。

表單可以用於輸入、修改或查詢資料庫中的資料。

表單通常包含多個欄位，每個欄位對應資料庫表中的一個列。使用者透過填寫表單並提交，可以將資料填寫到資料庫中或更新現有資料。

2. SQL語句基礎概念

2.1 SQL語句規範

SQL語句不區分大小寫，為了提高程式碼的可讀性，建議採用統一的書寫規範。

關鍵字大寫：SELECT, FROM, WHERE, JOIN。

表名、列名小寫，用雙引號括起來以確保大小寫的敏感性。

字串與日期：字串與日期型別的資料應使用單引號' '括起來，單詞之間用英文半形空格隔開。

SQL語句可單行或多行書寫，預設以英文半形分號 ; 結尾，關鍵詞不能跨多行

單行註釋：--或#

多行註釋：

註釋內容

2.2 SQL語句型別

根據用途，SQL語句可以分為幾種主要型別：

1. 資料查詢語言 (DQL Data Query Language)
用於從一個或多個表中檢索資料。
主要命令是 SELECT。

2. 資料定義語言 (DDL Data Definition Language)
用於定義或改變資料庫結構。
常用命令包括：
CREATE：建立資料庫、表、檢視等。
ALTER：修改現有資料庫物件的結構。
DROP：刪除資料庫物件。
TRUNCATE：清除表中的所有資料但保留表結構。
RENAME：重新命名資料庫物件（某些資料庫系統可能使用不同的語法）。

3. 資料操縱語言 (DML Data Manipulation Language)
用於新增、更新和刪除資料庫中的資料。
常用命令包括：
INSERT：向表中插入新記錄。
UPDATE：更新表中的現有記錄。
DELETE：從表中刪除記錄。
MERGE：在某些資料庫中，用於基於條件更新或插入資料。

4. 事務控制語言 (TCL Transaction Control Language)
用於管理事務，確保資料的一致性和完整性。
常用命令包括：
COMMIT：提交當前事務，使更改永久化。
ROLLBACK：撤銷當前事務，恢復到事務開始前的狀態。
SAVEPOINT：設定儲存點，允許部分回滾事務。
SET TRANSACTION：設定事務的特性（如只讀、隔離級別等）。

5. 資料控制語言 (DCL Data Control Language)
用於控制資料庫訪問許可權。
常用命令包括：
GRANT：授予使用者特定許可權。
REVOKE：撤銷使用者的特定許可權。

6. 其他命令
還有一些其他的命令不屬於上述分類，例如：
EXPLAIN：顯示查詢計劃，幫助最佳化查詢效能。
SHOW：顯示資料庫資訊，如表結構、索引等。
USE：選擇當前工作的資料庫。
BEGIN 或 START TRANSACTION：開始一個新的事務。

2.3 常用命令

3. SQL語句操作庫、表、資料

3.1 運算元據庫(資料夾級別)---對庫的增刪改查

[1]建立資料庫

方法一：create database 資料庫名稱;

方法二：create database if not exists 資料庫名稱;　　　　

　　　　--資料庫不存在則建立，存在則忽略

方法三：create database if not exists 資料庫名稱 charset 字元編碼名稱;

　　　　--latin1：西歐語言字符集

　　　　--utf8mb4（當前中文預設）：完整的utf-8編碼，支援所有Unicode字元，包括表情符號，一個字元最多佔4個位元組

　　　 --gbk：簡體中文字符集

[2]檢視資料庫

系統自帶的資料庫：

檢視所有資料庫：

show databases;

模糊查詢，檢視名稱中包含指定字元的資料庫：

show databases like "%obj%";

檢視資料庫的建庫SQL語句：

show create database 資料庫名稱;

示例：

show create database test01;

show create database test02;

show create database test03;

[3]修改資料庫

修改資料庫預設的編碼集：

alter database 資料庫名稱 charset "utf8mb4";

舉例：將test03的字符集由gbk改為utf8mb4

[4]刪除資料庫

以下MySQL自帶的資料庫不能刪除

drop database [if exists] 資料庫名稱;　　　　--中括號裡的內容表示可選

3.2 運算元據庫中的表(檔案級別)---對錶的增刪改查

[1]操作表之前的操作：進入到指定庫、檢視所在的庫

use 庫名稱;　　　　--進入到指定庫

注意：進入到指定資料庫後無法退回到之前剛登入的狀態，但可以透過use進行切換

select database();　　　　--檢視所在的庫

[2] 建立表

語法：

create table 表名(
       列名1 資料型別[(最多字元個數)]  [約束條件],
       列名2 資料型別[(最多字元個數)]  [約束條件],
       ...
       列名n 資料型別[(最多字元個數)]  [約束條件]
);

列名和資料型別必填，中括號裡的內容選填。

列名不可重複。

常見資料型別：

int：整數　　括號內的引數預設為11，是顯示寬度，不影響儲存大小，不推薦使用

　　　　有符號 INT
　　　　位數: 32 位　　4個位元組
　　　　最小值: -2,147,483,648 (-2^31)
　　　　最大值: 2,147,483,647 (2^31 - 1)
　　　　無符號 INT
　　　　位數: 32 位　　4個位元組
　　　　最小值: 0
　　　　最大值: 4,294,967,295 (2^32 - 1)

varchar(n)：變長字串，n是字串的最大長度　　1~255，預設寬度為1，只能存1個字元
text：長文字
date：日期
datetime：日期和時間
decimal(m, n)：小數，m是總位數，n是小數點後的位數

常見約束條件：

not null：不能為空值
primary key：主鍵，列（或列組合的）唯一標識，每個表可以有一個主鍵
unique：列的值必須唯一
default value：列的預設值
auto_increment：自增，通常用於主鍵，自動生成唯一的數字；一般與主鍵配合使用
check：檢查約束，確保列中的值滿足特定條件；MySQL 8.0.16之前的版本要使用觸發器才能實現check的約束功能
foreign key：外來鍵，用於在兩個表之間建立連線，確保參照完整性

主鍵寫法一：

create table product1(
      id int auto_increment primary key,
      name varchar(10) not null,
      unique(name),
      price decimal(10, 2),
      stock int default 20
);

主鍵寫法二：

create table product2( 
       id int auto_increment,
       name varchar(10) not null,
       unique(name),
       price decimal(10, 2),
       stock int default 20,
       primary key(id)
);