数据库

关系型数据库

关系数据库，是指采用了关系模型来组织数据的数据库简单来说，关系模型指的就是二维表格模型

非关系型数据库

非关系型数据库，又被称为NoSQL(Not Only SQL),意为不仅仅是SQL,对NOSQL最普遍的定义是非关联型的，强调Key-Value的方式存储。

数据库管理系统

数据库管理系统(Database Management System,简称DBMS)是为管理数据库而设计的软件系统

  • 包括三大部分构成。
    • 数据库文件集合:主要是一系列的数据文件，作用是存储数据。
    • 数据库服务器:主要负责对数据文件以及文件中的数据进行管理。
    • 数据库客户端:主要负责和服务端通信，向服务端传输数据或者从服务端获取数据。

SQL语句

数据库客户端和数据库服务端怎么通讯呢?
数据库客户端通过“特殊的语言”告诉服务端，客户端想要做什么.这个专门的语言就是SQL语句

  • SQL(Structured Query Language)是结构化査询语言，是一种用来操作RDBMS(Relational Database Management System 所谓的关系型数据库管理系统RDBMS)的数据库语言。
  • 当前几乎所有关系型数据库都支持使用SQL语言进行操作,也就是说可以通过 SQL 操作 oracle,sql server,mysql,sqlite 等等所有的关系型的数据库。

关系型数据库中核心元素（主键定义）

  • 字段:一列数据类型相同的数据
  • 记录:一行记录某个事物的完整信息的数据
  • 数据表:有若干字段和记录组成
  • 数据库:由若干数据表组成
  • 主键:唯一标识一行记录的字段,不能为空（可以由多个字段组成）
    alt text

搭建mysql环境

  • 安装 MySQL 数据库服务端：

    sudo apt-get install mysql-server

  • 安装 MySQL 数据库客户端：

    sudo apt-get install mysql-client

  • 启动 MySQL 数据库：

    sudo service mysql start

  • 停止 MySQL 数据库：

    sudo service mysql stop

  • 查看 MySQL 数据库启动状态：

    ps ajx|grep mysql

MySQL配置文件(了解)

配置文件目录为/etc/mysql/mysql.cnf:
进入mysql.conf.d目录，打开mysql.cnf，可以看到配置项:

主要配置选项

  • bind-address表示服务器绑定的ip，默认为127.0.0.1
  • port表示端口，默认为3306
  • datadir表示数据库目录，默认为/var/lib/mysql
  • general_log_file表示普通日志，默认为/var/log/mysql/mysql.log
  • log_error表示错误日志，默认为/var/log/mysql/error.log

MySql数据类型

  • 枚举类型
    枚举类型enum，在定义字段时就预告规定好固定的几个值，然后插入记录时值只能在这几个固定好的值中选择一个
    • 语法定义:
      gender enum('男','女','妖')
    • 应用场景:
      当值是几个固定可选时，比如:性别、星期、月份、表示状态时(比如:是、否)
  • decimal: 定点数
    • decimal(5,2)代表共5位数字，其中2位是小数，比如:888.88
  • char:定长字符串
    • char(10):不管实际使用,必然占用10个字符空间
  • varchar:变长字符串
    • varchar(10):根据实际使用占用空间

数据完整性和约束

1.数据完整性

数据完整性用于保证数据的正确性。系统在更新、插入或除等操作时都要检查数据的完整性，核实其约束条件。
参照完整性
参照完整性属于表间规则。在更新、插入或者删除记录时，如果只改其一，就会影响数据的完整性。如删除表2的某记录后，表1的相应记录未删除，致使这些记录成为孤立记录。

2.约束

约束类型 约束说明
NOT NULL 非空约束(设置非空约束，该字段不能为空)
PRIMARY KEY 主键约束(唯一性，非空性)
UNIQUE KEY 唯一约束(唯一性，可以空，但只能有一个)
DEFAULT 默认约束(该数据的默认值)
FOREIGN KEY 外键约束(需要建立两表间的关系)

MySql基本操作

  • 快捷键
    1. ctrl + a 快速回到行首
    2. ctrl + e 快速回到行尾
    3. ctrl + l 清屏
    4. ctrl + c 回车 结束
  • 数据库登陆和连接
    1. mysql -uroot -p密码 root是根用户(超级用户)
    2. quit/exit/ctrl + d 退出mysql
  • 显示数据库信息
    1. select version(); 显示数据库版本
    2. select now(); 显示时间

数据库基本操作命令

