--创建用户
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;
相关资源：oracle代码
