MySQL 是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，在 WEB 應(yīng)用方面 MySQL 是最好的 RDBMS(Relational Database Management System：關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng))應(yīng)用軟件之一

介紹

什么是數(shù)據(jù)庫(kù)

數(shù)據(jù)庫(kù)（Database）是按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲(chǔ)和管理數(shù)據(jù)的倉(cāng)庫(kù)。每個(gè)數(shù)據(jù)庫(kù)都有一個(gè)或多個(gè)不同的 API 用于創(chuàng)建，訪問，管理，搜索和復(fù)制所保存的數(shù)據(jù)。我們也可以將數(shù)據(jù)存儲(chǔ)在文件中，但是在文件中讀寫數(shù)據(jù)速度相對(duì)較慢。所以，現(xiàn)在我們使用關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)（RDBMS）來存儲(chǔ)和管理大數(shù)據(jù)量。所謂的關(guān)系型數(shù)據(jù)庫(kù)，是建立在關(guān)系模型基礎(chǔ)上的數(shù)據(jù)庫(kù)，借助于集合代數(shù)等數(shù)學(xué)概念和方法來處理數(shù)據(jù)庫(kù)中的數(shù)據(jù)。

MySQL數(shù)據(jù)庫(kù)

MySQL 是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典 MySQL AB 公司開發(fā)，目前屬于 Oracle 公司。MySQL 是一種關(guān)聯(lián)數(shù)據(jù)庫(kù)管理系統(tǒng)，關(guān)聯(lián)數(shù)據(jù)庫(kù)將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個(gè)大倉(cāng)庫(kù)內(nèi)，這樣就增加了速度并提高了靈活性。

• MySQL 是開源的，目前隸屬于 Oracle 旗下產(chǎn)品。
• MySQL 支持大型的數(shù)據(jù)庫(kù)。可以處理?yè)碛猩锨f條記錄的大型數(shù)據(jù)庫(kù)。
• MySQL 使用標(biāo)準(zhǔn)的 SQL 數(shù)據(jù)語言形式。
• MySQL 可以運(yùn)行于多個(gè)系統(tǒng)上，并且支持多種語言。這些編程語言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
• MySQL 對(duì)PHP有很好的支持，PHP 是目前最流行的 Web 開發(fā)語言。
• MySQL 支持大型數(shù)據(jù)庫(kù)，支持 5000 萬條記錄的數(shù)據(jù)倉(cāng)庫(kù)，32 位系統(tǒng)表文件最大可支持 4GB，64 位系統(tǒng)支持最大的表文件為8TB。
• MySQL 是可以定制的，采用了 GPL 協(xié)議，你可以修改源碼來開發(fā)自己的 MySQL 系統(tǒng)。

RDBMS 術(shù)語

在我們開始學(xué)習(xí)MySQL 數(shù)據(jù)庫(kù)前，讓我們先了解下RDBMS的一些術(shù)語

• 數(shù)據(jù)庫(kù): 數(shù)據(jù)庫(kù)是一些關(guān)聯(lián)表的集合。
• 數(shù)據(jù)表: 表是數(shù)據(jù)的矩陣。在一個(gè)數(shù)據(jù)庫(kù)中的表看起來像一個(gè)簡(jiǎn)單的電子表格。
• 列: 一列(數(shù)據(jù)元素) 包含了相同類型的數(shù)據(jù), 例如郵政編碼的數(shù)據(jù)。
• 行：一行（=元組，或記錄）是一組相關(guān)的數(shù)據(jù)，例如一條用戶訂閱的數(shù)據(jù)。
• 冗余：存儲(chǔ)兩倍數(shù)據(jù)，冗余降低了性能，但提高了數(shù)據(jù)的安全性。
• 主鍵：主鍵是唯一的。一個(gè)數(shù)據(jù)表中只能包含一個(gè)主鍵。你可以使用主鍵來查詢數(shù)據(jù)。
• 外鍵：外鍵用于關(guān)聯(lián)兩個(gè)表。
• 復(fù)合鍵：復(fù)合鍵（組合鍵）將多個(gè)列作為一個(gè)索引鍵，一般用于復(fù)合索引。
• 索引：使用索引可快速訪問數(shù)據(jù)庫(kù)表中的特定信息。索引是對(duì)數(shù)據(jù)庫(kù)表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)。類似于書籍的目錄。
• 參照完整性: 參照的完整性要求關(guān)系中不允許引用不存在的實(shí)體。與實(shí)體完整性是關(guān)系模型必須滿足的完整性約束條件，目的是保證數(shù)據(jù)的一致性。

MySQL 為關(guān)系型數(shù)據(jù)庫(kù)(Relational Database Management System), 這種所謂的關(guān)系型可以理解為表格的概念, 一個(gè)關(guān)系型數(shù)據(jù)庫(kù)由一個(gè)或數(shù)個(gè)表格組成, 如圖所示的一個(gè)表格

數(shù)據(jù)庫(kù)表的存儲(chǔ)位置

MySQL數(shù)據(jù)表以文件方式存放在磁盤中：

• 包括表文件、數(shù)據(jù)文件以及數(shù)據(jù)庫(kù)的選項(xiàng)文件
• 位置：MySQL安裝目錄data下存放數(shù)據(jù)表。目錄名對(duì)應(yīng)數(shù)據(jù)庫(kù)名，該目錄下文件名對(duì)應(yīng)數(shù)據(jù)表

注：

InnoDB類型數(shù)據(jù)表只有一個(gè)*. frm文件，以及上一級(jí)目錄的ibdata1文件
MylSAM類型數(shù)據(jù)表對(duì)應(yīng)三個(gè)文件：

• *. frm —— 表結(jié)構(gòu)定義文件
• *. MYD —— 數(shù)據(jù)文件
• *. MYI —— 索引文件

存儲(chǔ)位置：因操作系統(tǒng)而異，可查my.ini

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

MySQL提供的數(shù)據(jù)類型包括數(shù)值類型（整數(shù)類型和小數(shù)類型）、字符串類型、日期類型、復(fù)合類型（復(fù)合類型包括enum類型和set類型）以及二進(jìn)制類型 。

一. 整數(shù)類型

• 整數(shù)類型的數(shù)，默認(rèn)情況下既可以表示正整數(shù)又可以表示負(fù)整數(shù)（此時(shí)稱為有符號(hào)數(shù)）。如果只希望表示零和正整數(shù)，可以使用無符號(hào)關(guān)鍵字“unsigned”對(duì)整數(shù)類型進(jìn)行修飾。
• 各個(gè)類別存儲(chǔ)空間及取值范圍。

二. 小數(shù)類型

• decimal(length, precision)用于表示精度確定（小數(shù)點(diǎn)后數(shù)字的位數(shù)確定）的小數(shù)類型，length決定了該小數(shù)的最大位數(shù)，precision用于設(shè)置精度（小數(shù)點(diǎn)后數(shù)字的位數(shù)）。

• 例如： decimal (5,2)表示小數(shù)取值范圍：999.99～999.99 decimal (5,0)表示： -99999～99999的整數(shù)。

• 各個(gè)類別存儲(chǔ)空間及取值范圍。

三. 字符串

• char()與varchar(): 例如對(duì)于簡(jiǎn)體中文字符集gbk的字符串而言，varchar(255)表示可以存儲(chǔ)255個(gè)漢字，而每個(gè)漢字占用兩個(gè)字節(jié)的存儲(chǔ)空間。假如這個(gè)字符串沒有那么多漢字，例如僅僅包含一個(gè)‘中’字，那么varchar(255)僅僅占用1個(gè)字符（兩個(gè)字節(jié)）的儲(chǔ)存空間；而char(255)則必須占用255個(gè)字符長(zhǎng)度的存儲(chǔ)空間，哪怕里面只存儲(chǔ)一個(gè)漢字。
• 各個(gè)類別存儲(chǔ)空間及取值范圍。

四. 日期類型

1、date表示日期，默認(rèn)格式為‘YYYY-MM-DD’； time表示時(shí)間，格式為‘HH:ii:ss’； year表示年份； datetime與timestamp是日期和時(shí)間的混合類型，格式為’YYYY-MM-DD HH:ii:ss’。

2、datetime與timestamp都是日期和時(shí)間的混合類型，區(qū)別在于： 表示的取值范圍不同，datetime的取值范圍遠(yuǎn)遠(yuǎn)大于timestamp的取值范圍。 將NULL插入timestamp字段后，該字段的值實(shí)際上是MySQL服務(wù)器當(dāng)前的日期和時(shí)間。 同一個(gè)timestamp類型的日期或時(shí)間，不同的時(shí)區(qū)，顯示結(jié)果不同。

