/ROWNUM 是一个只有在实际返回数据时一个附加的数列/ select rownum, deptno , dname from dept; insert into dept values(30,‘jack’,‘new york’); insert into dept values(40,‘AAA’,‘new york’); insert into dept values(50,‘VVV’,‘new york’); insert into dept values(60,‘CCC’,‘new york’);
/创建一个表/
create table student( stuName varchar2(10) NOT NULL, stuNo char(6) not null, stuAddress varchar2(50) )
/修改数据库的字段属性/ alter table student modify(stuName varchar2(50));
/在数据表中添加字段/ alter table student add(stuPhone varchar(11))
/删除数据表中的某一字段/ alter table student drop(stuPhone);
/删除表/ drop table student;
/插入数据/ insert into student values(‘张三’,‘000001’,‘中国’); insert into student values(‘李四’,‘000002’,‘中国’); insert into student values(‘王五’,‘000003’,‘中国’); insert into student values(‘张三’,‘000004’,‘中国’); commit;
select * from student;
/去掉返回集中重复的数据 as 给字段起一个别名/ select distinct stuname as “学生姓名” from student
/* order by 根据某一个字段进行排序*/ select * from student order by stuName desc
/创建一个新表，但是这个表的数据来自student/ create table student2 as select * from student;
/* 选择指定的列来创建新表*/ create table student3 as select stuname,stuaddress from student2
create table student4 as select * from student where 1 = 2
select * from student4
select count(*) from student; select count(1) from student; select count(stuname) from student
select rowid, stuname, stuno from student group by rowid, stuname ,stuno having (count(stuname||stuno)<2)
drop from student where rowid not in select max(rowid) from student group by stuname,stuno having(count(stuname||stuno)=1)
select max(rowid) from student group by stuname,stuno having(count(stuname||stuno)>=1)
/筛选出重复的数据/ select rowid,stuname from student where rowid not in( select max(rowid) from student group by stuname,stuno having(count(stuname||stuno)>=1) )
/删除重复数据，（学生姓名和学生编号相同的视为重复）/ delete from student where rowid not in( select max(rowid) from student group by stuname,stuno having(count(stuname||stuno)>=1) ); commit;
select * from student
create table tTest( str char(5) not null )
insert into tTest values(‘a’); insert into tTest values(‘b’); insert into tTest values(‘c’); insert into tTest values(‘d’)
select * from ttest
/* 创建存档点*/ savepoint a; insert into tTest values(‘e’)
/回退到某一个存档点/ rollback to a
/回退所有未提交的事务/ rollback;
commit;
select * from ttest insert into tTest values(‘f’); insert into tTest values(‘g’) commit;
select rownum , str from ttest order by str desc
select str , rownum rn from(select str from ttest order by str desc) where rownum>1 and rownum<7
/* Orcale 分页*/ select * from(select str , rownum rn from(select str from ttest order by str desc)) where rn >=2 and rn<=6
/集合操作符/ /创建表/ create table tablea( /* not null 不能为空*/ str char(2) not null )
/创建表/ create table tableb( str char(2) not null )
/插入数据/ insert into tablea values(‘a’); insert into tablea values(‘b’); insert into tablea values(‘c’); insert into tablea values(‘d’); insert into tablea values(‘a’); /提交事务/ commit; select * from tablea;
insert into tableb values(‘a’); insert into tableb values(‘b’); insert into tableb values(‘b’); insert into tableb values(‘d’); insert into tableb values(‘e’); commit;
/集合操作符/ /union 联合查询，返回的数据是不重复的数据 （会自动去除重复的数据，只保留一条）/ select str from tablea union select str from tableb ; /union all 会将两个表中的所有的数据全部返回，包括重复的数据/ select str from tablea union all select str from tableb;
select str from tablea ;
/minus 返回第一个表中存在 但是第二个表中没有的数据 （第一个表指的是minus左边的表！！！）/ select str from tablea minus select str from tableb;
/intersect 返回两个表共有的数据/ select str from tablea intersect select str from tableb;
/连接操作符，将列名链接在一起/ select dname ||’-’|| address from dept
