Oracle使用（二）—— 一些常用的SQL命令和基础知识（包含权限、序列、视图、索引、完整性约束、事务等）

一、使用SQLPLUS连接数据库并切换或解锁账号

sqlplus  sys/123@orcl as sysdba(只用于dba) --初次连接

conn system/123@orcl  --切换帐号

conn sys/123@orcl as sysdba  --切换帐号

show user  --显示当前帐户

alter user scott account unlock;  --解锁

alter user scott account lock;  --加锁 

二、表空间、用户、角色、系统权限和对象权限

--查询数据文件
select * from v$datafile;

--查询日志文件
select * from v$logfile;

--查询控制文件
select * from v$controlfile;

--创建表空间
create tablespace ts_alex
datafile 'D:\Oracle\Server\oradata\orcl\alex_file.dbf' --数据文件的后缀名无固定类型，可随意编写
size 10M --指定数据文件的初始容量，单位可以是K（千字节）和M（兆字节）
autoextend on--设置初始空间使用完毕之后是否自动增加容量，on表示打开，off表示关闭
next 10M --当打开容量自动增加时，使用next设置每次增加的大小
maxsize 100M; --表示允许为数据文件分配的最大空间，不设置表示无限制

--创建临时表空间
crate temporary tablespace ts_alex_temp
...  --同上

--查询所有表空间
select * from v$tablespace;

--修改表空间
alter tablespace ts_alex 
add datafile 'D:\Oracle\Server\oradata\orcl\data_file2.dbf'
...  --同上

--删除表空间
drop tablespace ts_alex including contents and datafiles;

--查询所有用户
select * from all_users;

--创建用户（新创建的用户没有任何功能，不能连接数据库，这时要赋予角色或权限）
create user alex
identified by 123
default tablespace ts_alex; --tmporary tablespace ts_alex_temp

--修改用户
alter user alex
identified by alex123;

--修改用户表空间
alter user alex
default tablespace system
temporary tablespace ts_alex_temp;

--删除用户
drop user alex cascade; 

--角色：是一组权限的集合，常用的系统角色有：connect、resource、dba
connect --连接数据库
resource --使用数据库的表空间
dba --系统管理员的所有权限

--查看所有的角色
select * from user_role_privs;

--创建角色
create role alex;

--删除角色
drop role alex;

--权限：
--系统权限：表示在任何oracle帐号中执行指定的语句，如：create/drop/alter等DDL语句
--对象权限：是由用户赋予的访问或操作数据库对象的权限，如：insert/update/select等DML语句
--查询当前用户下的系统权限
select * from user_sys_privs;

--查询某个角色下的系统权限
select * from dba_sys_privs where grantee='CONNECT';

--查询对象权限
select * from user_tab_privs;

--查询当前用户下的表
select * from user_tables;

--系统权限传递
grant create user to scott with admin option;

--对象权限传递
grant create user to scott with grant option;

--角色权限
--将connect/resource/dba角色赋予alex角色
grant connect,resource,dba to alex;

--赋予alex角色对scott.emp表的查询、修改权限
grant select,update on scott.emp to alex;

--回收alex角色的dba角色
revoke dba from alex;

--回收alex角色中对scott.emp表的修改权限
revoke update scott.emp from alex;

--将角色alex赋予用户scott
grant alex to scott;

--回收用户scott的alex角色
revoke alex from scott;

--授予用户scott无限表空间权限
grant unlimited tablespace to scott;

--用户权限同上

--查询表的伪列
select e.*,rownum from emp e;

--复制一个新的表
create table emp3 as select * from emp where 1=1;

三、数据类型

1、字符类型

①  char类型：表示固定长度的字符串，它的列长度是1-2000个字节，未定义时默认占用一个字节，若用户输入的值小于指定长度，

则在值后用空格补全，若用户输入的值大于指定的长度时则返回错误报告；

②  varchar2类型：表示可变长度的字符串，它的列长度是1-4000个字节；

③  long类型：用来存储可变长度的字符串，最多能存储2GB，它可以用来存储varchar2类型不能存储的长文本信息，其长度受计算机

上可用存储空间的限制，设置long数据类型时需遵循以下原则：

a.一个表只能有一个列可以为long数据类型；

b.long列不能定义为唯一约束或主键约束；

c.long列上不能建立索引；

d.过程或存储过程不能接受long数据类型的参数

2、数值类型

number类型可以存储正数、负数、零、定点数和精度为38位的浮点数，用法如下：

①  列名  number	默认值（P=38，S=0）

②  列名  number(P)	使用定点数

③  列名  number(P,S)	使用浮点数

P为精度表示数字的有效位数，值在1-38之间，S为范围表示小数点右边的位数，值在-84~-127之间


3、日期时间类型

①  date类型：用于存储表中的日期和时间类型的数据，date类型的存储空间是固定的7个字节，每个字节分别存储世纪、年、月、日

小时、分钟、秒

sysdate  代表当前的系统时间

②  timestamp类型：用于存储年、月、日、小时、分钟、秒（精确到小数点后6位），同时包含时区信息

4、RAW和LONG RAW类型（存储的是二进制数据值）

①  raw类型：用于存储基于字节的数据，些类型最多存储2000个字节，该类型没有默认大小，使用时应指定大小，可建立索引

②  long raw：用于存储可变长度的二进制数据，最多存储2GB，不可建立索引，long类型受到的所有限制对long raw类型也同样有效

5、LOB数据类型（大对象数据类型，可存储4GB的非结构化信息）

① clob(字符lob),它能存储大量字符数据，可用于存储非结构化的xml文档；

② blob(二进制lob),它能存储较大的二进制对象，如图片、视频、音频等；

③ bfile(二进制文件)，它能将二进制文件存储在数据库外部的操作系统文件中

6