数据库MySQL+JDBC学习复习总结记录

数据库MySQL+JDBC学习复习总结记录

数据库

一. SQL分类：

1. DDL(Data Definition Language)数据定义语言
   关键字：create, drop, alter
2. DML(Data Manipulation Language)数据操作语言
   关键字：insert, delete, update
3. DQL(Data Query Language)
   关键字：select, where等，模糊查询like
4. DCL(Data Control Language)
   关键字：GRANT, REVOKE等

数据库CRUD：Create, Retrieve, Update, Delete

一些Interesting point:
分页查询（想象百度一页显示的条目数量, select * from tablename limit startIdex, pageNum;）
WHERE和HAVING的区别：WHERE在分组前进行限定，如果不满足条件不参与分组；HAVING在分组后进行限定，如果不满足结果，则不会被查询出来。WHERE后不可以跟聚合函数，HAVING后可以进行聚合函数的判断。

二. 约束：

主键约束：primary key （非空且唯一，一张表只能一个字段为主键。）
非空约束：not null （创建表时在列后加语句NOT NULL）
唯一约束：unique（值不能重复，mysql中可多个null）
外键约束：foreign key（表与表产生关系，从而保证数据的正确性）

自动增长：如果某一列是数值类型的，使用auto_increment可以来完成值的自动增长。

三. 数据库的设计

设计准则（范式）

1. 多表之间的关系
   1对1（一对一关系实现，可以在任意一方添加唯一外键，指向另一方主键）
   1对多（在多的一方建立外键，指向1的一方的主键）
   多对多（需要借助第三张中间表（联合主键），中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键）

2. 数据库设计的范式
   设计关系型数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

第一范式（1NF）：每一列都是不可分割的原子数据项。（MySQL在create表时自己解决了）
第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码。在1NF的基础上消除非主属性对主码的部分函数依赖。（解决冗余数据）
第三范式（3NF）：在2NF的基础上消除，任何非主属性不依赖于其他非主属性。在2NF基础上消除传递依赖。（解决数据添加、删除问题）
巴斯-科德范式（BCNF）
第四范式（4NF）
第五范式（5NF，又称完美范式）

数据库的备份和还原（.sql文件，执行语句备份并还原）

四. 多表查询

查询语法：

SELECT
	列名列表
FROM
	表名列表
WHERE
	...


SELECT
	t1.name,
	t1.gender,
	t2.name,
FROM
	emp t1,
	dept t2,
WHERE
	t1.dept_id = t2.id;
	

SELECT * FROM emp, dept;
笛卡尔积A, B（A里有2条，B里有3条，共查出6条）
消除无用的数据（笛卡尔积）

五. 事务

事务：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。（用转帐操作理解）
操作：1. 开启事务：start transaction; 2. 回滚：rollback; 3. 提交：commit;

MySQL中，一条DML（增删改）语句会自动提交一次事务。（可以通过设置来决定是否开启自动提交，若改为0，再写DML，不commit，不会持久永远更新。Oracle数据库是默认手动提交事务）

事务的四大特征：

1. 原子性：是不可分割的最小操作单位，同时成功or失败。
2. 持久性：提交or回滚后，db会持久保存数据。
3. 隔离性：多个事务之间。相互独立。
4. 一致性：事务操作前后，数据总量不变。

事务的隔离级别：
多个事务之间是隔离的，相互独立的。但如果多个事务操作同一批数据，则会引发问题（并发），要设置隔离级别。

存在问题：
脏读：一个事务，读取到另一个事务中没有提交的数据。
虚读（不可重复读）：在同一个事务中，两次读取到的数据不一样
幻读：一个事务操作DML数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

隔离级别：

1. read uncommitted：读未提交。产生问题：脏读、虚读、幻读
2. read committed：读已提交。（Oracle默认）产生问题：不可重复读、幻读。
3. repeatable read：可重复读。（MySQL默认）产生问题：幻读。
4. serializable：串行化。（Java里有）可以解决所有的问题（锁表，一个事务在操作，另一个不让操作，完事开锁）

级别从小到大，安全性越来越高，效率越来越低。隔离级别查询、设置都有语句。

DCL：管理用户，授权。MySQL这个db里的userTABLE放的用户信息。通配符：%表示可以在任意主机使用用户登录数据库。

悲观锁：互斥锁，java的synchronized，c/c++的pthread_mutex
数据库：
（事务必须排队执行。数据锁住了，不允许并发）
用SELECT查表中的某条数据，在该语句后边加FOR UPDATE，实现查这条数据时，在当前事务没有结束的时候，其他线程的事务不能改这条记录。

CAS（操作系统的TSL，汇编原语）
乐观锁：java的如AtomicInteger
数据库：
（支持并发，事务也不需要排队，只不过需要一个版本号）

JDBC

仅记录一些学习到的JDBC重要的概念：

SQL注入：用户输入的信息被编译进去了
SQL注入解决：PreparedStatement（预编译SQL语句，相比Statement防止注入，且减少sql执行，PreparedStatement还会在编译阶段做类型的安全检查）
需要注意的是，当项目中需要用到SQL语句拼接的时候，需要用到SQL注入；单纯给SQL语句传值，用PreparedStatement

因为往往一个事务需要多条增删改才能完成：
JDBC一条DML（增删改）语句会自动提交一次事务（与mysql中默认一样）。
JDBC设置禁用自动提交，修改为手动提交事务，添加事务提交语句，在trycatch中添加回滚。（单机事务）

在mybatis中一条DML（增删改）语句默认不提交事务，需要调session.commit()来提交。

mybatis中若使用注解进行开发，就不需要mapper.xml的配置文件了