node数据库学习之mysql 1

coderlin_

已于 2024-03-07 19:55:24 修改

阅读量849

点赞数

分类专栏： node.js系列文章标签： react.js 前端 reactjs

于 2021-11-25 10:27:48 首次发布

本文链接：https://blog.csdn.net/lin_fightin/article/details/121484966

版权

node.js系列专栏收录该内容

28 篇文章 0 订阅

订阅专栏

数据库

mysql 关系型数据库 //一张张表 (有关联的数据)
mongodb 非关系型数据(文档型数据库) //一个个文档（数据量大，安全性较低）
redis key-value键值对 //存放键值对（速度最快）

数据库的基本概念

实体
只要在客观世界存在的，可以被描述出来的都是实体。如，人，狗，桌子等等
数据库
数据库就是数据的仓库，可以存放结构化的数据
数据库管理系统(DBMS)
是一种系统软件，提供操作数据库的环境，可以通过数据库管理系统对数据进行插入，修改，删除，查询等操作。
SQL
结构华查询语言，专门用来和数据库进行交流的语言，几乎所有的DBMS都支持SQL。
SQL规范

语句不区分大小写，建议SQL关键字大写，表明和列表小写。
命令用分号结尾
命令可以缩进和换行，一种类型的关键字放在一行。
可以写单行和多行注释，#和–是单行注释。/**/是多行注释。

数据表

表示数据库中包含所有数据的数据库对象，也是其他对象的基础。
表定义是一个列的集合，数据在表中是按行和列的格式组织的，用来存放数据。
行也称为记录，用来存放一个个实体，列成为字段，用来描述实体的某一个属性。

Mysql

安装过后安装（安装教程网上有很多）
在这里插入图片描述
这个软件。

创建表

在这里插入图片描述

基础命令

查看所有表: SHOW TABLES;
创建表:

CREATE TABLE IF NOT EXISTS  `students` (
	name VARCHAR(10) comment '学生的名字',
	age  int comment '学生的年龄',
	score int '学生的分数'
);

删除表 DROP TABLE IF EXISTS students;
查看表结构 DESC students;
查看创建表的sql语句 SHOW CREATE TABLE students;

数据结构

常见的

数字类型

整数

iNTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

在这里插入图片描述

浮点数

FLOAT(4字节) DOUBLE(8字节)

精确数字类型

DECIMAL, NUMERIC

日期类型

YEAR 年(YYYY 1901-2155或者0000)
DATE 日期（YYYY-MM-DD 1000-01-01到9999-12-31）
DATETIME YYYY-MM-DD hh:mm:ss(UTC)

字符串

CHAR 长度在0-255之间，查询的时候会删除后面的空格（固定长度）
VARCHAR 可变长度，长度为0-65535，被查询时不会删除后面空格/
BINARY VARBINARY 存储二进制字符串
BLOB 存储大的二进制类型
TEXT 存储大的字符串类型

表约束

主键 PRIMARY KEY 唯一。多列索引的叫做联合主键。
UNIQUE 唯一不重复，比如手机号码。但是UNIQUE允许多个Nul l，即nu l l可以重复。
NOT NULL
DEFAULT
AUTO_INCREAMENT 自动递增
外键

创建完整的表

CREATE TABLE IF NOT EXISTS `users` (
	id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
	age INT DEFAULT 0,
	phoneNum VARCHAR(20) UNIQUE,
	createTime DATETIME
);

修改表

修改表名字

 ALTER TABLE `users` RENAME TO `user`;

添加新的列

ALTER TABLE `user` ADD `updateTime` DATETIME comment '创建时间';

修改字段的名称

ALTER TABLE `user` CHANGE `phoneNum` `telPhone` VARCHAR(20);

修改表的类型

ALTER TABLE `user` MODIFY `telPhone` VARCHAR(30)

删除某个字段

ALTER TABLE `user` DROP `telphone`

根据表结构创建另一张表

CREATE TABLE `user1` LIKE `user`   //只会复制结构，不会复制数据

CREATE TABLE `user2` (SELECT * FROM `user`) //会复制数据

DML(数据操作语言) 对数据库进行增删改查

插入数据

insert into `students` VALUES (`lin`, 20, 90); 

