数据和信息
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-- 满足触发条件后执行的内容-- endcreate trigger mytriggerafter insert on userinfo for each rowbegin 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存储殷勤，支持事务
