當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

mysql快速了解

發(fā)布時(shí)間：2023/12/10 数据库 24 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql快速了解小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

文章目錄

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

數(shù)據(jù)庫(kù)了解：

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

快速操作：

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

安裝mysql

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

啟動(dòng),關(guān)閉,重啟mysql服務(wù)

sudo service mysql start/stop/restart

連接mysql的root用戶

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

或者連接遠(yuǎn)程數(shù)據(jù)庫(kù)：

mysql -h0.0.0.0 -u root -p

創(chuàng)建數(shù)據(jù)庫(kù)

create database runoob;

刪除數(shù)據(jù)庫(kù)

drop database runoob;

選擇數(shù)據(jù)庫(kù)

use runoob;

mysql 數(shù)據(jù)類型

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

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

MySQL 創(chuàng)建數(shù)據(jù)表

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;

實(shí)例解析：
如果你不想字段為 NULL 可以設(shè)置字段的屬性為 NOT NULL，在操作數(shù)據(jù)庫(kù)時(shí)如果輸入該字段的數(shù)據(jù)為NULL ，就會(huì)報(bào)錯(cuò)。
AUTO_INCREMENT定義列為自增的屬性，一般用于主鍵，數(shù)值會(huì)自動(dòng)加1。
PRIMARY KEY關(guān)鍵字用于定義列為主鍵。您可以使用多列來定義主鍵，列間以逗號(hào)分隔。
ENGINE 設(shè)置存儲(chǔ)引擎，CHARSET 設(shè)置編碼。

MySQL 刪除數(shù)據(jù)表

DROP TABLE table_name ;

例子：

DROP TABLE runoob_tb1

MySQL 插入數(shù)據(jù)

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

例子：

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

檢查一下：

select * from runoob_tbl;

MySQL 查詢數(shù)據(jù)

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

例子：

select * from runoob_tbl;

MySQL WHERE 子句

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

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

例子：

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

BINARY 關(guān)鍵字

MySQL 的 WHERE 子句的字符串比較是不區(qū)分大小寫的。你可以使用 BINARY 關(guān)鍵字來設(shè)定 WHERE 子句的字符串比較是區(qū)分大小寫的。
如下實(shí)例:
BINARY 關(guān)鍵字:

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

MySQL UPDATE 更新

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

語(yǔ)法
以下是 UPDATE 命令修改 MySQL 數(shù)據(jù)表數(shù)據(jù)的通用 SQL 語(yǔ)法：

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

你可以同時(shí)更新一個(gè)或多個(gè)字段。
你可以在 WHERE 子句中指定任何條件。
你可以在一個(gè)單獨(dú)表中同時(shí)更新數(shù)據(jù)。

例子：

UPDATE runoob_tbl SET runoob_title='學(xué)習(xí) C++' WHERE runoob_id=3;

批量更新1：使用case when

例子：
修改前：

runoob_idrunoob_titlerunoob_authorsubmission_date

1	學(xué)習(xí) PHP	菜鳥教程	2017-04-12
2	學(xué)習(xí) MySQL	菜鳥教程	2017-04-12
3	學(xué)習(xí) Java	RUNOOB.COM	2015-05-01
4	學(xué)習(xí) Python	RUNOOB.COM	2016-03-06
5	學(xué)習(xí) C	FK	2017-04-05

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

修改后：

runoob_idrunoob_titlerunoob_authorsubmission_date

1	php	菜鳥	2017-04-12
2	mysql	菜鳥2	2017-04-12
3	java	runoob.com	2015-05-01
4	學(xué)習(xí) Python	RUNOOB.COM	2016-03-06
5	學(xué)習(xí) 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：創(chuàng)建臨時(shí)表，先更新臨時(shí)表，然后從臨時(shí)表中update

修改前：

runoob_idrunoob_titlerunoob_authorsubmission_date

1	php	菜鳥	2017-04-12
2	mysql	菜鳥2	2017-04-12
3	java	runoob.com	2015-05-01
4	學(xué)習(xí) Python	RUNOOB.COM	2016-03-06
5	學(xué)習(xí) C	FK	2017-04-05

修改后：

步驟：

1.創(chuàng)建臨時(shí)表

1.1 首先獲取原表中的字段結(jié)構(gòu)：

show columns from runoob_tbl;

1.2 創(chuàng)建臨時(shí)表

具體代碼：

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.在臨時(shí)表中插入要修改的數(shù)據(jù)

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)

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

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

修改之后的表內(nèi)容如下：

runoob_idrunoob_titlerunoob_authorsubmission_date

1	myphp	菜鳥	2017-04-12
2	myjava	菜鳥2	2017-04-12
3	java	runoob.com	2015-05-01
4	學(xué)習(xí) Python	RUNOOB.COM	2016-03-06
5	學(xué)習(xí) 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

修改之后的表內(nèi)容如下：

runoob_idrunoob_titlerunoob_authorsubmission_date

