常用数据库操作ITeye - 牛牛娱乐

常用数据库操作ITeye

2019年03月26日10时18分22秒 | 作者: 鹏池 | 标签: 数据库,数据,字段名 | 浏览: 2129

下面按类别列出一些ORACLE用户常用数据字典的查询使用办法。 

    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; 

    2、表 

            检查用户下一切的表 
            SQL select * from user_tables; 

            检查称号包括log字符的表 
            SQL select object_name,object_id from user_objects 
                where instr(object_name,LOG)  

            检查某表的创立时刻 
            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)  

    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); 

 

三、检查数据库的SQL 
1、检查表空间的称号及巨细 

    select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size 
    from dba_tablespaces t, dba_data_files d 
    where t.tablespace_name = d.tablespace_name 
    group by t.tablespace_name; 

2、检查表空间物理文件的称号及巨细 

    select tablespace_name, file_id, file_name, 
    round(bytes/(1024*1024),0) total_space 
    from dba_data_files 
    order by tablespace_name; 

3、检查回滚段称号及巨细 

    select segment_name, tablespace_name, r.status, 
    (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, 
    max_extents, v.curext CurExtent 
    From dba_rollback_segs r, v$rollstat v 
    Where r.segment_id = v.usn(+) 
    order by segment_name ; 

4、检查操控文件 

    select name from v$controlfile; 

5、检查日志文件 

    select member from v$logfile; 

6、检查表空间的使用情况 

    select sum(bytes)/(1024*1024) as free_space,tablespace_name 
    from dba_free_space 
    group by tablespace_name; 

    SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE, 
    (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE" 
    FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C 
    WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 

7、检查数据库库目标 

    select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status; 

8、检查数据库的版别 

    Select version FROM Product_component_version 
    Where SUBSTR(PRODUCT,1,6)=Oracle; 

9、检查数据库的创立日期和归档办法 

    Select Created, Log_Mode, Log_Mode From V$Database; 
四、ORACLE用户衔接的办理 

用体系办理员,检查当时数据库有几个用户衔接: 

SQL select username,sid,serial# from v$session; 

假如要停某个衔接用 

SQL alter system kill session sid,serial#; 

假如这指令不行,找它UNIX的进程数 

SQL select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr; 

阐明:21是某个衔接的sid数 

然后用 kill 指令杀此进程号。 

 

七、ORACLE 常用的SQL语法和数据目标 

一.数据操控句子 (DML) 部分 

1.INSERT  (往数据表里刺进记载的句子) 

INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……); 
INSERT INTO 表名(字段名1, 字段名2, ……)  SELECT (字段名1, 字段名2, ……) FROM 别的的表名; 

字符串类型的字段值有必要用单引号括起来, 例如: ’GOOD DAY’ 
假如字段值里包括单引号’ 需求进行字符串转化, 咱们把它替换成两个单引号. 
字符串类型的字段值超越界说的长度会犯错, 最好在刺进行进行长度校验. 

日期字段的字段值能够用当时数据库的体系时刻SYSDATE, 准确到秒 
或许用字符串转化成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’) 
TO_DATE()还有很多种日期格局, 能够参看ORACLE DOC. 
年-月-日 小时:分钟:秒 的格局YYYY-MM-DD HH24:MI:SS 

INSERT时最大可操作的字符串长度小于等于4000个单字节, 假如要刺进更长的字符串, 请考虑字段用CLOB类型, 
办法借用ORACLE里自带的DBMS_LOB程序包. 

INSERT时假如要用到从1开端主动添加的序列号, 应该先树立一个序列号 
CREATE SEQUENCE 序列号的称号 (最好是表名+序列号符号) INCREMENT BY 1  START  WITH  1 
MAXVALUE  99999  CYCLE  NOCACHE; 
其间最大的值按字段的长度来定, 假如界说的主动添加的序列号 NUMBER(6) , 最大值为999999 
INSERT 句子刺进这个字段值为: 序列号的称号.NEXTVAL 

2.DELETE  (删去数据表里记载的句子) 

DELETE FROM表名 WHERE 条件; 

留意:删去记载并不能开释ORACLE里被占用的数据块表空间. 它只把那些被删去的数据块标成unused. 

