函数使用方法：

mysql> select UPPER(cust_name) from customers; +------------------+ | UPPER(cust_name) | +------------------+ | VILLAGE TOYS | | KIDS PLACE | | FUN4ALL | | FUN4ALL | | THE TOY STORE | +------------------+ 5 rows in set

 常用的函数：

1.文本处理函数

 SOUNDEX（）函数的使用例子：查找读音相似的数据，如果where cost_contact = ‘Michalle green’ 则查不到任何数据，但是michalle green 和 michelle green 读音相似可以用SOUNDEX () 函数来查找数据;

mysql> Select * from customers where SOUNDEX(cust_contact) = Soundex("michalle green"); +------------+------------+----------------------+-----------+------------+----------+--------------+----------------+------------+ | cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | +------------+------------+----------------------+-----------+------------+----------+--------------+----------------+------------+ | 1000000002 | Kids Place | 333 South Lake Drive | Columbus | OH | 43333 | USA | Michelle Green | NULL | +------------+------------+----------------------+-----------+------------+----------+--------------+----------------+------------+ 1 row in set

 2.日期处理函数

日期函数比较杂，每种数据库的用法都有所不同；

在SQL Server 中检索2012 年的所有订单，可如下进行：

SELECT order_num FROM Orders WHERE DATEPART(yy, order_date) = 2012;

在Access 中使用如下版本：

SELECT order_num FROM Orders WHERE DATEPART('yyyy', order_date) = 2012;

mysql版：使用year() 函数提取年份

mysql> select * -> from orders -> where year(order_date) = 2012; +-----------+---------------------+------------+ | order_num | order_date | cust_id | +-----------+---------------------+------------+ | 20005 | 2012-05-01 00:00:00 | 1000000001 | | 20006 | 2012-01-12 00:00:00 | 1000000003 | | 20007 | 2012-01-30 00:00:00 | 1000000004 | | 20008 | 2012-02-03 00:00:00 | 1000000005 | | 20009 | 2012-02-08 00:00:00 | 1000000001 | +-----------+---------------------+------------+ 5 rows in set

3.数值处理函数

4.汇总数据， 聚集函数：

有时候我们需要计算某一列的平均值或某一列有多少行，这个时候不需要将它们的实际数据检索出来，我们可以使用sql 提供的函数 来完成操作：

AVG()函数：有where和没有where两种使用方法

mysql> select AVG(item_price) from orderitems; +-----------------+ | AVG(item_price) | +-----------------+ | 5.74 | +-----------------+ 1 row in set mysql> select AVG(item_price) from orderitems where prod_id='BR03 '; +-----------------+ | AVG(item_price) | +-----------------+ | 11.615 | +-----------------+ 1 row in set

COUNT() 函数：如果使用COUNT(*) 会计算所有的列的数量，如果指定列，则不会计算该列值为null 的行 

mysql> select COUNT(*) from customers; +----------+ | COUNT(*) | +----------+ | 5 | +----------+ 1 row in set mysql> select COUNT(cust_email) from customers; +-------------------+ | COUNT(cust_email) | +-------------------+ | 3 | +-------------------+ 1 row in set

SUM（）函数 ： 

mysql> select SUM(item_price) from orderitems; +-----------------+ | SUM(item_price) | +-----------------+ | 103.32 | +-----------------+ 1 row in set mysql> select SUM(item_price*order_item) from orderitems; +----------------------------+ | SUM(item_price*order_item) | +----------------------------+ | 234.05 | +----------------------------+ 1 row in set

 

 

 

 

 

 

 

 

 

 

 

转载于:https://www.cnblogs.com/lastingjava/p/10420733.html