MySQL

Mysql
数据库
1.什么是数据库
数据库（DataBase，DB）：指长期保存在计算机的存储设备(硬盘)上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合. 还是以文件的方式存在服务器的电脑上的。
说白了就是数据的仓库, 用来持久化保存数据的.

2.常见的关系型数据库

• MySql:开源免费的数据库，中小型的数据库，已经被Oracle收购了。MySql6.x版本也开始收费。后来Sun公司收购了MySql，而Sun公司又被Oracle收购
• Oracle：收费的大型数据库.Oracle公司的产品.Oracle收购SUN公司，收购MySql.
  DB2：IBM公司的数据库产品,收费的.银行系统中.
• SQLServer：MS公司.收费的中型的数据库.
• SyBase：已经淡出历史舞台.提供了一个非常专业数据建模的工具PowerDesigner.
• SQLite: 嵌入式的小型数据库,应用在手机端.

3.数据库结构

• 一般情况下,一个系统(软件,项目) 就设计一个数据库; eg: jd项目, 设计一个jd的数据库
• 一个数据库里面有多(>=1)张表. 一个实体(java类)对应一张表
• 一张表里面有多条(>=1)记录, 一个对象对应一条记录

SQL
1.概述

• SQL：Structure Query Language。（结构化查询语言）,通过sql操作数据库(操作数据库,操作表,操作数据)
• SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准
• 各数据库厂商(MySql,oracle,sql server)都支持ISO的SQL标准。
• 各数据库厂商在标准的基础上做了自己的扩展。 各个数据库自己特定的语法

2.sql的语法
每条语句以分号结尾(命令行里面需要)，如果在navicat,sqlyog,java代码中不是必须加的。
SQL在window中不区分大小写，关键字中认为大写和小写是一样的

3.sql的分类

• Data Definition Language (DDL数据定义语言) 如：操作数据库，操作表
• Data Manipulation Language(DML数据操纵语言)，如：对表中的记录操作增删改
• Data Query Language(DQL 数据查询语言)，如：对表中的数据查询操作
• Data Control Language(DCL 数据控制语言)，如：对用户权限的设置

DDL
1.创建数据库:默认字符集为utf8
Create database 数据库名 [character set 字符集] [collate 校验规则]

2.查看数据库
Show databases
Show create database 数据库名

3.删除数据库
Drop database 数据库名

4.修改数据库
Alter database 数据库名 character set 字符集

5.数据库的其它操作
Use 数据库名 选中数据库
Select database() 查看当前选中的数据库

DML
1.创建表的语法介绍

• 增 create table 表名( 字段名 字段类型 约束, … 字段名 字段类型 约束 )
• 删 drop table 表名 ,Alter table 表名 drop 字段名
• 改
  Alter table 表名 add 字段名 字段类型 约束
  Alter table 表名 modify 字段名 字段类型 约束
  Alter table 表名 change 旧字段名 新字段名 字段类型 约束
  Rename table旧表名 to 新表名
• 查 show tables,Desc 表名

2.MySql常见的类型

注意：

• float(M,D) eg: float(4,2) 表达的范围: -99.99~99.99
• char(n) eg: char(20), 固定长度，最大能存放20个字符. ‘aaa’, 还是占20个字符的空间
• varchar(n) eg:varchar(20), 可变长度，最大能存放20个字符. ‘aaa’, 占3个字符的空间
• 一般使用varchar(n) 节省空间; 如果长度(eg:身份证)是固定的话 可以使用char(n) 性能高一点

3.MySql约束

• Primary key 主键
• Unique唯一
• Not null 非空

注意:

• 先设置了primary key 才能设置auto_increment
• 只有当设置了auto_increment 才可以插入null 自己维护 否则插入null会报错
• id列:
• 给id设置为int类型, 添加主键约束, 自动增长
• 或者给id设置为字符串类型,添加主键约束, 不能设置自动增长

4.操作表记录

• 增：insert into 表名（字段名,字段名…) values(字段值,字段值…)
• 删：delete from 表名 where 条件,Truncate table 表名
• 改: update 表名 set 字段名 = 字段值,… where 条件

注意:
1.delete 和truncate区别

• DELETE 删除表中的数据，表结构还在; 删除后的数据可以找回,一条一条的删除.
• TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

2.工作里面的删除

• 物理删除: 真正的删除了, 数据不在, 使用delete就属于物理删除
• 逻辑删除: 没有真正的删除, 数据还在. 搞一个标记, 其实逻辑删除是更新 eg: state 1 启用 0禁用