1	myphp	菜鳥大佬	2017-04-12
2	myjava	菜鳥大佬2	2017-04-12
3	java	runoob.com	2015-05-01
4	學(xué)習(xí) Python	RUNOOB.COM	2016-03-06
5	學(xué)習(xí) 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

修改了三個(gè)字段之后的最終的表內(nèi)容為：

runoob_idrunoob_titlerunoob_authorsubmission_date

1	myphp	菜鳥大佬	2019-02-03
2	myjava	菜鳥大佬2	2014-04-09
3	java	runoob.com	2015-05-01
4	學(xué)習(xí) Python	RUNOOB.COM	2016-03-06
5	學(xué)習(xí) C	FK	2017-04-05

MySQL DELETE 語(yǔ)句

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

DELETE FROM table_name [WHERE Clause]

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

例子：

DELETE FROM runoob_tbl WHERE runoob_id=3;

MySQL LIKE 子句

我們知道在 MySQL 中使用 SQL SELECT 命令來讀取數(shù)據(jù)，同時(shí)我們可以在 SELECT 語(yǔ)句中使用 WHERE 子句來獲取指定的記錄。
WHERE 子句中可以使用等號(hào) = 來設(shè)定獲取數(shù)據(jù)的條件，如 “runoob_author = ‘RUNOOB.COM’”。

但是有時(shí)候我們需要獲取 runoob_author 字段含有 “COM” 字符的所有記錄，這時(shí)我們就需要在 WHERE 子句中使用 SQL LIKE 子句。

SQL LIKE 子句中使用百分號(hào) %字符來表示任意字符，類似于UNIX或正則表達(dá)式中的星號(hào) *。

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

語(yǔ)法
以下是 SQL SELECT 語(yǔ)句使用 LIKE 子句從數(shù)據(jù)表中讀取數(shù)據(jù)的通用語(yǔ)法：

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

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

例子：

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

MySQL UNION 操作符

本教程為大家介紹 MySQL UNION 操作符的語(yǔ)法和實(shí)例。

描述
MySQL UNION 操作符用于連接兩個(gè)以上的 SELECT 語(yǔ)句的結(jié)果組合到一個(gè)結(jié)果集合中。多個(gè) SELECT 語(yǔ)句會(huì)刪除重復(fù)的數(shù)據(jù)。

語(yǔ)法
MySQL UNION 操作符語(yǔ)法格式：

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

參數(shù)

expression1, expression2, … expression_n: 要檢索的列。
tables: 要檢索的數(shù)據(jù)表。
WHERE conditions: 可選，檢索條件。
DISTINCT: 可選，刪除結(jié)果集中重復(fù)的數(shù)據(jù)。默認(rèn)情況下 UNION 操作符已經(jīng)刪除了重復(fù)數(shù)據(jù)，所以 DISTINCT 修飾符對(duì)結(jié)果沒啥影響。
ALL: 可選，返回所有結(jié)果集，包含重復(fù)數(shù)據(jù)。

例子：

SQL UNION 實(shí)例

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

SQL UNION ALL 實(shí)例

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

實(shí)例:

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

帶有 WHERE 的 SQL UNION ALL

下面的 SQL 語(yǔ)句使用 UNION ALL 從 “Websites” 和 “apps” 表中選取所有的中國(guó)(CN)的數(shù)據(jù)（也有重復(fù)的值）：

實(shí)例:

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 語(yǔ)句來讀取數(shù)據(jù)。
如果我們需要對(duì)讀取的數(shù)據(jù)進(jìn)行排序，我們就可以使用 MySQL 的 ORDER BY 子句來設(shè)定你想按哪個(gè)字段哪種方式來進(jìn)行排序，再返回搜索結(jié)果。

語(yǔ)法：
以下是 SQL SELECT 語(yǔ)句使用 ORDER BY 子句將查詢數(shù)據(jù)排序后再返回?cái)?shù)據(jù)：

SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1 [ASC [DESC][默認(rèn) ASC]], [field2...] [ASC [DESC][默認(rèn) ASC]]

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

SELECT * from runoob_tbl ORDER BY submission_date ASC;

MySQL GROUP BY 語(yǔ)句

GROUP BY 語(yǔ)句根據(jù)一個(gè)或多個(gè)列對(duì)結(jié)果集進(jìn)行分組。
在分組的列上我們可以使用 COUNT, SUM, AVG,等函數(shù)。

GROUP BY 語(yǔ)法

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

實(shí)例：

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 '登錄次數(shù)',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 語(yǔ)句將數(shù)據(jù)表按名字進(jìn)行分組，并統(tǒng)計(jì)每個(gè)人有多少條記錄：

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

使用 WITH ROLLUP

WITH ROLLUP 可以實(shí)現(xiàn)在分組統(tǒng)計(jì)數(shù)據(jù)基礎(chǔ)上再進(jìn)行相同的統(tǒng)計(jì)（SUM,AVG,COUNT…）。

例如我們將以上的數(shù)據(jù)表按名字進(jìn)行分組，再統(tǒng)計(jì)每個(gè)人登錄的次數(shù)：

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

