MySQL入门-day06-数据库表设计、视图、索引

已于 2022-08-13 11:14:11 修改
阅读量1.3k 收藏 4
点赞数 1
分类专栏： Java第二阶段 文章标签： 数据库 mysql java
于 2022-08-13 11:13:11 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yb14672/article/details/126308158
版权

文章目录

学习目标

  • 数据库表设计
    • 三大范式
    • ER图与数据建模
    • 外键约束与级联操作
  • 索引
  • 视图
  • 触发器

数据库表设计

三大范式

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

第一范式

  • 第一范式（1NF）要求数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值。（单表如何设计）

比如说住址：xx省xx市xx区xx路
如果有相应的业务要求，这个字段就可以按第一范式进行拆分，这样在针对地址进行检索时更加精确方便

字段名值字段名值字段名值字段名值
省份xx省地市xx市区名xx区街道名xx路
  • 若某一列有多个值，可以将该列单独拆分成一个实体，新实体和原实体间是一对多的关系。

说人话：需要将一个事物拆分到无法拆分
如某人的成绩可以分为学生信息表、科目表、成绩表

第二范式

  • 满足第二范式需要先满足第一范式
  • 非主属性必须完全依赖主属性（多对多怎么设计）

如：订单表要有订单信息和货物信息，那只能订单编号和货物编号作为联合主键，但这样订单相关信息不依赖货物编号这个主属性，货物信息不依赖订单编号，不符合第二范式，所以要把这个订单信息表进行拆分，把商品信息分离到另一个表中，订单表的商品编号与商品表的商品编号产生多对1的关联，即可满足第二范式。

说人话：表内所有数据都要和主键有所关联

第三范式

  • 满足第三范式必须先满足第二范式。

  • 实体中的属性不能是其他实体中的非主属性。因为这样会出现冗余。即：属性不依赖于其他非主属性。（一对多怎么设计）

  • 如果一个实体中出现其他实体的非主属性，可以将这两个实体用外键关联，而不是将另一张表的非主属性直接写在当前表中。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

说人话：表内所有属性都要和主键有直接关系，两表之间的联系要用外键连接

ER图与数据建模

什么是ER图

ER（Entity，RelationShip）图又称实体关联图、实体联系图，是用来描述实体本身以及与其它实体关系一种图。

ER图核心要素

  • 实体：长方形，即数据模型中的数据对象，例如人、学生、音乐都可以作为一个数据对象，用长方体来表示
  • 属性：椭圆形，即数据对象所具有的属性，例如学生具有姓名、学号、年级等属性，属性分为唯一属性和非唯一属性，唯一属性指的是唯一可用来标识该实体实例或者成员的属性，用下划线表示，一般来讲实体都至少有一个唯一属性(主键)。
  • 关系：菱形，用来表现数据对象与数据对象之间的联系
    三者之间要用线连接

ER图实践

学生考勤系统案例
在这里插入图片描述

此图就没有标明哪个是唯一属性

数据建模

比起ER图，数据建模更加常用

下面是账号表和角色表的数据建模
一个账号可以有多个角色
在这里插入图片描述

外键约束与级联操作

外键

什么是外键？

外键是用来建立两个表的关联关系的字段。 外键是表的一个字段，不是本表的主键，但对应另一个表的主键。

什么是外键约束

用来约束表中的数据必须符合外键表的相关条件，以及在更新数据，自动对关联表做出相关处理。

外键对表的影响
  1. 添加时外键的值必须是在外键表存在的
  2. 修改时外键的值必须是在外键表存在的
  3. 删除时，不能先删除外键主表中外键所在行记录，只能先删除外键所在表的记录
外键约束的操作
创建表时添加外键约束
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
				REFERENCES <主表名> 主键列1 [，主键列2，…]
建表后添加外键约束
ALTER TABLE <数据表名> ADD CONSTRAINT <索引名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);
查看表中的外键
show indexes from 表名;
删除外键
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

级联操作

在涉及外键时，删除更新会受到限制，不允许删除，如要删除，要先去对应子表把对应数据删除后才能删改，若数据过多则很麻烦，因此可以在创建外键时使用级联操作来自动处理

