图书管理系统2

1）查询作者为“刘刚”的图书的数量。

SELECT COUNT(*) FROM book WHERE author = '刘刚';

    

2）查询出版单位为人民邮电出版社的图书明细。

SELECT * 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 (book_name, author, price, publish, isbn, book_classify_id) VALUES ('疑难杂病临证手册（第2版）', '余孟学', 158, '河南科技出版社', '9787534989230', (SELECT id FROM book_classify WHERE book_classify_name = 'R 医药、卫生'));

5）查询用户小影的借书记录，包括用户姓名、借阅图书名称、出版社、借书时间、归还时间。

SELECT u.user_name, b.book_name, b.publish, bb.borrow_time, br.return_time FROM user u JOIN book_borrow bb ON u.id = bb.user_id JOIN book b ON bb.book_id = b.id LEFT JOIN book_return br ON bb.id = br.borrow_id WHERE u.user_name = '小影';

6）针对用户表user、图书表book、图书借阅表book_borrow建立一个用户借阅图书信息查询视图user_book_borrow_view，查询用户编号、登录名称、姓名、图书名称、出版社、借阅时间、归还时间。

CREATE VIEW user_book_borrow_view AS SELECT u.id AS user_id, u.login_name, u.user_name, b.book_name, b.publish, bb.borrow_time, br.return_time FROM user u JOIN book_borrow bb ON u.id = bb.user_id JOIN book b ON bb.book_id = b.id LEFT JOIN book_return br ON bb.id = br.borrow_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 b.book_name FROM book b JOIN book_lose bl ON b.id = bl.book_id;

1. 创建数据库和表：

CREATE DATABASE staff;

USE staff;

CREATE TABLE employee (

  id INT PRIMARY KEY,

  userName VARCHAR(50),

  birthDate DATE,

  idCard VARCHAR(18),

  loginName VARCHAR(50),

  password VARCHAR(50),

  mobile VARCHAR(11),

  email VARCHAR(50),

  deptId INT,

  level INT,

  avatar VARCHAR(255),

  remark VARCHAR(255)

);

CREATE TABLE dept (

  id INT PRIMARY KEY,

  deptName VARCHAR(50),

  managerId INT

);

CREATE TABLE payroll (

  id INT PRIMARY KEY,

  empId INT,

  baseSalary DECIMAL(10, 2),

  actualSalary DECIMAL(10, 2),

  bonus DECIMAL(10, 2),

  deductMoney DECIMAL(10, 2),

  grantDate DATE

);

CREATE TABLE ask_leave (

  id INT PRIMARY KEY,

  empId INT,

  leaveReason VARCHAR(255),

  beginDate DATE,

  endDate DATE,

  submitDate DATE,

  auditId INT,

  status INT,

  auditOpinion VARCHAR(255)

);

  

2.存储过程：

DELIMITER //

CREATE PROCEDURE insert_employee(

  IN p_id INT,

  IN p_userName VARCHAR(50),

  IN p_birthDate DATE,

  IN p_idCard VARCHAR(18),

  IN p_loginName VARCHAR(50),

  IN p_password VARCHAR(50),

  IN p_mobile VARCHAR(11),

  IN p_email VARCHAR(50),

  IN p_deptId INT,

  IN p_level INT,

  IN p_avatar VARCHAR(255),

  IN p_remark VARCHAR(255)

)

BEGIN

  INSERT INTO employee (id, userName, birthDate, idCard, loginName, password, mobile, email, deptId, level, avatar, remark)

  VALUES (p_id, p_userName, p_birthDate, p_idCard, p_loginName, p_password, p_mobile, p_email, p_deptId, p_level, p_avatar, p_remark);

END //

DELIMITER ;

   

3.插入工记录：

CALL insert_employee(1, '张三', '1990-01-01', '110101199001010000', 'zhangsan', '123456', '13800000000', 'zhangsan@example.com', 1, 1, 'avatar1.jpg', '备注1');

CALL insert_employee(2, '李四', '1991-02-02', '110101199102020000', 'lisi', '123456', '13800000001', 'lisi@example.com', 1, 2, 'avatar2.jpg', '备注2');

CALL insert_employee(3, '王五', '1992-03-03', '110101199203030000', 'wangwu', '123456', '13800000002', 'wangwu@example.com', 2, 3, 'avatar3.jpg', '备注3');

CALL insert_employee(4, '赵六', '1993-04-04', '110101199304040000', 'zhaoliu', '123456', '13800000003', 'zhaoliu@example.com', 2, 4, 'avatar4.jpg', '备注4');

CALL insert_employee(5, '孙七', '1994-05-05', '110101199405050000', 'sunqi', '123456', '13800000004', 'sunqi@example.com', 3, 5, 'avatar5.jpg', '备注5');

4.触发器：

DELIMITER //

CREATE TRIGGER update_payroll_actualSalary

AFTER INSERT ON payroll

FOR EACH ROW

BEGIN

  UPDATE payroll

  SET actualSalary = baseSalary + bonus - deductMoney

  WHERE id = NEW.id;

END //

DELIMITER ;

  

5.索引：

CREATE INDEX index_userName ON employee (userName);

6.视图：

CREATE VIEW v_employee_dept_payroll AS

SELECT e.userName, d.deptName, p.baseSalary, p.actualSalary, p.bonus, p.deductMoney

FROM employee e

JOIN dept d ON e.deptId = d.id

JOIN payroll p ON e.id = p.empId;

7.触发器：

DELIMITER //

CREATE TRIGGER insert_ask_leave

BEFORE INSERT ON ask_leave

FOR EACH ROW

BEGIN

  DECLARE managerId INT;

  SELECT managerId INTO managerId FROM dept WHERE id = (SELECT deptId FROM employee WHERE id = NEW.empId);

  SET NEW.auditId = managerId;

END //

DELIMITER ;

8.备份数据库：

mysqldump -u root -p --databases staff > staff_sjk.sql

上传至服务器：

将生成的staff_sjk.sql文件上传至服务器