Express-05

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

常用函数

官方文档

Built-In Scalar SQL Functions

序号	函数 & 描述
1	COUNT 函数 COUNT 计算一个数据库表中的行数。
2	MAX 函数 MAX 选择某列的最大值。
3	MIN 函数 MIN 选择某列的最小值。
4	AVG 函数 AVG 计算某列的平均值。
5	SUM 函数 SUM 数值列计算总和。

例子:

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 中可以使用下面的命令来控制事务

命令	说明
BEGIN TRANSACTION	开始事务处理。
COMMIT END TRANSACTION	保存更改
ROLLBACK	回滚所做的更改

事务控制命令只与 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)
 }
});