oralce基础--转载

--1.将所有的员工名字按小写方式显示
select lower(ENAME)ENAME from emp;

--2.将所有的员工名字按大写的方式显示
select upper(ENAME)ENAME from emp;

--3.显示正好为5个字符的员工姓名
SQL> select ENAME as 姓名长度正好为5的 from emp where length(ENAME)=5;

--4.显示所有员工姓名的前三个字符
select substr(ENAME,0,3) 姓名前三个字符 from emp;

--5.以首字母大写的方式显示所有员工的姓名
select initcap(LOWER(ENAME)) 首字母大写  from emp;

--5.以首字母小写的方式显示所有员工的姓名
SQL> select substr(LOWER(ENAME),0,1)||substr(UPPER(ENAME),1) 首字母小写  from emp;

select * from emp;

--统计函数
--排序，如果有被排序的字段有相同的值，也不中断。1，2，3，4，5 ……
select EMPNO,ENAME,SAL,row_number() over (order by sal DESC)as SAL_RANK from EMP;

--排序，如果有被排序的字段有相同的值，则会跳跃。1，2，2，4，5 ……
select EMPNO,ENAME,SAL,RANK() over (order by sal DESC)as SAL_RANK from EMP;

--排序，如果有被排序的字段有相同的值，也不中断，连续下去。1，2，2，3，4 ……
select EMPNO,ENAME,SAL,DENSE_RANK() over (order by sal DESC)as SAL_RANK from EMP;

--nvl 如果有空则用后面的值代替(如果COMM中有空的，则用0补充)。
select ENAME, nvl(COMM,0) from emp;

--分页
--1. 生成rownum

select * from emp;    --生成rownum
--2. 进行第一次筛选   <最大值
select a.*,rownum from (select * from emp)a  where rownum<=15;  --注意a表示查询结果的别名

--3. 进行第二次筛选
select * from (select a.*, rownum as rn  from (select * from emp) a where rownum<=10) where rn>5; 
--注意:  rn指的是子查询的结果集中的rownum的别名，如果不用上次查询出来的rownum,则会从新生成rownum。此时rownum 1=6 恒不成立，无法实现其功能。

--利用一个emp表来创建一个新表newemp3
--语法: CREATE TABLE <new_table_name> AS
--          SELECT column_names FROM <old_table_name>;
create table newemp4 as select * from emp;       --复制emp表的结构与数据到新表newemp3中
--只利用emp表复制结构到newemp5中，而不复制数据
create table newemp5 as select * from emp  where 1=2;    --    1=2    永远为false 

select * from newemp5;

--插入日期,  用到to_date()函数
--函数语法:  to_date('日期字符串','模式')
insert into newemp5 values(6677,'abc','clerk',null,to_date('1-20-2012','MM-DD-YYYY'),3000,2000,null);  --固定时间
insert into newemp5 values(6688,'abdc','clerk',null,sysdate,3000,2000,null);  --系统时间  sysdate
select * from newemp5;

--这个表已经存在
--语法：
--     INSERT INTO <表名> [(cloumn_list)] 
--           SELECT column_names FROM <源表名>;
--案例: 将emp表中的数据追加到newemp5
insert into newemp5 select * from emp;

 

--TCL: 事务处理
--COMMIT - 提交并结束事务处理
--ROLLBACK -  撤销事务中已完成的工作
--SAVEPOINT – 标记事务中可以回滚的点
savepoint m1;
update newemp5 set ename='yz' where empno=6688;
savepoint m2;
delete from newemp5 where empno=6688;
savepoint m3;
rollback to savepoint m1;
commit;     --确认最终结果并提交 .
select * from emp;
--union关键字
update emp set job='MANAGER' where empno=3344;

--union 去掉重复的值
select * from emp where sal>8000
union
select * from emp where JOB='MANAGER';

--union all   去掉重复的值
select * from emp where sal>8000
union all
select * from emp where JOB='MANAGER';

--intersect:   交集,  两个查询都有的
select * from emp where sal>8000
intersect
select * from emp where JOB='MANAGER';

--minus:   减,   第一个查询中有的，而第二个查询中没有的
select * from emp where JOB='MANAGER'
minus
select * from emp where sal>8000
;

--连接多个数据输出
set serveroutput on;    --  开启服务器的输出
select ename||' '||job||' '||sal from emp;  --ename后面接一个空格，后面接job,再连接一个空格，再连接

--日期函数
select sysdate   from dual;    --dual  是oracle提供的一张测试表

--日期函数案例
--1.查找已经入职8个月多的员工
select * from emp where add_months(hiredate,8)<sysdate;   --  2011.6  +8  -> 2012.2

--2. 显示满10年服务年限的员工的姓名和受雇日期
select * from emp where add_months(hiredate,12*10)<sysdate

--3.对于每个员工，显示其加入公司天数
select trunc(sysdate-hiredate) from emp;

--4.找出各月倒数第3天受雇的所有员工. 
select ename,hiredate from emp where hiredate=last_day(hiredate)-2;

--字符函数案例

--1.将所有员工的名字按小写的方式显示
select lower(ename) from emp;
--2.将所有员工的名字按大写的方式显示
select upper(ename) from emp;
--3.显示正好为5个字符的员工的姓名
select ename from emp where length(ename)=5;
--4.显示所有员工姓名的前三个字符
select substr(ename,0,3) from emp;
--5.以首字母大写的方式显示所有员工的姓名
select upper(substr(ename,1,1))||substr(ename,2,length(ename)-1) from emp;
--6.以首字母小写的方式显示所有员工姓名
select lower(substr(ename,1,1))||substr(ename,2,length(ename)-1) from emp;

