自己遇到的一些问题（随意杂乱）
create table suntan
(
  id number(22) not null,
  name varchar2(10) not null,
  num number(6),
  inseDate timestamp(6)
)

创建和删除索引的两种方法
alter table test add  primary key (id);
alter table test drop primary key

alter table test add constraint s_id primary key (id);
alter table test drop constraint s_id



alter table suntan add constraint suntan_pk primary key (id);

alter table suntan add constraint suntan_uk unique (name);

create index suntan_index on suntan(id);

alter table suntan add constraint suntan_fk foreign key (num) references item(id);

alter table suntan add  s_next varchar2(10);

alter table suntan modify s_next varchar2(20);

alter table suntan drop column s_next;

alter table suntan disable constraint suntan_fk;

alter table suntan enable constraint suntan_fk;

alter table suntan drop --foreign key(num)不行 --unique (name); 可以

alter table suntan drop constraint suntan_fk;--foreign key (num);--unique (name);

alter table suntan drop constraint suntan_uk cascade;

insert into suntan values(2, 'suntan', 2, to_date('2008-07-02', 'yyyy-MM-dd'), 'aa');

insert into suntan values(1, 'suntan1', 1, to_date('2008-07-02', 'yyyy-MM-dd'), 'aa');

----检验unique key 是否允许插入两个空值，当它没有规定NOT NULL 时

alter table suntan modify name varchar2(20) null;

insert into suntan values(3, NULL, 3, to_date('2008-07-02', 'yyyy-MM-dd'), 'aa3');

insert into suntan values(4, NULL, 4, to_date('2008-07-02', 'yyyy-MM-dd'), 'aa4');

经过证明可以，但是当为primary key 时，必须有值且都能为NULL，因为要根据这个primary key 能查到值
------------------

select * from suntan where num < (select max(num) from suntan)--ANY(select max(num) from suntan)

select * from suntan where num > (select MIN(num) from suntan) --(select MIN(num) from suntan)

select id,num, decode(id, 1, num*2,
                          2, num*2,
                          num) newDa from suntan;
                         
select to_number('$2', '$99.99') from suntan;

select constraint_name, constraint_type, search_condition from user_constraints where table_name = 'SUNTAN'

select constraint_name, column_name from user_cons_columns where table_name = 'SUNTAN'

describe suntan; -- pl/sql 不支持， SQL/PLUS 支持

DESC suntan; -- pl/sql 不支持， SQL/PLUS 支持

select * from user_tables;

select * from user_objects; --查询当前用户不同的对象类型

select * from user_catalog; -- 查询当前用户的表、视图、同义词、序列


--创建视图和操作视图
create VIEW suntanView(id1, name1) as select s.id, s.name from suntan s where s.id=1; --PL/SQL不支持

select view_name, text from user_views;

select * from SUNTANVIEW;



--修改视图
create or replace View SUNTANVIEW as select id, name, num from suntan;
--在视图上可以执行DML操作，但必须遵循以下规则：
--1。 在简单视图上可以执行DML操作
insert into suntanView(5, 'suntan5', 5); 错误， 不能执行
update suntanView set name = 'suntan2' where id=2; 可以执行
delete from suntanView where id=2;
--2。 如下视图中包含以下内容不能删除行
      -- a。 group 函数
      -- b.  group by 函数
      -- c.  distinct 函数
-- 删除视图
drop view suntanView



--序列
create sequence suntan_seq
increment by 1
s