Oracle基础

/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