SQL 数据库设计、事务、视图 <13>

一、数据库设计

1.多表之间的关系

1) 一对一(了解)

如:人和身份证

分析:一个人只有一个身份证,一个身份证只能对应一个人

2)一对多(多对一)

如:部门和员工

分析:一个部门有多个员工,一个员工只能对应一个部门

3)多对多

如:学生和课程

分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择

2、实现关系

1)一对多(多对一)

如:部门和员工

实现方式:在多的一方建立外键,指向一另一方的主键。

2)多对多

如:学生和课程

实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,

这两个字段作为第三张表的外键,分别指向两张表的主键。

3)一对一(了解)

如:人和身份证

实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。

3、数据库设计的范式

        设计数据库时,需要遵循的一些规范。要遵循后边的范式要求,必须先遵循前边的所有范式要求。

        基本表及其字段之间的关系, 应尽量满足第三范式。但是,满足第三范式的数据库设计,往往不是最好的设计。

        为了提高数据库的运行效率,常常需要降低范式标准:适当增加冗余,达到以空间换时间的目的。

1)第一范式(确保每列保持原子性)

        第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库表满足了第一范式。

        第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性,本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分,那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储,这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式,如下表所示。

上表所示的用户信息遵循了第一范式的要求,这样在对用户使用城市进行分类的时候就非常方便,也提高了数据库的性能。

2)第二范式(确保表中的每列都和主键相关)

        第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。

        比如要设计一个订单信息表,因为订单中可能会有多种商品,所以要将订单编号和商品编号作为数据库表的联合主键,如下表所示。         

        这样就产生一个问题:这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关,而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

        而如果把这个订单信息表进行拆分,把商品信息分离到另一个表中,把订单项目表也分离到另一个表中,就非常完美了。如下所示。

        这样设计,在很大程度上减小了数据库的冗余。如果要获取订单的商品信息,使用商品编号到商品信息表中查询即可。

3)第三范式(确保每列都和主键列直接相关,而不是间接相关)

        第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。

        比如在设计一个订单数据表的时候,可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息(比如姓名、所属公司等)的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

二、事务

1、事务的基本介绍

如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。

1)操作:

        1.开启事务:start transaction;

        2.回滚:rollback;

        3.提交:commit;

2)实例:
--建表
CREATE TABLE account(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),balance DOUBLE
);--插入数据
INSERT INTO account(NAME,balance) VALUES ('zhangsan',1000),('lisi',1000);SELECT * FROM account;-- 张三给李四转账500元
-- 0.开启事务
START TRANSACTION;
-- 1.张三账户 -500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 2.李四账户 + 500
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';-- 出错了/没出错...-- 发现没有问题了,提交事务
COMMIT;
-- 发现出问题了,回滚事务
ROLLBACK;

3)MySQL数据库中事务默认自动提交,事务提交的两种方式:
• 自动提交:

        mysql就是自动提交的

        例如:一条DML(增删改)语句会自动提交一次事务。

• 手动提交:

        需要先开启事务,再提交

        Oracle数据库默认是手动提交事务

4)修改事务的默认提交方式
--查看事务的默认提交方式:
SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交--修改默认提交方式:
SET @@autocommit = 0; 

2.事务的四大特征ACID

1)原子性(Atomicity)

        原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚。

2)一致性(Consistency)

        一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须处于一致性状态。

        拿转账来说,假设用户A和用户B两者的钱加起来一共是5000,那么不管A和B之间如何转账,转几次账,事务结束后两个用户的钱相加起来应该还得是5000,这就是事务的一致性。

3)隔离性(Isolation)

        隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。

        即要达到这么一种效果:对于任意两个并发的事务T1和T2,在事务T1看来,T2要么在T1开始之前就已经结束,要么在T1结束之后才开始,这样每个事务都感觉不到有其他事务在并发地执行。

4)持久性(Durability)

        持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

        例如我们在使用JDBC操作数据库时,在提交事务方法后,提示用户事务操作完成,当我们程序执行完成直到看到提示后,就可以认定事务以及正确提交,即使这时候数据库出现了问题,也必须要将我们的事务完全执行完成,否则就会造成我们看到提示事务处理完毕,但是数据库因为故障而没有执行事务的重大错误。

3、隔离级别

  • Serializable (串行化):可避免脏读、不可重复读、幻读的发生。

  • Repeatable read (可重复读)(默认):可避免脏读、不可重复读的发生。

  • Read committed (读已提交):可避免脏读的发生。

  • Read uncommitted (读未提交):最低级别,任何情况都无法保证。

4、存在问题

1)脏读

        脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

        当一个事务正在多次修改某个数据,而在这个事务中这多次的修改都还未提交,这时一个并发的事务来访问该数据,就会造成两个事务得到的数据不一致。例如:用户A向用户B转账100元,对应SQL命令如下