其中記錄 NULL 表示所有人的登錄次數(shù)。

我們可以使用 coalesce 來設(shè)置一個(gè)可以取代 NUll 的名稱，coalesce 語(yǔ)法：

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

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

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

Mysql 連接的使用

在前幾章節(jié)中，我們已經(jīng)學(xué)會(huì)了如何在一張表中讀取數(shù)據(jù)，這是相對(duì)簡(jiǎn)單的，但是在真正的應(yīng)用中經(jīng)常需要從多個(gè)數(shù)據(jù)表中讀取數(shù)據(jù)。

本章節(jié)我們將向大家介紹如何使用 MySQL 的 JOIN 在兩個(gè)或多個(gè)表中查詢數(shù)據(jù)。

你可以在 SELECT, UPDATE 和 DELETE 語(yǔ)句中使用 Mysql 的 JOIN 來聯(lián)合多表查詢。

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

INNER JOIN（內(nèi)連接,或等值連接）：獲取兩個(gè)表中字段匹配關(guān)系的記錄。
LEFT JOIN（左連接）：獲取左表所有記錄，即使右表沒有對(duì)應(yīng)匹配的記錄。
RIGHT JOIN（右連接）：與 LEFT JOIN 相反，用于獲取右表所有記錄，即使左表沒有對(duì)應(yīng)匹配的記錄。
新建表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;

等價(jià)與：

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 會(huì)讀取左邊數(shù)據(jù)表的全部數(shù)據(jù)，即便右邊表無對(duì)應(yīng)數(shù)據(jù)。

實(shí)例
嘗試以下實(shí)例，以 runoob_tbl 為左表，tcount_tbl 為右表，理解 MySQL LEFT JOIN 的應(yīng)用：

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 會(huì)讀取右邊數(shù)據(jù)表的全部數(shù)據(jù)，即便左邊邊表無對(duì)應(yīng)數(shù)據(jù)。

實(shí)例:
嘗試以下實(shí)例，以 runoob_tbl 為左表，tcount_tbl 為右表，理解MySQL RIGHT JOIN的應(yīng)用：

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 值處理

我們已經(jīng)知道 MySQL 使用 SQL SELECT 命令及 WHERE 子句來讀取數(shù)據(jù)表中的數(shù)據(jù),但是當(dāng)提供的查詢條件字段為 NULL 時(shí)，該命令可能就無法正常工作。

為了處理這種情況，MySQL提供了三大運(yùn)算符:

IS NULL: 當(dāng)列的值是 NULL,此運(yùn)算符返回 true。
IS NOT NULL: 當(dāng)列的值不為 NULL, 運(yùn)算符返回 true。
<=>: 比較操作符（不同于=運(yùn)算符），當(dāng)比較的的兩個(gè)值為 NULL 時(shí)返回 true。
關(guān)于 NULL 的條件比較運(yùn)算是比較特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值。
在 MySQL 中，NULL 值與任何其它值的比較（即使是 NULL）永遠(yuǎn)返回 false，即 NULL = NULL 返回false 。

MySQL 中處理 NULL 使用 IS NULL 和 IS NOT NULL 運(yùn)算符。

注意：

select * , columnName1+ifnull(columnName2,0) from tableName;
columnName1，columnName2 為 int 型，當(dāng) columnName2 中，有值為 null 時(shí)，columnName1+columnName2=null， ifnull(columnName2,0) 把 columnName2 中 null 值轉(zhuǎn)為 0。

MySQL 事務(wù)

MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。比如說，在人員管理系統(tǒng)中，你刪除一個(gè)人員，你即需要?jiǎng)h除人員的基本資料，也要?jiǎng)h除和該人員相關(guān)的信息，如信箱，文章等等，這樣，這些數(shù)據(jù)庫(kù)操作語(yǔ)句就構(gòu)成一個(gè)事務(wù)！

在 MySQL 中只有使用了 Innodb 數(shù)據(jù)庫(kù)引擎的數(shù)據(jù)庫(kù)或表才支持事務(wù)。
事務(wù)處理可以用來維護(hù)數(shù)據(jù)庫(kù)的完整性，保證成批的 SQL 語(yǔ)句要么全部執(zhí)行，要么全部不執(zhí)行。
事務(wù)用來管理 insert,update,delete 語(yǔ)句
一般來說，事務(wù)是必須滿足4個(gè)條件（ACID）：：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨(dú)立性）、持久性（Durability）。
原子性：一個(gè)事務(wù)（transaction）中的所有操作，要么全部完成，要么全部不完成，不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié)。事務(wù)在執(zhí)行過程中發(fā)生錯(cuò)誤，會(huì)被回滾（Rollback）到事務(wù)開始前的狀態(tài)，就像這個(gè)事務(wù)從來沒有執(zhí)行過一樣。
一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫(kù)的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預(yù)設(shè)規(guī)則，這包含資料的精確度、串聯(lián)性以及后續(xù)數(shù)據(jù)庫(kù)可以自發(fā)性地完成預(yù)定的工作。
隔離性：數(shù)據(jù)庫(kù)允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫和修改的能力，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同級(jí)別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（Serializable）。
持久性：事務(wù)處理結(jié)束后，對(duì)數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會(huì)丟失。

