一、MySQL基本函数使用
日期和时间函数
-
CURDATE()
或CURRENT_DATE()
返回当前的日期 -
CURTIME()
或CURRENT_TIME()
返回当前的时间 -
DATE_ADD(date,INTERVAL int keyword)
返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如:SELECT DATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
-
DATE_FORMAT(date,fmt)
依照指定的fmt格式格式化日期date值 -
DATE_SUB(date,INTERVAL int keyword)
返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如:SELECT DATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
-
DAYOFWEEK(date)
返回date所代表的一星期中的第几天(1~7) -
DAYOFMONTH(date)
返回date是一个月的第几天(1~31) -
DAYOFYEAR(date)
返回date是一年的第几天(1~366) -
DAYNAME(date)
返回date的星期名,如:SELECT DAYNAME(CURRENT_DATE);
-
FROM_UNIXTIME(ts,fmt)
根据指定的fmt格式,格式化UNIX时间戳ts -
HOUR(time)
返回time的小时值(0~23) -
MINUTE(time)
返回time的分钟值(0~59) -
MONTH(date)
返回date的月份值(1~12) -
MONTHNAME(date)
返回date的月份名,如:SELECT MONTHNAME(CURRENT_DATE);
-
NOW()
返回当前的日期和时间 -
QUARTER(date)
返回date在一年中的季度(1~4),如SELECT QUARTER(CURRENT_DATE);
-
WEEK(date)
返回日期date为一年中第几周(0~53) -
YEAR(date)
返回日期date的年份(1000~9999)
类型转换函数
为了进行数据类型转化,MySQL提供了CAST()
函数,它可以把一个值转化为指定的数据类型。类型有:BINARY
,CHAR
,DATE
,TIME
,DATETIME
,SIGNED
,UNSIGNED
SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0; SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY);
控制流程语句
MySQL有4个函数是用来进行条件操作的,这些函数可以实现SQL的条件逻辑,允许开发者将一些应用程序业务逻辑转换到数据库后台。
-
CASE WHEN[test1] THEN [result1]...ELSE [default] END
如果testN是真,则返回resultN,否则返回default -
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END
如果test和valN相等,则返回resultN,否则返回default -
IF(test,t,f)
如果test是真,返回t;否则返回f -
IFNULL(arg1,arg2)
如果arg1不是空,返回arg1,否则返回arg2 -
NULLIF(arg1,arg2)
如果arg1=arg2返回NULL;否则返回arg1
IFNULL(),它有两个参数,并且对第一个参数进行判断。如果第一个参数不是NULL,函数就会向调用者返回第一个参数;如果是NULL,将返回第二个参数。
SELECT IFNULL(1,2), IFNULL(NULL,10),IFNULL(4*NULL,'false');
NULLIF()函数将会检验提供的两个参数是否相等,如果相等,则返回NULL,如果不相等,就返回第一个参数。
如:SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,4+1);
MySQL的IF()函数也可以建立一个简单的条件测试,这个函数有三个参数,第一个是要被判断的表达式,如果表达式为真,IF()将会返回第二个参数,如果为假,IF()将会返回第三个参数。
如:SELECTIF(1<10,2,3),IF(56>100,'true','false');
CASE函数:
CASE` `[expression ``to` `be evaluated] WHEN` `[val 1] ``THEN` `[result 1] WHEN` `[val 2] ``THEN` `[result 2] WHEN` `[val 3] ``THEN` `[result 3] ...... WHEN` `[val n] ``THEN` `[result n] ELSE` `[``default` `result] END 或者 CASE WHEN [conditional test 1] THEN [result 1] WHEN [conditional test 2] THEN [result 2] ELSE [default result] END
如:SELECT CASE 'green' WHEN 'red' THEN 'stop' WHEN 'green1' THEN 'go' else 'ttt' END;
全文本搜索
为什么要使用全文本搜索:
LIKE关键字,它利用通配操作符匹配文本(和部分文 本)。使用LIKE,能够查找包含特殊值或部分值的行(不管这些值位于列 内什么位置)。 用基于文本的搜索作为正则表达式匹配列值的更进一 步的介绍。使用正则表达式,可以编写查找所需行的非常复杂的匹配模 式。
虽然这些搜索机制非常有用,但存在几个重要的限制。
1、性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行(而且这些搜索极少使用表索引)。因此,由于被搜索行数不断增加,这些搜索可能非常耗时。
2、明确控制——使用通配符和正则表达式匹配,很难(而且并不总 是能)明确地控制匹配什么和不匹配什么。例如,指定一个词必 须匹配,一个词必须不匹配,而一个词仅在第一个词确实匹配的 情况下才可以匹配或者才可以不匹配。
3、智能化的结果——虽然基于通配符和正则表达式的搜索提供了非 常灵活的搜索,但它们都不能提供一种智能化的选择结果的方法。 例如,一个特殊词的搜索将会返回包含该词的所有行,而不区分 包含单个匹配的行和包含多个匹配的行(按照可能是更好的匹配 来排列它们)。类似,一个特殊词的搜索将不会找出不包含该词但 包含其他相关词的行。
所有这些限制以及更多的限制都可以用全文本搜索来解决。在使用 全文本搜索时,MySQL不需要分别查看每个行,不需要分别分析和处理 每个词。MySQL创建指定列中各词的一个索引,搜索可以针对这些词进 行。这样,MySQL可以快速有效地决定哪些词匹配(哪些行包含它们), 哪些词不匹配,它们匹配的频率,等等。
启用全文本搜索支持
1、创建的表必须使用引擎为MyISAM来创建;
2、创建的表必须指定某列作为全文本搜索,FULLTEXT(colunms)
语法:
在索引之后,使用两个函数Match()和Against()执行全文本搜索, 其中Match()指定被搜索的列,Against()指定要使用的搜索表达式。
示例:
SELECT note_text,MATCH(note_text) Against('rabbit') from productnotes;
此SELECT语句检索单个列note_text。由于WHERE子句,一个全 文本搜索被执行。Match(note_text)指示MySQL针对指定的 列进行搜索,Against('rabbit')指定词rabbit作为搜索文本。由于有 两行包含词rabbit,这两个行被返回。
使用查询扩展
查询扩展用来设法放宽所返回的全文本搜索结果的范围。考虑下面 的情况。你想找出所有提到anvils的注释。只有一个注释包含词anvils, 但你还想找出可能与你的搜索有关的所有其他行,即使它们不包含词anvils。
示例:
SELECT note_text,MATCH(note_text) Against('anvils' WITH QUERY EXPANSION) from productnotes;
布尔文本搜索
MySQL支持全文本搜索的另外一种形式,称为布尔方式(boolean mode)。以布尔方式,可以提供关于如下内容的细节:
1、要匹配的词;
2、要排斥的词(如果某行包含这个词,则不返回该行,即使它包含 其他指定的词也是如此);
3、排列提示(指定某些词比其他词更重要,更重要的词等级更高);
4、表达式分组;
4、另外一些内容。
语法:IN BOOLEAN MODE
示例:SELECT note_text,MATCH(note_text) Against('heavy' IN BOOLEAN MODE) from productnotes;
二、管理事务处理
三、分组数据
GROUP BY
组合WITH ROLLUP使用,使用WITH ROLLUP关键字,可以得到每个分组以及汇总每个分组(针对每个分组)的值。
使用场景:增加“总计”行,SELECT IFNULL(vend_id,'总计'),count(1) FROM products GROUP BY vend_id WITH ROLLUP;
HAVING:过滤分组
HAVING非常类似于WHERE。事实上,目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行,而HAVING过滤分组。
select cust_id, count() as orders from orders group by cust_id having count() >=2;
where和having配合使用
select vend_id, count() as orders from products WHERE prod_price>=10 group by vend_id having count() >=2;