数据库基本内容

数据和信息

Data 数据

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

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

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

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

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

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

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

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

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

数据库

DataBase , 称为数据库，简称 DB

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

数据永久保存在硬盘中。

数据库管理系统

DataBase Manager System ，简称 DBMS

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

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

总结

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

常见的数据库管理系统

关系型数据库

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

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

每 行 称为一条 记录 。

每 列 称为一个 字段 。

学号	姓名	专业
1001	小王	计算机
1002	小李	计算机
1003	小赵	计算机

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

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

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

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

 

• 优点
  • 易于维护：数据都是以表的结构存储，格式一致
  • 使用方便：SQL语句通用，可用于不同的关系型数据库
  • 支持复杂查询：可以通过SQL语句在多个表之间查询出复杂数据
• 缺点
  • 读写性能差，在处理海量数据时， 频繁读写效率略低
  • 表结构不易改动，灵活度稍欠佳
  • 高并发读写时，硬盘I/O决定了读写速度

非关系型数据库

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

 

非关系型数据库的特点

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

MySQL8.x的安装和使用

1.下载

mysql官网https://www.mysql.com/cn/

 

2.安装 

 

 3.使用

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

Linux 服务器上，无法使用图形界面。

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

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

 

常用指令

• show databases; 查看所有数据库
• use 数据库名; 切换到指定数据库
• create database 数据库名; 创建指定数据库
• drop database 数据库名; 删除指定数据库show tables; 在某个数据库中，查看其中的所有表

...

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

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

如 navicat 、 datagrip 、 sqlyog 等。

Navicat15软件的破解

安装 Navicat 时，最好不要修改路径，默认路径按照后，无需打开。直接打开破解工具

1. 点击 Patch ，等待弹出

2.生成注册码，手动激活码，粘贴后手动激活

 

3.复制手动激活中的内容，粘贴请求码

 

 

Navicat的使用 

连接mysql

连接后双击进入连接

 

 

创建数据库

在连接上右键，新建数据库，只需填写数据库名 

切换数据库

只需双击对应的数据库

创建数据表 

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

 

保存时输入表名

• 每张表需要一个编号"id"列，通常设置为主键，目的是为了区分每条记录。主键列中的数据不能重复，通常还需将主键列设置为自增列。
• 由于mysql中大小写不敏感，字段名使用全部小写字母，多个单词用_隔开
• 数据类型和所占长度根据实际情况选择
• 如果某列数据必须要填写，将"不是null"勾选
• 如果某个字段有默认值，可以在设计表的时候设置，字符串用单引号引起来
• 最好加上注释

SQL

Structrued Query Language 结构化查询语言

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

--mysql中的注释

操作数据库

创建数据库

create database 数据库名 ;

切换数据库

use 数据库名 ;

删除数据库

drop database 数据库名 ;

操作数据表

创建数据表

