华清远见-重庆中心-数据库阶段技术总结/知识点梳理/关于数据库技术的解析

数据和信息

Data数据

任何描述事物的文字和符号的都可以称为数据。

软件开发就是为了收集数据，从中筛选出有用的信息。

Infomation 信息

数据经过处理之后得到的内容称为信息。

数据需要保存，保存的介质有内存和硬盘等。

内存中的数据是临时的，随着系统的关闭，数据也会消失；

硬盘中的数据是永久的，就算系统关闭，数据依然保留。

excel 等文件保存数据是一种保存到硬盘中的途径，

但是如果大量数据要保存，文件系统就不再方便。

使用一个系统化的数据仓库才能更高效地处理数据。

数据库

DataBase ，简称为 DB

运行在操作系统上，按一定的数据结构，保存数据的仓库 。是一个电子化的文件柜。

数据永久保存在硬盘中。

数据库管理系统

DataBase Manager System ，简称为 DBMS

通常所说的数据库，是指数据库管理系统，如 MySQL 、 Oracle 等。

是一种操作和管理数据库的大型软件，用于建立、使用和维护数据库。

MySQL控制台常用命令

查看所有数据库

show databases;

切换数据库

use 数据库名 ;

查看当前数据库下的所有表

show tables ;

创建一个数据库

create database 数据库名 ;

删除数据库

drop database 数据库名 ;

SQL

S tructrued Q uery L anguage 结构化查询语言

用于操作关系型数据库的一门语言。可以用来创建、维护数据库和数据。

操作数据库

1.创建数据库

create database 数据库名 ;

2.切换数据库

use 数据库名;

3.删除数据库

drop database 数据库名;

建表的同时添加约束

-- 创建数据库 gamedb

create database gamedb;

-- 切换数据库

use gamedb;

-- 创建游戏角色表 hero

create table hero(

-- 编号 整型 非空 主键 自增

id int not null primary key auto_increment comment ' 编号 ' ,

-- 姓名 字符串 非空 唯一

name varchar ( 20 ) not null unique comment ' 姓名 ' ,

-- 定位 字符串 非空

position varchar ( 20 ) not null comment ' 定位 ' ,

-- 性别 字符串 非空 默认男

sex char ( 1 ) not null default ' 男 ' comment ' 性别 ' ,

-- 价格 整型 非空 默认 4800

price int not null default '4800' comment ' 价格 ' ,

-- 上架日期

shelf_date date comment ' 上架日期 '

)

-- 创建战斗表 battle

create table battle(

hero_id int not null ,

position varchar ( 20 ),

-- 外键 hero_id 参考 hero 表中的 id 字段

foreign key (hero_id) references hero(id)

)

数据完整性  

数据完整性是指数据精确可靠。不能保存无意义或无效的数据。

如不合理的年龄、全为空的记录、重复记录等。

为了保证保存在数据库中的数据是完整数据，就要在设计数据表时添加一些约束或特征来保证数据完整性。

操作数据

数据的操作，是指数据的增加 create ，修改 update ，查询 read 和删除 delete 。

简称为 CURD 。

数据添加insert

数据添加时，都是整行 ( 一条记录 ) 添加。不能只给一个字段添加数据。

如果只给某个字段添加数据，实际是修改。

给所有字段赋值

insert into 表名 values ( ' 值 1' , ' 值 2' ...)

表名后无需添加字段名，添加时保证值的顺序和字段的顺序一致

遇到自增字段，不能省略不写，要使用 0 、 null 或 default 让其自动填充

遇到有默认值的字段，不能省略不写，要使用 default 让其自动填充默认值

遇到允许为空的字段，不能省略不写，要使用 null 让其设置为空

给指定字段赋值

insert into 表名 ( 字段 1, 字段 2...) values ( ' 值 1' , ' 值 2' ...)

没有默认值的非空字段必须要写出来

表名后的字段顺序要和值的顺序一致

批量添加

可以用一个 insert into 语句添加多条记录

insert into 表名 [( 字段 1, 字段 2)] values

( ' 值 1' , ' 值 2' ...),

( ' 值 1' , ' 值 2' ...),

...

( ' 值 1' , ' 值 2' ...)

可以省略表名后的字段名

值的顺序和字段的顺序一致

