138 張圖帶你 MySQL 入門

SQL 基礎(chǔ)使用

MySQL 是一種關(guān)系型數(shù)據(jù)庫，說到關(guān)系，那么就離不開表與表之間的關(guān)系，而最能體現(xiàn)這種關(guān)系的其實(shí)就是我們接下來需要介紹的主角 SQL，SQL 的全稱是 Structure Query Language ，結(jié)構(gòu)化的查詢語言，它是一種針對表關(guān)聯(lián)關(guān)系所設(shè)計(jì)的一門語言，也就是說，學(xué)好 MySQL，SQL 是基礎(chǔ)和重中之重。SQL 不只是 MySQL 中特有的一門語言，大多數(shù)關(guān)系型數(shù)據(jù)庫都支持這門語言。

下面我們就來一起學(xué)習(xí)一下這門非常重要的語言。

查詢語言分類

在了解 SQL 之前我們需要知道下面這幾個(gè)概念

• 數(shù)據(jù)定義語言：簡稱DDL (Data Definition Language)，用來定義數(shù)據(jù)庫對象:數(shù)據(jù)庫、表、列等；
• 數(shù)據(jù)操作語言：簡稱DML (Data Manipulation Language)，用來對數(shù)據(jù)庫中表的記錄進(jìn)行更新。關(guān)鍵字：insert、update、delete等
• 數(shù)據(jù)控制語言：簡稱DCL(Data Control Language)，用來定義數(shù)據(jù)庫訪問權(quán)限和安全級別，創(chuàng)建用戶等。關(guān)鍵字：grant等
• 數(shù)據(jù)查詢語言：簡稱DQL(Data Query Language)，用來查詢數(shù)據(jù)庫中表的記錄，關(guān)鍵字：select from where等

DDL 語句

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

下面就開始我們的 SQL 語句學(xué)習(xí)之旅，首先你需要啟動 MySQL 服務(wù)，我這里是 mac 電腦，所以我直接可以啟動

然后我們使用命令行的方式連接數(shù)據(jù)庫，打開 iterm，輸入下面

MacBook:~ mr.l$ mysql -uroot -p

就可以連接到數(shù)據(jù)庫了

在上面命令中，mysql 代表客戶端命令，- u 表示后面需要連接的用戶，-p 表示需要輸入此用戶的密碼。在你輸入用戶名和密碼后，如果成功登陸，會顯示一個(gè)歡迎界面（如上圖 ）和 mysql> 提示符。

歡迎界面主要描述了這些東西

• 每一行的結(jié)束符，這里用 ; 或者 \g 來表示每一行的結(jié)束
• 「Your MySQL connection id is 4」，這個(gè)記錄了 MySQL 服務(wù)到目前為止的連接數(shù)，每個(gè)新鏈接都會自動增加 1 ，上面顯示的連接次數(shù)是 4 ，說明我們只連接了四次
• 然后下面是 MySQL 的版本，我們使用的是 5.7
• 通過 help 或者 \h 命令來顯示幫助內(nèi)容，通過 \c 命令來清除命令行 buffer。

然后需要做的事情是什么？我們最終想要學(xué)習(xí) SQL 語句，SQL 語句肯定是要查詢數(shù)據(jù)，通過數(shù)據(jù)來體現(xiàn)出來表的關(guān)聯(lián)關(guān)系，所以我們需要數(shù)據(jù)，那么數(shù)據(jù)存在哪里呢？數(shù)據(jù)存儲的位置被稱為 表(table)，表存儲的位置被稱為 數(shù)據(jù)庫(database)，所以我們需要先建數(shù)據(jù)庫后面再建表然后插入數(shù)據(jù)，再進(jìn)行查詢。

所以我們首先要做的就是創(chuàng)建數(shù)據(jù)庫，創(chuàng)建數(shù)據(jù)庫可以直接使用指令

CREATE DATABASE dbname;

進(jìn)行創(chuàng)建，比如我們創(chuàng)建數(shù)據(jù)庫 cxuandb

create database cxuandb;

注意最后的 ; 結(jié)束語法一定不要丟掉，否則 MySQL 會認(rèn)為你的命令沒有輸出完，敲 enter 后會直接換行輸出

創(chuàng)建完成后，會提示 「Query OK, 1 row affected」，這段語句什么意思呢？Query OK 表示的就是查詢完成，為什么會顯示這個(gè)？因?yàn)樗械?DDL 和 DML 操作執(zhí)行完成后都會提示這個(gè)， 也可以理解為操作成功。后面跟著的 **1 row affected ** 表示的是影響的行數(shù)，() 內(nèi)顯示的是你執(zhí)行這條命令所耗費(fèi)的時(shí)間，也就是 0.03 秒。

上圖我們成功創(chuàng)建了一個(gè) cxuandb 的數(shù)據(jù)庫，此時(shí)我們還想創(chuàng)建一個(gè)數(shù)據(jù)庫，我們再執(zhí)行相同的指令，結(jié)果提示

提示我們不能再創(chuàng)建數(shù)據(jù)庫了，數(shù)據(jù)庫已經(jīng)存在。這時(shí)候我就有疑問了，我怎么知道都有哪些數(shù)據(jù)庫呢？別我再想創(chuàng)建一個(gè)數(shù)據(jù)庫又告訴我已經(jīng)存在，這時(shí)候可以使用 show databases 命令來查看你的 MySQL 已有的數(shù)據(jù)庫

show databases;

執(zhí)行完成后的結(jié)果如下

因?yàn)閿?shù)據(jù)庫我之前已經(jīng)使用過，這里就需要解釋一下，除了剛剛新創(chuàng)建成功的 cxuandb 外，informationn_schema 、performannce_schema 和 sys 都是系統(tǒng)自帶的數(shù)據(jù)庫，是安裝 MySQL 默認(rèn)創(chuàng)建的數(shù)據(jù)庫。它們各自表示

• informationn_schema：主要存儲一些數(shù)據(jù)庫對象信息，比如用戶表信息、權(quán)限信息、分區(qū)信息等
• performannce_schema：MySQL 5.5 之后新增加的數(shù)據(jù)庫，主要用于收集數(shù)據(jù)庫服務(wù)器性能參數(shù)。
• sys: MySQL 5.7 提供的數(shù)據(jù)庫，sys 數(shù)據(jù)庫里面包含了一系列的存儲過程、自定義函數(shù)以及視圖來幫助我們快速的了解系統(tǒng)的元數(shù)據(jù)信息。

其他所有的數(shù)據(jù)庫都是作者自己創(chuàng)建的，可以忽略他們。

在創(chuàng)建完數(shù)據(jù)庫之后，可以用如下命令選擇要操作的數(shù)據(jù)庫

use cxuandb

這樣就成功切換為了 cxuandb 數(shù)據(jù)庫，我們可以在此數(shù)據(jù)庫下進(jìn)行建表、查看基本信息等操作。

比如想要看康康我們新建的數(shù)據(jù)庫里面有沒有其他表

show tables;

果然，我們新建的數(shù)據(jù)庫下面沒有任何表，但是現(xiàn)在，我們還不進(jìn)行建表操作，我們還是先來認(rèn)識一下數(shù)據(jù)庫層面的命令，也就是其他 DDL 指令

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

如果一個(gè)數(shù)據(jù)庫我們不想要了，那么該怎么辦呢？直接刪掉數(shù)據(jù)庫不就好了嗎？刪表語句是

drop database dbname;

比如 cxuandb 我們不想要他了，可以通過使用

drop database cxuandb;

進(jìn)行刪除，這里我們就不進(jìn)行演示了，因?yàn)?cxuandb 我們后面還會使用。

