MySQL（六）

1、创建视图

CREAT VIEW + 视图名 + AS + 表达式

CREAT OR REPLACE VIEW + 视图名 + AS + 表达式  

CREAT VIEW sales_by_clients AS
SELECT 
    c.client_id,
    c.name,
    SUM(invoice_total) AS total_sale
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id , name

2、删除视图

删除视图
DROP VIEW + 视图名

DROP VIEW sales_by_client

3、可更新视图

视图中没有 DISTINCT 、聚合函数 （MAX、MIN、SUM）、GROUP BY \HAVING 、 UNION 
这个视图就是可更新视图

只有可更新视图才可以进行 插入、更新、删除操作

4、WITH OPTION CHECK 子句

这条子句会防止UPDATE或者DELETE语句将行从视图中删除

5、创建存储过程

存储过程就是存储语句的

创建一个存储过程
DELIMITER $$ (把分隔符由原来的;改成$$)
CREATE PROCEDURE + 名称(参数) + BEGIN + 语句; + END$$
DELIMITER ; (再把分隔符改回来)

调用存储过程
CALL + 存储过程名

 6、删除存储过程

DROP PROCEDURE IF EXISTS + 存储过程名;

 7、存储过程参数

创建一个存储过程
DELIMITER $$ (把分隔符由原来的;改成$$)
CREATE PROCEDURE + 名称(参数) + BEGIN + 语句; + END$$
DELIMITER ; (再把分隔符改回来)

eg:
DELIMITER $$
CREATE PROCEDURE get_department
(
	department_name VARCHAR(2),
    manager_id INT
)
BEGIN
SELECT * FROM departments d
WHERE d.department_name = department_name;
END$$
DELIMITER ;

8、带默认值的参数 

参数为null 返回特定的数据
DELIMITER $$
CREATE PROCEDURE get_employess_by_first_name
(
	first_name VARCHAR(20)
)
BEGIN
IF first_name IS NULL 
THEN SET first_name = 'Steven';
END IF;
SELECT * FROM employees e
WHERE e.first_name = first_name;
END $$
DELIMITER ;

参数为mull 返回所有

DELIMITER $$
CREATE PROCEDURE get_employess_by_first_name
(
	first_name VARCHAR(20)
)
BEGIN 
SELECT * FROM employees e
WHERE e.first_name = IFNULL(first_name,e.first_name) ;
END $$
DELIMITER ;

9、参数验证 

DELIMITER $$
CREATE PROCEDURE get_employess_by_first_name
(
	department_id int,
    employee_id int
) 

BEGIN
    IF department_id  <= 0 THEN
        SIGNAL SQLLSTATE '22003'
            SET MESSAGE_TEXT = '不和规矩的数';
    END IF;
    UPDATAE employee e
    SET 
        i.department_id = department_id ,
    WHERE 
        i.employee_id = employee_id 

END $$
DELIMITER ;

10、输出参数

加入OUT就是输出参数可以不填写
DELIMITER $$
CREATE PROCEDURE get_employess_by_first_name
(
	client_id INT,
    OUT invoices_count INT,
    OUT invoices_count DECIMAL(9,2)
) 

BEGIN
    SELECT COUNT(*),SUM(invoices_total)
    INTO invoices_count ,invoices_count 
    FROM invoices i
    WHERE i.client_id = client_id
        AND payment_total = 0

END $$
DELIMITER ;

11、变量

CREATE PROCEDURE get_risk_factor()
BEGIN
     DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
     DECLARE invoices_total DECIMAL(9,2);
     DECLARE invoices_count INT;

     SELECT COUNT(*),SUM(invoices_total)
     INTO invoices_count , invoices_count 
     FROM invoices;

     SET risk_factor = invoices_total / invoices_count * 5;

     SELECT risk_factor ;
END

12、 函数

删除函数

DROP FUNCTION IF EXISTS get_risk_faction_for_client;

函数只能返回单一值，相比储存过程他们无法返回拥有多行多列的结果集
CREATE FUNCTION get_risk_faction_for_client
(
client_id INT
)
RETURNS INTEGER --确认返回值类型（必须写）
DETERMINIS --确定性 （同一个输入参数结果是相同的《有公式的时候使用》）
READS SQL DATA -- 获取SQL数据（函数中会配置选择语句，可以用于读取一些数据）
MODIFIES SQL DATA --修改SQL语句（函数中有插入、更新、删除函数）
BEGIN
     DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
     DECLARE invoices_total DECIMAL(9,2);
     DECLARE invoices_count INT;

     SELECT COUNT(*),SUM(invoices_total)
     INTO invoices_count , invoices_count 
     FROM invoices i
     WHERE i.client_id = client_id; 

     SET risk_factor = invoices_total / invoices_count * 5;

    RETURN IFNULL(risk_factor , 0 ) ;
END

使用
SELECT 
     client_id,
     name,
     get_risk_faction_for_client
FROM clients