# 通过指定列，插入到对应的数据中
insert into `students` (name, age, score)
					values (`linmoinha`, 30, 100),
					 (`linmoinha1`, 30, 100),
					 (`linmoinha2`, 30, 100);
# 自动设置值，改变值的类型并且如果没有传值的话，将当前的事件作为值
ALTER TABLE `students` MODIFY `createTime` TIMESTAMP DEfAULT CURRENT_TIMESTAMP;
# 自动设置值，并且在改变的时候，当没值的时候赋予默认值
ALTER TABLE `user` MODIFY `updateTIme` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
												 DEfAULT CURRENT_TIMESTAMP

删除数据

# 删除所有数据
DELETE FROM `students` 
# 删除id为10的数据  where
DELETE FROM `students` WHERE id = 10

更新数据

# 更新id为10的数据UPDATE `stuents` SET name = `luya`, age = 20 WHERE id = 10

DQL 数据查询语言

先创建表

create table if not exists `products` (	id INT primary key auto_increment,	brand VARCHAR(20),	title VARCHAR(100) not null,	price DOUBLE not null,	score DECIMAL(2,1),	voteCnt INT,	url VARCHAR(100),	pid INT);

查询数据

# 查询数据 SELECT 用于向一个表或者多个表进行查询
SELECT 字段列表 FROM 表名 WHERE 条件

# 查询所有字段
SELECT * FROM `products`;

# 查询对应字段,起别名
SELECT title, price as MyPrice FROM `products`;

# where条件查询
# 查询所有price小于10的数据
SELECT * FROM `products` WHERE price < 10;

## price小于10并且大于5
SELECT * FROM `products` WHERE price < 10 AND price > 5;
SELECT * FROM `products` WHERE price < 10 && price > 5;

# BETWEEN AND包含等于, 1000<=price<=2000
SELECT * FROM `products` WHERE price BETWEEN 1000 AND 2000;

# 联合查询
SELECT * FROM `products` WHERE price > 500 || brand = `华为`;

# 更新数据, 2000以下的手机都改为三星
UPDATE `products` SET brand = `三星` where price < 2000

# 模糊查询 LIKE关键字,只要字段有"为"就返回。%为,表示匹配以为结尾。为%表示匹配以为开头。%为%，只要匹配到为就成功。
# %表示匹配任意个，而_只匹配一个。如'_为%',就是匹配'x为xx...'这样的。_只匹配一个。
SELECT * FROM `products` WHERE brand LIKE '%为%'; 
SELECT * FROM `products` WHERE brand LIKE '_为%';

# IN 表示取多个值的其中一个即可，是华为，三星，苹果的任何一种就满足。
SELECT * FROM `products` WHERE brand IN ('华为', '三星', '苹果')

#  url不为null
SELECT * FROM `products` WHERE url IS NOT NULL

# 结果排序 ORDER BY  'age' ASC(生序) DESC(降序)
# 华为手机价格升序pid降序排序
SELECT * FROM `products` WHRER brand = '华为' ORDER BY PRICE ASC PID DESC

# 分页查询 LIMIT 限制， OFFSET偏移
# 从第21条开始查找10条,两种写法一样。
SELECT * FROM `products` LIMIT 10 OFFSET 20
SELECT * FROM `pruducts` LIMIT 20, 10

聚合函数

默认情况下将整个表的数据当作一组数据，对这一组数据进行操作。

# 求总和，对price进行求和
SELECT SUM(price) as  totalPrice FROM  `products`
;# 华为手机的价格总和
SELECT SUM(price) as totalPrice FROM `products` WHERE branh = '华为'
# 除了SUM,还有AVG(平均值), MAX, MIN, COUNT(出现的个数)
# DISTINCT是用来去掉重复数据的。
SELECT COUNT(DISTINCT price) as TotalPrice  FROM  `products`;

GROUP BY 分组

# 通过name进行分组，字段不能乱选，只能是函数或者是分组的字段。
因为如果同个分组有多个title，sql不知道显示哪条title。
SELECT  brand, MAX(id) FROM `products` GROUP BY brand;
# 分组查询，不能在group by中使用where，
只能用having,having后面跟着字段也只能是查询字段其中一个。
SELECT  brand, MAX(id) FROM `products` GROUP BY brand HAVING AVG(id) > 3;

