MySQL 書店狀況劇 (2) 表單操作

今天要用 MySQL 將下面的表單存進電腦裡。

Author	Title	Type	Year
Mark Twain	The Adventures of Tom Sawyer	Fiction	1876
Charles Darwin	The Origin of Species	Nonfiction	1856
J. K. Rowling	Harry Potter and the Sorcerer's Stone	Fiction	1999

mycli 操作流程：

1. 建立一個名為 publications 的 database
2. 建立一個名為 store 的 table
3. 插入資料
4. 查詢資料
5. 修改資料
6. 改變欄位型別
7. 刪除 table
8. 刪除 data base

1. Creating database:

使用 tab 會自動補全的 cli:

$ mycli -u user_name

CREATE DATABASE publications ;
SHOW DATABASES ;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| publications       |
| sys                |
+--------------------+

使用 publications:

USE publications;

2. Creating table:

CREATE TABLE store ( 
  author VARCHAR(128), 
  title VARCHAR(128), 
  type VARCHAR(16), 
  year CHAR(4)
) ENGINE MyISAM;

VARCHAR 是可變長度的字串，CHAR 是長度不可變的字串。

查看所有 tables 中有沒有 store:

SHOW TABLES ;

查看 store 的資訊：

DESCRIBE store;

+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| author | varchar(128) | YES  |     | <null>  |       |
| title  | varchar(128) | YES  |     | <null>  |       |
| type   | varchar(16)  | YES  |     | <null>  |       |
| year   | char(4)      | YES  |     | <null>  |       |
+--------+--------------+------+-----+---------+-------+

解釋一下上面的表格的意思：

• Field: 表單的欄位名稱
• Type: 欄位的型別
• Null: 欄位可否為 NULL
• Key: 是否有建立索引加速搜尋該欄位的資料
• Default: 欄位預設值
• Extra: 其它資訊

3. Inserting data:

依照下面格式插入資料，資料順序對應欄位順序：

INSERT INTO store (
  author, 
  title, 
  type, 
  year
) VALUES (
  'Mark Twain', 
  'The Adventures of Tom Sawyer', 
  'Fiction', 
  '1876'
);

重複上面動作把所有資料輸入表單中。

顯示所有資料：

SELECT * FROM store;

+----------------+---------------------------------------+------------+------+
| author         | title                                 | type       | year |
+----------------+---------------------------------------+------------+------+
| Mark Twain     | The Adventures of Tom Sawyer          | Fiction    | 1876 |
| J. K. Rowling  | Harry Potter and the Sorcerer's Stone | Fiction    | 1999 |
| Charles Darwin | The Origin of Species                 | NonFiction | 1856 |
+----------------+---------------------------------------+------------+------+

4. Query data:

列出 J. K. Rowling 寫的書：

SELECT author, title 
FROM store 
where author='J. K. Rowling';

+---------------+---------------------------------------+
| author        | title                                 |
+---------------+---------------------------------------+
| J. K. Rowling | Harry Potter and the Sorcerer's Stone |
+---------------+---------------------------------------+

列出 1856 年有出書的作者名稱：

SELECT author, year 
FROM store 
WHERE year='1856';

+----------------+------+
| author         | year |
+----------------+------+
| Charles Darwin | 1856 |
+----------------+------+

5. Updating data:

假設我們發現哈利波特是 2000 出版，我們要如何修改我們的資料。

UPDATE store 
SET year='2000' 
WHERE author='J. K. Rowling';

SELECT * FROM store ;

+----------------+---------------------------------------+------------+------+
| author         | title                                 | type       | year |
+----------------+---------------------------------------+------------+------+
| Mark Twain     | The Adventures of Tom Sawyer          | Fiction    | 1876 |
| J. K. Rowling  | Harry Potter and the Sorcerer's Stone | Fiction    | 2000 |
| Charles Darwin | The Origin of Species                 | NonFiction | 1856 |
+----------------+---------------------------------------+------------+------+

再把資料改回來。

UPDATE store 
SET year='1999' 
WHERE author='J. K. Rowling';

6. Changing the data type of a column:

我們發現把年份設成字串不好，因為我們沒辦法找出 1900 年後出版的書。

我們把年份轉成整數這樣可以使用大於和小於等數學算。

ALTER TABLE store 
MODIFY year SMALLINT;

查看一下轉換後的欄位的型別。

DESCRIBE store;

+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| author | varchar(128) | YES  |     | <null>  |       |
| title  | varchar(128) | YES  |     | <null>  |       |
| type   | varchar(16)  | YES  |     | <null>  |       |
| year   | smallint(6)  | YES  |     | <null>  |       |
+--------+--------------+------+-----+---------+-------+

找出 1900 年後出版的書：

SELECT * FROM store WHERE year > 1900;

+---------------+---------------------------------------+---------+------+
| author        | title                                 | type    | year |
+---------------+---------------------------------------+---------+------+
| J. K. Rowling | Harry Potter and the Sorcerer's Stone | Fiction | 1999 |
+---------------+---------------------------------------+---------+------+