命令 作用 示例
show databases; 查看所有数据库 show databases;
select database(); 查看当前使用的数据库 select database();
create database数据库名 charset=utf8; 创建数据库 create database python charset=utf8;
use 数据库名; 使用数据库 use python;
drop database 数据库名; 删除数据库-慎重 drop database python;

在MySQL中，您可以使用以下SQL语句来查看创建数据库的SQL语句：

SHOW CREATE DATABASE database_name;

这条SQL语句将显示创建指定数据库的SQL语句，包括数据库名称和其默认字符集。您可以将 database_name 替换为实际的数据库名称来查看该数据库的创建语句。

数据表基本操作命令

命令 作用
show tables; 查看当前数据库中所有表
desc 表名; 查看表结构
show create table 表名 查看表的创建语句-详细过程
  • 创建表
    • int unsigned 无符号整形
    • auto_increment 表示自动增长
    • not null 表示不能为空
    • primary key 表示主键
    • default 默认值
    • create table 数据表名字(字段 类型 约束[，字段 类型 约束]);

数据表基本操作命令

命令 作用
ALTER TABLE 表名 ADD 列名 类型; 添加字段：向表中增加一个新的列，需指定列名和数据类型。
ALTER TABLE 表名 CHANGE 原名 新名 类型及约束; 重命名字段：更改现有列的名字，并可同时修改其数据类型和约束条件。
ALTER TABLE 表名 MODIFY 列名 类型及约束; 修改字段类型：更改指定列的数据类型及约束条件，不改变列名。
ALTER TABLE 表名 DROP 列名; 删除字段：从表中移除指定的列。
DROP TABLE 表名; 删除表：完全删除指定的表及其所有数据，这是一个不可逆的操作。

数据增删改查操作

  • 增加数据
命令形式 说明 示例
INSERT INTO 表名 VALUES (...); 全列插入：值的顺序需与表结构中的字段顺序一致。 INSERT INTO Students VALUES ('John Doe', 20, 'Computer Science');
INSERT INTO 表名 (列1,...) VALUES (值1,...); 部分列插入：仅指定列和对应的值，值的顺序与列出的列顺序对应。 INSERT INTO Students (Name, Major) VALUES ('Jane Doe', 'Mathematics');
INSERT INTO 表名 VALUES (...),(...)...; 一次性插入多行数据：在单个命令中插入多行，每行数据用逗号分隔。所有行都必须遵循全列插入的规则。 INSERT INTO Students VALUES ('Alice Smith', 19, 'Physics'), ('Bob Johnson', 21, 'Chemistry');
INSERT INTO 表名 (列1,...) VALUES (值1,...),(值1,...)...; 部分列多行插入：结合了部分列插入和一次性插入多行的特点，每对值集对应一行，并指定了列。 INSERT INTO Students (Name, Major) VALUES ('Eva Brown', 'Biology'), ('Sam Wilson', 'Engineering');
  • 修改查询数据
命令形式 说明 示例
SELECT * FROM 表名; 查询所有列数据：从指定表中选取所有列的所有数据行。 SELECT * FROM Employees;
SELECT 列1,列2... FROM 表名; 查询指定列数据：仅选取指定列的数据行。 SELECT FirstName, LastName FROM Employees;
UPDATE 表名 SET 列1=值1,列2=值2... WHERE 条件; 修改数据：更新表中满足指定条件的行的列值。 UPDATE Employees SET Salary=50000 WHERE Department='Sales';
SELECT 字段 as "别名", 字段 as "别名"... FROM 表名; 查询指定列(可以使用as为列或表指定别名)数据：仅选取指定列的数据行,并起别名 SELECT FirstName as "姓", LastName as "名" FROM Employees;
SELECT 别名.字段 FROM 表名 AS 表的别名 给表起别名 SELECT s.name FROM students AS s;
SELECT DISTINCT 字段 FROM 表名; 去重查询 SELECT DISTINCT gender FROM students;
  • 删除数据
  1. 物理删除
命令 作用
delete from 表名 where 条件; 删除数据
  1. 逻辑删除
    用一个字段来表示 这条信息是否已经不能再使用了
    给 students 表添加is_delete 字段 bit 类型
    举例:
ALTER TABLE students add is_delete BIT DEFAULT 0;
UPDATE students SET is_delete=l WHERE id=1;

1.WHERE关键字

1.1 where语句的作用

使用where子句对表中的数据筛选，结果为true的记录会出现在结果集中

  • 条件查询语法:

    select *from 表名 where 条件;
