Oracle基础

RenJig 2020-07-03 05:58:21

/*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;

...全文