蒙在股里 发表于 2015-1-16 20:12:57

MYSQL教程之在MySQL中操纵日期和工夫

RDBMS并非没有局限性。它们难以扩展，需要大量的资源来配置和维护，比如时间、硬件和人力。同样，它们往往遵循峰值性能模型，这就要求系统按照峰值容量来配置可用性，而不考虑典型的数据使用情况。择要：日期和工夫函数对创建一个站点长短常有效的。站点的仆人常常对一个表中的数据什么时候被更新感乐趣。经由过程日期和工夫函数，你能够在秒级跟踪一个表的改动。
日期和工夫函数对创建一个站点长短常有效的。站点的仆人常常对一个表中的数据什么时候被更新感乐趣。经由过程日期和工夫函数，你能够在秒级跟踪一个表的改动。
日期和工夫范例是DATETIME、DATE、TIMESTAMP、TIME和YEAR。这些的每个都有正当值的一个局限，而“零”当你指定的确分歧法的值时被利用。注重，MySQL同意你存储某个“不严厉地”正当的日期值，比方1999-11-31，缘故原由我们以为它是使用程序的义务来处置日期反省，而不是SQL服务器。为了使日期反省更“快”，MySQL仅反省月份在0-12的局限，天在0-31的局限。上述局限如许被界说是由于MySQL同意你在一个DATE或DATETIME列中存储日期，这里的天或月是零。这对存储你不晓得正确的日期的一个诞辰的使用程序来讲是极为有效的，在这类情形下，你复杂地存储日期象1999-00-00或1999-01-00。（固然你不克不及希冀从函数如DATE_SUB()或DATE_ADD()失掉相似以这些日期的准确值）。
前往以后日期和工夫
经由过程函数GETDATE（），你能够取得以后的日期和工夫。比方，
CURDATE()前往以后日期
CURRENT_DATE
以YYYY-MM-DD或YYYYMMDD格局前往明天日期值，取决于函数是在一个字符串仍是数字高低文被利用。
mysql>selectCURDATE();
+------------+
|CURDATE()|
+------------+
|2001-02-20|
+------------+
mysql>selectCURDATE()+0;
+-------------+
|CURDATE()+0|
+-------------+
|20010220|
+-------------+
CURTIME()前往以后工夫
以HH:MM:SS或HHMMSS格局前往以后工夫值，取决于函数是在一个字符串仍是在数字的高低文被利用。
mysql>selectCURTIME();
+-----------+
|CURTIME()|
+-----------+
|10:42:38|
+-----------+
mysql>selectCURTIME()+0;
+-------------+
|CURTIME()+0|
+-------------+
|104525|
+-------------+
NOW()前往以后时代和工夫
NOW()以YYYY-MM-DDHH:MM:SS的格局大概YYYYMMDDHHMMSS的格局前往日期和工夫值，取决于高低文。
mysql>selectnow();
+---------------------+
|now()|
+---------------------+
|2001-02-2010:45:57|
+---------------------+
mysql>selectnow()+0;
+----------------+
|now()+0|
+----------------+
|20010220105635|
+----------------+
这些失掉以后日期和工夫的函数，关于日期和工夫的盘算很便利，特别是盘算一个工夫到如今的工夫差。比方，在pet表中，我们以天为单元盘算宠物的岁数：
mysql>SELECTname,CURDATE()-birthFROMpet;
+----------+-----------------+
|name|CURDATE()-birth|
+----------+-----------------+
|Fluffy|80016|
|Claws|69903|
|Buffy|119707|
|Chirpy|29309|
|Fang|109393|
|Bowser|109389|
|Whistler|39011|
|Slim|49791|
|Puffball|19890|
+----------+-----------------+


主动纪录数据的改动工夫
TIMESTAMP列范例供应一品种型，TIMESTAMP值能够从1970的某时的入手下手一向到2037年，精度为一秒，其值作为数字显现。你可使用它主动地用以后的日期和工夫标志INSERT或UPDATE的操纵。假如你有多个TIMESTAMP列，只要第一个主动更新。
主动更新第一个TIMESTAMP列鄙人列任何前提下产生：
列没有明白地在一个INSERT或LOADDATAINFILE语句中指定。
列没有明白地在一个UPDATE语句中指定且一些别的的列改动值。（注重一个UPDATE设置一个列为它已有的值，这将不引发TIMESTAMP列被更新，由于假如你设置一个列为它以后的值，MySQL为了效力而疏忽变动。）
你明白地设定TIMESTAMP列为NULL.
除第一个之外的TIMESTAMP列也能够设置到以后的日期和工夫，只需将列设为NULL，或NOW()。
比方，创立以下的表：
mysql>CREATETABLEstudent
->(
->idint,
->namechar(16),
->englishtinyint,
->chinesetinyint,
->historytinyint,
->timetimestamp
->);

