MySQL數(shù)據(jù)庫語句完全講解

更新時間：2026年01月15日 10:10:24 作者：?_APPROPRIATE"ぁ.

MySQL作為最流行的開源關(guān)系型數(shù)據(jù)庫,其查詢語句涵蓋了從基礎數(shù)據(jù)檢索到復雜多表關(guān)聯(lián)的全場景需求,這篇文章主要介紹了MySQL數(shù)據(jù)庫語句的相關(guān)資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下

基礎知識

數(shù)據(jù)庫（database） 是一個以某種有組織的方式存儲的數(shù)據(jù)集合。理解數(shù)據(jù)庫的一種最簡單的辦法是將其想象為一個文件柜。此文件柜是一個存放數(shù)據(jù)的物理位置，不管數(shù)據(jù)是什么以及如何組織的
表（table） 是一種結(jié)構(gòu)化的文件，可用來存儲某種特定類型的數(shù)據(jù)（數(shù)據(jù)庫中的每個表名都是唯一的）
模式（schema） 是指數(shù)據(jù)庫的結(jié)構(gòu)和組織方式，描述了數(shù)據(jù)庫中對象的布局、特性及它們之間的關(guān)系
列（column） 是表中的一個字段。所有表都是由一個或多個列組成的。
行（row） 是表中的一個記錄。
主鍵（primary key） 是一列（或一組列），其值能夠唯一區(qū)分表中每個行。
- 一列：主鍵列不允許NULL值和重復值
- 一組列：所有列值的組合必須是唯一的（但單個列的值可以不唯一）

打開數(shù)據(jù)庫

打開數(shù)據(jù)庫：

mysql -u 用戶名 -p

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

USE 數(shù)據(jù)庫名;

必須先使用 USE 打開數(shù)據(jù)庫，才能讀取其中的數(shù)據(jù)。

基礎命令

命令輸入在 mysql> 之后；

命令用 ; 或 \g 結(jié)束，換句話說，僅按Enter不執(zhí)行命令；
輸入 help 或 \h 獲得幫助，也可以輸入更多的文本獲得特定命令的幫助（如，輸入 help select; 獲得使用SELECT語句的幫助）；
輸入 quit 或 exit 退出命令行實用程序。

顯示信息

顯示數(shù)據(jù)庫信息：

SHOW DATABASES;

獲得一個數(shù)據(jù)庫內(nèi)的表的列表:

SHOW TABLES;

顯示表列：

SHOW COLUMNS FROM 表名; 
DESCRIBE 表名;  # 另一種快捷方式

顯示廣泛的服務器狀態(tài)信息：

SHOW STATUS;

顯示創(chuàng)建特定數(shù)據(jù)庫：

SHOW CREATE DATABASE 數(shù)據(jù)庫名;

顯示創(chuàng)建特定表：

SHOW CREATE TABLE 表名;

顯示表的列結(jié)構(gòu)：

desc 表名;

顯示各列數(shù)據(jù)：

select 列名1, 列名2, ... from 表名;

select 命令還能用來顯示與數(shù)據(jù)庫無關(guān)的值；例如：
select '測試123123';  # 輸出結(jié)果為：`測試123123`
select 2+3*4;  # 輸出結(jié)果為：`14`

顯示表格中全部列的數(shù)據(jù)：

select * from 表名;

顯示授予用戶的安全權(quán)限：

SHOW GRANTS;

顯示服務器錯誤或者警告信息：

SHOW ERROES;
SHOW WARNINGS;

顯示允許的 SHOW 語句：

HELP SHOW;

查看和顯示數(shù)據(jù)庫的編碼方式：

show create database 數(shù)據(jù)庫名;

顯示當前使用的數(shù)據(jù)庫：

select database();

顯示索引

show insex from 表名;

增刪改

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

create database 數(shù)據(jù)庫名;

# 舉例：創(chuàng)建并使用數(shù)據(jù)庫
create database phone;  # 創(chuàng)建數(shù)據(jù)庫 phone
show databases;  # 查看是否成功創(chuàng)建 phone
use phone;  # 使用數(shù)據(jù)庫

/* 在使用 use 選擇數(shù)據(jù)庫的狀態(tài)下，也能夠操作其他數(shù)據(jù)庫中的表。（將數(shù)據(jù)庫名和表名用 . 連接起來）*/
select * from db2.table1;

創(chuàng)建數(shù)據(jù)庫并設置數(shù)據(jù)庫的字符編碼：

create database 數(shù)據(jù)庫名 character set utf8;
# 另一種縮寫方式
create database 數(shù)據(jù)庫名 charset utf8;

創(chuàng)建表：

create table 表名(列名1 字段類型, 列名2 字段類型, 列名3 字段類型, …);

為創(chuàng)建索引

create index 索引名 on 表名 (列名1, 列名2, ...);

復制表的列結(jié)構(gòu)和記錄：

create table 新表名 as select * from 舊表名;

僅復制表的列結(jié)構(gòu)：