在 MySQL 命令行的默認(rèn)設(shè)置下，事務(wù)都是自動(dòng)提交的，即執(zhí)行 SQL 語(yǔ)句后就會(huì)馬上執(zhí)行 COMMIT 操作。因此要顯式地開啟一個(gè)事務(wù)務(wù)須使用命令 BEGIN 或 START TRANSACTION，或者執(zhí)行命令 SET AUTOCOMMIT=0，用來禁止使用當(dāng)前會(huì)話的自動(dòng)提交。

事務(wù)控制語(yǔ)句：

BEGIN 或 START TRANSACTION 顯式地開啟一個(gè)事務(wù)；
COMMIT 也可以使用 COMMIT WORK，不過二者是等價(jià)的。COMMIT 會(huì)提交事務(wù)，并使已對(duì)數(shù)據(jù)庫(kù)進(jìn)行的所有修改成為永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不過二者是等價(jià)的。回滾會(huì)結(jié)束用戶的事務(wù)，并撤銷正在進(jìn)行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允許在事務(wù)中創(chuàng)建一個(gè)保存點(diǎn)，一個(gè)事務(wù)中可以有多個(gè) SAVEPOINT；
RELEASE SAVEPOINT identifier 刪除一個(gè)事務(wù)的保存點(diǎn)，當(dāng)沒有指定的保存點(diǎn)時(shí)，執(zhí)行該語(yǔ)句會(huì)拋出一個(gè)異常；
ROLLBACK TO identifier 把事務(wù)回滾到標(biāo)記點(diǎn)；
SET TRANSACTION 用來設(shè)置事務(wù)的隔離級(jí)別。InnoDB 存儲(chǔ)引擎提供事務(wù)的隔離級(jí)別有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事務(wù)處理主要有兩種方法：
1、用 BEGIN, ROLLBACK, COMMIT來實(shí)現(xiàn)

BEGIN 開始一個(gè)事務(wù) ROLLBACK 事務(wù)回滾 COMMIT 事務(wù)確認(rèn)

2、直接用 SET 來改變 MySQL 的自動(dòng)提交模式:

SET AUTOCOMMIT=0 禁止自動(dòng)提交 SET AUTOCOMMIT=1 開啟自動(dòng)提交

實(shí)例：
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb; # 創(chuàng)建數(shù)據(jù)表
Query OK, 0 rows affected (0.04 sec)

mysql> select * from runoob_transaction_test; Empty set (0.01 sec)mysql> begin; # 開始事務(wù) 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; # 提交事務(wù) 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; # 開始事務(wù) 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; # 因?yàn)榛貪L所以數(shù)據(jù)沒有插入 +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec)mysql>

使用保留點(diǎn) SAVEPOINT

savepoint 是在數(shù)據(jù)庫(kù)事務(wù)處理中實(shí)現(xiàn)“子事務(wù)”（subtransaction），也稱為嵌套事務(wù)的方法。事務(wù)可以回滾到 savepoint 而不影響 savepoint 創(chuàng)建前的變化, 不需要放棄整個(gè)事務(wù)。

ROLLBACK 回滾的用法可以設(shè)置保留點(diǎn) SAVEPOINT，執(zhí)行多條操作時(shí)，回滾到想要的那條語(yǔ)句之前。

使用 SAVEPOINT

SAVEPOINT savepoint_name; // 聲明一個(gè) savepoint

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

保留點(diǎn)再事務(wù)處理完成（執(zhí)行一條 ROLLBACK 或 COMMIT）后自動(dòng)釋放。

MySQL5 以來，可以用:

RELEASE SAVEPOINT savepoint_name; // 刪除指定保留點(diǎn)

MySQL ALTER命令

當(dāng)我們需要修改數(shù)據(jù)表名或者修改數(shù)據(jù)表字段時(shí)，就需要使用到MySQL ALTER命令。
開始本章教程前讓我們先創(chuàng)建一張表，表名為：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 子句來刪除以上創(chuàng)建表的 i 字段：

mysql> ALTER TABLE testalter_tbl DROP i;

如果數(shù)據(jù)表中只剩余一個(gè)字段則無法使用DROP來刪除字段。

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

mysql> ALTER TABLE testalter_tbl ADD i INT;

執(zhí)行以上命令后，i 字段會(huì)自動(dòng)添加到數(shù)據(jù)表字段的末尾。

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提供的關(guān)鍵字 FIRST (設(shè)定位第一列)， AFTER 字段名（設(shè)定位于某個(gè)字段之后）。

