前言

本文系統(tǒng)梳理MySQL核心知識(shí)體系，從基礎(chǔ)概念到SQL實(shí)操，助你快速建立數(shù)據(jù)庫(kù)思維，夯實(shí)進(jìn)階根基。

第 1 章 MySQL 概述

1.1 基本概念

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

數(shù)據(jù)庫(kù)（DB：Database）：存儲(chǔ)數(shù)據(jù)的地方。

1.1.2 為什么要用數(shù)據(jù)庫(kù)？

應(yīng)用程序產(chǎn)生的數(shù)據(jù)是在內(nèi)存中的，如果程序退出或者是斷電了，則數(shù)據(jù)就會(huì)消失。使用數(shù)據(jù)庫(kù)是為了能夠永久保存數(shù)據(jù)。當(dāng)然這里指的是非內(nèi)存數(shù)據(jù)庫(kù)。

1.1.3 用普通文件存儲(chǔ)行不行？

把數(shù)據(jù)寫(xiě)入到硬盤(pán)上的文件中，當(dāng)然可以實(shí)現(xiàn)持久化的目標(biāo)，但是不利于后期的檢索和管理等。

1.1.4 MySQL、Oracle、SqlServer 是什么？

MySQL、Oracle、SqlServer 都是數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS，Database Management System），是一種操縱和管理數(shù)據(jù)庫(kù)的大型軟件，例如建立、使用和維護(hù)數(shù)據(jù)庫(kù)。

1.1.5 SQL 是什么？

SQL 是結(jié)構(gòu)化查詢語(yǔ)言（Structure Query Language），專門(mén)用來(lái)操作/訪問(wèn)數(shù)據(jù)庫(kù)的通用語(yǔ)言。

1.2 MySQL 數(shù)據(jù)庫(kù)管理系統(tǒng)

在互聯(lián)網(wǎng)行業(yè)，MySQL 數(shù)據(jù)庫(kù)毫無(wú)疑問(wèn)已經(jīng)是最常用的數(shù)據(jù)庫(kù)。MySQL 數(shù)據(jù)庫(kù)由瑞典 MySQL AB 公司開(kāi)發(fā)。公司名中的 “AB” 是瑞典語(yǔ) “aktiebolag” 股份公司的首字母縮寫(xiě)。該公司于 2008 年 1 月 16 號(hào)被 Sun（Stanford University Network）公司收購(gòu)。然而 2009 年，SUN 公司又被 Oracle 收購(gòu)。因此，MySQL 數(shù)據(jù)庫(kù)現(xiàn)在隸屬于 Oracle（甲骨文）公司。MySQL 中的 “My” 是其發(fā)明者（Michael Widenius，通常稱為 Monty）根據(jù)其女兒的名字來(lái)命名的。對(duì)這位發(fā)明者來(lái)說(shuō)，MySQL 數(shù)據(jù)庫(kù)就仿佛是他可愛(ài)的女兒。

1.2.1 關(guān)系型數(shù)據(jù)庫(kù)和非關(guān)系數(shù)據(jù)庫(kù)

• 關(guān)系型數(shù)據(jù)庫(kù)：MySQL、Oracle、SqlServer 等是關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)。關(guān)系型數(shù)據(jù)庫(kù)采用關(guān)系模型來(lái)組織數(shù)據(jù)，簡(jiǎn)單來(lái)說(shuō)，關(guān)系模型指的就是二維表格模型，類似于 Excel 工作表。
• 非關(guān)系型數(shù)據(jù)庫(kù)：MongoDB、Redis、Elasticsearch 等是非關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)。非關(guān)系型數(shù)據(jù)庫(kù)可看成傳統(tǒng)關(guān)系型數(shù)據(jù)庫(kù)的功能閹割版本，基于鍵值對(duì)存儲(chǔ)數(shù)據(jù)，通過(guò)減少不常用的功能來(lái)提高性能。

1.2.2 MySQL 的優(yōu)點(diǎn)

• 可移植性：MySQL 數(shù)據(jù)庫(kù)幾乎支持所有的操作系統(tǒng)，如 Linux、Solaris、FreeBSD、Mac 和 Windows。
• 免費(fèi)：MySQL 的社區(qū)版完全免費(fèi)，一般中小型網(wǎng)站的開(kāi)發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫(kù)。
• 開(kāi)源：2000 年，MySQL 公布了自己的源代碼，并采用 GPL（GNU General Public License）許可協(xié)議，正式進(jìn)入開(kāi)源的世界。開(kāi)源意味著可以讓更多人審閱和貢獻(xiàn)源代碼，可以吸納更多優(yōu)秀人才的代碼成果。
• 關(guān)系型數(shù)據(jù)庫(kù)：MySQL 可以利用標(biāo)準(zhǔn) SQL 語(yǔ)法進(jìn)行查詢和操作。
• 速度快、體積小、容易使用：與其他大型數(shù)據(jù)庫(kù)的設(shè)置和管理相比，其復(fù)雜程度較低，易于學(xué)習(xí)。MySQL 的早期版本（主要使用的是 MyISAM 引擎）在高并發(fā)下顯得有些力不從心，隨著版本的升級(jí)優(yōu)化（主要使用的是 InnoDB 引擎），在實(shí)踐中也證明了高壓力下的可用性。從 2009 年開(kāi)始，阿里的"去 IOE"備受關(guān)注，淘寶 DBA 團(tuán)隊(duì)再次從 Oracle 轉(zhuǎn)向 MySQL，其他使用 MySQL 數(shù)據(jù)庫(kù)的公司還有 Facebook、Twitter、YouTube、百度、騰訊、去哪兒、魅族等等，自此，MySQL 在市場(chǎng)上占據(jù)了很大的份額。
• 安全性和連接性：十分靈活和安全的權(quán)限和密碼系統(tǒng)，允許基于主機(jī)的驗(yàn)證。連接到服務(wù)器時(shí)，所有的密碼傳輸均采用加密形式，從而保證了密碼安全。由于 MySQL 是網(wǎng)絡(luò)化的，因此可以在因特網(wǎng)上的任何地方訪問(wèn)，提高數(shù)據(jù)共享的效率。
• 豐富的接口：提供了用于 C、C++、Java、PHP、Python、Ruby 和 Eiffel、Perl 等語(yǔ)言的 API。
• 靈活：MySQL 并不完美，但是卻足夠靈活，能夠適應(yīng)高要求的環(huán)境。同時(shí)，MySQL 既可以嵌入到應(yīng)用程序中，也可以支持?jǐn)?shù)據(jù)倉(cāng)庫(kù)、內(nèi)容索引和部署軟件、高可用的冗余系統(tǒng)、在線事務(wù)處理系統(tǒng)等各種應(yīng)用類型。
• 存儲(chǔ)引擎架構(gòu)：MySQL 最重要、最與眾不同的特性是它的存儲(chǔ)引擎架構(gòu)，這種架構(gòu)的設(shè)計(jì)將查詢處理（Query Processing）及其他系統(tǒng)任務(wù)（Server Task）和數(shù)據(jù)的存儲(chǔ)/提取相分離。這種處理和存儲(chǔ)分離的設(shè)計(jì)可以在使用時(shí)根據(jù)性能、特性，以及其他需求來(lái)選擇數(shù)據(jù)存儲(chǔ)的方式。MySQL 中同一個(gè)數(shù)據(jù)庫(kù)，不同的表格可以選擇不同的存儲(chǔ)引擎。其中使用最多的是 InnoDB 和 MyISAM，MySQL 5.5 之后 InnoDB 是默認(rèn)的存儲(chǔ)引擎。

