oracle程序员我爱编程

Oracle专题三之Oracle基础操作、DDL/DCL/DML

2016-11-13  本文已影响208人  谜碌小孩

Oracle基础操作

常用函数

create table shopping(u_id  int,goods varchar2(100),num int);
insert into shopping values(1,'苹果',2);
insert into shopping values(2,'梨子',5);
insert into shopping values(1,'西瓜',4);
insert into shopping values(3,'葡萄',1);
insert into shopping values(3,'香蕉',1);
insert into shopping values(1,'橘子',3);
commit;
SELECT * FROM shopping;
select u_id, wmsys.wm_concat(goods) goods_sum  from shopping  group by u_id;
select u_id, wmsys.wm_concat(goods || '(' || num || '斤)' ) goods_sum  from shopping  group by u_id ;
select u_id,LISTAGG(goods) WITHIN GROUP (ORDER BY u_id) goods_sum  from shopping  group by u_id;
select u_id, LISTAGG(goods || '(' || num || '斤)' ) WITHIN GROUP (ORDER BY goods) goods_sum from shopping group by u_id ;

 select earnmonth 月份,
       area 地区,
       sname 打工者,
       personincome 收入,
       rank() over(partition by earnmonth, area order by personincome desc) 排名
  from earnings;

(6)dense_rank() over开窗函数
按照月份、地区，求打工收入排序2

select earnmonth 月份,
       area 地区,
       sname 打工者,
       personincome 收入,
       dense_rank() over(partition by earnmonth, area order by personincome desc) 排名
  from earnings;

(7)row_number() over开窗函数
按照月份、地区，求打工收入排序3

select earnmonth 月份,
       area 地区,
       sname 打工者,
       personincome 收入,
       row_number() over(partition by earnmonth, area order by personincome desc) 排名
  from earnings;

rank,dense_rank,row_number的区别：
结果集中如果出现两个相同的数据，那么rank会进行跳跃式的排名，
比如两个第二，那么没有第三接下来就是第四；
但是dense_rank不会跳跃式的排名，两个第二接下来还是第三；
row_number即使两个数据相同，排名也不一样。
(8)sum累计求和
根据月份、地区求出各个打工者收入总和，按照收入由少到多排序

select earnmonth 月份,
       area 地区,
       sname 打工者,
       sum(personincome) over(partition by earnmonth, area order by personincome) 总收入
  from earnings;

(9)max，min，avg和sum函数综合运用
按照月份和地区求打工收入最高值，最低值，平均值和总额

  select distinct earnmonth 月份,
                area 地区,
                max(personincome) over(partition by earnmonth, area) 最高值,
                min(personincome) over(partition by earnmonth, area) 最低值,
                avg(personincome) over(partition by earnmonth, area) 平均值,
                sum(personincome) over(partition by earnmonth, area) 总额
  from earnings;

(10)lag和lead函数
lag和lead函数可以在一次查询中取出某个字段的前N行和后N行的数据（可以是其他字段的数据，比如根据字段甲查询上一行或下两行的字段乙）
求出每个打工者上个月和下个月有没有赚钱(personincome大于零即为赚钱)

 select earnmonth 本月,
       sname 打工者,
       lag(decode(nvl(personincome, 0), 0, '没赚', '赚了'), 1, 0) over(partition by sname order by earnmonth) 上月,
       lead(decode(nvl(personincome, 0), 0, '没赚', '赚了'), 1, 0) over(partition by sname order by earnmonth) 下月
  from earnings;

create table TEST_TB_GRADE 
( 
ID NUMBER(10) not null, 
USER_NAME VARCHAR2(20 CHAR), 
COURSE VARCHAR2(20 CHAR), 
SCORE FLOAT
);
insert into TEST_TB_GRADE values(1,'michael','语文',78);
insert into TEST_TB_GRADE values(2,'michael','数学',95);
insert into TEST_TB_GRADE values(3,'michael','英语',81);
insert into TEST_TB_GRADE values(4,'xiaoxiao','语文',97);
insert into TEST_TB_GRADE values(5,'xiaoxiao','数学',78);
insert into TEST_TB_GRADE values(6,'xiaoxiao','英语',91);
insert into TEST_TB_GRADE values(7,'zhangsan','语文',80);
insert into TEST_TB_GRADE values(8,'zhangsan','数学',55);
insert into TEST_TB_GRADE values(9,'zhangsan','英语',75);
insert into TEST_TB_GRADE values(10,'lisi','语文',87);
insert into TEST_TB_GRADE values(11,'lisi','数学',65);
insert into TEST_TB_GRADE values(12,'lisi','英语',75);
commit;
SELECT * FROM TEST_TB_GRADE;

