oracle数据库建表语句

oracle数据库建 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 语句 篇一: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; --中间表实现多对多关联 --(11， 1n，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 and sc.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_id and sc.stu_id=s.id; select s.stu_name from students s,stu_couse sc where s.id=sc.stu_id group by s.id,s.stu_name; select c.classname,count(sc.couse_id) from stu_couse sc,students s,classes c where s.class_id=c.id and s.id=sc.stu_id group by c.classname; select s.stu_name, count(sc.couse_id) from stu_couse sc,students s,classes cl where s.id=sc.stu_id group by s.id,s.stu_name having count(sc.stu_couse_id)>3; 班级 学生 选课数量 select cl.classname,count(sc.stu_couse_id) from stu_couse sc,students s,classes cl where s.id=sc.stu_id and s.class_id=cl.id group by cl.classname; --班级 学生 选课数量 select cl.classname,s.stu_name,count(sc.stu_couse_id) from stu_couse sc,students s,classes cl where s.id=sc.stu_id and s.class_id=cl.id group by s.stu_name; select cl.classname,s.stu_name,count(sc.stu_couse_id) from stu_couse sc ,students s,classes cl where sc.stu_id=s.id and s.class_id=cl.id group by s.id; 篇二:oracle数据库建表空间语句汇总 建立表空间和用户的步骤: 用户 建立:create user 用户名 identified by 密码; 授权:grant create session to 用户名; grant create table to 用户名; grant create tablespace to 用户名; grant create view to 用户名; 表空间 建立表空间(一般建N个存数据的表空间和一个索引空 间): create tablespace 表空间名 datafile ' 路径(要先建好路径)\***.dbf ' size *M tempfile ' 路径\***.dbf ' size *M autoextend on --自动增长 --还有一些定义大小的命令，看需要 default storage( initial 100K, next 100k, ); 用户权限 授予用户使用表空间的权限: alter user 用户名 quota unlimited on 表空间; 或 alter user 用户名 quota *M on 表空间; --表空间 CREATE TABLESPACE deej DATAFILE 'E:\database\deej.dbf' size 2G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --索引表空间 CREATE TABLESPACE sdt_Index DATAFILE 'F:\tablespace\demo' size 512M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --2.建用户 create user deej identified by deej default tablespace deej; --3.赋权 grant connect,resource,dba to deej; --导入导出命令 ip导出方式: exp deej/deej@10.120.1.3:1521/orcl file=f:/deej.dmp owner=deej imp celaw/celaw@celaw full=y ignore=y file=e:/201208201223.DMP DROP TABLESPACE celaw INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; 删除用户 drop user celaw cascade; 表空间赋权给用户:alter user celaw quota unlimited on TBS_CONTRACT; 建立物化视图: 指定必须的权限: GRANT ON COMMIT REFRESH to celaw; GRANT GLOBAL QUERY REWRITE TO celaw; 实例: 汇率:create materialized view mv_exchange_rate refresh force on demand start with sysdate next sysdate + 1 with rowid as select * from apps.alx_con_exchange_rate_v@fincon ; 税率:create materialized view mv_vat refresh force on demand start with sysdate next sysdate + 1 with rowid as select vat_code,tax_type_display,description,tax_rate,start_date from apps.alx_vat_v@fincon ; 物资类型:create materialized view mv_material_types refresh force on demand start with sysdate next sysdate + 1 with rowid as select * from apps.alx_material_types_v@fincon ; +1表示一天; 数据库由于误操作造成数据库用户被锁解决方法: 登录到服务器，在命令行环境下运行 sqlplus / as sysdba 然后 alter user sys identified by oracle; sys 用户的密码就被改成了oracle; SQL> alter user 用户名 account unlock; 用户就解锁了 端口号被占用解决 办法 鲁班奖评选办法下载鲁班奖评选办法下载鲁班奖评选办法下载企业年金办法下载企业年金办法下载 用命令 netstat -a -n -o 最后一个选项表示连接所在进程id. 找到8080端口的PID 然后打开任务管理器, 切换到进程选项卡, 在菜单栏选择 查看->选择列, 选择PID. 在列表中找到PID对应的进程 就可以了 Word打开缓慢修改 在资源管理器–工具–文件夹选项–文件类型中选中扩展名 为DOC,点击―高级‖-选择―打开‖-点击―编辑‖-在―用于执行编辑的应用程序中‖在 /n的后而加上―%1‖(注:一定要添上空格及―‖号)例如:―D:\Program\Microsoft Office\OFFICE11\WINWORD.EXE‖ /n ―%1‖ /dde 。注意一定是这个顺序，其它顺序都是不对的。取消―使用DDE‖前的钩，点击确定后，你的WORD打开速度就会提高很多，没有延迟了。同理，EXECL就 是选择扩展名为XLS，用相同的方法在/e的后而加―%1‖,并取消―使用DDE‖前的钩。在资源管理器–工具–文件夹选项–文件类型中选中扩展名为DOC,点击―高级‖-选择―打开‖-点击―编辑‖-在―用于执行编辑的应用程序中‖在 /n的后而加上―%1‖(注:一定要添上空格及―‖号)例如:―D:\Program\Microsoft Office\OFFICE11\WINWORD.EXE‖ /n ―%1‖ /dde 。注意一定是这个顺序，其它顺序都是不对的。取消―使用DDE‖前的钩，点击确定后，你的WORD打开速度就会提高很多，没有延迟了。同理，EXECL就 是选择扩展名为XLS，用相同的方法在/e的后而加―%1‖,并取消―使用DDE‖前的钩。 篇三:Oracle基本建表语句 创建表格语法: create table 表名( 字段名1 字段类型(长度) 是否为空, 字段名2 字段类型 是否为空 ); -增加主键 alter table 表名 add constraint 主键名 primary key (字段名1); -增加外键: alter table 表名 add constraint 外键名 foreign key (字段名1) references 关联表 (字段名2); 在建立表格时就指定主键和外键 create table T_STU ( STU_IDchar(5) not null, STU_NAME varchar2(8)not null, constraint PK_T_STU primary key (STU_ID) ); 主键和外键一起建立: create table T_SCORE ( EXAM_SCORE number(5,2), EXAM_DATEdate, AUTOIDnumber(10) not null, STU_IDchar(5), SUB_IDchar(3), constraint PK_T_SCORE primary key (AUTOID), constraint FK_T_SCORE_REFE foreign key (STU_ID) references T_STU (STU_ID) ) --创建表 create table classes( id number(9) not null primary key, classname varchar2(40) not null ) --查询表 select * from classes; --删除表 drop table classes; --修改表的名称 rename classes to classes1; --显示表结构 describe test -----------------------对字段的操(转载 于:www.cNBotHwin.cOm 博 威 范文网:oracle数据库建表 语句)作----------------------------------- --增加列 alter table test add address varchar2(40); --删除列 alter table test drop column address; --修改列的名称 alter table test rename column 旧列名 to新列名 // alter table test modify address addresses varchar(40); --修改列的属性 alter table test modify address(80) 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; selectclass_seq.currval from dual --插入数据 insert into classes values(class_seq.nextval,'软件一班 ') commit; --更新数据 updatestu_account set username='aaa' where count_id=2; commit; --行锁 在新打开的对话中不能对此行进行操作 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 ) createtalbefymingxi( 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 , stuinfosi 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,'南京'); --函数 selectrownum,id,stu_name from students t order by id asc; createtable course( course_id number(9) not null, couser_namevarchar2(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,coursec,stu_cousesc where stu_id=1 --select couse_id from stu_couse where stu_id=1 selectcl.classname,s.stu_name,c.couser_name from stu_cousesc, students s,coursec,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; --班级——姓名 selectc.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 --班级——姓名——课程 selectcl.classname,s.stu_name,c.couse_name from stu_cousesc,studentss,classescl,couse c where sc.stu_id=s.id and sc.couse_id=c.couse_id and s.id=26; --sql 语句的写法，现写出关联到的表，然后写出要查找 的字段，第三 写出关联条件，记住在写关联到的表时先写 数据多的表，这样有助于提高sql的效率 selectc.couser_name,s.stu_name from stu_cousesc,studentss,course c where c.course_id=1 and c.course_id=sc.couse_id and sc.stu_id=s.id; selects.stu_name from students s,stu_cousesc where s.id=sc.stu_id group by s.id,s.stu_name; selectc.classname,count(sc.couse_id) from stu_cousesc,studentss,classes c where s.class_id=c.id and s.id=sc.stu_id group by c.classname; selects.stu_name, count(sc.couse_id) from stu_cousesc,studentss,classes cl where s.id=sc.stu_id group by s.id,s.stu_name having count(sc.stu_couse_id)>3; 班级 学生 选课数量 selectcl.classname,count(sc.stu_couse_id) from stu_cousesc,studentss,classes cl where s.id=sc.stu_id and s.class_id=cl.id group by cl.classname; --班级 学生 选课数量 selectcl.classname,s.stu_name,count(sc.stu_couse_id) from stu_cousesc,studentss,classes cl where s.id=sc.stu_id and s.class_id=cl.id group by s.stu_name; selectcl.classname,s.stu_name,count(sc.stu_couse_id) from stu_cousesc ,students s,classes cl where sc.stu_id=s.id and s.class_id=cl.id group by s.id; selectcl.classname,s.stu_name,count(sc.stu_couse_id) from stu_cousesc,studentss,classes cl where sc.stu_id=s.id and s.class_id=cl.id group by s.stu_name; --班级 学生 所选课程id 所选课程名称 --创建试图 目的把表联合起来 然后看成一个表，在与其 他的联合进行查询 create view xsxk as select cl.classname, s.stu_name,c.couse_id, c.couse_name from stu_cousesc,studentss,classescl,couse c where sc.stu_id=s.id and sc.couse_id=c.couse_id and s.class_id=cl.id; select * from xsxk create view classstu as select s.id,c.classname,s.stu_name from students s,classes c where c.id=s.class_id; drop view classstu; --删除视图 select * from classstu; create view stu_couse_view as select s.id ,c.couse_name from stu_cousesc,studentss,couse c where s.id=sc.stu_id and sc.couse_id=c.couse_id; select * from stu_couse_view; create view csc as select cs.classname,cs.stu_name,scv.couse_name from classstucs,stu_couse_viewscv where cs.id=scv.id; select * from csc; select * from classes cross join students; --全连接，相当于 select * from classes,students; select * from classes cl left join students s on cl.id=s.class_id; --左连接 不管左表有没有 都显示出来 select * from classes cl right join students s on cl.id=s.class_id; --右连接 select * from classes cl full join students s on cl.id=s.class_id; --全连接 insert into classes values(class_seq.nextval,'软件四班 '); create table sales( nian varchar2(4), yeji number(5) ); insert into sales values('2001',200); insert into sales values('2002',300);