資料庫基礎

MySQL系列
約 19 分鐘閱讀

什麼是資料庫？

一般來說，存儲資料以文件存儲就可以了，為什麼還需要資料庫？

文件的缺點：

  • 安全性問題
  • 對於查詢資料與管理較不方便
  • 如果有海量資料，只用文件存儲也不方便
  • 不利於程序當中管控

而資料庫存儲的介質 => 硬盤、記憶體

資料庫中，又存在資料庫服務的客戶端(mysql)與資料庫服務的服務器端(mysqld)。
mysql的本質是基於C(mysql)S(mysqld)模式的網路服務 => 所以可以理解為，mysql是一套給我們提供存取服務的網路程序。
我們說的「資料庫」，一般是指在硬盤或記憶體中以某種特定結構組織的資料。
假設我們要進行資料查詢，其應該是這樣運行的。
graph1.png

存儲引擎

存儲引擎：資料庫管理系統如何存儲資料、如何為存儲的資料建立索引和如何更新、查詢資料等技術的實現方法(常用的MYISAM、InnoDB)。

SQL的分類

  • DDL【data definition language】資料定義語言，用來維護存儲資料的結構
    代表指令: create, drop, alter
  • DML【data manipulation language】 資料操作語言，用來對資料進行操作
    代表指令： insert，delete，update
  • DML中又單獨分了一個DQL，資料查詢語言
    代表指令： select
  • DCL【Data Control Language】 資料控制語言，主要負責權限管理和事務
    代表指令： grant，revoke，commit

資料庫操作與表操作

  1. 創建資料庫 --- 其本質是在/var/lib/mysql目錄底下創建一個目錄

    CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,
create_specification] ...]
# ---------------------------------------------------------------
create_specification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

    創建資料庫的時候會有兩個編碼集

    • 資料庫編碼集：資料庫未來存儲資料時所用的編碼

    • 資料庫校驗集：資料庫進行字段比較時使用的編碼 => 讀取資料庫中資料時所用的編碼
      資料庫不管有沒有要對資料進行操作，編碼集與校驗集都應保持相同
      查看系統默認字符集與校驗規則

      show variables like 'character_set_database';
show variables like 'collation_database';

      校驗規則對資料庫的影響
      graph2.png

  2. 刪除資料庫 <---> 刪除目錄

    drop database db_name;

  3. 修改資料庫

    ALTER DATABASE db_name
[alter_spacification [,alter_spacification]...]

alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

  4. 資料庫備份與還原

    • 備份

      # mysqldump -P3306 -u root -p 密碼 -B 資料庫名 > 資料庫備份存儲的文件路徑
# mysqldump -P3306 -u root -p123456 -B mytest > D:/mytest.sql

# 如果備份的不是整個資料庫，而是其中的一張表，怎麼做？
# mysqldump -u root -p 資料庫名 表名1 表名2 > D:/mytest.sql

# 同時備份多個資料庫
# mysqldump -u root -p -B 資料庫名1 資料庫名2 ... > 資料庫存放路徑

    • 還原

      mysql> source D:/mysql-5.7.22/mytest.sql;

表的操作

  1. 建表

     CREATE TABLE table_name (
  field1 datatype (comment),
  field2 datatype (comment),
  field3 datatype (comment)  # 最後一欄不用加逗號
) character set 字符集 collate 校驗規則 engine 存儲引擎; # 記得分號

  2. 查看表結構

    desc table_name;

    表結構.png

  3. 修改表

    ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]...);
ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column datatype]...);
ALTER TABLE tablename DROP (column);

    (1) 添加字段
    添加字段.png
    (2) 對字段修改
    修改字段.png
    (3) 刪除某個字段
    刪除字段.png
    (4) 修改表名
    修改表名.png
    (5) 修改字段名 --- 新字段需要完整定義
    修改字段名.png

  4. 刪除表

    DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name]

資料類型

數值類型

  1. bit類型

    bit[(M)]  # 位字段類型。M表示每個值的位數，範圍從1到64。如果M被忽略，默認為1
    • M的大小是否越界取決於插入的值轉成binary後的位數是否超出M
    • 默認情況下插入的值以bit顯示是使用16進制

  2. float & decimal

    float[(m, d)] [unsigned] # M指定顯示長度，d指定小數位數，佔用空間4個字節
decimal(m, d) [unsigned] # 定點數m指定長度，d表示小數點的位數
# decimal整數最大位數m為65。支持小數最大位數d是30。如果d被省略，默認為0.如果m被省略，默認是10
    • float的精度一般最多是7位，如果超出7位mysql可能會對其做一些優化 --- 如果希望精度高就使用decimal
      decimal.png

字符串類型

  1. char

    char(L) # 固定長度字符串，L是可以存儲的長度，單位為「字符」，最大長度值可以為255

  2. varchar

    varchar(L)# 可變長度字符串，L表示字符長度，最大長度65535個「字節」

    關於varchar(len),len到底是多大,這個len值,和表的編碼密切相關
    varchar長度可以指定為0到65535之間的值,但是有1~3個字節用於記錄數據大小,所以說有效字節數是65532
    當我們的表的編碼是utf8時,varchar(n)的參數n最大值是 65532/3=21844 (因為utf中,一個字符佔用3個字節)
    如果編碼是gbk,varchar(n)的參數n最大是 65532/2=32766 (因為gbk中,一個字符佔用2字節)