但是這里注意一點(diǎn)，你刪除數(shù)據(jù)庫成功后會出現(xiàn) 「0 rows affected」，這個(gè)可以不用理會，因?yàn)樵?MySQL 中，drop 語句操作的結(jié)果都是 「0 rows affected」。

創(chuàng)建表

下面我們就可以對表進(jìn)行操作了，我們剛剛 show tables 發(fā)現(xiàn)還沒有任何表，所以我們現(xiàn)在進(jìn)行建表語句

CREATE TABLE 表名稱
(
列名稱1 數(shù)據(jù)類型 約束,
列名稱2 數(shù)據(jù)類型 約束,
列名稱3 數(shù)據(jù)類型 約束,
....
)

這樣就很清楚了吧，列名稱就是列的名字，緊跟著列名后面就是數(shù)據(jù)類型，然后是約束，為什么要這么設(shè)計(jì)？舉個(gè)例子你就清楚了，比如 cxuan 剛被生出來就被打印上了標(biāo)簽

比如我們創(chuàng)建一個(gè)表，里面有 5 個(gè)字段，姓名(name)、性別(sex)、年齡(age)、何時(shí)雇傭(hiredate)、薪資待遇(wage)，建表語句如下

create table job(name varchar(20), sex varchar(2), age int(2), hiredate date, wage decimal(10,2));

事實(shí)證明這條建表語句還是沒問題的，建表完成后可以使用 DESC tablename 查看表的基本信息

DESC 命令會查看表的定義，但是輸出的信息還不夠全面，所以，如果想要查看更全的信息，還要通過查看表的創(chuàng)建語句的 SQL 來得到

show create table job \G;

可以看到，除了看到表定義之外，還看到了表的 engine(存儲引擎) 為 InnoDB 存儲引擎，\G 使得記錄能夠豎著排列，如果不用 \G 的話，效果如下

刪除表

表的刪除語句有兩種，一種是 drop 語句，SQL 語句如下

drop table job

一種是 truncate 語句，SQL 語句如下

truncate table job

這兩者的區(qū)別簡單理解就是 drop 語句刪除表之后，可以通過日志進(jìn)行回復(fù)，而 truncate 刪除表之后永遠(yuǎn)恢復(fù)不了，所以，一般不使用 truncate 進(jìn)行表的刪除?！?/span>

修改表

對于已經(jīng)創(chuàng)建好的表，尤其是有大量數(shù)據(jù)的表，如果需要對表做結(jié)構(gòu)上的改變，可以將表刪除然后重新創(chuàng)建表，但是這種效率會產(chǎn)生一些額外的工作，數(shù)據(jù)會重新加載近來，如果此時(shí)有服務(wù)正在訪問的話，也會影響服務(wù)讀取表中數(shù)據(jù)，所以此時(shí)，我們需要表的修改語句來對已經(jīng)創(chuàng)建好的表的定義進(jìn)行修改。

修改表結(jié)構(gòu)一般使用 alter table 語句，下面是常用的命令

ALTER TABLE tb MODIFY [COLUMN] column_definition [FIRST | AFTER col_name];

比如我們想要將 job 表中的 name 由 varchar(20) 改為 varchar(25)，可以使用如下語句

alter table job modify name varchar(25);

也可以對表結(jié)構(gòu)進(jìn)行修改，比如增加一個(gè)字段

alter table job add home varchar(30);

將新添加的表的字段進(jìn)行刪除

alter table job drop column home;

可以對表中字段的名稱進(jìn)行修改，比如吧 wage 改為 salary

alter table job change wage salary decimal(10,2);

修改字段的排列順序，我們前面介紹過修改語法涉及到一個(gè)順序問題，都有一個(gè)可選項(xiàng) first | after?column_name，這個(gè)選項(xiàng)可以用來修改表中字段的位置，默認(rèn) ADD 是在添加為表中最后一個(gè)字段，而 「CHANGE/MODIFY」 不會改變字段位置。比如

alter table job add birthday after hiredate;

可以對表名進(jìn)行修改，例如將 job 表改為 worker

alter table job rename worker;

DML 語句

有的地方把 DML 語句（增刪改）和 DQL 語句（查詢）統(tǒng)稱為 DML 語句，有的地方分開，我們目前使用分開稱呼的方式

插入

表創(chuàng)建好之后，我們就可以向表里插入數(shù)據(jù)了，插入記錄的基本語法如下

INSERT INTO tablename (field1,field2) VALUES(value1,value2);

例如，向中插入以下記錄

insert into job(name,sex,age,hiredate,birthday,salary) values("cxuan","男",24,"2020-04-27","1995-08-22",8000);

也可以不用指定要插入的字段，直接插入數(shù)據(jù)即可

insert into job values("cxuan02","男",25,"2020-06-01","1995-04-23",12000);

這里就有一個(gè)問題，如果插入的順序不一致的話會怎么樣呢？

對于含可空字段、非空但是含有默認(rèn)值的字段、自增字段可以不用在 insert 后的字段列表出現(xiàn)，values 后面只需要寫對應(yīng)字段名稱的 value 即可，沒有寫的字段可以自動的設(shè)置為 NULL、默認(rèn)值或者自增的下一個(gè)值，這樣可以縮短要插入 SQL 語句的長度和復(fù)雜性。

比如我們設(shè)置一下 hiredate、age 可以為 null，來試一下

insert into job(name,sex,birthday,salary) values("cxuan03","男","1992-08-23",15000);

我們看一下實(shí)際插入的數(shù)據(jù)

我們可以看到有一行兩個(gè)字段顯示 NULL。在 MySQL 中，insert 語句還有一個(gè)很好的特性，就是一次可以插入多條記錄

INSERT INTO tablename (field1,field2) VALUES
(value1,value2),
(value1,value2),
(value1,value2),
...;

可以看出，每條記錄之間都用逗號進(jìn)行分割，這個(gè)特性可以使得 MySQL 在插入大量記錄時(shí)，節(jié)省很多的網(wǎng)絡(luò)開銷，大大提高插入效率。

更新記錄

對于表中已經(jīng)存在的數(shù)據(jù)，可以通過 update 命令對其進(jìn)行修改，語法如下

UPDATE tablename SET field1 = value1, field2 = value2 ;

例如，將 job 表中的 cxuan03 中 age 的 NULL 改為 26，SQL 語句如下

update job set age = 26 where name = 'cxuan03';

SQL 語句中出現(xiàn)了一個(gè) where 條件，我們會在后面說到 where 條件，這里簡單理解一下它的概念就是根據(jù)哪條記錄進(jìn)行更新，如果不寫 where 的話，會對整個(gè)表進(jìn)行更新

刪除記錄

如果記錄不再需要，可以使用 delete 命令進(jìn)行刪除

DELETE FROM tablename [WHERE CONDITION]

例如，在 job 中刪除名字是 cxuan03 的記錄

delete from job where name = 'cxuan03';

在 MySQL 中，刪除語句也可以不指定 where 條件，直接使用

delete from job

這種刪除方式相當(dāng)于是清楚表的操作，表中所有的記錄都會被清除。

DQL 語句

下面我們一起來認(rèn)識一下 DQL 語句，數(shù)據(jù)被插入到 MySQL 中，就可以使用 SELECT 命令進(jìn)行查詢，來得到我們想要的結(jié)果。

SELECT 查詢語句可以說是最復(fù)雜的語句了，這里我們只介紹一下基本語法

一種最簡單的方式就是從某個(gè)表中查詢出所有的字段和數(shù)據(jù)，簡單粗暴，直接使用 SELECT *

SELECT * FROM tablename;

