MySQL學習筆記(二）增删改

寫在前面：本篇為作者自學總結，學習内容為課堂所學和網絡學習筆記彙總，對于内容引用部分在文中和文末注明。

文章僅供參考，如需深入了解，請查閱MySQL參考手冊。附上下載下傳連結：

連結：https://pan.baidu.com/s/1FpONA6oyI6O73m_ebN7J_Q 

提取碼：7pr8 

複制這段内容後打開百度網盤手機App，操作更友善哦

目錄

增（INSERT)、删(DELETE)、改(UPDATE)的操作

1. 對資料庫(database)的操作

1.1 建立資料庫

1.2 删除資料庫

 1.3 檢視資料庫

2. 對表(table)和表中資料(data)的操作

2.1 建立表

2.2 添加索引

2.3 向actor表中增加資料

2.4 修改表名

2.5 字段管理

2.6 修改資料

2.7 删除資料 

2.8 删除表

參考資料

在之前的MySQL學習筆記(一)中已經總結了MySQL的基礎概念，這裡将介紹MySQL的語句。在MySQL學習筆記(二)中僅記錄增删改的指令，查詢語句将在MySQL學習筆記(三）中更新。

MySQL文法:

增（INSERT)、删(DELETE)、改(UPDATE)的操作

對資料庫和表來說，MySQL支援建立（定義），複制，删除，字段的修改，查詢。

對資料庫中的資料來說，MySQL同樣支援增删改查。

一般增删改的知識了解即可，大部分實際工作中對資料庫查找的要求更高。

1. 對資料庫(database)的操作

1.1 建立資料庫

CREATE DATABASE test_db；
           

建立一個名為test_db的資料庫。

1.2 删除資料庫

DROP DATABASE test_db；
           

将名為test_db的資料庫删除，資料庫中的東西都被删除。

 1.3 檢視資料庫

SHOW DATABASES;
           

可以檢視資料庫中有什麼表。注意DATABASES為複數。

2. 對表(table)和表中資料(data)的操作

以下例子根據現實邏輯進行，可以跟着自己寫一遍練習，加深了解。 

2.1 建立表

CREATE TABLE actor(actor_id SMALLINT(5), first_name VARCHAR(45), 
                   last_name VARCHAR(45), last_update TIMESTAMP);
           

建立名為actor的表，其中有四個字段名字分别為actor_id, first_name, last_name, last_update。四個字段可以了解為表格中的列名，他們的資料存在對應的列資料中。它們分為為SMALLINT，VARCHAR， VARCHAR， TIMESTAMP類型。VARCHAR(45)中的45是對字元最大長度的限定。

基于上面所學，我們可以對字段加限定詞，以滿足日常需求：

CREATE TABLE actor(
    actor_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT, 
		first_name VARCHAR(45) NOT NULL, 
		last_name VARCHAR(45) NOT NULL, 
		last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
       CURRENT_TIMESTAMP);
           

這裡還限定了actor_id為無符号的SMALLINT，即範圍為0 --- 65535，它還可以自增。

四個字段都有NOT NULL的限定，且last_update有預設值。

在資料庫中actor表的視圖為：

2.2 添加索引

1. 主鍵

主鍵可以幫助我們快速查詢表中的記錄，要求數值互不相同，即是唯一的，不能為NULL值。每個表隻有一個主鍵。

1）添加主鍵

繼續修改我們上面actor表，定義actor_id為主鍵：

CREATE TABLE actor(
    actor_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT, 
		first_name VARCHAR(45) NOT NULL, 
		last_name VARCHAR(45) NOT NULL, 
		last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
        CURRENT_TIMESTAMP，
        PRIMARY KEY (actor_id));
           

下面這種方法和上面的方法效果一樣，選擇一種即可：

CREATE TABLE actor(
    actor_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
		first_name VARCHAR(45) NOT NULL, 
		last_name VARCHAR(45) NOT NULL, 
		last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
        CURRENT_TIMESTAMP);
           

2）删除主鍵 

兩種方法等同：

法1：

ALTER TABLE actor MODIFY actor_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT;
           

法2：

ALTER TABLE actor DROP PRIMARY KEY;
           

 2. 唯一索引和普通索引

建立唯一索引的目的不是為了提高通路速度，而隻是為了避免資料出現重複。唯一索引可以有多個但索引列的值必須唯一，索引列的值允許有空值。如果能确定某個資料列将隻包含彼此各不相同的值，在為這個資料列建立索引的時候就應該使用關鍵字UNIQUE。

普通索引和唯一索引在查詢能力上沒啥差别，主要考慮對更新性能的影響，要盡量選擇普通索引。

/*唯一索引*/