create table 表名(

字段名1 数据类型 [字段特征],

字段名 2 数据类型 [ 字段特征 ],
...

字段名 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 ' 默认值 ' ;

• 添加外键约束

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

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

建表的同时添加约束

-- 创建游戏数据库 gamedb

create database gamedb;

-- 切换到 gamedb

use gamedb;

-- 创建游戏人物表 hero

create table hero(

-- 编号 id 主键 非空 自增

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

-- 姓名 唯一 非空

name varchar ( 20 ) unique not null ,

-- 性别 默认男 非空

sex char ( 1 ) not null default ' 男 ' ,

-- 价格 非空

price int not null ,

-- 发布时间 可以为空

make_time date

)

-- 添加 " 定位 "position 字段

alter table hero add column position varchar ( 20 )

-- 创建战斗表 battle

create table battle(

-- id ，主键字段

id int not null primary key auto_increment,

-- 出战人物编号 hero_id, 来自于 hero 表中的 id 字段 非空

hero_id int not null ,

-- 昵称 可以为空

nick_name varchar ( 20 ),

-- 分路 不能为空

way varchar ( 20 ) not null ,

-- 设置外键

foreign key(hero_id) REFERENCES hero (id)

)

删除数据表

drop table 表名

如果要删除有外键关联的表，要先删除从表，再删除主表。

数据完整性

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

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

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

数据的完整性

MySQL中常见的数据类型

整形
tinyint	对应Java中的short	短整型
int	对应Java中的int	整形
bigint	对应Java中的long	长整型

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

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

 

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

约束

约束/字段特征	关键字	概念
非空约束	不写或写null表示允许为空，not null表示不能为空	是否允许该字段为Null
主键约束	primary key	主键(primary key)。也可以称为主关键字、主码。用于区分表中的每条记录的一个字段。如果有现成的字段可以区分每条记录时，将该字段设置为主键字段；如果没有现成的字段可以区分每条记录时，通常会额外添加一个id字段设置为主键。通常一张表只有一个主键。
唯一约束	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 字段 = '值',字段 = '值'...;

根据条件修改(where子句)

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

指定值

使用=指定某个字段值

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

指定范围

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

• 使用“字段between A and B”表示字段在[A,B]范围内

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

指定集合

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

• 某个字段的值不在某个集合中时 字段not in('值1'，值2')

空值匹配

• 某个字段为空：字段is null

• 某个字段不为空：字段is not null

模糊查询

• %表示未知长度字符串

• _表示一个字符

-- 字母 like '%琳%'
-- 带有'琳'字

-- 字段like '张%'
-- 姓'张'

-- 字段 like '%儿'
-- 以"儿'结尾

-- 字段 like '__'
-- 两个字

-- 字段 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 * from 表名 where 条件;

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

排序

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

• 排序默认是升序

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

  • 排序规则写desc表示降序

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

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

统计函数(聚合函数)

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

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

数学相关函数

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

字符串相关函数

函数名	作用
length(字符串或字段)	得到字符串长度
trim(字符串或字段)/ltrim(字符串或字段)/rtrim(字符串或字段)	去首尾/首/尾空格
substr(字符串或字段，start)	从start开始截取字符串到末尾
substr(字符串或字段，strat，len)	从start开始截取len个字符串
left(字符串或字段,len)/right(字符串或字段,len)	从首/尾开始截取len个字符
lcase(字符串或字段)/ucase(字符串或字段)	转换为小写/大写
recerse(字符串或字段)	反转字符串
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)

分组

select 分组字段，统计函数 from 表名 group by 分组字段 having 统计函数条件

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

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

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

group_concat(函数)

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

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

实际业务对应SQL操作

登录

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

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

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

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

注册

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

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

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

充值

充值、消息都是将数据进行更新，

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

create table userinfo(
    userid int not null primary key auto_increment,
    username 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','123123',0);
​
--登录
select * from userinfo where username = 'admin' and password ='123123';
​
-- 充值
-- 获取注册时自动生成的id
update userinfo set balance = balance + 100 where userid = 1

删除

逻辑删除

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

用户名	电话	地址	是否删除0-未删除 1-已删除
小赵	123123123	十八弯	0
小李	124*561	十八弯	0
小王	24145132	十八弯	0

查询所有未删除的数据

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

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

update 用户表 set 是否删除 = 1 where 小王编号 = 值

物理删除

真实删除

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

数据库设计

实体关系模型

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

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

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

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

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

实体之间的关系

一对一

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

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

ER图

 

在数据库中创建表的过程

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

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

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

最好使用两张表实现

• 国家表

  create table country(
      country_id int not null primary key auto_increment,
      country_name varchar(50) not null,
      country_population 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(20) 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(20) 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_increnment,
      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 country(leader_id)
  )

一对多／多对一

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

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

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

如多个学员对应一个训练他们的教官，多个教官不能对应一个学员。

ER图

在数据库中创建表的过程

• 创建主表(一)

  create table coach(
      coach_id int not null primary key auto_increment,
      coach_name varchar(20) not null,
      coach_leavl varchar(20) not null
  )

• 创建从表(多)，添加外键字段关联主表中的主键字段

  create table student(
      stu_id int not null primary key auto_increment,
      stu_name varchar(20) not null,
      stu_phone varchar(20).
      coach_id int not null,
      foreign key (coach_id) references coach(coach_id)
  )
  

多对多

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

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

如一个医生可以有多个病人，一个病人可以看多个医生。

ER图

 

在数据库中创建的过程

• 创建学生表

  create table student(
      stu_id int not null primary key auto_increment,
      stu_bane varchar(20) not null,
      stu_phone varchar(20)
  )

• 创建课程表

  create table course(
      course_id int not null primary key auto_increment,
      course_name varchar(20) not null,
      course_score int not null
  )

• 体现多对多关系的表：成绩表

  create table score(
      score_id not null primary key auto_increment,
      stu_id int not null,
      course_id int not null,
      --可以选择添加外键约束
      foreign key (stu_id)references student(stu_id),
      foreign key (course_id)references course(course_id)
  )

  

   

总结：

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

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

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

数据库设计规范

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

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

六种。

这六种范式的级别越高 ( 第一范式到第五范式 ) ，表示数据库设计的结构越规范。每一个高等级的范式都包

含了低等级的范式。

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

如有该原始表。

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

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

• 如果要加入一个新的系别，就要添加学生、系主任
• 如果要删除"刘定宇"，他所在的系别也会被删除
• 如要将"王海"的系别改为会计，相应的系主任也要修改
• 当前表中有大量重复冗余数据

第一范式1NF

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

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

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

第二范式2NF

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

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

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

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

 

 

第三范式3NF

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

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

 

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

 

名词解释

主键/主码/主属性

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

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

联合主键

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

完全依赖

如果能通过 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隔离性

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

Durabillity持久性

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

事务的使用

提交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，一旦提交，不能回滚

-- 查看事务自动提交状态
select @@autocommit
-- 关闭事务自动提交
set @@autocommit = 0
​
-- 开启事务
start TRANSACTION
-- 模拟id = 1 转账给id = 2 2000
-- 查询
select * from user where id = 1 and money >= 2000;
-- 扣钱
update user set money = money - 2000 where id = 1;
sdghjl
-- 转钱
update user set monry = monry + 2000 where id = 2;
​
​
-- 遇到问题及时回滚
rollback
​
-- 确认无误后，使用commit提交，一旦提交无法回滚
commit;

事务并发可能出现的问题

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

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

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

事务隔离级别

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

查看事务隔离级别

select @@transaction_isolation

设置事务隔离急别

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

触发器trigger

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

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

慎用触发器，因为触发器对于每行都生效，数据多时，触发器也要执行很多次，影响效率。

-- 用户表
create table userinfo(
    id int not null primary key auto_increment,
    username varchar(20) not null,
    password varchar(20) not null
)
-- 日志表
create table log(
    id int not null primary key auto_increment,
    content varchar(255) not null,
    time datetime not null
)

创建触发器

-- 语法
-- create trigger 触发器名
-- 触发时机(before/after) 触发操作(update/insert/delete) on 表 for each row
-- begin
-- 满足触发条件后执行的内容
-- end
​
create trigger mytrigger
after insert on userinfo for each row
begin
    insert int log values(null,'执行了添加操作',now());
end

使用触发器

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

-- 在执行这句话之后，就会自动在log表中添加一条记录
insert into userinfo values(null,'admin','123123');

删除触发器

drop trigger 触发器名;

存储过程procedure

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

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

调用存储过程

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

定义存储过程

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

定义无参数的存储过程

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

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

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

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

输出型参数类似于有返回值的方法

create procedure 存储过程名(out 参数名 数据类型)
begin
    sql语句;
    -- 通常需要将查询后的结果保存到输出参数中
    -- 通过“select 值或参数 into 参数 ”赋值；
end

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

create procedure 存储过程名(inout 参数名 数据类型)
begin
    sql语句;
    -- 通常需要将查询后的结果保存到输出参数中
    -- 通过“select 值或参数 into 参数 ”赋值；
end

删除存储过程

drop procedure 存储过程名

MySQL编程

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

定义变量

create procedure 存储过程名 ()

begin

-- declare 变量名 数据类型 ;

declare num int ;

declare name varchar ( 20 );

end

给变量赋值

create procedure 存储过程名 ()

begin

-- declare 变量名 数据类型 ;

declare num int ;

declare name varchar ( 20 );

-- 给 num 和 name 赋值

-- select 字段 / 值 into 变量 [from 表 ];

select 123 into num;

select 'hello' into name;

select book_author into name from book_info ;

end

读取变量的值

create procedure 存储过程名 ()

begin

-- declare 变量名 数据类型 ;

declare num int ;

declare name varchar ( 20 );

-- 给 num 和 name 赋值

-- select 字段 / 值 into 变量 [from 表 ];

select 123 into num;

select 'hello' into name;

select book_author into name from book_info ;

-- select 变量名

select num;

select name;

end

-- 创建存储过程，查询所有图书库存总数，保存到变量中

CREATE PROCEDURE myproc5 ()

BEGIN

-- 定义变量，保存查询到的数据

DECLARE num INT ;

-- 执行查询，将结果赋值给变量

SELECT

sum( book_num ) INTO num

FROM

book_info;

-- 输出变量的值

SELECT num;

END

-- 调用存储过程

call myproc5()

条件语句

单分支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( ' 小说 ' )

case语句

case 变量

when 值 then

变量值为该值时执行的 sql 语句 ;

when 值 then

变量值为该值时执行的 sql 语句 ;

else

没有任何值满足时执行的 sql 语句 ;

end case;

-- 输入类型编号，输出对应类型名称，没有输出不存在

create PROCEDURE myproc8( in id int )

begin

declare type varchar ( 20 );

select type_name into type from book_type where type_id=id;

case type

when ' 小说 ' then

select concat(id, '--' , ' 小说 ' );

when ' 漫画 ' then

select concat(id, '--' , ' 漫画 ' );

when ' 杂志 ' then

select concat(id, '--' , ' 杂志 ' );

else

select concat(id, ' 不存在 ' );

end case;

end

call myproc8( 2 )

循环语句

while循环

while 循环条件 do

循环体 ;

end while;

-- 添加 50 本图书

create PROCEDURE myproc9()

begin

-- 定义循环变量

declare n int ;

-- 循环变量初始 1

select 1 into n;

-- 循环

while n<= 50 do

-- 循环添加，同时更新循环变量的值

insert into book_info values ( null , 4 ,concat( ' 测试 ' ,n), ' 测试作

者 ' , 50 , null , null , 100 );

set n = n + 1 ;

end while;

end

call myproc9()

repeat循环

repeat

循环体 ;

until 条件 end repeat;

-- 添加 50 本图书

create PROCEDURE myproc10()

begin

DECLARE n int ;

select 51 into n;

repeat

insert into book_info values ( null , 4 ,concat( ' 测试 ' ,n), ' 测试作

者 ' , 50 , null , null , 100 );

set n = n + 1 ;

until n= 100 end repeat;

end

call myproc10

loop循环

循环名 :loop

循环体 ;

if 循环条件 then

leave 循环名 ;

end if;

end loop 循环名 ;

-- 添加 50 本图书

create PROCEDURE myproc11()

begin

DECLARE n int ;

select 100 into n;

myloop:loop

insert into book_info values ( null , 4 ,concat( ' 测试 ' ,n), ' 测试作

者 ' , 50 , null , null , 100 );

set n = n + 1 ;

if n= 150 then

leave myloop;

end if;

end loop myloop;

end

call myproc11()

MySQL核心内容

SQL语句

• 建库、建表

• 修改表结构

• 数据完整性(约束)

• 单表的增删改查CURD

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

  • 修改：update 表 字段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 = "root";
  String URL = "jdbc:mysql://loclhost:3306/数据库名?serverTimezone=Asia/Shanghai";

事务

• 事务的概念和特性

  • A原子性

  • C一致性

  • I隔离性

  • D持久性

• 事务并发时出现的问题

  • 脏读

  • 不可重复读

  • 幻读

• 事务的隔离级别

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

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

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

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

存储引擎

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

• MySQL5.5版本之后，默认使用lnnoDB存储殷勤，支持事务