例如我們將 job 表中的所有數(shù)據(jù)查出來

select * from job;

其中 * 是查詢出所有的數(shù)據(jù)，當(dāng)然，你也可以查詢出指定的數(shù)據(jù)項(xiàng)

select name,sex,age,hiredate,birthday,salary from job;

上面這條 SQL 語句和 select * from job 表是等價(jià)的，但是這種直接查詢指定字段的 SQL 語句效率要高。

上面我們介紹了基本的 SQL 查詢語句，但是實(shí)際的使用場景會會比簡單查詢復(fù)雜太多，一般都會使用各種 SQL 的函數(shù)和查詢條件等，下面我們就來一起認(rèn)識一下。

去重

使用非常廣泛的場景之一就是 去重，去重可以使用 distinct 關(guān)鍵字來實(shí)現(xiàn)

為了演示效果，我們先向數(shù)據(jù)庫中插入批量數(shù)據(jù)，插入完成后的表結(jié)構(gòu)如下

下面我們使用 distinct 來對 age 去重來看一下效果

你會發(fā)現(xiàn)只有兩個(gè)不同的值，其他和 25 重復(fù)的值被過濾掉了，所以我們使用 distinct 來進(jìn)行去重

條件查詢

我們之前的所有例子都是查詢?nèi)康挠涗?，如果我們只想查詢指定的記錄呢？這里就會用到 where條件查詢語句，條件查詢可以對指定的字段進(jìn)行查詢，比如我們想查詢所有年齡為 24 的記錄，如下

select * from job where age = 24;

where 條件語句后面會跟一個(gè)判斷的運(yùn)算符 =，除了 = 號比較外，還可以使用 「>、<、>=、<=、!=」 等比較運(yùn)算符；例如

select * from job where age >= 24;

就會從 job 表中查詢出 age 年齡大于或等于 24 的記錄

除此之外，在 where 條件查詢中還可以有多個(gè)并列的查詢條件，比如我們可以查詢年齡大于等于 24，并且薪資大雨 8000 的記錄

select * from job where age >= 24 and salary > 8000;

多個(gè)條件之間還可以使用 or、and 等邏輯運(yùn)算符進(jìn)行多條件聯(lián)合查詢，運(yùn)算符會在以后章節(jié)中詳細(xì)講解。

排序

我們會經(jīng)常有這樣的需求，按照某個(gè)字段進(jìn)行排序，這就用到了數(shù)據(jù)庫的排序功能，使用關(guān)鍵字 order by 來實(shí)現(xiàn)，語法如下

SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC] ， field2 [DESC|ASC]，……fieldn [DESC|ASC]]

其中 DESC 和 ASC 就是順序排序的關(guān)鍵字，DESC 會按照字段進(jìn)行降序排列，ASC 會按照字段進(jìn)行升序排列，默認(rèn)會使用升序排列，也就是說，你不寫 order by 具體的排序的話，默認(rèn)會使用升序排列。order by 后面可以跟多個(gè)排序字段，并且每個(gè)排序字段可以有不同的排序順序。

為了演示功能，我們先把表中的 salary 工資列進(jìn)行修改，修改完成后的表記錄如下

下面我們按照工資進(jìn)行排序，SQL 語句如下

select * from job order by salary desc;

語句執(zhí)行完成后的結(jié)果如下

這是對一個(gè)字段進(jìn)行排序的結(jié)果，也可以對多個(gè)字段進(jìn)行排序，但是需要注意一點(diǎn)

?

根據(jù) order by 后面聲名的順序進(jìn)行排序，如果有三個(gè)排序字段 A、B、C 的話，如果 A 字段排序字段的值一樣，則會根據(jù)第二個(gè)字段進(jìn)行排序，以此類推。

如果只有一個(gè)排序字段，那么這些字段相同的記錄將會無序排列。

?

限制

對于排序后的字段，或者不排序的字段，如果只希望顯示一部分的話，就會使用 LIMIT 關(guān)鍵字來實(shí)現(xiàn)，比如我們只想取前三條記錄

select * from job limit 3;

或者我們對排序后的字段取前三條記錄

select * from job order by salary limit 3;

上面這種 limit 是從表記錄的第 0 條開始取，如果從指定記錄開始取，比如從第二條開始取，取三條記錄，SQL 如下

select * from job order by salary desc limit 2,3;

limit 一般經(jīng)常和 order by 語法一起實(shí)現(xiàn)分頁查詢。

?

注意：limit 是 MySQL 擴(kuò)展 SQL92 之后的語法，在其他數(shù)據(jù)庫比如 Oracle 上就不通用，我犯過一個(gè)白癡的行為就是在 Oracle 中使用 limit 查詢語句。。。

?

聚合

下面我們來看一下對記錄進(jìn)行匯總的操作，這類操作主要有

• 匯總函數(shù)，比如 sum 求和、count 統(tǒng)計(jì)數(shù)量、max 最大值、min 最小值等
• group by，關(guān)鍵字表示對分類聚合的字段進(jìn)行分組，比如按照部門統(tǒng)計(jì)員工的數(shù)量，那么 group by 后面就應(yīng)該跟上部門
• with 是可選的語法，它表示對匯總之后的記錄進(jìn)行再次匯總
• having 關(guān)鍵字表示對分類后的結(jié)果再進(jìn)行條件的過濾。

?

看起來 where 和 having 意思差不多，不過它們用法不一樣，where 是使用在統(tǒng)計(jì)之前，對統(tǒng)計(jì)前的記錄進(jìn)行過濾，having 是用在統(tǒng)計(jì)之后，是對聚合之后的結(jié)果進(jìn)行過濾。也就是說 where 永遠(yuǎn)用在 having 之前，我們應(yīng)該先對篩選的記錄進(jìn)行過濾，然后再對分組的記錄進(jìn)行過濾。

?

可以對 job 表中員工薪水進(jìn)行統(tǒng)計(jì)，選出總共的薪水、最大薪水、最小薪水

select sum(salary) from job;

select max(salary),min(salary) from job;

比如我們要統(tǒng)計(jì) job 表中人員的數(shù)量

select count(1) from job;

統(tǒng)計(jì)完成后的結(jié)果如下

我們可以按照 job 表中的年齡來進(jìn)行對應(yīng)的統(tǒng)計(jì)

select age,count(1) from job group by age;

既要統(tǒng)計(jì)各年齡段的人數(shù)，又要統(tǒng)計(jì)總?cè)藬?shù)

select age,count(1) from job group by age with rollup;

在此基礎(chǔ)上進(jìn)行分組，統(tǒng)計(jì)數(shù)量大于 1 的記錄

select age,count(1) from job group by age with rollup having count(1) > 1;

表連接

表連接一直是筆者比較痛苦的地方，曾經(jīng)因?yàn)橐粋€(gè)表連接掛了面試，現(xiàn)在來認(rèn)真擼一遍。

表連接一般體現(xiàn)在表之間的關(guān)系上。當(dāng)需要同時(shí)顯示多個(gè)表中的字段時(shí)，就可以用表連接來實(shí)現(xiàn)。

為了演示表連接的功能，我們?yōu)?job 表加一個(gè) type 字段表示工作類型，增加一個(gè) job_type 表表示具體的工作種類，如下所示

下面開始我們的演示

查詢出 job 表中的 type 和 job_type 表中的 type 匹配的姓名和工作類型

select job.name,job_type.name from job,job_type where job.type = job_type.type;

上面這種連接使用的是內(nèi)連接，除此之外，還有外連接。那么它們之間的區(qū)別是啥呢？

?

內(nèi)連接：選出兩張表中互相匹配的記錄；

