NodeJs 第六章 简单了解数据库(MySql)

数据库简介

什么是数据库

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据

数据库能做些什么

• 持久的存储数据（数据存储在硬盘文件中）
• 备份和恢复数据
• 快速的存取数据
• 权限控制

数据库类型

关系型数据库

• 特点：以表和表的关联构成的数据结构
• 优点
  • 能表达复杂的数据关系
  • 强大的查询语言，能精确查找想要的数据
• 缺点
  • 读写性能比较差，尤其是海量数据的读写
  • 数据结构比较死板
• 用途：存储结构复杂的数据
• 代表
  • Oracle
  • MySql
  • Sql Server

非关系型数据库

• 特点：
  • 以极其简单的结构存储数据
  • 文档型
  • 键值对
• 优点
  • 格式灵活
  • 海量数据读写效率很高
• 缺点
  • 难以表示复杂的数据结构
  • 对于复杂查询效率不好
• 用途：存储结构简单的数据
• 代表
  • MongoDB
  • Redis
  • Membase

关于数据库的专业术语

• DB： database 数据库
• DBA：database administrator 数据库管理员
• DBMS：database management system 数据库管理系统
• DBS：database system 数据库系统（DBS包含DB、DBA、DBMS）

MySql

• 特点： 开源、轻量、快速
• 安装
  • 官方下载源
  • 腾讯下载源
• 可视化工具： navicat

数据库设计

SQL简介和对应分支

SQL(Structured Query Language) 结构化查询语言。大部分关系型数据，拥有着基本一致的SQL语法。

• DDL(Data Definition Language) 数据定义语言,操作数据库对象的 库、表、视图、存储过程
• DML(Data Manipulation Language) 数据操控语言, 操作数据库中的记录
• DCL(Data Control Language) 数据控制语句, 操作用户权限

如何创建表

1. 方式一： 通过 SQL 语句

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

2. 方式二： 通过可视化工具创建表

创建表需要填写的内容

• 字段名
• 字段类型
  • bit：占1位，0或1，false或true
  • int：占32位，整数
  • decimal(M,N)：能精确计算的实数，M是总的数字位数，N是小数位数
  • char(n)：固定长度位n的字符
  • varchar(n)：长度可变，最大长度位n的字符
  • text：大量的字符
  • date：仅日期
  • datetime：日期和时间
  • time：仅时间
• 是不是null
• 自增
• 默认值
• 主键
  • 根据设计原则，每张表都要有主键
  • 主键必须满足的要求
    • 唯一
    • 不能更改
    • 无业务含义
• 外键
  • 用于产生表关系的列
  • 外键列会连接到另一张表（或自己）的主键

如何通过 SQL 操作表的增删改查

增加数据

INSERT INTO `test`(`name`) VALUES('wangwu')

删除数据

DELETE FROM test WHERE id=1

修改数据

UPDATE test SET `name`='lisi' WHERE id=1

查找数据

SELECT `name` FROM test WHERE `name` = 'lisi'

表单操作基本SQL语句

select ...
from ...
where ...
order by ...
limit ...

• select
• from
• where：过滤查询结果
  • =、>、 <、 >=、 <=、!=
  • in
  • is
  • is not
  • between
  • like
  • and
  • or
• order by
  • asc
  • desc
• limit
  • n,m 跳过n条数据，取出m条数据

运行顺序

from > where > select > order by > limit

如何联表查询

什么是笛卡尔积

笛卡尔积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

笛卡尔积又叫笛卡尔乘积，是一个叫笛卡尔的人提出来的。 简单的说就是两个集合相乘的结果。

假设集合A={a, b}，集合B={0, 1,2}，则两个集合的笛卡尔积为{(a, 0),(a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

多表连接

在关系数据库中，一个查询往往会涉及多个表，因为很少有数据库只有一个表，而如果大多查询只涉及到一个表的，那么那个表也往往低于第三范式，存在大量冗余和异常。

笛卡尔积在SQL中的实现方式既是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合,上图中两个表连接即为笛卡尔积(交叉连接)

内连接

内连接可以看做先对两个表进行了交叉连接后，再通过加上限制条件(SQL中通过关键字on)剔除不符合条件的行的子集,得到的结果就是内连接了

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

左链接(LEFT JOIN )
LEFT JOIN 返回左表的所有行，并包括右表中匹配的行，如果右表中没有匹配的行，将返回 NULL 值

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

右链接(RIGHT JOIN)

RIGHT JOIN 返回右表的所有行，并包括左表中匹配的行，如果左表中没有匹配的行，将返回 NULL 值

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

mysql 内置函数

数学函数

• ABS(x) 返回x的绝对值
• CEILING(x) 返回大于x的最小整数值
• FLOOR(x) 返回小于x的最大整数值
• MOD(x,y) 返回x/y的模（余数）
• PI() 返回pi的值（圆周率）
• RAND() 返回０到１内的随机值
• ROUND(x,y) 返回参数x的四舍五入的有y位小数的值
• TRUNCATE(x,y) 返回数字x截短为y位小数的结果

聚合函数

• AVG(col) 返回指定列的平均值
• COUNT(col) 返回指定列中非NULL值的个数
• MIN(col) 返回指定列的最小值
• MAX(col) 返回指定列的最大值
• SUM(col) 返回指定列的所有值之和

字符函数

• CONCAT(s1,s2…,sn) 将s1,s2…,sn连接成字符串
• CONCAT_WS(sep,s1,s2…,sn) 将s1,s2…,sn连接成字符串，并用sep字符间隔
• TRIM(str) 去除字符串首部和尾部的所有空格
• LTRIM(str) 从字符串str中切掉开头的空格
• RTRIM(str) 返回字符串str尾部的空格

日期函数

• CURDATE()或CURRENT_DATE() 返回当前的日期
• CURTIME()或CURRENT_TIME() 返回当前的时间
• TIMESTAMPDIFF(part, date1,date2) 返回date1到date2之间相隔的part值，part是用于指定的相隔的年或月或日等
  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR加粗样式

什么是驱动程序

驱动程序是连接内存和其他存储介质的桥梁。mysql驱动程序是连接内存数据和mysql数据的桥梁，mysql驱动程序通常使用mysql、mysql2(mysql-native)

SQL 注入

用户通过注入sql语句到最终查询中，导致了整个sql与预期行为不符

假设有一个登录系统，用户通过输入用户名和密码进行身份验证：

SELECT * FROM users WHERE username = 'input_username' AND password = 'input_password';

如果没有正确的输入验证和防范措施，攻击者可以输入类似于以下内容的用户名：

' OR '1'='1'; --

在这种情况下，SQL 查询会变成：

SELECT * FROM users WHERE username = '' OR '1'='1'; --' AND password = 'input_password';

如何防范SQL 注入

使用参数化查询或预编译语句： 使用参数化查询（Prepared Statements）可以有效防止 SQL 注入，因为它们在执行查询之前将输入数据与查询语句分离。

输入验证和转义： 对用户输入进行适当的验证，并使用合适的转义函数（如mysqli_real_escape_string）来处理输入，以防止恶意注入。

最小权限原则： 给予数据库用户最小的权限，确保它们只能执行必要的操作，以降低潜在的损害。

使用ORM框架： 使用对象关系映射（ORM）框架（如Hibernate、Sequelize）可以帮助抽象 SQL 查询，从而降低 SQL 注入的风险。

禁用错误消息显示： 在生产环境中，禁用显示详细的错误消息，以防止攻击者获取有关数据库结构的敏感信息。