数据库知识点

1.关系型数据库
底层以二维表的形式保存数据的库就是关系型数据库
种类：

• Sql Server：微软提供，收费，适用于一些中型或大型的项目中，在java中的使用占比不高（.NET中使用的较多）
• Oracle：甲骨文公司提供，收费，适用于一些大型或者超大型的项目中，在java中的使用占比非常高
• Mysql：瑞典MySQLAB公司提供，免费开源，适用于一些小型或者中型的项目中，在Java中的使用占比较高（小巧轻量），mariadb其实就是MySQL的一个分支，用法和MySQL完全一样。
• DB2：IBM公司提供，收费，在一些银行、金融等行业中使用较多。在java中的使用占比也不高。
• Sqlite：迷你数据库，嵌入式设备中（安卓、苹果手机、pad）

非关系型数据库：MongoDB、Redis、ElasticSearch、Hive、Solr、HBase

2.事务（transaction）
事务4个条件ACID：
一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务处理：

• 提交commit：当所有的操作步骤都被完整执行后，称该事务被提交。
• 回滚rollback：由于某一操作步骤执行失败，导致所有步骤都没有被提交，则事务必须回滚，即回到事务执行前的状态。

3.三大范式

• 第一范式（1NF）确保原子性,数据库中的字段不可再分
• 第二范式（2NF）消除部份依赖,规定所有非主属性必须依赖全部主键
• 第三范式（3NF）消除传递性依赖,要求非主属性必须直接依赖主属性

4.备份

• 方式一：
  C:>mysqldump -uroot -proot yhdb > d:/yhdb20190901-tony.sql
  注意：
  备份不是在mysql环境里，而是直接在操作系统环境中执行mysqldump命令，Mysql的dump为sql纯文本文件，oracle备份为二进制文件，命令有差异；
• 方式二：
  C:>mysqldump -uroot -proot --databases yhdb > d:\yh.sql
  加—databases参数，导出的sql脚本中会含有创建数据和打开数据的脚步
  起名规则：数据库名称-备份日期-备份人

5.恢复

• 方式一：
  Mysql习惯创建sql的备份文件没有创建数据库的sql语句，需手动创建

MariaDB [yhdb]> create database yhdb charset utf8;	#创建库，设置u8
MariaDB [yhdb]> use yhdb;								#打开yhdb数据库
MariaDB [yhdb]> source d:/yhdb-20190901-tony.sql	#恢复数据
MariaDB [yhdb]> show tables;							#展示所有表

• 方式二：
  使用—databases参数导出的sql含有数据库创建脚本，就可以无需创建数据库：C:>mysql -uroot -proot < d:/yh.sql

6.索引的目的是什么？
种类：主键索引,唯一索引,普通索取,全文索引,组合索引

• 快速访问数据表中的特定信息，提高检索速度
• 创建唯一性索引，保证数据库表中每一行数据的唯一性。
• 加速表和表之间的连接
• 使用分组和排序（order by）子句进行数据检索时，可以显著减少查询中分组和排序的时间

缺点：

• 创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；
• 索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；
• 当对表进行增、删、改的时候索引也要动态维护，这样就降低了数据的维护速度。

7.数据类型
数值类型：

• tinyint：占用1个字节，相对于java中的byte
• smallint：占用2个字节，相对于java中的short
• int：占用4个字节，相对于java中的int
• bigint：占用8个字节，相对于java中的long
• 其次是浮点类型即：float和double类型
  • float：4字节单精度浮点类型，相对于java中的float
  • double：8字节双精度浮点类型，相对于java中的double

字符串类型：

• char(n) 定长字符串，最长255个字符。n表示字符数。
  char类型往往用于存储长度固定的数据。
• varchar(n) 变长字符串，最长不超过65535个字节，n表示字符数，一般超过255个字符，会使用text类型，
• text大文本（长文本）类型：最长65535个字节，一般超过255个字符列的会使用text。

char(n)、varchar(n)、text的区别：

• (1)char(n)在保存数据时，如果存入的字符串长度小于指定的长度n，后面会用空格补全，因此可能会造成空间浪费，但是char类型的存储速度较varchar和text快。因此char类型适合存储长度固定的数据，这样就不会有空间浪费，存储效率比后两者还快！
• (2)varchar(n)保存数据时，按数据的真实长度存储，剩余的空间可以留给别的数据用，因此varchar不会浪费空间。因此varchar适合存储长度不固定的数据，这样不会有空间的浪费。
• (3)text是大文本类型，一般文本长度超过255个字符，就会使用text类型存储。

日期类型：

• date：年月日
• time：时分秒
• datetime：年月日 时分秒
• timestamp：时间戳(实际存储的是一个时间毫秒值)，与datetime存储日期格式相同。两者的区别是：
  • timestamp最大表示2038年，而datetime范围是1000~9999
  • timestamp在插入数据、修改数据时，可以自动更新成系统当前时间

8.字段约束

• 主键约束：特点：是唯一且不能为空；
• 非空约束：这个列的值就不能为空，但可以重复；
• 唯一约束：这个列的值就必须是唯一的（即不能重复），但可以为空；
• 外键约束：于通知数据库两张表数据之间对应关系的这样一个列。

9.存储过程
SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

create procedure 存储过程名(参数列表)
begin
//....存储过程体
end;

优点：

• 增强SQL语言的功能和灵活性
• 标准组件式编程
• 较快的执行速度
• 减少网络流量
• 作为一种安全机制来充分利用

10.调优

• 适当创建索引(索引会提高select,但是会降低insert以及update)
• 优化的查询语句
  • 在建有索引的字段上尽量不要使用函数进行操作。
  • 不要用select *
• 多个表连接查询,把每个表名置于属性之前,减少解析时间
• 使用临时表
• varchar代替char类型
• Where代替having
• 使用预编译机制
• 压缩多条sql语句，放到一句中执行

11.视图

• 视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。
• 使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。
• 视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。

12.去重

• group by 分组可以对多个列进行分组, 分组后可以过滤掉重复的数据。

SELECT id,`name`,age,count(1)
	FROM user GROUP BY `name`,age

• distinct方法：

select distinct name
	From student