oracle——其它示例语句

create tablespace test datafile 'C:\test.dbf' size 20M;

create table student( id number,
                      name varchar2(20),
                      sex varchar2(20),
                      age number,
                      address varchar2(20)
                    ) tablespace test
                    
select * from student;

insert into student(id,name,sex,age,address) values(2,'yuanling','nv',27,'zhejiang');

insert into student values(1,'shenweiwei','nan',31,'jiangsu');

insert into student values(4,'shenweiwei04','nv',32,'jiangsu04');

insert into student values(3,'shenweiwei03','nan',22,'jiangsu03');

select * from student;

select * from student order by id desc;

select * from student order by id asc;

select * from student where id >=3;

select * from student where id in (1,3);

select * from student where id not in (1,3);

select name id,name from student where address in ('zhejiang','jiangsu');

select * from student where age between 20 and 28;

select * from student where age not between 20 and 28;

select * from student where name like 'sh%';

select * from student where name like '%nl%';

select * from student where name like '%i';

select * from student where name not like '%nl%';

select count(*) from student;

select max(id) from student;

select min(id) from student;

select avg(id) from student;


select max(age) from student;

select min(age) from student;

select avg(age) from student;

select * from student;

insert into student(id,name,sex,age) values(5,'shenweiwei05','nan',22);

select * from student where address is null;

select * from student where address is not null;

select * from student;

update student set address = 'beijing' where id = 5;


===============================================================================================

create table student02( id number,
                      name varchar2(20),
                      sex varchar2(20),
                      age number,
                      address varchar2(20)
                    ) tablespace test
                    
select * from student02;

insert into student02(id,name,sex,age,address) values(7,'yuanling07','nan',27,'jiangsu');

insert into student02 values(6,'shenweiwei06','nv',25,'zhejiang');

insert into student02 values(8,'shenweiwei08','nv',45,'zhejiang01');

=================================================================

insert into student(id,name,sex,age) select id,name,sex,age from student02 where student02.id = 6;

select * from student;

/*delete from student;*/

update student set address = 'zhejiang' where id = 6;
insert into student(id,name,sex,age,address) select id,name,sex,age,address from student02 where student02.id in (7,8);


update student set sex = 'nan',age = 33 where id = 8;

=================================================================
select * from student02;

alter table student02 rename to ss;

select * from ss;

alter table ss rename to student02;

=======================================================================

select sex,count(*) from student group by sex;

select * from student02;

alter table student02 add( telphone number,kecheng varchar(40));   /*给已经存在的表，增加2个字段名*/

update student02 set telphone = 18758298426,kecheng = 'yuwen' where id = 6;

update student02 set telphone = 18758298427,kecheng = 'shuxue' where id = 7;

update student02 set telphone = 18758298428,kecheng = 'ruanjian' where id = 8;

alter table student02 modify (kecheng varchar(60));               /*修改已有列的数据类型*/

alter table student02 rename column address to dz;       /*修改字段名*/

alter table student02 rename column dz to address;       

alter table student02 drop column kecheng;              /*删除已经存在的字段*/

===============================================================================

将表从一个空间转移到另外一个空间（另外一个空间要存在）：

alter table 表名 move tablespace 新空间名;

删除表：

drop table student02;

======================================

create tablespace test02 datafile 'C:\test02.dbf' size 20M;

alter table student move tablespace test02;

alter table student move tablespace test;


select * from student;

select id as a,name as b,sex as c,age,address from student;


select id as 学号,name as 姓名,sex as 性格,age,address from student;

select * from student t where t.id = 4;

select id as a,name as b,sex,age from student t where t.id in (2,3,4,5);

select * from student;

select * from student where rownum <=3;

select * from student order by id desc;

select * from student where rownum < 4 order by id desc;

select * from ( select * from student order by id desc ) where rownum <4;

select * from student order by 4;    /*通过列的顺序值进行排序*/

select * from student order by 1;

select * from student order by 1 desc;

select count(sex) from student where sex = 'nan';
select count(sex) from student where sex = 'nv';

select count(age) from student where age between 20 and 30;

alter tablespace test02 rename to xs;            /*修改表空间的名字*/

select tablespace_name,file_name from dba_data_files order by file_name; /*查看表空间*/

alter tablespace  xs rename to test02;

drop tablespace test02 including contents and datafiles;   /*删除表空间，删除的时候必须保证不被引用*/