在本教程中,您将学习如何使用MySQL DAYNAME函数来获取给定日期的工作日的名称。
MySQL DAYNAME函数介绍
MySQL DAYNAME函数返回指定日期的工作日的名称。 以下说明了DAYNAME函数的语法:
DAYNAME(date);
DAYNAME函数接受1个参数,该参数是要获取其工作日名称的日期。
如果日期为或无效,例如2017-02-30,DAYNAME函数将返回NULL。
MySQL DAYNAME函数示例
以下示例将返回2018年1月1日的工作日名称。
mysql> SELECT DAYNAME('2018-01-01') dayname; +---------+ | dayname | +---------+ | Monday | +---------+ 1 row in set
默认情况下,MySQL返回由lc_time_names系统变量控制的语言中的工作日的名称,查询当前lc_time_names变量设置的值,如下 –
mysql> SELECT @@lc_time_names; +-----------------+ | @@lc_time_names | +-----------------+ | en_US | +-----------------+ 1 row in set
您可以看到,目前区域设置设置为en_US。
要在特定区域设置中获取日期名称一个工作日,您需要更改lc_time_names变量的值。 例如,以下语句将语言环境设置为中文:
mysql> SET @@lc_time_names = 'zh_CN'; Query OK, 0 rows affected
现在,我们来查询2018年1月1日的工作日名称:
mysql> SELECT DAYNAME('2018-01-01') dayname; +---------+ | dayname | +---------+ | 星期一 | +---------+ 1 row in set
您可以看到,工作日名称已更改为中文:星期一。
下表显示了MySQL所支持的lc_time_names系统变量的有效语言环境的值:
国家
编/代码
Albanian – Albania
sq_AL
Arabic – Algeria
ar_DZ
Arabic – Bahrain
ar_BH
Arabic – Egypt
ar_EG
Arabic – India
ar_IN
Arabic – Iraq
ar_IQ
Arabic – Jordan
ar_JO
Arabic – Kuwait
ar_KW
Arabic – Lebanon
ar_LB
Arabic – Libya
ar_LY
Arabic – Morocco
ar_MA
Arabic – Oman
ar_OM
Arabic – Qatar
ar_QA
Arabic – Saudi Arabia
ar_SA
Arabic – Sudan
ar_SD
Arabic – Syria
ar_SY
Arabic – Tunisia
ar_TN
Arabic – United Arab Emirates
ar_AE
Arabic – Yemen
ar_YE
Basque – Basque
eu_ES
Belarusian – Belarus
be_BY
Bulgarian – Bulgaria
bg_BG
Catalan – Spain
ca_ES
Chinese – China
zh_CN
Chinese – Hong Kong
zh_HK
Chinese – Taiwan Province of China
zh_TW
Croatian – Croatia
hr_HR
Czech – Czech Republic
cs_CZ
Danish – Denmark
da_DK
Dutch – Belgium
nl_BE
Dutch – The Netherlands
nl_NL
English – Australia
en_AU
English – Canada
en_CA
English – India
en_IN
English – New Zealand
en_NZ
English – Philippines
en_PH
English – South Africa
en_ZA
English – United Kingdom
en_GB
English – United States
en_US
English – Zimbabwe
en_ZW
Estonian – Estonia
et_EE
Faroese – Faroe Islands
fo_FO
Finnish – Finland
fi_FI
French – Belgium
fr_BE
French – Canada
fr_CA
French – France
fr_FR
French – Luxembourg
fr_LU
French – Switzerland
fr_CH
Galician – Spain
gl_ES
German – Austria
de_AT
German – Belgium
de_BE
German – Germany
de_DE
German – Luxembourg
de_LU
German – Switzerland
de_CH
Greek – Greece
el_GR
Gujarati – India
gu_IN
Hebrew – Israel
he_IL
Hindi – India
hi_IN
Hungarian – Hungary
hu_HU
Icelandic – Iceland
is_IS
Indonesian – Indonesia
id_ID
Italian – Italy
it_IT
Italian – Switzerland
it_CH
Japanese – Japan
ja_JP
Korean – Republic of Korea
ko_KR
Latvian – Latvia
lv_LV
Lithuanian – Lithuania
lt_LT
Macedonian – FYROM
mk_MK
Malay – Malaysia
ms_MY
Mongolia – Mongolian
mn_MN
Norwegian – Norway
no_NO
Norwegian(Bokmål) – Norway
nb_NO
Polish – Poland
pl_PL
Portugese – Brazil
pt_BR
Portugese – Portugal
pt_PT
Romanian – Romania
ro_RO
Russian – Russia
ru_RU
Russian – Ukraine
ru_UA
Serbian – Yugoslavia
sr_RS
Slovak – Slovakia
sk_SK
Slovenian – Slovenia
sl_SI
Spanish – Argentina
es_AR
Spanish – Bolivia
es_BO
Spanish – Chile
es_CL
Spanish – Columbia
es_CO
Spanish – Costa Rica
es_CR
Spanish – Dominican Republic
es_DO
Spanish – Ecuador
es_EC
Spanish – El Salvador
es_SV
Spanish – Guatemala
es_GT
Spanish – Honduras
es_HN
Spanish – Mexico
es_MX
Spanish – Nicaragua
es_NI
Spanish – Panama
es_PA
Spanish – Paraguay
es_PY
Spanish – Peru
es_PE
Spanish – Puerto Rico
es_PR
Spanish – Spain
es_ES
Spanish – United States
es_US
Spanish – Uruguay
es_UY
Spanish – Venezuela
es_VE
Swedish – Finland
sv_FI
Swedish – Sweden
sv_SE
Tamil – India
ta_IN
Telugu – India
te_IN
Thai – Thailand
th_TH
Turkish – Turkey
tr_TR
Ukrainian – Ukraine
uk_UA
Urdu – Pakistan
ur_PK
Vietnamese – Viet Nam vi_VN
请参阅中的以下orders表:
mysql> desc orders; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+-------+ | orderNumber | int(11) | NO | PRI | NULL | | | orderDate | date | NO | | NULL | | | requiredDate | date | NO | | NULL | | | shippedDate | date | YES | | NULL | | | status | varchar(15) | NO | | NULL | | | comments | text | YES | | NULL | | | customerNumber | int(11) | NO | MUL | NULL | | +----------------+-------------+------+-----+---------+-------+ 7 rows in set
以下语句返回2014年按工作日名称分组的订单计数。
SELECT DAYNAME(orderdate) weekday, COUNT(*) total_orders FROM orders WHERE YEAR(orderdate) = 2004 GROUP BY weekday ORDER BY total_orders DESC;
执行上面查询语句,得到以下结果 –
+---------+--------------+ | weekday | total_orders | +---------+--------------+ | 星期三 | 37 | | 星期一 | 28 | | 星期日 | 27 | | 星期二 | 22 | | 星期六 | 21 | | 星期四 | 14 | | 星期五 | 2 | +---------+--------------+ 7 rows in set
星期三的订单数量是最高的,星期五有两个订单。
在本教程中,您已经学习了如何使用MySQL DAYNAME函数获取特定日期的工作日名称。
¥ 我要打赏 纠错/补充 收藏