MySQL数据库 JDBC

一，数据库

1.1 概念

数据库就是存储数据的仓库

数据库可以实现数据的持久化存储

数据库的本质是文件系统

1.2 DBMS

数据库管理系统：Database Management System

常见的DBMS：

1. mysql：Oracle公司的中小型数据库
2. oracle：Oracle公司的大型数据库
3. db2：IBM公司的大型数据库
4. sqlserver：Microsoft公司的大型数据库
5. sqlite：移动端的嵌入式的小型数据库

注：

1. java项目中一般使用数据库 mysql、oracle
2. 以上数据库都是关系型数据库

1.3 数据库的结构

一个数据库管理系统中可以管理维护着多个数据库

一个数据库中包含若干张表

一张表中包含若干条记录

一条记录中包含若干个字段

表->实体类

记录->对象

字段->属性

1.4 mysql的基本操作

登录：

方式1：在cmd中输入mysql -uroot -p密码

方式2：在cmd中输入mysql -uroot -p，再输入密码

方式3：打开安装完mysql的commamd line client，输入密码

退出：

方式1：输入exit

方式2：输入quit

方式3：按ctrl+c

1.5 sql语言

结构化查询语言：Structured Query Language

分类：

1. DDL：数据定于语言，对库、表进行创建、修改、删除等

   关键字：create、drop、alter等

2. DML：数据操作语言，对表中记录进行增删改

   关键字：insert、delete、update等

3. DQL：数据查询语言，对表中数据进行查询的

   关键字：select、where、having、group by、order by、asc、desc、like、in、limit、if、not、null、exists、left、right、inner、outer、join、distinct、between、and

4. TCL：事务控制语言，对事务进行操作的

   关键字：commit、rollback、session、global

1.5.1 库的操作

-- 查看有哪些数据库
show databases;
-- 创建数据库
create database 库名;
-- 先判断数据库是否存在，不存在才创建
create database if not exists 库名;
-- 查看数据库的编码格式
show create database 库名;
-- 修改编码格式
alter database 库名 character set 编码格式;
-- 创建数据库的同时指定编码格式
create database 库名 character set 编码格式;
-- 先判断数据库是否存在，不存在才创建，创建的同时指定编码格式
create database if not exists 库名 character set 编码格式;
-- 删除数据库
drop database 库名;
-- 先判断数据库是否存在，存在才删除
drop database if exists 库名;
-- 指定使用的数据库
use 库名;

1.5.2 表的操作

-- 查看所有表
show tables;
-- 删除表
drop table 表名;
-- 先判断表是否存在，存在才删除
drop table if exists 表名;
-- 创建表
create table 表名(字段名 字段类型,字段名 字段类型,...);
-- 先判断表是否存在，不存在才创建
create table if not exists 表名(字段名 字段类型,字段名 字段类型,...);
-- 查看表结构
desc 表名;
-- 根据指定旧表的结构创建一个结构相同的新表 (复制表结构)
create table 新表名 like 旧表名;
-- 修改表名
alter table 旧表名 rename to 新表名;

-- 修改表结构
-- 1.添加新字段
alter table 表名 add 新字段 字段类型;
-- 2.删除字段
alter table 表名 drop 字段名;
-- 3.修改字段类型
alter table 表名 modify 字段名 字段类型;
-- 4.修改字段名
alter table 表名 change 旧字段名 新字段名 字段类型;

1.5.3 记录和字段的操作

-- 查询表中所有记录的所有字段
select * from 表名;

添加记录：

-- 添加一条记录，向表中所有字段添加数据
insert into 表名 values(值1,值2,值3,...);
-- 添加一条记录，向表中部分字段添加数据
insert into 表名 (字段名1,字段名2,字段名3,...)values(值1,值2,值3,...);
-- 批量添加记录（所有字段）
insert into 表名 values(值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...),...;
-- 批量添加记录（部分字段）
insert into 表名 (字段名1,字段名2,...)values(值1,值2,...),(值1,值2,...),..;

删除记录：

-- 删除表中所有记录
delete from 表名;
-- 根据条件删除记录
delete from 表名 where 条件;

修改记录：

-- 修改表中所有记录的指定字段
update 表名 set 字段名1 = 值,字段名2 = 值,...;
-- 根据条件修改表中记录的指定字段
update 表名 set 字段名1 = 值,字段名2 = 值,... where 条件;

1.6 查询操作

1.6.1 基础查询

