掌握数据库知识，轻松学习大数据处理技巧

5、数据库的信息:TRUNCATE,DELETE,DROP放在一起比较:

TRUNCATE TABLE:删除内容、释放空间但不删除定义。

DELETE TABLE:删除内容不删除定义，不释放空间。

DROP TABLE:删除内容和定义，释放空间。

注意:TRUNCATE TABLE不能回滚,DELETE TABLE可以回滚,因为DELETE TABLE删除的数据写入到日志中。

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。

但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。 DELETE 语句每次删除一行，

并在事务日志中为所删除的每行记录一项。

TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

6、创建表的sql语句

create table test(表名)(

id(列名) int(类型),

name varchar(25),

sex varchar(4) default '男' //为空时默认为男

);

7、修改表结构(以下都是对oracle数据库的操作):

7.1、添加列:

alter table test(表名)

add

(

age(列名) int(类型),

sex(列名) int(类型)

)

xxx

MySQL添加字段到指定列后面

alter table sms_industry_statistics

add parent_id bigint(20) COMMENT '父级部门ID' AFTER org_id

7.2、修改列定义(列类型):

alter table test(表名)

modify id(列名) varchar(111)(类型)

7.3、删除列

alter table test

drop column sex(列名)

注意:如果改列存在外键约束，要先删除约束，才能删除列

7.4、修改列名

alter table test(表名)

rename column id(老列名) to no(新列名);

7.5、修改表名

alter table test(表名) rename to mytest(新表名)

7.6、删除表

drop table test(表名)

8、truncate 表名 :将表中的所有数据删除，truncate的速度比delete的速度快

9、数据库的约束:

NOT NULL:非空约束，指定某列不能为空

UNIQUE:唯一约束，指定某列或几列组合不能重复

PRIMARY KEY:主键，指定该列的值可以唯一的标识该条记录

FOREIGN KEY:外键，指定该行记录从属于主表中的一条记录，注意用于保证参照完整性

CHECK:检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式。

salary number(5,0) check(salary>0) // 列名 列类型 check(表达式)

10、多列约束和单列约束

多列约束是由多个列组成的约束,如:primary key(name,password);

单列约束是由一列组成的约束,如:primary key(id);

11、添加和删除主键约束

添加主键约束:alter table test(表名) add primary key(id(列名));

删除主键约束: alter table test(表名) drop primary key;

12、添加和删除外键约束

添加外键约束(注意括号):

alter table test1(表名)

add foreign key (tid-主表列名) references test2(表名)(id-从表列名)

删除外键约束:

alter table test1(表名)

drop foreign key tid;

13、在定义外键约束时添加on delete cascade 或 on delete set null

on delete cascade:当主表记录删除时，从表记录也全部删除(根据主表记录删除的id)

on delete set null：当主表记录删除时，从表记录的外键值全部设置为空(根据主表记录删除的id)

14、视图，视图本身是没有数据的，视图相当于一个查询结果集。

15、比较运算符:>,>=,<,<=,=,<>

特殊的比较运算符:between, in ,like ,is null

16、%:代表任意多个字符，

_:代码任意一个字符。

17、逻辑运算符:not ,and ,or

注意:比较运算符的优先级高于逻辑运算符

18、数据库函数

concat函数:连接函数，将多个列的值进行连接

distinct函数:去掉重复的行

avg函数:计算多行的平均值

count函数:计算总条数

max函数:计算多行的最大值

min函数:计算多行的最小值

sum函数:计算多行的总和

19、事务的几大特性:原子性(A)、一致性(C)、隔离性(I)、持续性(持久性)(D)

原子性:事务中所有数据的修改，要么全部执行，要么全部不执行

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

隔离性:一个事务的执行不能干扰其它事务。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

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

事务的几个动作:创建、提交、回滚、关闭数据库连接

20、数据库的三大范式:

第一范式(1NF):字段具有原子性,不可再分。

第二范式:要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主

关键字或主键。注意:第二范式必须先满足第一范式

第三范式:每一个表都不包含其他表已经包含的非主关键字信息。注意:第三范式必须先满足第二范式

BCNF范式:数据库表中不存在任何字段对任一候选关键字段的传递函数依赖。

1NF:无重复的列（数据库表中的每一列都是不可分割的基本数据项）

2NF：满足1NF且非主键列都完全函数依赖于主键。

3NF：满足2NF且非主属性列都不传递依赖于主键。

BCNF：满足3NF且不允许主键的一部分被另一部分或其它部分所决定（即满足3范式，并且主属性之间没有依赖关系）

