oracle学习笔记

1 启动oracle服务（使用oracle必须启动的两个服务）

监听和实例（lsnrctl意识是listener-control）

启动监听服务 ->lsnrctl start （+监听的名字，不写为默认名字）

停止监听服务 ->lsnrctl stop （+监听的名字，不写为默认名字）

启动数据库实例 -> oradim -starup -sid orcl(实例名)

（或者 -> net start OracleServiceORCL）

说明：启动数据库实例就是启动一个数据库；

      也可以同时启动多个实例；              

2 oracle系统默认的三个用户

SYS用户：超级管理员，权限最高，它的角色是DBA。默认密码是change_on_install。具有创建数据库的权限

SYSTEM用户：系统管理员，权限很高，它的角色是DBA operator，默认密码manager。不具有创建数据库的权限！

普通用户(normal)，如系统安装时的scott用户，默认密码是tiger。普通用户的权限是SYS用户或SYSTEM用户给的，如果没有给，那普通用户连很基本的访问权限，连接权限也没有。

3 创建用户

用户登录 –> conn user/password@ip(网络服务器名)[若是特权用户必须带上 as sysdba ]

用超级用户登录 ->sqlplus / as sysdba;

显示用户 ->show user;

创建用户 ->create uesr username(用户名) identified by password(密码);

修改当前用户的密码 –> passw；（说明：如果要修改其他用户的密码，需要sys/system登录）；

删除当前用户 –> drop user username cascade;（如果删除的用户已经建立了表，则必须带cascade关键字）；

断开与当前数据库的链接 -> disconn;

断开与数据库的链接，并退出 –> exit;

4 系统权限

创建会话权限（用户授权） ->grant create session to username;

撤销创建会话权限 -> revoke create session from userename;

创建表权限 ->grant create table to username;

撤销权限创建表权限 ->revoke create table from username;

创建表空间权限 ->grant unlimited tablespace to name;

撤销创建表空间权限 ->revoke unlimited tablespace from username;

创建表 ->create table tablename(id int);
_
获取系统信息 ->select * from user_sys_privs;

所有用户授权（public）-> grant create session to public;

5 对象权限(对象向对象授权，谁拥有权限谁授权)

对象授权 -> grant select on table1 to username2;

全部授权 -> grant all on table1 to username2;

所有用户授权 -> grant all on tabe1 to public;

撤销授权 -> revoke all on table1 from username2;

对象所有表的权限 -> select * from user_tab_privs;

指定用户对表的某一列进行操作 ->grant update|insert(某一列) on mytable to username1;(查询，删除不能控制到列)

查询对列的操作权限 -> select * from user_col_privs;

6 权限传递

系统权限实现权限传递 -> grant create table|alter any table|(权限) to username with admin option;

对象权限实现权限传递 -> grant select on mytable to username1 with grant option;

7 角色（权限的集合）

创建角色 -> create role myrole;

赋予角色权限 -> grant create table,create session to myrloe;

角色赋予用户 -> grant myrole to username;

删除角色 ->drop role myrole;

注意：

有些系统权限无法赋予角色

如：unlimited tablespace,alert table,drop table

表示属于某个用户的，但是角色是不属于某个用户的。

8 修改用户密码

修改普通户密码 -> alert user username identified by newpassword;

9 补充命令

获取一个数据库中有多少张表 -> select * from tab;

获取表的完整结构 -> desc tablename;

10 文件操作命令

运行sql脚本 –> start  d:/aa.sql; (->@  d:/a.sql;)

编辑sql脚本 –> edit  d:/aa,sql;

将sql*plus屏幕上的内容输入到指定文件 –> spool d:/bb.sql; (创建bb.sql文件)
                                       Spool off;

 

10 显示和设置环境变量

展示行的宽度 -> show linesize

设置行的宽度 –> set linesize 100;

展示每页显示的行数 –> show pagesize

设置每页显示的行数 -> set pagesize 10

 

11 oracle 基本查询

查看表的结构 –> desc tablename;

查看取消重复行 –> select distinct 【列名】from tablename;

如何处理null值？函数 nvl（row1,row2）,如果row1为null 则就用row2所替代，如果row1不为空就取row2的值；

Oracle查询日期格式为“day/month月/year”或者“day-month月-year”；

如何使用like？ %表示0到多个任意多的字符， _ 表示单个的任意字符；

排序查询（order by）-> select * from tablename order by row (asc/desc);(说明：默认排序是升序排列，asc是升序排列，desc是降序排列)；

多个字段排序 -> select * from emp order by row1 (asc),row desc;

使用别名排序 –> select row1 as name,row2 from tablename order by name;

12 oracle 复杂查询（数据分组）

