数据库基础

数据库设计

E-R模型

E-R模型的基本元素：实体，联系，属性

E:表示实体，一个数据对象，描述具有相同特征的事物

R:表示联系，一个或者多个实体之间的联系，关系的类型包括一对一，一对多，多对多

属性：实体的某一特性成为属性

外键(foreign key)

如果一个实体的某个字段指向另一个实体的主键，就叫做外键

被指向的实体，称为主实体（主表），也叫父实体（父表）

负责指向的实体，称为从实体（从表），也叫子实体（子表）

对关系字段进行约束：当为从表中的关系字段填写值时，会到关联的主表中查询是否存在，如果存在则填写成功，如果不存在则填写失败并报错。

外键扩展：

1.当主表存在的时候，如何添加外键？

-- alter table 从表名 add foreign key (从表字段) reference 主表名(主表主键);
-- 例子：
alter table stu add foreign key (class_id) reference class(id);

2.查看外键，删除外键

-- 查看外键
show create table stu; 
-- 删除外键
alter table stu drop foreign key (外键名称);

由于设计外键会极大降低对数据修改效率，因此在实际工作中遇到使用外键的几率比较低

索引

定义：索引（index）是帮助MySQL高效获取数据的[数据结构

作用：对于大量数据进行查询效率优化时，可以采取添加索引的策略

查看开索引和不开索引

-- 开启时间监测：set profiling = 1

-- 查询示例数据如num = 10000的值
select * 
from test_index 
where num = 10000;

-- 查看运行时间
show profiles;

-- 添加索引
-- create index 索引名称 on 表名(目标字段)
create index index on test_index(num);

-- 再次执行查询操作进行，查看运行时间进行比较

查看索引

show index from 表名

索引缺点

虽然索引提高了查询效率，但同时会降低更新表的速度，如对表进行insert ,update ,delete,在更新表的时候

mysql不仅要保存数据，还要保存索引文件

使用：查询语句大于增删改语句，故在大数据导入时，可以先删除索引，再批量插入数据，最后再添加索引

删除

delete ，truncate,drop区别

共同点：都表示删除

不同点：delete可回滚，删除的时候表还在，删除表的全部或者部分数据行，删除速度慢，需要逐条删除

​ truncate不可回滚，表结构还在，删除表的所有数据，删除速度快

​ drop不可回滚，删除的时候从数据库中删除表，删除数据，删除索引，权限，删除速度最快

应用：如果不需要一张表，就选用drop,如果想删除部分数据行，用delete，如果想保留表删除数据就用truncate

数据表操作：

-- 查看表结构：
desc 表名;

-- 查看创表语句：
show create table 表名;

存储过程的介绍以及使用过程

存储过程：可以叫存储程序，通过一定的代码逻辑，将一条sql语句进行封装，通过调用存储过程，快速实现其内部的封装sql语句操作

用途：可以用来向数据库中快速插入大量测试数据

事物

事物是一个方法中所有的持久化操作要么全部成功，要么全部失败

如：一条update语句，由于执行时间太长，中途执行失败，更新过的语句会回滚到update之前的状态；也就是说，一条sql就是一个事务。

事务具有**原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）**四个特性，简称 ACID。

-- 注意:事物操作一般由修改事物操作产生(插入数据insert,更新数据update,删除数据delete)
-- 支持事物的存储引擎为Innodb
-- 开启事务
bejin;
-- 提交事物
commit;
-- 回滚事物
rollback;

视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。分布式查询也可用于定义使用多个异类源数据的视图。

场景：能够封装sql语句，以类似于表的形式存在

-- 创建视图语法
-- create view 视图名 as select 语句;

-- 视图的用法:当成查询表使用即可
-- 视图的作用:可以起到隐藏真实数据表内容的作用

-- 删除视图语句
drop view 视图名称

数据库日志文件

查看：

show variables like 'general%';

开启日志：

set global general_log = 1;

-- 关闭日志
set global general_log = 0;
-- 注意：日志功能使用完成，需要马上关闭，避免影响数据库性能