-- eg:  
select *from students where id=1;

  • where后面支持多种运算符，进行条件的处理:

    • 比较运算符
    • 逻辑运算符
    • 模糊查询
    • 范围查询
    • 空判断

1.2 比较运算符

常见的比较运算符

  • 等于: =
  • 大于: >
  • 大于等于: >=
  • 小于: <
  • 小于等于: <=
  • 不等于: != 或 <>
    -- eg:  其他运算符类似
select *from students where id < 1;
select *from students where age <= 18;

1.3 逻辑运算符

  • AND 表示有多个条件时，多个条件必须同时成立(值为True)
  • OR表示有多个条件时，满足任意一个条件时成立
  • NOT表示取反操作
-- 18和28岁之间的所有学生信息
select * from students where age > 18 and age < 28;
-- 失败(错误格式):
select * from students where 18<age<28;

-- 18岁以上的女性
select * from students where age > l8 and gender="女";

-- 18以上或者身高高过180(包含)以上
select * from students where age > 18 or height >= 180;
-- not
-- 不在 18岁以上的女性 这个范围内的信息
select * from students where not(age > l8 and gender="女");
-- 注意:使用 "()"   运算符优先级问题

1.4 模糊查询

  • like关键字
  • like后跟% 表示任意多个任意字符,_表示一个任意字符
    查询姓名中 以“小”开始的名字

select * from students where name like "小%"

1.5 范围查询

  • 知道between-and和in的区别
  • 范围查询分为连续范围查询和非连续范围查询:
    in 表示在一个非连续的范围内
    between .. and .. 表示在一个连续的范围内
-- 查询编号为3至8的学生:
select * from students where id between 3 and 8;
-- 注意:select* from students where age not(petween 18 and 34);是错误的

-- 查询年龄为18、34的姓名
select name, age from students where age in (18,34);

1.6 where之空值判断

select * from students where height is null;

order排序查询

  • asc 升序
  • desc 降序
    select name, age from students where age in (18,34) and gender = '男' order by height desc, age desc; 
-- 字段1 排序规则 ，字段2 排序规则
    可以按照多个字段排序，当第一字段数值相同，比较第二个字段，以此类推

聚合函数

命令 作用
count(字段) 计算包含非NULL值的指定字段的总行数
max(字段) 求指定字段的最大值
min(字段) 求指定字段的最小值
sum(字段) 求指定字段所有值的总和
avg(字段) 求指定字段的平均值

7.2 聚合函数的作用和特点

聚合函数作用:聚合函数会把当前所在表当做一个组进行统计
聚合函数有以下几个特点:

  • 每个组函数接收一个参数(字段名或者表达式)
  • 统计结果中默认忽略字段为NULL的记录
  • 不允许出现嵌套 比如sum(max(xx))
    -- 查询男性有多少人
select count(*) from students where gender=1;

-- 计算平均年龄
select avg(age) from students;
  • round函数
    -- round(123.23, 1)表示保留1位小数

-- 对年龄求平均值并保留两位小数
select round(avg(age),2) from students;

group分组查询

1.1 什么是分组

什么是分组?

所谓的分组就是将一个“数据集”划分成若干个“小区域”,然后针对若干个“小区域”进行数据处理。

group by分组

使用特点
group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
group by可用于单个字段分组，也可用于多个字段分组

-- 按照性别分组，查询所有的性别
select gender from students group by gender;
select name,gender from students group by gender;错误

-- 计算每种性别中的人数
select gender,count(*) from students group by gender;

-- group_concat()函数
-- 查询同种性别中的姓名
select group concat(name),gender from students group by gender;

-- 查询平均年龄超过30岁的性别，以及姓名 having avg(age)> 30(重点)
select group concat(name),gender from students group by gender having avg(age) > 30;

-- with rollup 汇总的作用(了解)
select count(*),gender from students group by gender with rollup;
-- with rollup的作用是:在数据表最后新增一行，来记录当前表中该字段对应的操作结果，一般是汇总结果

limit(限制)分页查询

1.引入

Q select * from 淘宝某个数据表，会产生什么现象?
A 由于数据过多很有可能直接死机了

2.limit限制查询

可以使用 limit 限制取出记录的数量，但 limit 要写在 sql语句的最后

语法

  • limit 起始记录,记录数

说明:

  • 起始记录是指从第几条记录开始取，第一条记录的下标是0
  • 记录数是指从起始记录开始向后依次取的记录数

连接查询

1.为什么要使用连接查询

内连接