--数学函数案例
--计算一个月30天的情况下，每人的日薪数,忽略余数 trunc()
select floor(sal/30),ename from emp;

--nvl
select ename,nvl(comm,0) as comm from emp;

--统计函数
--row_number()    排序:   如果排序字段的值相等，序号也不中断
SELECT ename, job, deptno, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS SAL_RANK 
FROM EMP;

--RANK 具有相等值的行排位相同，序数随后跳跃
SELECT deptno, ename, sal, comm, RANK() OVER  (PARTITION BY deptno ORDER BY sal asc, comm) sal_RANK FROM emp;

--DENSE_RANK 具有相等值的行排位相同，序号是连续的
SELECT d.dname, e.ename, e.sal, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS DENRANK
FROM emp e, dept d WHERE e.deptno = d.deptno;

 

--行级锁
select * from emp;

--行级锁
select * from emp where empno=7654 for update;  --其他用户不能操作7369这一行，只能查看
update emp set ename=navy where empno=7654;  --更新用户名
commit;  --提交事务，此时会自动解锁

select * from emp where empno=7654 for update of ename;  --其他用户不能操作7369这一行中的ename列，只能查看

--范围分区
create table student(
       sno number(5) primary key,
       sname varchar(10)
)
partition by range(sno)(  --按学号来分
         partition p1 values less than(10000),--10000以下的在p1分区
         partition p2 values less than(20000),--20000以下的在p1分区
         partition p3 values less than(30000),--30000以下的在p1分区
         partition p4 values less than(maxvalue) --其他值在p4分区
)

insert into student values(00100,'zhou');  --P1
insert into student values(20100,'li');    --P3
insert into student values(30100,'zhang');   --P4
insert into student values(40100,'zhao'); --p4
insert into student values(50100,'hu');   --p4
insert into student values(70100,'liang');  --p4
insert into student values(10100,'zheng'); --p2

--查询所有的记录，从所有分区中查出来
select * from student;

--指定分区，只从指定的分区中查找
select * from student partition(p4);
select * from student partition(p1);

--指定分区，并加条件
select * from student partition(p4) where sno=40100;
select * from student partition(p3) where sno=40100;  --无记录，因为40100不在p3分区，而在p4分区

--散列分区
create table student(
       sno number(5) primary key,
       sname varchar(10)
)
partition by range(sno)(  --按学号来分
         partition p1 values less than(10000),--10000以下的在p1分区
         partition p2 values less than(20000),--20000以下的在p1分区
         partition p3 values less than(30000),--30000以下的在p1分区
         partition p4 values less than(maxvalue) --其他值在p4分区
)

/*
1. 事务:  在oracle中insert,update,delete默认采用的是隐式事务方式.

2. 行级锁:   insert,update,delete,    select....for update    加行级锁

3. 表级锁:
      五大类型:
              行共享：允许用户进行任何操作，禁止排他锁 
  lock table person in row share mode;

  行排他：允许用户进行任何操作，禁止共享锁 
  lock table person in row exclusive mode;

  共享锁：其他用户只能看，不能修改 
  lock table person in share mode;

  共享行排他：比共享锁有更多限制 
  lock table person in share row exclusive mode;

  排他锁：其他用户只能看，不能修改，不能加其他锁 
  lock table person in exclusive mode;

  总结: 保证数据的一致性，安全性

4. 表分区:   提高系统的并发访问性能.

   优点:  提高系统访问效率，便于备份.

   表分区的类型:
       四大类型:
              范围分区:     按某个字段的值范围进行分区
               散列分区:    按某个字段的值的二进制编码的反码来进行分区
              列表分区:     按某个字段的值来分区  ( 性别字段, 类别字段)
              复合分区      综合几种分区
*/

--范围分区:
    PARTITION BY RANGE (列名)
(
  PARTITION 分区名  VALUE LESS THAN(值),
  PARTITION 分区名2 VALUE LESS THAN(值),
  ...
  [PARTITION 分区名n   VALUE LESS THAN(MAXVALUE)]     ---其它值放入到 这个分区中
);

 

--散列分区:
PARTITION BY HASH (column_name)
PARTITIONS number_of_partitions;
--或
PARTITION BY HASH (column_name)
( PARTITION part1 [TABLESPACE tbs1],
  PARTITION part2 [TABLESPACE tbs2],
  ...
  PARTITION partN [TABLESPACE tbsN]
);

--列表分区
PARTITION BY LIST (column_name)
(
  PARTITION part1 VALUES (values_list1),
  PARTITION part2 VALUES (values_list2),
  ...
  PARTITION partN VALUES (DEFAULT)
);

--复合分区
PARTITION BY RANGE (column_name1)
SUBPARTITION BY HASH (column_name2)
SUBPARTITIONS number_of_partitions
(
  PARTITION part1 VALUE LESS THAN(range1),
  PARTITION part2 VALUE LESS THAN(range2),
  ...
  PARTITION partN VALUE LESS THAN(MAXVALUE)
);

/*
5. 分区的管理
  添加分区 : alter table 表名 add partition 分区名 values less than(值);
  删除分区:  alter table 表名 drop partition 分区名;
  截断分区: alter table 表名 truncate partition 分区名;
  合并分区:  alter table 表名 merge partitions 分区名1, 分区名2 into partition 分区名
  拆分分区:  alter table 表名 split partition 分区名 at(值) into (partition 新分区名,partition 新分区名2)
*/