-- 查询所有记录的所有字段，*通配符表示所有列
select * from 表名;
-- 查询所有记录的指定字段
select 字段名1,字段名2,... from 表名;
-- 去重查询
select distinct 字段名 from 表名;
-- 根据指定条件查询记录
select 字段名1,字段名2,... from 表名 where 条件;

1.6.2 条件查询

关系运算

>、<、>=、<=、=、!=、<>不等于

逻辑运算

&&、||、!
and、or、not

指定范围内

between ... and ...
注：含头含尾

指定列表中

in(值1,值2,值3,...)

模糊查询

like "模糊查询的内容"

占位符：

1. %：任意多个任意字符
2. _：单个任意字符

1.6.3 排序查询

order by 字段名 排序方式

排序方式：

1. 升序：asc，默认排序方式
2. 降序：desc

多个字段的排序，只有在前一个字段值相同时，才会根据后一个字段再进行排序

order by 字段名1 排序方式, 字段名2 排序方式

1.6.4 聚合函数

将一列作为一个整体，然后进行纵向的计算

-- 1.求最大值
max()
-- 2.求最小值
min()
-- 3.求平均值
avg()
-- 4.求总和
sum()
-- 5.求个数，一般使用非空字段或者*，也可以使用数字
count()

注：

1. 聚合函数不能作为where后的条件
2. 聚合函数不计算null值

1.6.5 分组查询

group by 字段名 -- 表示以指定的字段进行分组

注：

1. 分组后查询的字段只能是：分组字段、聚合函数

2. 分组查询后要对分组后查询到的数据再进行条件的筛选需要使用having

   where和having的区别：

   1. where用在分组前的条件筛选，满足where条件的进入分组，不满足where条件的不进入分组；having用在分组后的条件筛选，满足having条件的能被查到，不满足having条件的不被查到
   2. where后不能使用聚合函数；having后可以使用聚合函数

1.6.6 分页查询

limit 开始索引，每页查询的记录数

注：开始索引 = (页码 - 1) * 记录数

1.6.7 查询语句的结构

select 
	字段列表
from 
	表名
where 
	分组前的条件
group by
	分组字段
having 
	分组后的条件
order by
	排序字段 排序方式
limit 
	开始索引, 记录数

1.7 约束

对表中字段的限定

分类：

1. 非空约束：not null
2. 唯一约束：unique
3. 主键约束：primary key
4. 外键约束：foreign key

1.7.1 非空约束

限定了字段值不能为空

-- 1.建表时，添加非空约束
create table 表名(字段名 字段类型 not null, 字段名 字段类型, ...);
-- 2.建表后，添加非空约束
alter table 表名 modify 字段名 字段类型 not null;

注：如果记录中的某个字段已经是null了，那么该字段不能再添加非空约束

1.7.2 唯一约束

限定了字段值不能重复

-- 1.建表时，添加唯一约束
create table 表名(字段名 字段类型 unique, 字段名 字段类型, ...);
-- 2.建表后，添加唯一约束
alter table 表名 modify 字段名 字段类型 unique;

注：

1. 如果某个字段已经存在了重复值，那么该字段不能再添加唯一约束
2. 一个字段可以同时被设置成非空且唯一的，非空和唯一的关键字的先后顺序任意

1.7.3 主键约束

特点：

1. 非空且唯一
2. 一张表中只能有一个主键字段

-- 1.建表时，添加主键约束
create table 表名(字段名 字段类型 primary key, 字段名 字段类型, ...);
-- 2.建表后，添加主键约束
alter table 表名 modify 字段名 字段类型 primary key;

主键自增长：

-- 1.建表时，添加主键自增长
create table 表名(字段名 字段类型 primary key auto_increment, 字段名 字段类型, ...);

注：

1. 只有主键才能自增长
2. 主键必须是数值类型的才能自增长
3. 在添加时，主键字段赋值null时，主键会自增长
4. 第一条自增长的字段值是1，每次自增1
5. 从存在过的最大值开始自增

1.7.4 外键约束

外键约束的作用是让表与表之间产生联系，为了保证数据的完整性

注：

1. 多表时，一的一方称为主表，多的一方称为从表
2. 从表的外键类型必须与主表主键的类型一致

-- 一对多的关系中创建从表
create table 表名(字段名 字段类型, 字段名 字段类型, ... , foreign key(外键字段) references 主表名(主表的主键字段));
-- 多对多，需要创建中间表，中间表中至少包含两个字段，分别指向两张主表的主键
create table 表名(
	字段1 字段类型, 字段2 字段类型, ... ,
    primary key(字段1,字段2),
    foreign key(字段1) references 主表1(主表1的主键),
    foreign key(字段2) references 主表2(主表2的主键)
);

