SQL学习-存储数据

1.创建存储

DELIMITER $$

CREATE PROCEDURE get_clients()

BEGIN

        SELECT * FROM clients; (BEGIN中最后一行要加;)

END $$

DELIMITER ;

获取存储数据

CALL get_clients()

简便创建存储方式：右键Stored Procedures→Create→Apply

2.删除存储

DROP PROCEDURE IF EXISTS get_clients (加上IF EXISTS更加安全不易出错)

3.加参数

DELIMITER $$

CREATE PROCEDURE get_clients_by_state

(

        state CHAR(2)  （多参数用，隔开）

)

BEGIN

        SELECT * FROM clients c

        WHERE c.state = state;

END $$

DELIMITER ;

获取存储数据

CALL get_clients_by_state('参数')

4.加默认值

DELIMITER $$

CREATE PROCEDURE get_clients_by_state

(

        state CHAR(2)  

)

BEGIN

        IF state is NULL THEN 

                SET state ='CA';

        END IF;

        SELECT * FROM clients c

        WHERE c.state = state;

END $$

DELIMITER ;

BEGIN

        SELECT * FROM clients c

        WHERE c.state = IFNULL (state, c.state); （c.state = c.state返回所有值）

END $$

5.更新数据(参数验证)

CREATE PROCEDURE meke_payment

(

        invoice_id INT,

        payment_amount DECIMAL(9,2),

        payment_date DATE

)

BEGIN

        IF payment_amount <= 0 THEN

        SIGNAL SQLSTATE '22003' SET MASSAGE_TEXT = 'Invalid payment amount';

        UPDATE  invoice i

        SET

                i.payment_total = payment_amount,

                i.payment_date = payment_date,

                WHERE i.invoice_id = invoice_id;

END $$

6.触发器

DELIMITER $$

CREATE TRIGGER payments_after_insert （用表payments更新表invoices）                                                   payments_after_delete

        AFTER INSERT ON payments

        FOR EACH ROW

BEGIN 

        UPDATE invoices

        SET payment_total = payment_total + new.amout （减去数据用 - OLD.amount

        WHERE invoice_id = new.invoice_id; （减去数据用 OLD.invoice_id

END $$

DELIMITER ;

改变payments中数据：

INSTER INTO payments

VALUES(, , , )