创建多个表

create table if not exists `products1` 
(	id INT primary key auto_increment,
brand VARCHAR(20),	
title VARCHAR(100) not null,	
price DOUBLE not null,	
score DECIMAL(2,1),	
voteCnt INT,	
url VARCHAR(100),	
pid INT,	
brand_id int,	
// 定义外键brand_id，关联brandd的id	
constraint fk_brand_id foreign key(brand_id) references brand(id));

create table if not exists `brand` 
(	id int primary key auto_increment,
name varchar(20) not null,
website varchar(100),
phoneRank int);

insert into `brand` (name, website, phoneRank)
values('华为', 'www.huawei.com', 1),			
	('小米', 'www.huawei.com', 2),			
		('苹果', 'www.huawei.com', 5),			
			('三星', 'www.huawei.com', 4),		
					('oppo', 'www.huawei.com', 3);

外键

修改字段为外键

alter table `products` add `brand_id` int;# 将brand_id设置为外键，关联brand表的idalter table `products` add foreign key(brand_id) references brand(id)

外键的约束

外键在创建的时候有两个约束，一个是on delete时候的行为，一个是on update的行为。默认是 RESTRICT，当更新或者删除的时候，会检查该记录是否有关联的外键记录。有的话报错，不允许更新删除。NO ACTIO，和RESTRICT一样CASCADE: 跟RESTRICT一样，但是如果检查到有的话，更新的时候，会更新对应的记录，删除的时候，关联的记录会一并删除。比如brand表中的id改变的时候，会改变																																																							products中关联的brand_id。SET NULL： 检查到有的话会将对应的值置为null。

SQL语句
# 修改外键的action,先删除外键，再修改。
# 找到外键
show create table `products`
# 删除外键
alter table `products` drop froeign key products_ibfk_1;
# 重新添加外键约束
alter table `products` add foreign key (brand_id) references brand(id)
																									on update CASCADE
																									on delete RESTRICT;

多表查询(JOIN操作)

join用来连接表，大致分为内连接，外连接，右连接，左连接，自然连接.

普通join，就是强行将表连在一起

# A, B 是别名
select * from products as A join brand B;
# 筛选
select * from products as A join brand B where price < 20;
# on可以对单个表内最筛选，比如筛选出b中name为三星的数据再去join。
select * from products as A join brand B on B.name = '三星' where price < 20

这个查询后的数据的总数是A.length * B.length

A表的数据会跟B表的数据每条都会结合一下。称之为笛卡尔乘积。也成为直积。表示为x*y。

左连接，右链接，内连接，全连接

内连接取交集

# 找出b表中name等于a表中brand的数据,用on来判断
select * from products as A join brand B on B.name = A.brand

左连接取交集后，将左边的表剩余的数据也查出来

#找出b表中name等于a表中brand的数据，并且左表剩余的数据也会查出来。
select * from products as A left join brand B on B.name = A.brand

在这里插入图片描述

剩余的数据中字段属于b表的会为null。

右连接，跟左查询一样的道理。

where与on的区别

on是先做筛选，在做选择

where是先做选择在做筛选

select * from students where age > 20 # 拿到所有的数据，再whereselect * from students as A left join score B on A.age = B.age # 先筛选，找到符合A.age = B.age的条件再拿出来。

联合查询 union 左联合查出的数据+右联合查出的数据

(select * from products as A left join brand B on B.name = A.brand)union(select * from products as A right join brand B on B.name = A.brand)

子查询

在这里插入图片描述

select * from `users` where age = (select age from `students` where name = 'test')

查询在user表中，年龄跟test一样大的人物。
或者员工表，要查询在xx员工入职后的所有人；

select * from `users` where time > (select time from `users` where name = 'xx');

在这里插入图片描述
查询A部门和B部门在所有的员工

先拿到A部门和B部门的id
通过id在员工表拿到对应的用户信息

select * from `users` where dep_id in (select id from `dep` where name = 'A' || name = 'B') //多行一列

先where查询出id，在通过子查询从user表判断dep_id属于里面的。

