当前位置:Gxlcms > mysql > MySQL必知必会之10-14_MySQL

MySQL必知必会之10-14_MySQL

时间:2021-07-01 10:21:17 帮助过:11人阅读

bitsCN.com

10.创建计算字段

计算字段并不实际存在于数据库表中。计算字段是运行时在你SELECT语句内创建的。

字段(filed):基本上与列的意思相同,经常互换使用,不过数据库列一般称为列,而术语字段通常用在计算字段的连接上。

10.1拼接字段

拼接:将值联结到一起构成单个值。

Concat()函数:多数DBMS使用+或||实现拼接,而MYSQL使用Concat()函数实现。

Rtrim()函数:去掉值右边的所有空格

mysql> SELECT CONCAT(RTrim(vend_name),' (',RTrim(vend_country),')')FROM vendors

ORDER BY vend_name;

+-------------------------------------------------------+

| CONCAT(RTrim(vend_name),'(',RTrim(vend_country),')') |

+-------------------------------------------------------+

| ACME (USA) |

| Anvils R Us (USA) |

| Furball Inc. (USA) |

| Jet Set (England) |

| Jouets Et Ours (France) |

| LT Supplies (USA) |

+-------------------------------------------------------+

6 rows in set (0.00 sec)

10.2使用别名

别名用AS关键字赋予。

mysql> SELECT CONCAT(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_titl

e FROM vendors ORDER BY vend_name;

+-------------------------+

| vend_title |

+-------------------------+

| ACME (USA) |

| Anvils R Us (USA) |

| Furball Inc. (USA) |

| Jet Set (England) |

| Jouets Et Ours (France) |

| LT Supplies (USA) |

+-------------------------+

6 rows in set (0.00 sec)

输出结果与以前相同,但是现在列名为vend_title。

10.3执行算术计算

SELECTprod_id,quantity,item_price,quantity*item_price ASexpanded_price FROMorderitems WHERE order_num = 20005;

+---------+----------+------------+------------------+

| prod_id | quantity | item_price |ASexpanded_price |

+---------+----------+------------+------------------+

| ANV01 | 10 | 5.99 | 59.90 |

| ANV02 | 3 | 9.99 | 29.97 |

| TNT2 | 5 | 10.00 | 50.00 |

| FB | 1 | 10.00 | 10.00 |

+---------+----------+------------+------------------+

4 rows in set (0.03 sec)

11.使用数据处理函数

使用函数

大多数SQL实现支持以下类型的函数:

1) 用于处理文本串(如删除或填充值,转换值大小写)的文本函数

2) 用于在数值数据上进行算术操作(如返回绝对值,进行代数运算的数值函数)

3) 用于出来日期和时间并从这些值中提取特定成分(如返回日期之差)的日期和时间函数

4) 返回DBMS正使用的特殊信息(如返回用户登录信息)的系统函数

11.1文本处理函数

Left():返回串左边的字符

Length():返回串的长度

Locate():找出串的一个字串

Lower():将串转换为小写

LTrim():去掉左边的空格

Right():返回串右边的字符

Rtrim():去除列值右边的空格

Soundex():返回串的SOUNDEX值,将任何文本串转换为描述其语音表示的字母数字模式算法

SubString():返回子串的字符

Upper():将文本转换为大写

SELECT vend_name,Upper(vend_name) ASvend_name_upcase FROM vendors;

SELECT cust_name,cust_contact FROM customersWHERE Soundex(cust_contact)= Soundex('Y Lie');//结果如下,输出cust_contact发音和'Y Lie'一致的结果,这里发音一致的是Y Lee。

+-------------+--------------+

| cust_name | cust_contact |

+-------------+--------------+

| Coyote Inc. | Y Lee |

+-------------+--------------+

1 row in set (0.00 sec)

11.2日期和时间处理函数

AddDate() 增加一个日期

AddTime() 增加一个时间

CurDate() 返回当前日期

Data() 返回日期时间的日期部分

DataDiff() 返回两个日期之差

Data_Add() 高度灵活的日期运算函数

Data_Format() 返回一个格式化的日期或时间串

Day() 返回一个日期的天数部分

DayOfWeek () 对于一个日期,返回对应的星期几

Hour() 返回一个时间的小时部分

Minute() 返回一个时间的分钟部分

Mounth() 返回一个日期的月份部分

Now() 返回当前的日期和时间

Second() 返回一个时间秒部分

Time() 返回一个时间的实践部分

Year() 返回一个日期的年份部分

MYSQL日期格式为yyyy-mm-dd

mysql> SELECT cust_id,order_num FROMorders WHERE Date(order_date) = '2005-09-01

';//输出结果如下

+---------+-----------+

| cust_id | order_num |

