mysql数据库以及JDBC

数据库

数据库即数据的仓库。在数据库中提供了专门的管理系统，对数据库中的数据进行集中的控制和管理。能高效的对数据进行存储、检索。

优势：

1.降低存储数据的冗余度

2.更高的数据的一致性

3.存储数据可以共享

4.建立数据库所遵循的标准

5.便于维护数据的完整性

6.能够实现数据的安全性

关系型数据库

关系模型把世界看作是由实体（Entity）和联系（Relationship）组成的。所谓实体就是指在现实世界中客观存在并可相互区别的事物。实体所具有的某一特性称为属性（Attribute）。

关系模型数据库是一种以表作为实体，以主键和外键关系作为联系的一种数据库结构。

在关系型数据库中，相类似的实体被存入表中。表（table）是关系型数据库的核心单元，它是数据存储的地方。

主键

在关系型数据库表中，用一个唯一的标识符来标识每一行，这个标识符就是主键（Primary Key）。

主键有两个特点：不可以重复、不能为空。

外键

在关系型数据库中，外键（Foreign Key）是用来表达表和表之间关联关系的列。

表之间的关系

1. 一对一关系
2. 一对多关系
3. 多对多关系

关系型数据库管理系统

关系型数据库只是一个保存数据的容器，大多数数据库依靠一个称为数据库管理系统（Database Management System，简称DBMS）

数据库管理系统分类：

1.本地数据库管理系统

2.数据库服务器管理系统

结构化查询语言SQL

SQL是结构化查询语言（Structured Query Language）的英文缩写，是一种用于管理关系型数据库，并与数据库中的数据进行通讯的计算机语言。

SQL是一种用于数据库操作的语言，并且已经成为数据库管理的标准语言。

SQL与RDBMS协同工作，来定义数据库的结构、存储数据、操纵数据、获取数据、控制对数据的访问以及确保数据的完整性

SQL可以完成对数据库的所有操作。它已经成为完成数据库的一种规范。

SQL方言

SQL提供所有基本的数据操作，但是，不同的RDBMS之间存在一些差别，每个RDBMS实现标准的方法可能有细微的区别，不同的RDBMS可能会有一些特有的语法

SQL的非过程性质

SQL在本质上就是非过程式的，它主要关心操作的结果，不关心过程

SQL语言的分类

数据定义语言（DDL） 创建、修改、删除数据库的内部数据结构

数据查询语言（DQL） 用于数据库中数据的查询

数据操作语言 （DML）用于数据库中数据的修改，包括添加、删除、修改等

数据控制语言（DCL）控制数据库访问权限

常用数据类型

数值型 INT

浮点型 DECIMAL

字符串类型CHAR（n）定长字符串VARCHAR（n）变长字符串

日期时间类型DATE、DATETIME、TIMSTAMP

数据完整性

数据完整性的概念

可靠性+准确性=数据完整性

为什么要保证数据库完整性？

为了防止垃圾数据的产生，从而影响数据库的执行效率！

数据库完整性的分类：

1实体完整性 2.域完整性 3.引用完整性 4.自定义完整性

实体完整性

实体完整性的概念

保证每行所代表的实体能互相区别，不能存在两条一模一样的记录。

实体完整性的实现方式：

主键约束（Primary Key）

主键（Primary Key）是表中的一到多个列，主键列不能为空，也不能重复，一个表中只能有一个主键

设置主键约束

s_id int primary key

必须满足四个条件：

1. 值不能为空
2. 值必须唯一
3. 不能有业务含义
4. 值不能发生变动

设置唯一约束

设置唯一约束可以在创建表时指定

s_id int primary key

s_telephone varchar(50) unique

域完整性

概念：保证指定列的数据的有效性

实现方式：非空约束（Not null）

默认约束 default 指定默认约束

引用完整性

概念：从表外键中出现的数据，必须在主表的主键列中出现。

实现方式：外键约束

外键约束和外键：没有建议外键约束不等于没有外键，并不是完全相等的关系

DML

添加记录

以用insert into语句可以向表中添加记录：

insert into t_student(学号，姓名，性别)values（1,'张三','男'）

在下面几种情况下可以省略某些列：

1、列值有RDBMS自动创建，如自增长。

