数据库总结

数据库

DataBase，简称为DB

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

数据永久保存在硬盘中。

数据库管理系统

DataBase Manager System，简称为DBMS

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

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

• 数据Data需要永久保存到数据库中
• 数据库DB是运行在操作系统上的一个软件
• 数据库管理系统DBMS是管理数据库的一个软件
• 学习数据库就是学习如何使用DBMS创建、使用数据仓库来管理数据

常见的数据库分类

关系型数据库

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

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

关系型数据库的特点

• 优点

  • 易于维护：都是使用表结构存储数据，格式一致
  • 使用方便：SQL语句通用，可用于不同关系型数据库
  • 复杂查询：可以通过SQL语句在多个表之间查询出复杂数据

• 缺点

  • 读写性能差，尤其是还是数据的高效读写
  • 固定的表结构，灵活度少欠
  • 高并发读写时，硬盘I/O决定了读写速度

非关系型数据库

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

非关系型数据库的特点

• 保存数据的格式多样
• 对于海量数据的读写性能高
• 不支持复杂查询

SQL

Structrued Query Language 结构化查询语言

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

MySQL中常见的数据类型

整型
tinyint	短整型	对应java中的byte和short
int	整型	对应java中的int
bigint	长整型	对应java中的long

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

字符串
char(长度)	定长字符串	char(10)表示占10个字符，就算保存了3个字符，也占10个字符，对应java中的String
varchar(长度)	可变字符串	varchar(10)表示最多占10个字符，保存了3个字符，旧占3个字符，对应java中的String
text	文本

日期
date	日期	yyyy-MM-dd
time	时间	HH:mm:ss
datetime	日期时间	yyyy-MM-dd HH:mm:ss
timestamp(14或8)	毫秒	保存日期的毫秒数.14表示yyyyMMddHHmmss，8表示yyyyMMdd

数据完整性

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

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

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

约束

字段特征	概念	关键字
非空约束	是否允许该字段为null	null表示可以为空，not null表示不可以为空
主键约束	主键(primary key)。用于区分表中的每条记录的一个字段。如果有现成的字段可以区分每条记录时，将该字段设置为主键字段;如果没有现成的字段可以区分每条记录时，通常会额外添加一个id字段用于设置为主键字段。通常一张表只有一个主键字段。	primary key
唯一约束	保证该字段值不能重复	unique
默认值约束	如果在不给该字段添加数据的情况下，保证该字段有一个默认值。	default
外键约束	在主从关系的表中，给从表的某个字段添加外键约束后，该字段的值只能来自于主表中的某个主键字段	foreign key references

查看信息

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

• 查看所有表：show tables;

• 查看表结构：desc 表名;

• 查看建表语言：show create table 表名;

数据库操作

• 创建数据库：create database 数据库名：

• 使用数据库：use 数据库名：

• 删除数据库：drop database 数据库名;

数据表操作

• 创建数据表

create table 表名(
    字段名 数据类型 [是否为空|唯一约束|主键约束|默认值约束|自动递增],
    字段名 数据类型 [是否为空|唯一约束|默认值约束],
    ...
    字段名 数据类型 [是否为空|唯一约束|默认值约束]
)

• 修改数据表

  • 表重命名：alter table 表名 rename to 新表名;

  • 添加新字段：alter table 表名 add column 字段名 数据类型 字段特征;

  • 修改字段：alter table 表名 change 旧字段 新字段 数据类型 字段特征;

  • 删除字段：alter table 表名 drop 字段名;

• 删除数据表：drop table 表名

    删除时如果有外键约束，先删除从表，再删除主表
  

• 添加约束

  • 添加主键约束：alter table 表名 add primary key(字段名)

  • 添加唯一约束：alter table 表名 add unique(字段名)

  • 添加默认值约束：alter table 表名 alter 字段名 set default ‘值’

  • 添加外键约束：alter table 从表 add foreign key(字段名) references 主表(主键字段)

• 删除约束：alter table 表名 drop [primary key|index/foreign key 约束名]

数据操作

