华清远见重庆中心—数据库总结/个人总结

数据和信息

---

Data数据

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

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

信息就是经过分析筛选后的数据

数据需要保存，保存的介质有内存或硬盘

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

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

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

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

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

数据库

DataBase，称为数据库，简称DB

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

数据库管理系统

DataBase Manager System，简称DBMS

通常所说的数据库，其实是指数据库管理系统，如MySQL，SQLServer,Oracle

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

总结

• 数据Data需要永久保存在数据库中

• 数据库DB是运行在操作系统上的一个软件

• 数据库管理系统DBMS是管理数据库的一个软件

• 学习数据库就是学习如何使用DBMS创建、使用数据仓库来管理数据

常见的数据库管理系统

关系型数据库

关系型数据库是主流的数据库类型

数据通过行row和列column的形式（表格）保存。

每行数据称为一条记录。

每列数据称为一个字段。

学号（字段）	姓名（字段）	专业（字段）
1001	小王	计算机
1002	小吴	计算机

字段通常为Java中某个实体类的属性，通过这个类创建的对象，就是一条记录。

如class Employee，有员工编号、部门、工资等属性，

对应数据库中有一张Employee员工表，这张表中有员工编号、姓名、部门、等字段。

关系型数据库中，数据表中间进行关联，能快速地查询想要的数据

• 优点

• 易于维护：数据都是以表的结构存储，格式一致

• 使用方便：SQL语句通用，可以用于不同的关系型数据库

• 支持复杂查询：可以通过SQL语句在多个表之间查询出复杂数据

• 缺点

• 读写性能差，在处理海量数据时，频繁读写效率略低

• 表结构不易改动，灵活度欠佳。

• 高并发读写时，硬盘的输入/输出(I/O)决定了读写的速度。

非关系型数据库

数据通过对象的形式保存，对象可以是一个键值对、文档、图片等。

非关系型数据库的特点

• 保存数据的格式多样

• 对于海量数据的读写性能高

• 不支持复杂查询

MySQL8.x的安装和使用

1. 下载

2. 安装

3. 使用

可以之后使用图形化界面方便操作，但最后了解纯命令的形式使用MySQL，因为最终项目运行在Linux服务器上，无法使用图形界面

在安装目录的bin文件夹中，输入cmd进入控制台

输入mysql -uroot -p后回车，输入安装时的密码

常用指令

• show databases; 查看所有数据库。

• use数据库名； 切换到指定数据库。

• create database 数据库名; 创建指定数据库

• drop database 数据库名; 删除指定数据库

• show tables; 在某个数据库中，查看其中的所有表

数据库管理系统图形化管理工具

如果只是使用控制台操作数据库管理系统很不方便，所以在windows下有很多图形化管理工具

Navicat的使用

连接mysql

创建数据库

右键新建数据库，输入名称

切换数据库

只需双击对应的数据库

创建数据表

在展开后的数据库中，在表的选项上右键新建表

保存时输入表名

• 每张表需要一个编号"id"列，通常设置为主键，目的是为了区分每条记录。主键列中的数据不能重复，通常还需要将主键列设置为自增列

• 由于mysql中大小写不敏感，字段名使用全部小写字母，多个单词用_隔开

• 数据类型和所占长度根据实际情况选择

• 如果某列数据必须要填写，将"不是null"勾选

• 如果某个字段有默认值，可以在设计表的时候设置，字符串用单引号引起了。

• 最好加上注释

SQL

Structrued Query Language 结构化查询语言

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

--mysql 中的注释

操作数据库

创建数据库

create database 数据库名称;

切换数据库

use 数据库名;

删除数据库

drop database 数据库名;

操作数据表

创建数据表

create table 表名(
    字段名1 数据类型 [字段特征],
    字段名2 数据类型 [字段特征],
    字段名3 数据类型 [字段特征],
    ……
    字段名n 数据类型 [字段特征],
)

修改数据表

• 对表重命名

alter table 旧表名 rename to 新表名;

• 添加新字段

alter table 表名 add column 字段名1 数据类型 [字段特征];

• 修改字段

alter table 表名 change 旧字段名 新字段名 数据类型 [字段特征];

• 删除字段

alter table 表名 drop 字段名;

添加约束

• 添加唯一约束

alter table 表名 add unique(字段名);

• 添加主键约束

alter table 表名 add primary key(字段名);

• 添加默认值约束