3、各個(gè)類別存儲(chǔ)空間及取值范圍。

五. 復(fù)合類型

MySQL 支持兩種復(fù)合數(shù)據(jù)類型：enum枚舉類型和set集合類型。 enum類型的字段類似于單選按鈕的功能，一個(gè)enum類型的數(shù)據(jù)最多可以包含65535個(gè)元素。 set 類型的字段類似于復(fù)選框的功能，一個(gè)set類型的數(shù)據(jù)最多可以包含64個(gè)元素。

六. 二進(jìn)制類型

二進(jìn)制類型的字段主要用于存儲(chǔ)由‘0’和‘1’組成的字符串，因此從某種意義上將，二進(jìn)制類型的數(shù)據(jù)是一種特殊格式的字符串。二進(jìn)制類型與字符串類型的區(qū)別在于：字符串類型的數(shù)據(jù)按字符為單位進(jìn)行存儲(chǔ)，因此存在多種字符集、多種字符序；而二進(jìn)制類型的數(shù)據(jù)按字節(jié)為單位進(jìn)行存儲(chǔ)，僅存在二進(jìn)制字符集binary。

約束

約束是一種限制，它通過對(duì)表的行或列的數(shù)據(jù)做出限制，來確保表的數(shù)據(jù)的完整性、唯一性。

下面文章就來給大家介紹一下6種mysql常見的約束，希望對(duì)大家有所幫助。

一. 非空約束(not null)

• 非空約束用于確保當(dāng)前列的值不為空值，非空約束只能出現(xiàn)在表對(duì)象的列上。
• Null類型特征：所有的類型的值都可以是null，包括int、float 等數(shù)據(jù)類型

二. 唯一性約束(unique)

• 唯一約束是指定table的列或列組合不能重復(fù)，保證數(shù)據(jù)的唯一性。
• 唯一約束不允許出現(xiàn)重復(fù)的值，但是可以為多個(gè)null。
• 同一個(gè)表可以有多個(gè)唯一約束，多個(gè)列組合的約束。
• 在創(chuàng)建唯一約束時(shí)，如果不給唯一約束名稱，就默認(rèn)和列名相同。
• 唯一約束不僅可以在一個(gè)表內(nèi)創(chuàng)建，而且可以同時(shí)多表創(chuàng)建組合唯一約束。

三. 主鍵約束(primary key) PK

• 主鍵約束相當(dāng)于 唯一約束 + 非空約束 的組合，主鍵約束列不允許重復(fù)，也不允許出現(xiàn)空值。

• 每個(gè)表最多只允許一個(gè)主鍵，建立主鍵約束可以在列級(jí)別創(chuàng)建，也可以在表級(jí)別創(chuàng)建。

• 當(dāng)創(chuàng)建主鍵的約束時(shí)，系統(tǒng)默認(rèn)會(huì)在所在的列和列組合上建立對(duì)應(yīng)的唯一索引。

四. 外鍵約束(foreign key) FK

• 外鍵約束是用來加強(qiáng)兩個(gè)表（主表和從表）的一列或多列數(shù)據(jù)之間的連接的，可以保證一個(gè)或兩個(gè)表之間的參照完整性，外鍵是構(gòu)建于一個(gè)表的兩個(gè)字段或是兩個(gè)表的兩個(gè)字段之間的參照關(guān)系。
• 創(chuàng)建外鍵約束的順序是先定義主表的主鍵，然后定義從表的外鍵。也就是說只有主表的主鍵才能被從表用來作為外鍵使用，被約束的從表中的列可以不是主鍵，主表限制了從表更新和插入的操作。

五. 默認(rèn)值約束 (Default)

若在表中定義了默認(rèn)值約束，用戶在插入新的數(shù)據(jù)行時(shí)，如果該行沒有指定數(shù)據(jù)，那么系統(tǒng)將默認(rèn)值賦給該列，如果我們不設(shè)置默認(rèn)值，系統(tǒng)默認(rèn)為NULL。

六. 自增約束(AUTO_INCREMENT)

• 自增約束(AUTO_INCREMENT)可以約束任何一個(gè)字段，該字段不一定是PRIMARY KEY字段，也就是說自增的字段并不等于主鍵字段。
• 但是PRIMARY_KEY約束的主鍵字段，一定是自增字段，即PRIMARY_KEY 要與AUTO_INCREMENT一起作用于同一個(gè)字段。

當(dāng)插入第一條記錄時(shí)，自增字段沒有給定一個(gè)具體值，可以寫成DEFAULT/NULL，那么以后插入字段的時(shí)候，該自增字段就是從1開始，沒插入一條記錄，該自增字段的值增加1。當(dāng)插入第一條記錄時(shí)，給自增字段一個(gè)具體值，那么以后插入的記錄在此自增字段上的值，就在第一條記錄該自增字段的值的基礎(chǔ)上每次增加1。也可以在插入記錄的時(shí)候，不指定自增字段，而是指定其余字段進(jìn)行插入記錄的操作。

常用命令

登錄數(shù)據(jù)庫(kù)相關(guān)命令

一. 啟動(dòng)服務(wù)

語法：

mysql> net stop mysql

二. 關(guān)閉服務(wù)

語法：

mysql> net start mysql

三. 鏈接MySQL

語法：mysql -u用戶名 -p密碼;

root@243ecf24bd0a:/ mysql -uroot -p123456;

在以上命令行中，mysql 代表客戶端命令，-u 后面跟連接的數(shù)據(jù)庫(kù)用戶，-p 表示需要輸入密碼。如果數(shù)據(jù)庫(kù)設(shè)置正常，并輸入正確的密碼，將看到上面一段歡迎界面和一個(gè) mysql>提示符。

四. 退出數(shù)據(jù)庫(kù)

語法：quit

mysql> quit

結(jié)果：

DDL（Data Definition Languages）

語句：即數(shù)據(jù)庫(kù)定義語句

對(duì)于數(shù)據(jù)庫(kù)而言實(shí)際上每一張表都表示是一個(gè)數(shù)據(jù)庫(kù)的對(duì)象，而數(shù)據(jù)庫(kù)對(duì)象指的就是DDL定義的所有操作，例如：表，視圖，索引，序列，約束等等，都屬于對(duì)象的操作，所以表的建立就是對(duì)象的建立，而對(duì)象的操作主要分為以下三類語法

• 創(chuàng)建對(duì)象：CREATE 對(duì)象名稱;
• 刪除對(duì)象：DROP 對(duì)象名稱;
• 修改對(duì)象：ALTER 對(duì)象名稱;

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

語法：create database 數(shù)據(jù)庫(kù)名字;

mysql> create database sqltest;

結(jié)果：

二. 查看已經(jīng)存在的數(shù)據(jù)庫(kù)

語法：show databases;

mysql> show databases;

結(jié)果：

• information_schema：主要存儲(chǔ)了系統(tǒng)中的一些數(shù)據(jù)庫(kù)對(duì)象信息。比如用戶表信息、列信息、權(quán)限信息、字符集信息、分區(qū)信息等。
• cluster：存儲(chǔ)了系統(tǒng)的集群信息。
• mysql：存儲(chǔ)了系統(tǒng)的用戶權(quán)限信息。
• test：系統(tǒng)自動(dòng)創(chuàng)建的測(cè)試數(shù)據(jù)庫(kù)，任何用戶都可以使用。

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

語法：use 數(shù)據(jù)庫(kù)名;

mysql> use mzc-test;

返回Database changed代表我們已經(jīng)選擇 sqltest 數(shù)據(jù)庫(kù)，后續(xù)所有操作將在 sqltest 數(shù)據(jù)庫(kù)上執(zhí)行。

有些人可能會(huì)問到，連接以后怎么退出。其實(shí)，不用退出來，use 數(shù)據(jù)庫(kù)后，使用show databases就能查詢所有數(shù)據(jù)庫(kù)，如果想跳到其他數(shù)據(jù)庫(kù)，用use 其他數(shù)據(jù)庫(kù)名字。

四. 查看數(shù)據(jù)庫(kù)中的表

語法：show tables;

mysql> show tables;

結(jié)果：

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

語法：drop database 數(shù)據(jù)庫(kù)名稱;

mysql> drop database mzc-test;

結(jié)果：

注意：刪除時(shí)，最好用 `` 符號(hào)把表明括起來

六. 設(shè)置表的類型

MySQL的數(shù)據(jù)表類型：MyISAM、InnoDB、HEAP、 BOB、CSV等

語法：

