数据库入门

数据库

数据库的基本概念：

数据库：DataBase，简称为DB

数据库：用于存储和管理数据的仓库
数据库的特点：
1. 持久化存储数据：数据库是一个文件系统
2. 方便存储和管理数据
3. 使用了统一的方式操作数据库 —— SQL
常见的数据库软件：
- MySQL：开源的免费小型数据库，已经被Oracle收购，MySQL6.x版本开始收费。
- Oracle：收费的大型数据库，Oracle公司的产品
- DB2：IBM公司的收费数据库产品，常应用在银行系统中
- SQLServer：微软公司的中型收费数据库，常用于C#、.NET等语言
- SyBase：已淡出历史舞台，提供了一个专业的数据建模工具PowerDesigner
- SQLite：嵌入式的小型数据库，应用在手机端

MySQL数据库软件：

安装：参见教程
卸载：
1. 在MySQL的安装目录下找到my.ini文件，复制其中的datadir="路径"这一行
2. 控制面板卸载MySQL
3. 删除掉第1步datadir路径的那个目录及所有文件
配置：
- 启动MySQL服务
  1. 在windows服务里手动启动
  2. 在cmd窗口下输入services.msc打开windows的服务窗口再启动
  3. 以管理员身份运行cmd，输入net start mysql即可启动服务
- 关闭MySQL服务
  1. 在windows服务里手动关闭
  2. 在cmd窗口下输入services.msc打开windows的服务窗口再关闭
  3. 以管理员身份运行cmd，输入net stop mysql即可启动服务
- 登录MySQL：
  - 控制台输入mysql -u用户名 -p密码，也可以-p不跟密码，然后再密文输入密码
  - 远程登录：
    1. mysql -h主机地址 -u用户名 -p密码
      
      例如登录本机：mysql -hlocalhost -uroot -p123456
    2. mysql --host=主机地址 --user=用户名 --password=密码
      
      例如登录本机mysql --host=localhost --user=root --password=123456
- 退出MySQL：
  - 控制台输入exit
  - 控制台输入quit
MySQL目录结构：
- MySQL安装目录：
- MySQL数据目录：
- 几个概念：
  - 数据库：文件夹
  - 表：文件
  - 字段：表中的每一行记录

SQL

概念

结构化查询语言（Structured Query Language）简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL定义了操作所有关系型数据库的规则，但是不同的数据库的操作方式可能存在差异，称为”方言”

通用语法

SQL语句可以写单行或多行，以分号结尾
可使用空格和缩进来增强语句的可读性
MySQL数据库的SQL语句不区分大小写，但关键字建议大写
注释：
- 单行注释：-- 注释内容 或 #注释内容(MySQL特有)，-- 注释内容的形式注意空格
- 多行注释：/* 注释内容 */

SQL分类

DDL(Data Definition Language)数据定义语言

用来定义数据库对象：数据库、表、列等。

关键字：create、drop、alter等

操作数据库：CRUD
- C(Create)：创建
  - 创建数据库：create database 数据库名称;如果数据库已存在会报错
  - 创建数据库，判断数据库不存在才创建：create database if not exists 数据库名称;
  - 创建数据库，并且指定字符集：create database 数据库名称 character set 字符集;
- R(Retrieve)：查询
  - 查询所有数据库的名称列表：show databases;
    
    对于MySQL自带的几个数据库：
    1. information_schema：存放视图，并不对应真正的物理文件
    2. mysql：核心数据库，存放核心数据
    3. performance_schema：性能相关
    4. test：测试数据库，可以随意修改
  - 查看某个数据库的创建语句(以及字符集)：show create database 数据库名称;
- U(Update)：修改
  - 修改数据库的字符集：alter database 数据库名称 character set 字符集名称;
- D(Delete)：删除
  - 删除数据库：drop database 数据库名称;如果数据库不存在会报错
  - 删除数据库，判断数据库存在才会删除：drop database if exists 数据库名称;
- 使用数据库
  - 查询当前正在使用的数据库名称：select database();：可能返回NULL或正在使用的数据库名称
  - 使用某个数据库：use 数据库名称;
操作表
- C(Create)：创建
  - 语法：
    1. 创建表，同时指定列名和数据类型：
      
      crate table 表名(列名1 数据类型1,列名2 数据类型2,...,列名N 数据类型N)
      
      注意：最右一个列名和数据类型不能加,逗号
      
      数据类型：(除了数字类型，其他类型要使用引号引起来(单双都可以))
      
      常用的数据类型：
      1. int(integer)：整数类型，例如age int
      2. double：小数类型，需要指定小数位数，例如score double(5,2)表示一共5位，小数点后2位
      3. date：日期类型，只包含年月日，格式为yyyy-MM-dd
      4. datetime：日期类型，包含年月日时分秒，格式为yyyy-MM-dd HH:mm:ss
      5. timestamp：时间戳类型，包含年月日时分秒，格式为yyyy-MM-dd HH:mm:ss
        
        datetime和timestamp的区别：如果不给timestamp赋值，或给timestamp赋值为null，则默认使用系统当前时间赋值，适用于添加当前时间
        
        varchar：字符串类型，需要指定最大字符长度，例如name varchar(20)
    2. 从其他表复制表：create table 表名 like 被复制的表名;
- R(Retrieve)：查询
  - 查询某个数据库中所有的表的名称：show tables;
  - 查询表结构：desc 表名;