-- 添加, u_id為是唯一索引的名字，自己定義
ALTER TABLE actor ADD UNIQUE u_id(acotor_id);
-- 删除
ALTER TABLE actor DROP INDEX u_id;

/*普通索引*/
-- 添加, i_id為是唯一索引的名字，自己定義
ALTER TABLE actor ADD INDEX i_id(acotor_id);
-- 删除
ALTER TABLE actor DROP INDEX i_id;
           

2.3 向actor表中增加資料

INSERT INTO actor(`actor_id`, `first_name`, `last_name`, `last_update`) 
VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33');
           

如果列名順序改變，對應的資料的順序也應改變。如果資料一一對應，也可以簡略為

INSERT INTO actor VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33');
           

不再重複輸入字段名，這樣可以節省時間，更簡潔友善。以上兩行的意思相同。

因為我們設定了actor_id自增，是以不為它輸入值的時候，它也可以自動增加自己的數值，每次增加1。同時last_update在沒有輸入資料的時候可以自動更新目前時間，在本表格的情景下不需要每次手動輸入資料，是以INSERT語句可以改寫為：

INSERT INTO actor(first_name,last_name) VALUES ('NICK', 'WAHLBERG');
           

此條first_name自動填充為2，last_update自動填充為目前時間。

重複類似操作多次，輸入不同的資料，可以得到以下表内容(使用SELECT * FROM actor;進行查詢）視圖：

每一次輸入的INSERT語句對應表格中的一行内容。

2.4 修改表名

RENAME TABLE actor TO myActor;
           

将名為actor的表名字改成myActor, 表屬性沒有變。

2.5 字段管理

使用ALTER關鍵字。

1）添加字段

ALTER TABLE actor ADD age TINYINT UNSIGNED NOT NULL;
ALTER TABLE actor ADD address VARCHAR(50) NOT NULL AFTER last_name;
ALTER TABLE actor ADD telephone VARCHAR(11) NOT NULL FIRST;
SELECT * FROM actor;
           

使用ADD關鍵字，如果沒有指定，則添加到表格最後一列。使用AFTER添加特定列之後，使用FIRST添加表的第一列。添加完的字段後表的視圖如下：

因為有限定NOT NULL,對于插入的數字類型用0填充，字元類型用‘’填充。 

2）修改字段

-- 修改字段定義
ALTER TABLE actor MODIFY telephone VARCHAR(20) NOT NULL;

-- 重命名且修改字段定義，不更改定義的時候也可以隻用來重命名
ALTER TABLE actor CHANGE address new_addr VARCHAR(35) NOT NULL;

-- 修改字段定義
ALTER TABLE actor CHANGE age age INT NOT NULL;
SELECT * FROM actor;
           

使用MODIFY或CHANGE關鍵字。二者都可以修改表的定義，但是CHANGE需要寫兩次字段名，不是很友善。CHANGE的優點是可以對字段名稱重命名，第一個字段名是原字段名，第二個字段名是修改完的名字，而MODIFY沒有這個功能。修改完的字段後表的視圖如下：

3）删除字段

ALTER TABLE actor DROP telephone;
ALTER TABLE actor DROP new_addr;
ALTER TABLE actor DROP age;
SELECT * FROM actor;
           

使用DROP關鍵字，直接删除所選字段以及其全部資料。删除完的字段後表的視圖如下： 

4）檢視字段 

DESC actor;
           

使用DESC關鍵字，可得以下視圖：

2.6 修改資料

修改前的第一行資料： 

通過以下指令行進行修改，将actor_id為1的上次更新時間更新成目前時間： 

UPDATE actor SET last_update = CURRENT_TIMESTAMP WHERE actor_id = 1;
           

 修改完之後的第一行資料：

可以用"，"連接配接，同時更改兩個及以上的字段：

UPDATE actor SET last_update = CURRENT_TIMESTAMP, last_name = 'new last name' WHERE actor_id = 3000;
           

更改完的第一行如圖所示：

2.7 删除資料 

DELETE FROM actor WHERE actor_id = 1;
           

第一行資料被删除。 

2.8 删除表

DROP TABLE actor;
           

删除myActor表。

參考資料：

課堂資料：

X. Zhu(2019). Introduction to Database [PowerPoint slides]. Available: https://ice.xjtlu.edu.cn/course/view.php?id=293

Mysql資料庫設計與操作 (access time: 2021/6/23)

網絡資料：

淺析MySQL中change與modify的差別 (access time: 2021/6/29)

MySQL删除表操作（delete、truncate、drop的差別） (access time: 2021/6/29)

MySQL 普通索引和唯一索引該如何選擇？ (access time: 2021/6/29)

MYSQL 唯一索引UNIQUE使用方法詳解 (access time: 2021/6/29)