外連接：不僅選出匹配的記錄，也會選出不匹配的記錄；

?

外連接分為兩種

• 左外連接：篩選出包含左表的記錄并且右表沒有和它匹配的記錄
• 右外連接：篩選出包含右表的記錄甚至左表沒有和它匹配的記錄

為了演示效果我們在 job 表和 job_type 表中分別添加記錄，添加完成后的兩表如下

下面我們進(jìn)行左外連接查詢：查詢出 job 表中的 type 和 job_type 表中的 type 匹配的姓名和工作類型

select job.name,job_type.name from job left join job_type on job.type = job_type.type;

查詢出來的結(jié)果如下

可以看出 cxuan06 也被查詢出來了，而 cxuan06 他沒有具體的工作類型。

使用右外連接查詢

select job.name,job_type.name from job right join job_type on job.type = job_type.type;

可以看出，job 表中并沒有 waiter 和 manager 的角色，但是也被查詢出來了。

子查詢

有一些情況，我們需要的查詢條件是另一個(gè) SQL 語句的查詢結(jié)果，這種查詢方式就是子查詢，子查詢有一些關(guān)鍵字比如 「in、not in、=、!=、exists、not exists」 等，例如我們可以通過子查詢查詢出每個(gè)人的工作類型

select job.* from job where type in (select type from job_type);

如果子查詢數(shù)量唯一的話，還可以用 = 來替換 in

select * from job where type = (select type from job_type);

意思是自查詢不唯一，我們使用 limit 限制一下返回的記錄數(shù)

select * from job where type = (select type from job_type limit 1,1);

在某些情況下，子查詢可以轉(zhuǎn)換為表連接

聯(lián)合查詢

我們還經(jīng)常會遇到這樣的場景，將兩個(gè)表的數(shù)據(jù)單獨(dú)查詢出來之后，將結(jié)果合并到一起進(jìn)行顯示，這個(gè)時(shí)候就需要 UNION 和 UNION ALL 這兩個(gè)關(guān)鍵字來實(shí)現(xiàn)這樣的功能，UNION 和 UNION ALL 的主要區(qū)別是 UNION ALL 是把結(jié)果集直接合并在一起，而 UNION 是將 UNION ALL 后的結(jié)果進(jìn)行一次 DISTINCT 去除掉重復(fù)數(shù)據(jù)。

比如

select type from job union all select type from job_type;

它的結(jié)果如下

上述結(jié)果是查詢 job 表中的 type 字段和 job_type 表中的 type 字段，并把它們進(jìn)行匯總，可以看出 UNION ALL 只是把所有的結(jié)果都列出來了

使用 UNION 的 SQL 語句如下

select type from job union select type from job_type;

可以看出 UNION 是對 UNION ALL 使用了 distinct 去重處理。

DCL 語句

DCL 語句主要是管理數(shù)據(jù)庫權(quán)限的時(shí)候使用，這類操作一般是 DBA 使用的，開發(fā)人員不會使用 DCL 語句。

關(guān)于幫助文檔的使用

我們一般使用 MySQL 遇到不會的或者有疑問的東西經(jīng)常要去查閱網(wǎng)上資料，甚至可能需要去查 MySQL 官發(fā)文檔，這樣會耗費(fèi)大量的時(shí)間和精力。

下面教你一下在 MySQL 命令行就能直接查詢資料的語句

按照層次查詢

可以使用 ? contents 來查詢所有可供查詢的分類，如下所示

? contents;

我們輸入

? Account Management

可以查詢具體關(guān)于權(quán)限管理的命令

比如我們想了解一下數(shù)據(jù)類型

? Data Types

然后我們想了解一下 VARCHAR 的基本定義，可以直接使用

? VARCHAR

可以看到有關(guān)于 VARCHAR 數(shù)據(jù)類型的詳細(xì)信息，然后在最下面還有 MySQL 的官方文檔，方便我們快速查閱。

快速查閱

在實(shí)際應(yīng)用過程中，如果要快速查詢某個(gè)語法時(shí)，可以使用關(guān)鍵字進(jìn)行快速查詢，比如我們使用

? show

能夠快速列出一些命令

比如我們想要查閱 database 的信息，使用

SHOW CREATE DATABASE cxuandb;

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

MySQL 提供很多種數(shù)據(jù)類型來對不同的常量、變量進(jìn)行區(qū)分，MySQL 中的數(shù)據(jù)類型主要是 「數(shù)值類型、日期和時(shí)間類型、字符串類型」 選擇合適的數(shù)據(jù)類型進(jìn)行數(shù)據(jù)的存儲非常重要，在實(shí)際開發(fā)過程中，選擇合適的數(shù)據(jù)類型也能夠提高 SQL 性能，所以有必要認(rèn)識一下這些數(shù)據(jù)類型。

數(shù)值類型

MySQL 支持所有標(biāo)準(zhǔn)的 SQL 數(shù)據(jù)類型，這些數(shù)據(jù)類型包括嚴(yán)格數(shù)據(jù)類型的嚴(yán)格數(shù)值類型，這些數(shù)據(jù)類型有

• INTEGER
• SMALLINT
• DECIMAL
• NUMERIC。

近似數(shù)值數(shù)據(jù)類型 并不用嚴(yán)格按照指定的數(shù)據(jù)類型進(jìn)行存儲，這些有

• FLOAT
• REAL
• DOUBLE PRECISION

還有經(jīng)過擴(kuò)展之后的數(shù)據(jù)類型，它們是

• TINYINT
• MEDIUMINT
• BIGINT
• BIT

其中 INT 是 INTEGER 的縮寫，DEC 是 DECIMAL 的縮寫。

下面是所有數(shù)據(jù)類型的匯總

整數(shù)

在整數(shù)類型中，按照取值范圍和存儲方式的不同，分為

• TINYINT ，占用 1 字節(jié)
• SMALLINT，占用 2 字節(jié)
• MEDIUMINT，占用 3 字節(jié)
• INT、INTEGER，占用 4 字節(jié)
• BIGINT，占用 8 字節(jié)

五個(gè)數(shù)據(jù)類型，如果超出類型范圍的操作，會發(fā)生錯誤提示，所以選擇合適的數(shù)據(jù)類型非常重要。

還記得我們上面的建表語句么

我們一般會在 SQL 語句的數(shù)據(jù)類型后面加上指定長度來表示數(shù)據(jù)類型許可的范圍，例如

int(7)

表示 int 類型的數(shù)據(jù)最大長度為 7，如果填充不滿的話會自動填滿，如果不指定 int 數(shù)據(jù)類型的長度的話，默認(rèn)是 int(11)。

我們創(chuàng)建一張表來演示一下

create table test1(aId int, bId int(5));

/* 然后我們查看一下表結(jié)構(gòu) */
desc test1;

整數(shù)類型一般配合 zerofill 來使用，顧名思義，就是用 0 進(jìn)行填充，也就是數(shù)字位數(shù)不夠的空間使用 0 進(jìn)行填充。

分別修改 test1 表中的兩個(gè)字段

alter table test1 modify aId int zerofill;

alter table test1 modify bId int(5) zerofill;

然后插入兩條數(shù)據(jù)，執(zhí)行查詢操作

如上圖所示，使用zerofill 可以在數(shù)字前面使用 0 來進(jìn)行填充，那么如果寬度超過指定長度后會如何顯示？我們來試驗(yàn)一下，向 aId 和 bId 分別插入超過字符限制的數(shù)字

會發(fā)現(xiàn) aId 已經(jīng)超出了指定范圍，那么我們對 aId 插入一個(gè)在其允許范圍之內(nèi)的數(shù)據(jù)

