oracle 总结与练习

 

create table order_status
(
    id integer constant order_status_pk primary key ,
    status varchar(120) ,
    last_modified date default sysdate
);
------------------------ 约束 ------------
-- check 约束
alter table order_status add constraint order_status_ck check (status in ('BeiJing','ShangHai','TaiWan'));
alter table order_status add constraint order_status_ck check (id>0);
insert into order_status values(132,'ShangHai');
-- Not Null 约束
alter table order_status modify status constraint order_status_newref not null;
alter table order_status modify last_modified  not null;
-- 外键约束
create global temporary table test_orderstatus
(
    id integer constraint order_statustest_pk primary key,
    status_id number(15),
    status varchar(120),
    last_modifieddate date default sysdate
);
alter table test_orderstatus add constraint order_statusmodify_fk status_id reference order_status(id);
-- Unique 约束
alter table order_status add constraint order_status_up unque(status);
-- 删除列
alter table test_orderstatus drop column status_id ;
-- 删除约束
alter table order_status drop constraint order_status_up ;
-- 禁用约束（Disable References）
alter table order_status add constraint order_status_uq unique(status) disable;
-- 使用禁止约束
alter table order_status disable constraint order_status_uq;
-- 启用约束（ReStart References）
alter table order_status enable constraint order_status_uq;
-- 只对新插入的数据才执行当前约束
alter table order_status enable novalidate constraint order_status_uq;
-- 创建唯一约束为延迟约束    Deferrable是否为延迟约束 或 Not Deferred
alter table order_status add constraint order_status_uq unique(status) deferrable initially deferred;
-- Delete
drop table test_orderstatus
drop table order_status

----------- SEQUENCE 序列 
CREATE SEQUENCE emp_sequence
INCREMENT BY 1  -- 每次加几个
START WITH 1    -- 从1开始计数
NOMAXVALUE      -- 不设置最大值 ， 如：minvalue 0 ， maxvalue 999999 最大值为999999
NOCYCLE         -- 一直累加，不循环   ； cycle 循环
CACHE 10 ;

一旦定义了emp_sequence，你就可以用 CURRVAL，NEXTVAL
CURRVAL=返回 sequence的当前值
NEXTVAL=增加sequence的值，然后返回 sequence 值
比如：
emp_sequence.CURRVAL
emp_sequence.NEXTVAL

可以看如下例子：
INSERT INTO emp VALUES
(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);

SELECT empseq.currval FROM DUAL;

ALTER ANY SEQUENCE 权限才能改动sequence

DROP SEQUENCE order_seq;

--------- 函数

select ascii('A') A , ascii('a') a , ascii('0') zero, ascii(' ') space from dual ;

-- GREATEST 返回一组表达式中的最大值，即比较字符的编码大小
select greatest('AA','AB','AC') from dual;
select greatest('啊','安','天') from dual;

select chr(54740) zhao,chr(65) chr65 from dual;

select concat('010-','88888888') || '转23' MyTel from dual ;   -- concat 和 || 都是连接字符串

select length('范德萨') as len1 ,length(to_char(666)) as len2 from dual;

select abs(100),abs(-100),acos(-1), asin(0.5), atan(1) from dual ;
select cos(-3.1415927) from dual;  --返回一个给定数字的余弦值
select sin(1.57079) from dual;     --返回一个给定数字的正弦值
select cosh(20) from dual;    --返回一个数字反余弦
select sinh(20) from dual;    --返回双曲正弦值
select tan(20), tan(10) from dual;    --返回数字的正切值
select tanh(20), tanh(20) from dual;  --返回双曲正切值

select exp(2),exp(1) from dual;  --返回一个数字e的m次方根

-- LOG(n1,n2) 返回一个以n1为底n2的对数
select log(2,1), log(2,4) from dual; 

-- MOD(n1,n2) 返回一个以n1为底n2的余数
select mod(10,3), mod(3,3), mod(2,3) from dual;

-- POWER(n1,n2) 返回一个以n1为底n2的方根
select power(2,10), power(3,3) from dual;

