。

关联关系是指两个或多个表之间的关系#xff0c;通过共享相同的列或键来建立连接。

常见的关联关系有三种类型#xff1a;一对多关系#xff0c;…MySQL基础二

在关系型数据库中表之间可以通过关联关系进行连接和查询。

关联关系是指两个或多个表之间的关系通过共享相同的列或键来建立连接。

常见的关联关系有三种类型一对多关系多对多关系以及一对一关系。

1.1、一对多

在一对多关系中一个表的记录可以与另一个表中的多个记录相关联而另一个表的记录只能与一个表中的记录相关联有且只有一个一对多。

建立一对多的关系在‘多’的表中创建关联字段

CREATE

2022-2023);在上述示例中将班级相关的字段直接添加到学生表中会产生一些问题比如可能会导致冗余数据。

如果多个学生属于同一个班级那么每个学生记录都会重复存储相同的班级信息。

这样的冗余数据可能浪费存储空间并增加数据的不一致性和更新的复杂性。

为了解决这些问题更好的做法是使用关联表来处理学生和班级之间的一对多关系。

CREATE

中比较常用的查询方法通过子查询可以实现多表关联查询。

子查询指将一个查询语句嵌套在另一个查询语句中。

1.2、多对多

学生表Students存储学生的信息。

课程表Courses存储课程的信息。

选课表Enrollment作为中间表存储学生和课程之间的关联关系。

作者表Authors存储作者的信息。

书籍表Books存储书籍的信息。

作者-书籍关系表Author_Book作为中间表存储作者和书籍之间的关联关系。

用户表Users存储用户的信息。

角色表Roles存储角色的信息。

用户-角色关系表User_Role作为中间表存储用户和角色之间的关联关系。

商品表Products存储商品的信息。

订单表Orders存储订单的信息。

订单-商品关系表Order_Product作为中间表存储订单和商品之间的关联关系。

标签表Tags存储标签的信息。

文章表Articles存储文章的信息。

文章-标签关系表Article_Tag作为中间表存储文章和标签之间的关联关系。

当在学生表和课程表中直接使用course_id和student_id来表示多对多关系时会导致数据冗余。

这意味着课程信息将在课程表中重复存储而每个学生选修的课程也将在学生表中重复存储。

要实现多对多一般都需要有一张中间表也叫关联表将两张表进行关联形成多对多的形式。

如果将学生和课程的关联关系直接存储在学生表和课程表中那么数据将如下所示

CREATE

建立一对一的表关系关联字段必须唯一unique这两个表都可以放。

但是最好将关联字段放在详情表中因为并不是每个学生都有详情信息。

如果放在主要信息表中没有详情信息的字段还需要置空。

一对一关系One-to-One

Relationship指的是两个实体之间存在一种对应关系其中一个实体的每个记录只能对应另一个实体的一条记录而另一个实体的每个记录也只能对应一个实体的记录。

在一对一关系中关联字段在每个表中都加上了唯一约束以确保关联的唯一性。

学生Students存储用户的基本信息学生详情表StudentDetails存储学生地址、联系方式等。

每个学生只有一条详情记录。

员工表Employees存储员工的基本信息如员工号、姓名、职位等。

薪资信息表Salary存储员工的薪资信息如基本工资、津贴等。

每个员工在薪资信息表中只有一条记录与员工表中的对应记录关联。

将两个实体分开存储在不同的表中可以更好地保持数据的规范性和完整性。

每个表都可以有自己的约束和验证规则确保数据的一致性和有效性。

数据隐私和安全性

笛卡尔积查询会通过n*m的组合所查询的表。

然后通过where过滤用来拼接表。

但是where一般更喜欢去做逻辑上的过滤

SELECT

Students.classidClasses.id;所以就有了join关联表查询

join

Classes.ID;多对多的join是基于第三张表然后把三张表拼接成一张大表在进行过滤。

【1】笛卡尔积查询

product是指两个集合之间的所有可能的组合。

在关系型数据库中笛卡尔积是指两个表之间的所有可能的行组合。

如果有两个表A和B每个表包含n和m行那么它们的笛卡尔积将产生n

SELECT

查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。

案例1多对一拼接

查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。

SELECT

From

StudentCourses.StudentID;SELECT

FROM

表2.连接字段;上述语法中“表1”为基表“表2”为参考表。

左连接查询时可以查询出“表1”中的所有记录和“表2”中匹配连接条件的记录。

如果“表1”的某行在“表2”中没有匹配行那么在返回结果中“表2”的字段值均为空值NULL。

SELECT

StudentCourses.StudentID右外连接又称为右连接右连接是左连接的反向连接。

使用

RIGHT

与左连接相反右连接以“表2”为基表“表1”为参考表。

右连接查询时可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。

如果“表2”的某行在“表1”中没有匹配行那么在返回结果中“表1”的字段值均为空值NULL。

全连接FULL

全连接返回左表和右表中的所有行无论是否有匹配。

如果某个表的行没有匹配结果集中对应的列将包含NULL值。

三、外键约束

外键约束是关系数据库中的一种机制用于维护表之间的关联关系和数据完整性。

外键约束经常和主键约束一起使用用来确保数据的一致性。

外键约束FOREIGN

KEY是表的一个特殊字段经常与主键约束一起使用。

对于两个具有关联关系的表而言相关联字段中主键所在的表就是主表父表外键所在的表就是从表子表。

外键用来建立主表与从表的关联关系为两个表的数据建立连接约束两个表中数据的一致性和完整性。