如果一次添加多条记录时，优先使用批量添加，效率更高

数据修改update  

修改单个字段的所有值

update 表名 set 字段 = 值 ;

修改多个字段的所有值

update 表名 set 字段 1 = ' 值 ' , 字段 2 = ' 值 ' ...

根据条件修改(where子句)

update 表名 set 字段 = ' 值 ' where 条件

指定值

update 表名 set 字段 = ' 值 ' where 字段 = ' 值 '

指定范围

使用 > 、 < 、 >= 、 <= 表示范围，使用 and 、 or 、 && 、 || 将多个条件关联

update 表名 set 字段 = ' 值 ' where 字段

使用 " 字段 between 值 1 and 值 2" 表示字段在闭区间 [ 值 1 ，值 2]

update 表名 set 字段 = ' 值 ' where 字段 between 值 1 and 值 2

使用 != 或 <> 表示不等于

update 表名 set 字段 = ' 值 ' where 字段 <> 值

指定集合

在某个集合中 in

update 表名 set 字段 = ' 值 ' where 字段 in ( ' 值 1' , ' 值 2' ...)

不在某个集合中 not in

update 表名 set 字段 = ' 值 ' where 字段 not in ( ' 值 1' , ' 值 2' ...)

数据删除delete

数据删除是删除一条或多条记录。

删除所有

delete from 表名 ;

-- 或

truncate table 表名 ;

delete 会保留自增列删除前的值，删除后再添加时，自动从删除前的值开始自增

truncate 会重置自增列的值。效率更高

如果要删除主从关系且设置了外键的表中的数据，如果从表中有数据，不能直接删除主表中相关数

据，先删除从表中的数据后，才能删除主表中的数据

条件删除

delete from 表 where 条件

数据查询select

查询所有字段

select * from 表名 ;

查询指定字段

select 字段名 1, 字段名 2... from 表名 ;

字段重命名

select 字段1 as '重命名',字段2 '重命名'... from 表名;

查询指定条数

-- 查询前 N 条记录

select * from 表名 limit N;

-- 查询从索引 N 开始的 M 条记录

select * from 表名 limit N,M;

-- 每页显示 size 条，第 page 页

select * from 表名 limit (page- 1 )*size,size

去重复 

select distinct 字段名 from 表名 ;

条件查询

where 子句，语法同修改、删除时的 where

select * from 表名 where 条件 ;

排序

select * from 表名 where 条件 order by 排序字段 [ ASC /DESC], 排序字段 [ ASC /DESC]...

排序可以是升序或降序

默认不写是升序 asc

降序需要写 desc

排序时如果有条件， where 条件写在表名之后，排序之前

多字段排序时，在 order by 之后写多个字段及排序规则，用逗号隔开

按字段顺序优先排序

统计函数(聚合函数)

select 统计函数 ( 字段名 ) from 表名 ;

连接查询

交叉连接、笛卡尔积

将两张表中的数据两两组合，得到的结果就是交叉连接的结果，也称为笛卡尔积

集合 A ： {a,b}

集合 B ： {1,2,3}

集合 A x 集合 B={a1,a2,a3,b1,b2,b3}

select * from 表 1, 表 2;

select * from 表 1 cross join 表 2;

select * from 表 1 inner join 表 2;

将两张表中的数据互相组合成一张新表，其中有很多无效数据。

内连接  

select * from 表 1, 表 2 where 表 1. 字段 = 表 2. 字段 ;

select * from 表 1 inner join 表 2 on 表 1. 字段 = 表 2. 字段 ;

-- 如查询图书类型表 ( 类型编号、类型名称 ) 和图书详情表 ( 图书编号、类型编号、图书名称 )

select * from 图书类型表 t1 , 图书详情表 t2 where t1. 类型编号 =t2. 类型编号 ;

select * from 图书类型表 t1 inner join 图书详情表 t2 on t1. 类型编号 =t2. 类型编号 ;

通常是通过主表的主键字段关联从表的外键字段

如果两张表中关联的字段名一致，一定要通过 " 表名 . 字段名 " 进行区分，通常给表重命名

如果使用 inner join ，带条件时需要加入 where 子句；如果使用 , 隔开各个表，带条件时使用 and 拼

接条件

内连接只会显示两张表中有关联的数据

左连接