嘗試以下 ALTER TABLE 語(yǔ)句, 在執(zhí)行成功后，使用 SHOW COLUMNS 查看表結(jié)構(gòu)的變化：

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 關(guān)鍵字可用于 ADD 與 MODIFY 子句，所以如果你想重置數(shù)據(jù)表字段的位置就需要先使用 DROP 刪除字段然后使用 ADD 來添加字段并設(shè)置位置。

2.修改字段類型及名稱

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

2.1修改字段類型

例如，把字段 c 的類型從 CHAR(1) 改為 CHAR(10)，可以執(zhí)行以下命令:

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

使用 CHANGE 子句, 語(yǔ)法有很大的不同。在 CHANGE 關(guān)鍵字之后，緊跟著的是你要修改的字段名，然后指定新字段名及類型。嘗試如下實(shí)例：

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

ALTER TABLE 對(duì) Null 值和默認(rèn)值的影響
當(dāng)你修改字段時(shí)，你可以指定是否包含值或者是否設(shè)置默認(rèn)值。

以下實(shí)例，指定字段 j 為 NOT NULL 且默認(rèn)值為100 。

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

如果你不設(shè)置默認(rèn)值，MySQL會(huì)自動(dòng)設(shè)置該字段默認(rèn)為 NULL。

3.修改字段默認(rèn)值

你可以使用 ALTER 來修改字段的默認(rèn)值，嘗試以下實(shí)例：

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子句來刪除字段的默認(rèn)值，如下實(shí)例：

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:

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

注意：查看數(shù)據(jù)表類型可以使用 SHOW TABLE STATUS 語(yǔ)句。

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.修改表名

如果需要修改數(shù)據(jù)表的名稱，可以在 ALTER TABLE 語(yǔ)句中使用 RENAME 子句來實(shí)現(xiàn)。

嘗試以下實(shí)例將數(shù)據(jù)表 testalter_tbl 重命名為 alter_tbl：

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

此外：ALTER 命令還可以用來創(chuàng)建及刪除MySQL數(shù)據(jù)表的索引。

MySQL 索引

MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的，索引可以大大提高M(jìn)ySQL的檢索速度。

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

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

創(chuàng)建索引時(shí)，你需要確保該索引是應(yīng)用在SQL 查詢語(yǔ)句的條件(一般作為 WHERE 子句的條件)。
實(shí)際上，索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄。

缺點(diǎn)：

上面都在說使用索引的好處，但過多的使用索引將會(huì)造成濫用。因此索引也會(huì)有它的缺點(diǎn)：雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。
建立索引會(huì)占用磁盤空間的索引文件。

普通索引

創(chuàng)建索引

這是最基本的索引，它沒有任何限制。它有以下幾種創(chuàng)建方式：

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

如果是CHAR，VARCHAR類型，length可以小于字段實(shí)際長(zhǎng)度；如果是BLOB和TEXT類型，必須指定 length。

修改表結(jié)構(gòu)(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

創(chuàng)建表的時(shí)候直接指定

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

刪除索引的語(yǔ)法

DROP INDEX [indexName] ON mytable;

唯一索引

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

創(chuàng)建索引

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

修改表結(jié)構(gòu)

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

創(chuàng)建表的時(shí)候直接指定

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

使用ALTER 命令添加和刪除索引

有四種方式來添加數(shù)據(jù)表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 該語(yǔ)句添加一個(gè)主鍵，這意味著索引值必須是唯一的，且不能為NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 這條語(yǔ)句創(chuàng)建索引的值必須是唯一的（除了NULL外，NULL可能會(huì)出現(xiàn)多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出現(xiàn)多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):該語(yǔ)句指定了索引為 FULLTEXT ，用于全文索引。

以下實(shí)例為在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

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

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令添加和刪除主鍵
主鍵只能作用于一個(gè)列上，添加主鍵索引時(shí)，你需要確保該主鍵默認(rèn)不為空（NOT NULL）。實(shí)例如下：

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;

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

顯示索引信息

你可以使用 SHOW INDEX 命令來列出表中的相關(guān)的索引信息。可以通過添加 \G 來格式化輸出信息。

嘗試以下實(shí)例:

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

MySQL 臨時(shí)表

MySQL 臨時(shí)表在我們需要保存一些臨時(shí)數(shù)據(jù)時(shí)是非常有用的。臨時(shí)表只在當(dāng)前連接可見，當(dāng)關(guān)閉連接時(shí)，Mysql會(huì)自動(dòng)刪除表并釋放所有空間。

臨時(shí)表在MySQL 3.23版本中添加，如果你的MySQL版本低于 3.23版本就無法使用MySQL的臨時(shí)表。不過現(xiàn)在一般很少有再使用這么低版本的MySQL數(shù)據(jù)庫(kù)服務(wù)了。

MySQL臨時(shí)表只在當(dāng)前連接可見，如果你使用PHP腳本來創(chuàng)建MySQL臨時(shí)表，那每當(dāng)PHP腳本執(zhí)行完成后，該臨時(shí)表也會(huì)自動(dòng)銷毀。

