华清远见-重庆中心-数据库阶段知识点梳理/个人总结

数据和信息

Data数据

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

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

Infomation信息

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

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

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

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

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

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

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

数据库

DataBase，简称为DB

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

数据永久保存在硬盘中。

数据库管理系统

DataBase Manager System，简称为DBMS

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

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

总结

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

SQL

Structrued Query Language 结构化查询语言

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

操作数据库

1.创建数据库

create database 数据库名

2.切换数据库

use 数据库名

3.删除数据库

drop database 数据库名

操作数据表

1.创建数据表

create table 表名(
    字段名 数据类型 [字段特征]
    字段名 数据类型 [字段特征]
)

2.删除数据表

drop table 表名;

3.修改数据表

• 对表重命名

  alter table 旧表名 rename to 新表名;
  

• 添加新字段

  alter table 表名 add colum 字段名 数据类型 字段特征;
  

• 修改字段

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

• 删除字段

  alter table 表名 drop 字段名;
  

添加约束

1.添加非空约束

alter table 表名 change 旧字段名 新字段名 数据类型 not null;

2.添加主键约束

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

3.添加唯一约束

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

4.添加默认值约束

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

5.添加外键约束

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

添加约束的操作通常是对已存在的表进行修改和维护时使用。如果是一张新表，最好在创建表的时候设计好约束。

数据完整性

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

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

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

MySQL中常见的数据类型

整型
tinyint	短整型	对应java中的byte和short
int	整型	对应java中的int
bidint	长整型	对应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字段用于设置为主键字段。通常一张表只有一个主键字段。	primarykey
唯一约束	保证该字段值不能重复	unique
默认值约束	如果在不给该字段添加数据的情况下，保证该字段有一个默认值。	default
外键约束	在主从关系的表中，给从表的某个字段添加外键约束后，该字段的值只能来自于主表中的某个主键字段	foreign key references

操作数据

数据的操作，是指数据的增加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'...)
  

空值

• 使用is null表示空

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

• 使用is not null表示非空

  update 表名 set 字段 = '值' where 字段 is not null
  

模糊查询

update 表名 set 字段 = '值' where 字段 like ‘%文字%’

数据删除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 表名;

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

数学相关函数

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

时间相关函数

函数
now()	当前日期时间
current_date()/curdate()	当前日期
current_time()/curtime()	当前时间
year(日期)/month(日期)/day(日期)	得到年/月/日部分
datediff(时间1,时间2)	计算时间1与时间2相隔的天数
timediff(时间1,时间2)	计算时间1与时间2相隔的时分秒
TIMESTAMPDIFF(时间单位,时间1,时间2)	计算时间1与时间2之间相隔的指定时间单位

分组

select 分组字段,统计函数 from 表名 group by 分组字段

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

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

如果统计函数作为条件，不能写在where之后，要写在having之后，

having子句放在分组之后。

group_concat()函数

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

group_concat(字段1或字符串,字段2或字符串...)
select group_concat(字段1,字段2...) from 表名 group by 分组字段

连接查询

交叉连接、笛卡尔积

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

集合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.字段;

• 通常是通过主表的主键字段关联从表的外键字段
• 如果两张表中关联的字段名一致，一定要通过"表名.字段名"进行区分，通常给表重命名
• 如果使用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 表)

JDBC

Java Database Connectivity Java数据库连接

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

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

核心接口

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

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

使用数据库简化JDBC操作

package com.hqyj.util;

import java.sql.*;

/*
 * 数据库工具类
 * 连接方法
 * 释放资源方法
 * */
public class DBUtil {

    //连接数据库所需字符串
    private static String url = "jdbc:mysql://localhost:3306/数据库名?serverTimezone=Asia/Shanghai";
    private static String username = "数据库账号";
    private static String password = "数据库密码";

    /*
     * 静态代码块
     * 加载驱动
     * */
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动异常" + e);
        }
    }

    /*
     * 静态方法
     * 获取连接
     * */
    public static Connection getConn() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            System.out.println("连接数据库异常" + e);
        }
        return connection;
    }

    /*
     * 静态方法
     * 释放资源
     * */
    public static void release(Connection conn, PreparedStatement pst, ResultSet rs) {
        try {
            if(conn!=null){
                conn.close();
            }
            if(pst!=null){
                pst.close();
            }
            if(rs!=null){
                rs.close();
            }
        }catch (SQLException e){
            System.out.println("释放资源异常"+e);
        }
    }
}

实体关系模型

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

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

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

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

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

实体之间的关系

一对一

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

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

一对多/多对一

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

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

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

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

多对多

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

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

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

数据库设计规范

第一范式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就是消除这种传递依赖。

视图View

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

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

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

创建视图

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

使用视图

select * from 视图名

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

删除视图

drop view 视图名;

事务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

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

事务的使用

提交：commit

回滚：rollback

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

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

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

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

如果不出错，通过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已提交读RC(Oracle默认)	不会	会	会
Repeatable Read可重复读RR(MySQL默认)	不会	不会	会
Serializable可序列化	不会	不会	不会

查看事务隔离级别

select @@transatcion_isolation

设置事务隔离级别

set [session|global] transaction isolation level [read uncommitted|read committed|repeatable read|serializable]

触发器trigger

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

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

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

创建触发器

create trigger 触发器名
触发时机 触发操作 on 表名 for each row
begin
	触发时执行的sql;
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 存储过程名(in 形参名 数据类型)
begin 
	sql语句;
end

输出型参数

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

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

输入输出型参数

create procedure 存储过程名(inout 形参名 数据类型)
begin 
	sql语句;
end

删除存储过程

drop procedure 存储过程名;

MySQL编程

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

定义变量

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

给变量赋值

create procedure 存储过程名()
begin 
	declare num int;
	declare name varchar(20);
	-- 给num赋值 
	-- select 字段/值 into 变量 [from 表];
	select 123 into num;
	select book_name into name from book_info where book_id=1;
end

读取变量的值

create procedure 存储过程名()
begin 
	declare num int;
	declare name varchar(20);
	select 123 into num;
	select book_name into name from book_info where book_id=1;
	-- select 变量;
	select num;
	select name;
end

条件语句

单分支if语句

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

双分支if语句

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

case语句

CASE 变量
	WHEN 值 THEN
		满足该值时执行sql语句;
	WHEN 值 THEN
		满足该值时执行sql语句;
	ELSE
		没有任何值满足时sql语句;
END CASE;

循环语句

while循环

while 条件 do
	满足条件时执行的内容;
end while;

repeat循环

repeat
	循环体;
until 条件 end repeat;

loop循环

循环名:loop
	循环体;
	if 条件 then leave 循环名;
	end if;
end loop;