分组函数：
查询最大值 –> select max(row) from tablename
查询最小值 –> select min(row) from tablebname;
查询某一列值的总和 -> select sum(row) from tablename;
查询某一列值的平均值 –> select avg(row) from tablename;
查询表的总行数或者某一列非空值的总行数 –> select count(*/row) from tablename;

group by 和 having 子句：
group by 用于对查询结果进行分组统计 –> select 表达式1,表达式2,…,row1,row2,… from tablename group by row1,row2,..;
Having 用于限制分组显示结果 –> select 表达式1,表达式2,…,row1,row2,… from tablename group by row1,row2,.. having (条件); (说明：条件中所用的参数必须是select后面的表达式或者row)；

数据分组总结：
分组函数只能出现在选择列表，having，order by子句中；
如果select子句中同时出现 order by，having，group by中那么他的顺序是group by….having….order by ;
在选这列中如果有列，表达式，和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则就会出错；

13  oracle 复杂查询（子查询）

不同表之间进行查询 –> select a1.row1,a1.row2,…a2.row1,a2.row2,…from table1 a1,table2 a2 where a1.row1=a2.row1(条件);

内连接查询（一个表链接自身）-> select a1.row1,….,a2.row,…. From table a1,table a2 where a1.row1=a2.row2(条件);

子查询？子查询是指嵌入在其他sql语句中select语句也叫嵌套语句；

单行子查询？是指只返回一行数据的子查询 -> select * from table where row = (select row from table/table1 where [条件])；

多行子查询？是指返回多行语句的子查询 -> select * from table where row in (select row from table/table1 where [条件])；

多行子查询中使用all -> select * from table where row > all(select row from table/table1 where [条件])；

多行子查询中使用any -> select * from table where row > any(select row from table/table1 where [条件])；

多行子查询（特例）-> select row1,row2,…from table where (row1,row2,….)=(select row1,row2,…from table where(条件));

把一个子查询看做一个子表->select a1.row1,a1.row2,a2.row1,a2.row2,… from table1 a1 ,(select row1,avg(row2),max(row3),….from table2 group by row1) a2 where a1.row=a2.row(条件)；

14 oracle 复杂查询（分页查询）
Oracle分页查询（三种，介绍其中一种 rownum分页）

Rownum分页
 建立视图 -> select * from table
 显示rownum（oracle分配行id号）->select a1.* ,rownum rn from (select * from table) a1;
 进行分页，如显示从5—10行的数据:
显示小于等于10行一下的数据-> select a1.* ,rownum rn from (select * from table) where rownum <= 10;
显示5—10行一下的数据->select * from (select a1.* ,rownum rn from (select * from table) a1 where rownum <= 10) where rn>=5;
 对查询进行变化，如指定查询列，对查询进行排序只需修改最里层的查询；
指定查询列 ->select * from (select a1.*,rownum rn from (select row1,row2,row3,… from table) a1 where rownum <= 10) rn >=5;
对查询进行排序 –> select * from (select a1.*,rownum rn from (select row1,row2,row3,… from table order by row1) a1 where rownum <= 10) rn >= 5;

Rowed 分页（不做介绍）；

分析函数分页(不做介绍)；

15  oracle 复杂查询（合并查询）
再实际应用中，为了合并多个select查询结果，可以使用集合操作符号 union,union all,intersect,minus;
 Union 该操作符用于取的两个结果的并集->select * from tablename1 where (条件) union select * from tablename2 where (条件); 
 Union all 该操作符赋予union 相似，但是不会消除重复行，不会进行排序；
 Intersect 该操作符用于取得两个结果的交集；
 Minus 该操作符用于取得两个结果的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据；

补充：
设置显示执行SQL语句所消耗的时间 -> set timing on;

疯狂复制法 -> insert into tablename(row1,row2,row3,……) select * from tablename; 

用查询结果创建表
 (这种命令是一种快速的创建表方式) –> create table tablename2(row1,row2,row3) as select row1,row2,row3 from tablename1;(说明在创建表的同事也将查询结果的数据导入新创建的表中)；

衡量一个程序员的标准
 对 SQL  语句的使用；
 对网络的了解；
 对程序的优化；

16  JAVA操作ORACLE
一、 Java如何连接oracle
举例说明：写一个showEmp.java 分页显示emp表中的数据：
Emp表中的数据如下：
 
 Java 操作 oracle有两种操作方式
1. Jdbc-odbc桥连接
a) 通过ODBC数据源配置
b) //1.加载jdbc-odbc数据库驱动
 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   //2.获取链接
Connection  conn = DriverManage.getConnection(“jdbc:odbc: testOra”,”userName”,”password”); (说明：testOra 是配置数据源的实例名)；

//Statement 是 Java 执行数据库操作的一个重要方法，用于在已经建立数据库连接的基础上，向数据库发送要执行的SQL语句
Statement sm= conn.createStatement();

//获取执行SQL语句
ResultSet rs=sm.executeQuery("select * from emp");
   