1.8 连接查询

也称多表查询，当查询的字段来自于多张表时，就要使用连接查询

1.8.1 交叉查询

select 字段列表 from 表1, 表2,...;
select * from category c,product p;

注：交叉查询的结果是笛卡尔积，表1有n条记录，表2有m条记录，查询的结果就有n * m条记录；这样的查询结果会有许多无效数据，因此需要加上有效的连接条件来去除无效记录

1.8.2 内连接(sql92语法，隐式内连接)

等值连接

连接条件是：主表的主键与从表的外键相同

select 字段列表 from 表1, 表2,... where 连接条件;

-- 查询所有商品以及分类
select * from category c,product p
where c.id = p.category_id;
-- 查询所有价格超过10块的商品以及它的分类
select * from category c,product p
where c.id = p.category_id and p.price > 10;
-- 查询每种分类的平均价格
select avg(price),c.name from category c,product p
where c.id = p.category_id group by c.name;
-- 查询平均价格超过低于20块的分类
select avg(price),c.name from category c,product p
where c.id = p.category_id group by c.name having avg(price) < 20;
-- 查询平均价格超过低于20块的分类，并按平均价格降序排列
select avg(price),c.name from category c,product p
where c.id = p.category_id group by c.name having avg(price) < 20
order by  avg(price) desc;

非等值连接

-- 查询每个员工的工资级别
select e.name,j.level from job j,employee2 e
where e.salary between j.min_salary and j.max_salary;

自连接

-- 查询员工名以及他的上级的名字
select e2.last_name 员工名,e1.last_name 上级名 
from employees e1,employees e2
where e1.employee_id = e2.manager_id;

1.8.3 内连接(sql99语法，显式内连接)

等值连接

连接条件是：主表的主键与从表的外键相同

select 字段列表 from 表1, 表2,... where 连接条件; -- 92语法
select 字段列表 from 表1 inner join 表2 on 连接条件; -- 99语法

-- 查询所有商品以及分类
select * from category c inner join product p
on c.id = p.category_id;
-- 查询所有价格超过10块的商品以及它的分类
select * from category c inner join product p
on c.id = p.category_id
where p.price > 10
-- 查询每种分类的平均价格
select c.name,avg(p.price) from category c inner join product p
on c.id = p.category_id group by c.name; 
-- 查询平均价格超过低于20块的分类
select c.name,avg(p.price) a from category c inner join product p
on c.id = p.category_id group by c.name having a < 20;
-- 查询平均价格超过低于20块的分类，并按平均价格降序排列
select c.name,avg(p.price) 平均价格 from category c inner join product p
on c.id = p.category_id group by c.name having 平均价格 < 20 order by 平均价格 desc

非等值连接

-- 查询每个员工的工资级别
select e.name,j.level from employee2 e inner join job j
on e.salary between j.min_salary and j.max_salary;

自连接

-- 查询员工名以及他的上级的名字
select e1.last_name 员工, e2.last_name 上级 from employees e1 inner join employees e2
on e1.manager_id = e2.employee_id;

注：inner可以省略

1.8.4 外连接

-- 左外连接
select 字段列表 from 表1 left outer join 表2 on 连接条件;
-- 右外连接
select 字段列表 from 表2 right outer join 表2 on 连接条件;

注：

1. 内连接查询的是多张表的交集
2. 左外连接查询的是左表的全部和两表的交集
3. 右外连接查询的是右表的全部和两表的交集

-- 查询所有分类的商品个数
select c.name,count(p.id) from category c left outer join product p
on c.id = p.category_id group by c.name;
-- 查询每个学生选课的数量
select t2.sname,count(t1.sid) from tb_student_course t1 right outer join student t2
on t1.sid = t2.sid group by t2.sname;

注：outer可以省略

1.9 子查询

一条查询语句的结果在另一条查询语句中，子查询也称为内查询

外部的查询语句称为主查询或者外查询

1.9.1 子查询出现在where/having后

注：

1. 如果子查询 查询的结果是单行单列要配合单行运算符：>、<、>=、<=、=、!=、<>
2. 如果子查询 查询的结果是多行单列要配合多行运算符：in
3. 子查询的执行都是优先于主查询的
4. 子查询必须在小括号内

-- 查询年纪比周瑜大的人
-- 1.查询周瑜的年纪
select sage from student where sname = "周瑜"
-- 2.查询年纪比指定年纪大的
select * from student 
where sage > (select sage from student where sname = "周瑜");