在这里插入图片描述
查询入职时间和A一样，而且年龄一样大的员工

select time, age from `users` where name = 'A' //多列
// 假设time是2023,age是20
select * from `users` where time = 2023 && age = 20;
// 也可以这样写
select * from `users` where (time, age) = (2023, 20)
// 那么就可以用子查询
select * from `users` where (time, age) = (select time, age from `users` where name = 'A') //一行多列

在这里插入图片描述
多行多列，作为临时表，就是把

select * from `users` where age > 20;

把上述返回的内容作为表

select U.*, D.name from (select * from `users` where age > 20) as U, dep as D where U.time > 2023 && D.name === '教研部';

先查询user里面所有年龄大于20的员工(多行多列)作为临时表，再进行查询，查询入职时间为2023以后的员工和部门为教研部的的所有员工。

多对多关系

三种关系：一对一关系，一对多关系，多对多大关系。

# 创建数据create table if not exists students (	id int primary key auto_increment,	name varchar(20) not null,	age int);create table if not exists courses(	id int primary key auto_increment,	name varchar(20) not null,	price double not null)insert into students (name, age)			values('lin', 18),			('li', 22),			('a', 32),			('b', 24),			('d', 20),			('c', 16);				insert into courses (name, price)			values('语文', 68),			('英语', 122),			('数学', 42),			('物理', 89),			('化学', 90),			('地理', 76);

比如学生和课程。那么不仅要有学生表和课程表，还必须有一个关系表。不然关系全放在学生表的话，比如学生表的一个字段为cource，然后放着课程的id。那么就要写多个查询语句才能查询学生选择的课程。所以一般我们都会有一个关系表，来记录学生和课程之间的联系。

比如students_select_courses表，然后每一条数据都是记录着哪个学生选了哪个课程，如

id	students_id	courses_id
1	1(小明)	1(语文)
2	1(小明)	4(英语)
3	1(小明)	5(数学)

建立关系表

create table if not exists students_select_sources (
	id int primary key auto_increment,
	student_id int not null,
	courses_id int not null,
	foreign	key(student_id) references students1(id),
	foreign key(courses_id) references courses(id)
)

修改外键的约束
ALTER TABLE students_select_sources DROP FOREIGN KEY students_select_sources_ibfk_2;
ALTER TABLE students_select_sources ADD CONSTRAINT students_select_sources_ibfk_2 FOREIGN KEY (courses_id) REFERENCES courses(id) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE students_select_sources DROP FOREIGN KEY students_select_sources_ibfk_1;
ALTER TABLE students_select_sources ADD CONSTRAINT students_select_sources_ibfk_1 FOREIGN KEY (student_id) REFERENCES aaa.students1(id) ON DELETE RESTRICT ON UPDATE CASCADE;

查找所有学生选择的课程

join students_select_sources B on A.id = B.student_id (先找出students和关系表的交集)
join courses C on B.courses_id  = C.id ;（再通过id找出对应的课程表数据组合起来）

查找所有学生的选课情况，不仅要查找选的，还要查找没选的，所以要使用left join

select A.id as studentId, A.name as studentName, C.name as coursesName from students1 A  
left join students_select_sources B on A.id = B.student_id (查找交集并且还要获取没有选课的学生)
left join courses C on B.courses_id  = C.id ;（查找交集还有获取没有选课的学生）

查找没有选课的学生

select A.id as studentId, A.name as studentName, C.name as coursesName from students1 A  
left join students_select_sources B on A.id = B.student_id
left join courses C on B.courses_id  = C.id
where C.id is null; # 在后面再做个where判断即可

查找没有被学生选中的课程，反其道而行，查找所有课程的选课i情况，然后筛选出没有被选中的

select A.id as studentId, A.name as studentName, C.name as coursesName from students1 A  right join students_select_sources B on A.id = B.student_idright join courses C on B.courses_id  = C.idwhere A.id is null; # A.id为null就表示右连接中，A表字段为null，表示这门课没有被学生选中过。

查找某个学生的选课，查找所有学生的选课在where

select A.id as studentId, A.name as studentName, C.name as coursesName from students1 A  left join students_select_sources B on A.id = B.student_idleft join courses C on B.courses_id  = C.idwhere A.id = 2;