- U(Update)：修改
  - 修改表名：alter table 表名 rename to 新表名;
  - 修改表的字符集：alter table 表名 character set 字符集名称;
  - 修改表中的字段：
    1. 添加一列：alter table 表名 add 添加的列名添加的数据类型;
    2. 修改列名称和类型：
      - 只修改列的类型：alter table 表名 modify 列名修改之后的数据类型;
      - 同时修改列名和数据类型：alter table 表名 change 原列名新列名新数据类型;
    3. 删除列：alter table 表名 drop 被删除的列名;
- D(Delete)：删除
  - 删除表：drop table 表名如果表不存在会报错
  - 删除表，判断表存在才会删除：drop table if exists 表名

DML(Data Manipulation Language)数据操作语言

用来对数据库中表的数据进行增删改。

关键字：insert、delete、update等

添加数据
- 语法
  - 添加数据到某些对应的列：insert into 表名(列名1,列名2,...,列名N) values(值1,值,...,值N);
- 添加数据到每一列：insert into 表名 values(值1,值,...,值N); 此时必须为每一列赋值
  
  注意：
  1. 列名要和值一一对应
  2. 如果要给所有的列添加数据，可以省略表名后(列名...)的内容
删除数据
- 语法
  - 按条件删除：delete from 表名 where 条件;
  - 删除表中所有记录：delete from 表名;:warning:
    
    另一种方式：truncate 表名;或truncate table 表名;:warning:
  注意：
  1. :warning:如果不加where条件，则删除表中的所有记录
  2. :warning:如果要删除所有记录，不加where条件即可，相当于对每一行删除，效率低
    
    :warning:推荐使用truncate删除表，相当于直接删除原表，再创建一个一模一样的空表，效率高
修改数据
- 语法：
  - :warning:更新指定列名的所有位置数据：update 表名 set 列名1 = 值1,列名2 = 值2,...,列名N = 值N;
  - 按条件更新指定列名的数据：update 表名 set 列名1 = 值1,...列名N = 值 N where 条件;

DQL(Data Query Language)数据查询语言

用来查询数据库中表的记录（数据）。

关键字：select、where等

查询数据：
- 基础查询
  - 查询表中所有字段的记录：select * from 表名;
  - 查询需要的字段：select 字段1,字段2,...字段N from 表名;
  - 去除重复结果：select distinct 字段 from 表名
    
    注意：DISTINCT去重的依据是查询出来的字段那一行所有字段值完全相同，否则不认为是重复
  - 引入表达式，例如计算两个字段的和可以写成：select num1 + num2 from 表名;
    
    注意：如果num1或num2有一个为NULL，那么结果都为NULL
    
    解决方法：利用IFNULL(exp1,exp2)判空，如果exp1的值为NULL，则表达式的值为exp2
  - 给字段起别名：select 字段1 as 别名1,字段2 as 别名2,...,字段N as 别名N from 表名;
    
    注意：利用as起别名可以省略as，用空格代替即可
- 条件查询：select * from 表名 where 条件;
  - where字句后跟查询条件
  - 运算符
    - >、<、<=、>=、=、<>
      
      注意：在SQL中：
      1. 等于就是=，而不是Java中的==
      2. 不等于可以用!=表示，也可以用<>表示
    - between value1 and value2：选取满足value1到value2之间值的行，mysql中包括边界
    - in：简化对于同一个值的选取，例如where age=18 or age=20 or age=22;
      
      简化之后可以写作：where age in (18,20,22);
    - like：模糊查询
      - 占位符：
        
        _：单个任意字符
        
        %：0个或多个任意字符
    - is NULL：判断某字段的值为NULL不能直接用=号，要用字段值is NULL判断
    - and或&&：逻辑与
    - or或||：逻辑或，同一个字段的多次连续||推荐使用in(值的集合)
    - not或!：逻辑非
      
      not可应用于：
      1. 对between...and取反：not between...and，不介于…之间
      2. 对is NULL取反：is not NULL，某字段的值不为空
- 排序查询：select * from 表名 order by 排序字段1 排序方式1,排序字段2 排序方式2,...;
  - 排序方式：默认为ASC顺序排序，DESC逆序排序
    
    注意：如果有多个排序字段和方式，则只在满足第一个排序字段和方式产生相等结果时，按顺序按照后面的排序字段和方式排序。
- 聚合函数：将一列数据作为一个整体，进行纵向计算
  - count：计算个数（计数器）
  - max：计算最大值
  - min：计算最小值
  - sum：计算和
  - avg：计算平均值，对于包含NULL的字段，avg不会此字段计入平均值计算
    
    注意：聚合函数的计算会排除参数字段值为NULL的那一行，特别是count()
    
    解决方案：
    1. 尽量选择不含有NULL的列进行聚合函数的计算，例如主键
    2. 利用IFNULL(exp1,exp2)把可能含有NULL的字段作为exp1替换为exp2
- 分组查询：select * from 表名 group by 分组字段;
  
  注意：
  1. 分组查询之后的字段只能为：分组字段、聚合函数，不推荐为其他字段，包括*
  2. 对分组查询之后的结果进行条件判断，利用**having**子句
    - where 和 having 的区别：
      1. where 在分组之前进行限定，如果不满足where的条件，则不参与分组
      2. having在分组之后进行限定，如果不满足having后聚合函数的条件，则不显示
      3. where后不可以跟聚合函数，having可以进行聚合函数的判断