-- 查询部门编号在60~100的员工名及其部门名
select e.last_name,e.department_id,d.department_name from employees e inner join departments d 
on e.department_id = d.department_id
where e.department_id between 60 and 90;

-- 查询位置编号是1700或者2500的部门中的所有员工
-- 1.查询位置编号是1700或者2500的部门
select department_id from departments where location_id = 1700 or location_id = 2500
-- 2.查询哪些员工在查到的这些部门中
select last_name from employees where department_id in (
select department_id from departments where location_id = 1700 or location_id = 2500)

-- 查询1班和2班学生选择的课程
select * from course where cid in (
select distinct cid from tb_student_course where sid in (
select sid from student where sclass = 1 or sclass = 2))

1.9.2 子查询出现在from后

-- 1.查询商品价格最高的商品所属分类
select t.name,t.price,c.name from category c inner join 
(select * from product where price = (
	select max(price) from product
)) t 
on c.id = t.category_id;

二，JDBC

2.1 概念

JDBC：Java Database Connectivity，Java数据库连接，JDBC让我们可以通过Java代码访问数据库

JDBC的本质：它是官方推出的一套专门用于操作关系型数据库的规则（接口）。各个不同数据库厂商都要去实现这些接口，并提供相应的jar包。jar包是Java工程打包成的压缩包。

2.2 使用步骤

1. 导入jar包：mysql-connector-java-5.1.37-bin.jar

   1. 在项目的根目录下创建lib文件夹
   2. 将jar包复制到该目录下
   3. 右键此jar包，点击 Add as Library

2. 注册驱动

   Class.forName("com.mysql.jdbc.Driver");
   

   注：从mysql5开始，注册驱动的语句可以省略，因为在jar包的配置文件中已经配置好了

3. 获取连接对象

   目的是为了明确后续的操作是针对哪个数据库的

   Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hr","root","1234");
   

   参数：

   1. 数据库的地址：jdbc:mysql://ip地址:端口号/库名

      注：如果ip地址是本机并且端口号是默认的3306，

      那么地址可以简化成：jdbc:mysql:///库名

   2. 数据库管理员的用户名

   3. 数据库管理员的密码

4. 编写SQL语句

5. 获取执行对象

   PreparedStatement ps = conn.prepareStatement(sql);
   

6. 为占位符添加数据

   ps.setInt(1,222);
   ps.setString(2,"张三丰");
   ps.setInt(3,33);
   ps.setString(4,"男");
   ps.setDouble(5,88.8);
   // setXxx(第几个占位符,值)
   

7. 执行sql，并接收返回的结果

   ps.executeUpdate()：// 用于增删改，返回受影响的记录数
   ps.executeQuery()：// 用于查询，返回查询到的ResultSet结果集
   

8. 处理返回的结果

9. 释放资源

   ps.close();
   conn.close();
   

   注：如果执行的是查询，那么查询返回的结果集也要释放

ResultSet结果集：封装了查询后的结果

方法：

1. next()：判断是否有下一条可以遍历的记录，返回布尔值

2. getXxx(int columnIndex)：通过查询结果的字段序号来获取值，序号从1开始

3. getXxx(String columnLabel)：通过查询结果的字段名来获取值

2.3 数据库连接池

数据库连接池的本质就是一个容器，用于存放数据库的连接对象

好处：

1. 节省了内存的开销
2. 提高了程序的性能

在javax.sql包中提供了一个数据库连接池的接口：DataSource，

它提供了一个功能：

1. getConnection()：从连接池中获取一个连接对象

如果连接对象是从连接池中获取的，那么此时连接对象的close()功能就不再是释放资源了，而是将它归还到连接池中

2.3.1 C3P0

使用步骤：

1. 导入jar包：c3p0-0.9.5.5.jar、mchange-commons-java-0.2.15.jar