1.2.3 MySQL 不同版本

針對(duì)不同用戶，MySQL 提供三個(gè)不同的版本：

1.3 表的關(guān)系

在關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)中，很多表之間是有關(guān)系的，表之間的關(guān)系分為：一對(duì)一關(guān)系、一對(duì)多關(guān)系和多對(duì)多關(guān)系。

1.3.1 一對(duì)一

該關(guān)系中第一個(gè)表中的一個(gè)行只可以與第二個(gè)表中的一個(gè)行相關(guān)，且第二個(gè)表中的一個(gè)行也只可以與第一個(gè)表中的一個(gè)行相關(guān)。

示例：“員工基本信息表"和"員工緊急情況聯(lián)系信息表”。

• "員工基本信息表"中存儲(chǔ)的是頻繁使用的信息
• "員工緊急情況聯(lián)系信息表"中存儲(chǔ)的是不常用的信息
• 這兩個(gè)表中的一條記錄都代表一個(gè)員工的信息
• "員工基本信息表"中的一條記錄在"員工緊急情況聯(lián)系信息表"中只能找到唯一的一條對(duì)應(yīng)記錄，反過(guò)來(lái)也一樣，即它們是一一對(duì)應(yīng)關(guān)系

這兩個(gè)表存在相同意義的"員工編號(hào)"字段，使它們建立了一對(duì)一關(guān)系。

1.3.2 一對(duì)多

第一個(gè)表中的一個(gè)行可以與第二個(gè)表中的一個(gè)或多個(gè)行相關(guān)，但第二個(gè)表中的一個(gè)行只可以與第一個(gè)表中的一個(gè)行相關(guān)。

示例：“部門(mén)表"和"員工基本信息表”。"部門(mén)表"中的一條記錄，在"員工基本信息表"中可以找到一條或多條記錄對(duì)應(yīng)，但反過(guò)來(lái)"員工基本信息表"中的一條記錄在"部門(mén)表"中只能找到一條記錄對(duì)應(yīng)，即一個(gè)部門(mén)可以有多個(gè)員工，但是一個(gè)員工只能屬于一個(gè)部門(mén)。這兩個(gè)表存在相同意義的"部門(mén)編號(hào)"字段，使它們建立了一對(duì)多關(guān)系。

1.3.3 多對(duì)多

該關(guān)系中第一個(gè)表中的一個(gè)行可以與第二個(gè)表中的一個(gè)或多個(gè)行相關(guān)。第二個(gè)表中的一個(gè)行也可以與第一個(gè)表中的一個(gè)或多個(gè)行相關(guān)。通常兩個(gè)表的多對(duì)多關(guān)系會(huì)借助第三張表，轉(zhuǎn)換為兩個(gè)一對(duì)多的關(guān)系。

示例：項(xiàng)目系統(tǒng)的"員工基本信息表"和"項(xiàng)目信息表"是多對(duì)多關(guān)系。一個(gè)員工可以參與多個(gè)項(xiàng)目，一個(gè)項(xiàng)目也可以有多個(gè)員工參與，即"員工基本信息表"中一條記錄可以與"項(xiàng)目信息表"多條記錄對(duì)應(yīng)，反過(guò)來(lái)"項(xiàng)目信息表"的一條記錄也可以與"員工基本信息表"中多條記錄對(duì)應(yīng)。它們之間借助第三張"員工項(xiàng)目表"實(shí)現(xiàn)關(guān)聯(lián)關(guān)系，而"員工基本信息表"與"員工項(xiàng)目表"是一對(duì)多關(guān)系，"項(xiàng)目信息表"與"員工項(xiàng)目表"也是一對(duì)多關(guān)系。"員工項(xiàng)目表"中"員工編號(hào)"字段與"員工基本信息表"中"員工編號(hào)"字段意義相同。"員工項(xiàng)目表"中"項(xiàng)目編號(hào)"字段與"項(xiàng)目信息表"中"項(xiàng)目編號(hào)"字段意義相同。

第 2 章 SQL 語(yǔ)句簡(jiǎn)介

2.1 SQL 語(yǔ)句的分類

SQL 語(yǔ)句分為三種類型：