对象和数组类型 json_object(id,value…) json_arrayagg(A.name…)

将联合查询到的数据转为对象，如一对多的时候，查询一个商品表和详情信息，比如华为，华为的详情信息是一样的，但是华为有很多种手机，所以这时候华为的详情信息跟华为手机的种类就是一对多。所以要把华为的详情信息转为对象。比如

{
	type: 'p40',
	price: 5000,
	brand: {
		type: '华为',
		rank: 1,
		origin: 'Chinesee,
		id: 1,
	}

}

而不是
{
	type: 'p40',
	price: 5000,
	type: '华为',
	rank: 1,
	origin: 'Chinesee,
	id: 1,
}

select P.title title, P.price price, B.name brand  B.ranks ranks, B.origin origin  from products P 
left join brand B on P.brand = B.name ;

在这里插入图片描述

把这三个转为对象。

只需要利用json_object(id,value,id,value,id,vlaue.....)
select P.title title, P.price price, 
JSON_OBJECT('brand', B.name, 'ranks', B.ranks, 'origin', B.origin) brand
from products P left join brand B on P.brand = B.name ;

在这里插入图片描述

转换成功。

将查询到的数据组织成对象放入一个数组中。

select A.id as studentId,
json_arrayagg(json_object('name', A.name, 'courses',C.name)) array  //将查询到的数据放入一个数组中
from students1 A  
left join students_select_sources B on A.id = B.student_id
left join courses C on B.courses_id  = C.id
group by A.id;

在这里插入图片描述

select A.id as studentId, A.name students_name,
json_arrayagg(C.name) courses  //数组里只存放字符串数据。
from students1 A  
left join students_select_sources B on A.id = B.student_id
left join courses C on B.courses_id  = C.id
group by A.id;

在这里插入图片描述

事务

多条sql操作的时候使用事务，当一个失败之后，成功的sql将撤销。
在这里插入图片描述
默认一条sql就是一个事务。

事务四大特性
在这里插入图片描述

索引

当数据表内容越多时，查询就越慢。可以通过建立索引增加查询速度。

create index 索引名 on 表名(字段名);

创建完索引后再去select，速度会快几百倍几千倍。

在这里插入图片描述
索引是一种数据结构，对表内的数据进程处理，使其可以高效查询。索引类似书中的目录。

索引结构

在这里插入图片描述

node中操作数据库

借助mysql2这个库。

cnpm i mysql2

const mysql = require("mysql2");

//创建数据库连接，需要先建立连接，跟在软件上建立连接的操作是一样的。
const connection = mysql.createConnection({
  host: "localhost",
  port: 3306,
  database: "aaa",
  user: "root",
  password: '123456',
});

//执行sql语句
const statement = `select * from products;`;

//接受sql语句并且通过回调返回结果
connection.query(statement, (err, result, fields) => {
  console.log(result);
  connection.end()
  // connection.destroy() 强制清除
});

connection.on('error',()=>{})

最基本的连接，一样的步骤，先建立连接，再运行sql语句。命令行执行node index.js

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5OP9tcHe-1637806805677)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20211124231445843.png)]

预处理语句(提高效率)prepared statement

提高性能，将创建的语句模块发送给mysql,然后mysql编译语句模块，并且存储，但不执行。之后真正执行的时候会给 ‘?’ 提供实际的参数才会执行，就算多次执行，也只会编译一次，所以能优化性能。
防止sql注入，之后传入的值不会像模块引起那样就编译，，一些sql注入的内容不会被执行，or 1= 1不会被执行。

如
select * from products where price > 500 and score > 8;
1 性能较低
	* sql语句的执行过程：解析，优化，转换，执行；
	上面那条每次查询都会执行，如果10次就是重新解析优化。。。10次。
