/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