- 分页查询：select * from 表名 limit 开始的索引,要显示的条数;
  - 分页计算公式： 开始的索引 = (当前页码 - 1) * 每页的条数
  - 分页操作是一个方言，例如LIMIT只适用于MySQL数据库

DCL(Data Control Language)数据控制语言

用来定义数据库的访问权限和安全级别，及创建用户。DCL主要由DBA（数据库管理员）进行管理。

关键字：GRANT、REVOKE等

管理用户
- 添加用户：create user '用户名'@'主机名' identified by '密码';
- 删除用户：drop user '用户名'@'主机名';
- 修改用户密码：
  1. 修改表字段内容：update user set Password = password('新密码') where User = '用户名';
  2. DCL简化语法：set password for '用户名'@'主机名' = password('新密码');
  忘记了root密码？
  1. 管理员运行cmd：net stop mysql 停止mysql服务
  2. 使用无验证方式启动mysql服务：mysqld --skip-grant-tables 此时当前cmd会陷入阻塞
  3. 在另一个cmd直接输入mysql回车，登录进去修改user表的root用户密码，然后关掉所有cmd
  4. 在任务管理器杀掉mysqld的进程
  5. 管理员运行cmd：net start mysql 启动mysql服务
- 查询用户：
  1. 切换到mysql数据库：use mysql;
  2. 查询user表：select * from user;
    
    Host字段通配符：%表示可以在任意主机使用用户登录数据库（包含了localhost）
授权
- 查询权限：show grants for '用户名'@'主机名';
- 授予权限：grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
  - 授予所有权限到所有数据库的所有表：grant all on *.* to 用户名;
- 撤销权限：revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
建议刷新：flush privileges;

约束

作用：对表中的数据进行限定，保证数据的正确性、有效性和完整性。

约束的分类：

主键约束：primary Key

作用：非空且唯一，一张表只能有一个字段为主键，主键是表中记录的唯一标识
- 添加主键：
  1. 创建表时指定主键约束：id int primary key
  2. 修改表字段属性时添加主键约束：alter table 表名 modify 字段名数据类型 primary key;
- 删除主键约束：alter table 表名 drop primary key，因为主键只有一个，所以不需要指定字段
  
  注意：使用alter table 表名 modify 字段的方式不能删除掉主键约束
  
  主键约束使得一列的字段值不能相同且非空，相当于unique和not null
- 主键自动增长：
  1. 创建表时对主键指定自动增长：id int primary key auto_increment
  2. 修改表字段时添加自动增长：alter table 表名 modify 字段名数据类型 auto_increment;
    
    注意：
    1. 只有当自动增长的字段值赋值为0或null时，会触发自动增长，产生自增数值并插入
    2. 后续才添加自动增长时，必须保证自动增长字段没有值为1，否则添加自动增长不成功
    3. :warning:后续才添加自动增长会导致自动增长字段中为0的变成1，除非手动配置ini文件
    4. 在发生外键值不存在时，自动增长值依然会+1，但主键冲突不会导致+1
  3. 删除自动增长：alter table 表名 modify 字段属性值;，可以省略主键设置，因为主键不会被modify移除
非空约束：not null
- 添加非空约束：
  1. 创建表时指定非空约束：直接在字段的数据类型后指定约束：name varchar(20) not null
  2. 修改表字段属性时添加非空约束：alter table 表名 modify 字段名数据类型 not null;
- 删除非空约束：alter table 表名 modify 字段名数据类型;此时没有给该字段指定约束
唯一约束：unique
- 添加唯一约束：
  1. 创建表时指定唯一约束：直接在字段的数据类型后指定约束：phone varchar(20) unique
- 删除唯一约束：alter table 表名 drop index 要删除唯一约束的字段名;
  
  注意：使用alter table 表名 modify 字段的方式不能删除掉唯一约束
  
  唯一约束使得一列的字段值不能相同，但不包括null（null可重复）

外键约束：foreign key

作用：让表与表之间事实的依赖关系由外键链接，从而保证数据的正确性

添加外键约束：

创建表时指定外键约束：

create table 表名(
	...,
	外键字段 外键属性,
    constraint 随便起个外键名称 foreign key (本表外键字段名称)
    references 外键连接的表名(外键连接的表的唯一约束列名称)
);

修改表字段属性时添加外键约束：

1
2
3

alter table 要添加外键的本表名
add constraint 随便起个外键名称 foreign key (本表外键字段名称) 
references 外键链接的表名(外键连接的表的唯一约束列名称);

注意：

对于本表中作为其他表外键的约束字段，如果不考虑外键操作会导致操作失败，例如删除一个作为其他表中外键的本表字段会删除失败，因为其他表外键依赖了本表这个字段
对于本表中被其他表外键约束的字段，如果本表的值不存在于这个外键对应的字段的值，则操作失败，只能为外键对应的表中被约束的字段值之一，但是外键字段值可以为NULL

删除外键：alter table 表名 drop foreign key 要删除的外键名称;

级联操作：使得修改外键对应的表的字段值，可以自动影响本表外键字段

级联更新：

在创建表时指定外键的级联更新：

create table 表名(
	...,
	外键字段 外键属性,
    constraint 随便起个外键名称 foreign key (本表外键字段名称)
    references 外键连接的表名(外键连接的表的唯一约束列名称)
    on update cascade
);

修改表字段属性时添加外键的级联更新：

