数据库小笔记

存储过程

一次性可操作多条语句，方便重复调用

• 优点:简单(一次封装，多次调用)，安全(限制对基础数据的访问，减少错误)，高性能(比执行单条sql快)
• 缺点:存储过程编写复杂;需要有创建存储过程的权限
• 调用存储过程：
  call productPrice(@avgPrice);@avgPrice是返回值
  call productPrice();没有传入参数也需要加括号
• 创建存储过程：
  DELIMITER //
  CREATE PROCEDURE productPrice()
  BEGIN
  SELECT Avg(price) as avgPrice
  FROM products;
  END //
  ()里可以写传入参数，BEGIN和END限制存储过程体
  DELIMITER //是告诉命令行实用程序使用//作为新的语句分隔符
• 删除存储过程
  存储过程创建后，一般保存在服务器上
  DROP PROCEDURE productPrice;没有括号，存储过程不存在会报错，使用DROP PROCEDURE productPrice IF EXISTS;则不会报错

sql题

1. 查询年龄18-20岁的人员，并且每个年龄的人数需要大于2个人

--查询年龄18-20岁的人员，并且每个年龄的人数需要大于2个人
--并且才是需要重点处理的条件
select *
  from User_temp b
 where b.age in (select a.age
                   from (select t.age age
                           from User_temp t
                          where t.age between 18 and 20
                          group by t.age
                         having count(t.age) > 2) a);
-- Create table
create table USER_TEMP
(
  id   VARCHAR2(20) not null,
  name VARCHAR2(32),
  age  NUMBER
)
insert into User_temp (ID, NAME, AGE) values ('18404', 'zhao', 18);
insert into User_temp (ID, NAME, AGE) values ('18405', 'qian', 19);
insert into User_temp (ID, NAME, AGE) values ('18406', 'sun', 20);
insert into User_temp (ID, NAME, AGE) values ('18407', 'li', 21);
insert into User_temp (ID, NAME, AGE) values ('18408', 'zhou', 18);
insert into User_temp (ID, NAME, AGE) values ('18409', 'wu', 19);
insert into User_temp (ID, NAME, AGE) values ('18410', 'zhen', 20);
insert into User_temp (ID, NAME, AGE) values ('18411', 'wang', 18);
insert into User_temp (ID, NAME, AGE) values ('18412', 'zhu', 19);
insert into User_temp (ID, NAME, AGE) values ('18413', 'liu', 20);

2. 查询换入换出发票的金额之和

##postgresql写法
select b3.ctr_id_1  合同号,
       b3.rltv_id_1 换入发票号,
       b3.amt_1     换入金额,
       b3.rltv_id_2 换出发票号,
       b3.amt_2     换出金额
  from ((select t1.ctr_id as ctr_id_1,
                string_agg(t1.rltv_id, ',') as rltv_id_1,
                sum(t1.amt) as amt_1
           from rltv_dtl t1
          where t1.rltv_cd = '1'
          group by t1.ctr_id)as b1 left join
        (select t2.ctr_id as ctr_id_2,
                string_agg(t2.rltv_id, ',') as rltv_id_2,
                sum(t1.amt) as amt_2
           from rltv_dtl t2
          where t2.rltv_cd = '2'
          group by t2.ctr_id) as b2 on b1.ctr_id_1 = b2.ctr_id_2) as b3;
##Oracle写法 

select b3.ctr_id_1 as 合同号,
     b3.rltv_id_1 as 换入发票号,
     b3.amt_1   as  换入金额,
     b3.rltv_id_2  as 换出发票号,
     b3.amt_2   as  换出金额
from (select 
            b1.*,b2.* 
      from ((select 
                   t1.ctr_id as ctr_id_1,
                   to_char(wm_concat(t1.rltv_id)) as rltv_id_1,
                   sum(t1.amt) as amt_1
             from hr.rltv_dtl t1
             where t1.rltv_cd = '1' --1表示换入
             group by t1.ctr_id )b1 
             left join(select 
                             t2.ctr_id as ctr_id_2,
                             to_char(wm_concat(t2.rltv_id)) as rltv_id_2,
                             sum(t2.amt) as amt_2
                       from hr.rltv_dtl t2
                       where t2.rltv_cd = '2' --2表示换出
                       group by t2.ctr_id)b2 
             on b1.ctr_id_1 = b2.ctr_id_2)
       )b3;