21、函数依赖:若在一张表中，在属性(或属性组)X的值确定的情况下，必定能确定属性Y的值，那么就可以说Y函数依赖于X，写作X → Y 。

官方定义：设一个关系为R(U),X和Y为属性集U上的子集,若对于X上的每个值都有Y上的一个唯一值与之对应,则称X和Y具有函数依赖关系,并称X 函数决定Y,或称Y函数依赖于X,记作X→Y,称X为决定因素。

理解：在一个表中，将所有字段分为两部分X和Y，如果X中的一条数据可以唯一确定Y中的一条数据，则称X和Y具有函数依赖关系，并称为X函数决定Y，或Y函数依赖于X，记为：X一>Y，X 为决定因素

函数依赖的分类：

完全函数依赖

设有关系模式R(U)，U是属性集，X和Y是U的子集，如果X→Y是一个函数依赖，且对X的任何一个真子集X'都不存在X'→Y，则称X→Y是一个完全函数依赖(Full Functional Dependency)，即Y完全函数依赖于X

即在一张表中字段分为 X 和 Y 两个集合，X集合数据唯一确定一条数据 Y ，X集合字段中的任何一个字段都不能确定唯一一条数据 Y ，就称 Y 完全函数依赖于 X

如:(学号,课程号)——>(成绩)

部分函数依赖

设有关系模式R(U)，U是属性集，X和Y是U的子集，如果X→Y是一个函数依赖，且对X的任何一个真子集X'都存在X'→Y，则称X→Y是一个部分函数依赖(Full Functional Dependency)，即Y部分函数依赖于X

即在一张表中分为 X 和 Y 集合，X集合数据唯一确定一条数据Y，并且X中任意字段或组合字段都可以唯一确定Y集合数据，则称 Y 部分函数依赖于 X

如:(学号,课程号)——>(姓名) 存在 (学号)——>(姓名)

传递函数依赖

在关系模式R(U)中，设X，Y，Z是U的不同的属性子集，如果X确定Y、Y确定Z，且有X不包含Y，Y不确定X，（X∪Y）∩Z=空集合，则称Z传递函数依赖(transitive functional dependency) 于X。

即在一张表中，字段分为 X、Y、Z，如果X可以决定Y，Y决定Z，但是Y不能决定X，则称Z传递函数依赖于X

平凡函数依赖:若X->Y，且Y是X的子集（对任一关系模式，平凡函数依赖必然成立），就是平凡函数依赖。

如果X决定Y，而且Y属于X，也就是Y是X 的真子集，则称该依赖为平凡依赖；否则为非平凡依赖。

简单的讲，X可以推导出自己活着自己的一部分。

例如：

在学生表(学号,姓名,年级)中,(学号,姓名)可以推出学号和姓名其中的任何一个,这就是平凡函数依赖.

直白点说，就是只要Y是X的子集，Y就依赖于X

非平凡函数依赖:若X->Y，但Y不是X的子集，就是非平凡函数依赖。

在学生表(学号,姓名,年级)中,通过(学号,姓名)可以推出这个学生所在的年级,但年级不是（学号，姓名）的子集，这是非平凡函数依赖.((学号,姓名)就是一个x,学号或者姓名就是一个x’)。

22、redo即重做，undo即撤销还原

redo日志 记录某数据块被修改后的值，可以用来恢复未写入data file 的已成功事物更新的数据，redo即重做

undo日志 记录某数据被修改前的值，可以在事物失败时进行rollback；undo即撤销还原

23、选码、主码、全码、外码、主属性、主键、主关键字、非主属性清晰总结
:https://blog.csdn.net/sumaliqinghua/article/details/85872446#commentBox

1、码=超键：能够唯一标识一条记录的属性或属性集。

标识性：一个数据表的所有记录都具有不同的超键

非空性：不能为空

有些时候也把码称作“键”

2、候选键=候选码：能够唯一标识一条记录的最小属性集

标识性：一个数据表的所有记录都具有不同的候选键

最小性：任一候选键的任何真子集都不能唯一标识一个记录（比如在成绩表中（学号,课程号）是一个候选键，单独的学号，课程号都不能决定一条记录）

非空性：不能为空

候选键是没有多余属性的超键

举例：学生ID是候选码，那么含有候选码的都是码。

少部分地方也有叫超级码的，但是见得不多

3、主键=主码：某个能够唯一标识一条记录的最小属性集（是从候选码里人为挑选的一条）

唯一性：一个数据表只能有一个主键

标识性：一个数据表的所有记录都具有不同的主键取值

非空性：不能为空

人为的选取某个候选码为主码

4、主属性 包含在任一候选码中的属性称主属性。简单来说，主属性是候选码所有属性的并集