2、创建列时设定了默认值，如果不提供值，则由RDBMS自动创建。

3、如果列被设置为允许空值，那么不输入值不影响数据库的完整性

删除记录

利用delete语句，可以删除现有的行数据

delete from 表名 where 过滤条件

示例：删除员工张三的信息

delete from t_employee where em_name='张三'；

修改行记录信息

利用update语句，可以对现有的行数据进行更新

update 表名 set 列1=新值1，列2=新值2 where 过滤条件

示例：将张三电话修改为138888

update t_employee set phone='138888' where en_name='张三'

查询记录

利用select语句，可以查询现有的行数据

示例：查询所有的员工的姓名和电话

select employee_name,phone from t_employee

如果希望查询一个表中所有的列，可以用*代替查询列

select * from t_emloyee

查询

查询的基本结构

查询是数据库中最为常用和复杂的一种SQL结构，它也是数据库用最关注的部分，数据库的查询是使用select语句来实现查询的

select 语句的完整语法，可以有6个子句。完整的语法如下：

select 目标表的列名或列表达式集合

from 基本表或（和）视图集合

（where 条件表达式)

（group by 列名集合）

（having 组条件表达式）

（order by 列名[集合]）

投影操作

投影操作是查询语句里必须有的子句，关键字则为select，他将选择对表中那些列进行操作，这些列将出现子啊结果中。

select 列1，列2，列3，.....列N  from  表名

表前缀、列别名、表别名

表前缀：

select t_student.name from t_student

在列别名或表名用as关键字来提供别名：

select 列A as A，列B as B from 表名 as T

使用表别名

select T.列A as A,T.列B as B 表 as T

计算列

返回数据不存在于数据库中，但可以从数据构造或者计算得来

字符串链接用concat函数：

select concat(money,'美元')from t_employee

排除重复记录

标准语法：

select distinct 列名 from 表名

返回限定行的查询

标准语法：

select 列名 from 表名 limit 开始序列号，返回的行数

条件查询

单条件查询：

select 列名 from 表名 where 列=值 

多条件查询：

组合where条件：

and：并且

or：或者

select 列名 from 表 where 条件1（and 或者 or）条件2

定义集合关系

在查询操作中，可以定义集合关系（in 或 not in），在指定的某几个值中进行搜索。

标准结构：

select 列名 from 表 where 列名 in（值集合）

模糊查询

标准结构

 select 列名 from 表名 where 列名 like 模式

通配符：

“_” 匹配任何的单个字符

“%” 匹配零个或多个的任何字符

示例：

select * from t_student where s_name like '王_';
select * from t_student where s_name like '王%';

处理空值

示例：

select * from t_student where s_age is null;

排序操作

示例：

select * from t_student order by s_age desc;

多条用逗号隔开

当所有语句同时出现时，执行顺序是：

select 3

from 1

where 2

order by 4

limit 5

时间函数

now() 得到当前时间（年月日 时分秒）

curdate() 得到当前时间（年月日）

date_add(@dt,interval 100 day) 得到指定时间一百天后的日期

year(@dt) 得到指定时间的年份

timestampdiff(day,@dt1,@dt2) 得到两个日期之间的时间间隔

条件判断

单条条件判断示例：

select * if(score>=60,'合格','不合格')from t_student;

多条件判断示例：

select *,case when s_score>=60 and s_score<80 then '中'
  when s_score>=80 and s_score<90 then '良'
  when s_score>90 then '优'
  else '差'
  end 成绩
  from t_student

聚合函数

使用聚合函数进行统计汇总

count() 统计行的数量

sum() 获取单个列的合计值

avg() 获取单个列的平均值

max() 获取单个列的最大值

min() 获取单个列的最小值

count函数

select count(计算规范) from 表名

计算规范：

*：计算所有选择的行，包括空值

all 列名：技术指定列的所有的非空值行，如果仅仅是指定列而不带all或者distinct，这是默认操作

distinct 列名：计数指定列的唯一非空值行

sum函数

统计指定列非空值的总和

select sum(all 列名) from 表名；

统计指定列非空唯一值的总和

select sun(distinct 列名)from 表名

avg函数

统计指定列非空值的平均分

select avg(all 列名) from 表名

统计指定列非空唯一值的平均分

select avg(distinct 列名)from 表名

