oracle学习

用查询结果创建新表，这个命令是一种快捷的建表方式
CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp;
恢复数据
第一种：

1.打开Flash存储的权限
ALTER TABLE tableName ENABLE row movement ;
2.把表还原到指定时间点
flashback table tableName to timestamp to_timestamp(’‘2011-02-28 10:40:00’’,’‘yyyy-mm-dd hh24:mi:ss’’);
后面的参数为要还原的时间点

第二种：利用ORacle的快照进行查找某个时间点的数据
select * from tableName AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL ‘100’ MINUTE)

或

select * from tableName as of timestamp to_timestamp(‘2011-05-21 11:40:00’,‘YYYY-MM-DD HH24:MI:SS’);

这样可以查询到指定的时间段的数据，再把查询到的数据复制到原来的表中。
–创建表

学生表
create table student (
   xh number(4), --学号
   xm varchar2(20), --姓名
   sex char(2), --性别
   birthday date, --出生日期
   sal number(7,2) --奖学金
);

--班级表
create table class(
  classid number(2),
  cname varchar2(40)
);

–添加一个字段
alter table student add (classid number(2));

–修改一个字段的长度
alter table student modify(classid number(20))

–删除表
sql>drop table student;

–添加数据
–所有字段都插入数据
insert into student values (‘1’, ‘张三’, ‘男’, ‘01-5 月-05’, 10);

–插入部分字段
insert into student(xh, xm, sex) values (‘3’, ‘john’, ‘女’);

–插入空值
insert into student(xh, xm, sex, birthday ) values (‘4’, ‘martin’, ‘男’, null);

如果你要查询student表里birthday为null的记录，怎么写sql呢？
select * from student where birthday is null;

–修改一个字段
update student set sex = ‘女’ where xh = ‘1’;

–修改多个字段
update student set sex = ‘男’, birthday = ‘1984-04-01’ where xh = ‘1’;

–删除数据
delete from student; --删除所有记录，表结构还在，写日志，可以恢复的，速度慢。
–delete的数据可以恢复。
savepoint a; --创建保存点
delete from student;
rollback to a; --恢复到保存点
drop table student; --删除表的结构和数据；
delete from student where xh = ‘3’; --删除一条记录；
truncate table student; --删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。

emp 雇员表

字段名称   数据类型       是否为空   备注
--------------------------------------
EMPNO    NUMBER(4)                 员工编号                

ENAME    VARCHAR2(10) Y         员工名称        

JOB        VARCHAR2(9)   Y         职位                

MGR       NUMBER(4)       Y         上级的编号            

HIREDATE DATE             Y         入职日期               

SAL         NUMBER(7,2)   Y         月工资            

COMM      NUMBER(7,2)   Y         奖金                

DEPTNO   NUMBER(2)      Y         所属部门

dept 部门表

字段名称    数据类型          是否为空   备注

--------    -----------      --------   --------

DEPTNO   NUMBER(2)                    部门编号               

DNAME    VARCHAR2(14)    Y          部门名称        

LOC         VARCHAR2(13)   Y          部门所在地点

salgrade 工资级别表

字段名称  数据类型   是否为空  备注
--------  ---------  --------  --------
  GRADE   NUMBER     Y         级别                

	LOSAL    NUMBER     Y         最低工资               

	HISAL     NUMBER     Y         最高工资

如何显示每个雇员的年工资？

select sal*13+nvl(comm, 0)*13 "年薪" , ename, comm from emp;

1、如何连接字符串(||) select ename || ' is a ' || job from emp;
2、使用where子句 问题：如何显示工资高于3000的员工？
select * from emp where sal > 3000
3、如何查找2019-1-1后入职的员工
select ename,hiredate from emp where hiredate >'2019-1-1';
4、如何显示工资在1000到2000的员工？
select ename,sal from emp where sal>=1000 and sal<=2000;
5、如何使用like操作符 %：表示0到多个字符 _：表示任意单个字符 问题：如何显示首字符为s的员工姓名和工资？
select ename,sal from emp where ename like 's%';
6、 如何显示第三个字符为小写o的所有员工的姓名和工资？
select ename,sal from emp where ename like '__o%';
7、在where条件中使用in 问题：如何显示empno为7844,7839,123,456的雇员情况？
select * from emp where empno in (1, 2, 3, 4);
8、使用is null的操作符 问题：如何显示没有上级的雇员的情况？
select * from emp where mgr is null;
9、问题：查询工资高于500或者是岗位为manager的雇员，同时还要满足他们的姓名首字母为大写的J？
select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';
10、使用order by字句 默认asc 问题：如何按照工资从低到高的顺序显示雇员的信息？
select * from emp order by sal;
11、使用列的别名排序 问题：按年薪排序
select ename, (sal+nvl(comm,0))*12 "年薪" from emp order by "年薪";
12、聚合函数用法：max，min，avg，sum，count 问题：如何显示所有员工中最高工资和最低工资？
select ename, sal from emp where sal=(select max(sal) from emp);
13、问题：如何显示所有员工的平均工资和工资总和？
select sum(e.sal), avg(e.sal) from emp e;
14、查询最高工资员工的名字，工作岗位
select ename, job, sal from emp e where sal = (select max(sal) from emp);
15、显示工资高于平均工资的员工信息
select * from emp e where sal > (select avg(sal) from emp);
16、group by 和 having 子句 group by 用于对查询的结果分组统计， having 子句用于限制分组显示结果。 问题：如何显示每个部门的平均工资和最高工资？

