oracle基本操作 oracle常用命令

1,select [distinct] [*,column_name[alias],……]
   
    from tablename
   
    where
   
    group by
   
    having
   
    order by
   
    其中，各个参数的意义分别如下：
   
    Select:指定一个或多个列名，或者使用*号
   
    Distinct:删除副本，即只允许结果集中出现不同的值
   
    *:检索所有列的符号
   
    Where:指定所检索的数据应该满足的条件
   
    Group by;执行分组检索操作
   
    Having:指定分组检索的条件
   
    Order by:指定排序的条件
   
    注意：在使用select语句时，对于数字数据和日期数据都可以使用算术表达式：
   
    Select name,job,comm,comm*2+50 from emp;
   
    2,使用desc命令可查询表的结构
   
    Desc emp;
   
    3,使用where子句
   
    Where expression operator value
   
    后面可跟运算符
   
    普通比较运算符包括：=,>,<,>=,<=,!=
   
    特殊比较运算符
   
    Between……and……
   
    检索在两个值之间的内容（包含两端的值）
   
    In
   
    检索匹配列表中任意一个值的数据
   
    like
   
    检索匹配字符样式的数据
   
    Is null
   
    检索空数据
   
    逻辑运算符包括：and,or,not
   
    注意：like运算符用来指定模糊匹配，在使用like运算符时，可以使用两个符号：%和_
   
    如果希望得到的字符数量不确定用%
   
    如果只是希望在模糊条件中表示一个字符，则用_
   
    4, 使用order by子句
   
    语法格式如下：
   
    Order by [column_name,expression] [asc|desc]
   
    参数含义：
   
    Expression:将要排列顺序的表达式
   
    Asc:按照升序排列，默认
   
    Desc:降序
   
    注意：可以使用数字序号（即列号）代替列名称
   
    ,5,合计计算
   
    avg
   
    平均
   
    count
   
    总行数
   
    max
   
    最大值
   
    min
   
    最小值
   
    stddev
   
    标准偏差
   
    sum
   
    总和
   
    variance
   
    方差
   
    例子：select sum（sal），avg（sal），sum（sal*12） from emp;
   
    注意：使用合计函数时，输入变量既可以是指定的列名，也可以是指定的表达式。
   
    ,6,使用数学函数（部分）
   
    Abs（x）
   
    绝对值
   
    Acos（x）
   
    反余弦
   
    Asin（x）
   
    反正弦
   
    Cos（x）
   
    余弦
   
    Ceil（x）
   
    返回大于或等于x的最小整数
   
    Exp（x）
   
    返回e的x次幂
   
    Floor（x）
   
    返回小于或等于x的最大整数
   
    Power（x,y）
   
    返回x的y次幂
   
    Sign（x）
   
    符号函数，如果x是负数，则返回-1,如果x是正数，则返回1,如果是0,则返回0
   
    例句：select exp（1），power（2.71,10） from dual;

7, 使用group by和having子句
   
    语法格式如下：
   
    Group by group_by_expression having group_condition
   
    参数含义：
   
    Group_by_expression:指定将要分组的列
   
    Having:指定分组的条件
   
    注意：如果需要限制分组中的数据，则可以使用having子句而不能使用where子句
   
    例子：select deptno,avg（sal） from emp where avg（sal）>=2000 group by deptno; （错误）
   
    Select deptno,avg（sal） from emp group by deptno having avg（sal）>=2000; （正确）
   
    8,连接查询
   
    语法格式：select table1.column,table2.column from table1,table2 where table1.column=table2.column;
   
    例句：select worker.ename||'的经理是'||manager.ename from emp worker,empmanager where worker.mgr=manager.empno;
   
    9, 集合运算符
   
    Union 集合运算符
   
    Union all 同上，但不消除查询结果中重复的行
   
    Intersect 集合运算符，用来执行交集运算
   
    Minus 用来执行差集运算
   
    例子：select empno,ename from emp where ename like 'A%' or name like 'B%' union select empno ,ename from emp where ename like 'A%' or name like 'C%'
   
    10,修改数据
   
    插入数据语法格式：
   
    Insert into table（column[,column……]） values （value[,value……]）；
   
    更新数据语法格式：
   
    Update table set column=value[,column=value] where condition;
   
    删除数据语法格式：
   
    Delete from table where condition;
   
    需要注意的是，但更新表中数据时，不能破坏表的完整性约束。如果更新后的数据与表的完整性约束有冲突，那么这种更新操作将会失败。
   
    11,子查询
   
    语法格式：
   
    Select select_list
   
    From table
   
    Where expression operator（select select_list from table）
   
    上面的语句中，表示运算符的operator既可以是单行运算符，例如>,=,>= 等，也可以是多行运算符，例如：in,any,all等。
   
    多行比较运算符
   
    运算符
   
    描述
   
    In
   
    等于列表中的任何一个值
   
    Any
   
    与子查询返回的每一个值进行比较
   
    All
   
    与子查询返回的所有值进行比较
   
    Any与all需要与单行运算符结合起来使用。
   
    例句：select ename ,deptno,job from emp where sal in（select max（sal） from emp group by deptno）；

