mysql必会比知电子_mysql必知必会（三）

十一、使用数据操纵函数

在这章中，你将会学到mysql函数的类型有哪些和如何使用这些函数。

1、使用函数

大部分的SQL工具都支持如下类型的函数：文本函数是被用来操纵文本字符串(例如修剪或者填充值和转换值为首字母大写或者都小写等)

数字函数是在数字数据中国呢被使用执行算术操作(例如返回绝对数字和执行代数运算)

日期和时间函数是用来操纵日期和时间值和从这些元素中提取特殊的元素(例如返回日期和检查日期有效性的不同)

系统函数返回DBMS被使用情况的信息(例如返回用户登陆信息或者检查版本信息)

(1)文本操纵函数

select vend_name,upper(vend_name) as vend_name_upcase from vendors order by vend_name;

这行命令我们可以看到，使用upper函数将vend_nane属性下的所有字段都转换成了大写，并将它命名为vend_name_upcase

类似的函数

left() 从左边获取字符

length() 返回字符的长度

locate() 返回字符串substr中第一次出现子字符串的位置 str

lower() 转换成小写

ltrim() 将左侧空格删除

right() 从右边获取字符

rtrim() 将右侧空格删除

soundex() 是一个将任何文本串转换为描述语音表示的字母模式的算法

substring() 字符串中获取子字符串

Upper() 全部大写

这里我们举个例子

select cust_name,cust_contact from customers where cust_contact = ‘Y. Lie’;

也许在录入时出错，也许顾客输入错误，发现输出结果为空

这时我们使用soundex函数

select cust_name,cust_contact from customers where cust_contact = soundex(‘Y. Lie’);

找到同音的顾客

(2)日期和时间操纵函数

日期和时间是使用特殊的内部格式存储在表格中，所以可以被快速有效的过滤，也可以节省物理存储空间

adddate() 添加一个日期，天，周等等

addtime() 添加一个时间，小时，分钟等等

curdate() 返回当前日期

curtime() 返回当前时间

date() 返回完整时间中的日期部分

datediff() 返回两个日期之间的天数

date_add() 向日期添加指定的时间间隔

date_format() 用于以不同的格式显示日期/时间数据

day() 返回日期

dayofweek() 返回在本周是星期几

hour() 返回对应的小时

minute() 返回对应的分钟

month() 返回日期中对应的月份

now() 返回当前日期和时间

second() 返回当前的秒

time() 返回日期时间中的时间

year() 返回日期中的年份

select cust_id,order_num from orders where year(order_date) = 2005 and month(order_date) = 9;

这句话含义就是从订单表中找到2005年9月份的cust_id和order_num

(3) 数字操纵函数

abs() 返回一个数字的绝对值

cos() 返回余弦值

exp() 返回e(自然对数的底)的X次方的值

mod() 返回余数

pi() 返回pi的值

rand() 返回随机数

sin() 返回正弦值

sqrt() 返回开方值

tan() 返回正切值

十二、数据汇总

1、使用合计函数

我们经常需要去汇总字段的总和，mysql提供了特殊的方法，使用这些函数，mysql查询是经常被使用通过分析和报告的目的来检索数据，例如：决定表格中的行数(或者符合某种条件的行数)

获得行的小组的总和

发现最高，最低和平均值

avg() 返回平均值

count() 返回行的数量

max() 返回最大值

min() 返回最小值

sum() 返回一列中数的总和

(1)avg() 函数

上面我们知道avg()函数返回的平均值，例如这个例子

select avg(prod_price) as avg_price from products;

我们可以看到它将所有的prod_price做了平均值的计算

(2)比如我们现在只想要id = 1003的价格的总和，那么：

select avg(prod_price) as avg_price from products where vend_id = 1003;

(2)count()函数

count()函数可以计算出符合标准的行的数量

count() 可以被使用在两种方面：使用count(*) 计算出行的个数，无论这个行的值是否为空

使用count(column)计算特殊列有值的行的个数，忽略空值。

我们来看第一种

select count(*) as num_cust from customers;

在这个例子中，count(*)显示出了所有的行

第二个例子

select count(cust_email) as num_cust from customers;

(3)max()函数

如max含义所示，它是找出对应的最大值

select max(prod_price) as max_price from products;

(4)min()函数

它是找出属性中对应的最小值

select min(prod_price) as max_price from products;

(5)sum()函数

sum()返回一列数的总和，例如

select sum(quantity) as items_ordered from orderitems where order_num = 20005;

2、不同值的总和

mysql5或者之后的版本使用distinct在总和函数中。

这五个汇总函数可以通过两种方式被使用：执行所有行的计算，默认为该项

仅仅执行不重复的值，使用distinct

select avg(distinct prod_price) as avg_price from products;

注意：distinct在进行count()函数时，只能使用count(column)的，不能使用count( distinct *)

3、连接聚合函数

select count(*) as num_items, min(prod_price) as price_min, max(prod_price) as price_max, avg(prod_price) as price_avg from products;

因为所有的聚合函数返回的都是一个数值，所有可以将他们整合到一起。

十三、数据分组

1、理解数据分组

在先前的章节中，你学到了SQL聚合函数可以用来汇总数据，这能够让你计算行数，计算总和和平均数，和包含最大和最小值。

例如下面这个例子

select count(*) as num_prods from products where vend_id = 1003;

但是如果你想返回每一个供应商的数量，或者我想知道谁提供了一个产品，谁提供超过10种产品？

这是group 的作用，他可以统计出每一个小组的总和

2、创建分组

select vend_id, count(*) as num_prods from products group by vend_id;

上述select返回两列，vend_id是包含产品供应商的id,和num_prods是计算总数，group by+vend_id 是将根据vend_id进行排序，这导致每一个vend_id 计算一个 num_prods

在你使用group by之前，有一些重要的规则需要你知道：group by从句可以包含你想要的许多列，他可以建造组

group by跟在where后面

3、过滤分组

现在我们知道了group by可以用来分组，但是例如你只想要大于等于两个订单顾客信息，要怎么写呢？在这里where 不是一个好的方法，所以我们使用什么来代替where呢？having

having过滤组而where过滤行

select cust_id, count(*) as orders from orders group by cust_id having count(*) >=2;

我们可以这样理解where和having的不同，where是在group之前过滤，而having是在group之后在过滤

那么大家可能会想那么有没有where和having同时用到的地方呢？答案是有的，例如

select vend_id, count(*) as num_prods from products where prod_price >= 10 group by vend_id having count(*) >= 2;

这段话的含义是：找到商品价格大于10的，并将他们进行分组，统计vend_id 和count(*) 然后找出count(*) >= 2 的。

4、分组和排序

理解group by 和order by非常不一样是很重要的，例如

select order_num, sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >= 50 order by ordertotal;

5、select 从句汇总

select 本返回的列

from 检索的表

where 行过滤

group by 分组

having 分组过滤

order by 排序

limit 检索的行的个数