Sql学习-常用函数介绍

 函数使用方法：

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