管理表
   
    1,主键约束
   
    表的主键可以确保在一个表中没有重复行，防止冗余的信息。
   
    添加主键：
   
    Alter table another_dept add constraint another_dept_pk primary key（deptno）；
   
    注意：在创建主键约束时，一定要遵循下面的原则：
   
    （1）一个表中最多只能有一个主键约束
   
    （2）表中不能有任何两行在主键约束列上既有相同的值
   
    （3）主键列不能为空
   
    2,外键约束
   
    如果插入子表的行在外键列中具有非null值，那么父表行中必须在所引用的列中有相同的值。
   
    语法格式如下：
   
    Alter table child_table
   
    Add constraint foreign_key_name
   
    Foreign key（child_table_column）
   
    References parent_table（parent_table_column）；
   
    3,唯一性约束
   
    唯一性约束可以确保表中的各行，对于值为非null的给定列或列组都具有唯一值。
   
    语法格式如下：
   
    Alter table table_name
   
    Add constraint unique_key_name
   
    Unique（column_name）；
   
    4,检查约束
   
    检查约束就是指定表中某个列的取值范围。对于即将插入表中的数据来说，如果插入的数据满足检查约束指定的条件，则插入操作成功，否则插入操作失败。
   
    例句：alter table students
   
    Add constraint ck_gender
   
    Check（gender in（'female','male'））；
   
    5, 索引
   
    语法格式：
   
    Create index idx_emp_name
   
    On tableName（column_name）；
   
    如果oracle有能力在一次扫描中读取多个数据块，那么他就会将考虑使用索引的阀值设置的相当高。如果oracle认为用户的查询将要选取记录的2%-5%或更多，那么他就会执行全表搜索，而不考虑索引是否可用。
   
    为了防止数据块分割，在创建索引时可以设置pctfree属性
   
    例句：
   
    Create index idx on tableName（column_name） pctfree 0;
   
    修改：
   
    Alter index idx
   
    Rebuild pctfree 10;
   
    6, 索引和约束
   
    当某一列有唯一性约束和主键约束时，无论用户是否喜欢，将会自动为这一列创建一个索引。
   
    首先创建表，没有任何约束
   
    接着创建索引（假设表为inventory）
   
    Create indexs idx
   
    On inventory（partno,warehouse）
   
    Pctfree 10;
   
    接下来向表中增加以下约束：
   
    Alter table inventory add（
   
    Constraint pk primary key（partno）
   
    Using index idx）；
   
    这时，oracle将会检查它是否可以使用已经存在的索引，如果索引能够满足它，它就会使用这个索引。
   
    7, 视图
   
    例句：create view viewName（name）
   
    As
   
    Select name from emp;
   
    查询视图：select * from viewName order by name;
   
    注意：create or replace
   
    删除视图：
   
    Drop view viewName;
   
    8, 过程
   
    使用过程代替PL/SQL程序块有许多好处，例如，增强可扩性，提高模块化水平，可重用性增强，可维护性加强，有利于抽象和数据的隐藏，增强安全性等。
   
    例句：create or replace procedure
   
    Insert_into_t（p_parm in number）
   
    Is
   
    Begin
   
    Insert into t values（p_parm）；
   
    End insert_into_t;
   
    执行过程：
   
    Exec insert_into_t（p_parm=>100）；
   
    参数传递：
   
    名称表示法：parm_name=>value
   
    位置表示法：跟java语言传递参数一样
   
    局域声明：
   
    Declare
   
    Num1 number:=111;
   
    9, 触发器
   
    触发器是一种特殊的过程，但是用户不能直接调用触发器，触发器是当特定事件出现时自动执行的代码块。
   
    触发器示例：
   
    Create trigger biufer_emp_empno
   
    Before insert or update
   
    Of empno
   
    On emp
   
    Referencing old as old_value
   
    New as new_value
   
    For each row
   
    When（new_value.empno<>7782）
   
    Begin
   
    :new _value.comm:=0;
   
    End;
   
    解析：before insert or update
   
    Of empno
   
    On emp
   
    对emp表进行insert操作时
   
    对emp的表的empno进行update操作时
   
    When（new_value.empno<>7782）
   
    这就是说，如果列的新值不等于7782时，触发器就会执行。任何布尔表达式都可以使用when子句

 

