数据库与JDBC总结

数据库（DBMS）知识点小结

数据库基本知识：
1、主键和外键：主键是在关系型数据库中用一个唯一标识来表示每一行，这个唯一标识就是主键（注意特点：非空，唯一，与业务无关）；外键是指在关系型数据库中，用来表达表与表之间的关系的。
2、关系型数据库中的三种关系：
一对一关系：特点：外键在谁身上无所谓，但是外键必须唯一。列如夫妻关系。
一对多关系：特点：外键必须在多的一方身上，外键可以重复。列如学生班级关系。
多对多关系：特点：外键需要在重新创建的一张中间表上，这个中间表来管理多对多的关系，外键可以重复。列如学生老师关系。

SQL语句分类：
1、DDL语句；数据定义语句，用于数据列表设计（了解）
2、DML语句；数据操作语句，用于数据增删改的操作（掌握）
3、DQL语句；数据查询语句，用于数据查询（掌握）
4、DCL语句；数据控制语句，用于数据的控制

DML语句
1、插入单行记录
INSERT INTO 表名（列名1，列名2）
VALUES(数据类型1，数据类型2)

2、修改语句
UPDATE 表名 SET 列1=值1，列2=值2…WHERE 修改哪一行 （大部分用主键控制，也可以用其他来控制，根据业务来）

约束条件的控制：AND是并且 OR是或 BETWEEN 下限AND上限 在什么之内（包括边间值）
在指定的某几个值范围内进行搜索 列名 in(值1，值2…)
在指定的某几个值范围外进行搜索 列名 not in(值1，值2…)

3、删除数据
DELETE FROM 表名 WHERE 过滤条件（注意必须要有过滤条件 不然就是删表里的全部数据）
使用DELETE删除不加WHERE就能删除所有数据 但是会记录日志，所以可以恢复

TRUNCATE TABLE 表名
直接删除整张表。**速度快，无法恢复。**不能用于有外键，在有外键约束的情况下，先删从表数据，再删主表数据

DQL语句
最简单的查询结构
1、投影操作
可以在投影结果上对数据进行整合修改，但该修改不影响原表数据
SELECT 列名1，列2…FROM 表名 可以用关键字AS将列名改名
SELECT 表名.列名1，表名.列名2…FROM 表名 优点：增加可读性
SELECT 别名.列名1…FROM 表名 AS别名

SELECT CONCAT（列名1,’-’,列名2）FROM 表名 ————进行列1列2 的拼接

利用DISTINCT关键字排除重复行
SELECT DISTINCT 列1，DISTINCT 列2 FROM 表 ————列1，列2 必须都要有DISTINCT

分页显示，MySQL专用语句 LIMIT（一定写在语句最后）
SELECT 列1，列2 FROM表LIMIT 开始的序号,显示的行数————开始的序号是从0开始的，所以它的值应该是（页数-1）显示的行数
要显示所有列，可以使用selectfrom 表名，但是用*做投影，运行效率低！在实际开发中不准使用！

2、模糊查询
SELECT 列名1，列2…FROM 表名 LIKE ‘表达式’
通配符’_‘表示单个任意字符，通配符’%'任意个任意字符

3、处理null的情况
在WHERE条件中判断是否为NULL，使用IS NULL和IS NOT NULL

4、排序操作
SELECT 列1，列2,列3FROM 表 WHERE 条件 ORDER BY 列名1，列2（默认情况下按列升序，升序是加ASC）
降序列名后面加DESC：ORDER BY 列名1 ，列2 DESC

DQL语句的执行顺序
第一步：执行FROM
第二步：WHERE条件过滤
第三步：执行SELECT投影列
第四步：执行ORDER BY排序

使用聚合函数进行统计
1、COUNT：进行统计行数量
统计满足条件的行数 COUNT() 括号中的表达式有三种用法（*）表示所有行；（ALL 列名）代表这一列不为空的行有多少；（DISTINCT 列名）代表这一行有多少个不为空不重复的行

2、SUM：获取单个列的数值的合计值
SUM只有ALL和DISTINCT这两种 （ALL 列名）——其中ALL可以省，这是统计该列所有行非空值之和；（DISTINCT 列名）——统计该列所有行非空值且不重复之和 数据 相加

3、AVG：
计算某个列的平均值：同SUM，注意AVG和SUM/COUNT(*)的区别，有null值的话，前者数据大一点，后者要小点
4、MAX：
某一列的最大值:同SUM，不过它直接打括号不用写ALL或者DISTINCT写列名 直接得到最大值
5、MIN：
某一列的最小值:同SUM，不过它直接打括号不用写ALL或者DISTINCT写列名 直接得到最小值

数据分组：
在一张表上，根据条件将所有行进行划分，然后对同组的行进行统一的投影，最终把所有的结果显示在一个结果集上。
SELECT列A,聚合函数（）FROM表名 WHERE过滤条件 GROUP BY 列名 1，列名2 如果没有WHERE那就是对本表所有的值进行聚合
SQL在执行中，会把分组列的值一样的行，划分为一组，并作为最终结果集的一列，来进行展示。所以，在分组的情况下，投影列只有聚合函数或分组相关列——具有该组统一共性的展示列——才有意义。
使用HAVING子句————在分组投影集以后的结果集当中再次筛选数据。
写在GROUP BY后面 HAVING过滤条件
WHERE中不能有任何聚合函数，而HAVING是可以有聚合函数

