Oracle学习第二天

oracle数据库的常见数据类型
oracle全部数据类型 有26种

char
定长字符串类型 长度是固定不变的 例如：no char(10) 如果存入的值不足十个字符，其它位也被占用
默认长度是1 最大长度是2000个字节 GBK编码 1000个汉子
占用的空间较多，但是查询效率较高

varchar2
边长字符串类型 长度可变 例如：定义的长度是100个字节，存入的值是80个字节，那么实际占用的就是80个字节
varchar在oracle中不推荐使用，强烈推荐你使用varchar2
必须要为其指定长度 1-4000个字节 多以就需要考虑编码问题
oracle使用GBK编码 一个中文占两个字节，英文、数字占一个字节
占用的空间较少，但是查询的效率较低

number
数字类型 number(all_len,point_len)
第一个参数：整个数字的位数
第二个参数：以小数点为参照，右边为正数，左边为负数，0就是没有小数位
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 precusion(有效位数为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
将数据存入数据库中，需要进行格式化存储的函数(方法)
默认格式：DD-MM-YY
select sysdate from dual;
查询结果：30-9月 -15
to_date('存入的值','存入的格式');
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');

create table test_date(name char(3),test_time date default sysdate);

to_char();
select to_char(test_time,'YYYY-MM-DD HH24: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)
);
查询某用户下某表的约束名称（在dba角色下查询）
select constraint_name from dba_constraints where owner='SCOOT' and table_name='EMP';

查询当前用户下的表约束
select constraint_name,constraint_type,search_condition,r_constraint_name
from user_constraints where table_name=upper('emp');

删除外键约束
alter table tablename
drop constraint FK_tablename_col1;
--------------------------------------------------------------------------------------
表结构操作：
修改表名：
rename tt to ttt;
添加表字段：
alter table ttt add gender char default 'M';
表字段重命名：
alter table ttt rename column name to username;
修改表字段：
alter table ttt modify (name varchar2(50) default 'Owen');

oracle函数
运算符：
算数运算符 + - * /
select 1+1 a from dual;
连接运算符 ||
select 'jack'||'love'||'rose' from dual;
比较运算符 > = >= < <= != like between is null in
select * from tt where username like 'j%';
任何值与null进行运算，都是null

ascii('a'); 根据字符返回对应的编码值
chr(编码值); 返回对应的字符
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; 减掉两边的所有需要去掉的字符
select trim(leading 's' from 'strings') from dual; 减去前面的所有需要去掉的字符
select trim(trailing 's' from 'strings') from dual; 减去后面的所有需要去掉的字符

abs(字段名) 返回数字的绝对值
select abs(123) abs1,abs(-1234) abs2 from dual;

floor(字段名) 将小数位去掉，返回整数（不四舍五入）
select floor(1234.567) from dual;

ceil(字段名) 将小数转成整数（有小数就+1）

mod(被除数，除数)
select mod(10,3) m1,mod(3,3) m2,mod(1,3) m3 from dual;

power(原数字，幂)
select power(2,3) from dual;

round(字段名) 对小数四舍五入
select round(55.5) from dual;

trunc(字段名) 直接截取整数部分（不四舍五入）
select trunc(55.5) from dual;

to_number()

show autocommit; 显示自动提交的状态
set autocommit on/off; 打开/关闭自动提交
commit; 手动提交

rollback; 回滚（autocommit off），撤销之前一部的操作（未提交状态）

----------------------------------------------------------------------------------------
日期常用函数
last_day(date) 返回日期date所在月的最后一天
select last_day(sysdate) from dual;

add_months(date,i) 返回日期date加上i个月后的日期值
i：取正值整数 小数：截取整数后再计算 负数：减去i个月的日期值
select add_months(sysdate,1) from dual;

months_between(date,date_) 返回两个日期间隔多少月
结果可能是负数或小数（可以用ceil函数直接进位）

next_day(date,周几) 返回date日期数据的下一个周几的日期，例如4即下一个周四的日期
Sunday、Monday、Tuesday、Wednesday、Thursday、Friday、Saturday
select next_day(sysdate,1) from dual;

least(); 取最小值
least(1,100,2,31);
greatest(); 取最大值
greatest(1,100,2,31);

extract(year/month/day from date) 从参数date中提取参数指定的数据类型
select extract(year from sysdate) from dual;

null和任何值进行运算返回的都是null
select * tablename where column1=null; //对于null不能这样进行比较
select * tablename where column1 is null;
select * tablename where column1 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;