非主属性 不包含在候选码中的属性称为非主属性。 非主属性是相对于主属性来定义的。

5、外键（foreign key）：子数据表中出现的父数据表的主键，称为子数据表的外键。

6、全码：当所有的属性共同构成一个候选码时，这时该候选码为全码。（教师，课程，学生）假如一个教师可以讲授多门课程，某门课程可以有多个教师讲授，学生可以听不同教师讲授的不同课程，那么，要区分关系中的每一个元组，

这个关系模式R的候选码应为全部属性构成 （教师、课程、学生），即主码。

7、代理键：当不适合用任何一个候选键作为主键时（如数据太长等），添加一个没有实际意义的键作为主键，这个键就是代理键。（如常用的序号1、2、3）

8、自然键：自然生活中唯一能够标识一条记录的键（如身份证）

超键包含着候选键，候选键中包含着主键。主键一定是惟一的。

24、数据库范式
:https://blog.csdn.net/Haode258/article/details/114316559

https://blog.csdn.net/weixin_30379911/article/details/96180100

https://www.cnblogs.com/ybwang/archive/2010/06/04/1751279.html

1NF——>2NF:消除非主属性对码的部分函数依赖

2NF——>3NF:消除非主属性对码的传递函数依赖

3NF——>BCNF:消除主属性对码的部分和传递函数依赖

BCNF——>4NF:消除非平凡且非函数依赖的多值依赖

检测范式违例:

1.当多个值存储在一个单元中时会发生1NF违例。

2.2NF违例只会发生在具有关联键的表中，且非键字段只依赖于主键的一部分。

3.3NF违例发生在一个非键字段确定另一个非键字段的情况下，表可能有一个任何大小的键。

4.BCNF违例出现在非键字段确定主键的一部分的情况下。这些违例只能发生在主键是有关联键组成的表中。

5.4NF违例发生在表的主键至少由3个键连接而成且没有非键字段的情况下，此外，键的一部分确定键的另一部分的多个值。

25、数据流程
:https://blog.csdn.net/shulianghan/article/details/109271777

加工 ( 核心 )使用 圆形 / 圆角矩形 表示加工(P) 描述 “输入数据流” 到 “输出数据流” 之间的变换 , 即 对数据进行了什么样的处理 , 使得 “输入数据流” 变为 “输出数据流” ;

数据存储使用 双横线 / 半框形矩形 表示(D) 表示 暂时存储的数据 , 数据存储的粒度是以 表 为单位

外部实体 矩形 表示(E)

26、E-R图的绘图规范
:https://blog.csdn.net/Edraw_Max/article/details/110244759

https://blog.csdn.net/weixin_51966461/article/details/116505235

1 矩形框：表示实体，在框中记入实体名。数据库的表名就可以做一个实体对象。一个系统是由很多个实体对象构成的，然后它们之间存在一定的关系和属性。

2 菱形框：表示联系，在框中记入联系名。

3 椭圆形框：表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线。

4 连线：实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型。

（对于一对一联系，要在两个实体连线方向各写1； 对于一对多联系，要在一的一方写1，多的一方写N；对于多对多关系，则要在两个实体连线方向各写N，M。)

5 双实线长方形：表示弱实体。

什么是弱实体？如果一个实体依赖于某个实体而存在，那么前者是弱实体，后者为强实体。例如订单实体依赖于商品实体而存在，为什么？因为如果没有商品你能够下订单吗？不可能！所以订单为弱实体，商品为强实体。

6 双实线椭圆形：表示多值属性。

什么是多值属性？例如一个用户可能拥有多个电话号码，所以电话号码可以作为多值属性。一个用户只能拥有一个身份证号码，所以身份证号不能作为多值属性。

7 虚线椭圆形：表示派生属性。

什么是派生属性？例如一个用户的年龄我们可以作为派生属性，为什么？因为它可以通过身份证号推导出来。所以需要注意的是能推导出来的属性我们都可以作为派生属性。

8 双实线菱形：表示弱关系。什么是弱关系？弱关系一般是和弱实体一起使用的，只有弱实体才会用到弱关系。

9 单竖线倒三角形：表示概化。什么是概化？说直白点就是分组！！！一个商品类型可能包含图书类、服装类、数码类等。

10 双竖线倒三角形：表示全部概化。什么是全部概化？说直白点也是分组！！！但是！！！一个商品类型只能包含图书类、服装类、数码类。这就是概化与全部概化的区别。

27、无损连接定义:无损连接是指分解后的关系通过自然连接可以恢复成原来的关系，即通过自然连接得到的关系与原来的关系相比，既不多出信息、又不丢失信息。

