目录
一、数据库设计三大范式
1.第一范式(确保每列保持原子性)：从字段的角度来做到原子性， 表字段在保存数据的时候，实际意义不可分割！保证字段的原子性： 如果可拆分：一个人可能有两个手机号：
-
可以来两个字段—每个字段保存一个手机--------这种是符合1NF
-
就来一个字段，这一个字段中保存了以逗号分割的两个手机号 ----没有遵循1NF 在一些特殊场景中，是必要去违背这1NF； UserInfo —[id,name,age,description, Detail] Detail—JOSN格式的用户信息
优势：查询很简单—只需要查询detail字段—就可以找到所有的数据信息
缺陷：修改数据–成本很高—修改任何一个字段的数据—都需要把detail的数据拿出来重新组装然后保存 进去
建议----如果表数据查询很多，修改很少----违背一下也无所谓； 扬长避短；
2..第二范式(确保表中的每列都和主键相关)：从一行数据的角度来做到单一性；最多保存一个其他信息的Id
一个表只负责描述一个事物：
从一行数据的角度来做到唯一性：一行数据只描述一种信息 Userinfo—Id Name age Decription—部
门
一条数据只描述一个对象：多对多的关系，不要冗余在一个表里面—多个表来保存
3.第三范式(确保每列都和主键列直接相关,而不是间接相关)：建议不用冗余字段字段中的数据保存，要求和主键直接相关，而不是简间接相关
描述主外键的关系
Company
userInfo—保存一个CompanyId(公司的主键Id)-----经常会保存一个CompanyName
userInfo—userid–username–userage–companyid–companyName(冗余字段)----违背了2NF；
建议大家不用使用冗余字段：
如果只保存CompanyId，如果我知道用户—查公司—必须要去关联一次----查询很费劲儿；
二、主键
- 主键分为单字段主键和多字段联合主键
- 每个表只能定义一个主键
- 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。这是唯一性原则
- 一个字段名只能在联合主键字段表中出现一次
- 联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则
- 设置主键为int、long类型开启自增，会自动设置聚集索引自动排序
- 设置GUID、UUID能确定全球主键唯一性，但不会开启聚集索引
-
超键(super key):在关系中能唯一标识元组的属性集称为关系模式的超键：1.保证实体的完整性 2.加快数据库的操作速度3.在表中添加新记录时，表会自动检查新纪录的主键值，不允许该值与其他记录主键值重复4.表自动按主键值的顺序显示表中的记录，如果没有定义主键，则按输入记录的顺序显示表中的记录
-
候选键(candidate key):不含有多余属性的超键称为候选键：1.保证实体的完整性 2.加快数据库的操作速度3.在表中添加新记录时，表会自动检查新纪录的主键值，不允许该值与其他记录主键值重复4.表自动按主键值的顺序显示表中的记录，如果没有定义主键，则按输入记录的顺序显示表中的记录
-
主键(primary key):用户选作元组标识的一个候选键程序主键
-
外键(foreign key)：如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键
三、外键
- 外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。
- 必须为主表定义主键。
- 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
- 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
- 外键中列的数目必须和主表的主键中列的数目相同
- 外键中列的数据类型必须和主表主键中对应列的数据类型相同。
四、事物
1.数据库事务的概念和特性（ACID）
-
原子性（Atomicity）：事务是一个完整的操作。事务的各元素是不可分的（原子的）。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。
-
致性（Consistency）：当事务完成时，数据必须处于一致状态。也就是说，在事务开始之前，数据库中存储的数据处于一致状态。在正在进行的事务中. 数据可能处于不一致的状态，如数据可能有部分被修改。然而，当事务成功完成时，数据必须再次回到已知的一致状态。通过事务对数据所做的修改不能损坏数据，或者说事务不能使数据存储处于不稳定的状态。
-
隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。
-
持久性（Durability）：事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。
2.执行事务的语法和流程
- 开始事务
##### A.自动提交事务
一条Sql语句
##### B.显式事务
use CustomerDB
--select * from Company
--delete Company where id>3
---开启事务
begin tran
--错误扑捉机制，看好啦，这里也有的。并且可以嵌套。
begin try
--语句正确
insert into [Company] (Name,CreateTime,CreatorId) values ('朝夕004','2019-03-20',3)
--加入保存点
--save tran SavePoint
--CreatorId为int类型，出错
--insert into [Company] (Name,CreateTime,CreatorId) values ('朝夕002','2019-03-20','测试')
--语句正确
insert into [Company] (Name,CreateTime,CreatorId) values ('朝夕005','2019-03-20','test')
save tran SavePoint
end try
begin catch
select Error_number() as ErrorNumber, --错误代码
Error_severity() as ErrorSeverity, --错误严重级别，级别小于10 try catch 捕获不到
Error_state() as ErrorState , --错误状态码
Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
Error_line() as ErrorLine, --发生错误的行号
Error_message() as ErrorMessage --错误的具体信息
if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
rollback tran ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
end catch
if(@@trancount>0)
rollback tran SavePoint --如果成功Company表中，将会有3条数据。
--表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型
select * from [Company]
##### C.隐式事务
use CustomerDB
GO
SET IMPLICIT_TRANSACTIONS ON
insert into [Company] (Name,CreateTime,CreatorId) values ('朝夕001---隐式事务','2019-03-20',3)
GO
UPDATE [Company] SET Name='朝夕00ccc',CreatorId='bbbbbb' WHERE id=1
GO
UPDATE [Company] SET [Name]='朝夕00xx' WHERE id=2
COMMIT
SET IMPLICIT_TRANSACTIONS OFF
--delete Company where Id>3
select * from Company
- 提交事务
MySQL 使用下面的语句来提交事务：
COMMIT;
COMMIT 表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。
- 回滚（撤销）事务
MySQL 使用以下语句回滚事务：
ROLLBACK;
ROLLBACK 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态。这里的操作指对数据库的更新操作。
五、锁
1.如果没有锁？
- 修改丢失
- 不可重复读
- 脏读/幻读
2.锁的意义：
- 避免以上情况
- 解决并发问题
3.乐观锁：不是使用数据锁机制，通过程序设计角度来谈的
- 认为没有并发，读取数据—修改—保存（没有锁），通过数据判断
- 更新的时候做一个判断-----需要一个判断维度（时间戳–Timestamp—long数字），就判断这个时间戳
先查询–再更新，查询出来以后，得到时间戳，在更新的时候，加个1（规则统一），判断加1 之后的数据是否大于之前的时间戳，大于则正常更新，否则这个数据被修改过，那就不能操作-----失败了； - Version/跟时间戳套路一样 -----时间戳建议大家使用long----bigint
- 规则需要统一，所以其实还有漏洞；高性能；
4.悲观锁：基于数据库锁机制完成
- 认为任何时候都可能多线程并发，认为我们在操作某一条数据的时候，刚好别人在操作；
- 共享锁 S锁 读锁，允许别的事务来读，但是不允许修改；读完就释放，锁定数据页；(除非holdlock就一直锁定)
- 排他锁 X锁 写锁，准备写数据，不允许读也不允许写；—Insert
- 更新锁 U锁 先查询再更新----update
- 行锁 where id=123
- 表锁 select name; where 1
5.锁的分类
锁的种类非常多，专业名词数不胜数，我们无需将其所有都记住，在本小节我们只了解其常用且提及最广的部分。
从锁的粒度上，我们可以将其大致的分为如下几类：
从数据库系统管理角度来看，可以把锁分为如下两大类：
我们分别从粒度和管理两个角度上对锁进行了分类。在粒度上，不同数据库，甚至不同引擎对锁的粒度支持都是不同的，如 MySQL 的 InnoDB 引擎支持行锁、表锁和库锁，而 MyISAM 引擎只能支持到表锁。对于页锁，只有 SQL Server 支持，而不同数据库也有类似间隙锁的实现，它的功能与页锁差不多。在管理上，锁根据数据是否共享来分类，对于读多写少的场景，共享锁几乎是并发的标配，而一旦涉及数据修改，锁就必须独占了。
6.如何避免死锁
- 不用锁就不会死锁，建议大家使用乐观锁—高性能
- 统一操作顺序–先A后B再C。。。。在系统中所有的操作都需要统一顺序
- 最小单元锁，锁里面操作尽量减少操作
- 避免事务中等待用户输入，避免在事务中等待时间过久
- 减少数据库并发----微服务
- 分库分表表分区
- 降低事务级别(遗留项)
- 设置死锁时间 set lock_timeout（锁超时时间）
六、存储过程
存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。可以独立的来完成业务逻辑计算-----就类似于我们在程序中的某一个方法；----单纯从数据库角度来说； 就有点像在数据库中定义的用来处理业务逻辑的Api;
1. 存储过程的优势
1.存储过程允许标准组件式编程，每一个业务逻辑的处理都可以独立定义成一个存储过程；面对一些业务逻辑的更新，其实只需要修改数据库存储过程即可；
2.存储过程能够实现较快的执行速度
3.存储过程减轻网络流量
4.存储过程可被作为一种安全机制来充分利用
2. 系统存储过程
SQL分页
exec sp_databases; --查看数据库
exec sp_tables; --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;exec sp_configure--例：--表重命名
exec sp_rename 'stu', 'stud';
select * from stud;
--列重命名
exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';
--重命名索引
exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';
--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
参考文章地址：https://www.imooc.com/wiki/sqlbase/sqlview.html
《高性能MySQL》
《SQLServer入门到实战》
本人学习分享，如有错误，欢迎指正，请勿谩骂(*￣︶￣) ↩︎
1250
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
