MySQL学习笔记

一、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;

四、正则表达式搜索