char vs. varchar
char vs. varchar.png
如何選擇定長或變長字符串？

  • 如果資料確定長度都一樣，就使用定長（char），比如：身份證，手機號，md5
  • 如果資料長度有變化,就使用變長(varchar), 比如：名字，地址，但要保證最長的能存的進去
  • 定長的磁盤空間比較浪費，但是效率高 ; 變長的磁盤空間比較節省，但是效率低
  • 定長的意義是 -- 直接開闢好對應的空間 ; 變長的意義是 -- 在不超過自定義範圍的情況下，用多少，開闢多少

日期與時間類型

date :日期 'yyyy-mm-dd' ，佔用 3 bytes
datetime 時間日期格式 'yyyy-mm-dd HH:ii:ss' 表示範圍從 1000 到 9999 ，佔用8 bytes
timestamp ：時間戳，從1970年開始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，佔用 4 bytes
  • timestamp 時間會隨著資料進行增刪查改操作而變動。

enum && set

語法：
enum：枚舉，“單選”類型；
enum('選項1','選項2','選項3',...);
該設定只是提供了若干個選項的值，最終一個單元格中，實際只存儲了其中一個值；而且出於效率考慮，這些值實際存儲的是“數字”，因為這些選項的每個選項值依次對應如下數字：1,2,3,....最多65535個；當我們添加枚舉值時，也可以添加對應的數字編號。
set：集合，“多選”類型；

set('選項值1','選項值2','選項值3', ...);

該設定只是提供了若干個選項的值，最終一個單元格中，設計可存儲了其中任意多個值；而且出於效率考慮，這些值實際存儲的是“數字”，因為這些選項的每個選項值依次對應如下數字：1,2,4,8,16,32，....最多64個。

  • 一個查找函數

    • find_in_set(sub,str_list) -- 如果 sub 在 str_list 中，則返回下標；如果不在，返回0；str_list 用逗號分隔的字符串

      mysql> select * from votes where find_in_set('登山', hobby);  # 返回所有有登山的搜尋結果

表的約束

空屬性與默認值

空屬性的值只有兩個: not null && null -- 實際當中儘量以not null為主 -> 因爲null無法參與運算。
而默認值是在sql語句中添加 default 默認值。
關於空屬性與默認值的關係

  • 假設我們有一欄位沒有設定默認值，而且空屬性設定not null

    1. 如果沒有插入值 --> 報錯，因爲沒有默認值
    2. 插入null -> 報錯，因爲設定為not null

  • 假設我們有一欄設定了默認值，且空屬性設定not null

    1. 如果沒有插入值 --> 使用默認值
    2. 插入null -> 報錯，因爲設定為not null

主鍵

主鍵(Primary key)其用來唯一的約束該字段裡面的資料，不能重復，不能為空，一張表中最多只能有一個。

  • 主鍵所在的行通常是整數類型
  • 當表創建後如果沒有設定主鍵可以追加
alter table 表名 add primary key(字段列表)

  • 刪除主鍵

    alter table 表名 drop primary key;

    primary_key1.png
    primary_key2.png
    primary_key3.png

  • 復合主鍵：在創建表的時候，在所有字段之後，使用primary key(主鍵字段列表)來創建主鍵，如果有多個字段作為主鍵，可以使用復合主鍵。
    在這裡要注意是以復合主鍵中所設定的主鍵整個整體為主鍵，而非個別。

    mysql> create table tt14(
-> id int unsigned,
-> course char(10) comment '課程代碼',
-> score tinyint unsigned default 60 comment '成績',
-> primary key(id, course) -- id和course為復合主鍵
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tt14 (id,course)values(1, '123');
Query OK, 1 row affected (0.02 sec)
mysql> insert into tt14 (id,course)values(1, '123');
ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY' -- 主鍵衝突

Auto-increment

auto_increment：當對應的字段，不給值，會自動的被系統觸發，系統會從當前字段中已經有的最大值+1操作，得到一個新的不同的值。通常和主鍵搭配使用，作為邏輯主鍵。
自增長的特點:

  • 任何一個字段要做自增長，前提是本身是一個索引（key一欄有值）
  • 自增長字段必須是整數
  • 一張表最多只能有一個自增長
    auto_incrment1.png
    auto_incrment2.png
    auto_incrment3.png

如果插入的時候修改了ID,則下一次會自增修改後的ID+1
舉例來說，插入了 (1000,'d') 然後再插入 ('e') 則e的ID會變成1001

唯一鍵

一張表中有往往有很多字段需要唯一性，數據不能重復，但是一張表中只能有一個主鍵：唯一鍵就可以解決表中有多個字段需要唯一性約束的問題。
唯一鍵的本質和主鍵差不多，唯一鍵允許為空，而且可以多個為空，空字段不做唯一性比較。
關於唯一鍵和主鍵的區別：
我們可以簡單理解成，主鍵更多的是標識唯一性的。而唯一鍵更多的是保證在業務上，不要和別的信息出現重復
其實也可以直接想成：今天資料是否需唯一且不可為空？ 是 -> 用主鍵 不是 -> 不設定或給定唯一鍵

外鍵

外鍵用於定義主表和從表之間的關係：外鍵約束主要定義在從表上，主表則必須是有主鍵約束或unique約束。
當定義外鍵後，要求外鍵列數據必須在主表的主鍵列存在或為null。
也就是說外鍵應符合兩個條件

  1. 從表與主表的關聯關係

  2. 產生外鍵約束

    # 定義外鍵
# 需先定義字段名再定義外鍵
foreign key (字段名) references 主表(行)

    比如班級與學生的關係
    學生隸屬於某個班級，班級屬於主表，學生屬於從表。班級有自己的ID，所以學生資料中有一個班級ID這個班級ID應與班級表中的ID對應。