MySQL資料庫操作-插入資料

從檔案一次插入多筆資料:
LOAD DATA LOCAL INFILE '檔案名'
INTO TABLE '資料表名稱'
FILEDS TERMINATED BY '欄位分隔符號'
OPTIONALLY ENCLOSED BY '欄位包圍符號'
LINES TERMINATED BY '行結束符號';
例:
LOAD DATA LOCAL INFILE 'myfile.txt'
INTO TABLE 'tbMyTable'
FILEDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY ' " '
LINES TERMINATED BY '\r\n';
表示在 tbMyTable 中插入 myfile.txt 裡的資料
檔案中欄位的內容都用雙引號包圍並以逗號分隔, 該檔案是使用windows的分行方式

操作時要注意客戶端的權限!

SQL資料類型

建立欄位:
欄位名 欄位類型 [屬性]
例:
username VARCHAR(30) NOT NULL
user_id INTEGER AUTO_INCREMENT

常用 SQL 資料類型:
SMALLINT -- 16 位元整數
INT -- 4 位元整數, -2147483648 ~ 2147483647
BIGINT -- 8 位元整數, -9223372036854775808 ~9223372036854775807
FLOAT -- 4 位元浮點數, 精度低到 6 位, 範圍介於 1x10 的正負 37 次方
DOUBLE PRECISION-- 8 位元浮點數, 精度低到 15 位, 範圍介於 1x10 的 -307 次方~ 1x10 的 308 次方
REAL -- 在 MySQL 中為 FLOAT 的別名
NUMERIC(p,d) -- 10進位數字, 指定精度 p 與小數位數 d (皆為正整數), 非近似值, 效能不佳
DECIMAL(p,d) -- 10進位數字, 指定精度 p 與小數位數 d (皆為正整數), 非近似值, 效能極差

SQL 日期時間類型:
DATE -- 日期
TIME -- 時間
TIMESTAMP -- 日期時間 (MySQL 為 DATETIME )

常用字元 SQL 資料類型:
CHAR(n) -- 固定長度字串, 一般狀況下最大允許值為 255
VARCHAR(n) -- 可變長度字串, 一般最大允許值為 255
TEXT -- 非 ANSI SQL 標準, 用來儲存大量文字資料
NCHAR(n) -- CHAR(n)的變體, 對於不能指定整個資料庫或表的"預設字元集"的 server, 用於輸入 unicode 字元
NVARCHAR(n) -- VARCHAR(n)的變體, 對於不能指定整個資料庫或表的"預設字元集"的 server, 用於輸入 unicode 字元

常用二進位 SQL 資料類型:
BLOB -- MySQL, Oracle 使用 -- MySQL中最大可到 216 bytes, 更大檔案可用 MEDIUMBLOB( 2 的 24次方 bytes) 和 LONGBLOB( 2 的 32次方 bytes); Oracle 的 BLOB 則可到 4GB
image -- MS SQL server 使用 -- 最大 2GB
bytea -- PostgreSQL 使用

欄位屬性:
NOT NULL -- 不允許為空
DEFAULT value -- 給定預設值
AUTO_INCREMENT -- 自動遞增
PRIMARY KEY -- 主鍵
UNIQUE -- 唯一值

MySQL資料庫操作-使用者管理與資料庫建立

登入MySQL:
mysql --user=資料庫用戶名 -p
(參數p 代表需要輸入密碼)

查看資料庫:
SHOW DATABASES;

切換資料庫:
USE 資料庫;

查看目前資料庫有哪些資料表:
SHOW TABLES;

查看資料表:
DESCRIBE 資料表;

查看排序整理(collation):
SHOW COLLATION;


用戶:
MySQL 的用戶資料存放在 mysql.user 資料表裡面
可以對它直接做查詢及修改
如:
SELECT User,Host FROM mysql.user

建立用戶:
GRANT 權限
ON 資料庫 . 表
TO '使用者名稱' @ '域名'
IDENTIFIED BY '密碼欄位'
WITH GRANT OPTION;
(如果有WITH GRANT OPTION表示允許用戶對其他用戶進行授權)
例:
GRANT CREATE, DROP, SELECT, UPDATE, INSERT, DELETE
ON dbMember.*
TO 'edward' @ 'localhost'
IDENTIFIED BY 'userpassword'

刪除用戶:
DROP USER '使用者名稱' @ '域名'

刪除特定權限:
REVOKE 權限
ON 資料庫 . 表
FROM '使用者名稱' @ '域名'

建立資料庫:
CREATE DATABASE 資料庫名稱
DEFAULT CHARACTER SET 預設字元集
DEFAULT COLLATE 排序整理名稱;
例:
CREATE DATABASE dbMember
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

建立資料表:
CREATE TABLE 資料表名稱(
欄位名 欄位類型 [屬性],
......
[索引]
)[屬性];
例:
CREATE TABLE MyMem(
user_id INTEGER AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(50) NOT NULL,
user_email VARCHAR(200) NOT NULL,
birthday DATE,
INDEX (user_name)
)ENGINE= InnoDB;

在已存在的表建立索引:
CREATE INDEX 索引名稱 ON 資料表名稱(欄位名稱);

外鍵:
(設定外鍵, 資料表都必須使用支援SQL Transcation 的 InnoDB 引擎, 否則會被忽略)
FOREIGN KEY (欄位名稱) REFERENCES 另一資料表(另一資料表的欄位) ON DELETE CASCADE;
設定外鍵的用意在於子表的紀錄必須對應到父表, 是一種驗證機制 (如討論區的回應一定是跟主題中的某筆記錄相關)
ON DELETE CASCADE表示"級聯刪除"(cascading delete), 當父表的紀錄被刪除時, 一併刪除在子表中外鍵設為該資料的相關記錄

刪除: (一旦刪除, 救不回來, 不應賦予一般使用者此權限)
DROP DATABASE 資料庫;
DROP TABLE 資料表;