會發(fā)現(xiàn)，aId 已經(jīng)插進(jìn)去了，bId 也插進(jìn)去了，為什么 bId 顯示的是 int(5) 卻能夠插入 7 位長度的數(shù)值呢？

所有的整數(shù)都有一個(gè)可選屬性 UNSIGNED(無符號)，如果需要在字段里面保存非負(fù)數(shù)或者是需要較大上限值時(shí)，可以使用此選項(xiàng)，它的取值范圍是正常值的下限取 0 ，上限取原值的 2 倍。如果一個(gè)列為 zerofill ，會自動為該列添加 UNSIGNED 屬性。

除此之外，整數(shù)還有一個(gè)類型就是 AUTO_INCREMENT，在需要產(chǎn)生唯一標(biāo)識符或者順序值時(shí)，可利用此屬性，這個(gè)屬性只用于整數(shù)字符。一個(gè)表中最多只有一個(gè) AUTO_INCREMENT 屬性，一般用于自增主鍵，而且 NOT NULL，并且是 PRIMARY KEY 和 UNIQUE 的，主鍵必須保證唯一性而且不為空。

小數(shù)

小數(shù)說的是啥？它其實(shí)有兩種類型；一種是浮點(diǎn)數(shù)類型，一種是定點(diǎn)數(shù)類型；

浮點(diǎn)數(shù)有兩種

• 單精度浮點(diǎn)型 - float 型
• 雙精度浮點(diǎn)型 - double 型

定點(diǎn)數(shù)只有一種 decimal。定點(diǎn)數(shù)在 MySQL 內(nèi)部中以字符串的形式存在，比浮點(diǎn)數(shù)更為準(zhǔn)確，適合用來表示精度特別高的數(shù)據(jù)。

浮點(diǎn)數(shù)和定點(diǎn)數(shù)都可以使用 (M,D) 的方式來表示，M 表示的就是 「整數(shù)位 + 小數(shù)位」 的數(shù)字，D 表示位于 . 后面的小數(shù)。M 也被稱為精度 ，D 被稱為標(biāo)度。

下面通過示例來演示一下

首先建立一個(gè) test2 表

CREATE TABLE test2 (aId float(6,2) default NULL, bId double(6,2) default NULL,cId decimal(6,2) default NULL)

然后向表中插入幾條數(shù)據(jù)

insert into test2 values(1234.12,1234.12,1234.12);

這個(gè)時(shí)候顯示的數(shù)據(jù)就是

然后再向表中插入一些約束之外的數(shù)據(jù)

insert into test2 values(1234.123,1234.123,1234.123);

發(fā)現(xiàn)插入完成后還顯示的是 1234.12，小數(shù)位第三位的值被舍去了。

現(xiàn)在我們把 test2 表中的精度全部去掉，再次插入

alter table test2 modify aId float;

alter table test2 modify bId double;

alter table test2 modify cId decimal;

先查詢一下，發(fā)現(xiàn) cId 舍去了小數(shù)位。

然后再次插入 1.23，SQL 語句如下

insert into test2 values(1.23,1.23,1.23);

結(jié)果如下

這個(gè)時(shí)候可以驗(yàn)證

• 浮點(diǎn)數(shù)如果不寫精度和標(biāo)度，會按照實(shí)際的精度值進(jìn)行顯示
• 定點(diǎn)數(shù)如果不寫精度和標(biāo)度，會按照 decimal(10,0) 來進(jìn)行操作，如果數(shù)據(jù)超過了精度和標(biāo)題，MySQL 會報(bào)錯

位類型

對于位類型，用于存放字段值，BIT(M) 可以用來存放多位二進(jìn)制數(shù)，M 的范圍是 1 - 64，如果不寫的話默認(rèn)為 1 位。

下面我們來掩飾一下位類型

新建一個(gè) test3 表，表中只有一個(gè)位類型的字段

create table test3(id bit(1));

然后隨意插入一條數(shù)據(jù)

insert into test3 values(1);

發(fā)現(xiàn)無法查詢出對應(yīng)結(jié)果。

然后我們使用 hex() 和 bin() 函數(shù)進(jìn)行查詢

發(fā)現(xiàn)能夠查詢出對應(yīng)結(jié)果。

也就是說當(dāng)數(shù)據(jù)插入 test3 時(shí)，會首先把數(shù)據(jù)轉(zhuǎn)換成為二進(jìn)制數(shù)，如果位數(shù)允許，則將成功插入；如果位數(shù)小于實(shí)際定義的位數(shù)，則插入失敗。如果我們像表中插入數(shù)據(jù) 2

insert into test3 values(2);

那么會報(bào)錯

因?yàn)?2 的二進(jìn)制數(shù)表示是 10，而表中定義的是 bit(1) ，所以無法插入。

那么我們將表字段修改一下

然后再進(jìn)行插入，發(fā)現(xiàn)已經(jīng)能夠插入了

日期時(shí)間類型

MySQL 中的日期與時(shí)間類型，主要包括：「YEAR、TIME、DATE、DATETIME、TIMESTAMP」，每個(gè)版本可能不同。下表中列出了這幾種類型的屬性。

下面分別來介紹一下

YEAR

YEAR 可以使用三種方式來表示

• 用 4 位的數(shù)字或者字符串表示，兩者效果相同，表示范圍 1901 - 2155，插入超出范圍的數(shù)據(jù)會報(bào)錯。
• 以 2 位字符串格式表示，范圍為 ‘00’~‘99’?！?0’~‘69’ 表示 2000~2069，‘70’~‘99’ 表示1970~1999?！?’ 和 ‘00’ 都會被識別為 2000，超出范圍的數(shù)據(jù)也會被識別為 2000。
• 以 2 位數(shù)字格式表示，范圍為 1~99。1~69 表示 2001~2069, 70~99 表示 1970~1999。但 0 值會被識別為0000，這和 2 位字符串被識別為 2000 有所不同

下面我們來演示一下 YEAR 的用法，創(chuàng)建一個(gè) test4 表

create table test4(id year);

然后我們看一下 test4 的表結(jié)構(gòu)

默認(rèn)創(chuàng)建的 year 就是 4 位，下面我們向 test4 中插入數(shù)據(jù)

insert into test4 values(2020),('2020');

然后進(jìn)行查詢，發(fā)現(xiàn)表示形式是一樣的

使用兩位字符串來表示

delete from test4;

insert into test4 values ('0'),('00'),('11'),('88'),('20'),('21');

使用兩位數(shù)字來表示

delete from test4;

insert into test4 values (0),(00),(11),(88),(20),(21);

發(fā)現(xiàn)只有前兩項(xiàng)不一樣。

TIME

TIME 所表示的范圍和我們預(yù)想的不一樣

我們把 test4 改為 TIME 類型，下面是 TIME 的示例

alter table test4 modify id TIME;

insert into test4 values ('15:11:23'),('20:13'),('2 11:11'),('3 05'),('33');

結(jié)果如下

DATE

DATE 表示的類型有很多種，下面是 DATE 的幾個(gè)示例

create table test5 (id date);

查看一下 test5 表

然后插入部分?jǐn)?shù)據(jù)

insert into test5 values ('2020-06-13'),('20200613'),(20200613);

DATE 的表示一般很多種，如下所示 DATE 的所有形式

• 'YYYY-MM-DD'
• 'YYYYMMDD'
• YYYYMMDD
• 'YY-MM-DD'
• 'YYMMDD'
• YYMMDD

DATETIME

DATETIME 類型，包含日期和時(shí)間部分，可以使用引用字符串或者數(shù)字，年份可以是 4 位也可以是 2 位。

下面是 DATETIME 的示例

