图书管理数据库

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

2)查询出版单位为人民邮电出版社的图书明细。SELECT
id, book name, author` price` `cd` publish, book classify id, account, isbn',`create time`, remarkFROM `book` WHERE `publish`='人民邮电出版社'

3)检索2024年3月的借阅图书的读者人数。
SELECT COUNT( DISTINCT user id ) FROM `book borrowWHERE 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 bbook borrow w WHERE w.book id = b.id AND w.user id = u.id AND ulogin_ name = '小影';

6)针对用户表user、图书表book、图书借阅表book borrow建立一个用户借阅图书信息查询视图user book borrow _view，查询用户编号、登录名称、姓名、图书名称、出版社、借阅时间、归还时间。CREATE OR REPLACE VlEW user book borrow viewAS
SELECT u.id,u.login name,u.user name,b.book name,b.publish,w.borrow time,w.return timeFROM USER u, book b, book borrow w
WHERE w.book id = b.id AND w.user id = u.id ;

7)按book name字段建立图书系引。索引名为:booknameindex;
CREATEINDEX 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 loseWHERE `book lose` borrow id`=`book borrow .id`AND `book borrow .user id`=`user id ANDbook borrow. book id`= `book.id`

员工管理数据库系统

员工表 employee(员工编号id,.姓名userName,出生日期birthDate,身份证号idCard,登录名称loginName,登录密码password,手机号mobile,电子邮件email,部门编号deptld,员工级别level,员工头像avatar,备注remark)部门表 dept(部门编号id,部门名称deptName,部门经理编号managerld)工资表payrol(工资编号id,员工编号empld,基本工资basesalary,应发工资actualsalary,奖金bonus,缺勤扣钱deductMoney,薪资发放日期grantDate)
清假表ask leave(请假编号id,员工编号empld,请假原因leaveReason,请假开始时间beginDate,请假结束时间endDate,提交时间submitDate,审核人编号auditld(该列有触发器维护),申请状态status,审核意见auditOpinion)基础数据自己填写。
2、编写存储过程实现插入员工表:参数为:员工编号id,姓名userName,出生日期birthDate,身份证号idCard,登录名称loginName,登录密码password,手机号mobile,电子邮件emal,部门编号deptld,员工级别level,员工头像avatar,备注remark。存储过程名称为:insert employee。
3、利用存储过程在员工表中插入5条记录。
创建触发器，当插入或修改工资表payroll时，应发工资自动为“基本工资+奖金-缺勤扣钱”。4.
在员工表中依据姓名userName建立索引。索引名为:indexuserName。5.建立员工部门工资视图(包含员工名称，部门名称，基本工资,应发工资,奖金,缺勤扣钱)视图名称:v_employee_dept_payroll.。.利用触发器实现插入请假信息时，审核人编号自动填入请假人所在部门的部门经理编号。触发器名称为:insert askleave。7备份数据库的结构和数据，导出SQL文件名为:staff_sijk.sql。8.
9、将staff sik.sql上传至服务器；

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),
deptld INT,
LEVEL INT,
avatar BLOB,
remark TEXT.
PRIMARY KEY(id)

);

CREATE TABLE dept(
id INT NOT NULL AUTO INCREMENT,
deptName VARCHAR(255),
manageld lNT.
remark VARCHAR(255),
PRIMARY KEY(id)
CREATE TABLE payroll(id INT NOT NULL AUTO INCREMENT,
empld INT.
baseSalary DOUBLE,
actualSalary DOUBLE,
bonus DOUBLE,
deductMoney DOUBLE
grantDate DATE,PRIMARY KEY(id)

);

CREATE TABLE ask leave(
id INT NOT NULL AUTO INCREMENT,
empld INT,
leaveReason TEXT
beginDate DATE.
endDate DATE,
submitDate DATE,
auditld lNT.
STATUS INT,
auditOpinion TEXT
PRIMARY KEY(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文件上传至服务器