内连接查询:查询的结果为两个表符合条件匹配到的数据
alt text

  • 语法:
    select 字段 from 表1 inner join 表2 on 表1.字段1=表2.字段2
  • 注意:
    1. 内连接:根据连接条件取出两个表“交集”
    2. on 是连接条件， where是连接后筛选条件

外连接

左外连接查询:查询数据为两个表匹配到的数据和左表的数据

  • 注意: 对于右表中不存在的数据使用null填充
    alt text
  • 语法
    -- 左连接:
主表 left join 从表 on 连接条件;
-- 右连接:
主表 right join 从表 on 连接条件;

-- 能够使用连接的前提是，多表之间有字段上的关联
-- 左连接和右连接区别在于主表在SQL语句中的位置,因此实际左连接就可以满足常见需求

自连接

使用自身和自身连接,第一次查询的结果被第二次查询时用到

select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle="广东省";

使用自连接査询 只需要使用一个表 可以加开査询速度 减少数据表占用空间
自连接查询本质就还是连接查询

子查询

子查询:把一个查询的结果当做另一查询的条件

子查询分为三类

  • 标量子查询:子查询返回的结果是一个数据(一行一列)
  • 列子查询:返回的结果是一列(一列多行)
  • 行子查询:返回的结果是一行(一行多列)
    -- 查出高于平均身高的信息
select from students where height >(select avg(height) from students);
-- 查出能够对应上班级号的学生信息
select from students where cls_id in(select id from classes);

MySql高级

删除异常

alt text

由于只存在商品信息一张表，其中既有商品信息又有分类名称和品牌名称。

当把某个商品信息删除后，本不应该删除的 <独立于商品信息的分类和品牌> 等信息结果也随着删除商品信息而删除，那么此时存在删除异常。

alt text

信息表优化

  • 第一步: 创建商品种类表
  • 第二步: 同步数据到商品种类表中
  • 第三步: 更新商品信息表数据
  • 第四步: 修改商品信息表表结构

外键

1.什么是外键？

  • 外键的作用
    • 外键有一个很重要的作用: 防止无效信息的插入
    • 外键约束可以使一个数据库的多张表之间建立关联，外键约束可以保证数据的参照完整性
  • 注意:
    • 关键字: foreign key,只有 innodb 数据库引擎才能使用外键
    • 外键本身也是一种约束 和 not null 等约束一样

例如，在成绩表的学号字段上建立外键约束，关联到学生表的学号字段

  • 此时，学生表称为“主表”，成绩表称为“从表”(或称“相关表”)

  • 一个表可以有多个外键

  • 设置了外键约束后，外键的值只能取主表中主键的值或空值，从而保证了参照完整性

image-20241128115642683

2.外键的使用

-- 添加外键
alter table goods add foreign key(cate_id) references goods_cates(id);

-- 如何取消外键约束
-- 需要先获取外键约束名称，该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table goods;
-- 获取名称之后就可以根据名称来删除外键约束
alter table goods drop foreign key goods_ibfk_1;
alter table goods drop foreign key goods_ibfk_2;
-- 注意:使用到外键约束会极大的降低表更新的效率,所以在追求读写效率优先的场景下一般很少使用外键
  • 在创建数据表的时候设置外键约束
    -- 创建数据表的同时直接创建外键
create table goods(
  id int primary key auto_increment not null,
  name varchar(40) default ' ',
  price decimal(5,2),
  cate_id int unsigned,
  brand_id int unsigned,
  is_show bit default 1,
  is_saleoff bit default 0,

  foreign key(cate_id)references goods_cates(id),foreign key(brand_id) references goods_brands(id)
);

视图

1.什么是视图

视图就是一个能够把复杂SQL语句的功能封装起来的一个虚表。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上

视图是对若干张基本表的引用，一张虚表，不存储具体的数据(基本表数据发生了改变，视图也会跟着改变)

视图的好处:方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性，复用性;

2.视图的使用

-- 定义(创建)视图
create view 视图名字(一般使用v开头) as select语句;
-- 查看视图
show tables;
-- 使用视图
select * from v_goods_info,
-- 删除视图
drop view 视图名称;
  • 总结
  1. 视图封装了对多张基本表的复杂操作，简化用户操作
  2. 视图只是一个虚表,并不存储任何基本表的表数据,当用户使用视图的时候。视图会从基本表中取出
  3. 通过视图可以对用户展示指定字段从而屏蔽其他字段数据，更加安全

事务

1. 事务的概念

事务Transaction,是指作为一个基本工作单元执行的一系列SQL语句的操作,要么完全地执行,要么完全地都不执行。

事务回滚

2.事务四大特性