1, instead of 触发器
   
    例句：create trigger update_emp_job_view
   
    Instead of update
   
    On emp_job
   
    Begin
   
    Update emp
   
    Set empno=:new.empno,
   
    Ename=:new.ename,
   
    Job=:new.job
   
    Where empno=:old.empno;
   
    End;
   
    在该示例中可以看出，在触发器主体中有一个对表emp的update关键字。由于emp_job视图是建立在emp表上的视图。所以，如果更新这个视图时，实际上就是更新该视图的基本表。
   
    2, 系统时间触发器
   
    Oracle系统还允许开发人员建立与特定系统事件相关的触发器。这些特定事件如下：
   
    （1） 数据库启动
   
    （2） 数据库关闭
   
    （3） 服务器错误等
   
    示例：
   
    Create trigger ad_startup
   
    After startup
   
    On database
   
    Begin
   
    PL/SQL代码位置
   
    End;
   
    另外两个：
   
    Before shutdown
   
    Before servererror
   
    补充：删除触发器：drop trigger trigger_name;
   
    禁用触发器：alter trigger trigger_name diaable;
   
    插入日期时的格式：
   
    （1） insert into values（'5-1月-12'）；
   
    （2） insert into table_name（date'2012-1-5'）；
   
    复制表：create table emp_copy
   
    As select * from emp;
   
    但如果想复制表结构，不想复制数据：
   
    Create table emp_copy
   
    As select * from emp
   
    Where 1=2;

3, 控制语句
   
    （1） 条件
   
    If ……then……else语句
   
    有三种形式，分别如下：
   
    第一种形式：
   
    If expression then
   
    语句
   
    End if;
   
    第二种形式：
   
    If expression then
   
    语句
   
    Else
   
    语句
   
    End if;
   
    第三种形式：
   
    If expression then
   
    语句
   
    Else if expression then
   
    语句
   
    Else
   
    语句
   
    End if;
   
    （2,case语句
   
    Case value
   
    When expression then
   
    语句；
   
    When expression then
   
    语句；
   
    ……
   
    Else
   
    语句；
   
    End case;
   
    4, 循环
   
    （1） 无限制循环
   
    其基本语法格式为：；loop……end loop.无限制循环是指如果没有exit语句，循环将一直进行下去。
   
    例句：set serverout on //打开屏幕输出
   
    Declare loops number:=0;
   
    Begin
   
    Dbms_output.put_line（'循环开始'）；
   
    Loop
   
    If loops>4 then
   
    Exit;
   
    End if;
   
    Dbms_output.put_line（'第'||loops||'次循环'）；
   
    Loops:=loops+1;
   
    End loop;
   
    Dbms_output.put_line（'循环结束'）；
   
    End;
   
    （2） while循环
   
    例子：
   
    Declare loops number :=0;
   
    Begin
   
    Dbms_output.put_line（'循环开始'）；
   
    While loops<5 loop
   
    Dbms_output.put_line（'第'||loops||'次循环'）；
   
    Loops:=loops+1;
   
    End loop;
   
    Dbms_output.put_line（'循环结束'）；
   
    End;

5, 情景查询
   
    （1） decode（）函数
   
    decode（value_expression,search_value,result_value,default_value）
   
    参数含义：
   
    value_expression;表示将要与搜索值比较的值或表达式
   
    search_value;表示将要搜索的值
   
    result_value:表示search_value参数与value相等时value_expression参数的取值结果
   
    default_value;表示search_value参数与value不相等时value_expression参数的取值结果
   
    例句：select name ,sal,decode（deptno,'10','会计部','20','研发部','30','销售部','其他部门'） from emp
   
    （2） case表达式
   
    语法格式如下：
   
    Case search_expression
   
    When expression_1 then display_result_1
   
    When expression_2 then display_result_2
   
    ……
   
    When expression_n then display_result_n
   
    Else default_display_result
   
    End
   
    参数意义如下：
   
    search_expression:表示用于检索的表达式，一般情况下是表中的列名称
   
    expression_1,……，expression_n:表示用于比较的表达式
   
    display_result_1,……，display_result_n:表示用于比较的表达式与search_expression参数匹配时应该显示的结果。
   
    例句：select ename,sal,
   
    Case deptno
   
    When 10 then '会计部'
   
    When 20 then '研发部'
   
    Else '其他部门'
   
    End
   
    From emp;

