1. sql基本函數大全
SQLServer基本函數
1.字元串函數長度與分析用
datalength(Char_expr) 返回字元串包含字元數,但不包含後面的空格
substring(expression,start,length) 不多說了,取子串
right(char_expr,int_expr) 返回字元串右邊int_expr個字元
字元操作類
upper(char_expr) 轉為大寫
lower(char_expr) 轉為小寫
space(int_expr) 生成int_expr個空格
replicate(char_expr,int_expr)復制字元串int_expr次
reverse(char_expr) 反轉字元串
stuff(char_expr1,start,length,char_expr2) 將字元串char_expr1中的從
start開始的length個字元用char_expr2代替
ltrim(char_expr) rtrim(char_expr) 取掉空格
ascii(char) char(ascii) 兩函數對應,取ascii碼,根據ascii嗎取字元
字元串查找
charindex(char_expr,expression) 返回char_expr的起始位置
patindex("%pattern%",expression) 返回指定模式的起始位置,否則為0
2.數學函數
abs(numeric_expr) 求絕對值
ceiling(numeric_expr) 取大於等於指定值的最小整數
exp(float_expr) 取指數
floor(numeric_expr) 小於等於指定值得最大整數
pi() 3.1415926.........
power(numeric_expr,power) 返回power次方
rand([int_expr]) 隨機數產生器
round(numeric_expr,int_expr) 安int_expr規定的精度四捨五入
sign(int_expr) 根據正數,0,負數,,返回+1,0,-1
sqrt(float_expr) 平方根
3.日期函數
getdate() 返回日期
datename(datepart,date_expr) 返回名稱如 June
datepart(datepart,date_expr) 取日期一部份
datediff(datepart,date_expr1.dateexpr2) 日期差
dateadd(datepart,number,date_expr) 返回日期加上 number
上述函數中datepart的
寫法 取值和意義
yy 1753-9999 年份
qq 1-4 刻
mm 1-12 月
dy 1-366 日
dd 1-31 日
wk 1-54 周
dw 1-7 周幾
hh 0-23 小時
mi 0-59 分鍾
ss 0-59 秒
ms 0-999 毫秒
日期轉換
convert()
4.系統函數
suser_name() 用戶登錄名
user_name() 用戶在資料庫中的名字
user 用戶在資料庫中的名字
show_role() 對當前用戶起作用的規則
db_name() 資料庫名
object_name(obj_id) 資料庫對象名
col_name(obj_id,col_id) 列名
col_length(objname,colname) 列長度
valid_name(char_expr) 是否是有效標識符
2. sql 語句中count函數怎麼用
COUNT() 函數返回匹配指定條件的行數。
SQL COUNT(column_name) 語法
COUNT(column_name) 函數返回指定列的值的數目（NULL 不計入）：
SELECT COUNT(column_name) FROM table_name;
SQL COUNT(*) 語法
COUNT(*) 函數返回表中的記錄數：
SELECT COUNT(*) FROM table_name;
SQL COUNT(DISTINCT column_name) 語法
COUNT(DISTINCT column_name) 函數返回指定列的不同值的數目：
SELECT COUNT(DISTINCT column_name) FROM table_name;
注釋：COUNT(DISTINCT) 適用於 ORACLE 和 Microsoft SQL Server，但是無法用於 Microsoft Access。
SQL COUNT(column_name) 實例
下面的 SQL 語句計算 "access_log" 表中 "site_id"=3 的總訪問量：
實例
SELECT COUNT(count) AS nums FROM access_log
WHERE site_id=3;
SQL COUNT(*) 實例
下面的 SQL 語句計算 "access_log" 表中總記錄數：
實例
SELECT COUNT(*) AS nums FROM access_log;
執行以上 SQL 輸出結果如下：
3. 求SQL的全部函數！完整的加100分！
一.聚合函數
AVG 返回組中值的平均值。空值將被忽略
BINARY_CHECKSUM 返回對表中的行或表達式列表計算的二進制校驗值。BINARY_CHECKSUM 可用於檢測表中行的更改
CHECKSUM 返回在表的行上或在表達式列表上計算的校驗值。CHECKSUM 用於生成哈希索引
CHECKSUM_AGG 返回組中值的校驗值。空值將被忽略
COUNT 返回組中項目的數量
COUNT_BIG 返回組中項目的數量。COUNT_BIG 的使用與 COUNT 函數相似。它們之間的唯一差別是它們的返回值：COUNT_BIG 總是返回 bigint 數據類型值，而 COUNT 則總是返回 int 數據類型值
GROUPING "是一個聚合函數,它產生一個附加的列，當用 CUBE 或 ROLLUP 運算符添加行時，附加的列輸出值為1，當所添加的行不是由 CUBE 或 ROLLUP 產生時，附加列值為0。
僅在與包含 CUBE 或 ROLLUP 運算符的 GROUP BY 子句相聯系的選擇列表中才允許分組"
MAX 返回表達式的最大值
MIN 返回表達式的最小值
SUM 返回表達式中所有值的和，或只返回 DISTINCT 值。SUM 只能用於數字列。空值將被忽略
STDEV 返回給定表達式中所有值的統計標准偏差
STDEVP 返回給定表達式中所有值的填充統計標准偏差
VAR 返回給定表達式中所有值的統計方差。
VARP 返回給定表達式中所有值的填充的統計方差。
二.數學函數
ABS 返回給定數字表達式的絕對值
ACOS 返回以弧度表示的角度值，該角度值的餘弦為給定的 float 表達式；本函數亦稱反餘弦。
ASIN 返回以弧度表示的角度值，該角度值的正弦為給定的 float 表達式；亦稱反正弦
ATAN 返回以弧度表示的角度值，該角度值的正切為給定的 float 表達式；亦稱反正切
ATN2 返回以弧度表示的角度值，該角度值的正切介於兩個給定的 float 表達式之間；亦稱反正切
CEILING 返回大於或等於所給數字表達式的最小整數
COS 一個數學函數，返回給定表達式中給定角度（以弧度為單位）的三角餘弦值
COT 一個數學函數，返回給定 float 表達式中指定角度（以弧度為單位）的三角餘切值
DEGREES 當給出以弧度為單位的角度時，返回相應的以度數為單位的角度
EXP 返回所給的 float 表達式的指數值
FLOOR 返回小於或等於所給數字表達式的最大整數
LOG 返回給定 float 表達式的自然對數
LOG10 返回給定 float 表達式的以 10 為底的對數
PI 返回 PI 的常量值
POWER 返回給定表達式乘指定次方的值
RADIANS 對於在數字表達式中輸入的度數值返回弧度值
RAND 返回 0 到1 之間的隨機float 值
ROUND 返回數字表達式並四捨五入為指定的長度或精度
SIGN 返回給定表達式的正 (+1)、零 (0) 或負 (-1) 號
SIN 以近似數字 (float) 表達式返回給定角度（以弧度為單位）的三角正弦值
SQUARE 返回給定表達式的平方
SQRT 返回給定表達式的平方根
TAN 返回輸入表達式的正切值
三.日期函數
DATEADD 在向指定日期加上一段時間的基礎上，返回新的 datetime 值。
DATEDIFF 返回跨兩個指定日期的日期和時間邊界數
DATENAME 返回代表指定日期的指定日期部分的字元串
DATEPART 返回代表指定日期的指定日期部分的整數
DAY 返回代表指定日期的天的日期部分的整數
GETDATE 按 datetime 值的 Microsoft�0�3 SQL Server�6�4 標准內部格式返回當前系統日期和時間
GETUTCDATE 返回表示當前 UTC 時間（世界時間坐標或格林尼治標准時間）的 datetime 值
MONTH 返回代表指定日期月份的整數
YEAR 返回表示指定日期中的年份的整數
四.系統函數
APP_NAME 返回當前會話的應用程序名稱（如果應用程序進行了設置）。
CASE 表達式 計算條件列表並返回多個可能結果表達式之一（詳見PPT資料）
CAST 和 CONVERT 將某種數據類型的表達式顯式轉換為另一種數據類型（詳見PPT資料）
COALESCE 返回其參數中第一個非空表達式
COLLATIONPROPERTY 返回給定排序規則的屬性
CURRENT_TIMESTAMP 返回當前的日期和時間。等價於 GETDATE()
CURRENT_USER 返回當前的用戶。價於 USER_NAME()
4. SQL的基本函數
【二】SQL的基本函數
2.1 關系型資料庫SQL命令類別
數據操縱語言：DML: select; insert; delete; update; merge.
數據定義語言：DDL: create; alter; drop; truncate; rename; comment.
事務控制語言：TCL: commit; rollback; savepoint.
數據控制語言：DCL: grant; revoke.
2.2 單行函數與多行函數
單行函數：指一行數據輸入，返回一個值得函數。所以查詢一個表時，對選擇的每一行數據都會返回一個結果。
SQL>select empno,lower(ename) from emp;
多行函數：指多行數據輸入，返回一個值得函數。所以對表的群組進行操作，並且每組返回一個結果。（典型的是聚合函數）
SQL>select sum(sal) from emp;
2.3 單行函數的幾種類型
2.3.1 字元型函數
lower('SQL Course')----->sql course 返回小寫
upper('sql course')----->SQL COURSE 返回大學
initcap('SQL course')-----> Sql Course 每個單字返回首字母大寫
concat('good','string')---->good string 拼接 只能拼接2個字元串
substr('String',1,3)---->Str 從第1位開始截取3位數，
演變：只有兩個參數的
substr('String',3) 正數第三位起始，得到後面所有字元
substr('String',-2) 倒數第二位，起始，得到最後所有字元
instr('t#i#m#r#a#n#','#') --->找第一個#字元在那個絕對位置，得到的數值
Instr參數經常作為substr的第二個參數值
演變：Instr參數可有四個之多
如select instr('aunfukk','u',-1,1) from al; 倒數第一個u是哪個位置，結果返回5
length('String')---->6 長度，得到的是數值
length參數又經常作為substr的第三個參數
lpad('first',10,'#39;)左填充
rpad(676768,10,'*')右填充
replace('JACK and JUE','J','BL')---->BLACK and BLUE
trim('m' from 'mmtimranm')---->timran 兩頭截，這里的『m』是截取集，僅能有一個字元
trim( ' timran ')---->timran 作用是兩頭去空字元
處理字元串時，利用字元型函數的嵌套組合是非常有效的：
create table customers(cust_name varchar2(20));
insert into customers values('Lex De Hann');
insert into customers values('Renske Ladwig');
insert into customers values('Jose Manuel Urman');
insert into customers values('Joson Malin');
select * from customers;
CUST_NAME
--------------------
Lex De Hann
Renske Ladwig
Jose Manuel Urman
Joson Malin
一共四條記錄，客戶有兩個名的，也有三個名的，現在想列出僅有三個名的客戶，且第一個名字用*號略去
答案之一：
SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME"
FROM customers
WHERE INSTR(cust_name,' ',1,2)>0;
CUST NAME
------------------------------------------------------------------------------------------------------------------------
*** De Hann
**** Manuel Urman
分析：
先用INSTR(cust_name,' ')找出第一個空格的位置，
然後，SUBSTR(cust_name,INSTR(cust_name,' '))從第一個空格開始往後截取字元串到末尾，結果是第一個空格以後所有的字元,
最後，LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*')用LPAD左填充到cust_name原來的長度，不足的部分用*填充，也就是將第一個空格前的位置，用*填充。
where後過濾是否有三個名字，INSTR(cust_name, ' ',1,2)從第一個位置，從左往右，查找第二次出現的空格，如果返回非0值，則說明有第二個空格，則有第三個名字。
2.3.2 數值型函數
round 對指定的值做四捨五入,round(p,s) s為正數時，表示小數點後要保留的位數，s也可以為負數，但意義不大。
round:按指定精度對十進制數四捨五入,如:round(45.923, 1),結果,45.9
round(45.923, 0),結果,46
round(45.923, -1),結果,50
trunc 對指定的值取整 trunc(p,s)
trunc:按指定精度截斷十進制數,如:trunc(45.923, 1),結果,45.9
trunc(45.923),結果,45
trunc(45.923, -1),結果, 40
mod 返回除法後的余數
SQL> select mod(100,12) from al;
2.3.3 日期型函數
因為日期在oracle里是以數字形式存儲的，所以可對它進行加減運算，計算是以天為單位。
預設格式：DD-MON-RR.
可以表示日期范圍：（公元前）4712 至（公元）9999
時間格式
SQL> select to_date('2003-11-04 00:00:00' ,'YYYY-MM-DD HH24:MI:SS') FROM al;
SQL> select sysdate+2 from al; 當前時間+2day
SQL> select sysdate+2/24 from al; 當前時間+2hour
SQL> select sysdate+2/1440 from al; 當前時間+2分鍾
SQL> select (sysdate-hiredate)/7 week from emp; 兩個date類型差，結果是以天為整數位的實數。
①MONTHS_BETWEEN 計算兩個日期之間的月數
SQL>select months_between('1994-04-01','1992-04-01') mm from al;
查找emp表中參加工作時間>30年的員工
SQL>select * from emp where months_between(sysdate,hiredate)/12>32;
很容易認為單行函數返回的數據類型與函數類型一致，對於數值函數類型而言的確如此，但字元和日期函數可以返回任何數據類型的值。比如instr函數是字元型的，months_between函數是日期型的，但它們返回的都是數值。
②ADD_MONTHS 給日期增加月份
SQL>select hiredate,add_months(hiredate,4) from emp;
③LAST_DAY 日期當前月份的最後一天
SQL>select hiredate,last_day(hiredate) from emp;
④NEXT_DAY NEXT_DAY的第2個參數可以是數字1-7，分別表示周日--周六(考點）
比如要取下一個星期六，則應該是：
SQL>select next_day(sysdate,7) FROM DUAL;
⑤ROUND(p,s)，TRUNC(p,s)在日期中的應用，如何舍入要看具體情況，s是MONTH按30天計，應該是15舍16入，s是YEAR則按6舍7入計算。
SQL>SELECT empno, hiredate,round(hiredate,'MONTH') AS round,trunc(hiredate,'MONTH') AS trunc FROM emp;
SQL>SELECT empno, hiredate, round(hiredate,'YEAR') AS round,trunc(hiredate,'YEAR') AS trunc FROM emp;
2.3.4 幾個有用的函數和表達式
1）DECODE函數和CASE表達式：
實現sql語句中的條件判斷語句，具有類似高級語言中的if-then語句的功能。
decode函數源自oracle, case表達式源自sql標准，實現功能類似，decode語法更簡單些。
decode函數用法：
SQL> SELECT job, sal,
decode(job, 'ANALYST', SAL*1.1, 'CLERK', SAL*1.15,'MANAGER', SAL*1.20, SAL) SALARY FROM emp
decode函數的另幾種常見用法：
SQL>select ename,job,decode(job,'MANAGER','中層幹部') leader from emp;
SQL> select ename,job,comm,decode (comm,null,'nonsale','sale') saleman from emp;
註：單一列處理，共四個參數：含義是：comm 如果為null就取'nonsale，否則取'sale'
SQL> select sal,sign(sal-1500) from emp;
SQL> select ename,decode (sign(sal-1500), 1, 'NORMAL','LOW') as "LEV" from emp;
註：sign()函數根據某個值是0、正數還是負數，分別返回0、1、-1，含義是：工資大於1500,返回1，真取'NORMAL'，假取'LOW'
CASE表達式第一種用法：
SQL> SELECT job, sal,
case job when 'ANALYST' then SAL*1.10
when 'CLERK' then SAL*1.15
when 'MANAGER' then SAL*1.20
else sal end SALARY
FROM emp
CASE表達式第二種用法：
SQL> SELECT job, sal, case
when job='ANALYST' then SAL*1.1
when job='CLERK' then SAL*1.15
when job='MANAGER' then SAL*1.20
else sal end SALARY
FROM emp
以上三種寫法結果都是一樣的
CASE第二種語法比第一種語法增加了搜索功能。形式上第一種when後跟定值，而第二種還可以使用表達式和比較符。
看一個例子
SQL> SELECT ename,sal,case
when sal>=3000 then '高級'
when sal>=2000 then '中級'
else '低級' end 級別
FROM emp
再看一個例子:使用了復雜的表達式
SQL> SELECT AVG(CASE
WHEN sal BETWEEN 500 AND 1000 AND JOB='CLERK'
THEN sal ELSE null END) "CLERK_SAL"
from emp;
比較;
SQL> select avg(sal) from emp where job='CLERK';
2）DISTINCT(去重)限定詞的用法：
distinct貌似多行函數，嚴格來說它不是函數而是select子句中的一個選項。
SQL> select distinct job from emp; 消除錶行重復值。
SQL> select distinct job,deptno from emp; 重復值是後面的欄位組合起來考慮的
SQL> select distinct * from emp; 消除重復記錄
3）sys_context 獲取環境上下文的函數（多用於應用環境）
scott遠程登錄
SQL>select SYS_CONTEXT('USERENV','IP_ADDRESS') from al;
--------------------------------------------------------------------------------
192.168.0.136
SQL> select sys_context('userenv','sid') from al;
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
129
SQL> select sys_context('userenv','terminal') from al;
SYS_CONTEXT('USERENV','TERMINAL')
--------------------------------------------------------------------------------
TIMRAN-222C75E5
the end ！！！
@jackman 共築美好！
5. sql 如何在sql語句里使用函數
樓主的邏輯估計不對，應該是用b保質期減去現在日期和a的差，算出來才對。即 b-(現在日期 - a) <0表示過期。
select b-datediff(day,a,getdate()) as DayNum,
IsOvere = case when (b-datediff(day,a,getdate()))<0 then '過期' else '未過期' end
from tablename
6. 怎麼用SQL語句修改數值為保留兩位小數(sql中保留兩位小數的函數)
1、創建測試表，createtabletest_replace_num(idnumber,valuenumber);
2、插入測試數據；
insertintotest_replace_numvalues(1,4.9528);
insertintotest_replace_numvalues(2,3.8821);
insertintotest_replace_numvalues(3,41.2287);
insertintotest_replace_numvalues(4,18.1675);
commit;
3、查詢謹帆純表中全量祥咐數據；selectt.*,rowidfromtest_replace_numt;
4、編寫語句，將value欄位轎虛更新為只保留兩位小數；
updatetest_replace_numsetvalue=round(value,2);
commit;
5、再次查詢表中全量數據，發現vlaue只保留兩位小數；
selectt.*,rowidfromtest_replace_numt;
7. SQL語句整理——函數篇
函數是大牛封裝好的程序，一些函數經過時間的沉澱和不斷地被開發運用，變得十分的精巧和實用。
函數讓人不再糾結於底層的邏輯，就像『遙控器』或者說類似於數學中的抽象概念一樣，在數學中運用定理，很多時候不需要一步步推導出定理（當然能推導的都是大牛。），我們在學習過程中，很多時候也不用糾結『如果不懂底層的邏輯成為不了高手』這樣到處可見的言論，只管做，體會就行了，當有需要的時候，自然會了解底層的東西，順其自然就行（自勉哈哈）。
聚合函數就是常見的匯總函數：SUM 求和，AVG平均（相當於average）,MAX最大，MIN最小，COUNT平均等。
打開EXCEL數據→現有連接
點擊recent
相當於if函數，最多能嵌套14層。
IIF（條件，true,false）
給語文成績大於90的人發小紅花
它是一個取整函數。
根據第一參數的結果，在後面參數對應位置給出結果。
如果表達式結果有小數則取整。
在實際運用中choose函數還是比較小眾的。
下面的例子有些意思，統計生肖的人數。需要做到數據透視表中。
left 從左到右提取字元
right 從右向左提取字元
語法結構 mid(字元串，提取的起始位置，提取的長度)
根據身份證號判斷性別
身份證號有的是15位有的是18位，是15位的最後一位代表性別，18位的第十七位代表性別。那麼取出第15位到第17位的數字求余，就能滿足條件。
字元串長度提取。
INSTR函數與工作表函數FIND類似，只是參數位置略有區別：
INSTR([查找的起始位置]（可省略），查找區域，查找關鍵字)
FIND(查找的關鍵字，查找的區域，查找的起始位置（可省略）)
我們想要把市名提取出來
REPLACE（查找的區域，查找的內容，替換內容）
相當於在工作表中按CTRL+F替換的功能。
拆分出生年月日
我們可以用group by 和聚合函數進行各種匯總，但是想對匯總結果進行進一步的條件篩選，這時就用到having語句，having語句與where條件語句基本類似，不過它是針對group by匯總後的進行的條件篩選。
需要篩選匯總出農作物總產量大於10000數據。