数据库总结

基本概念

一、（冯·诺伊曼）计算机的基本组成：运算器、控制器、存储器、输入设备和输出设备

内存存储特点：易失性存储；读写速度快；支持O（1）的下标访问

硬盘、磁盘、光盘等特点：持久化存储；读写速度慢；更多支持顺序访问

二、概念

1、数据库（database）

狭义:一系列有关系的数据的总和;数据广义:DBMS 数据管理软件

2、数据管理系统（DBMS）

1）DBMS在管理数据时，总是存在共性，关于数据的建模与抽象问题，一般应用广泛的被称为关系型模型(relationship model)关系型数据库管理软件(RDBMS)

2）DBMS-软件的功能以对表中的记录进行增、删、查、改的操作（数据本身的操作），除此之外，我们还需要对表的结构进行调整的操作!
DBMS有很多种的实现:MysQL.Oracle ....
业内统一了标准，抽象了一门语言，专门用于DB的操作，叫做查询语言（query language)，这门语言非常结构化(structure query language -> SQL)
学习SQL语言的使用，本质上学习的是如何使用DBMS来进行数据的管理

3、SQL

SQL主要是这么三大类：

数据本身的操作:DML(数据维护语言)Data Manipulation Language
结构的操作:DDL(数据定义语言)Data Definition Language
修改DBMS配置:DCL(数据控制语言)Data Control Language   修改权限、权限DBMS用户

4、mySQL

1）MySQL是一种典型C/S (Client/Servery）架构 (MySQL 官方开发的Wcrkbench)

2)CS 架构:一个程序(client）通过网络与另一个程序(server）进行通信
Client:客户端     

Server:服务器 

3)服务器程序 (mysqld)：提供服务service的一种程序，提供RDBMS服务以接受+看懂SQL，并进行回复提供服务

4)

3、表(table) 
现实中具有共同特征的一系列数据的总和，类似:学生、老师、课程...表类似Java语言中类的概念
表有的这套属性，可以视为类的属性的定义

三、SQL语言

1、SQL通过反引号门把库名、表名、字段名引起来，消除歧义

2、建库操作：CREATE DATABASE库名称CHRASETutf8mb4; 创建一个叫做XX的数据库，默认字符集是utf8mb4
3、库名称、表名、字段名:
1）不要使用关键字，一般也用不到反引号
2）最好只出现以下字符。全部统一小写[a-z][0-9][下划线_]而且，不要以下划线和数字开头

四、DML核心

SELECT  投影
要查询的字段(备注1) 支持表达式
FROM   
查询源(备注2)可能不止一张表、也可能不是真实表
WHERE 当满足....条件时，进行过滤
结果是bool类型(1/0)的关系表达式
检索条件
ORDER BY排序
排序条件
LIMIT
符合条件的记录中继续按照出现行进行挑选

select 投影:

1.*代表全字段

2．用逗号分割的表达式，表达式中可以有字面量(1、"Hello")、字段(name)、函数调用等参与的运算表达式

3.任意表达式，都可以给出别名(alias)

4.某些情况下，可以去重distinct

5. count(...)这个函数调用必须掌握

from查询源:
1.表名
2.表名也可以使用别名exam_result | exam_result AS er 把er作为表的别名| exam_result er同理

where选择:

让查询源中的每条记录，都经过 where 指定的布尔表达式，然后只保留结果为真的记录

order by:
1.没有order by，查询出的结果认为无序
2.order by默认升序(asc)，可以指定为降序(desc)
3.order by 允许指定多字段，含义:优先按照第一个字段为依据进行排序，在第一个字段的值相等的情况下，按照第二个字段的指定进行排序。同理，如果有更多的排序凭据，也是一样处理
4.order by可以使用字段的别名

limit:     limit ... offset ...
offset:偏移limit:

Update 更新操作:
指定 where 条件(如果不写where，视为所有记录都满足条件)，一定可以选择出一批记录(可能只有1条，或者1条没有)只能统一将这批记录中的指定字段的值，一次性进行修改!

五、maven

1、一个java的程序:

真正干活的是一条一条的语句。

语句以方法为单位进行组织。

方法以类为单位进行组织。<--- Java 中的第一公民

类以包为单位进行组织。

2、我们写的类，自己写*.java，自己编译成*.class       不是我们写的类，我们有的直接就是*.class文件

3、maven的使用
开发稍微复杂的项目时
1.该应用属于哪个组织groupld

2.该应用的名称  artifactld          artifact : 工艺品、作品
3.该应用的版本        1.0-SNAPSHOT              shapshot:快照（内部开发版本，不对外)
4.最终，应用的全名:              groupld.artifactld.version

六、JDBC

1.JDBC : Java DataBase Connectivity      专门处理Java连接数据库的一套类

2.查询过程

第一步：进行连接 MySQL 服务器的一系列配置

导mysqlDataSource包；

第一种：

setServerName：设置mySQL的服务器代码（主机代码）-->"127.0.0.1"/"localhost"

setPort：设置mySQL的端口