语法：CONSTRAINT 外键名 FOREIGN KEY(列名) REFERENCES 主表名(主列名) ON 操作类型 级联操作类型

  • 操作类型是指删除(delete)、更新(update)

级联操作类型：

  1. 不做级联操作 RESTRICT、no action>

默认情况下就是不做级联操作，即子表有数据，则不准父表删改

  1. 级联更新、删除 CASCADE

父表在更新或者删除时，更新或者删除子表对应记录

  1. 设为NULL set null

则是表示父表在更新或者删除的时候，子表的对应字段被改为NULL

视图

什么是视图

视图是一张虚拟表，是一张逻辑表，本身没有数据。本质就是一段SQL，是一段保存在数据库，且可以重复利用的sql。

为什么要用

用视图的大部分情况是为了保障数据安全性，提高查询效率。

  • 简单：不用关注视图背后的表结构和逻辑，就把视图当成一表用。
  • 安全：仅能访问到视图返回的数据。

视图的操作

创建视图

语法：create VIEW 视图名 as 查询语句;

修改视图

语法：create or replace VIEW 视图名 as 查询语句;

删除视图

语法：drop view 视图名;

查看视图

语法：SELECT * FROM information_schema.views where table_schema='数据库名';

使用视图

语法：select * from 视图名;

索引

什么是索引

索引类似书籍中的目录，本质是一张表.

索引的分类

  • 主键索引(PRIMARY KEY)
  • 唯一索引(UNIQUE)
  • 普通索引(INDEX)
  • 全文索引(FULLTEXT)

索引的底层实现原理

索引的底层是一棵B+树

索引的操作

创建索引

  1. 直接创建索引
CREATE 索引类型 索引名 ON 表名(列名);
  1. 在已存在的表上追加普通索引
ALTER table 表名 ADD 索引类型 索引名(列名);
  1. 创建表的时候直接指定
create table 表名(
  ...,
  索引类型 索引名(列名)
);

查看索引

查看表中的所有索引

show index from tc_9;

删除索引

删除指定索引

drop 索引类型 索引名 on 表名;

索引注意事项

  1. 索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
  2. 一张表最好不要超过3个索引
  3. 一个字段重复率超过30%时，不建议添加索引

触发器

触发器的作用

监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行

触发器四大因素

  1. 监视地点(table)
  2. 监视事件(insert/update/delete)
  3. 触发时间(after/before)
  4. 触发事件(内部执行逻辑)

触发器的操作

创建触发器

触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中。
需要注意的是，old 和 new 不是所有触发器都有

  • INSERT型触发器 没有 old，只有 new，new 表示将要(插入前)或者已经增加(插入后)- 的数据
  • UPDATE型触发器 既有 old 也有 new，old 表示更新之前的数据，new 表示更新之后的数据
  • DELETE型触发器 没有 new，只有 old，old 表示将要(删除前)或者已经被删除(删除后)的数据

语法：

delimiter $ //设置MySQL执行结束标志，默认为;
		create trigger tg_name  -- 触发器名称
		  after|before  -- 触发时间
		  insert|update|delete  -- 监视事件 
		  on table_name -- 监控表名
		  for each row  -- 固定写法
		  begin  -- 开始触发器内部逻辑
			查询语句
		end$  -- 结束触发器内部逻辑
		delimiter ;  //重新将MySQL执行结束标志设置为;

PS：如果 before 触发器失败或者语句本身失败，将不执行 after 触发器(如果有的话)

删除触发器

语法：drop trigger 名称;

查看触发器

查看所有触发器：

show triggers;
select * from information_schema.TRIGGERS;

查看指定数据库、表的触发器

show triggers;
select * from information_schema.TRIGGERS where trigger_schema='数据库名' and event_object_table='表名';

查看触发器的创建语法

show create TRIGGER t1;

触发器的使用限制

触发器不是程序员手工调用的，是DBMS自动调用，由对应语句触发。
MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作，否则会报错

触发器的优缺点

  • 优点
  • 触发器可以通过数据库中的关联表实现级联更改，即一张表数据的改变会影响其他表的数据
  • 可以保证数据安全，并进行安全校验
  • 缺点
  • 过分依赖触发器，影响数据库的结构，增加数据库的维护成本
