--创建用户
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
      increment by 1
      start with 1
      nomaxvalue
      nominvalue
      nocache
      
       create sequence seq_cj_id
      increment by 1
      start with 1
      nomaxvalue
      nominvalue
      nocache
      --添加，实现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;