向表中拔出纪录，能够检察效果：
mysql>INSERTstudent(id,name,englisht,Chinese,history)VALUES(11,”Tom”,66,93,67);
检察纪录的存储情形：
mysql>SELECT*FROMstudent;
+------+---------+---------+---------+---------+----------------+
|id|name|english|chinese|history|time|
+------+---------+---------+---------+---------+----------------+
|11|Tom|66|93|67|20010220123335|
+------+---------+---------+---------+---------+----------------+
你能够看到time列记录下了数据录进时的工夫值。假如你更新改纪录，在检察操纵的了局：
mysql>UPDATEstudentSETenglish=76WHEREid=11;
mysql>SELECT*FROMstudent;

+------+------+---------+---------+---------+----------------+
|id|name|english|chinese|history|time|
+------+------+---------+---------+---------+----------------+
|11|Tom|76|93|67|20010220125736|
+------+------+---------+---------+---------+----------------+
能够分明的看到，time列的工夫被主动变动为修正纪录的工夫。
偶然候你但愿不变动任何值，也能打到修正TIMESTAMP列的值，这时候只需设置该列的值为NULL，MySQL就能够主动更新TIMESTAMP列的值：
mysql>UPDATEstudentSETtime=NULLWHEREid=11;
mysql>select*fromstudentwhereid=11;
+------+------+---------+---------+---------+----------------+
|id|name|english|chinese|history|time|
+------+------+---------+---------+---------+----------------+
|11|Tom|76|93|67|20010220130517|
+------+------+---------+---------+---------+----------------+
经由过程明白地设置但愿的值，你能够设置任何TIMESTAMP列为分歧于以后日期和工夫的值，即便对第一个TIMESTAMP列也是如许。比方，假如，当你创立一个行时，你想要一个TIMESTAMP被设置到以后的日期和工夫，但在今后不管什么时候行被更新时都不改动，你可使用如许利用：
让MySQL外行被创立时设置列，这将初始化它为以后的日期和工夫。
当你实行随后的对该行中其他列的变动时，明白设定TIMESTAMP列为它确当前值。
比方，当你在修正列时，能够把原本的值付给TIMESTAMP列：
mysql>UPDATEstudentSETenglish=66,time=timeWHEREid=11;
mysql>select*fromstudentwhereid=11;

+------+------+---------+---------+---------+----------------+
|id|name|english|chinese|history|time|
+------+------+---------+---------+---------+----------------+
|11|Tom|66|93|67|20010220130517|
+------+------+---------+---------+---------+----------------+
另外一方面，你大概发明，当你想要完成下面这个效果时，很简单用一个你用NOW()初始化的DATETIME列然后不再改动它，如许大概间接些。可是，TIMESTAMP列的今后优点是存储请求对照小，节俭空间。TIMESTAMP的存储需求是4字节，而DATETIME列的存储需求是8字节。
前往日期和工夫局限
当你剖析表中的数据时，你大概但愿掏出某个特准时间的数据。我们用上面一个表来仿照一个web站点的纪录。
mysql>CREATETABLEweblog
->(
->datafloat,
->entrydatedatetime
->);