• 增加

  • insert into 表名 values(值1,值2…);保证按表中字段顺序添加数据，不能缺少任何一个字段

    • 自增字段用null

    • 默认值字段用default

    • 允许为空字段用null

  • insert into 表名(字段1,字段2…) values(值1,值2…);保证必须写上非空字段，值的顺序和字段顺序保持一致

• 修改

  • update 表名 set 字段=值,字段=值 [where 条件]

• 删除

  • delete from 表名 [where 条件];会保留自增列的值

  • truncate table 表名;会重置自增列的值

• 查询

  • select [字段|*] from 表名 [where 条件] [order by 字段] [having 条件]

  • limit N：查询前N条记录

  • limit N,M：从索引为N的开始查询M条记录

  • distinct：去重复

  • order by 字段1,字段2：多字段排序

  • group by 字段名：根据字段分组

  • having 统计函数条件

常用函数

• 统计函数

  • sum()：求和

  • avg()：平均

  • count()：统计数量

  • max()：最大值

  • min()：最小值

• 字符串函数concat(‘值’,‘值’,字段,)：拼接值或字段

  • trim()：去除首尾空格

  • left(length)/right(length)：从左/右开始截取指定长度字符串

• 数学函数

  • round()/ceil()/floor()：取整

  • abs()：绝对值

• 时间函数

  • now()/curdate()curtime()：得到当前日期时间

  • year()/month()/day()：得到日期中的指定部

  • datediff()/timediff()/timestampdiff()：计算时间间隔

• 补充

  • if(条件,表达式1,表达式2)：条件为真，结果为表达式1，条件为假，结果为表达式2

  • group_concat()：拼接分组后的其他字段

连接查询

交叉连接、笛卡尔积

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

集合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

数据库设计

实体关系模型

ER图

• 矩形：实体

• 椭圆形：实体的属性

• 菱形：实体之间的关系

实体关系分类

• 一对一：一个国家有一个领导人

  • 1.根据两个实体创建两张表，都加上主键

  • 2.在其中一张表中添加一个字段，保存另一张表的主键并设置唯一

• 一对多/多对一：一个班级有多个学生，一个学生不能有多个班级

  • 1.先根据两个实体创建两张表，都加上主键

  • 2.在从表中添加外键字段管理主表中的主键字段

• 多对多：一个学生学习多门课程，一门课程可以被多个学生学习

  • 1.先根据两个实体创建两张表，都加上主键

  • 2.创建第三张"关系"表，在该表中添加两个字段分别保存两个实体表中的主键

数据库设计规范

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

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

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

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

第一范式1NF

字段不可再分

第二范式2NF

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

对于联合主键而言，所有非主属性字段必须完全依赖于主属性。

第三范式3NF

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

名词解释

主键/主码/主属性

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

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

联合主键

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

完全依赖

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

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

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

部分依赖

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

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

除了成绩外，其余字段都是部分依赖于(学号+科目)联合主键

2NF就是消除这种部分依赖，只保留完全依赖。

传递依赖

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

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

3NF就是消除这种传递依赖。

JDBC

Java Database Connectivity Java数据库连接

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

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

核心接口

• Connection:用于设置连接的数据库的地址、账号、密码
• PreparedStatement:用于预处理、执行sql语句
• ResultSet:用于接收查询后的数据

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

查询的步骤

1.获取连接对象Connection

2.构造sql语句String

3.预处理sql语句pst=conn.prepareStatement(sql)

4.给sql语句中的?赋值pst.setXXX(?顺序,值)

5.调用executeQuery()得到ResultSet结果集

6.遍历结果集rs.next()后rs.getXXX(字段顺序/字段名)

7.释放资源

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();
    }
}

增删改的步骤

1.获取连接对象Connection

2.构造sql语句String

3.预处理sql语句pst=conn.prepareStatement(sql)

4.给sql语句中的?赋值pst.setXXX(?顺序,值)

5.调用executeUpdate()得到受影响的行数

6.释放资源

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='亚索'
-- 这样会查询出所有数据

事务transaction

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

这个执行单元要么全部执行，要么全部不执行。

转账

