按單欄位排序

asc 升序[默認] desc 降序
語法

select 顯示用欄位 from 表名 where 判斷用欄位>=Number order by 以哪個欄位做排序 desc;

舉例：

select id from user where point>=200 order by point desc;

多欄位排序

order by 以后的引數 使用","分割優先是按先后循序而定
語法

select 欄位1 from 表名 order by 欄位2 desc,欄位3 desc;

舉例：

select id from user order by point desc,id desc;

現根據欄位1進行降序排序，在進行欄位2的降序排序
第一個欄位只有在出現相同數值的時候第二欄位才有意義

or/and

語法

select * from 表名 xx >20 and xx<=10;

and表示需要同時滿足
or表示只需要滿足其中之一
舉例：

select id from user where point > 100 and point <= 500;

嵌套/多條件

語法

select * from 表名 where xx > 10 or (xx > 15 and xx < 50);

舉例：

select id from user where point > 100 or (point > 150 and point < 500);

查詢不重復記錄

語法

select distinct 欄位 from 表名;

舉例：

select distinct id from user;

指定多欄位去重

distinct必須放在最開頭
distinct只能使用需要去重的欄位進行操作
指定多個欄位去重的話，必須要滿足這幾個欄位同時重復時才會被過濾
語法

select distinct 欄位 from 表名 where 欄位 in (select distinct 欄位 from 表名);

舉例：

select id,name from user where point in (select distinct point from user);

對結果進行分組

通過 SQL 查詢出來的結果，還可以對其進行分組，使用group by陳述句來實作，group by 通常都是結合聚合函式一起使用的，常用的聚合函式包括：計數(count) 求和(sum) 求平均數(avg) 最大值(max) 最小值(min)，group by 分組的時候可以按一個或多個欄位對結果進行分組處理

語法

select count(欄位)  from * where 表名 group by 欄位;  

舉例：

select count(name),point from user where point>=300 group by point;

對user表進行分組
篩選范圍/條件是point大于等于300的name，point相同的會默認分在一組

結合排序語法

語法

select count(欄位)  from * where 表名 group by 欄位 order by count(欄位) desc;

舉例：

select count(id),name from user group by point;

select count(id),name from user group by point order by count(id) asc;

限制條目結果

結果集回傳的是所有匹配的記錄
使用limit陳述句限制條目
位置偏移量第一條是0第二條是1以此類推
語法

select * from 表名 limit number;

舉例：

select * from user limit 2,3;

結合order by排序

select * from 表名 order by 欄位 desc limit number;

select * from user order by id desc limit 2,3;

設定別名

當表中有命名過長的欄位時，可以通過
設定別名來縮短操作

語法

select 欄位 AS 別名 from 表名;

舉例：

select name AS '名字' from user;

AS可以省略

使用AS后可以用別名代替欄位名，其中AS陳述句時可選的
之后的別名，主要為表內列或者表提供臨時的名稱
在查詢程序中使用，庫內實際的表名或欄位不會改變的

表資料別名設定示例

select a.name as 姓名,a.point as 點數 from user as a;

使用場景
1.對復雜的表進行查詢的時候，別名可以縮短查詢陳述句書寫
2.多表相連查詢的時候

AS作為連接陳述句

語法

create table 表名 as select * from 表名

舉例：

create table user_new as select * from user;

1.創建一個新的表 定義了表結構 插入表資料
2.但是約束沒有復制過來，但是如果原表設定了主鍵那么Defaut默認為0

通配符

通配符主要用于替換字串的部分字符，通過部分字符的匹配將相關結果查詢出來
%百分號表示零個或以后多個字符
_下劃線表示單個字符

查詢名字是a開頭的記錄
模糊查詢舉例：
搜索任意包含M字符的行

select * from info where name like '%M%';

搜索F后任意單個字符i后零個或多個任意字符k

select * from info where name like 'F_i%k';

子查詢

子查詢也被稱作內查詢或者嵌套查詢，是指在一個查詢陳述句里面還嵌套著另一個查詢陳述句
子查詢陳述句是先于著查詢陳述句被執行，其結果作為外層的條件回傳給主查詢進行下一步的查詢過濾

子陳述句可以與主陳述句所查詢的表相同，也可以是不同的表

不同表舉例：

select name,score from info where id in (select id from info where score>80);

主陳述句舉例：

select name,score from info where id;

子陳述句舉例：

select id from info where score > 80;

子查詢不僅可以在select陳述句中使用，在insert、update、delete中也同樣使用
支持多次嵌套
可以使用not in表示對結果集取反

子查詢-別名

先查詢表欄位[看作一個結果集]
將結果集作為一張表將那些查詢的時候
語法

