Oracle_基本建表语句

Oracle_基本建表语句

--创建⽤户

create user han identified by han default tablespace

users Temporary TABLESPACE Temp;

grant connect,resource,dba to han; //授予⽤户han开发⼈员的权利

--------------------对表的操作--------------------------

--创建表

create table classes(

id number(9) not null primary key,

classname varchar2(40) not null

)

--查询表

select * from classes;

--删除表

drop table students;

--修改表的名称

rename alist_table_copy to alist_table;

--显⽰表结构

describe test --不对没查到

-----------------------对字段的操作

-----------------------------------

--增加列

alter table test add address varchar2(40);

--删除列

alter table test drop column address;

--修改列的名称

alter table test modify address addresses varchar(40;

--修改列的属性

alter table test modi

create table test1(

id number(9) primary key not null,

name varchar2(34)

)

rename test2 to test;

--创建⾃增的序列create sequence class_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;

select class_seq.currval from dual

--插⼊数据

insert into classes values(class_seq.nextval,'软件⼀班')

commit;

--更新数据

update stu_account set username='aaa' where count_id=2;

commit;

--创建唯⼀索引

create unique index username on stu_account(username); --唯⼀索引不能插⼊相同的数据

--⾏锁在新打开的对话中不能对此⾏进⾏操作

select * from stu_account t where t.count_id=2 for update; --⾏锁

--alter table stuinfo modify sty_id to stu_id;

alter table students drop constraint class_fk;

alter table students add constraint class_fk foreign key (class_id) references classes(id);--外键约束

alter table stuinfo add constraint stu_fk foreign key (stu_id) references students(id) ON DELETE CASCADE;--外键约束,级联删除alter table stuinfo drop constant stu_fk;

insert into students values(stu_seq.nextval,'张三',1,sysdate);

insert into stuinfo values(stu_seq.currval,'威海');

select * from stuinfo;

create table zhuce(

zc_id number(9) not null primary key,

stu_id number(9) not null,

zhucetime date default sysdate

)

create table feiyong (

fy_id number(9) not null primary key,

stu_id number(9) not null,

mx_id number(9) not null,

yijiao number(7,2) not null default 0,

qianfei number(7,2) not null

)

create talbe fymingxi(

mx_id number(9) not null primary key,

feiyong number(7,2) not null, //共7位数字，⼩数后有两位 class_id number(9) not null}

create table card(

card_id number(9) primary key,

stu_id number(9) not null,

money number(7,2) not null default 0,

status number(1) not null default 0 --0表可⽤,1表挂失

)

--链表查询

select c.classname||'_'||s.stu_name as 班级_姓名,si.address from classes c,students s , stuinfo si where c.id=s.class_id and

s.id=si.stu_id;

insert into students values(stu_seq.nextval,'李四',1,sysdate); insert into stuinfo values(stu_seq.currval,'南京');

--函数

select rownum,id,stu_name from students t order by id asc;

--中间表实现多对多关联

--（1 1， 1 n，n 1，n n ）

--1 n的描述 1的表不作处理 n的表有1表的字段

--1 1的描述主外键关联

--n n的描述中间表实现多对多关联

create table course(

course_id number(9) not null,

couser_name varchar2(40) not null

)

alter table course to couse;

create table stu_couse(

stu_couse_id number(9) primary key,

stu_id number(9) not null,

couse_id number(9) not null

)

create unique index stu_couse_unq on stu_couse(stu_id,couse_id); --唯⼀学⽣

create sequence stu_couse_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;

create sequence couses_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;

insert into course values(couses_seq.nextval,'计算机原理');

insert into course values(couses_seq.nextval,'编译原理');

insert into course values(couses_seq.nextval,'数据库原理');

insert into course values(couses_seq.nextval,'数据结构');insert into course values(couses_seq.nextval,'计算机基础');

insert into course values(couses_seq.nextval,'C语⾔初步');

commit;

insert into stu_couse values(stu_couse_seq.nextval,1,1);

insert into stu_couse values(stu_couse_seq.nextval,1,3);

insert into stu_couse values(stu_couse_seq.nextval,1,5);

insert into stu_couse values(stu_couse_seq.nextval,1,5);

insert into stu_couse values(stu_couse_seq.nextval,2,1);

commit;

select * from stu_couse;

select * from course;

--select s.stu_name,sc.couse_id, c.couser_name from students s,course c,stu_couse sc where stu_id=1

--select couse_id from stu_couse where stu_id=1

select cl.classname,s.stu_name,c.couser_name from stu_couse sc, students s,course c,classes cl where s.id=sc.stu_id and

sc.couse_id=c.course_id and s.class_id=cl.id and s.id=1;

--班级——姓名

select c.classname,s.stu_name from students s,classes c where

s.class_id=c.id and s.id=2;

select * from students s where s.id=2

--班级——姓名——课程

select cl.classname,s.stu_name,c.couse_name from stu_couse sc,students s,classes cl,couse c where sc.stu_id=s.id andsc.couse_id=c.couse_id and s.id=26;

--sql 语句的写法，现写出关联到的表，然后写出要查找的字段，第三写出关联条件，记住在写关联到的表时先写数据多的表，这样有助于提⾼sql的效率select c.couser_name,s.stu_name from stu_couse sc,students s,course c where c.course_id=1 and c.course_id=sc.couse_idand sc.stu_id=s.id;