oracle 基础操作

发表于 | 更新于 | 分类于 | 阅读次数：

用户账号密码

工具：SQL plus
sys,system,sysman:自己设定的密码
scott:tiger
错误警告密码7天后过期解决办法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL>SELECT PROFILE FROM DBA_USERS WHERE USERNAME='SYS';

PROFILE
------------------------------
DEFAULT

SQL>
SQL>SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES WHERE PROFILE ='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME                  LIMIT
------------------------------ ------------------------------
PASSWORD_LIFE_TIME             180

SQL>alter profile <profile_name> limit password_life_time UNLIMITED; -- profile_name: DEFAULT

SQL>alter user `c##scott` identified by 123456; -- 如仍有警告则修改

用户登录：[username/password] [@server] [as sysdba|sysoper]
如：system/root @orcl as sysdba
登录之后切换换用户：[conn | connect] sys/root as sysdba
查看当前用户：show user(非SQL语句可以不用分号结尾)
用户信息可以使用sysdba身份查看dba_users字典
启动和锁定用户账户:alter user username account lock/unlock;

表空间

分类：永久表空间（存储表、视图等），临时表空间（数据库执行过程的临时空间），UNDO表空间（保存事务执行前的状态，可以进行回滚）
dba_tablespaces,user_tablespaces数据字典

1
2
3
4
5
6
SQL>select tablespace_name from dba_tablespaces/user_tablespaces;
SYSTEM  -- 系统表空间
SYSAUX --Example辅助表空间
UNDOTBS1 -- UNDO表空间
TEMP  --临时表空间
USERS --用户表空间

dba_users,user_users数据字典
oracle字符串使用单引号
设置默认/临时表空间ALTER USER username DEFAULT|TEMPORARY TABLESPACE tablespace_name;
创建表空间：CREATE [TEMPORARY] TABLESPACE tablespace_name TEMPFILE|DATAFILE 'test.dbf' size 10m;其中test为文件名，10m为文件大小。文件名中可以设置文件路径否则在默认路径。
查看数据文件位置：

1
2
3
4
5
6
7
8
9
SQL>select file_name from dba_data_files/dab_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
D:\APP\12148\VIRTUAL\ORADATA\ORCL\SYSTEM01.DBF
D:\APP\12148\VIRTUAL\ORADATA\ORCL\SYSAUX01.DBF
D:\APP\12148\VIRTUAL\ORADATA\ORCL\UNDOTBS01.DBF
D:\APP\12148\VIRTUAL\ORADATA\ORCL\USERS01.DBF
D:\APP\12148\VIRTUAL\ORADATA\ORCL\TEMP01.DBF

修改表空间状态ALTER TABLESPACE tablespace_name ONLINE|OFFLINE;
查看状态select status from dba_tablespaces;
表空间读写状态ALTER TABLESPACE tablespace_name READ ONLY | READ WRITE;（只读||可读写，都需要ONLINE状态下，ONLINE状态默认是READ WRITE，status的三种取值：OFFLINE、READ ONLY、ONLINE）
增加数据文件ALTER TABLESPACE tablespace_name ADD DATAFILE 'xx.dbf' SIZE xx;
删除数据文件ALTER TABLESPACE tablespace_name DROP DATAFILE 'xx.dbf';（不能删除创建表空间时第一个数据文件）
删除表空间DROP TABLESPACE tablespace_name [INCLUDING CONTENTS];（INCLUDING CONTENTS表示连同文件内容一起删除）

数据表

数据类型：字符型，数值型，日期型，其他类型

  1. 字符型
    CHAR(n),NCHAR(n):NCHAR按照Unicode存储，汉字常使用此类型，charMaxn=2000,ncharMaxn=1000
    VARCHAR2(n),NVARCHAR2(n):不补充空格，不浪费空间，varcharMaxn=4000,nvarcharMaxn=2000
  2. 数值型
    NUMBER(p,s):p有效数字，s小数点后位数
    FLOAT(n):二进制
  3. 日期型
    DATE：范围-公元前4712年1月1日到公元9999年12月31日，精确到秒
    TIMESTAMP：精确到小数秒
  4. 其他类型(大对象)
    BLOB: 4GB,二进制
    CLOB: 4GB,字符串

创建表CREATE TABLE table_name(colum_name datatype,...);
修改表：
添加字段ALTER TABLE table_name ADD column_name datatype;
修改字段数据类型ALTER TABLE table_name MODIFY column_name datatype;
删除字段ALTER TABLE table_name DROP COLUMN column_name;
修改字段名ALTER TABLE table_name RENAME COLUMN column_name TO new_name;
修改表名RENAME table_name TO new_name;
截断表（清空数据）TRUNCATE TABLE table_name;
删除表DROP TABLE table_name;
添加数据INSERT INTO table_name(column1,column2,...) VALUES (value1,value2,...);

建表时复制CREATE TABLE table_new AS SELECT * FROM table_old;
添加时复制INSERT INTO ... SELECT ... ;
修改数据UPDATE table_name SET column1='...',column2='...' [where conditions];
删除数据DELETE FROM table_name [where conditions];(TRUNCATE速度更快)

约束

查看约束信息user_constraints数据字典

  1. 非空约束NOT NULL非空约束没有名字使用MODIFYALTER TABLE table_name MODIFY column datatype NOT NULL;
  2. 主键约束PRIMARY KEY,表级约束CONSTRAINT constaint_name PRIMARY KEY(column_name1,...);
    修改表添加主键ALTER TABLE table_name ADD CONSTRAINT constaint_name PRIMARY KEY(column_name1,...);
    更改约束名称ALTER TABLE table_name CONSTRAINT old_name TO new_name;
    禁用/启用约束ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT constaint_name;
    删除约束ALTER TABLE table_name DROP CONSTRAINT constaint_name;删除主键DROP PRIMARY KEY [CASCADE];
  3. 外键约束FOREIGN KEY
    创建表时设置CREATE TABLE table1(column_name datatype REFERENCE table2(column_name),...);table2主表
    表级约束CONSTRAINT constaint_name FOREIGN KEY(column_name) REFERENCES table2(column_name) [ON DELETE CASCADE];主表删除，对应记录也被删除
    修改表时修改外键约束ALTER TABLE table_name ADD CONSTRAINT constaint_name FOREIGN KEY(column_name) REFERENCES table2(column_name) [ON DELETE CASCADE];
    禁用/启用约束，删除约束
  4. 唯一约束UNIQUE
  5. 检查约束CHECK
    CHECK(expressions)如salary number(10,0) check(salary > 0)

查询

语句SELECT [DISTINGCT] column_name,...|* FROM table_name [where conditions];
SQL PLUS设置格式
别名COLUMN(COL) column_name HEADING new_name;
a开头后面指定长度COL column_name FORMAT a10;
显示位数COL column_name FORMAT 999.9;
数字前面多个$符号COL column_name FORMAT $999.9;
清除格式COL column_name CLEAR;
字段设置别名column_name [as] name;

逻辑运算符顺序 not > and > or
比较运算符 > 逻辑运算符
模糊查询_%下划线代表任一字符，%代表任意多字符
范围查询between ... and,in(...),not in()
条件跳转case ... when ... then ... [else ...] end,case when ... then ... [else ...] end
decode函数decode(column_name,value1,result1,value2,result2,...,defaultValue)