数据库MySql与JdbcTemplate总结

数据库部分总结

本文的目录如下

MySql:

0.mysql管理
1.mysql语法
2.mysql约束
3.多表关系与查询:
多表关系:
多表查询:

事务

1.事务基本概念
2.事务管理
手动管理:
自动管理:
3.JDBC中的事务管理
4. 事物特性和隔离级别(概念性)
4.1 事务的四大特性ACID（面试题）
4.2 事务的并发问题和隔离级别

JDBC

1.注册驱动
2.获取连接
3.创建SQL执行平台
4.执行SQL语句
5.处理结果
6.释放资源
7封装
8示例

预编译和连接池

1.预编译
1.0. SQL注入漏洞
1.1 什么是预编译对象
1.2. 怎样使用预编译对象
1.3. 预编译对象的好处
2.自定义连接池
2.1功能增强的方式
2.2装饰者模式创建步骤
2.3自定义连接池示例
3.连接池的使用
3.1 常见的连接池
3.2.c3p0连接池的使用
3.3.druid连接池的使用

JdbcTemplate

一. 什么是元数据
二、自定义JDBCTemplate
三、JdbcTemplate的使用

1. 执行任意语句：execute()
2. 执行DML语句：update()
3. 执行DQL语句：queryxxx()

数据库部分可以说是前后端等领域的交集部分.下面我将梳理数据库的基础知识,总结相互间的联系.

MySql:

 数据库：存储数据的仓库，本质是一个文件系统。可以结构化的存储数据，方便的进行数据的增、删、改、查。

0.mysql管理

启动服务：net start mysql
关闭服务：net stop mysql
登录MySql：mysql -u用户名 -p密码
退出：exit 或者 quit
1.mysql语法
DDL:数据定义语言。

   1.创建库：create database 库名称	
            2.删除库：drop database 库名称
               eg:CREATE DATABASE IF NOT EXISTS heima63 CHARACTER SET gbk;
            3.查询有哪些库 : SHOW DATABASES;
            4.查看具体库的创建信息:SHOW CREATE DATABASE heima;
            5.修改库的字符集:ALTER DATABASE heima63 (DEFAULT) CHARACTER SET utf8;      default可省
            6.使用库:USE heima;
            7.查询现在使用的库:SELECT DATABASE();
            8.  数据类型
          int：整型，对应Java的int, Integer
          double(m,n)：小数，对应Java的double，Double
          varchar(n)：可变长度字符串，对应Java的String
          char(n)：固定长度字符串，对应Java的String
          date：日期类型，对应Java的Date
          datetime：日期时间类型，对应Java的Date
            9.创建表:CREATE TABLE student (                         
	                      id INT,
	                     NAME VARCHAR(10),
	                     age INT,
	                     sex CHAR(1),
	                     birthday DATE,
	                     address VARCHAR(100)
                      )
            10. 查询当前库里有哪些表SHOW TABLES;
            11. 查看具体表创建信息:SHOW CREATE TABLE student;
            12. 查看表的结构:DESC student;
            13. 删除表:  DROP TABLE USER;
            14. 给表添加列：ALTER TABLE student ADD salary DOUBLE(9,2);
            15. 修改表中的列数据类型:ALTER TABLE student MODIFY salary INT;
            16. 修改表列的重命名：ALTER TABLE student CHANGE english math INT;
            17. 修改表删除列:ALTER TABLE student DROP address;
            18. 表重命名：RENAME TABLE student TO students;
            19. 修改表的字符集：gbk（不建议使用）:ALTER TABLE students CHARACTER SET gbk;

DML：数据操作语言。

 1.添加数据:  INSERT INTO students (id,NAME,age) VALUES (4, '公明', 45);     全加,可以省去表后的数据声明
 2.修改数据:  UPDATE students SET salary = 20000 WHERE id = 4
 3.删除数据:  DELETE FROM students WHERE id = 3;
             DELETE FROM students;    --  一条一条删
             TRUNCATE TABLE students; --  摧毁重建表

DQL：数据查询语言。

 1.SELECT NAME AS '姓名', IFNULL(english, 0) FROM students;   AS可省,DISTINCT去重,*为所以信息
            2.条件查询:
                        单条件:运算符有：>, <, >=, <=, =, <>。is null 和 is not null
                       多条件:连接符有：and, or, not
                       范围查询:between 开始值 and 结束值，包含开始值和结束值
                                       离散: in(值1, 值2, ....)
                       模糊查询:语法：like '表达式'。在表达式里可以写：  _：一个任意字符;   %：任意个任意字符
            3.排序查询:语法：order by 排序字段 排序规则, 排序字段2 排序规则2, ...。排序规则有：
                       ASC：升序，从小到大，默认的
                      DESC：降序，从大到小
           4.聚合函数:  用于统计的,会忽略null。常用的聚合函数有：
                      count()：统计数量
                      sum()：求和
                      avg()：求平均值
                      max()：求最大值
                      min()：求最小值
           5.分组查询:  group by 分组字段 having 分组后的过滤条件
           6.分页查询:  limit 起始索引，查询数量
           7.综合查询:eg:  SELECT address, COUNT(*) FROM student3 WHERE sex = '男' GROUP BY address    
                          HAVING  COUNT(*) > 1 ORDER BY COUNT(*) ASC LIMIT 0,2;