CREATE TABLE 表名(	#省略代碼）ENGINE= InnoDB;

適用場(chǎng)景：

1. 使用MyISAM：節(jié)約空間及響應(yīng)速度快；不需事務(wù)，空間小，以查詢?cè)L問為主
2. 使用InnoDB：安全性，事務(wù)處理及多用戶操作數(shù)據(jù)表；多刪除、更新操作，安全性高，事務(wù)處理及并發(fā)控制

1. 查看mysql所支持的引擎類型

語法：

SHOW ENGINES

結(jié)果：

2. 查看默認(rèn)引擎

語法：

SHOW VARIABLES LIKE "storage_engine";

結(jié)果：

數(shù)據(jù)庫(kù)表相關(guān)操作

一. 創(chuàng)建表

語法：create table 表名 {列名,數(shù)據(jù)類型,約束條件};

CREATE TABLE `Student`(	`s_id` VARCHAR(20),	`s_name` VARCHAR(20) NOT NULL DEFAULT "",	`s_birth` VARCHAR(20) NOT NULL DEFAULT "",	`s_sex` VARCHAR(10) NOT NULL DEFAULT "",	PRIMARY KEY(`s_id`));

結(jié)果

注意：表名還請(qǐng)遵守?cái)?shù)據(jù)庫(kù)的命名規(guī)則，這條數(shù)據(jù)后面要進(jìn)行刪除，所以首字母為大寫。

二. 查看表定義

語法：desc 表名

mysql> desc Student;

結(jié)果：

雖然 desc 命令可以查看表定義，但是其輸出的信息還是不夠全面，為了查看更全面的表定義信息，有時(shí)就需要通過查看創(chuàng)建表的 SQL 語句來得到，可以使用如下命令實(shí)現(xiàn)

語法：show create table 表名 G;

mysql> show create table Student G;

結(jié)果：

從上面表的創(chuàng)建 SQL 語句中，除了可以看到表定義以外，還可以看到表的engine（存儲(chǔ)引擎）和charset（字符集）等信息。G選項(xiàng)的含義是使得記錄能夠按照字段豎著排列，對(duì)于內(nèi)容比較長(zhǎng)的記錄更易于顯示。

三. 刪除表

語法：drop table 表名

mysql> drop table Student;

結(jié)果：

四. 修改表 (重要)

對(duì)于已經(jīng)創(chuàng)建好的表，尤其是已經(jīng)有大量數(shù)據(jù)的表，如果需要對(duì)表做一些結(jié)構(gòu)上的改變，我們可以先將表刪除（drop），然后再按照新的表定義重建表。這樣做沒有問題，但是必然要做一些額外的工作，比如數(shù)據(jù)的重新加載。而且，如果有服務(wù)在訪問表，也會(huì)對(duì)服務(wù)產(chǎn)生影響。因此，在大多數(shù)情況下，表結(jié)構(gòu)的更改一般都使用 alter table語句，以下是一些常用的命令。

1. 修改表類型

語法：ALTER TABLE 表名 MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

例如，修改表 student 的 s_name 字段定義，將 varchar(20)改為 varchar(30)

mysql> alter table Student modify s_name varchar(30);

結(jié)果：

2. 增加表字段

語法：ALTER TABLE 表名 ADD [COLUMN] [FIRST | AFTER col_name];

例如，表 student 上新增加字段 s_test，類型為 int(3)

mysql> alter table student add column s_test int(3);

結(jié)果：

3. 刪除表字段

語法：ALTER TABLE 表名 DROP [COLUMN] col_name

例如，將字段 s_test 刪除掉

mysql> alter table Student drop column s_test;

結(jié)果：

4. 字段改名

語法：ALTER TABLE 表名 CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]

例如，將 s_sex 改名為 s_sex1，同時(shí)修改字段類型為 int(4)

mysql> alter table Student change s_sex s_sex1 int(4);

結(jié)果：

注意：change 和 modify 都可以修改表的定義，不同的是 change 后面需要寫兩次列名，不方便。但是 change 的優(yōu)點(diǎn)是可以修改列名稱，modify 則不能。

5. 修改字段排列順序

前面介紹的的字段增加和修改語法（ADD/CNAHGE/MODIFY）中，都有一個(gè)可選項(xiàng)first|after column_name，這個(gè)選項(xiàng)可以用來修改字段在表中的位置，默認(rèn) ADD 增加的新字段是加在表的最后位置，而 CHANGE/MODIFY 默認(rèn)都不會(huì)改變字段的位置。

例如，將新增的字段 s_test 加在 s_id 之后

語法：alter table 表名 add 列名 數(shù)據(jù)類型 after 列名;

mysql> alter table Student add s_test date after s_id;

結(jié)果：

修改已有字段 s_name，將它放在最前面

mysql> alter table Student modify s_name varchar(30) default ‘’ first;

結(jié)果：

注意：CHANGE/FIRST|AFTER COLUMN 這些關(guān)鍵字都屬于 MySQL 在標(biāo)準(zhǔn) SQL 上的擴(kuò)展，在其他數(shù)據(jù)庫(kù)上不一定適用。

6.表名修改

語法：ALTER TABLE 表名 RENAME [TO] new_tablename

例如，將表 Student 改名為 student

mysql> alter table Student rename student;

結(jié)果：

DML（Data Manipulation Language）

語句：即數(shù)據(jù)操縱語句 用于操作數(shù)據(jù)庫(kù)對(duì)象中所包含的數(shù)據(jù)

一. 添加數(shù)據(jù)：INSERT

Insert 語句用于向數(shù)據(jù)庫(kù)中插入數(shù)據(jù)

1. 插入單條數(shù)據(jù)（常用）

語法：insert into 表名(列名1,列名2,...) values(值1,值2,...)

特點(diǎn)：

插入值的類型要與列的類型一致或兼容。插入NULL可實(shí)現(xiàn)為列插入NULL值。列的順序可以調(diào)換。列數(shù)和值的個(gè)數(shù)必須一致。可省略列名，默認(rèn)所有列，并且列的順序和表中列的順序一致。

案例：

-- 插入學(xué)生表測(cè)試數(shù)據(jù)insert into Student(s_id,s_name,s_birth,s_sex) values("01" , "趙信" , "1990-01-01" , "男");

2. 插入單條數(shù)據(jù)

語法：INSERT INTO 表名 SET 列名 = 值，列名 = 值

這種方式每次只能插入一行數(shù)據(jù)，每列的值通過賦值列表制定。

案例：

INSERT INTO student SET s_id="02",s_name="德萊厄斯",s_birth="1990-01-01",s_sex="男"

3. 插入多條數(shù)據(jù)

語法：insert into 表名 values(值1,值2,值3),(值4,值5,值6),(值7,值8,值9);

案例：

INSERT INTO student VALUES("03","艾希","1990-01-01","女"),("04","德萊文","1990-08-06","男"),("05","俄洛依","1991-12-01","女");

上面的例子中，值1,值2,值3),(值4,值5,值6),(值7,值8,值9) 即為 Value List，其中每個(gè)括號(hào)內(nèi)部的數(shù)據(jù)表示一行數(shù)據(jù)，這個(gè)例子中插入了三行數(shù)據(jù)。Insert 語句也可以只給部分列插入數(shù)據(jù)，這種情況下，需要在 Value List 之前加上 ColumnName List，

例如：

INSERT INTO student(s_name,s_sex) VALUES("艾希","女"),("德萊文","男");

每行數(shù)據(jù)只指定了 s_name 和 s_sex 這兩列的值，其他列的值會(huì)設(shè)為 Null。

4. 表數(shù)據(jù)復(fù)制

語法：INSERT INTO 表名 SELECT * from 表名;

案例：

INSERT INTO student SELECT * from student1;

注意：

兩個(gè)表的字段需要一直，并盡量保證要新增的表中沒有數(shù)據(jù)

二. 更新數(shù)據(jù)：UPDATE

Update 語句一共有兩種語法，分別用于更新單表數(shù)據(jù)和多表數(shù)據(jù)。

注意：沒有 WHERE 條件的 UPDATE 會(huì)更新所有值！

1. 修改一條數(shù)據(jù)的某個(gè)字段

語法：UPDATE 表名 SET 字段名 =值 where 字段名=值

案例：

UPDATE student SET s_name ="張三" WHERE s_id ="01"

2. 修改多個(gè)字段為同一的值

語法：UPDATE 表名 SET 字段名= 值 WHERE 字段名 in ('值1','值2','值3');

案例：

UPDATE student SET s_name = "李四" WHERE s_id in ("01","02","03");

3. 使用case when實(shí)現(xiàn)批量更新

語法：update 表名 set 字段名 = case 字段名 when 值1 then '值' when 值2 then '值' when 值3 then '值' end where s_id in (值1,值2,值3)

