数据库操作题

一、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 between `2024-03-01` and `2024-03-31`;

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`

二、 CREATE TABLE employee( id INT AUTO_INCREMENT primary key, 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);       baseSalary DOUBLE,    auditId INT, STATUS INT,

2、编写存储过程实现插入员工表：参数为：员工编号id,姓名userName,出生日期birthDate,身份证号idCard,登录名称loginName,登录 密码password,手机号mobile,电子邮件email,部门编号deptId,员工级别level,员工头像avatar,备注remark。 存储过程名称为：insert_employee。

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `staff`.`inset_employee`(`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)
    
    BEGIN
    INSERT INTO employee VALUES(NULL,`userName`,`birthDate`,`idCard`,`loginName`,`password`,`mobile`,`email`,`deptId`,`level`
    ,`avatar`,`remark`);
    END$$

DELIMITER ;

3、利用存储过程在员工表中插入5条记录。

CALL `inset_employee`('aa','2009-8-7','22222','mmm','1234','1111','333','1','3',NULL,NULL);

4、创建触发器，当插入或修改工资表payroll时，应发工资自动为“基本工资+奖金-缺勤扣钱”

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `staff`.`insert_payroll` BEFORE INSERT
    ON `staff`.`payroll`
    FOR EACH ROW BEGIN
    SET new.`actualSalary`=new.`baseSalary`+new.`bonus`-new.`deductMoney`;
    END$$

DELIMITER ;

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `staff`.`update_payroll` BEFORE UPDATE
    ON `staff`.`payroll`
    FOR EACH ROW BEGIN
SET new.`actualSalary`=new.`baseSalary`+new.`bonus`-new.`deductMoney`;
    END$$

DELIMITER ;

。 5、在员工表中依据姓名userName建立索引。索引名为：index_userName。

CREATE INDEX index_userName ON employee(`userName`)

6、建立员工部门工资视图（包含员工名称，部门名称，基本工资,应发工资,奖金,缺勤扣钱）视图名称：v_employee_dept_payroll。

CREATE
    /*[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]*/
    VIEW `staff`.`v_employee_dept_payrpll` 
    AS
SELECT `userName`,`deptName`,`baseSalary`,`actualSalary`
,`bonus`,`deductMoney`
FROM `dept` d,`employee` e,`payroll` p
WHERE d.id=e.`deptId` AND p.`empId`=e.id

7、利用触发器实现插入请假信息时，审核人编号自动填入请假人所在部门的部门经理编号。触发器名称为：insert_ask_leave。

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `staff`.`insert_ask_leave` BEFORE INSERT
    ON `staff`.`ask_leave`
    FOR EACH ROW BEGIN
    SELECT `mangerId` INTO @managerid FROM `dept` 
    WHERE `id`=(SELECT `deptId` FROM `employee` WHERE `id`=new.`empId`);
    SET new.`auditId`=@managerid;
    END$$

DELIMITER ;

8、备份数据库的结构和数据，导出SQL文件名为：staff_sjk.sql。

9、将staff_sjk.sql上传至服务器。 我的答案： 正确答案：