create table 新表名 like 原表名;

僅復制表的記錄：
insert into 新表名 select * from 原表名;
選擇某一列進行復制：(復制列的數(shù)據(jù)結(jié)構(gòu)必須一致，否則可能會復制失敗)
insert into 新表名(新列名) select 原列名 from 原表名;

設置主鍵：

# 設置單個主鍵列
create table 表名 (主鍵列名 數(shù)據(jù)類型 primary key, 普通列名 數(shù)據(jù)類型);

# 設置復合主鍵
CREATE TABLE 表名 (主鍵列名1 數(shù)據(jù)類型, 主鍵列名2 數(shù)據(jù)類型, 普通列名 數(shù)據(jù)類型, PRIMARY KEY (主鍵列名1, 主鍵列名2));

設置唯一鍵：

create table 表名 (唯一鍵列名 數(shù)據(jù)類型 unique, 普通列名 數(shù)據(jù)類型);

主鍵和唯一鍵區(qū)別：

特性	主鍵約束 (PRIMARY KEY)	唯一約束 (UNIQUE)
列的唯一性	確保列值唯一，且不能為 `NULL`。	確保列值唯一，但可以包含 `NULL`（根據(jù)數(shù)據(jù)庫的具體實現(xiàn)）。
NULL 值	不允許有 `NULL` 值。	可以有 `NULL` 值，但通常每個 `NULL` 只能出現(xiàn)一次（具體依賴數(shù)據(jù)庫實現(xiàn)）。
索引	自動創(chuàng)建唯一索引，且該索引被視為主索引。	自動創(chuàng)建唯一索引，但它不是主索引。
表中的數(shù)量	每個表只能有一個主鍵。	一個表可以有多個唯一約束。
用途	用于標識每行唯一數(shù)據(jù)。	用于確保某列的值是唯一的，適用于不需要作為行標識符的列。

添加能自動連續(xù)編號的列，列要求：

數(shù)據(jù)類型為 int 等整數(shù)類型
加上關(guān)鍵字 auto_increment (每個表中只能有一個 AUTO_INCREMENT 列)
必須是主鍵或 唯一鍵。
create table 表名 (能自動編號的列名 整數(shù)數(shù)據(jù)類型 auto_increment primary key, 其他列名 數(shù)據(jù)類型);
create table 表名 (能自動編號的列名 整數(shù)數(shù)據(jù)類型 auto_increment unique, 其他列名 數(shù)據(jù)類型);

插入數(shù)據(jù)驗證是否連續(xù)編號：
insert into 表名 (其他列名) values(插入的值);
# 舉例
insert into phone_test (name) values('mike');
設置連續(xù)編號的初始值：
insert into 表名  values(連續(xù)編號值, 其他列值);
# 舉例
insert into phone_test values(100, 'mike');
如果把表中數(shù)據(jù)都刪除，然后重新輸入，編號不會從頭開始，而是從最大值+1 開始分配
create table test1 (id1 int auto_increment primary key, name char(10));
insert into test1 (name) values('mmike');  # 1 mmike
insert into test1 values(100, 'mike');  # 1 mmike  和 100 mike
delete from test1;  # 刪除上面兩條數(shù)據(jù)
insert into test1 (name) values('moke');  # 101 moke
初始化 auto_increment 的值 (如果初始化的值小于現(xiàn)有表格中最大值，那么初始化會不起作用；如果想要重新從 1 開始，必須清空表中的數(shù)據(jù)。)
alter table 表名 auto_increment=200;

設置列的默認值

create table 表名 (列名 數(shù)據(jù)類型 default 默認值 ...);

# 修改已有列并為其添加默認值
alter table 表名 modify column 列名 數(shù)據(jù)類型 default 默認值;
# 添加默認值到新列
alter table 表名 add column 列名 數(shù)據(jù)類型 default 默認值;
# 添加默認值，不更改數(shù)據(jù)類型
alter table 表名 alter column 列名 set default 默認值;

更新數(shù)據(jù)：

UPDATE customers   # 更新 customers 表
SET Cust_name = 'The Fudds', cust_email='elmer@fudd.com'  # 將 Cust_name 列的值更新為 The Fudds,cust_email 列的值更新為 elmer@fudd.com
WHERE cust_id = 10005;  # 僅更新 Cust_id 等于 '10005' 的行

UPDATE IGNORE 表名
SET 列1 = 值1, 列2 = 值2, ...
WHERE 條件;  # 在執(zhí)行 UPDATE 操作時，如果出現(xiàn)某些錯誤（如重復鍵錯誤），則忽略這些錯誤并繼續(xù)更新其余記錄

重命名表：

RENAME TABLE 舊表名1 TO 新表名1,舊表名2 TO 新表名2... ;

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

drop database 數(shù)據(jù)庫名;

刪除表：

drop table 表名;
# 如果目標表不存在的情況下執(zhí)行drop命令會報錯，可以加個 if exists
drop table if exists 表名;

