console
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>小型图书馆借阅系统 </title>
<script src="https://cdn.jsdelivr.net/npm/sql.js@1.8.0/dist/sql-wasm.js"></script>
<style>
body { font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto; padding: 20px; }
h2 { color: #333; border-bottom: 1px solid #eee; padding-bottom: 10px; }
.panel { margin-bottom: 20px; padding: 15px; border: 1px solid #ddd; border-radius: 5px; }
button { padding: 8px 15px; background-color: #4CAF50; color: white; border: none; border-radius: 4px; cursor: pointer; }
button:hover { background-color: #45a049; }
table { width: 100%; border-collapse: collapse; margin-top: 10px; }
table, th, td { border: 1px solid #ddd; }
th, td { padding: 8px; text-align: left; }
th { background-color: #f2f2f2; }
tr:nth-child(even) { background-color: #f9f9f9; }
.code-block { background-color: #f5f5f5; padding: 10px; border-left: 4px solid #4CAF50; overflow-x: auto; }
</style>
</head>
<body>
<h2>小型图书馆借阅系统</h2>
<div class="panel">
<h3>数据库操作</h3>
<button id="initDB">初始化数据库</button>
<button id="queryAll">查询所有记录</button>
<button id="queryOverdue">查询逾期图书</button>
<button id="returnBook">归还图书</button>
</div>
<div class="panel">
<h3>操作结果</h3>
<div id="result"></div>
</div>
<div class="panel">
<h3>SQL执行日志</h3>
<div id="log" class="code-block"></div>
</div>
<script>
let db;
let logOutput = document.getElementById('log');
let resultOutput = document.getElementById('result');
async function initDatabase() {
const SQL = await initSqlJs({
locateFile: (file) => `https://cdn.jsdelivr.net/npm/sql.js@1.8.0/dist/${file}`
});
db = new SQL.Database();
log('成功创建数据库实例');
executeSql(`
-- 创建图书表
CREATE TABLE IF NOT EXISTS 图书 (
图书编号 INTEGER PRIMARY KEY,
书名 VARCHAR(100) NOT NULL,
作者 VARCHAR(50) NOT NULL,
出版社 VARCHAR(50) NOT NULL,
馆藏位置 VARCHAR(50) NOT NULL,
状态 VARCHAR(20) NOT NULL CHECK (状态 IN ('在馆', '已借出'))
);
-- 创建读者表
CREATE TABLE IF NOT EXISTS 读者 (
读者编号 INTEGER PRIMARY KEY,
姓名 VARCHAR(50) NOT NULL,
联系方式 VARCHAR(20) NOT NULL,
借阅权限 VARCHAR(20) NOT NULL CHECK (借阅权限 IN ('普通借阅', '教师借阅', '学生借阅'))
);
-- 创建借阅表
CREATE TABLE IF NOT EXISTS 借阅 (
借阅编号 INTEGER PRIMARY KEY AUTOINCREMENT,
图书编号 INTEGER NOT NULL,
读者编号 INTEGER NOT NULL,
借阅日期 DATE NOT NULL,
应还日期 DATE NOT NULL,
实际归还日期 DATE,
FOREIGN KEY (图书编号) REFERENCES 图书(图书编号),
FOREIGN KEY (读者编号) REFERENCES 读者(读者编号)
);
`);
log('成功创建表结构');
executeSql(`
-- 插入图书数据
INSERT INTO 图书 (图书编号, 书名, 作者, 出版社, 馆藏位置, 状态) VALUES
(1001, 'Python编程从入门到实践', 'Eric Matthes', '人民邮电出版社', 'A区1排2架', '在馆'),
(1002, 'Java核心技术', 'Cay S. Horstmann', '机械工业出版社', 'B区3排5架', '已借出'),
(1003, '数据结构与算法分析', 'Mark Allen Weiss', '机械工业出版社', 'C区2排1架', '在馆'),
(1004, '计算机网络', 'Andrew S. Tanenbaum', '清华大学出版社', 'D区4排3架', '已借出'),
(1005, '数据库系统概念', 'Abraham Silberschatz', '机械工业出版社', 'E区1排5架', '在馆');
-- 插入读者数据
INSERT INTO 读者 (读者编号, 姓名, 联系方式, 借阅权限) VALUES
(2001, '张三', '13800138001', '普通借阅'),
(2002, '李四', '13900139002', '教师借阅'),
(2003, '王五', '13700137003', '学生借阅'),
(2004, '赵六', '13600136004', '普通借阅'),
(2005, '钱七', '13500135005', '学生借阅');
-- 插入借阅数据
INSERT INTO 借阅 (图书编号, 读者编号, 借阅日期, 应还日期, 实际归还日期) VALUES
(1002, 2001, '2023-03-10', '2023-03-24', NULL),
(1004, 2003, '2023-03-15', '2023-03-29', NULL),
(1001, 2002, '2023-02-28', '2023-03-14', '2023-03-10'),
(1003, 2005, '2023-03-05', '2023-03-19', '2023-03-18');
`);
log('成功插入初始数据');
showMessage('数据库初始化完成');
}
function executeSql(sql) {
log(`执行SQL: ${sql}`);
try {
const result = db.exec(sql);
log(`执行结果: ${JSON.stringify(result)}`);
return result;
} catch (error) {
log(`执行错误: ${error.message}`);
throw error;
}
}
function queryAllBorrowRecords() {
const result = executeSql(`
SELECT
读者.姓名,
图书.书名,
借阅.借阅日期,
借阅.应还日期,
借阅.实际归还日期
FROM
借阅
JOIN
读者 ON 借阅.读者编号 = 读者.读者编号
JOIN
图书 ON 借阅.图书编号 = 图书.图书编号
`);
displayQueryResult(result, '所有借阅记录');
}
function queryOverdueBooks() {
const today = '2023-04-01';
const result = executeSql(`
SELECT
读者.姓名,
图书.书名,
借阅.应还日期
FROM
借阅
JOIN
读者 ON 借阅.读者编号 = 读者.读者编号
JOIN
图书 ON 借阅.图书编号 = 图书.图书编号
WHERE
借阅.实际归还日期 IS NULL AND
借阅.应还日期 < '${today}'
`);
displayQueryResult(result, '逾期未还图书');
}
function returnBookExample() {
const bookId = 1002;
executeSql('BEGIN TRANSACTION;');
try {
executeSql(`
UPDATE 图书
SET 状态 = '在馆'
WHERE 图书编号 = ${bookId}
`);
executeSql(`
UPDATE 借阅
SET 实际归还日期 = '${new Date().toISOString().split('T')[0]}'
WHERE 图书编号 = ${bookId} AND 实际归还日期 IS NULL
`);
executeSql('COMMIT;');
showMessage(`图书编号 ${bookId} 归还成功`);
queryAllBorrowRecords();
} catch (error) {
executeSql('ROLLBACK;');
showMessage(`归还失败: ${error.message}`);
}
}
function displayQueryResult(result, title) {
if (!result || result.length === 0) {
showMessage(`查询无结果: ${title}`);
return;
}
const rows = result[0].values;
const columns = result[0].columns;
let html = `<h4>${title}</h4>`;
html += `<table><thead><tr>`;
columns.forEach(col => html += `<th>${col}</th>`);
html += `</tr></thead><tbody>`;
rows.forEach(row => {
html += `<tr>`;
row.forEach(cell => {
html += `<td>${cell || 'NULL'}</td>`;
});
html += `</tr>`;
});
html += `</tbody></table>`;
resultOutput.innerHTML = html;
}
function showMessage(message) {
resultOutput.innerHTML = `<p>${message}</p>`;
}
function log(message) {
logOutput.innerHTML += message + '<br>';
logOutput.scrollTop = logOutput.scrollHeight;
}
document.getElementById('initDB').addEventListener('click', initDatabase);
document.getElementById('queryAll').addEventListener('click', queryAllBorrowRecords);
document.getElementById('queryOverdue').addEventListener('click', queryOverdueBooks);
document.getElementById('returnBook').addEventListener('click', returnBookExample);
window.onload = () => {
showMessage('请点击"初始化数据库"按钮开始');
};
</script>
</body>
</html>