alter table 表名 alter 字段名 set default '默认值';

• 添加外键约束

alter table 从表表名 add foreign key(从表外键字段) references 主表表名(主表主键字段)

删除数据表

drop table 表名

如果要删除主表，需要先删除从表

操作数据

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

简称CURD。

数据添加insert

数据添加时，都是整行添加，不能只给一个字段添加数据

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

给所有字段赋值

insert into 表名 valuse('值1','值2'……)

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

• 遇到自增字段，不能省略不写，可以使用0，null或defaut让其填充自增值

• 遇到有默认值的字段，不能省略不写，使用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 字段='值',字段 = '值'...;

根据条件修改（where子句）

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

指定值

使用= 指定某个字段的值

update 表名 set 字段 = '值',字段 = '值'... where 字段 = '值';

指定范围

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

• 使用''字段between A and B 表示字段在[A,B)范围内

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

指定集合

• 某个字段的值在某个集合中时 字段in('值1','值2'...)

• 某个字段

空值匹配

• 某个字段的值为空，字段is null

• 某个字段的值不为空 字段 is not mull

模糊查询

-- 字段 like '%琳%' 带有‘琳’字的
-- 字段 like '张%' 第一个字张
-- 字段 like '%二' 以‘二’字结尾
-- 字段 like '__' 两个字
-- 字段 like '%的_'  第二个字为‘的’

数据删除delete

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

删除所有

-- 删除battle表中的所有数据
delete from battle;
truncate table battle;
-- delete from 删除后，会保留自增列的自增值

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

• truncate会重置自增列的值，删除所有数据时效率更高

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

条件删除

delete from 表名 where 条件;

删除时的条件同修改时的条件语句

数据查询select

查询所有字段

select * from 表名;

查询指定字段

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

字段重命名

selecct 字段1 as '自定义名称',字段2 as '自定义名称'...from 表名;

查询指定条数

-- 查看前5本图书 相当于每页显示五本，第一页数据
select  * from book_info limit 0,5;
-- limit n,m 从索引n开始的m条
select * from book_info limit 5,3;
-- 第二页
select * from book_info limit 5,5;
-- 第三页
Select * from book_info limit 10,5;
-- 每页显示size条记录,第page页；
limit (page - 1)* size,size

条件查询

select * from 表名 where 条件;

查询时的条件同修改时的条件语句

排序

select * from 表名 where 条件 order by 排序字段 排序规则,排序字段 排序规则...

• 排序默认是升序

• 排序规则不写或写asc表示升序

• 排序规则写desc表示降序

• 排序时如果有条件，排序写在条件语句之后

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

统计函数（聚合函数）

select 统计函数（字段）from 表名;

函数名	作用
sum(字段)	求和
avg(字段)	平均值
count(字段)	计数
max(字段)	最大值
min(字段)	最小值

数学相关函数

函数名	作用
abs(值或字段)	绝对值
pow(值或字段)	次幂
sqrt(值或字段)	开平方
round(值或字段)	四舍五入取整
cell(值或字段)	向上取整
floor(值或字段)	向下取整

字符串相关函数