假如的确要删去一个大表里的悉数记载, 能够用 TRUNCATE 指令, 它能够开释占用的数据块表空间 
TRUNCATE TABLE 表名; 
此操作不行回退. 

3.UPDATE  (修正数据表里记载的句子) 

UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件; 

假如修正的值N没有赋值或界说时, 将把本来的记载内容清为NULL, 最好在修正行进行非空校验; 
值N超越界说的长度会犯错, 最好在刺进行进行长度校验.. 

留意事项: 
A.        以上SQL句子对表都加上了行级锁, 
        承认完结后, 有必要加上事物处理完毕的指令 COMMIT 才干正式收效, 
        不然改动不一定写入数据库里. 
        假如想撤回这些操作, 能够用指令 ROLLBACK 恢复. 

B.        在运转INSERT, DELETE 和 UPDATE 句子前最好预算一下或许操作的记载规模, 
        应该把它限定在较小 (一万条记载) 规模内,. 不然ORACLE处理这个事物用到很大的回退段. 
        程序呼应慢乃至失掉呼应. 假如记载数上十万以上这些操作, 能够把这些SQL句子分段分次完结, 
        其间加上COMMIT 承认事物处理. 
二.数据界说 (DDL) 部分 

1.CREATE (创立表, 索引, 视图, 近义词, 进程, 函数, 数据库链接等) 

ORACLE常用的字段类型有 
CHAR                        固定长度的字符串 
VARCHAR2                可变长度的字符串 
NUMBER(M,N)                数字型M是位数总长度, N是小数的长度 
DATE                        日期类型 

创立表时要把较小的不为空的字段放在前面, 或许为空的字段放在后边 

创立表时能够用中文的字段名, 但最好仍是用英文的字段名 

创立表时能够给字段加上默许值, 例如 DEFAULT SYSDATE 
这样每次刺进和修正时, 不必程序操作这个字段都能得到动作的时刻 

创立表时能够给字段加上束缚条件 
例如 不允许重复 UNIQUE, 关键字 PRIMARY KEY 

2.ALTER        (改动表, 索引, 视图等) 

改动表的称号 
ALTER TABLE 表名1  TO 表名2; 

在表的后边添加一个字段 
ALTER TABLE表名 ADD 字段名 字段名描绘; 

修正表里字段的界说描绘 
ALTER TABLE表名 MODIFY字段名 字段名描绘; 

给表里的字段加上束缚条件 
ALTER TABLE 表名 ADD CONSTRAINT 束缚名 PRIMARY KEY (字段名); 
ALTER TABLE 表名 ADD CONSTRAINT 束缚名 UNIQUE (字段名); 

把表放在或取出数据库的内存区 
ALTER TABLE 表名 CACHE; 
ALTER TABLE 表名 NOCACHE; 

3.DROP        (删去表, 索引, 视图, 近义词, 进程, 函数, 数据库链接等) 

删去表和它一切的束缚条件 
DROP TABLE 表名 CASCADE CONSTRAINTS; 

4.TRUNCATE (清空表里的一切记载, 保存表的结构) 

TRUNCATE 表名; 

三.查询句子 (SELECT) 部分 

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 条件; 

字段名能够带入函数 
  例如:  COUNT(*), MIN(字段名),  MAX(字段名),  AVG(字段名), DISTINCT(字段名), 
           TO_CHAR(DATE字段名,YYYY-MM-DD HH24:MI:SS) 

NVL(EXPR1, EXPR2)函数 
解说: 
IF EXPR1=NULL 
                RETURN EXPR2 
ELSE 
                       RETURN EXPR1 

DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函数 
解说: 
IF AA=V1 THEN RETURN R1 
IF AA=V2 THEN RETURN R2 
..… 
ELSE 
RETURN NULL 

LPAD(char1,n,char2)函数 
解说: 
字符char1按拟定的位数n显现,缺乏的位数用char2字符串替换左面的空位 

字段名之间能够进行算术运算 
例如:  (字段名1*字段名1)/3 

查询句子能够嵌套 
例如: SELECT …… FROM 
(SELECT …… FROM表名1, [表名2, ……] WHERE 条件) WHERE 条件2; 

两个查询句子的成果能够做调集操作 
例如: 并集UNION(去掉重复记载), 并集UNION ALL(不去掉重复记载), 差集MINUS,  交集INTERSECT 