刪除列：

alter table 表名 drop 列名;

刪除表中所有記錄：

delete from 表名;
# 刪除符合條件的那一行數(shù)據(jù)
delete from 表名 where 列名=1006;

# turncate table（速度比delect快）
# 如果表被其他表通過外鍵約束引用，通常無法執(zhí)行 TRUNCATE 操作。你需要先刪除外鍵約束，或使用 DELETE 操作。
# 通常，TRUNCATE 不會觸發(fā)與表相關(guān)的觸發(fā)器（Triggers），而 DELETE 可能會觸發(fā) BEFORE DELETE 或 AFTER DELETE 觸發(fā)器。
# TRUNCATE 不支持條件WHERE,它會刪除表中所有的記錄，沒有條件限制。
TRUNCATE TABLE 表名;  # 刪除整個表的數(shù)據(jù)，表結(jié)構(gòu)不變

修改數(shù)據(jù)庫編碼：

alter database 數(shù)據(jù)庫名 character set utf8;

修改字段數(shù)據(jù)類型：

alter table 表名 modify 列名 數(shù)據(jù)類型;

修改字段的數(shù)據(jù)類型并且改名：

alter table 表名 change 原列名 新列名 數(shù)據(jù)類型;

修改列的順序：

# 把某一列放在最前面
alter table 表名 modify 列名 數(shù)據(jù)類型 first;

向表中插入一列數(shù)據(jù)：

alter table 表名 add 列名 數(shù)據(jù)類型;
# 把列添加到最前面
alter table 表名 add 列名 數(shù)據(jù)類型 first;
# 把列插到某一列后面
alter table 表名 add 列名 數(shù)據(jù)類型 after 某一列列名;

刪除默認值

alter table 表名 modify column 列名 數(shù)據(jù)類型 default null;

刪除索引

drop index 索引名 on 表名;

create index my_ind on test1 (name);  # 在 表test1 的 列name 上創(chuàng)建名為 my_ind 的索引
show index from test1;  # 顯示創(chuàng)建的索引
show index from test1 \G  # 縱向顯示列值方便查看
drop index my_ind on test1;  # 刪除索引

定義外鍵

ALTER TABLE 表名  # 指定要修改的表
ADD CONSTRAINT 外鍵名稱  # 定義外鍵名稱
FOREIGN KEY (外鍵列名)  # 當前表中哪個列將作為外鍵
REFERENCES 參照表名(主鍵列名);  # 引用目標表及其主鍵

# 假設我們有兩個表：orders 和 customers。我們想要在 orders 表中添加一個外鍵，以引用 customers 表中的 customer_id 列。
CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE orders (order_id INT PRIMARY KEY, order_date DATE, customer_id INT);
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

復雜的表結(jié)構(gòu)更改一般需要手動刪除過程，：

用新的列布局創(chuàng)建一個新表；
使用INSERT SELECT語句從舊表復制數(shù)據(jù)到新表。如果有必要，可使用轉(zhuǎn)換函數(shù)和計算字段；
檢驗包含所需數(shù)據(jù)的新表；
重命名舊表或者刪除舊表；
用舊表原來的名字重命名新表；
根據(jù)需要，重新創(chuàng)建觸發(fā)器、存儲過程、索引和外鍵。

向表中插入一行數(shù)據(jù)：

insert into 表名 values(數(shù)據(jù)1， 數(shù)據(jù)2， ...);  # 句高度依賴于表中列的定義次序

指定列名插入數(shù)據(jù)：

insert into 表名 (列名1, 列名2, ...) values(數(shù)據(jù)1， 數(shù)據(jù)2， ...);  
# 也可以一次性插入多條數(shù)據(jù)： 
insert into 表名 (列名1, 列名2, ...) values (數(shù)據(jù)1， 數(shù)據(jù)2， ...),(數(shù)據(jù)1， 數(shù)據(jù)2， ...),(數(shù)據(jù)1， 數(shù)據(jù)2， ...)...;

# 舉例
insert into Phone (pid, name, color) values(456, 'marry', 'blue');
insert into Phone (pid, name, color) values(456, 'aaa', 'rrrr'),(789, 'bbb', 'ooo'),(012, 'ccc', 'yyy');

插入列往往比較耗時，可以使用LOW_PRIORITY降低insert語句的優(yōu)先級(尤其是在數(shù)據(jù)庫有大量的并發(fā)寫操作時)

INSERT LOW_PRIORITY INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);

插入檢索出的數(shù)據(jù)

INSERT INTO 新表名(列1, 列2, ...) SELECT 列1, 列2, ... FROM 原表名 WHERE 條件;

檢索

檢索不同的列：

# 從表中檢索一列
select 列名 from 表名;
# 從表中檢索多列
select 列名, 列名, 列名 from 表名;
# 檢索所有列
select * from 表名;

檢索不同的行：