DQL
1.单表查询
select [*] [列名 ,列名] [列名 as 别名 …] [distinct 字段] from 表名 [where 条件]
注意: distinct前面不能有字段名,别名前的as可以省略

2.条件查询
select … from 表 where 条件

• like 模糊查询 一般和_或者%一起使用
• _ 占一位
• % 占0或者n位

3.排序查询
SELECT 字段名 FROM 表名 [WHERE 条件] ORDER BY 字段名 [ASC|DESC]; //ASC: 升序，默认值; DESC: 降序

4.聚合函数
SELECT 聚合函数(列名) FROM 表名 [where 条件];
聚合函数类型/作用

• max(列名):求这一列的最大值
• min(列名) :求这一列的最小值
• avg(列名) :求这一列的平均值
• count(列名) :统计这一列有多少条记录
• sum(列名):对这一列求总和

注意:我们发现对于NULL的记录不会统计，建议如果统计个数则不要使用有可能为null的列，但如果需要把NULL也统计进去呢？我们可以通过 IFNULL(列名，默认值) 函数来解决这个问题. 如果列不为空，返回这列的值。如果为NULL，则返回默认值。

5.分组查询:
SELECT 字段1,字段2… FROM 表名 [where 条件] GROUP BY 列 [HAVING 条件];
注意:

• 根据某一列进行分组, 将分组字段结果中相同内容作为一组; 有几组 返回的记录就有几条
• 单独分组 没有意义, 返回每一组的第一条记录
• 分组的目的一般为了做统计使用, 所以经常和聚合函数一起使用
• 在分组里面, 如果select后面的列没有出现在group by后面 展示这个组的这个列的第一个数据

where和having的区别:
1)where 字句

• 对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组。
• where后面不可以使用聚合函数

2)having字句

• having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。
• having后面可以使用聚合函数

例子:
SELECT sex, count() FROM student GROUP BY sex HAVING count() > 5
– 练习复杂: 统计sid为8之前的的, 根据性别分组, 每一组学生的总人数 > 2的(分组后筛选)
select sex,count() from student where sid < 8 group by sex having count() > 2

6.分页查询
select … from … limit a ,b.
a:从哪里开始查询, 从0开始计数 【a=(当前页码-1)*b】
b: 一页查询的数量【固定的,自定义的】

查询语句总结:
Select * from 表名 where 条件 group by 字段 having 条件 order by 字段 asc/desc limit…

多表操作
1.拆表原因:

• 有些情况下,使用一张表表示数据 数据不好维护, 存在数据冗余,比较乱的现象
• 使用多张表,需要对数据进行约束,不约束添加的数据也会不合法

2.外键约束:
保证引用的完整性,维护多表间的关系

• 外键: 一张从表中的某个字段引用主表中的主键
• 主表： 约束别人
• 副表/从表： 使用别人的数据，被别人约束

1)新建表时增加外键：
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
关键字解释：
CONSTRAINT – 约束关键字
FOREIGN KEY(外键字段名) –- 某个字段作为外键
REFERENCES – 主表名(主键字段名) 表示参照主表中的某个字段

2)已有表增加外键：
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

3)删除外键:
ALTER TABLE 表 drop foreign key 外键名称;

4)什么是级联操作：
在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作
ON UPDATE CASCADE – 级联更新，主键发生更新时，外键也会更新
ON DELETE CASCADE – 级联删除，主键发生删除时，外键也会删除

3.多表间关系:

• 一对多:在从表(多方的一方)创建1一个字段,字段作为外键指向主表(一方)的主键
• 多对多:需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。
• 一对一:外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一UNIQUE;外键是主键：主表的主键和从表的主键，形成主外键关系

4.连接查询
1)交叉查询: 左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积

• select a.列,a.列,b.列,b.列 from a,b ;
• select a.,b. from a,b ;
• select * from a,b;

2)内连接查询

• 隐式内连接:不出现inner
• select [字段,字段,字段][*] from a,b where 连接条件 --(a表里面的主键 =
  b表里面的外键) 显式内连接:出现inner
• select [字段,字段,字段][*] from a [inner] join b on 连接条件 [ where 其它条件]

3)外连接查询

• 左外连接:以join左边的表为主表,展示主表的所有数据,根据条件查询连接右边表的数据,若满足条件则展示,若不满足则以null显示.
• select [字段][*] from a left [outer] join b on 条件
• 右外连接:以join右边的表为主表,展示右边表的所有数据,根据条件查询join左边表的数据,若满足则展示,若不满足则以null显示
• select 字段 from a right [outer] join b on 条件