create table test6 (id datetime);

insert into test4 values ('2020-06-13 11:11:11'),(20200613111111),('20200613111111'),(20200613080808);

TIMESTAMP

TIMESTAMP 類型和 DATETIME 類型的格式相同，存儲 4 個(gè)字節(jié)（比DATETIME少），取值范圍比 DATETIME 小。

下面來說一下各個(gè)時(shí)間類型的使用場景

• 一般表示年月日，通常用 DATE 類型；

• 用來表示時(shí)分秒，通常用 TIME 表示；

• 年月日時(shí)分秒 ，通常用 DATETIME 來表示；

• 如果需要插入的是當(dāng)前時(shí)間，通常使用 TIMESTAMP 來表示，TIMESTAMP 值返回后顯示為 YYYY-MM-DD HH:MM:SS 格式的字符串，

• 如果只表示年份、則應(yīng)該使用 YEAR，它比 DATE 類型需要更小的空間。

每種日期類型都有一個(gè)范圍，如果超出這個(gè)范圍，在默認(rèn)的 SQLMode 下，系統(tǒng)會提示錯誤，并進(jìn)行零值存儲。

下面來解釋一下 SQLMode 是什么

MySQL 中有一個(gè)環(huán)境變量是 sql_mode ，sql_mode 支持了 MySQL 的語法、數(shù)據(jù)校驗(yàn)，我們可以通過下面這種方式來查看當(dāng)前數(shù)據(jù)庫使用的 sql_mode

select @@sql_mode;

一共有下面這幾種模式

來源于 https://www.cnblogs.com/Zender/p/8270833.html

字符串類型

MySQL 提供了很多種字符串類型，下面是字符串類型的匯總

下面我們對這些數(shù)據(jù)類型做一個(gè)詳細(xì)的介紹

CHAR 和 VARCHAR 類型

CHAR 和 VARCHAR 類型很相似，導(dǎo)致很多同學(xué)都會忽略他們之間的差別，首先他倆都是用來保存字符串的數(shù)據(jù)類型，他倆的主要區(qū)別在于存儲方式不同。CHAR 類型的長度就是你定義多少顯示多少。占用 M 字節(jié)，比如你聲明一個(gè) CHAR(20) 的字符串類型，那么每個(gè)字符串占用 20 字節(jié)，M 的取值范圍時(shí) 0 - 255。VARCHAR 是可變長的字符串，范圍是 0 - 65535，在字符串檢索的時(shí)候，CHAR 會去掉尾部的空格，而 VARCHAR 會保留這些空格。下面是演示例子

create table vctest1 (vc varchar(6),ch char(6));

insert into vctest1 values("abc  ","abc  ");

select length(vc),length(ch) from vctest1;

結(jié)果如下

可以看到 vc 的字符串類型是 varchar ，長度是 5，ch 的字符串類型是 char，長度是 3。可以得出結(jié)論，varchar 會保留最后的空格，char 會去掉最后的空格。

BINARY 和 VARBINARY 類型

BINARY 和 VARBINARY 與 CHAR 和 VARCHAR 非常類似，不同的是它們包含二進(jìn)制字符串而不包含非二進(jìn)制字符串。BINARY 與 VARBINARY 的最大長度和 CHAR 與 VARCHAR 是一樣的，只不過他們是定義字節(jié)長度，而 CHAR 和 VARCHAR 對應(yīng)的是字符長度。

BLOB 類型

BLOB 是一個(gè)二進(jìn)制大對象，可以容納可變數(shù)量的數(shù)據(jù)。有 4 種 BLOB 類型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區(qū)別在于可容納存儲范圍不同。

TEXT 類型

有 4 種 TEXT 類型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對應(yīng)的這 4 種 BLOB 類型，可存儲的最大長度不同，可根據(jù)實(shí)際情況選擇。

ENUM 類型

ENUM 我們在 Java 中經(jīng)常會用到，它表示的是枚舉類型。它的范圍需要在創(chuàng)建表時(shí)顯示指定，對 1 - 255 的枚舉需要 1 個(gè)字節(jié)存儲；對于 255 - 65535 的枚舉需要 2 個(gè)字節(jié)存儲。ENUM 會忽略大小寫，在存儲時(shí)都會轉(zhuǎn)換為大寫。

SET 類型

SET 類型和 ENUM 類型有兩處不同

• 存儲方式

SET 對于每 0 - 8 個(gè)成員，分別占用 1 個(gè)字節(jié)，最大到 64 ，占用 8 個(gè)字節(jié)

• Set 和 ENUM 除了存儲之外，最主要的區(qū)別在于 Set 類型一次可以選取多個(gè)成員，而 ENUM 則只能選一個(gè)。

MySQL 運(yùn)算符

MySQL 中有多種運(yùn)算符，下面對 MySQL 運(yùn)算符進(jìn)行分類

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

下面那我們對各個(gè)運(yùn)算符進(jìn)行介紹

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

MySQL 支持的算術(shù)運(yùn)算符包括加、減、乘、除和取余，這類運(yùn)算符的使用頻率比較高

下面是運(yùn)算符的分類

運(yùn)算符	作用
+	加法
-	減法
*	乘法
/, DIV	除法，返回商
%, MOD	除法，返回余數(shù)

下面簡單描述了這些運(yùn)算符的使用方法

• + 用于獲得一個(gè)或多個(gè)值的和
• - 用于從一個(gè)值減去另一個(gè)值
• * 用于兩數(shù)相乘，得到兩個(gè)或多個(gè)值的乘積
• / 用一個(gè)值除以另一個(gè)值得到商
• % 用于一個(gè)值除以另一個(gè)值得到余數(shù)

在除法和取余需要注意一點(diǎn)，如果除數(shù)是 0 ，將是非法除數(shù)，返回結(jié)果為 NULL。

比較運(yùn)算符

熟悉了運(yùn)算符，下面來聊一聊比較運(yùn)算符，使用 SELECT 語句進(jìn)行查詢時(shí)，MySQL 允許用戶對表達(dá)式的兩側(cè)的操作數(shù)進(jìn)行比較，比較結(jié)果為真，返回 1， 比較結(jié)果為假，返回 0 ，比較結(jié)果不確定返回 NULL。下面是所有的比較運(yùn)算符

運(yùn)算符	描述
=	等于
<> 或者是 !=	不等于
<=>	NULL 安全的等于，也就是 NULL-safe
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定范圍內(nèi)
IS NULL	是否為 NULL
IS NOT NULL	是否為 NULL
IN	存在于指定集合
LIKE	通配符匹配
REGEXP 或 RLIKE	正則表達(dá)式匹配

比較運(yùn)算符可以用來比較數(shù)字、字符串或者表達(dá)式。數(shù)字作為浮點(diǎn)數(shù)進(jìn)行比較，字符串以不區(qū)分大小寫的方式進(jìn)行比較。

• = 號運(yùn)算符，用于比較運(yùn)算符兩側(cè)的操作數(shù)是否相等，如果相等則返回 1， 如果不相等則返回 0 ，下面是具體的示例，NULL 不能用于比較，會直接返回 NULL

• <> 號用于表示不等于，和 = 號相反，示例如下

• <=> NULL-safe 的等于運(yùn)算符，與 = 號最大的區(qū)別在于可以比較 NULL 值

• < 號運(yùn)算符，當(dāng)左側(cè)操作數(shù)小于右側(cè)操作數(shù)時(shí)，返回值為 1， 否則其返回值為 0。

• 和上面同理，只不過是滿足 <= 的時(shí)候返回 1 ，否則 > 返回 0。這里我有個(gè)疑問，為什么

