Oracle 基本的SQL 操作

经客户端连接数据库,进行相应的操作

同、访问方式:SQL Plus ; PL Sql
Developer

 以system帐户连接数据库,链接标识符是:orcl

  1. 透过SQL Plus 连接访问数据库
       两栽方式:web访问
    http://localhost:5560/isqlplus/
       通过菜单直接进去命令执行窗口
    2.打开PL/SQL
    Developer,FILE->new–>SQL Window

第二、新建对象

   创建用户以及表如一个用户称也school
密码吗school的用户帐户:
   Create user school
   Identified by school;
 
也school用户分配:连接至数据库的权力、在好的模式遭遇创造目标的权。               

   Grant create session,resource

   to school;
    创建表
    Oracle常用之字段类型有
  CHAR 固定长度的字符串
  VARCHAR2 可转换长的字符串
  NUMBER(M,N) 数字型M是各项数总长度,
N是小数的长度
  DATE 日期类型
   示例:
   CREATE TABLE “SCHOOL”.”STUDENT” (
“SNO” VARCHAR2(9), “SNAME” VARCHAR2(20), “SSEX” VARCHAR2(2),    “SAGE”
NUMBER, “SDEPT” VARCHAR2(20),   PRIMARY KEY (“SNO”) VALIDATE )
   CREATE TABLE “SCHOOL”.”COURSE” ( “CNO”
VARCHAR2(4), “CNAME” VARCHAR2(40), “CPNO” VARCHAR2(4), “CCREDIT” NUMBER,
PRIMARY KEY (“CNO”) VALIDATE , FOREIGN KEY (“CPNO”) REFERENCES
“SCHOOL”.”COURSE” (“CNO”) VALIDATE )
   CREATE TABLE “SCHOOL”.”SC” ( “SNO”
VARCHAR2(9), “CNO” VARCHAR2(4), “GRADE” NUMBER, PRIMARY KEY (“SNO”,
“CNO”) VALIDATE , FOREIGN KEY (“SNO”) REFERENCES “SCHOOL”.”STUDENT”
(“SNO”) VALIDATE , FOREIGN KEY (“CNO”) REFERENCES “SCHOOL”.”COURSE”
(“CNO”) VALIDATE )
  约束状态
   enable
validate:是默认,新老数据而满足约束规则
   enable
novalidate:旧数据好无饱,检新插入的数额而满足约束
   disable
validate:不允以表上执行另外DML操作,主要用当分区表,对于主键和唯一约从,会删除相应的唯一索引,但约状态任可用
   disable
novalidate数据可满足约束规则,对于主键和唯一约从,会删除相应的唯一索引,

老三、 查询相关命令,以下是于SQL
Plus中尽示例
  1)用户
  查看时用户之缺省表空间
   SQL>select
username,default_tablespace from user_users;
  查看时用户之角色
  SQL>select * from
user_role_privs;
  查看时用户之网权限和表级权限
  SQL>select * from
user_sys_privs;
  SQL>select * from
user_tab_privs;
  显示当前会晤讲话所拥有的权能
  SQL>select * from
session_privs;
  显示指定用户所具有的系统权限
  SQL>select * from dba_sys_privs
where grantee=’GAME’;
  2)表
  查看用户下有所的阐明
  SQL>select * from
user_tables;
  查看名称包含log字符的发明
  SQL>select object_name,object_id
from user_objects
  where
instr(object_name,’LOG’)>0;
  查看某表的创时间
  SQL>select object_name,created
from user_objects where object_name=upper(‘&table_name’);
  查看某表的轻重缓急
  SQL>select sum(bytes)/(1024*1024)
as “size(M)” from user_segments
  where
segment_name=upper(‘&table_name’);
  查看在ORACLE的内存区里的阐明
  SQL>select table_name,cache from
user_tables where instr(cache,’Y’)>0;
  3)索引
  查看索引个数和类型
  SQL>select
index_name,index_type,table_name from user_indexes order by
table_name;
  查看索引被索引的字段
  SQL>select * from
user_ind_columns where index_name=upper(‘&index_name’);
  查看索引的大小
  SQL>select sum(bytes)/(1024*1024)
as “size(M)” from user_segments
  where
segment_name=upper(‘&index_name’);
  4)序列号
  查看序列号,last_number是现阶段价值
  SQL>select * from
user_sequences;
  5)视图
  查看视图的名
  SQL>select view_name from
user_views;
  查看创建视图的select语句
  SQL>set view_name,text_length
from user_views;
  SQL>set long 2000;
说明:可以根据视图的text_length值设定set long 的大小
  SQL>select text from user_views
where view_name=upper(‘&view_name’);
  6)同义词
  查看同义词的称呼
  SQL>select * from
user_synonyms;
  7)约束原则
  查看某表的牢笼原则
  SQL>select constraint_name,
constraint_type,search_condition, r_constraint_name
  from user_constraints where
table_name = upper(‘&table_name’);
  SQL>select
c.constraint_name,c.constraint_type,cc.column_name
  from user_constraints
c,user_cons_columns cc
  where c.owner =
upper(‘&table_owner’) and c.table_name =
upper(‘&table_name’)
  and c.owner = cc.owner and
c.constraint_name = cc.constraint_name
  order by cc.position;
  8)存储函数和经过
  查看函数和过程的状态
  SQL>select object_name,status
from user_objects where object_type=’FUNCTION’;
  SQL>select object_name,status
from user_objects where object_type=’PROCEDURE’;
  查看函数和经过的源代码
  SQL>select text from all_source where
owner=user and name=upper(‘&plsql_name’);

相关文章