select 別名.欄位 from (select 欄位 from 表) 別名;

舉例：

select a.id from (select id,name from info)a;

select * from 表名，此為標準格式，而以上的查詢陳述句，"表名"的位置其實是一個結果集，mysql并不能識別
此時給與結果集設定一個別名，并且以select a.id,name from a;的方式查詢，將此結果集視為一張表就可以正常查詢出資料了

相當于select info.id,name from info;
     select 表.欄位,欄位 from 表;

子查詢-exists

select count(*) as number from 表名 where exists(
select id from 表名 where 欄位='值'
)

exists 布林值判斷，后面的內查詢陳述句，是否成立where 之后跟條件判斷加exists之后的條件是否成立，如果成立，則正常執行count為計數 sum為求和 使用sum求和結合exists，如子查詢結果集不成立輸出null

設定模式(指定欄位) 選擇表 判斷 (選中查詢的欄位);

試圖[類似與鏡像]

用于動態保存結果集 as 可以作為把上一個表的內容復制過來
語法

create view v_xxx as select * from 表名 where 欄位>=Number;

舉例：

create view v_user as select * from info where score>60;

查看表的狀態

show table status\G

Comment:VIEW

查看視圖

select * from v_xxx;

修改原表資料[該原資料后就自動映射到試圖中]

update 表名 set 欄位='xx' where 欄位='xx';

update info set score=1 where name='Sans';

IN

顯示已知的資料
語法

select 欄位 from 表名 where 欄位 in ('值1','值2'...);

舉例：

select name from user where money in (100,200);

NULL值

NULL通過用來表示缺失的值，也就是在表中該欄位沒有值
如果在創建表時，限制某些欄位不為空，則可以使用NOT NULL關鍵字不適用則默認可以為空
在向表內插入記錄或者更新記錄時，如果該欄位沒有NOT NULL并且沒有值這時候新紀錄的該欄位將被保存為NULL，需要注意的是，NULL值與數字0或者空白的欄位是不同的，值為NULL的欄位沒有值，在SQL陳述句中，使用IS NULL可以判斷表內的某個欄位是不是NULL值，相反用IS NOT NULL可以判斷不是NULL值

NULL值和空值的區別

NULL值	空值
空值長度為0	不占用空間
NULL值的長度為null	占用空間

IS NULL無法判斷空值
空值使用=或者<>來處理(!=)
count()計算時，NULL會忽略，空值會加入計算

舉例：

檢測NULL值是否會計入統計

select count(id) from 表名;

將其中一條值改為空值

update 表名 set 欄位='' from 欄位=值;

檢測空值是否會被統計

select count(欄位) from 表名;

查詢為null的值

select * from 表名 where 欄位 is null;

查看不能null的值

select * from 表名 where 欄位 is not null;

正則運算式

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

REGEXP運算子所支持的匹配模式如下

運算子	含義
^	匹配文本的開始字符
$	匹配文本的結束字符
.	匹配任何單個字符
*	匹配零個或多個在它前面的字符
+	匹配前面的字符一次或多次
字串	匹配包含指定的字串
^a	匹配所有以a開頭的字符
a1 | a2	匹配a1或a2
[a-z]	匹配集合中任意一個字符
[^a-z]	匹配除集合中任意一個字符
{n}	匹配前面的字串n次
{n,m}	匹配前面的字串至少n次，至多m次

舉例：
匹配C開頭的內容

select id,name from info where name regexp '^C';

匹配所有n結尾的內容

select id,name,address from info where address regexp 'n$';

匹配任何單個或多個字符

select id,name from info where name regexp 'S..s';

匹配零個或多個在它前面的字符

select address from info where address regexp 'p*';

匹配town和Home

select address from info where address regexp 'town|Home';

匹配集合中的任意一個字符

select address from info where address regexp '[a,b]';

匹配集合以外的字符

select address from info where address regexp '[^lazy]';

匹配前面的字串1次

select name from info where name regexp 's{1}';

匹配前面的字符1次最多1次

select name from info where name regexp 's{1,1}';

運算子

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

演算法運算

運算子	描述
+	加法
-	減法
*	懲罰
/	除法
%	取余

在觸發運算和取余數運算中，除數不能為0，若除數是0，回傳的結果則為NULL
如果有多個運算發，按照先乘除后加減的優先級進行運算，相同優先級的運算子沒有先后順序

舉例：

select 4+5,3*3,12/2,20-20,9%9,1+8-2;

create table awsl (select 4+5,3*3,12/2,20-20,9%9,1+8-2);

比較運算子

