OracleOracle 基本的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’);

相关文章