Oracle常用命令

(1) 初始化密码
     sys change_on_intall
     system manager
     scott tiger
(2) 复制一个表
    create table new_table as(select * from old_table)
(3) 修改一个表的主键
    alter table tableName drop constraint a_pk;//删除原来的主键约束
    alter table tableName add constraint a_pk  primary key(id)//增加一个新约束
(4) 查看表结构
    desc tableName
(5) 快速清空一张表的数据
    truncate table new_table
(6) 把select的结果导入到文件
    spool c:/abc.text;
    select * from new_table;
    spool off;
(7) 修改表名称
    alter table old_table rename to new_table;
(8) 检索前N条记录
    select * from new_table where rownum<N order by id;
(9) 统计两张表的总记录数
    select ((select count(id) from a)+(select count(id) from b))from dual;
(10)日期加上两年
    select add_months(t.curdate,24) from new_table t.id=1;
(11)日期加两天
    select t.curdate-2 from new_table t.id=1;//t.curdate是日期类型
(12)返回当月的最后一天
    select last_day(t.hiredate) from emp t where t.empno=7369;
(13)集合加减
    select * from new_table union all select * from old_table;
    select * from new_table minus select * from old_table;
(14) 创建序列
    create sequence seq_name start 1 increment by 1;
    insert into tableName()values(seq_name.nextval,)
(15) 创建索引
    create index index_name on tableName(name);
(16) 修改sys密码
    alter user "sys" identified by "new_password";
(17) 删除表空间
    drop tablespace including contents and datafiles;
(18) 创建表空间
    create tablespace tpName
       datafile 'tpName'

          size 32m 
          autoextend on next 200M
                  maxsize unlimited;
(19)   删除用户
 drop user pork1 CASCADE;    

             
(20)  创建用户和密码并指定表空间
 create user userName identified by values "passwd"
 default tablespace tpName;

 

(21)  给用户session授超级用户权限
 grant connect,resource to userName;

 

(22)  给用户userName授DBA权限
     grant DBA to userName;
(23) 倒入倒出数据命令
  
导出指定的表：
exp userid=trswcm/trs owner=trswcm buffer=204800000 tables=(WCMChannel,WCMWebSite) file=/trs/MyData.dmp grants=y
导入指定的数据：
imp userid=trswcmtest/trs fromuser=trswcm touser=trswcmtest ignore=y buffer=204800000 file=/trs/MyData.dmp
//204800000 字节提交一次，ignore保证第二次输入即使对象存在也能成功

(24)Having 和werhe 的区别
Having和Where子句可以控制用于生成结果集的源表中的行
(1) 作用对象不一样。where是作用在表或者视图中，having是作用在组（group）
(2) where 总是在聚集计算（count,max）或者分组之前选取行，而Having总是在分组或者之后选取行
因此where子句中不能有聚集函数，而Having中必须包含聚集函数

结论：
1.WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
2.GROUP BY 子句用来分组 WHERE 子句的输出。
3.HAVING 子句用来从分组的结果中筛选行。

 

(25) group by 和where的区别
     where 条件是根据某个字段来进行过滤操作的，group by是对分组进行操作的

 

(26) 转义字符 escape
select * from bonus  t where t.ename like 'S/_%' escape '/'
表示/是转义字符，对某些关键字无法模糊查询，对_(关键字)进行转义

(27)函数分类
1、单行函数
 lower、upper initcap(首字母大写) concat substr length nvl
 如果原来值是null的话由指定数值代替
 select nvl(count(gard),0) from t;
2、数值函数
 round 四舍五入
 select round(45.935,2) from dual;  结果45.94 第二个参数表示保留小数点后两位
 trunc 截取
 select trunc(45.935,2) from dual; 结果45.90   第二个参数表示截取到小数点后两位
3、日期函数
 add_mounths(sysdate,-5)  延迟五月
 add_mounths(sysdate,-5*12) 延迟五年
 sysdate-5 延迟五天
 to_char(sysdate,'yyyy-mm-dd'),to_date('2008-01-12','yyyy-mm-dd'),to_number() 日期格式转换
4、组函数
 group by  分组子句，分组后的记录过滤是用having 而对where是对记录过滤
 avg()、count()、max()、min()、sum() avg和sum只能用于数字类型
 count(*)是求记录数
 深刻理解这条查询语句
 select id,count(*) from t group by id
 首先是对id进行分组，然后再对每组进行统计，最后的结果是每组的数量和，平均数量(avg())
 还可以对分组加过滤条件
 having max(),min(),count()等，取出符合条件的组进行统计.
 having 子句对group by 子句所确定的行组进行控制，having子句条件只允许涉及常量，聚组函数或group by子句中的列。

 

(28)、SQL子查询
 子查询的运行顺序：先运行子查询再运行主查询，子查询一般出现在运算符的右边，子查询结果一般是一行一列，一行多列，
 一列多行、多行多列
 主查询对应有单值运算符(一行一列，一行多列)和多值运算符(一列多行、多行多列)，如果使用单运算符但子查询返回是多行时
 会报错
 eg: select name  from emp where title =(select title from emp where emp.f_name='chen')有可能有多个f_name是chen的
 员工，所以title也是多个结果。使用单运算符是错误的。
 更正：select name  from emp where title in (select title from emp where emp.f_name='chen')

(29)、分页显示
  rownum要么等于1，或者小于某个值，不能等于或者大于某个值
  因为ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列。rownum是对符合条件结果的  序列号。它总是从1开始排起的

。所以你选出的结果不可能没有1。between……and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除，接着取

下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。为什么 between 1 and 10 或者 between 0 and 10 能查到结果，而用

between 2 and 10 却得不到结果原因同上一样，因为 rownum 总是从 1 开始。
但如果就是想要用 rownum > 10这种条件的话话就要用嵌套语句，把rownum先生成，然后对他进行查询。
  select *
      from （selet rownum as rn，t1.* from a where ……）
          where rn >10
一般代码中对结果集进行分页就是这么干的。即，先生成rownum后对列过滤，
  select * from (select a.*,rownum r from emp a) where r between 5 and 10