函数名	作用
length(字符串或字符)	得到字符串长度
trim/ltrim/rtrim(字符串或字符)	去尾/首/尾空格
substr(字符串或字符)	从start开始截取字符串到末尾
substr(字符串或字段,start)	从start开始截取len个字符串
left(字符串或字段,len)/right(字符串或字段,len)	从首/尾开始截取len个字符
lcase(字符串或字段)/ucase(字符串或字段)	转换为小写/大写
reverse(字符串或字段	反转字符串
concat(字符串1,字符串2...)	将所有字符串拼接
replace(字符串或字段,旧字符串,新字符串)	将字符串中的旧字符串替换为新字符 串

时间相关函数

函数名	作用
now()	得到当前日期时间
current_date()/curdate()	得到当前日期
current_time()/curtime()	得到当前时间
year(日期)/month(日期)/day(日期)	得到年/月/日部分
datediff(时间1，时间2)	得到时间1与时间2相隔的天数
timediff(时间1，时间2)	得到时间1与时间2相隔的时分秒
timestampdiff(时间单位，时间1，时间2)

分组

按指定的字段进行分组，会将该字段值相同的记录归纳中同一组

分组通常配合统计函数使用

如果统计函数作为条件，不能使用where 而是使用having子句，将整个having 子句放在分组之后

group_concat()函数

将分组后的数据拼接成字符串

group_concat(字段或字符串，字段或字符串...)
-- 根据图书类型分组，查看每组下的图书名和作者
select book_author,group_concat(book_name,'--',book_author) from book_info 

实际业务对应SQL操作

登录

登录通常是使用用户名和密码查询用户表

select * from 用户表 where 用户名 = ? and 密码 = ?

如果没有查询到数据，说明用户不存在或输入有误。

如果能查询到数据，返回查询到的内容

注册

注册通常是使用用户名和密码添加一条记录

注册时一般还会验证要注册的用户是否存在

insert into 用户表 values(注册信息1，注册信息2...)

充值

充值、消费都是将数据进行更新及修改

update 用户表 set 余额 = 余额 +/- 值 where 主键 = 值

create table userinfo(
    userid int not null primary key auto_increment,
    usernaem varchar(20) not null,
    password varchar(20) not null,
    balance double not null
)
-- 注册
--检查要注册的用户名是否存在
select * from userinfo where username = 'admin';
-- 如果上一句没有查询到数据，才进行添加
insert into userinfo values(null,'admin','123',0);
-- 登录
select * from userinfo where username = 'admin' and password= '123';
-- 充值
-- 获取注册时自动生成的id
update userinfo set balance = balance + 100 where userid = 1;

删除

逻辑删除

不删除数据，只是不显示数据。

用户名	电话	地址	是否删除 0未删除，1已删除
小王	123	钱钱钱	0
小李	123	钱钱钱	1

查询所有未删除数据

select * from 用户表 where 是否删除 = 0

假设要删除小王 只需将是否删除的值改为1

update 用户表 set 是否删除 = 1 where name = ‘小王’;

物理删除

真实删除

delect from 用户表 where 用户编号 = 小王编号

数据完整性

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

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

为了保证数据的完整性，就要在设计数据表的时候添加一些约束或字段特征来包装数据的完整性

MySQL中常见的数据类型

整型
tinyint	对应java中的short	短整型
int	对应java中的int	整型
brgint	对应java中的long	长整型

浮点型
float	对应java中的float	单精度浮点型
double	对应java中的double	双精度浮点型
decimal(宽度，精度)	指定保留的小数位和整体宽度	如decimal(4,2)如3,1415122—>3.14

字符串
char(大小)	定长字符串	对应java中的String.char(10) 表示就算实际保存3个字符，也占10个字符
varchar(大小)	可变字符串	对应java中的String。varchar(10)表示实际保存3个字符，占3个字符
text	文本

日期
data	日期	yyyy-MM-dd
time	时间	HH:mm:ss
datetime	日期时间	yyyy-MM-dd HH:mm:ss
timestamp	毫秒	保存日期的毫秒数

约束

约束/字段特征	关键字	概念
非空约束	不写或写null表示允许为空，not null 表不能为空	是否允许该字段为Null
主键约束	primary key	主键(primary key)。也可以称为主关键字、主码；用于区分表中的每条记录的一个字段。如果有现成的字段可以区分每条记录时，该字段设置为主键字段；如果没有现成的字段可以区分每条记录时，通常会额外添加一个Id字段设置为主键。通常一张表只有一个主键
唯一约束	unique	保证该字段值不能重复
默认值约束	default	添加一条记录时，可以不给有默认值约束的字段赋值，让其使用默认值
外键约束	foriegn key<br> references	在主从关系的表中，给从表中的某个字段添加外键约束后，该字段的值

连接查询

交叉连接、笛卡尔积

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

集合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中的数据

右连接

-- 保证右表数据显示完整的情况下，关联左表中的数据，没有关联的用null表示
select * from 表1 right join 表2 on 表1.字段 = 表2.字段
-- 最终显示表2中的所有数据，关联表1中的数据

嵌套查询

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

-- 查询价格最低的图书信息
select * from book_info where book_price = (select min(book_price) from book_info)
-- 按类型分组，查询每组中图书价格大于平均价格的图书
select * from book_info bi inner join
(select type_id,avg(book_price) as avg from book_info groupby type_id)temp
on bi.type_id = temp.type_id  where book_price > avg

Jar

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

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

在Java项目中使用.jar文件

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

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

JDBC

Java DataBase Connectivity Java数据库连接

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

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

核心接口

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

• PreparedStatement：用于预处理、执行SQL语句

• ResultSet：用于保存查询后的数据

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

单表查询

import java.sql.*;
​
public class Main {
    public static void main(String[] args) throwsClassNotFoundException, SQLException {
        //连接mysql数据库实现单表查询
        //1.加载连接MySQL的驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.连接数据库,提供要连接的数据库的URL、用户名、密码
        String url="jdbc:mysql://localhost:3306/gamedb?serverTimezone=Asia/Shanghai";
        Connection conn=Driver Manager.getConnection(url, "root", "root");
        //3.构造查询的sql语句
        String sql="select * from hero";
        //4.处理sql语句
        PreparedStatement pst=conn.prepareStatement(sql);
        //5.处理执行sql语句后的结果
        ResultSet rs=pst.executeQuery();
        //6.循环遍历读取查询后的数据
        while (rs.next()) {
            //根据字段名获取
            intid=rs.getInt("id");
            //根据字段顺序获取
            String name=rs.getString(2);
            String sex=rs.getString(3);
            int price=rs.getInt(4);
            String makeTime=rs.getString(5);
            String position=rs.getString(6);
            System.out.println(id+"\t"+name+"\t"+sex+"\t"+price+"\t"+makeTime+"\t"+position);
        }
        //7.关闭连接
        rs.close();
        pst.close();
        conn.close();
    }
}

SQL注入

---

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

-- 如删除的sql String sql="delete from 表 where 主键="+参数;
-- 实际传递 '' or 1=1
deletefrom 表 where 主键= ''or1=1
-- 导致条件永远成立，最终删除了所有数据
​
-- 查询的sql  String sql="select * from 表 where name="+name +"and pwd="+pwd;
-- 实际第一个参数传递 '' or 1=1 -- 
select * from 表 where name = ''or1=1-- pwd=
-- 导致第二个参数被注释，第一个参数永远成立

在构造sql语句时，使用?占位，通过preparestatement方法防止sql注入的风险

简化JDBC

DBUtil数据库工具类

import java.sql.*;
​
/*
* 定义数据库工具类，简化JDBC操作
* 可以简化的操作
* 1.加载驱动   √
* 2.连接数据库 √
* 6.关闭   √
* */
public class DBUtil {
    /*
     * 定义静态代码块用于加载驱动
     * */
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundExceptione) {
            System.out.println("驱动不存在"+e);
        }
    }