子查询：
嵌套在主查询中的查询——子查询
当最终投影列，来自多张表的时候，需要用子查询。
首先要确定谁是主查询，依据是最终结果集的每一行都是基于哪张表的。
理论上可以在任何位置都可以嵌套子查询
子查询中的SQL语句要求查询的值只能是单行和单列，注意：但是在FROM语句中嵌套可以是多行多列

FROM中嵌套子查询时，当我们要查询的结果不是来自于某个单独实体表，而是从某个结果集当中再次进行二次查询的时候，可以使用。

在WHERE语句中 in 和notin中是单列多行，在>,<,=,>=,<=,!=是单行单列
ALL运算符 和子查询的结果逐一比较，必须全部满足时表达式的值才为真
ANY运算符 和子查询的结果逐一比较，其中一条记录满足条件则表达式的值就为真
EXISTS/NOT EXISTS
EXISTS判断子查询是否存在数据，如果存在表达式为真，反之为假；NOT EXISTS与之相反。

组合查询：使用UNION（纵向联接）
语法：
SELECT …
UNION
SELECT…
要点：1、UNION可以连续使用
2、UNION之后 结果集的列名是以第一个Select的列名为准
3、放置同一列的数据类型不用保持一致
4、同一列的数据值要去重，不管它来自哪个查询
5、要保证每个Select的列数目要相等。

表联接（横向连接）
原理：笛卡尔乘积原理
关键字：JOIN（用来连接），ON（用来排除错误信息）
SELECT 列1，列2 FROM表1 JOIN 表2
ON表1.列=表2.列
内连接：标准语法 INNER JOIN 不过INNER可以省略
外连接：左外连 LEFT OUTER JOIN 右外连RIGHT OUTER JOIN OUT可以省略 还有一种全连接 FULL 但是MYSQL不支持
自连接：两张同样的表自己关联，运用于特殊设计的自关联表

JDBC

SQL是直接访问数据，而JDBC是一种用于执行SQL语句的Java的API

JDBC两大类：
面向开发人员：作为是API
面向数据库厂商：是接口

JDBC步骤：
1、导入数据库jar包——1、拷贝jar文件到工程下lib文件夹2、配置BuildPath构建路径
2、加载驱动——告诉驱动管理器，我们要操作的是哪个数据库
Class.forName(’'com.mysql.jdbc.Driver");
3、获取连接——Connection接口
Connection con=DriverManager.gerConection(“jdbc:mysql://127.0.0.1:3306/j172?useUnicode=true&characterEncodion=UTF-8”，用户名，密码)补充知识：URL统一资源定位符
协议://ip地址:端口号/具体资源?参数名1=值1&参数2=值2
4、关闭连接
先关Statement,再关Connection
.close()
5、构建SQL语句
口诀：选中双引号双加号
6、获取语句对象——Statement
Statement state=con.createStatement();
7、让语句对象执行SQL语句——DML语句调用executeUpdate() 返回Int类型 ，DQL语句调用executeQuery()返回结果集（ResultSet）
DQL语句用next（）方法来查询如果查到返回true同时可以取该行，没查到返回flase
取数据：get数据类型（列名）若有别名用别名取数据

SQL注入
当SQL语句的部分内容采用字符串拼接的方式执行，那么有可能发生注入的字符串改变原有结构，这被称为SQL注入。
解决方式：
1、SQL语句执行之前，先把SQL的结构交给数据库进行预先编译；这样先让SQL的结构固定下来；然后再通过传参的方式，放入字符串条件值。————关键字PrepareStatement
步骤：
1、SQL语句首先确定结果，需要传的值的位置用？占位符代替
2、交给数据库预编译：con.prepareStatement(sql);返回的是PrepareStatement对象
3、设置值，set数据类型（？的下标，真正的值）
4、执行这个SQL语句，executeUpdate() ；executeQuery()但是可以不用再次传值了

怎么在获取语句对象的时候要求返回生成的主键？
在获取语句对象的时候，传入RETURN_GENERATED_KEYS,要求返回自动生成的主键（PrepareStatement；Statement都有）
步骤：
ResultSet rs=ps.getGeneratedKeys();——完成主键查询
key=rs.getInt———通过结果集获取当条新增记录的主键

事务操作：
在复杂业务当中，我们有可能需要执行多条DML语句，才能完成一个完整的业务操作。而多条DML语句要同时成功才能代表该业务成功；只要有一个失败该业务失败，其他没有失败的也要恢复开始状态。
事务的四大特征（ACID特性）：
1、原子性；指代的是业务不可再分的，那么完成这个业务的多条DML语句组成了完成这个业务的基本单位。
2、一致性；业务最终执行的结果要么DML全部成功要么DML全部失败
3、隔离性；该业务的多条SQL必须作为一个整体完成，SQL与SQL之间不能被别的业务插入
4、持久性；指代的是事物重点考虑的是DML语句，这种影响数据持久化的操作

步骤：
1、开启事务；设置自动提交为假（在try块最前面）——con.setAutoCommit(false)
2、所有DML成功，则提交事务；手动提交（在try块最后）——con.commit();
3、只要发生失败，事务回滚;con.rollback();——在catch块中回滚

注意：
事务的多条DML语句，在JDBC实现中，首先要保证它们用的是同一个Connection对象

JDBC小结：
1、JDBC中下标都是从1开始
2、不用滥用PrepareStatement语句的数目最好不用过多，因为数据库预编译以后会存放这些预编译语句，过多会让数据库负担过重；最好使用在需要外部输入的时候使用
3、JDBC中的setDate方法是要求传入sql包中的Date,而我们在Bean的属性设计中用的是util包中的Date，需要做转换——new java.sql.Date(new Date().getTime())