oracle的简单代码

--创建用户

create user lhy1 identified by passwd123

--表空间

 default tablespace users

 --临时表空间

 temporary tablespace temp;

 --给用户权限

 grant create session to lhy1;

 grant resource,connect to lhy1;

 -----------------------------************************----------------------------

--创建表

 create table student(stu_id number,stu_name varchar2(100));

  create table kemu(km_id number,km_name varchar2(100));

  create table chengji(cj_id number,km_id number,stu_id number,fs number

      );

     --加主键外键

      alter table student add constraint pri_stu primary key (stu_id);

      alter table kemu add constraint pri_km primary key(km_id);

      alter table chengji add constraint fore_km foreign key(km_id) references kemu(km_id)

      alter table chengji add constraint fore_stu foreign key(stu_id) references student

(stu_id)

      --创建序列

      create sequence seq_stu_id

      increment by 1

      start with 1

      nomaxvalue

      nominvalue

      nocache;

       create sequence seq_km_id

      nocache

       create sequence seq_cj_id

      --添加，实现id自动增长 

      insert into student values(seq_stu_id.nextval,'刘辉要1');

      insert into student values(seq_stu_id.nextval,'刘辉要2');

      insert into student values(seq_stu_id.nextval,'刘辉要3');

      select * from student for update;

      insert into kemu values (seq_km_id.nextval,'数学');

      insert into kemu values (seq_km_id.nextval,'语文');

     insert into kemu values (seq_km_id.nextval,'英语');

     select * from kemu for update;

     insert into chengji values(seq_cj_id.nextval,1,1,98);

      insert into chengji values(seq_cj_id.nextval,1,2,90);

       insert into chengji values(seq_cj_id.nextval,1,3,88);

        insert into chengji values(seq_cj_id.nextval,2,1,98);

      insert into chengji values(seq_cj_id.nextval,2,2,90);

       insert into chengji values(seq_cj_id.nextval,2,3,88);

        insert into chengji values(seq_cj_id.nextval,3,1,98);

      insert into chengji values(seq_cj_id.nextval,3,2,90);

       insert into chengji values(seq_cj_id.nextval,3,3,88);

       delete from chengji;

       select * from chengji;

   --查询学生 和他的各科成绩

    with sql1

    as

    (select c.stu_id ,k.km_name,c.fs from kemu k inner join chengji c on c.km_id=k.km_id)

    select s.stu_id,s.stu_name,sql1.km_name,sql1.fs  from student s inner join sql1 on 

s.stu_id=sql1.stu_id order by s.stu_id;

  --查询没有选课的学生 及没有选的课 使用集合 并集，差集，交集

 with t 

 as (select s.stu_id ,k.km_id from student s,kemu k minus select c.stu_id,c.km_id from 

chengji c)

select s.stu_name ,k.km_name from student s,kemu k where (s.stu_id,k.km_id) in (select * 

from t) order by s.stu_id;

    ------------------------********************-------------------------

create table test(id number,name varchar2(100),age number, constraint p_k primary key(id));

alter table test add constraint u_k unique(name);

alter table test modify age default 20;

--alter table test modify name default null;

insert into test (id,name) values (1,'lhy');

--设置自动增长

create sequence user_seq1

start with 1

increment by 1 

nomaxvalue

nominvalue

nocache;

--删除序列

drop sequence user_seq;

--查询序列

select * from user_objects where object_type='sequence'; 

create or  replace trigger tr_user1

before insert on test--test为表名

for each row

begin

select user_seq1.nextval into :new.id from dual;

end;

--删除触发器

drop trigger tr_user1;

--查询触发器

select * from user_objects where object_type='trigger'; 

delete from test;

    --插入

insert into test (name) values ('lhy3');

select * from test;