SQL強化(一)保險業務

標籤：邏輯   start   也會   客戶   user   tom   超過   check   src   

保險業務 :

表結構 : 

 

sql語句 : 
/*1. 根據投保人電話查詢出
投保人 姓名 社會安全號碼 所有保單 編號 險種 繳費類型*/
SELECT
t2.cust_name,
t2.idcard,
t4.pro_id,
t5.pay_type_name,
t6.protype_name
FROM
contact t1, -- 聯絡表
customer t2, -- 客戶表
holder t3, --
product t4,
pay_type t5,
protype t6
WHERE
t1.contact_text = ‘15987654565‘
AND t1.cust_id = t2.cust_id
AND t3.cust_id = t2.cust_id
AND t3.pro_id = t4.pro_id
AND t4.pay_type = t5.pay_type_id
AND t4.pro_type = t6.protype_id

/*
2. 根據保單號 查詢 保單號 保單險種 
投保人 姓名 社會安全號碼 被保人 姓名社會安全號碼 和受益人的姓名身份證
*/
SELECT
t1.pro_id,
t1.pro_type,
c1.cust_name,
c1.idcard,
c2.cust_name,
c2.idcard,
c3.cust_name,
c3.idcard
FROM
product t1, 
holder t2,
insurer t3,
benefit t4,
customer c1,
customer c2,
customer c3
WHERE
t1.pro_id = ‘1100012313441122‘
AND t1.pro_id = t2.pro_id
AND t2.cust_id = c1.cust_id
AND t1.pro_id = t3.pro_id
AND t3.cust_id = c2.cust_id
AND t1.pro_id = t4.pro_id
AND t4.bene_id = c3.cust_id

/*
4. 找到 所有 投保人 被保人 受益人都是同一個人的 保單
*/
select * from product t1 ,holder t2, insurer t3, benefit t4
where t1.pro_id = t2.pro_id
and t1.pro_id = t3.pro_id
and t1.pro_id = t4.pro_id
and t2.cust_id = t3.cust_id
and t3.cust_id = t4.bene_id

/*
6 找到 各險種中 保費最多的 前三張保單 （分組後排序）*/
select * from(
select row_number() over(partition by pro_type order by premium desc) rn ,
product.* from product 
) where rn<=3

//============================================

-- 關於時間 的比較
select * from product where pro_start_date < to_date(‘2017-04-10 23:12:12‘,‘yyyy-mm-dd hh24:mi:ss‘)
/* 關於時間和字串的轉換

mm 月份 注意不是 MM
mi 分鐘

hh24 24小時的小時
hh 12小時的小時

字串 不能超過 格式的長度 
*/
視圖
一 : 什麼是視圖 view

視圖(view)，也稱虛表, 資料不佔用物理空間。
視圖只有邏輯定義。每次使用的時候,只是重新執行SQL。
一個視圖 可以插敘多張表 查詢的每張表 叫做基表
修改視圖的資料 基表的資料也會改變

二 : 試圖的作用 
1. 簡化查詢
2. 規避敏感列
3. 簡化許可權管理 增加安全性

三: 建立視圖 使用者需要擁有 create view 許可權 grant create view to USERNAME; 
-- 授權時 必須使用管理員權限

四 文法:
create [ or replace ] [ force ] view [schema.]view_name
[ (column1,column2,...) ]
as 
select ...
[ with check option ] [ constraint constraint_name ]
[ with read only ];
tips:
1 or replace: 如果存在同名的視圖, 則使用新視圖"替代"已有的視圖
2 force: "強制"建立視圖,不考慮基表是否存在,也不考慮是否具有使用基表的許可權
3 column1,column2,...：視圖的列名, 列名的個數必須與select查詢中列的個數相同; 
如果select查詢包含函數或運算式, 則必須為其定義列名.此時, 既可以用column1, column2指定列名, 也可以在select查詢中指定列名.
4 with check option: 指定對視圖執行的dml操作必須滿足“視圖子查詢”的條件即,對通過視圖進行的增刪改操作進行"檢查",
要求增刪改操作的資料, 必須是select查詢所能查詢到的資料,否則不允許操作並返回錯誤提示. 預設情況下, 
在增刪改之前"並不會檢查"這些行是否能被select查詢檢索到. 
5 with read only：建立的視圖只能用於查詢資料, 而不能用於更改資料.

五 : 樣本 : 
create view customer_contact_inner
as 
select t1.cust_name custname,
t1.cust_id custid,
t1.idcard idcard,
t1.age age,
t1.sex sex,
t1.brith birth,
t2.contact_type contacttype,
t2.contact_text text
from customer t1, contact t2
where t1.cust_id = t2.cust_id

select * from customer_contact_inner where custname = ‘張效民‘
這樣就減少了查詢的邏輯語句

SQL強化(一)保險業務