oracle数据库基本操作

oracle数据库基本操作

1.初始orcle

. 练习所需表  以下纯代码都是此表操作

create table teacher2(
​
   name varchar2(30),
​
   tid number(4) primary key ,
​
   monmary varchar2(100)
​
   );
​
insert into teacher(name,tid,monmary) values('张三',01,'描述');
​
insert into teacher(name,tid,monmary) values('李四',02,'描述');
​
insert into teacher(name,tid,monmary) values('王二',03,'描述');
​
insert into teacher(name,tid,monmary) values('王五',04,'描述');
​
insert into teacher(name,tid,monmary) values('王六',05,'描述');
​
insert into teacher(name,tid,monmary) values('王琦',06,'描述');
​
insert into teacher(name,tid,monmary) values('网吧',07,'描述');
​
insert into teacher(name,tid,monmary) values('账务',08,'描述');
​
insert into teacher(name,tid,monmary) values('李六',09,'描述');
​
insert into teacher(name,tid,monmary) values('篱笆',10,'描述');
​
insert into teacher(name,tid,monmary) values('王下',11,'描述');
​
select * from teacher;

​

 

去重

select distinct monmary from teacher;

 

分页查询

select * from 
​
(select name,rownum as rn from teacher) t
​
where rn between 5 and 10;
​

 

创建新表

create table teacher2
​
as 
​
select * from teacher;
​
select * from teacher2;
​

 

集合函数

--union    相同列加不同列
​
   select * from teacher
​
   union
​
   select * from teacher2;
​
   --union all   全部列
​
   select * from teacher
​
   union all
​
   select * from teacher2;
​
   --intersect    相同列
​
   select * from teacher
​
   intersect
​
   select * from teacher2;
​
   --minus        不同列
​
   select * from teacher
​
   minus
​
   select * from teacher2;
​

 

转换函数

 --tochar
​
    SELECT to_char(324,'$9,9,9,9,9') FROM dual;
​
   --todate
​
    SELECT SYSDATE FROM dual;
​
    SELECT to_date('1999-01-01','yyyy-MM-dd') FROM dual;
​
if else
​
   SELECT NVL(2,3)FROM dual;
​
   SELECT NVL2(NULL,1,10)FROM dual;
​
   --switch
​
   SELECT DECODE(2,1,'一月',2,'二月','100')FROM dual;
​

 

分析函数

SELECT NAME,tid ,
​
       RANK() OVER(PARTITION BY NAME ORDER BY tid DESC)  "rank",
​
       dense_RANK() OVER(PARTITION BY NAME ORDER BY tid DESC)  "dense_RANK",
​
       row_number() OVER(PARTITION BY NAME ORDER BY tid DESC)  "row_number"
​
    FROM teacher t;   
​

 

2.orcle核心操作

1.表空间

--创建表空间
​
CREATE TABLESPACE tb_sbs
​
DATAFILE 'd:\orcleSpace\tb1.dbf'
​
SIZE 50m;
​
--更改表空间文件大小
​
ALTER DATABASE 
​
DATAFILE 'd:\orcleSpace\tb1.dbf'
​
RESIZE 100m;
​
--为表空间添加数据库文件  ,并文件大小自动增长
​
ALTER TABLESPACE  tb_sbs
​
ADD DATAFILE 'd:\orcleSpace\tb2.dbf'
​
SIZE 20m
​
AUTOEXTEND ON;
​
--删除表空间
​
DROP TABLESPACE tb_sbs INCLUDING CONTENTS;
​

 

2.用户操作

--创建用户  并指定PASSWORD EXPIRE;让用户