用户 A 向用户 B 转账 500 元，该过程涉及两个核心操作：

  • 从 A 的账户扣除 500 元
  • 向 B 的账户增加 500 元
  1. 原子性(Atomicity)
    • 一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
    • 若系统在扣除 A 的 1000 元后、给 B 账户增加金额前崩溃：
      • 原子性要求这两个操作 “要么都成功，要么都失败”。此时事务未完成，系统会回滚，A 的账户金额恢复原状，B 的账户不变。
    • 反之，若两个操作都完成，则事务提交，状态永久保存。
  2. 一致性(Consistency)
    • 数据库总是从一致性的状态转换到另一个一致性的状态。
    • 假设 A 初始余额 2000 元，B 初始余额 1000 元，总余额 3000 元。
    • 事务执行中：
      • 若 A 扣除 1000 元后（A=1000 元），系统崩溃且未回滚，此时总余额变为 1000 元（A）+1000 元（B）=2000 元，与初始值不符，这违反一致性。
      • 但原子性保证事务回滚，A 的余额恢复为 2000 元，总余额仍为 3000 元，确保一致性。
    • 若事务成功完成：A=1000 元，B=2000 元，总余额 3000 元，状态合法。
    • (在前面的例子中，一致性确保了即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失500元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中)
  3. 隔离性(Isolation)
    • 通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。(在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去500元)
    • 假设事务 T1 正在执行转账（A 扣钱但未给 B 加钱），此时事务 T2 查询 A 和 B 的余额：
      • 隔离性要求 T2 看不到 T1 的中间状态（如 A 已扣钱但 B 未到账），只能看到 T1 提交前的原始状态（A=2000 元，B=1000 元）或提交后的最终状态（A=1000 元，B=2000 元）。
    • 若隔离性不满足，T2 可能读到 “中间数据”（如 A=1000 元，B=1000 元），导致数据查询错误。
  4. 持久性(Durability)
    • 一旦事务提交，则其所做的修改会永久保存到数据库。(此时即使系统崩溃，修改的数据也不会丢失。)
    • 当转账事务完成提交后，即使数据库服务器突然断电或崩溃：
      • 持久性保证 A 的账户扣除 1000 元、B 的账户增加 1000 元的结果会被永久记录，重启后数据依然有效。
    • 若未提交时系统崩溃，数据不会被保存（由原子性和一致性保证）。

3.事务的使用

  • 开启事务

开启事务后执行修改命令变更会维护到本地缓存中，而不维护到物理表中
begin; 或者start transaction;

  • 提交事务

将缓存中的数据变更维护到物理表中
commit;

  • 回滚事务

放弃缓存中变更的数据 表示事务执行失败 应该回到开始事务前的状态
rollback;

begin;
update students set age=10 where id=1;
commit;
-- 至此才完成一次事务,真正修改了数据

索引

1.索引的作用

思考:
如何在一个图书馆中找到一本书?
图书馆管理系统，大家要找书籍先在系统上查找到书籍所在的房屋编号和货架编号，然后就可以直接大摇大摆的去取书了。

我们把这种能够帮助我们快速查询数据的线索就称之为索引
如果数据库本身是一个字典，那么索引就是这个字典的目录

索引的本质
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的位置信息。

2.索引的使用

查看表中已有索引

show index from 表名;

创建索引

alter table 表名 add index 索引名【可选】(字段名,... );

删除索引

drop index 索引名称 on 表名;
-- 1. 创建测试表：

CREATE TABLE test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(10)
);

-- 2. 插入数据：

使用Python脚本或其他方式向`test`表中插入数据。这里不展示具体的Python脚本，但请确保插入足够的数据以便观察索引的效果。

-- 3. 没有索引时测试查询性能：

在测试之前，确保MySQL的profiling功能是开启的（如果您的MySQL版本支持）。但请注意，profiling在MySQL 8.0及更高版本中已被弃用。

-- 注意：MySQL 8.0+中SET profiling = 1; 已被弃用
-- 使用 EXPLAIN 替代
EXPLAIN SELECT * FROM test WHERE title = 'some_title';

-- 如果您使用的是MySQL 8.0或更高版本，直接使用`EXPLAIN`命令来分析查询的执行计划。

-- 4. 为`title`字段创建索引：

ALTER TABLE test ADD INDEX idx_title (title);

-- 5. 有索引时测试查询性能：

再次使用`EXPLAIN`命令来查看查询的执行计划，或者使用其他性能分析工具（如`Performance Schema`）来监控性能。

EXPLAIN SELECT * FROM test WHERE title = 'some_title';

