MySQL 教程基礎介紹

weixin_48954143發表於2021-01-04

MySql

MySQL 教程基礎介紹

什麼是資料庫？

資料庫（Database）是按照資料結構來組織、儲存和管理資料的倉庫。

每個資料庫都有一個或多個不同的 API 用於建立，訪問，管理，搜尋和複製所儲存的資料。

我們也可以將資料儲存在檔案中，但是在檔案中讀寫資料速度相對較慢。

所以，現在我們使用關係型資料庫管理系統（RDBMS）來儲存和管理大資料量。所謂的關係型資料庫，是建立在關係模型基礎上的資料庫，藉助於集合代數等數學概念和方法來處理資料庫中的資料。

RDBMS 即關聯式資料庫管理系統(Relational Database Management System)的特點：

1.資料以表格的形式出現
2.每行為各種記錄名稱
3.每列為記錄名稱所對應的資料域
4.許多的行和列組成一張表單
5.若干的表單組成database

RDBMS 術語

在我們開始學習MySQL 資料庫前，讓我們先了解下RDBMS的一些術語：

資料庫: 資料庫是一些關聯表的集合。
資料表: 表是資料的矩陣。在一個資料庫中的表看起來像一個簡單的電子表格。
列: 一列(資料元素) 包含了相同型別的資料, 例如郵政編碼的資料。
行：一行（=元組，或記錄）是一組相關的資料，例如一條使用者訂閱的資料。
冗餘：儲存兩倍資料，冗餘降低了效能，但提高了資料的安全性。
主鍵：主鍵是唯一的。一個資料表中只能包含一個主鍵。你可以使用主鍵來查詢資料。
外來鍵：外來鍵用於關聯兩個表。
複合鍵：複合鍵（組合鍵）將多個列作為一個索引鍵，一般用於複合索引。
索引：使用索引可快速訪問資料庫表中的特定資訊。索引是對資料庫表中一列或多列的值進行排序的一種結構。類似於書籍的目錄。
參照完整性: 參照的完整性要求關係中不允許引用不存在的實體。與實體完整性是關係模型必須滿足的完整性約束條件，目的是保證資料的一致性。

MySQL 為關係型資料庫(Relational Database Management System), 這種所謂的"關係型"可以理解為"表格"的概念, 一個關係型資料庫由一個或數個表格組成

表頭(header): 每一列的名稱;
列(col): 具有相同資料型別的資料的集合;
行(row): 每一行用來描述某條記錄的具體資訊;
值(value): 行的具體資訊, 每個值必須與該列的資料型別相同;
鍵(key): 鍵的值在當前列中具有唯一性。

MySQL資料庫

MySQL 是一個關係型資料庫管理系統，由瑞典 MySQL AB 公司開發，目前屬於 Oracle 公司。MySQL 是一種關聯資料庫管理系統，關聯資料庫將資料儲存在不同的表中，而不是將所有資料放在一個大倉庫內，這樣就增加了速度並提高了靈活性。

MySQL 是開源的，所以你不需要支付額外的費用。
MySQL 支援大型的資料庫。可以處理擁有上千萬條記錄的大型資料庫。
MySQL 使用標準的 SQL 資料語言形式。
MySQL 可以執行於多個系統上，並且支援多種語言。這些程式語言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
MySQL 對PHP有很好的支援，PHP 是目前最流行的 Web 開發語言。
MySQL 支援大型資料庫，支援 5000 萬條記錄的資料倉儲，32 位系統表檔案最大可支援 4GB，64 位系統支援最大的表檔案為8TB。
MySQL 是可以定製的，採用了 GPL 協議，你可以修改原始碼來開發自己的 MySQL 系統。

管理MySQL的命令

以下列出了使用Mysql資料庫過程中常用的命令：

USE 資料庫名 :
選擇要操作的Mysql資料庫，使用該命令後所有Mysql命令都只針對該資料庫。

mysql> use RUNOOB;
Database changed
SHOW DATABASES:
列出 MySQL 資料庫管理系統的資料庫列表。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| RUNOOB             |
| cdcol              |
| mysql              |
| onethink           |
| performance_schema |
| phpmyadmin         |
| test               |
| wecenter           |
| wordpress          |
+--------------------+
10 rows in set (0.02 sec)
SHOW TABLES:
顯示指定資料庫的所有表，使用該命令前需要使用 use 命令來選擇要操作的資料庫。

mysql> use RUNOOB;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_runoob |
+------------------+
| employee_tbl     |
| runoob_tbl       |
| tcount_tbl       |
+------------------+
3 rows in set (0.00 sec)
SHOW COLUMNS FROM 資料表:
顯示資料表的屬性，屬性型別，主鍵資訊 ，是否為 NULL，預設值等其他資訊。

mysql> SHOW COLUMNS FROM runoob_tbl;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| runoob_id       | int(11)      | NO   | PRI | NULL    |       |
| runoob_title    | varchar(255) | YES  |     | NULL    |       |
| runoob_author   | varchar(255) | YES  |     | NULL    |       |
| submission_date | date         | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
SHOW INDEX FROM 資料表:
顯示資料表的詳細索引資訊，包括PRIMARY KEY（主鍵）。

mysql> SHOW INDEX FROM runoob_tbl;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| runoob_tbl |          0 | PRIMARY  |            1 | runoob_id   | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] \G:
該命令將輸出Mysql資料庫管理系統的效能及統計資訊。

mysql> SHOW TABLE STATUS  FROM RUNOOB;   # 顯示資料庫 RUNOOB 中所有表的資訊

mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%';     # 表名以runoob開頭的表的資訊
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G;   # 加上 \G，查詢結果按列列印

MySQL 資料庫基本操作

MySQL 建立資料庫

我們可以在登陸 MySQL 服務後，使用 create 命令建立資料庫，語法如下:

CREATE DATABASE 資料庫名;

以下命令簡單的演示了建立資料庫的過程，資料名為 RUNOOB:

[root@host]# mysql -u root -p   
Enter password:******  # 登入後進入終端

mysql> create DATABASE RUNOOB;

使用 mysqladmin 建立資料庫
使用普通使用者，你可能需要特定的許可權來建立或者刪除 MySQL 資料庫。

所以我們這邊使用root使用者登入，root使用者擁有最高許可權，可以使用 mysql mysqladmin 命令來建立資料庫。

以下命令簡單的演示了建立資料庫的過程，資料名為 RUNOOB:

[root@host]# mysqladmin -u root -p create RUNOOB
Enter password:******

以上命令執行成功後會建立 MySQL 資料庫 RUNOOB。

MySQL 刪除資料庫

使用普通使用者登陸 MySQL 伺服器，你可能需要特定的許可權來建立或者刪除 MySQL 資料庫，所以我們這邊使用 root 使用者登入，root 使用者擁有最高許可權。

在刪除資料庫過程中，務必要十分謹慎，因為在執行刪除命令後，所有資料將會消失。

drop 命令刪除資料庫
drop 命令格式：

drop database <資料庫名>;

例如刪除名為 RUNOOB 的資料庫：

mysql> drop database RUNOOB;

使用 mysqladmin 刪除資料庫
你也可以使用 mysql mysqladmin 命令在終端來執行刪除命令。

[root@host]# mysqladmin -u root -p drop RUNOOB
Enter password:******

執行以上刪除資料庫命令後，會出現一個提示框，來確認是否真的刪除資料庫：

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'RUNOOB' database [y/N] y
Database "RUNOOB" dropped

在 mysql> 提示視窗中可以很簡單的選擇特定的資料庫。你可以使用SQL命令來選擇指定的資料庫。

例項

[root@host]# mysql -u root -p
Enter password:******
mysql> use RUNOOB;
Database changed
mysql>

執行以上命令後，你就已經成功選擇了資料庫，在後續的操作中都會在 RUNOOB 資料庫中執行。