select avg(sal), max(sal), deptno from emp group by deptno;

17、问题：显示每个部门的每种岗位的平均工资和最低工资？
select min(sal), avg(sal), deptno, job from emp group by deptno, job;
18、问题：显示平均工资低于2000的部门号和它的平均工资？
select avg(sal), max(sal), deptno from emp group by deptno having avg(sal)< 2000;
分组总结：

1 分组函数只能出现在选择列表、having、order by子句中(不能出现在where中) 2
如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having,
order by 3 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则就会出错。
如select deptno, avg(sal), max(sal) from emp group by deptno having
avg(sal) < 2000;这里deptno就一定要出现在group by中

19、显示雇员名，雇员工资及所在部门的名字【笛卡尔集】？

 SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno

规定：多表查询的条件是至少不能少于表的个数N-1才能排除笛卡尔集（如果有N张表联合查询，必须得有N-1个条件，才能避免笛卡尔集合）

20、问题：显示部门号为1的部门名、员工名和工资？
SELECT d.dname, e.ename, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno and e.deptno = 1;
21、问题：显示各个员工的姓名，工资及工资的级别？
SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
21、问题：显示雇员名，雇员工资及所在部门的名字，并按部门排序？
SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno;

注意：如果用group by，一定要把e.deptno 放到查询列里面

22、自连接 自连接是指在同一张表的连接查询 问题：显示某个员工的上级领导的姓名？
SELECT worker.ename, boss.ename FROM emp worker, emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'Jo';
23、在多行子查询中使用all操作符 问题：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号？

    --方法一
	SELECT ename, sal, deptno FROM emp WHERE sal > all(SELECT sal FROM emp WHERE deptno = 30);

	--方法二(执行效率最高，使用聚合函数)

	SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT max(sal) FROM emp WHERE deptno = 30);

24、在多行子查询中使用any操作符 问题：如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号？

    --方法一
	SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30);

	--方法二(执行效率最高，使用聚合函数)

	SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30);

25、如何查询与Jo的部门和岗位完全相同的所有雇员。

SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'Jo');

用查询结果创建新表，这个命令是一种快捷的建表方式
CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp;

1). union 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。

SELECT ename, sal, job FROM emp WHERE sal >2500	UNION SELECT ename, sal, job FROM emp WHERE job = 'clerk ';

两边有一个条件符合就输出

union all 该操作符与union相似，但是它不会取消重复行，而且不会排序

oracle分页

    --   方法（1）效率最好
SELECT *
  FROM EMP
 WHERE ROWID IN
       (SELECT RID
          FROM (SELECT ROWNUM RN, RID
                  FROM (SELECT ROWID RID, EMPNO FROM EMP ORDER BY EMPNO DESC)
                 WHERE ROWNUM <= ( (1-1) * 5 + 5 )) --每页显示几条
         WHERE RN > ((1-1) * 5) ) --当前页数
 ORDER BY EMPNO DESC;
      -- 方法 （2）
        SELECT *
      FROM (SELECT T.*, ROWNUM RN
              FROM (SELECT * FROM EMP ORDER BY EMPNO DESC) T
             WHERE ROWNUM <= 5)
     WHERE RN > 0;

五、事务的几个重要操作
java程序中如何使用事务

