javaweb（学习笔记）之数据库

• 介绍：

• javaweb是用Java技术来解决相关web互联网领域的技术栈。

• 结构：

• 网页：展示数据

• 数据库：管理和存储数据

• javaweb程序：逻辑处理

• 数据库相关概念

• 数据库（DataBase):存储数据的仓库，数据是有组织的进行存储。

• 数据库管理系统：（DataBase Management System,DBMS):管理数据库的大型软件。

• SQL（Structured Query Language):结构化查询语句，操作关系型数据库的编程语言，定义所有关系型数据库的统一标准。

关系型数据库：是建立在关系型模型基础上的数据库，简单地说，关系型数据库是由多张能互相连接的二维表组成的数据库。

• SQL分类：

• DDL：操作数据库，表等。

• DML：对表中的数据进行增删改。

• DQL：对表中的数据进行查询

• DCL：对数据库进行权限控制

DDL:

增加(create):

create database 数据库名；

create database 数据库名 default charset utf8 collate utf8_general_ci;//建立一个数据库并制定编码

格式

create table 表名称(字段1 类型，字段2 类型);//在一个数据库中创建一个表

//创建并增加约束

create table 表名称（

字段1 类型1 约束1 约束1，

字段2 类型2 约束2 约束2，

）;

使用create复制表

//只复制表结构及约束，不复制数据

create table 复制出的表 like 原表

//复制表结构及数据，但不复制约束

create table 复制出的表 as select * from 原表

alter：

添加字段：

alter table 表名称 add 字段名 字段约束

alter table 表名称 add column 字段名 字段约束

添加索引：

alter table 表名称 add index 索引名称（字段名）

添加主键：

alter table 表名称 add primary key （字段名）

alter table 表名称 add unique key（字段名）

添加外键约束：

alter table 需要添加外键表名称 add foreign key 外键名（设为外键的字段）references 被添加外键表名称（设为被添加外键的字段）

修改字段类型时要注意字段类型的兼容性及精度，除非该列全部数据为null

alter table 表名称 change column name 修改字段 修改为字段类型

alter table 表名称 modify column 修改字段 修改为字段类型

修改与删除表中default（默认值）：

//设置default

alter table 表名称 alter column 字段名 set default ‘修改的值’

//删除default

alter table 表名称 alter column 字段名 drop default

禁用和启用约束：

//禁用约束

alter table 表名称 disable keys

//启用约束

alter table 表名称 enable keys

删除表字段、主键、索引、外键约束：

//删除表字段

alter table 表名称 drop column 字段名

//删除主键

alter table 表名称 drop primary key

//删除索引

alter table 表名称 drop index 索引的字段名

//删除外键约束

alter table 表名称 drop foreign key 外键的字段名

修改表名：

alter table 修改之前的表名称 rename 修改后的表名称

drop：

删除数据库

drop database 数据库名

删除表

drop table 表名称

truncate：

与drop的区别是truncate只删除数据不删除表的结构（定义），释放空间

truncate table 表名称；

DML：

增(insert)insert into 表(字段，字段....) values(数据，数据...)，（数据，数据..);

改（update）update 表名 set 列名1=值1，列名2=值2，...[where 条件];

注意：如果update语句没有加where条件，则会将表中的所有数据全部修改；

删（delete) delete from 表名 [where 条件];

注意：如果delete语句没有加where条件，则会将表中的所有数据全部删除；

DQL:

查询语法：

基础查询

条件查询（where）

（between and 在什么之间，in（...）多选一，like模糊查询,_单字符，%任意个数字符）

分组查询（group by）

语法：select 字段列表 from 表名 [where 分组前条件限定] group by 分组字段名 [having 分组后条件过滤];

注意：分组后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义。

where和having的区别：

执行时机不一样：where时分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。

可判断的条件不一样：where不能对聚合函数进行判断，having可以。

执行顺序：where>聚合函数>having

聚合函数：

将一列数据作为一个整体，进行纵向计算。

聚合函数分类：

count(列名)统计数量（一般选用不为null的列）

max（列名）最大值

min（列名）最小值

sum（列名）求和

avg（列名)平均值

聚合函数语法：select 聚合函数名（列名） from 表；

注意：null值不参与所有聚合函数运算

排序查询（order by）

select 字段列表 from 表名 order by 排序字段名1 [排序方式],排序字段名2 [排序方式2]...;

排序方式：

ASC：升序排列（默认值）

DESC：降序排列

注意：如果有多个排序条件，当前边的条件一样时，才会根据第二个条件进行排序。

