# CREATE VIEW
CREATE VIEW total_order AS
SELECT
lo.loan_order_id,
lo.submit_time,
lo.loan_money
FROM t_loan_order lo
WHERE date(lo.submit_time) >= '2018-11-01'
SELECT * FROM total_order WHERE loan_order_id in (226855, 226861)
DROP VIEW total_order # DELETE VIEW
# 不能创建包含子查询的视图,解决方法是分成多个视图然后联结
# E.G
CREATE VIEW v1 AS SELECT # 视图1
loan_order_id,
loan_money
FROM
t_loan_order
WHERE
submit_time >= '2018-11-30'
AND order_status = 60
CREATE VIEW v2 AS SELECT # 视图2
lci.loan_order_id,
lci.claims_status
FROM
t_loan_claims_info lci
LEFT JOIN t_loan_order lo on lci.loan_order_id=lo.loan_order_id
WHERE lo.submit_time >= '2018-11-30'
AND order_status = 60
SELECT # 联结视图
v1.*,
v2.claims_status
FROM v1, v2
WHERE v1.loan_order_id=v2.loan_order_id
# CREATE PROCEDURE
# CREATE PROCEDURE can't show data
CREATE PROCEDURE test_procedure() # 无参数
BEGIN
SELECT
AVG(loan_money)
FROM t_loan_order
WHERE date(submit_time) >= '2018-11-01';
END;
CALL test_procedure() # 调用过程
DROP PROCEDURE test_procedure # DELETE PROCEDURE
# 将存储过程返回给一个特定的变量,并调用它
CREATE PROCEDURE test (
OUT Avg_Loan_Money DECIMAL (8, 2),
OUT User_Num DECIMAL (8, 2),
OUT Total_Money DECIMAL (8, 2)
)
BEGIN
SELECT AVG(loan_money) INTO Avg_Loan_Money
FROM t_loan_order
WHERE
submit_time >= '2018-11-30'
AND order_status = 60; # 每一条语句结束后加分号
SELECT
count(*) INTO User_Num
FROM
t_loan_order
WHERE
submit_time >= '2018-11-30'
AND order_status = 60;
SELECT
sum(loan_money) INTO Total_Money
FROM
t_loan_order
WHERE
submit_time >= '2018-11-30'
AND order_status = 60;
END;
# 调用过程
CALL test(@Avg_Loan_Money,
@User_Num,
@Total_Money);
# 展示变量
SELECT @Avg_Loan_Money
SELECT
@Avg_Loan_Money as 'Avg_Loan_Money',
@User_Num as 'User_Num',
@Total_Money as 'Total_Money'
SELECT # 运用变量
FLOOR(loan_money-@Avg_Loan_Money) as 'test'
FROM t_loan_order
WHERE
submit_time >= '2018-11-30'
AND order_status = 60;
SELECT # 变量相互运算
(a1 + a2+ a3) as 'sum'
FROM
(SELECT
@Avg_Loan_Money as 'a1',
@User_Num as 'a2',
@Total_Money as 'a3') t2
# 未完待续...