甲骨文数据库基本知识

date数据库的常见数据类型
oracle全部数据类型有26种
char
定长字符串  长度是固定不变的  no char(10) 如果存入的值不足十个字符，其他位也被占用
12345678##（空白的地方占用）
默认长度为1 即一个字符 最大长度：2000个字符
占用的空间较多，查询效率较高


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




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   NUMBR(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
 将数据库存入数据库中，需要进行格式化存储的函数（方法）
默认格式：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(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 HH24:MI:SS') to_char_time from test_date where name='123';


timestamp 


clob 用来存储单字节字符型数据  适用于存储超长文本 如小说，博客


blob 用来存储二进制数据  存储视频，图像，音频
------------------------------------------------------------------------------------------------------
约束 
  创建表时使用约束
  是强加在表上的规则和条件。确保数据库满足业务规则。可以保证数据的完整性
  当对表进行DML或DDL操作时，如果此操作会造成表中的数据违反约束条件或规则是，数据库系统就会拒绝执行这个操作
  
定义约束时，如果么有给定一个明确的约束名称，系统将会自动为该约束生成一个名字，oracle强烈建议创建约束时要给约束起名称
  
约束的分类：
 非空约束(NOT NULL)
       约束字段（列）不能为null值
 唯一约束（unique KEY）
       在表中每一行中定义的这列或这些列的值都不能相同 即唯一性
 主键约束（PRIMARY KEY)
       唯一的标识表中的每一条数据（每一行记录） 不能为null 不能重复
 外键约束（FOREIGN KEY）
       用来维护从表和主表之间引用的完整性  （不推荐使用外键）
 条件/检查约束（check）
 表中每一行该列都要满足该约束条件
 
 命名规则：
 约束名称简称_表名_列名
 非空约束  NN_tablename_coll
 唯一约束  UK_tablename_coll
 主键约束  PK_tablename_coll
 外键约束  FK_tablename_coll
 条件/检查约束 CK_tablename_coll
 
 create table c_test(
     id number(6),
name varchar2(30),
gender char,
age number(3),
brithday date,
constraint PK_C_TEST_ID primary key(id)
constraint UK_C_TEST_NAME unique (name)
 );
-----------------------------------------------------------------
表结构操作
 修改表名 rename tt to ttt;
 添加表字段  alter table ttt add gender char default 'M'
 修改表字段  alter table ttt modify(name varchar2(50) default '姓名')
表字段重命名 alter table ttt rename column name to username;
 修改表字段类型 alter table tt modtify(username varchar2(100));


 oracle函数
 运算符：
 算术运算符  + - * /
 连接运算符  ||
     select 'jack'||'love'||'rose' from dual;
select username ||'---'||age from ttt;      jack---23   rose---24
 比较运算符  > = >= < <= !=  like between is null in
     select * from ttt  where username like '%k';  //最后一个字符是k
select * from ttt  where username like 'j%';  //第一个字符是j
select * from ttt  where username like '%a%';
     select username from ttt where age between 23 and 24;  
select * from ttt where username is null;  //任何值与null进行运算 都是null
select * from tt where age in(23,33,43);
 
 
 函数 
 ascii('a')   返回对应的编码值
 chr(编码值) 返回对应的字符  select chr(97) from dual; //  'a' 97
 
 concat 连接两个字符串
      select concat(username||'--',age+100) from tt;    jack--123
 initcap(字段名)   返回字符串并将字符串的第一个字母变大写
      select initcap(username) from tt;  Jack Rose
 
 length(字段名) 返回字段值得长度
      select length(username) username_length from tt;
 
 lower(字段名)  返回字符串的全部小写
 
 upper(字段名)  返回字段值全部大写 
      select lower(initcap(username)) low,upper(username) from tt;
 
 ltrim(字段名) 清除字段名左边的空字符串
 
 rtrim(字段名) 清除字段名右边的空字符串
     select ltrim(rtrim(username)) usename from tt;
 
 substr(原字符串，从第几个字符开始，截取多少个)；
     select substr('abcdefghijklmn',1,3) from dual;  abc
 
 replace(原字符串，被替换的字符串，要替换成的字符串)；
     select replace('He love rose！','He','I') from dual;
 
 trim('要去掉的#字符#'from '目标字符串');只能一个字符 减掉两边的所有字符
   trim('s' from 'strings')
   select trim('s' from 'strings') from dual;    tring 
 
  select trim(leading 's' from 'strings') from dual; 减掉前面的s字符   trings
  select trim(trailing 's' from 'strings') from dual;  减掉后面的s字符string
 
  abs(数字字段名) 返回数字的绝对值
select abs (123) abs1,abs(-1234),abs2 from dual;
 
 floor(字段名)  去掉小数不四舍五入成为整数
 select floor(1234.524) from daul ; 1234
 
 ceil(字段名) 将小数转成整数（有小数就+1）
 
 mod(被除数，除数)
 select mod(10,3) ml,mod(3,3) m2,mod(1,3)m3 from dual；
 
 power(原数字,幂)
 select power(2,3) from dual;  8
 
 round(字段名) 对小数四舍五入成整数
 select round(55.5) from dual; 56
 select round(55.4) from dual; 55
 
 trunc(字段名) 直接截取整数，不四舍五入
 select trunc(55.5) from dual;
 
 avg(字段名) 平均值
 select avg(age) from tt;
 
 count(字段名或者*);获取记录数 如果使用字段名，该字段不能有null值，否则忽略
 select count(*) from tt;
 
 sum(字段名) 求和
 select sum(age) from ttt;
 
 min(字段名) 
 
 max(字段名)
 
 to_number('1234') 字符串传成数字   
 select to_number('1234')from dual; 1234   
 
 -------------------------------------------------------------
 查看当前oracle 自动提交状态
 show autocommit;
 修改自动提交状态（这是一个会话级的环境变量）
 set autocommit on;
 set autocommit off;
 手动提交
 commit;
 回滚(autocommit off)
 rollback;   
 
 --------------------------------------------------------------
 日期常用函数
 last_day(date) 返回日期date所在月的最后一天
 select last_day(sysdate) from dual;
 
 add_months(date,i) 返回日期date加上i个月后的日期值
    i取正整数 小数：截取整数后再运算  负数：减少i个月的日期值
 months_between(date,date1)返回两个日期间隔多少个月;
结果可能是负数和小数(可以用ceil函数直接进位);
next_day(date,周几)返回date日期数据的下一个周几的日期
Sunday Monday Tuesday Wednesday Thursday Friday Saturday
January February March April May June July August September October November December


 
 
 least(1,3,55,100,2,24)
 greatest(1,66,677)
 也称作比较函数，参数类型必须一致
 第二个参数类型会被转向一定参数类型 不能转换就报错
 
 extract(year/month/day from date)从参数datetime中提取参数date指定的数据类型，列如提取年，月，日
 列如: select extract(year from sysdate) from dual;


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