​
    //定义静态常量保存连接数据库的字符串
    private final static String URL="jdbc:mysql://localhost:3306/gamedb?serverTimezone=Asia/Shanghai";
    private final static String USERNAME="root";
    private final static String PASSWORD="root";
​
    /*
     * 定义静态方法获取连接对象
     * */
    public static ConnectiongetConn() {
        Connectionconnection=null;
        try {
            connection=Driver Manager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLExceptione) {
            System.out.println("连接数据库信息异常"+e);
        }
        return connection;
    }
​
    /*
     * 定义静态方法释放资源
     * */
    public static void release(Connectionconn, PreparedStatementpst, ResultSetrs) {
        //使用了哪个接口就关闭那个接口
        try {
            if (rs!=null) {
                rs.close();
            }
            if (pst!=null) {
                pst.close();
            }
            if (conn!=null) {
                conn.close();
            }
        } catch (SQLExceptione) {
            System.out.println("关闭异常"+e);
        }
    }
}

数据库设计

实体关系模型

实体Entity：一张表就是一个实体

关系Relationship：实体与实体之间的关系。

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

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

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

实体之间的关系

一对一

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

ER图

在数据库中创建表的过程

可以使用一张表实现，但是后期维护拓展时较为不便

国家名称和领导人姓名需要添加唯一约束

国家名称（唯一约束）	人口数量	国土面积	领导人编号	领导人姓名（唯一约束）	性别	生日
中国	15亿	960万	1	xxx	xxx	xxx
美国	xxx	xxx	2	xxx	xxx	xxx
xxx	xxx	xxx	xxx	xxx	xxx	xxx

最好使用两张表实现

• 国家表

create table country(
    country_id int not null primary key auto_increment,
    country_name varchar(20) not null,
    country_population bigint not null,
    country_area int not null
)

• 领导人表

create table leader(
    leader_id int not null primary key auto_increment,
    leader_name varchar(20) not null,
    leader_sex varchar(2) not null,
    leader_birthday date
)

创建后两张表是独立的，并不能体现一对一的关系，可以通过以下方式体现一对一

• 方式一：