-- 在保证左表数据显示完整的情况下，关联右表中的数据，没有关联的数据用 null 表示

select * from 表 1 left join 表 2 on 表 1. 字段 = 表 2. 字段 ;

-- 以上语句中表 1 称为左表，表 2 称为右表 , 会完整显示表 1 中的数据

右连接 

-- 在保证右表数据显示完整的情况下，关联左表中的数据，没有关联的数据用 null 表示

select * from 表 2 right join 表 1 on 表 1. 字段 = 表 2. 字段 ;

-- 以上语句中表 1 称为左表，表 2 称为右表，会完整显示表 2 中的数据

嵌套查询 

将查询出的结果继续嵌套使用在另一个查询语句中

-- 查询大于平均价格的图书

select * from 表

where price >( select avg(price) from 表 )

-- 根据作者分组，查询每组大于平均价格的图书

select *

from 表 t1,( select author,avg(price) avg_price from 表 )t2

where t1 .author =t2 .author and price>avg_price

Jar 

以 .jar 为后缀的文件，称为 Java 归档文件，保存的是 Java 的字节码 .class 文件。

在 Java 程序中导入某个 .jar 文件，就能使用其中的 .class 文件。

在Java项目中使用.jar文件

1. 创建一个 Java 项目，最好新建一个文件夹，将 .jar 文件保存在其中

2. 在 .jar 文件上右键，点击 “add as library”

JDBC

J ava D ata b ase C onnectivity Java 数据库连接

用于 Java 程序连接不同的数据库。

实际在 Java 中定义的相关数据库连接时所需的接口，不同的数据库对其进行了实现。

核心接口

Connection : 用于设置连接的数据库的地址、账号、密码

PreparedStatement : 用于预处理、执行 sql 语句

ResultSet : 用于接收查询后的数据

以上接口都来自于 java.sql 包中

数据查询

import java . sql . * ;