setUser：设置连接mySQL的用户名（root用户名连接）

setPassword：设置连接mySQL的密码

setDatabaseName：设置连接上mySQL的默认数据库

setCharacterEncoding：设置使用的字符集-->"utf8"

setServerTimezone：设置服务器默认时区-->"Asia/Shangha"

setUseSSL：把SSL关掉

第二种：setUrl   setUser     setPassword

第二步：从 dataSource 获取连接(Connection)

导 Java.sql 下的Connection包（可能会抛出一个受查异常，try catch 和 继续向上抛异常中这里选择继续向上抛异常）

处理关闭问题：定义Connection的初始值为空       执行结束时，需要关闭 Connection

两种方法:方法一：try{语句}finally{if(connection != null){connection.close()}}

               方法二：try-with-resource   try(语句){}代码执行到这里时，会自动调用.close()

第三步：准备好要执行的 SQL        在 Java 代码中，以字符串形式体现 SQL ：String sql = "sql语句"

第四步：要执行 SQL，需要从 Connection 对象中得到 XXXStatement对象    statement : 语句

导 Java.sql 下的PreparedStatement包

处理关闭问题：定义PreparedStatement的初始值为空       执行结束时，需要关闭 PreparedStatement

第五步：通过 XXXStatement 对象执行查询过程   用到execute操作

execute有两个变形：

executeQuery: 用来执行查询类的 SQL，有查询结果

executeUpdate: 用来执行 增、删、改类似的操作，没有查询结果

这里使用executeQuery，从这里可以得到  ResultSet 查询到的结果集（Result 结果 Set 集合）

*ResultSet resultSet = preparedStatement.executeQuery()

导 Java.sql 下的ResultSet包

处理关闭问题：定义ResultSet的初始值为空       执行结束时，需要关闭 ResultSet

第六步：遍历我们的结果集，遍历过程是以行记录作为单位进行的操作            一次操作是一行

resultSet.next() 1: 返回值代表是否还有新行 2: 让游标(cursor)走到下一行

boolean hasNext = resultSet.next(); 返回值是布尔类型

第七步：

通过字段名称获取值 //int id = resultSet.getInt("id"); //System.out.println(id);

通过字段顺序获取值（顺序 SELECT 后边的顺序，* 就是建表顺序） // 不是从 0 开始，是从 1 开始 //int id2 = resultSet.getInt(1); //System.out.println(id2);

3.概念总结
1）连接数据库，执行SQL场景

DataSource对象（数据源)

     serverName、port ...
2）Connection对象（连接)

     通过dataSource获取Connection对象

     connection.close()
3）PrepareStatement(语句)
     通过connection对象+sql语句得到

     ps.close()
4）如果执行的查询，得到ResutSet(结果集)

如果执行的不是查询，没有ResultSet对象
rs.close()

4.执行非查询类操作：：没有ResultSet对象

 5.插入操作时，拿到插入后的自增id

 6.SQL的注入

PrepareStatement的参数写法
SQL: "select * from books where current_count > ?"
"?"参数占位符(parameter placeholder)

ps.setInt(要替换第几个参数，要求从1开始算,int类型的要替换的值)
ps.setString(1,"XXX")

七、MySQL事务

1.数据的一致性(Consistency)
DBMS 用户根据其应用场景，对数据提出的一系列约束要求
2.事务处理结果： 成功 或者  失败

3.风险

1)SQL执行过程，出现异常，导致SQL执行不下去

        异常的原因:

        1.硬件原因:JDBC程序所在的电脑死机了

                          MySQL服务器程序所在的电脑死机了

                          连接的网络中断了
        2.软件问题:后边SQL执行异常（比如SQL写错了)
                          程序遇到软件异常(NPE、AlE...)
2)当多个会话(Session)同时动同一份数据，造成互相干扰

4.如何使用事务(Workbench、可以执行SQL的客户端)
核心:如何让DBMS知道我们要执行的哪些SQL应该被当成一个整体（事务）来对待

通过明确:开头＋结尾
start transaction;

开始事务，告诉DBMS，接下之后的所有SQL应该被当成一个整体（事务）来对待正常执行1条或者多条SQL
commit;
以成功的状态结束事务（提交事务)，告诉DBMS，这组SQL已经全部告诉DBMS了，再接下来的就是下一组事务了

rollback;

代表本次事务全部不生效

5.在jdbc 中执行事务
默认情况，每条SQL都当成一个的事务对待。自动进行提交(auto commit)

关闭auto commit
明确进行commit

1) 关闭自动提交功能 c.setAutoCommit(false);

auto commit 和 Connection 对象绑定，再得到一个新的 Connection 对象，默认还是 autocommit = true 的

2) 进行事务的提交 c.commit();

进行事务的回滚，主动终止 c.rollback();

事务中允许添加保存点(savepoint)，类似游戏的存档。使得回滚操作时，可以选择回滚到哪个保存点。

八、MySQL索引

1.

九、数据建模(数据抽象、表的设计)

(虽然目前专注于DB领域，但数据建模（数据抽象）的价值不局限于DB领域)