2. 编写配置文件

   1. 在项目的src目录下新建c3p0-config.xml
   2. 在该文件中配置驱动包、数据库的地址、用户名、密码等信息

   <?xml version="1.0" encoding="utf-8" ?>
   <c3p0-config>
       <named-config name="myc3p0">
           <!-- 驱动包 -->
           <property name="driverClass">com.mysql.jdbc.Driver</property>
           <!-- 数据库的地址 -->
           <property name="jdbcUrl">jdbc:mysql:///day0422</property>
           <!-- 管理员的用户名 -->
           <property name="user">root</property>
           <!-- 管理员的密码 -->
           <property name="password">1234</property>
           <!-- 初始化连接对象的数量 -->
           <property name="initialPoolSize">3</property>
           <!-- 最大连接对象的数量 -->
           <property name="maxPoolSize">5</property>
           <!-- 超时时间 -->
           <property name="checkoutTimeout">3000</property>
       </named-config>
   
       <default-config>
           <!-- 驱动包 -->
           <property name="driverClass">com.mysql.jdbc.Driver</property>
           <!-- 数据库的地址 -->
           <property name="jdbcUrl">jdbc:mysql:///day0422</property>
           <!-- 管理员的用户名 -->
           <property name="user">root</property>
           <!-- 管理员的密码 -->
           <property name="password">1234</property>
           <!-- 初始化连接对象的数量 -->
           <property name="initialPoolSize">3</property>
           <!-- 最大连接对象的数量 -->
           <property name="maxPoolSize">5</property>
           <!-- 超时时间 -->
           <property name="checkoutTimeout">3000</property>
       </default-config>
   </c3p0-config>
   

3. 创建C3P0的核心类对象：ComboPooledDataSource

   //1.使用default-config配置来创建连接池
   DataSource ds = new ComboPooledDataSource();
   //2.使用指定名字的named-config配置来创建连接池
   DataSource ds = new ComboPooledDataSource("myc3p0");
   // 注：如果指定名字的配置找不到，就使用默认配置
   

4. 获取连接池中的连接对象

   Connection conn = ds.getConnection();
   

2.3.2 Druid

使用步骤：

1. 导入jar包：druid-1.0.9.jar

2. 编写配置文件

   1. 在src目录下创建druid.properties
   2. 在该文件中配置驱动包、数据库的地址、用户名、密码等信息

   driverClassName=com.mysql.jdbc.Driver
   url=jdbc:mysql:///day0422
   username=root
   password=1234
   initialSize=3
   maxActice=5
   maxWait=3000
   

3. 使用数据库连接池的工厂类中的静态方法createDataSource来创建数据库连接池

2.4 DBUtils

DBUtils是一个专门针对数据库以及数据库连接池提供简化操作的工具

使用步骤：

1. 导入jar包：commons-dbutils-1.4.jar

2. 准备连接池

3. 创建DBUtils的核心类对象QueryRunner

   //1.使用以下无参的构造函数创建QueryRunner，由于没有传入数据库连接池，因此在后续的数据库操作时必须手动传入连接对象 
   QueryRunner qr = new QueryRunner();
   //2.使用以下参数是数据库连接池的构造函数来创建QueryRunner，在后续的数据库操作时不需要再传入连接对象，因为QueryRunner会在传入的数据库连接池中自动获取连接对象
   QueryRunner qr = new QueryRunner(DateSource ds);
   

4. 调用QueryRunner的方法执行功能

   qr.update();// 用于执行增删改
   qr.query();// 用于执行查询
   

执行查询功能时，需要传入一个接口ResultSetHandler结果集处理者，用于处理结果集

实现类：

1. BeanListHandler：将查询到的每条记录封装成指定类的对象，并将查询到的字段值赋值给该对象的属性，再将这些对象存储到List集合中
2. BeanHandler：将查询到的记录封装成指定类的对象，并将查询到的字段值赋值给该对象的属性
3. ScalarHandler：查询聚合函数

public static void f1() throws Exception{
    QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
    String sql = "select user_id id,user_username username,user_password password from user";
    List<User> users = qr.query(sql,new BeanListHandler<User>(User.class));
    for(User u : users){
        System.out.println(u);
    }
}

public static void f2() throws Exception{
    QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
    String sql = "select user_id id,user_username username,user_password password from user where user_username = ? and user_password = ?";
    User users = qr.query(sql,new BeanHandler<User>(User.class),"zhangsan","666");
    System.out.println(users);
}

public static void f3() throws Exception{
    QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
    String sql = "select count(*) from user";
    Object o = qr.query(sql,new ScalarHandler());
    System.out.println(o);
}

使用BeanListHandler和BeanHandler的原理：

1. 它们都是通过指定类的无参构造函数来创建对象
2. 它们都是通过指定类中的set方法将记录的字段值赋值给对象的属性值，处理者会拿着查询出的字段值在指定类中查找是否存在一个方法，将这个方法的set去掉，将剩下内容的首字母变成小写与该字段值所属的字段名是相同的，如果有这样的方法就调用该方法为对象的指定属性进行赋值