MySQL 入門學習（三）：建庫建表與數據導入

1. 查看現有資料庫

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+

2. 建立資料庫

mysql> CREATE DATABASE abccs;
Query OK, 1 row affected (0.00 sec)
注意：Windows 不區分大小寫，Linux 區分。

3. 選擇資料庫

mysql> USE abccs;
Database changed

4. 建立資料表

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> CREATE TABLE mytable (
    -> name VARCHAR(20),
    -> sex CHAR(1),
    -> birth DATE,
    -> birthaddr VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

說明：name/birthaddr 用 VARCHAR(20) 可變長；sex 用 CHAR(1) 存 'm'/'f'；birth 用 DATE 型別。

5. 檢視表結構

mysql> DESCRIBE mytable;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| name        | varchar(20) | YES  |     | NULL    |       |
| sex         | char(1)     | YES  |     | NULL    |       |
| birth       | date        | YES  |     | NULL    |       |
| birthaddr   | varchar(20) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

6. 插入記錄

mysql> SELECT * FROM mytable;
Empty set (0.00 sec)

mysql> INSERT INTO mytable VALUES ('abccs','f','1977-07-07','china');
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM mytable;
+-------+------+------------+-----------+
| name  | sex  | birth      | birthaddr |
+-------+------+------------+-----------+
| abccs | f    | 1977-07-07 | china     |
+-------+------+------------+-----------+

7. 批量導入文本文件

建立 mytable.txt（Tab 分隔，欄位順序同建表）：

abccs	f	1977-07-07	china
mary	f	1978-12-12	usa
tom	m	1970-09-02	usa

導入：

mysql> LOAD DATA LOCAL INFILE "mytable.txt" INTO TABLE mytable;
Query OK, 3 rows affected (0.01 sec)

mysql> SELECT * FROM mytable;
+-------+------+------------+-----------+
| name  | sex  | birth      | birthaddr |
+-------+------+------------+-----------+
| abccs | f    | 1977-07-07 | china     |
| mary  | f    | 1978-12-12 | usa       |
| tom   | m    | 1970-09-02 | usa       |
+-------+------+------------+-----------+