Oracle操作命令详解

                                                                                                             Oracle操作命令详解

一.用户管理

    1.使用profile管理用户口令，profile是口令限制，资源限制的命令集合，当系统建立数据库是

         用户没有指定profile选项，那oracle就会将默认的分配给用户。

        1）账户锁定：（必须要有dba权限的用户才可以执行如sys，system）

                             指定某用户登录只可以尝试3次，锁定时间位2天，

                             创建profile：

create profile AAAlimit failed_login_attempts 3password_lock_time 2

                              把profile指定给testuser：alter user testuser profile AAA  

        2）账户解锁：alter user testuser account unlock

        3）删除profile：drop profile AAA cascade(表示级联删除)

   2.连接命令：conn 用户名/密码@网络服务名

       文件操作命令：start：运行sql脚本，edit：编辑指定的脚本，spool：将sql操作结果输出到指定文件

                               linesize：设置行的宽度，pagesize ：设置行的数目

        创建用户：create user AAA identified by 密码

   3.表的管理：创建表：create table AAA();

                        修改表：

                                    往表中添加一个字段：alter tableAAAadd(classid number(2));

                                     修改表中字段的长度：alter table AAA modify(classid char(3));字段中不可以有数据

                                     删除一个字段：          alter table AAAdrop  columclassid    

                                     修改表的名字：          rename tableAAAtoaaa      

                                     删除表：                      drop table AAA

                       表中修改数据：

                                              增：

                                                    insert into AAA values('01-1月-2001');日期格式必须注意日-月-年

                                                     可以修改格式：alter session set nls_date_format='yyyy-mm-dd'

                                                     添加部分字段 insert into AAA(xh,xm) values('','');

                                              删：delete from AAA删除后不删除表的结构只删数据，写日志，还可以找回

                                                     truncate table AAA删除后不删除表的结构只删数据，不写日志，不可以找回。

                                                     drop table  AAA 删除后删除表的结构和数据，不可以找回

                                              改：update AAA set xh='222' where xm=sadsa";

                     表的简单查询：

                                             1.查询结果要求去除重复行：使用distinct关键字。

                                             2.查看表的结构：desc AAA

                                             3.使用算数表达式，使用列的别名，

                                                 使用nvl函数处理null(nvl(comm,0)如果comm为null则用0替代)，

                                                  使用||连接字符串

                                             4.where子句，

                                             5.  like操作符：%表示任意0到多个字符，_表示任意一个字符

                                                 例子：查询首字母为S的员工工资：select sal where name like ‘S%’

                                                            查询首第三个字母为S的员工工资：select sal where name like ‘__S%’

                                            6.where子句中使用in

                                            7.使用order by排序：order by 字段 desc降序  order by 字段 asc升序

                                                注意使用列的别名

                   表的复杂查询

                                       1.数据分组：max()，min()，avg(),sum(),count();

                                          select ename from emp where sal=(select max(sax) from emp)

                                       2.group by哈having子句

                                          group by：用于对查询的结果进行分组统计

                                          having 子句：用于限制分组显示结果

                                         事例：查询每个部门的平均工资和最高工资：

                                                   

select avg(sal),max(sal),depton from emp group by depton       

             

                                                    查询每个部门每种岗位的平均工资和最高工资： 

                                                   

select avg(sal),max(sal),depton，job from emp group by depton，job 

  

                                                  查询平均工资低于2000的部门号和他的平均工资： 

                                                

  select avg(sal),depton from emp group by depton having avg(sal)<2000;

                                          数据分组总结：分组函数只能出现在选择咧和having order by子句后

                                                                    group by，having ，order by顺序一定如此

                                     3.多表查询：多表连接查询，

                                                          实例：查询雇员名称，雇员工资，雇员部门，并按照雇员部门编号排序

                                                                   

 select a1.ename,a1.sal,a2.depton from emp a1,depton a2 
                                                                    where a1.depton = a2.depton order by depton

                                                           自连接:把一个表看成两个表，这两个表建立连接进行查询。

                                     4.子查询：

                                                    1.单行一列子查询用“=“（子查询返回结果只有一行数据）

                                                    2.多行一列子查询用”in“ （子查询返回结果有多行数据）

                                                    3.

                                                        all操作符的使用，对应可以使用max()

                                                        any操作符的使用 对应可以使用min()

                                                     4.

                                                        单行多列子查询

                                                        实例：

select * from emp where（deptno,job）=
                                                                (select deptno,job,from emp where name='smith');

                                                     5.

                                                         from后使用子句查询：

                                                         查询比自己所在部门平均工资要高的员工信息，及平均工资

                                                         

select a1.*,a2.mysal from emp a1,（select deptno avg(sal) mysal from emp）
                                                         where   a1.sal>a2.mysal and a1.deptno=a2.deptno;

                                                    6.使用子查询进行创建表：

                                                     

  create table AAA(xh,xm,sex) as(select xh,xm,sex from aaa);

                                        5.分页查询：

                                             oracle中的分页查询共有三种方式，下面介绍rownum方式，oracle分页就是进行子句

                                             嵌套查询：

                                          

   select a1.* rownum rn form(select *from emp) where rownum<=10

                                             执行结果是显示前10条结果

                                           

  select *from(select a1.* rownum rn form(select *from emp) where rownum<=10) 
                                             where rn>=5

                                             执行结果是显示5-10条结果 

                                             若排序需要对字段特殊要求如排序则只需对最内层的查询进行修改即可

                                             (select *from emp)

                                        6.合并查询：

                                           在实际应用中，为了合并多个select查询结果可以使用集合操作符

                                            union，union all，intersect，minus

                                            1）union两个集合查询结果想加减去重复的数据

                                            2）union all两个集合查询结果想加不减去重复的数据

                                            3）intersect取得两个集合的交集

                                            3）minus取得两个集合的差集