`
liuxinglanyue
  • 浏览: 564974 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

oracle基本操作语句

阅读更多

 

 
基本的查询操作语句 
1.在数据字典查询约束的相关信息: 
SELECT constraint_name, constraint_type,search_condition 
FROM    user_constraints WHERE    table_name = 'EMPLOYEES'; 
        //这里的表名都是大写! 
2对表结构进行说明: 
    desc Tablename 
3查看用户下面有哪些表 
    select table_name from user_tables; 
4查看约束在那个列上建立: 
    SELECT constraint_name, column_name 
    FROM     user_cons_columns 
    WHERE     table_name = 'EMPLOYEES'; 
5结合变量查找相关某个表中约束的相关列名: 
   select constraint_name,column_name from user_cons_columns where table_name = '&tablename' 
6查询数据字典看中间的元素: 
SELECT    object_name, object_type 
FROM      user_objects 
WHERE     object_name LIKE 'EMP%'      
OR        object_name LIKE 'DEPT%' 
7查询对象类型: 
SELECT DISTINCT object_type FROM        user_objects ; 
8改变对象名:(表名,视图,序列) 
   rename   emp to emp_newTable 
9查看视图结构: 
    describe view_name 
10在数据字典中查看视图信息: 
   select viewe_name,text from user_views 
11查看数据字典中的序列: 
   select * from user_sequences 
12得到所有的时区名字信息: 
        select   * from v$timezone_names 
13显示对时区‘US/Eastern’的时区偏移量 
        select TZ_OFFSET('US/Eastern') from DUAL--dual英文意思是‘双重的’ 
    显示当前会话时区中的当前日期和时间: 
    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';--修改显示时间的方式的设置 
    ALTER SESSION SET TIME_ZONE = '-5:0';--修改时区 
    SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;--真正有用的语句! 
   SELECT CURRENT_TIMESTAMP FROM DUAL;--返回的时间是当前日期和时间,含有时区 
   SELECT CURRENT_TIMESTAMP FROM DUAL;--返回的时间是当前日期和时间,不含有时区!!!   
14显示数据库时区和会话时区的值: 
        select datimezone,sessiontimezone from dual; 

基本的数据操作语句 
1普通的建表语句: 
CREATE TABLE dept 
(deptno         NUMBER(2), 
dname    VARCHAR2(14), 
loc VARCHAR2(13)); 
2使用子查询建立表: 
CREATE TABLE      dept80 
   AS   SELECT   employee_id, last_name, 
             salary*12 ANNSAL, 
             hire_date    FROM     employees    WHERE    department_id = 80; 
3添加列:// alter table EMP add column (dept_id number(7));错误!! 
   alter table EMP add (dept_id number(7)); 
4删除一列: 
   alter table emp drop column dept_id; 
5添加列名同时和约束: 
alter table EMP add (dept_id number(7) 
    constraint my_emp_dept_id_fk   references dept(ID)); 
6添加一个有check约束的新列: 
   alter table EMP 
   add (COMMISSION number(2) constraint emp_commission_ck check(commission>0)) 
7改变列://注意约束不能够修改 的!! 
alter table dept80 modify(last_name varchar2(30));//这里使用的是modify而不是alter! 
8增加一行: 
   insert into table_name values(.。。。。。。。); 
9添加主键: 
alter Table EMP   add constraint my_emp_id_pk primary key (ID); 
10删除表: 
    drop table emp; 
11创建视图: 
    CREATE VIEW    empvu80 
AS SELECT   employee_id, last_name, salary 
     FROM     employees      WHERE    department_id = 80; 
12删除视图: 
    drop view view_name 
高级的数据对象:序列,同义词,索引等等 
1建立同义词: 
   create synonym 同义词名 for 原来的名字 
或者   create public synonym 同义词名 for 原来的名字
   
 
   
2 oracle基本操作语句
 
2建立序列:(注意,这里并没有出现说是哪个表里面的序列!!) 
   CREATE SEQUENCE dept_deptid_seq 
                 INCREMENT BY 10 
                 START WITH 120 
                 MAXVALUE 9999 
                 NOCACHE 
                 NOCYCLE   
3使用序列:(使用了伪列:nextval) 
        insert into dept(ID,NAME) values(DEPT_ID_SEQ.nextval,'Administration'); 
4查找序列的相关信息: 
        SELECT        sequence_name, min_value, max_value, increment_by, last_number 
FROM    user_sequences; 
5.查找伪列currval:(记住不能够查找nextval的,否则就变化了,调用的话就自增!!) 
        SELECT        dept_deptid_seq.CURRVAL FROM        dual; 
6修改序列: 
        ALTER SEQUENCE dept_deptid_seq 
                INCREMENT BY 20 
                MAXVALUE 999999 
                NOCACHE 
                NOCYCLE; 
        删除序列:DROP SEQUENCE dept_deptid_seq; 
7建立索引://默认就是nonunique索引,除非使用了关键字:unique 
        CREATE INDEX emp_last_name_idx ON employees(last_name); 
8确定索引状态:使用数据字典USER_INDEXES和数据视图USER_IND_COLUMNS 
        SELECT        ic.index_name, ic.column_name,        ic.column_position col_pos,ix.uniqueness 
FROM    user_indexes ix, user_ind_columns ic WHERE        ic.index_name = ix.index_name 
AND       ic.table_name = 'EMPLOYEES'; 
9.函数的索引 
        CREATE INDEX upper_dept_name_idx ON departments(UPPER(department_name)); 
        //上面的就建立了一个关于函数departments 表中的UPPER(department_name)函数的索引! 
SELECT * FROM    departments WHERE   UPPER(department_name) = 'SALES'; 
10删除同义词: 
        DROP SYNONYM d_sum; 
关于数据库连接的建立 
1建立用户:(可能有错,详细查看帮助) 
        create user   nuesoft(用户名) 
        identified by oracle(密码) 
        default tablespace   data01(表空间名//默认存在system表空间里面) 
        quota 10M(设置大小,最大为unlimited)   on 表空间名//必须分配配额! 
2创建角色:create ROLE manager 
     赋予角色权限:grant create table,create view to manage 
     赋予用户角色:grant manager to DENHAAN,KOCHHAR( 两个用户) 
3分配权限: 
        GRANT   update (department_name, location_id) 
        ON      departments 
        TO      scott, manager; 
4. 回收权限 
        REVOKE   select, insert 
        ON       departments 
        FROM     scott; 
5.总结建立远程数据库连接的过程: 
        TNSNAMES本地NET服务名:要知道服务器的hostname,service name,port(默认是1521). 
        首先关掉作为服务器上面的防火墙; 
        进入Net Configuration Assistant-》本地Net服务器配置-》添加—》下一步-》服务器名写'orcl'(根据不同的服务器不同,看服务里面的'OracleService___项目!') 
-》选择TCP-》主机名为服务器的IP或机器名,端口默认1521-》进行测试(假定登录的在服务器上的用户名为USER,密码为PWD)-》填写Net服务器名,为一个与自己机器本地服务器名不同的名字(假定为ORACLTEMP)-》不配置另外一个NET服务器--》完成。 
        进入Dos。进入Oracle:sqlplus/nolog   ;使用服务器上的帐号登录:connect   USER/PWD@ORACLTEMP ;验证是否已经连接成功:输入语句 
select * from v$instance;(注意语句以句号结束!!) ;可以输入语句在服务器上面访问。 
        在本机上访问服务器,首先要在本地进行数据库连接:DOS界面进行数据库连接注册:create database link lsq_link(需要的连接的名字) using 'ORACLTEMP'(前面设置的在服务器上面可以使用的用户名);连接成功,可以输入查询语句:select * from tablename@lsq_link;
   
 
   
3 oracle基本操作语句
 

时间的函数,包括高级的时间类型 
1一般的时间函数: 
        MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')--两个日期之间的月数,返回一个浮点数 
        ADD_MONTHS ('11-JAN-94',6)--添加月数 
      NEXT_DAY ('01-SEP-95','FRIDAY') --下一个星期五的日期 
        LAST_DAY('01-FEB-95')--当月的最后一天! 
        ROUND(SYSDATE,'MONTH')          --四舍五入月 
        ROUND(SYSDATE ,'YEAR')        --四舍五入年 
        TRUNC(SYSDATE ,'MONTH')         --阶段月 
         TRUNC(SYSDATE ,'YEAR')         --截断年   
2从时间中提取年,月,日:使用函数extract 
        select extract(year from sysdate) year,extract(month from sysdate), 
extract(day from sysdate) from dual; 
3使用函数得到数月之后的日期:to_yminterval(‘01-02’)表示加上1年2月,不能够到天!! 
        select hire_date,hire_date +to_yminterval('01-02') as hire_date_new from employees where department_id=20 
     得到多少天之后的日期:直接日期加数字! 
        select hire_date +3 from employees where department_id=20 
关于子查询和其他的编程思路 
1group语句:和高级的应用语句: 
        SELECT    department_id, job_id, SUM(salary),   COUNT(employee_id) FROM      employees 
               GROUP BY department_id, job_id ; 
        可以使用having进行约束: 
        I.group by rollup:对n列组合得到n+1种情况 
        SELECT    department_id, job_id, SUM(salary)   FROM      employees   WHERE     department_id < 60 GROUP BY ROLLUP(department_id, job_id); 
        II.group by cube:得到2的n次方种情况 
        SELECT    department_id, job_id, SUM(salary) FROM      employees   WHERE     department_id < 60 GROUP BY CUBE (department_id, job_id) ; 
        III.使用grouping得到一行中构成列的情况,只是返回1和0:是空的话就返回1,否则返回0(注意不要弄反了!) 
        SELECT    department_id DEPTID, job_id JOB,   SUM(salary),     GROUPING(department_id) GRP_DEPT,     GROUPING(job_id) GRP_JOB 
               FROM      employees WHERE     department_id < 50 GROUP BY ROLLUP(department_id, job_id); 
        IV.grouping sets:根据需要得到制定的组合情况 
        SELECT    department_id, job_id, manager_id,avg(salary) FROM      employees GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id)); 
2from中使用子查询:返回每个部门中大于改部门平均工资的与员工信息 
        SELECT   a.last_name, a.salary, a.department_id, b.salavg   FROM     employees a,--下面的地方就是子查询了,主要返回的是一组数据! 
         (SELECT    department_id, AVG(salary) salavg    FROM      employees   GROUP BY department_id) b 
        WHERE    a.department_id = b.department_id 
        AND      a.salary > b.salavg; 
3exists语句的使用: 
        SELECT employee_id, last_name, job_id, department_id 
        FROM    employees outer--下面的 exists里面的select选择出来的是随便的一个字符或者数字都可以 
        WHERE   EXISTS ( SELECT 'X'    FROM    employees WHERE   manager_id =   outer.employee_id); 
4厉害的with语句: 
        WITH 
         dept_costs   AS (--定义了一个临时的表 
                  SELECT   d.department_name, SUM(e.salary) AS dept_total--其间定义了一个临时的列dept_total 
                  FROM     employees e, departments d 
                  WHERE    e.department_id = d.department_id 
                  GROUP BY d.department_name),/*注意这里有逗号*/ 
        avg_cost     AS ( 
           SELECT SUM(dept_total)/COUNT(*) AS dept_avg 
           FROM    dept_costs)--这里的第二张临时表里面就引用了前面定义的临时表和之间的列! 
        SELECT *   FROM    dept_costs   WHERE   dept_total >   (SELECT dept_avg    FROM avg_cost) ORDER BY department_name;---最后的查询语句中使用了前面的临时表 
5遍历树: 
        SELECT employee_id, last_name, job_id, manager_id 
        FROM    employees 
        START   WITH   employee_id = 101 
        CONNECT BY PRIOR manager_id = employee_id ;--自底向上的遍历树。 
6top-n分析:找到工资最高的5个人。(行内视图) 
select rownum,employee_id from (select employee_id,salary from 
employees order by salary desc) 
   where rownum<5; 

分享到:
评论

相关推荐

    Oracle导出INSERT语句

    INSERT语句是SQL语言的基本组成部分,用于向数据库表中插入新的数据记录。通过生成这些语句,我们可以方便地在目标环境中重新创建源数据库中的数据状态,而无需进行完整数据库的导入导出操作,这对于测试环境或数据...

    oracle 数据库操作语句大全

    oracle数据库详细的语句操作大全,对不同的操作语句都有详细的解释和举例说明。

    Oracle_基本建表语句-操作语句

    在Oracle数据库管理系统中,建表语句是进行数据存储和管理的基础操作。Oracle支持SQL标准的CREATE TABLE语句,用于创建新的数据库表。本篇将深入探讨Oracle中的基本建表语句及其相关操作。 首先,建表语句的基本...

    Oracle基本sql语句

    本文将总结一些Oracle基本SQL语句,帮助用户快速复习并掌握其用法。 首先,了解SQL(结构化查询语言)的基础功能是十分必要的。SQL由一系列命令组成,这些命令允许用户与数据库进行交流。其中,SELECT语句是SQL中最...

    oracle常用SQL语句(汇总版).docx

    Oracle 是一个功能强大且复杂的关系数据库管理系统,它提供了多种 SQL 语句来管理和操作数据库。在本文中,我们将详细介绍 Oracle 中常用的 SQL 语句,包括数据控制语句(DML)、数据定义语句(DDL)和其他相关知识...

    Oracle导出INSERT语句V1.1

    在日常数据库管理和开发过程中,有时我们需要将Oracle数据库中的数据导出为INSERT语句,以便在其他环境或者备份恢复时使用。"Oracle导出INSERT语句V1.1"是一个专门为此目的设计的实用工具。 这个工具的主要功能是...

    Oracle数据库sql语句的基本操作详解

    本文将深入解析Oracle SQL语句的基本操作,帮助初学者更好地理解和应用这些技巧,从而提高数据库查询效率。 首先,理解选择最有效率的表名顺序至关重要。在FROM子句中,Oracle会按照从右到左的顺序处理表,这意味着...

    ORACLE常用SQL语句大全.pdf

    本文档总结了 Oracle 中常用的 SQL 语句,包括数据库的创建、删除、备份、表的创建、删除、修改、索引的创建、视图的创建等基本操作,以及一些高级查询运算符的使用。 一、数据库操作 * 创建数据库:CREATE ...

    oracle数据库常用操作语句

    以下是一些Oracle数据库的常用操作语句及其详细解释: 1. **查看帮助信息**: - `help show`:显示特定命令的帮助信息。 - `show all`:列出所有系统变量的当前值。 2. **查看系统状态**: - `show user`:显示...

    oracle基础的语句教程

    本教程将带你深入了解Oracle的基础知识,包括基本SQL语句、内连接、外连接以及多表查询,还有如何创建数据库表。 一、Oracle基础SQL语句 SQL(Structured Query Language)是用于管理和操作数据库的语言。在Oracle...

    Oracle查询语句大全-精华版

    查询当前用户下的所有表是使用 Oracle 的一个基本操作,使用以下语句可以查询当前用户下的所有表: select * from tab; 其中,tab 是 Oracle 中的一个系统视图,用于显示当前用户下的所有表和视图。 七、显示当前...

    oracle的SQL语句的一些经验总结

    1. **基本查询**:SQL的基础是SELECT语句,用于从表中提取数据。例如,`SELECT column1, column2 FROM table_name;`用于获取指定列的数据。理解如何使用WHERE子句进行条件过滤,如`WHERE column = value`,以及如何...

    excel2003 生成oracle 建表语句

    标签"excel2003"表明了工具的基础平台,"oracle"表示目标数据库系统,"sql"则强调了涉及的数据库操作语言。文件"ORACLE生成sql工具.xls"很可能就是包含宏的Excel工作簿,用户需要打开并按照指示操作。 这样的工具...

    oracle监听执行sql语句

    #### 二、监听执行SQL语句的基本方法 1. **使用`v$session`和`v$sqlarea`视图** ```sql SELECT a.username, a.sid, b.SQL_TEXT, b.SQL_FULLTEXT FROM v$session a, v$sqlarea b WHERE a.sql_address = b....

    mysql和oracle和sql语句

    例如,MySQL的DML(Data Manipulation Language)语句,如`INSERT`、`UPDATE`和`DELETE`,与标准SQL基本一致,但其DDL(Data Definition Language)如`CREATE TABLE`在某些特性上可能与Oracle或SQL Server有所不同。...

    oracle数据库查询语句大全

    此外,文章还介绍了基本的Oracle操作语句,包括创建表、修改列属性、添加列、添加主键约束和非空约束、删除主键约束、失效约束、有效约束等。这些语句对于初学者非常重要,因为它们可以帮助初学者快速地掌握Oracle...

    Oracle_基本建表语句

    本文将详细介绍Oracle的基本建表语句及相关操作。 首先,创建用户是数据库管理的基础。Oracle中创建用户的基本语法如下: ```sql CREATE USER 用户名 IDENTIFIED BY 密码 DEFAULT TABLESPACE 表空间名 TEMPORARY ...

    oracle_plsql语句大全

    以上内容覆盖了Oracle PL/SQL的基本语法、SQL*PLUS命令、SQL查询、条件表达式、算术运算、日期和时间处理以及DML语句等方面。对于想要深入了解Oracle PL/SQL的开发者来说,这些知识点是非常宝贵的资源。

    ORACLE_SQL语句

    根据给定的文件信息,以下是对Oracle SQL语句的关键知识点的详细解读: ### Oracle SQL语句概述...以上就是Oracle SQL语句的一些基础知识点,通过理解和掌握这些概念,你可以更有效地查询和操作Oracle数据库中的数据。

Global site tag (gtag.js) - Google Analytics