读SQL学习指南（第3版）笔记11_字符串函数和数值函数

2023-09-05 06:46 由躺柒发表于 #数据库

1. 尽管SQL标准指定了部分函数，但数据库厂商并没有遵循这些函数规范

2. 字符串

2.1. char

2.1.1. 固定长度、不足部分用空格填充的字符串

2.1.2. MySQL允许的char类型的最大长度为255个字符

2.1.3. Oracle Database允许的最大长度为2,000个字符

2.1.4. SQL Server允许的最大长度为8,000个字符

2.2. varchar

2.2.1. 变长字符串

2.2.2. MySQL允许的varchar类型的最大长度为65,536个字符

2.2.3. Oracle Database（通过varchar2类型）允许的最大长度为4,000个字符

2.2.4. SQL Server允许的最大长度为8,000个字符

2.3. text

2.3.1. MySQL和SQL Server

2.4. clob

2.4.1. Oracle Database

2.5. 保存非常大的可变长字符串

2.5.1. 文档

2.5.2. MySQL有多种text类型（tinytext、text、mediumtext和longtext），可用于最大4GB的文档

2.5.3. SQL Server为最大2GB的文档提供了单一的text类型

2.5.3.1. SQL Server 2005还包括varchar(max)数据类型，并建议使用其代替text类型，text类型将在未来的某个版本中被删除

2.5.4. Oracle Database包括clob数据类型，可保存最大为128TB的巨大文档

2.6. 可以配置MySQL和SQL Server以实现悄无声息地截断字符串，而不是抛出异常

2.7. 从MySQL 6.0版开始，默认行为是“strict”模式，意味着在发生问题时抛出异常，而在早先版本的服务器中，字符串会被截断，并发出警告，如果希望数据库引擎采取这种方式，可以选择ANSI模式

2.8. 避免字符串被截断（或是在Oracle Database和MySQL的strict模式下抛出异常）的最好方法是将列长度的上限设置得足够大，以容纳可能存储在其中的最长的字符串（服务器只会分配足以存储字符串的空间，所以为varchar列设置长度更大的上限并不会浪费存储资源

2.9. 包含单引号

2.9.1. 需要在字符串中添加转义字符，以便服务器将字符串中的撇号视为普通字符

2.9.2. 直接在单引号前再添加一个单引号进行转义

2.9.3. mysql

 -＞ UPDATE string_tbl
    -＞ SET text_fld = 'This string didn''t work, but it does now';

2.9.4. Oracle Database和MySQL用户也可以选择使用反斜杠字符来转义单引号

2.9.5. sql

UPDATE string_tbl SET text_fld =

  'This string didn\'t work, but it does now'

2.9.6. 如果要检索字符串，以用于屏幕显示或报表字段，则无须对内嵌引号作任何特殊处理

2.9.7. 如果要将检索出的字符串添加到其他程序要读取的文件中，就需要将转义字符加入字符串

2.9.7.1. 如果使用的是MySQL，可以通过内建函数quote()来实现，该函数会将整个字符串放入引号内并对其中任意的引号/撇号进行转义

2.9.7.2. mysql

-＞ SELECT quote(text_fld)
    -＞ FROM string_tbl;

2.9.7.3. 在检索数据用于数据导出时，可以对所有非系统生成的字符列（比如customer_notes列）使用quote()函数

2.10. 包含特殊字符

2.10.1. SQL Server和MySQL服务器包含内建函数char()

2.10.2. Oracle Database用户可以使用chr()函数

2.10.3. 用于从ASCII字符集的255个字符中任意构建字符串

2.10.4. 需要熟悉字符集的布局，从中定位特定的字符

2.10.5. 可以使用concat()函数来拼接若干字符串，可以自行输入这些字符串，也可以通过char()函数生成

2.10.5.1. Oracle Database用户可以使用拼接运算符（||）代替concat()函数

2.10.5.2. SQL Server并未提供concat()函数，需要使用拼接运算符（+）代替

2.10.6. 可以使用ascii()函数，该函数接受字符串最左侧的字符，并返回其编码数值

2.10.7. 使用char()、ascii()和concat()函数（或者拼接运算符），应该能够处理任何罗马字符，即使所用的键盘不包括重音字符或其他特殊字符

2.10.8. 返回数值的字符串函数

2.10.8.1. length()

2.10.8.1.1. 该函数返回字符串所包含的字符数

2.10.8.1.2. SQL Server用户需要使用len()函数

2.10.8.2. position()函数

2.10.8.2.1. 字符串中查找子串的位置

2.10.8.2.2. 在使用数据库时，一定要记住，字符串的第一个字符的位置为1

2.10.8.2.3. 如果没有找到指定的子串，position()函数返回0

2.10.8.2.4. SQL:2003标准的一部分

2.10.8.3. locate()函数

2.10.8.3.1. MySQL专有的

2.10.8.3.2. 从目标字符串的其他位置开始搜索

2.10.8.3.3. 可以接受可选的第3个参数，该参数用于指定搜索的起始位置

2.10.8.4. instr()函数

2.10.8.4.1. Oracle Database专有