update account set money=money + 100 where name=’B’;  (此时A通知B)
update account set money=money - 100 where name=’A’;

        当只执行第一条SQL时,A通知B查看账户,B发现确实钱已到账(此时即发生了脏读),而之后无论第二条SQL是否执行,只要该事务不提交,则所有操作都将回滚,那么当B以后再次查看账户时就会发现钱其实并没有转。

2)不可重复读

        不可重复读是指在对于数据库中的某个数据,一个事务范围内多次查询却返回了不同的数据值,这是由于在查询间隔,被另一个事务修改并提交了。

        例如事务T1在读取某一数据,而事务T2立马修改了这个数据并且提交事务给数据库,事务T1再次读取该数据就得到了不同的结果,发生了不可重复读。

  不可重复读和脏读的区别是,脏读是某一事务读取了另一个事务未提交的脏数据,而不可重复读则是读取了前一事务提交的数据。

  在某些情况下,不可重复读并不是问题,比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题,例如对于同一个数据A和B依次查询就可能不同,A和B就可能打起来了……

3)虚读(幻读)

        幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作,这时事务T2又对这个表中插入了一行数据项,而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据,会发现还有一行没有修改,其实这行是从事务T2中添加的,就好像产生幻觉一样,这就是发生了幻读。

三、视图

1、完整创建语法:

CREATE[OR REPLACE][ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}][DEFINER = user][SQL SECURITY { DEFINER | INVOKER }]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]

语法讲解:

1)OR REPLACE:表示替换已有视图,如果该视图不存在,则CREATE OR REPLACE VIEW与CREATE VIEW相同。

2)ALGORITHM:表示视图选择算法,默认算法是UNDEFINED(未定义的):MySQL自动选择要使用的算法 ;merge合并;temptable临时表,一般该参数不显式指定。

3)DEFINER:指出谁是视图的创建者或定义者,如果不指定该选项,则创建视图的用户就是定义者。

4)SQL SECURITY:SQL安全性,默认为DEFINER。

5)select_statement:表示select语句,可以从基表或其他视图中进行选择。

6)WITH CHECK OPTION:表示视图在更新时保证约束,默认是CASCADED。

2、一般用法:

CREATE VIEW 视图名 AS SELECT 查询语句;

3、修改视图

ALTER VIEW 视图名 AS SELECT 查询语句;

4、删除视图

DROP VIEW 视图名;

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/404322.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Flink消费Kafka数据积压排查解决

0、背景 有个Flink任务每天不定时会出现数据积压,无论是白天还是数据量很少的夜里,且积压的数据量会越来越多,得不到缓解,只能每日在积压告警后重启,重启之后消费能力一点毛病没有,积压迅速缓解&#xff0…

立仪光谱共焦传感器行业应用|薄膜高度差扫描

01|检测需求:扫描薄膜圆圈的高度差 02|检测方式 客户要求扫描薄膜圆圈的高度差,根据观察样品我们选择立仪科技D40A30镜头搭配H系列控制器进行测量 03|光谱共焦测量结果 薄膜圆圈的高度差轮廓 04|光谱共焦…

在CodeBlocks搭建SDL2工程OLED液晶模拟器虚拟OLED单色液晶(128x64)

在CodeBlocks搭建SDL2工程OLED液晶模拟器虚拟OLED单色液晶 例程说明源码下载目录工程配置一、SDL2的初始化和退出二、OLED画点和读点三、更新OLDE的GRAM四、清屏和清某区域五、点阵字库六、显示字符串七、显示中文字符八、显示图片九、测试代码十、主函数十一、运行结果 例程说…

05 serv00安装typecho

下载 ‍ cd domain/xxx.serv00.net/# 下载typecho git clone https://github.com/typecho/typecho.git# 当前有两个目录 typecho/ 和 public_html/ ls# 替换html rm -rf public_html/ mv typecho public_html‍ 安装 浏览器访问你的网站 xxx.serv0.net,看见 type…

8月15日笔记

masscan安装使用 首先需要有c编译器环境。查看是否有c编译器环境: gcc -v如果系统中已经安装了 GCC,这个命令将输出 GCC 的版本信息。如果未安装,你会看到类似于 “command not found” 的错误消息。 如果没有下载,使用如下命令…

万维网与HTTP协议:基础知识简明指南

引言 在当今的数字时代,了解万维网(World Wide Web, WWW)和HTTP协议(Hyper Text Transfer Protocol)是至关重要的。本文将为基础小白们简明扼要地介绍万维网及其核心协议HTTP,并通过简单的例子和清晰的段落…

C语言内存操作函数

目录 一. C语言内存操作函数 1. memcpy的使用和模拟实现 2. memmove函数 3. memset函数 4. memcmp函数 一. C语言内存操作函数 随着知识的不断积累,我们所想要实现的目标程序就会更加复杂,今天我们来学习一个新的知识叫做C语言内存操作函数&#x…