注意:所有的資料庫名，表名，表欄位都是區分大小寫的。所以你在使用SQL命令時需要輸入正確的名稱。

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。下面的表顯示了需要的每個整數型別的儲存和範圍。
在這裡插入圖片描述

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

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

BLOB 是一個二進位制大物件，可以容納可變數量的資料。有 4 種 BLOB 型別：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區別在於可容納儲存範圍不同。

有 4 種 TEXT 型別：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對應的這 4 種 BLOB 型別，可儲存的最大長度不同，可根據實際情況選擇。

關於 char、varchar 與 text 平時沒有太在意，一般來說，可能現在大家都是用 varchar。但是當要儲存的內容比較大時，究竟是選擇 varchar 還是 text 呢？

這三種型別比較：

（1）char: char 不用多說了，它是定長格式的，但是長度範圍是 0~255. 當你想要儲存一個長度不足 255
的字元時，Mysql 會用空格來填充剩下的字元。因此在讀取資料時，char 型別的資料要進行處理，把後面的空格去除。
（2）varchar: 關於 varchar，有的說最大長度是 255，也有的說是 65535，查閱很多資料後發現是這樣的：varchar
型別在 5.0.3 以下的版本中的最大長度限制為 255，而在 5.0.3 及以上的版本中，varchar 資料型別的長度支援到了
65535，也就是說可以存放 65532 個位元組（注意是位元組而不是字元！！！）的資料（起始位和結束位佔去了3個位元組），也就是說，在
5.0.3 以下版本中需要使用固定的 TEXT 或 BLOB 格式存放的資料可以在高版本中使用可變長的 varchar 來存放，這樣就能有效的減少資料庫檔案的大小。（3）text: 與 char 和 varchar 不同的是，text
不可以有預設值，其最大長度是 2 的 16 次方-1 總結起來，有幾點：

經常變化的欄位用 varchar 知道固定長度的用 char 儘量用 varchar 超過 255 字元的只能用 varchar
或者 text 能用 varchar 的地方不用 text

MySQL 建立資料表

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

表名
表欄位名
定義每個表欄位
語法
以下為建立MySQL資料表的SQL通用語法：

CREATE TABLE table_name (column_name column_type);

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

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

例項解析：
如果你不想欄位為 NULL 可以設定欄位的屬性為 NOT NULL，在運算元據庫時如果輸入該欄位的資料為NULL ，就會報錯。
AUTO_INCREMENT定義列為自增的屬性，一般用於主鍵，數值會自動加1。
PRIMARY KEY關鍵字用於定義列為主鍵。您可以使用多列來定義主鍵，列間以逗號分隔。
ENGINE 設定儲存引擎，CHARSET 設定編碼。

通過命令提示符建立表

通過 mysql> 命令視窗可以很簡單的建立MySQL資料表。你可以使用 SQL 語句 CREATE TABLE 來建立資料表。

例項
以下為建立資料表 runoob_tbl 例項:

root@host# mysql -u root -p
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_tbl(
   -> runoob_id INT NOT NULL AUTO_INCREMENT,
   -> runoob_title VARCHAR(100) NOT NULL,
   -> runoob_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( runoob_id )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)
mysql>

注意：MySQL命令終止符為分號 ; 。
注意： -> 是換行符標識

MySQL 刪除資料表

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

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

DROP TABLE table_name ;

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

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

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

MySQL 插入資料

MySQL 表中使用 INSERT INTO SQL語句來插入資料。

你可以通過 mysql> 命令提示視窗中向資料表中插入資料，或者通過PHP指令碼來插入資料。

語法
以下為向MySQL資料表插入資料通用的 INSERT INTO SQL語法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

如果資料是字元型，必須使用單引號或者雙引號，如：“value”。

通過命令提示視窗插入資料
以下我們將使用 SQL INSERT INTO 語句向 MySQL 資料表 run_tbl 插入資料