计算所有人的平均分

select sum(列名)/count(*) from 表名

max函数

返回最大值

select max(列名) from 表名

min函数

返回最小值

select min(列名) from 表名

分组查询

标准结构：

select 列名，聚合函数 from 表名 group by 列名

having过滤语句

select 列名，聚合函数 from 表名 
where 过滤条件 
group by 列名
having 过滤条件

where和having的区别：

他们都是过滤语句，不同的是where是在分组前运行的，不能过滤聚合函数

having是在分组后运行的，并只能用来过滤聚合函数

基本查询SQL的执行顺序

基本查询SQL的执行顺序为：

第一步：执行from

第二步：where条件过滤

第三步：group by分组

第四步：执行select投影列

第五步：having条件过滤

第六步：执行order by排序

第七步：执行limit语句，返回限定行

子查询

在一个查询语句中再嵌套一个查询语句，称为子查询。

子查询是嵌套再查询语句里面的查询语句，就像语句块里嵌套语句块类似

子查询是一个select语句。它可以嵌套在一个select语句、insert…into语句、delete语句、或update语句或嵌套在另一子查询中。

select中嵌入子查询

标准结构：

select 列1，列2，（子查询） as 列别名 from 表名

嵌套在select语句中SQL语句要求查询的值只能是单行和单列

from中嵌入子查询

标准结构：

select 列1，列2 from（子查询） as 别名

from里的子查询可以是任意查询语句，然后将其结果作为外部查询的表

select * from(select 学号，姓名 from t_student) s

from后面的子查询必须写别名

from后面的子查询可以返回多行和多列

where中嵌入子查询

标准结构：

select 列名 from 表名 where 列名=(子查询)

在where中嵌套的子查询根据不同的运算符有不同的费雷：

比较运算符(>、<、=、>=、<=、!=)

in和not in运算符

子查询运算符（all、any、exists）

where示例：只能返回单行单例

SELECT * FROM t_employee WHERE e_id 比较运算符(
SELECT e_id FROM t_employee WHERE e_employee_name='张三'
)AND e_employee_name!='张三';

in和not in示例：

SELECT * FROM t_employee WHERE e_id in(
SELECT e_id FROM t_employee WHERE e_employee_name='张三' or e_employee_name='李四'
)AND e_employee_name!='张三' AND e_employee_name!='李四';

all运算符

和子查询的结果逐一比较，必须全部满足时表达式的值才为真

示例：

select * from t_student where 分数>all(
select 分数 from t_student where 姓名='张三'
)

any运算符

和子查询的结果逐一比较，其中一条记录满足条件则表达式的值就为真。

示例：

select * from t_student where 分数>any(
select 分数 from t_student where 姓名='张三'
)

exists运算符

exists判断子查询是否存在数据，如果存在则表达式为真，反之为假，not exists 相反

示例：

select * from t_student s1 where exists(
select s_score from t_student s2
 where s_name='张三' and s2.s_score=s1.s_score
)and name!='张三'

exists和in两者是可以互换。

exists与in的使用效率问题。通常情况下采用exists要比in效率高。因为in不走索引，但要看实际情况具体使用

相关子查询

在主查询中，每查询一条记录，需要重新做一次子查询，这种称为相关子查询。相关子查询的执行，依赖于外部查询的数据，外部返回一行，子查询就执行一次

select enName,(select d_deptname from t_dept d where e.deptld =d.deptld) deptname from t_employee e;

非相关子查询

在主查询中，子查询只需要执行一次，子查询结果不会变化。子查询结果供主查询使用，这种查询方式称为非相关子查询

select * from t_studentScore where math=(
select max(math) from t_studentScore
)

组合查询

使用UNION运算符

UNION是一种联合两条或以上查询的运算符，类似多条查询结果相组合的的效果

标准结构：

select 列1，列2 from 表1
union
select 列3，列4 from 表2

UNION的结果集列名与UNION运算符中第一个色了CR语句的结果集中的列名相同。另一个select的结果集列名将被忽略。

如果组合查询的数据有完全一样的，则结果会去除掉重复的数据。UNION ALL 不会去重

联表查询

如果数据来自多个表，那么可以采用联接查询的方式来实现。

表联接就是值指将多个表联合在一起实现查询效果。