alter table 要添加外键的本表名
add constraint 随便起个外键名称 foreign key (本表外键字段名称) 
references 外键链接的表名(外键连接的表的唯一约束列名称)
on update cascade;

级联删除：

在创建表时指定外键的级联删除：

create table 表名(
	...,
	外键字段 外键属性,
    constraint 随便起个外键名称 foreign key (本表外键字段名称)
    references 外键连接的表名(外键连接的表的唯一约束列名称)
    on delete cascade
);

修改表字段属性时添加外键的级联删除：

alter table 要添加外键的本表名
add constraint 随便起个外键名称 foreign key (本表外键字段名称) 
references 外键链接的表名(外键连接的表的唯一约束列名称)
on delete cascade;

数据库的设计

多表之间的关系

分类：
- 一对一：例如：人和身份证号：一个人只有一个身份证号，一个身份证号只对应一个人
- 一对多：例如：部门和员工：一个部门含有多个员工，一个员工只属于一个部门
- 多对多：例如：学生和课程：一个学生可以选修多个课程，一个课程可以被多个学生选择
实现关系：
- 一对一：在任意一方添加外键（这个外键必须是unique的），指向另一方的主键。多在一张表实现
- 一对多：在多的一方建立外键，指向一的一方的主键
- 多对多：需要借助第三方中间表，至少包含两个字段作为外键（联合主键），分别指向两张表的主键
案例：一条路线的分类只有一种，一个用户可以选择多条路线，一个路线也可以被多个用户选择。

范式

概念：设计数据库时需要遵循的一些规范。遵循越高的范式要求遵循较低的所有范式
- 来自百度百科：
  
  设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
分类：
- 第一范式（1NF）：每一列都是不可分割的原子数据项
  
  可能存在的问题：
  1. 存在非常严重的数据冗余（重复）
  2. 添加数据存在问题
  3. 删除数据存在问题
- 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖，可以解决数据冗余问题）
  
  概念：
  - 函数依赖：A–>B，通过A属性（属性组）的属性值可以确定唯一B属性的值，则B依赖于A
    1. A属性的值确定B属性的值：例如学号A可以确定唯一的学生姓名B，则学生姓名B依赖于学号A
    2. A属性组的值确定B属性值：例如学号和课程为一组A，可以确定成绩B，则成绩B依赖于属性组A
  - 完全函数依赖：A–>B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有属性值
  - 部分函数依赖：A–>B，如果A是一个属性组，则B属性值的确定只需要依赖A属性组中某些属性值
  - 传递函数依赖：A–>B，B–>C，如果通过A属性（属性组）的值可以确定唯一B属性的值，再通过B属性（属性组）的值可以确定唯一C属性的值，则称C传递函数依赖于A。例如学号可以确定系名，系名又可以确定系主任，则系主任是传递依赖于学号的
  - 码：在一张表中，如果一个属性或属性组被其他所有属性完全依赖，则这个属性或属性组为该表的码
    
    该表中，学号并不能确定唯一的分数，只有（学号+课程）可以确定唯一的分数，此时的码为属性组
    - 主属性：码属性组中的所有属性
    - 非主属性：除去码属性组的属性
- 第三范式（3NF）：在2NF的基础上，任何非码属性不依赖于其他非主属性（在2NF基础上消除传递依赖）

数据库的备份与还原

命令行：
- 备份：mysqldump -u用户名 -p密码要备份的数据库名称 > 要保存到的路径
- 还原：
  1. 登录数据库mysql -u用户名 -p密码
  2. 创建数据库create database 数据库名称
  3. 使用数据库use 数据库名称
  4. 执行文件：source 保存的文件路径
图形化工具：
- DataGrip：
  1. 数据库列表右键需要备份的数据库
  2. 选择：
  3. 配置mysqldump的路径：
  4. 配置输出位置：

多表查询

基本语法：select 字段列表 from 表名列表 where 条件...;，直接查询的结果为笛卡尔积
- 笛卡尔积：全称笛卡尔乘积，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。也就是两个集合的所有组成情况。要完成多表查询需要消除无用数据。

多表查询的分类：

内连接查询：

内连接只查询出符合条件的数据

基本思路：

从哪些表中查询数据
条件是什么
查询哪些字段

隐式内连接：select 字段列表 from 表名1,表名2 where 条件;使用where条件消除无用数据

例如：1. 查询所有员工信息和对应的部门名称：

select * from emp,dept where emp.dept_id = dept.id; -- 隐式内连接

2. 查询员工表所有的员工名称、性别和对应部门表中的部门名称：
显式内连接：select 字段列表 from 表名1 inner join 表名2 on 条件;其中inner可省略

例如：查询所有员工信息和对应的部门名称：

select * from emp inner join dept on emp.dept_id = dept.id

外连接查询：

外连接可以查询某张表的数据，包括不满足条件的

左外连接：select 字段列表 from 表1 left outer join 表2 on 条件;其中outer可省略
- 查询到的结果是所有的左表（表1）记录和交集内容
右外连接：select 字段列表 from 表1 right outer join 表2 on 条件;其中outer可省略
- 查询到的结果是所有的右表（表2）记录和交集内容

子查询：

概念：查询中嵌套查询，称嵌套查询为子查询
子查询不同情况：
- 子查询的结果是单行单列：子查询可以作为条件，使用运算符去判断
  - 运算符：>、<、>=、<=、=
- 子查询的结果是多行单列：子查询可以作为条件，使用运算符in
- 子查询的结果是多行多列：子查询可以作为一张虚拟表进行表的查询