-- 请注意，即使您使用的是支持profiling的MySQL版本，也建议优先使用`EXPLAIN`命令，因为它提供了更详细的信息，并且不需要在每次查询之前都开启profiling。

-- 6. 比较结果：

-- 比较没有索引和有索引时查询的执行计划，观察索引如何影响查询性能。在有索引的情况下，您应该能看到查询速度的提升，特别是当查询条件能够充分利用索引时。
  • 优点:

加快数据的查询速度

  • 缺点:

创建索引会浪费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间会越来越多

  • 使用原则:
  1. 经常发生数据更新的表避免使用过多的索引
  2. 数据量小的表没有必要使用索引
  3. 数据量较大同时不会频发发生数据更改的表可以使用索引

数据库设计之三范式

1.什么是三范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式

各种范式呈递次规范，越高的范式数据库几余越小。

2.范式的划分

数据冗余是指数据之间的重复，也可以说是同一数据存储在不同数据文件中的现象

alt text

3.范式的划分

根据数据库冗余的大小,目前关系型数据库有六种范式,各种范式呈递次规范，越高的范式数据库冗余越小。

六种范式:

  • 第一范式(1NF)
  • 第二范式(2NF)
  • 第三范式(3NF)
  • 巴斯-科德范式(BCNF)
  • 第四范式(4NF)
  • 第五范式(5NF，又称完美范式)

一般遵循 前三种范式即可

第一范式1NF

第一范式(1NF):强调的是字段的原子性，即一个字段不能够再分成其他几个字段
alt text

第二范式2NF

第二范式(2NF):满足 1NF的基础上，另外包含两部分内容

一是表必须有一个主键
二是非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分（意思是关系中非主键字段必须可以由主键唯一确定）

alt text

alt text

第三范式3NF

第三范式(3NF):满足 2NF
另外非主键字段必须直接依赖于主键，不能存在传递依赖。

即不能存在:非主键字段 A 依赖于非主键字段 B,非主键字段 B 依赖于主键的情况

alt text

alt text

总结

范式:
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

  1. 第一范式（1NF, First Normal Form）:

    • 强调列的原子性。即数据库表的每一列都是不可分割的原子数据项，不能再分。
    • 所有的列都是单一的数据类型，如整数、字符串等。
    • 如果某个列包含多个值（如一个列存储多个电话号码），则需要拆分该列，使得每一行数据都是唯一的，且每列数据都是原子的。

  2. 第二范式（2NF, Second Normal Form）:

    • 必须满足1NF。
    • 表必须有一个主键，主键可以是一个列或多个列的组合（复合主键）。
    • 所有的非主键列必须完全依赖于整个主键，而不是主键的一部分。
    • 如果存在复合主键，那么非主键列不能仅依赖于复合主键中的某一个字段，它必须依赖于整个复合主键。

  3. 第三范式（3NF, Third Normal Form）:

    • 必须满足2NF。
    • 任何非主键列不能传递依赖于主键。
    • 这意味着，非主键列A不能依赖于非主键列B，而列B又依赖于主键。如果存在这样的依赖关系，就需要将列A和列B（以及它们相关的其他列）分离到另一个新的表中，并在两个表之间建立关联关系（如使用外键）。

第三范式的主要目的是减少数据冗余和更新异常。通过消除传递依赖，我们可以确保数据的一致性，并在更新数据时避免不必要的麻烦。

E-R模型和表间关系

E-R模型即实体-关系模型
E-R模型就是描述数据库存储数据的结构模型

三种关系

表现形式

  • 实体: 用矩形表示，并标注实体名称
  • 属性: 用椭圆表示，并标注属性名称
  • 关系: 用菱形表示，并标注关系名称
    E-R模型中的三种关系
  • 一对一
  • 一对多(1-n)
  • 多对多(m-n)
    alt text

数据库安全性

概述

  • 不安全因素
    1. 非授权对数据库的恶意存取和破坏
    2. 数据库中重要的数据泄露
    3. 安全环境的脆弱性

自主存取控制方法

  1. grant revoke
    grant <权限> on table <表名> to <用户>
revoke<权限> on table <表名> from <用户>

用户、角色

创建用户
create user <username> with

安全性控制

  • 数据库安全性控制
    • 用户身份鉴别
      • 静态口令鉴别
      • 动态口令鉴别
      • 生物特征鉴别
      • 智能卡鉴别
    • 存取控制
    • 自主存取控制方法(重点讲解)；字面意思就是我们用户可以自定义和分配其他用户的操作权限
      主要通过grant  revoke来进行控制
