Mysql| Mysql函数,聚集函数的介绍与使用(Lower,Date,Mod,AVG,...)
轉(zhuǎn)載自https://blog.csdn.net/u011479200/article/details/78633382
關(guān)于MYSQL的函數(shù)
函數(shù)沒有通用的SQL語句的可移植性強(qiáng) 能運行在多個系統(tǒng)上的代碼稱為可移植的（ portable）。相對來說，多數(shù)SQL語句是可移植的，在SQL實現(xiàn)之間有差異時，這些差異通常不那么難處理。而函數(shù)的可移植性卻不強(qiáng)。幾乎每種主要的DBMS的實現(xiàn)都支持其他實現(xiàn)不支持的函數(shù)，而且有時差異還很大。?
(也就是說在mysql中可以使用的函數(shù),在其他比如oracle中有可能就不支持使用,函數(shù)是沒有普通SQL語句的可移植性強(qiáng).)
函數(shù)使用注意事項:
- 1.關(guān)于函數(shù)的關(guān)鍵字使用,MySQL是不區(qū)分大小寫的.
- 2.低版本的MYSQL可能不支持高版本中部分聚集函數(shù),具體的聚集函數(shù)的使用應(yīng)根據(jù)MySQL的版本選擇支持的聚集函數(shù)使用.
常見函數(shù)分類:
- 1.用于處理文本串（如刪除或填充值，轉(zhuǎn)換值為大寫或小寫）的文本函數(shù).
- 2.用于在數(shù)值數(shù)據(jù)上進(jìn)行算術(shù)操作（如返回絕對值，進(jìn)行代數(shù)運算）的數(shù)值函數(shù)。
- 3.用于處理日期和時間值并從這些值中提取特定成分（例如，返回兩個日期之差，檢查日期有效性等）的日期和時間函數(shù)。
- 4.返回DBMS正使用的特殊信息（如返回用戶登錄信息，檢查版本細(xì)節(jié)）的系統(tǒng)函數(shù)。
1.關(guān)于文本處理函數(shù)
常用的文本處理函數(shù)
| Left() | 返回串左邊的字符 |
| Length() | 返回串的長度 |
| Locate() | 找出串的一個子串 |
| Lower() | 將串轉(zhuǎn)換為小寫 |
| LTrim() | 去掉串左邊的空格 |
| Right() | 返回串右邊的字符 |
| RTrim() | 去掉串右邊的空格 |
| Soundex() | 返回串的SOUNDEX值 |
| SubString() | 返回子串的字符 |
| Upper() | 將串轉(zhuǎn)換為大寫 |
①使用UPPER函數(shù)的例子(其他文本函數(shù)使用方法類似):?
這里需要特別注意的是MYSQL中別名字段不能有-?,應(yīng)該使用下劃線.?
SELECT vend_name,UPPER(vend_name) vend_name_upcase FROM vendors ORDER BY vend_name ASC;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
②特別的函數(shù)Soundex:?
SOUNDEX函數(shù)一個將任何文本串轉(zhuǎn)換為描述其語音表示的字母數(shù)字模式的算法。 SOUNDEX考慮了類似的發(fā)音字符和音節(jié)，使得能對串進(jìn)行發(fā)音比較而不是字母比較。(也就是說Soundex()函數(shù)進(jìn)行搜索，它匹配所有類似的發(fā)音文本.)
普通的全文本匹配:?
SELECT customers.cust_name,customers.cust_contact FROM customers WHERE cust_contact = 'Y.Lie';?
執(zhí)行沒有數(shù)據(jù)返回.
使用Sounddex函數(shù):?
SELECT customers.cust_name,customers.cust_contact FROM customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Y.Lie');?
執(zhí)行結(jié)果如下:
- 1
- 2
- 3
- 4
- 5
- 6
2.日期和時間處理函數(shù)
常用日期和時間處理函數(shù)
| AddDate() | 增加一個日期（天、周等） |
| AddTime() | 增加一個時間（時、分等） |
| CurDate() | 返回當(dāng)前日期 |
| CurTime() | 返回當(dāng)前時間 |
| Date() | 返回日期時間的日期部分 |
| DateDiff() | 計算兩個日期之差 |
| Date_Add() | 高度靈活的日期運算函數(shù) |
| Date_Format() | 返回一個格式化的日期或時間串 |
| Day() | 返回一個日期的天數(shù)部分 |
| DayOfWeek() | 對于一個日期，返回對應(yīng)的星期幾 |
| Hour() | 返回一個時間的小時部分 |
| Minute() | 返回一個時間的分鐘部分 |
| Month() | 返回一個日期的月份部分 |
| Now() | 返回當(dāng)前日期和時間 |
| Second() | 返回一個時間的秒部分 |
| Time() | 返回一個日期時間的時間部分 |
| Year() | 返回一個日期的年份部分 |
MySQL中關(guān)于日期函數(shù)的使用:
首先需要注意的是MySQL使用的日期格式。無論你什么時候指定一個日期，不管是插入或更新表值還是用WHERE子句進(jìn)行過濾，日期必須包含格式y(tǒng)yyy-mm-dd.
說明:比如:2005年9月1日,給出為2005-09-01。雖然其他的日期格式可能也行，但這是首選的日期格式，因為它排除了多義性（如，04/05/06是2006年5月4日或2006年4月5日或2004年5月6日或……
對比不使用時間函數(shù)和使用使用MySQL的時間函數(shù)效果:
orders表結(jié)構(gòu): +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | order_num | int(11) | NO | PRI | NULL | auto_increment | | order_date | datetime | NO | | NULL | | | cust_id | int(11) | NO | MUL | NULL | | +------------+----------+------+-----+---------+----------------+orders表中所有數(shù)據(jù): +-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20005 | 2005-09-01 00:00:00 | 10001 | | 20006 | 2005-09-12 00:00:00 | 10003 | | 20007 | 2005-09-30 00:00:00 | 10004 | | 20008 | 2005-10-03 00:00:00 | 10005 | | 20009 | 2005-10-08 00:00:00 | 10001 | +-----------+---------------------+---------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
不使用時間函數(shù)使用基本語法:(兩條語句等價)?
SELECT * FROM orders WHERE order_date ='2005-09-01';?
SELECT * FROM orders WHERE order_date ='2005-09-01 00:00:00';
這樣也就有一個問題,當(dāng)數(shù)據(jù)庫中的數(shù)據(jù)不恰好是0點0分0秒時,上面的SQL語句就匹配不到結(jié)果.比如修改一下第一條記錄為一下上午10點時,上面的SQL語句就不能匹配到結(jié)果了.
+-----------+---------------------+---------+ | order_num | order_date | cust_id | +-----------+---------------------+---------+ | 20005 | 2005-09-01 10:00:00 | 10001 | +-----------+---------------------+---------+- 1
- 2
- 3
- 4
- 5
為了解決上面說的這個問題,MySQL提供了Date函數(shù)來解決這個問題.Date(order_date)指示MySQL僅提取列的日期部分，更可靠的SELECT語句為：?
SELECT * FROM orders WHERE DATE(order_date) ='2005-09-01';同樣道理對應(yīng)的函數(shù)Time() 只返回一個日期時間的時間部分.其他時間函數(shù)用法類似.
關(guān)于對日期的其他操作一樣了,比如:查詢2005年9月份的訂單記錄(不用單行月份的天數(shù),需要注意的是函數(shù)不能加引號)?
SELECT * FROM orders WHERE YEAR(order_date) = 2005 AND MONTH(order_date) = 9;
3.數(shù)值處理函數(shù)
數(shù)值函數(shù)是最一致最統(tǒng)一的函數(shù),也就是說不同個數(shù)據(jù)庫廠商對著類函數(shù)的兼容性最好,這類函數(shù)通用性最強(qiáng).
常用數(shù)值處理函數(shù)
| Abs() | 返回一個數(shù)的絕對值 |
| Cos() | 返回一個角度的余弦 |
| Exp() | 返回一個數(shù)的指數(shù)值 |
| Mod() | 返回除操作的余數(shù) |
| Pi() | 返回圓周率 |
| Rand() | 返回一個隨機(jī)數(shù) |
| Sin() | 返回一個角度的正弦 |
| Sqrt() | 返回一個數(shù)的平方根 |
| Tan() | 返回一個角度的正切 |
4.系統(tǒng)函數(shù)
省略.
聚集函數(shù)
聚集函數(shù)（ aggregate function）: 運行在行組上，計算和返回單個值的函數(shù).(與普通的函數(shù)最主要的區(qū)別聚集函數(shù)值返回一條結(jié)果,這當(dāng)然是使用分組語句的情況下)?
當(dāng)我們只需要匯總數(shù)據(jù)而不用把它們實際數(shù)據(jù)檢索出來時,可以使用聚集函數(shù).在只需要返回匯總數(shù)據(jù)時,返回實際表數(shù)據(jù)是對時間和處理資源的一種浪費,比如帶寬.?
(通常都是對數(shù)值進(jìn)行聚集返回一條數(shù)據(jù).但是min和max函數(shù)除了可以對數(shù)值型數(shù)據(jù)進(jìn)行計算還支持時間類型和文本類型的計算,具體見相關(guān)函數(shù)的詳情用法.)
SQL聚集函數(shù)
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行數(shù) |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
聚集函數(shù)注意事項:
- 1.所有的聚集函數(shù)在對指定的列進(jìn)行計算時,會忽略列值為NULL的行.
- 2.特別的COUNT函數(shù)在對所有的列進(jìn)行計算時允許使用*, 對行進(jìn)行計數(shù)時,不會忽略一行數(shù)據(jù)中每個列為null值的行.(聚集函數(shù)除了COUNT函數(shù)有COUNT(*)用法,其他的聚集函數(shù)沒有此用法.)
- 3.所有聚集函數(shù)都可以在多個列上進(jìn)行計算,利用標(biāo)準(zhǔn)的算術(shù)操作符，所有聚集函數(shù)都可用來執(zhí)行多個列上的計算。(eg: SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;)
聚集函數(shù)優(yōu)缺點:
- 1.聚集函數(shù)用來匯總數(shù)據(jù)。 MySQL支持一系列聚集函數(shù)，可以用多種方法使用它們以返回所需的結(jié)果。這些函數(shù)是高效設(shè)計的，它們返回結(jié)果一般比你在自己的客戶機(jī)應(yīng)用程序中計算要快得多.
- 2.聚集函數(shù)的可移植性不強(qiáng),取決于不同的數(shù)據(jù)庫廠商和不同的版本.
①AVG()函數(shù)用法:
只用于單個列 AVG()只能用來確定特定數(shù)值列的平均值，而且列名必須作為函數(shù)參數(shù)給出。為了獲得多個列的平均值，必須使用多個AVG()函數(shù)。
關(guān)于空值:?NULL值 AVG()函數(shù)忽略列值為NULL的行。?
EG:SELECT AVG(prod_price) AS avg_price FROM products;
②COUNT()函數(shù)用法:
進(jìn)行計數(shù)。可利用COUNT()確定表中行的數(shù)目或符合特定條件的行的數(shù)目。?
COUNT()函數(shù)有兩種使用方式:?
①使用COUNT(*)對表中行的數(shù)目進(jìn)行計數(shù)， 不管表列中包含的是空值（ NULL）還是非空值。 (對行進(jìn)行計數(shù)時,不會忽略一行數(shù)據(jù)中每個列為null值的行)?
②使用COUNT(column)對特定列中具有值的行進(jìn)行計數(shù)，忽略NULL值。
關(guān)于空值:?column如果指定列名，則指定列的值為空的行被COUNT()函數(shù)忽略，但如果COUNT()函數(shù)中用的是星號(*)，則不忽略。?
EG:?
SELECT COUNT(*) AS num_cust FROM products;?
SELECT COUNT(cust_email) AS num_cust FROM customers;
③MAX()函數(shù)用法:
MAX()返回指定列中的最大值.對非數(shù)值數(shù)據(jù)使用MAX() 雖然MAX()一般用來找出最大的數(shù)值或日期值，但MySQL允許將它用來返回任意列中的最大值，包括返回文本列中的最大值。在用于文本數(shù)據(jù)時，如果數(shù)據(jù)按相應(yīng)的列排序，則MAX()返回最后一行。
關(guān)于空值:?NULL值 MAX()函數(shù)忽略列值為NULL的行。?
EG:?SELECT MAX(prod_price) AS max_price FROM products;
④MIN()函數(shù)用法:
MIN()的功能正好與MAX()功能相反，它返回指定列的最小值。與MAX()一樣， MIN()要求指定列名.對非數(shù)值數(shù)據(jù)使用MIN() MIN()函數(shù)與MAX()函數(shù)類似，MySQL允許將它用來返回任意列中的最小值，包括返回文本列中的最小值。在用于文本數(shù)據(jù)時，如果數(shù)據(jù)按相應(yīng)的列排序，則MIN()返回最前面的行。
關(guān)于空值:?MIN()函數(shù)忽略列值為NULL的行?
EG:SELECT MIN(prod_price) AS min_price FROM products;
⑤SUM()函數(shù)用法:
SUM()用來返回指定列值的和（總計）。
關(guān)于空值:?NULL值 SUM()函數(shù)忽略列值為NULL的行。?
EG:?
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;?
SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;
聚集不同-用法:
以上5個聚集函數(shù)都可以如下使用：(在MYSQL5版本中有小在5以下無效)?
①對所有的行執(zhí)行計算，指定ALL參數(shù)或不給參數(shù)（因為ALL是默認(rèn)行為）；?
②只包含不同的值，指定DISTINCT參數(shù)。
EG:?
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;?
解釋:?計算平均值時,去除價格相同的記錄(也就是prod_price列值相同的值)后再計算平均值.
組合聚集-用法
EG:?
SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM products;?
注意:?聚集函數(shù)一般是使用在數(shù)值類型列上(類型為int,double…),也就是說這些記錄中如果沒有設(shè)置值,不會存在為null情況,mysql系統(tǒng)會默認(rèn)值為0.
超強(qiáng)干貨來襲 云風(fēng)專訪：近40年碼齡，通宵達(dá)旦的技術(shù)人生
總結(jié)
以上是生活随笔為你收集整理的Mysql| Mysql函数,聚集函数的介绍与使用(Lower,Date,Mod,AVG,...)的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mysql 函数substring_in
- 下一篇： MySQL的学习--触发器