如果你使用了其他MySQL客戶端程序連接MySQL數(shù)據(jù)庫(kù)服務(wù)器來創(chuàng)建臨時(shí)表，那么只有在關(guān)閉客戶端程序時(shí)才會(huì)銷毀臨時(shí)表，當(dāng)然你也可以手動(dòng)銷毀。

實(shí)例
以下展示了使用MySQL 臨時(shí)表的簡(jiǎn)單實(shí)例，以下的SQL代碼可以適用于PHP腳本的mysql_query()函數(shù)。

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)

當(dāng)你使用 SHOW TABLES命令顯示數(shù)據(jù)表列表時(shí)，你將無法看到 SalesSummary表。

如果你退出當(dāng)前MySQL會(huì)話，再使用 SELECT命令來讀取原先創(chuàng)建的臨時(shí)表數(shù)據(jù)，那你會(huì)發(fā)現(xiàn)數(shù)據(jù)庫(kù)中沒有該表的存在，因?yàn)樵谀阃顺鰰r(shí)該臨時(shí)表已經(jīng)被銷毀了。

刪除MySQL 臨時(shí)表

默認(rèn)情況下，當(dāng)你斷開與數(shù)據(jù)庫(kù)的連接后，臨時(shí)表就會(huì)自動(dòng)被銷毀。當(dāng)然你也可以在當(dāng)前MySQL會(huì)話使用 DROP TABLE 命令來手動(dòng)刪除臨時(shí)表。

以下是手動(dòng)刪除臨時(shí)表的實(shí)例：

臨時(shí)表的應(yīng)用場(chǎng)景

1.批量更新數(shù)據(jù)

MySQL 復(fù)制表

如果我們需要完全的復(fù)制MySQL的數(shù)據(jù)表，包括表的結(jié)構(gòu)，索引，默認(rèn)值等。如果僅僅使用CREATE TABLE … SELECT 命令，是無法實(shí)現(xiàn)的。

本章節(jié)將為大家介紹如何完整的復(fù)制MySQL數(shù)據(jù)表，步驟如下：

使用 SHOW CREATE TABLE 命令獲取創(chuàng)建數(shù)據(jù)表(CREATE TABLE) 語(yǔ)句，該語(yǔ)句包含了原數(shù)據(jù)表的結(jié)構(gòu)，索引等。
復(fù)制以下命令顯示的SQL語(yǔ)句，修改數(shù)據(jù)表名，并執(zhí)行SQL語(yǔ)句，通過以上命令將完全的復(fù)制數(shù)據(jù)表結(jié)構(gòu)。
如果你想復(fù)制表的內(nèi)容，你就可以使用 INSERT INTO … SELECT 語(yǔ)句來實(shí)現(xiàn)。
實(shí)例
嘗試以下實(shí)例來復(fù)制表 runoob_tbl 。

步驟一：獲取數(shù)據(jù)表的完整結(jié)構(gòu)。

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語(yǔ)句的數(shù)據(jù)表名，并執(zhí)行SQL語(yǔ)句。

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)

步驟三：拷貝全部數(shù)據(jù)

執(zhí)行完第二步驟后，你將在數(shù)據(jù)庫(kù)中創(chuàng)建新的克隆表 clone_tbl。如果你想拷貝數(shù)據(jù)表的數(shù)據(jù)你可以使用 INSERT INTO… SELECT 語(yǔ)句來實(shí)現(xiàn)。

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

執(zhí)行以上步驟后，你將完整的復(fù)制表，包括表結(jié)構(gòu)及表數(shù)據(jù)。

MySQL 元數(shù)據(jù)

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

查詢結(jié)果信息： SELECT, UPDATE 或 DELETE語(yǔ)句影響的記錄數(shù)。
數(shù)據(jù)庫(kù)和數(shù)據(jù)表的信息：包含了數(shù)據(jù)庫(kù)及數(shù)據(jù)表的結(jié)構(gòu)信息。
MySQL服務(wù)器信息：包含了數(shù)據(jù)庫(kù)服務(wù)器的當(dāng)前狀態(tài)，版本號(hào)等。
在MySQL的命令提示符中，我們可以很容易的獲取以上服務(wù)器信息。

MySQL 序列使用

MySQL 序列是一組整數(shù)：1, 2, 3, …，由于一張數(shù)據(jù)表只能有一個(gè)字段自增主鍵，如果你想實(shí)現(xiàn)其他字段也實(shí)現(xiàn)自動(dòng)增加，就可以使用MySQL序列來實(shí)現(xiàn)。

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

使用 AUTO_INCREMENT

MySQL 中最簡(jiǎn)單使用序列的方法就是使用 MySQL AUTO_INCREMENT 來定義列。

實(shí)例
以下實(shí)例中創(chuàng)建了數(shù)據(jù)表 insect， insect 表中 id 無需指定值可實(shí)現(xiàn)自動(dòng)增長(zhǎng)。

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

重置序列

