文章目錄

一、按關鍵字排序
- 1.1 語法結構
二、對結果進行分組
- 2.1 語法結構
三、限制結果條目
- 3.1 語法結構
- 3.2 環境創造
四、AS的用法
- 4.1 設定別名
- 4.2 作為連接陳述句
五、常用通配符的用法
- 5.1 %的用法
- 5.2 _的用法
- 5.3 兩者結合使用
六、子查詢
七、查看表的狀態
八、創建視圖（虛擬表)
九、結果集
十、exists的用法
十一、NULL值
- 11.1 給表添加屬性
- 11.2 在統計時null值不會進入統計
- 11.3 在統計時空值會進入統計
十二、正則運算式
十三、運算子
- 13.1 算術運算子
- 13.2 比較運算子
十四、比較運算子
- 14.1 least 求最小值 Greatest求最大值
- 14.2 IN NOT IN
- 14.3 LIKE NOT LIKE
十五、邏輯運算子
- 15.1 邏輯非
- 15.2 邏輯與
- 16.3 邏輯或
- 15.4 邏輯異或
十六、位運算子
- 16.1 向左位移運算
- 16.2 向右位移運算
十七、運算子的優先級
十八、連接查詢
- 18.1 內連接查詢
- 18.2 左連接查詢
- 18.3 右連接查詢
十九、數學函式
- 19.1 abs(x) 回傳x的絕對值
- 19.2 rand() 回傳0-1的亂數
- 19.3 mod(x,y）回傳x除以y以后的余數
- 19.4 power(x,y) 回傳x的y次方
- 19.5 round(x) 回傳離x最近的整數
- 19.6 sqrt(x) 回傳x的平方根
- 19.7 truncate(x,y) 回傳數字x截斷為y位小數的值
- 19.8 ceil(x)、floor(x)回傳大于或等于x的最小整數
二十、聚合函式
- 20.1 avg()回傳指定列的平均值
- 20.2 count()回傳指定列中非NULL值的個數
- 20.3 min()回傳指定列的最小值
- 20.4 max()回傳指定列的最大值
- 20.5 sum()回傳指定列的所有值之和
二十一、字串函式
- 21.1 length(x) 回傳字串 x 的長度
- 21.2 trim() 回傳去除指定格式的值
- 21.3 concat(x,y) 將提供的引數 x 和 y 拼接成一個字串
- 21.4 upper(x)、lower(x) 將字串 x 的所有字母變成大寫字母
- 21.5 left(x,y) 、right(x,y)回傳字串 x 的前 y 個字符
- 21.6 repeat(x,y) 將字串 x 重復 y 次
- 21.7 space(x) 回傳 x 個空格
- 21.8 replace(x,y,z) 將字串 z 替代字串 x 中的字串 y
- 21.9 strcmp(x,y) 比較 x 和 y，回傳的值可以為-1,0,1
- - 21.9.1 substring(x,y,z) 獲取從字串 x 中的第 y 個位置開始長度為 z 的字串
  - 21.9.2 reverse(x）將字串 x 反轉

一、按關鍵字排序

使用SELECT陳述句可以將需要的資料從MySQL資料庫中查詢出來，如果對查詢的結果進行排序，該如何去實作呢？
可以使用 ORDER BY 陳述句來完成排序，并最終將排序后的結果回傳給用戶，這個陳述句的排序不光可以針對某一個欄位，
也可以針對多個欄位，以下就是MySQL中ORDER BY陳述句的語法結構，

1.1 語法結構

排序的欄位可以根據具體需求進行選擇，沒有限制，排序的關鍵字可以使用 ASC 或者DESC，ASC 是按照升序進行排序的，
是默認的排序方式，即 ASC 可以省略，SELECT 陳述句中如果沒有指定具體的排序方式，則默認按 ASC 方式進行排序，
DESC 是按降序方式進行排列，

SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

當然 ORDER BY 前面也可以使用 WHERE 子句對查詢結果進一步過濾，
例如，執行以下操作可查詢等級大于等于 45 級的用戶，并按降序進行排序，

create database player;
use player;
create table player (id int(4) not null,name varchar(10) not null,level int(3) not null,primary key (`id`));
insert into player (id,name,level) values ('30','搶寶真多呀',47);
insert into player (id,name,level) values ('15','新五皇·白胡子',46);
insert into player (id,name,level) values ('63','新五皇–敬神',46);
insert into player (id,name,level) values ('199','D 丶狙擊王',46);
insert into player (id,name,level) values ('298','唐三',46);
insert into player (id,name,level) values ('51','新五皇·暴雪',45);
insert into player (id,name,level) values ('272','D 丶搶人頭輔助',45);


mysql> select id,name,level from player where level>=45 order by level desc;
+-----+----------------------+-------+
| id  | name                 | level |
+-----+----------------------+-------+
|  30 | 搶寶真多呀           |    47 |
|  15 | 新五皇·白胡子        |    46 |
|  63 | 新五皇–敬神          |    46 |
| 199 | D 丶狙擊王           |    46 |
| 298 | 唐三                 |    46 |
|  51 | 新五皇·暴雪          |    45 |
| 272 | D 丶搶人頭輔助       |    45 |
+-----+----------------------+-------+
7 rows in set (0.00 sec)

對于排序要求，多數情況下一個欄位就可以實作，其實 ORDER BY 陳述句也可以使用多個欄位來進行排序，
當排序的第一個欄位相同的記錄有多條的情況下，這些多條的記錄再按照第二個欄位進行排序，
例如，執行以下操作可查詢等級在 45 級及以上的用戶，并以 level 降序排列和 id 降序排列，

mysql> select id,name,level from player where level>=45 order by level desc,id desc;
+-----+----------------------+-------+
| id  | name                 | level |
+-----+----------------------+-------+
|  30 | 搶寶真多呀           |    47 |
| 298 | 唐三                 |    46 |
| 199 | D 丶狙擊王           |    46 |
|  63 | 新五皇–敬神          |    46 |
|  15 | 新五皇·白胡子        |    46 |
| 272 | D 丶搶人頭輔助       |    45 |
|  51 | 新五皇·暴雪          |    45 |
+-----+----------------------+-------+
7 rows in set (0.00 sec)

以上結果不難看出order by 后面第一個引數優先級高，level先進行倒敘排序，然后是id進行排序，并且level有一樣的值的記錄時，id是在level值一樣的記錄里進行倒敘排列，

order by 使用時要注意

ORDER BY 后面跟多個欄位時，欄位之間使用英文逗號隔開，
優先級是按先后順序而定，下面以A和B分別表示兩個欄位，
##ORDER BY A,B desc 指A用升序，B用降序；##
##ORDER BY A asc,B desc 指A用升序，B用降序；##
##ORDER BY A desc,B desc 指A用降序，B用降序；##

二、對結果進行分組

通過 SQL 查詢出來的結果，還可以對其進行分組，使用 GROUP BY 陳述句來實作，
GROUP BY 從字面上看，是以 BY 后面的內容對查詢出的資料進行分組，就是將一個“資料集”
劃分成若干個“小區域”，然后針對這些個“小區域”進行資料處理，
GROUP BY通常都是結合聚合函式一起使用的，常用的聚合函式包括：
計數（COUNT）、求和（SUM）、求平均數（AVG）、最大值（MAX）、最小值（MIN），
這些聚合函式的用法在后面函式小節會有更詳細的講解，GROUP BY 分組的時候可以按一個或多個欄位對結果進行分組處理，

2.1 語法結構

SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator valueGROUP BY column_name;

GROUP BY除了配合聚合函式一起使用外，還可以引入WHERE子句，首先通過WHERE過濾掉一部分不符合需求的查詢結果，
然后再對結果進行分組，如果有排序的需求，也可以引入ORDER BY陳述句，例如，執行以下操作即可統計等級在 45 級及以上，
以等級為分組，每個等級有多少人，

mysql> select count(name),level from player where level>=45 group by level;
+-------------+-------+
| count(name) | level |
+-------------+-------+
|           2 |    45 |
|           4 |    46 |
|           1 |    47 |
+-------------+-------+
3 rows in set (0.00 sec)

從以上結果可以看出，每個等級的人數已經統計出來了，但是數量是無序的，如果結果條目變得更多，
很難一眼看出哪個等級的人數是最多的，所以需要對數量進行排序，而GROUP BY 結合 ORDER BY
即可實作分組并排序的查詢，例如，查詢等級在 45 級及以上，按等級進行分組，并將每個等級的人
數按降序排序，具體操作如下，

mysql> select count(name),level from player where level>=45 group by level order by count(name) desc;
+-------------+-------+
| count(name) | level |
+-------------+-------+
|           4 |    46 |
|           2 |    45 |
|           1 |    47 |
+-------------+-------+
3 rows in set (0.00 sec)

排序后的結果一目了然，方便用戶獲取用戶最多的等級資訊，

三、限制結果條目

在使用 MySQL SELECT 陳述句進行查詢時，結果集回傳的是所有匹配的記錄，有時候僅需要回傳第
一行或者前幾行，這時候就需要用到 LIMIT 子句，

3.1 語法結構

SELECT column1, column2, ... FROM table_name LIMIT [offset,] number

LIMIT 的第一個引數是位置偏移量（可選引數），是設定 MySQL 從哪一行開始顯示，
如果不設定第一個引數，將會從表中的第一條記錄開始顯示，需要注意的是，
第一條記錄的位置偏移量是 0，第二條是 1，以此類推，第二個引數是設定回傳記錄行的最大數目，
如果 SELECT 查詢出的結果記錄比較多，用戶查看不是很方便，這個時候可以回傳固定的、有限的記錄數量，
使用 MySQL 資料庫的 LIMIT 子句即可實作，LIMIT 子句是一種簡單的分頁方法，
它的使用減少了資料結果的回傳時間，提高了執行效率，也解決了由于資料量過大從而導致的性能問題，
例如，執行以下操作即可查詢表的前 3 個用戶的資訊，

3.2 環境創造

create table kgc（
-> id int(4) not null primary key,
-> user_name char(10) not null,
-> level int(4) not null,
-> hobby int(4) not null);


insert into kgc (id,user_name,level,hobby) values ('1','zhangshan','88',1);
insert into kgc (id,user_name,level,hobby) values ('2','lisi','77',2);
insert into kgc (id,user_name,level,hobby) values ('3','wangwu','66',1);
insert into kgc (id,user_name,level,hobby) values ('4','zhaoliu','66',3);
insert into kgc (id,user_name,level,hobby) values ('5','tianqi','90',2);
insert into kgc (id,user_name,level,hobby) values ('6','wangba','60',2);

查詢前3個用戶的資訊

mysql> select * from kgc limit 3;
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  1 | zhangshan |    88 |     1 |
|  2 | lisi      |    77 |     2 |
|  3 | xiaoming  |    66 |     1 |
+----+-----------+-------+-------+
3 rows in set (0.00 sec)

查詢第三行后面的兩行

mysql> select * from kgc limit 3,2;
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  4 | zhaoliu   |    66 |     3 |
|  5 | tianqi    |    90 |     2 |
+----+-----------+-------+-------+
2 rows in set (0.00 sec)

四、AS的用法

4.1 設定別名

在MySQL查詢時，當表的名字比較長或者表內某些欄位比較長時，為了方便書寫或者多次使用相同的表，
可以給欄位列或表設定別名，使用的時候直接使用別名，簡潔明了，增強可讀性，設定別名使用AS陳述句，
列的別名語法結構：

mysql> select i.user_name as 姓名,i.level as 成績 from kgc as i;
+-----------+--------+
| 姓名      | 成績   |
+-----------+--------+
| zhangshan |     88 |
| lisi      |     77 |
| xiaoming  |     66 |
| zhaoliu   |     66 |
| tianqi    |     90 |
| wangba    |     60 |
+-----------+--------+
6 rows in set (0.00 sec)

4.2 作為連接陳述句

as可作為連接陳述句，可克隆別的表結構，克隆后的表和原先的表資料型別是一樣的，但是約束條件不能克隆，

mysql> create table tmp as select * from kgc where  level>=80;
mysql> select * from tmp;
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  1 | zhangshan |    88 |     1 |
|  5 | tianqi    |    90 |     2 |
+----+-----------+-------+-------+
2 rows in set (0.00 sec)

五、常用通配符的用法

通配符主要用于替換字串中的部分字符，通過部分字符的匹配將相關結果查詢出來，
通常通配符都是跟 LIKE 一起使用的，并協同 WHERE 子句共同來完成查詢任務，
常用的通配符有兩個，分別是：

%：百分號表示零個、一個或多個字符
_：下劃線表示單個字符
like的作用是模糊查詢

5.1 %的用法

mysql> select * from kgc where user_name like 'z%';
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  1 | zhangshan |    88 |     1 |
|  4 | zhaoliu   |    66 |     3 |
+----+-----------+-------+-------+
2 rows in set (0.00 sec)

5.2 _的用法

mysql> select * from kgc where user_name like 'l_s_';
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  2 | lisi      |    77 |     2 |
+----+-----------+-------+-------+
1 row in set (0.00 sec)

5.3 兩者結合使用

mysql> select id,user_name,level from kgc where user_name like '_es%';
+----+-----------+-------+
| id | user_name | level |
+----+-----------+-------+
|  7 | leslieF   |    70 |
+----+-----------+-------+
1 row in set (0.00 sec)

六、子查詢

子查詢也被稱作內查詢或者嵌套查詢，是指在一個查詢陳述句里面還嵌套著另一個查詢陳述句子查詢陳述句是先于主查詢陳述句被執行的，其結果作為外層的條件回傳給主查詢進行下一步的查詢過濾，子查詢不僅可以在 SELECT 陳述句中使用，在 INERT、UPDATE、DELETE中也同樣適用，在嵌套的時候，子查詢內部還可以再次嵌套新的子查詢，也就是說可以多層嵌套，
在開始實際的舉例之前，先來學習一下IN 這個運算子的用法，IN用來判斷某個值是否
##在給定的結果集中，通常結合子查詢來使用，IN 的語法結構如下##

<運算式> [NOT] IN <子查詢>

```bash
mysql> select * from kgc where id in (1,3,5,7)
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  1 | zhangshan |    88 |     1 |
|  3 | xiaoming  |    66 |     1 |
|  5 | tianqi    |    90 |     2 |
|  7 | leslieF   |    70 |     2 |
+----+-----------+-------+-------+
4 rows in set (0.00 sec)

挨個遍歷資料，但是沒有的資料不能顯示，內部陳述句執行的結果作為外部陳述句執行的條件

mysql> create table num (id int(4));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into num values (1),(3),(5),(7);
Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from num;
+------+
| id   |
+------+
| 1 |
| 3 |
| 5 |
| 7 |
+------+
4 rows in set (0.00 sec)

內外層資料型別必須統一
否則會報錯在這里插入圖片描述
嵌套舉例

mysql> select * from kgc where id in (select id from num);
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  1 | zhangshan |    88 |     1 |
|  3 | xiaoming  |    66 |     1 |
|  5 | tianqi    |    90 |     2 |
|  7 | leslieF   |    70 |     2 |
+----+-----------+-------+-------+
4 rows in set (0.00 sec)

七、查看表的狀態

可查詢表中幾乎一切資訊，包括存盤引擎

show table status;

八、創建視圖（虛擬表)

視圖是真實表的一個映射，只具有讀的功能，而且只能讀往視圖中添加的資料

mysql> create  view v_score as select * from kgc  where level >= 80;
Query OK, 0 rows affected (0.00 sec)
mysql> select  * from   v_score；
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  1 | zhangshan |    88 |     1 |
|  5 | tianqi    |    90 |     2 |
+----+-----------+-------+-------+
2 rows in set (0.01 sec)

九、結果集

這就是個結果集
在這里插入圖片描述

想要讀取結果集當中的某個屬性，得給結果集起個別名，將其視為一個整體
from 后面跟結果集就要加別名

錯誤示范：

mysql> select id from (select id,user_name from kgc);
ERROR 1248 (42000): Every derived table must have its own alias

mysql> select a.id from (select id,user_name from kgc)a;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
7 rows in set (0.00 sec)

十、exists的用法

exists 是測驗子陳述句中的數值存不存在的意思，下例陳述句的意思是，如果表中存在zhangshan，就統計表中的記錄條數

mysql> select count(*) from kgc where exists (select * from kgc where user_name='zhangshan');
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)

輸入一個不存在的用戶名，測驗能否統計記錄

mysql> select count(*) from kgc where exists (select * from kgc where user_name='xxxoo');
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

十一、NULL值

表示缺失的值
與數字0或者空白（spaces)是不同的
使用IS NULL 或 IS NOT NULL 進行判斷
NULL值和空值的區別
空值長度為0，不占空間；NULL值的長度為NULL，占用空間
IS NULL 無法判斷空值
空值使用"=“或者”<>"來處理
COUNT（）計算時，NULL會忽略，空值會加入計算

11.1 給表添加屬性

陳述句結構

alter table 表名 add column 屬性名 資料型別（int)等；

11.2 在統計時null值不會進入統計

創建測驗環境
mysql> alter table kgc add column addr varchar(50);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from kgc;
+----+-----------+-------+-------+------+
| id | user_name | level | hobby | addr |
+----+-----------+-------+-------+------+
|  1 | zhangshan |    88 |     1 | NULL |
|  2 | lisi      |    77 |     2 | NULL |
|  3 | xiaoming  |    66 |     1 | NULL |
|  4 | zhaoliu   |    66 |     3 | NULL |
|  5 | tianqi    |    90 |     2 | NULL |
|  6 | wangba    |    60 |     2 | NULL |
|  7 | leslieF   |    70 |     2 | NULL |
+----+-----------+-------+-------+------+
7 rows in set (0.00 sec)
成功添加進了一個屬性，設定可以為空便于測驗

將所有level屬性值大于70的 addr屬性值變為nj
mysql> update kgc set addr='nj' where level >= 70;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0
mysql> select * from kgc;
+----+-----------+-------+-------+------+
| id | user_name | level | hobby | addr |
+----+-----------+-------+-------+------+
|  1 | zhangshan |    88 |     1 | nj   |
|  2 | lisi      |    77 |     2 | nj   |
|  3 | xiaoming  |    66 |     1 | NULL |
|  4 | zhaoliu   |    66 |     3 | NULL |
|  5 | tianqi    |    90 |     2 | nj   |
|  6 | wangba    |    60 |     2 | NULL |
|  7 | leslieF   |    70 |     2 | nj   |
+----+-----------+-------+-------+------+
7 rows in set (0.00 sec)

測驗nullz值是否計入統計
mysql> select count(addr) from kgc;
+-------------+
| count(addr) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)
不計入

11.3 在統計時空值會進入統計

將一行null值的記錄修改為空值，測驗是否會計入統計
mysql> update kgc set addr='' where user_name='wangba';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select count(addr) from kgc;
+-------------+
| count(addr) |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)

測驗
mysql> select count(addr) from kgc;
+-------------+
| count(addr) |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)

十二、正則運算式

MySQL 正則運算式通常是在檢索資料庫記錄的時候，根據指定的匹配模式匹配記錄中符合要求的特殊字串，
MySQL 的正則運算式使用 REGEXP 這個關鍵字來指定正則運算式的匹配模式，REGEXP 運算子所支持的匹配模式

正則運算式過濾開頭為z的記錄

mysql> select * from kgc where user_name regexp '^z';
+----+-----------+-------+-------+------+
| id | user_name | level | hobby | addr |
+----+-----------+-------+-------+------+
|  1 | zhangshan |    88 |     1 | nj   |
|  4 | zhaoliu   |    66 |     3 | NULL |
+----+-----------+-------+-------+------+
2 rows in set (0.00 sec)

過濾不含有xyz字符的記錄
這里是指連續匹配

+----+-----------+-------+-------+------+
| id | user_name | level | hobby | addr |
+----+-----------+-------+-------+------+
|  1 | zhangshan |    88 |     1 | nj   |
|  2 | lisi      |    77 |     2 | nj   |
|  3 | xiaoming  |    66 |     1 | NULL |
|  4 | zhaoliu   |    66 |     3 | NULL |
|  5 | tianqi    |    90 |     2 | nj   |
|  6 | wangba    |    60 |     2 |      |
|  7 | leslieF   |    70 |     2 | nj   |
+----+-----------+-------+-------+------+
7 rows in set (0.01 sec)

十三、運算子

MySQL 的運算子用于對記錄中的欄位值進行運算，MySQL 的運算子共有四種，
分別是：算術運算子、比較運算子、邏輯運算子和位運算子，下面分別對這四種運算子進行說明，

13.1 算術運算子

運算子描述

加法

減法

乘法
/ 除法
% 取余數
例如，以 SELECT 命令來實作最基礎的加減乘除運算，具體操作如下所示，

mysql> select 1+2 as addition, 2-1 as subtraction, 2*3 as multiplication, 4/2 as division, 7%2 as remainder;

13.2 比較運算子

比較運算子是查詢資料記錄時經常使用的一類運算子，通過使用比較運算子可以判斷出
表中有哪些記錄是符合條件的，如果比較的結果為真則回傳 1，如果為假則回傳 0，
比較的結果如果不確定則回傳 NULL，其中字串在進行比較的時候默認是不區分大小寫的，
如果要區分大小寫可以通過 binary 關鍵字來實作，常用的比較運算子
運算子描述運算子描述
= 等于 IS NOT NULL 判斷一個值是否不為 NULL
大于 BETWEEN AND 兩者之間
< 小于 IN 在集合中
= 大于等于 LIKE 通配符匹配
<= 小于等于 GREATEST 兩個或多個引數時回傳最大值
!=或<> 不等于 LEAST 兩個或多個引數時回傳最小值
IS NULL 判斷一個值是否為 NULL REGEXP 正則運算式

MySQL當中成立的回傳值不為0，不成立為0
在這里插入圖片描述

倆個字母比的是底層的ASCll碼

mysql> select 'a'>'b';
+---------+
| 'a'>'b' |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

等于運算子在字串上的用法
比的是字符順序和字符個數

mysql> select 'abc'='abc';
+-------------+
| 'abc'='abc' |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

或的關系，當b比a大時，資料庫就默認baa大于abc，其余的不會在比較了

mysql> select 'abc'<'baa';
+-------------+
| 'abc'<'baa' |
+-------------+
|           1 |
+-------------+
1 row in set (0.01 sec)

between的用法
下面陳述句是意思是：4是不是在2和6 的中間？

mysql> select 4 between 2 and 6;
+-------------------+
| 4 between 2 and 6 |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

between是包含頭和尾的

mysql> select 2 between 2 and 6;
+-------------------+
| 2 between 2 and 6 |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

十四、比較運算子

14.1 least 求最小值 Greatest求最大值

mysql> select least(10,20,30),greatest(10,20,30);  
+-----------------+--------------------+
| least(10,20,30) | greatest(10,20,30) |
+-----------------+--------------------+
|              10 |                 30 |
+-----------------+--------------------+
1 row in set (0.00 sec)

支持小數

mysql> select least(10.1,20,30),greatest(10,20,30.1);
+-------------------+----------------------+
| least(10.1,20,30) | greatest(10,20,30.1) |
+-------------------+----------------------+
|              10.1 |                 30.1 |
+-------------------+----------------------+
1 row in set (0.00 sec)

least如果放的是字符和數值的混合資料，是不能執行成功的
greatest:自動屏蔽不同型別的資料

mysql> select least(10.1,20,30,'a'),greatest(10,20,30.1,'a');
+-----------------------+--------------------------+
| least(10.1,20,30,'a') | greatest(10,20,30.1,'a') |
+-----------------------+--------------------------+
| 0                     | 30.1                     |
+-----------------------+--------------------------+
1 row in set, 2 warnings (0.00 sec)

14.2 IN NOT IN

IN 判斷一個值是否在對應的串列中，如果是回傳 1，否則回傳 0，
NOT IN 判斷一個值是否不在對應的串列中，如果不是回傳 1，否則回傳 0，

mysql> select 97 in ('a','b','c'),'a' not in ('a','b','c');
+---------------------+--------------------------+
| 97 in ('a','b','c') | 'a' not in ('a','b','c') |
+---------------------+--------------------------+
|                   0 |                        0 |
+---------------------+--------------------------+
1 row in set, 3 warnings (0.00 sec)

14.3 LIKE NOT LIKE

LIKE 用來匹配字串，如果匹配成功則回傳 1，反之回傳 0，LIKE 支持兩種通配符：’%’ 用于匹配任意數目的字符，
而’_’只能匹配一個字符，NOT LIKE 正好跟 LIKE 相反，如果沒有匹配成功則回傳 1，反之回傳 0，

mysql> select 'bdqn' like 'bdq_','kgc' like '%c', 'etc'Not like '%th';
+--------------------+-----------------+---------------------+
| 'bdqn' like 'bdq_' | 'kgc' like '%c' | 'etc'Not like '%th' |
+--------------------+-----------------+---------------------+
|                  1 |               1 |                   1 |
+--------------------+-----------------+---------------------+
1 row in set (0.00 sec)

十五、邏輯運算子

邏輯運算子又被稱為布爾運算子，通常用來判斷運算式的真偽，如果為真回傳 1，否則回傳 0，
真和假也可以用 TRUE 和 FALSE 表示，MySQL 中支持使用的邏輯運算子有四種，
運算子描述
NOT 或！邏輯非
AND 或 && 邏輯與
OR 或 || 邏輯或
XOR 邏輯異或

15.1 邏輯非

邏輯運算子中最簡單的運算子就是邏輯非，邏輯非使用 NOT 或！表示，邏輯非將跟在它后面的邏輯測驗取反，
把真變為假，把假變為真，如果 NOT 后面的運算元為 0 時，所得值為 1；如果運算元為非 0 時，所得值為 0；
如果運算元為 NULL 時，所得值為 NULL，

mysql> select not 0,!2,!(4-4);
+-------+----+--------+
| not 0 | !2 | !(4-4) |
+-------+----+--------+
|     1 |  0 |      1 |
+-------+----+--------+
1 row in set (0.00 sec)

15.2 邏輯與

邏輯與通常用于判斷兩個值或多個值的有效性，如果所有值都是真回傳 1，否則回傳 0
邏輯與使用 AND 或者&&表示，

0與NULL時結果為0
1與NULL時結果為NULL

mysql> select 2&&3,0&&1,0&&NULL,1 and NULL;
+------+------+---------+------------+
| 2&&3 | 0&&1 | 0&&NULL | 1 and NULL |
+------+------+---------+------------+
|    1 |    0 |       0 |       NULL |
+------+------+---------+------------+
1 row in set (0.00 sec)

16.3 邏輯或

邏輯或表示包含的運算元，任意一個為非零值并且不是 NULL 值時，回傳 1，否則回傳0，
邏輯或通常使用 OR 或者||來表示，

注意：最好用OR，ll有時不能準確表達或的關系，有時候會合并

mysql> select 2 or 3, 2||0;
+--------+------+
| 2 or 3 | 2||0 |
+--------+------+
|      1 | 20   |
+--------+------+
1 row in set (0.05 sec)

mysql> select 2 or 3, 2 or 0;
+--------+--------+
| 2 or 3 | 2 or 0 |
+--------+--------+
|      1 |      1 |
+--------+--------+
1 row in set (0.00 sec)

字串無法識別

mysql> select 2 or 3,'a' or 'c';
+--------+------------+
| 2 or 3 | 'a' or 'c' |
+--------+------------+
|      1 |          0 |
+--------+------------+
1 row in set, 2 warnings (0.00 sec)

邏輯或 1和NULL在一起時，輸出結果還是 1

mysql> select 0 or NULL,1 or NULL;
+-----------+-----------+
| 0 or NULL | 1 or NULL |
+-----------+-----------+
|      NULL |         1 |
+-----------+-----------+
1 row in set (0.00 sec)

15.4 邏輯異或

兩個非 NULL 值的運算元，如果兩者都是 0 或者都是非 0，則回傳 0；如果一個為 0，另一個為非 0，則回傳結果為 1；
當任意一個值為 NULL 時，回傳值為 NULL，

mysql> select 0 xor 3,0 xor 0,0 xor NULL,1 xor NULL;
+---------+---------+------------+------------+
| 0 xor 3 | 0 xor 0 | 0 xor NULL | 1 xor NULL |
+---------+---------+------------+------------+
|       1 |       0 |       NULL |       NULL |
+---------+---------+------------+------------+
1 row in set (0.00 sec)

十六、位運算子

位運算子實際上是對二進制數進行計算的運算子，MySQL 內位運算會先將運算元變成二進制格式，然后進行位運算，
最后在將計算結果從二進制變回到十進制格式，方便用戶查看，MySQL 支持 6 種位運算子
運算子描述
& 按位與
| 按位或
~ 按位取反
^ 按位異或
<< 按位左移 >>按位右移

例子

取反1的二進制，取5的二進制，二者進行且的計算
1的二進制：0001
1的取反：1110
5的二進制：0101
1110
0101
將上面二者用且關系計算得出
0100就是4

mysql> select 5&~1;
+------+
| 5&~1 |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

16.1 向左位移運算

mysql> select 1<<2;
0001
0100    4
+------+
| 1<<2 |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

16.2 向右位移運算

向右移動位數，多余的位數直接刪掉

15>>2
1111
0011 3
mysql> select 15>>2;
+-------+
| 15>>2 |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

十七、運算子的優先級

優先級    運算子                   優先級      運算子
1          ！                      8          |
2          ~                       9          =,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
3          ^                      10          BETWEEN,CASE,WHEN,THEN,ELSE
4          * , /(DIV), %(MOD)     11          NOT
5          +,-                    12          &&,AND
6          >>,<<                  13          ||,OR,XOR
7          &                      14          :=

十八、連接查詢

MySQL 的連接查詢，通常都是將來自兩個或多個表的行結合起來，基于這些表之間的共同欄位，進行資料的拼接，
首先，要確定一個主表作為結果集，然后將其他表的行有選擇性的連接到選定的主表結果集上，使用較多的連接查詢包括：
內連接、左連接和右連接，

18.1 內連接查詢

將兩個表的id 連接起來顯示id和user_name 屬性

mysql> select kgc.id,kgc.user_name from kgc inner join num on kgc.id=num.id;
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | zhangshan |
|  3 | xiaoming  |
|  5 | tianqi    |
|  7 | leslieF   |
+----+-----------+
4 rows in set (0.00 sec)

用內連接的方法將kgc表中的id 和 uer_name屬性顯示出來，并且顯示出hob表中的hob_name屬性，將kgc表中的hobby屬性和hob表中的id連接一起.

mysql> create table hob (id int(3) not null primary key auto_increment,hob_name varchar(10) not null);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into hob(hob_name) values('云計算'),('大資料'),('人工智能');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select kgc.id,kgc.user_name,hob.hob_name from kgc inner join hob on kgc.hobby=hob.id;
+----+-----------+--------------+
| id | user_name | hob_name     |
+----+-----------+--------------+
|  1 | zhangshan | 云計算       |
|  2 | lisi      | 大資料       |
|  3 | xiaoming  | 云計算       |
|  4 | zhaoliu   | 人工智能     |
|  5 | tianqi    | 大資料       |
|  6 | wangba    | 大資料       |
|  7 | leslieF   | 大資料       |
+----+-----------+--------------+
7 rows in set (0.00 sec)

mysql> select * from kgc;
+----+-----------+-------+-------+------+
| id | user_name | level | hobby | addr |
+----+-----------+-------+-------+------+
|  1 | zhangshan |    88 |     1 | nj   |
|  2 | lisi      |    77 |     2 | nj   |
|  3 | xiaoming  |    66 |     1 | NULL |
|  4 | zhaoliu   |    66 |     3 | NULL |
|  5 | tianqi    |    90 |     2 | nj   |
|  6 | wangba    |    60 |     2 |      |
|  7 | leslieF   |    70 |     2 | nj   |
+----+-----------+-------+-------+------+
7 rows in set (0.00 sec)

18.2 左連接查詢

MySQL 除了內連接，還可以使用外連接，區別于 MySQL 外連接是將表分為基礎表和參考表，再依據基礎表回傳滿足條件或不滿足條件的記錄，外連接按照連接時表的順序來分，
有左連接和右連接之分，
左連接也可以被稱為左外連接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 關鍵字來表示，左連接以左側表為基礎表，接收左表的所有行，
并用這些行與右側參考表中的記錄進行匹配，也就是說匹配左表中的所有行以及右表中符合條件的行，

左連接是左表的優先級大，所以會根據左表的hobby進行排序，匹配右表的值

mysql> insert into hob(hob_name) values('云開發'),('云交付');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select kgc.id,kgc.user_name,hob.hob_name from kgc left join hob on kgc.hobby=hob.id;
+----+-----------+--------------+
| id | user_name | hob_name     |
+----+-----------+--------------+
|  1 | zhangshan | 云計算       |
|  3 | xiaoming  | 云計算       |
|  2 | lisi      | 大資料       |
|  5 | tianqi    | 大資料       |
|  6 | wangba    | 大資料       |
|  7 | leslieF   | 大資料       |
|  4 | zhaoliu   | 人工智能     |
+----+-----------+--------------+
7 rows in set (0.00 sec)

18.3 右連接查詢

右連接也被稱為右外連接，在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 關鍵字來表示，右連接跟左連接正好相反，它是以右表為基礎表，用于接收右表中的所有行，并用這些記錄與左表中的行進行匹配，也就是說匹配右表中的每一行及左表中符合條件的記錄，

以右表為主，顯示右表的全部資訊，左表資訊有則匹配
給右表添加一段屬性 addr

mysql> alter table hob add column addr varchar(50);
mysql> select kgc.id,kgc.user_name,hob.hob_name,hob.addr from kgc right join hob on kgc.hobby=hob.id;
+------+-----------+--------------+------+
| id   | user_name | hob_name     | addr |
+------+-----------+--------------+------+
|    1 | zhangshan | 云計算       | NULL |
|    2 | lisi      | 大資料       | NULL |
|    3 | xiaoming  | 云計算       | NULL |
|    4 | zhaoliu   | 人工智能     | NULL |
|    5 | tianqi    | 大資料       | NULL |
|    6 | wangba    | 大資料       | NULL |
|    7 | leslieF   | 大資料       | NULL |
| NULL | NULL      | 云開發       | NULL |
| NULL | NULL      | 云交付       | NULL |
+------+-----------+--------------+------+
9 rows in set (0.00 sec)

十九、數學函式

MySQL提供了實作各種功能的函式
常用的函式分類
1.數學函式
2.聚合函式
3.字串函式
4.日期時間函式
##常用的數學函式##
abs(x) 回傳x的絕對值
rand() 回傳0-1的亂數
mod(x,y）回傳x除以y以后的余數
power(x,y) 回傳x的y次方
round(x) 回傳離x最近的整數
round(x,y) 保留x的y位小數四舍五入后的值
sqrt(x) 回傳x的平方根
truncate(x,y) 回傳數字x截斷為y位小數的值
ceil(x) 回傳大于或等于x的最小整數
floor(x)回傳小于或等于x的最大整數

19.1 abs(x) 回傳x的絕對值

mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

19.2 rand() 回傳0-1的亂數

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.0633721929633731 |
+--------------------+
1 row in set (0.00 sec)

如何利用rand控制取值范圍？
以下取0-100的亂數

mysql> select rand()*100;
+-------------------+
| rand()*100        |
+-------------------+
| 89.42384467406556 |
+-------------------+
1 row in set (0.00 sec)

19.3 mod(x,y）回傳x除以y以后的余數

mysql> select mod(7,2);
+----------+
| mod(7,2) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

19.4 power(x,y) 回傳x的y次方

mysql> select power(2,3);
+------------+
| power(2,3) |
+------------+
|          8 |
+------------+
1 row in set (0.00 sec)

19.5 round(x) 回傳離x最近的整數

mysql> select round(1.4);
+------------+
| round(1.4) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

round(x,y) 保留x的y位小數四舍五入后的值
保留小數點后1位并且四舍五入

mysql> select round(1.49,1);
+---------------+
| round(1.49,1) |
+---------------+
|           1.5 |
+---------------+
1 row in set (0.00 sec)

19.6 sqrt(x) 回傳x的平方根

mysql> select sqrt (8);
+--------------------+
| sqrt (8)           |
+--------------------+
| 2.8284271247461903 |
+--------------------+
1 row in set (0.00 sec)

19.7 truncate(x,y) 回傳數字x截斷為y位小數的值

mysql> select truncate(1.449,2);
+-------------------+
| truncate(1.449,2) |
+-------------------+
|              1.44 |
+-------------------+
1 row in set (0.00 sec)

19.8 ceil(x)、floor(x)回傳大于或等于x的最小整數

mysql> select ceil(1.1),floor(1.1);
+-----------+------------+
| ceil(1.1) | floor(1.1) |
+-----------+------------+
|         2 |          1 |
+-----------+------------+
1 row in set (0.00 sec)

取余限制范圍 0 ，1 ，2
限制思路：0-100以內的數除以3，余數都是0 1 2 所以輸出結果也都是0-2這個范圍，ceil是大于x的最小整數

mysql> select mod(ceil(rand()*100),3);
+-------------------------+
| mod(ceil(rand()*100),3) |
+-------------------------+
|                       2 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select mod(ceil(rand()*100),3);
+-------------------------+
| mod(ceil(rand()*100),3) |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select mod(ceil(rand()*100),3);
+-------------------------+
| mod(ceil(rand()*100),3) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

二十、聚合函式

MySQL 資料庫函式中專門有一組函式是特意為庫內記錄求和或者對表中的資料進行集中概括而設計的，這些函式被稱作聚合函式，
常用的聚合函式
avg()回傳指定列的平均值
count()回傳指定列中非NULL值的個數
min()回傳指定列的最小值
max()回傳指定列的最大值
sum()回傳指定列的所有值之和

20.1 avg()回傳指定列的平均值

求kgc表中level屬性的平均值

mysql> select avg(level) from kgc;
+------------+
| avg(level) |
+------------+
|    73.8571 |
+------------+
1 row in set (0.00 sec)

20.2 count()回傳指定列中非NULL值的個數

這里的3，是作為一個值統計的

mysql> select count(3);
+----------+
| count(3) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

統計表中 name屬性值的個數

mysql> select count(user_name) from kgc;
+------------------+
| count(user_name) |
+------------------+
|                7 |
+------------------+
1 row in set (0.00 sec)

20.3 min()回傳指定列的最小值

mysql> select min(level) from kgc;
+------------+
| min(level) |
+------------+
|         60 |
+------------+
1 row in set (0.00 sec)

20.4 max()回傳指定列的最大值

mysql> select max(level) from kgc;
+------------+
| max(level) |
+------------+
|         90 |
+------------+
1 row in set (0.00 sec)

20.5 sum()回傳指定列的所有值之和

mysql> select sum(level) from kgc;
+------------+
| sum(level) |
+------------+
|        517 |
+------------+
1 row in set (0.00 sec)

二十一、字串函式

常用函式不僅包括數學函式和聚合函式，還包含字串函式，MySQL 為字串的相關操作設計了豐富的字串函式，
常用的字串函式
length(x) 回傳字串 x 的長度
trim() 回傳去除指定格式的值
concat(x,y) 將提供的引數 x 和 y 拼接成一個字串
upper(x) 將字串 x 的所有字母變成大寫字母
lower(x) 將字串 x 的所有字母變成小寫字母
left(x,y) 回傳字串 x 的前 y 個字符
right(x,y) 回傳字串 x 的后 y 個字符
repeat(x,y) 將字串 x 重復 y 次
space(x) 回傳 x 個空格
replace(x,y,z) 將字串 z 替代字串 x 中的字串 y
strcmp(x,y) 比較 x 和 y，回傳的值可以為-1,0,1
substring(x,y,z) 獲取從字串 x 中的第 y 個位置開始長度為 z 的字串
reverse(x) 將字串 x 反轉

21.1 length(x) 回傳字串 x 的長度

mysql> select length('abdc');
+----------------+
| length('abdc') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

空格也會占位置

mysql> select length(' bdc');
+----------------+
| length(' bdc') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

空值不占空間

mysql> select length('');
+------------+
| length('') |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

NULL占空間，資料顯示就叫NULL

mysql> select length(NULL);
+--------------+
| length(NULL) |
+--------------+
|         NULL |
+--------------+
1 row in set (0.00 sec)

21.2 trim() 回傳去除指定格式的值

trim 能夠去除頭尾空格

mysql> select length(trim(' abc'));
+----------------------+
| length(trim(' abc')) |
+----------------------+
|                    3 |
+----------------------+
1 row in set (0.06 sec)

中間的格式是去除不掉的

mysql> select length(trim(' a bc'));
+-----------------------+
| length(trim(' a bc')) |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.00 sec)

21.3 concat(x,y) 將提供的引數 x 和 y 拼接成一個字串

mysql> select length(concat('abc','def'));
+-----------------------------+
| length(concat('abc','def')) |
+-----------------------------+
|                           6 |
+-----------------------------+
1 row in set (0.00 sec)

21.4 upper(x)、lower(x) 將字串 x 的所有字母變成大寫字母

mysql> select upper('abc'),lower('ABC');
+--------------+--------------+
| upper('abc') | lower('ABC') |
+--------------+--------------+
| ABC          | abc          |
+--------------+--------------+
1 row in set (0.00 sec)

21.5 left(x,y) 、right(x,y)回傳字串 x 的前 y 個字符

mysql> select left('abcdefg',3),right('abcdefg',2);
+-------------------+--------------------+
| left('abcdefg',3) | right('abcdefg',2) |
+-------------------+--------------------+
| abc               | fg                 |
+-------------------+--------------------+
1 row in set (0.00 sec)

21.6 repeat(x,y) 將字串 x 重復 y 次

mysql> select repeat('abc',3);
+-----------------+
| repeat('abc',3) |
+-----------------+
| abcabcabc       |
+-----------------+
1 row in set (0.00 sec)

21.7 space(x) 回傳 x 個空格

由于單單輸入這條命令難以看到效果，所以有以下兩種方法測驗
（1）使用length便于觀看

mysql> select length(concat('abc',space(3),'abc'));
+--------------------------------------+
| length(concat('abc',space(3),'abc')) |
+--------------------------------------+
|                                    9 |
+--------------------------------------+
1 row in set (0.00 sec)

（2）用concat也能進行驗證

mysql> select concat('abc',space(3),'abc');
+------------------------------+
| concat('abc',space(3),'abc') |
+------------------------------+
| abc   abc                    |
+------------------------------+
1 row in set (0.00 sec)

21.8 replace(x,y,z) 將字串 z 替代字串 x 中的字串 y

mysql> select replace('abcdefg','bc','12');
+------------------------------+
| replace('abcdefg','bc','12') |
+------------------------------+
| a12defg                      |
+------------------------------+
1 row in set (0.00 sec)

洗掉字符

mysql> select replace('abcdefg','bc','');
+----------------------------+
| replace('abcdefg','bc','') |
+----------------------------+
| adefg                      |
+----------------------------+
1 row in set (0.00 sec)

21.9 strcmp(x,y) 比較 x 和 y，回傳的值可以為-1,0,1

mysql> select strcmp(4,5);
+-------------+
| strcmp(4,5) |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

mysql> select strcmp(5,5);
+-------------+
| strcmp(5,5) |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> select strcmp(6,5);
+-------------+
| strcmp(6,5) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

21.9.1 substring(x,y,z) 獲取從字串 x 中的第 y 個位置開始長度為 z 的字串

mysql> select substring('abcdefg',3,3);
+--------------------------+
| substring('abcdefg',3,3) |
+--------------------------+
| cde                      |
+--------------------------+
1 row in set (0.01 sec)

21.9.2 reverse(x）將字串 x 反轉

mysql> select reverse('abc');
+----------------+
| reverse('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/200211.html

標籤：其他

上一篇：mysql 資料表的增刪改查

下一篇：Centaur: A Framework for Hybrid CPU-FPGA Databases

（簡單易懂！一看就會！）MySQL高級陳述句的詳細用法