Express-05

本节：数据库的常见函数，子查询，join查询，连接查询，事务，使用node连接sqlite，也就是连接数据库

常用函数

官方文档

Built-In Scalar SQL Functions

例子:

SELECT count(*) FROM `user`
SELECT MAX(`id`) as 'abc' FROM `user`
SELECT AVG(`id`) FROM `user`
SELECT UPPER(`name`),random() FROM `user`

子查询

子查询或称为内部查询、嵌套查询，指的是在 SQLite 查询中的 WHERE 子句中嵌入查询语句

例子

select * from `user` where `id` in (select `id` from `user_log`)
select * from `user` where `id` in (select `id` from `user_log` where `login_times` > 5)

JOIN 查询

CROSS JOIN

又叫笛卡尔积，匹配前一个表与后一个表的每一行和每一列，这样得到的结果集为n*m行（n, m分别为每张表的行数），x+y列（x, y分别为每张表的列数）。可见，该结果集可能会成为一个巨大的表，对内存和后续处理都会造成巨大压力，所以，慎用（真没用过）。

SELECT * FROM `user` CROSS JOIN `user_log` ON `user`.`id` = `user_log`.`id`

Inner Join

内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。

SELECT * FROM `user` JOIN `user_log` ON `user`.`id` = `user_log`.`id`
SELECT * FROM `user` INNER JOIN `user_log` ON `user`.`id` = `user_log`.`id`

LEFT OUTER JOIN

SQLite 只支持 左外连接（LEFT OUTER JOIN）

SQLite3只支持left outer join，其结果集由不大于x + y列，n - n*m行构成，至少包含左侧表的每一行，对于Join后不存在的字段值，则赋NULL。这样得到的表与我们之前设计那个全集结果一样，但数据结构更清晰，空间占用更少。

SELECT * FROM `user` LEFT OUTER JOIN `user_log` ON `user`.`id` = `user_log`.`id`

事务（Transaction）

事务 (Transaction) 是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行

事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源

举个例子，A 向 B 转账 100 元，其实整个转账过程就是一个事务，要么转账成功了，A 的账户扣了 100 元，B 的账户增加了 100 元，要么转账失败，A 还是那么多钱，B 还是没钱，如果出现 A 扣了 100 元，B 的账户却没增加 100 元，那是要出问题的，是不？

事务，就是用来做这件事的，用来保证要么转账成功，要么转账失败

事务属性

事务可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行

一般来说，事务是必须满足4个条件 ( ACID )

Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)

1. 原子性

   一组事务，要么成功；要么失败回滚当作什么事都没发生

2. 稳定性

   有非法数据 (外键约束之类)，事务撤回

3. 隔离性

   事务独立运行。一个事务处理后的结果，影响了其它事务，那么其它事务会撤回

   事务的100%隔离，需要牺牲速度

4. 可靠性

   软、硬件崩溃后，SQLite 数据表驱动会利用日志文件重构修改

SQLite 事务

SQLite 默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作

因此要显式地开启一个事务务须使用命令 BEGIN TRANSACTION

SQLite 中可以使用下面的命令来控制事务

事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用

他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的

BEGIN TRANSACTION 命令

事务 ( Transaction ) 可以使用 BEGIN TRANSACTION 命令或 BEGIN 命令来启动

BEGIN;
BEGIN TRANSACTION;

事务通常会持续执行下去，直到遇到下一个 COMMIT 或 ROLLBACK 命令

有个特殊情况是：在数据库关闭或发生错误时，事务处理会回滚

COMMIT 命令

COMMIT 命令用于把事务调用的更改保存到数据库中

COMMIT;
END TRANSACTION;

ROLLBACK 命令

ROLLBACK 命令是用于撤消尚未保存到数据库的事务,如果执行不超过，或者判断不成功就进行回滚，回滚就是之前的执行的代码都作废了不做效

ROLLBACK;

例子：

--开启事务
BEGIN;
INSERT INTO `user` (`id`,`name`,`account`,`password`,`create_time`,`balance`)
VALUES (6, 'peter', 'testaccount01', '123456', 123456,100 );
--回滚事务，以上代码都不做效，不执行
ROLLBACK;
 
 
--开启事务，commit提交了事务，才执行代码
BEGIN;
INSERT INTO `user` (`id`,`name`,`account`,`password`,`create_time`,`balance`)
VALUES (7, 'hello', 'testaccount01', '123456', 123456,100 );
--提交事务
COMMIT;

使用Node链接sqlite

安装sqlite3

运行命令

npm install sqlite3

ps:一开始安装又是不成功，在网上找到了解决办法：

先执行这个代码，再安装可以了

 npm install -g node-pre-gyp

（1）创建一个js后缀的路由中间件，来连接sqlite数据库：

const express = require("express")
const fs = require("fs")
const path = require("path")
var router = express.Router();
 
//导入模块
var sqlite3 = require("sqlite3").verbose();
//指定数据库文件位置
var db = new sqlite3.Database("D://zhangchumei/mydatabase/one/test.sqlite3");
//path.join(__dirname, 路径)路径拼接
 
router.get("/testlist", (req, res) => {
 
    db.all("select * from `user`", [], (err, rows) => {
        if (err == null) {//如果没有报错
            res.send(rows)//就返回内容
        } else {
            res.send(err)//报错就返回报错
        }
    })
 
})
 
 
module.exports = router;

（2）在app.js文件，里面引入这个中间件

app.use("/db", require("./router/DbRouter"))

（3）在浏览器测试，调用查找的接口：

这里又遇到问题，数据库连接没问题，数据库也有数据，可以正常查找，可是浏览器出来的就是空。 

执行插入、修改、删除语句，使用run方法

let insert_sql = "INSERT INTO `user` (`id`,`name`,`account`,`password`,`create_time`,`balance`)
VALUES (?, ?, ?, ?, ?, ? );"
db.run(insert_sql, [6, 'peter', 'testaccount01', '123456', 123456,100],
function(err, rows){ 
 if(err == null){
   console.log("数据插入成功");
 }
});

执行查询语句，使用all方法

db.all("SELECT * FROM `user` where `id` = ?", [1],(err,rows)={ 
   if(err == null){
    console.log("查询数据:",rows)
  }
});
 
db.all("SELECT * FROM `user`", [],(err,rows)={ 
​​​​​​​ if(err == null){
     console.log("查询数据:",rows)
 }
});