然后随机的增添几个数据：
mysql>INSERTweblogVALUES(rand(),now());
rand()函数前往一个随机的浮点值，now()函数前往以后工夫。多实行下面语句几回，失掉一个作为测试的表。
最为测试你还能够增添一个值：
mysql>INSERTweblogVALUES(rand(),”2001-02-08”);
这条语句，拔出一个entry为”2001-02-0800:00:00”的值（假定如今为2001年2月8日），你能够检察这个表的值：
mysql>select*fromweblog;
+-----------+---------------------+
|data|entrydate|
+-----------+---------------------+
|0.973723|2001-02-0800:00:00|
|0.437768|2001-02-0813:57:06|
|0.327279|2001-02-0813:57:09|
|0.0931809|2001-02-0813:58:29|
|0.198805|2001-02-0813:57:54|
+-----------+---------------------+
你大概对特定的某一天中DD好比说2001年2月18日DD会见者在你站点上的举动感乐趣。要掏出这类范例的数据，你大概会试图利用如许的SELECT语句：
mysql>SELECT*FROMweblogWHEREentrydate="2001-02-08"
不要如许做。这个SELECT语句不会前往准确的纪录DD它将只前往值为2000-02-0800:00:00的纪录，换句话说，只前往当天零点零时的纪录。下面语句的了局为：
+----------+---------------------+
|data|entrydate|
+----------+---------------------+
|0.973723|2001-02-0800:00:00|
+----------+---------------------+
要前往准确的纪录，你必要合用日期和工夫局限。有不止一种路子能够做到这一点。
1、利用干系运算符和逻辑运算符来限定工夫局限
比方，上面的这个SELECT语句将能前往准确的纪录：
mysql>SELECT*FROMweblog
->WHERE　entrydate>="2001-02-08"ANDentrydate<"2001-02-09";
这个语句能够完成义务，由于它拔取的是表中的日期和工夫年夜于即是2001-02-0800:00:00并小于2001-02-0900:00:00的纪录。换句话说，它将准确地前往2000年2月8日这一天输出的每笔记录。其了局为：
+-----------+---------------------+
|data|entrydate|
+-----------+---------------------+
|0.973723|2001-02-0800:00:00|
|0.437768|2001-02-0813:57:06|
|0.327279|2001-02-0813:57:09|
|0.0931809|2001-02-0813:58:29|
|0.198805|2001-02-0813:57:54|
+-----------+---------------------+
2、另外一种办法是，你可使用LIKE来前往准确的纪录。经由过程在日期表达式中包括通配符“％”，你能够婚配一个特定日期的一切工夫。
这里有一个例子：
mysql>SELECT*FROMweblogWHEREentrydateLIKE2001-02-08%;
这个语句能够婚配准确的纪录。由于通配符“％”代表了任什么时候间。
+-----------+---------------------+
|data|entrydate|
+-----------+---------------------+
|0.973723|2001-02-0800:00:00|
|0.437768|2001-02-0813:57:06|
|0.327279|2001-02-0813:57:09|
|0.0931809|2001-02-0813:58:29|
|0.198805|2001-02-0813:57:54|
+-----------+---------------------+
3、下面两种办法的异同
因为利用干系运算符举行的是对照历程，时转换成外部的存储格局落后行的，因而，因而工夫的誊写能够不是那末严厉请求。
比方，上面几种写法是等价的：
mysql>SELECT*FROMweblogWHEREentrydate>="2001-02-08";
mysql>SELECT*FROMweblogWHEREentrydate>="2001-2-8";
mysql>SELECT*FROMweblogWHEREentrydate>="2001*02*08";
mysql>SELECT*FROMweblogWHEREentrydate>="20010208";
SELECT*FROMweblogWHEREentrydate>="2001/2/8";

而利用LIKE运算符和形式婚配，是经由过程对照串值举行的，因而必需利用尺度的工夫誊写格局，YYYY-MM-DDHH-MM-SS。
对照日期和工夫
已知两个日期，对照它们的前后，能够间接求出它们的差和零值对照，也能够使用已知的工夫函数：
TO_DAYS(date)
给出一个日期date，前往一个天数(从0年的天数)，date能够是一个数字，也能够是一个串值，固然更能够是包括日期的工夫范例。
mysql>selectTO_DAYS(960501);
+-----------------+
|TO_DAYS(960501)|
+-----------------+
|729145|
+-----------------+
mysql>selectTO_DAYS(1997-07-01);
+-----------------------+
|TO_DAYS(1997-07-01)|
+-----------------------+
|729571|
+-----------------------+
比方：前往2个工夫相差的天数(21世纪已已往了几天)
mysql>selectto_days(now())-to_days(20010101);
+---------------------------------------------------+
|to_days(now()-00000012000000)-to_days(20010101)|
+---------------------------------------------------+
|38|
+---------------------------------------------------+
MySQL已经为支持所有最流行的Web2.0语言做好了准备，诸如Ruby、Ajax等，当然还有PHP。有的业界分析师说过，“每一个Web2.0公司实质上就是一个数据库公司。

若天明 发表于 2015-1-18 18:46:10

一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。）

乐观 发表于 2015-1-24 12:53:07

你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

飘灵儿 发表于 2015-2-1 16:00:22

这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

爱飞 发表于 2015-2-7 08:20:51

这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

飘飘悠悠 发表于 2015-2-21 06:20:34

呵呵，这就是偶想说的

海妖 发表于 2015-3-6 19:58:51

你可以简单地认为适合的就是好，不适合就是不好。

兰色精灵 发表于 2015-3-13 08:08:39

另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程);

小魔女 发表于 2015-3-20 17:28:52

sqlserver的痛苦之处在于有用文档的匮乏，很多只是表明的东西
页: [1]
查看完整版本: MYSQL教程之在MySQL中操纵日期和工夫