MySQL DAYNAME函数

时间:2019-11-20 08:52:22  来源:igfitidea点击:

简介:在本教程中,您将学习如何使用MySQL DAYNAME函数获取给定日期的工作日名称。

MySQL DAYNAME函数简介

MySQL DAYNAME函数返回指定日期的工作日名称。
以下说明了DAYNAME函数的语法:

DAYNAME(date);

DAYNAME函数接受1个参数,该参数是您要获取其工作日名称的日期。

如果日期为NULL或无效,例如2017-02-30,则DAYNAME函数将返回NULL。

MySQL DAYNAME函数示例

下面的示例返回2000年1月1日的工作日名称。

mysql> SELECT DAYNAME('2000-01-01') dayname;
+----------+
| dayname  |
+----------+
| Saturday |
+----------+
1 row in set (0.00 sec)

默认情况下,MySQL以lc_time_names系统变量控制的语言返回工作日的名称。

mysql> SELECT @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| en_US           |
+-----------------+
1 row in set (0.00 sec)

如您所见,当前,语言环境设置为en_US。

要在特定语言环境中获取星期几的日期名称,您需要更改lc_time_names变量的值。
例如,以下语句将语言环境设置为法语:

mysql> SET @@lc_time_names = 'fr_FR';
Query OK, 0 rows affected (0.00 sec)

现在,让我们查询2000年1月1日的工作日名称:

mysql> SELECT DAYNAME('2000-01-01') dayname;
+---------+
| dayname |
+---------+
| samedi  |
+---------+
1 row in set (0.00 sec)

如您所见,工作日名称已更改为法语。

下表显示了MySQL支持的lc_time_names的有效语言环境:

Albanian – Albaniasq_AL
Arabic – Algeriaar_DZ
Arabic – Bahrainar_BH
Arabic – Egyptar_EG
Arabic – Indiaar_IN
Arabic – Iraqar_IQ
Arabic – Jordanar_JO
Arabic – Kuwaitar_KW
Arabic – Lebanonar_LB
Arabic – Libyaar_LY
Arabic – Moroccoar_MA
Arabic – Omanar_OM
Arabic – Qatarar_QA
Arabic – Saudi Arabiaar_SA
Arabic – Sudanar_SD
Arabic – Syriaar_SY
Arabic – Tunisiaar_TN
Arabic – United Arab Emiratesar_AE
Arabic – Yemenar_YE
Basque – Basqueeu_ES
Belarusian – Belarusbe_BY
Bulgarian – Bulgariabg_BG
Catalan – Spainca_ES
Chinese – Chinazh_CN
Chinese – Hong Kongzh_HK
Chinese – Taiwan Province of Chinazh_TW
Croatian – Croatiahr_HR
Czech – Czech Republiccs_CZ
Danish – Denmarkda_DK
Dutch – Belgiumnl_BE
Dutch – The Netherlandsnl_NL
English – Australiaen_AU
English – Canadaen_CA
English – Indiaen_IN
English – New Zealanden_NZ
English – Philippinesen_PH
English – South Africaen_ZA
English – United Kingdomen_GB
English – United Statesen_US
English – Zimbabween_ZW
Estonian – Estoniaet_EE
Faroese – Faroe Islandsfo_FO
Finnish – Finlandfi_FI
French – Belgiumfr_BE
French – Canadafr_CA
French – Francefr_FR
French – Luxembourgfr_LU
French – Switzerlandfr_CH
Galician – Spaingl_ES
German – Austriade_AT
German – Belgiumde_BE
German – Germanyde_DE
German – Luxembourgde_LU
German – Switzerlandde_CH
Greek – Greeceel_GR
Gujarati – Indiagu_IN
Hebrew – Israelhe_IL
Hindi – Indiahi_IN
Hungarian – Hungaryhu_HU
Icelandic – Icelandis_IS
Indonesian – Indonesiaid_ID
Italian – Italyit_IT
Italian – Switzerlandit_CH
Japanese – Japanja_JP
Korean – Republic of Koreako_KR
Latvian – Latvialv_LV
Lithuanian – Lithuanialt_LT
Macedonian – FYROMmk_MK
Malay – Malaysiams_MY
Mongolia – Mongolianmn_MN
Norwegian – Norwayno_NO
Norwegian(Bokmål) – Norwaynb_NO
Polish – Polandpl_PL
Portugese – Brazilpt_BR
Portugese – Portugalpt_PT
Romanian – Romaniaro_RO
Russian – Russiaru_RU
Russian – Ukraineru_UA
Serbian – Yugoslaviasr_RS
Slovak – Slovakiask_SK
Slovenian – Sloveniasl_SI
Spanish – Argentinaes_AR
Spanish – Boliviaes_BO
Spanish – Chilees_CL
Spanish – Columbiaes_CO
Spanish – Costa Ricaes_CR
Spanish – Dominican Republices_DO
Spanish – Ecuadores_EC
Spanish – El Salvadores_SV
Spanish – Guatemalaes_GT
Spanish – Hondurases_HN
Spanish – Mexicoes_MX
Spanish – Nicaraguaes_NI
Spanish – Panamaes_PA
Spanish – Paraguayes_PY
Spanish – Perues_PE
Spanish – Puerto Ricoes_PR
Spanish – Spaines_ES
Spanish – United Stateses_US
Spanish – Uruguayes_UY
Spanish – Venezuelaes_VE
Swedish – Finlandsv_FI
Swedish – Swedensv_SE
Tamil – Indiata_IN
Telugu – Indiate_IN
Thai – Thailandth_TH
Turkish – Turkeytr_TR
Ukrainian – Ukraineuk_UA
Urdu – Pakistanur_PK
Vietnamese – Viet Namvi_VN

请参阅示例数据库中的以下订单表:

以下语句返回按2004年工作日名称分组的订单计数。

SELECT 
    DAYNAME(orderdate) weekday, 
    COUNT(*) total_orders
FROM
    orders
WHERE
    YEAR(orderdate) = 2004
GROUP BY weekday
ORDER BY total_orders DESC;

周五下的订单数量最高,周日有两个订单。

在本教程中,您学习了如何使用MySQL DAYNAME函数来获取特定日期的工作日名称。