MySQL基本命令總結

原文連結：https://www.w3cschool.cn/mysql/mysql-drop-tables.html

MySQL 資料型別

MySQL中定義資料欄位的型別對你資料庫的優化是非常重要的。

MySQL支援多種型別，大致可以分為三類：數值、日期/時間和字串(字元)型別。

---

數值型別

MySQL支援所有標準SQL數值資料型別。

這些型別包括嚴格數值資料型別(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值資料型別(FLOAT、REAL和DOUBLE PRECISION)。

關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞。

BIT資料型別儲存位欄位值，並且支援MyISAM、MEMORY、InnoDB和BDB表。

作為SQL標準的擴充套件，MySQL也支援整數型別TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個整數型別的儲存和範圍。

型別	大小	範圍（有符號）	範圍（無符號）	用途
TINYINT	1 位元組	(-128，127)	(0，255)	小整數值
SMALLINT	2 位元組	(-32 768，32 767)	(0，65 535)	大整數值
MEDIUMINT	3 位元組	(-8 388 608，8 388 607)	(0，16 777 215)	大整數值
INT或INTEGER	4 位元組	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整數值
BIGINT	8 位元組	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	極大整數值
FLOAT	4 位元組	(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	單精度 浮點數值
DOUBLE	8 位元組	(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	雙精度 浮點數值
DECIMAL	對DECIMAL(M,D) ，如果M>D，為M+2否則為D+2	依賴於M和D的值	依賴於M和D的值	小數值

---

日期和時間型別

表示時間值的日期和時間型別為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每個時間型別有一個有效值範圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值。

TIMESTAMP型別有專有的自動更新特性，將在後面描述。

型別	大小 (位元組)	範圍	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	時間值或持續時間
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和時間值
TIMESTAMP	8	1970-01-01 00:00:00/2037 年某時	YYYYMMDD HHMMSS	混合日期和時間值，時間戳

---

字串型別

字串型別指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節描述了這些型別如何工作以及如何在查詢中使用這些型別。

型別	大小	用途
CHAR	0-255位元組	定長字串
VARCHAR	0-65535 位元組	變長字串
TINYBLOB	0-255位元組	不超過 255 個字元的二進位制字串
TINYTEXT	0-255位元組	短文字字串
BLOB	0-65 535位元組	二進位制形式的長文字資料
TEXT	0-65 535位元組	長文字資料
MEDIUMBLOB	0-16 777 215位元組	二進位制形式的中等長度文字資料
MEDIUMTEXT	0-16 777 215位元組	中等長度文字資料
LOGNGBLOB	0-4 294 967 295位元組	二進位制形式的極大文字資料
LONGTEXT	0-4 294 967 295位元組	極大文字資料

CHAR和VARCHAR型別類似，但它們儲存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在儲存或檢索過程中不進行大小寫轉換。

BINARY和VARBINARY類類似於CHAR和VARCHAR，不同的是它們包含二進位制字串而不要非二進位制字串。也就是說，它們包含位元組字串而不是字元字串。這說明它們沒有字符集，並且排序和比較基於列值位元組的數值值。

BLOB是一個二進位制大物件，可以容納可變數量的資料。有4種BLOB型別：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們只是可容納值的最大長度不同。

有4種TEXT型別：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應4種BLOB型別，有相同的最大長度和儲存需求。

MySQL 建立資料表

建立MySQL資料表需要以下資訊：

• 表名
• 表欄位名
• 定義每個表欄位

語法

以下為建立MySQL資料表的SQL通用語法：

CREATE TABLE table_name (column_name column_type);

 

以下例子中我們將在 W3CSCHOOL 資料庫中建立資料表w3cschool_tbl：

tutorials_tbl(
   tutorial_id INT NOT NULL AUTO_INCREMENT,
   tutorial_title VARCHAR(100) NOT NULL,
   tutorial_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( w3cschool_id )

MySQL 刪除資料表

MySQL中刪除資料表是非常容易操作的， 但是你再進行刪除表操作時要非常小心，因為執行刪除命令後所有資料都會消失。

語法

以下為刪除MySQL資料表的通用語法：

DROP TABLE table_name ;

---

在命令提示視窗中刪除資料表

在mysql>命令提示視窗中刪除資料表SQL語句為 DROP TABLE ：

例項

以下例項刪除了資料表w3cschool_tbl:

root@host# mysql -u root -p
Enter password:*******
mysql> use W3CSCHOOL;
Database changed
mysql> DROP TABLE w3cschool_tbl
Query OK, 0 rows affected (0.8 sec)
mysql>

MySQL 排序

我們知道從MySQL表中使用SQL SELECT 語句來讀取資料。

如果我們需要對讀取的資料進行排序，我們就可以使用MySQL的 ORDER BY 子句來設定你想按哪個欄位哪中方式來進行排序，再返回搜尋結果。

本章節使用的資料庫結構及資料下載：MySQL.sql

語法

以下是SQL SELECT 語句使用 ORDER BY 子句將查詢資料排序後再返回資料：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

• 你可以使用任何欄位來作為排序的條件，從而返回排序後的查詢結果。
• 你可以設定多個欄位來排序。
• 你可以使用 ASC 或 DESC 關鍵字來設定查詢結果是按升序或降序排列。 預設情況下，它是按升排列。
• 你可以新增 WHERE...LIKE 子句來設定條件。

MySQL GROUP BY 語句

GROUP BY 語句根據一個或多個列對結果集進行分組。

在分組的列上我們可以使用 COUNT, SUM, AVG,等函式。

GROUP BY 語法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

MySQL 連線的使用

在前幾章節中，我們已經學會了如果在一張表中讀取資料，這是相對簡單的，但是在真正的應用中經常需要從多個資料表中讀取資料。

本章節我們將向大家介紹如何使用 MySQL 的 JOIN 在兩個或多個表中查詢資料。

你可以在SELECT, UPDATE 和 DELETE 語句中使用 Mysql 的 JOIN 來聯合多表查詢。

JOIN 按照功能大致分為如下三類：

• INNER JOIN（內連線,或等值連線）：獲取兩個表中欄位匹配關係的記錄。
• LEFT JOIN（左連線）：獲取左表所有記錄，即使右表沒有對應匹配的記錄。
• RIGHT JOIN（右連線）： 與 LEFT JOIN 相反，用於獲取右表所有記錄，即使左表沒有對應匹配的記錄。

本章節使用的資料庫結構及資料下載：W3CSCHOOL.sql。

MySQL NULL 值處理

我們已經知道MySQL使用 SQL SELECT 命令及 WHERE 子句來讀取資料表中的資料,但是當提供的查詢條件欄位為 NULL 時，該命令可能就無法正常工作。

為了處理這種情況，MySQL提供了三大運算子:

• IS NULL: 當列的值是NULL,此運算子返回true。
• IS NOT NULL: 當列的值不為NULL, 運算子返回true。
• <=>: 比較操作符（不同於=運算子），當比較的的兩個值為NULL時返回true。

關於 NULL 的條件比較運算是比較特殊的。你不能使用 = NULL 或 != NULL 在列中查詢 NULL 值 。

在MySQL中，NULL值與任何其它值的比較（即使是NULL）永遠返回false，即 NULL = NULL 返回false 。

MySQL中處理NULL使用IS NULL和IS NOT NULL運算子。

 

MySQL 正規表示式

由 youj 建立，最後一次修改 2015-09-28

MySQL 正規表示式

在前面的章節我們已經瞭解到MySQL可以通過 LIKE ...% 來進行模糊匹配。

MySQL 同樣也支援其他正規表示式的匹配， MySQL中使用 REGEXP 操作符來進行正規表示式匹配。

如果您瞭解PHP或Perl，那麼操作起來就非常簡單，因為MySQL的正規表示式匹配與這些指令碼的類似。

下表中的正則模式可應用於 REGEXP 操作符中。

模式	描述
^	匹配輸入字串的開始位置。如果設定了 RegExp 物件的 Multiline 屬性，^ 也匹配 '\n' 或 '\r' 之後的位置。
$	匹配輸入字串的結束位置。如果設定了RegExp 物件的 Multiline 屬性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.	匹配除 "\n" 之外的任何單個字元。要匹配包括 '\n' 在內的任何字元，請使用象 '[.\n]' 的模式。
[...]	字符集合。匹配所包含的任意一個字元。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...]	負值字符集合。匹配未包含的任意字元。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
p1|p2|p3	匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 則匹配 "zood" 或 "food"。
*	匹配前面的子表示式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等價於{0,}。
+	匹配前面的子表示式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等價於 {1,}。
{n}	n 是一個非負整數。匹配確定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的兩個 o。
{n,m}	m 和 n 均為非負整數，其中n <= m。最少匹配 n 次且最多匹配 m 次。

例項

瞭解以上的正則需求後，我們就可以更加自己的需求來編寫帶有正規表示式的SQL語句。以下我們將列出幾個小例項(表名：person_tbl )來加深我們的理解：

查詢name欄位中以'st'為開頭的所有資料：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查詢name欄位中以'ok'為結尾的所有資料：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查詢name欄位中包含'mar'字串的所有資料：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查詢name欄位中以母音字元開頭或以'ok'字串結尾的所有資料：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

MySQL 事務

MySQL 事務主要用於處理操作量大，複雜度高的資料。比如說，在人員管理系統中，你刪除一個人員，你即需要刪除人員的基本資料，也要刪除和該人員相關的資訊，如信箱，文章等等，這樣，這些資料庫操作語句就構成一個事務！

• 在MySQL中只有使用了Innodb資料庫引擎的資料庫或表才支援事務
• 事務處理可以用來維護資料庫的完整性，保證成批的SQL語句要麼全部執行，要麼全部不執行
• 事務用來管理insert,update,delete語句

一般來說，事務是必須滿足4個條件（ACID）： Atomicity（原子性）、Consistency（穩定性）、Isolation（隔離性）、Durability（可靠性）

• 1、事務的原子性：一組事務，要麼成功；要麼撤回。
• 2、穩定性 ： 有非法資料（外來鍵約束之類），事務撤回。
• 3、隔離性：事務獨立執行。一個事務處理後的結果，影響了其他事務，那麼其他事務會撤回。事務的100%隔離，需要犧牲速度。
• 4、可靠性：軟、硬體崩潰後，InnoDB資料表驅動會利用日誌檔案重構修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit選項 決定什麼時候吧事務儲存到日誌裡。

在MySQL控制檯使用事務來操作

1，開始一個事務

start transaction

2, 做儲存點

savepoint 儲存點名稱

3, 操作

4，可以回滾，可以提交，沒有問題，就提交，有問題就回滾。

 

MySQL 複製表

如果我們需要完全的複製MySQL的資料表，包括表的結構，索引，預設值等。 如果僅僅使用CREATE TABLE ... SELECT 命令，是無法實現的。

本章節將為大家介紹如何完整的複製MySQL資料表，步驟如下：

• 使用 SHOW CREATE TABLE 命令獲取建立資料表(CREATE TABLE) 語句，該語句包含了原資料表的結構，索引等。
• 複製以下命令顯示的SQL語句，修改資料表名，並執行SQL語句，通過以上命令 將完全的複製資料表結構。
• 如果你想複製表的內容，你就可以使用 INSERT INTO ... SELECT 語句來實現。

 

轉載自W3C網站