例項
以下例項中我們將向 run_tbl 表插入三條資料:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use RUNB;
Database changed
mysql> INSERT INTO run_tbl 
    -> (runb_title, runb_author, submission_date)
    -> VALUES
    -> ("學習", "教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runb_tbl
    -> (runb_title, runb_author, submission_date)
    -> VALUES
    -> ("MySQL", "教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runb_tbl
    -> (runb_title, runb_author, submission_date)
    -> VALUES
    -> ("JAVA", "RUNB.COM", '2016-05-06');
Query OK, 1 rows affected (0.00 sec)
mysql>

注意：使用箭頭標記 -> 不是 SQL 語句的一部分，它僅僅表示一個新行，如果一條SQL語句太長，我們可以通過Enter鍵來建立一個新行來編寫 SQL 語句，SQL 語句的命令結束符為分號 ;。

在以上例項中，我們並沒有提供 runb_id 的資料，因為該欄位我們在建立表的時候已經設定它為 AUTO_INCREMENT(自動增加) 屬性。所以，該欄位會自動遞增而不需要我們去設定。例項中 NOW() 是一個 MySQL 函式，該函式返回日期和時間。

接下來我們可以通過以下語句檢視資料表資料：

讀取資料表：

select * from runoob_tbl;

MySQL 查詢資料

MySQL 資料庫使用SQL SELECT語句來查詢資料。

你可以通過 mysql> 命令提示視窗中在資料庫中查詢資料，或者通過PHP指令碼來查詢資料。

語法
以下為在MySQL資料庫中查詢資料通用的 SELECT 語法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

查詢語句中你可以使用一個或者多個表，表之間使用逗號(,)分割，並使用WHERE語句來設定查詢條件。
SELECT 命令可以讀取一條或者多條記錄。
你可以使用星號（*）來代替其他欄位，SELECT語句會返回表的所有欄位資料
你可以使用 WHERE 語句來包含任何條件。
你可以使用 LIMIT 屬性來設定返回的記錄數。
你可以通過OFFSET指定SELECT語句開始查詢的資料偏移量。預設情況下偏移量為0。

MySQL 操作基礎語句

MySQL WHERE 子句

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

如需有條件地從表中選取資料，可將 WHERE 子句新增到 SELECT 語句中。

語法
以下是 SQL SELECT 語句使用 WHERE 子句從資料表中讀取資料的通用語法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

查詢語句中你可以使用一個或者多個表，表之間使用逗號, 分割，並使用WHERE語句來設定查詢條件。
你可以在 WHERE 子句中指定任何條件。
你可以使用 AND 或者 OR 指定一個或多個條件。
WHERE 子句也可以運用於 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句類似於程式語言中的 if 條件，根據 MySQL 表中的欄位值來讀取指定的資料。
以下為操作符列表，可用於 WHERE 子句中。

下表中例項假定 A 為 10, B 為 20
在這裡插入圖片描述
如果我們想在 MySQL 資料表中讀取指定的資料，WHERE 子句是非常有用的。

使用主鍵來作為 WHERE 子句的條件查詢是非常快速的。

如果給定的條件在表中沒有任何匹配的記錄，那麼查詢不會返回任何資料。

MySQL UPDATE 更新

如果我們需要修改或更新 MySQL 中的資料，我們可以使用 SQL UPDATE 命令來操作。

語法
以下是 UPDATE 命令修改 MySQL 資料表資料的通用 SQL 語法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

你可以同時更新一個或多個欄位。
你可以在 WHERE 子句中指定任何條件。
你可以在一個單獨表中同時更新資料。
當你需要更新資料表中指定行的資料時 WHERE 子句是非常有用的。

MySQL DELETE 語句

你可以使用 SQL 的 DELETE FROM 命令來刪除 MySQL 資料表中的記錄。

你可以在 mysql> 命令提示符或 PHP 指令碼中執行該命令。

語法
以下是 SQL DELETE 語句從 MySQL 資料表中刪除資料的通用語法：

DELETE FROM table_name [WHERE Clause]

如果沒有指定 WHERE 子句，MySQL 表中的所有記錄將被刪除。
你可以在 WHERE 子句中指定任何條件
您可以在單個表中一次性刪除記錄。
當你想刪除資料表中指定的記錄時 WHERE 子句是非常有用的。

MySQL LIKE 子句

我們知道在 MySQL 中使用 SQL SELECT 命令來讀取資料，同時我們可以在 SELECT 語句中使用 WHERE 子句來獲取指定的記錄。

WHERE 子句中可以使用等號 = 來設定獲取資料的條件，如 “runoob_author = ‘RUNOOB.COM’”。

但是有時候我們需要獲取 runoob_author 欄位含有 “COM” 字元的所有記錄，這時我們就需要在 WHERE 子句中使用 SQL LIKE 子句。

SQL LIKE 子句中使用百分號 %字元來表示任意字元，類似於UNIX或正規表示式中的星號 *。

如果沒有使用百分號 %, LIKE 子句與等號 = 的效果是一樣的。

語法
以下是 SQL SELECT 語句使用 LIKE 子句從資料表中讀取資料的通用語法：

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

你可以在 WHERE 子句中指定任何條件。
你可以在 WHERE 子句中使用LIKE子句。
你可以使用LIKE子句代替等號 =。
LIKE 通常與 % 一同使用，類似於一個元字元的搜尋。
你可以使用 AND 或者 OR 指定一個或多個條件。
你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句來指定條件。

MySQL UNION 操作符

描述
MySQL UNION 操作符用於連線兩個以上的 SELECT 語句的結果組合到一個結果集合中。多個 SELECT 語句會刪除重複的資料。

語法
MySQL UNION 操作符語法格式：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

引數

expression1, expression2, … expression_n: 要檢索的列。

tables: 要檢索的資料表。

WHERE conditions: 可選，檢索條件。

DISTINCT: 可選，刪除結果集中重複的資料。預設情況下 UNION 操作符已經刪除了重複資料，所以 DISTINCT
修飾符對結果沒啥影響。

ALL: 可選，返回所有結果集，包含重複資料。

演示資料庫
在本教程中，我們將使用樣本資料庫。

下面是選自 “Websites” 表的資料：

mysql> SELECT * FROM Websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘寶         | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鳥教程      | http://www.runoob.com/   | 4689  | CN      |
| 4  | 微博         | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/| 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/|   0  | IND     |
+----+---------------+---------------------------+-------+---------+

下面是 “apps” APP 的資料：

mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name   | url                     | country |
+----+------------+-------------------------+---------+
|  1 | QQ APP     | http://im.qq.com/       | CN      |
|  2 | 微博 APP   | http://weibo.com/       | CN      |
|  3 | 淘寶 APP   | https://www.taobao.com/ | CN      |
+----+------------+-------------------------+---------+
3 rows in set (0.00 sec)

SQL UNION 例項
下面的 SQL 語句從 “Websites” 和 “apps” 表中選取所有不同的country（只有不同的值）：

例項

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

執行以上 SQL 輸出結果如下：
在這裡插入圖片描述

註釋：UNION 不能用於列出兩個表中所有的country。如果一些網站和APP來自同一個國家，每個國家只會列出一次。UNION 只會選取不同的值。請使用 UNION ALL 來選取重複的值！

SQL UNION ALL 例項
下面的 SQL 語句使用 UNION ALL 從 “Websites” 和 “apps” 表中選取所有的country（也有重複的值）：

例項

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

執行以上 SQL 輸出結果如下：
在這裡插入圖片描述

帶有 WHERE 的 SQL UNION ALL
下面的 SQL 語句使用 UNION ALL 從 “Websites” 和 “apps” 表中選取所有的中國(CN)的資料（也有重複的值）：

例項

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

執行以上 SQL 輸出結果如下：
在這裡插入圖片描述

UNION 語句：用於將不同表中相同列中查詢的資料展示出來；（不包括重複資料）

UNION ALL 語句：用於將不同表中相同列中查詢的資料展示出來；（包括重複資料）

使用形式如下：

SELECT 列名稱 FROM 表名稱 UNION SELECT 列名稱 FROM 表名稱 ORDER BY 列名稱；
SELECT 列名稱 FROM 表名稱 UNION ALL SELECT 列名稱 FROM 表名稱 ORDER BY 列名稱；

MySQL 排序

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

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

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

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

你可以使用任何欄位來作為排序的條件，從而返回排序後的查詢結果。
你可以設定多個欄位來排序。
你可以使用 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 相反，用於獲取右表所有記錄，即使左表沒有對應匹配的記錄。
在這裡插入圖片描述

MySQL left join 與 join 有所不同。 MySQL LEFT JOIN 會讀取左邊資料表的全部資料，即便右邊表無對應資料。
在這裡插入圖片描述

以上例項中使用了 RIGHT JOIN，該語句會讀取右邊的資料表 tcount_tbl 的所有選取的欄位資料，即便在左側表 runoob_tbl 中沒有對應的runoob_author 欄位值。
在這裡插入圖片描述

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 運算子。

注意：

select * , columnName1+ifnull(columnName2,0) from tableName;

columnName1，columnName2 為 int 型，當 columnName2 中，有值為 null 時，columnName1+columnName2=null， ifnull(columnName2,0) 把 columnName2 中 null 值轉為 0。

MySQL 正規表示式

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

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

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

下表中的正則模式可應用於 REGEXP 操作符中。
在這裡插入圖片描述
例項
瞭解以上的正則需求後，我們就可以根據自己的需求來編寫帶有正規表示式的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 '^ok$';

Mysql 基礎架構

MySQL 事務

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

在 MySQL 中只有使用了 Innodb 資料庫引擎的資料庫或表才支援事務。
事務處理可以用來維護資料庫的完整性，保證成批的 SQL 語句要麼全部執行，要麼全部不執行。
事務用來管理 insert,update,delete 語句
一般來說，事務是必須滿足4個條件（ACID）：：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、永續性（Durability）。

原子性：一個事務（transaction）中的所有操作，要麼全部完成，要麼全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。

一致性：在事務開始之前和事務結束以後，資料庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及後續資料庫可以自發性地完成預定的工作。

隔離性：資料庫允許多個併發事務同時對其資料進行讀寫和修改的能力，隔離性可以防止多個事務併發執行時由於交叉執行而導致資料的不一致。事務隔離分為不同級別，包括讀未提交（Read
uncommitted）、讀提交（read committed）、可重複讀（repeatable
read）和序列化（Serializable）。

永續性：事務處理結束後，對資料的修改就是永久的，即便系統故障也不會丟失。

在 MySQL 命令列的預設設定下，事務都是自動提交的，即執行 SQL 語句後就會馬上執行 COMMIT 操作。因此要顯式地開啟一個事務務須使用命令 BEGIN 或 START TRANSACTION，或者執行命令 SET AUTOCOMMIT=0，用來禁止使用當前會話的自動提交。

事務控制語句：

BEGIN 或 START TRANSACTION 顯式地開啟一個事務；

COMMIT 也可以使用 COMMIT WORK，不過二者是等價的。COMMIT 會提交事務，並使已對資料庫進行的所有修改成為永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不過二者是等價的。回滾會結束使用者的事務，並撤銷正在進行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允許在事務中建立一個儲存點，一個事務中可以有多個 SAVEPOINT；

RELEASE SAVEPOINT identifier 刪除一個事務的儲存點，當沒有指定的儲存點時，執行該語句會丟擲一個異常；

ROLLBACK TO identifier 把事務回滾到標記點；

SET TRANSACTION 用來設定事務的隔離級別。InnoDB 儲存引擎提供事務的隔離級別有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事務處理主要有兩種方法：

1、用 BEGIN, ROLLBACK, COMMIT來實現

BEGIN 開始一個事務
ROLLBACK 事務回滾
COMMIT 事務確認
2、直接用 SET 來改變 MySQL 的自動提交模式:

SET AUTOCOMMIT=0 禁止自動提交
SET AUTOCOMMIT=1 開啟自動提交

事務測試
mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 建立資料表
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  # 開始事務
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事務
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 開始事務
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滾
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   # 因為回滾所以資料沒有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql>

使用保留點 SAVEPOINT

savepoint 是在資料庫事務處理中實現“子事務”（subtransaction），也稱為巢狀事務的方法。事務可以回滾到
savepoint 而不影響 savepoint 建立前的變化, 不需要放棄整個事務。

ROLLBACK 回滾的用法可以設定保留點 SAVEPOINT，執行多條操作時，回滾到想要的那條語句之前。

使用 SAVEPOINT

SAVEPOINT savepoint_name; // 宣告一個 savepoint

ROLLBACK TO savepoint_name; // 回滾到savepoint 刪除 SAVEPOINT

保留點再事務處理完成（執行一條 ROLLBACK 或 COMMIT）後自動釋放。

MySQL5 以來，可以用:

RELEASE SAVEPOINT savepoint_name; // 刪除指定保留點

MySQL ALTER命令

當我們需要修改資料表名或者修改資料表欄位時，就需要使用到MySQL ALTER命令。
如果你需要指定新增欄位的位置，可以使用MySQL提供的關鍵字 FIRST (設定位第一列)， AFTER 欄位名（設定位於某個欄位之後）。

嘗試以下 ALTER TABLE 語句, 在執行成功後，使用 SHOW COLUMNS 檢視錶結構的變化：

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

FIRST 和 AFTER 關鍵字可用於 ADD 與 MODIFY 子句，所以如果你想重置資料表欄位的位置就需要先使用 DROP 刪除欄位然後使用 ADD 來新增欄位並設定位置。

如果需要修改欄位型別及名稱, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句。

例如，把欄位 c 的型別從 CHAR(1) 改為 CHAR(10)，可以執行以下命令:

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

使用 CHANGE 子句, 語法有很大的不同。在 CHANGE 關鍵字之後，緊跟著的是你要修改的欄位名，然後指定新欄位名及型別。嘗試如下例項：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE 對 Null 值和預設值的影響
當你修改欄位時，你可以指定是否包含值或者是否設定預設值。

以下例項，指定欄位 j 為 NOT NULL 且預設值為100 。

mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

如果你不設定預設值，MySQL會自動設定該欄位預設為 NULL。
修改表名
如果需要修改資料表的名稱，可以在 ALTER TABLE 語句中使用 RENAME 子句來實現。

嘗試以下例項將資料表 testalter_tbl 重新命名為 alter_tbl：

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

alter其他用途：

修改儲存引擎：修改為myisam

alter table tableName engine=myisam;

刪除外來鍵約束：keyName是外來鍵別名

alter table tableName drop foreign key keyName;

修改欄位的相對位置：這裡name1為想要修改的欄位，type1為該欄位原來型別，first和after二選一，這應該顯而易見，first放在第一位，after放在name2欄位後面

alter table tableName modify name1 type1 first|after name2;

MySQL 索引

MySQL索引的建立對於MySQL的高效執行是很重要的，索引可以大大提高MySQL的檢索速度。

打個比方，如果合理的設計且使用索引的MySQL是一輛蘭博基尼的話，那麼沒有設計和使用索引的MySQL就是一個人力三輪車。

拿漢語字典的目錄頁（索引）打比方，我們可以按拼音、筆畫、偏旁部首等排序的目錄（索引）快速查詢到需要的字。

索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索引包含多個列。

建立索引時，你需要確保該索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。

實際上，索引也是一張表，該表儲存了主鍵與索引欄位，並指向實體表的記錄。

上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案。

建立索引會佔用磁碟空間的索引檔案。

普通索引

建立索引
這是最基本的索引，它沒有任何限制。它有以下幾種建立方式：

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR型別，length可以小於欄位實際長度；如果是BLOB和TEXT型別，必須指定 length。

修改表結構(新增索引)

ALTER table tableName ADD INDEX indexName(columnName)

建立表的時候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);

刪除索引的語法

DROP INDEX [indexName] ON mytable;

唯一索引

它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種建立方式：

建立索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表結構

ALTER table mytable ADD UNIQUE [indexName] (username(length))

建立表的時候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);

