資料的操作

常說的CRUD，引用維基百科表格

中文	英文	意思	SQL	HTTP	資料分散服務	MongoDB
增加	Create	建立	INSERT	PUT / POST	WRITE	Insert
刪除	Delete	刪除	DELETE	DELETE	DISPOSE	Remove
查詢	Read	讀取	SELECT	GET	READ / TAKE	Find
改正	Update	更新	UPDATE	PUT / POST / PATCH	WRITE	Update

新增資料

使用INSERT INTO語句

插入一條完整的資料

完整即是此筆資料包含所有欄位的意思

格式:

insert into 表名
values (value1,value2,....);

values是正規語法，也可以省略s
內容(value1,value2,....)的順序必須嚴格依照表的欄位順序
字符與日期的資料類型必須包在''單引號中

插入指定欄位的資料

格式:

insert into 表名(欄位名1,欄位名2,...)
values (value1,value2,....);

內容(value1,value2,....)的順序必須嚴格依照表的欄位順序
上面有列出的，下面就要填入

插入多條資料

執行效率更高，推薦使用

INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

使用INSERT同時插入多條記錄時，MySQL會返回一些在執行單行插入時沒有的額外資訊，這些資訊的含義如下:
- Records:表明插入的記錄條數
- Duplicates:表明插入時被忽略的記錄，原因可能是這些記錄包含了重複的主鍵值
- Warnings:表明有問題的資料值，例如發生資料類型轉換

將查詢結果做為資料插入

格式:

INSERT INTO 目標表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

注意沒有values了
查詢的欄位與插入的欄位需對應，例如:
- 並且要特別注意資料的長度，把長的資料放到短的格子可能出錯

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM   employees
WHERE  job_id LIKE '%REP%';
/*
sales_reps中的id對應查來的employee_id
...
*/

更新資料

使用UPDATE語句更新資料，格式:

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

可以一次更新多條資料
如果需要回滾資料，需要保證在DML前，進行設置SET AUTOCOMMIT = FALSE;，防止自動提交
使用WHERE子句指定需要更新的資料

UPDATE employees
SET    department_id = 70
WHERE  employee_id = 113;

如果省略WHERE子句，則表中的所有資料都將被更新，慎用!!!

UPDATE 	copy_emp
SET    	department_id = 110;

更新中的資料完整性錯誤
- 有些欄位可能受到約束影響，比如department_id是源自departments表，其中沒有id為110的項目，所以這樣更新就會報錯

UPDATE employees
SET    department_id = 55
WHERE  department_id = 110;

刪除資料

實際開發中都是用邏輯刪除，就是建一個欄位表示"刪除"，要刪的那筆資料"刪除"=true。基本上不會用到這些指令的

使用DELETE語句從表中刪除資料，格式:

DELETE FROM 表名 [WHERE <condition>];

table_name指定要執行刪除操作的表
[WHERE <condition>]為可選參數，指定刪除條件
如果沒有WHERE子句，DELETE語句將刪除表中的所有記錄。
使用 WHERE 子句刪除指定的記錄

DELETE FROM departments
WHERE  department_name = 'Finance';

如果省略 WHERE 子句，則表中的全部資料將被刪除

DELETE FROM  copy_emp;

刪除中的資料完整性錯誤

DELETE FROM departments
WHERE       department_id = 60;

計算列

MySQL8新增的特性，感覺用的不多，了解就好

舉例:

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

這個表中的c欄位是透過(a+b)自動得來，而不需要手動插入或更新

綜合練習

create database if not exists test01_library;
use test01_library;
create table if not exists books
(
    id      int,
    name    varchar(50),
    authors varchar(100),
    price   float,
    pubdate year,
    note    varchar(100),
    num     int
);

insert into books
values (1, 'Tao of AAA', 'Dickes', 23, '1995', 'novel', 11);

insert into books (id, name, authors, price, pubdate, note, num)
values (2, 'EmmaT', 'Jane', 25, '1993', 'joke', 22);

select *
from books;

insert into books (id, name, authors, price, pubdate, note, num)
values (3, 'Story of Jane', 'Jane Tim', 40, 2001, 'novel', 0),
       (4, 'Lovey Day', 'George Byron', 20, 2005, 'novel', 30),
       (5, 'Old land', 'Honore Blade', 30, 2010, 'Law', 0),
       (6, 'The Battle', 'Upton Sara', 30, 1999, 'medicine', 40),
       (7, 'Rose Hood', 'Richard haggard', 28, 2008, 'cartoon', 28);

# 4、將小說類型(novel)的書的價格都增加5。
update books
set price=price + 5;

# 5、將名稱為EmmaT的書的價格改為40，並將說明改為drama。
update books
set price=40,
    note='drama'
where name like 'EmmaT';

# 6、刪除庫存為0的記錄。
delete
from books
where num = 0;

# 7、統計書名中包含a字母的書
select count(*)
from books
where name like '%a%';

# 8、統計書名中包含a字母的書的數量和庫存總量
select count(*), sum(num)
from books
where name like '%a';

# 9、找出"novel"類型的書，按照價格降冪排列
select *
from books
where note = 'novel'
order by price desc;

# 10、查詢圖書資訊，按照庫存量降冪排列，如果庫存量相同的按照note昇冪排列
select *
from books
order by num desc, note;

# 11、按照note分類統計書的數量
select count(*), note
from books
group by note;

# 12、按照note分類統計書的庫存量，顯示庫存量超過30本的
select count(*), note
from books
group by note
having sum(num) >= 30;

# 13、查詢所有圖書，每頁顯示5本，顯示第二頁
select *
from books
limit 5,5;

# 14、按照note分類統計書的庫存量，顯示庫存量最多的
select note, sum(num) sum_num
from books
group by note
order by sum_num desc
limit 1;

# 15、查詢書名達到10個字元的書，不包括裡面的空格
select *
from books
where length(replace(name, ' ', '')) >= 10;

# 16、查詢書名和類型，其中note值為novel顯示小說，law顯示法律，medicine顯示醫藥，cartoon顯示卡通，joke顯示笑話
select name,
       note,
       case note
           when 'novel' then '小說'
           when 'law' then '法律'
           end '類型'
from books;

# 17、查詢書名、庫存，其中num值超過30本的，顯示滯銷，大於0並低於10的，顯示暢銷，為0的顯示需要無貨
select name,
       num,
       case
           when num > 30 then '滯銷'
           when num > 10 then '暢銷'
           when num = 0 then '待補'
           end '庫存'
from books;

# 18、統計每一種note的庫存量，並合計總量
SELECT IFNULL(note, '合计总库存量') AS note, SUM(num)
FROM books
GROUP BY note
WITH ROLLUP;

# 19、統計每一種note的數量，並合計總量
select ifnull(note, '數量') as note, count(*)
from books
group by note
with rollup;

# 20、統計庫存量前三名的圖書
select *
from books
order by num desc
limit 3;

# 21、找出最早出版的一本書
select *
from books
order by pubdate
limit 1;

# 22、找出novel中價格最高的一本書
select *
from books
where note = 'novel'
order by price desc
limit 1;

# 23、找出書名中字數最多的一本書，不含空格
select *
from books
order by length(replace(name, ' ', '')) desc
limit 1;

上次修改於 2021-12-22