解决：
这时候如果有预处理语句：
select * from products where price > ? and score > ?;
prepare()//准备
query(6000,7)//真正查询
query(6000,7)//真正查询
query(6000,7)//真正查询
query(6000,7)//真正查询，不管查询多少次，该sql语句也只编译一次。
2 容易被sql注入(往sql语句中多注入一个东西，如where 1=1)。
select * from user where username = xx and password = 123; //通过正确的用户名和密码,通过客户端传过来，但如果用户又船多了一个命令，如or 1= 1
select * from user where username = xx and password = 123 or 1= 1; or 1= 1永远为true，所以它可以查看所有的用户信息，即使它密码错误。
解决问题：
依然是预处理。
prepare(select * from user where username = ? and password = ?;)
query('aa', 123)
因为预处理只会在一开始编译一次。
当你传信息进来的时候，只会接受两个信息，因为预处理没有or 1=1这个条件。所以预处理也可以防止sql注入。

//执行sql语句
const statement = `select * from products where price = ? && id < ?;`;

connection.execute(statement, [2000, 4], (err, result)=>{
    console.log(result);
})

只需要在编写sql的时候将参数作为？，然后通过connection.excute来执行该语句，第二个参数为入参。这样他就会先预处理，再执行。

当我们再次运行的时候，他会从LRU Cache（缓存）中获取。省略了编译的过程。

连接池connection pools

我们之前创建了一个连接，但是如果有多个请求的话，该连接很可能被占用。s是否需要每一次请求都去创建一个新的连接呢？

事实上，Mysql2提供了连接池(connection pools)
连接池可以在需要的时候自动创建连接，并且创建的链接不会被销毁，会放到连接池中，后续可以继续使用
可以在创建连接池的时候设置LIMIT，也就是最大创建个数。

const mysql = require("mysql2");

// 创建连接池
const connections = mysql.createPool({
    host: 'localhost',
    port: 3306,
    database: 'aaa',
    //连接池限制(不会在开始的时候创建很多个连接)
    connectionLimit: 10,
    user:'root',
    password: '123456',
})


// //执行sql语句
const statement = `select * from products where price = ? && id < ?;`;

//使用连接池，会去连接池里面直接找可以用的连接
connections.execute(statement, [2000, 4], (err, result)=>{
     console.log(result);
 })

创建连接池，再去执行sql的时候会直接去连接池获取可以用的连接。

使用promise的方式替换回调。

//使用连接池，会去连接池里面直接找可以用的连接
//connections.execute(statement, [2000, 4]);
connections
  .promise()
  .execute(statement, [2000, 4])
  .then(([results, err]) => {
    console.log(results);
  })
  .catch((err) => console.log(err));

使用mysql2提供的promise通过.then拿到数据。

认识ORM 对象关系映射

是一种程序设计的方案。提供一个可在编程语言中，使用虚拟对象数据库的效果。

数据库开发有两种：

1 在node中编写sql语句，通过mysql2提供给数据库执行。

2 ORM，不需要编写sql语句，增删改查只需要操作一个对象。比如this.sqlModel.find()…

常见的ORM库，java: Hibernate,MyBaits; node: Sequelize, Typeom

像mongodb，操作mongodb，每个表都会转成一个个类或者对象，操作数据库只需要操作这个类或者对象。比如this.xxx.find()，orm库会转化为sql语句再提供给数据库。

Sequelize的使用

第一步，创建一个Sequelize的对象，指定数据库，用户名，密码等
第二部：测试连接是否成功。

使用：cnpm i sequelize -D

const { Sequelize } = require('sequelize')

//连接 new Sequelize('数据库名'，'用户', '密码', {host:xx, dialect:'数据库类型'})
const sequelize = new Sequelize(
    'aaa', 'root','123456',{
        host: 'localhost',
        dialect: 'mysql'
    }
)



//测试连接

sequelize.authenticate().then(res=>{
    console.log('连接数据库成功', res);
}).catch(err=>{
    console.log('连接数据库失败', err);
})

coderlin_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
node数据库学习之mysql 1

数据库mysql 关系型数据库 //一张张表 (有关联的数据)mongodb 非关系型数据(文档型数据库) //一个个文档（数据量大，安全性较低）redis key-value键值对 //存放键值对（速度最快）数据库的基本概念实体只要在客观世界存在的，可以被描述出来的都是实体。如，人，狗，桌子等等数据库数据库就是数据的仓库，可以存放结构化的数据数据库管理系统(DBMS)是一种系统软件，提供操作数据库的环境，可以通过数据库管理系统对数据进行插入，修改，删除，查询等操作。SQL结构
复制链接

扫一扫