2. Jdbc连接方式
  //加载驱动
          Class.forName("oracle.jdbc.driver.OracleDriver");
         
          //获取链接
          Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.15:1521:TEST","testUser","testUser");
         
         //Statement 是 Java 执行数据库操作的一个重要方法，用于在已经建立数据库连接的基础上，向数据库发送要执行的SQL语句
          Statement sm=conn.createStatement();
         
          //获取执行SQL语句
ResultSet rs=sm.executeQuery("select * from emp");

 进行分页

 <%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
   
    <title>My JSP 'MyTestOracl.jsp' starting page</title>
   
 <meta http-equiv="pragma" content="no-cache">
 <meta http-equiv="cache-control" content="no-cache">
 <meta http-equiv="expires" content="0">   
 <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
 <meta http-equiv="description" content="This is my page">
 <!--
 <link rel="stylesheet" type="text/css" href="styles.css">
 -->

  </head>
 
  <body>
    <table>
    <tr><td>姓名</td><td>工资</td></tr>
    <%
          //加载驱动
          Class.forName("oracle.jdbc.driver.OracleDriver");
         
          //获取链接
          Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.15:1521:TEST","testUser","testUser");
         
         //Statement 是 Java 执行数据库操作的一个重要方法，用于在已经建立数据库连接的基础上，向数据库发送要执行的SQL语句
          Statement sm=conn.createStatement();
         
          //接受pageNow
          int pageNow = 1;
         
          String s_pageNow = (String)request.getParameter("pageNow");
         
          if(s_pageNow != null)
          {
                 pageNow = Integer.parseInt(s_pageNow);
          }
         
          //总页数
          int pageCount = 0;
         
          //每页显示的行数
          int pageSize = 3;
         
          //总行数
          int rowSize = 0;
         
          //获取执行SQL语句
          ResultSet rs = sm.executeQuery("select count(*) from emp");
         
          if(rs.next())
          {
                  //获取总行数
                  rowSize = rs.getInt(1);
                 
                  //计算总页数
               /*   if(rowSize%pageSize == 0)
                  {
                       pageCount = rowSize/pageSize;
                  }
                  else
                  {
                      pageCount = rowSize/pageSize+1;
                  }*/
                 pageCount = (rowSize%pageSize==0) ? rowSize/pageSize : rowSize/pageSize+1;
          }
         
          ResultSet rs1=sm.executeQuery("select * from (select  a1.*,rownum rn from(select * from emp) a1 where rownum<="+pageNow*pageSize+") where rn>="+((pageNow-1)*pageSize+1)+" ");
         
          while(rs1.next())
          {
                out.println("<tr>");
                out.println("<td>"+rs1.getString(2)+"</td>");
                out.println("<td>"+rs1.getString(6)+"</td>");
                out.println("</tr>");
          }
         
          for(int i=1; i<=pageCount; i++)
          {
                 out.print("<a href=MyTestOracl.jsp?pageNow="+i+">["+i+"]</a>");
          }
   
     %>
    </table>
  </body>
</html>

17.子查询修改数据
 To_date 函数
使用to_date函数-> update tablename set rowdate = to_date('03-26-2013','mm-dd-yyyy') where (条件); (注，实际年月日要与年月日样式保持一致，如（’2013-03-23’,’yyyy-mm-dd’）)
 使用子查询插入数据时，一条insert语句可以大量数据；
 insert into tablename1(row1,row2) select row1,row2 from tablename2 where (条件);
 使用子查询修改数据时，一条update语句可同时修多条语句
 Update tablename set (row1,row2,…)=(select row1,row2,…from [条件]) where [条件]
18.oracle中事务处理
 什么是事务？
事务就是用于保证数据的一致性，它由一组相关的dml语句组成，改组的dml语句要么全部成功，要么全部失败;(dml语句就是数据操作语言，如查询，删除等等)；
 事务和锁
当执行事务操作时（dml语句），oracle会在被操作的表上加锁，防止其他用户改变表的结构；
 提交事务
当执行使用commit语句会提交事务，当执行了commit事务，会确认事务的变化，结束事务，删除保存点，释放锁。当使用commit提交事务后，其他会话可以看到事务确认变化的新数据；
 保存点
保存点是事务中的一个点，用于取消部分事务，当事务结束时，会自动删除该事务所定义的所有保存点。
 回滚事务
当执行rollback时，通过制定保存点可以回退到制定的点；（注：当事务执行commit提交了以后，不能再回滚了）。
 事务的几个重要操作
1) 设置保存点
Savepoint  a1；
2) 取消部分事务
Rollback to a1；
3) 取消全部事务
Rollback；
4) 提交事务
Commit；
 Java中如何使用事务
在java中操作数据时，为了保证数据库的一致性，比如转账操作；

import java.sql.*;

