Oracle-第二章-数据类型_oracle中number类型除100

二、数据类型

1数字

整型（整数型）int/integer
浮点型（小数）float
在oracle中常用NUMBER（包括以上两类）定义数据的数字类型
 
####
定义数据类型 number（10，2）指定义一个长度为10精度为2的数字类型
键入 1234567890 报错
键入 1234 得 1234.00
键入 .123456788890 得.12
--思考：如果把该类型转换成字符串，那么会不会存在看不见的空格？like关键字能使用吗？（目前不知道怎么转换-可以用to_char转换）
 
####
to_number把字符串变成数字
注意：
select *
from student s 
where from s.gender=1;--隐式类型转换
等同于
select *
from student s 
where from to_number(s.gender)=1;
--等号两边类型不一致的时，会将左边转换成右边类型，从而报错ORA-01722

1.1四则运算（加减乘除）

select * from dual;--dual表用于进行试验性操作,得一行一列的结果
select 1/3 from dual;--得3.333333333....

1.2取余（mod函数）

select mod(10,3) from dual;--得1

1.3截取（trunc函数，round函数-四舍五入-常用）

select trunc(1.2345) from dual;--得1
select trunc(1.2345,3)from dual;--得1.234
select trunc(125341.2345,-3)from dual;--得120000
 
select round(1.2345) from dual;--得1
select round(1.2345,3)from dual;--得1.235 
select round(125341.2345,-3)from dual;--得130000
--把小数换成分数一样可以运行
 
特别注意：
题|随机返回1-10的整数
select trunc(dbms_random.value(1,11)) from dual;
select round(dbms_random.value(1,10)) from dual;--但是此处1和10的几率小于其他数

1.4值（abs函数）

select abs(-1) from dual;得1

1.5乘方（power函数）

select power(3,4) from dual;--3的4次方

1.6平方根（sqrt函数）

select sqrt(4) from dual;--得2（注意和数学运算有所区别）
 
select power(4,1/2) from dual;--得2（也可）

1.7向上取整（ceil函数）

select ceil(1.2) from dual;--得2

1.8向下取整（floor函数）

select floor(1.2) from dual;--得1

1.9伪随机数（dbms_random.value函数-在oracle中很少用）