表联接采用的是笛卡尔乘积，称之为横向联接。

笛卡尔乘积是指将两张表的所有数据相连，最后联接的结果数为两张表数量的乘积。

标准结构：

select 列1，列2 from 表1 join 表2

笛卡尔乘积出来的结果数量太多，其中有不少数据是没用的，所以在表联接时就要根据其外键来过滤没用的数据。使用ON关键字来确定其是否匹配

select 列1，列2 from 表1 join 表2 on 表1.外键=表2.主键

两表联接时经常出现不同表有相同的列名，因此尽量使用别名来区分

表连接的分类：

内联接

外联接

自联接

内联接

内联接时从结果表中删除与其它被联接表中没有匹配行的所有行。所以内联接可能会丢失信息

内联接的标准语法时inner join，inner可以省略

示例：查询员工姓名，并显示该员工所在的部门名称

select e.emName,d.deotName from t_employee e join
t_dept d on e.deptid=d.id;

外联接

外连接是指不管有没有匹配，被定义了外连接的表数据都要出现在结果中。

外联接分为：

左外联接： left outer join 或left join

在left join左边的表就被定义为外联接，那么此表中所有的数据都会出现在查询结果中。

右外联接：right outer join或right join

在right join右边的表就被定义为外联接，那么此表中所有的数据都会出现在查询结果中。

自联接

自联接就是内联接或外联接的一种特例，同样使用join

示例：查询每位员工姓名及他们直属领导的名字

select e1.emName,e2.emName 领导名字 from 
t_employee e1 join t_employee e2 on e1.leaderId=e2.emId

视图

视图可以看作时SQL语句的封装，可以看作是临时表，视图查询的结果会随着真实表的变化而变化。视图只提供查询功能，不提供数据修改的功能。

创建视图：

create view 视图名 as 查询SQL语句

删除视图：

drop view 视图名

数据规范化

仅有好的RDBMS并不足以避免数据冗余，必须在数据库的设计中创建好的表结构。

在具有关系的实体之间，存在着函数依赖（插入异常，删除异常，修改时数据冗余）

Dr E.F.codd最初定义了规范化的三个级别，范式是具有最小冗余的表结构

第一范式

在所有范式中，第一范式是最重要的。它提供了建立其它范式的基础，并且代表了表的核心特性。

为遵从第一范式，一个表必须满足如下需求：

1、一个行的每个列必须是原子性的，即任何给定行的列只能包含一个值。

2、表中的每一行必须包含相同数量的列。假如每个列只能包含一个值，意味着每行必须包含相同数量的值。

3、一个表中的所有行必须是不同的。虽然行可能包含相同的值，但是每一行作为一个整体必须在表中是唯一的。

第二范式

第二范式在第一范式的基础上进行了扩展。

为遵从第二范式，一个表必须满足如下需求：

1、表必须符合第一范式

2、表中的所有非主键必须依赖于整个主键列。

第二范式要求每个表只描述一件事情（一个实体）。

第三范式

满足第二范式，并且除了主键以外的其它列都不互相依赖，则满足第三范式

不是必须的，一般情况不会遵循第三范式

总结：

范式一：

列不可再分（值必须唯一），行不可重复（定义主键）

范式二：

非主键列必须依赖于主键列（非主依主）

范式三：

非主键列之间必须互相独立（非主独立）

JDBC

JDBC全称是Java DataBase Connectivity，是java连接数据库的一套API

驱动包

JDBC是一种用于执行SQL语句的Java API，它由一组用Java语言编写的类和接口组成。通过这些类和接口，JDBC把SQL语句发送给不用类型的数据库进行处理并接收处理结果

JDBC规范可以让开发者，不论连接什么数据库都是一套API

驱动包是数据库厂商针对JDBC规范所写的实现类

连接不同数据库时，应该使用不同数据库厂商提供的驱动包

开发流程

JDBC主要完成数据库的磁盘数据和内存中对象数据的交互，本质时流操作

JDBC的开发流程 流的开发流程

建立连接 建立流

执行SQL语句 操作流

关闭连接 关闭流

JDBC常用API

DriverManager JDBC驱动程序管理器，它是JDBC体系结构的支柱，主要作用是把Java应用程序连接到JDBC驱动程序上，然后退出。