事务

事务的基本介绍

概念：如果一个包含多个步骤的业务操作被事务管理，那么这些操作要么同时成功，要么同时失败
操作：
- 开启事务：start transaction;
  - 开启事务后的DML语句不会立即更新结果到数据库上，除非commit
- 回滚：rollback;
- 提交：commit;
MySQL数据库中事务默认自动提交（Oracle数据库默认手动提交事务）
- 一条DML语句会自动提交一次事务
- 事务提交的两种方式：
  - 自动提交：DML语句
  - 手动提交：需要手动先开启事务，然后手动提交事务
- 查看事务的默认提交方式：select @@autocommit;结果为0表示手动提交，为1表示自动提交
- 修改事务的默认提交方式：set @@autocommit = 0;修改为手动提交

事务的四大特征ACID

原子性(Atomicity)：是不可分割的最小操作单位，要么同时成功，要么同时失败
一致性(Consistency)：事务操作前后数据总量不变
隔离性(Isolation)：多个事务之间相互独立
持久性(Durability)：当事务提交或回滚后，数据库会持久化的保存数据

事务的隔离级别

概念：多个事务之间是隔离的、相互独立的。但是如果多个事务操作同一批数据，就会引发一些问题，通过设置不同的隔离级别就可以解决这些问题
存在的问题：
1. 脏读：一个事务读取到另一个事务中没有提交的数据
2. 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样
3. 幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改，好像产生了幻觉一样
隔离级别：
1. read uncommitted：读未提交
  
  产生的问题：脏读、不可重复读、幻读
2. read committed：读已提交（Oracle默认）
  
  产生的问题：不可重复读、幻读
3. repeatable read：可重复读（MySQL默认）
  
  产生的问题：幻读
4. serializable：串行化
  
  可以解决所有的问题
  
  注意：从小到大安全性越来越高，但是效率越来越低
查询数据库的隔离级别：select @@tx_isolation;
设置数据库的隔离级别：set global transaction isolation level 级别字符串;
- 级别字符串：上述4种隔离级别的枚举。

JDBC

概念

JDBC是 Java DataBase Connectivity （ Java数据库连接）的简写，即利用Java操作数据库

JDBC的本质：官方(SUN公司)定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现了这些接口，提供数据库驱动jar包，我们可以使用这套接口（JDBC）编程，真正执行的是驱动jar包中的实现类

快速入门

步骤：
1. 导入驱动jar包
  - 把驱动jar包复制到项目里
  - 添加为库
2. 注册驱动：Class.forName("com.mysql.jdbc.Driver");
3. 获取数据库连接对象Connection getConnection(String url,String user,String password)
  1
  Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "199988"); //指定URL，用户名，密码
4. 定义sql：String sql = "update account set money = 200 where id = 2";
  - IDEA连接数据库可能需要指定时区：
    
    在URL后面加上?serverTimezone=GMT%2B8
5. 获取执行sql语句的Statement对象：Statement createStatement()
  1
  Statement statement = conn.createStatement();
6. 执行sql，接收返回的结果
  1
  int count = statement.executeUpdate(sql); //返回执行的语句的行数
7. 处理结果
8. 释放资源：关闭Statement对象和Connection对象

详解各个对象

DriverManager驱动管理对象

功能：
1. 注册驱动：告诉程序该使用哪一个驱动jar包
  1
  static synchronized void registerDriver(java.sql.Driver driver)
  注册给定的驱动程序，写代码使用：Class.forName("com.mysql.jdbc.Driver");
  
  实际上com.mysql.jdbc.Driver里含有一个静态代码块：
  1
  2
  3
  4
  5
  6
  7
  static {
  try {
  DriverManager.registerDriver(new Driver());
  } catch (SQLException var1) {
  throw new RuntimeException("Can't register driver!");
  }
  }
  在MySQL5之后的驱动jar包的META-INF/services含有一个java.sql.Driver文件，会自动注册驱动
2. 获取数据库连接：
  1
  public static Connection getConnection(String url,String user, String password)
  - 参数：
    - url：指定连接的路径（jdbc:mysql://ip:端口/数据库名称）
      
      例如：jdbc:mysql://ip地址(域名):端口号/数据库名称
      
      例如：jdbc:mysql://localhost:3306/db3
      
      如果连接的是本机的mysql，并且默认端口是3306，那么可以不写ip:端口
      
      例如jdbc:mysql:///db3
    - user：用户名
    - password：密码

Connection数据库连接对象

功能：
1. 获取执行sql的对象
  - Statement createStatement()：获取一个可以执行SQL的Statement对象
  - PreparedStatement prepareStatement(String sql)
2. 管理事务
  - 开启事务：void setAutoCommit(boolean autoCommit)设置为false则开启事务
  - 提交事务：void commit()
  - 回滚事务：void rollback()

Statement执行SQL的对象

功能：用于执行静态 SQL 语句并返回它产生的结果的对象。
1. boolean execute(String sql)执行给定的 SQL 语句，该语句可能返回多个结果。在某些（不常见）情况下，单个 SQL 语句可能返回多个结果集和/或更新计数。
  
  返回值：如果第一个结果是ResultSet对象，则为true ；如果是更新计数或没有结果，则为false