-- SQRT 返回数字n的根
select sqrt(64),sqrt(10) from dual;

select ceil(3.1415926) from dual;
select floor(3.14159),floor(3.54159) from dual;

-- LN 返回一个数字的对数值
select ln(1),ln(2),ln(2.7182818) from dual;

-- TRUNC 按照指定的精度截取一个数
select trunc(124.1666,-2) ,trunc(124.16666,2) from dual;
select round(55.6), round(55.4),round(-55.4),trunc(55.5), trunc(-55.5) from dual;

-- SIGN 取数字n的符号，大于0返回1，小于0返回-1，等于0返回0
select sign(123), sign(-123), sign(0) from dual;

-- INITCAP 返回字符串并将字符串的第一个字母变为大写
select initcap('smith') upp from dual;

select lower('AaBbCcDd') AaBbCcDd,
       upper('AaBbCcDd') AaBbCcDd from dual;

-- RPAD 在列的右边粘贴字符   LPAD 在列的左边粘贴字符
select lpad(rpad('gao',10,'*'),27,'*') from dual;

-- LTRIM 删除左边出现的字符串   RTRIM 删除右边出现的字符串
select ltrim(rtrim('    gao qian jing    ',' '),' ') from dual;
-- TRIM('s' from 'string')  LEADING 剪切前面的字符串   TRAILING 剪切后面的字符串   如果不指定，默认为空格符
select trim('s' from 'string') from dual;

-- INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符，返回发现指定的字符串位置
-- C1 被搜索的字符串  C2 希望搜索的字符串 I 搜索开始的位置，默认为1  J 出现的位置，默认为1
select instr('oracle traning', 'ra',1,1 ) instring from dual;   --返回 2
select instr('oracle traning', 'ra',1,2 ) instring from dual;   --返回 9

-- SUBSTR(string,start,count) 取子字符串，从strat开始(按个数),取count个
select substr('13088888888',3,8) from dual ;   -- 返回 0888 8888

-- REPLACE('string','s1','s2')  string 希望被替换的字符串或变量  s1 被替换的字符串   s2 要替换的字符串
select replace('he love you','he','I') from dual;

-- SOUNDEX 返回一个与给定的字符串读音相同的字符串
create table table1 (xm varchar(8));
insert into table1 values('weather');
insert into table1 values('wether');
insert into table1 values('gao');
select xm from table1 where soundex(xm) = soundex('weather');
drop table table1;

-- ADD_MONTHS 增加或减去月份
select to_char( add_months(to_date('199912','yyyymm'),2 ),'yyyymm') from dual;

-- LAST_DAY 返回日期的最后一天
select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;
select last_day(sysdate) from dual;

-- MONTHS_BETWEEN(date2,date1) 给出date2-date1的月份
-- select months_between('19-12月-1999','19-3月-1999') from dual; Error

-- NEXT_DAY（date，'day'） 给出日期date和星期x之后计算下一个星期的日期
--select next_day('18-5月-2001','星期五') next_day from dual;  Error

-- NEW_TIME(date，'this','that') 给出在this时区=other时区的日期和时间
select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,
       to_char(new_time(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
      
select to_char(sysdate,'dd-mm-yyyy day') from dual;

-- trunc(date,fmt) 按照给出的要求将日期截断，如果fmt='mi'表示保留分，截取秒
select to_char(trunc(sysdate,'hh'), 'yyyy.mm.dd hh24:mi:ss') hh,
       to_char(trunc(sysdate,'mi'), 'yyyy.mm.dd hh24:mi:ss') hhmm from dual;

select to_number('1999') from dual;

-- BFILENAME(dir,file)
insert into file_tb1 values( bfilename('lob_dir1','image1.gif') );

select user from dual;
select username,user_id from dba_users where user_id = uid;

-- select avg() , max(), min(),count(),sum()

-------------------
-- rownum 查询记录列的行，如果有两条一样的数据，可以用rownum标识，然后根据rownum不同删除一条

 

---
select * from all_sequences  --查询数据库所有sequence

select * from all_tables    --查询数据库所有表