在国家表中添加领导人编号，将其设置为唯一；或在领导人表中添加国家编号字段，将其设置为唯一

create table leader(
    leader_id int not null primary key auto_increment,
    leader_name varchar(20) not null,
    leader_sex varchar(2) not null,
    leader_birthday date,
    country_id int not null unique,
    foreign key (country_id) references country(country_id)
)

• 方式二：

创建第三张表：关系表

create table relation(
    relation_no int not null primary key auto_increment,
    country_id int not null unique,
    leader_id int not null unique,
    foreign key (country_id) references country(country_id),
    foreign key (leader_id) references leader(leader_id)
)

一对多/多对一

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

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

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

如多个学员对应一个教官，但多个教官不能对应一个学员

在数据库中创建表

• 创建主表（一） coach教官表

• 创建从表 （多） student学员 添加外键字段关联主表中的关键字段

• foreign key (coach_id) references coach(coach_id)

多对多

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

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

在数据库中创建表的

• 创建学生表

• 创建课程表

• 不需要添加外键

• 体现多对多关系的表：成绩表 （包含分数编号（主键），(学生编号，课程编号--不是unique)）

• 可以选择添加外键约束（学生id和课程id作为外键约束）

总结

一对一：创建各自的实体表，在任意一张表中添加另一张表中添加另一表的主键字段，将其设置为唯一

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

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

数据库设计规范

数据库设计的规范，简称为范式（NF）

范式分为第一范式1NF，第二范式2NF、第三范式3NF、BC范式BCNF、第四范式4NF、第五范式5NF共六种

这六种范式的级别越高（第一范式到第五范式），表示数据库设计的结构越规范，每一个高等级的范式都包含了低等级的方式。

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

如有该原始表。

黄色背景称为联合主键。由学号和科目一起区分每一条记录。暂时这张表不满足任何范式。

当前表如果要做增删改查的操作，会涉及到的问题

• 如果要加入一个新的系别，就要添加学生、系主任

• 如果要删除"刘定宇"，他所在的系别也会被删除

• 如要将"王海"的系别改为会计，相应的系主任也要修改

• 当前表中有大量重复冗余数据

第一范式1NF

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

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

上表中的联系方式列，可以再分为手机和QQ两列，不满足原子性，不满足1NF。根据1NF，修改后

第二范式2NF

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

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

在上图中，无法用学号当主键，需要将学号和科目组合为联合主键，通过联合主键才能得到分数。

除了学号和科目外，其他字段都是非主键字段，分数完全依赖于联合主键，其他字段不满足，所以进行

拆分

第三范式3NF

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

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

最终根据实体关系模型进行优化，体现对应关系

名词解释

主键/主码/主属性

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

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

联合主键

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

完全依赖

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

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

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

部分依赖

通过A和B可以得到C，单独通过A或B也能得到C，称为C部分依赖于A和B。

如(学号+科目)联合主键-->姓名，其实只通过学号也能得到姓名，称为姓名部分依赖于联合主键。

传递依赖

如果通过A得到B，通过B得到C，称为C传递依赖于A。

如学号-->系-->系主任，其实只通过系也能得到系主任，称为系主任传递依赖于学号。

视图View

视图可以当做数据库中的一个临时表，保存一些较为复杂的查询后的结果

之后可以直接通过该视图查询数据，不需要再次编写复杂的sql语句

视图可以隐藏一些查询细节，定制查询数据

创建视图

create view 视图名 as 
查询的sql语句;

使用视图

select * from 视图名;

修改视图中的数据，会直接修改原始表中的数据

删除视图

drop view 视图名;

事务transaction

事务是由一组sql语句组成的执行单元，这些sql之间一般都互相依赖。

如A给B转账

1. update 表 set money = money - 1000 where id = 1

2. update 表 set money = money + 1000 where id = 2

以上的两句sql 组成了一个转账的事务，一个事务要么全部执行，要么全部不执行

事务的特性ACID

Atomicity 原子性

事务是最小的执行单元，要么全部执行，要么全部不执行

Consistency 一致性

事务在执行前后，必须让所有数据保持一致状态，（总体数据守恒）

Isolation 隔离性

多个事务并发执行时，应该互相隔离，互不影响

Durability 持久性

事务一旦提交，对数据的改变是永久的

事务的使用

提交commit

回滚rollback

mysql中事务默认是自动提交的

