高级数据库技术_Oracle SQL.docVIP

Oracle基本SQL语句 1.建表： create table name(column1 number, column2 decimal(m,n), column3 char(m), column4 varchar(m), column5 date ); 2.删除表： drop table table_name; 3.删除表内容： delete from table_anme [where …]; 4.重命名表： remane table_name1 to table_name2; 5.锁定表： select * from table_name for update; 6.修改表头信息： alter table table_name add(column datatype); alter table table_name drop column column_name; alter table table_name modify column_name datatype; eg：alter table stu2011 add (score number check (score between 0 and 100)); 7.插入数据： insert into table_name (column1, column2, column3) values(‘char’, number, ‘varchar’); 8.备份表： create table table_name2 as select * from table_name1; create table temp as select * from table_name where 0=1;--只有表头信息 9.更新表： update table_name set column1=’char’, column2=number where column3=’varchar’; 10.约束： create table class (-- 父表，主表 bjh char(2) primary key,--unique name varchar(2)); create table st11( --子表，从表 stuno char(11), name varchar(12) not null unique , gender char(1) , classno char(2),--班级号 telno varchar(18) not null , gpa number check (gpa between 0 and 100), birthday date, constraint pk_stu11 primary key(stuno), constraint ck_sex (gender in (F,M)), constraint fk_classno foreign key (classno) references class(bjh) , constraint name_unique unique(name)); 11.提交撤销： commit; rollback; Oracle复杂SQL语句 1.标识列： amount generated always as (qty*price) 2.begin…end 分块语句： begin begin insert into item_sal(qty,price)values(56,12.5); commit; end; insert into item_sal(qty,price)values(100,12.5); rollback; end; 3.order by语句： select stuno, xm from stu2011 where bj=二班 order by stuno desc nulls last; --asc升序，desc降序 4.随机值： create table stu as select stuno, xm, xb, bj, dbms_random.value(60,100) score from stu2011; insert into stu select stuno, xm, xb, bj, dbms_random.value(60,100) score from stu2011; update stu set score=round(score,1); update stu set score=trunc(score,1); update stu a set score=(select round(dbms_random.value(60,100))from stu where stuno=a.