|
記了一些用 Windows 的命令行來操作 MySQL 的指令。
摘要
連結 MySQL
->mysql -u username -p
username 是你的帳號名稱。
遠程連結
->mysql -h hostname -u username -p
hostname 是 MySQL 主機的名稱。
顯示全部的數據庫
SHOW DATABASES;
mysql 是 MySQL 用來儲存帳號和權限的數據庫，不能刪除。
選擇數據庫
USE database_name;
顯示數據庫里的資料表
SHOW TABLES;
顯示資料表的信息
DESCRIBE table_name;
注銷 MySQL 顯示器
\q
SQL 的執行指令可以從文本文件內輸入。
SOURCE file_name
如果沒有登入 MySQL 的顯示器，用：
->mysql -u username -p < file_name
Primary key - 用來識別字段的 key。
Foreign key - 代表連結的資料表。再另一個資料表里的 primary key。
使用 MySQL
建立數據庫
CREATE DATABASE employee;
這會建立一個叫 employee 的數據庫。
建立資料表
CREATE TABLE table_name (table definition) [type=table_type];
完整格式：
程序代碼:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name [(create_definition, ...)]
[table_options] [select_statement];
or
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name LIKE old_table_name;
create_definition:
col_name TYPE [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] (index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or FULLTEXT [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
or CHECK (expression)
TEMPORARY 關鍵詞是用來建立暫時的資料表，當你的連結階段結束后會自動刪除。
CREATE TEMPORARY TABLE table_name;
IF NOT EXISTS 只有在資料表不存在時才會建立。
CREATE TABLE IF NOT EXISTS table_name;
LIKE old_table_name 會依照 old_table_name 的構造來建立新的資料表。
字段定義 (column definition) 可用的選項：
NOT NULL 或 NULL 來定義字段不能包括 NULL (NOT NULL) 或可以包括 NULL (NULL)。
默認值是可以包括 NULL。
DEFAULT 可用來宣告字段的默認值。
AUTO_INCREMENT 會自動產生連續的數字。產生的數字會大于資料表內的最大數值。
每個資料表只能有一個 AUTO_INCREMENT 字段。他必須是索引。
任何 PRIMARY KEY 的字段會自動建立索引。
PRIMARY KEY 可用來宣告 PRIMARY KEY 的字段。
REFERENCES 可用來宣告字段是 FOREIGN KEY，只能用在 InnoDB 資料表。
下面的關鍵詞可用來宣告字段的資料：
PRIMARY KEY 來設定多字段的 PRIMARY KEY，跟著是字段的名稱。
INDEX 和 KEY 是一樣的關鍵詞，用來設定索引。
UNIQUE 表示字段是獨特的，一樣是索引。
FULLTEXT 可用來建立 FULLTEXT 索引，可用于 TEXT, CHAR, VARCHAR 類型。
FULLTEXT 只能用在 MyISAM 資料表。
FOREIGN KEY 可用來宣告 FOREIGN KEY。
下面的選項可以不用，通常是用在最佳化：
AUTO_INCREMENT = #
設定開始的 AUTO_INCREMENT 數值。
AVG_ROW_LENGTH = #
可用來估計你要的字段長度。
CHECKSUM = 1
開啟資料表的核對值?？捎脕硇迯蛽p壞的資料表。只能用在 MyISAM。
COMMENT = "字符串"
用來輸入資料表的批注。
MAX_ROWS = #
設定資料表允許的字段值 (最大)。
MIN_ROWS = #
設定資料表允許的字段值 (最小)。
PACK_KEYS = {0 | 1 | DEFAULT}
MySQL 的預設是會壓縮 CHAR, VARCHAR, 和 TEXT 的字符串。
如要關閉選 0。
DELAY_KEY_WRITE = {0 | 1}
延后 key 的更新，直到關閉數據庫。只能用在 MyISAM。
ROW_FORMAT = {default | dynamic | fixed | compress}
用來設定字段的儲存方式。只能用在 MyISAM。
RAID_TYPE = {1 | STRIPED | RAID0} RAID_CHUNKS = # RAID_CHUNKSIZE = #
RAID 的設定。
UNION = (table_name, [table_name...])
用于 MERGE 資料表。允許你設定 MERGE 使用的資料表。
INSERT_METHOD = {NO | FIRST | LAST}
用于 MERGE 資料表。設定要插入資料到哪個資料表。
DATA_DIRECTORY = "絕對路徑"
儲存資料的目錄。
INDEX_DIRECTORY = "絕對路徑"
儲存索引的目錄。
字段和數據類型
數字類型
INT 和 FLOAT
范例：salary decimal(10, 2) - 寬度 10 和 2 個小數點。
可以用 UNSIGNED 和 ZEROFILL 關鍵詞。
UNSIGNED 只能包括零或整數。ZEROFILL 會顯示數字前的零數。
整數和變化
可以縮寫成 INT，大小是 4 bytes。
TINYINT 是 1 byte，跟 BIT 和 BOOL 相同。
SMALLINT 是 2 bytes。
MEDIUMINT 是 3 bytes。
BIGINT 是 8 bytes。
FLOAT 是單精密度的浮點數。
DOUBLE 是雙精密度的浮點數，跟 REAL 和 DOUBLE PRECISION 相同。
字符串和文字類型
CHAR 可用來儲存固定長度的字符串，如果不設定長度，預設是 CHAR(1)。
最大值是 255 個字符。
固定長度有時候會比 VARCHAR 來的快。
如果跟著的是 BINARY 關鍵詞，比較字符串的時候必須注意大小寫。
VARCHAR 可用來儲存動態的字符串，如果預先不知道字符串的長度，范圍是 0 到 255。
TEXT 可儲存較長的字符串，最大到 65535 個字符或 bytes。
BLOB 代表 binary large object，用來儲存 binary 資料，最大到 65535 個字符或 bytes。比較字符串時需要注意大小寫。
TINYTEXT 或 TINYBLOB 可儲存小于 255 個字符或 bytes 的字符串。
MEDIUMTEXT 或 MEDIUMBLOB 可儲存到 16777215 個字符或 bytes 的字符串。
LONGTEXT 或 LONGBLOB 可儲存到 4294,967,295 個字符或 bytes 的字符串。
ENUM 可用來儲存自訂的值。
范例：gender enum('m', 'f')
表示儲存的直是 m 和 f。也可儲存 NULL，所以可能的直是 m, f, NULL, 或 error。
SET 跟 ENUM 類似不過可以儲存其它的 ENUM 值。
日期和時間類型
date 可用來儲存日期，以 YYYY-MM-DD 顯示。
time 可用來儲存時間，以 HH:MM:SS 顯示。
datetime 包含了日期和時間，以 YYYY-MM-DD HH:MM:SS 顯示。
timestamp 儲存插入字段或改變的時間。
版本 4.0 之前可以用 timestamp(10) 來改變顯示的寬度。
year 可用來儲存年份，可以用 year(2) 或 year(4) 來設定顯示格式，預設是 year(4)。
year(2) 代表從 1970 到 2069 的年份。
建立索引
任何宣告為 PRIMARY KEY, KEY, UNIQUE, 或 INDEX 的字段都會自動建立索引。
要將索引建立到字段用：
CREATE INDEX name ON employee(name);
這會建立一個叫 name 的索引到 employee 資料表里的 name 字段。
刪除數據庫，資料表，和索引
刪除整個數據庫，包括里面的資料：
DROP DATABASE employee;
可以在 employee 的前面加上 IF EXISTS。
刪除一個資料表：
DROP TABLE assignment;
完整格式：
程序代碼:
DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name...]
刪除索引：
DROP INDEX index_name ON employee;
改變資料表結構
如果要改變資料表的結構，建立 name 的索引在 employee 里面的 name 字段：
ALTER TABLE employee;
ADD INDEX name (name);
完整格式：
程序代碼:
ALTER [IGNORE] TABLE table_name alter_spec [, alter_spec ...]
alter_spec:
ADD [COLUMN] create_definition [FIRST | AFTER col_name]
or ADD [COLUMN] (create_definition, create_definition,...)
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ADD FULLTEXT [index_name] (index_col_name,...)
or ADD [CONSTRAINT symbol] FOREIGN KYE [index_name] (index_col_name,...)
[reference_definition]
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER column_name]
or MODIFY [COLUMN] create_definition [FIRST | AFTER col_name]
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or DISABLE KEYS
or ENABLE KEYS
or RENAME [TO] new_table_name
or ORDER BY col_name
or table_options
因為 ALTER 非常有彈性所以有很多的查詢子句。
CHANGE 和 MODIFY 相同，可以讓你改變字段的定義或位置。
DROP COLUMN 會刪除資料表里的字段。
DROP PRIMARY KEY 和 DROP INDEX 會刪除相連的字段索引。
DISABLE KEYS 會告訴 MySQL 停止更新索引，只能用在 MyISAM。
ENABLE KEYS 會繼續更新索引。
RENAME 可以讓你改變資料表的名稱。
ORDER BY 會重新排序字段。
使用 INSERT
INSERT 可以讓你輸入字段到資料表里。
完整格式：
程序代碼:
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] table_name [(col_name,...)]
VALUES ((expression | DEFAULT),...),(...),...
[ON DUPLICATE KEY UPDATE col_name=expression, ...]
or
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] table_name [(col_name,...)]
SELECT ...
or
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] table_name
SET col_name=(expression | DEFAULT), ...
[ON DUPLICATE KEY UPDATE col_name=expression, ...]
范例：
程序代碼:
INSERT INTO department VALUES
(42, 'Finance'),
(128, 'Research and Development'),
(NULL, 'Human Resources');
INTO 可以不用。
INSERT INTO department
SELECT ...
可以從其它的資料表里取得資料，存到 department 這個資料表。
INSERT INTO department
SET name = 'Asset Management';
這會輸入一個字段的資料，只提供 name 的字段。其它沒有資料的字段會是 NULL。
其它查詢子句：
LOW PRIORITY 或 DELAYED 會等其它用戶讀取完資料后再輸入資料。
LOW PRIORITY 會阻止要輸入資料的用戶，所以在跑完查詢后需要等待一段時間。
DELAYED 不會阻止用戶，但是輸入資料要等沒人使用資料表后才會完成。
范例：INSERT DELAYED INTO ...
IGNORE 會忽略輸入跟字段的 PRIMARY KEY 或 UNIQUE 值的沖突所產生的錯誤訊息。
DEFAULT 會用默認值建立字段。
范例：INSERT INTO department VALUES(DEFAULT)
ON DUPLICATE KEY UPDATE 會解決跟 PRIMARY KEY 或 UNIQUE 的沖突。
范例：
INSERT INTO warning (employeeID)
VALUES(6651)
ON DUPLICATE KEY UPDATE count = count+1;
使用 REPLACE
使用方法跟 INSERT 相同。
REPLACE 可以解決有沖突的輸入。如果有沖突字段會被更新。
使用 DELETE
用來刪除字段。
范例：DELETE FROM department;
會刪除 department 里面所有的字段。
如果要選擇哪個字段，用 WHERE。
范例：DELETE FROM department WHERE name='Asset Management';
如果有開啟 --safe-updates 或 --i-am-a-dummy 選項可以避免一次刪除全部的字段。
所以必須選擇要刪除的字段。
完整格式：
程序代碼:
DELETE [LOW_PRIORITY] [QUICK] FROM table_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT rows]
or
DELETE [LOW_PRIORITY] [QUICK] table_name[.*] [, table_name[.*] ...]
FROM table-references
[WHERE where_definition]
or
DELETE [LOW_PRIORITY] [QUICK]
FROM table_name[.*] [, table_name[.*] ...]
USING table-references
[WHERE where_definition]
后兩個格式可以用來刪除其它資料表里的字段。
范例：
程序代碼:
DELETE employee, employeeSkills
FROM employee, employeeSkills, department
WHERE employee.employeeID = employeeSkills.employeeID
AND employee.departmentID = department.departmentID
AND department.name='Finance';
上面的會刪除全部的有在 Finance department 里面工作的 employees，然后刪除他們的 employeeSkills。
FROM 里面的字段不會被刪除，他們只是用來搜尋。只有在 employee 和 employeeSkills 里面的字段會被刪除。
AND 是包含的條件。employee.employeeID 表示 employee 資料表里的 employeeID 字段。
第三個格式跟第二個差不多，但是刪除 FROM 里面的資料表，用 USING 來表示被搜尋的資料表。
范例：
程序代碼:
DELETE FROM employee, employeeSkills
USING employee, employeeSkills, department
WHERE employee.employeeID = employeeSkills.employeeID
AND employee.departmentID = department.departmentID
AND department.name='Finance';
跟上面一樣，不同的表示方法。
額外的查詢子句：
LOW_PRIORITY 跟 INSERT 里的意思相同。
QUICK 可用來加快 DELETE 的速度。
ORDER BY 是用來刪除字段的順序。
LIMIT 可用來限制被刪除的字段數量。
使用 TRUNCATE
可用來刪除全部的字段。
TRUNCATE TABLE employee;
速度比 DELETE 還快。
使用 UPDATE
可以用來更新字段。
程序代碼:
UPDATE employee
SET job='DBA'
WHERE employeeID='6651';
完整格式：
程序代碼:
UDPATE [LOW_PRIORITY] [IGNORE] table_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT rows]
or
UPDATE [LOW_PRIORITY] [IGNORE] table_name [, table_name ...]
SET col_name=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
WHERE 可以用來選擇要更新哪個字段。
第二個格式可以用來更新多個資料表。
LOW_PRIORITY 和 IGNORE 跟在 INSERT 里的相同，ORDER BY 和 LIMIT 跟在 DELETE 的相同。
用 LOAD DATA INFILE 上傳資料文件
可以讓你用檔案來輸入資料到一個資料表。
數據文件范例：
程序代碼:
42 Finance
128 Research and Development
NULL Human Resources
NULL Marketing
加載資料文件：
程序代碼:
LOAD DATA LOCAL INFILE 'department_infile.txt'
INTO TABLE department;
LOAD DATA INFILE 需要 FILE 的權限。
完整格式：
程序代碼:
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'fileName.txt'
[REPLACE | IGNORE]
INTO TABLE table_name
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\']
]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name, ...)]
額外的查詢子句：
LOW_PRIORITY 跟 INSERT 里的類似，會等用戶讀取完資料表后在繼續。
CONCURRENT 允許其它用戶讀資料表里的資料。
LOCAL 表示資料文件在用戶的計算機里，如果不用 MySQL 會在主機內讀取資料文件。
REPLACE 會覆蓋舊的字段，IGNORE 會留舊的字段，如果有沖突。
FIELDS 和 LINES 可以用來規定資料表的格式，預設的格式是
每行一個字段，字段資料用 tabs 來分開，要或不要用單引號，和用 \ 來跳脫字符。
IGNORE number LINES 會忽略檔案內行數，number 表示行數。
最后一個子句可以用來設定只輸入資料到某些字段。
|