如果你刪除了數(shù)據(jù)表中的多條記錄，并希望對(duì)剩下數(shù)據(jù)的AUTO_INCREMENT列進(jìn)行重新排列，那么你可以通過刪除自增的列，然后重新添加來實(shí)現(xiàn)。不過該操作要非常小心，如果在刪除的同時(shí)又有新記錄添加，有可能會(huì)出現(xiàn)數(shù)據(jù)混亂。操作如下所示：

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

設(shè)置序列的開始值

一般情況下序列的開始值為1，但如果你需要指定一個(gè)開始值100，那我們可以通過以下語(yǔ)句來實(shí)現(xiàn)：

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;

或者你也可以在表創(chuàng)建成功后，通過以下語(yǔ)句來實(shí)現(xiàn)：

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

MySQL 處理重復(fù)數(shù)據(jù)

有些 MySQL 數(shù)據(jù)表中可能存在重復(fù)的記錄，有些情況我們?cè)试S重復(fù)數(shù)據(jù)的存在，但有時(shí)候我們也需要?jiǎng)h除這些重復(fù)的數(shù)據(jù)。

本章節(jié)我們將為大家介紹如何防止數(shù)據(jù)表出現(xiàn)重復(fù)數(shù)據(jù)及如何刪除數(shù)據(jù)表中的重復(fù)數(shù)據(jù)。

防止表中出現(xiàn)重復(fù)數(shù)據(jù)

你可以在 MySQL 數(shù)據(jù)表中設(shè)置指定的字段為 PRIMARY KEY（主鍵）或者 UNIQUE（唯一）索引來保證數(shù)據(jù)的唯一性。
讓我們嘗試一個(gè)實(shí)例：下表中無索引及主鍵，所以該表允許出現(xiàn)多條重復(fù)記錄。

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

方法1：設(shè)置主鍵

如果你想設(shè)置表中字段 first_name，last_name 數(shù)據(jù)不能重復(fù)，你可以設(shè)置雙主鍵模式來設(shè)置數(shù)據(jù)的唯一性，如果你設(shè)置了雙主鍵，那么那個(gè)鍵的默認(rèn)值不能為 NULL，可設(shè)置為 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) );

如果我們?cè)O(shè)置了唯一索引，那么在插入重復(fù)數(shù)據(jù)時(shí)，SQL 語(yǔ)句將無法執(zhí)行成功,并拋出錯(cuò)。

INSERT IGNORE INTO 與 INSERT INTO 的區(qū)別就是 INSERT IGNORE 會(huì)忽略數(shù)據(jù)庫(kù)中已經(jīng)存在的數(shù)據(jù)，如果數(shù)據(jù)庫(kù)沒有數(shù)據(jù)，就插入新的數(shù)據(jù)，如果有數(shù)據(jù)的話就跳過這條數(shù)據(jù)。這樣就可以保留數(shù)據(jù)庫(kù)中已經(jīng)存在數(shù)據(jù)，達(dá)到在間隙中插入數(shù)據(jù)的目的。

以下實(shí)例使用了 INSERT IGNORE INTO，執(zhí)行后不會(huì)出錯(cuò)，也不會(huì)向數(shù)據(jù)表中插入重復(fù)數(shù)據(jù)：

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 當(dāng)插入數(shù)據(jù)時(shí)，在設(shè)置了記錄的唯一性后，如果插入重復(fù)數(shù)據(jù)，將不返回錯(cuò)誤，只以警告形式返回。而 REPLACE INTO 如果存在 primary 或 unique 相同的記錄，則先刪除掉。再插入新記錄。

方法2 ：設(shè)置數(shù)據(jù)的唯一性方法是添加一個(gè) UNIQUE 索引

另一種設(shè)置數(shù)據(jù)的唯一性方法是添加一個(gè) 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) );

統(tǒng)計(jì)重復(fù)數(shù)據(jù)

以下我們將統(tǒng)計(jì)表中 first_name 和 last_name的重復(fù)記錄數(shù)：

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

以上查詢語(yǔ)句將返回 person_tbl 表中重復(fù)的記錄數(shù)。一般情況下，查詢重復(fù)的值，請(qǐng)執(zhí)行以下操作：

確定哪一列包含的值可能會(huì)重復(fù)。
在列選擇列表使用COUNT(*)列出的那些列。
在GROUP BY子句中列出的列。
HAVING子句設(shè)置重復(fù)數(shù)大于1。

過濾重復(fù)數(shù)據(jù)

如果你需要讀取不重復(fù)的數(shù)據(jù)可以在 SELECT 語(yǔ)句中使用 DISTINCT 關(guān)鍵字來過濾重復(fù)數(shù)據(jù)。

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

你也可以使用 GROUP BY 來讀取數(shù)據(jù)表中不重復(fù)的數(shù)據(jù)：

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

刪除重復(fù)數(shù)據(jù)

如果你想刪除數(shù)據(jù)表中的重復(fù)數(shù)據(jù)，你可以使用以下的SQL語(yǔ)句：

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;