--以下是oracle错误写法，还需嵌套一层select
/*select b3.ctr_id_1 as 合同号,
       b3.rltv_id_1 as 换入发票号,
       b3.amt_1   as  换入金额,
       b3.rltv_id_2  as 换出发票号,
       b3.amt_2   as  换出金额
  from ((select t1.ctr_id as ctr_id_1,
                to_char(wm_concat(t1.rltv_id)) as rltv_id_1,
                sum(t1.amt) as amt_1
           from hr.rltv_dtl t1
          where t1.rltv_cd = '1' --1表示换入
          group by t1.ctr_id )b1 left join
        (select t2.ctr_id as ctr_id_2,
                to_char(wm_concat(t2.rltv_id)) as rltv_id_2,
                sum(t2.amt) as amt_2
           from hr.rltv_dtl t2
          where t2.rltv_cd = '2' --2表示换出
          group by t2.ctr_id)b2 on b1.ctr_id_1 = b2.ctr_id_2)b3;*/

--create table
create table HR.RLTV_DTL
(
  id      VARCHAR2(20) not null,
  rltv_id VARCHAR2(32),
  ctr_id  VARCHAR2(32),
  amt     NUMBER,
  rltv_cd VARCHAR2(1)
)
insert into hr.RLTV_DTL (ID, RLTV_ID, CTR_ID, AMT, RLTV_CD)
values ('18404', 'FP18404', 'HT18404', 1000, '1');
insert into hr.RLTV_DTL (ID, RLTV_ID, CTR_ID, AMT, RLTV_CD)
values ('18405', 'FP18405', 'HT18404', 2000, '2');
insert into hr.RLTV_DTL (ID, RLTV_ID, CTR_ID, AMT, RLTV_CD)
values ('18406', 'FP18406', 'HT18404', 500, '1');
insert into hr.RLTV_DTL (ID, RLTV_ID, CTR_ID, AMT, RLTV_CD)
values ('18407', 'FP18407', 'HT18405', 2000, '1');
insert into hr.RLTV_DTL (ID, RLTV_ID, CTR_ID, AMT, RLTV_CD)
values ('18408', 'FP18408', 'HT18405', 2500, '2');
insert into hr.RLTV_DTL (ID, RLTV_ID, CTR_ID, AMT, RLTV_CD)
values ('18409', 'FP18409', 'HT18405', 5000, '2');

常用sql总结

1. select 字段 from xxx for update;可以手动添加数据，commit后生效
   【Oracle】在pl/sql中对该表加锁，表示可以对已经存在的数据进行更新
2. 谨慎用char，char(3),存的数据不够三位自动用空格补全，查询时会出问题
3. Oracle序列不存在，sequence文件夹为空；

create sequence seq_users #序列名称
increment by 1 #每次增长多少
start with 1 #从几开始
minvalue 1 #最小值
maxvalue 999999999 #最大值
cycle/no cycle #序列到达最大值之后怎么办，一般去cycle
cache 20 #需要不需要使用缓存，就是一次生成多少个序列的值，如果生成了多个序列值，如果缓存中有数据， 就不需要查询缓存了
order/noorder #获取序列的时候是否按顺序给值。如果多用户一起获取序列的值，使用order可以保证序列值的顺序按访问序列的事件排序。

create sequence seq_users
increment by 1
start with 1
minvalue 1
maxvalue 9999999
order
cache 20
cycle;

查看当前用户的所有序列

#必须以管理员身份登录；sequence_owner必须为大写
select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences where sequence_owner='用户名';

4. int后的数字代表二进制位数，int4就代表0000-1111
   一个字节有8位，int8是一个字节，int16为两个字节。
   varchar (N)中的N指的是该字段最多能存储多少个字符(characters)，不是字节数。
5. datetime和timestamp的区别
   范围不同。
   “datetime” 以’YYYY-MM-DD HH:MM:SS’格式检索和显示DATETIME值。支持的范围为’1000-01-01 00:00:00’到’9999-12-31 23:59:59’
   TIMESTAMP值不能早于1970或晚于2037
   date没有时分秒