MySQL快速回顧：資料庫和表操作

前提要述：參考書籍《MySQL必知必會》
利用空閑時間快速回顧一些資料庫基礎，

4.1 連接

在最初安裝MySQL，可能會要求你輸入一個管理登錄（通常為root）和一個口令（密碼），

連接MySQL需要以下資訊：

• 主機名（計算機名）——如果連接到本地MySQL服務器，為localhost；
• 埠（如果使用默認埠3306之外的埠）；
• 一個合法的用戶名（默認為root）
• 用戶口令（密碼，如果需要的話），

cmd登錄前一篇有說，

會簡單介紹Navicat Premium 12，但是后面都是執行命令，所以在Navicat Premium 12的命令列視窗做或者在cmd做都可以，

現在說下：Navicat Premium 12，打開它，

3.2 創建資料庫

《MySQL必知必會》前面一開始就開講SELETE查詢等，而像新手可能就會不知道怎么創建資料庫，創建表；然后利用這些表去測驗所學的SELETE查詢等，

下面提供兩種方式創建資料庫：

1. 圖形界面創建資料庫

那么資料庫就創建完成，

2. 命令列創建資料庫

然后輸入：

CREATE DATABASE ts;

更加規范的寫法（推薦）：
格式：

CREATE DATABASE IF NOT EXISTS <資料庫名> DEFAULT CHARACTER SET='utf8';

比如：

CREATE DATABASE IF NOT EXISTS ts DEFAULT CHARACTER SET='utf8';

解釋：

• CREATE DATABASE ts：表示創建一個名為ts的資料庫；
• IF NOT EXISTS：翻譯過來就是如果不存在，防止當資料庫存在時會報錯；
• CREATE DATABASE IF NOT EXISTS ts：表示如果不存在一個名為ts的資料庫，則創建該資料庫；
• CHARACTER SET='utf8'：這個跟我們上面圖形界面設定的一樣，就是設定資料庫的字符集，
• ; ：分號表示陳述句結束，所以每一條陳述句都需要分號結尾，

注意：寫的時候注意中英文符號，比如 ''，

4.3 洗掉資料庫

格式：

drop database <資料庫名>;

4.4 選擇資料庫

可以看到我們剛剛是在my連接這里開啟命令界面，我們操作的使用并不是對所有資料庫進行操作，而是對單個資料庫進行操作，那么就需要輸入選擇資料庫的陳述句，

格式：

USE <資料庫名>;

選擇剛剛建立的ts資料庫：

USE ts;

命令列界面會回傳：

Database changed # 表示成功

4.5 了解資料庫和表

資料庫、表、用戶、列、權限等資訊被存盤在資料庫和表中（MySQL用MySQL來存盤這些資訊），不過，內部的表一般不直接訪問，可用MySQL的SHOW命令來顯示這些資訊（MySQL從內部表中提取這些資訊），

1. 查詢已經存在的資料庫(包括MySQL系統資料庫：mysql和information_schema)：

SHOW DATABASES;

輸出：

2. 獲取一個資料庫中的表的串列，前提是已經選擇資料庫，不然也不知道你要獲取哪個資料庫，假設獲取mysql資料庫的表，這里只是演示，一般別去動mysql資料庫，畢竟是mysql的系統資料庫，

USE mysql;
SHOW TABLES; # 顯示資料庫的全部表

輸出：

3. 顯示一個表中的列

USE mysql;
SHOW COLUMNS FROM 表名稱;

輸出：

MySQL支持用 DESCRIBE 作為 SHOW COLUMNS FROM 的一種快捷方式，

4.其他的SHOW陳述句：

• SHOW STATUS：用于顯示廣泛的服務器狀態資訊；
• SHOW CREATE DATABASE和SHOW CREATE TABLE：分別用來顯示創建特定資料庫或表的MySQL陳述句；
• SHOW GRANTS：用來顯示授予用戶（所有用戶或特定用戶）的安全權限；
• SHOW ERRORS和SHOW WARNINGS：用來顯示服務器錯誤或警告訊息，

4.6 MySQL資料型別

轉自菜鳥教程

MySQL中定義資料欄位的型別對你資料庫的優化是非常重要的，

MySQL支持多種型別，大致可以分為三類：數值、日期/時間和字串(字符)型別，

4.6.1 數值型別

MySQL支持所有標準SQL數值資料型別，這些型別包括嚴格數值資料型別(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值資料型別(FLOAT、REAL和DOUBLE PRECISION)，

關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞，

BIT資料型別保存位欄位值，并且支持MyISAM、MEMORY、InnoDB和BDB表，

作為SQL標準的擴展，MySQL也支持整數型別TINYINT、MEDIUMINT和BIGINT，下面的表顯示了需要的每個整數型別的存盤和范圍，

4.6.2 日期和時間型別

表示時間值的日期和時間型別為DATETIME、DATE、TIMESTAMP、TIME和YEAR，

每個時間型別有一個有效值范圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值，

TIMESTAMP型別有專有的自動更新特性，將在后面描述，

4.6.3 字串型別

字串型別指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET，該節描述了這些型別如何作業以及如何在查詢中使用這些型別，

CHAR 和 VARCHAR 型別類似，但它們保存和檢索的方式不同，它們的最大長度和是否尾部空格被保留等方面也不同，在存盤或檢索程序中不進行大小寫轉換，

BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR，不同的是它們包含二進制字串而不要非二進制字串，也就是說，它們包含位元組字串而不是字符字串，這說明它們沒有字符集，并且排序和比較基于列值位元組的數值值，

BLOB 是一個二進制大物件，可以容納可變數量的資料，有 4 種 BLOB 型別：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，它們區別在于可容納存盤范圍不同，

有 4 種 TEXT 型別：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT，對應的這 4 種 BLOB 型別，可存盤的最大長度不同，可根據實際情況選擇，

轉自菜鳥教程評論區：

關于 char、varchar 與 text 平時沒有太在意，一般來說，可能現在大家都是用 varchar，但是當要存盤的內容比較大時，究竟是選擇 varchar 還是 text 呢？

這三種型別比較：

1. char: char 不用多說了，它是定長格式的，但是長度范圍是 0~255， 當你想要儲存一個長度不足 255 的字符時，Mysql 會用空格來填充剩下的字符，因此在讀取資料時，char 型別的資料要進行處理，把后面的空格去除， 這就是一個坑，
2. varchar: 關于 varchar，有的說最大長度是 255，也有的說是 65535，查閱很多資料后發現是這樣的：++varchar 型別在 5.0.3 以下的版本中的最大長度限制為 255++，而++在 5.0.3 及以上的版本中，varchar 資料型別的長度支持到了 65535++，也就是說可以存放 65532 個位元組（注意是位元組而不是字符！！！）的資料（起始位和結束位占去了3個位元組），也就是說，在 5.0.3 以下版本中需要使用固定的 TEXT 或 BLOB 格式存放的資料可以在高版本中使用可變長的 varchar 來存放，這樣就能有效的減少資料庫檔案的大小，
3. text: 與 char 和 varchar 不同的是，text 不可以有默認值，其最大長度是 2 的 16 次方-1

總結起來，有幾點：

• 經常變化的欄位用 varchar
• 知道固定長度的用 char（比如性別）
• 超過 255 字符的只能用 varchar 或者 text
• 能用 varchar 的地方不用 text
• 理論上在表中的列全都可以使用varchar來定義列的資料型別，但是千萬不要這樣，最好根據列的實際情況選擇對應的資料型別，并且可以優化資料庫，比如：性別-》char，學生學號位一般用整型來存盤，

下面轉載
資料型別的選擇特別重要：

1. 資料型別會影響存盤空間的開銷，
2. 資料型別會影響資料庫查詢性能，

所以當一個資料型別可以有多種選擇多種型別的時候，應該優先考慮數字型別，其次是日期或二進制型別，最后應該是字符型別，對于相同級別的資料型別，應該優先選擇占用空間小的資料型別，
原理：在對資料進行比較（查詢條件，JOIN條件及排序）操作時：同樣的資料，字符處理往往比數字處理慢，而且在資料庫中，資料的處理是以頁為單位，列的長度越小，資料型別占用的空間越小，利于性能的提升，

更多的優化等我看《高性能MySQL》再總結，

4.7 創建資料庫表

創建表有兩種方式：

• 使用具有互動式創建和管理表的工具（Navicat Premium 12 圖形界面）；
• 表也可以直接用MySQL陳述句操縱，

利用CREATE TABLE創建表需要以下資訊：

• 新表的名稱，再關鍵字CREATE TABLE之后給出；
• 表列的名字和定義，用逗號隔開

創建表的語法：

CREATE TABLE 表名(
    列名1 列的定義比如資料型別，是否為空，是否為主鍵等,
    列名2 列的定義，
    ...
);

簡單創建單表的例子：

CREATE TABLE student(
    stu_id INT AUTO_INCREMENT,
    stu_name varchar(10) NOT NULL,
    stu_sex char(1) NULL,
    PRIMARY KEY(stu_id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;;

解釋：

• 如果你不想欄位為 NULL 可以設定欄位的屬性為 NOT NULL， 在操作資料庫時如果輸入該欄位的資料為NULL ，就會報錯，
• AUTO_INCREMENT定義列為自增的屬性，一般用于主鍵，數值會自動加1，
• PRIMARY KEY關鍵字用于定義列為主鍵，表中的每個行必須具有唯一的主鍵值，也就是一個表中至少有一個主鍵， 可以使用多列來定義主鍵，列間以逗號分隔，該關鍵字也可以直接寫在stu_id的定義中，
• ENGINE 設定存盤引擎（這個先別管），CHARSET 設定編碼，

額外：

NULL和NOT NULL：NULL值就是沒有值或缺值，允許NULL值的列也允許在插入行時不給出該列的值，不允許NULL值的列不接受該列沒有值的行，

理解NULL：不要把NULL值與空串相混淆，NULL值是沒有值，它不是空串，如果指定''（兩個單引號，其間沒有字符），這在NOT NULL列中是允許的，空串是一個有效的值，它不是無值，NULL值用關鍵字NULL而不是空串指定，

主鍵中只能使用不允許NULL值得列，允許NULL值得列不能作為唯一標識，

AUTO_INCREMENT：被該關鍵字指定的列，表示每次插入資料時會對該列自動增長，通常使用它去定義主鍵，比如在增加一個新記錄時，需要一個ID值，這ID可以任意，只要它是唯一的即可，那么最簡單的就是使用下一個ID值，即當前表中最后一條記錄的ID為1，那么新插入資料時，新插入的資料的ID就為2，所以就可以使用AUTO_INCREMENT自動增加ID值，我們在插入的時候也不用去理會ID是多少，
如何在表中獲得AUTO_INCREMENT的值，輸入：
SELECT last_insert_id();
就會回傳最后一個AUTO_INCREMENT值，
也可以自己修改AUTO_INCREMENT的值，只要是唯一的，

DEFAULT：指定列的默認值，注意默認值不允許為函式，《MySQL必知必會》推薦使用默認值而不是使用NULL值，否則使用NOT NULL也可以，

評論區說的注意事項

4.8 引擎型別

上面創建表中最后有一個 ENGINE=InnoDB，下面來解釋一下：

MySQL與其他DBMS一樣有一個具體管理和處理資料的內部引擎，比如當你使用CREATE TABLE陳述句時，該引擎具體創建表，而在你使用SELECE查詢陳述句時或進行其他資料庫處理時，該引擎在內部處理你的請求，所以它是對我們透明化的，

MySQL與其他DBMS不一樣的是，MySQL具有多種引擎，這些引擎都隱藏在MySQL服務器內，全都能執行CREATE TABLE和SELECE等命令，

為什么要這么多引擎，因為它們具有各自不同的功能和特效，為不同的任務選擇正確的引擎能夠獲得良好的功能和靈活性，

但是在CREATE TABLE時也可以忽略它，會自動使用默認引擎（很可能是MyISAM），多數SQL陳述句都會默認使用它，

以下是幾個必須知道的引擎：

• InnoBD是一個可靠的事務處理引擎，它步支持全文本搜索；
• MEMORY在功能等同于MyISAM，但由于資料存盤在記憶體（不是磁盤）中，速度很快（特別適合于臨時表），
• MyISAM是一個性能極高的引擎，它支持全文本搜索，但不支持事務處理，

引擎型別可以混用，但是外鍵不能跨引擎，混合引擎有一個很大缺陷，外鍵（用于強制實施參考完整性）不能跨引擎，即使用一個引擎的表不能參考具有使用不同引擎的表的外鍵，

4.9 洗掉表

格式：

DROP TABLE <表名> ;

4.10 更新表

語法：

ALTER TABLE <表名>
操作

比如：
給表添加一個列：

ALTER TABLE student
 ADD stu_phone CHAR(11);

給表洗掉一個列：

ALTER TABLE student
 DROP stu_phone;

外鍵可以在創建表定義，也可以使用ALTER TABLE來定義，下面是定義外鍵的一種方式：

再創建一張班主任表

CREATE TABLE Headmaster(
    ma_id INT PRIMARY KEY AUTO_INCREMENT,
    ma_name VARCHAR(10) NOT NULL
    )ENGINE=InnoDB DEFAULT CHARSET='utf8';

先給學生表添加一個 班主任的列：

ALTER TABLE student ADD ma_id INT;

定義外鍵：與學生表相關聯，

ALTER TABLE student
 ADD CONSTRAINT fk_student_master
 FOREIGN KEY (ma_id) REFERENCES Headmaster(ma_id);

外鍵語法：在創建表直接加上下面這句，修改表得再加個ADD

CONSTRAINT <外鍵名，自定義>
 FOREIGN KEY (<當前表的列，比如學生表>) REFERENCES <要關聯的表，比如班主任表>(<要關聯的列>);

復雜得表結構更改一般需要手動洗掉程序，它涉及一下步驟：

• 用新的列布局創建一個新表；
• 使用INSERT SELECT陳述句（待了解）從舊表復雜資料到新表，如果有必要，可使用轉換函式和計算欄位；
• 校驗包含所需資料的新表；
• 重命名舊表（如果確定，可以洗掉它）；
• 用舊表原來的名字重命名新表；
• 根據需要，重新創建觸發器，存盤程序，索引和外鍵（除外鍵其他待了解），

小心使用ALTER TABLE ：應該在改動前做一個完整的備份（模式和資料的備份），資料庫表的更改不能撤銷，如果增加了不需要的列，可能不能洗掉它們，類似地，如果洗掉了不應該洗掉的列，可能會丟失該列中的所有資料，

4.11 重命名表

語法：

RENAME TABLE <原表名> TO <新表名>;

標籤：MySQL

上一篇：php操作mysql（資料庫常規操作）

下一篇：uniapp后臺api設計(微信user表)