2.10.8.4.2. Oracle Database未提供函数position()或locate()

2.10.8.4.3. 该函数在使用两个参数时，能够模拟position()函数

2.10.8.4.4. 使用三个参数时，能够模拟locate()函数

2.10.8.4.5. 返回值0 表示没有找到指定的子串，而不是表示该子串位于字符串的位置0

2.10.8.5. charindx()函数

2.10.8.5.1. SQL Server专有

2.10.8.5.2. SQL Server也没有提供position()或locate()函数

2.10.8.5.3. 该函数和Oracle的instr()函数类似，也可以接受两个或三个参数

2.10.8.6. strcmp()

2.10.8.6.1. MySQL专有

2.10.8.6.1.1. 不区分大小写

2.10.8.6.2. 无法在Oracle Database和SQL Server中模拟

2.10.8.6.3. 一个接受字符串作为参数并返回数值的字符串比较函数

2.10.8.6.3.1. −1（第一个字符串的排序位于第二个字符串之前）

2.10.8.6.3.2. 0（两个字符串相同）

2.10.8.6.3.3. 1（第一个字符串的排序位于第二个字符串之后）

2.10.8.6.4. MySQL还允许在select子句中使用运算符like和regexp来比较字符串

2.10.8.6.4.1. 比较的结果为1（true）或0（false）

2.10.9. 返回字符串的字符串函数

2.10.9.1. concat()函数

2.10.9.1.1. 通过数据片段构建字符串

2.10.9.1.2. Oracle中使用，可以使用拼接运算符（||）来代替

2.10.9.1.3. SQL Server的拼接运算符（+）代替||

2.10.9.2. 在字符串中间添加或替换部分字符

2.10.9.2.1. insert()函数

2.10.9.2.1.1. MySQL提供的函数接受4个参数：原始字符串、起始位置、要替换的字符数量和替换字符串

2.10.9.2.1.2. mysql＞ SELECT INSERT('goodbye world', 1, 7, 'hello') string;

2.10.9.2.2. replace()函数

2.10.9.2.2.1. Oracle Database

2.10.9.2.2.2. SELECT REPLACE('goodbye world', 'goodbye', 'hello') FROM dual;

2.10.9.2.3. SQL Server也有replace()函数，其功能与Oracle Database的相同

2.10.9.2.4. SQL Server还提供了与MySQL的insert()函数功能相似的stuff()函数

2.10.9.2.4.1. SELECT STUFF('hello world', 1, 5, 'goodbye cruel')

2.10.9.2.5. 从字符串中提取子串

2.10.9.2.5.1. 三种数据库服务器均提供了substring()函数

2.10.9.2.5.2. Oracle Database提供的是substr()函数

3. 算术函数

3.1. acos(X)

3.1.1. 计算x的反余弦

3.2. asin(x)

3.2.1. 计算x的反正弦

3.3. atan(x)

3.3.1. 计草x的反正切

3.4. cos(x)

3.4.1. 计军x的余弦

3.5. cot(x)

3.5.1. 计算x的余切

3.6. exp(x)

3.6.1. 计算e

3.7. ln(x)

3.7.1. 计算x的自然对数

3.8. sin(x)

3.8.1. 计军x的正弦

3.9. sqrt(x)

3.9.1. 计军x的平方根

3.10. tan(x)

3.10.1. 计算x的正切

3.11. mod()函数

3.11.1. 用于计算余数

3.11.2. 常用于整数参数

3.11.3. MySQL中也可以使用实数

3.11.3.1. mysql＞ SELECT MOD(22.75, 5);

3.11.4. SQL Server未提供mod()函数，可以使用运算符%来代替

3.12. pow()函数

3.12.1. 返回第一个参数的第二个参数次幂

3.12.2. Oracle Database或SQL Server，则为power()函数

3.12.3. pow(2,8)是MySQL中用于计算2^8的方式

3.12.4. 计算机内存通常是以2x字节为单位分配的，所以pow()函数可以非常方便地确定某段内存确切的字节数

3.12.4.1. mysql

-＞ SELECT POW(2,10) kilobyte, POW(2,20) megabyte,
    -＞ POW(2,30) gigabyte, POW(2,40) terabyte;

4. 数值精度

4.1. 在处理浮点数时，可能并不总是需要使用或显示数值的全精度

4.2. ceil()

4.2.1. SQL Server使用ceiling()替代ceil()

4.2.2. 向上取整

4.2.2.1. 哪怕小数部分非常小

4.3. floor()

4.3.1. 向下取整

4.3.1.1. 哪怕小数部分非常大

4.4. round()

4.4.1. 四舍五入

4.4.2. 使用负数作为第2个参数，表示小数点左侧的部分需要被截取或取整多少位

4.5. truncate()

4.5.1. Oracle Database使用trunc()替代truncate()

4.5.2. 将不需要的小数位直接丢弃

4.5.3. SQL Server并未提供truncate()函数，而round()函数的第3个可选参数如果存在且不为0，则表明要执行截取操作，而非取整

4.5.4. 使用负数作为第2个参数，表示小数点左侧的部分需要被截取或取整多少位