oracle——其它示例语句

￡神魔★判官ぃ 2023-10-02 08:13 0阅读 0赞

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