关于用户的命令

创建新的用户命令

create  user 用户名 identified by 密码 default tablespace 默认表空间名

用户锁定和解锁

# 锁定
alter user 用户名 account lock;

# 解锁
alter user 用户名 account unlock;

用户修改密码操作

两种做法:

第一种(忘记自己管理员密码(即system用户)时)

# 1: 进入Oracle控制台
sqlplus /nolog 
# 2: 以管理员权限登录
conn /as sysdba 
# 3: 修改用户名和密码
alter user 用户名 identified by 密码

第二种(system登录成功后)

alter user 用户名 identified by 密码;

用户授权操作

# 创建会话权限
grant create session to 用户名;
# 连接创建表,使用表空间权限
grate connect , resource to 用户名
# 用户将某个表的访问权限授予某个用户,权限信息,(select,delete,insert,update,all等)
grant 权限信息 on 表名 to 用户名
 # 如果系统权限,需要执行以下语句
grant all on 表名 to 用户名 with admin option;
# 对象权限
grant 权限信息 on 表名 to 用户名 with grant option
# 用户撤销对某个用户赋予的权限
revoke 权限信息 on 表名 to 用户名

对表的操作

查询非当前用户下的表

前提条件:拥有表的用户必须将该表授权给当前用户

select * from 拥有该表的用户名.表名

根据rownum进行分页查询

select * from (select rownum r , d.*from dept d) where r <= 3 and r>0;

复制表&数据复制

#复制表
create table dept1 as select * from dept;
#数据复制
insert into dept1 as select * from dept1;

复制完成后,dept1与dept的差别在于,dept1没有dept中字段的约束(如:NOT NULL) 外,字段名,类型,表中数据完全一样.

插入日期格式的数据

insert into 表名 values (date'2021-02-03')

insert into 表名 values (to_date('2021/02/03','YYYY/MM/DD'))

整型运算处理空值

select empno,ename,sal,comm,sal+nvl(comm,0) zsal from emp

集合操作符

• UNION 并集
• UNION ALL 并集且不去重
• INTERSECT 交集
• MINUS 把第一个查询结果中含有第二个查询结果的元素去掉

分组函数

• GROUP BY 子句
  • 用于将信息划分为更小的组
  • 每一组行返回针对对改组信息的单个结果
• HAVING子句
  • 用于指定GROUP BY 子句检索行的条件

select 字段列表
from 表清单
where 查询条件
group by 分组字段
having 分组查询条件

分析函数

以下三个分析函数用于计算一个行在一组有序行中的排位,序号从1开始

• ROW_NUMBER 返回连续的排位,不论值是否相等

  例如:

  

• RANK 具有相等值得行排位相同,序数随后跳跃

  

• DENSE_RANK 具有相等值的行排位相同,序号是连续的

树查询

create table tree(
   id number(10) not null primary key,
　　name varchar2(100) not null,
　　super number(10) not null                // 0 is root

)

从子到父

select  * from tree start with id = ? connect by id = prior super

从父到子

select * from tree start with id = ? connect by prior id = super

序列

创建序列

CREATE SEQUENCE 序列名
	START WITH 10	//起始    不可更改
	INCREMENT BY 10 //步长
	MAXVALUE 2000	//最大取值
	MINVALUE 10		//最小取值
	NOCYCLE 		//是否循环
	CACHE 10;		//是否缓存

查看序列下一值

select 序列名.nextval from dual

查看序列当前值

select 序列名.currval from dual

更改序列

除start with 起始值不可修改以外 ,其他属性都是能改的

alert sequence 序列名 
increment by 要更改的值

表分区

查看当前表中的分区

select partition_name,high_value from user_tab_partitions where table_name = '表名';    //表名字母必须大写

范围分区

创建分区

partition by range(column_name) (
  partition p1 values less than (date'1979-12-31'),
  partition p2 values less than (date'1989-12-31'),
  partition p3 values less than (date'1999-12-31'),
  partition p4 values less than (date'2020-12-31')
 )

追加分区

alert table person add partition p5 values less than(maxvalue)

maxvalue:是一个伪值,它没有界限

散列分区

创建分区

散列分区语法:

PARTITION BY HASH(column_name)
PARTITIONS number_of_partitions;
或
PARTITION BY HASH(column_name)
(PARTITION part1 [TABLESPACE tbs1],
PARTITION part2 [TABLESPACE tbs2],
···
PARTITION part1N [TABLESPACE tbsN],)

列表分区

创建分区

允许用户将不相关的数据组织在一起

PARTITION BY LIST(column_name)
(
PARTITION part1 VALUES (values_list1),
PARTITION part2 VALUES (values_list2),
···
PARTITION partN VALUES (values_listN),
)

复合分区

创建分区

PARTITION BY RANGE (column_name1)
SUBPARTITION BY HASH (column_name2)
SUBPARTITIONS number_of_partitions
(
	PARTITION part1 VALUE LESS THAN(range1),
	PARTITION part2 VALUE LESS THAN(range2),
	···
	PARTITION partN VALUE LESS THAN(MAXVALUE)
);

合并分区

ALTER TABLE SALES MERGE PARTITIONS S1 , S2 INTO PARTITION S2

拆分分区

alter table person split partition p3 at (date'1989-12-31')
 into(partition p31,partition p32);

视图

创建视图

CREATE [OR REPLACE][FORCE] VIEW
 view_name [(alias[,alias]....)]
 AS select_statement
 [WITH CHECK OPTION]
 [WITH READ ONLY];

索引

创建标准索引

CREATE INDEX item_index ON itemfile(itemcode) TABLESPACE index_tbs;

反向键索引

CREATE INDEX rev_index ON itemfile(itemcode) REVERSE;

重建索引

ALTER INDEX item_index REBUILD

删除索引

DROP INDEX item_index

查看索引

select index_name,index_type from user_indexes where table_name ='表名';