# 返回每列的唯一值 (DISTINCT 會作用于查詢中的所有列的組合，而不是單獨某一列。)
select distinct 列名, 列名 from 表名;
# 如果只去除某一列的重復，應該只選擇該列，而不是整個組合。
select distinct 列名 from 表名;

限制結(jié)果：

# 顯示前m行
select * from 表名 limit m;
# 如果要得出m行后的n行，可指定要檢索的開始行和行數(shù)
select * from 表名 limit m,n;  # m：跳過前m行，即從第m+1行開始；  n：從第m+1行開始，返回后n行數(shù)據(jù)

使用完全限定的表名：

select 表名.列名 from 數(shù)據(jù)庫名.表名;

按順序檢索：

# 按順序檢索某一列
select 列名1 from 表名 order by 列名2;  # 按列名2的順序給列名1排序（order by后面的排序列和select的選擇列可以不一樣）
# 按多個列排序
select 列名1, 列名2, 列名3...  from 表名 order by 列名1, 列名2...;  # 先按 order by 中列1的順序排序，如果列1有相同值，相同部分按列2排序
# 降序排序
select 列名1, 列名2, 列名3... from 表名 order by 列名1 desc; 
# 也可以指定某一列為降序
select 列名1, 列名2, 列名3... from 表名 order by 列名1 desc, 列名2... ;  # 列1降序，列2升序，如果每列都想降序；必須對每列都指定desc關(guān)鍵字

找到一列中最高或最低值：

# 結(jié)合order by + limit
select 列名 from 表名 order by 列名 desc limit 1;  # 找到最高值

按指定搜索條件過濾檢索：

select 列名1, 列名2 from 表名 where 列名1 = 'a';  # 找到列名1中值等于a的 列1列2數(shù)據(jù)
select 列名1, 列名2 from 表名 where 列名1 between A and B;  # 找到列名1中值在`A<=值<=B`范圍內(nèi)的 列1列2數(shù)據(jù)
# where 子句可以組合使用
select 列名1, 列名2, 列名3 from 表名 where 列名1=100 and 列名2 <=10;  # 找到 列名1中值=100 并且 列2數(shù)據(jù)<=10 的 列1列2列3數(shù)據(jù)
select 列名1, 列名2, 列名3 from 表名 where 列名1=100 or 列名2 =200;  # 找到 列名1中值=100 或者 列2數(shù)據(jù)=200 的 列1列2列3數(shù)據(jù)

# where子句的計算次序（默認 AND 的優(yōu)先級高于 OR）
select 列名1, 列名2, 列名3 from 表名 where 列名1=100 or 列名2 =200 and 列名3>=10;  # 等價于WHERE (列名1 = 100) OR (列名2 = 200 AND 列名3 >= 10);找到 列名1中值=100 或者 （列2數(shù)據(jù)=200并且列名3>=10） 的 列1列2列3數(shù)據(jù)
select 列名1, 列名2, 列名3 from 表名 where (列名1=100 or 列名2 =200) and 列名3>=10;  # 找到 （列名1中值=100或者列2數(shù)據(jù)=200）并且 列名3>=10 的 列1列2列3數(shù)據(jù)

# where 子句的 in 操作符：in 操作符等價于多個 or 條件組合。它的作用是檢查 列名1 的值是否匹配括號中的任意一個值
select 列名1, 列名2, 列名3 from 表名 where 列名1 in (值1， 值2， 值3) order by 列名2;

# where 子句的 not 操作符：not 操作符否定它之后所跟的任何條件
select 列名1, 列名2, 列名3 from 表名 where 列名1 not in (值1， 值2， 值3) order by 列名2;

在同時使用order by和where子句時，應該讓order by位于where之后，否則將會產(chǎn)生錯誤

where 子句操作符：

操作符	說明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between	在指定的兩個值之間（包括邊界值）

空值檢索：

select 列名 from 表名 where 列名 is null;

用通配符過濾檢索：

# %通配符：%表示任何字符出現(xiàn)任意次數(shù)。（%會區(qū)分大小寫；%可以匹配0個字符）
select 列名 from 表名 where 列名 like 'aaa' order by 列名;  # 檢索字符串 完全等于 'aaa' 的字符串
select 列名1 from 表名 where 列名1 like 'aaa%';  # 檢索任意以aaa開頭的字符串（字符串：aaa也會被檢索）
select 列名1 from 表名 where 列名1 like '%aaa%';  # 檢索任何包含aaa的字符串
select 列名1 from 表名 where 列名1 like 'a%b';  # 檢索任何以a開頭b結(jié)尾的字符串

# %通配符不能匹配null
select 列名1 from 表名 where 列名1 like '%';  # 這樣也檢索不到 null 值

# _通配符：和%通配符用途一樣，但只匹配單個字符
select 列名1 from 表名 where 列名1 like 'aaa_';  # 檢索任意以aaa開頭且后面只跟一個字符的字符串 （字符串：aaa不會被檢索；aaall也不會被檢索，只能檢索到aaal）

用正則表達式進行搜索