public class testOra2 {

 //演示jdbc链接操作oracle
 
 public static void main(String[] args) {
  
  Connection conn=null;
  try
  {
   //1.加载jdbc驱动
   
   Class.forName("oracle.jdbc.driver.OracleDriver");
   
   //2. 获取链接
   conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.15:1521:TEST","testUser","testUser");
   
   //下面操作和sql server一样
   Statement sm=conn.createStatement();
   
   //加载事务处理
   conn.setAutoCommit(false);
   
    sm.executeUpdate("update emp set sal=sal-100 where ename='KING'");
   
    //设置异常
   int i=7/0;
   
    sm.executeUpdate("update emp set sal=sal+100 where ename='SCOTT'");
   
    //提交事务处理
    conn.commit();
   //关闭资源
   conn.close();
   sm.close();
   
  }
  catch(Exception ex)
  {
   try
   {
    //事务回滚
    conn.rollback();
   }
   catch (Exception e)
   {
        e.printStackTrace();
   }
   
   ex.printStackTrace();
   
  }
 

 }

}

 只读事务
只读事务就是只允许执行查询操作，不允许执行其他任何dml操作的事务，使用只读事务可以确保用户只能取得某时间点的数据。
设置只读事务
Set transaction read only;

 

 

 

19 oracle -sql函数的使用（字符函数）
Lower(char)将字符串转化为小写格式；
Upper(char)将字符串转化为大写格式；
Length(char)返回字符串的长度；
Substr(char,m,n)（注：m表示截取字符串的起始位置，n表示截取的长度）去字符串的子串；
 典型案例：
以首字母大写其余字母小写显示某一列 -> select upper(substr(row,1,1))||lower(substr(row,2,length(row)-1)) from tablename;
Replace(char1,search_string,replace_string)替换，search_string表示需要被替换的字符串，replace_string表示替换的字符串；
Instr(char1,char2,[,n[,m]])取字串在字符串中的位置；

20． oracle -sql函数的使用（数学函数）
数学函数输入的参数和返回值的数据类型都是数字类型，数学函数包括cos，cosh，exp，ln，log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round，我们讲常用的：
 round(n,[m]) 该函数用于执行四舍五入，如果省略m则四舍五入到整数位，如果m为整数，则截取到小数点m位后，如果m为负数，则截取到小数点的前m位；
 trunc(n,[m]) 该函数用于截取数字，如果省略掉m，就截去小数点部分，如果m是正数就截取小数点m位后，如果m为负数就截取小数点的前m位；
 mod(n,m) 该函数取n模m ，类似于n除以m的余数，例：select mod(13,2) from dual;(注：dual是一个虚拟表)
 floor(n) 返回小于或等于n的最大整数；
 ceil(n) 返回大于或等于n的最大整数；

其他的数学函数：
 abs(n) 返回数值n的绝对值；
 acos(n) 返回n的反余弦值；
 asins(n) 返回n的反正弦值；
 atan(n) 返回 n的方正切值；
 exp(n) 返回e的n次幂；
 log(n,m) 返回对数值；
 power(n,m) 返回m的n次幂；

21． oracle -sql函数的使用（日期函数）
日期函数用于处理date类型的数据：
 sysdate该函数返回系统时间
 select sysdate from dual;
 add_months(row,months) 增加约数
 select add_months(row,10) from tablename;
 last_day(rowdate) 指定日期所在月份的最后一天：
 select last_day(rowdate) from tablename;
 To_date 函数
使用to_date函数-> update tablename set rowdate = to_date('03-26-2013','mm-dd-yyyy') where (条件); (注，实际年月日要与年月日样式保持一致，如（’2013-03-23’,’yyyy-mm-dd’）)
22． oracle -sql函数的使用（转换函数）
 
 select to_char(rowdate,’$99,999.99’) from tablename;
 select to_char(sal,’yyyy-mm-dd hh24:mi:ss’) from tablename;
 select to_char(sal,’yyyy’) from tablename;

 

 

23． oracle -sql函数的使用（系统函数）
 sys_context
 terminal:当前会话客户所对应的终端标示符；
select sys_context(‘userenv’,’terminal’) from dual;
 language:返回当前操作的语言：
select sys_context(‘userenv’,’language’) from dual;
 db_name:当前数据库名称
select sys_context(‘userenv’,’db_name’) from dual;
 nls_date_format:当前会话客户所对应的日期格式：
select sys_context(‘userenv’,’nls_date_format’) from dual;
 session_user:当前会话客户所对应的数据库用户名：
select sys_context(‘userenv’,’session_user’) from dual;
 current_schema:当前会话客户所对应的默认方案名：
select sys_context(‘userenv’,’ current_schema’) from dual;
 host:返回数据库所在的主机名：
select sys_context(‘userenv’,’ host’) from dual;