与简单的case表达式相比，搜索表达式的每一个条件都可以不同，因此更加具有灵活性，搜索case表达式的语法格式如下：
   
    Case
   
    When expression_1 then display_result_1
   
    When expression_2 then display_result_2
   
    ……
   
    When expression_n then display_result_n
   
    Else default_display_result
   
    End
   
    例句：select ename.case
   
    When hiredate<'1-7月-01' then '老员工'
   
    When hiredate>'1-7月-01' and hiredate<='1-7月-02' then '普通员工'
   
    When hiredate>'1-7月-02' then '新员工'
   
    End from emp;    --两个表之间进行的连接（99语法）
   
    select * from emp join dept on emp.deptno=dept.deptno;
   
    select * from emp join dept on dept.deptno=emp.deptno;
   
    等价于（92语法）
   
    select * from emp,dept where emp.deptno=dept.deptno;
   
    --左外连接 以右边为万能行 （确保左边的数据能在右边找到）
   
    select e1.ename,e1.empno ,e1.deptno from emp e1 left join emp e2 on e1.mgr=e2.empno;
   
    --右外连接 以左边为万能行
   
    select * from emp;
   
    select * from emp e1 right join emp e2 on e1.mgr=e2.empno;
   
    等价于
   
    select e1.ename,e1.empno ,e1.deptno from emp e1 left join emp e2 on e1.empno=e2.mgr;（左外连接）
   
    --全外连接 保证左边和右边的记录都被找到
   
    select * from emp e1 full join emp e2 on e1.mgr=e2.empno;
   
    92语法关于左外连接 在右边加上一个"+"
   
    select * from emp,dept where emp.deptno=dept.deptno（+）；
   
    反过来右外连接 在左边加上一个"+"
   
    select * from emp,dept where emp.deptno（+）=dept.deptno;
   
    -使用伪字段：rownum,----------------------
   
    ------------------------------------------------------------------------------------------
   
    --用来标识每条记录的行号，行号从1开始，每次递增1
   
    select a.* ,rownum from emp a;
   
    或者
   
    select emp.* ,rownum from emp ;
   
    --oracle下rownum只能使用 < <=, 不能使用 = > >= 等比较操作符，
   
    --当rownum和order by 一起使用时，会首先选出符合rownum条件的记录，然后再排序
   
    --例如，当我们要求薪水最高的前5个人时，最直接的想法可以这样写：
   
    select sal from emp order by sal desc;
   
    select ename ,sal from
   
    （select ename,sal from emp order by sal desc）
   
    where rownum<=5
   
    //取出薪水在排名在6-10的人
   
    select * from（
   
    select t.*,rownum r from（
   
    （select sal from emp order by sal desc）t
   
    ）where rownum<=10）
   
    where r>=6
   
    --------------------------------------------------------
   
    --不准用组函数（即MAX（）），求薪水的最高值（面试题）
   
    --第一种解决办法：
   
    --1,先把所有薪水按照倒序排列
   
    select sal from emp order by sal desc;