4)子查询:一条语句里面包含了多个select

• 子查询的结果是一个值的时候,一般放在where后面作为条件, 通过=,>,<,<>
• SELECT 查询字段 FROM 表 WHERE 字段[= > < <>]（子查询）
• 子查询结果是单列多行的时候,一般放在where后面作为条件, 通过in
• SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）
• 子查询的结果是多行多列,一般放在from后面作为虚拟表, 需要给虚拟表取别名
• SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件

5.事物:
指逻辑上的一组操作，组成这组操作的单元要么全部成功，要么全部失败;作用:保证一组操作全部成功或者失败.
1)自动事务:一条sql语句就是一个事务
2)手动开启事务:

• start transaction;开启事务
• commit；提交
• rollback；回滚

3)回滚点:在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。
设置回滚点: savepoint 名字
回到回滚点: rollback to 名字
注意:

• 建议手动开启事务, 用一次 就开启一次
• 开启事务之后, 要么commit, 要么rollback
• 一旦commit或者rollback, 当前的事务就结束了
• 回滚到指定的回滚点, 但是这个时候事务没有结束的

4)事务特性ACDI:

• 原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性（Consistency）事务前后数据的完整性必须保持一致.
• 持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
• 隔离性（Isolation）事务的隔离性是指多个用户并发操作数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。
  简单来说: 事务之间互不干扰

5)隔离级别:隔离级别越高，性能(效率)越差，安全性越高.
级别/名字/隔离级别/脏读/不可重复读/幻读/数据库默认隔离级别
1 读未提交 read uncommitted 是 是 是
2 读已提交 read committed 否 是 是 Oracle
3 可重复读 repeatable read 否 否 是 MySQL
4 串行化 serializable 否 否 否

• 脏读:一个事务读到了另一个事务尚未提交的数据
• 不可重复读:一个事务中两次读取的数据内容不一致,要求是一个事务中两次读取的事务是一致的,这个是事务update引发的问题
• 幻读:一个事务中两次读取的数量不一致,要求是一个事务中两次读取的事务是一致的,这个是事务insert或delete时引发的问题

6.数据库设计三范式

• 1NF:原子性:表中每一列数据不可再拆分
• 2NF:不产生局部依赖,一张表只描述一件事情
• 3NF:不产生传递依赖,表中每一列都直接依赖于主键,而不是通过其他列间接依赖主键

7.常用函数：

1. if相关的函数(重要)
   if（exp1,exp2,exp3）:判断exp1,为true执行exp2,为false执行exp3
   ifnull(exp1,exp2):如果exp1不为null,返回exp1,为null返回exp2

2. 字符串相关的函数(了解)

   1. 字符串拼接:concat
   2. 大小写转换:upper/lower
   3. 去掉左右空格:trim
   4. 截取字符串:substring/substr

3. 日期时间相关的函数(了解)

   1. current_date():返回当前时间的年月日
   2. current_time():返回当前时间的时分秒
   3. now():返回当前时间的年月日时分秒

4. 数学相关的函数(了解)
   1.绝对值:abs
   2.向上取整:ceil
   3.向下取整:floor
   4.幂:pow
   5.随机数:rand

8.JDBC

1. JDBC的思想(接口的思想、解耦的思想):市面上会有很多种不同的数据库,为了在java中能够形成统一的规范,降低学习成本,在数据库和java之间建立一个通用的接口

