前端进击全栈 sql 篇(下)
站长
· 阅读数 6
前言
如果你已经掌握了 Better-Sqlite3 和 Jest 的基本API，那么你可以深入探索它们的高级API，例如事务处理、约束、联结等。在本文中，我们将介绍这些高级API，并通过实例演示它们的用法和功能。
github 相关项目
同样将项目地址 First Sql 放在开头，你可以直接 clone https://github.com/guanwanxiao/first-sql.git 开始测试用例。
单元测试
当你开始编写单元测试时，你可以先使用 Jest 的 describe 和 test 函数来定义测试用例的结构和具体的测试点。如下：然后写具体代码实现
// 测试数据库连接
test('Database connection', () => {
// ...
});
// 测试创建用户表
test('create user table', () => {
// ...
})
// 创建外键
test('FOREIGN KEY constraint', () => {
// ...
})
设计表、创建表、外键
在关系型数据库中，创建表是一项基本操作，它允许你定义表的结构和属性，以便在其中存储数据。当你创建表时，你可以使用外键来建立表之间的关联关系。外键是一种约束，用于确保表与表之间的数据一致性和完整性。
以下是 SELECT 语句测试用例：
- 测试数据库连接是否成功打开。
- 测试创建用户表和删除店铺表的功能。
- 测试使用 FOREIGN KEY 约束插入数据时，有效的外键和无效的外键分别能否成功插入。
let db;
beforeEach(() => {
// 每执行一个 test，都会创建一个临时数据库用于测试,保证数据独立不受污染。
db = new Database(':memory:');
});
describe('Database creation and deletion ', () => {
test('create user table', () => {
db.exec(`
CREATE TABLE IF NOT EXISTS user (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name Varchar(30),
age INTEGER
)
`)
const result = db.pragma('table_info(user)')
// 判断表列名
expect(result[0].name).toBe('id')
// 判断表列类型
expect(result[0].type).toBe('INTEGER')
expect(result[1].name).toBe('name')
expect(result[1].type).toBe('Varchar(30)')
expect(result[2].name).toBe('age')
expect(result[2].type).toBe('INTEGER')
})
test('delete shop table', () => {
db.exec(`
CREATE TABLE IF NOT EXISTS shop (
id integer PRIMARY KEY AUTOINCREMENT,
shop varchar(30),
address varchar(30)
)
`)
// 删除表
db.exec('DROP TABLE IF EXISTS shop')
// 获取表信息
const result = db.pragma('table_info(shop)')
// 判断表是否存在
expect(result).toHaveLength(0)
})
})
外键测试
let db;
beforeAll(() => {
db = new Database(':memory:');
/**
* 启用外键约束的命令
* SQLite 默认情况下是不启用外键约束的，这意味着你可以在表中插入任何值，而不会检查其关联的外键约束。
*/
db.pragma('foreign_keys = ON');
// 创建两张表，employees 表中 department_id 引用了 departments 表中的 id 字段
// 通过使用外键约束，数据库系统可以在进行数据插入、更新或删除操作时，自动检查和强制执行相关表之间的引用完整性，防止出现无效的引用和不一致的数据
db.exec(`
CREATE TABLE IF NOT EXISTS departments (
id INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE IF NOT EXISTS employees (
id INTEGER PRIMARY KEY,
name TEXT,
department_id INTEGER,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
`);
});
describe('FOREIGN KEY constraint', () => {
// departments 主表 employees 子表
test('insert employee with valid department_id', () => {
// 插入部门数据
const { lastInsertRowid: departmentId } = db.prepare('INSERT INTO departments (name) VALUES (?)').run('IT');
// 插入员工数据
const { changes } = db.prepare('INSERT INTO employees (name, department_id) VALUES (?, ?)').run('Alice', departmentId);
expect(changes).toBe(1);
});
test('insert employee with invalid department_id', () => {
try {
// 尝试插入一个不存在的部门 ID
const { changes } = db.prepare('INSERT INTO employees (name, department_id) VALUES (?, ?)').run('Bob', 999);
// 预期插入失败，因为 department_id 不存在
expect(changes).toBe(0);
} catch (error) {
// 预期插入失败，因为 department_id 不存在
expect(error.code).toContain("SQLITE_CONSTRAINT_FOREIGNKEY");
}
});
});
查询、联结表、子查询
在关系型数据库中，SELECT 语句用于选择要检索的列，并使用 FROM 子句指定要查询的表。SELECT 语句允许我们根据条件筛选数据，并选择所需的列。 以下是 SELECT 语句测试用例：
AND：测试使用逻辑运算符 AND 的情况OR：测试使用逻辑运算符 OR 的情况NOT：测试使用逻辑运算符 NOT 的情况Like：测试使用 LIKE 进行模糊查询的情况ORDER BY age ASC：测试按照特定列升序排序的情况ORDER BY age DESC：测试按照特定列降序排序的情况select and group by name：测试使用 GROUP BY 分组聚合的情况select with limit：测试使用 LIMITselect distinct names：测试使用 DISTINCT 去重的情况INNER JOIN测试使用内连接查询每个学生的成绩
describe('ORDER BY、GROUP BY、LIMIT、 DISTINCT、AND、OR、NOT、Like', ()=> {
test('AND',() => {
const query = db.prepare(`SELECT name, age,grade FROM students where name='Alice' AND age=18`)
const result = query.all()
expect(result).toHaveLength(1) // 只命中1条
expect(result).toEqual([{name: 'Alice',age:18,grade:12}])
})
test('OR',() => {
const query = db.prepare(`SELECT name, age FROM students WHERE name='Charlie' OR age > 18`)
const result = query.all()
expect(result).toHaveLength(2) // 命中2条
expect(result).toEqual([{name: 'Charlie',age:18}, {name: 'Bob',age:19}])
})
test('NOT',() => {
const query = db.prepare(`SELECT name, age FROM students WHERE name NOT IN('Alice','Charlie')`)
const result = query.all()
expect(result).toHaveLength(2)
expect(result).toEqual([{name: 'Bob',age:17}, {name: 'Bob', age: 19}])
})
test('Like',() => {
// 不区分大小写，查询包含 c 字母的名称
const query = db.prepare(`SELECT name FROM students WHERE UPPER(name) LIKE UPPER('%c%')`)
const result = query.all()
expect(result).toHaveLength(3)
expect(result).toEqual([
{name: 'Alice'},
{name: 'Charlie'},
{name: 'Alice'}
])
})
test('ORDER BY age ASC', ()=> {
const query = db.prepare('SELECT age FROM students ORDER BY age ASC')
// pluck(true) 只会取第一列的值，这里只查了 age 列
const result = query.pluck(true).all()
expect(result).toEqual([16,17,18,18,19])
})
test('ORDER BY age DESC', ()=> {
const query = db.prepare('SELECT age FROM students ORDER BY age DESC')
// pluck(true) 只会取第一列的值，这里只查了 age 列
const result = query.pluck(true).all()
expect(result).toEqual([19, 18, 18, 17, 16])
})
test('select and group by name', () => {
const query = db.prepare('SELECT name, COUNT(id) as count FROM students GROUP BY name')
const result = query.all()
expect(result).toEqual(
[
{name: 'Alice', count:2},
{name: 'Bob', count:2},
{name: 'Charlie', count:1}
]
)
});
test('select with limit', () => {
const query = db.prepare('SELECT * FROM students ORDER BY age DESC LIMIT 1');
// 查询所有的结果
const result = query.all();
expect(result).toEqual([
{ id: 5, name: 'Bob', age: 19, grade: 12 }
]);
});
test('select distinct names', () => {
const query = db.prepare('SELECT DISTINCT name FROM students');
const result = query.all();
expect(result).toEqual([
{ name: 'Alice' },
{ name: 'Bob' },
{ name: 'Charlie' },
]);
});
})
在关系型数据库中，SELECT 是用于检索数据的关键字，JOIN 是一种联结方式，而子查询是一种特殊的查询语句嵌套在主查询语句中的方式。
describe('join', () => {
test('To query the grades of each student with inner join', () => {
const query = db.prepare(`
SELECT s.name,s.age, g.subject, g.score
FROM students as s
INNER JOIN grades as g
ON s.id = student_id
`)
const result = query.all()
expect(result).toEqual([
{name:'Alice', age: 18, subject: 'Math', score: 85},
{name:'Bob', age: 17, subject: 'Math', score: 78},
{name:'Charlie', age: 18, subject: 'Math', score: 92},
{name:'Alice', age: 16, subject: 'Math', score: 79},
{name:'Bob', age: 19, subject: 'Math', score: 88}
])
})
})
更新、Transaction 事务
在关系型数据库中，使用 UPDATE 语句可以对现有的记录进行更新。这条SQL命令允许你根据指定的条件，修改表中的数据。而在一些情况下，你可能需要将一组更新操作作为一个事务进行处理，以确保数据的一致性和完整性。
通过使用 TRANSACTION，你可以将一组更新操作包装在一个事务中，要么全部成功应用到数据库，要么全部回滚回之前的状态。这样可以保证在多个更新操作中发生错误或异常时，不会影响数据库的一致性。
以下是 UPDATE 语句测试用例：
- 基本更新 (Basic Update)
- 更新不存在的记录 (Update Nonexistent Record)
- 类似匹配更新 (Update Like Match)
- 更新性能 (Update Performance)
- 组合更新 (Transaction Update)
- 组合更新错误 (Transaction Update Error)
// 更新语句
function update(name, subject, grade) {
const stmt = db.prepare('UPDATE grades SET grade=? where name=? AND subject=?')
return stmt.run( grade, name, subject)
}
// 查询语句
function query(name, subject) {
const query = db.prepare('SELECT * FROM grades WHERE name=? and subject=?')
return query.get(name, subject)
}
describe('Database Update Tests', () => {
test('Basic Update', () => {
const targetGrade = 99
const result = update('Alice', 'chinese', targetGrade)
expect(result.changes).toBe(1)
const updateRow = query('Alice', 'chinese')
expect(updateRow.grade).toBe(targetGrade)
});
test('Update Nonexistent Record', () => {
const result = update('jack', 'math', 85)
expect(result.changes).toBe(0); // 确保当更新不存在的记录时，变更数为0
});
test('Update Like Match', () => {
const pattern = '%e';
const targetGrade = 77
const stmt = db.prepare(`UPDATE grades SET grade=? WHERE name LIKE ? AND subject='math'`)
const result = stmt.run(targetGrade,pattern)
expect(result.changes).toBe(1)
const stmt1 = db.prepare(`SELECT * FROM grades WHERE name LIKE ? AND subject='math'`)
const result1 = stmt1.get(pattern)
expect(result1.grade).toBe(targetGrade)
})
test('Update Performance', () => {
// 创建大型数据集
db.exec('CREATE TABLE bigtable (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT)');
const stmt = db.prepare('UPDATE bigtable SET data=? WHERE id % 2 = 0')
const startTime = new Date();
for (let i = 0; i < 10000; i++) {
db.prepare('INSERT INTO bigtable (data) VALUES (?)').run(`row ${i}`);
}
stmt.run('UpdatedData')
const endTime = new Date();
const timeDiff = (endTime - startTime) / 1000;
console.log('timeDiff',timeDiff)
expect(timeDiff).toBeLessThan(1); // 确保更新操作的性能足够快（此处假设1秒以内为正常）
});
test('Transaction Update', async () => {
const updateTwoRows = db.transaction(() => {
update('Alice', 'math', 100);
update('Alice', 'math', 60);
})
updateTwoRows()
const row = query('Alice', 'math')
expect(row.grade).toBe(60)
})
test('Transaction Update Error', async ()=> {
const updateTwoRows = db.transaction(() => {
update('Alice', 'math', 100);
throw new Error('一个意外')
update('Alice', 'math', 60);
})
try {
updateTwoRows()
} catch (error){
console.log('error', error.message)
}
const row = query('Alice', 'math')
expect(row.grade).toBe(100)
})
})
约束、索引
在关系型数据库中，SQL约束和索引是两个重要的概念，它们能够帮助我们规范数据的完整性和提高查询性能。
首先，SQL约束用于强制实施数据的完整性和一致性。通过定义不同类型的约束，如主键约束、唯一约束、非空约束和外键约束，我们可以确保数据库中的数据符合特定的规则和条件。例如，主键约束可以保证每条记录都具有唯一的标识符，而外键约束可以建立不同表之间的引用关系，从而维护数据的一致性。
这里介绍下SQL的CHECK约束。通过CHECK约束，我们可以自定义规则，以验证插入或更新的数据是否满足特定的条件。
let db;
beforeEach(() => {
db = new sqlite(':memory:');
db.exec(`
CREATE TABLE grades (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
subject varchar(10) CHECK (subject IN('math', 'chinese', 'english')),
grade INTEGER NOT NULL
);
INSERT INTO grades (name,subject, grade) VALUES ('Alice', 'chinese',98);
`)
})
test('Update Not In Check', () => {
// 更新的内容不在 Check 列表中，导致更新失败
const result = update('Alice', 'music', 100)
expect(result.changes).toBe(0)
})
索引则是用于提高数据库查询性能的重要手段。索引可以帮助数据库系统快速定位和访问数据，特别是在大型数据集上进行检索时，索引的作用尤为明显。通过在表的列上创建索引，我们可以加快查询的速度，并降低数据库系统的资源消耗。
test('Query with Index', () => {
const start = Date.now();
const stmt = db.prepare('SELECT * FROM grades WHERE name=? AND subject=?');
const aliceMath = stmt.get('Alice', 'math');
const end = Date.now();
expect(aliceMath).not.toBeNull();
expect(aliceMath.score).toBeGreaterThan(0);
});
转载自:https://juejin.cn/post/7311279319417176104