--2,再取第一行
   
    select sal from
   
    （select sal from emp order by sal desc）
   
    where rownum=1
   
    --第二种解决办法：
   
    思路：将同一个表复制成两份，要求某一个表中的sal小于另外一个表中的sal,只有工资最高的不可能小，
   
    再利用 not in 进行筛选从而起得最大值
   
    --1,先跨表查询自己，先求出的结果中，e1.sal不可能出现最大数
   
    select e1.sal from emp e1 join emp e2 on e1.sal<e2.sal
   
    --2,然后再not in
   
    select sal from emp where sal not in（select e1.sal from emp e1 join emp e2 on e1.sal<e2.sal）
   
    -----------------------------------------------------------------
   
    --求平均薪水最高的部门的部门编号
   
    --第一种解决办法：
   
    --1,先求出每个部门的平均薪水，
   
    select deptno,avg（sal） from emp group by deptno;
   
    --2,再求每个部门的平均薪水的最高值，
   
    select max（avg_sal） from（select avg（sal） avg_sal from emp group by deptno）
   
    --3,最后再求第一步结果中avg_sal = 最高薪水的记录。
   
    select deptno,avg_sal from
   
    （select deptno,avg（sal） avg_sal from emp group by deptno）
   
    where avg_sal=
   
    （select max（avg_sal）from（select avg（sal） avg_sal from emp group by deptno） ）
   
    --这种写法没有考虑并列第一的情况
   
    select deptno from
   
    （select deptno,avg（sal） avg_sal from emp group by deptno order by avg（sal） desc）
   
    where rownum<=1
   
    --第二种解决办法：
   
    --1,将上面的第一步第二步合并，先求最高平均薪水，用max（avg（sal））的办法
   
    select max（avg（sal）） from emp group by deptno;
   
    --2,求出每个部门的平均薪水
   
    select deptno,avg（sal） from emp group by deptno;
   
    --3,最后再求第二步结果中（即每个部门的平均薪水），avg_sal = （第一步结果）的记录。即avg_sal =最高薪水的记录。
   
    select deptno,avg（sal） from emp group by deptno
   
    having avg（sal）=（select max（avg（sal）） from emp group by deptno）；
   
    --第三种解决办法：
   
    --1,先求出每个部门的平均薪水，
   
    select deptno,avg（sal） from emp group by deptno
   
    --2,求最高平均薪水，用max（avg（sal））的办法
   
    select max（avg（sal）） from emp group by deptno;
   
    --3,再使用having语句， avg（sal） = 第二步的结果
   
    --注意：为组函数起的别名在having中不能用
   
    select deptno,avg（sal） avg_sal from emp group by deptno
   
    having avg（sal）=（select max（avg（sal）） from emp group by deptno）
   
    --课堂练习：求平均薪水最高的部门的部门名称
   
    --1,部门平均最高薪水
   
    --2,得到部门编号列表，注意用group by deptno
   
    --3,再应用having子句， having avg（sal） = （第一步的结果）
   
    --4,得到平均最高薪水的那个部门的编号
   
    --5,再得到部门名称
   
    select * from dept where deptno =
   
    （select deptno from emp group by deptno
   
    having avg（sal）=（select max（avg（sal）） from emp group by deptno））
   
    --求平均薪水的等级最低的部门的部门名称
   
    --第一步：部门平均薪水的等级，分成两个小步骤，第一小步是求部门平均薪水
   
    select t1.deptno,t1.avg_sal ,s.grade
   
    from （select deptno, avg（sal） avg_sal from emp group by deptno ） t1
   
    join salgrade s on t1.avg_sal between s.losal and s.hisal ---t1
   
    --第二步：最低的等级值
   
    select min（grade） from（
   
    select t1.deptno,t1.avg_sal ,s.grade
   
    from （select deptno, avg（sal） avg_sal from emp group by deptno ） t1
   
    join salgrade s on t1.avg_sal between s.losal and s.hisal）
   
    --第三步：等于最低值的部门编号
   
    select *
   
    from （select t1.deptno, t1.avg_sal, s.grade
   
    from （select deptno, avg（sal） avg_sal from emp group by deptno） t1
   
    join salgrade s on t1.avg_sal between s.losal and s.hisal） t1
   
    where t1.grade =
   
    （select min（grade）
   
    from （select t1.deptno, t1.avg_sal, s.grade
   
    from （select deptno, avg（sal） avg_sal
   
    from emp
   
    group by deptno） t1
   
    join salgrade s on t1.avg_sal between s.losal and s.hisal））
   
    --第四步：求名称
   
    select t.*,dname,loc from
   
    （select *
   
    from （select t1.deptno, t1.avg_sal, s.grade
   
    from （select deptno, avg（sal） avg_sal from emp group by deptno） t1
   
    join salgrade s on t1.avg_sal between s.losal and s.hisal） t1
   
    where t1.grade =
   
    （select min（grade）
   
    from （select t1.deptno, t1.avg_sal, s.grade
   
    from （select deptno, avg（sal） avg_sal
   
    from emp
   
    group by deptno） t1
   
    join salgrade s on t1.avg_sal between s.losal and s.hisal））
   
    ）t join dept d on t.deptno=d.deptno