select 'a' <= 'b';  /* 返回 1 */

/*而*/

select 'a' >= 'b'; /* 返回 0 呢*/

• 關(guān)于 > 和 >= 是同理

• BETWEEN 運(yùn)算符的使用格式是 「a BETWEEN min AND max」 ，當(dāng) a 大于等于 min 并且小于等于 max 時(shí)，返回 1，否則返回 0 。操作數(shù)類型不同的時(shí)候，會轉(zhuǎn)換成相同的數(shù)據(jù)類型再進(jìn)行處理。比如

• IS NULL 和 IS NOT NULL 表示的是是否為 NULL，ISNULL 為 true 返回 1，否則返回 0 ；IS NOT NULL 同理

• IN 這個(gè)比較操作符判斷某個(gè)值是否在一個(gè)集合中，使用方式是 xxx in (value1,value2,value3)

• LIKE 運(yùn)算符的格式是 xxx LIKE %123%，比如如下

當(dāng) like 后面跟的是 123% 的時(shí)候， xxx 如果是 123 則返回 1，如果是 123xxx 也返回 1，如果是 12 或者 1 就返回 0 。123 是一個(gè)整體。

• REGEX 運(yùn)算符的格式是 s REGEXP str ，匹配時(shí)返回值為 1，否則返回 0 。

后面會詳細(xì)介紹 regexp 的用法。

邏輯運(yùn)算符

邏輯運(yùn)算符指的就是布爾運(yùn)算符，布爾運(yùn)算符指返回真和假。MySQL 支持四種邏輯運(yùn)算符

運(yùn)算符	作用
NOT 或 ！	邏輯非
AND 或者是 &&	邏輯與
OR 或者是 ||	邏輯或
XOR	邏輯異或

下面分別來介紹一下

• NOT 或者是 ! 表示的是邏輯非，當(dāng)操作數(shù)為 0（假） ，則返回值為 1，否則值為 0。但是有一點(diǎn)除外，那就是 NOT NULL 的返回值為 NULL

• AND 和 && 表示的是邏輯與的邏輯，當(dāng)所有操作數(shù)為非零值并且不為 NULL 時(shí)，結(jié)果為 1，但凡是有一個(gè) 0 則返回 0，操作數(shù)中有一個(gè) null 則返回 null

• OR 和 || 表示的是邏輯或，當(dāng)兩個(gè)操作數(shù)均為非 NULL 值時(shí)，如有任意一個(gè)操作數(shù)為非零值，則結(jié)果為 1，否則結(jié)果為 0。

• XOR 表示邏輯異或，當(dāng)任意一個(gè)操作數(shù)為 NULL 時(shí)，返回值為 NULL。對于非 NULL 的操作數(shù)，如果兩個(gè)的邏輯真假值相異，則返回結(jié)果 1；否則返回 0。

位運(yùn)算符

一聽說位運(yùn)算，就知道是和二進(jìn)制有關(guān)的運(yùn)算符了，位運(yùn)算就是將給定的操作數(shù)轉(zhuǎn)換為二進(jìn)制后，對各個(gè)操作數(shù)的每一位都進(jìn)行指定的邏輯運(yùn)算，得到的二進(jìn)制結(jié)果轉(zhuǎn)換為十進(jìn)制后就說是位運(yùn)算的結(jié)果，下面是所有的位運(yùn)算。

運(yùn)算符	作用
&	位與
|	位或
^	位異或
～	位取反
>>	位右移
<<	位左移

下面分別來演示一下這些例子

• 位與 指的就是按位與，把 & 雙方轉(zhuǎn)換為二進(jìn)制再進(jìn)行 & 操作

按位與是一個(gè)數(shù)值減小的操作

• 位或 指的就是按位或，把 | 雙方轉(zhuǎn)換為二進(jìn)制再進(jìn)行 | 操作

位或是一個(gè)數(shù)值增大的操作

• 位異或 指的就是對操作數(shù)的二進(jìn)制位做異或操作

• 位取反 指的就是對操作數(shù)的二進(jìn)制位做 NOT 操作，這里的操作數(shù)只能是一位，下面看一個(gè)經(jīng)典的取反例子：對 1 做位取反，具體如下所示：

為什么會有這種現(xiàn)象，因?yàn)樵?MySQL 中，常量數(shù)字默認(rèn)會以 8 個(gè)字節(jié)來顯示，8 個(gè)字節(jié)就是 64 位，常量 1 的二進(jìn)制表示 63 個(gè) 0，加 1 個(gè) 1 ， 位取反后就是 63 個(gè) 1 加一個(gè) 0 ， 轉(zhuǎn)換為二進(jìn)制后就是 18446744073709551614，我們可以使用 「select bin()」 查看一下

• 位右移 是對左操作數(shù)向右移動指定位數(shù)，例如 50 >> 3，就是對 50 取其二進(jìn)制然后向右移三位，左邊補(bǔ)上 0 ，轉(zhuǎn)換結(jié)果如下

• 位左移 與位右移相反，是對左操作數(shù)向左移動指定位數(shù)，例如 20 << 2

MySQL 常用函數(shù)

下面我們來了解一下 MySQL 函數(shù)，MySQL 函數(shù)也是我們?nèi)粘ｉ_發(fā)過程中經(jīng)常使用的，選用合適的函數(shù)能夠提高我們的開發(fā)效率，下面我們就來一起認(rèn)識一下這些函數(shù)

字符串函數(shù)

字符串函數(shù)是最常用的一種函數(shù)了，MySQL 也是支持很多種字符串函數(shù)，下面是 MySQL 支持的字符串函數(shù)表

函數(shù)	功能
LOWER	將字符串所有字符變?yōu)樾?/span>
UPPER	將字符串所有字符變?yōu)榇髮?/span>
CONCAT	進(jìn)行字符串拼接
LEFT	返回字符串最左邊的字符
RIGHT	返回字符串最右邊的字符
INSERT	字符串替換
LTRIM	去掉字符串左邊的空格
RTRIM	去掉字符串右邊的空格
REPEAT	返回重復(fù)的結(jié)果
TRIM	去掉字符串行尾和行頭的空格
SUBSTRING	返回指定的字符串
LPAD	用字符串對最左邊進(jìn)行填充
RPAD	用字符串對最右邊進(jìn)行填充
STRCMP	比較字符串 s1 和 s2
REPLACE	進(jìn)行字符串替換

下面通過具體的示例演示一下每個(gè)函數(shù)的用法

• LOWER(str) 和 UPPER(str) 函數(shù)：用于轉(zhuǎn)換大小寫

• CONCAT(s1,s2 ... sn) ：把傳入的參數(shù)拼接成一個(gè)字符串

上面把 c xu an 拼接成為了一個(gè)字符串，另外需要注意一點(diǎn)，任何和 NULL 進(jìn)行字符串拼接的結(jié)果都是 NULL。

• LEFT(str,x) 和 RIGHT(str,x) 函數(shù)：分別返回字符串最左邊的 x 個(gè)字符和最右邊的 x 個(gè)字符。如果第二個(gè)參數(shù)是 NULL，那么將不會返回任何字符串

• INSERT(str,x,y,instr) ：將字符串 str 從指定 x 的位置開始， 取 y 個(gè)長度的字串替換為 instr。

• LTRIM(str) 和 RTRIM(str) 分別表示去掉字符串 str 左側(cè)和右側(cè)的空格

• REPEAT(str,x) 函數(shù)：返回 str 重復(fù) x 次的結(jié)果

• TRIM(str) 函數(shù)：用于去掉目標(biāo)字符串的空格

• SUBSTRING(str,x,y) 函數(shù)：返回從字符串 str 中第 x 位置起 y 個(gè)字符長度的字符串