# 基本字符匹配(正則表達式不會區(qū)分大小寫，區(qū)分大小寫的話需要在 regexp 后面添加 binary 關(guān)鍵字，)
select 列名 from 表名 where 列名 regexp 'aaa' order by 列名;  # 檢索字符串所有包含aaa的字符串
select 列名 from 表名 where 列名 regexp '.aaa' order by 列名;  # 檢索字符串所有包含aaa結(jié)且前面跟有一個字符 的 字符串（.表示匹配任意一個字符，例如：22aaa,2aaa,2aaab）

# or 匹配 （|）
select 列名 from 表名 where 列名 regexp 'aaa|bbb|ccc' order by 列名;  # 匹配包含 aaa、bbb 或 ccc 任意一個的字符串

# 匹配幾個字符之一 （[]）
select 列名 from 表名 where 列名 regexp '[123]aaa' order by 列名;  # 它要求該字符串必須以 1、2 或 3 之一開始，并緊接著是 aaa。換句話說，它會匹配 1aaa、2aaa 或 3aaa 開頭的任何字符串。

# 匹配范圍
select 列名 from 表名 where 列名 regexp '[1-5]aaa' order by 列名;  # 匹配 列名 中以數(shù)字 1 到 5 之間的任何一個數(shù)字開頭，并緊接著是 aaa 的字符串

# 匹配特殊字符（例如：.[]|_等）:用\\為前導
select 列名 from 表名 where 列名 regexp '\\.' order by 列名;  # 匹配包含 .（點號）的字符串

空白元字符

元字符	說明
`\\f`	換頁
`\\n`	換行
`\\r`	回車
`\\t`	制表
`\\v`	縱向制表

字符類：放在 方括號內(nèi)部 ，例如 [[:alnim:]]

類	說明
[:alnum:]	任意字母和數(shù)字（同[a-z A-Z 0-9]）
[:alpha:]	任意字符（同[a-z A-Z]）
[:blank:]	空格和制表（同[`\\t`]）
[:cntrl:]	ASCII控制字符（ASCII 0到31和127）
[:digit:]	任意數(shù)字（同[0-9]）
[:graph:]	與[:print:]相同，但不包括空格
[:lower:]	任意小寫字母（同[a-z]）
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在內(nèi)的任意空白字符（同[`\\f` `\\n` `\\r` `\\t` `\\v`]）
[:upper:]	任意大寫字母（同[A-Z]）
[:xdigit:]	任意十六進制數(shù)字（同[a-fA-F0-9]）

重復元字符

元字符	說明
*	0個或多個匹配
+	1個或多個匹配（等于{1,}）
?	0個或1個匹配（等于{0,1}）
{n}	指定數(shù)目的匹配
{n,}	不少于指定數(shù)目的匹配
{n,m}	匹配數(shù)目的范圍（m不超過255）

# 舉例
select 列名 from 表名 where 列名 regexp '[[:digit:]]{4}'  # 匹配任何包含 4個連續(xù)數(shù)字 的字符串
select 列名 from 表名 where 列名 regexp '[0-9][0-9][0-9][0-9]'  # 和上面一致：匹配任何包含 4個連續(xù)數(shù)字 的字符串
select 列名 from 表名 where 列名 regexp '\\([0-9]aas?\\)'  # 匹配任何包含 以(開頭，后跟一個數(shù)字，后跟aas或者aa(aaa? 只匹配 aas 或 aa) 最后跟一個) 的字符串

定位元字符

元字符	說明
^	文本的開始
$	文本的結(jié)尾
[[:<:]]	詞的開始
[[:>:]]	詞的結(jié)尾

# 舉例
select 列名 from 表名 where 列名 regexp '^[0-9\\.]'  # 匹配任何 以一個數(shù)字（0-9）或一個點（.）開始 的字符串
select 列名 from 表名 where 列名 regexp '[0-9\\.]'  # 匹配任何 包含至少一個數(shù)字（0-9）或一個點號（.）的字符串。

創(chuàng)建計算字段

拼接字段：Concat()需要一個或多個指定的串，各個串之間用逗號分隔。

select concat(列名1, '(', 列名2, ')') from 表名 order by 列名1;  # 返回結(jié)果：列名1(列名2)

刪除數(shù)據(jù)右側(cè)多余的空格：RTrim()

select concat(rtrim(列名1), '(', 列名2, ')') from 表名 order by 列名1;  # rtrim()：去掉右邊所有空格;  ltrim()：去掉左邊所有空格;  trim()：去掉兩邊所有空格

使用別名：as

select concat(rtrim(列名1), '(', 列名2, ')') as 新列名 from 表名 order by 列名1;

執(zhí)行算數(shù)計算：

select 列名3+列名4 as 新列名 from 表名;

算術(shù)操作符

操作符	說明
+	加
-	減
*	乘
/	除

使用數(shù)據(jù)處理函數(shù)

文本處理函數(shù)