2. int executeUpdate(String sql)执行给定的 SQL 语句，它可以是INSERT 、 UPDATE或DELETE语句或不返回任何内容的DDL 语句。
  
  返回值：(1) SQL 数据操作语言 (DML) 语句受影响的行数 (2) 对于不返回任何内容的 SQL 语句返回0
3. ResultSet executeQuery(String sql)执行给定的 SQL 语句，该语句返回单个ResultSet对象
  
  返回值：一个ResultSet对象，包含给定查询生成的数据；从不为null

ResultSET结果集对象

功能：封装了查询的结果
1. boolean next()将光标从当前位置向前移动一行。 ResultSet游标最初位于第一行之前；第一次调用next方法使第一行成为当前行；第二次调用使第二行成为当前行，依此类推。
  
  返回值：如果新的当前行有效，则为true ；如果没有更多行，则为false
2. xxx getXxx(参数)获取数据，其中Xxx是数据类型，例如getInt()、getString()
  
  参数：
  - int columnIndex：根据列的编号获取，从1开始
  - String columnLabel：根据列名称获取，是AS子句指定的列名。如果未指定，则标签是列的名称
使用步骤：
1. 游标向下移动一行
2. 判断是否有数据
3. 获取数据
抽取JDBC工具类
- 目的：简化书写
- 分析：
  1. 注册驱动
  2. 抽取一个方法获取连接对象：不传递参数，还要保证工具类的通用性，通过配置文件解决
  3. 抽取一个方法释放资源

PreparedStatement

功能：表示预编译 SQL 语句的对象。SQL 语句被预编译并存储在PreparedStatement对象中。然后可以使用此对象多次有效地执行此语句。
解决SQL注入问题：在拼接SQL语句时，有一些SQL特殊关键词参与字符串的拼接会造成安全性问题

对于：

如果password值为

则整条SQL语句为：select * from user where username = 'ur' AND password = 'a' or 'a' = 'a'

此时最后一个表达式为 false or true，结果为true，造成了SQL注入
使用步骤：
1. 导入驱动jar包
2. 注册驱动
3. 获取数据库连接对象Connection
4. 定义SQL
  - 此时SQL语句的参数使用?作为占位符
    
    例如：select * from user where username = ? AND password = ?;
5. 获取执行SQL的对象PreparedStatement prepareStatement(String sql)
6. 给?赋值
  - 方法：void setXxx(int parameterIndex,Xxx x)
    
    参数：
    1. parameterIndex：第几个?问好
    2. x：Xxx类型的值
7. 执行SQL，不需要再次传递SQL语句，直接调用executeQuery()
8. 处理结果
9. 释放资源
注意：建议都使用PreparedStatement来完成CRUD操作，好处：
- 可以防止SQL注入
- 效率更高

JDBC控制事务

事务：一个包含多个步骤的业务操作，如果这个业务操作被事务管理，那么这些步骤要么同时成功，要么同时失败。
操作：
- 开启事务：void setAutoCommit(boolean autoCommit)设置为false则开启事务
- 提交事务：void commit()
- 回滚事务：void rollback()
使用Connection对象管理事务
1. 在执行SQL之前开启事务
2. 在所有SQL执行完毕后提交事务
3. 如果产生异常，就在异常处理程序中回滚事务

数据库连接池

概念：一个存放数据库连接的容器（集合），当系统初始化好之后，容器被创建，容器中会申请一些连接对象，当用户申请访问数据库时，从容器中获取连接对象，当用户访问完之后，把连接归还给连接池。
好处：
1. 节约资源
2. 访问高效
实现：
1. 标准接口：
  - 方法：
    1. 获取连接：Connection getConnection()
    2. 归还连接：如果连接对象Connection是从连接池获取的，那么调用Connection.close()方法，不会再关闭连接，而是归还连接
2. DataSource接口由驱动程序供应商实现，一般有：

`C3P0`：数据库连接池技术

步骤：
1. 导入jar包：c3p0-0.9.5.2.jar和mchange-commons-java-0.2.12.jar。前提是要导入mysql驱动包
2. 定义配置文件：
  - 名称：c3p0.properties或c3p0-config.xml
    
    在xml配置文件中可以定义多套配置：
    1. 默认配置在<default-config>中
    2. 其他自定义配置在<named-config name="配置名称">中
  - 路径：直接将文件放在src目录下即可
3. 创建核心对象：数据库连接池对象ComboPooledDataSource
  
  无参构造会使用配置文件的<default-config>：
  
  需要使用指定<named-config name="配置名称">利用重载的ComboPooledDataSource(String conf)
4. 获取连接对象：Connection getConnection()

`Druid`：数据库连接池实现技术，由阿里巴巴提供

步骤：
1. 导入jar包：druid-1.1.21.jar
2. 定义配置文件：
  - properties形式的
  - 可以叫任意名称，可以放在任意目录下
3. 加载配置文件：利用Properties对象的load()
4. 获取数据库连接池对象：通过工厂类：DruidDataSourceFactory来获取
  
  方法：DataSource createDataSource(Properties properties)传入Properties对象
5. 获取连接对象：Connection getConnection()
定义工具类：
- 定义一个DruidUtils
- 提供静态代码块加载配置文件并且初始化连接池对象
- 提供方法
  1. 获取连接的方法：提高数据库连接池获取连接
  2. 释放资源
  3. 获取连接池的方法

Spring JDBC：JDBC Template

Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象来简化JDBC的开发

基本步骤

