mysql快速了解

文章目錄

- 數據庫了解：
- 快速操作：
- 安裝mysql
- 啟動,關閉,重啟mysql服務
- 連接mysql的root用戶
- 創建數據庫
- 刪除數據庫
- 選擇數據庫
- mysql 數據類型
- MySQL 創建數據表
- MySQL 刪除數據表
- MySQL 插入數據
- MySQL 查詢數據
- MySQL WHERE 子句
- - - BINARY 關鍵字
- MySQL UPDATE 更新
- - 批量更新1：使用case when
  - 批量更新2：創建臨時表，先更新臨時表，然后從臨時表中update
  - - 1.創建臨時表
    - - 1.1 首先獲取原表中的字段結構：
      - 1.2 創建臨時表
    - 2.在臨時表中插入要修改的數據
    - 3.更新原表
    - - 3.1 我們先更新了runoob_title字段：
      - 3.2 接著更新runoob_author字段
      - 3.3接著更新submission_date字段
- MySQL DELETE 語句
- MySQL LIKE 子句
- MySQL UNION 操作符
- - - SQL UNION 實例
    - SQL UNION ALL 實例
    - 帶有 WHERE 的 SQL UNION ALL
- MySQL 排序
- MySQL GROUP BY 語句
- - - 使用 WITH ROLLUP
- Mysql 連接的使用
- - - INNER JOIN
    - LEFT JOIN
    - RIGHT JOIN
- MySQL NULL 值處理
- - - MySQL 中處理 NULL 使用 IS NULL 和 IS NOT NULL 運算符。
- MySQL 事務
- - - 使用保留點 SAVEPOINT
- MySQL ALTER命令
- - - 1.刪除，添加或修改表字段
    - - 1.1指定新增字段的位置
    - 2.修改字段類型及名稱
    - - 2.1修改字段類型
    - 3.修改字段默認值
    - 4.修改表名
- MySQL 索引
- - - 普通索引
    - - 創建索引
      - 修改表結構(添加索引)
      - 創建表的時候直接指定
    - 刪除索引的語法
    - 唯一索引
    - - 創建索引
      - 修改表結構
      - 創建表的時候直接指定
    - 使用ALTER 命令添加和刪除索引
    - 顯示索引信息
- MySQL 臨時表
- - - 刪除MySQL 臨時表
    - 臨時表的應用場景
    - - 1.批量更新數據
- MySQL 復制表
- - - 步驟一：獲取數據表的完整結構。
    - 步驟二：修改SQL語句的數據表名，并執行SQL語句。
    - - 步驟三：拷貝全部數據
- MySQL 元數據
- MySQL 序列使用
- - - 使用 AUTO_INCREMENT
    - 獲取AUTO_INCREMENT值
    - 重置序列
    - 設置序列的開始值
- MySQL 處理重復數據
- - - 防止表中出現重復數據
    - - 方法1：設置主鍵
      - 方法2 ：設置數據的唯一性方法是添加一個 UNIQUE 索引
    - 統計重復數據
    - 過濾重復數據
    - 刪除重復數據
- MySQL 運算符
- 級聯刪除和級聯更新
- - 1.建立兩張表stu和sc
  - 分別插入數據
  - 級聯刪除：將stu表中id為2的學生刪除，該學生在sc表中的成績也會級聯刪除
  - 級聯更新：stu表中id為3的學生更改為id為6，該學生在sc表中的對應id也會級聯更新
  - 注意
- 參考：

數據庫了解：

SQL語言包括四種主要程序設計語言類別的語句：數據定義語言(DDL)，數據操作語言(DML)，數據控制語言(DCL)和事務控制語言（TCL）

快速操作：

展示某個表中的所有字段信息：
show columns from testalter_tb1;

安裝mysql

sudo apt-get update
sudo apt-get -y mysql-server mysql-client
若上述步驟出現失敗:
參考:https://blog.csdn.net/weixx3/article/details/80782479
或者參考:崔慶才網絡爬蟲第29頁.
-重置mysql root用戶的密碼,參考:https://www.cnblogs.com/woshimrf/p/ubuntu-install-mysql.html