函數(shù)	說明
upper(string)	將文本轉(zhuǎn)換為大寫
trim(string)	去掉兩邊所有空格
left(string, length)	返回串左邊的字符
length(string)	返回串的長度
locate(substring, string, [start_position])	返回一個子字符串在另一個字符串中首次出現(xiàn)的位置
lower(string)	將串轉(zhuǎn)換為小寫
right(string, length)	返回串右邊的字符
soundex(string)	將字符串轉(zhuǎn)換為一個表示其發(fā)音的編碼值（用于模糊匹配和聲音相似度比較）
subString(string, start, length)	從一個字符串中提取指定位置開始的子字符串

select left(列名1, 3) as 新列名 from 表名;  # 返回列名1的前3個字符
select locate('Pro', product_name, 3) from 表名;  # 從 列product_name 第3個字符開始 找到 Pro 首次出現(xiàn)的位置（找不到返回 0 ）
select right(列名1, 3) as 新列名 from 表名;  # 返回列名1的后3個字符
select soundex(列名) from 表名;  # 返回每個數(shù)據(jù)發(fā)音的編碼值
select 列名 from 表名 where soundex(列名) = soundex('Smith');  # 想找出所有發(fā)音與 "Smith" 相似的數(shù)據(jù)
select subString(列名, 1, 3) FROM 表名;  # 提取前 3 個字符
select subString(列名, 4) FROM 表名;  # 從第 4 個字符開始提取，提取到字符串的末尾

日期和時間處理函數(shù)

函數(shù)	說明
AddDate()	增加一個日期（天、周等）
AddTime()	增加一個時間（時、分等）
CurDate()	返回當前日期
CurTime()	返回當前時間
Date()	返回日期時間的日期部分
DateDiff()	計算兩個日期之差
Date_Add()	高度靈活的日期運算函數(shù)
Date_Format()	返回一個格式化的日期或時間串
Day()	返回一個日期的天數(shù)部分
DayOfWeek()	對于一個日期，返回對應的星期幾
Hour()	返回一個時間的小時部分
Minute()	返回一個時間的分鐘部分
Month()	返回一個日期的月份部分
Now()	返回當前日期和時間
Second()	返回一個時間的秒部分
Time()	返回一個日期時間的時間部分
Year()	返回一個日期的年份部分

數(shù)值處理函數(shù)

函數(shù)	說明
Abs()	返回一個數(shù)的絕對值
Cos()	返回一個角度的余弦
Exp ()	返回一個數(shù)的指數(shù)值
Mod()	返回除操作的余數(shù)
Pi()	返回圓周率
Rand()	返回一個隨機數(shù)
Sin()	返回一個角度的正弦
Sqrt()	返回一個數(shù)的平方根
Tan()	返回一個角度的正切

select abs(列名) from 表名;  # 返回絕對值

匯總數(shù)據(jù)

聚合函數(shù) (MySQL返回結(jié)果一般比你在自己的客戶機應用程序中計算要快得多。)

函數(shù)	說明
AVG()	返回某列的平均值, 忽略列值為`NUL`的行。
COUNT()	返回某列的行數(shù)() `COUNT(*)`對表中行的數(shù)目進行計數(shù)，不管表列中包含的是空值（`NULL`）還是非空值；`COUNT(column)` 對特定列中具有值的行進行計數(shù)，忽略`NULL`值
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

以上5個聚集函數(shù)都可以如下使用：

對所有的行執(zhí)行計算，指定ALL參數(shù)或不給參數(shù)（因為ALL是默認行為）；
只包含不同的值，指定DISTINCT參數(shù)。(如果指定列名，則DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允許使用COUNT(DISTINCT))

select avg(數(shù)值列名) from 表名;
select count(*) from 表名;
select avg(distinct prod_price) as acg_price from prducts where vend_id = 1003;
select count(*) as num_items, min(prod_price) as price_min, max(prod_price) as price_max, avg(prod_price) as price_avg from products;

數(shù)據(jù)分組

GROUP BY子句

如果在GROUP BY子句中嵌套了分組，數(shù)據(jù)將在最后規(guī)定的分組上進行匯總。
GROUP BY子句中列出的每個列都必須是檢索列或有效的表達式（但不能是聚集函數(shù)）。
如果分組列中具有NULL值，則NULL將作為一個分組返回。如果列中有多行NULL值，它們將分為一組。
GROUP BY子句必須出現(xiàn)在WHERE子句之后，ORDER BY子句之前。
使用WITH ROLLUP關(guān)鍵字，可以得到每個分組以及每個分組匯總級別（針對每個分組）的值

select vend_id, count(*) as num_prods from products group by vend_id;  # 對每個vend_id而不是整個表計算num_prods

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;  # WITH ROLLUP 是一個用于生成匯總數(shù)據(jù)的特殊選項。它在原本分組的基礎上，額外生成一個“總計”行，該行顯示所有vend_id的總產(chǎn)品數(shù)量。

HAVING子句

WHERE過濾行，HAVING過濾分組。