主表删除某条记录时从表中与之对应的记录也必须有相应的改变。

一个表可以有一个或多个外键外键可以为空值若不为空值则每一个外键的值必须等于主表中主键的某个值。

比如上面的书籍管理案例若删除一个ID1的班级记录没有任何影响但是学生表中ClassID

【1】创建表时设置外键约束

FK_StudentCourses_StudentsFOREIGN

KEY

FK_StudentCourses_CoursesFOREIGN

KEY

RESTRICT如果关联的主表中的数据被删除且从表中存在对应的数据则禁止删除操作。

ON

DELETE

CASCADE当关联的主表中的数据被删除时自动删除从表中对应的数据。

ON

DELETE

NULL当关联的主表中的数据被删除时从表中对应的数据将被设置为

DELETE

DEFAULT当关联的主表中的数据被删除时从表中对应的数据将被设置为默认值。

ON

UPDATE

RESTRICT如果关联的主表中的数据更新且从表中存在对应的数据则禁止更新操作。

ON

UPDATE

CASCADE当关联的主表中的数据更新时自动更新从表中的对应数据。

ON

UPDATE

DEFAULT当关联的主表中的数据更新时从表中对应的数据将被设置为默认值。

语句可以在创建或修改外键约束时使用以定义在不同操作更新或删除发生时从表中的数据应该如何处理。

请注意这些

MySQL内置函数是MySQL数据库提供的一组函数用于在SQL查询中执行各种操作和计算。

以下是一些常用的MySQL内置函数

字符串函数

...)将多个字符串连接成一个字符串。

UPPER(str)将字符串转换为大写。

LOWER(str)将字符串转换为小写。

SUBSTRING(str,

start,

length)返回字符串的子串。

LENGTH(str)返回字符串的长度。

TRIM(str)去除字符串两端的空格。

REPLACE(str,

old,

d)将一个数四舍五入到指定的小数位数。

FLOOR(x)返回不大于给定数的最大整数。

CEILING(x)返回不小于给定数的最小整数。

RAND()返回一个0到1之间的随机数。

日期和时间函数

NOW()返回当前日期和时间。

CURDATE()返回当前日期。

CURTIME()返回当前时间。

DATE_FORMAT(date,

format)将日期格式化为指定的格式。

DATEDIFF(date1,

date2)计算两个日期之间的天数差。

DATE_ADD(date,

INTERVAL

COUNT(expr)计算满足条件的行数。

SUM(expr)计算指定列的总和。

AVG(expr)计算指定列的平均值。

MIN(expr)计算指定列的最小值。

MAX(expr)计算指定列的最大值。

这只是MySQL内置函数的一部分还有许多其他函数可用于字符串处理、数值计算、日期时间操作、聚合计算等。

五、Python操作MySQL

pymysql.connect(hostlocalhost,useryour_username,passwordyour_password,databaseyour_database

创建游标对象

conn.close()在上述示例中首先使用pymysql.connect()函数连接到MySQL数据库。

您需要提供数据库的连接参数如主机名、用户名、密码和数据库名称。

然后使用连接对象的cursor()方法创建一个游标对象。

游标用于执行SQL查询并获取结果。

执行SQL查询时可以使用游标对象的execute()方法传递SQL查询语句。

在示例中我们执行了一个简单的SELECT语句来获取表中的所有数据。

接下来使用游标对象的fetchall()方法获取查询结果。

fetchall()方法返回一个包含所有行的列表每行又是一个元组。

最后使用cursor.close()关闭游标对象使用conn.close()关闭数据库连接。

请注意在实际使用中可能需要根据具体情况进行错误处理、事务处理和参数化查询等操作以确保代码的正确性和安全性。

案例创建user表插入用户记录

pymysql.connect(hostlocalhost,userroot,passwordyuan0316,databasedb_day02)cursor

conn.cursor()#

语句cursor.execute(create_table_query)cursor.close()conn.close()def

password):conn

pymysql.connect(hostlocalhost,userroot,passwordyuan0316,databasedb_day02)cursor

conn.cursor()#

语句cursor.execute(insert_query)conn.commit()cursor.close()conn.close()#

调用创建表函数

SQL注入是一种常见的安全漏洞它发生在应用程序未正确验证和处理用户输入数据时。

攻击者可以通过在输入中插入恶意的SQL代码利用这个漏洞来执行未经授权的数据库操作。

import

pymysql.connect(hostlocalhost,userroot,passwordyuan0316,databasedb_day02)cursor

conn.cursor()#

cursor.fetchall()cursor.close()conn.close()if

len(result)

在上述示例中我们定义了一个login函数该函数接收用户名和密码作为参数。

在函数内部我们使用pymysql库连接到MySQL数据库并构造了一个包含用户名和密码的SQL查询语句。

然而代码中存在SQL注入漏洞。

如果用户输入恶意的数据例如在用户名输入框中输入

11;--

111这将导致查询条件始终为真绕过了实际的身份验证过程从而允许攻击者以任意用户登录。

参数化查询

要修复SQL注入漏洞可以使用参数化查询或输入验证来过滤和转义用户输入数据确保它们不会被当作SQL代码执行。

以下是修复SQL注入漏洞的示例代码

使用参数化查询构造SQL语句

params)在修复后的代码中我们使用了参数化查询的方式来构造SQL语句。

将用户输入作为参数传递给execute()方法而不是直接将它们插入到SQL查询语句中。

这样可以确保用户输入的数据被正确地转义和处理防止SQL注入攻击的发生。

若有错误与不足请指出关注DPT一起进步吧