数据库基础知识简答操作

Byear77

于 2024-05-14 23:46:50 发布

阅读量317

点赞数 5

文章标签：数据库 oracle

本文链接：https://blog.csdn.net/2203_75709904/article/details/138873536

版权

简答题

已知某图书管理数据库有如下表格：

用户表user、部门表dept、角色表role、图书表book、图书分类表book_classify、图书借阅表book_borrow、还书表book_return、借阅预约表book_appoint、图书遗失表book_lose；

（1）用户表：包含用户编号id、姓名user_name、出生日期birth_date、身份证号id_card

、帐号login_name、密码password、手机号mobile、电子邮件email、部门编号dept_id、角色编号role_id信息。

（2）部门表：包含部门编号id、部门名称dept_name、部门创建时间create_date信息。

（3）角色表：包含角色编号id、角色名称role_name、备注信息remark。

（4）图书表：包含图书编号id、书籍名称book_name、作者author、定价price、有无光盘cd、出版社publish、图书分类编号book_classify_id、总数量account、图书ISBN编号isbn、图书创建时间create_time、备注信息remark。

（5）图书分类表：包含编号id、图书分类名称book_classify_name、父分类编号father_id、创建时间create_time。

（6）图书借阅表：包含编号id、图书编号book_id、用户编号user_id、借阅时间borrow_time、归还时间return_time、创建图书借阅状态create_time、备注信息remark。

（7）图书还书表：包含编号id、图书借阅编号borrow_id、归还时间return_time、创建时间create_time、备注信息remark。

（8）借阅预约表：包含预约流水编号id、图书编号book_id、用户编号user_id、预约时间appoint_time、创建时间create_time、备注信息remark。

（9）图书遗失表：包含遗失流水编号id、图书借阅流水编号borrow_id、创建时间create_time、备注信息remark。

对于图书管理数据库，请写出正确的SQL语句：

下面链接中放的是该题的数据库、基本表和初始数据，同学们自己下载。

