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語法和相關操作,尤其是多表聯合查詢、存儲過程和事務處理等,內容相對較難,有興趣的讀者可以參考有關數據庫的書籍,這裡僅為入門級的描述。本節中的數據庫和表的設計還很簡單,只為了說明問題並便於理解,由於本書後續環節還將使用這裡的數據庫和表,到時我們會對它們進行優化處理。有關本節所涉及的有關代碼、表結構,成書過程中我們會逐漸整理並掛在書後附件對應的網站上,請讀者自行下載學習。