yb14672
关注
  • 1
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
数据库设计视图生成器 word 格式 包括 字段、类型、键
01-08
数据库设计视图生成器 word 格式 包括 字段 类型 键 这个工具可以方便的导出数据库设计视图，是我自己用来向编程人员提供开发文档的数据库导出工具 需要安装word 2003
数据库数据类型以及建库语句
01-29 247
tinyint(1字节), smallint(2字节), mediumint(3字节),int(4字节), bigint(8字节) 通用设定形式 定义一个字段的时候的类型的写法。 比如： create table tab1 (f1 数据类型 ); 数据类型： 类型名[（长度n）] [unsigned] [zerofill] 长度n： 表示的意思是该数字的“显示...
数据库表设计，及物化视图
qq_21562243的博客
06-08 526
视图，分区，表结构设计 数据库 垂直拆分： 一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面， 这样也就将数据或者说压力分担到不同的库上面。 水平拆分： 垂直拆分后遇到单机瓶颈，可以使用水平拆分。相对于垂直拆分的区别是：垂直拆分是把不同的表拆到不同的数据库中， 而水平拆分是把同一个表拆到不同的数据库中。（每个表包含一部分数据） 注意：索引字段加函数进行条件过滤时，索引会失效。 解决方案：索引字段的值，有一定逻辑规则，根据索引字段的值，进行过滤。不使用
在mysql中设计视图_Mysql中的视图
weixin_36054032的博客
02-05 866
1、什么是视图通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。视图是一种虚拟存在的表或逻辑表，视图并不在数据库中实际存在，行和列数据来自定义视图的查询总使用的表，并且是在使用视图时动态生成的。因为它与物理模式无关。数据库系统将数据库视图存储为具有连接的SQL SELECT语句。当表的数据发生变化时，视图也反映了这些数据的...
MySQL 第七篇：数据库设计、视图与触发器
w1lgy的博客
10-12 2392
试图一、二、三、
高级软件人才培训专家-day06-数据库-MySQL(1)
最新发布
03-10
高级软件人才培训专家_day06-数据库-MySQL(1)
2019-05-31-MySQL数据库设计规范1
08-08
MySQL数据库设计规范1. 规范背景与目的2. 设计规范 2.1 数据库设计 2.1.1 库名 2.1.2 表结构 2.1.3 列数据类型优化 2.1.4 索
MySQL：-数据库和表的创建与管理PPT课件.ppt
11-23
MySQL：-数据库和表的创建与管理PPT课件.ppt
网站设计与建设-MySQL数据库.ppt
11-15
网站设计与建设-MySQL数据库.ppt
MySQL数据库-实验九 数据库设计
02-19
class.csv
2012-02-24我写的视图(附数据库设计）
02-24
2012-02-24我写的视图，里面有数据库设计。这些视图的设计很具代表性
domino展示数据库设计文档视图的样例
07-03
domino展示数据库设计文档视图的样例
SQL Server--视图设计
老王的博客
09-17 2662
SQL Server--视图设计 数据库的建立，步骤如下 1. 新建数据库，建表，如下图，很简单基本上右键新建就行，然后添加字段 2. 接下来要建立视图查询，查询2个表中，指定两个用户之间的消息，先要新建视图，点击新建查询即可，输入代码，视图的命名空间与表相同，所以不能跟表取相同的名称 视图是什么？视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询，他没有表中任何列和数据，他只是一个SQL查询; 如果大家觉得有时候会需要...
数据库设计（设计视图）（下）
qq_19925065的博客
08-17 6315
由上一篇可以知道，我们记录写出了： （1）在使用中的学习卡的课程的部分信息和学习人数 （2）在订单中的课程的部分信息和学习人数 （2）不在使用中的学习卡的课程也不在订单中的课程的部分信息和学习人数接下来我们要讲的是视图。我们先将普通表和视图的区别于联系。普通表与视图： 普通表是实实在在的表，它占据空间；而视图是虚拟的，他几乎不占据空间，只占据少许视图定义的空间，当我们创建了视图后，就可以像普
MySQL-视图&数据库设计原则
qq_51978429的博客
09-02 969
MySQL-视图&数据库设计原则
索引+视图+数据库设计
small_py_trade的博客
10-03 470
对数据库进行优化时优先考虑的就是索引，其分类有：单一索引（一个字段上加索引）、复合索引（两个或更多添加索引）、主键索引（主键上加索引）、唯一性索引（unique修饰的字段上添加索引)等。mysql注意到id字段有索引对象，然后通过索引idIndex定位到101（索引对象是以B-Tree存储的，可以缩小查找范围）。（1）如带有like的模糊查询语句，如果查询内容是%开头的匹配语句，索引会失效。Notations:如果一张表两个字段有一对多的关系，为了满足第三范式，分成两张表，多出来的表添加外键。
MySQL数据库实验：任务五 数据库的视图设计
落落free的博客
04-29 8431
任务五 数据库的视图设计 文章目录任务五 数据库的视图设计【实训目的与要求】【实训原理】【实训步骤】一、创建视图（注意WITH CHECK OPTION子句用法）1．创建行列子集视图2．在多表上建立视图3．创建带表达式的视图4．创建分组视图二、查看视图1．使用DESC语句查看视图2．使用SHOW TABLE STATUS语句查看视图3．使用SHOW CREATE VIEW查看视图三、查询视图...
mysql第四章——存储程序，视图和索引，数据库设计，数据库优化，备份和还原
戴戴的博客
06-30 259
一：存储程序 什么是存储程序？ 存储程序指的一组存储和执行在数据库服务器端的程序。存储程序总是在服务器的进程或者线程的内存中执行的。 类似于java中的方法，但是它不是在客户端存储，而是在服务器中的进程或者线程的内存中进行的，在客户端去使用。效率快。资源占用率大。 存储程序分为：存储过程，存储函数 （一） 存储过程 类似于方法,有过程名称，参数列表，过程体组成。 存储过程...
举一个数据库设计中的视图设计的例子
weixin_35756690的博客
12-21 314
在数据库设计中，视图是一种虚拟的表，它不存储实际的数据，而是基于一个或多个表或视图，使用 SQL 查询从这些表或视图中选择数据并将其展示为一个单独的表的形式。 举个例子，假设我们有一个员工表和一个部门表，其中员工表包含员工的姓名、部门编号、工资等信息，部门表包含部门的名称、经理编号等信息。我们可以创建一个视图，该视图显示每个员工所在部门的名称、员工的姓名、工资和所在部门的经理。这样，我们就可以通过...
java的mysql多表操作_MySQL----MySQL数据库入门----第五章 多表操作
05-25
在Java中进行MySQL多表操作需要使用JDBC（Java Database Connectivity）技术，具体步骤如下： 1. 加载MySQL驱动程序 在Java中连接MySQL数据库需要加载MySQL驱动程序，可以使用Class.forName()方法加载。 ``` Class.forName("com.mysql.jdbc.Driver"); ``` 2. 建立数据库连接 使用DriverManager.getConnection()方法建立数据库连接，需要传入数据库URL、用户名和密码。 ``` String url = "jdbc:mysql://localhost:3306/test"; String user = "root"; String password = "123456"; Connection conn = DriverManager.getConnection(url, user, password); ``` 3. 创建Statement对象 使用Connection.createStatement()方法创建Statement对象，用于执行SQL语句。 ``` Statement stmt = conn.createStatement(); ``` 4. 执行SQL语句 使用Statement.executeUpdate()方法执行SQL语句，可以执行增、删、改操作。 ``` String sql = "insert into user(name, age) values('Tom', 20)"; stmt.executeUpdate(sql); ``` 使用Statement.executeQuery()方法执行SQL查询语句，返回ResultSet对象，可以遍历结果集获取查询结果。 ``` String sql = "select * from user"; ResultSet rs = stmt.executeQuery(sql); while(rs.next()) { String name = rs.getString("name"); int age = rs.getInt("age"); System.out.println(name + " " + age); } ``` 5. 关闭连接 使用Connection.close()方法关闭连接。 ``` conn.close(); ``` 以上是Java进行MySQL多表操作的基本步骤，具体操作需要根据业务需求来设计SQL语句。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值