oracle 基础命令整理

/*

本页面只整理部分基础的 常用命令 以备不时之需

*/

1. 登陆

sqlplus /nolog -- 不登陆

sqlplus username/password@orcl

sqlplus / as sysdba -- 系统管理员登陆

select user from dual; -- 查看当前用户 或者 直接 show user 

conn / as sysdba -- 切换用户

2. 用户管理

create user username identified by password; --新建用户（默认建在SYSTEM表空间下） 

grant connect,resource to username;  -- 赋权限

drop user username cascade;-- 删除用户

alter user username identified by newPass;--修改密码

3.  表操作

----------------------------------------------------- 表 ----------------------------------------
create table 表名(字段名 类型, 字段名 类型, 约束);  -- 创建表
drop table 表名 cascade constraints;  -- 删除表
truncate table student;             -- 清空表

rename students to student -- 重命名
alter table student rename to students; -- 重命名

-- 修改字段
alter table student add studentname varchar2(32)
alter table student modify studentname varchar2(52);
alter table student drop column studentname;

-- 添加备注
comment on table students is '学生表';
comment on column students.age is '学生年龄';

----------------------------------------------------- 约束 ----------------------------------------
-- 创建不命名
create table student (
studentid int primary key not null,         -- 主键
studentname varchar(8),
class varchar(32) references class(id),     -- 外键
age int);

-- 创建并命名
create table student(
studentid int ,
studentname varchar(8),
age int,
class varchar(32),
constraint yy primary key(studentid),                    -- 主键
constraint cc foreign key(class) references class(id)    -- 外键
);

-- 创建并命名2
create table student (
studentid int constraint yy primary key,    -- 主键
studentname varchar(8),
class varchar(32), 
age int);  

-- 创建后添加
alter table student add constraint pk_student primary key(studentid);
alter table student add constraint cc foreign key(class) references class(id); 
alter table students drop constraint yy;

----------------------------------------------------- 索引 ----------------------------------------
create index stu_index on student (studentid, studentname);
drop index stu_index;
----------------------------------------------------- 视图 ----------------------------------------
create or replace view view_stu_class(classname, studentname) as 
select t2.class_name,t1.studentname from student t1,class t2 where t1.class=t2.id ;

drop view view_stu_class;
----------------------------------------------------- 同义词 ----------------------------------------
create [public] synonym syn_stu for student; 
drop [public] synonym syn_stu;
----------------------------------------------------- 数据库链接 ----------------------------------------
create [public] database link 数据库链接名 connect to 用户名 identified by 密码 using ‘数据库连接字符串’;
drop [public] database link 数据库链接名;