28、一、Armstrong公理系统设关系模式R<U,F>，其中U为属性集，F是U上的一组函数依赖，那么有如下推理规则：

① A1自反律：若Y⊆X⊆U，则X→Y为F所蕴含；

② A2增广律：若X→Y为F所蕴含，且Z⊆U，则XZ→YZ为F所蕴含(XZ表示X∩Z，对应XZ属性值相等)；

③ A3传递律：若X→Y，Y→Z为F所蕴含，则X→Z为F所蕴含。

根据上面三条推理规则，又可推出下面三条推理规则：

④ 合并规则：若X→Y，X→Z，则X→YZ为F所蕴含；

⑤ 伪传递规则：若X→Y，WY→Z，则XW→Z为F所蕴含；

⑥ 分解规则：若X→Y，Z⊆Y，则X→Z为F所蕴含。

注意:Y⊆X表示Y是X的子集，如:

Y⊆X⊆U

U为(学生姓名，年龄，电话号码)

X为(学生姓名，年龄)

Y为(学生姓名)

29、数据库事务加入排它锁后，不能加入共享锁;而加入共享锁后也不能加入排它锁但是能加入共享锁。

30、sql优化的常用方法:

1)查询sql时尽量不要用*号代替具体的字段，因为用*号数据库还要将*号解析为具体字段

.字段多时，大表能达到100多个字段甚至达200多个字段

.只取需要的字段，节省资源、减少网络开销

.select * 进行查询时，很可能不会用到索引，就会造成全表扫描

2)应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

3)应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

4)避免在where中对字段进行表达式操作

.SQL解析时，如果字段相关的是表达式就进行全表扫描

.字段干净无表达式，索引生效

5)避免在where子句中使用!=或<>操作符

应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。

.使用!=和<>很可能会让索引失效

6)去重distinct过滤字段要少用

.带distinct的语句占用cpu时间高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，如cpu时间

7)where中使用默认值代替null

.并不是说使用了is null 或者 is not null 就会不走索引了，这个跟mysql版本以及查询成本都有关

.如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃索引的

.如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点

8)批量删除优化:避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。

.一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作

9)提高group by语句的效率:可以在执行到该语句前，把不需要的记录过滤掉，也就是用where字句替换HAVING字句

10)不要有超过5个以上的表连接

.关联的表个数越多，编译的时间和开销也就越大

.每次关联内存中都生成一个临时表

.应该把连接表拆开成较小的几个执行，可读性更高

.如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了

.阿里规范中，建议多表联查三张表以下

11)尽量使用union all替代union

.union和union all的区别是，union会自动去掉多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复

.union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序

.union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION

12)为常用搜索字段建索引

13)当只需要1条查询结果时，limit 1的使用

14)使用查询缓存

15)EXPLAIN 查询语句

16)使用JOIN来代替子查询

17)将小表作为基表来驱动大表，小表驱动大表

18)使用truncate代替delete

19)用>= 替代 >

20)尽量多使用COMMIT，因为COMMIT可以释放资源

21)避免频繁创建和删除临时表，以减少系统表资源的消耗。

31、如何设计数据库

1、数据库设计最起码要占用这个项目开发的40%以上的时间

2、数据库设计不仅仅停留在页面demo的表面

页面内容所需字段，在数据库设计中只是一部分，还有系统运转、模块交互、中转数据、表之间的联系等等所需要的字段，因此数据库设计绝对不是简单的基本数据存储，还有逻辑数据存储。

3、数据库设计完成后，项目80%的设计开发都要存在你的脑海中

每个字段的设计都要有他存在的意义，要清楚的知道程序中如何去运用这些字段，多张表的联系在程序中是如何体现的。

4、数据库设计时就要考虑效率和优化问题

数据量大的表示粗粒度的，会冗余一些必要字段，达到用最少的表，最弱的表关系去存储海量的数据。大数据的表要建立索引，方便查询。对于含有计算、数据交互、统计这类需求时，还要考虑是否有必要采用存储过程。

5、添加必要的冗余字段

像创建时间、修改时间、操作用户IP、备注这些字段，在每张表中最好都有，一些冗余的字段便于日后维护、分析、拓展而添加。

6、设计合理的表关联

若两张表之间的关系复杂，建议采用第三张映射表来关联维护两张表之间的关系，以降低表之间的直接耦合度。

7、设计表时不加主外键等约束关联，系统编码阶段完成后再添加约束性关联

8、选择合适的主键生成策略

数据库的设计难度其实比单纯的技术实现难很多，他充分体现了一个人的全局设计能力和掌控能力，最后说一句，数据库设计，很重要，很复杂。