mysql

CREATE DATABASE DEMO02；
USE DEMO01
DELIMITER //
CREATE TABLE book(
bookid INT PRIMARY KEY AUTO_INCREMENT COMMENT 'bookid',
ISBN VARCHAR(20) COMMENT 'ISBN',
bookname CHAR(10) NOT NULL COMMENT 'bookname',
amount VARCHAR(20) COMMENT 'amounmt'
);
END //
DELIMITER ;

DELIMITER //
CREATE TABLE reader(
readerid INT PRIMARY KEY AUTO_INCREMENT COMMENT 'readerid',
phone VARCHAR(20)  NOT NULL COMMENT 'phone',
readername CHAR(10) NOT NULL COMMENT 'bookname'
);
END //
DELIMITER ;

DELIMITER //
CREATE TABLE borrowtable(
borrowid INT  PRIMARY KEY AUTO_INCREMENT COMMENT 'id',
id INT   COMMENT 'bookid',
readerid INT   COMMENT 'readerid',
borrowTime datetime NOT NULL COMMENT 'borrowTime',
backTime   VARCHAR(20) COMMENT 'backTime',
ifback SET('YES','NO') DEFAULT 'YES' COMMENT 'ifback',
FOREIGN KEY(id) REFERENCES book(id),
FOREIGN KEY(readerid) REFERENCES reader(readerid)
);
END //
DELIMITER ;

8--4
DROP TRIGGER IF EXISTS tg_goods_ins;
delimiter //
CREATE TRIGGER tg_goods_ins
AFTER INSERT ON item
FOR EACH ROW
BEGIN
UPDATE goods
SET amount=amount-new.quantity
WHERE id=new.goods_id;
END // 
delimiter ；

7--13
set global log_bin_trust_function_creators=1;
delimiter //
CREATE FUNCTION staffer_search(sid INT)
RETURNS VARCHAR(10)
BEGIN
DECLARE sname VARCHAR(10);
SELECT staff_name INTO sname FROM staffer WHERE id=sid;
IF ISNULL(sname) THEN
RETURN '无人';
ELSE
RETURN sname;
END IF;
END //
DELIMITER ;
SET @sname =staffer_search(6);
SELECT @sname,staffer_search(4);

7--11

delimiter //
CREATE PROCEDURE p_count(IN id INT,OUT n INT)
BEGIN
SELECT count(*) INTO n FROM staffer WHERE dept_id=id;
END //
delimiter ;
CALL p_count(1,@a);
SELECT @a AS '1号部门员工数';

6-13
CREATE VIEW v_staffer
AS SELECT dept_name,staff_name,sex,phone FROM staffer
INNER JOIN department ON  staffer.dept_id=department.id;
 

INSERT INTO `customer` VALUES (1, '10', '10', 'he', 'F', '2020-02-04', 'art', 0.00, 0.00, NULL, '', '{\"city\": \"广州\", \"road\": \"天天寿路\", \"province\": \"广东省\"}');