字串的比較默認不區分大小寫，可以用binary來區分
常用比較運算子(比較物件：數字、字符)

=	等于
！=或<>	不等于
like	通配符匹配
>	大于
>=	大于等于
<	小于
<=	小于等于
IS NULL	判斷一個值是否為NULL
IS NOT NULL	判斷一個值是否不為NULL
BETWEEN AND	兩者之間
GREATEST	兩個或多個引數時回傳最大值
LEAST	兩個或多個引數時回傳最小值
IN	在集合中

等于

等號用來判斷數字、字串和運算式是否相等，如果相等則回傳1，如果不相等則回傳0，如果比較的兩者有一個值時NULL，則比較的結果就是NULL

其中字符的比較時根據ASCII碼來判斷的，如果ASCII碼相等，則表示兩個字符相同

如果ASCII碼不相等，則表示兩個字符不相同，例如字串比較
起始比較的時ASCII碼

舉例：

select 'A'+'SOH'='B';

為什么A+SOH會等于B？？
那是因為他們比較之前會先轉為ASCII碼中的十進制
轉換后：65(A)+1(SOH)=66(B)

不等于

用于針對數字、字串和運算式不相等的比較，如果不相等則回傳1如果相等則回傳0
與等于的回傳值相反，同時不等于無法用于判斷是否為null

select 'Cat'<>'Dog',1<>2,3!=3,6.9!=6, NULL<>NULL;

大于運算子用來判斷左側的運算元是否大于右側的運算元，若大于回傳1否則回傳0，他也同樣不能用于判斷NULL

小于運算子用來判斷左側的運算元是否小于右側的運算元，若小于回傳1否則回傳0，同樣他也不能用于判斷NULL

大于等于判斷左側的運算元是否大于等于右側的運算元，若大于等于回傳1，否則回傳0，同樣他也不能用于判斷NULL

小于等于判斷左側的運算元是否小于等于右側的運算元，若小于等于回傳1，否則回傳0，同樣他也不能用于判斷NULL

舉例：

select 10>9,'a'>'b',2>=3,(2+3)>=(9+6),7.74<7,'x'<='y',5<=5.5,'u'>=NULL;

判斷一個值為/不為NULL(是否為慷訓非空)

IS NULL 判斷一個值是否為NULL，如果為NULL回傳1，否則回傳0
IS NOT NULL 判斷一個值是否不為NULL，如果不為NULL回傳1，否則回傳0

select '404 ERROR Page' is null,'=)' is not null,null is null;

兩者之間(between…and…)

比較運算通常用于判斷一個值是否落在某兩個值之間
例如：判斷某數字是否在另外兩個數字之間，也可以判斷某英文字母是否
在另外兩個字母之間，具體操作，條件符合回傳1，否則回傳0
實體

select 4 between 2 and 6,5 between 6 and 8,'c' between 'a' and 'f'

數字比較，按大小排列
字母比較，從a到b排序，字母越前越小

在/不在集合中(in,not in)

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

select 3 in (1,2,3,4,5),'c' not in ('a','b','c');

通配符匹配

like用來匹配字串，如果匹配成功則回傳1，反之回傳0，like支持兩種通配符：'%'
用于匹配任意數目的字符，而’_'只能匹配一個字符，not like正好跟like相反，如果沒有匹配成功則回傳1，反之回傳0
若要判斷某字串能否匹配成功，分分字符匹配和多字符匹配，也可以判斷不匹配，具體操作如下所示

舉例：

select 'Bendy' like 'Ben__','Sans'like'%s','ak47' not like '%k5';

邏輯運算子

邏輯運算子又被稱為布爾運算子，通常用來判斷運算式的真偽，如果為真回傳1，否則回傳0，真和假也可以用TRUE和FALSE表示
MySQL中支持使用的邏輯運算子有四種

not 或 ！	邏輯非
and 或 &&	邏輯與
or	邏輯或
xor	邏輯異或

邏輯非(not 或 ！)

邏輯非將跟在它后面的值取反，如果not后面的運算元為0時所得值為1
如果運算元非0時，所得值為0，如果運算元為NULL時，所得值為NULL

舉例：

select not 2,13,not 0,!(4-4);

邏輯與

當所有運算元都為非零值并且不為NULL時，回傳值為1
當一個或多個運算元為0時，回傳值為0
運算元中有任何一個為NULL時，回傳值為NULL

舉例：

select 2 and 3,4 && 0,0 && null,1 and null;

邏輯或

當兩個運算元都為非NULL值時，如果又任意一個運算元為非零值，則回傳值為1，否則結果為0
當又一個運算元為NULL時，如果另一個運算元為非零值，則回傳值為1，否則結果為NULL
加入兩個運算元均為NULL時，則回傳值為NULL