當(dāng)然你也可以在數(shù)據(jù)表中添加 INDEX（索引）和 PRIMAY KEY（主鍵）這種簡(jiǎn)單的方法來刪除表中的重復(fù)記錄。方法如下：

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

MySQL 運(yùn)算符

本章節(jié)我們主要介紹 MySQL 的運(yùn)算符及運(yùn)算符的優(yōu)先級(jí)。 MySQL 主要有以下幾種運(yùn)算符：

算術(shù)運(yùn)算符
比較運(yùn)算符
邏輯運(yùn)算符
位運(yùn)算符

級(jí)聯(lián)刪除和級(jí)聯(lián)更新

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)

分別插入數(shù)據(jù)

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表中插入數(shù)據(jù)時(shí)，若插入的sid為22，則會(huì)插入失敗，違反外鍵約束，因?yàn)橥怄Isid
來自stu表中的id的主鍵，即stu中的id沒有等于22的數(shù)據(jù)。

級(jí)聯(lián)刪除：將stu表中id為2的學(xué)生刪除，該學(xué)生在sc表中的成績(jī)也會(huì)級(jí)聯(lián)刪除

delete from stu where sid = '2';

級(jí)聯(lián)更新：stu表中id為3的學(xué)生更改為id為6，該學(xué)生在sc表中的對(duì)應(yīng)id也會(huì)級(jí)聯(lián)更新

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

注意

刪除表的時(shí)候必須先刪除外鍵表(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 菜鳥聯(lián)盟

總結(jié)

以上是生活随笔為你收集整理的mysql快速了解的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

MySQL
快速

上一篇：第一百一十二期:96秒100亿！如何抗住
下一篇：第四十期:九个对Web开发者最有用的Py

3atv精品不卡视频,97人人超碰国产精品最新,中文字幕av一区二区三区人妻少妇,久久久精品波多野结衣,日韩一区二区三区精品

数据库

mysql快速了解

文章目錄

數(shù)據(jù)庫(kù)了解：

快速操作：

安裝mysql

啟動(dòng),關(guān)閉,重啟mysql服務(wù)

連接mysql的root用戶

創(chuàng)建數(shù)據(jù)庫(kù)

刪除數(shù)據(jù)庫(kù)

選擇數(shù)據(jù)庫(kù)

mysql 數(shù)據(jù)類型

MySQL 創(chuàng)建數(shù)據(jù)表

MySQL 刪除數(shù)據(jù)表

MySQL 插入數(shù)據(jù)

MySQL 查詢數(shù)據(jù)

MySQL WHERE 子句

BINARY 關(guān)鍵字

MySQL UPDATE 更新

批量更新1：使用case when

批量更新2：創(chuàng)建臨時(shí)表，先更新臨時(shí)表，然后從臨時(shí)表中update

1.創(chuàng)建臨時(shí)表

1.1 首先獲取原表中的字段結(jié)構(gòu)：

1.2 創(chuàng)建臨時(shí)表

2.在臨時(shí)表中插入要修改的數(shù)據(jù)

3.更新原表

3.1 我們先更新了runoob_title字段：

3.2 接著更新runoob_author字段

3.3接著更新submission_date字段

MySQL DELETE 語(yǔ)句

MySQL LIKE 子句

MySQL UNION 操作符

SQL UNION 實(shí)例

SQL UNION ALL 實(shí)例

帶有 WHERE 的 SQL UNION ALL

MySQL 排序

MySQL GROUP BY 語(yǔ)句

使用 WITH ROLLUP

Mysql 連接的使用

INNER JOIN

LEFT JOIN

RIGHT JOIN

MySQL NULL 值處理

MySQL 中處理 NULL 使用 IS NULL 和 IS NOT NULL 運(yùn)算符。

MySQL 事務(wù)

使用保留點(diǎn) SAVEPOINT

MySQL ALTER命令

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

1.1指定新增字段的位置

2.修改字段類型及名稱

2.1修改字段類型

3.修改字段默認(rèn)值

4.修改表名

MySQL 索引

普通索引

創(chuàng)建索引

修改表結(jié)構(gòu)(添加索引)

創(chuàng)建表的時(shí)候直接指定

刪除索引的語(yǔ)法

唯一索引

創(chuàng)建索引

修改表結(jié)構(gòu)

創(chuàng)建表的時(shí)候直接指定

使用ALTER 命令添加和刪除索引

顯示索引信息

MySQL 臨時(shí)表

刪除MySQL 臨時(shí)表

臨時(shí)表的應(yīng)用場(chǎng)景

1.批量更新數(shù)據(jù)

MySQL 復(fù)制表

步驟一：獲取數(shù)據(jù)表的完整結(jié)構(gòu)。

步驟二：修改SQL語(yǔ)句的數(shù)據(jù)表名，并執(zhí)行SQL語(yǔ)句。

步驟三：拷貝全部數(shù)據(jù)

MySQL 元數(shù)據(jù)

MySQL 序列使用

使用 AUTO_INCREMENT

獲取AUTO_INCREMENT值

重置序列

設(shè)置序列的開始值