数据库基础（二）

一、CHAR 和 VARCHAR 的存储编码

默认单位是字节，可指定为字符

- CHAR（10），等价于 CHAR（10 byte）

每个英文字符占用一个字节，每个中文按编码不同，占用2-4个字节

- GBK 占用2个字节
- utf-8 占用3个字节

二、CHAR 和 VARCHAR 的最大长度

CHAR 默认长度为1，即 name CHAR 相当于 name CHAR（1），但 VARCHAR 长度不可省略

三、LONG 和 CLOB 类型

LONG—VARCHAR的加长版，存储变长字符串，最多到达2GB的字符串数据

LONG有诸多存储限制:每个表只能有一个LONG类型列；不能作为主键；不能建立索引；不能出现在查询条件中…
CLOB：存储定长或变长字符串，最多达4GB的字符串数据

- ORACLE 建议开发中使用 CLOB 替代 LONG 类型

四、字符串函数

CONCAT 拼接字符串

SELECT CONCAT(CONCAT(name,':'), age) FROM student

使用『||』也可以连接字符串，通常使用它来代替 CONCAT 函数

SELECT name||':'|| age FROM student

LENGTH 用于返回字符串长度（字符量)

SELECT name,LENGTH(name) FROM student

UPPER，LOWER 将字符转换为大写或小写

SELECT UPPER(name) FROM studentSELECT LOWER(name) FROM student
SELECT LOWER(name) FROM student

INITCAP 将首字母转换为大写（其中可以使用空格分隔多个单词，那么每个单词首字母都会大写）,其他字母转换为小写

SELECT INITCAP(name) FROM student

DUAL 伪表
为了满足 SELECT 语法要求使用，当我们查询的数据不来自任何一张表时，可以使用伪表代替 FROM 字句中的内容伪表仅会查询出一条记录

SELECT UPPER(‘hello') FROM DUAL

TRIM 去除当前字符串中两边重复的给定字符,截取集只能有一个字符

SELECT TRIM('e' FROM 'eeeehelloeeeee') FROM DUAL     //a

LTRIM，RTRIM 单独去除字符串中左边重复或右边的给定字符，截取集可以有多个字符，会把截取集中的每个字符都去除

SELECT LTRIM('esweeehelloeeewsee','esw') FROM DUAL;     //helloeeewsee
SELECT RTRIM('esweeehelloeeewsee','esw') FROM DUAL;     //esweeehello

LPAD，RPAD 补位函数

SELECT LPAD(name,3,'$') FROM student

如果比原来小则会进行截取
通过 LPAD ， RPAD 可以实现左右对齐效果

SUBSTR（char，offset，len） 从给定字符串的给定位置开始，连续向后截取len长度的字符串(字符位置从1开始)
offset可正可负，若是负数，则从倒数字符开始截取
len必须是>=0的数字，若截取的字符数量超过实际可以截取的字符数量时，则截取到字符串末尾

SELECT SUBSTR（'HelloWorld',5,2）     //oW
SELECT SUBSTR（'HelloWorld',-3,2）     //rl

INSTR(char1,char2[,n,m]) 查看char2在char1中的位置
n:从char1的第几个字符开始查找（不写则为1）
m:查找第几次出现（不写则为1）
没有返回0

SELECT INSTR （'HelloWorld','l',4,2）//9

五、数值函数

ROUND（n[,m]）：用于四舍五入
参数中的n可以是任何数字，指要被处理的数字

• m 必须是整数
• m 取正数则四舍五入到小数点后第m位
• m 取0值则四舍五入到整数位
• m 取负数，则四舍五入到小数点前m位
• m 缺省，默认值是0

SELECT ROUND（45.678,2） FROM dual     //45.68
SELECT ROUND（45.678,-1） FROM dual     //50
SELECT ROUND（95.678,-2） FROM dual     //100

TRUNC 与 ROUND 的参数相同，但区别在于，仅保留指定位数，不做四舍五入操作

SELECT TRUNC（45.678,2） FROM dual     //45.67
SELECT TRUNC（45.678,-1） FROM dual     //40
SELECT TRUNC（95.678,-2） FROM dual     //0

MOD（m，n）模运算，取余

• m 被除数
• n 除数

若n为0，则直接返回m

SELECT name,MOD(id,1000) from student

CEIL 和 FLOOR 向上取整和向下取整

SELECT CEIL(4.5) FROM dual     //5
SELECT FLOOR(4.5) FROM dual     //4

六、日期转换函数

SYSDATE 返回一个DATE类型的当前系统时间，精确到秒，默认显示格式是DD-MON-RR

INSERT INTO student hiredate VALUES SYSDATE

SYSTIMESTAMP 返回一个TIMESTAMP类型的当前系统时间，精确到毫秒

TO_DATE 字符串转时间
日期类型的数据室可以进行计算的，两个日期类型的值相减，得到的差是相差的天数。给一个日期的值加上一个数字，相当于增加了x天。

SELECT TRUNC((SYSDATE - TO_DATE('1994-11-08','YYYY-MM-DD'))) FROM DUAL

TO_CHAR 可以将给定的DATE按照特定的日期格式转化为字符串

SELECT TO_CHAR(TO_DATE('98-05-30','RR_MM_DD'),'YYYY-MM-DD') FROM DUAL     //1998-05-30
SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日"') FROM DUAL    //2015年12月17日

七、日期常用函数

LAST_DAY 返回日期date所在月的最后一天

SELECT LAST_DAY('20-2月-09') FROM DUAL

ADD_MONTHS（date，i） 返回日期date加上i个月后的日期值

• 参数i可以是任何数字，大部分的时候去正值整数
• 如果i是小数，将会被截取整数后再参与运算
• 如果i是负数，则获得的是减去i个月后的日期值

SELECT name，ADD_MONTHS（hiredate，20*12） FROM student
`

MONTHS_BETWEEN（date1，date2） 计算date1和date2两个日期之间间隔了多少个月

SELECT name,MONTHS_BETWEEN（SYSDATE,hiredate） FROM student

NEXT_DAY（date，char） 返回date日期数据的下一个周几，周几是由参数char来决定的
char可以用数字表示，1-7表示周日到周六

SELECT NEXT_DAY(SYSDATE,4) as  'NEXT_WEDN' FROM DUAL     //下周三的日期

*LEAST，GREATEST 返回结果是参数中最大或最小的值在比较之前，参数列表中第二个以后的参数类型会被转成第

SELECT LEAST（SYSDATE,TO_DATE('2008-10-10','YYYY-MM-DD')） FROM DUAL

EXTRACT（date FROM datetime） 从参数datetime中提取参数date指定的数据，比如提取年、月、日

SELECT name,hiredate FROM student WHERE EXTRACT（YEAR FROM hiredate）= 1981

八、空值操作

判断一个字段的值是否为NULL时，不能使用『=』判断，而是使用 IS NULL 或 IS NOT NULL

UPDATE student SET gender='M' WHERE gender IS NULL

NULL与字符串链接，等于什么都没干
NULL与数字运算，结果还是NULL

NVL（expr1，expr2）将NULL转变为非NULL值,若expr1为NULL，则函数返回expr2，否则就返回expr1的值

SELECT name,NVL(comm,0) FROM student

NVL2（expr1,expr2,expr3）和NVL类似，用来判断expr1是否为NULL，如果不为NULL，则返回expr2，如果为NULL，则返回expr3

SELECT name ,NVL2(comm,’有奖金’,'没有奖金') FROM student

NVL2能实现NVL的功能，但NVL不能实现NVL2的全部功能