ORACLE 基本建表语句

--删除列 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)
失 )
--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 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;
alter table stuinfo drop constant stu_fk;
insert into students values(stu_seq.nextval,'张三',1,sysdate);
insert into stuinfo values(stu_seq.currval,'威海');
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
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 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;
--删除表 drop table students;
--修改表的名称 rename alist_table_copy to alist_table;
--显示表结构 describe test --不对没查到
-----------------------对字段的操作 ------------------------------------增加列 alter table test add address varchar2(40);
--函数 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 的描述 中间表实现多对多关联
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
references T_STU (STU_ID)
)
--创建表 create table classes(
id number(9) not null primary key, classname varchar2(40) not null ) --查询表 select * from classes;
在建立表格时就指定主键和外键
create table T_STU (
STU_ID
r(5)
not null,
STU_NAME
varchar2(8)
not null,
constraint PK_T_STU primary key (STU_ID)
);
主键和外键一起建立：
create table T_SCORE (
--班级——姓名 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 and sc.couse_id=c.couse_id and s.id=26;
)
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 user han identified by han default tablespace users Temporary TABLESPACE Temp; grant connect,resource,dba to han; //授予用户 han 开发人员的权利
--------------------对表的操作--------------------------
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 classes values(class_seq.nextval,'软件一班') commit;
--更新数据 update stu_account set username='aaa' where count_id=2; commit;