使用ALTER 命令新增和刪除索引

有四種方式來新增資料表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):
該語句新增一個主鍵，這意味著索引值必須是唯一的，且不能為NULL。 ALTER TABLE tbl_name ADD UNIQUE
index_name (column_list): 這條語句建立索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）。 ALTER
TABLE tbl_name ADD INDEX index_name (column_list): 新增普通索引，索引值可出現多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):該語句指定了索引為
FULLTEXT ，用於全文索引。

以下例項為在表中新增索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你還可以在 ALTER 命令中使用 DROP 子句來刪除索引。嘗試以下例項刪除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令新增和刪除主鍵

主鍵只能作用於一個列上，新增主鍵索引時，你需要確保該主鍵預設不為空（NOT NULL）。例項如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令刪除主鍵：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

刪除主鍵時只需指定PRIMARY KEY，但在刪除索引時，你必須知道索引名。

顯示索引資訊

你可以使用 SHOW INDEX 命令來列出表中的相關的索引資訊。可以通過新增 \G 來格式化輸出資訊。

嘗試以下例項:

mysql> SHOW INDEX FROM table_name; \G
........

MySQL 臨時表

MySQL 臨時表在我們需要儲存一些臨時資料時是非常有用的。臨時表只在當前連線可見，當關閉連線時，Mysql會自動刪除表並釋放所有空間。

臨時表在MySQL 3.23版本中新增，如果你的MySQL版本低於 3.23版本就無法使用MySQL的臨時表。不過現在一般很少有再使用這麼低版本的MySQL資料庫服務了。

MySQL臨時表只在當前連線可見，如果你使用PHP指令碼來建立MySQL臨時表，那每當PHP指令碼執行完成後，該臨時表也會自動銷燬。

如果你使用了其他MySQL客戶端程式連線MySQL資料庫伺服器來建立臨時表，那麼只有在關閉客戶端程式時才會銷燬臨時表，當然你也可以手動銷燬。

例項
以下展示了使用MySQL 臨時表的簡單例項，以下的SQL程式碼可以適用於PHP指令碼的mysql_query()函式。

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

當你使用 SHOW TABLES命令顯示資料表列表時，你將無法看到 SalesSummary表。

如果你退出當前MySQL會話，再使用 SELECT命令來讀取原先建立的臨時表資料，那你會發現資料庫中沒有該表的存在，因為在你退出時該臨時表已經被銷燬了。

刪除MySQL 臨時表
預設情況下，當你斷開與資料庫的連線後，臨時表就會自動被銷燬。當然你也可以在當前MySQL會話使用 DROP TABLE 命令來手動刪除臨時表。

以下是手動刪除臨時表的例項：

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table 'RUNOOB.SalesSummary' doesn't exist

MySQL 複製表

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

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

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

步驟一：

獲取資料表的完整結構。

mysql> SHOW CREATE TABLE runoob_tbl \G;
*************************** 1. row ***************************
       Table: runoob_tbl