1.select * from 表 where id =1 and money>=1000

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

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

以上的所有sql组成了一个转账的事务。

事务的特性ACID

原子性Atomicity

事务是最小的执行单元

一致性Consistency

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

隔离性Isolation

事务并发时相互隔离，互不影响

持久性Durability

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

事务并发可能出现的问题

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

事务并发出现的问题

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

事务隔离级别

为了防止事务并发时出现以上各种问题，数据库中设计了几种事务与事务之间的隔离级别。

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

事务相关指令

• 查看事务自动提交状态

  select @@autocommit

• 修改事务自动提交状态

  set @@autocommit=0/1 0表示不开启动，1表示开启

• 手动开启事务

  start transaction;

• 提交

  commit;

• 回滚

  rollback;

• 查询当前事务隔离级别

  select @@transaction_isolation

• 设置事务隔离级别

  set [session|global] transaction isolation level [Read Uncommitted | Read Committed |Repeatable Read |Serializable]

视图View

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

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

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

创建视图

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

使用视图

select * from 视图名

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

删除视图

drop view 视图名;

触发器trigger

如果要在更新某张表之前或之后，自动执行另一组sql时，可以使用触发器实现。

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

慎用触发器，因为如果有10000条记录，在修改所有记录时，触发器就会执行10000次，会花费很多时间。

创建触发器

create trigger 触发器名
触发时机 触发操作 on 表名 for each row
begin
    触发时执行的sql;
end

-- 创建操作日志表
create table log(
    log_id int not null primary key auto_increment,
    log_opt varchar(20) not null,
    log_time datetime not null
)

-- 创建触发器，在向客户表中添加记录后，自动向日志表中添加记录
create trigger mytrigger
after insert on customer for each row
begin
    insert into log values(null,'添加了数据',now())
end

使用触发器

一旦创建成功触发器，无需刻意调用，在执行相应的操作时，自动执行触发器

-- 只对customer表做插入操作，会自动向log表中添加记录
insert into customer values(null,'测试插入','123123',0,null);

删除触发器

drop trigger 触发器名;

存储过程procedure

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

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

存储过程可以有参数。

调用存储过程

-- 调用无参数的存储过程
call 存储过程名();
-- 调用输入型参数的存储过程
call 存储过程名('实参');
-- 调用输出型参数的存储过程
call 存储过程名(@变量);
-- 调用输入输出型参数的存储过程
set @变量
call 存储过程名(@变量)

定义存储过程

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

无参数

create procedure 存储过程名()
begin 
    sql语句
end

-- 创建存储过程，查询每本图书的书名、作者、类型
CREATE PROCEDURE myproc1 () BEGIN
    SELECT
        book_name,
        book_author,
        type_name 
    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,
        book_type bt 
    WHERE
        bi.type_id = bt.type_id 
        AND type_name = lx;
END

-- 调用
call myproc2('杂志')

输出型参数

类似于java中有返回值的方法

create procedure 存储过程名(out 形参名 数据类型)
begin 
    sql语句;
    -- 通常需要将查询出的结果通过into赋值给形参
end

-- 根据作者查询图书数量
CREATE PROCEDURE myproc3 ( IN zz VARCHAR (20), OUT book_count INT ) 
BEGIN
-- 将查询的结果into到参数book_count中
    SELECT
        count( book_id ) INTO book_count 
    FROM
        book_info 
    WHERE
        book_author = zz;

    END
-- 调用存储过程,@x表示将存储过程的输出型参数保存到变量x中
call myproc3('金庸',@x)
-- 查询参数中保存的数据
select @x

输入输出型参数

create procedure 存储过程名(inout 形参名 数据类型)
begin 
    sql语句;
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;

删除存储过程

drop procedure 存储过程名;

总结

前端是用以展示数据，后端处理数据，得到有用的信息，而数据库则保存数据。

在开发过程中，三者缺一不可，所以数据库是非常重要的。

以前在学校学得不明白的地方，这次基本都学懂，学透了，老师讲的很详细，也很好理解。

不过还得在课下多多练习，相关操作得熟练掌握。前路任重而道远。