• LPAD(str,n,pad) 和 RPAD(str,n,pad) 函數(shù)：用字符串 pad 對 str 左邊和右邊進(jìn)行填充，直到長度為 n 個(gè)字符長度

• STRCMP(s1,s2) 用于比較字符串 s1 和 s2 的 ASCII 值大小。如果 s1 < s2，則返回 -1；如果 s1 = s2 ，返回 0 ；如果 s1 > s2 ，返回 1。

• REPLACE(str,a,b) : 用字符串 b 替換字符串 str 種所有出現(xiàn)的字符串 a

數(shù)值函數(shù)

MySQL 支持?jǐn)?shù)值函數(shù)，這些函數(shù)能夠處理很多數(shù)值運(yùn)算。下面我們一起來學(xué)習(xí)一下 MySQL 中的數(shù)值函數(shù)，下面是所有的數(shù)值函數(shù)

函數(shù)	功能
ABS	返回絕對值
CEIL	返回大于某個(gè)值的最大整數(shù)值
MOD	返回模
ROUND	四舍五入
FLOOR	返回小于某個(gè)值的最大整數(shù)值
TRUNCATE	返回?cái)?shù)字截?cái)嘈?shù)的結(jié)果
RAND	返回 0 - 1 的隨機(jī)值

下面我們還是以實(shí)踐為主來聊一聊這些用法

• ABS(x) 函數(shù)：返回 x 的絕對值

• CEIL(x) 函數(shù)：返回大于 x 的整數(shù)

• MOD(x,y)，對 x 和 y 進(jìn)行取模操作

• ROUND(x,y) 返回 x 四舍五入后保留 y 位小數(shù)的值；如果是整數(shù)，那么 y 位就是 0 ；如果不指定 y ，那么 y 默認(rèn)也是 0 。

• FLOOR(x) : 返回小于 x 的最大整數(shù)，用法與 CEIL 相反

• TRUNCATE(x,y): 返回?cái)?shù)字 x 截?cái)酁?y 位小數(shù)的結(jié)果， TRUNCATE 知識截?cái)?，并不是四舍五入?/span>

• RAND() ：返回 0 到 1 的隨機(jī)值

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

日期和時(shí)間函數(shù)也是 MySQL 中非常重要的一部分，下面我們就來一起認(rèn)識一下這些函數(shù)

函數(shù)	功能
NOW	返回當(dāng)前的日期和時(shí)間
WEEK	返回一年中的第幾周
YEAR	返回日期的年份
HOUR	返回小時(shí)值
MINUTE	返回分鐘值
MONTHNAME	返回月份名
CURDATE	返回當(dāng)前日期
CURTIME	返回當(dāng)前時(shí)間
UNIX_TIMESTAMP	返回日期 UNIX 時(shí)間戳
DATE_FORMAT	返回按照字符串格式化的日期
FROM_UNIXTIME	返回 UNIX 時(shí)間戳的日期值
DATE_ADD	返回日期時(shí)間 + 上一個(gè)時(shí)間間隔
DATEDIFF	返回起始時(shí)間和結(jié)束時(shí)間之間的天數(shù)

下面結(jié)合示例來講解一下每個(gè)函數(shù)的使用

• NOW(): 返回當(dāng)前的日期和時(shí)間

• WEEK(DATE) 和 YEAR(DATE) ：前者返回的是一年中的第幾周，后者返回的是給定日期的哪一年

• HOUR(time) 和 MINUTE(time) : 返回給定時(shí)間的小時(shí)，后者返回給定時(shí)間的分鐘

• MONTHNAME(date) 函數(shù)：返回 date 的英文月份

• CURDATE() 函數(shù)：返回當(dāng)前日期，只包含年月日

• CURTIME() 函數(shù)：返回當(dāng)前時(shí)間，只包含時(shí)分秒

• UNIX_TIMESTAMP(date) : 返回 UNIX 的時(shí)間戳

• FROM_UNIXTIME(date) : 返回 UNIXTIME 時(shí)間戳的日期值，和 UNIX_TIMESTAMP 相反

• DATE_FORMAT(date,fmt) 函數(shù)：按照字符串 fmt 對 date 進(jìn)行格式化，格式化后按照指定日期格式顯示

我們演示一下將當(dāng)前日期顯示為「年月日」的這種形式，使用的日期格式是 「%M %D %Y」。

• DATE_ADD(date, interval, expr type) 函數(shù)：返回與所給日期 date 相差 interval 時(shí)間段的日期

interval 表示間隔類型的關(guān)鍵字，expr 是表達(dá)式，這個(gè)表達(dá)式對應(yīng)后面的類型，type 是間隔類型，MySQL 提供了 13 種時(shí)間間隔類型

表達(dá)式類型	描述	格式
YEAR	年	YY
MONTH	月	MM
DAY	日	DD
HOUR	小時(shí)	hh
MINUTE	分	mm
SECOND	秒	ss
YEAR_MONTH	年和月	YY-MM
DAY_HOUR	日和小時(shí)	DD hh
DAY_MINUTE	日和分鐘	DD hh : mm
DAY_SECOND	日和秒	DD hh ：mm ：ss
HOUR_MINUTE	小時(shí)和分	hh:mm
HOUR_SECOND	小時(shí)和秒	hh:ss
MINUTE_SECOND	分鐘和秒	mm:ss

• DATE_DIFF(date1, date2) 用來計(jì)算兩個(gè)日期之間相差的天數(shù)

查看離 2021 - 01 - 01 還有多少天

流程函數(shù)

流程函數(shù)也是很常用的一類函數(shù)，用戶可以使用這類函數(shù)在 SQL 中實(shí)現(xiàn)條件選擇。這樣做能夠提高查詢效率。下表列出了這些流程函數(shù)

函數(shù)	功能
IF(value,t f)	如果 value 是真，返回 t；否則返回 f
IFNULL(value1,value2)	如果 value1 不為 NULL，返回 value1，否則返回 value2。
CASE WHEN[value1] THEN[result1] ...ELSE[default] END	如果 value1 是真，返回 result1，否則返回 default
CASE[expr] WHEN[value1] THEN [result1]... ELSE[default] END	如果 expr 等于 value1， 返回 result1， 否則返回 default

其他函數(shù)

除了我們介紹過的字符串函數(shù)、日期和時(shí)間函數(shù)、流程函數(shù)，還有一些函數(shù)并不屬于上面三類函數(shù)，它們是

函數(shù)	功能
VERSION	返回當(dāng)前數(shù)據(jù)庫的版本
DATABASE	返回當(dāng)前數(shù)據(jù)庫名
USER	返回當(dāng)前登陸用戶名
PASSWORD	返回字符串的加密版本
MD5	返回 MD5 值
INET_ATON(IP)	返回 IP 地址的數(shù)字表示
INET_NTOA(num)	返回?cái)?shù)字代表的 IP 地址

下面來看一下具體的使用

• VERSION: 返回當(dāng)前數(shù)據(jù)庫版本

• DATABASE: 返回當(dāng)前的數(shù)據(jù)庫名

• USER : 返回當(dāng)前登錄用戶名

• PASSWORD(str) : 返回字符串的加密版本，例如

• MD5(str) 函數(shù)：返回字符串 str 的 MD5 值

• INET_ATON(IP): 返回 IP 的網(wǎng)絡(luò)字節(jié)序列

• INET_NTOA(num)函數(shù)：返回網(wǎng)絡(luò)字節(jié)序列代表的 IP 地址，與 INET_ATON 相對

長按關(guān)注，還原真實(shí)面試現(xiàn)場！