必會單詞
regexp
正規表示式trim
修剪replace
替換ceiling
最高限度year
年month
月day
日hour
小時minute
分鐘second
秒current
當前的date
日期time
時間now
現在week
星期version
版本
運算子
算術運算子
-
加 +
- select int型別的欄位,int型別的欄位+10 from 表;
select bookprice,bookprice+10 from book; 複製程式碼
-
減 -
- select int型別的欄位,int型別的欄位-10 from 表;
select bookprice,bookprice-10 from book; 複製程式碼
-
乘 *
- select int型別的欄位,int型別的欄位*10 from 表;
select bookprice,bookprice*10 from book; 複製程式碼
-
除 /
- select int型別的欄位,int型別的欄位/10 from 表;
select bookprice,bookprice/10 from book; 複製程式碼
-
求餘/取模 %
- select int型別的欄位,int型別的欄位%10 from 表;
select bookprice,bookprice%10 from book; 複製程式碼
比較運算子
-
大於/小於
- SELECT 欄位1, 欄位2 FROM 表 WHERE 欄位2 > 值;
SELECT bookname, bookprice FROM book WHERE bookprice > 20; 複製程式碼
- SELECT 欄位1, 欄位2 FROM 表 WHERE 欄位2 < 值;
SELECT bookname, bookprice FROM book WHERE bookprice < 20; 複製程式碼
-
不等
- SELECT 欄位1, 欄位2 FROM 表 WHERE 欄位2 <> 值;
SELECT bookname, bookprice FROM book WHERE bookprice <> 20; 複製程式碼
- SELECT 欄位1, 欄位2 FROM 表 WHERE 欄位2 != 值;
SELECT bookname, bookprice FROM book WHERE bookprice != 20; 複製程式碼
-
正則
- select 欄位1,欄位1 REGEXP '正規表示式1',欄位2,欄位2 REGEXP '正規表示式2',欄位3,欄位3 REGEXP '正規表示式3' from 表;
select bookname,bookname REGEXP '^j',bookauthor,bookauthor REGEXP '紅$',bookpublisher,bookpublisher REGEXP '.+出版社' from book; 複製程式碼
- select * from 表 where 欄位 REGEXP '正規表示式';
select * from book where bookpublisher REGEXP '.+[0-9]$'; 複製程式碼
邏輯運算子
-
&& and
- select * from 表名 where 欄位 > 值1 and 欄位 < 值2;
select * from book where borrowsum > 5 and borrowsum < 30; 複製程式碼
- select * from 表名 where 欄位 > 值1 && 欄位 < 值2;
select * from book where borrowsum > 5 && borrowsum < 30; 複製程式碼
-
! not
- select * from 表名 where not 欄位名 = 值;
select * from book where not borrowsum = 30; 複製程式碼
- select * from 表名 where 欄位名 != 值;
select * from book where borrowsum != 30; 複製程式碼
- select * from 表名 where 欄位名 <> 值;
select * from book where borrowsum <> 30; 複製程式碼
-
|| or
- select * from 表名 where 欄位 <= 值1 || 欄位 >= 值2;
select * from book where borrowsum <= 5 or borrowsum >= 30; 複製程式碼
- select * from 表名 where 欄位 <= 值1 or 欄位 >= 值2;
select * from book where borrowsum <= 5 || borrowsum >= 30; 複製程式碼
-
這四個語句, 結果一樣
select * from book where borrowsum > 5 and borrowsum < 30; 複製程式碼
select * from book where borrowsum > 5 && borrowsum < 30; 複製程式碼
select * from book where not( borrowsum <= 5 or borrowsum >= 30); 複製程式碼
select * from book where not( borrowsum <= 5 || borrowsum >= 30); 複製程式碼
內建函式
字串函式
-
left(s,n)/right(s,n)
- select 欄位,left(欄位,2) from 表;
select bookname,left(bookname,2) from book; 複製程式碼
- select 欄位,right(欄位,2) from 表;
select bookname,right(bookname,2) from book; 複製程式碼
-
concat()/concat_ws()
- select 欄位1,欄位2,欄位3,欄位4,欄位5,CONCAT(欄位1,欄位2,欄位3,欄位4,欄位5) as 別名1, CONCAT_WS('分隔符',欄位1,欄位2,欄位3,欄位4,欄位5) as 別名2 from 表;
select bookid,bookname,bookauthor,bookpublisher,bookprice,CONCAT(bookid,bookname,bookauthor,bookpublisher,bookprice) as 詳情1,CONCAT_WS('_',bookid,bookname,bookauthor,bookpublisher,bookprice) as 詳情2 from book; 複製程式碼
-
trim()/ltrim(s)/rtrim(s)
- select 欄位, trim(欄位) 別名1, ltrim(欄位) 別名2, rtrim(欄位) 別名3 from 表名;
select bookpublisher,trim(bookpublisher) 刪除左右空格,ltrim(bookpublisher) 刪除左空格,rtrim(bookpublisher) 刪除右空格 from book; 複製程式碼
-
replace()
- select 欄位 別名,replace(欄位,'匹配的欄位','替換的欄位') 替換後 from 表名;
select bookname 替換前,replace(bookname,'設計','崩潰') 替換後 from book; 複製程式碼
-
substring()
- select 欄位, SUBSTRING(欄位,開始位置從一開始,長度) from 表名;
select bookname, SUBSTRING(bookname,2,3) from book; 複製程式碼
日期函式
-
now()
select now(); 複製程式碼
-
curdate()/curtime()
select now(),CURRENT_DATE(),CURRENT_TIME(),curdate(),curtime(); 複製程式碼
-
dayofweek(d)/dayofmonth(d)/dayofyear(d)
select now(),DAYOFWEEK(now()),DAYOFMONTH(now()),DAYOFYEAR(now()),WEEKDAY(now()); 複製程式碼
-
hour(t)/minute(t)/second(t)
select now(),HOUR(now()),MINUTE(now()),SECOND(now()); 複製程式碼
-
date_add()/date_sub()
select DATE_ADD(now(),interval 3 day); 複製程式碼
select DATE_SUB(now(),INTERVAL 7 MINUTE); 複製程式碼
-
datediff()
select DATEDIFF('2020-10-1',now()); 複製程式碼
數學函式
-
abs(x)
select abs(-789),abs(-123.666); 複製程式碼
-
floor(x)/ceiling(x)
select FLOOR(-2.3),CEILING(-2.3),FLOOR(9.9),CEILING(9.9); 複製程式碼
-
greatest()/least()
select GREATEST(1,2,3,4),LEAST(1,2,3,4); 複製程式碼
-
round(x)/truncate(x,y)
select round(3.4567),round(4.567),TRUNCATE(3.4567,3); 複製程式碼
-
rand()
select rand(),rand(); 複製程式碼
-
sqrt(x)/mod(x,y)
select sqrt(64),sqrt(2),TRUNCATE(sqrt(2),3),mod(10,4); 複製程式碼
系統函式
-
database()/user()/version()
select DATABASE(),user(),version(); 複製程式碼
-
charset(str)/collation(str)
select charset('123'),COLLATION('123'); 複製程式碼