分页查询（limit）

语法：select 字段列表 from 表名 limit 起始索引，查询条目数；

起始索引从0开始；

计算公式：起始索引=（当前页码-1)*每页显示的条数

tips:

分页查询limit是mysql数据库的方言

oracle分页查询使用rownumber

sql server分页查询使用top

去除重复数据（distinct）

起别名（as）

约束

约束概念：

约束是作用于表中列上的规则，用于限制加入表的数据

约束的存在保证了数据库中数据的正确性、有效性和完整性

约束的分类：

tips:Mysql不支持检查约束。

自动增长：auto_increment：当列是数字类型并且唯一约束。

非空约束：

语法：

添加约束：

删除约束：

默认约束：

语法：

添加约束：

删除约束：

外键约束：

语法：

添加约束：

删除约束：

数据库设计

• 数据库设计简介

• 软件的研发步骤

• 需求分析->设计->编码->测试->安装部署

• 数据库设计概念

• 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。

• 建立数据库中的表结构以及表与表之间的关联关系的过程

• 有哪些表？表里有哪些字段？表和表之间有什么关系？

• 数据库设计的步骤

• 需求分析（数据是什么？数据具有哪些属性？数据与属性的特点是什么？）

• 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）

• 物理分析（根据数据库自身的特点把逻辑设计转换为物理设计）

• 维护设计（1、对新的需求进行建表；2、表优化）

• 表关系之一对多

• 一个部门对应多个员工

• 实现方式：在多的一方建立外键，指向一的一方的主键。

• 表关系之多对多

• 一个商品对应多个订单，一个订单包含多个商品

• 实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

• 表关系之一对一

• 用户和用户详情

• 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能。

• 实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一（unique）

• 数据库设计案例

多表查询

笛卡尔积：有A,B两个集合 取A,B所有的组合情况

连接查询：

内连接：相当于查询AB交集数据

语法：

外连接：

左外连接：相当于查询A表所有数据和交集部分数据

右外连接：相当于查询B所有数据和交集部分数据

子查询：

查询中嵌套查询，称嵌套查询为子查询

子查询根据查询结构不同，作用不同：

单行单列

多行单列

多行多列

事务

数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令

事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败

事务是一个不可分割的工作逻辑单元

事务的四大特征：

原子性（Atomicity):事务是不可分割的最小操作单位，要么同时成功，要么同时失败。

一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态

隔离性（Isolation）：多个事务之间，操作的可见性

持久性（Durability）：事务一旦提交或者回滚，它对数据库中的数据的改变就是永久的

JDBC

JDBC就是使用java语言操作关系型数据库的一套API。

快速入门：

在idea中创建项目

导入jdbc驱动包

创建一个java类

import java.sql.DriverManager;

import java.sql.Statement;

public class JDBCDemo {

public static void main(String[] args) throws Exception {

//注册驱动

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

//获取连接

String url="jdbc:mysql://127.0.0.1:3306/demo";

String username="root";

String password="root";

Connection conn = DriverManager.getConnection(url, username, password);

//定义sql

String sql = "insert into user(id,name,password) values(5,'xiaoli','123')";

//获取执行sql的对象statement

Statement stmt = conn.createStatement();

//执行sql

int count = stmt.executeUpdate(sql);

System.out.println(count);

//释放资源

stmt.close();

conn.close();

} }

JDBC API详情

DriverManager

驱动管理类：注册驱动，获取数据库连接。

Connection

数据库连接对象：

获取执行sql的对象

管理事务

Statement

执行SQL语句

ResultSet

结果集对象

封装了DQL查询语句的结果

使用步骤：

游标向下移动一行，并判断该行是否有数据：next（）

获取数据：getXxx（参数）

可以用来将数据封装成list；

PreparedStatement

预编译SQL语句并执行，预防SQL注入问题。（将敏感字符进行转译）

SQL注入

是通过操作输入来修改事先定义好的SQL语句，用以达到执行代码对服务器进行攻击的方法

数据库连接池

简介

Druid数据库连接池是一个容器，负责分配、管理数据库连接（Connection）

它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；

释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏

好处：

资源重用

提升系统响应速度

避免数据库连接遗漏

数据库连接池的实现

标准接口：DataSource

功能：获取连接

Connection getConnection()

常见的数据库连接池：

DBCP

C3P0

Druid

Druid：

是阿里巴巴开源的数据库连接池项目

功能强大，性能优秀，是java语言最好的数据库连接池之一