数据库设计

最新推荐文章于 2024-09-29 16:19:38 发布

紫夜✘

最新推荐文章于 2024-09-29 16:19:38 发布

阅读量281

点赞数

分类专栏： MYSQL 文章标签：数据库 Powered by 金山文档

本文链接：https://blog.csdn.net/Purple_night_/article/details/128835034

版权

MYSQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

三范式

第一范式：要求有主键，并且要求每一个字段原子性不可再分。

第二范式：要求所有的非主键字段完全依赖于主键，不能产生部分依赖。

第三范式：所有非主键字段和主键字段之间不能产生依赖传递。

常见的表关系

一对一。一个表和另一张表的关系是一对一

一对多。两张表，外键在多的一方。一个学生对应一个专业，一个专业可以有好多学生。

多对多。

MYSQL的系统架构

数据库和数据库实例

数据库：按照数据结构来组织，存储和管理数据的仓库，通常由数据库管理系统。

数据库管理软件（RDBMS）：我们所有的数据库管理系统软件，他强调的是软件

数据库实例：启动数据库软件，在内存中运行一个独立的进程，用来操作数据。这个正在运行的进程就是一个数据库实例。理论上可以在一台电脑上启动多个数据库实例，要监听在不同的端口。

MYSQL架构

MYSQL会向外提供交互接口

connectors组件，MYSQL向外提供交互的组件。

管理服务组件和工具组件：备份，恢复，安全管理

连接池组件：接收客户端发送的命令。负责线程调度。

SQL接口组件：DDL,DML,DQL。

查询分析器组件，检查SQL的合法性

优化器组件：对SQL命令进行优化分析，会按照mysql认为的最优的方式来进行优化

缓存组件

存储引擎：8个，最常用的InnoDB（支持事务）,MylSAM（不支持事务。

目录结构

MYSQL的安装目录：c:\Program Files\MySQL\

数据文件目录：C:\ProgramData\MySQL\MySQL Server 5.7

data目录：用来放置一些日志文件和数据库。每一个数据库都是一个单独的文件夹，每张表又是一个单独的文件。

my.ini:mysql的配置文件。

内置数据库：

mysql:mysql的核心数据库，存储数据库的用户，权限设置，关键字等mysql自己需要使用的信息。

information_schema:维护了数据库其他表的一些描述性信息，元数据。比如：当前有哪些表，哪些视图，哪些列等。

performance_schema:用来存储mysql服务器运行过程中的一些状态信息，做性能监控。比如：最近执行了什么sql,内存使用情况等。

sys:结合information_schema和performance_schema的数据，来了解mysql服务器的性能信息。

索引

加快查询速度

数据结构

B-树

B+树

索引的分类和创建

聚簇索引和非聚簇索引

普通索引（常规索引）

创建普通索引：

create index idx_s_id on scores(s_id);
--给邮箱的前5个字符添加索引
create index idx_email on employee(email(5));

删除索引：

drop index idx_s_id on scores;

创建索引：

--通过修改的方式添加索引
alter table scores add index idx_c_id(c_id);
--创建表的同时添加索引
create table tb1_tame(
    tid int,
        tname varchar(20),
        gender varchar(1),
        index idx_tid(tid)
        )

创建唯一索引

    create unique index idx_name on course(`name`);

通过alter语句添加索引

        alter  table book  add unique index idx_name(name(2));

面试题

- 唯一索引和主键的区别？

唯一索引允许空值，主键不允许空值。

主键在创建时已经默认为非空+唯一。

主键可以被其他表引用为物理外键，唯一索引不能。

一个表最多只能有一个主键，但是可以创建多个唯一索引。

主键更适合那些不容易更改的唯一标识，如自动递增，身份证号。

- 唯一约束和唯一索引的区别?

都可以实现数据的唯一，列的值可以为null.

创建唯一约束的时候，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束时会自动删除索引。唯一约束是通过唯一索引来实现数据的唯一。

创建唯一索引，这个索引就是独立的索引，是可以独立删除的。

如果一个列上有约束和索引，且两者可以单独删除，可以先创建唯一索引，再创建唯一约束。

- 使用索引的问题

哪些情况下适合创建索引

频繁作为where条件语句查询的字段。

关联字段需要建立索引。

分组，排序字段可以建立索引。

统计字段可以建立索引，例如count(列）,max(列)等

哪些情况下不适合创建索引

频繁更新的字段。

where条件中用不到的字段。

表数据可以确定比较少。

数据重复且分布比较均匀的字段。

唯一性太差。

参与列计算的字段，索引会失效。

- 索引失效的几种情况

如果条件中有or

符合索引不满足最左原则

like查询以%开头

存在列运算

如果mysql觉得不用索引会比用索引块，不使用索引

存在类型转换

- 索引的弊端

在进行数据库操作的时候，会同时维护索引。

创建索引，占物理内存空间。随着数据量的增加，索引占据的内存会越来越大

视图

含义：虚拟表，和普通表一样使用。Mysql5.1出现的新特性。

为什么要使用视图？

如果有一些复杂的sql语句，并且需要大量使用，可以考虑把sql的执行结果以视图的形式存储起来，后续在使用相同的功能，可以直接使用视图查询，简化了sql语句。

创建视图

create view stu_sco_cou
as
select s.id,s.name,c.name,sc.score
from student s
join scores sc on s.id=sc.s_id
join course c on c.id=sc.c_id;

使用视图

select * from stu_sco_cou;
select * from stu_sco_cou order by score desc;
select s.gender,s.name,sv.score
from student s
join stu_sco_cou sv on s.id=sv.id;

修改视图

--方式一
create or replace view stu_sco_cou
as
select s.id,s.name,c.name,sc.score,c.t_id tid
from student s
join scores sc on s.id=sc.s_id
join course c on c.id=sc.c_id;
--方式二
alter view stu_sco_cou
as
select s.id,s.name,c.name,sc.score
from student s
join scores sc on s.id=sc.s_id
join course c on c.id=sc.c_id;

删除视图

drop view stu_sco_cou;

查看视图

desc stu_sco_cou
show create view stu_sco_cou;

注意：

增删改会影响原始表中的数据。

具备以下特点的试图是不允许更新。

包含group by,distinct,having,union。

常量视图

select包含子查询

join连接查询

from一个不能更新的视图

where子句的子查询引用了from子句中的表

注意：我们开发中如果涉及到使用视图，基本上就是用来查询。

mysql不支持只读视图，oracle支持。

- 面试题

视图和表的区别。

	创建语法关键字	是否实际占用物理空间	使用
视图	create view	只是保存了sql逻辑	增删改查，一般不能增删改
表	create table	保存数据	增删改查