行转列

select t.user_name, 
sum(decode(t.course, '语文', score,null)) as CHINESE, 
sum(decode(t.course, '数学', score,null)) as MATH, 
sum(decode(t.course, '英语', score,null)) as ENGLISH 
from test_tb_grade t 
group by t.user_name 
order by t.user_name;

select t2.SCORE_GP, 
  sum(decode(t2.course, '语文', COUNTNUM,null)) as CHINESE, 
  sum(decode(t2.course, '数学', COUNTNUM,null)) as MATH, 
  sum(decode(t2.course, '英语', COUNTNUM,null)) as ENGLISH 
from ( 
  select t.course, 
         case when t.score  <60 then '00-60'
              when t.score >=60 and t.score <80  then '60-80'
              when t.score >=80 then '80-100' end as SCORE_GP, 
         count(t.score) as COUNTNUM 
  FROM test_tb_grade t 
  group by t.course,  
        case when t.score  <60  then '00-60'
              when t.score >=60 and t.score <80  then '60-80'
              when t.score >=80 then '80-100' end
  order by t.course ) t2 
group by t2.SCORE_GP 
order by t2.SCORE_GP;

列转行

CREATE TABLE TEST_TB_GRADE2 AS
select t.user_name USER_NAME, 
sum(decode(t.course, '语文', score,null)) as CN_SCORE, 
sum(decode(t.course, '数学', score,null)) as MATH_SCORE, 
sum(decode(t.course, '英语', score,null)) as EN_SCORE 
from test_tb_grade t 
group by t.user_name 
order by t.user_name;
commit;
SELECT * FROM TEST_TB_GRADE2;

select user_name, 'CN_SCORE' COURSE, CN_SCORE as SCORE from test_tb_grade2
union
select user_name, 'MATH_SCORE' COURSE, MATH_SCORE as SCORE  from test_tb_grade2
union
select user_name, 'EN_SCORE' COURSE, EN_SCORE as SCORE  from test_tb_grade2
 order by user_name, COURSE;

DCL

    1、创建用户test2，密码也是test2（记得最有以分；号结束）：
        create user test2 identified by test2;
    2、给test2授权：create session;（允许用户登陆Oracle）：
        grant create session to test2;
    3、给test2分配创建表的权限；
        grant create table to test2;
    4、给test2分配表空间的使用权限；
        grant unlimited tablespace to test2;
    5、收回test2用户的create session权限
       revoke create session  from test2;   
    6、收回test2用户的create table、unlimited tablespace权限
       revoke create table,unlimited tablespace  from test2;

DDL

create table t_user
(
      userid number(30),
      username varchar2(20),
      age number(3),
      sex varchar(2)
);

DML

约束

(1)如果某个约束只作用于单独的字段，既可以在字段级定义约束，也可以在表级定义约束；但如果某个约束将作用于多个字段，必须在表级定义约束。
(2)oracle中的约束通过名称来进行识别。在定义约束时可以通过constraint关键字为约束命名。

create table person
 (
       p_id int primary key,        --定义该字段为主键约束【字段级约束】
       p_name varchar2(20),
       p_age int
 );

create table person2
(
       p_id int constraint p_pk2 primary key,    --定义该字段为主键约束，并指定约束名字【字段级约束】
       p_name varchar2(20),
       p_age int
);

create table person3
(
       p_id int,
       p_name varchar2(20),
       p_age int,
       constraint p_pk3 primary key (p_id,p_name)--定义复合主键,并指定名字【表级约束】
);

create table person4
(
       p_id int,
       p_name varchar2(20) not null, --定义该列的非空约束
       p_age int 
 );

create table person2
(
       p_id int,
       p_name varchar2(20) constraint gulaijing unique not null, --同时定义唯一和非空约束
       p_age int
);

create table person
(
       p_id int,
       p_name varchar2(20),
       p_age int check(p_age > 20)  
);
上一篇 下一篇

猜你喜欢

热点阅读