木木zszhydt

CREATE DATABASE staff;
USE staff
CREATE TABLE employee(
id INT NOT NULL AUTO_INCREMENT,
userName VARCHAR(255),
birthDate DATE,
idCard VARCHAR(255),
loginName VARCHAR(255),
PASSWORD VARCHAR(255),
mobile VARCHAR(255),
email VARCHAR(255),
deptId INT,
LEVEL INT,
avatar BLOB,
remark TEXT,
PRIMARY KEY(id)
);

REATE TABLE dept(
id INT NOT NULL AUTO_INCREMENT,
deptName VARCHAR(255),
manageId INT,
remark VARCHAR(255),
PRIMARY KEY(id)
);

CREATE TABLE payroll(
id INT NOT NULL AUTO_INCREMENT,
empId INT,
baseSalary DOUBLE,
actualSalary DOUBLE,
bonus DOUBLE,
deductMoney DOUBLE,
grantDate DATE,
PRIMARY KEY(id)
);

CREATE TABLE ask_leave(
id INT NOT NULL AUTO_INCREMENT,
empId INT,
leaveReason TEXT,
beginDate DATE,
endDate DATE,
submitDate DATE,
auditId INT,
STATUS INT,
auditOpinion TEXT,
PRIMARY KEY(id)
);

DELIMITER $$
CREATE
       PROCEDURE `staff`.`insert_employee`( IN id INT, IN userName VARCHAR(255),
IN birthDate DATE,
IN idCard VARCHAR(255),
IN loginName VARCHAR(255),
IN PASSWORD VARCHAR(255),
IN mobile VARCHAR(255),
IN email VARCHAR(255),
IN deptId INT,
LEVEL INT,
IN avatar BLOB,IN remark TEXT)
   
    BEGIN
          INSERT INTO `employee` VALUES(NULL,userName,birthDate,idCard,
loginName,PASSWORD,mobile,email,deptId,LEVEL,avatar,remark);
    END$$
DELIMITER ;
3、CALL `insert_employee`(NULL,'aa','2001-1-
1','41032222222222222','abc','1234','123445555','123@qq.com',1,1,NULL,NULL);写5条即可

4、
DELIMITER $$
CREATE
    TRIGGER `staff`.`beforeinsert_payloyee` BEFORE  INSERT 
    ON `staff`.`payroll`
    FOR EACH ROW BEGIN
       SET  new.`actualSalary`=new.`baseSalary`+new.`bonus`-new.`deductMoney`;
    END$$
DELIMITER ;
DELIMITER $$
CREATE
    TRIGGER `staff`.`before_update_payloyee` BEFORE  UPDATE 
    ON `staff`.`payroll`
    FOR EACH ROW BEGIN
   SET  new.`actualSalary`=new.`baseSalary`+new.`bonus`-new.`deductMoney`;
    END$$
DELIMITER ;

5、
CREATE INDEX index_userName ON `employee`(`userName`);
6、
CREATE VIEW  v_employee_dept_payroll
AS
SELECT `userName`,`deptName`,`baseSalary`,`actualSalary`,`bonus`,`deductMoney`
FROM`dept`,`employee`,`payroll`
WHERE `dept`.`id`=`employee`.`deptId` AND `employee`.`id`=`payroll`.`empId`
7、
DELIMITER $$
CREATE
    TRIGGER `staff`.`insert_ask_leave` BEFORE  INSERT 
    ON `staff`.`ask_leave`
    FOR EACH ROW BEGIN
       DECLARE manangeID1 INT;
       SELECT `manageId` INTO  manangeID1 FROM `dept` WHERE `id`=(
              SELECT `deptId` FROM `employee` WHERE `id`=new.`empId`);
        SET new.`auditId`=manangeID1;
    END$$
DELIMITER ;

1）查询作者为“刘刚”的图书的数量。 SELECT COUNT(*) AS 数量  FROM `book`  WHERE `author`='刘刚'

2）查询出版单位为人民邮电出版社的图书明细。 SELECT `id`,`book_name`,`author`,`price`,`cd`,`publish`,`book_classify_id`,`account`,`isbn`,`create_time`,`remark` FROM `book` WHERE `publish`='人民邮电出版社';

3）检索2024年3月的借阅图书的读者人数。 SELECT COUNT( DISTINCT `user_id`) FROM `book_borrow`  WHERE YEAR(`borrow_time`)='2024' AND MONTH(`borrow_time`)='3';

4）图书管理系统里需要新增一本刚采购的医药方面的书籍：书名为《疑难杂病临证手册（第2版）》、作 者为余孟学、定价为158元、出版社为河南科技出版社、ISBN编号为9787534989230。要把它添加到图书 表book里，图书分类选择“R 医药、卫生”。 INSERT INTO`book`VALUES('5','疑难杂病临证手册（第2版）','余孟学','158','1','河南科技出版 社','4','1000','9787534989230',NOW(),NULL);

5）查询用户小影的借书记录，包括用户姓名、借阅图书名称、出版社、借书时间、归还时间。 SELECT u.user_name,b.book_name,b.publish,w.borrow_time,w.return_time FROM USER u, book b, book_borrow w WHERE w.book_id = b.id AND w.user_id = u.id AND u.login_name = '小影';

6）针对用户表user、图书表book、图书借阅表book_borrow建立一个用户借阅图书信息查询视图 user_book_borrow_view，查询用户编号、登录名称、姓名、图书名称、出版社、借阅时间、归还时间。 CREATE OR REPLACE VIEW user_book_borrow_view AS SELECT u.id,u.login_name,u.user_name,b.book_name,b.publish,w.borrow_time,w.return_time FROM USER u, book b, book_borrow w WHERE w.book_id = b.id AND w.user_id = u.id ;

7）按book_name字段建立图书索引。索引名为：booknameindex; CREATE INDEX booknameindex ON  `book`(`book_name`)

8）按publish，book_name字段建立图书表复合索引。索引名为：bookpublishnameindex ; CREATE INDEX bookpublishnameindex ON  `book`(`publish`,`book_name`)

9）更新小刚用户的部门为运维部。 UPDATE `user` SET `dept_id`=(SELECT `id` FROM `dept` WHERE `dept_name`='运维部') WHERE `user_name`='小刚'

10）删除图书名称包含：“经济学”的所有图书 delete from `book` where `book_name` like '%经济学%'

11)删除用户小影的全部借书记录。 DELETE FROM `book_borrow` WHERE`user_id`=(SELECT `id` FROM `user` WHERE `user_name`='小影')

12）查询所有用户丢失的图书名称。 SELECT `user_name`,`book_name` FROM `book`,`book_borrow`,`user`,`book_lose` WHERE `book_lose`.`borrow_id`=`book_borrow`.`id` AND `book_borrow`.`user_id`=`user`.id AND `book_borrow`.`book_id`=`book`.`id`