標籤：指定表 let charset 有符號 position unique extra 注意 自動更新
MySQL入門筆記（二）
一、資料類型1. 整型 2. 浮點型 3. 字元型 4. 日期時間型 二、資料庫操作1. 建立庫CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;??上述代碼中DATABASE和SCHEMA完全相同，可任選一個（花括弧內的參數為任選其一）；
??添加IF NOT EXISTS的作用則是，若建立資料庫的名稱與已有資料庫名稱衝突，則產生一個警告，若無該關鍵字，則會產生錯誤（中括弧內的參數為可省略參數）；
??db_name為資料庫名稱；
??[DEFAULT] CHARACTER SET [=] 為指定資料庫的字元編碼，可不指定而使用預設的字元編碼。
??例1：建立一個名為test的資料庫
CREATE DATABASE test;??例2：建立一個名為test2的資料庫並指定字元編碼為GBK
CREATE DATABASE test2 CHARACTER SET gbk;??有一點需要注意的是，這一步驟僅僅是建立了資料庫，在後面需要建立資料表時，需要開啟指定資料庫：
USE db_name;DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;??參數與建立資料庫基本一致。
3. 修改庫ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name;??可修改的僅為字元編碼方式。若不指定資料庫名稱，則修改當前選中的資料庫。例：將名為test的資料庫的字元編碼方式改為utf-8
ALTER DATABASE test CHARACTER SET utf8;SHOW DATABASES;SHOW CREATE DATABASE db_name;SELECT DATABASE();CREATE TABLE [IF NOT EXISTS] table_name( column_name data_type [constraint], ···);??constraint為約束，選擇性參數。（詳情見1.2 約束）例：建立一個名稱為t1，包含id、username以及age三個欄位的資料表
CREATE TABLE t1( id SMALLINT UNSIGNED, username VARCHAR(20), age TINYINT UNSIGNED);??約束，顧名思義，即對某些列或整個表產生約束、限制，增加輸入規則，例如某些列不允許為空白、某些列不允許重複等。按照功能劃分，有以下幾種：
（1）主鍵約束PRIMARY KEY??主鍵約束用於唯一地標識表中的每一條記錄，通俗地說，就是加了主鍵約束的列或者表，不允許存在重複的記錄。==添加了主鍵約束的列自動為NOT NULL。==
??例：某表中存在一列用於儲存使用者名稱，用主鍵約束限制其不能存在重複的使用者名稱
username VARCHAR(20) PRIMARY KEY??==自動編號：==
AUTO_INCREMENT??顧名思義，即自動編號，序號從1開始。需要注意，AUTO_INCREMENT必須與主鍵約束配套使用。
??例：
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENTUNIQUE KEY??唯一約束與主鍵約束作用相同，也用於標識記錄的唯一性，不同之處在於，同一個資料表中可存在多個唯一約束，但主鍵約束只能存在一個。此外，主鍵約束不允許為空白，唯一約束則允許存在唯一的NULL值。
（3）非空約束NOT NULL??非空約束的用于禁止使用者在非空約束的列中輸入NULL。
（4）預設約束DEFAULT value??用於設定欄位的預設值，當使用者未輸入當前欄位時，將自動填入預設值。
??例：定義一個sex欄位，預設情況下為3
sex ENUM(‘1‘, ‘2‘, ‘3‘) DEFAULT 3FOREIGN KEY(column_name1) REFERENCES table_name(column_name2)??外鍵約束一般用於兩個存在某種關係的欄位，實現一對一或一對多的關係。
??例1：建立一個父表location和一個子表users，通過外鍵約束關聯location中的id與users中的lid
CREATE TABLE location( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL);CREATE TABLE users( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(20) NOT NULL, lid SMALLINT UNSIGNED, FOREIGN KEY(lid) REFERENCES location(id));??使用外鍵約束需要注意一些問題，第一，父表和子表禁止使用暫存資料表；第二，父表和子表必須使用相同的儲存引擎，且必須為InnoDB；第三，外鍵列與參照列必須具有相似的資料類型，其中若為==數字==（INT、FLOAT等），則其==類型長度和是否有符號位都必須完全相同==，若為==字元==，則可為==不同長度==；第四，向子表插入記錄時，需保證父表不為空白。
外鍵約束的參照操作：
??指定當刪除或更新父表中的記錄時，對子表進行的操作。使用方式為在外鍵約束末尾加上ON DELETE，然後加上相應操作的關鍵字。操作分別有以下幾種：
??1）CASCADE：當刪除或更新父表中的記錄時，自動更新或刪除子表中相應的記錄。
??2）SET NULL：當刪除或更新父表中的記錄時，將子表中相應的記錄的外鍵列設定為NULL。==注意，需保證改外鍵列沒有指定為NOT NULL。==
??3）RESTRICT：當父表中的記錄被子表中的記錄所參照時，這些被參照的記錄不允許進行刪除或更新操作，而未被參照的記錄則可自由刪除或更新。
??例：
??父表：
| id（參照列） | name |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
??子表：
| name | aid（外鍵列） |
|---|---|
| a | 1 |
| b | 1 |
| c | 2 |
??在上面這種情況中，若子表的外鍵約束加了RESTRICT關鍵字，則由於父表中id為1和2的兩條記錄被參照，不能被刪除或更新；而id為3的記錄未被參照，因此可以刪除或更新。
??4）NO ACTION：標準SQL中的關鍵字，在MySQL中等同於RESTRICT。
DROP TABLE tbl_name;ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name;RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ……SHOW TABLES [FROM db_name];SHOW CREATE TABLE table_name;SHOW COLUMNS FROM table_name;ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name2];??關鍵字COLUMN可不加；FIRST的作用是將增加列置於指定表的最前面，AFTER col_name2則是將增加列置於col_name2的下一列。
??例：存在一表test，含有id欄位，現在id欄位的下一列增加一個name欄位
ALTER TABLE test ADD name VARCHAR(20) NOT NULL AFTER id;ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition, ……)??添加多列時多個欄位寫進小括弧內，以逗號相隔，添加多列與添加單列的不同之處在於添加多列不可以指定添加位置，而是預設添加到資料表的最後。
5.2 刪除列ALTER TABLE tbl_name DROP [COLUMN] col_name;??如需同時刪除多列，則調用多次DROP。
??例：存在表test，含有password、sex欄位，現同時刪除這兩個欄位
ALTER TABLE test DROP password, DROP sex;ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];??MODIFY可用於修改列定義以及列位置。例：存在以下資料表t2，
+-------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------------------+------+-----+---------+-------+| id | smallint(5) unsigned | NO | | NULL | || name | varchar(20) | YES | MUL | NULL | || age | tinyint(4) | YES | UNI | NULL | || sex | enum(‘1‘,‘2‘,‘3‘) | YES | | NULL | || aid | smallint(5) unsigned | YES | MUL | NULL | |+-------+----------------------+------+-----+---------+-------+??現將id欄位移至name欄位下一列，操作為：
ALTER TABLE t2 MODIFY id SMALLINT UNSIGNED NOT NULL AFTER name;??再將id欄位的資料類型改為INT，操作為：
ALTER TABLE t2 MODIFY id INT UNSIGNED NOT NULL;??也可以將兩步操作同時進行：
ALTER TABLE t2 MODIFY id INT UNSIGNED NOT NULL AFTER name;??（2）CHANGE
ALTER TABLE tbl_name CHANE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name];??CHANGE與MODIFY基本一致，不同之處在於CHANGE可修改列名稱而MODIFY不可以。
5.4 添加約束??考慮到入門層級的讀者大多未學習索引，因此對約束的操作中涉及到索引的部分全部去除，僅介紹最基本的用法，需要研究完整功能的讀者可自行尋找相關資料。
（1）主鍵約束ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY (col_name)??symbol為約束的標記或別名，自行定義。例：為表test中的id欄位添加主鍵約束並起名為PK_test_id
ALTER TABLE test ADD CONSTRAINT PK_test_id PRIMARY KEY (id);ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE (col_name, ……);??添加唯一約束的方法與主鍵約束基本一致，不同之處在於唯一約束可同時添加多個，而主鍵約束只能有一個。
（3）預設約束ALTER TABLE tbl_name ALTER [COLUMN] col_name SET DEFAULT literal;??literal為預設值。例：為表test中的age欄位設定預設值為18
ALTER TABLE test ALTER age SET DEFAULT 15;ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY (col_name) reference_definition;??例：為表test中的aid欄位添加外鍵約束，參照列為表test2中的id欄位
ALTER TABLE test ADD FOREIGN KEY(aid) REFERENCES test2(id); ALTER TABLE tbl_name DROP PRIMARY KEY;ALTER TABLE tbl_name DROP {INDEX | KEY} index_name;??花括弧內的INDEX和KEY任選其一。注意，在這裡必須使用要刪除的約束所在欄位的==索引==而不是欄位名稱，查看索引可使用命令SHOW INDEXES FROM tbl_name\G（\G是指定結果以網格形式輸出，可不加），輸出結果中的Key_name即為索引名稱。
*************************** 1. row *************************** Table: t2 Non_unique: 0 Key_name: name Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: ??例如上面這一欄位的索引名稱即為name。
（3）預設約束ALTER TABLE tbl_name ALTER [COLUMN] col_name DROP DEFAULT;ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;??刪除外鍵約束不需要指定列，但必須輸入外鍵的標記fk_symbol，查看外鍵標記可使用命令SHOW CREATE TABLE tbl_name，即查看資料表的建立資訊。
| t2 | CREATE TABLE `t2` ( `id` smallint(5) unsigned NOT NULL, `name` varchar(20) DEFAULT NULL, `age` tinyint(4) DEFAULT NULL, `sex` enum(‘1‘,‘2‘,‘3‘), `aid` smallint(5) unsigned DEFAULT NULL, UNIQUE KEY `name` (`name`,`age`), UNIQUE KEY `age` (`age`), UNIQUE KEY `age_2` (`age`), KEY `aid` (`aid`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`aid`) REFERENCES `t1` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |??例如，上面這一資料表t2中，aid為外鍵列，因而找到相應的外鍵標記，為t2_ibfk_1，所以刪除這一外鍵約束的操作為：
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;INSERT [INTO] tbl_name [(col_name,···)] {VALUES | VALUE} ({expr | DEFAULT}, ...), (...), ...;??添加記錄可不指定列，這種情況下必須依次輸入目前記錄所有欄位的資料；若需要僅輸入其中某些欄位的值，則在表名後指定相應的列名稱；{expr | DEFAULT}的意思為，可以直接輸入相應的值，也可以輸入運算式；此外，使用INSERT語句插入記錄可同時插入多條記錄。
??例：向一個名為test，含有id、username、age三個欄位的表中插入一條記錄：
INSERT test VALUES(DEFAULT, ‘Kity‘, 16+2);??若僅輸入username、age：
INSERT test(username, age) VALUES(‘Kity‘, 18);??若同時插入兩條記錄：
INSERT test(username, age) VALUES(‘Kity‘, 18), (‘Smith‘, 26);INSERT [INTO] tbl_name SET col_name = {expr | DEFAULT}, ...;??這種方式與第一種方式的區別在於，這種方法能用於子查詢，同時這種方法每次只能輸入一條記錄。
1.3 INSERT-SELECTINSERT [INTO] tbl_name [(col_name, ...)] SELECT ...??這種方式的作用是將SELECT（若對SELECT的文法不明白，可先看後面的4.查詢記錄）的查詢結果插入到指定資料表中（實際上運用了子查詢，不明白的讀者可查看MySQL入門筆記（二）），需要注意，查詢的欄位數量必須與插入的欄位數量相匹配，否則就會出現錯誤。
??例：將表t1中age大於30的記錄的name、age欄位插入到表t2中：
INSERT t2(username, age) SELECT name, age FROM t1 WHERE age > 30;DELETE FROM tbl_name [WHERE where_condition]??WHERE關鍵字為對更新記錄的條件（相當於Java中的if），若不加，則刪除全部記錄。
??例：刪除表test中id為偶數的記錄：
DELETE FROM test WHERE id % 2 = 0;UPDATE [LOW_PRIORITY] [IGNORE] tbl_references SET col_name1 = {expr1 | DEFAULT} [, col_name2 = {expr2 | DEFAULT}] ... [WHERE where_condition];??tbl_reference為表格名稱。例1：更新表t1中的age欄位，使其全部增加5：
UPDATE t1 SET age = age + 5;??例2：使表t2中的age欄位全部為0，sex欄位（非枚舉類型）全部取相反數：
UPDATE t2 SET age = 0, sex = -sex;??例3：使表t3中id為2的一項name為John：
UPDATE t3 SET name = ‘John‘ WHERE id = 2;SELECT select_expr [, select_expr2 ...][ FROM table_references [WHERE where_condition] [GROUP BY {col_name | position} [ASC | DESC]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}]];??例：查看資料庫版本：
SELECT VERSION();??查詢資料表中的指定列的記錄。例：查詢表test中的全部欄位的記錄：
SELECT * FROM test;??或僅查詢其中的id、name欄位==（順序可根據需要自由調整）==：
SELECT id, name FROM test;??在查詢表時，有時（例如單表類比多表操作）會需要為欄位賦予別名，這時就用到AS。
??例：查詢表test中的id、username欄位，並給username賦予別名name：
SELECT id, username AS name FROM test;??GROUP BY用於對查詢結果進行排序。可以指定進行分組的列名稱，也可以指定位置position，位置即SELECT語句查詢的查詢順序，例如SELECT id, username FROM test;，在這裡若輸入位置為2，則對username欄位進行分組；此外，還可以指定分組是的排列順序，ASC為升序，DESC為降序。
??例：查詢表test中的name、age欄位，並根據age欄位分組從大到小排列：
SELECT name, age FROM test GROUP BY age DESC;??HAVING用於設定分組條件，即限制進行分組的記錄範圍。==需要注意的是，若條件中用到表中的某些欄位進行判斷，那麼所使用的欄位必須是在查詢列表中的。==
??例：查詢表test中的name、age欄位，並將age>30的記錄進行分組：
SELECT name, age FROM test GROUP BY age HAVING age > 30;??ORDER BY用於對查詢結果進行排序。若添加個條件，則在第一條件相同的情況下，比較第二條件，以此類推。
??例：查詢表test中的所有欄位，並按照age升序排列，若age相同，則按照id降序排列：
SELECT * FROM test ORDER BY age, id DESC;??LIMIT用於限制返回的查詢結果的數量。offset為查詢的起點，要注意，同大部分語言一樣，==MySQL中第一條記錄為0==；row_count則為查詢結果的數量。此外使用LIMIT語句進行限制時，==其遵循的順序是查詢結果的順序==，而不是資料表格儲存體的順序。
??例：查詢表test中的所有欄位，顯示第3到第5條記錄：
SELECT * FROM test LIMIT 2, 3;MySQL入門筆記（一）
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