public static void main(String[] args) {
        Connection conn = null;
        try {
            // 1.加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            // 2.得到连接
            conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
            // 加入事务处理
            conn.setAutoCommit(false);// 设置不能默认提交
            Statement sm = conn.createStatement();
            // 从scott的sal中减去100
            sm.executeUpdate("update emp set sal=sal-100 where ename='SCOTT'");
            int i = 7 / 0;
            // 给smith的sal加上100
            sm.executeUpdate("update emp set sal=sal+100 where ename='SMITH'");
            // 提交事务
            conn.commit();
            // 关闭打开的资源
            sm.close();
            conn.close();
        } catch (Exception e) {
            // 如果发生异常，就回滚
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
    }

oracle 常用函数
一、字符函数

lower(char)：将字符串转化为小写的格式。 upper(char)：将字符串转化为大写的格式。
length(char)：返回字符串的长度。 substr(char, m,
n)：截取字符串的子串，n代表取n个字符的意思，不是代表取到第n个 replace(char1, search_string,
replace_string) instr(C1,C2,I,J)
–>判断某字符或字符串是否存在，存在返回出现的位置的索引，否则返回小于1;在一个字符串中搜索指定的字符,返回发现指定的字符的位置; C1 被搜索的字符串 C2 希望搜索的字符串 I 搜索的开始位置,默认为1 J 出现的位置,默认为1

问题：将所有员工的名字按小写的方式显示

SQL> select lower(ename) from emp;

二、数学函数 三、日期函数 日期函数用于处理date类型的数据。默认情况下日期格式是dd-mon-yy 即“12-7 月-12”
(1)sysdate 返回系统时间 select sysdate from dual;
(2)oracle add_months函数oracle add_months(time,months)函数可以得到某一时间之前或之后n个月的时间
select add_months(sysdate,-6) from dual; --该查询的结果是当前时间半年前的时间
select add_months(sysdate,6) from dual; --该查询的结果是当前时间半年后的时间
(3)last_day(d)：返回指定日期所在月份的最后一天 问题：查找已经入职8个月多的员工
select * from emp where sysdate>=add_months(hiredate,8);
问题：显示满10年服务年限的员工的姓名和受雇日期。
select ename, hiredate from emp where sysdate>=add_months(hiredate,12*10);
对于每个员工，显示其加入公司的天数
select floor(sysdate-hiredate) “入职天数”,ename from emp;
问题：找出各月倒数第3天受雇的所有员工。
select hiredate,ename from emp where last_day(hiredate)-2=hiredate;

四、转换函数 转换函数用于将数据类型从一种转为另外一种。
1、显示薪水的时候，把本地货币单位加在前面

select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss'), to_char(sal,'L99999.99') from emp;	

2、显示2019年入职的所有员工

 select * from emp where to_char(hiredate, 'yyyy')=1980;

3：显示所有12月份入职的员工

select * from emp where to_char(hiredate, 'mm')=12;

oracle 约束
二、约束
二、约束

2)、unique(唯一)
当定义了唯一约束后，该列值是不能重复的，但是可以为null。
5)、check
用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间如果不在1000-2000之间就会提示出错。

   1)、增加商品名也不能为空
SQL> alter table goods modify goodsName not null;
2)、增加身份证也不能重复
SQL> alter table customer add constraint xxxxxx unique(cardId);
3)、 增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’；
SQL> alter table customer add constraint yyyyyy check (address in ('海淀','朝阳','东城','西城','通州','崇文','昌平'));
删除约束
当不再需要某个约束时，可以删除。
alter table 表名 drop constraint 约束名称；
那么在删除主表的主键约束时，必须带上cascade选项 如像：alter table 表名 drop primary key cascade;

oracle 索引
二、创建索引

1)、单列索引
单列索引是基于单个列所建立的索引
语法：create index 索引名 on 表名(列名);
eg、create index nameIndex on custor(name);
2)、复合索引
复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同，比如：
create index emp_idx1 on emp(ename, job);
create index emp_idx1 on emp(job, ename);
以上这两个索引是两个不同的索引。
六、显示索引信息
1)、在同一张表上可以有多个索引，通过查询数据字典视图dba_indexs和user_indexs，可以显示索引信息。其中dba_indexs用于显示数据库所有的索引信息，而user_indexs用于显示当前用户的索引信息：select index_name, index_type from user_indexes where table_name = '表名';
2)、显示索引列
通过查询数据字典视图user_ind_columns,可以显示索引对应的列的信息
select table_name, column_name from user_ind_columns where index_name ='IND_ENAME';
你也可以通过pl/sql developer工具查看索引信息

存储过程
1.请考虑编写一个存储过程，可以输入雇员名，新工资，用来修改雇员的工资

--根据雇员名去修改工资
CREATE PROCEDURE sp_update(uname VARCHAR2, newsal NUMBER) IS
BEGIN
   update emp set sal=newsal where ename=uname;
END;
/

3.如何在java程序中调用一个存储过程

public class ProcedureTest {

    public static void main(String[] args) {

        try {
            // 1.加载驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            // 2.得到连接
            Connection ct = DriverManager.getConnection(
                    "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");

            // 3.创建CallableStatement
            CallableStatement cs = ct.prepareCall("{call sp_update(?,?)}");
            // 4.给?赋值
            cs.setString(1, "SMITH");
            cs.setInt(2, 4444);
            // 5.执行
            cs.execute();
            // 关闭
            cs.close();
            ct.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

在java程序中调用oracle函数：select annual_incomec(‘C##qinym_user’) income from dual;
public class ProcedureTest {

public static void main(String[] args) {

    try {
        // 1.加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 2.得到连接
        Connection ct = DriverManager.getConnection(
                "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "oracle");
        // 3.创建PreparedStatement
        PreparedStatement ps = ct.prepareStatement("select annual_incomec('C##qinym_user') annual from dual");
        // 4.执行
        ResultSet rs=ps.executeQuery();
        if(rs.next()){
            Float annual=rs.getFloat("annual");
            System.out.println(annual);
        }
        //5、关闭
        rs.close();
        ps.close();
        ct.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}
}