基于Python的火车票售票系统/基于django的火车购票系统

摘 要 随着信息技术和网络技术的飞速发展,人类已进入全新信息化时代,传统管理技术已无法高效,便捷地管理信息。为了迎合时代需求,优化管理效率,各种各样的管理系统应运而生,各行各业相继进入信息管理时代&…

面试经典算法150题系列-最长公共前缀

最长公共前缀 编写一个函数来查找字符串数组中的最长公共前缀。 如果不存在公共前缀,返回空字符串 ""。 示例 1: 输入:strs ["flower","flow","flight"] 输出:"fl"示例 2&…

HTML及CSS面试题4

1、BFC 1.1、介绍BFC及其应用 补充——触发BFC的方式,常见的有: 设置浮动overflow设置为:auto、scroll、hiddenpositon设置为:absolute、fixed 介绍: ○ 所谓BFC,指的是:一个独立的布局环境&am…

集合的知识点

一、集合的简介 1.1 什么是集合 集合(Collection),也是一个数据容器,类似于数组,但是和数组是不一样的。集合是一个可变的容器,可以随时向集合集合中添加元素,也可以随时从集合中删除元素。另外,集合还提…

鸿蒙(API 12 Beta3版)【录像流二次处理(C/C++)】媒体相机开发指导

通过ImageReceiver创建录像输出,获取录像流实时数据,以供后续进行图像二次处理,比如应用可以对其添加滤镜算法等。 开发步骤 导入NDK接口,接口中提供了相机相关的属性和方法,导入方法如下。 // 导入NDK接口头文件#in…

使用python实现3D聚类图

实验记录,在做XX得分预测的实验中,做了一个基于Python的3D聚类图,水平有限,仅供参考。 一、以实现三个类别聚类为例 代码: import pandas as pd import numpy as np from sklearn.decomposition import PCA from sk…

开源版最新LoveCardsV2表白墙源码下载

源码亮点 模板系统,给你无限可能 卡片不限字数,支持多图片上传 支持评论,点赞,让互动性拉满 管理后台可添加多个管理员 卡片一键分享至多平台 卡片浏览次数统计 发行版开箱即用 部署教程 1. 环境(参考开发环境&…

XSS- DOMclobbering与svg深度利用

目录 源码展示 解法一&#xff1a;绕过过滤-DOM clobbering 什么是DOM clobbering DOM clobbering原理 全局变量自动创建 属性名冲突 影响脚本执行 逐过程分析 源码展示 <script>const data decodeURIComponent(location.hash.substr(1));;const root documen…

图像处理之:Video Processing Subsystem(三)

免责声明&#xff1a; 本文所提供的信息和内容仅供参考。作者对本文内容的准确性、完整性、及时性或适用性不作任何明示或暗示的保证。在任何情况下&#xff0c;作者不对因使用本文内容而导致的任何直接或间接损失承担责任&#xff0c;包括但不限于数据丢失、业务中断或其他经济…

【硬件模块】震动传感器模块

震动传感器模块实物图 DO&#xff1a;数字信号量输出&#xff0c;接单片机管脚&#xff1b; AO&#xff1a;模拟输出&#xff0c;无效&#xff0c;一般不接。 无震动&#xff0c;DO输出高电平&#xff0c;信号指示灯灭&#xff1b; 有震动&#xff0c;DO输出低电平&#xff0c;…

DHCP的原理与配置

目录 DHCP的原理 DHCP是什么 DHCP的好处 DHCP的分配方式 DHCP的工作原理 DHCP的配置 环境设置 DHCP配置 验证配置是否成功 DHCP的原理 DHCP是什么 DHCP:Dynamic Host Configuration Protocol&#xff0c;动态主机配置协议。由Internet工作小组开发&#xff0c;专门用…

牛客网习题——通过C++实现

一、目标 实现下面4道练习题增强C代码能力。 1.求123...n_牛客题霸_牛客网 (nowcoder.com) 2.计算日期到天数转换_牛客题霸_牛客网 (nowcoder.com) 3.日期差值_牛客题霸_牛客网 (nowcoder.com) 4.打印日期_牛客题霸_牛客网 (nowcoder.com) 二、对目标的实现 1.求123...n_…

【unity小技巧】下载原神模型,在Blender中PMX模型转FBX模型,导入到Unity中实现基于光照模型的内置和URP卡通渲染

最终效果 前言 最近在研究人物模型的使用和卡通渲染效果&#xff0c;这里我们就使用原神的模型来演示。 一、原神模型下载 原神的模型可以在官网直接下载到。 1、第一期模型 官网&#xff1a;https://ys.biligame.com/gczj/ 2、第二期模型 官网&#xff1a;http://ys.bi…