分组查询 
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] GROUP BY字段名1 
[HAVING 条件] ; 

两个以上表之间的衔接查询 

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 
                表名1.字段名 = 表名2. 字段名 [ AND ……] ; 

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 
                表名1.字段名 = 表名2. 字段名(+) [ AND ……] ; 

有(+)号的字段方位主动补空值 

查询成果集的排序操作, 默许的排序是升序ASC, 降序是DESC 

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] 
ORDER BY字段名1, 字段名2 DESC; 

字符串含糊比较的办法 

INSTR(字段名, ‘字符串’) 0 
字段名 LIKE  ‘字符串%’  [‘%字符串%’] 

每个表都有一个隐含的字段ROWID, 它符号着记载的唯一性. 

四.ORACLE里常用的数据目标 (SCHEMA) 

1.索引 (INDEX) 

CREATE INDEX 索引名ON 表名 ( 字段1, [字段2, ……] ); 
ALTER INDEX 索引名 REBUILD; 

一个表的索引最好不要超越三个 (特别的大表在外), 最好用单字段索引, 结合SQL句子的剖析执行情况, 
也能够树立多字段的组合索引和根据函数的索引 

ORACLE8.1.7字符串能够索引的最大长度为1578 单字节 
ORACLE8.0.6字符串能够索引的最大长度为758 单字节 

2.视图 (VIEW) 

CREATE VIEW 视图名AS SELECT …. FROM …..; 
ALTER VIEW视图名 COMPILE; 

视图仅是一个SQL查询句子, 它能够把表之间杂乱的联系简练化. 

3.近义词 (SYNONMY) 
CREATE SYNONYM近义词名FOR 表名; 
CREATE SYNONYM近义词名FOR 表名@数据库链接名; 

4.数据库链接 (DATABASE LINK) 
CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 暗码 USING ‘数据库衔接字符串’; 

数据库衔接字符串能够用NET8 EASY CONFIG或许直接修正TNSNAMES.ORA里界说. 

数据库参数global_name=true时要求数据库链接称号跟远端数据库称号相同 

数据库大局称号能够用以下指令查出 
SELECT * FROM GLOBAL_NAME; 

查询远端数据库里的表 
SELECT …… FROM 表名@数据库链接名; 

五.权限办理 (DCL) 句子 

1.GRANT        赋于权限 
常用的体系权限调集有以下三个: 
CONNECT(根本的衔接), RESOURCE(程序开发), DBA(数据库办理) 
常用的数据目标权限有以下五个: 
ALL         ON 数据目标名,         SELECT ON 数据目标名,         UPDATE ON 数据目标名, 
DELETE         ON 数据目标名,  INSERT ON 数据目标名,   ALTER  ON 数据目标名 

GRANT CONNECT, RESOURCE TO 用户名; 
GRANT SELECT ON 表名 TO 用户名; 
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2; 

2.REVOKE 收回权限 

REVOKE CONNECT, RESOURCE FROM 用户名; 
REVOKE SELECT ON 表名 FROM 用户名; 
REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2; 


查询数据库中第63号过错: 
select orgaddr,destaddr from sm_histable0116 where error_code=63; 

查询数据库中开户用户最大提交和最大下发数: select MSISDN,TCOS,OCOS from ms_usertable; 


查询数据库中各种过错代码的总和: 
select error_code,count(*) from sm_histable0513 group by error_code order 
by error_code; 

查询报表数据库中话单计算品种查询。 
select sum(Successcount) from tbl_MiddleMt0411 where ServiceType2=111 
select sum(successcount),servicetype from tbl_middlemt0411 group by servicetype 

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表牛牛娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    常用数据库操作ITeye

    数据库,数据,字段名
  • 2

    UDP 单播、播送和多播ITeye

    多播,播送,单播
  • 3
  • 4

    一个简略的切开shellITeye

    一个,文件,体系
  • 5

    Mac/Linux 装备环境变量ITeye

    这个,文件,增加
  • 6

    rabbit及redis 装置ITeye

    装置,文件,进入
  • 7
  • 8
  • 9

    netstat指令详解ITeye

    显现,数据,衔接
  • 10

    centos6.5 装备JavaWeb环境ITeye

    装置,这儿,是从