Connection 定义到数据库的连接，主要用于创建Statement、对象

Statement 执行SQL语句，通过相关方法执行静态的SQL！语句

PreparedStatement 预编译的SQL语句，结合参数可以大大提高JDBC的执行效率

CallableStatement 执行存储过程

ResultSet 接收某查询SQL查询语句所返回的结果及对象

创建连接

// 1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 创建连接
            Connection con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/j189?userSSL=true&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT",
                    "root","lovo");
            System.out.println("con:" + con);

封装增加

   public static void insert(String name, String password, Double money) {
        //创建连接
        Connection con = getConnection();
        Statement statement = null;
        try {
            //获取执行SQL语句
            statement = con.createStatement();
            String sql = "insert into t_user(u_name,u_password,u_money)values ('" + name + "','" + password + "'," + money + ")";
            //执行SQL语句
            int row = statement.executeUpdate(sql);
            System.out.println("影响的行数" + row);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭连接
            close(con, statement);
        }
    }

封装查询

   public List query() {
        Connection con = getConnection();
        Statement statement = null;
        ResultSet res = null;
        List personlist = new ArrayList();
        try {
            statement = con.createStatement();
            String str = "select * from t_user";
            res = statement.executeQuery(str);
            while (res.next()) {
                Person person = new Person();
                person.setId(res.getInt("u_name"));
                person.setName(res.getString("u_password"));
                person.setAge(res.getInt("u_money"));
                personlist.add(person);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close(con, statement, res);
        }
        return personlist;
    }

封装关闭连接

    public void close(Connection con, Statement statement, ResultSet res) {
        if (res != null) {
            try {
                res.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

数据库执行SQL语句的过程

第一步：编写SQL语句

String sql="select * from t_dept";

第二步：分析

语法分析、语义分析、选择连接方式、选择搜索路径······

将分析结果保存在数据库的SQL缓冲区

第三步：编译

数据库内部编译

第四步：运行

可能要锁定相应的资源或先访问缓冲区运行并返回结果。

执行SQL语句的种类

静态SQL

String sql="select * from depy=10"

​ 只编译一次，不可变化

动态SQL

String sql="select * from dept="+deptId(变量)

​ 编译多次，可变化

在开发中，需要更新的数据或需要查询的数据，都是用户通过应用程序进行录入的。所以基本上都使用动态SQL的方式，执行SQL语句

使用Statement对象的缺点

使用Statement对象时，只能以拼接字符串方式执行SQL语句

String sql="select * from dept"+deptId(变量)

当变量值发生变化时，会导致SQL语句的变化，在执行时会发生多次编译。

使用Statement对象时，效率较低

另外，使用Statement对象时，容易引起SQL注入

SQL注入

什么时SQL注入？示例：

String deptId=";5'";
String sql="select * from dept="+deptId;

通常情况下，depyld的值是由用户输入的。如果用户输入了非法字符，会导致SQL语句的错误

拼接后的SQL语句如下：

String sql="select * from dept=;5";

执行SQL语句时会导致语法错误。

如果用户再坏一点，输入SQL语句的关键字，后果会更严重，以拼接字符串方式插入动态SQL语句值的时候，如果值中有非法字符，或SQL语句关键字中，会导致SQL语句语法错误，或执行结果不正确的情况，这称为SQL注入。

PreparedStatement

那么，如何提高SQL语句的执行效率？又如何防止SQL注入呢？

javaAPI中，提供了Statement的子接口：PreparedStatement，可以解决以上问题。

使用PreparedStatement对象执行预编译语句，能提高Java程序执行SQL语句的效率

            String sql = "update t_user set u_password=?,u_money=? where u_id=?";
            statement = con.prepareStatement(sql);
            statement.setObject(1,user.getPwd());
            statement.setObject(2,user.getMoney());
            statement.setObject(3,user.getId());

Statement和PreparedStatement的区别和联系

1、Statement是PreparedStatement的父接口。都能执行SQL语句

2、Statement在执行动态SQL时，只能以拼接字符串方式插入值。效率即低，也容易引起SQL注入

3、PreparedStatement提供占位符方式注入值，执行SQL语句时，只编译一次。而且无论是什么值都作为字符串处理。效率较高，也可以防止SQL注入