Create Table: CREATE TABLE `runoob_tbl` (
  `runoob_id` int(11) NOT NULL auto_increment,
  `runoob_title` varchar(100) NOT NULL default '',
  `runoob_author` varchar(40) NOT NULL default '',
  `submission_date` date default NULL,
  PRIMARY KEY  (`runoob_id`),
  UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB 
1 row in set (0.00 sec)

ERROR:
No query specified

步驟二：

修改SQL語句的資料表名，並執行SQL語句。

mysql> CREATE TABLE `clone_tbl` (
  -> `runoob_id` int(11) NOT NULL auto_increment,
  -> `runoob_title` varchar(100) NOT NULL default '',
  -> `runoob_author` varchar(40) NOT NULL default '',
  -> `submission_date` date default NULL,
  -> PRIMARY KEY  (`runoob_id`),
  -> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (1.80 sec)

步驟三：

執行完第二步驟後，你將在資料庫中建立新的克隆表 clone_tbl。如果你想拷貝資料表的資料你可以使用 INSERT INTO… SELECT 語句來實現。

mysql> INSERT INTO clone_tbl (runoob_id,
    ->runoob_title,
    ->runoob_author,
    ->submission_date)
    -> SELECT runoob_id,runoob_title,
    ->        runoob_author,submission_date
    -> FROM runoob_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

執行以上步驟後，你將完整的複製表，包括表結構及表資料。

第一、只複製表結構到新表
create table 新表 select * from 舊錶 where 1=2
或者
create table 新表 like 舊錶
第二、複製表結構及資料到新表
create table新表 select * from 舊錶

MySQL 後設資料

MySQL 後設資料
你可能想知道MySQL以下三種資訊：

查詢結果資訊： SELECT, UPDATE 或 DELETE語句影響的記錄數。
資料庫和資料表的資訊：包含了資料庫及資料表的結構資訊。
MySQL伺服器資訊：包含了資料庫伺服器的當前狀態，版本號等。
在MySQL的命令提示符中，我們可以很容易的獲取以上伺服器資訊。但如果使用Perl或PHP等指令碼語言，你就需要呼叫特定的介面函式來獲取。接下來我們會詳細介紹。

獲取查詢語句影響的記錄數
PERL 例項
在 DBI 指令碼中，語句影響的記錄數通過函式 do( ) 或 execute( )返回：

# 方法 1
# 使用do( ) 執行  $query 
my $count = $dbh->do ($query);
# 如果發生錯誤會輸出 0
printf "%d 條資料被影響\n", (defined ($count) ? $count : 0);

# 方法 2
# 使用prepare( ) 及 execute( ) 執行  $query 
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d 條資料被影響\n", (defined ($count) ? $count : 0);

資料庫和資料表列表
你可以很容易的在MySQL伺服器中獲取資料庫和資料表列表。如果你沒有足夠的許可權，結果將返回 null。

你也可以使用 SHOW TABLES 或 SHOW DATABASES 語句來獲取資料庫和資料表列表。

PERL 例項

# 獲取當前資料庫中所有可用的表。
my @tables = $dbh->tables ( );
foreach $table (@tables ){
   print "表名 $table\n";
}

MySQL 序列使用

MySQL 序列是一組整數：1, 2, 3, …，由於一張資料表只能有一個欄位自增主鍵，如果你想實現其他欄位也實現自動增加，就可以使用MySQL序列來實現。

本章我們將介紹如何使用MySQL的序列。

使用 AUTO_INCREMENT
MySQL 中最簡單使用序列的方法就是使用 MySQL AUTO_INCREMENT 來定義列。

例項
以下例項中建立了資料表 insect， insect 表中 id 無需指定值可實現自動增長。

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES
    -> (NULL,'housefly','2001-09-10','kitchen'),
    -> (NULL,'millipede','2001-09-10','driveway'),
    -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

獲取AUTO_INCREMENT值
在MySQL的客戶端中你可以使用 SQL中的LAST_INSERT_ID( ) 函式來獲取最後的插入表中的自增列的值。

在PHP或PERL指令碼中也提供了相應的函式來獲取最後的插入表中的自增列的值。

PERL例項
使用 mysql_insertid 屬性來獲取 AUTO_INCREMENT 的值。例項如下：

$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

重置序列
如果你刪除了資料表中的多條記錄，並希望對剩下資料的AUTO_INCREMENT列進行重新排列，那麼你可以通過刪除自增的列，然後重新新增來實現。不過該操作要非常小心，如果在刪除的同時又有新記錄新增，有可能會出現資料混亂。操作如下所示：

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

設定序列的開始值
一般情況下序列的開始值為1，但如果你需要指定一個開始值100，那我們可以通過以下語句來實現：

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, 
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;
或者你也可以在表建立成功後，通過以下語句來實現：

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

MySQL 處理重複資料

MySQL 處理重複資料
有些 MySQL 資料表中可能存在重複的記錄，有些情況我們允許重複資料的存在，但有時候我們也需要刪除這些重複的資料。

本章節我們將為大家介紹如何防止資料表出現重複資料及如何刪除資料表中的重複資料。

防止表中出現重複資料
你可以在 MySQL 資料表中設定指定的欄位為 PRIMARY KEY（主鍵）或者 UNIQUE（唯一）索引來保證資料的唯一性。
讓我們嘗試一個例項：下表中無索引及主鍵，所以該表允許出現多條重複記錄。

CREATE TABLE person_tbl
(
    first_name CHAR(20),
    last_name CHAR(20),
    sex CHAR(10)
);

如果你想設定表中欄位 first_name，last_name 資料不能重複，你可以設定雙主鍵模式來設定資料的唯一性，如果你設定了雙主鍵，那麼那個鍵的預設值不能為 NULL，可設定為 NOT NULL。如下所示：

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

如果我們設定了唯一索引，那麼在插入重複資料時，SQL 語句將無法執行成功,並丟擲錯。

INSERT IGNORE INTO 與 INSERT INTO 的區別就是 INSERT IGNORE 會忽略資料庫中已經存在的資料，如果資料庫沒有資料，就插入新的資料，如果有資料的話就跳過這條資料。這樣就可以保留資料庫中已經存在資料，達到在間隙中插入資料的目的。

以下例項使用了 INSERT IGNORE INTO，執行後不會出錯，也不會向資料表中插入重複資料：

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

INSERT IGNORE INTO 當插入資料時，在設定了記錄的唯一性後，如果插入重複資料，將不返回錯誤，只以警告形式返回。而 REPLACE INTO 如果存在 primary 或 unique 相同的記錄，則先刪除掉。再插入新記錄。

另一種設定資料的唯一性方法是新增一個 UNIQUE 索引，如下所示：

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   UNIQUE (last_name, first_name)
);

統計重複資料
以下我們將統計表中 first_name 和 last_name的重複記錄數：

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

以上查詢語句將返回 person_tbl 表中重複的記錄數。一般情況下，查詢重複的值，請執行以下操作：

確定哪一列包含的值可能會重複。
在列選擇列表使用COUNT(*)列出的那些列。
在GROUP BY子句中列出的列。
HAVING子句設定重複數大於1。
過濾重複資料
如果你需要讀取不重複的資料可以在 SELECT 語句中使用 DISTINCT 關鍵字來過濾重複資料。

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl;

你也可以使用 GROUP BY 來讀取資料表中不重複的資料：

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);

刪除重複資料
如果你想刪除資料表中的重複資料，你可以使用以下的SQL語句：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

當然你也可以在資料表中新增 INDEX（索引）和 PRIMAY KEY（主鍵）這種簡單的方法來刪除表中的重複記錄。方法如下：

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);

select 列名1，count（1） as count from 表名 group by 列名1 having count>1
and 其他條件

select 列名1，列名2，count（1） as count from 表名 group by 列名1，列名2 having
count>1 and 其他條件原理：先按照要查詢出現重複資料的列，進行分組查詢。count > 1 代表出現 2 次或 2 次以上。

示例：

/查詢重複資料/ select serialnum,cdate,count(*) as count from
m_8_customer_temp_20180820bak group by serialnum,cdate having
count>1 and cdate>=‘2018-08-20 00:00:00’;

Mysql 導外以及函式

MySQL 及 SQL 注入

如果您通過網頁獲取使用者輸入的資料並將其插入一個MySQL資料庫，那麼就有可能發生SQL隱碼攻擊安全的問題。

本章節將為大家介紹如何防止SQL隱碼攻擊，並通過指令碼來過濾SQL中注入的字元。

所謂SQL隱碼攻擊，就是通過把SQL命令插入到Web表單遞交或輸入域名或頁面請求的查詢字串，最終達到欺騙伺服器執行惡意的SQL命令。

我們永遠不要信任使用者的輸入，我們必須認定使用者輸入的資料都是不安全的，我們都需要對使用者輸入的資料進行過濾處理。

