MySQL 入門
目錄
- MySQL 入門
- SQL 基礎使用
- 查詢語言分類
- DDL 陳述句
- 創建資料庫
- 洗掉資料庫
- 創建表
- 洗掉表
- 修改表
- DML 陳述句
- 插入
- 更新記錄
- 洗掉記錄
- DQL 陳述句
- 去重
- 條件查詢
- 排序
- 限制
- 聚合
- 表連接
- 子查詢
- 聯合查詢
- DCL 陳述句
- 關于幫助檔案的使用
- 按照層次查詢
- 快速查閱
- DDL 陳述句
- MySQL 資料型別
- 數值型別
- 整數
- 小數
- 位型別
- 日期時間型別
- YEAR
- TIME
- DATE
- DATETIME
- TIMESTAMP
- 字串型別
- CHAR 和 VARCHAR 型別
- BINARY 和 VARBINARY 型別
- BLOB 型別
- TEXT 型別
- ENUM 型別
- SET 型別
- 數值型別
- MySQL 運算子
- 算術運算子
- 比較運算子
- 邏輯運算子
- 位運算子
- MySQL 常用函式
- 字串函式
- 數值函式
- 日期和時間函式
- 流程函式
- 其他函式
SQL 基礎使用
MySQL 是一種關系型資料庫，說到關系，那么就離不開表與表之間的關系，而最能體現這種關系的其實就是我們接下來需要介紹的主角 SQL，SQL 的全稱是 Structure Query Language ，結構化的查詢語言，它是一種針對表關聯關系所設計的一門語言，也就是說，學好 MySQL，SQL 是基礎和重中之重，SQL 不只是 MySQL 中特有的一門語言，大多數關系型資料庫都支持這門語言，
下面我們就來一起學習一下這門非常重要的語言，
查詢語言分類
在了解 SQL 之前我們需要知道下面這幾個概念
-
資料定義語言： 簡稱
DDL(Data Definition Language)，用來定義資料庫物件:資料庫、表、列等； -
資料操作語言： 簡稱
DML(Data Manipulation Language)，用來對資料庫中表的記錄進行更新，關鍵字： insert、update、delete等 -
資料控制語言： 簡稱
DCL(Data Control Language)，用來定義資料庫訪問權限和安全級別，創建用戶等，關鍵字： grant等 -
資料查詢語言： 簡稱
DQL(Data Query Language)，用來查詢資料庫中表的記錄，關鍵字： select from where等
DDL 陳述句
創建資料庫
下面就開始我們的 SQL 陳述句學習之旅，首先你需要啟動 MySQL 服務，我這里是 mac 電腦，所以我直接可以啟動
然后我們使用命令列的方式連接資料庫，打開 iterm，輸入下面
MacBook:~ mr.l$ mysql -uroot -p
就可以連接到資料庫了
在上面命令中，mysql 代表客戶端命令，- u 表示后面需要連接的用戶，-p 表示需要輸入此用戶的密碼，在你輸入用戶名和密碼后，如果成功登陸，會顯示一個歡迎界面（如上圖 ）和 mysql> 提示符，
歡迎界面主要描述了這些東西
- 每一行的結束符，這里用
;或者\g來表示每一行的結束 - Your MySQL connection id is 4，這個記錄了 MySQL 服務到目前為止的連接數，每個新鏈接都會自動增加 1 ，上面顯示的連接次數是 4 ，說明我們只連接了四次
- 然后下面是 MySQL 的版本，我們使用的是 5.7
- 通過
help或者\h命令來顯示幫助內容，通過\c命令來清除命令列 buffer，
然后需要做的事情是什么？我們最終想要學習 SQL 陳述句，SQL 陳述句肯定是要查詢資料，通過資料來體現出來表的關聯關系，所以我們需要資料，那么資料存在哪里呢？資料存盤的位置被稱為 表(table)，表存盤的位置被稱為 資料庫(database)，所以我們需要先建資料庫后面再建表然后插入資料，再進行查詢，
所以我們首先要做的就是創建資料庫，創建資料庫可以直接使用指令
CREATE DATABASE dbname;
進行創建，比如我們創建資料庫 cxuandb
create database cxuandb;
注意最后的 ; 結束語法一定不要丟掉，否則 MySQL 會認為你的命令沒有輸出完，敲 enter 后會直接換行輸出
創建完成后，會提示 Query OK, 1 row affected，這段陳述句什么意思呢？ Query OK 表示的就是查詢完成，為什么會顯示這個？因為所有的 DDL 和 DML 操作執行完成后都會提示這個， 也可以理解為操作成功，后面跟著的 **1 row affected ** 表示的是影響的行數，() 內顯示的是你執行這條命令所耗費的時間，也就是 0.03 秒，
上圖我們成功創建了一個 cxuandb 的資料庫，此時我們還想創建一個資料庫，我們再執行相同的指令，結果提示
提示我們不能再創建資料庫了，資料庫已經存在，這時候我就有疑問了，我怎么知道都有哪些資料庫呢？別我再想創建一個資料庫又告訴我已經存在，這時候可以使用 show databases 命令來查看你的 MySQL 已有的資料庫
show databases;
執行完成后的結果如下
因為資料庫我之前已經使用過，這里就需要解釋一下，除了剛剛新創建成功的 cxuandb 外，informationn_schema 、performannce_schema 和 sys 都是系統自帶的資料庫，是安裝 MySQL 默認創建的資料庫，它們各自表示
- informationn_schema： 主要存盤一些資料庫物件資訊，比如用戶表資訊、權限資訊、磁區資訊等
- performannce_schema： MySQL 5.5 之后新增加的資料庫，主要用于收集資料庫服務器性能引數，
- sys: MySQL 5.7 提供的資料庫，sys 資料庫里面包含了一系列的存盤程序、自定義函式以及視圖來幫助我們快速的了解系統的元資料資訊，
其他所有的資料庫都是作者自己創建的，可以忽略他們，
在創建完資料庫之后，可以用如下命令選擇要操作的資料庫
use cxuandb
這樣就成功切換為了 cxuandb 資料庫，我們可以在此資料庫下進行建表、查看基本資訊等操作，
比如想要看康康我們新建的資料庫里面有沒有其他表
show tables;
果然，我們新建的資料庫下面沒有任何表，但是現在，我們還不進行建表操作，我們還是先來認識一下資料庫層面的命令，也就是其他 DDL 指令
洗掉資料庫
如果一個資料庫我們不想要了，那么該怎么辦呢？直接刪掉資料庫不就好了嗎？刪表陳述句是
drop database dbname;
比如 cxuandb 我們不想要他了，可以通過使用
drop database cxuandb;
進行洗掉，這里我們就不進行演示了，因為 cxuandb 我們后面還會使用，
但是這里注意一點，你洗掉資料庫成功后會出現 0 rows affected，這個可以不用理會，因為在 MySQL 中，drop 陳述句操作的結果都是 0 rows affected，
創建表
下面我們就可以對表進行操作了，我們剛剛 show tables 發現還沒有任何表，所以我們現在進行建表陳述句
CREATE TABLE 表名稱
(
列名稱1 資料型別 約束,
列名稱2 資料型別 約束,
列名稱3 資料型別 約束,
....
)
這樣就很清楚了吧，列名稱就是列的名字，緊跟著列名后面就是資料型別，然后是約束，為什么要這么設計？舉個例子你就清楚了，比如 cxuan 剛被生出來就被列印上了標簽
比如我們創建一個表，里面有 5 個欄位，姓名(name)、性別(sex)、年齡(age)、何時雇傭(hiredate)、薪資待遇(wage)，建表陳述句如下
create table job(name varchar(20), sex varchar(2), age int(2), hiredate date, wage decimal(10,2));
事實證明這條建表陳述句還是沒問題的，建表完成后可以使用 DESC tablename 查看表的基本資訊
DESC 命令會查看表的定義，但是輸出的資訊還不夠全面，所以，如果想要查看更全的資訊，還要通過查看表的創建陳述句的 SQL 來得到
show create table job \G;
可以看到，除了看到表定義之外，還看到了表的 engine(存盤引擎) 為 InnoDB 存盤引擎，\G 使得記錄能夠豎著排列，如果不用 \G 的話，效果如下
洗掉表
表的洗掉陳述句有兩種，一種是 drop 陳述句，SQL 陳述句如下
drop table job
一種是 truncate 陳述句，SQL 陳述句如下
truncate table job
這兩者的區別簡單理解就是 drop 陳述句洗掉表之后，可以通過日志進行回復，而 truncate 洗掉表之后永遠恢復不了，所以，一般不使用 truncate 進行表的洗掉，‘
修改表
對于已經創建好的表，尤其是有大量資料的表，如果需要對表做結構上的改變，可以將表洗掉然后重新創建表，但是這種效率會產生一些額外的作業，資料會重新加載近來，如果此時有服務正在訪問的話，也會影響服務讀取表中資料，所以此時，我們需要表的修改陳述句來對已經創建好的表的定義進行修改，
修改表結構一般使用 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);
也可以對表結構進行修改，比如增加一個欄位
alter table job add home varchar(30);
將新添加的表的欄位進行洗掉
alter table job drop column home;
可以對表中欄位的名稱進行修改，比如吧 wage 改為 salary
alter table job change wage salary decimal(10,2);
修改欄位的排列順序，我們前面介紹過修改語法涉及到一個順序問題，都有一個可選項 **first | after ** column_name，這個選項可以用來修改表中欄位的位置，默認 ADD 是在添加為表中最后一個欄位，而 CHANGE/MODIFY 不會改變欄位位置，比如
alter table job add birthday after hiredate;
可以對表名進行修改，例如將 job 表改為 worker
alter table job rename worker;
DML 陳述句
有的地方把 DML 陳述句（增刪改）和 DQL 陳述句（查詢）統稱為 DML 陳述句，有的地方分開，我們目前使用分開稱呼的方式
插入
表創建好之后，我們就可以向表里插入資料了，插入記錄的基本語法如下
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);
也可以不用指定要插入的欄位，直接插入資料即可
insert into job values("cxuan02","男",25,"2020-06-01","1995-04-23",12000);
這里就有一個問題，如果插入的順序不一致的話會怎么樣呢？
對于含可空欄位、非空但是含有默認值的欄位、自增欄位可以不用在 insert 后的欄位串列出現，values 后面只需要寫對應欄位名稱的 value 即可，沒有寫的欄位可以自動的設定為 NULL、默認值或者自增的下一個值，這樣可以縮短要插入 SQL 陳述句的長度和復雜性，
比如我們設定一下 hiredate、age 可以為 null，來試一下
insert into job(name,sex,birthday,salary) values("cxuan03","男","1992-08-23",15000);
我們看一下實際插入的資料
我們可以看到有一行兩個欄位顯示 NULL，在 MySQL 中，insert 陳述句還有一個很好的特性，就是一次可以插入多條記錄
INSERT INTO tablename (field1,field2) VALUES
(value1,value2),
(value1,value2),
(value1,value2),
...;
可以看出，每條記錄之間都用逗號進行分割，這個特性可以使得 MySQL 在插入大量記錄時，節省很多的網路開銷，大大提高插入效率，
更新記錄
對于表中已經存在的資料，可以通過 update 命令對其進行修改，語法如下
UPDATE tablename SET field1 = value1, field2 = value2 ;
例如，將 job 表中的 cxuan03 中 age 的 NULL 改為 26，SQL 陳述句如下
update job set age = 26 where name = 'cxuan03';
SQL 陳述句中出現了一個 where 條件，我們會在后面說到 where 條件，這里簡單理解一下它的概念就是根據哪條記錄進行更新，如果不寫 where 的話，會對整個表進行更新
洗掉記錄
如果記錄不再需要，可以使用 delete 命令進行洗掉
DELETE FROM tablename [WHERE CONDITION]
例如，在 job 中洗掉名字是 cxuan03 的記錄
delete from job where name = 'cxuan03';
在 MySQL 中，洗掉陳述句也可以不指定 where 條件，直接使用
delete from job
這種洗掉方式相當于是清楚表的操作，表中所有的記錄都會被清除，
DQL 陳述句
下面我們一起來認識一下 DQL 陳述句，資料被插入到 MySQL 中，就可以使用 SELECT 命令進行查詢，來得到我們想要的結果，
SELECT 查詢陳述句可以說是最復雜的陳述句了，這里我們只介紹一下基本語法
一種最簡單的方式就是從某個表中查詢出所有的欄位和資料，簡單粗暴，直接使用 SELECT *
SELECT * FROM tablename;
例如我們將 job 表中的所有資料查出來
select * from job;
其中 * 是查詢出所有的資料，當然，你也可以查詢出指定的資料項
select name,sex,age,hiredate,birthday,salary from job;
上面這條 SQL 陳述句和 select * from job 表是等價的，但是這種直接查詢指定欄位的 SQL 陳述句效率要高，
上面我們介紹了基本的 SQL 查詢陳述句，但是實際的使用場景會會比簡單查詢復雜太多，一般都會使用各種 SQL 的函式和查詢條件等，下面我們就來一起認識一下，
去重
使用非常廣泛的場景之一就是 去重，去重可以使用 distinct 關鍵字來實作
為了演示效果，我們先向資料庫中插入批量資料，插入完成后的表結構如下
下面我們使用 distinct 來對 age 去重來看一下效果
你會發現只有兩個不同的值，其他和 25 重復的值被過濾掉了，所以我們使用 distinct 來進行去重
條件查詢
我們之前的所有例子都是查詢全部的記錄，如果我們只想查詢指定的記錄呢？這里就會用到 where條件查詢陳述句，條件查詢可以對指定的欄位進行查詢，比如我們想查詢所有年齡為 24 的記錄，如下
select * from job where age = 24;
where 條件陳述句后面會跟一個判斷的運算子 =，除了 = 號比較外，還可以使用 >、<、>=、<=、!= 等比較運算子；例如
select * from job where age >= 24;
就會從 job 表中查詢出 age 年齡大于或等于 24 的記錄
除此之外，在 where 條件查詢中還可以有多個并列的查詢條件，比如我們可以查詢年齡大于等于 24，并且薪資大雨 8000 的記錄
select * from job where age >= 24 and salary > 8000;
多個條件之間還可以使用 or、and 等邏輯運算子進行多條件聯合查詢，運算子會在以后章節中詳細講解，
排序
我們會經常有這樣的需求，按照某個欄位進行排序，這就用到了資料庫的排序功能，使用關鍵字 order by 來實作，語法如下
SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC] ， field2 [DESC|ASC]，……fieldn [DESC|ASC]]
其中 DESC 和 ASC 就是順序排序的關鍵字，DESC 會按照欄位進行降序排列，ASC 會按照欄位進行升序排列，默認會使用升序排列，也就是說，你不寫 order by 具體的排序的話，默認會使用升序排列，order by 后面可以跟多個排序欄位，并且每個排序欄位可以有不同的排序順序，
為了演示功能，我們先把表中的 salary 工資列進行修改，修改完成后的表記錄如下
下面我們按照工資進行排序，SQL 陳述句如下
select * from job order by salary desc;
陳述句執行完成后的結果如下
這是對一個欄位進行排序的結果，也可以對多個欄位進行排序，但是需要注意一點
根據 order by 后面聲名的順序進行排序，如果有三個排序欄位 A、B、C 的話，如果 A 欄位排序欄位的值一樣，則會根據第二個欄位進行排序，以此類推，
如果只有一個排序欄位，那么這些欄位相同的記錄將會無序排列，
限制
對于排序后的欄位，或者不排序的欄位，如果只希望顯示一部分的話，就會使用 LIMIT 關鍵字來實作，比如我們只想取前三條記錄
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 一般經常和 order by 語法一起實作分頁查詢，
注意：limit 是 MySQL 擴展 SQL92 之后的語法，在其他資料庫比如 Oracle 上就不通用，我犯過一個白癡的行為就是在 Oracle 中使用 limit 查詢陳述句，，，
聚合
下面我們來看一下對記錄進行匯總的操作，這類操作主要有
匯總函式，比如 sum 求和、count 統計數量、max 最大值、min 最小值等group by，關鍵字表示對分類聚合的欄位進行分組，比如按照部門統計員工的數量，那么 group by 后面就應該跟上部門with是可選的語法，它表示對匯總之后的記錄進行再次匯總having關鍵字表示對分類后的結果再進行條件的過濾，
看起來 where 和 having 意思差不多，不過它們用法不一樣，where 是使用在統計之前，對統計前的記錄進行過濾，having 是用在統計之后，是對聚合之后的結果進行過濾，也就是說 where 永遠用在 having 之前，我們應該先對篩選的記錄進行過濾，然后再對分組的記錄進行過濾，
可以對 job 表中員工薪水進行統計，選出總共的薪水、最大薪水、最小薪水
select sum(salary) from job;
select max(salary),min(salary) from job;
比如我們要統計 job 表中人員的數量
select count(1) from job;
統計完成后的結果如下
我們可以按照 job 表中的年齡來進行對應的統計
select age,count(1) from job group by age;
既要統計各年齡段的人數，又要統計總人數
select age,count(1) from job group by age with rollup;
在此基礎上進行分組，統計數量大于 1 的記錄
select age,count(1) from job group by age with rollup having count(1) > 1;
表連接
表連接一直是筆者比較痛苦的地方，曾經因為一個表連接掛了面試，現在來認真擼一遍，
表連接一般體現在表之間的關系上，當需要同時顯示多個表中的欄位時，就可以用表連接來實作，
為了演示表連接的功能，我們為 job 表加一個 type 欄位表示作業型別，增加一個 job_type 表表示具體的作業種類，如下所示
下面開始我們的演示
查詢出 job 表中的 type 和 job_type 表中的 type 匹配的姓名和作業型別
select job.name,job_type.name from job,job_type where job.type = job_type.type;
上面這種連接使用的是內連接，除此之外，還有外連接，那么它們之間的區別是啥呢？
內連接：選出兩張表中互相匹配的記錄；
外連接：不僅選出匹配的記錄，也會選出不匹配的記錄；
外連接分為兩種
- 左外連接：篩選出包含左表的記錄并且右表沒有和它匹配的記錄
- 右外連接：篩選出包含右表的記錄甚至左表沒有和它匹配的記錄
為了演示效果我們在 job 表和 job_type 表中分別添加記錄，添加完成后的兩表如下
下面我們進行左外連接查詢：查詢出 job 表中的 type 和 job_type 表中的 type 匹配的姓名和作業型別
select job.name,job_type.name from job left join job_type on job.type = job_type.type;
查詢出來的結果如下
可以看出 cxuan06 也被查詢出來了，而 cxuan06 他沒有具體的作業型別，
使用右外連接查詢
select job.name,job_type.name from job right join job_type on job.type = job_type.type;
可以看出，job 表中并沒有 waiter 和 manager 的角色，但是也被查詢出來了，
子查詢
有一些情況，我們需要的查詢條件是另一個 SQL 陳述句的查詢結果，這種查詢方式就是子查詢，子查詢有一些關鍵字比如 in、not in、=、!=、exists、not exists 等，例如我們可以通過子查詢查詢出每個人的作業型別
select job.* from job where type in (select type from job_type);
如果自查詢數量唯一的話，還可以用 = 來替換 in
select * from job where type = (select type from job_type);
意思是自查詢不唯一，我們使用 limit 限制一下回傳的記錄數
select * from job where type = (select type from job_type limit 1,1);
在某些情況下，子查詢可以轉換為表連接
聯合查詢
我們還經常會遇到這樣的場景，將兩個表的資料單獨查詢出來之后，將結果合并到一起進行顯示，這個時候就需要 UNION 和 UNION ALL 這兩個關鍵字來實作這樣的功能，UNION 和 UNION ALL 的主要區別是 UNION ALL 是把結果集直接合并在一起，而 UNION 是將 UNION ALL 后的結果進行一次 DISTINCT 去除掉重復資料，
比如
select type from job union all select type from job_type;
它的結果如下
上述結果是查詢 job 表中的 type 欄位和 job_type 表中的 type 欄位，并把它們進行匯總，可以看出 UNION ALL 只是把所有的結果都列出來了
使用 UNION 的 SQL 陳述句如下
select type from job union select type from job_type;
可以看出 UNION 是對 UNION ALL 使用了 distinct 去重處理，
DCL 陳述句
DCL 陳述句主要是管理資料庫權限的時候使用，這類操作一般是 DBA 使用的，開發人員不會使用 DCL 陳述句，
關于幫助檔案的使用
我們一般使用 MySQL 遇到不會的或者有疑問的東西經常要去查閱網上資料，甚至可能需要去查 MySQL 官發檔案，這樣會耗費大量的時間和精力，
下面教你一下在 MySQL 命令列就能直接查詢資料的陳述句
按照層次查詢
可以使用 ? contents 來查詢所有可供查詢的分類，如下所示
? contents;
我們輸入
? Account Management
可以查詢具體關于權限管理的命令
比如我們想了解一下資料型別
? Data Types
然后我們想了解一下 VARCHAR 的基本定義，可以直接使用
? VARCHAR
可以看到有關于 VARCHAR 資料型別的詳細資訊，然后在最下面還有 MySQL 的官方檔案，方便我們快速查閱，
快速查閱
在實際應用程序中，如果要快速查詢某個語法時，可以使用關鍵字進行快速查詢，比如我們使用
? show
能夠快速列出一些命令
比如我們想要查閱 database 的資訊，使用
SHOW CREATE DATABASE cxuandb;
MySQL 資料型別
MySQL 提供很多種資料型別來對不同的常量、變數進行區分，MySQL 中的資料型別主要是 數值型別、日期和時間型別、字串型別 選擇合適的資料型別進行資料的存盤非常重要，在實際開發程序中，選擇合適的資料型別也能夠提高 SQL 性能，所以有必要認識一下這些資料型別，
數值型別
MySQL 支持所有標準的 SQL 資料型別，這些資料型別包括嚴格資料型別的嚴格數值型別，這些資料型別有
- INTEGER
- SMALLINT
- DECIMAL
- NUMERIC，
近似數值資料型別 并不用嚴格按照指定的資料型別進行存盤，這些有
- FLOAT
- REAL
- DOUBLE PRECISION
還有經過擴展之后的資料型別，它們是
- TINYINT
- MEDIUMINT
- BIGINT
- BIT
其中 INT 是 INTEGER 的縮寫，DEC 是 DECIMAL 的縮寫，
下面是所有資料型別的匯總
整數
在整數型別中，按照取值范圍和存盤方式的不同，分為
- TINYINT ，占用 1 位元組
- SMALLINT，占用 2 位元組
- MEDIUMINT，占用 3 位元組
- INT、INTEGER，占用 4 位元組
- BIGINT，占用 8 位元組
五個資料型別，如果超出型別范圍的操作，會發生錯誤提示，所以選擇合適的資料型別非常重要，
還記得我們上面的建表陳述句么
我們一般會在 SQL 陳述句的資料型別后面加上指定長度來表示資料型別許可的范圍，例如
int(7)
表示 int 型別的資料最大長度為 7，如果填充不滿的話會自動填滿，如果不指定 int 資料型別的長度的話，默認是 int(11)，
我們創建一張表來演示一下
create table test1(aId int, bId int(5));
/* 然后我們查看一下表結構 */
desc test1;
整數型別一般配合 zerofill 來使用，顧名思義，就是用 0 進行填充，也就是數字位數不夠的空間使用 0 進行填充，
分別修改 test1 表中的兩個欄位
alter table test1 modify aId int zerofill;
alter table test1 modify bId int(5) zerofill;
然后插入兩條資料，執行查詢操作
如上圖所示，使用zerofill 可以在數字前面使用 0 來進行填充，那么如果寬度超過指定長度后會如何顯示？我們來試驗一下，向 aId 和 bId 分別插入超過字符限制的數字
會發現 aId 已經超出了指定范圍，那么我們對 aId 插入一個在其允許范圍之內的資料
會發現，aId 已經插進去了，bId 也插進去了，為什么 bId 顯示的是 int(5) 卻能夠插入 7 位長度的數值呢？
所有的整數都有一個可選屬性 UNSIGNED(無符號)，如果需要在欄位里面保存非負數或者是需要較大上限值時，可以使用此選項，它的取值范圍是正常值的下限取 0 ，上限取原值的 2 倍，如果一個列為 zerofill ，會自動為該列添加 UNSIGNED 屬性，
除此之外，整數還有一個型別就是 AUTO_INCREMENT，在需要產生唯一識別符號或者順序值時，可利用此屬性，這個屬性只用于整數字符，一個表中最多只有一個 AUTO_INCREMENT 屬性，一般用于自增主鍵，而且 NOT NULL，并且是 PRIMARY KEY 和 UNIQUE 的，主鍵必須保證唯一性而且不為空，
小數
小數說的是啥？它其實有兩種型別；一種是浮點數型別，一種是定點數型別；
浮點數有兩種
- 單精度浮點型 - float 型
- 雙精度浮點型 - double 型
定點數只有一種 decimal，定點數在 MySQL 內部中以字串的形式存在，比浮點數更為準確，適合用來表示精度特別高的資料，
浮點數和定點數都可以使用 (M,D) 的方式來表示，M 表示的就是 整數位 + 小數位 的數字，D 表示位于 . 后面的小數，M 也被稱為精度 ，D 被稱為標度，
下面通過示例來演示一下
首先建立一個 test2 表
CREATE TABLE test2 (aId float(6,2) default NULL, bId double(6,2) default NULL,cId decimal(6,2) default NULL)
然后向表中插入幾條資料
insert into test2 values(1234.12,1234.12,1234.12);
這個時候顯示的資料就是
然后再向表中插入一些約束之外的資料
insert into test2 values(1234.123,1234.123,1234.123);
發現插入完成后還顯示的是 1234.12，小數位第三位的值被舍去了，
現在我們把 test2 表中的精度全部去掉，再次插入
alter table test2 modify aId float;
alter table test2 modify bId double;
alter table test2 modify cId decimal;
先查詢一下，發現 cId 舍去了小數位，
然后再次插入 1.23，SQL 陳述句如下
insert into test2 values(1.23,1.23,1.23);
結果如下
這個時候可以驗證
- 浮點數如果不寫精度和標度，會按照實際的精度值進行顯示
- 定點數如果不寫精度和標度，會按照
decimal(10,0)來進行操作，如果資料超過了精度和標題，MySQL 會報錯
位型別
對于位型別，用于存放欄位值，BIT(M) 可以用來存放多位二進制數，M 的范圍是 1 - 64，如果不寫的話默認為 1 位，
下面我們來掩飾一下位型別
新建一個 test3 表，表中只有一個位型別的欄位
create table test3(id bit(1));
然后隨意插入一條資料
insert into test3 values(1);
發現無法查詢出對應結果，
然后我們使用 hex() 和 bin() 函式進行查詢
發現能夠查詢出對應結果，
也就是說當資料插入 test3 時，會首先把資料轉換成為二進制數，如果位數允許，則將成功插入；如果位數小于實際定義的位數，則插入失敗，如果我們像表中插入資料 2
insert into test3 values(2);
那么會報錯
因為 2 的二進制數表示是 10，而表中定義的是 bit(1) ，所以無法插入，
那么我們將表欄位修改一下
然后再進行插入，發現已經能夠插入了
日期時間型別
MySQL 中的日期與時間型別，主要包括：YEAR、TIME、DATE、DATETIME、TIMESTAMP，每個版本可能不同，下表中列出了這幾種型別的屬性，
下面分別來介紹一下
YEAR
YEAR 可以使用三種方式來表示
- 用 4 位的數字或者字串表示，兩者效果相同，表示范圍 1901 - 2155，插入超出范圍的資料會報錯，
- 以 2 位字串格式表示，范圍為 ‘00’‘99’，‘00’‘69’ 表示 20002069，‘70’‘99’ 表示1970~1999，‘0’ 和 ‘00’ 都會被識別為 2000，超出范圍的資料也會被識別為 2000，
- 以 2 位數字格式表示，范圍為 199，169 表示 2001~2069, 70~99 表示 1970~1999，但 0 值會被識別為0000，這和 2 位字串被識別為 2000 有所不同
下面我們來演示一下 YEAR 的用法，創建一個 test4 表
create table test4(id year);
然后我們看一下 test4 的表結構
默認創建的 year 就是 4 位，下面我們向 test4 中插入資料
insert into test4 values(2020),('2020');
然后進行查詢，發現表示形式是一樣的
使用兩位字串來表示
delete from test4;
insert into test4 values ('0'),('00'),('11'),('88'),('20'),('21');
使用兩位數字來表示
delete from test4;
insert into test4 values (0),(00),(11),(88),(20),(21);
發現只有前兩項不一樣，
TIME
TIME 所表示的范圍和我們預想的不一樣
我們把 test4 改為 TIME 型別，下面是 TIME 的示例
alter table test4 modify id TIME;
insert into test4 values ('15:11:23'),('20:13'),('2 11:11'),('3 05'),('33');
結果如下
DATE
DATE 表示的型別有很多種，下面是 DATE 的幾個示例
create table test5 (id date);
查看一下 test5 表
然后插入部分資料
insert into test5 values ('2020-06-13'),('20200613'),(20200613);
DATE 的表示一般很多種，如下所示 DATE 的所有形式
- 'YYYY-MM-DD'
- 'YYYYMMDD'
- YYYYMMDD
- 'YY-MM-DD'
- 'YYMMDD'
- YYMMDD
DATETIME
DATETIME 型別，包含日期和時間部分，可以使用參考字串或者數字，年份可以是 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 個位元組（比DATETIME少），取值范圍比 DATETIME 小，
下面來說一下各個時間型別的使用場景
- 一般表示
年月日，通常用DATE型別； - 用來表示
時分秒，通常用TIME表示； 年月日時分秒，通常用DATETIME來表示；- 如果需要插入的是當前時間，通常使用
TIMESTAMP來表示，TIMESTAMP 值回傳后顯示為YYYY-MM-DD HH:MM:SS格式的字串， - 如果只表示年份、則應該使用 YEAR，它比 DATE 型別需要更小的空間，
每種日期型別都有一個范圍，如果超出這個范圍，在默認的 SQLMode 下，系統會提示錯誤，并進行零值存盤，
下面來解釋一下 SQLMode 是什么
MySQL 中有一個環境變數是 sql_mode ，sql_mode 支持了 MySQL 的語法、資料校驗，我們可以通過下面這種方式來查看當前資料庫使用的 sql_mode
select @@sql_mode;
一共有下面這幾種模式
來源于 https://www.cnblogs.com/Zender/p/8270833.html
字串型別
MySQL 提供了很多種字串型別，下面是字串型別的匯總
下面我們對這些資料型別做一個詳細的介紹
CHAR 和 VARCHAR 型別
CHAR 和 VARCHAR 型別很相似，導致很多同學都會忽略他們之間的差別，首先他倆都是用來保存字串的資料型別，他倆的主要區別在于存盤方式不同，CHAR 型別的長度就是你定義多少顯示多少，占用 M 位元組，比如你宣告一個 CHAR(20) 的字串型別，那么每個字串占用 20 位元組，M 的取值范圍時 0 - 255，VARCHAR 是可變長的字串，范圍是 0 - 65535，在字串檢索的時候，CHAR 會去掉尾部的空格，而 VARCHAR 會保留這些空格，下面是演示例子
create table vctest1 (vc varchar(6),ch char(6));
insert into vctest1 values("abc ","abc ");
select length(vc),length(ch) from vctest1;
結果如下
可以看到 vc 的字串型別是 varchar ，長度是 5，ch 的字串型別是 char，長度是 3，可以得出結論，varchar 會保留最后的空格，char 會去掉最后的空格，
BINARY 和 VARBINARY 型別
BINARY 和 VARBINARY 與 CHAR 和 VARCHAR 非常類似，不同的是它們包含二進制字串而不包含非二進制字串，BINARY 與 VARBINARY 的最大長度和 CHAR 與 VARCHAR 是一樣的，只不過他們是定義位元組長度，而 CHAR 和 VARCHAR 對應的是字符長度，
BLOB 型別
BLOB 是一個二進制大物件，可以容納可變數量的資料，有 4 種 BLOB 型別：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，它們區別在于可容納存盤范圍不同，
TEXT 型別
有 4 種 TEXT 型別：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT，對應的這 4 種 BLOB 型別，可存盤的最大長度不同，可根據實際情況選擇，
ENUM 型別
ENUM 我們在 Java 中經常會用到，它表示的是列舉型別，它的范圍需要在創建表時顯示指定，對 1 - 255 的列舉需要 1 個位元組存盤；對于 255 - 65535 的列舉需要 2 個位元組存盤，ENUM 會忽略大小寫，在存盤時都會轉換為大寫，
SET 型別
SET 型別和 ENUM 型別有兩處不同
- 存盤方式
SET 對于每 0 - 8 個成員，分別占用 1 個位元組，最大到 64 ，占用 8 個位元組
- Set 和 ENUM 除了存盤之外，最主要的區別在于 Set 型別一次可以選取多個成員，而 ENUM 則只能選一個，
MySQL 運算子
MySQL 中有多種運算子，下面對 MySQL 運算子進行分類
- 算術運算子
- 比較運算子
- 邏輯運算子
- 位運算子
下面那我們對各個運算子進行介紹
算術運算子
MySQL 支持的算術運算子包括加、減、乘、除和取余，這類運算子的使用頻率比較高
下面是運算子的分類
| 運算子 | 作用 |
|---|---|
| + | 加法 |
| - | 減法 |
| * | 乘法 |
| /, DIV | 除法，回傳商 |
| %, MOD | 除法，回傳余數 |
下面簡單描述了這些運算子的使用方法
+用于獲得一個或多個值的和-用于從一個值減去另一個值*用于兩數相乘，得到兩個或多個值的乘積/用一個值除以另一個值得到商%用于一個值除以另一個值得到余數
在除法和取余需要注意一點，如果除數是 0 ，將是非法除數，回傳結果為 NULL，
比較運算子
熟悉了運算子，下面來聊一聊比較運算子，使用 SELECT 陳述句進行查詢時，MySQL 允許用戶對運算式的兩側的運算元進行比較，比較結果為真，回傳 1， 比較結果為假，回傳 0 ，比較結果不確定回傳 NULL，下面是所有的比較運算子
| 運算子 | 描述 |
|---|---|
| = | 等于 |
| <> 或者是 != | 不等于 |
| <=> | NULL 安全的等于，也就是 NULL-safe |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| BETWEEN | 在指定范圍內 |
| IS NULL | 是否為 NULL |
| IS NOT NULL | 是否為 NULL |
| IN | 存在于指定集合 |
| LIKE | 通配符匹配 |
| REGEXP 或 RLIKE | 正則運算式匹配 |
比較運算子可以用來比較數字、字串或者運算式，數字作為浮點數進行比較，字串以不區分大小寫的方式進行比較，
- = 號運算子，用于比較運算子兩側的運算元是否相等，如果相等則回傳 1， 如果不相等則回傳 0 ，下面是具體的示例，NULL 不能用于比較，會直接回傳 NULL
<>號用于表示不等于，和=號相反，示例如下
<=>NULL-safe 的等于運算子，與 = 號最大的區別在于可以比較 NULL 值
<號運算子，當左側運算元小于右側運算元時，回傳值為 1， 否則其回傳值為 0，
- 和上面同理，只不過是滿足 <= 的時候回傳 1 ，否則 > 回傳 0，這里我有個疑問，為什么
select 'a' <= 'b'; /* 回傳 1 */
/*而*/
select 'a' >= 'b'; /* 回傳 0 呢*/
- 關于
>和>=是同理 BETWEEN運算子的使用格式是 a BETWEEN min AND max ，當 a 大于等于 min 并且小于等于 max 時，回傳 1，否則回傳 0 ，運算元型別不同的時候，會轉換成相同的資料型別再進行處理，比如
IS NULL和IS NOT NULL表示的是是否為 NULL，ISNULL 為 true 回傳 1，否則回傳 0 ；IS NOT NULL 同理
IN這個比較運算子判斷某個值是否在一個集合中，使用方式是 xxx in (value1,value2,value3)
LIKE運算子的格式是xxx LIKE %123%，比如如下
當 like 后面跟的是 123% 的時候， xxx 如果是 123 則回傳 1，如果是 123xxx 也回傳 1，如果是 12 或者 1 就回傳 0 ，123 是一個整體，
REGEX運算子的格式是s REGEXP str，匹配時回傳值為 1，否則回傳 0 ，
后面會詳細介紹 regexp 的用法，
邏輯運算子
邏輯運算子指的就是布爾運算子，布爾運算子指回傳真和假，MySQL 支持四種邏輯運算子
| 運算子 | 作用 |
|---|---|
| NOT 或 ！ | 邏輯非 |
| AND 或者是 && | 邏輯與 |
| OR 或者是 || | 邏輯或 |
| XOR | 邏輯異或 |
下面分別來介紹一下
NOT或者是!表示的是邏輯非，當運算元為 0（假） ，則回傳值為 1，否則值為 0，但是有一點除外，那就是 NOT NULL 的回傳值為 NULL
AND和&&表示的是邏輯與的邏輯，當所有運算元為非零值并且不為 NULL 時，結果為 1，但凡是有一個 0 則回傳 0，運算元中有一個 null 則回傳 null
OR和||表示的是邏輯或，當兩個運算元均為非 NULL 值時，如有任意一個運算元為非零值，則結果為 1，否則結果為 0，
XOR表示邏輯異或，當任意一個運算元為 NULL 時，回傳值為 NULL，對于非 NULL 的運算元，如果兩個的邏輯真偽值相異，則回傳結果 1；否則回傳 0，
位運算子
一聽說位運算，就知道是和二進制有關的運算子了，位運算就是將給定的運算元轉換為二進制后，對各個運算元的每一位都進行指定的邏輯運算，得到的二進制結果轉換為十進制后就說是位運算的結果，下面是所有的位運算，
| 運算子 | 作用 |
|---|---|
| & | 位與 |
| | | 位或 |
| ^ | 位異或 |
| ～ | 位取反 |
| >> | 位右移 |
| << | 位左移 |
下面分別來演示一下這些例子
位與指的就是按位與，把 & 雙方轉換為二進制再進行 & 操作
按位與是一個數值減小的操作
位或指的就是按位或，把 | 雙方轉換為二進制再進行 | 操作
位或是一個數值增大的操作
位異或指的就是對運算元的二進制位做異或操作
位取反指的就是對運算元的二進制位做NOT操作，這里的運算元只能是一位，下面看一個經典的取反例子：對 1 做位取反，具體如下所示：
為什么會有這種現象，因為在 MySQL 中，常量數字默認會以 8 個位元組來顯示，8 個位元組就是 64 位，常量 1 的二進制表示 63 個 0，加 1 個 1 ， 位取反后就是 63 個 1 加一個 0 ， 轉換為二進制后就是 18446744073709551614，我們可以使用 select bin() 查看一下
位右移是對左運算元向右移動指定位數，例如 50 >> 3，就是對 50 取其二進制然后向右移三位，左邊補上 0 ，轉換結果如下
位左移與位右移相反，是對左運算元向左移動指定位數，例如 20 << 2
MySQL 常用函式
下面我們來了解一下 MySQL 函式，MySQL 函式也是我們日常開發程序中經常使用的，選用合適的函式能夠提高我們的開發效率，下面我們就來一起認識一下這些函式
字串函式
字串函式是最常用的一種函式了，MySQL 也是支持很多種字串函式，下面是 MySQL 支持的字串函式表
| 函式 | 功能 |
|---|---|
| LOWER | 將字串所有字符變為小寫 |
| UPPER | 將字串所有字符變為大寫 |
| CONCAT | 進行字串拼接 |
| LEFT | 回傳字串最左邊的字符 |
| RIGHT | 回傳字串最右邊的字符 |
| INSERT | 字串替換 |
| LTRIM | 去掉字串左邊的空格 |
| RTRIM | 去掉字串右邊的空格 |
| REPEAT | 回傳重復的結果 |
| TRIM | 去掉字串行尾和行頭的空格 |
| SUBSTRING | 回傳指定的字串 |
| LPAD | 用字串對最左邊進行填充 |
| RPAD | 用字串對最右邊進行填充 |
| STRCMP | 比較字串 s1 和 s2 |
| REPLACE | 進行字串替換 |
下面通過具體的示例演示一下每個函式的用法
- LOWER(str) 和 UPPER(str) 函式：用于轉換大小寫
- CONCAT(s1,s2 ... sn) ：把傳入的引數拼接成一個字串
上面把 c xu an 拼接成為了一個字串，另外需要注意一點，任何和 NULL 進行字串拼接的結果都是 NULL，
- LEFT(str,x) 和 RIGHT(str,x) 函式：分別回傳字串最左邊的 x 個字符和最右邊的 x 個字符，如果第二個引數是 NULL，那么將不會回傳任何字串
- INSERT(str,x,y,instr) ： 將字串 str 從指定 x 的位置開始， 取 y 個長度的字串替換為 instr，
- LTRIM(str) 和 RTRIM(str) 分別表示去掉字串 str 左側和右側的空格
- REPEAT(str,x) 函式：回傳 str 重復 x 次的結果
- TRIM(str) 函式：用于去掉目標字串的空格
- SUBSTRING(str,x,y) 函式：回傳從字串 str 中第 x 位置起 y 個字符長度的字串
- LPAD(str,n,pad) 和 RPAD(str,n,pad) 函式：用字串 pad 對 str 左邊和右邊進行填充，直到長度為 n 個字符長度
- STRCMP(s1,s2) 用于比較字串 s1 和 s2 的 ASCII 值大小，如果 s1 < s2，則回傳 -1；如果 s1 = s2 ，回傳 0 ；如果 s1 > s2 ，回傳 1，
- REPLACE(str,a,b) : 用字串 b 替換字串 str 種所有出現的字串 a
數值函式
MySQL 支持數值函式，這些函式能夠處理很多數值運算，下面我們一起來學習一下 MySQL 中的數值函式，下面是所有的數值函式
| 函式 | 功能 |
|---|---|
| ABS | 回傳絕對值 |
| CEIL | 回傳大于某個值的最大整數值 |
| MOD | 回傳模 |
| ROUND | 四舍五入 |
| FLOOR | 回傳小于某個值的最大整數值 |
| TRUNCATE | 回傳數字截斷小數的結果 |
| RAND | 回傳 0 - 1 的隨機值 |
下面我們還是以實踐為主來聊一聊這些用法
- ABS(x) 函式：回傳 x 的絕對值
- CEIL(x) 函式： 回傳大于 x 的整數
- MOD(x,y)，對 x 和 y 進行取模操作
- ROUND(x,y) 回傳 x 四舍五入后保留 y 位小數的值；如果是整數，那么 y 位就是 0 ；如果不指定 y ，那么 y 默認也是 0 ，
- FLOOR(x) : 回傳小于 x 的最大整數，用法與 CEIL 相反
- TRUNCATE(x,y): 回傳數字 x 截斷為 y 位小數的結果， TRUNCATE 知識截斷，并不是四舍五入，
- RAND() ：回傳 0 到 1 的隨機值
日期和時間函式
日期和時間函式也是 MySQL 中非常重要的一部分，下面我們就來一起認識一下這些函式
| 函式 | 功能 |
|---|---|
| NOW | 回傳當前的日期和時間 |
| WEEK | 回傳一年中的第幾周 |
| YEAR | 回傳日期的年份 |
| HOUR | 回傳小時值 |
| MINUTE | 回傳分鐘值 |
| MONTHNAME | 回傳月份名 |
| CURDATE | 回傳當前日期 |
| CURTIME | 回傳當前時間 |
| UNIX_TIMESTAMP | 回傳日期 UNIX 時間戳 |
| DATE_FORMAT | 回傳按照字串格式化的日期 |
| FROM_UNIXTIME | 回傳 UNIX 時間戳的日期值 |
| DATE_ADD | 回傳日期時間 + 上一個時間間隔 |
| DATEDIFF | 回傳起始時間和結束時間之間的天數 |
下面結合示例來講解一下每個函式的使用
- NOW(): 回傳當前的日期和時間
- WEEK(DATE) 和 YEAR(DATE) ：前者回傳的是一年中的第幾周，后者回傳的是給定日期的哪一年
- HOUR(time) 和 MINUTE(time) : 回傳給定時間的小時，后者回傳給定時間的分鐘
- MONTHNAME(date) 函式：回傳 date 的英文月份
- CURDATE() 函式：回傳當前日期，只包含年月日
- CURTIME() 函式：回傳當前時間，只包含時分秒
- UNIX_TIMESTAMP(date) : 回傳 UNIX 的時間戳
- FROM_UNIXTIME(date) : 回傳 UNIXTIME 時間戳的日期值，和 UNIX_TIMESTAMP 相反
- DATE_FORMAT(date,fmt) 函式：按照字串 fmt 對 date 進行格式化，格式化后按照指定日期格式顯示
具體的日期格式可以參考這篇文章 https://blog.csdn.net/weixin_38703170/article/details/82177837
我們演示一下將當前日期顯示為年月日的這種形式，使用的日期格式是 %M %D %Y，
- DATE_ADD(date, interval, expr type) 函式：回傳與所給日期 date 相差 interval 時間段的日期
interval 表示間隔型別的關鍵字，expr 是運算式，這個運算式對應后面的型別，type 是間隔型別，MySQL 提供了 13 種時間間隔型別
| 運算式型別 | 描述 | 格式 |
|---|---|---|
| YEAR | 年 | YY |
| MONTH | 月 | MM |
| DAY | 日 | DD |
| HOUR | 小時 | hh |
| MINUTE | 分 | mm |
| SECOND | 秒 | ss |
| YEAR_MONTH | 年和月 | YY-MM |
| DAY_HOUR | 日和小時 | DD hh |
| DAY_MINUTE | 日和分鐘 | DD hh : mm |
| DAY_SECOND | 日和秒 | DD hh ：mm ：ss |
| HOUR_MINUTE | 小時和分 | hh:mm |
| HOUR_SECOND | 小時和秒 | hh:ss |
| MINUTE_SECOND | 分鐘和秒 | mm:ss |
- DATE_DIFF(date1, date2) 用來計算兩個日期之間相差的天數
查看離 2021 - 01 - 01 還有多少天
流程函式
流程函式也是很常用的一類函式，用戶可以使用這類函式在 SQL 中實作條件選擇，這樣做能夠提高查詢效率，下表列出了這些流程函式
| 函式 | 功能 |
|---|---|
| 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 |
其他函式
除了我們介紹過的字串函式、日期和時間函式、流程函式，還有一些函式并不屬于上面三類函式，它們是
| 函式 | 功能 |
|---|---|
| VERSION | 回傳當前資料庫的版本 |
| DATABASE | 回傳當前資料庫名 |
| USER | 回傳當前登陸用戶名 |
| PASSWORD | 回傳字串的加密版本 |
| MD5 | 回傳 MD5 值 |
| INET_ATON(IP) | 回傳 IP 地址的數字表示 |
| INET_NTOA(num) | 回傳數字代表的 IP 地址 |
下面來看一下具體的使用
- VERSION: 回傳當前資料庫版本
- DATABASE: 回傳當前的資料庫名
- USER : 回傳當前登錄用戶名
- PASSWORD(str) : 回傳字串的加密版本，例如
- MD5(str) 函式：回傳字串 str 的 MD5 值
- INET_ATON(IP): 回傳 IP 的網路位元組序列
- INET_NTOA(num)函式：回傳網路位元組序列代表的 IP 地址，與 INET_ATON 相對
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/22900.html
標籤：MySQL