• DDL：數(shù)據(jù)定義語(yǔ)句（Data Define Language）如創(chuàng)建（create），修改（alter），刪除（drop）等。
• DML：數(shù)據(jù)操作語(yǔ)句，如增（insert），刪（delete），改（update），查（select）。因?yàn)椴樵冋Z(yǔ)句使用的非常頻繁，因此很多人把查詢語(yǔ)句單獨(dú)劃分為 DQL（數(shù)據(jù)查詢語(yǔ)句）。
• DCL：數(shù)據(jù)控制語(yǔ)句，如 grant，commit，rollback 等。
• 其他語(yǔ)句：use 語(yǔ)句，show 語(yǔ)句，set 語(yǔ)句等。這類的官方文檔中一般稱為命令。

2.2 SQL 語(yǔ)法規(guī)范

• MySQL 的 SQL 語(yǔ)法不區(qū)分大小寫(xiě)。
• 命名時(shí)盡量使用 26 個(gè)英文字母大小寫(xiě)，數(shù)字 0-9，下劃線，不要使用其他符號(hào)。
• 建議不要使用 MySQL 的關(guān)鍵字等來(lái)作為表名、字段名、數(shù)據(jù)庫(kù)名等，如果不小心使用，請(qǐng)?jiān)?SQL 語(yǔ)句中使用 `（飄號(hào)）引起來(lái)。
• 數(shù)據(jù)庫(kù)和表名、字段名等對(duì)象名中間不要包含空格。
• 同一個(gè) MySQL 軟件中，數(shù)據(jù)庫(kù)不能同名，同一個(gè)庫(kù)中，表不能重名，同一個(gè)表中，字段不能重名。

2.3 SQL 腳本中的注釋

單行注釋：#（MySQL 特有的）或 -- 注釋內(nèi)容

show databases; #顯示庫(kù)
show databases; -- 顯示庫(kù)

多行注釋：/* 注釋內(nèi)容 */

/*
顯示庫(kù)
*/
show databases;

第 3 章 DDL

3.1 庫(kù)相關(guān)

3.1.1 創(chuàng)建庫(kù)

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

3.1.2 查看庫(kù)

1）查看所有庫(kù)

show databases;

2）查看某個(gè)數(shù)據(jù)庫(kù)的詳細(xì)定義語(yǔ)句

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

3.1.3 修改庫(kù)編碼

修改數(shù)據(jù)庫(kù)編碼只會(huì)影響之后新創(chuàng)建的表的默認(rèn)編碼，之前創(chuàng)建的表不會(huì)受影響。

alter database 數(shù)據(jù)庫(kù)名 character set 字符集名稱 collate 字符集對(duì)應(yīng)校對(duì)規(guī)則;

例如：

alter database db01 character set utf8 collate utf8_general_ci;

3.1.4 使用庫(kù)

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

3.1.5 刪除庫(kù)

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

3.1.6 示例

-- 創(chuàng)建數(shù)據(jù)庫(kù)
create database if not exists jdfs;
-- 查看所有數(shù)據(jù)庫(kù)
show databases;
-- 查看數(shù)據(jù)庫(kù)的創(chuàng)建語(yǔ)句
show create database jdfs;
-- 切換數(shù)據(jù)庫(kù)
use jdfs;
-- 修改數(shù)據(jù)庫(kù)的字符集為L(zhǎng)atin1，Latin1字符集不支持中文
alter database jdfs character set Latin1 collate Latin1_general_ci;
-- 刪除數(shù)據(jù)庫(kù)
drop database if exists jdfs;

3.2 表相關(guān)

3.2.1 創(chuàng)建表

create table [if not exists] 表名(
    字段名 數(shù)據(jù)類型,
    字段名 數(shù)據(jù)類型
);

例如：

create table teacher(
    tid int,
    tname varchar(5),
    salary double,
    weight double(5,2),
    birthday date,
    gender enum('男','女'),
    blood enum('A','B','AB','O'),
    tel char(11)
);

3.2.2 查看表

1）查看某個(gè)數(shù)據(jù)庫(kù)的所有表

use 數(shù)據(jù)庫(kù)名;
show tables;

或

show tables from 數(shù)據(jù)庫(kù)名;

2）查看表的詳細(xì)定義

show create table 表名;

3）查看表結(jié)構(gòu)

desc 表名;

3.2.3 修改表編碼

alter table teacher character set 字符集 collate 校對(duì)規(guī)則;

例如：

alter table teacher character set utf8mb4 collate utf8mb4_general_ci;

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

1）添加字段

alter table 表名 add 字段名 數(shù)據(jù)類型;           -- 在表末尾添加字段
alter table 表名 add 字段名 數(shù)據(jù)類型 first;       -- 在表開(kāi)頭添加字段
alter table 表名 add 字段名 數(shù)據(jù)類型 after 另一個(gè)字段; -- 在另一個(gè)字段后添加字段

2）刪除字段

alter table 表名 drop 字段名;

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

alter table 表名 modify 字段名 新的數(shù)據(jù)類型;

4）修改字段名

alter table 表名 change 舊字段名 新字段名 新的數(shù)據(jù)類型;

5）修改字段位置

alter table 表名 modify 字段名 數(shù)據(jù)類型 first;       -- 將字段移動(dòng)到表的開(kāi)頭
alter table 表名 modify 字段名 數(shù)據(jù)類型 after 另一個(gè)字段; -- 將字段移動(dòng)到表的另一個(gè)字段之后

3.2.5 修改表名

alter table 舊表名 rename 新表名;

或

rename table 舊表名 to 新表名;

3.2.6 刪除表

drop table [if exists] 表名;

3.2.7 示例

create database if not exists jdfs;
use jdfs;
-- 創(chuàng)建表
create table if not exists teacher(
    id int,
    name varchar(20),
    gender enum('男','女'),
    birthday date,
    salary double,
    tel varchar(11)
);
-- 查看數(shù)據(jù)庫(kù)中所有表
show tables;
-- 查看表結(jié)構(gòu)
desc teacher;
-- 查看建表語(yǔ)句
show create table teacher;
-- 添加字段到表末尾
alter table teacher add address varchar(200);
-- 添加字段到表開(kāi)頭
alter table teacher add age int first;
-- 添加字段到指定位置
alter table teacher add heart_rate float after age;
-- 刪除字段
alter table teacher drop address;
-- 修改字段數(shù)據(jù)類型
alter table teacher modify heart_rate double;
-- 修改字段名
alter table teacher change heart_rate bpm double;
-- 移動(dòng)字段位置到表開(kāi)頭
alter table teacher modify bpm double first;
-- 移動(dòng)字段位置到另一個(gè)字段之后
alter table teacher modify bpm double after age;
-- 修改表名
alter table teacher rename teacher_info;
-- 刪除表
drop table teacher_info;

第 4 章 DML

4.1 添加

4.1.1 只寫(xiě)表名

值列表需與表結(jié)構(gòu)對(duì)應(yīng)。

insert into 表名 values(值列表),(值列表),(值列表);
-- 值列表中的值的順序、類型、個(gè)數(shù)必須與表結(jié)構(gòu)一一對(duì)應(yīng)

4.1.2 寫(xiě)表名和字段列表

值列表需與字段列表對(duì)應(yīng)。

insert into 表名(字段列表) values(值列表),(值列表),(值列表);
-- 值列表中的值的順序、類型、個(gè)數(shù)必須與字段列表一一對(duì)應(yīng)

4.1.3 示例

create database if not exists jdfs;
use jdfs;
drop table if exists teacher;
create table teacher(
    id int,
    name varchar(20),
    gender enum('男','女'),
    birthday date,
    salary double,
    tel varchar(11)
);
-- 插入一條數(shù)據(jù)
insert into teacher values(1,'張三','男','1990-01-01',10000,'12345678901');
insert into teacher(id,name,gender) values(2,'李四','男');
select * from teacher;
-- 插入多條數(shù)據(jù)
insert into teacher values
(3,'王五','男','1990-01-01',10000,'12345678901'),
(4,'趙六','男','1990-01-01',10000,'12345678901'),
(5,'孫七','男','1990-01-01',10000,'12345678901');
insert into teacher(id,name) values(6,'周八'),(7,'吳九'),(8,'鄭十');
select * from teacher;

4.2 刪除

4.2.1 刪除多條數(shù)據(jù)

delete from 表名 where 條件;

4.2.2 刪除表中所有數(shù)據(jù)

表結(jié)構(gòu)會(huì)保留下來(lái)。

delete from 表名;

4.2.3 截?cái)啾?/h4>

表結(jié)構(gòu)會(huì)保留下來(lái)。

truncate 表名;

truncate 表和 delete 表的區(qū)別：

• delete 是一條一條刪除記錄的。如果在事務(wù)中，事務(wù)提交之前支持回滾。
• truncate 是把整個(gè)表 drop，新建一張，效率更高。就算在事務(wù)中，也無(wú)法回滾。

4.2.4 示例

-- 刪除姓名為鄭十的記錄
delete from teacher where name="鄭十";
select * from teacher;
-- 刪除所有記錄
delete from teacher;
-- 截?cái)啾?
truncate teacher;

4.3 修改

4.3.1 修改部分行數(shù)據(jù)

update 表名 set 字段名 = 值, 字段名 = 值 where 條件;
-- 修改滿足條件的行

4.3.2 修改所有行數(shù)據(jù)

update 表名 set 字段名 = 值, 字段名 = 值;
-- 修改所有行

4.3.3 示例

-- 修改周八的信息
update teacher set gender="男",birthday="1960-01-01",salary=100000,tel="12332132131" where name="周八";
-- 修改所有人的薪資
update teacher set salary=salary+10000;

4.4 查詢

4.4.1 select 語(yǔ)句

select 語(yǔ)句是用于查看計(jì)算結(jié)果、或者查看從數(shù)據(jù)表中篩選出的數(shù)據(jù)的。

select 語(yǔ)句的基本語(yǔ)法：

select 常量;
select 表達(dá)式;
select 函數(shù);

例如：

select 1;
select 9/2;
select now();

如果要從數(shù)據(jù)表中篩選數(shù)據(jù)，需要加 from 子句。from 指定數(shù)據(jù)來(lái)源。字段列表篩選列。

select 字段列表 from 表名;

如果要從數(shù)據(jù)表中根據(jù)條件篩選數(shù)據(jù)，需要加 from 和 where 子句。where 篩選行。

select 字段列表 from 表名 where 條件;

完整的 select 語(yǔ)句后面可以跟 7 個(gè)子句，后面會(huì)逐一講解。

4.4.2 使用別名

在當(dāng)前 select 語(yǔ)句中給某個(gè)字段或表達(dá)式計(jì)算結(jié)果，或表等取個(gè)臨時(shí)名稱，便于當(dāng)前 select 語(yǔ)句的編寫(xiě)和理解。這個(gè)臨時(shí)名稱稱為別名。

select 字段名1 as "別名1", 字段名2 as "別名2" from 表名 as 表別名;

• 列的別名有空格時(shí)需要加雙引號(hào)。列的別名中沒(méi)有空格時(shí)雙引號(hào)可以不加。
• 表的別名不能加雙引號(hào)，表的別名中間不能包含空格。
• as 大小寫(xiě)都可以，as 也完全可以省略。

例如：

select id as 編號(hào), name "姓名" from teacher;

4.4.3 結(jié)果去重

MySQL 可以在查詢結(jié)果中使用 distinct 關(guān)鍵字去重。

select distinct 字段列表 from 表名;

第 5 章 運(yùn)算符

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

示例：

select 2+3;
select 9/2, 9 div 2;
select 10.1/2.1, 10.1 div 2.1;
select 9%2, 9 mod 2;
select "hello"+"world";
-- NULL 與其他數(shù)據(jù)類型的運(yùn)算結(jié)果為 NULL
select 2+null;
select "hello"+null;
select date("2020-01-01")+null;

5.2 比較運(yùn)算符

示例：

use jdfs;
-- 查詢薪資高于 15000 的員工姓名和薪資
select ename, salary from t_employee where salary>15000;
-- 查詢薪資為 9000 的員工的姓名和薪資
select ename, salary from t_employee where salary=9000;
-- 查詢地址不在北苑的
select * from t_employee where address!="北苑";
select * from t_employee where address<>"北苑";

5.3 區(qū)間或集合范圍比較運(yùn)算符

示例：

use jdfs;
-- 查詢薪資在 [10000,15000]
select * from t_employee where salary>=10000 && salary<=15000;
select * from t_employee where salary between 10000 and 15000;
-- 查詢薪資不在 [10000,15000]
select * from t_employee where salary not between 10000 and 15000;
-- 查詢地址在這幾個(gè)地方的
select * from t_employee where address in ('北苑', '望京', '龍澤');
-- 查詢地址不在這幾個(gè)地方的
select * from t_employee where address not in ('北苑', '望京', '龍澤');

5.4 模糊匹配比較運(yùn)算符

示例：

use jdfs;
-- 查詢名字有 冰 字的
select * from t_employee where ename like '%冰%';
-- 查詢名字以 雨 結(jié)尾的
select * from t_employee where ename like '%雨';
-- 查詢名字以 李 開(kāi)頭的
select * from t_employee where ename like '李%';
-- 查詢名字有 紅 這個(gè)字，但是 紅 的前面只能有 1 個(gè)字
select * from t_employee where ename like '_紅%';
-- 查詢當(dāng)前 MySQL 數(shù)據(jù)庫(kù)的字符集情況
show variables like '%character%';

5.5 邏輯運(yùn)算符

示例：

use jdfs;
-- 查詢薪資高于 15000，并且性別是男的員工
select * from t_employee where salary>15000 and gender='男';
-- 查詢薪資高于 15000，或者 did 為 1 的員工
select * from t_employee where salary>15000 || did = 1;
-- 查詢薪資不在 [15000,20000] 范圍的
select * from t_employee where !(salary between 15000 and 20000);
-- 查詢薪資高于 15000，或者 did 為 1 的員工，兩者只能滿足其一
select * from t_employee where salary>15000 xor did = 1;
select * from t_employee where (salary>15000) ^ (did = 1);

5.6 關(guān)于 NULL 值的問(wèn)題

示例：

use jdfs;
-- 查詢獎(jiǎng)金比例為 null 的員工
select * from t_employee where commission_pct is null;
select * from t_employee where commission_pct <=> null;
-- 查詢獎(jiǎng)金比例不為 null 的員工
select * from t_employee where commission_pct is not null;
-- 查詢員工的實(shí)發(fā)工資，實(shí)發(fā)工資 = 薪資 + 薪資 * 獎(jiǎng)金比例
select ename, salary, commission_pct, salary + salary * ifnull(commission_pct,0) "實(shí)發(fā)工資" from t_employee;

5.7 位運(yùn)算符

第 6 章 MySQL 數(shù)據(jù)類型

6.1 數(shù)值類型

6.1.1 整數(shù)類型

對(duì)于整數(shù)類型，MySQL 還支持在類型名稱后面加小括號(hào)(M)，而小括號(hào)中的 M 表示顯示寬度，M 的取值范圍是(0, 255)。int(M) 這個(gè) M 在字段的屬性中指定了 unsigned（無(wú)符號(hào)）和 zerofill（零填充）的情況下才有意義。表示當(dāng)整數(shù)值不夠 M 位時(shí)，用 0 填充。如果整數(shù)值超過(guò) M 位但是沒(méi)有超過(guò)當(dāng)前數(shù)據(jù)類型的范圍時(shí)，就按照實(shí)際位數(shù)存儲(chǔ)。當(dāng) M 寬度超過(guò)當(dāng)前數(shù)據(jù)類型可存儲(chǔ)數(shù)值范圍的最大寬度時(shí)，也是以實(shí)際存儲(chǔ)范圍為準(zhǔn)。

MySQL 8 之前，int 沒(méi)有指定(M)，默認(rèn)顯示(11)。最多能存儲(chǔ)和顯示 11 位整數(shù)。從 MySQL 8.0.17 開(kāi)始，整數(shù)數(shù)據(jù)類型不推薦使用顯示寬度屬性，默認(rèn)顯示 int。

-- 創(chuàng)建表
create table t_int(
    i1 int,
    i2 int(2) -- 沒(méi)有 unsigned zerofill，(2)沒(méi)有意義
);
-- 查看表結(jié)構(gòu)
desc t_int;
-- 創(chuàng)建表
create table t_int2(
    i1 int,
    i2 int(2) unsigned zerofill
);
-- 查看表結(jié)構(gòu)
desc t_int2;
-- 添加數(shù)據(jù)
insert into t_int2 values(1234,1234);
-- 查詢數(shù)據(jù)
select * from t_int2;
-- 添加數(shù)據(jù)
insert into t_int2 values(1,1);
-- 查詢數(shù)據(jù)
select * from t_int2;

6.1.2 浮點(diǎn)數(shù)類型

MySQL 中使用浮點(diǎn)數(shù)和定點(diǎn)數(shù)來(lái)表示小數(shù)。浮點(diǎn)數(shù)有兩種類型：?jiǎn)尉雀↑c(diǎn)數(shù)（FLOAT）和雙精度浮點(diǎn)數(shù)（DOUBLE），定點(diǎn)數(shù)只有 DECIMAL。

浮點(diǎn)數(shù)和定點(diǎn)數(shù)都可以用 (M, D) 來(lái)表示：

• M 是精度，表示該值總共顯示 M 位，包括整數(shù)位和小數(shù)位，對(duì)于 FLOAT 和 DOUBLE 類型來(lái)說(shuō)，M 取值范圍為 0~255，而對(duì)于 DECIMAL 來(lái)說(shuō)，M 取值范圍為 0~65。
• D 是標(biāo)度，表示小數(shù)的位數(shù)，取值范圍為 0~30，同時(shí)必須 <=M。

FLOAT 和 DOUBLE 類型在不指定(M,D)時(shí)，默認(rèn)會(huì)按照實(shí)際的精度來(lái)顯示。DECIMAL 類型在不指定(M, D)時(shí)，默認(rèn)為 (10, 0)，即只保留整數(shù)部分。例如，定義 DECIMAL(5,2) 的類型，表示該列取值范圍是 -999.99~999.99。如果用戶插入數(shù)據(jù)的小數(shù)部分位數(shù)超過(guò) D 位，MySQL 會(huì)四舍五入處理，但是如果用戶插入數(shù)據(jù)的整數(shù)部分位數(shù)超過(guò) M-D 位，則會(huì)報(bào) Out of range 的錯(cuò)誤。

DECIMAL 實(shí)際是以字符串形式存放的，在對(duì)精度要求比較高的時(shí)候（如貨幣、科學(xué)數(shù)據(jù)等）使用 DECIMAL 類型會(huì)比較好。浮點(diǎn)數(shù)相對(duì)于定點(diǎn)數(shù)的優(yōu)點(diǎn)是在長(zhǎng)度一定的情況下，浮點(diǎn)數(shù)能夠表示更大的數(shù)據(jù)范圍，它的缺點(diǎn)是會(huì)引起精度問(wèn)題。

-- 創(chuàng)建表
create table t_double(
    d1 double,
    d2 double(5,2) -- -999.99~999.99
);
-- 查看表結(jié)構(gòu)
desc t_double;
-- 添加數(shù)據(jù)
insert into t_double values(2.5,2.5);
-- 查詢數(shù)據(jù)
select * from t_double;
-- 添加數(shù)據(jù)
insert into t_double values(2.5526,2.5526);
insert into t_double values(2.5586,2.5586);
-- 查詢數(shù)據(jù)
select * from t_double;
-- 添加數(shù)據(jù)
insert into t_double values(12852.5526,12852.5526);
-- d2字段整數(shù)部分超過(guò)(5-2=3)位，添加失敗
-- 創(chuàng)建表
create table t_decimal(
    d1 decimal, -- 沒(méi)有指定(M,D)默認(rèn)是(10,0)
    d2 decimal(5,2)
);
-- 查看表結(jié)構(gòu)
desc t_decimal;
-- 添加數(shù)據(jù)
insert into t_decimal values(2.5,2.5);
-- 查詢數(shù)據(jù)
select * from t_decimal;
-- 把小數(shù)賦值給整數(shù)類型的字段時(shí)，會(huì)截?cái)嘈?shù)部分，考慮四舍五入
insert into t_int2 values(1.5,1.5);
select * from t_int2;

6.1.3 BIT 類型

BIT 是一種存儲(chǔ)位數(shù)據(jù)的類型，每個(gè)字段的長(zhǎng)度可以指定為 1 到 64 位。數(shù)據(jù)以位流的形式存儲(chǔ)，并以字節(jié)（byte）對(duì)齊。即使只存儲(chǔ) 1 位，最小存儲(chǔ)單元也是 1 字節(jié)。

可以使用十六進(jìn)制、二進(jìn)制或整數(shù)形式插入數(shù)據(jù)。插入值的位數(shù)不能超過(guò)定義的位數(shù)，否則會(huì)報(bào)錯(cuò)。

查詢 BIT 字段時(shí)，MySQL 會(huì)以二進(jìn)制格式顯示數(shù)據(jù)。如果需要以整數(shù)形式顯示，可以使用 BIN() 或 CONV()。

可以對(duì) BIT 數(shù)據(jù)進(jìn)行邏輯運(yùn)算，例如 AND，OR，XOR 等。

-- 創(chuàng)建表
create table t_bit(
    b1 bit, -- 沒(méi)有指定(M)，默認(rèn)是 1 位二進(jìn)制
    b2 bit(4) -- 能夠存儲(chǔ) 4 位二進(jìn)制 0000~1111
);
-- 查看表結(jié)構(gòu)
desc t_bit;
-- 添加數(shù)據(jù)
insert into t_bit values(1,1);
-- 查詢數(shù)據(jù)
select * from t_bit;
-- 顯示二進(jìn)制值，需要使用 bin 函數(shù)
select bin(b1),bin(b2) from t_bit;
-- 添加數(shù)據(jù)
insert into t_bit values(2,2);
-- values()中是十進(jìn)制值，需要轉(zhuǎn)為二進(jìn)制存儲(chǔ)，2 對(duì)應(yīng) 10，超過(guò) 1 位，b1 存不下
-- 添加數(shù)據(jù)
insert into t_bit values(1,8);
-- 查看數(shù)據(jù)
select * from t_bit;
-- 顯示二進(jìn)制值，需要使用 bin 函數(shù)
select conv(b1,2,10),conv(b2,2,10) from t_bit;
-- 添加數(shù)據(jù)
insert into t_bit values(1,16); -- 16 的二進(jìn)制 10000

6.2 字符串類型

6.2.1 定長(zhǎng)與變長(zhǎng)字符串

CHAR(M) 為固定長(zhǎng)度的字符串，M 表示最多能存儲(chǔ)的字符數(shù)，取值范圍是 0~255 個(gè)字符，如果未指定(M)表示只能存儲(chǔ) 1 個(gè)字符。例如 CHAR(4) 定義了一個(gè)固定長(zhǎng)度的字符串列，其包含的字符個(gè)數(shù)最大為 4，如果存儲(chǔ)的值少于 4 個(gè)字符，右側(cè)將用空格填充以達(dá)到指定的長(zhǎng)度，當(dāng)查詢顯示 CHAR 值時(shí)，尾部的空格將被刪掉。

VARCHAR(M) 為可變長(zhǎng)度的字符串，M 表示最多能存儲(chǔ)的字符數(shù)，M 的范圍由最長(zhǎng)的行的大?。ㄍǔＪ?65535）和使用的字符集確定。例如 utf8mb4 字符編碼單個(gè)字符所需最長(zhǎng)字節(jié)值為 4 個(gè)字節(jié)，所以 M 的范圍是 [0,16383]。而 VARCHAR 類型的字段實(shí)際占用的空間為字符串的實(shí)際長(zhǎng)度加 1 或 2 個(gè)字節(jié)，這 1 或 2 個(gè)字節(jié)用于描述字符串值的實(shí)際字節(jié)數(shù)，即字符串值在 [0,255] 個(gè)字節(jié)范圍內(nèi)，那么額外增加 1 個(gè)字節(jié)，否則需要額外增加 2 個(gè)字節(jié)。在 VARCHAR 后面的 M 必須指定。

例如，身份證號(hào)、手機(jī)號(hào)碼、QQ 號(hào)、用戶名 username、密碼 password、銀行卡號(hào)等固定長(zhǎng)度的文本字符串適合使用 CHAR 類型，而評(píng)論、朋友圈、微博不定長(zhǎng)度的文本字符串更適合使用 VARCHAR 類型。

另外，存儲(chǔ)引擎對(duì)于選擇 CHAR 和 VARCHAR 是有影響的：

• 對(duì)于 MyISAM 存儲(chǔ)引擎，最好使用固定長(zhǎng)度的數(shù)據(jù)列代替可變長(zhǎng)度的數(shù)據(jù)列。這樣可以使整個(gè)表靜態(tài)化，從而使數(shù)據(jù)檢索更快，用空間換時(shí)間。
• 對(duì)于 InnoDB 存儲(chǔ)引擎，使用可變長(zhǎng)度的數(shù)據(jù)列，因?yàn)?InnoDB 數(shù)據(jù)表的存儲(chǔ)格式不分固定長(zhǎng)度和可變長(zhǎng)度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照實(shí)際的長(zhǎng)度存儲(chǔ)的，比較節(jié)省空間，所以對(duì)磁盤(pán) I/O 和數(shù)據(jù)存儲(chǔ)總量比較好。

drop table if exists t_char;
create table t_char (
    c1 char,
    c2 char(3)
);
insert into t_char values('男','女'); -- 成功
insert into t_char values('節(jié)點(diǎn)飛思','節(jié)點(diǎn)飛思'); -- 失敗
insert into t_char values('男','節(jié)點(diǎn)飛思'); -- 成功
select * from t_char;

drop table if exists t_char;
create table t_char (
    c1 varchar -- 錯(cuò)誤
);
create table t_char (
    c1 varchar(3) -- 最多不超過(guò) 3 個(gè)字符
);

insert into t_char values('節(jié)點(diǎn)飛思');
insert into t_char values('節(jié)點(diǎn)飛思真好'); -- 失敗
drop table if exists t_char;
create table t_char (
    name varchar(65535)
);
-- 錯(cuò)誤，字符串過(guò)長(zhǎng)

6.2.2 枚舉與集合

有時(shí)候我們指定在固定的幾個(gè)值范圍內(nèi)選擇一個(gè)或多個(gè)，那么就需要使用 ENUM 枚舉類型和 SET 集合類型了。比如性別只有"男"或"女"；上下班交通方式可以有"地鐵"、“公交”、“出租車”、“自行車”、"步行"等。

枚舉和集合類型字段聲明的語(yǔ)法格式如下：

• 字段名 ENUM(值1,值2,值3)
• 字段名 SET(值1,值2,值3)

ENUM 類型的字段在賦值時(shí)，只能在指定的枚舉列表中取值，而且一次只能取一個(gè)。枚舉列表最多可以有 65535 個(gè)成員。ENUM 值在內(nèi)部用整數(shù)表示，每個(gè)枚舉值均有一個(gè)索引值，MySQL 存儲(chǔ)的就是這個(gè)索引編號(hào)。

SET 類型的字段在賦值時(shí)，可從定義的值列表中選擇 1 個(gè)或多個(gè)值的組合。SET 列最多可以有 64 個(gè)成員。SET 值在內(nèi)部也用整數(shù)表示，分別是 1, 2, 4, 8……，都是 2 的 n 次方值。因?yàn)檫@些整數(shù)值對(duì)應(yīng)的二進(jìn)制都是只有 1 位是 1，其余是 0。

drop table if exists t_enum;
create table t_enum (
    gender enum('男','女'),
    hobby set('睡覺(jué)','打游戲','運(yùn)動(dòng)','寫(xiě)代碼')
);
desc t_enum;
insert into t_enum values('男','睡覺(jué),打游戲'); -- 成功
insert into t_enum values('男,女','睡覺(jué),打游戲'); -- 失敗
insert into t_enum values('妖','睡覺(jué),打游戲'); -- 失敗
insert into t_enum values('男','睡覺(jué),打游戲,吃飯'); -- 失敗
select * from t_enum;
insert into t_enum values(2, 2);
select * from t_enum;
insert into t_enum values(1, 5);
-- 5(0101)是 1(0001)、4(0100)的組合
select * from t_enum;
insert into t_enum values(1, 7);
-- 7(0111)是 1(0001)、2(0010)、4(0100)的組合
select * from t_enum;
insert into t_enum values(2, 15);
select * from t_enum;

6.2.3 文本類型

6.2.4 二進(jìn)制類型

BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR，只是它們存儲(chǔ)的是二進(jìn)制字符串。

BINARY(M) 為固定長(zhǎng)度的二進(jìn)制字符串，M 表示最多能存儲(chǔ)的字節(jié)數(shù)，取值范圍是 0~255 個(gè)字節(jié)，如果未指定(M)表示只能存儲(chǔ) 1 個(gè)字節(jié)。例如 BINARY(8)，表示最多能存儲(chǔ) 8 個(gè)字節(jié)，如果字段值不足(M)個(gè)字節(jié)，將在右邊填充’\0’以補(bǔ)齊指定長(zhǎng)度。

VARBINARY(M) 為可變長(zhǎng)度的二進(jìn)制字符串，M 表示最多能存儲(chǔ)的字節(jié)數(shù)，總字節(jié)數(shù)不能超過(guò)行的字節(jié)長(zhǎng)度限制 65535，另外還要考慮額外字節(jié)開(kāi)銷，VARBINARY 類型的數(shù)據(jù)除了存儲(chǔ)數(shù)據(jù)本身外，還需要 1 或 2 個(gè)字節(jié)來(lái)存儲(chǔ)數(shù)據(jù)的字節(jié)數(shù)。VARBINARY 類型和 VARCHAR 類型一樣必須指定(M)，否則報(bào)錯(cuò)。

6.3 日期時(shí)間類型

如果僅僅是表示年份信息，可以只使用 YEAR 類型，這樣更節(jié)省空間，格式為"YYYY"，例如"2022"。YEAR 允許的值范圍是 1901~2155。YEAR 還有格式為"YY" 2 位數(shù)字的形式，值是 00~69，表示 2000~2069 年，值是 70~99，表示 1970~1999 年，從 MySQL 5.5.27 開(kāi)始，2 位格式的 YEAR 已經(jīng)不推薦使用。YEAR 默認(rèn)格式就是"YYYY"，沒(méi)必要寫(xiě)成 YEAR(4)，從 MySQL 8.0.19 開(kāi)始，不推薦使用指定顯示寬度的 YEAR(4) 數(shù)據(jù)類型。這個(gè) 0 年，如果是以整數(shù)的 0 添加的話，那么是 0000 年，如果是以日期/字符串的’0’添加的話，是 2000 年。

如果要表示年月日，可以使用 DATE 類型，格式為"YYYY-MM-DD"，例如"2022-02-04"。

如果要表示時(shí)分秒，可以使用 TIME 類型，格式為"HH:MM:SS"，例如"10:08:08"。

如果要表示年月日時(shí)分秒的完整日期時(shí)間，可以使用 DATETIME 類型，格式為"YYYY-MM-DD HH:MM:SS"，例如"2022-02-04 10:08:08"。

如果需要經(jīng)常插入或更新日期時(shí)間為系統(tǒng)日期時(shí)間，則通常使用 TIMESTAMP 類型，格式為"YYYY-MM-DD HH:MM:SS"，例如"2022-02-04 10:08:08"。TIMESTAMP 與 DATETIME 的區(qū)別在于 TIMESTAMP 的取值范圍小，只支持 1970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC 范圍的日期時(shí)間值，其中 UTC 是世界標(biāo)準(zhǔn)時(shí)間，并且 TIMESTAMP 類型的日期時(shí)間值在存儲(chǔ)時(shí)會(huì)將當(dāng)前時(shí)區(qū)的日期時(shí)間值轉(zhuǎn)換為時(shí)間標(biāo)準(zhǔn)時(shí)間值，檢索時(shí)再轉(zhuǎn)換回當(dāng)前時(shí)區(qū)的日期時(shí)間值，這會(huì)更友好。而 DATETIME 則只能反映出插入時(shí)當(dāng)?shù)氐臅r(shí)區(qū)，其他時(shí)區(qū)的人查看數(shù)據(jù)必然會(huì)有誤差的。另外，TIMESTAMP 的屬性受 MySQL 版本和服務(wù)器 SQLMode 的影響很大。

drop table if exists t_date;
create table t_date (
    d1 datetime,
    d2 timestamp
);
insert into t_date values('2021-9-2 14:45:52','2021-9-2 14:45:52');
select * from t_date;
-- 修改當(dāng)前的時(shí)區(qū)
set time_zone = '+2:00';
insert into t_date values('202192144552','202192144552'); -- 格式錯(cuò)誤
insert into t_date values('20210902144552','20210902144552');
insert into t_date values('2021&9&2 14%45%52','2021#9#2 14@45@52');
select * from t_date;
drop table if exists t_date;
create table t_date (
    d year
);
insert into t_date values(2021),(85),(22),(69),(0),('0');
select * from t_date;

6.4 其他類型

6.4.1 JSON 類型

在 MySQL 5.7 之前，如果需要在數(shù)據(jù)庫(kù)中存儲(chǔ) JSON 數(shù)據(jù)只能使用 VARCHAR 或 TEXT 字符串類型。從 5.7.8 版本之后開(kāi)始支持 JSON 數(shù)據(jù)類型。

6.4.2 空間類型

MySQL 空間類型擴(kuò)展支持地理特征的生成、存儲(chǔ)和分析。這里的地理特征表示世界上具有位置的任何東西，可以是一個(gè)實(shí)體，例如一座山；可以是空間，例如一座辦公樓；也可以是一個(gè)可定義的位置，例如一個(gè)十字路口等等?，F(xiàn)在的應(yīng)用程序開(kāi)發(fā)中空間數(shù)據(jù)的存儲(chǔ)越來(lái)越多了，例如，釘釘?shù)拇蚩ㄎ恢檬欠裨谵k公區(qū)域范圍內(nèi)，滴滴打車的位置、路線等。MySQL 提供了非常豐富的空間函數(shù)以支持各種空間數(shù)據(jù)的查詢和處理。

MySQL 中使用 Geometry（幾何）來(lái)表示所有地理特征。Geometry 指一個(gè)點(diǎn)或點(diǎn)的集合，代表世界上任何具有位置的事物。MySQL 的空間數(shù)據(jù)類型（Spatial Data Type）對(duì)應(yīng)于 OpenGIS 類，包括 GEOMETRY、POINT、LINESTRING、POLYGON 等單值類型以及 MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION 存放不同幾何值的集合類型。

結(jié)語(yǔ)

掌握這些基礎(chǔ)，你已邁出MySQL精進(jìn)的第一步。理論結(jié)合實(shí)踐，方能在實(shí)際開(kāi)發(fā)中游刃有余。

到此這篇關(guān)于MySQL核心概念及SQL語(yǔ)句與數(shù)據(jù)類型詳解的文章就介紹到這了,更多相關(guān)mysql 概念及sql語(yǔ)句內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論