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; /*删除表空间，删除的时候必须保证不被引用*/