由两个元素构成:  数据库对象 和 操作权限
我们定义用户的存取权限 成为授权

主要存取权限
Reference权限代表是否允许创建外键

4、授权 Grant
Grant单词翻译:
vt.承认;同意;准许;授予
n.拨款;补助金;授给物(如财产、授地、专有权、补助、拨款等)

Grant <权限> on 表名[(列名)] to 用户 With grant option
授权命令是由数据库管理员使用的，若给用户分配权限时带 Withgrant option 子句，则普通用户获权后,可把自己的权限授予其他用户。

grant all privilege on 视图或者表 to usr1;

5回收权限 revoke 
REVOKE <权限> 0N <数据对象> FR0M <数据库用户名> cascade(级联)

revoke翻译:
vt.撤销，取消;废除

alt text

数据库角色

数据库角色（Role）是数据库管理系统（DBMS）中用于管理权限的一种机制。通过角色，数据库管理员（DBA）可以为一组用户分配相同的权限，而无需单独为每个用户设置权限。这大大简化了权限管理过程，提高了安全性和可管理性。

角色的基本概念

  • 角色：代表了一类用户，如CEO、总监、普通职员等。通过角色，可以为这些用户组分配相同的权限。

角色的操作

1. 角色的创建

使用CREATE ROLE语句创建角色。例如：

CREATE ROLE CEO;

2. 角色授权

使用GRANT语句为角色分配权限。例如，为CEO角色授予Student表上的SELECT权限：

GRANT SELECT ON Student TO CEO;

如果要为多个角色分配权限，可以在TO子句后列出这些角色，用逗号分隔：

GRANT SELECT ON Student TO CEO, Director;

3. 把角色授权给其他用户或角色

使用GRANT语句将角色授权给其他用户或角色。如果加上WITH ADMIN OPTION，则意味着被授权的用户或角色还可以将这个角色及其权限进一步授予其他用户或角色。例如：

GRANT CEO TO User1 WITH ADMIN OPTION;

4. 角色权限的收回

使用REVOKE语句收回角色的权限。例如，从CEO角色中收回Student表上的SELECT权限：

REVOKE SELECT ON Student FROM CEO;

如果要从用户或角色中收回角色本身，也需要使用REVOKE语句：

REVOKE CEO FROM User1;

注意事项

  • 在使用角色时，要确保遵循数据库的安全策略和最佳实践。
  • 不要为不必要的角色分配过多的权限，以减少潜在的安全风险。
  • 定期审查和更新角色的权限设置，以确保其与组织的需求和策略保持一致。

Python数据库编程

1.Python连接MySQL

1.1 PyMysql模块

如果使用之前学习的MySQL客户端来完成这个操作，那么这个工作量无疑是巨大的

我们可以通过使用程序代码的方式去连接MySQL数据库，然后对MySQL数据库进行增删改查的方式，实现10000条数据的插入，像这样使用代码的方式操作数据库就称为数据库编程

安装pymysql第三方包:sudo pip3 install pymysql

1.2 使用PyMysql模块

pymysql使用步骤

  1. 导入pymysql包
  2. 创建连接对象
  3. 获取游标对象
  4. pymysql完成数据的增删改查操作
  5. 关闭游标和连接

1.3 python代码

在使用 pymysql 这个 Python 库与 MySQL 数据库进行交互时，通常需要遵循以下步骤。以下是详细的步骤说明：

① 导入 pymysql 模块

在使用 pymysql 之前，首先需要导入该模块。

import pymysql

② 创建连接对象

调用 pymysql 模块中的 connect() 函数来创建连接对象。你需要提供一系列参数来配置你的连接。

conn = pymysql.connect(
    host='localhost',       # 连接的mysql主机，如果本机是'localhost'
    port=3306,              # 连接的mysql主机的端口，默认是3306
    user='yourusername',    # 连接的用户名
    password='yourpassword',# 连接的密码
    database='yourdb',      # 数据库的名称
    charset='utf8'          # 通信采用的编码方式，推荐使用utf8
)

③ 获取游标对象

获取游标对象的目标是要执行 SQL 语句，完成对数据库的增、删、改、查操作。

cur = conn.cursor()

④ 使用游标执行 SQL 语句

使用游标对象的 execute() 方法来执行 SQL 语句。返回受影响的行数，主要用于执行insert、update、delete、select等语句

# 插入数据
sql = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"
cur.execute(sql, ('value1', 'value2'))