啟動,關閉,重啟mysql服務

sudo service mysql start/stop/restart

連接mysql的root用戶

mysql -u root -p
接著輸入密碼:'123456'

或者連接遠程數據庫：

mysql -h0.0.0.0 -u root -p

創建數據庫

create database runoob;

刪除數據庫

drop database runoob;

選擇數據庫

use runoob;

mysql 數據類型

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

參考：https://www.runoob.com/mysql/mysql-data-types.html

MySQL 創建數據表

CREATE TABLE table_name (column_name column_type);
例子：

  CREATE TABLE IF NOT EXISTS `runoob_tb1`(`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 刪除數據表

DROP TABLE table_name ;

例子：

DROP TABLE runoob_tb1

MySQL 插入數據

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

例子：

insert into runoob_tbl (runoob_title,runoob_author, submission_date) 
values ("學習 mysql","菜鳥",NOW());

檢查一下：

select * from runoob_tbl;

MySQL 查詢數據

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

例子：

select * from runoob_tbl;

MySQL WHERE 子句

我們知道從 MySQL 表中使用 SQL SELECT 語句來讀取數據。
如需有條件地從表中選取數據，可將 WHERE 子句添加到 SELECT 語句中。

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

例子：

SELECT * from runoob_tbl WHERE runoob_author='菜鳥教程';

BINARY 關鍵字

MySQL 的 WHERE 子句的字符串比較是不區分大小寫的。你可以使用 BINARY 關鍵字來設定 WHERE 子句的字符串比較是區分大小寫的。
如下實例:
BINARY 關鍵字:

mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author='runoob.com';

MySQL UPDATE 更新

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

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

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

你可以同時更新一個或多個字段。
你可以在 WHERE 子句中指定任何條件。
你可以在一個單獨表中同時更新數據。

例子：

UPDATE runoob_tbl SET runoob_title='學習 C++' WHERE runoob_id=3;

批量更新1：使用case when

例子：
修改前：

runoob_id	runoob_title	runoob_author	submission_date
1	學習 PHP	菜鳥教程	2017-04-12
2	學習 MySQL	菜鳥教程	2017-04-12
3	學習 Java	RUNOOB.COM	2015-05-01
4	學習 Python	RUNOOB.COM	2016-03-06
5	學習 C	FK	2017-04-05

修改了兩個字段（runoob_title和runoob_author）中的信息

修改后：

runoob_id	runoob_title	runoob_author	submission_date
1	php	菜鳥	2017-04-12
2	mysql	菜鳥2	2017-04-12
3	java	runoob.com	2015-05-01
4	學習 Python	RUNOOB.COM	2016-03-06
5	學習 C	FK	2017-04-05

update runoob_tbl 
set runoob_author = case runoob_id   when 1 then '菜鳥'   when 2 then '菜鳥2'  when 3 then 'runoob.com' end, runoob_title= case runoob_id  when 1 then 'php'   when 2 then 'mysql'   when 3 then 'java' end 
where runoob_id in (1,2,3);

批量更新2：創建臨時表，先更新臨時表，然后從臨時表中update

修改前：

runoob_id	runoob_title	runoob_author	submission_date
1	php	菜鳥	2017-04-12
2	mysql	菜鳥2	2017-04-12
3	java	runoob.com	2015-05-01
4	學習 Python	RUNOOB.COM	2016-03-06
5	學習 C	FK	2017-04-05

修改后：

步驟：

1.創建臨時表

1.1 首先獲取原表中的字段結構：

show columns from runoob_tbl;

1.2 創建臨時表

具體代碼：

mysql>  create temporary  table  `tmp`(->        `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;
Query OK, 0 rows affected (0.00 sec)

2.在臨時表中插入要修改的數據

mysql> insert into tmp values(1,'myphp','菜鳥大佬','20190203');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tmp values(2,'myjava','菜鳥大佬2','20140409');
Query OK, 1 row affected (0.00 sec)

根據runoob_id可知我們要修改前兩條，而且我們修改除runoob_id之外的三個字段

3.更新原表

3.1 我們先更新了runoob_title字段：

mysql> update runoob_tbl,tmp set runoob_tbl.runoob_title = tmp.runoob_title where runoob_tbl.runoob_id = tmp.runoob_id;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 2  Changed: 0  Warnings: 0

修改之后的表內容如下：

runoob_id	runoob_title	runoob_author	submission_date
1	myphp	菜鳥	2017-04-12
2	myjava	菜鳥2	2017-04-12
3	java	runoob.com	2015-05-01
4	學習 Python	RUNOOB.COM	2016-03-06
5	學習 C	FK	2017-04-05

3.2 接著更新runoob_author字段

mysql> update runoob_tbl,tmp set runoob_tbl.runoob_author = tmp.runoob_author where runoob_tbl.runoob_id = tmp.runoob_id;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

修改之后的表內容如下：

runoob_id	runoob_title	runoob_author	submission_date
1	myphp	菜鳥大佬	2017-04-12
2	myjava	菜鳥大佬2	2017-04-12
3	java	runoob.com	2015-05-01
4	學習 Python	RUNOOB.COM	2016-03-06
5	學習 C	FK	2017-04-05

3.3接著更新submission_date字段

mysql> update runoob_tbl,tmp set runoob_tbl.submission_date = tmp.submission_date where runoob_tbl.runoob_id = tmp.runoob
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

修改了三個字段之后的最終的表內容為：

runoob_id	runoob_title	runoob_author	submission_date
1	myphp	菜鳥大佬	2019-02-03
2	myjava	菜鳥大佬2	2014-04-09
3	java	runoob.com	2015-05-01
4	學習 Python	RUNOOB.COM	2016-03-06
5	學習 C	FK	2017-04-05

MySQL DELETE 語句

你可以使用 SQL 的 DELETE FROM 命令來刪除 MySQL 數據表中的記錄。
你可以在 mysql> 命令提示符或 PHP 腳本中執行該命令。
語法
以下是 SQL DELETE 語句從 MySQL 數據表中刪除數據的通用語法：

DELETE FROM table_name [WHERE Clause]

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

例子：

DELETE FROM runoob_tbl WHERE runoob_id=3;

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 子句來指定條件。

例子：

select * from runoob_tbl where runoob_author like '%com';

MySQL UNION 操作符

本教程為大家介紹 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: 可選，返回所有結果集，包含重復數據。

例子：

SQL UNION 實例

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

SQL UNION ALL 實例

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

實例:

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

帶有 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;

MySQL 排序

我們知道從 MySQL 表中使用 SQL SELECT 語句來讀取數據。
如果我們需要對讀取的數據進行排序，我們就可以使用 MySQL 的 ORDER BY 子句來設定你想按哪個字段哪種方式來進行排序，再返回搜索結果。

語法：
以下是 SQL SELECT 語句使用 ORDER BY 子句將查詢數據排序后再返回數據：

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

你可以使用任何字段來作為排序的條件，從而返回排序后的查詢結果。
你可以設定多個字段來排序。
你可以使用 ASC 或 DESC 關鍵字來設置查詢結果是按升序或降序排列。默認情況下，它是按升序排列。
你可以添加 WHERE…LIKE 子句來設置條件。
例子：

SELECT * from runoob_tbl ORDER BY submission_date ASC;

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;

實例：

CREATE TABLE `employee_tbl` (`id` int(11) NOT NULL,`name` char(10) NOT NULL DEFAULT '',`date` datetime NOT NULL,`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登錄次數',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小麗', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');

使用 GROUP BY 語句將數據表按名字進行分組，并統計每個人有多少條記錄：

SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;

使用 WITH ROLLUP

WITH ROLLUP 可以實現在分組統計數據基礎上再進行相同的統計（SUM,AVG,COUNT…）。

例如我們將以上的數據表按名字進行分組，再統計每個人登錄的次數：

SELECT name, SUM(singin) as singin_count 
FROM  employee_tbl GROUP BY name WITH ROLLUP;

其中記錄 NULL 表示所有人的登錄次數。

我們可以使用 coalesce 來設置一個可以取代 NUll 的名稱，coalesce 語法：

select coalesce(a,b,c);
參數說明：如果anull,則選擇b；如果bnull,則選擇c；如果a!=null,則選擇a；如果a b c 都為null ，則返回為null（沒意義）。

以下實例中如果名字為空我們使用總數代替：

SELECT coalesce(name, '總數'), SUM(singin) as singin_count 
FROM  employee_tbl GROUP BY name WITH ROLLUP;

Mysql 連接的使用

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

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

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

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

INNER JOIN（內連接,或等值連接）：獲取兩個表中字段匹配關系的記錄。
LEFT JOIN（左連接）：獲取左表所有記錄，即使右表沒有對應匹配的記錄。

RIGHT JOIN（右連接）：與 LEFT JOIN 相反，用于獲取右表所有記錄，即使左表沒有對應匹配的記錄。
新建表tcount_tbl

CREATE TABLE  `tcount_tbl`(
`runoob_author` varchar(255) NOT NULL DEFAULT '' ,
`runoob_count` int(11) NOT NULL DEFAULT '0')ENGINE=InnoDB DEFAULT CHARSET=utf8;

INNER JOIN

SELECT a.runoob_id, a.runoob_author, b.runoob_count 
FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

等價與：

SELECT a.runoob_id, a.runoob_author, b.runoob_count 
FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;

MySQL LEFT JOIN
MySQL left join 與 join 有所不同。 MySQL LEFT JOIN 會讀取左邊數據表的全部數據，即便右邊表無對應數據。

實例
嘗試以下實例，以 runoob_tbl 為左表，tcount_tbl 為右表，理解 MySQL LEFT JOIN 的應用：

LEFT JOIN

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

MySQL RIGHT JOIN
MySQL RIGHT JOIN 會讀取右邊數據表的全部數據，即便左邊邊表無對應數據。

實例:
嘗試以下實例，以 runoob_tbl 為左表，tcount_tbl 為右表，理解MySQL RIGHT JOIN的應用：

RIGHT JOIN

SELECT a.runoob_id, a.runoob_author, b.runoob_count 
FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.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 事務主要用于處理操作量大，復雜度高的數據。比如說，在人員管理系統中，你刪除一個人員，你即需要刪除人員的基本資料，也要刪除和該人員相關的信息，如信箱，文章等等，這樣，這些數據庫操作語句就構成一個事務！

在 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> 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命令。
開始本章教程前讓我們先創建一張表，表名為：testalter_tbl。

root@host# mysql -u root -p password;
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> create table testalter_tbl-> (-> i INT,-> c CHAR(1)-> );
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

1.刪除，添加或修改表字段

如下命令使用了 ALTER 命令及 DROP 子句來刪除以上創建表的 i 字段：

mysql> ALTER TABLE testalter_tbl  DROP i;

如果數據表中只剩余一個字段則無法使用DROP來刪除字段。

MySQL 中使用 ADD 子句來向數據表中添加列，如下實例在表 testalter_tbl 中添加 i 字段，并定義數據類型:

mysql> ALTER TABLE testalter_tbl ADD i INT;

執行以上命令后，i 字段會自動添加到數據表字段的末尾。

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

1.1指定新增字段的位置

如果你需要指定新增字段的位置，可以使用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 來添加字段并設置位置。

2.修改字段類型及名稱

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

2.1修改字段類型

例如，把字段 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。

3.修改字段默認值

你可以使用 ALTER 來修改字段的默認值，嘗試以下實例：

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | 1000    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

你也可以使用 ALTER 命令及 DROP子句來刪除字段的默認值，如下實例：

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Changing a Table Type:

修改數據表類型，可以使用 ALTER 命令及 TYPE 子句來完成。嘗試以下實例，我們將表 testalter_tbl 的類型修改為 MYISAM ：

注意：查看數據表類型可以使用 SHOW TABLE STATUS 語句。

mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;
mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G
*************************** 1. row ****************Name: testalter_tblType: MyISAMRow_format: FixedRows: 0Avg_row_length: 0Data_length: 0
Max_data_length: 25769803775Index_length: 1024Data_free: 0Auto_increment: NULLCreate_time: 2007-06-03 08:04:36Update_time: 2007-06-03 08:04:36Check_time: NULLCreate_options:Comment:
1 row in set (0.00 sec)

4.修改表名

如果需要修改數據表的名稱，可以在 ALTER TABLE 語句中使用 RENAME 子句來實現。

嘗試以下實例將數據表 testalter_tbl 重命名為 alter_tbl：

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

此外：ALTER 命令還可以用來創建及刪除MySQL數據表的索引。

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

臨時表的應用場景

1.批量更新數據

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

執行以上步驟后，你將完整的復制表，包括表結構及表數據。

MySQL 元數據

你可能想知道MySQL以下三種信息：

查詢結果信息： SELECT, UPDATE 或 DELETE語句影響的記錄數。
數據庫和數據表的信息：包含了數據庫及數據表的結構信息。
MySQL服務器信息：包含了數據庫服務器的當前狀態，版本號等。
在MySQL的命令提示符中，我們可以很容易的獲取以上服務器信息。

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( ) 函數來獲取最后的插入表中的自增列的值。

重置序列

如果你刪除了數據表中的多條記錄，并希望對剩下數據的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 數據表中設置指定的字段為 PRIMARY KEY（主鍵）或者 UNIQUE（唯一）索引來保證數據的唯一性。
讓我們嘗試一個實例：下表中無索引及主鍵，所以該表允許出現多條重復記錄。

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

方法1：設置主鍵

如果你想設置表中字段 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 相同的記錄，則先刪除掉。再插入新記錄。

方法2 ：設置數據的唯一性方法是添加一個 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);

MySQL 運算符

本章節我們主要介紹 MySQL 的運算符及運算符的優先級。 MySQL 主要有以下幾種運算符：

算術運算符
比較運算符
邏輯運算符
位運算符

級聯刪除和級聯更新

1.建立兩張表stu和sc

create table stu(
sid int UNSIGNED primary key auto_increment,
name varchar(20) not null)create table sc(
scid int UNSIGNED primary key auto_increment,
sid int UNSIGNED not null,
score varchar(20) default '0',
FOREIGN KEY (sid) REFERENCES stu(sid) ON DELETE CASCADE ON UPDATE CASCADE)

分別插入數據

insert into stu (name) value ('zxf');
insert into stu (name) value ('ls');
insert into stu (name) value ('zs');
insert into stu (name) value ('ww');insert into sc(sid,score) values ('1','98');
insert into sc(sid,score) values ('1','98');
insert into sc(sid,score) values ('2','34');
insert into sc(sid,score) values ('2','98');
insert into sc(sid,score) values ('2','98');
insert into sc(sid,score) values ('3','56');
insert into sc(sid,score) values ('4','78');
insert into sc(sid,score) values ('4','98');

注意：在sc表中插入數據時，若插入的sid為22，則會插入失敗，違反外鍵約束，因為外鍵sid
來自stu表中的id的主鍵，即stu中的id沒有等于22的數據。

級聯刪除：將stu表中id為2的學生刪除，該學生在sc表中的成績也會級聯刪除

delete from stu where sid = '2';

級聯更新：stu表中id為3的學生更改為id為6，該學生在sc表中的對應id也會級聯更新

update stu set sid=6 where sid='3';

注意

刪除表的時候必須先刪除外鍵表(sc)，再刪除主鍵表（stu）

mysql> drop table stu;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

上述為違反外鍵約束，不能刪除

mysql> drop table sc;
Query OK, 0 rows affected (0.02 sec)mysql> drop table stu;
Query OK, 0 rows affected (0.01 sec)

上述為正常刪除，先刪除sc表，再刪除stu表！

參考：

（1）https://www.runoob.com/mysql/mysql-tutorial.html 菜鳥聯盟