案例：

update student set s_name = case s_id when 01 then "小王" when 02 then "小周" when 03 then "老周" end where s_id in (01,02,03)

這句sql的意思是，更新 s_name 字段，如果 s_id 的值為 01 則 s_name 的值為 小王，s_id = 02 則 s_name = 小周，如果s_id =03 則 s_name 的值為 老周。這里的where部分不影響代碼的執(zhí)行，但是會(huì)提高sql執(zhí)行的效率。確保sql語句僅執(zhí)行需要修改的行數(shù)，這里只有3條數(shù)據(jù)進(jìn)行更新，而where子句確保只有3行數(shù)據(jù)執(zhí)行。

案例 2：

UPDATE student SET s_birth = CASE s_nameWHEN "小王" THEN"2019-01-20"WHEN "小周" THEN"2019-01-22"END WHERE s_name IN ("小王","小周");

三. 刪除數(shù)據(jù)：DELETE

數(shù)據(jù)庫(kù)一旦刪除數(shù)據(jù)，它就會(huì)永遠(yuǎn)消失。 因此，在執(zhí)行DELETE語句之前，應(yīng)該先備份數(shù)據(jù)庫(kù)，以防萬一要找回刪除過的數(shù)據(jù)。 1. 刪除指定數(shù)據(jù)

語法：DELETE FROM 表名 WHERE 列名=值

注意：刪除的時(shí)候如果不指定where條件，則保留數(shù)據(jù)表結(jié)構(gòu)，刪除全部數(shù)據(jù)行，有主外鍵關(guān)系的都刪不了

案例：

DELETE FROM student WHERE s_id="09"

與 SELECT 語句不同的是，DELETE 語句中不能使用 GROUP BY、 HAVING 和 ORDER BY 三類子句，而只能使用WHERE 子句。