如果关闭了事务自动提交，在执行某个事务途中，如果出错，可以使用rollback进行回滚，让数据回到事务执行之前的状态

• 查看事务自动提交开启状态：select @@autocommit 1表示开启了自动提交，0表示关闭了自动提交

• 设置事务不自动提交 set @@autocommit=0

如果不出错，通过commit提交事务后，无法进行回滚

手动提交/回滚事务

1.关闭事务自动提交:set @@autocommit=0

2.开启事务:start transaction

3.事务要执行的sql

4.没有提交之前，可以使用rollback回滚

5.如果要提交，使用commit，一旦提交成功，无法rollback

事务并发可能出现的问题

在同一时刻同时执行多个事务时，称为事务并发

事务并发时会有可能出现以下问题

问题	描述
脏读	事务A读取到了事务B未提交的数据
不可重复读	事务A中如果要读取两次数据，在这期间，事务B对数据进行了修改并提交，导致事务A读取两次的情况不一致
幻读	事务A读取id为1-10之间的数据，假如只有id为2和5的数据，在读取期间，事务B添加了 一条id为3的数据，导致事务A多读取到了事务B中的数据

事务隔离级别

隔离级别 会 会 不会 会 会 不会 不会 会 不会 不会 不会	能否出现脏读	能否出现不可重复 读	能否出现幻 读
Read Uncommitted 未提交读RU	会
Read Committed 已提交读(Oracle默认)
Repeatable Read 可重复读RR(MySQL默认)
Serializable 可序列化

设置事务隔离级别

触发器tirgger

如表A是客户表，表B是操作日志，对表A进行更新操作时，将操作的记录保存到表B中

慎用触发器，数据很多时，每一条触发器都会执行，影响效率

创建触发器

-- 向User表中添加数据之后，向log表中添加操作日志
-- 创建触发器
-- create trigger 触发器名
-- 触发时机（after/before） 触发操作(insert/update/delete) on 表 for each row
-- begin
-- 满足触发条件后执行的内容
-- end
create trigger mytrigger
after insert on user for each row
begin 
    insert into log values(null,'执行了添加操作',now());
end

使用触发器

触发器创建成功后，无需手动调用，在执行满足触发器的操作后，会自动执行触发器

-- 当满足触发条件时，自动执行触发后的操作,在log表中添加一条记录
insert into userinfo values(null,'admin','123');

删除触发器

drop trigger 触发器名;

存储过程procedure

类似于java中的方法，定义一组用于完成待定功能的sql语句

定义存储过程后，通过调用存储过程名，就可以执行定义时的内容，存储过程还可以有参数

调用存储过程

-- 调用无参数的存储过程
call 存储过程名();
-- 调用有参数的存储过程
call 存储过程名('实参');

删除存储过程

drop PROCEDURE if EXISTS 存储过程名

定义存储过程

create procedure 存储过程名([参数类型 参数名 参数数据类型]) 参数类型分为输入型参数和输出型参数
begin
    sql语句;
end

定义无参数的存储过程

create procedure 存储过程名()
begin
    sql()
end
-- 定义无参数的存储过程，查询每本图书的名称、作者、类型
CREATE PROCEDURE myproc1 ()
BEGIN
SELECT
*
FROM
book_info bi,
book_type bt
WHERE
bi.type_id = bt.type_id;
END
-- 调用存储过程
call myproc1();

定义有输入型参数的存储过程

create procedure 存储过程名(in 参数名 数据类型)
begin
    sql()
end
-- 根据图书类型查询该类型下的所有图书
CREATE PROCEDURE myproc2 (IN lx VARCHAR ( 20 ))
BEGIN
SELECT
*
FROM
book_info bi
INNER JOIN book_type bt ON bi.type_id = bt.type_id
WHERE
type_name = lx ;
END
-- 调用输入型参数的存储过程
call myproc2('漫画');

定义有输出型参数的存储过程

create procedure 存储过程名(out 参数名 数据类型)
begin
    sql()
    -- 通常需要将查询后的结果保存到输出参数中
    - '通过select 值或字段 into 参数 赋值';
end
-- 根据作者查询其图书数量
CREATE PROCEDURE myproc3 (OUT book_count INT,IN zuozhe VARCHAR ( 20 ))
BEGIN-- 将查询到的结果使用Into赋值给输出参数book_count中
SELECT
count( book_id ) INTO book_count
FROM
book_info
WHERE
book_author = zuozhe;
END
-- 输出型参数，使用@变量 当做变量接收
call myproc3(@x,'古龙')
-- select @变量 查询变量中的数据
select @x

