oracle02--数据类型、约束与函数
oracle数据库的常见数据类型
oracle全部数据类型有26种
char
定长字符串类型 长度是固定不变的 no char(10) 如果存入的值不足10个字符，其他位也被占用
默认长度是1 即1个字符 最大长度2000个字节
占用的空间较多，但是查询效率较高
定长字符串 最大长度2000个字节 可以不指定长度 默认长度是1
char(10) 等价于 char(10 byte)
也可以这样定义 char(10 char)
varchar2
变长字符串类型 长度可变 例如存入的值定义的长度是100个字节，存入的值是80个字节，实际占用的就是80个字节
varchar在oracle中不推荐使用，强烈推荐使用varchar2
必要要为其指定长度 1-4000个字节 所以就需要考虑编码问题
oracle下使用gbk 一个中文占两个字节 英文数字占一个字节
占用的空间较少，但是查询效率较低
varchar2(10) 等价于 varchar2(10 byte)
也可以这样定义 varchar2(10 char)
number
数字类型 number(all_len,point_len)
第一个参数是整个数字的位数
第二个参数是以小数点为参照 右边为正数 左边为负数 0就是没有小数位
Actural Data Specified Stored AS
---------------------------------------------
123.89 number 123.89
123.89 number(3) 124
123.89 number(6,2) 123.89
123.89 number(6,1) 123.9
123.89 number(4,2) exceeds precision (有效位数为5，5>4) 超出范围
123.89 number(6,-2) 100
.01234 number(4,5) .01234 (有效位为4)
.00012 number(4,5) .00012
.000127 number(4,5) .00013
.0000012 number(2,7) .0000012
.00000123 number(2,7) .0000012
date
要将数据存入数据库中，需要进行格式化存储的函数(方法)
to_date('存入的值','存入的格式');
默认格式：DD-MM-YY
select sysdate from dual;
30-9月 -15
2015-12-12 12：12：12 YYYY-MM-DD HH24:MI:SS
to_date('2015-12-12 12：12：12','YYYY-MM-DD HH24:MI:SS');
drop table test_date;
create table test_date(name char(3), test_time date default sysdate);
insert into test_date(name, test_time) values('123',to_date('2015-12-12 12：12：12','YYYY-MM-DD HH24:MI:SS'));
to_char();
select to_char(test_time,'YYYY-MM-DD HH12:MI:SS') tochar_time from test_date where name='123';
timestamp
clob 用来存储单字节字符型数据 适用于存储超长文本 例如：小说，博客
blob 用来存储二进制数据 存视频\图像\音频
-------------------------------------------------------------------------------------------------------------
约束
创建表时使用约束
是强加在表上的规则和条件，确保数据库满足业务规则。可以保证数据的完整性
当对表进行DML或DDL操作时，如果此操作会造成表中的数据违反约束条件或规则时
系统就会拒绝执行这个操作。
定义约束时如果 没有给定一个明确的约束名称，系统将会自动为该约束生成一个名字。
oracle强烈建议创建约束时要给约束起个名称
约束的分类
非空约束(NOT NULL)
约束字段（列） 不能为NULL值
唯一约束（UNIQUE）
在表中每一行中定义的这列或这些的值都不能相同，即唯一性
主键约束（PRIMARY KEY）
唯一的标识表中的每一条数据（每一行记录） 不能为NULL 不能重复
外键约束（FOREIGN KEY）
用来维护主表和从表之间引用的完整性 (不推荐)
条件/检查约束(CHECK)
表中每一行该列都要满足该约束条件
命名规则：
约束名称简称_表名_列名
非空约束 NN_tablename_col1
唯一约束 UK_tablename_col1
主键约束 PK_tablename_col1
外键约束 FK_tablename_col1
条件/检查约束 CK_tablename_col1
create table c_test(
id number(6),
name varchar2(30),
gender char,
age number(3),
birthday date,
constraint PK_C_TEST_ID primary key(id)
);
create table c_mytest(
id number(6),
name varchar2(30),
gender char,
age number(3),
birthday date,
constraint PK3_C_TEST_ID primary key(id)
);
---------------------------------------------------------
表结构操作
修改表名
rename tt to ttt;
添加表字段
alter table ttt add gender char default 'M';
表字段重命名
alter table tt rename column name to username;
修改表字段
alter table ttt modify (name varchar2(50) default '哈哈');
oracle函数
运算符：
算术运算符 + - * /
连接运算符 ||
比较运算符 > = >= < <= != like between(and) is null in
任何值与null进行运算 都是null
ASCII('a') 根据字符返回编码值
chr(ascii编码值) 返回对应的字符
concat(字段名1,字段名2) 连接两个字符串
initcap(字段名) 返回字符串并将字符串的第一个字母变大写
length(字段名) 返回字段值的长度
lower(字段名) 返回字符串的全部小写
upper(字段名) 返回字段值全部大写
select lower(initcap(username)) low,upper(username) up from tt;
ltrim(字段名) 清除字段值左边的空字符串
rtrim(字段名) 清除字段值友边的空字符串
substr(原字符串,从第几个字符开始,截取多少个);
select substr('abcdefghijklmn',1,3) from dual;
replace(原字符串,被替换字符串,要替换的字符串);
select replace('He love rose','He','I') from dual;
trim('要去掉的#字符#' from '目标字符串');
select trim('s' from 'strings') from dual; 剪掉两边所有的s字符
select trim(leading 's' from 'strings') from dual; 剪掉前面所有的s字符
select trim(trailing 's' from 'strings') from dual; 剪掉后面所有的s字符
abs(数字字段名) 返回数字的绝对值
select abs(123) abs1, abs(-123) abs2 from dual;
round(字段名) 将小数转成整数(四舍五入)
select round(55.5) from dual;
trunc(字段名) 直接截取整数部分(不四舍五入)
select trunc(55.5) from dual;
floor(字段名) 将小数转成整数(不四舍五入)
select floor(123.123) from dual;
ceil(字段名) 将小数转成整数(有小数就加一)
select ceil(123.123) from dual;
mod(被除数,除数)
select mod(10,3) m1, mod(3,3) m2， mod(1,3) m3 from dual;
power(原数字,幂)
select power(2,3) from dual;
avg(字段名) 平均值
select avg(age) from tt;
count(字段名或*) 获取记录数 如果使用字段名该字段一定不能有null值，否则忽略
select count(*) from tt;
sum(字段名) 求和
select sum(age) from tt;
min(字段名) 最小值
select min(age) from tt;
max(字段名) 最大值
select max(age) from tt;
to_number('1234')
select to_number('1234') from dual;
last_day(date)返回日期date所在月的最后一天
add_months(date,i)返回日期date加上i个月后的日期值
i取正值整数 小数：截取整数后在运算 负数：减去i个月的日期值
months_between(date , date)返回两个日期间隔多少个月
结果可能是负数和小数(可以用ceil(取天花板)函数直接进位)
next_day(date, 周几)返回date日期数据的下一个周几的日期,例如4即下一个周四的日期
Sunday Monday Tuesday Wednesday Thursday Friday Saturday
January February March April May June July August September October November December
least(1,23,300,3,44)
greatest(1,23,300,3,44)
也被称作比较函数，参数类型必须一致
第二个参数类型会被转向第一参数类型 不能转换就报错
extract(year/month/day from date)从参数date中提取参数date指定的数据类型,例如提取年,月,日
null和任何值进行运算,返回的都是null;
select * from tablename where column = null; //对于null不能这样比较
select * from tablename where column is null;
select * from tablename where column is not null;
空值函数
nvl(col_null, col_not_null)将null值转变成非null处理 请保持两个参数的类型一致
例如：select sal,comm,sal+nvl(comm, 0) as 'salary' from emp;
nvl2(col_null, col_is_null,col_is_not_null)将null值转变成实际值 作用有点像三目表达式
如果是null,转成col_is_null,如果不是null,转换成col_is_not_null
例如：select sal,comm,sal+nvl2(comm, 0, 1000) as 'salary' from emp;
浙公网安备 33010602011771号