以下例項中，輸入的使用者名稱必須為字母、數字及下劃線的組合，且使用者名稱長度為 8 到 20 個字元之間：

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
{
   $result = mysqli_query($conn, "SELECT * FROM users 
                          WHERE username=$matches[0]");
}
 else 
{
   echo "username 輸入異常";
}

讓我們看下在沒有過濾特殊字元時，出現的SQL情況：

// 設定$name 中插入了我們不需要的SQL語句
$name = "Qadir'; DELETE FROM users;";
 mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

以上的注入語句中，我們沒有對$name的變數進行過濾，$name中插入了我們不需要的SQL語句，將刪除 users 表中的所有資料。

在PHP中的 mysqli_query() 是不允許執行多個 SQL 語句的，但是在 SQLite 和 PostgreSQL 是可以同時執行多條SQL語句的，所以我們對這些使用者的資料需要進行嚴格的驗證。

防止SQL隱碼攻擊，我們需要注意以下幾個要點：

1.永遠不要信任使用者的輸入。對使用者的輸入進行校驗，可以通過正規表示式，或限制長度；對單引號和雙"-"進行轉換等。
2.永遠不要使用動態拼裝sql，可以使用引數化的sql或者直接使用儲存過程進行資料查詢存取。
3.永遠不要使用管理員許可權的資料庫連線，為每個應用使用單獨的許可權有限的資料庫連線。
4.不要把機密資訊直接存放，加密或者hash掉密碼和敏感的資訊。
5.應用的異常資訊應該給出儘可能少的提示，最好使用自定義的錯誤資訊對原始錯誤資訊進行包裝
6.sql注入的檢測方法一般採取輔助軟體或網站平臺來檢測，軟體一般採用sql注入檢測工具jsky，網站平臺就有億思網站安全平臺檢測工具。MDCSOFT
SCAN等。採用MDCSOFT-IPS可以有效的防禦SQL隱碼攻擊，XSS攻擊等。

防止SQL隱碼攻擊

在指令碼語言，如Perl和PHP你可以對使用者輸入的資料進行轉義從而來防止SQL隱碼攻擊。

PHP的MySQL擴充套件提供了mysqli_real_escape_string()函式來轉義特殊的輸入字元。

if (get_magic_quotes_gpc()) 
{
  $name = stripslashes($name);
}
$name = mysqli_real_escape_string($conn, $name);
 mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

Like語句中的注入
like查詢時，如果使用者輸入的值有"“和”%"，則會出現這種情況：使用者本來只是想查詢"abcd"，查詢結果中卻有"abcd_"、“abcde”、“abcdf"等等；使用者要查詢"30%”（注：百分之三十）時也會出現問題。

在PHP指令碼中我們可以使用addcslashes()函式來處理以上情況，如下例項：

$sub = addcslashes(mysqli_real_escape_string($conn, "%something_"), "%_");
// $sub == \%something\_
 mysqli_query($conn, "SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

addcslashes() 函式在指定的字元前新增反斜槓。

語法格式:

addcslashes(string,characters)

在這裡插入圖片描述

MySQL 匯出資料

MySQL中你可以使用SELECT…INTO OUTFILE語句來簡單的匯出資料到文字檔案上。

使用 SELECT … INTO OUTFILE 語句匯出資料

以下例項中我們將資料表 runoob_tbl 資料匯出到 /tmp/runoob.txt 檔案中:

mysql> SELECT * FROM runoob_tbl 
    -> INTO OUTFILE '/tmp/runoob.txt';

你可以通過命令選項來設定資料輸出的指定格式，以下例項為匯出 CSV 格式：

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

在下面的例子中，生成一個檔案，各值用逗號隔開。這種格式可以被許多程式使用。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

SELECT … INTO OUTFILE 語句有以下屬性:

LOAD DATA INFILE是SELECT … INTO
OUTFILE的逆操作，SELECT句法。為了將一個資料庫的資料寫入一個檔案，使用SELECT … INTO
OUTFILE，為了將檔案讀回資料庫，使用LOAD DATA INFILE。 SELECT…INTO OUTFILE
'file_name’形式的SELECT可以把被選擇的行寫入一個檔案中。該檔案被建立到伺服器主機上，因此您必須擁有FILE許可權，才能使用此語法。
輸出不能是一個已存在的檔案。防止檔案資料被篡改。你需要有一個登陸伺服器的賬號來檢索檔案。否則 SELECT … INTO
OUTFILE 不會起任何作用。
在UNIX中，該檔案被建立後是可讀的，許可權由MySQL伺服器所擁有。這意味著，雖然你就可以讀取該檔案，但可能無法將其刪除。

匯出表作為原始資料

mysqldump 是 mysql 用於轉儲存資料庫的實用程式。它主要產生一個 SQL 指令碼，其中包含從頭重新建立資料庫所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 匯出資料需要使用 --tab 選項來指定匯出檔案指定的目錄，該目標必須是可寫的。

以下例項將資料表 runoob_tbl 匯出到 /tmp 目錄中：

$ mysqldump -u root -p --no-create-info \
            --tab=/tmp RUNOOB runoob_tbl
password ******

匯出 SQL 格式的資料

匯出 SQL 格式的資料到指定檔案，如下所示：

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******

以上命令建立的檔案內容如下：

-- MySQL dump 8.23
--
-- Host: localhost    Database: RUNOOB
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `runoob_tbl`
--

CREATE TABLE runoob_tbl (
  runoob_id int(11) NOT NULL auto_increment,
  runoob_title varchar(100) NOT NULL default '',
  runoob_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (runoob_id),
  UNIQUE KEY AUTHOR_INDEX (runoob_author)
) TYPE=MyISAM;

--
-- Dumping data for table `runoob_tbl`
--

INSERT INTO runoob_tbl 
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO runoob_tbl 
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO runoob_tbl 
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

如果你需要匯出整個資料庫的資料，可以使用以下命令：

$ mysqldump -u root -p RUNOOB > database_dump.txt
password ******

如果需要備份所有資料庫，可以使用以下命令：

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

–all-databases 選項在 MySQL 3.23.12 及以後版本加入。

該方法可用於實現資料庫的備份策略。

將資料表及資料庫拷貝至其他主機

如果你需要將資料拷貝至其他的 MySQL 伺服器上, 你可以在 mysqldump 命令中指定資料庫名及資料表。

在源主機上執行以下命令，將資料備份至 dump.txt 檔案中:

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

如果完整備份資料庫，則無需使用特定的表名稱。

如果你需要將備份的資料庫匯入到MySQL伺服器中，可以使用以下命令，使用以下命令你需要確認資料庫已經建立：

$ mysql -u root -p database_name < dump.txt
password *****

你也可以使用以下命令將匯出的資料直接匯入到遠端的伺服器上，但請確保兩臺伺服器是相通的，是可以相互訪問的：

$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

以上命令中使用了管道來將匯出的資料匯入到指定的遠端主機上。

將指定主機的資料庫拷貝到本地

如果你需要將遠端伺服器的資料拷貝到本地，你也可以在 mysqldump 命令中指定遠端伺服器的IP、埠及資料庫名。

在源主機上執行以下命令，將資料備份到 dump.txt 檔案中：

請確保兩臺伺服器是相通的：

mysqldump -h other-host.com -P port -u root -p database_name >
dump.txt password ****

在寫出的時候會出現The MySQL server is running with the --secure-file-priv
option so it cannot execute this statement的錯誤解決方法：

出現這個錯誤是因為沒有給資料庫指定寫出檔案的路徑或者寫出的路徑有問題。

首先使用下面的命令 show variables like ‘%secure%’; 檢視資料庫的儲存路徑。如果查出的
secure_file_priv 是 null 的時候就證明在 my.ini 檔案裡面沒有配置寫出路徑。

這時候就可以在 mysql.ini 檔案的 [mysqld] 程式碼下增加 secure_file_priv=E:/TEST 再重啟
mysql 就可以了。然後在匯出的地址下面寫上剛才配置的這個地址 eg: select * from tb_test into
outfile “E:/TEST/test.txt”；就可以了。

MySQL 匯入資料

MySQL 匯入資料
本章節我們為大家介紹幾種簡單的 MySQL 匯入資料命令。

1、mysql 命令匯入

使用 mysql 命令匯入語法格式為：

mysql -u使用者名稱 -p密碼 < 要匯入的資料庫資料(runoob.sql)

例項：

# mysql -uroot -p123456 < runoob.sql

以上命令將將備份的整個資料庫 runoob.sql 匯入。

2、source 命令匯入

source 命令匯入資料庫需要先登入到數庫終端：

mysql> create database abc;      # 建立資料庫
mysql> use abc;                  # 使用已建立的資料庫 
mysql> set names utf8;           # 設定編碼
mysql> source /home/abc/abc.sql  # 匯入備份資料庫

3、使用 LOAD DATA 匯入資料

MySQL 中提供了LOAD DATA INFILE語句來插入資料。以下例項中將從當前目錄中讀取檔案 dump.txt ，將該檔案中的資料插入到當前資料庫的 mytbl 表中。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

如果指定LOCAL關鍵詞，則表明從客戶主機上按路徑讀取檔案。如果沒有指定，則檔案在伺服器上按路徑讀取檔案。

你能明確地在LOAD DATA語句中指出列值的分隔符和行尾標記，但是預設標記是定位符和換行符。

兩個命令的 FIELDS 和 LINES 子句的語法是一樣的。兩個子句都是可選的，但是如果兩個同時被指定，FIELDS 子句必須出現在 LINES 子句之前。

如果使用者指定一個 FIELDS 子句，它的子句（TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可選的，不過，使用者必須至少指定它們中的一個。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
  -> FIELDS TERMINATED BY ':'
  -> LINES TERMINATED BY '\r\n';

LOAD DATA 預設情況下是按照資料檔案中列的順序插入資料的，如果資料檔案中的列與插入表中的列不一致，則需要指定列的順序。

如，在資料檔案中的列順序是 a,b,c，但在插入表的列順序為b,c,a，則資料匯入語法如下：

mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
    -> INTO TABLE mytbl (b, c, a);

4、使用 mysqlimport 匯入資料

mysqlimport 客戶端提供了 LOAD DATA INFILEQL 語句的一個命令列介面。mysqlimport 的大多數選項直接對應 LOAD DATA INFILE 子句。

從檔案 dump.txt 中將資料匯入到 mytbl 資料表中, 可以使用以下命令：

$ mysqlimport -u root -p --local mytbl dump.txt
password *****

mysqlimport 命令可以指定選項來設定指定格式,命令語句格式如下：

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  mytbl dump.txt
password *****

mysqlimport 語句中使用 --columns 選項來設定列的順序：

$ mysqlimport -u root -p --local --columns=b,c,a \
    mytbl dump.txt
password *****

mysqlimport的常用選項介紹

在這裡插入圖片描述

FUNCTION 函式

建立UDF語法

CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name
type,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body

說明：
引數可以有多個,也可以沒有引數
必須有且只有一個返回值

檢視函式列表：

SHOW FUNCTION STATUS;

檢視函式定義

SHOW CREATE FUNCTION function_name

刪除UDF:

DROP FUNCTION function_name

呼叫自定義函式語法:

SELECT function_name(parameter_value,...)

#無參UDF
CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";
#有引數UDF
DELIMITER //
CREATE FUNCTION deleteById(id SMALLINT UNSIGNED) RETURNS VARCHAR(20)
BEGIN
DELETE FROM students WHERE stuid = id;
RETURN (SELECT COUNT(*) FROM students);
END//
DELIMITER ;

自定義函式中定義區域性變數語法

DECLARE 變數1[,變數2,... ]變數型別 [DEFAULT 預設值]

說明：區域性變數的作用範圍是在BEGIN…END程式中,而且定義區域性變數語句必須在BEGIN…END的第一
行定義

DELIMITER //
CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, y SMALLINT UNSIGNED)
RETURNS SMALLINT
BEGIN
DECLARE a, b SMALLINT UNSIGNED;
SET a = x, b = y;
RETURN a+b;
END//
DELIMITER ;

為變數賦值語法

SET parameter_name = value[,parameter_name = value...]
SELECT INTO parameter_name

範例:

DECLARE x int;
SELECT COUNT(*) FROM tdb_name INTO x;
RETURN x;
END//

MySQL 運算子

本章節我們主要介紹 MySQL 的運算子及運算子的優先順序。 MySQL 主要有以下幾種運算子：
算術運算子
比較運算子
邏輯運算子
位運算子

算術運算子

MySQL 支援的算術運算子包括:
在這裡插入圖片描述

1、加

mysql> select 1+2;
+-----+
| 1+2 |
+-----+
|   3 |
+-----+
2、減

mysql> select 1-2;
+-----+
| 1-2 |
+-----+
|  -1 |
+-----+
3、乘

mysql> select 2*3;
+-----+
| 2*3 |
+-----+
|   6 |
+-----+
4、除

mysql> select 2/3;
+--------+
| 2/3    |
+--------+
| 0.6667 |
+--------+
5、商

mysql> select 10 DIV 4;
+----------+
| 10 DIV 4 |
+----------+
|        2 |
+----------+
6、取餘

mysql> select 10 MOD 4;
+----------+
| 10 MOD 4 |
+----------+
|        2 |
+----------+

比較運算子

SELECT 語句中的條件語句經常要使用比較運算子。通過這些比較運算子，可以判斷表中的哪些記錄是符合條件的。比較結果為真，則返回 1，為假則返回 0，比較結果不確定則返回 NULL。
在這裡插入圖片描述

1、等於

mysql> select 2=3;
+-----+
| 2=3 |
+-----+
|   0 |
+-----+


mysql> select NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+
2、不等於

mysql> select 2<>3;
+------+
| 2<>3 |
+------+
|    1 |
+------+
3、安全等於

與 = 的區別在於當兩個操作碼均為 NULL 時，其所得值為 1 而不為 NULL，而當一個操作碼為 NULL 時，其所得值為 0而不為 NULL。

mysql> select 2<=>3;
+-------+
| 2<=>3 |
+-------+
|     0 |
+-------+


mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+

        
mysql> select null<=>null;
+-------------+
| null<=>null |
+-------------+
|           1 |
+-------------+
4、小於

mysql> select 2<3;
+-----+
| 2<3 |
+-----+
|   1 |
+-----+
5、小於等於

mysql> select 2<=3;
+------+
| 2<=3 |
+------+
|    1 |
+------+
6、大於

mysql> select 2>3;
+-----+
| 2>3 |
+-----+
|   0 |
+-----+
7、大於等於

mysql> select 2>=3;
+------+
| 2>=3 |
+------+
|    0 |
+------+
8、BETWEEN

mysql> select 5 between 1 and 10;
+--------------------+
| 5 between 1 and 10 |
+--------------------+
|                  1 |
+--------------------+
9、IN

mysql> select 5 in (1,2,3,4,5);
+------------------+
| 5 in (1,2,3,4,5) |
+------------------+
|                1 |
+------------------+
10、NOT IN

mysql> select 5 not in (1,2,3,4,5);
+----------------------+
| 5 not in (1,2,3,4,5) |
+----------------------+
|                    0 |
+----------------------+
11、IS NULL

mysql> select null is NULL;
+--------------+
| null is NULL |
+--------------+
|            1 |
+--------------+

mysql> select 'a' is NULL;
+-------------+
| 'a' is NULL |
+-------------+
|           0 |
+-------------+
12、IS NOT NULL

mysql> select null IS NOT NULL;
+------------------+
| null IS NOT NULL |
+------------------+
|                0 |
+------------------+

        
mysql> select 'a' IS NOT NULL;
+-----------------+
| 'a' IS NOT NULL |
+-----------------+
|               1 |
+-----------------+
13、LIKE

mysql> select '12345' like '12%';
+--------------------+
| '12345' like '12%' |
+--------------------+
|                  1 |
+--------------------+

mysql> select '12345' like '12_';
+--------------------+
| '12345' like '12_' |
+--------------------+
|                  0 |
+--------------------+
14、REGEXP

mysql> select 'beijing' REGEXP 'jing';
+-------------------------+
| 'beijing' REGEXP 'jing' |
+-------------------------+
|                       1 |
+-------------------------+

mysql> select 'beijing' REGEXP 'xi';
+-----------------------+
| 'beijing' REGEXP 'xi' |
+-----------------------+
|                     0 |
+-----------------------+

邏輯運算子

邏輯運算子用來判斷表示式的真假。如果表示式是真，結果返回 1。如果表示式是假，結果返回 0。
在這裡插入圖片描述

1、與

mysql> select 2 and 0;
+---------+
| 2 and 0 |
+---------+
|       0 |
+---------+

        
mysql> select 2 and 1;   
+---------+     
| 2 and 1 |      
+---------+      
|       1 |      
+---------+
2、或

mysql> select 2 or 0;
+--------+
| 2 or 0 |
+--------+
|      1 |
+--------+

mysql> select 2 or 1;
+--------+
| 2 or 1 |
+--------+
|      1 |
+--------+

mysql> select 0 or 0;
+--------+
| 0 or 0 |
+--------+
|      0 |
+--------+

mysql> select 1 || 0;
+--------+
| 1 || 0 |
+--------+
|      1 |
+--------+
3、非

mysql> select not 1;
+-------+
| not 1 |
+-------+
|     0 |
+-------+

mysql> select !0;
+----+
| !0 |
+----+
|  1 |
+----+
4、異或

mysql> select 1 xor 1;
+---------+
| 1 xor 1 |
+---------+
|       0 |
+---------+

mysql> select 0 xor 0;
+---------+
| 0 xor 0 |
+---------+
|       0 |
+---------+

mysql> select 1 xor 0;
+---------+
| 1 xor 0 |
+---------+
|       1 |
+---------+

mysql> select null or 1;
+-----------+
| null or 1 |
+-----------+
|         1 |
+-----------+

mysql> select 1 ^ 0;
+-------+
| 1 ^ 0 |
+-------+
|     1 |
+-------+

位運算子

位運算子是在二進位制數上進行計算的運算子。位運算會先將運算元變成二進位制數，進行位運算。然後再將計算結果從二進位制數變回十進位制數。
在這裡插入圖片描述

1、按位與

mysql> select 3&5;
+-----+
| 3&5 |
+-----+
|   1 |
+-----+
2、按位或

mysql> select 3|5;
+-----+
| 3|5 |
+-----+
|   7 |
+-----+
3、按位異或

mysql> select 3^5;
+-----+
| 3^5 |
+-----+
|   6 |
+-----+
4、按位取反

mysql> select ~18446744073709551612;
+-----------------------+
| ~18446744073709551612 |
+-----------------------+
|                     3 |
+-----------------------+
5、按位右移

mysql> select 3>>1;
+------+
| 3>>1 |
+------+
|    1 |
+------+
6、按位左移

mysql> select 3<<1;
+------+
| 3<<1 |
+------+
|    6 |
+------+

在這裡插入圖片描述

Django基礎教程之介紹
2021-04-05
Django
《MySQL 8 DBA基礎教程》簡介
2022-07-01
MySql
重新整理 mysql 基礎篇————— 介紹mysql[一]
2021-06-09
MySql
關於mysql基礎知識的介紹
2021-09-09
MySql
Pandas基礎介紹
2020-05-06
Elasticsearch 基礎介紹
2021-04-04
Elasticsearch
kafka 基礎介紹
2017-11-16
Kafka
kafka基礎介紹
2017-11-16
Kafka
重新整理 mysql 基礎篇————— 介紹mysql日誌[二]
2021-06-11
MySql
《Android Tablet 3程式設計基礎教程》介紹
2012-04-05
Android程式設計
ActiveMq的基礎介紹
2020-12-14
MQ
JDBC的基礎介紹
2020-12-03
JDBC
基礎 RAID 介紹
2015-01-29
AI
java基礎介紹（轉）
2016-01-15
Java
1、Oracle 基礎介紹
2024-04-14
Oracle
Flink基礎教程：FlinkX RDB介紹與基本演示
2021-03-29
HTML5培訓教程：HTML5基礎介紹
2021-05-18
HTML
OSPF介紹及基礎配置
2019-10-11
ConstraintLayout基礎介紹
2017-09-19
AI
Epub基礎知識介紹
2013-09-07
HT for Web基礎動畫介紹
2015-08-03
Web動畫
分割槽表基礎介紹
2011-06-16
資料倉儲基礎介紹
2018-07-30
02-ASP.Net(基礎介紹)
2020-10-04
ASP.NET
Xtrabackup介紹和使用【基礎篇】
2014-10-27
Spark Streaming基礎概念介紹
2016-01-29
Spark
SSH Config教程之基礎知識介紹-必備技巧
2020-11-23
MySQL-基礎語法教程
2024-05-07
MySql
ComfyUI 基礎教程（二） —— Stable Diffusion 文生圖基礎工作流及模型、常用節點介紹
2024-09-06
UI模型
Xamarin.FormsShell基礎教程（7）Shell專案關於頁面的介紹
2019-12-08
ORM
什麼是Ansible?特性及優點介紹！Linux基礎教程
2021-08-03
Linux
Jumpserver基礎運維-01介紹
2020-03-24
Server運維
金融反欺詐-交易基礎介紹
2020-08-19
Android RxJava：基礎介紹與使用
2019-03-28
AndroidRxJava
【重溫基礎】17.WebAPI介紹
2019-02-16
WebAPI
資料庫基礎知識介紹！
2022-04-18
資料庫
Linux shell 指令碼基礎介紹
2021-07-11
Linux指令碼
[java基礎]之JDK目錄介紹
2021-02-07
JavaJDK

MySQL 教程基礎介紹

MySQL 教程基礎介紹

什麼是資料庫？

RDBMS 術語

MySQL資料庫

管理MySQL的命令

MySQL 資料庫基本操作

MySQL 建立資料庫

MySQL 刪除資料庫

MySQL 資料型別

MySQL 建立資料表

MySQL 建立資料表

通過命令提示符建立表

MySQL 刪除資料表

MySQL 插入資料

MySQL 查詢資料

MySQL 操作基礎語句

MySQL WHERE 子句

MySQL UPDATE 更新

MySQL DELETE 語句

MySQL LIKE 子句

MySQL UNION 操作符

MySQL 排序

MySQL GROUP BY 語句

MySQL 連線的使用

MySQL NULL 值處理

MySQL 正規表示式

Mysql 基礎架構

MySQL 事務

事務控制語句：

MYSQL 事務處理主要有兩種方法：

MySQL ALTER命令

MySQL 索引

普通索引

唯一索引

使用ALTER 命令新增和刪除索引

使用 ALTER 命令新增和刪除主鍵

顯示索引資訊

MySQL 臨時表

MySQL 複製表

MySQL 後設資料

MySQL 序列使用

MySQL 處理重複資料

Mysql 導外以及函式

MySQL 及 SQL 注入

防止SQL隱碼攻擊

MySQL 匯出資料

使用 SELECT … INTO OUTFILE 語句匯出資料

SELECT … INTO OUTFILE 語句有以下屬性:

匯出表作為原始資料

匯出 SQL 格式的資料

將資料表及資料庫拷貝至其他主機

MySQL 匯入資料

1、mysql 命令匯入

2、source 命令匯入

3、使用 LOAD DATA 匯入資料

4、使用 mysqlimport 匯入資料

mysqlimport的常用選項介紹

FUNCTION 函式

MySQL 運算子

算術運算子

比較運算子

邏輯運算子

位運算子

相關文章