目录
sqlite安装和使用
sqlite插入语句
sqlite查询语句
WHILE条件查询
BETWEEN查询
模糊查询
in查询
Limit关键字 （限制由SELECT语句返回的数据数量）可以和 WHILE结合一起用
Order By 关键字 基于一个或多个列按升序或者降序顺序排列数据 desc=> 降序 asc=> 升序
Distinct关键字 与select语句一起使用 来消除所有重复的记录，并只获取唯一一次记录
sqlite修改和删除语句
Update语句 带有WHILE子句的查询的基本语法
Delete语句
sqlite索引和约束
常用函数
例如：SELECT UPPER('name') FROM 'user'
子查询
JOIN查询
使用 Node.js 链接数据库
⭐sqlite查询语句
SELECT ... FROM ...
SELECT 'id','name' FROM '名'
SELECT * FROM '名'
WHILE条件查询
SELECT 'id','name' FROM '名' WHILR 'age' > 18
SELECT * FROM '名' WHILE 'age' > 18 AND 'balance' < 100
SELECT * FROM '名' WHILE 'age' = 18 or 'age' = 19
BETWEEN查询
SELECT * FROM '名' WHILE 'age' BETWEEN 18 AND 200
模糊查询
SELECT * FROM '名' WHILE 'name' LIKE '%a%'
in查询
SELECT * FROM '名' WHILE 'id' IN (2,5)
SELECT * FROM '名' WHILE 'id' NOT IN (2,5)
Limit关键字 （限制由SELECT语句返回的数据数量）可以和 WHILE结合一起用
select * from 'user' limit 2
select * from 'user' limit 2,2
Order By 关键字 基于一个或多个列按升序或者降序顺序排列数据 desc=> 降序 asc=> 升序
select * from 'user' order by 'id' desc limit 3
select * from 'user' order by 'id' asc limit 3
Distinct关键字 与select语句一起使用 来消除所有重复的记录，并只获取唯一一次记录
select 'password' from '名'
select DISTINCT 'password' from '名'
⭐sqlite修改和删除语句
Update语句 带有WHILE子句的查询的基本语法
UPDATE 'user' SET 'acmin01',password = '111' WHILE 'id' = 1
Delete语句
DELETE FROM 'user' WHILE 'id' = 1
⭐ sqlite索引和约束
sqlite索引
有索引的存在，会提升查询的速度的同时，但同时也会消耗一部分性能。
创建索引
--单列索引--
CREATE INDEX text_index【索引名】 ON 'user' ('account') 【user表的account字段】
--唯一索引--
CREATE UNIQUE INDEX test_unique_index ON 'user' ('account')
--组合索引--
CREATE INDEX text_index ON 'user' ('account','name')
查看索引
SELECT * FROM sqlite_master WHILE type = 'index'
删除索引
DROP INDEX text_unique_index
注意
虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引。使用索引时，应重新考虑下列准则:
索引不应该使用在较小的表上。
索引不应该使用在有频繁的大批量的更新或插入操作的表上。索引不应该使用在含有大量的NULL 值的列上。
索引不应该使用在频繁操f作的列上。
约束
约束是在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束有:非空约束(not null)、唯一约束(unique)、主键约束(primary key)
NOT NULL约束
UNIQUE约束
PRIMARY Key 约束
⭐子查询
子查询或称为内部查询、嵌套查询，指的是在 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查询
Inner Join
内连接（INNER JOIN）根据连接谓词结合两个表(table1和 table2)的列值来创建一个新的结果表。查询会把table1中的每一行与table2中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接谓词时，A和B行的每个匹配对的列值会合并成一个结果行。
SELECT * FROM 'user' JOIN 'user_log' ON 'user'.'id' = 'user_log'.'id'
⭐事物属性
事务可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行，—般来说，事务是必须满足4个条件（ACID）：Atomicity(原子性)、consistency(稳定性)、Ilsolation(隔离性)、Durability(可靠性)。
1．原子性
一组事务，要么成功:要么失败回滚当作什么事都没发生
2．稳定性
有非法数据(外键约束之类)，事务撤回
3．隔离性
事务独立运行。一个事务处理后的结果，影响了其它事务，那么其它事务会撤回事务的100%隔离，需要牺牲速度
4.可靠性
软硬件崩溃后，SQLite 数据表驱动会利用日志文件重构修改
⭐sQLite 事物
ROLLBACK 回滚事物
--开启事物
BEGIN;
INSERT INTo"userT(id , " name" , account` , password , ' create_time' , " balance")VALUES(6,"peter" , "testaccounte1" , "123456"，123456,180 );
INSERT INTo"user’( id` , name , " account" , password , ' create_time ' , " balance" )VALUES (7, "peter", "testaccounte1" , "123456",123456,100 );
--回滚事务
ROLLBACK;
--开启事物
BEGIN;
INSERT INTo"userT(id , " name" , account` , password , ' create_time' , " balance")VALUES(6,"peter" , "testaccounte1" , "123456"，123456,180 );
INSERT INTo"user’( id` , name , " account" , password , ' create_time ' , " balance" )VALUES (7, "peter", "testaccounte1" , "123456",123456,100 );
--提交事务
ROLLBACK;
⭐使用 Node.js 链接数据库
一. 安装sqlite3，运行如下命令
yarn add sqlite3 --ignore-scripts
二. 实现链接
文件夹目录
▽ Message
▸ node_modules
▸ public
▸ hello.html
▸ db
▸ db.sqlite3
▸ dbutils.js
▸ router
▸ MessageRouter.js
▸ app.js
▸ package-lock.json
▸ package.json
▸ yarn.lock
1、创建数据库
- 创建数据库文件：
注意 ③，数据文件配置的路径是要和我们项目调用数据文件保持一致
- 新建一个表：
- 添加字段：
- 添加数据
2、使用NODE开始链接数据库
首先，我们希望的是，使用我们搭建的服务器来连接数据库，那么创建服务器
app.js
const express = require("express")
const path = require("path")
const app = express()
let port = 3000
// => 为前端开放跨域请求
app.use(function (req,res,next) {
// => 设置允许跨域的域名，* 代表允许任意域名跨域
res.header("Access-Control-Allow-Origin","*");
// => 允许的header类型
res.header("Access-Control-Allow-Headers","content-type");
// => 跨域允许的请求方式
res.header("Access-Control-Allow-Methods","DELETE,PUT,POST,GET,OPTIONS");
if(req.method == "OPTIONS") res.sendStatus(200) // => 让options尝试请求快速结束
else next()
})
// => 导入路由
app.use("/message",require("./router/MessageRouter.js"))
// => 导入静态资源
app.use(express.static(path.join(__dirname,"./public")))
app.get("/",(req,res) => {
res.send("hello")
})
app.listen(port,(req,res) => {
console.log(`启动成功`)
})
然后我们配置相关路由
router / MessageRouter.js
const express = require("express")
const {
db} = require("../db/dbutils")
var router = express.Router()
router.get("/test",(req,res) => {
// => 查询数据库
db.all("select * from `message`",(err,rows) => {
res.send(rows)
})
})
module.exports = router
为了使得数据库导出及复用性更高，我们将其单独封装成一个链接
db / dbutils.js
const path = require("path") // => 导入 path 模块
const sqlite3 = require("sqlite3").verbose() // => 导入依赖
const db = new sqlite3.Database(path.join(__dirname,"./db.sqlite3")) // => 实例化
// => 暴露
module.exports = {
db}
启动
查看是否链接成功
