mysql笔记（5）--集合函数，时间日期函数，字符串匹配函数

Author：傻猫
发布时间：March 1, 2012
6188views
No comments
3174 words
Categories：弄数据

mysql中的集合函数有：count（），sum（），avg（），min（），max（），时间和日期函数等。
1.&nbsp;&nbsp; 1） select count(*) from xindi where phone=123456;//计算表xindi中phone=123456的个数 或者 select count(phone)from xindi where phone=123456;
&nbsp;&nbsp; 2)select count(phone)from xindi; //phone 列的数目
&nbsp;&nbsp; 3）select count(distinct phone)from xindi;//有多少个不同种类的电话
&nbsp;&nbsp;&nbsp; 4）如果种类phone出现了不止一次，它将只被计算一次。关键字DISTINCT 决定了只有互不相同的值才被计算。
通常，当你使用COUNT（）时，字段中的空值将被忽略，不算入内。
另外，COUNT()函数通常和GROUP BY子句配合使用，例如可以这样返回每种phone的数目：
select phone, count(phone) from xindi group by phone;
5）select avg（phone）from xindi; // 在计算平均时，忽略空值，phone共四个值，两个空值，除以时只除2.
select name,avg(phone) from xindi group by(name); // 计算每个名字的phone 的平均值
6）select sum(phone) from xindi;
&nbsp;&nbsp; Select name, sum(phone) group by (name);
7)极值：
Select min（phone）from xindi；
Select name，min（phone）from xindi；//这是错误的sql语句，因为集合函数不能和非分组的列混合使用，上述name未分组。所以应用下面的语句：
select name,min(phone) from xindi group by name;
2.操作时间和日期：日期和时间类型：DATETIME、DATE、TIMESTAMP、TIME和YEAR这5种类型的每一个都有合法值的一个范围，而&ldquo;零&rdquo;当你指定确实不合法的值时被使用。
a． 当前日期 select curdate（）；select curdate（）+0；
&nbsp;&nbsp; 当前时间 select curtime（）；select curtime（）+0；
b． 自动记录数据的改变时间：
&nbsp;&nbsp;&nbsp;&nbsp; TIMESTAMP类型提供一种类型，TIMESTAMP值可以从1970的某时的开始一直到2037年，精度为一秒，其值作为数字显示。你可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。
mysql&gt; CREATE TABLE student
-&gt; (
-&gt; id int,
-&gt; name char(16),
-&gt; time timestamp
-&gt; );
向表中插入记录，可以查看效果：
Insert student values（1,&rsquo;jim&rsquo;,null）;
select *from student;
update student set name=&rsquo;lan&rsquo;where id=1;
select*from student; //time的值会改变
有时希望不更改任何值，也能达到修改timestamp列的值，这时设置该列的值为null，mysql就可以自动更新timestamp的值：
Update student set time=null where id=1;
Select*from student ;//这时timestamp的值已改变
如果，当你创建一个行时，你想要一个TIMESTAMP被设置到当前的日期和时间，但在以后无论何时行被更新时都不改变，可以这样
Update student set name=&rsquo;yun&rsquo;,time=time where id=1;
Select *from student where id=1;
TIMESTAMP的存储需求是4字节，而DATETIME列的存储需求是8字节。
c.返回时间和日期
mysql&gt; SELECT * FROM weblog WHERE entrydate&gt;="2001-02-08" AND entrydate&lt;"2001-02-09" ;
或者 mysql&gt; SELECT * FROM weblog WHERE entrydate LIKE '2001-02-08%' ;
注：LIKE运算符和模式匹配，是通过比较串值进行的，因此必须使用标准的时间书写格式，YYYY-MM-DD HH-MM-SS
d. 比较日期和时间：
mysql&gt; SELECT FROM_DAYS(729669); -&gt; '1997-10-07' &nbsp; &nbsp;TO_DAYS(date) 给出一个日期 date，返回一个天数(从 0 年开始的天数)： mysql&gt; SELECT TO_DAYS(950501); -&gt; 728779 mysql&gt; SELECT TO_DAYS('1997-10-07'); -&gt; 729669 TO_DAYS()&nbsp; 无意于使用先于格里高里历法(即现行的阳历)(1582)出现的值，因为它不考虑当历法改变时所遗失的天数。 &nbsp; FROM_DAYS(N) 给出一个天数 N，返回一个 DATE 值： 返回2个时间相差的天数
select to_days(now())-to_days('20100510');
 3. 字符串模式匹配：
SQL的模式匹配允许你使用 &ldquo;_&rdquo;匹配任何单个字符，而&ldquo;%&rdquo;匹配任意数目字符(包括零个字符)。在MySQL中，SQL的模式缺省是忽略大小写的。
select*from xindi where name like "l%i";
select*from xindi where name like " %i";
select*from xindi where name like "l% ";
select*from xindi where name like "l___";//l后面有三个字符的
mysql&gt; SELECT * FROM pet WHERE name REGEXP "^[bB]";
mysql&gt; SELECT * FROM pet WHERE name REGEXP "fy$";
mysql&gt; SELECT * FROM pet WHERE name REGEXP "[wW]";
mysql&gt; SELECT * FROM pet WHERE name REGEXP "^.....$";//重复5次（有5个字符)
mysql&gt; SELECT * FROM pet WHERE name REGEXP "^.{5}$"; //重复5次(有5个字符）