# 更新数据
sql = "UPDATE your_table SET column1=%s WHERE column2=%s"
cur.execute(sql, ('new_value1', 'some_condition'))

# 删除数据
sql = "DELETE FROM your_table WHERE column2=%s"
cur.execute(sql, ('some_condition',))

# 查询数据
sql = "SELECT * FROM your_table WHERE column2=%s"
cur.execute(sql, ('some_condition',))

⑤ 获取查询结果

对于查询语句，可以使用游标对象的 fetchone() 或 fetchall() 方法来获取结果。

# 获取查询结果集中的一条数据
row = cur.fetchone()
print(row)  # 输出如: (1, '张三')

# 获取查询结果集中的所有数据
rows = cur.fetchall()
print(rows)  # 输出如: [(1, '张三'), (2, '李四')]

注意：在调用 fetchone() 或 fetchall() 后，游标的位置会移动到下一条数据或末尾。如果你需要再次遍历结果集，通常需要重置游标位置或使用新的游标对象。

⑥ 提交或回滚事务

如果执行了修改数据（如 INSERT、UPDATE、DELETE）的 SQL 语句，并且你希望这些更改被保存到数据库中，那么你需要调用连接对象的 commit() 方法。如果希望在出错时撤销这些更改，可以调用 rollback() 方法。

# 提交事务
conn.commit()

# 或者回滚事务
conn.rollback()

⑦ 关闭游标和连接

在完成数据库操作后，需要关闭游标和连接以释放资源。

# 关闭游标
cur.close()

# 关闭连接
conn.close()

注意：在实际应用中，应该使用 try...except...finally 语句块来确保即使在发生异常时也能正确地关闭游标和连接。

SQL语句参数化

1.SQL注入

什么是SQL注入?
用户提交带有恶意的数据与SQL语句进行字符串方式的拼接，从而影响了SQL语句的语义，最终产生数据泄露的现象。

SQL注入是一种网络安全漏洞，攻击者可以通过在输入字段中插入恶意的SQL代码来访问或修改数据库中的数据。以下是一个简单的SQL注入案例，用于说明其工作原理和潜在危害。

案例背景

假设我们有一个简单的用户登录页面，用户需要输入用户名和密码来登录系统。登录页面的后台代码可能使用类似以下的SQL查询来验证用户输入：

SELECT * FROM users WHERE username='$username' AND password='$password';

这里的$username和$password是用户输入的用户名和密码，它们被直接嵌入到SQL查询中。

SQL注入攻击

  1. 攻击者输入：
    攻击者尝试在用户名或密码字段中输入特殊字符来修改原始的SQL查询。例如，攻击者可能在用户名字段中输入' OR '1'='1（注意这里的单引号用于闭合原始查询中的$username变量），这样原始的SQL查询就会变为：

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

    由于'1'='1'始终为真，因此这个查询会返回数据库中的所有用户记录，而不管密码是什么。

  2. 结果：
    因为SQL查询被篡改，攻击者能够绕过登录验证，并可能访问到系统内的敏感信息或执行其他恶意操作。

防范措施

为了防止SQL注入攻击，应该采取以下措施：

  1. 使用参数化查询：避免直接在SQL查询中嵌入用户输入，而是使用参数化查询或预编译的语句。这样，即使用户输入包含特殊字符，它们也不会被解释为SQL代码的一部分。

  2. 输入验证和过滤：对用户输入进行严格的验证和过滤，确保它们只包含预期的字符和数据类型。

  3. 最小权限原则：为数据库连接分配最小的必要权限，以减少潜在的安全风险。

  4. 使用ORM框架：对象关系映射（ORM）框架可以帮助管理数据库操作，减少手动编写SQL查询的需要，从而降低SQL注入的风险。

  5. 定期更新和维护：保持数据库软件、Web服务器和应用程序的最新版本，以利用最新的安全特性和修复潜在的安全漏洞。

  6. 使用防火墙和入侵检测系统：这些工具可以帮助检测和阻止潜在的攻击行为，提高系统的安全性。

2.如何防止SQL注入

SQL语句参数化

  • SQL语言中的参数使用%s来占位，此处不是python中的字符串格式化操作
  • 将SQL语句中%s占位所需要的参数存在一个列表中，把参数列表传递给execute方法中第二个参数

不安全的方式

  • sql ="select * from goods where name='%s'" % find_name
  • cs.execute(sql)

安全的方式

  • 构造参数列表
    • params = [find_name]
  • 执行select语句
    • sql = "select * from goods where name=%s"(这里的%s不需要加引号)
    • cs.execute(sql,params)