导入jar包
创建JdbcTemplate对象：public JdbcTemplate(DataSource ds)该构造方法需要一个数据源对象
1
JdbcTemplate template = new JdbcTemplate(ds);
调用JdbcTemplate对象的方法来完成CRUD的操作
- update()：执行DML语句：增、删、改
- queryForMap()：查询结果，将结果集封装为Map集合，返回Map<String,Object>，只能查询一行
- queryForList()：查询结果，将结果集封装为List集合，返回List<Map<String, Object>>
- query()：查询结果，将结果集封装为JavaBean对象
  
  注意：query()方法的参数比较特殊，
  1. 一般来说第一个参数为String sql的SQL语句
  2. 第二个参数为RowMapper<T> rowMapper一个RowMapper<T>接口，我们可以：
    - 传递一个接口的匿名类，直接：
    - 使用lambda表达式简化上面的代码：
    - 传递一个RowMapper<T>接口的实现类：
      
      例如：new BeanPropertyRowMapper<T>(Class<T> mappedClass)
      
      构造方法参数为查询的JavaBean的Class对象，即T.class
- queryForObject()：查询结果，将结果封装为对象，一般用于聚合函数的查询
  
  方法：T queryForObject(String sql,Class<T> requiredType)
  
  参数：
  1. String sql：SQL语句，一般是聚合函数查询
  2. Class<T> requiredType：查询出来的类型的Class对象，一般为Long.class

练习：

需求：操作db3数据库的emp表
1. 修改1号记录的salary为8888
2. 添加一条记录
3. 删除刚才添加的记录
4. 查询id为1的记录，将其封装为Map集合
5. 查询所有的记录，将其封装为List集合
6. 查询所有的记录，将其封装为Emp对象的List集合
7. 查询总的记录数

Redis

概念

Redis是一款高性能的NOSQL系列的非关系型数据库
- 它是用C语言开发的一个高性能键值对（key-value）数据库，目前Redis支持的键值数据类型如下：
  - 字符串类型：string
  - 哈西类型：hash
  - 列表类型：list
  - 集合类型：set
  - 有序集合类型：sortedset
- Redis的应用场景：
  - 缓存（数据查询，短连接，新闻内容，商品内容等）
  - 聊天室的在线好友列表
  - 任务队列（秒杀，抢购，12306等）
  - 应用排行榜
  - 网站访问统计
  - 数据过期处理
  - 分布式集群架构中的session分离

NOSQL：

NOSQL是Not Only SQL（不仅仅是SQL）的缩写，是一项全新的数据库理念，泛指非关系型数据库。NOSQL数据库的产生是为了解决大规模数据集合多重数据种类带来的挑战，尤其是大数据应用难题。
- NOSQL和关系型数据库的比较：
  - 优点：
    1. 成本：NOSQL数据库简单易部署，基本都是开源软件，不需要像使用Oracle那样花费大量成本购买使用，相比关系型数据库价格便宜。
    2. 查询速度：NOSQL数据库将数据存储在缓存之中，关系型数据库将数据存储在硬盘之中，NOSQL查询速度远高于关系型数据库。
    3. 存储数据的格式：NOSQL的存储格式是key,value的形式、文档形式、图片形式等，所以可以存储基本数据类型以及对象或者集合等多种格式，而关系型数据库只支持基础类型。
    4. 扩展性：关系型数据库有类似于join这样的多表查询机制的限制导致扩展很艰难。
  - 缺点：
    1. 维护的工具和资料有限，因为NOSQL是属于新的技术，不如关系型数据库的技术成熟
    2. 不提供对SQL的支持，没有SQL这样的工业标准，将会导致一定的学习和使用成本
    3. 不提供关系型数据库对事务的处理
- NOSQL数据库的优势：
  - 性能NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高
  - 可扩展性同样也是基于键值对，数据之间没有耦合性，所以非常容易水平扩展
- 关系型数据库的优势：
  - 复杂查询可以用SQL语句方便的在一个表和多个表之间做非常复杂的数据查询
  - 事务支持使得对于安全性能很高的数据访问要求得以实现。
- 主流的NOSQL产品：
  1. 键值对（key,value）存储数据库：
    - 相关产品：Tokyo Cabinet/Tyrant、Redis、Voldemort、Berkeley DB
    - 典型应用：内容缓存，主要用于处理大量数据的高负载访问
    - 数据模型：一系列键值对
    - 优势：快速查询
    - 劣势：存储的数据缺少结构化
  2. 列存储数据库
    - 相关产品：Cassandra、HBase、Riak
    - 典型应用：分布式的文件系统
    - 数据模型：以列簇式的存储，将同一列数据存储在一起
    - 优势：查找速度快，可扩展性强，更容易进行分布式扩展
    - 劣势：功能相对局限
  3. 文档型数据库
    - 相关产品：CouchDB、MongoDB
    - 典型应用：Web应用（与key,value类似，value是结构化的）
    - 数据模型：一系列键值对
    - 优势：数据结构要求不严格
    - 劣势：查询性能不高，而且缺乏统一的查询语法
  4. 图形（Graph）数据库
    - 相关产品：Neo4J、InfoGrid、Infinite Graph
    - 典型应用：社交网络
    - 数据模型：图结构
    - 优势：利用图结构相关算法
    - 劣势：需要对整个图做计算才能得出结果，不容易做分布式的集群方案

下载安装