SELECT cust_id，COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;  # 過濾掉 聚合后cust_id數(shù)量 小于2 的 cust_id

SELECT vend_id，COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*) >= 2；  # 過濾掉 聚合后vend_id數(shù)量小于2 且 prod_price大于或等于10的 vend_id

ORDER BY 子句

一般在使用GROUP BY子句時，應該也給出ORDER BY子句

SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal;  # 查詢返回了所有ordertotal大于或等于 50 的order_num，并且按照ordertotal從小到大排序。

select 子句順序

子句	說明	是否必須使用
select	要返回的列或表達式	是
from	從中檢索數(shù)據(jù)的表	僅在從表選擇數(shù)據(jù)時使用
where	行級過濾	否
group by	分組說明	僅在按組計算聚集時使用
having	組級過濾	否
order by	輸出排序順序	否
limit	要檢索的行數(shù)	否

使用子查詢

子查詢：嵌套在其他查詢中的查詢
子查詢從內(nèi)向外處理
在WHERE子句中使用子查詢，應該保證SELECT語句具有與WHERE子句中相同數(shù)目的列。

select cust_nume,cust_contact from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'tnt2'));  # 先在表orderitems中查詢prod_id='tnt2'的order_num，再在orders表中找 order_num=子句中查詢結(jié)果 的cust_id，最后在表customers中找到 cust_id=子句中查詢結(jié)果 的cust_nume和cust_contact

# 修改格式
select cust_nume,cust_contact 
from customers 
where cust_id in (select cust_id 
                  from orders 
                  where order_num in (select order_num 
                                      from orderitems 
                                      where prod_id = 'tnt2'));
                                      
# 等同于
SELECT cust_name，cust_contact
FROM customers,orders,orderitems
wHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';

相關(guān)子查詢：涉及外部查詢的子查詢

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM orders
       	WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

聯(lián)結(jié)表

使用表別名

例如：customers AS c
表別名只在查詢執(zhí)行中使用

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;

SELECT cust_name，cust_contact
FROM customers AS c，orders AS o，orderitems AS oi
WHERE c.Cust_id = o.Cust_id
AND oi.order_num = o.order_num
AND prod_id = 'TNT2'；

等值聯(lián)結(jié)（內(nèi)部聯(lián)結(jié)）
- 例如：vendors.vend_id = products.vend_id
- 外鍵為某個表中的一列，它包含另一個表的主鍵值，定義了兩個表之間的關(guān)系。
- 聯(lián)結(jié)是一種機制，用來在一條SELECT語句中關(guān)聯(lián)表，因此稱之為聯(lián)結(jié)。
- 完全限定列名：在引用的列可能出現(xiàn)二義性時，必須使用完全限定列名（用一個點分隔的表名和列名）。
- 笛卡兒積：由沒有聯(lián)結(jié)條件的表關(guān)系返回的結(jié)果為笛卡兒積。檢索出的行的數(shù)目將是第一個表中的行數(shù)乘以第二個表中的行數(shù)

# 聯(lián)結(jié)（很少用，基本都用下面的內(nèi)聯(lián)結(jié)方式）
SELECT vend_name,prod_name,prod_price
FROM vendors，products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

# 笛卡爾積
SELECT vend_name,prod_name，prod_price
FROM vendorS，products
ORDER BY vend_name，prod_name;

聯(lián)結(jié)多個表

SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems,products,vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;

內(nèi)聯(lián)結(jié)
- 例如：vendors INNER JOIN products ON vendors.vend_id = products.vend_id

# 用內(nèi)聯(lián)結(jié)實現(xiàn)上面等值聯(lián)結(jié)的查詢
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;  # INNER JOIN 表示將 vendors 表與 products 表按照 vend_id 進行連接，連接條件是 vendors.vend_id 與 products.vend_id 相等。這樣能獲取所有與供應商相關(guān)的產(chǎn)品信息。

自聯(lián)結(jié)
- 例如： pl.vend_id = p2.vend_id
- p1和p2是同一個表

SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id = 'DTNTR');
                 
# 自聯(lián)結(jié)實現(xiàn)上述查詢
SELECT pl.prod_id, pl.prod_name
FROM products AS pl, products AS p2
WHERE pl.vend_id = p2.vend_id
  AND p2.prod_id = 'DTNTR';

自然聯(lián)結(jié)
- 無論何時對表進行聯(lián)結(jié)，應該至少有一個列出現(xiàn)在不止一個表中（被聯(lián)結(jié)的列）。標準的聯(lián)結(jié)返回所有數(shù)據(jù)，甚至相同的列多次出現(xiàn)。自然聯(lián)結(jié)排除多次出現(xiàn)，使每個列只返回一次。
- 自然聯(lián)結(jié)是這樣一種聯(lián)結(jié)，其中你只能選擇那些唯一的列。這一般是通過對表使用通配符（SELECT *），對所有其他表的列使用明確的子集來完成的

SELECT c.*, o.order_num, o.order_date,
oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND oi.prod_id = 'FB';