舉例：

select 2 OR 3,0 OR null;

邏輯異或

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

select 2 xor 3,0 xor 1,1 xor 0,1 xor null,null xor 3;

內連接

MySQL中的內連接就是兩張或多張表中同時相同的部分

select xx.欄位 from 表名 xx  inner join 表名2 b on xx.欄位=xx.欄位;

左連接

以左側表為基礎表，接收左表的所有行，并用這些行與右側關鍵字來表示

select * from 表名 left join 表名2 on 表名.欄位= 表名2.欄位;

右連接

以右側表為基礎表，接收右表的所有行，并用這些行與左側關鍵字來表示

select * from 表名 right join 表名2 on 表名.欄位=表名2.欄位;

資料庫函式

資料庫記憶體儲的記錄，經常要進行一系列的算數操作，所以MySQL支持很多數學函式
常用的數學函式如表所示

數學函式	描述
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的最大整數
greatest(x1,x2…)	回傳集合中最大的值
least(x1,x2…)	回傳集合中最小的值

舉例：
-2的絕對值

select abs(-2);

獲取亂數[范圍0-1]

select rand();

求9除2的余數

select mod(9,2);

求2的10次方

select power(2,10);

從超多小數中拯救整數

select round(3.14159265358979323846);

保留指定數量的小數并四舍五入

select round(3.14159265358979323846,2);

求6的平方根

select sqrt(6);

指定保留幾位小數

select truncate(1.1111,2);

回傳大于等于3.14的最小整數

select ceil(3.14);

回傳小于等于3.14的最大整數

select floor(3.14);

回傳集合中最大的值

select greatest(1,2,3,4,5);

回傳集合中最小值

select least(1,2,3,4,5);

聚合函式

聚合函式	描述
avg()	平均值
count()	計數
min()	最小值
max()	最大值
sum()	所有值之和

字串函式

字串函式	描述
length()	回傳字串長度
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反轉

測驗中發現一些有趣的bug
select upper(‘a’)=‘a’;會成立

它會優先去找可以轉化成數字的
如果單引號里寫1abc就不成立
它會把字母拋棄為0所以成立

日期時間函式

字串函式	描述
curdate()	回傳當前時間年月日
curtime()	回傳當前時間時分秒
now()	回傳當前時間的日期和時間
month(x)	回傳日期x中的月份值
week(x)	回傳日期x時年度第幾個星期
hour(x)	回傳x中的小時值
minute(x)	回傳x中的分鐘值
second(x)	回傳x中的秒值
dayofweek(x)	回傳x時星期幾，1星期日
dayofmonth(x)	計算日期x時本月的第幾天
dayofyear(x)	計算日期x時本年的第幾天

存盤程序

存盤程序是一組為了完成特定功能的SQL陳述句集合

存盤程序在使用程序中是將常用或者復雜的作業預先使用SQL陳述句寫好并用一個指定的名稱來進行儲存，這個程序經編譯和優化后存盤在資料庫服務器中，當需要使用該存盤程序時，只需要呼叫它即可，存盤程序在執行上比傳統SQL速度更快，執行效率更高，

存盤程序的優點

1. 執行一次后，會將生成的二進制代碼駐留緩沖區，提高執行效率
2. SQL陳述句加上控制陳述句的集合，靈活性高
3. 在服務器端存盤，客戶端呼叫時，降低網路負載
4. 可多次重復被呼叫，可隨時修改，不影響客戶端呼叫
5. 可完成所有的資料庫操作，也可控制資料庫的資訊訪問權

語法：

DELIMITER !!    				#將陳述句的結束符號從分號；臨時修改，以防出問題，可以自定義
CREATE PROCEDURE XXX()  	#創建存盤程序，程序名自定義,（）可帶引數
 	BEGIN   		    	#程序體以關鍵字BEGIN開始
 	select * from xxx;  	#程序體陳述句
 	END!!    				#程序體以關鍵字END結尾
DELIMITER ; 			    	#將陳述句的結束符號恢復為分號

call XXX;	  				#呼叫存盤程序


show create procedure [資料庫.]儲存程序名; 	  #查看某個儲存程序的具體資訊
show create procedure XXX;
show procedure status [like '%XXX%'] \G

例：

delimiter $$
create procedure test()
begin
create table user(id int(10));
insert into user values (1);
end $$

delimiter ;
call test;

查看存盤程序

show procedure status;

修改存盤功能

alter procedure 函式名 modifies sql data sql security invoker;

洗掉存盤程序

drop procedure if exists 函式名