原因很簡(jiǎn)單， GROUP BY 和 HAVING 是從表中選取數(shù)據(jù)時(shí)用來改變抽取數(shù)據(jù)形式的， 而 ORDER BY 是用來指定取得結(jié)果顯示順序的。因此，在刪除表中數(shù)據(jù) 時(shí)它們都起不到什么作用。`

2. 刪除表中全部數(shù)據(jù)

語法：TRUNCATE 表名;

注意：全部刪除，內(nèi)存無痕跡，如果有自增會(huì)重新開始編號(hào)。

與 DELETE 不同的是，TRUNCATE 只能刪除表中的全部數(shù)據(jù)，而不能通過 WHERE 子句指定條件來刪除部分?jǐn)?shù)據(jù)。也正是因?yàn)樗荒芫唧w地控制刪除對(duì)象， 所以其處理速度比 DELETE 要快得多。實(shí)際上，DELETE 語句在 DML 語句中也 屬于處理時(shí)間比較長(zhǎng)的，因此需要?jiǎng)h除全部數(shù)據(jù)行時(shí)，使用 TRUNCATE 可以縮短 執(zhí)行時(shí)間。

案例：

TRUNCATE student1;

DQL（Data Query Language）

語句：即數(shù)據(jù)查詢語句 查詢數(shù)據(jù)庫(kù)中的記錄，關(guān)鍵字 SELECT，這塊內(nèi)容非常重要！

一. wherer 條件語句

語法：select 列名 from 表名 where 列名 =值

where的作用：

用于檢索數(shù)據(jù)表中符合條件的記錄搜索條件可由一個(gè)或多個(gè)邏輯表達(dá)式組成，結(jié)果一般為真或假

搜索條件的組成：

算數(shù)運(yùn)算符

邏輯操作符（操作符有兩種寫法）

比較運(yùn)算符

注意：數(shù)值數(shù)據(jù)類型的記錄之間才能進(jìn)行算術(shù)運(yùn)算，相同數(shù)據(jù)類型的數(shù)據(jù)之間才能進(jìn)行比較。

表數(shù)據(jù)

案例 1（AND）：

SELECT  * FROM student WHERE s_name ="小王" AND s_sex="男"

案例 2（OR）：

SELECT  * FROM student WHERE s_name ="崔絲塔娜" OR s_sex="男"

案例 3（NOT）：

SELECT  * FROM student WHERE NOT s_name ="崔絲塔娜" 

案例 4（IS NULL）：

SELECT * FROM student WHERE s_name IS NULL;

案例 5（IS NOT NULL）：

SELECT * FROM student WHERE s_name IS NOT NULL;

案例 6（BETWEEN）：

SELECT * FROM student WHERE s_birth BETWEEN "2019-01-20" AND "2019-01-22"

案例 7（LINK）：

SELECT * FROM student WHERE s_name LIKE "小%"

案例 8（IN）：

SELECT * FROM student WHERE s_name IN ("小王","小周")

二. as 取別名

表里的名字沒有變，只影響了查詢出來的結(jié)果

案例：

SELECT s_name as `name` FROM student 

使用as也可以為表取別名 （作用：?jiǎn)伪聿樵円饬x不大，但是當(dāng)多個(gè)表的時(shí)候取別名就好操作，當(dāng)不同的表里有相同名字的列的時(shí)候區(qū)分就會(huì)好區(qū)分）

三. distinct 去除重復(fù)記錄

注意：當(dāng)查詢結(jié)果中所有字段全都相同時(shí) 才算重復(fù)的記錄

案例

SELECT DISTINCT * FROM student

指定字段

星號(hào)表示所有字段

手動(dòng)指定需要查詢的字段

SELECT DISTINCT s_name,s_birth FROM student

還可也是四則運(yùn)算聚合函數(shù) 四. group by 分組 group by的意思是根據(jù)by對(duì)數(shù)據(jù)按照哪個(gè)字段進(jìn)行分組，或者是哪幾個(gè)字段進(jìn)行分組。

語法：

select 字段名 from 表名 group by 字段名稱;

1. 單個(gè)字段分組

SELECT COUNT(*)FROM student GROUP BY s_sex;

2. 多個(gè)字段分組

SELECT s_name,s_sex,COUNT(*) FROM student GROUP BY s_name,s_sex;

注意：多個(gè)字段進(jìn)行分組時(shí)，需要將s_name和s_sex看成一個(gè)整體，只要是s_name和s_sex相同的可以分成一組；如果只是s_sex相同，s_sex不同就不是一組。 五. having 過濾 HAVING 子句對(duì) GROUP BY 子句設(shè)置條件的方式與 WHERE 和 SELECT 的交互方式類似。WHERE 搜索條件在進(jìn)行分組操作之前應(yīng)用；而 HAVING 搜索條件在進(jìn)行分組操作之后應(yīng)用。HAVING 語法與 WHERE 語法類似，但 HAVING 可以包含聚合函數(shù)。HAVING 子句可以引用選擇列表中顯示的任意項(xiàng)。

我們?nèi)绻樵兡猩蛘吲藬?shù)大于4的性別

SELECT s_sex as 性別,count(s_id) AS 人數(shù) FROM student GROUP BY s_sex HAVING COUNT(s_id)>4

六. order by 排序

根據(jù)某個(gè)字段排序，默認(rèn)升序(從小到大)

語法：

select * from 表名 order by 字段名;

1. 一個(gè)字段，降序（從大到小）

SELECT * FROM student ORDER BY s_id DESC;

2. 多個(gè)字段

SELECT * FROM student ORDER BY s_id DESC, s_birth ASC;

多個(gè)字段 第一個(gè)相同在按照第二個(gè) asc 表示升序 limit 分頁(yè) 用于限制要顯示的記錄數(shù)量

語法1:

select * from table_name limit 個(gè)數(shù);

語法2:

select * from table_name limit 起始位置,個(gè)數(shù);

案例：

查詢前三條數(shù)據(jù)

SELECT * FROM student LIMIT 3;

從第三條開始 查詢3條

SELECT * FROM student LIMIT 2,3;

注意：起始位置 從0開始

經(jīng)典的使用場(chǎng)景:分頁(yè)顯示

每一頁(yè)顯示的條數(shù) a = 3明確當(dāng)前頁(yè)數(shù) b = 2計(jì)算起始位置 c = (b-1) * a 子查詢

將一個(gè)查詢語句的結(jié)果作為另一個(gè)查詢語句的條件或是數(shù)據(jù)來源， 當(dāng)我們一次性查不到想要數(shù)據(jù)時(shí)就需要使用子查詢。

SELECT*FROMscoreWHEREs_id =(SELECTs_idFROMstudentWHEREs_name = ‘趙信")

1. in 關(guān)鍵字子查詢 當(dāng)內(nèi)層查詢 (括號(hào)內(nèi)的) 結(jié)果會(huì)有多個(gè)結(jié)果時(shí), 不能使用 = 必須是in ,另外子查詢必須只能包含一列數(shù)據(jù)

子查詢的思路:

要分析 查到最終的數(shù)據(jù) 到底有哪些步驟

根據(jù)步驟寫出對(duì)應(yīng)的sql語句

把上一個(gè)步驟的sql語句丟到下一個(gè)sql語句中作為條件

SELECT*FROMscoreWHEREs_id IN (SELECTs_idFROMstudentWHEREs_sex = ‘男")

exists 關(guān)鍵字子查詢 當(dāng)內(nèi)層查詢 有結(jié)果時(shí) 外層才會(huì)執(zhí)行 多表查詢 1. 笛卡爾積查詢 笛卡爾積查詢的結(jié)果會(huì)出現(xiàn)大量的錯(cuò)誤數(shù)據(jù)即,數(shù)據(jù)關(guān)聯(lián)關(guān)系錯(cuò)誤,并且會(huì)產(chǎn)生重復(fù)的字段信息 ! 2. 內(nèi)連接查詢 本質(zhì)上就是笛卡爾積查詢，inner可以省略。

語法:

select * from  表1 inner join 表2;

3. 左外連接查詢 左邊的表無論是否能夠匹配都要完整顯示，右邊的僅展示匹配上的記錄

注意: 在外連接查詢中不能使用where 關(guān)鍵字 必須使用on專門來做表的對(duì)應(yīng)關(guān)系

4. 右外連接查詢 右邊的表無論是否能夠匹配都要完整顯示，左邊的僅展示匹配上的記錄

DCL（Data Control Language）

語句：即數(shù)據(jù)控制語句 DCL(Data Control Language)語句：數(shù)據(jù)控制語句，用于控制不同數(shù)據(jù)段直接的許可和訪問級(jí)別的語句。這些語句定義了數(shù)據(jù)庫(kù)、表、字段、用戶的訪問權(quán)限和安全級(jí)別。

關(guān)鍵字

• GRANT
• REVOKE

查看用戶權(quán)限

當(dāng)成功創(chuàng)建用戶賬戶后，還不能執(zhí)行任何操作，需要為該用戶分配適當(dāng)?shù)脑L問權(quán)限。

可以使用SHOW GRANTS FOR語句來查詢用戶的權(quán)限。

例如：

mysql> SHOW GRANTS FOR test;+-------------------------------------------+| Grants for test@% |+-------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO "test"@"%" |+-------------------------------------------+1 row in set (0.00 sec)

GRANT語句 對(duì)于新建的MySQL用戶，必須給它授權(quán)，可以用GRANT語句來實(shí)現(xiàn)對(duì)新建用戶的授權(quán)。 格式語法

GRANT    priv_type [(column_list)]      [, priv_type [(column_list)]] ...    ON [object_type] priv_level    TO user [auth_option] [, user [auth_option]] ...    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]    [WITH {GRANT OPTION | resource_option} ...]GRANT PROXY ON user    TO user [, user] ...    [WITH GRANT OPTION]object_type: {    TABLE  | FUNCTION  | PROCEDURE}priv_level: {    *  | *.*  | db_name.*  | db_name.tbl_name  | tbl_name  | db_name.routine_name}user:    (see Section 6.2.4, “Specifying Account Names”)auth_option: {    IDENTIFIED BY "auth_string"  | IDENTIFIED WITH auth_plugin  | IDENTIFIED WITH auth_plugin BY "auth_string"  | IDENTIFIED WITH auth_plugin AS "auth_string"  | IDENTIFIED BY PASSWORD "auth_string"}tls_option: {    SSL  | X509  | CIPHER "cipher"  | ISSUER "issuer"  | SUBJECT "subject"}resource_option: {  | MAX_QUERIES_PER_HOUR count  | MAX_UPDATES_PER_HOUR count  | MAX_CONNECTIONS_PER_HOUR count  | MAX_USER_CONNECTIONS count}

權(quán)限類型(priv_type) 授權(quán)的權(quán)限類型一般可以分為數(shù)據(jù)庫(kù)、表、列、用戶。 授予數(shù)據(jù)庫(kù)權(quán)限類型

授予數(shù)據(jù)庫(kù)權(quán)限時(shí)，priv_type可以指定為以下值：

• SELECT：表示授予用戶可以使用 SELECT 語句訪問特定數(shù)據(jù)庫(kù)中所有表和視圖的權(quán)限。
• INSERT：表示授予用戶可以使用 INSERT 語句向特定數(shù)據(jù)庫(kù)中所有表添加數(shù)據(jù)行的權(quán)限。
• DELETE：表示授予用戶可以使用 DELETE 語句刪除特定數(shù)據(jù)庫(kù)中所有表的數(shù)據(jù)行的權(quán)限。
• UPDATE：表示授予用戶可以使用 UPDATE 語句更新特定數(shù)據(jù)庫(kù)中所有數(shù)據(jù)表的值的權(quán)限。
• REFERENCES：表示授予用戶可以創(chuàng)建指向特定的數(shù)據(jù)庫(kù)中的表外鍵的權(quán)限。
• CREATE：表示授權(quán)用戶可以使用 CREATE TABLE 語句在特定數(shù)據(jù)庫(kù)中創(chuàng)建新表的權(quán)限。
• ALTER：表示授予用戶可以使用 ALTER TABLE 語句修改特定數(shù)據(jù)庫(kù)中所有數(shù)據(jù)表的權(quán)限。
• SHOW VIEW：表示授予用戶可以查看特定數(shù)據(jù)庫(kù)中已有視圖的視圖定義的權(quán)限。
• CREATE ROUTINE：表示授予用戶可以為特定的數(shù)據(jù)庫(kù)創(chuàng)建存儲(chǔ)過程和存儲(chǔ)函數(shù)的權(quán)限。
• ALTER ROUTINE：表示授予用戶可以更新和刪除數(shù)據(jù)庫(kù)中已有的存儲(chǔ)過程和存儲(chǔ)函數(shù)的權(quán)限。
• INDEX：表示授予用戶可以在特定數(shù)據(jù)庫(kù)中的所有數(shù)據(jù)表上定義和刪除索引的權(quán)限。
• DROP：表示授予用戶可以刪除特定數(shù)據(jù)庫(kù)中所有表和視圖的權(quán)限。
• CREATE TEMPORARY TABLES：表示授予用戶可以在特定數(shù)據(jù)庫(kù)中創(chuàng)建臨時(shí)表的權(quán)限。
• CREATE VIEW：表示授予用戶可以在特定數(shù)據(jù)庫(kù)中創(chuàng)建新的視圖的權(quán)限。
• EXECUTE ROUTINE：表示授予用戶可以調(diào)用特定數(shù)據(jù)庫(kù)的存儲(chǔ)過程和存儲(chǔ)函數(shù)的權(quán)限。
• LOCK TABLES：表示授予用戶可以鎖定特定數(shù)據(jù)庫(kù)的已有數(shù)據(jù)表的權(quán)限。
• SHOW DATABASES：表示授權(quán)可以使用SHOW DATABASES語句查看所有已有的數(shù)據(jù)庫(kù)的定義的權(quán)限。
• ALL或ALL PRIVILEGES：表示以上所有權(quán)限。

授予表權(quán)限時(shí)，priv_type可以指定為以下值：

• SELECT：授予用戶可以使用 SELECT 語句進(jìn)行訪問特定表的權(quán)限。
• INSERT：授予用戶可以使用 INSERT 語句向一個(gè)特定表中添加數(shù)據(jù)行的權(quán)限。
• DELETE：授予用戶可以使用 DELETE 語句從一個(gè)特定表中刪除數(shù)據(jù)行的權(quán)限。
• DROP：授予用戶可以刪除數(shù)據(jù)表的權(quán)限。
• UPDATE：授予用戶可以使用 UPDATE 語句更新特定數(shù)據(jù)表的權(quán)限。
• ALTER：授予用戶可以使用 ALTER TABLE 語句修改數(shù)據(jù)表的權(quán)限。
• REFERENCES：授予用戶可以創(chuàng)建一個(gè)外鍵來參照特定數(shù)據(jù)表的權(quán)限。
• CREATE：授予用戶可以使用特定的名字創(chuàng)建一個(gè)數(shù)據(jù)表的權(quán)限。
• INDEX：授予用戶可以在表上定義索引的權(quán)限。
• ALL或ALL PRIVILEGES：所有的權(quán)限名

授予列(字段)權(quán)限類型

授予列(字段)權(quán)限時(shí)，priv_type的值只能指定為SELECT、INSERT和UPDATE，同時(shí)權(quán)限的后面需要加上列名列表(column-list)。

授予創(chuàng)建和刪除用戶的權(quán)限

授予列(字段)權(quán)限時(shí)，priv_type的值指定為CREATE USER權(quán)限，具備創(chuàng)建用戶、刪除用戶、重命名用戶和撤消所有特權(quán)，而且是全局的。

ON

有ON，是授予權(quán)限，無ON，是授予角色。如：

– 授予數(shù)據(jù)庫(kù)db1的所有權(quán)限給指定賬戶
GRANT ALL ON db1.* TO ‘user1’@‘localhost’;
– 授予角色給指定的賬戶
GRANT ‘role1’, ‘role2’ TO ‘user1’@‘localhost’, ‘user2’@‘localhost’;

對(duì)象類型(object_type)

在ON關(guān)鍵字后給出要授予權(quán)限的object_type，通常object_type可以是數(shù)據(jù)庫(kù)名、表名等。

權(quán)限級(jí)別(priv_level)

指定權(quán)限級(jí)別的值有以下幾類格式：

• *：表示當(dāng)前數(shù)據(jù)庫(kù)中的所有表。
• .：表示所有數(shù)據(jù)庫(kù)中的所有表。
• db_name.*：表示某個(gè)數(shù)據(jù)庫(kù)中的所有表，db_name指定數(shù)據(jù)庫(kù)名。
• db_name.tbl_name：表示某個(gè)數(shù)據(jù)庫(kù)中的某個(gè)表或視圖，db_name指定數(shù)據(jù)庫(kù)名，tbl_name指定表名或視圖名。
• tbl_name：表示某個(gè)表或視圖，tbl_name指定表名或視圖名。
• db_name.routine_name：表示某個(gè)數(shù)據(jù)庫(kù)中的某個(gè)存儲(chǔ)過程或函數(shù)，routine_name指定存儲(chǔ)過程名或函數(shù)名。

被授權(quán)的用戶(user)

"user_name"@"host_name"

Tips：'host_name’用于適應(yīng)從任意主機(jī)訪問數(shù)據(jù)庫(kù)而設(shè)置的，可以指定某個(gè)地址或地址段訪問。可以同時(shí)授權(quán)多個(gè)用戶。

user表中host列的默認(rèn)值

host

說明

%

匹配所有主機(jī)

localhost

localhost不會(huì)被解析成IP地址，直接通過UNIXsocket連接

127.0.0.1

會(huì)通過TCP/IP協(xié)議連接，并且只能在本機(jī)訪問

::1

::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

host_name格式有以下幾種：

• 使用%模糊匹配，符合匹配條件的主機(jī)可以訪問該數(shù)據(jù)庫(kù)實(shí)例，例如192.168.2.%或%.test.com；
• 使用localhost、127.0.0.1、::1及服務(wù)器名等，只能在本機(jī)訪問；
• 使用ip地址或地址段形式，僅允許該ip或ip地址段的主機(jī)訪問該數(shù)據(jù)庫(kù)實(shí)例，例如192.168.2.1或192.168.2.0/24或192.168.2.0/255.255.255.0；
• 省略即默認(rèn)為%。

身份驗(yàn)證方式(auth_option)

auth_option為可選字段，可以指定密碼以及認(rèn)證插件(mysql_native_password、sha256_password、caching_sha2_password)。

加密連接(tls_option)

tls_option為可選的，一般是用來加密連接。

用戶資源限制(resource_option)

resource_option為可選的，一般是用來指定最大連接數(shù)等。

參數(shù)

說明

MAX_QUERIES_PER_HOUR count

每小時(shí)最大查詢數(shù)

MAX_UPDATES_PER_HOUR count

每小時(shí)最大更新數(shù)

MAX_CONNECTIONS_PER_HOUR count

每小時(shí)連接次數(shù)

MAX_USER_CONNECTIONS count

用戶最大連接數(shù)

權(quán)限生效

若要權(quán)限生效，需要執(zhí)行以下語句：

FLUSH PRIVILEGES;

REVOKE語句

REVOKE語句主要用于撤銷權(quán)限。

語法格式

REVOKE語法和GRANT語句的語法格式相似，但具有相反的效果

REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] …
ON [object_type] priv_level
FROM user [, user] …

REVOKE ALL [PRIVILEGES], GRANT OPTION
FROM user [, user] …

REVOKE PROXY ON user
FROM user [, user] …

• 若要使用REVOKE語句，必須擁有MySQL數(shù)據(jù)庫(kù)的全局CREATE USER權(quán)限或UPDATE權(quán)限;
• 第一種語法格式用于回收指定用戶的某些特定的權(quán)限，第二種回收指定用戶的所有權(quán)限；

TCL（Transaction Control Language）

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

什么是事物？

一個(gè)或一組sql語句組成一個(gè)執(zhí)行單元，這個(gè)執(zhí)行單元要么全部執(zhí)行，要么全部不執(zhí)行

事務(wù)的ACID屬性

• 原子性：事務(wù)是一個(gè)不可分割的工作單位，事務(wù)中的操作要么都發(fā)生，要么都不發(fā)生

• 一致性：事務(wù)必須使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變換到另外一個(gè)一致性狀態(tài)

• 隔離性：一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)干擾，即一個(gè)事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對(duì)并發(fā)的其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個(gè)事務(wù)之間不能互相干擾

• 持久性：一個(gè)事務(wù)一旦被提交，它對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變就是永久性的，接下來的其他操作和數(shù)據(jù)庫(kù)故障不應(yīng)該對(duì)其有任何影響

分類

• 隱式事務(wù)：事務(wù)沒有明顯的開啟和結(jié)束的標(biāo)記(比如insert，update，delete語句)

• 顯式事務(wù)：事務(wù)具有明顯的開啟和結(jié)束的標(biāo)記(autocommit變量設(shè)置為0)

事務(wù)的使用步驟

開啟事務(wù)

默認(rèn)開啟事務(wù)

SET autocommit = 0 ;

提交事務(wù)

COMMIT;

回滾事務(wù)

ROLLBACK ;

查看當(dāng)前的事務(wù)隔離級(jí)別

select @@tx_isolation;

設(shè)置當(dāng)前連接事務(wù)的隔離級(jí)別

set session transaction isolation level read uncommitted;

設(shè)置數(shù)據(jù)庫(kù)系統(tǒng)的全局的隔離級(jí)別

set global transaction isolation level read committed ;

常用函數(shù)

MySQL提供了眾多功能強(qiáng)大、方便易用的函數(shù)，使用這些函數(shù)，可以極大地提高用戶對(duì)于數(shù)據(jù)庫(kù)的管理效率，從而更加靈活地滿足不同用戶的需求。本文將MySQL的函數(shù)分類并匯總，以便以后用到的時(shí)候可以隨時(shí)查看。

(這里使用 Navicat Premium 15 工具進(jìn)行演示)

因?yàn)閮?nèi)容太多了這里只演示一些常用的

一. 數(shù)學(xué)函數(shù)

對(duì)數(shù)值型的數(shù)據(jù)進(jìn)行指定的數(shù)學(xué)運(yùn)算，如abs()函數(shù)可以獲得給定數(shù)值的絕對(duì)值，round()函數(shù)可以對(duì)給定的數(shù)值進(jìn)行四舍五入。

1. ABS(number)

作用：返回 number 的絕對(duì)值

SELECT
ABS(s_score)
FROM
score;

ABS(-86) 返回：86

number 參數(shù)可以是任意有效的數(shù)值表達(dá)式。如果 number 包含 Null，則返回 Null；如果是未初始化變量，則返回 0。

2. PI()

例1：pi() 返回：3.141592653589793

例2：pi(2) 返回：6.283185307179586

作用：計(jì)算圓周率及其倍數(shù)

3. SQRT(x) 作用：返回非負(fù)數(shù)的x的二次方根 4. MOD(x,y) 作用：返回x被y除后的余數(shù) 5. CEIL(x)、CEILING(x) 作用：返回不小于x的最小整數(shù) 6. FLOOR(x) 作用：返回不大于x的最大整數(shù) 7. FLOOR(x) 作用：返回不大于x的最大整數(shù) 8. ROUND(x)、ROUND(x,y)

作用：前者返回最接近于x的整數(shù)，即對(duì)x進(jìn)行四舍五入；后者返回最接近x的數(shù)，其值保留到小數(shù)點(diǎn)后面y位，若y為負(fù)值，則將保留到x到小數(shù)點(diǎn)左邊y位

SELECT ROUND(345222.9)

參數(shù)說明： numberExp 需要進(jìn)行截取的數(shù)據(jù) nExp 整數(shù)，用于指定需要進(jìn)行截取的位置，>0：從小數(shù)點(diǎn)往右位移nExp個(gè)位數(shù)， <0：從小數(shù)點(diǎn)往左

nExp個(gè)位數(shù) =0：表示當(dāng)前小數(shù)點(diǎn)的位置

9. POW(x,y)和、POWER(x,y) 作用：返回x的y次乘方的值 10. EXP(x) 作用：返回e的x乘方后的值 11. LOG(x) 作用：返回x的自然對(duì)數(shù)，x相對(duì)于基數(shù)e的對(duì)數(shù) 12. LOG10(x) 作用：返回x的基數(shù)為10的對(duì)數(shù) 13. RADIANS(x) 作用：返回x由角度轉(zhuǎn)化為弧度的值 14. DEGREES(x) 作用：返回x由弧度轉(zhuǎn)化為角度的值 15. SIN(x)、ASIN(x) 作用：前者返回x的正弦，其中x為給定的弧度值；后者返回x的反正弦值，x為正弦 16. COS(x)、ACOS(x) 作用：前者返回x的余弦，其中x為給定的弧度值；后者返回x的反余弦值，x為余弦 17. TAN(x)、ATAN(x) 作用：前者返回x的正切，其中x為給定的弧度值；后者返回x的反正切值，x為正切 18. COT(x) 作用：返回給定弧度值x的余切

二. 字符串函數(shù)

1. CHAR_LENGTH(str)

作用：計(jì)算字符串字符個(gè)數(shù)

SELECT CHAR_LENGTH(‘這是一個(gè)十二個(gè)字的字符串’);

2. CONCAT(s1,s2，…)

作用：返回連接參數(shù)產(chǎn)生的字符串，一個(gè)或多個(gè)待拼接的內(nèi)容，任意一個(gè)為NULL則返回值為NULL

SELECT CONCAT(‘拼接’,‘測(cè)試’);

3. CONCAT_WS(x,s1,s2,…)

作用：返回多個(gè)字符串拼接之后的字符串，每個(gè)字符串之間有一個(gè)x

SELECT CONCAT_WS(‘-’,‘測(cè)試’,‘拼接’,‘WS’)

4. INSERT(s1,x,len,s2)

作用：返回字符串s1，其子字符串起始于位置x，被字符串s2取代len個(gè)字符

SELECT INSERT(‘測(cè)試字符串替換’,2,1,‘牛’);

5. LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)

作用：前兩者將str中的字母全部轉(zhuǎn)換成小寫，后兩者將字符串中的字母全部轉(zhuǎn)換成大寫

SELECT LOWER(‘JHGYTUGHJGG’),LCASE(‘HKJHKJHKJHKJ’);

SELECT UPPER("aaaaaa"),UCASE("vvvvv");

6. LEFT(s,n)、RIGHT(s,n)

作用：前者返回字符串s從最左邊開始的n個(gè)字符，后者返回字符串s從最右邊開始的n個(gè)字符

SELECT LEFT(‘左邊開始’,2),RIGHT(‘右邊開始’,2);

7. LPAD(s1,len,s2)、RPAD(s1,len,s2)

作用：前者返回s1，其左邊由字符串s2填補(bǔ)到len字符長(zhǎng)度，假如s1的長(zhǎng)度大于len，則返回值被縮短至len字符；前者返回s1，其右邊由字符串s2填補(bǔ)到len字符長(zhǎng)度，假如s1的長(zhǎng)度大于len，則返回值被縮短至len字符

SELECT LEFT(‘左邊開始’,2),RIGHT(‘右邊開始’,2);

8. LTRIM(s)、RTRIM(s)

作用：前者返回字符串s，其左邊所有空格被刪除；后者返回字符串s，其右邊所有空格被刪除

SELECT LTRIM(’ 左邊開始’),RTRIM(’ 右邊開始 ');

9. TRIM(s)

作用：返回字符串s刪除了兩邊空格之后的字符串

SELECT TRIM(’ 是是 ');

10. TRIM(s1 FROM s) 作用：刪除字符串s兩端所有子字符串s1，未指定s1的情況下則默認(rèn)刪除空格 11. REPEAT(s,n)

作用：返回一個(gè)由重復(fù)字符串s組成的字符串，字符串s的數(shù)目等于n

SELECT REPEAT(‘測(cè)試’,5);

12. SPACE(n)

作用：返回一個(gè)由n個(gè)空格組成的字符串

SELECT SPACE(20);

13. REPLACE(s,s1,s2) 作用：返回一個(gè)字符串，用字符串s2替代字符串s中所有的字符串s1 14. STRCMP(s1,s2)

作用：若s1和s2中所有的字符串都相同，則返回0；根據(jù)當(dāng)前分類次序，第一個(gè)參數(shù)小于第二個(gè)則返回-1，其他情況返回1

SELECT STRCMP(‘我我我’,‘我我我’);

SELECT STRCMP("我我我","是是是");

15. SUBSTRING(s,n,len)、MID(s,n,len)

作用：兩個(gè)函數(shù)作用相同，從字符串s中返回一個(gè)第n個(gè)字符開始、長(zhǎng)度為len的字符串

SELECT SUBSTRING(‘測(cè)試測(cè)試’,2,2);

SELECT MID("測(cè)試測(cè)試",2,2);

16. LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)

作用：三個(gè)函數(shù)作用相同，返回子字符串str1在字符串str中的開始位置（從第幾個(gè)字符開始）

SELECT LOCATE(‘字’,‘獲取字符串的位置’);

17. REVERSE(s)

作用：將字符串s反轉(zhuǎn)

SELECT REVERSE(‘字符串反轉(zhuǎn)’);

18. ELT(N,str1,str2,str3,str4,…)

作用：返回第N個(gè)字符串

SELECT ELT(2,‘字符串反轉(zhuǎn)’,‘sssss’);

三. 日期和時(shí)間函數(shù)

當(dāng)前時(shí)間

1. CURDATE()、CURRENT_DATE() 作用：將當(dāng)前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具體格式根據(jù)函數(shù)用在字符串或是數(shù)字語境中而定 2. CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()

作用：這四個(gè)函數(shù)作用相同，返回當(dāng)前日期和時(shí)間值，格式為"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具體格式根據(jù)函數(shù)用在字符串或數(shù)字語境中而定

SELECT CURRENT_TIMESTAMP()

SELECT LOCALTIME()

SELECT NOW()

SELECT SYSDATE()

3. UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)

作用：前者返回一個(gè)格林尼治標(biāo)準(zhǔn)時(shí)間1970-01-01 00:00:00到現(xiàn)在的秒數(shù)，后者返回一個(gè)格林尼治標(biāo)準(zhǔn)時(shí)間1970-01-01 00:00:00到指定時(shí)間的秒數(shù)

SELECT UNIX_TIMESTAMP()

4. FROM_UNIXTIME(date)

作用：和UNIX_TIMESTAMP互為反函數(shù)，把UNIX時(shí)間戳轉(zhuǎn)換為普通格式的時(shí)間 5. UTC_DATE()和UTC_TIME()

前者返回當(dāng)前UTC（世界標(biāo)準(zhǔn)時(shí)間）日期值，其格式為"YYYY-MM-DD"或"YYYYMMDD"，后者返回當(dāng)前UTC時(shí)間值，其格式為"YYYY-MM-DD"或"YYYYMMDD"。具體使用哪種取決于函數(shù)用在字符串還是數(shù)字語境中

SELECT UTC_DATE()

SELECT UTC_TIME()

6. MONTH(date)和MONTHNAME(date)

作用：前者返回指定日期中的月份，后者返回指定日期中的月份的名稱

SELECT MONTH(NOW())

SELECT MONTHNAME(NOW())

7. DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)

作用：DAYNAME(d)返回d對(duì)應(yīng)的工作日的英文名稱，如Sunday、Monday等；DAYOFWEEK(d)返回的對(duì)應(yīng)一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d對(duì)應(yīng)的工作日索引，0表示周一，1表示周二 8. WEEK(d)

計(jì)算日期d是一年中的第幾周

SELECT WEEK(NOW())

9. DAYOFYEAR(d)、DAYOFMONTH(d)

作用：前者返回d是一年中的第幾天，后者返回d是一月中的第幾天

SELECT DAYOFYEAR(NOW())

SELECT DAYOFMONTH(NOW())

10. YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time)

作用： YEAR(date)返回指定日期對(duì)應(yīng)的年份，范圍是1970~2069；QUARTER(date)返回date對(duì)應(yīng)一年中的季度，范圍是1~4；MINUTE(time)返回time對(duì)應(yīng)的分鐘數(shù)，范圍是0~59；SECOND(time)返回制定時(shí)間的秒值

SELECT YEAR(NOW())

SELECT QUARTER(NOW())

SELECT MINUTE(NOW())

SELECT SECOND(NOW())

11. EXTRACE(type FROM date)

作用：從日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND 12. TIME_TO_SEC(time)

作用：返回以轉(zhuǎn)換為秒的time參數(shù)，轉(zhuǎn)換公式為"3600_小時(shí) + 60_分鐘 + 秒"

SELECT TIME_TO_SEC(NOW())

13. SEC_TO_TIME()

作用：和TIME_TO_SEC(time)互為反函數(shù)，將秒值轉(zhuǎn)換為時(shí)間格式

SELECT SEC_TO_TIME(530)

14. DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)

作用：返回將起始時(shí)間加上expr type之后的時(shí)間，比如DATE_ADD(‘2010-12-31 23:59:59’, INTERVAL 1 SECOND)表示的就是把第一個(gè)時(shí)間加1秒

15. DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)

作用：返回將起始時(shí)間減去expr type之后的時(shí)間

16. ADDTIME(date,expr)、SUBTIME(date,expr)

作用：前者進(jìn)行date的時(shí)間加操作，后者進(jìn)行date的時(shí)間減操作

四. 條件判斷函數(shù)

1. IF(expr,v1,v2)

作用：如果expr是TRUE則返回v1，否則返回v2

2. IFNULL(v1,v2)

作用：如果v1不為NULL，則返回v1，否則返回v2

3. CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END

作用：如果expr等于某個(gè)vn，則返回對(duì)應(yīng)位置THEN后面的結(jié)果，如果與所有值都不想等，則返回ELSE后面的rn

五. 系統(tǒng)信息函數(shù)

1. VERSION()

作用：查看MySQL版本號(hào)

SELECT VERSION()

2. CONNECTION_ID()

作用：查看當(dāng)前用戶的連接數(shù)

SELECT CONNECTION_ID()

3. USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()

作用：查看當(dāng)前被MySQL服務(wù)器驗(yàn)證的用戶名和主機(jī)的組合，一般這幾個(gè)函數(shù)的返回值是相同的

SELECT USER()

SELECT CURRENT_USER()

SELECT SYSTEM_USER()

SELECT SESSION_USER()

4. CHARSET(str)

作用：查看字符串str使用的字符集

SELECT CHARSET(555)

5. COLLATION()

作用：查看字符串排列方式

SELECT COLLATION(‘sssfddsfds")

六. 加密函數(shù)

1. PASSWORD(str)

作用：從原明文密碼str計(jì)算并返回加密后的字符串密碼，注意這個(gè)函數(shù)的加密是單向的（不可逆），因此不應(yīng)將它應(yīng)用在個(gè)人的應(yīng)用程序中而應(yīng)該只在MySQL服務(wù)器的鑒定系統(tǒng)中使用

SELECT PASSWORD(‘mima’)

2. MD5(str)

作用：為字符串算出一個(gè)MD5 128比特校驗(yàn)和，改值以32位十六進(jìn)制數(shù)字的二進(jìn)制字符串形式返回

SELECT MD5(‘mima’)

3. ENCODE(str, pswd_str)

作用：使用pswd_str作為密碼，加密str

SELECT ENCODE(‘fdfdz’,‘mima’)

4. DECODE(crypt_str,pswd_str)

作用：使用pswd_str作為密碼，解密加密字符串crypt_str，crypt_str是由ENCODE函數(shù)返回的字符串

SELECT DECODE(‘fdfdz’,‘mima’)

七. 其他函數(shù)

1. FORMAT(x,n)

作用：將數(shù)字x格式化，并以四舍五入的方式保留小數(shù)點(diǎn)后n位，結(jié)果以字符串形式返回

SELECT FORMAT(446.454,2)

2. CONV(N,from_base,to_base)

作用：不同進(jìn)制數(shù)之間的轉(zhuǎn)換，返回值為數(shù)值N的字符串表示，由from_base進(jìn)制轉(zhuǎn)換為to_base進(jìn)制

3. INET_ATON(expr)

作用：給出一個(gè)作為字符串的網(wǎng)絡(luò)地址的點(diǎn)地址表示，返回一個(gè)代表該地址數(shù)值的整數(shù)，地址可以使4或8比特

4. INET_NTOA(expr)

作用：給定一個(gè)數(shù)字網(wǎng)絡(luò)地址（4或8比特），返回作為字符串的該地址的點(diǎn)地址表示

5. BENCHMARK(count,expr)

作用：重復(fù)執(zhí)行count次表達(dá)式expr，它可以用于計(jì)算MySQL處理表達(dá)式的速度，結(jié)果值通常是0（0只是表示很快，并不是沒有速度）。另一個(gè)作用是用它在MySQL客戶端內(nèi)部報(bào)告語句執(zhí)行的時(shí)間

6. CONVERT(str USING charset)

作用：使用字符集charset表示字符串str

更多用法還請(qǐng)參考：http://www.geezn.com/documents/gez/help/117555-1355219868404378.html

SQL實(shí)戰(zhàn)練習(xí)

題目來自互聯(lián)網(wǎng)，建議每道題都在本地敲一遍鞏固記憶 ！ 創(chuàng)建數(shù)據(jù)庫(kù)

創(chuàng)建表（并初始化數(shù)據(jù)）

-- 學(xué)生表CREATE TABLE `student`(`s_id` VARCHAR(20),`s_name` VARCHAR(20) NOT NULL DEFAULT "",`s_birth` VARCHAR(20) NOT NULL DEFAULT "",`s_sex` VARCHAR(10) NOT NULL DEFAULT "",PRIMARY KEY(`s_id`));-- 課程表CREATE TABLE `course`(`c_id` VARCHAR(20),`c_name` VARCHAR(20) NOT NULL DEFAULT "",`t_id` VARCHAR(20) NOT NULL,PRIMARY KEY(`c_id`));-- 教師表CREATE TABLE `teacher`(`t_id` VARCHAR(20),`t_name` VARCHAR(20) NOT NULL DEFAULT "",PRIMARY KEY(`t_id`));-- 成績(jī)表CREATE TABLE `score`(`s_id` VARCHAR(20),`c_id` VARCHAR(20),`s_score` INT(3),PRIMARY KEY(`s_id`,`c_id`));-- 插入學(xué)生表測(cè)試數(shù)據(jù)insert into student values("01" , "趙信" , "1990-01-01" , "男");insert into student values("02" , "德萊厄斯" , "1990-12-21" , "男");insert into student values("03" , "艾希" , "1990-05-20" , "男");insert into student values("04" , "德萊文" , "1990-08-06" , "男");insert into student values("05" , "俄洛依" , "1991-12-01" , "女");insert into student values("06" , "光輝女郎" , "1992-03-01" , "女");insert into student values("07" , "崔絲塔娜" , "1989-07-01" , "女");insert into student values("08" , "安妮" , "1990-01-20" , "女");-- 課程表測(cè)試數(shù)據(jù)insert into course values("01" , "語文" , "02");insert into course values("02" , "數(shù)學(xué)" , "01");insert into course values("03" , "英語" , "03");-- 教師表測(cè)試數(shù)據(jù)insert into teacher values("01" , "死亡歌頌者");insert into teacher values("02" , "流浪法師");insert into teacher values("03" , "邪惡小法師");-- 成績(jī)表測(cè)試數(shù)據(jù)insert into score values("01" , "01" , 80);insert into score values("01" , "02" , 90);insert into score values("01" , "03" , 99);insert into score values("02" , "01" , 70);insert into score values("02" , "02" , 60);insert into score values("02" , "03" , 80);insert into score values("03" , "01" , 80);insert into score values("03" , "02" , 80);insert into score values("03" , "03" , 80);insert into score values("04" , "01" , 50);insert into score values("04" , "02" , 30);insert into score values("04" , "03" , 20);insert into score values("05" , "01" , 76);insert into score values("05" , "02" , 87);insert into score values("06" , "01" , 31);insert into score values("06" , "03" , 34);insert into score values("07" , "02" , 89);insert into score values("07" , "03" , 98);

表結(jié)構(gòu) 這里建的表主要用于sql語句的練習(xí)，所以并沒有遵守一些規(guī)范。下面讓我們來看看相關(guān)的表結(jié)構(gòu)吧

學(xué)生表（student）

s_id = 學(xué)生編號(hào)，s_name = 學(xué)生姓名，s_birth = 出生年月，s_sex = 學(xué)生性別

課程表（course）

c_id =