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

数据库DateBase

简称DB，是结构化管理数据的仓库，称为电子化的文件柜。

数据库管理系统DateBase Manager System

简称DBMS，安装在操作系统上，管理数据库的软件

数据库的分类

关系型数据库

• SQLServer
• MySQL
• Oracle

非关系型数据库nosql

• Redis

SQL语句

在关系型数据库中，用来操作数据库的结构化查询语言。

数据库的详细介绍

数据库的操作

查看信息

• 查看所有库：show databases;
• 查看所有表：show tables;
• 查看表结构：desc 表名;
• 查看建表语句：show create table 表名;

数据库操作命令

• 创建数据库：create database 数据库名;
• 使用数据库：use 数据库名;
• 删除数据库：drop 数据库名;

数据表操作

数据类型

常用数据类型
int	整型
double	浮点型
char(长度)	定长字符串
varchar(长度)	可变字符串
text	文本
date	日期
time	时间
datetime	日期时间
timestamp	毫秒

数据完整性

数据完整性指数据精确可靠。

完整性约束	关键字	作用
非空约束	not null	保证字段值不为空
主键约束	primary key	保证字段值不重复，用于唯一区分每条记录
唯一约束(索引)	unique	保证字段值不重复
默认值约束	default	保证在没有填充数据时，自动使用默认值填充
外键约束	foreign key references	保证从表中的记录只能来自于主表

• 创建数据表

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

• 修改数据表

  • 表重命名：alter table 表名 rename ti 新表名;
  • 添加新字段：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 条件]

• 删除

  • delect 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 统计函数条件去

条件	符号
指定值	=、!=、<>
指定范围	>、<、>=、<=、between 值 and 值
指定集合	[not] in (元素1,元素2…)
模糊查询(%表示位置长度字符串，_表示一个字符)	like ‘%值_’
空值	is [not] null
多条件	使用 and、or、&、|串联条件

常用函数

• 统计函数
  • 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；
  • group_concat()：拼接分组后的其他字段

嵌套查询

select * from 表 where 字段=(select * from 表 where 条件);

select * from 表,(select * from 表) t where 表1.字段=t.字段;

多表查询

• 交叉查询/笛卡尔积

  select * from 表1，表2...;
  
  select * from 表1 cross/inner join 表2;
  

• 内连接

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

• 外连接

  • 左外连接

    select * from 表1 left join 表2 on 表1.字段=表2.字段;-- 保证表1中的数据完整
    

  • 右外连接

    select * from 表1 right join 表2 on 表1.字段=表2.字段;-- 保证表2中的数据完整
    

操作数据的详细介绍

JDBC

Java提供了一套规范用于连接各种数据库。不同的数据库厂商根据该规范设计连接驱动。

java.sql包下一组接口用于操作数据库。

• Connection接口：用于获取连接对象
• PreparedStatement接口：用于发送sql语句，处理sql语句
• ResultSet接口：用于保存查询后的结果集

数据库连接详解及举例

数据库设计

实体关系模型

ER图

• 矩形：实体
• 椭圆形：实体的属性
• 菱形：实体之间的练习

实体关系分类

• 一对一：一个国家有一个领导人
  • 1、根据两个实体创建两张表，都加上主键
  • 2、其中一张表中添加一个字段，保存另一张表的主键并设置唯一
• 一对多/多对一：一个班级有多个班级，一个学生不能有多个班级
  • 1、先根据两个实体创建两张表，都加上主键
  • 2、在从表中添加外键字段管理主表中的主键字段
• 多对多：一个学生学习多门课程，一门课程可以被多个学生学习
  • 1、先根据两个实体创建两张表，都加上主键
  • 2、创建第三张"关系"表，在该表中添加两个字段分别保存两个实体表中的主键

范式

第一范式1NF

字段不可再分

第二范式2NF

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

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

第三范式3NF

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

实体关系模型详细介绍

数据库补充知识

事务transaction

一组sql执行单元，要么全部执行，要么全部不执行

事物的特性ACID

原子性Atomicity

事务是最小的执行单元

一致性Consistency

事务执行前后，数据整体保持一致

隔离性Isolation

各个事物之间应当互不干涉

持久性Durability

事物一旦提交，改变是永久的

事务并发出现的问题

脏读

事务A读取到了事务B未提交的事务

不可重复读

事务A中前后两次读取到的数据不一致。

事务A在读取过程中，事务B对数据进行了修改。

幻读

事务A在读取过程中，事务B向其中添加了数据，导致事务A读到了事务B中添加的"幻影"数据。

以上问题可以通过设置事务隔离级别解决

事务隔离级别

隔离级别	是否会出现脏读	是否会出现不可重复读	是否会出现幻读
Read Uncommitted未提交度	会	会	会
Read Committed已提交读	不会	会	会
Repeatable Read可重复读(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]

视图

定义

create view 视图名 as sql语句;

使用

select * from 视图名;

触发器

定义

create trigger 触发器名

触发时机 触发条件 on 表 for each row

begin

​ 触发器触发时执行的sql语句;

end

存储过程

创建

create procedure 存储过程名(in/out/inout 参数名 数据类型)

begin

​ sql语句

end

调用

call 存储过程名()

MySQL编程

数据库补充知识的详细介绍，如：行列转换、视图、事务、触发器、存储过程、MySQL编程等