DCL：数据查询语言。

-- 1. 创建用户
create user 'tom'@'%' identified by 'tom';
-- 2. 用户授权
grant all on *.* to 'tom'@'%';
-- 3. 查看权限
show grants for 'tom'@'%';
-- 4. 取消授权
revoke delete on *.* from 'tom'@'%';
-- 5. 删除用户
drop user 'tom'@'%';
-- 6. 修改密码
set password for 'tom'@'%' = password('1234');权
revoke delete on *.* from 'tom'@'%';
-- 5. 删除用户
drop user 'tom'@'%';
-- 6. 修改密码
set password for 'tom'@'%' = password('1234');

TCL：事务管制语言。

-- 1. 开启事务
start transaction;  -- 或者  set autocommit = 0;
-- 2. 执行多条DML语句
-- 3. 关闭事务：提交事务
commit;
-- 3. 关闭事务：回滚事务
rollback;

2.mysql约束

• 主键约束
  主键：一张表的唯一标识。原则上：每张表必须有主键，且只能有一个主键
  特点：被主键约束的字段，值必须是非空、唯一主键约束：
  语法：primary key
  主键自增策略：
  要求：主键字段必须是整数类型，MySql会自动生成主键值：1,2,3, … +1
  语法：primary key auto_increment

• 唯一性约束
  效果：唯一性约束的字段，值不能重复，但是可以为null
  语法：字段名 类型 unique

• 非空约束
  效果：非空约束的字段，值不能为null
  语法：字段名 类型 not null

• 默认值约束
  效果：默认值约束的字段，如果不指定值，取默认值（如果指定null值，值就是null，不取默认值）
  语法：字段名 类型 default 默认值

• 外键约束
  效果：外键约束的字段，必须从指定表的主键中取值。
  目的：保证数据的一致性和完整性，避免脏数据
  语法：
  创建表时设置外键：[constraint 约束名称] foreign key(外键字段) references 主表(主键)
  已有表里设置外键：alter table 表名称 add [constraint 约束名称] foreign key(外键字段) references 主表(主键)

  3.多表关系与查询:
  多表关系:

  范式:共有6大范式：层层递进
  1NF：最基本的要求 要求所有列不可拆分
  2NF：1NF+更多要求 表里所有列完全依赖于主键
  3NF：2NF+更多要求 表里字段如果要引入其它表数据，要把字段设置为外键，引用其它表的主键
  BCNF：3NF+更多要求
  4NF：BCNF+更多要求
  5NF：4NF+更多要求， 完美范式

  一对一:
  不常用，因为一对一的两张表，通常可以合并成一张表。但是以下情况，建议拆分表

  一对多
  例如：分类和商品，用户和订单
  建表原则：在从表上增加一个字段，作为外键指向主表的主键。
  从表：多的一方
  主表：一的一方

  多对多
  例如：老师和学生，订单和商品，学生和课程
  建表原则：建立一张中间关系表，表里要有两个字段，是外键分别指向两张表的主键

  多表查询:
  迪卡尔积：多表之间毫无意义的排列组合，组合的结果是迪卡尔积。用多表关联查询来避免.

内连接
内连接的查询效果：查询两张表之间必定有关联的数据
内连接的查询语法：
显式内连接：select 字段 from 表1 inner join 表2 on 关联条件 where 条件

隐式内连接：select 字段 from 表1, 表2 where 关联条件 and 条件

外连接
外连接查询的效果：查询一张表的全部数据，以及另外一张表的关联数据
左外连接：查左表的全部数据，以及右表的关联数据
select 字段 from 表1 left join 表2 on 表关联条件 where 条件

右外连接：查右表的全部数据，以及左表的关联数据
select 字段 from 表1 right join 表2 on 表关联条件 where 条件

子查询
是一种查询的技巧，没有固定语法。查询嵌套
子查询结果是一行一列：一个值
子查询结果是多行一列：一个集合
子查询结果是多行多列：一张虚拟表

 SELECT * FROM (SELECT * FROM emp WHERE salary > 5000) t