定义输入输出型参数的存储过程

create procedure 存储过程名(inout 参数名 数据类型)
begin
sql语句;
-- 通常需要将查询后的结果保存到输出参数中
-- 通过 " select 值或字段 into 参数 " 赋值;
end
-- 根据书名的关键字查询书名、作者、类型
CREATE PROCEDURE myproc4 (INOUT keyword VARCHAR ( 20 ))
BEGIN
SELECT
book_name ,
book_author,
type_name
FROM
book_info bi,
book_type bt
WHERE
bi.type_id = bt.type_id
AND book_name LIKE concat( '%', keyword, '%' );
END
-- 创建变量
set @keyword='龙';
call myproc4(@keyword);
select @keyword

MySQL编程

在定义存储过程中，可以定义变量，使用流程控制语句等

定义变量

create procedure 存储过程名()
begin
    -- declare 变量名 数据类型;
    declare num int;
    declare name varchar(20);
end

给变量赋值

create procedure 存储过程名()
begin
    declare num int
    declare name varchar(123);

条件语句

单分支if 语句

if 条件
then
满足条件后执行的sql;
end if;
-- 根据作者查询图书库存，如果不足100，输出'库存不足100'
create PROCEDURE myproc6(in zz varchar(20))
begin
-- 定义变量保存该作者的图书库存
declare num int;
-- 根据参数查询该作者的图书库存
select sum(book_num) into num from book_info where book_author=zz;
-- 判断num
if num<100
then
select '不足100';
end if;
end
call myproc6('小刘')

双分支if语句

if 条件
then
满足条件后执行的sql;
else
不满足条件时执行的sql；
end if;

-- 根据图书类型查询图书数量，如果不足6，输出"不足6种"，如果足够，输出详情
create PROCEDURE myproc7(in lx varchar(20))
begin
-- 定义变量保存图书数量
declare book_count int;
-- 根据条件查询
select count(book_id) into book_count
from book_info bi,book_type bt
where bi.type_id =bt.type_id and type_name = lx;
-- 双分支判断
if book_count<6
then
select '不足6种';
else
select *
from book_info bi,book_type bt
where bi.type_id =bt.type_id and type_name = lx;
end if;
end
call myproc7('漫画')
call myproc7('小说')

循环语句

while循环

循环语句

while循环

repeat循环

loop循环

MySQL核心内容

SQL语句

• 建库、建表

• 修改表结构

• 数据完整性（约束）

• 单表的增删改查（CURD）

• 增加：insert into 表 values(值1,值2....)

• 修改：update 表 set 字段1=值,字段2=值... where 条件

• 删除：delete from 表 where 条件

• 查询：select */字段名 from 表

• 常用函数

• 统计函数

• 数学、时间、字符串

• 多表查询、嵌套查询

• 笛卡尔积

• 内连接

• 左连接/右连接

数据库设计

• 实体关系模型ER、ER图

• 实体间的关系

• 一对一

• 一对多/多对一

• 多对多

• 数据库三大范式

• 1NF：字段不可再分

• 2NF：消除部分依赖

• 3NF：消除传递依赖

JDBC

• 连接MySQL所需的jar文件

• 普通java项目需要手动导入jar文件

• maven项目需要使用依赖自动导入jar文件

• MySQL连接字符串

String DRIVER_CLASS="com.mysql.cj.jdbc.Driver";
String USERNAME="root";
String PASSWORD="密码";
String URL="jdbc:mysql://localhost:3306/数据库名?
serverTimezone=Asia/Shanghai";

事务

• 事务的概念和特性

• A原子性

• C一致性

• I隔离性

• D持久性

• 事务并发时出现的问题

• 脏读

• 不可重复读

• 幻读

• 事务隔离级别

• read uncommitted 未提交读 会出现脏读、不可重复读和幻读问题

• read committed 已提交读 (oracle默认) 不会出现脏读，会出现不可重复读和幻读问题

• repeatable read 可重复度(mysql默认) 不会出现脏读和不可重复读，会出现幻读问题

• serializable 可序列化 不会出现脏读、不可重复读和幻读问题，效率最低

存储引擎

• MySQL5.5版本之前，默认使用MyIsam存储引擎，不支持事务

• MySQL5.5版本之后，默认使用InnoDB存储引擎，支持事务