+---------+-----------+

| 10001 | 20005 |

+---------+-----------+

1 row in set (0.01 sec)

11.3数值处理函数

Abs() 返回一个数的绝对值

Cos() 一个角度的余弦值

Exp() 一个数的指数值

Mod() 除操作数的余数

Pi() 返回圆周率

Rand() 返回一个随机数

Sin() 一个角度的正弦

Sqrt() 一个数的平方根

Tan() 一个角度的正切

12.汇总数据

12.1聚集函数

聚集函数:运行在行组山,计算和返回单个值的函数

AVG() 某列的平均值,忽略值为NULL的行

COUNT() 某列的行数,忽略值为NULL的行

MAX() 某列的最大值

MIN() 某列的最小值

SUM() 某列值之和

还支持一些标准偏差聚集函数,这里不涉及。

SELECT AVG(PROD_PRICE) AS avg_price FROMproducts;

+-----------+

| avg_price |

+-----------+

| 16.133571 |

+-----------+

1 row in set (0.03 sec)

12.聚集不同的值

SELECT AVG(DISTINCT prod_price) AS avg_priceFROM products WHERE vend_id = 1003;

+-----------+

| avg_price |

+-----------+

| 15.998000 |

+-----------+

1 row in set (0.03 sec)

可以看到,在使用了DISTINCT后,此例子中的avg_price比较高,因为有多个物品具有相同的较低价格。

12.3组合聚集函数:

SELECT AVG(prod_price) ASavg_price,MIN(prod_price) AS price_min FROM products WHERE vend_id = 1003;

13.分组数据

分组允许把数据分为多个逻辑组,以便能对每个组进行聚集计算。

13.1创建分组

mysql> SELECT vend_id,COUNT(*) ASnum_prods FROM products GROUP BY vend_id;

+---------+-----------+

| vend_id | num_prods |

+---------+-----------+

| 1001 | 3 |

| 1002 | 2 |

| 1003 | 7 |

| 1005 | 2 |

+---------+-----------+

4 rows in set (0.05 sec)

GROUP BY子句指示MySQL分组数据,然后对每个组而不是整个结果进行聚集。

使用GROUP BY的重要规则:

1) 可以包含任意数目的列,可以嵌套分组

2) 除聚集语句外,SELECT语句中的每个列都必须在GROUPBY子句中给出

3) 如果分组中有NULL值,则将NULL作为一个分组

4) GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前

13.2过滤分组

HAVING支持所有WHERE操作符。

mysql> SELECT cust_id,COUNT(*) AS ordersFROM orders GROUP BY cust_id HAVING COU

NT(*) >= 2;

+---------+--------+

| cust_id | orders |

+---------+--------+

| 10001 | 2 |

+---------+--------+

1 row in set (0.00 sec)

这里的最后一行它过滤COUNT(*)>=2(两个以上的订单)的那些分组。

HAVING和WHERE的区别:WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。

13.3分组和排序

SELECT order_num,SUM(quantity*item_price) AS ordertotal FROMorderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;

+-----------+------------+

| order_num | ordertotal |

+-----------+------------+

| 20005 | 149.87 |

| 20006 | 55.00 |

| 20007 | 1000.00 |

| 20008 | 125.00 |

+-----------+------------+

4 rows in set (0.00 sec)

13.4 SELECT子句的顺序

SELECT->FROM->WHERE->GROUP BY->HAVING->ORDERBY->LIMIT

14 使用子查询

14.1利用子查询进行过滤

SELECT cust_name,cust_contact FROM customers WHERE cust_id IN(SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROMorderitems WHERE prod

_id = 'TNT2'));

+----------------+--------------+

| cust_name |cust_contact |

+----------------+--------------+

| Coyote Inc. | Y Lee |

| Yosemite Place | Y Sam |

+----------------+--------------+

2 rows in set (0.11 sec)

虽然子查询一般与IN操作符结合使用,但也可以用于测试等于(=)、不等于(<>)等。

14.2作为计算字段使用子查询

SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHEREorders.cust_id = customers.cust_id) AS orders FROM customers ORDER BYcust_name;

+----------------+------------+--------+

| cust_name | cust_state| orders |

+----------------+------------+--------+

| Coyote Inc. | MI | 2 |

| E Fudd | IL | 1 |

| Mouse House | OH | 0 |

| Wascals | IN | 1 |

| Yosemite Place | AZ | 1 |

+----------------+------------+--------+

5 rows in set (0.00 sec)

这条select语句对customers表中每个客户返回3列:cust_name,cust_state和orders.orders是一个计算字段,它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中,该子查询执行了5次,因为检索除了5个客户。



==参考MySQL必知必会

bitsCN.com

人气教程排行