官网：https://redis.io
中文网：https://www.redis.net.cn
下载解压之后可以直接使用：
- redis.windows.conf：配置文件
- redis-cli.exe：客户端，显示中文乱码时指定--raw方式打开：redis-cli.exe --raw
- redis-server.exe：服务器端

Redis的数据结构

Redis存储的是：key,value格式的数据，其中key都是字符串，而value有5种：
- 字符串类型：string，最基本的类型，是二进制安全的，string可以存储任意类型数据，每个最大512M
- 哈西类型：hash，类似于Map，一个键值对集合，每个hash可以存储2³²-1个键值对，大约42亿
- 列表类型：list，类似于linkedlist，允许重复的字符串列表，每个list最多可包含2³²-1个元素，大约42亿
- 集合类型：set，类似于HashSet，不允许重复的无序字符串集合，通过hash表实现，最大成员数2³²-1个
- 有序集合类型：sortedset，不允许重复的有序字符串集合，通过hash表实现，最大成员数2³²-1个

命令操作

操作字符串string类型

存储：set key value，存储一个value到key里
获取：get key，通过此key获取对应的value
删除：del key，删除此key对应的键值对

操作哈西hash类型

存储：hset key field value，存储一个field,value的Map到对应的key里
获取：hget key field，通过key获取对应的field的value
- 获取key中所有的键和值：hgetall key
删除：hdel key field，删除此key对应的field的键值对
- 删除此hash容器：del key，删除此key对应的hash容器
小知识：HSET只能设置单个键值对，同时设置多个请使用HMSET，但是Redis4.0.0开始HMSET已弃用

操作列表list类型

添加：lpush key value，添加一个key对应的value到列表左边
- rpush key value，添加一个key对应的value到列表右边
获取：lrange key start end，获取索引下标start到end的在key对应的元素，从左到右
- start表示起始位置下标
- end表示结束位置下标
- 下标可以为正数（从左到右以0开始的下标），也可以为负数（从右到左以-1表示最后一位）
- end下标必须在下标逻辑上大于start下标，否则会导致返回empty list or set
删除：lpop key，删除key对应的列表最左边的一个元素并返回被删除的元素
- rpop key，删除key对应的列表最右边的一个元素并返回被删除的元素

操作集合set类型

存储：sadd key value，添加一个value到key对应的set集合中
获取：smembers key，获取set集合中所有的元素
删除：srem key value，删除key对应的set集合中的value值

操作有序集合zset类型

存储：zadd key score value，添加一个value以及其排序依据score到key对应的集合中
获取：zrange key start end，获取索引下标为start到end的key对应的zset集合中的元素
- 可选的withscores：在获取元素的同时展示这些元素的score
删除：zrem key value，删除key对应的zset集合中的value元素

通用命令：

获取所有的键：keys *
- *可以为正则表达式
获取此key对应的value的类型：type key
删除此key对应的key-value键值对：del key
:warning:清空Redis服务器数据：flushall
:warning:清空当前库的所有键值对：flushdb

一个坑：

描述：windows控制台默认GBK编码，通过Jedis写入的数据如果是UTF-8可能导致控制台查询乱码
解决方法：除了redis-cli.exe --raw以中文显示汉字之外，还需要先设置控制台编码：chcp 65001

持久化操作

Redis是一个内存数据库，当Redis服务器重启后，或者电脑重启后，内存数据会丢失，所以需要将Redis的数据持久化保存到硬盘的文件中
Redis持久化机制：
- RDB：默认方式，不需要进行配置，在一定的间隔时间中，检测key的变化，然后持久化保存数据
  - 设置间隔时间和key的变化检测：
    1. 编辑配置文件：redis.windows.conf，设置save 秒数变化数
    2. 重新启动redis服务器，并且指定配置文件名称：redis-server.exe redis.windows.conf
- AOF：日志记录的方式，可以记录每一条命令的操作，在每一次命令操作后持久化数据
  - 指定是否在每次操作后进行日志记录：appendonly
    1. 编辑配置文件：redis.windows.conf，设置appendonly为yes开启日志记录，默认为no
  - 指定更新日志的条件：appendfsync
    1. 编辑配置文件：redis.windows.conf，设置appendfsync
      - no：等操作系统进行数据缓存同步到磁盘（快）
      - always：每次更新操作后手动调用fsync()将数据写到磁盘（慢，安全）
      - everysec：每秒同步一次（折中，默认值）

Jedis

概念：

一款Java操作Redis数据库的工具

使用步骤：

下载Jedis的jar包：jedis-2.7.0.jar和Apache的commons-pool2-2.3.jar
使用
- 创建一个Jedis对象：Jedis jedis = new Jedis()
  
  对于此构造方法，重载形式的Jedis(String host,int port)参数传递主机名和端口号，本机可省略
- 调用相应的方法，与命令行操作的方法名一致
- 使用完成后释放资源：jedis.close()

Jedis连接池

使用：
1. 创建JedisPool连接池对象
2. 调用方法getResource()获取一个Jedis连接
3. 调用close()方法归还连接到连接池

案例

使用Redis优化此案例：
- 提供一个index页面，页面中有一个省份的下拉列表
- 当页面加载完成，自动发送AJAX请求到服务器查询数据库中所有的省份列表
  
  注意：如果查询的数据经常变化，要记得更新缓存
  - 例如在增删改数据库时删除掉Redis缓存，这样下次再次查询时就会发现没有此key对应的缓存数据，这时查询数据更新Redis缓存，可以保证缓存是更新之后的数据

优化前：

优化后：