2. 使用JDBC执行SQL语句的步骤:(只要掌握变化的就可以了

   1. 注册驱动: Class.forName(“com.mysql.jdbc.Driver”); 掌握为什么要使用这种方式注册驱动

   2. 获得连接对象: DriverManager.getConnection(url,username,password);
      url 数据库的连接地址: jdbc:mysql:///数据库名
      username 数据库的用户名
      password 数据库密码

   3. 创建Statement对象，该对象可以执行SQL语句
      Statement stm = conn.createStatement();

   4. 使用statement对象执行SQL语句

      1. executeUpdate(sql);执行增删改的语句，返回值是int类型，表示受到影响的行数
      2. executeQuery(sql);执行查询的SQL语句，返回值是ResultSet，查询到的结果集

   5. 如果有结果集，则遍历结果集，使用while循环遍历

   6. 关闭资源，后创建的先关闭

3. 使用POJO对象封装查询到的结果

   1. 如果查询到的是一行数据，则封装到一个POJO对象中
   2. 如果查询到的是多行数据，那么每行数据封装到一个POJO对象中，所有的POJO对象存储到List集合中

4. POJO对象的编写规范:

   1. 成员变量私有
   2. 给所有的私有成员变量提供公有的get和set方法
   3. 必须有无参构造
   4. 建议重写toString方法，便于打印
   5. 属性如果是基本类型，声明成为对应的包装类型
   6. 建议实现Serializable接口

5. JDBC工具类的封装

   1. 静态代码块中注册驱动

   2. getConnection()方法获取连接对象，返回值是Connection对象

   3. close()方法关闭资源，需要关闭的资源就作为方法的参数传入
      方法重载:

      1. 俩参数的Statement stm,Connection conn
      2. 仨参数的ResultSet rst,Statement stm,Connection conn

   4. 使用配置文件解决硬编码问题(主要要去理解什么是硬编码问题)

6. 登录案例

   1. 提示用户输入用户名
   2. 获取用户输入的用户名
   3. 提示用户输入密码
   4. 获取用户输入的密码
   5. 通过执行SQL语句，校验用户名和密码
   6. 输出登录结果

   注意点: 有SQL注入问题:使用字符串拼接的过程中,加入了sql的语法

7. preparedStatement

   1. 作用:预编译SQL语句，提升SQL语句的执行效率，可以防止发生SQL注入问题
   2. 使用步骤:
      1. 编写SQL语句，如果要传入参数则用?占位符代替
      2. 调用connection对象的prepareStatement(sql);进行预编译
      3. 如果SQL语句中有?占位符，则需要给?占位符处设置参数
      4. 调用preparedStatement的方法执行SQL语句
         1. executeUpdate()执行增删改的SQL语句
         2. executeQuery()执行查询的SQL语句

8. 使用preparedStatement 优化登录案例，防止SQL注入

9. JDBC操作事务
   前提: 这组逻辑操作使用的是同一个connection

   1. 开启事务
      1. 什么时候: 那一组逻辑操作开始之前
      2. 怎么开启事务: connection.setAutoCommit(false)
   2. 提交事务
      1. 什么时候提交: 执行完这组逻辑操作，没有出现异常
      2. 怎么提交事务: connection.commit()
   3. 回滚事务
      1. 什么时候回滚: 执行这组逻辑操作的时候，出现异常，则在catch代码块中回滚
      2. 怎么回滚事务: connection.rollback()

9.连接池&DBUtils

连接池的原理

1. 为什么要使用连接池
   1.1 频繁创建和销毁连接，会带来很大的系统开销
   1.2 避免高并发的情况下，要一次性创建过多连接可能导致内存溢出的问题

2. 连接池的原理看图
   

C3P0

1. 配置文件的名字和路径都是固定的
2. 配置文件中的内容，不要自己去写，直接修改已有的内容
3. 封装一个C3P0的工具类，该工具类中有一个静态方法，可以获取C3P0连接池对象

Druid

1. 根据配置文件创建Druid连接池对象
2. 从Druid连接池对象获得Connection

DBUtils使用步骤

1.引入jar包

2.创建QueryRunner对象，并且将DataSource对象传入到构造函数

3.调用QueryRunner对象的方法执行SQL语句

DBUtils框架中的API

1.构造函数，一定要传入一个DataSource类型的参数

2.update(sql,params...),执行增删改的SQL语句

3.query(sql,resulSetHandler,params...),执行查询的SQL语句

ResultSetHandler接口的实现类

1.ScalaHandler，处理单个数据的结果集，返回值用简单类型接收

2.BeanHandler，处理一条数据封装到POJO对象的结果集，返回值就是POJO类型

3.BeanListHandler,处理多条数据封装到List<POJO>对象的结果集，返回值就是List<POJO>

4.MapHandler，处理一条数据封装到Map对象的结果集，返回值是Map

5.MapListHandler，处理多条数据封装到List<Map>对象的结果集，返回值是List<Map>

自定义DBUtils

1.元数据

1. DataBaseMetaData:包含数据库的相关信息，比如url、驱动类名等等

2. ParameterMetaData:包含了SQL语句的参数相关的信息，比如说参数的个数

3. ResultSetMetaData:包含了结果集相关的信息，比如说:结果集的列数、每列的列名

4. 自定义增删改的方法

5. 指定查询的方法