三大层次:
1.现实世界（概念层)
2.开发人员头脑中的世界（逻辑层)计算机软件世界

万物皆对象:现实世界的任何概念都可以被抽象成逻辑层的一个概念现实世界中的概念，可以是有形的入也可以是无形的;可以是静态的一个物体（实体),也可以是动态的一次过程;

3.计算机硬件世界（物理层)
B+树or红黑树or顺序方式存储哪种方式更快、哪种方式更好

关注属性特征即可（逻辑视角)
从概念层->逻辑层给定场景如何建表

设计人员(开发人员、架构师、领域设计师)将现实世界（逻辑层)的某个场景（主要表现为其中的实体概念和关系概念)通过一系列步骤，设计为可以进行计算机世界工作的素材（表现为表或者类和对象)
∶对象{数据类对象（属性)、控制类的对象(方法)}
DBMS表的大多是数据类的对象
指导步骤:
一个系统多个场景组成   一个场景是由多个角色＋动作  …
1)需求分析（一般是产品经理的职责)
        1）角色确定2)用户故事
2）进行建模（(开发人员)
        1）确定E-R图、确定表结果
3）进行调优设计(不在现阶段考虑范围)(资深开发人员)

4)实际建表(DBA)
 

数据建模中的指导步骤

1）确定角色——>系统的用户有哪些角色，思考问题的视角
2）根据角色，写故事(动作)——>以角色为视角，写出用户故事(user story)，主要是需求动作

3）根据故事，找实体(Entity)——>基本就是故事中出现的名词
4）表现实体之间的关系(借助E-R图工具)

实体Entity（矩形)     属性（椭圆)    关系Relationship（菱形)

A和B之间的关系，可能表现为:1对1的关系；1 对m的关系；m对1的关系；m 对n的关系
一个系统中，一个实体和其他实体之间必然产生必然的关系
两个实体之间的关系可能不止一种

5）为实体、关系确定属性（借助三大范式)

        1)主键必须明确出来，每张表必须有主键
           一张表里选择主键时可能有多种选择，都是候选键
        2）确定了属性之后，通过数据库的三大范式(3NF)，检查选择的字段是否合理

        （三大范式的目标，是消除数据的冗余(redudent)）

范式:一种经过前人总结出的指导意见
1）能遵守尽量遵守
2）如果某些特殊情况下，有违反范式的需要了，是可以违反的，至少数据库设计中有很多范式，只是比较经典的是三大范式

三大范式∶
1）要求表中的字段是不可再分割的
所谓的不可再分割，得根据上下文确定
2）要求表中的字段，和该表的主键有直接的关系，而不是间接的一种关系
3）要求表中的字段，和该表的主键（一般是说复合主键的情况)﹔和完整的主键有关系，而不是部分主键有关系
 

6)为每个属性确定类型＋约束

 .)根据优化需求，适当调整结果
7)完成建表(通过workbench 建好表，复制出来

十、进阶SQL
1.外键约束(Foreign Key Constraint)

建表时就明确外键存在，无论选择哪种策略，drop班级表都无法成功，必须先drop学生表，再drop班级表。外键策略主要影响的是update和delete
on update ... on delete ...
当udpate时;当delete时
1.NO ACTION 和 RESTRICT（NO ACTION 在 MySQL 下和 RESTRICT 同义）
不允许delete或者update
2.SET NULL
允许删除，同时把学生的记录的cid设为null
3.CASCADE
允许删除，同时把对应的学生记录删除

2.复杂查询

3.聚合查询（aggregation）

意义：常见的统计总数、计算平局值等操作，可以使用聚合函数来实现

SELECT 分组数据,COUNT(*) FROM 表 WHERE 条件 GROUP BY 分组数据;

聚合查询时(SQL标准)
select子句中只能出现两种内容1.分组依据(group by后边的字段)⒉聚合函数(count ....)
其他的内容都不允许出现

备注:
MySQL在实现时，如果没有设置严格模式，允许出现其他字段，但值不保证

聚合函数:
最常用: count(...)计算行数
了解（只能用在数值相关的字段上) : max(...)、min(...)、avg(...)、sum(..)

聚合后再过滤having
对比 where(聚合前过滤)
 

4.联表查询(多表查询)

意义：实际开发中往往数据来自不同的表，所以需要多表联合查询。

关联查询可以对关联表使用别名

例：select * from 班级表,学生表;同时从多表进行查询，用逗号分割表名称
得到的是两个关系的笛卡尔积(关系代数)

从笛卡尔积中过滤有效数据：例：select * from 班级表,学生表 where 班级.cid =学生.cid;
当字段名有歧义〈多张表中有相同名字的字段)，应该使用表名.字段名以作区隔

内联(inner join)VS外联(outer join)
内联的结果，只会保留有班级有学生的结果外联的结果:
1.左外联left outer join（左联left join)，以左表为主

2.右外联right outer join(右联right join)，以右表为主

3.全外联，左右表都参考（MySQL不支持全外联）

5.子查询(subquery)

6.合并查询