MySQL学习笔记5——函数和索引

一、函数

MySQL提供了很多功能强大，而且使用起来非常方便的函数，包括数学函数、字符串处理函数和条件判断函数等。

1、数学函数

数学函数主要用来处理数值数据，常用的主要有3类，分别是取整函数ROUND()、CEIL()、 FLOOR()，绝对值函数ABS()和求余函数MOD()。

• 向上取整CEIL(X) 和 CEILING(X)：返回大于等于X的最小INT型整数。
• 向下取整FLOOR(X)：返回小于等于X的最大INT型整数。
• 舍入函数ROUND(X,D)：X表示要处理的数，D表示保留的小数位数，处理的方式是四舍五入。ROUND(X)表示保留0位小数。
• 绝对值函数ABS(X)：获取X的绝对值。
• MOD（X,Y）：获取X被Y除后的余数。

SELECT
	c.membername AS '会员',
	b.transactionno AS '单号',
	b.transdate AS '交易时间',
	d.goodsname AS '商品名称',
	a.salesvalue AS '交易金额',
	FLOOR(a.salesvalue) AS '积分'	-- FLOOR向下取整
FROM
demo.transactiondetails a
JOIN
demo.transactionhead b ON (a.transactionid = b.transactionid) 
JOIN
demo.membermaster c ON (b.memberid = c.memberid)
JOIN
demo.goodmaster d ON (a.itemnumber = d.itemnumber);

CEIL()函数和FLOOR()用法类似，接下来演示一下ROUND()函数的用法：

SELECT ROUND(salesvalue,2)		-- 保留2位小数
FROM demo.transactiondetails
WHERE transactionid=1 AND itemnumber=1;

2、字符串函数

常用的字符串函数：

• CONCAT(1,2…)：表示把字符串s1、s2…拼接起来，组成一个字符串。
• CAST(表达式AS CHAR)：表示将表达式的值转换成字符串。
• CHAR_ LENGTH(字符串)：表示获取字符串的长度。
• SPACE(n)：表示获取一个由n个空格组成的字符串。

SELECT
CONCAT(goodsname,'(',specification,')') AS 商品信息
FROM
demo.goodmaster
WHERE itemnumber = 1;

打印结果：

SELECT
-- 把数量转换成字符串
-- 计算字符串的长度
CONCAT(CAST(quantity AS CHAR)),
-- 用空格补齐7位，在字符串后面补齐
SPACE(7-CHAR_LENGTH(CONCAT(CAST(quantity AS CHAR)))) AS 数量
FROM
demo.transactiondetails
WHERE transactionid = 1 AND itemnumber = 1;

除此以外，MySQL还支持SUBSTR ()、MID ()、 TRIM ()、LTRIM ()、RTRIM ()等字符串函数。

3、条件判断函数

条件判断函数的主要作用，就是根据特定的条件返回不同的值，常用的有两种：

• IFNULL (V1, V2)：表示如果V1的值不为空值，则返回V1，否则返回V2。
• IF (表达式, V1, V2)：如果表达式为真(TRUE) ，则返回V1，否则返回V2。

SELECT
goodsname,
specification,
-- 我们希望规格是空的商品，在拼接商品信息字符串的时候,规格不要是空。
CONCAT(goodsname,'(',IFNULL(specification,''),')') AS 拼接
FROM
demo.goodmaster;

规格为空时返回拼接信息“橡皮（）”后面打印“（）”和奇怪，所以我们通过条件判断函数将“（）”去除：

SELECT
goodsname,
specification,
-- 这里做判断，如果是空值，返回商品名称，否则就返回拼接规格
IF(
ISNULL(specification),
goodsname,
CONCAT(goodsname,'(',specification,')')
) AS 拼接
FROM
demo.goodmaster;

二、索引

在超市信息系统刚刚开始运营的时候，因为数据量很少，每一次的查询都能很快拿到结果。但是系统运转时间长了以后，数据量不断地累积，变得越来越庞大，很多查询的速度就变得特别慢。

这个时候，我们就采用了MySQL提供的高效访问数据的方法——索引，有效地解决了这个问题，甚至之前的一个需要8秒钟才能完成的查询，现在只用0.3秒就搞定了，速度提升了20多倍。

1、索引是什么

MySQL中的索引，就相当于图书馆的检索目录，它是帮助MySQL系统快速检索数据的一种存储结构。

我们可以在索引中按照查询条件，检索索引字段的值，然后快速定位数据记录的位置，这样就不需要遍历整个数据表了。而且，数据表中的字段越多，表中数据记录越多，速度提升越是明显。

2、单字段索引

MySQL支持单字段索引和组合索引，而单字段索引比较常见。

如何创建单字段索引：

• 可以通过CREATE语句直接给已经存在的表创建索引;
• 可以在创建表的同时创建索引;
• 可以通过修改表来创建索引。

-- 直接给数据表创建索引的语法如下:
CREATE INDEX 索引名 ON TABLE 表名 (字段);

-- 创建表的同时创建索引的语法如下所示:
CREATE TABLE 表名
(
字段 数据类型，
…
{INDEX|KEY}索引名(字段)
)

-- 修改表时创建索弓|的语法如下所示:
ALTER TABLE 表名 ADD {INDEX| KEY} 索引名 (字段);

这里有个小问题要注意一下，给表设定主键约束或者唯一性约束的时候，MySQL 会自动创建主键索引或唯一性索引。 这也是为什么建议在创建表的时候，一定要定义主键的原因之一。

在选择索引字段的时候，建议要选择那些经常被用做筛选条件的字段。这样才能发挥索引的作用，提升检索的效率。

3、组合索引

如果多个索引，且这些索引的字段同时作为筛选字段出现在查询中的时候，MySQL会选择使用最优的索引来执行查询操作。

能不能让这几个筛选字段同时发挥作用呢？这就用到组合索引了。组合索引，就是包含多个字段的索引。MySQL最多支持由16个字段组成的组合索引。

如何创建组合索引：

-- 直接给数据表创建索引
CREATE INDEX 索引名 ON TABLE 表名(字段1，字段2，…);

-- 创建表的同时创建索引
CREATE TABLE 表名
(
字段 数据类型,
…
{INDEX | KEY } 索引名(字段1,字段2,…)
)

-- 修改表时创建索引:
ALTER TABLE 表名 ADD { INDEX| KEY } 索引名 (字段1，字段2，...);

组合索引的多个字段是有序的，遵循左对齐的原则。

4、总结

-- 删除索引
DROP INDEX 索引名 ON 表名；
--删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;

索引的成本
索引能够提升查询的效率，但是创建索引也是有成本的，主要有2个方面：

• 存储空间的开销，是指索引|需要单独占用存储空间。
• 数据操作上的开销，是指一旦数据表有变动，无论是插入一条新数据，还是删除一条旧的数据，甚至是修改数据，如果涉及索引字段，都需要对索引本身进行修改，以确保索弓|能够指向正确的记录。

因此，索引也不是越多越好，创建索引有存储开销和操作开销，需要综合考虑。