public class JDBCTest {

public static void main ( String [] args ) throws SQLException ,

ClassNotFoundException {

// 连接 mysql 数据库 , 实现查询功能

// 连接对象

//Connection conn;

//sql 预处理对象

//PreparedStatement pst;

// 结果集对象

//ResultSet rs;

// 查询的流程

//1. 加载 mysql 驱动

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

//2. 连接指定的 mysql

String url = "jdbc:mysql://localhost:3306/gamedb?

serverTimezone=Asia/Shanghai" ;

String username = "root" ;

String password = "root" ;

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

//3. 构造 sql 语句

String sql = "select * from hero" ;

//4. 预处理 sql 语句

PreparedStatement pst = conn . prepareStatement ( sql );

//5. 执行查询的方法

ResultSet rs = pst . executeQuery ();

//6. 循环读取数据

while ( rs . next ()){ //rs.next() 方便表示读取且判断是否还有后续数据

// 获取读取到的数据 rs.get 数据类型 (int columnIndex) 根据字段索引获取值

rs.get 数据类型 (String columnName) 根据字段名获取值

int id = rs . getInt ( 1 );

String name = rs . getString ( "name" );

String sex = rs . getString ( "sex" );

String position = rs . getString ( "position" );

int price = rs . getInt ( "price" );

String shelf_date = rs . getString ( 6 );

System . out . println ( id + "--" + name + "--" + sex + "--" + position + "--" + price + "-

-" + shelf_date );

}

//7. 释放与 mysql 的连接资源

rs . close ();

pst . close ();

conn . close ();

}

}

数据增删改 

public void addHero ( Hero hero ) throws SQLException , ClassNotFoundException {

//1. 加载驱动

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

//2. 获取连接对象

conn = DriverManager . getConnection ( "jdbc:mysql://localhost:3306/gamedb?

serverTimezone=Asia/Shanghai" , "root" , "root" );

//3. 构造 sql, 参数使用 ? 占位

String sql = "insert into hero values(null,?,?,?,?,curdate())" ;

//4. 预处理 sql

pst = conn . prepareStatement ( sql );

// 给 ? 赋值

pst . setString ( 1 , hero . getName ());

pst . setString ( 2 , hero . getPosition ());

pst . setString ( 3 , hero . getSex ());

pst . setInt ( 4 , hero . getPrice ());

//5. 调用更新的方法

int i = pst . executeUpdate ();

//6. 判断执行结果

if ( i > 0 ){

System . out . println ( " 添加成功 " );

} else {

System . out . println ( " 添加失败 " );

}

//7. 释放资源

pst . close ();

conn . close ();

}

SQL注入

在构造 sql 语句时，如果使用字符串拼接的方式构造动态 sql ，可能会造成 sql 注入的风险，导致执行不是预期的sql 语句

-- 如删除的 sql ： String sql = "delete from 表 where id="+ 参数 ;

-- 实际传值时，参数为 ''or 1=1 会导致 sql 变成

delete from battle where id= '' or 1 = 1

-- 这样就会删除所有数据

-- 查询时的 sql: "select * from 表 where name="+ 参数 +" and pwd= "+ 参数

-- 实际传值时，第一个参数为 'or 1=1 -- 会导致 sql 变成

select * from hero where id= '' or 1 = 1 -- ' and name=' 亚索 '

-- 这样会查询出所有数据

实体关系模型 

实体 E ntity ：一个表就是一个实体。

关系 R elationship ：实体与实体之间的关系。

实体关系模型也称为 ER 模型。

用图形表示 ER 模型时，这个图就称为 ER 图。

用 矩形 表示 实体 ，用 椭圆形 表示实体的 属性 ，用 菱形 表示实体之间的 关系 ，用 直线 连接各个图形。

实体之间的关系

一对一

实体 A 与实体 B 之间唯一对应。

如一个国家有一个领导人，一个人对应一个配偶。

在数据库中创建表的过程

可以使用一张表实现，但如果使用一张表保存时，后期维护扩展时较为不便。

最好使用两张表实现。

一对多/多对一

一对多：一个实体 A 对应多个实体 B ，一个实体 B 不能对应多个实体 A

多对一：多个实体 B 对应一个实体 A ，多个实体 A 不能对应一个实体 B

如一个人有多辆车，一辆车不能对应多个人；

一个教官训练多个学员，一个学员不能对应多个教官；

多对多

一个实体 A 可以对应多个实体 B ，一个实体 B 也可以对应多个实体 A 。

如一个学生可以学习多门课程，一门课程也可以对应多个学习它的学生。

一个医生可以有多个病人，一个病人也可以对应多个医生。

总结

一对一：创建各自的实体表，在任意一张表中添加另一张表的主键字段，并添加唯一约束

一对多 / 多对一：先创建主表 ( 一 ) ，再创建从表 ( 多 ) ，在从表中添加主表中的主键字段，外键可选

多对多：创建各自的实体表，再创建第三张表： " 联系表 " ，在 " 联系表 " 中添加两个实体表中的主键字段,外键可选.

数据库设计规范

数据库设计的规范，简称为 范式 (NF) 。

范式分为第一范式 (1NF) 、第二范式 (2NF) 、第三范式 (3NF) 、 BC 范式 (BCNF) 、第四范式 (4NF) 和第五范式

(5NF) 共六种。

这六种范式的级别越高，表示数据库设计的结果越规范，每一个高级版的范式都包含了低级别的范式。

通常设计数据库时，只需满足 3NF 即可。

第一范式1NF

数据表中的每一列都是不可再分的原子项。

关系型数据库起码要满足 1NF ，才能创建表。

第二范式2NF

在满足 1NF 的基础上，消除部分依赖。

对于联合主键而言，每一个非主属性字段都需要完全依赖于主属性，而不是依赖其中一部分。

第三范式3NF

在满足 2NF 的基础上，消除传递依赖。

在上图中，系主任是通过学号 -> 系别 -> 系主任获取，系主任传递依赖于学号，消除这个传递依赖

名词解释

主键/主码/主属性

用于唯一区分每条记录的一个字段，称为主键字段，如学号、身份证号。

通常选择能唯一且几乎不会改变的某个字段作为主键。如果没有，自定义一个 id 列作为主键。

联合主键

如果一个字段不能唯一区分每条记录，而是需要多个字段才能唯一区分时，这些字段一起组成了联合主键。

完全依赖

如果能通过 A 和 B 得到 C ， A 和 B 都不能单独得到 C 时，称为 C 完全依赖于 A 和 B 。

如 ( 学号 + 科目 ) 联合主键 -> 分数，分数完全依赖于联合主键。

其他字段完全依赖于学号。