正确答案：

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

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`

操作题

按照要求实现员工管理数据库系统中的指定操作：

1、创建一个数据库 staff，并在数据库中创建以下四张表：

员工表 employee（员工编号id,姓名userName,出生日期birthDate,身份证号idCard,登录名称loginName,登录密码password,手机号mobile,电子邮件email,部门编号deptId,员工级别level,员工头像avatar,备注remark）

部门表 dept(部门编号id,部门名称deptName,部门经理编号managerId)

工资表payroll(工资编号id,员工编号empId,基本工资baseSalary,应发工资actualSalary,奖金bonus,缺勤扣钱deductMoney,薪资发放日期grantDate)

请假表ask_leave(请假编号id,员工编号empId,请假原因leaveReason,请假开始时间beginDate,请假结束时间endDate,提交时间submitDate,审核人编号auditId(该列有触发器维护),申请状态status,审核意见auditOpinion)

基础数据自己填写。

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

存储过程名称为：insert_employee。

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

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

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

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

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

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

9、将staff_sjk.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),

deptId INT,

LEVEL INT,

avatar BLOB,

remark TEXT,

PRIMARY KEY(id)

);

CREATE 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)

);

A爱：

-- 1. 创建数据库 staff
CREATE DATABASE IF NOT EXISTS staff;

-- 使用数据库 staff
USE staff;

-- 创建员工表 employee
CREATE TABLE IF NOT EXISTS employee (
id INT PRIMARY KEY AUTO_INCREMENT,
userName VARCHAR(50),
birthDate DATE,
idCard VARCHAR(18),
loginName VARCHAR(50),
password VARCHAR(50),
mobile VARCHAR(15),
email VARCHAR(100),
deptId INT,
level INT,
avatar VARCHAR(255),
remark TEXT
);

-- 创建部门表 dept
CREATE TABLE IF NOT EXISTS dept (
id INT PRIMARY KEY AUTO_INCREMENT,
deptName VARCHAR(50),
managerId INT
);

-- 创建工资表 payroll
CREATE TABLE IF NOT EXISTS payroll (
id INT PRIMARY KEY AUTO_INCREMENT,
empId INT,
baseSalary DECIMAL(10, 2),
actualSalary DECIMAL(10, 2),
bonus DECIMAL(10, 2),
deductMoney DECIMAL(10, 2),
grantDate DATE
);

-- 创建请假表 ask_leave
CREATE TABLE IF NOT EXISTS ask_leave (
id INT PRIMARY KEY AUTO_INCREMENT,
empId INT,
leaveReason VARCHAR(255),
beginDate DATE,
endDate DATE,
submitDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
auditId INT,
status VARCHAR(50),
auditOpinion TEXT
);

-- 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(15),
IN p_email VARCHAR(100),
IN p_deptId INT,
IN p_level INT,
IN p_avatar VARCHAR(255),
IN p_remark TEXT
)
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. 利用存储过程插入5条员工记录
CALL insert_employee(1, '张三', '1990-01-01', '123456789012345678', 'zhangsan', 'password1', '13812345678', 'zhangsan@example.com', 1, 1, 'avatar1.jpg', '备注1');
CALL insert_employee(2, '李四', '1995-05-05', '987654321098765432', 'lisi', 'password2', '13912345678', 'lisi@example.com', 2, 2, 'avatar2.jpg', '备注2');
CALL insert_employee(3, '王五', '1988-08-08', '111111111111111111', 'wangwu', 'password3', '13612345678', 'wangwu@example.com', 1, 2, 'avatar3.jpg', '备注3');
CALL insert_employee(4, '赵六', '1980-12-12', '222222222222222222', 'zhaoliu', 'password4', '13512345678', 'zhaoliu@example.com', 2, 3, 'avatar4.jpg', '备注4');
CALL insert_employee(5, '小明', '2000-03-03', '333333333333333333', 'xiaoming', 'password5', '13412345678', 'xiaoming@example.com', 1, 1, 'avatar5.jpg', '备注5');

-- 4. 创建触发器，自动计算应发工资
CREATE TRIGGER calculate_salary BEFORE INSERT ON payroll
FOR EACH ROW
BEGIN
SET NEW.actualSalary = NEW.baseSalary + NEW.bonus - NEW.deductMoney;
END;

-- 5. 在员工表中依据姓名userName建立索引
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. 创建触发器，自动填入请假人所在部门的部门经理编号
CREATE TRIGGER insert_ask_leave BEFORE INSERT ON ask_leave
FOR EACH ROW
BEGIN
DECLARE manager_id INT;
SELECT managerId INTO manager_id FROM dept WHERE id = (SELECT deptId FROM employee WHERE id = NEW.empId);
SET NEW.auditId = manager_id;
END;

-- 8. 备份数据库结构和数据，导出SQL文件
mysqldump -u username -p staff > staff_sjk.sql;

-- 9. 将 staff_sjk.sql 上传至服务器。

A爱2：

1. 创建数据库和表
CREATE DATABASE staff;
USE staff;

CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
userName VARCHAR(255) NOT NULL,
birthDate DATE,
idCard VARCHAR(18) UNIQUE NOT NULL,
loginName VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
mobile VARCHAR(20),
email VARCHAR(255),
deptId INT,
level VARCHAR(50),
avatar VARCHAR(255),
remark TEXT,
FOREIGN KEY (deptId) REFERENCES dept(id)
);

CREATE TABLE dept (
id INT PRIMARY KEY AUTO_INCREMENT,
deptName VARCHAR(255) NOT NULL,
managerId INT,
FOREIGN KEY (managerId) REFERENCES employee(id)
);

CREATE TABLE payroll (
id INT PRIMARY KEY AUTO_INCREMENT,
empId INT,
baseSalary DECIMAL(10, 2),
actualSalary DECIMAL(10, 2),
bonus DECIMAL(10, 2),
deductMoney DECIMAL(10, 2),
grantDate DATE,
FOREIGN KEY (empId) REFERENCES employee(id)
);

CREATE TABLE ask_leave (
id INT PRIMARY KEY AUTO_INCREMENT,
empId INT,
leaveReason TEXT,
beginDate DATE,
endDate DATE,
submitDate DATE,
auditId INT,
status VARCHAR(50),
auditOpinion TEXT,
FOREIGN KEY (empId) REFERENCES employee(id),
FOREIGN KEY (auditId) REFERENCES employee(id)
);

2. 编写存储过程
DELIMITER //
CREATE PROCEDURE insert_employee(
IN p_id INT,
IN p_userName VARCHAR(255),
IN p_birthDate DATE,
IN p_idCard VARCHAR(18),
IN p_loginName VARCHAR(255),
IN p_password VARCHAR(255),
IN p_mobile VARCHAR(20),
IN p_email VARCHAR(255),
IN p_deptId INT,
IN p_level VARCHAR(50),
IN p_avatar VARCHAR(255),
IN p_remark TEXT
)
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. 利用存储过程插入记录

您需要调用存储过程5次，每次传递不同的参数值。

4. 创建触发器
DELIMITER //
CREATE TRIGGER trg_after_payroll_update
AFTER INSERT OR UPDATE ON 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
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 trg_before_ask_leave_insert
BEFORE INSERT ON ask_leave
FOR EACH ROW
BEGIN
DECLARE dept_manager_id INT;
SELECT managerId INTO dept_manager_id FROM dept WHERE id = NEW.empId;

Byear77

关注

5
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
数据库基础知识简答操作

CALL insert_employee(1, '张三', '1990-01-01', '123456789012345678', 'zhangsan', 'password1', '13812345678', 'zhangsan@example.com', 1, 1, 'avatar1.jpg', '备注1');疑难杂病临证手册（第2版）','余孟学','158','1','河南科技出版社','4','1000','9787534989230',NOW(),NULL);
复制链接

扫一扫