外部聯(lián)結(jié)
- 聯(lián)結(jié)包含了那些在相關(guān)表中沒有關(guān)聯(lián)行的行。這種類型的聯(lián)結(jié)稱為外部聯(lián)結(jié)
  - 在使用OUTER JOIN語法時，必須使用RIGHT或LEFT關(guān)鍵字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右邊的表）。上面的例子使用LEFT OUTER JOIN從FROM子句的左邊表（customers表）中選擇所有行。

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;  # 返回 左表（第一個表） 中的所有記錄,對于沒有匹配的右表記錄，查詢結(jié)果中的相關(guān)列會顯示為 NULL

使用帶聚集函數(shù)的聯(lián)結(jié)

SELECT customers.cust_name, customers.cust_id,
	   COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

SELECT customers.cust_name，
customers.cust_id,
COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id= orders.cust_id
GROUP BY customers.cust_id;

組合查詢

有兩種基本情況，其中需要使用組合查詢：

在單個查詢中從不同的表返回類似結(jié)構(gòu)的數(shù)據(jù)；
對單個表執(zhí)行多個查詢，按單個查詢返回數(shù)據(jù)。

UNION規(guī)則：

UNION必須由兩條或兩條以上的SELECT語句組成，語句之間用關(guān)鍵字UNION分隔。
UNION中的每個查詢必須包含相同的列、表達式或聚集函數(shù)（不過各個列不需要以相同的次序列出）。
列數(shù)據(jù)類型必須兼容：類型不必完全相同，但必須是DBMS可以隱含地轉(zhuǎn)換的類型（例如，不同的數(shù)值類型或不同的日期類型）。

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5;

SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id in (1001,1002);

# 組合上面兩條語句
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id in (1001,1002);  # 會自動去除重復的行

# 等同于
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
OR vend_id IN (1001,1002);

# 包含重復的行
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id in (1001,1002);  

# 對組合查詢結(jié)果排序
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id in (1001,1002)
ORDER BY vend_id, prod_price;   # 對返回最終結(jié)果進行排序，不是只對第二條select語句進行排序

全文本搜索

全文索引的默認行為是忽略長度小于一定字符數(shù)的詞。這個長度閾值可以通過系統(tǒng)變量 ft_min_word_len 來配置，默認值是 4 字符
MySQL規(guī)定了一條50%規(guī)則，如果一個詞出現(xiàn)在50%以上的行中，則將它作為一個非用詞忽略。50%規(guī)則不用于IN BOOLEAN MODE
如果表中的行數(shù)少于3行，則全文本搜索不返回結(jié)果
忽略詞中的單引號。例如，don’t索引為dont
不具有詞分隔符（包括日語和漢語）的語言不能恰當?shù)胤祷厝谋舅阉鹘Y(jié)果。

CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text)  # 為 note_text 字段創(chuàng)建全文索引
)ENGINE=MyISAM;

如果正在導入數(shù)據(jù)到一個新表，應該首先導入所有數(shù)據(jù)，然后再修改表，定義FULLTEXT。
必須先創(chuàng)建了FULLTEXT索引，才能使用match()against()進行全文檢索查詢
Match()指定被搜索的列，Against()指定要使用的搜索表達式

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

SELECT note_text,
	Match(note_text) Against('rabbit') AS rank  # 計算每條記錄與搜索詞的相關(guān)性
FROM productnotes;  # 查詢結(jié)果返回note_text,rank兩列

查詢擴展

進行一個基本的全文本搜索，找出與搜索條件匹配的所有行
MySQL檢查這些匹配行并選擇所有有用的詞
MySQL再次進行全文本搜索，這次不僅使用原來的條件，而且還使用所有有用的詞。

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);  # 在原始查詢基礎上，自動找到與 'anvils' 相關(guān)的其他詞匯（這些詞是基于 FULLTEXT 索引的相關(guān)文檔生成的）。

布爾文本搜索

即使沒有FULLTEXT索引也可以使用
在布爾方式中，不按等級值降序排序返回的行

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*'IN BOOLEAN MODE);  # 匹配包含heavy但不包含任意以rope開始的詞的行

全文本布爾操作符

布爾操作符	說明
+	包含，詞必須存在
-	排除，詞必須不出現(xiàn)
>	包含，而且增加等級值
<	包含，且減少等級值
()	把詞組成子表達式（允許這些子表達式作為一個組被包含、排除、排列等）
~	取消一個詞的排序值
*	詞尾的通配符
“”	定義一個短語（與單個詞的列表不一樣，它匹配整個短語以便包含或排除這個短語）

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);  # 匹配包含詞rabbit和bait的行

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);  # 匹配包含rabbit和bait中的至少一個詞的行

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);  # 匹配短語rabbit bait而不是匹配兩個詞rabbit和 bait

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);  # 匹配rabbit和carrot，增加前者的等級，降低后者的等級

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);  # 匹配詞safe和combination，降低后者的等級。