SQL从入门到放弃系列——SQL函数[七]

2019年6月26日10:46:23 发表评论 219
摘要

算术函数。
字符串函数。
日期函数。
转换函数。
聚集函数。

算术函数

ABS()

取绝对值。

SELECT ABS(-2),运行结果为 2。

MOD()

取余。

SELECT MOD(101,3),运行结果 2。

ROUND()

四舍五入为指定小数位。两个参数,分别是字段名、小数位数。

SELECT ROUND(37.25,1),运行结果 37.3。

字符串函数

CONCAT()

拼接字符串。

SELECT CONCAT('abc', 123),运行结果为abc123。

LENGTH()

计算字符串长度。一个汉字算三个字符,数字或字母算一个。

SELECT LENGTH('你好'),运行结果为 6。

CHAR_LENGTH()

计算字符串长度。汉字、数字、字母均算一个。

SELECT CHAR_LENGTH('你好'),运行结果为2。

LOWER()

将字符串转为小写。

SELECT LOWER('ABC'),运行结果为 abc。

UPPER()

将字符串转为大写。

SELECT UPPER('abc'),运行结果 ABC。

REPLACE()

替换函数,3个参数。分别是:要替换的表达式或字段名、想要查找的被替换的字符串(旧)、替换成哪个字符串(新)。

SELECT REPLACE('fabcd', 'abc','123'),运行结果为f123d。

SUBSTRING()

截取字符串,3个参数。分别是:待截取的表达式或字段名、开始截取的位置、想要截取的字符串长度。

SELECT SUBSTRING('fabcd', 1,3),运行结果为fab。
扩展:instr函数

instr是一个非常好用的字符串处理函数,几乎所有的字符串分隔都用到此函数。instr函数在Oracle中是返回要截取的字符串在源字符串中的位置。

SQL从入门到放弃系列——SQL函数[七]

举例Oracle11:

SQL从入门到放弃系列——SQL函数[七]

查找'5040203'这个节点及其以下的记录:

SELECT
	*
FROM
	SA_ORG
WHERE
	"INSTR" (ORG_TREE, '5040203') > 0
ORDER BY
	ORG_TREE

SQL从入门到放弃系列——SQL函数[七]

日期函数

CURRENT_DATE()

系统当前日期。

CURRENT_TIME()

系统当前时间,不含日期。

CURRENT_TIMESTAMP()

系统当前日期+时间。

EXTRACT()

抽取具体的年月日。

SELECT EXTRACT(YEAR FROM '2019-06-01'),返回结果为2019。

DATE()

SELECT DATE('2019-04-01 12:00:00:05'),返回结果为 2019-04-01。

YEAR()

返回时间的年份部分。

MONTH()

返回时间的月份部分。

DAY()

返回时间的天数部分。

HOUR()

返回时间的小时部分。

MINUTE()

返回时间的分钟部分。

SECOND()

返回时间的秒部分。

转换函数

转换函数可以转换数据之间的类型,常用的函数:

CAST()

数据类型转换,参数是一个表达式,表达式通过AS关键词分割了两个参数,分别是原始数据、目标数据类型。

例如:

SELECT CAST(123.123 AS INT),运行结果会报错。
SELECT CAST(123.123 AS DECIMAL(8,2)),运行结果为123.12。

CAST函数在转换数据类型时,不会四舍五入,如果原始数值有小数,那么转换为整数的时候就会报错。在MySQL和SQLServer中可以采用DECIMAL(a,b)指定转化的小数类型。DECIMAL(8,2)表示精度为8位(整数加小数位共8位),小数位为2位的数据类型。

COALESCE()

返回第一个非空数值。

SELECT COALESCE(null,1,2),运行结果为1。

关于DATE()函数

举个例子简单感受一下,MySQL5中,表testdate中字段sdate为varchar类型,字段date为datetime类型。

原始数据:

mysql> SELECT * FROM testdate;
+------+------------+---------------------+
| id   | sdate      | date                |
+------+------------+---------------------+
|    1 | 20190601   | 2019-06-01 14:37:54 |
|    2 | 20190602   | 2019-06-02 14:38:10 |
|    3 | 2019-06-03 | 2019-06-03 14:42:03 |
+------+------------+---------------------+
3 rows in set (0.00 sec)

mysql>

使用和不适用DATE函数的区别