select dbms_random.value() from dual;
--得到值在[0,1)之间，没有括号也可以
select dbms_random.value(1,5) from dual;
--得到在[1,5）之间

1.10函数组合用法

例：随机得到1-10的整数
select trunc(dbms_random.value(1,11)) from dual;
select round(dbms_random.value(1,10))from dual;

1.11其他函数（不常用）

exp(n)返回e的n次幂
sin(n)返回n的正弦值（n为弧度）
cos(n)返回n的余弦值（n为弧度）
log(n1，n2)返回以n1为底n2的对数
sign(n)若n为负则返回-1，为正返回1，为返回

2字符串

char()固定长度字符串， varchar()可变长度字符串
当用通配符‘%’检索时
'6,7,8' char(10) 实际上8后面还存在空格 '%,8' 查询不出来
'6,7,8' varchar(10) 此处8后面就不存在空格 '%,8' 因此可以查询出来--你以为是小问题，其实是大问题，不能不求甚解
 
select '8'+5 from dual;--字符串类型的数字和数字类型的数字可以进行运算

2.1字符串连接（显示更有意义的信息）

连接字符串时，如果在字符串中加入数值，那么可以直接指定数字值；
如果在字符串中加入字符值或者日起值，那么必须用单引号引住
（1）用||连接字符串
select ename|| ' ''s job '||job from emp;--注意 ''两个单引号等同于一个单引号字符
select 1||2||3||4||5 from dual;--也可以连接数字变成字符串
 
（2） 用函数CONCAT连接字符（只了解即可）
select concat(concat(ename,'''s salary is'),sal)from emp;
 
题|
select * from test1 where ','||cs||',' like'%,8,%';
--可查找列中仅数字8但不要28，88等，如找到‘1，3，8’，‘4，566，7，8’，‘8，95’，‘7，8，88，9’

2.2字符串长度

select s.name,length(s.name) from student s;

2.3截取字符串

select 'zhangzhang',substr('zhangzhang',6,2) from dual;--从第六位开始截取2个字符得'zh'
select 'zhangzhang',substr('zhangzhang',6) from dual;--从第六位开始截取剩余的全部字符得'zhang'
select 'zhangzhang',substr('zhangzhang',-3) from dual;--从倒数第三位开始截取剩余的全部字符得'ang'

2.4替换字符串

select 
replace('zhangjianjian','jianjian','hhh')
from dual;
--得zhanghhh
--若第三个参数为空则替换为空

2.5查询字符串的位置

语法：instr(s1,s2[,i][,j])
     s2在s1中从第i个字符第j次出现的位置，i,j默认是1
select 
instr('zhangjianjian','an',5,2)
from dual;
--返回12
select 
instr('zhangjianjian','an',1,2)
from dual;    
--返回8
 
####
select 
instr('zhangjianjian','an')
from dual;--查询个‘an’的位置
 
select 
instr('zhangjianjian','an',4)
from dual;--查询从第四位开始往后的个‘an’的位置，返回8
 
select 
instr('zhangjianjian','an',3)
from dual;--注意：返回3
 
select 
instr('zhangjianjian','an',-1)
from dual;
--得12
--当第三个参数为负数时，搜索将从右向左进行，但是返回位置还是从左向右计算的

2.6其他函数

（1）大小写转换
select upper('zhangJIANJINA')
from dual;
select lower('zhangJIANJINA')
from dual;
 
 
（2）去除空格
select trim('    zhangjianjian    ')
from dual;
--去左边
select ltrim('    zhangjianjian    ')
from dual;
--去两边
select rtrim('    zhangjianjian    ')
from dual;
--去两边
 
 
（3）填充
select lpad('jian',10,'a')
from dual;
--左填充，使字符串长度达到10，不足的用第三个参数补齐，没有第三个参数则用空格补齐
--rpad右填充
--如果字符大于二个参数要求则从左到右截取第二个参数要求的长度
 
 
 
####
（4）返回字符的ASCII值、返回ASCII值的字符串
select ascii('\n'),ascii('A'),ascii('a')
from dual;
--得
ASCII('\N') ASCII('A')  ASCII('A')
92          65          97
--换行键是 10
--92是 \
 
select chr(92),chr(65),chr(97)
from dual;
--得
CHR(92) CHR(65) CHR(97)
\       A        a
 
 
（5）字符串每个单词首字母大写（INITCAP）（用空格、标点符号、控制字符来区分单词）
select initcap('zhang jian jian')
from dual;
 
（6）条件取值 decode
语法： decode（条件，值1，翻译值1.值2，翻译值2，.....值n，翻译值n，缺省值）

3日期

(date,timestamp)

3.1日期转字符串

select to_char(sysdate,'yyyy/mm/dd hh[24]:mi:ss q day')
from dual;
--分别对应年、月、日、时（如果是hh24则是24小时制，否则默认12小时制）、分、秒、季度、星期
--顺序可以调整
--sysdate是关键字，表示当前服务器的时间（非网络时间）
--中间的符号和空格可以用其他符号替换，但是目前不清楚怎么用汉字或者字母替换
--此外注意如果to_char('mm')只能知道是哪个月，不能知道是哪年哪月

3.2字符串转日期

select to_date('0101','mmdd') 
from dual;
--不带年份则默认为当年
--可依照日期转字符串反向思考
####
注意：
--如果条件添加为时间等于时间，应将时间转换成字符串
select *　
from　student s
where to_char(s.birthday,'yyyymmdd')='20220101';
--查询2022年1月1日出生的人
 
--如果把字符串变成时间会导致不
select *　
from　student s
where to_date('20220101','yyyymmdd')=s.birthday;
--因为日期实际上有很多位，包括时分秒等，所以无法相等

3.3日期的加减（日期格式之间不能相加）

select sysdate+.5,sysdate-1 
from dual;
--加半天，减一天
--两个时间只能想减，不能相加，得天数

3.4日期截取

（1）trunc函数（得到个时刻）
select trunc(sysdate)
from dual;
--得到当天凌晨的时间
--截取后仍然是date格式
select turnc(sysdate,'mm')
from dual;
--yyyy截取到当年个时刻
--mm截取到当月第个一时刻
--dd截取到当日个时刻
--day截取到当周个时刻
--（Oracle按照美国的习惯，每周的天为周日，如果得到周一则需要后+1，trunc(sysdate,'day')+1）
--其他类推

3.5当月后一天

select last_day(sysdate) 
from dual;
--返回当月的后一天的同一时刻，如1.31，2.28等（注意不是准点时刻）
####
--如果要上个月的后一天，建议先查找上个月，然后再查找后一天
select last_day(add_months(sysdate,-1))
from dual;

3.6月份相加减

select add_months(sysdate,1),add_months(sysdate,-2)
from dual;

3.7其他函数

（1）两个时间相差的月数
select months_between(add_months(sysdate,5),sysdate)
from dual;
--前一个减去后一个时间相差的月份
（2）从某时刻开始算的下一个周几
select next_day(sysdate,1)
from dual;
--1代表星期天
--注意：如果今天是周三，那下一个周四是明天
 
####
（3）时区
new_time(d1,t1,t2)
--d1是日期类型
--返回t1时区的d1时间在t2时区的时间