1.4 資料庫和常用SQL語句（正文）——MySQL資料庫命令和SQL語句
前面我們已經講述了，登錄時，我們使用mysql –u root –p命令進行，此時如果設置了密碼，則需要輸入密碼。
輸入密碼後即進入MySQL的操作介面，此時，命令行窗體左側顯示「mysql>」表示此時可接受mysql命令。
- （1）列出全部資料庫命令
我們使用「show databases;」命令列出當前MySQL管理的全部資料庫。（注意：mysql命令以英文;為結束符）。
這裡可以看到，目前mysql管理的資料庫共有4個。
- （2）切換資料庫
我們可以在上述顯示的4個資料庫中進行切換，表示當前所使用的是哪個資料庫。我們使用「use」+資料庫名進行資料庫切換。如
- （3）列出當前資料庫下全部表
我們使用「show tables;」命令，列出當前資料庫下全部表名稱，如
- （4）創建一個資料庫。
使用「create database」+資料庫名，來創建一個新的資料庫。如，按本節開始的例子，我們創建一個名為「db_edu_sys」的資料庫，並切換至該資料庫。
從提示資訊上我們看到，我們成功創建了名為「db_edu_sys」，並將當前資料庫切換至該資料庫。
- （5）向資料庫中添加表。
我們使用SQL語句「CREATE TABLE」命令進行添加表的操作。
例如，上述例子中，我們建立教師表（Teacher），可以寫成如下SQL語句
CREATE TABLE teacher(ID int auto_increment primary key,t_name varchar(30),t_title varchar(50));
這時，我們再查看資料庫中的表，可以看到新建立的教師表(teacher)。
- （6）導入sql文本
有時，對多條SQL語句進行操作時，我們一條一條的寫入不是很方便，尤其是在有事務操作和結構化語句時，就更加困難，因此，我們可以將SQL語句事先寫好，保存在文本中，然後一次性導入到資料庫中，我們使用如下命令對sql文本進行導入。
「source 」+文本文件
如source c:/createtable.sql
（如果不寫路徑名，則默認為當前路徑，即執行「mysql –u root –p」時前面顯示的路徑。）
我們將學生表、課程表、選課表、教學表寫到一個sql文本中，一次導入。這個文本我們命名為createtable.sql，其內容如下：
CREATE TABLE student(ID int auto_increment primary key,s_name varchar(30),s_class varchar(50)); CREATE TABLE course(ID int auto_increment primary key,c_name varchar(30),c_credit varchar(50)); CREATE TABLE teaching(ID int auto_increment primary key,t_ID int,c_ID int); CREATE TABLE selection(ID int auto_increment primary key,s_ID int,c_ID int);
我們運行導入，結果如下：
- （7）查看錶結構
我們可以通過「DESCRIBE」+表名，來查看某一張表的表結構，如執行「DESCRIBE teacher」結果如下
- （8）添加數據
向表內添加數據要使用SQL語句「INSERT INTO」，如，我們要向teacher表中添加一條教師的資訊，可以寫成如下形式：
INSERT INTO teacher(t_name,t_title) VALUES('張老師','副教授');
執行後，如下
- （9）查詢數據
我們通過SQL語句「SELECT … FROM … WHERE …」進行查詢，其中「SELECT」後面是要查詢的欄位名稱，可以多個，也可以用「*」，表示查詢全部欄位，「FROM」後面是要查詢的表名，「WHERE」後面是查詢條件，支援「AND」和「OR」的組合。
查詢條件中，運算符號可以為「=」（等於），「>」（大於），「<」（小於），「LIKE」（類似於），「<>」（不等於）等，請按照實際情況進行書寫。比如，我們要查詢出所有姓名不等於空的教師的資訊，可以使用如下SQL語句：
SELECT * FROM teacher WHERE t_name <> '';
- （10）刪除數據
我們使用SQL語句的「DELETE FROM…WHERE」進行數據刪除操作。例如，刪除ID為1且姓名為張的老師
DELETE FROM teacher WHERE t_name LIKE '張%' AND ID = 1;
- （11）修改數據
我們使用SQL語句「UPDATE [tablename] SET … WHERE …」來進行修改數據操作，例如，將ID為2的老師姓名改為「王志超」可以寫作
UPDATE teacher SET t_name='王志超' WHERE ID=2;
- （12）導出數據
我們使用「SELECT … FROM [tablename] INTO OUTFILE …」進行數據導出，例如，使用如下語句進行數據導出操作：
SELECT * FROM teacher INTO OUTFILE "E:/mysql-8.0.23/teacher.txt";
執行結果如下
得到的文件teacher.txt內容如下：
- （13）資料庫備份與恢復
使用mysqldump對MySQL進行備份。（注意：mysqldump是一個可執行程式，請直接在命令行下運行，不要進入mysql控制台後再運行）
例如，使用下面的命令對指定的資料庫進行備份。
mysqldump -uroot -p123456 db_edu_sys > "E:/mysql-8.0.23/db_edu_sys.sql"
將「db_edu_sys」資料庫備份至「E:/mysql-8.0.23/db_edu_sys.sql」文件中，執行後如下圖所示
這裡會出現一個警告：在命令行上使用密碼是不安全的。但是，命令已經執行完畢了，我們到對應的文件夾下可以看到備份出來的文件。部分內容如下：
即表明已經備份成功。
恢復資料庫時，導入備份文件（參看本節第（6）點）即可，我們進入mysql命令行狀態，輸入下列語句即可恢複數據：
USE db_edu_sys SET SQL_LOG_BIN=0; source "E:/mysql-8.0.23/db_edu_sys.sql";
- （14）事務
我們來討論下資料庫中一個比較重要的概念——事務，這在數據操作時會經常遇到。
我們將上述5個數據表內容進行了填充，將教師表、學生表、課程表、教學表、選課表內容進一步充實，每個表中都存在了數據。
考慮這樣一種情況，有一門課程（例如：課程ID：1，課程名稱：高等數學），已被教師（教師ID：2，教師姓名：王志超）領取了教學任務，同時，該課程被學生（學生ID：1，學生姓名：李曉軍）選課。由於一些原因，我們準備刪除這門課程，那麼，我們怎麼刪除才能保證數據的一致性呢？
如果要刪除這門課程，我們首先要刪除教師教學任務，還要刪除學生的選課情況，最後才能刪除這門課程，同時，我們還要保證上述過程必須同時完成，不能只刪除了課程，而教學任務和選課情況沒有刪除，這樣就會在教學表和選課表中留下不一致的數據（無法與課程表關聯）。
我們將必須一起完成的操作成為一個原子操作，我們使用事務來完成這個操作。即，當整個過程均無誤的完成時，我們對事務過程整體進行提交，確保事務操作全部完成；而當事務操作中部分操作出現錯誤時，我們可以通過「回滾」確保事務全部都沒有提交，返回沒有執行該事務時的狀態，從而確保數據的一致性。
例如，上述操作我們可以通過以下事務程式碼進行
delimiter $ CREATE PROCEDURE deleteCourse(IN cid int) -- 聲明刪除課程的存儲過程（函數）參數為課程ID BEGIN DECLARE i_error integer; -- 定義一個變數，用於接收出錯資訊 DECLARE CONTINUE handler FOR SQLEXCEPTION SET i_error = 1; -- 一旦出錯則將變數設置為1 START TRANSACTION ; -- 發起事務 DELETE FROM teaching WHERE c_ID=cid; -- 刪除教學表中有關該課程數據 DELETE FROM selection WHERE c_ID=cid; -- 刪除選課表中有關該課程數據 DELETE FROM course WHERE id=cid; -- 刪除課程表中的該課程 IF i_error = 1 THEN -- 判斷是否出錯 ROLLBACK; -- 回滾事務 ELSE COMMIT; -- 提交事務 END IF; END $
定義了上述存儲過程後，在命令行下調用
Call deleteCourse(1);
即可執行對應的事務，按照上述分析結論，同時刪除3個表對應的課程表編號為1的全部數據。
- （15）複雜SQL查詢
本節的最後，我們介紹複雜一些的SQL查詢——多表聯合查詢。
在實際運用查詢時，單獨對一個表的查詢往往不能滿足查詢的需求，這時就需要對多表進行聯合查詢。例如，我們想查詢「數值分析」這門課的教師姓名是什麼，這就涉及到從「課程表」、「教學表」和「教師表」3個表中聯合查詢出結果，我們使用「AND」關鍵字進行查詢。
SELECT a.t_name,c.c_name FROM teacher a,teaching b,course c WHERE a.ID = b.t_ID AND b.c_ID = c.ID AND c.c_name='數值分析';
再比如，我們想要查詢哪些課程都由哪個教師教學，以便學生進行選課，我們可以使用左連接的方式進行查詢。
（「左連接」即表示，只有左側表中有數據，無論右側表中是否有數據，都可查詢出結果，右側無數據的表使用「NULL」表示。本例中，由於不是所有的課程都由老師領取了教學任務，因此，將「課程表」放在左側連接教學任務表，沒有教師領取任務的課程，教師姓名一欄為「NULL」）,SQL查詢語句如下：
SELECT a.ID AS 課程編號,a.c_name AS 課程,b.t_name AS 教師 FROM course a LEFT JOIN (SELECT c.t_name,d.c_ID FROM teacher c,teaching d WHERE c.ID=d.t_ID) b ON a.ID = b.c_ID;
查詢結果為：
本節小結
這一節，我們簡單講述了MySQL資料庫的操作和一些基本的SQL語句，因為在後面的章節中，本節的內容會大量使用，請讀者熟練掌握。有關SQL語法和相關操作，尤其是多表聯合查詢、存儲過程和事務處理等，內容相對較難，有興趣的讀者可以參考有關資料庫的書籍，這裡僅為入門級的描述。本節中的資料庫和表的設計還很簡單，只為了說明問題並便於理解，由於本書後續環節還將使用這裡的資料庫和表，到時我們會對它們進行優化處理。有關本節所涉及的有關程式碼、表結構，成書過程中我們會逐漸整理並掛在書後附件對應的網站上，請讀者自行下載學習。