mysql> use sqltest;
Database changed
mysql> SELECT * FROM    testdate WHERE date > '20190602';
+------+------------+---------------------+
| id   | sdate      | date                |
+------+------------+---------------------+
|    2 | 20190602   | 2019-06-02 14:38:10 |
|    3 | 2019-06-03 | 2019-06-03 14:42:03 |
+------+------------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM    testdate WHERE DATE(date) > '20190602';
+------+------------+---------------------+
| id   | sdate      | date                |
+------+------------+---------------------+
|    3 | 2019-06-03 | 2019-06-03 14:42:03 |
+------+------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM    testdate WHERE sdate > '20190601';
+------+----------+---------------------+
| id   | sdate    | date                |
+------+----------+---------------------+
|    2 | 20190602 | 2019-06-02 14:38:10 |
+------+----------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM    testdate WHERE DATE(sdate) > '20190601';
+------+------------+---------------------+
| id   | sdate      | date                |
+------+------------+---------------------+
|    2 | 20190602   | 2019-06-02 14:38:10 |
|    3 | 2019-06-03 | 2019-06-03 14:42:03 |
+------+------------+---------------------+
2 rows in set (0.00 sec)

mysql>
结论:

当无法确认一个字段的数据类型是字符串还是datetime类型时,如果对日期部分进行比较,使用DATE()函数来比较是更安全的。

使用SQL函数可能带来的问题

不同DBMS之间差异很大,只有少数函数是被DBMS同时支持的,所以使用SQL函数的代码可移植性很差,在使用函数的时候需要特别注意。

SQL大小写规范问题

MySQL在Linux系统中,库名、表名、变量名是严格区分大小写,字段名不区分大小写;

在Windows系统中,全部不区分大小写。

因此,需要一个命名规范:

  1. 关键字、函数名全部大写;
  2. 库名、表名、字段名全部小写;
  3. SQL语句以分号结尾。

聚集函数

聚集函数共5个。

COUNT()

统计总行数。

MAX()

最大值。

MIN()

最小值。

SUM()

求和。

AVG()

平均值。

注意

AVG、MAX、MIN聚集函数会自动忽略值为NULL的行

比如:

张三语文成绩90分,李四成绩60分,王五成绩为null,则avg(score)是(90+60)/2。

MAX和MIN函数也可以用于字符串类型的统计

如果是英文字母,按照A-Z的顺序排列,越往后越大。如果是汉字按照全拼拼音排列。

比如:

SELECT MIN(CONVERT(name USING gbk)), MAX(CONVERT(name USING gbk)) FROM heros

需要把字段统一转为gbk,使用convert函数再max、min。

对数据分组,并进行聚集统计

对数据进行分组,需要用GROUP BY字句。

比如按照英雄的主要定位和次要定位分组,并统计每组英雄的数量。

SELECT COUNT(*) num, role_main from heros GROUP BY role_main

HAVING过滤分组

当创建很多分组时,需要对分组进行过滤,过滤分组用HAVING。HAVING和WHERE都起到过滤的作用,不同的是:WHERE作用于数据行,HAVING作用于分组。

比如按照英雄的主要定位、次要定位分组,并且筛选分组中英雄数量大于5的组,最后按照分组中的英雄数量从高到低排序。

SELECT COUNT(*) num, role_main, role_assist from heros GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC

SELECT查询中的关键字顺序

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

思考

1、筛选最大生命值大于6000的英雄,按照主要定位分组,选择分组英雄数量大于5的分组,按照英雄数量由高到低排序,并显示每个分组的英雄数量、主要定位和平均最大生命值。

SELECT COUNT(*) num, role_main, AVG(hp_max) from heros GROUP BY role_main HAVING num > 5 ORDER BY num DESC

2、筛选最大生命值与最大法力值之和大于7000的英雄,按照攻击范围进行分组,显示分组的英雄数量,以及分组英雄的最大生命值与最大法力值之和的平均值、最大值、最小值,并按照分组的英雄数由高到低排序,其中聚集函数的结果保留小数点后两位。

SELECT
	COUNT(*) num,
	ROUND(AVG(hp_max + mp_max), 2) avg,
	ROUND(MAX(hp_max + mp_max), 2) max,
	ROUND(MIN(hp_max + mp_max), 2) min
FROM
	heros
WHERE
	(hp_max + mp_max) > 7000
GROUP BY
	attack_range
ORDER BY
	num DESC

(本文完)

  • 我的微信
  • 微信扫一扫
  • weinxin
  • 微信公众号
  • 微信公众号扫一扫
  • weinxin
  • A+
所属分类:SQL

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: