devbox
IT小白
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

数据库知识的整理（简单入门+基础实操）_数据库应知应会

作者：IT小白 | 2024-04-23 19:26:21

赞

踩

数据库应知应会

DB分类

1.关系型数据库（支持sql（结构化语言））

mysql(3306)
oracle（1521）
sqlserver（1433）
db2（）

  • 关系：
    ①字段间有关系
    ②数据间有关系
    ③表间有关系

2.非关系型数据库

redis

3.层次结构数据库

4.网状数据库

dos命令的数据库

DDL命令：数据库定义语言
手动开启服务器：计算机管理-服务-管理

登录数据库： mysql -uroot -p  
显示所有数据库： show databases;
进入特定数据库：use 数据库名;
查看特定数据库的所有表：show tables;
查看表中内容:select * from 表名;
创建表和内容：create table 表名(
			字段名 字段类型（字段长度),
            字段名 字段类型（字段长度));

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

DB结构

库
​ ——表
​ ——数据（表格形式）
​ ——行（记录/数据） 列（字段）

信息孤岛

数据库三范式

①字段不可分割（一个字段只能表达一个意思）
②记录不可重复
③一个表只能表示一个实体

eg：user表只记录user的个人信息，show表记录演出时间和人员间的关系

建库
建表:
命名

驼峰式命名/tb_表名/下划线表示法

三键四约束

1.主键

(标识一条数据的唯一性；字段不能重复且不能为空)

SQL语句：
create table cat
(	id int(11),
	name varchar(2));
	
	CREATE TABLE cat1(
	id int(11) PRIMARY KEY, //设置主键
	name VARCHAR(5) NOT NULL//设置非空，非空约束
	);
	CREATE TABLE tb_empl(
	id int(11) PRIMARY KEY,-- 设置主键
	name VARCHAR(25),
	deptId int(11),
	salary FLOAT,
  --设置外键：记得加fk_
	CONSTRAINT fk_tb_emp1 FOREIGN KEY(deptId) REFERENCES tb_empl_1(id) 
);
CREATE TABLE tb_empl_1(
	id int(11),
	name VARCHAR(25),
	deptId int(11),
	salary FLOAT,
	PRIMARY KEY(id)-- 设置主键
);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

2.外键

  • A表的某个字段值必须来源于B表的主键值，A表的字段就是外键（此时B表的主键值不能删除）
  • 在建表leave的初始设置外键；
  • 主键才能做别的表的外键，才能被别的表依赖
法一

在这里插入图片描述在这里插入图片描述```sql

CREATE TABLE tb_empl(
	id int(11) PRIMARY KEY,-- 设置主键
	name VARCHAR(25),
	deptId int(11),
	salary FLOAT,
  --设置外键：记得加fk_
	CONSTRAINT fk_tb_emp1 FOREIGN KEY(deptId) REFERENCES tb_empl_1(id) 
);
CREATE TABLE salary(
salaryID int PRIMARY KEY Auto_INCREMENT,-- 设置主键且自增，工资编号
EmployeeID VARCHAR(6) not NULL, -- 记得varchar写长度，非空且外键，员工编号
PositionID VARCHAR(3) not NULL, -- 非空且外键，职位编号
Wage int not NULL DEFAULT 0, -- 设置默认约束且非空，工资
CONSTRAINT fk_salary FOREIGN KEY(EmployeeID) REFERENCES employee(EmployeeID), FOREIGN KEY(PositionID) REFERENCES PositionMessage(PosID) -- 设置外键时只需要写一次该表
);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

3.联合主键

（主键的任一键可以相同，但不能同时相同）
联合主键是多个键合成一个

主键的两种
CREATE TABLE tb_empl(
  id int(11) PRIMARY KEY,-- 设置主键
  name VARCHAR(25),
  deptId int(11),
  salary FLOAT
);
CREATE TABLE tb_empl_1(
  id int(11),-- 设置主键
  name VARCHAR(25),
  deptId int(11),
  salary FLOAT,
  PRIMARY KEY(id,name)-- 设置联合主键
);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

在这里插入图片描述

4.1非空约束

(不是null)：字段不可为空

CREATE TABLE tb_emp2(
	id int(11),
	name VARCHAR(25),
	deptId int(11) not NULL,-- 设置非空约束
	salary FLOAT,
	PRIMARY KEY(id)-- 设置联合主键
);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

4.2唯一约束

字段不能重复但可以为空

  • 唯一索引不能出现多个null值，即只能有1个null值
  • 主键与唯一约束的区别
    ①主键不可为null，唯一约束可以允许一个值为nu(存疑)ll；
    ②主键默认是唯一索引，但唯一索引不一定是主键
    ③主键若为int型，可设自增约束；但唯一不行
    ④主键可作为另一张表的外键来源，
    ⑤唯一约束可以有多个，主键只有一个
CREATE TABLE tb_emp2(
	id int(11),
	name VARCHAR(25) UNIQUE,-- 设置唯一约束
	deptId int(11) not NULL,-- 设置非空约束
	salary FLOAT,
	PRIMARY KEY(id)-- 设置联合主键
);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

索引

（数据库的目录：提高检索效率；占用空间，需要维护；）
1)唯一索引：表的目录
2)全文索引
3）普通索引
4）联合索引

4.3自增约束

（字段得是int型且为主键）
id进行自增约束

4.4默认约束

varchar：（得加英式的单引号‘’）当sex没有被人为赋值，即默认为’男’

CREATE TABLE tb_emp2(
	id int(11) PRIMARY KEY Auto_INCREMENT,-- 设置自增约束
	name VARCHAR(25) UNIQUE, -- 唯一约束
	deptId int(11) not NULL DEFAULT 30,-- 设置非空和默认约束
	salary FLOAT
);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

字段类型

①int类型：可不写长度
②小数类型：float、double；要写整数长度和小数长度
③char(字符类型):必须提前规定长度；
eg：当规定5长度，但实际只用了1，就会浪费空间
④varchar（可变字符类型），必须规定长度
但根据实际情况变化使用空间——一般用于name（因名字长度不一定）
⑤date（日期类型）：年月日 ，长度不用写
⑥datetime（日期时间类型）：年月日时分秒
⑦text（大文本类型）
id（一般为主键），name，date不可为空

SQL语言

数据持久化

把数据存储到磁盘上；
mysql的数据都是持久化的；redis的数据存在内存空间

SQL语言

漂号：``,起区分作用
navicat中快捷键：ctrl+“++” /鼠标左键+滚动滑轮

导入表

表——导入向导——Excel文件（xls）——选择要导入的xls文件——下一步——复制——下一步——开始

1.数据定义语言DDL

对库和表进行操作

定义库

查看数据库：show databases;
切换数据库：use 库名;
建库:create database 库名;
删库:drop database 库名;

  • 1
  • 2
  • 3
  • 4

定义表

建表：create table 表名(字段 数据类型(长度) 约束···,字段 数据类型，···,foreign key(外键名) references 另一张表的(主键名));
查看所有表:show tables;
查看表中字段信息:desc 表名;
新增表字段：alter table 表名 add 字段 字段类型;
修改字段数据类型：alter table 表名 modify 字段 字段类型;
删除字段：alter table 表名 drop 字段;
更改表名:rename table 表名 to 新表名;
修改字段名称:alter table 表名 change 旧字段 新字段 数据类型;
删除表：drop table 表名;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

2.数据操作语言DML（curd）

操作数据:增、删、改、查curd >Affected rows:1受影响条数

增加

  • 字段值与字段名一一对应
  • default：
//①
insert into `表名`(字段名,字段名,字段名) values(字段内容，字段内容); 
INSERT into boys (id,`boyName`,userCP) VALUES(5,'张云雷',777);
//②当主键自增
INSERT into boys (id,`boyName`,userCP) VALUES(default,'张云雷',777);//default
INSERT into boys (`boyName`,userCP) VALUES('张云雷',777);//省略自增主键
//③当默认自增
INSERT into boys (id,`boyName`,userCP) VALUES(5,default,777);
INSERT into boys (userCP) VALUES(777); //可省略默认
//④当值可为null
INSERT into boys (id,`boyName`,userCP) VALUES(5,'张云雷',default);
INSERT into boys (id,`boyName`) VALUES(5,'张云雷');

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

删除

1//删除一个表的所有内容:delete from 表名;
DELETE FROM cat;
2//删除表中的某一条记录：DELETE FROM `表名` WHERE 删除条件;
①//DELETE FROM `boys` WHERE id=8;//用=判断
②//当删除内容判断是否为空时，用 is null；
DELETE FROM `boys` WHERE userCP is null;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

修改

1//修改某条记录：UPDATE 表名 SET 想改成的条件where 该条记录;
UPDATE `boys` SET userCP=57 where `boyName`='郭';
2//修改整个表的某个属性:UPDATE 表名 SET 想改成的条件;
UPDATE `cat` SET `name`='好';
UPDATE `boys` SET userCP=57,`boyName`='无' where `boyName`='郭';//修改某条记录的多个属性

  • 1
  • 2
  • 3
  • 4
  • 5

查询（重点）

*：通配符

count（*)/ count(1)：查询记录数

原理

硬盘的数据通过select语句产生一张虚拟表，所有语句都在虚拟表上修改

关键字：

select
from
where：对原始数据筛选
order by：排序
limit：取值范围
distinct：去重
group by：分组
having：对数据二次筛选

SQL语句编写顺序(s f o w g h o l)

Select *|字段名|distinct(字段名)

​ From 表1 join表2

​ on 表1.字段=表2.字段

​ Where 原始数据的筛选条件

​ Group by 分组字段

​ Having 统计后数据的筛选条件

​ Order by 排序字段 排序规则，排序字段 排序规则

​ Limit 值1，值2

select nationality,count(*) ——想要的字段		5
from tb_student				——从那张表取		1	 
where age>10				——第一次筛选 	2
group by nationality		——分组			3
having count(*)>30			——二次筛选			4
order by count(*) desc		——排序
limit 0,3;					——取值

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
SQL语句执行顺序

from——join——on——where——group by(开始使用select中的别名，后面的语句中都可使用)——（聚合函数：avg、sum···——having——select——distinct——order by——limit

普通select查询
  • 1.//查询一个表的全部内容：SELECT * from 表名;
SELECT * from `boys`;

  • 1

  • 2//查询表中的某几个属性：SELECT 字段名,字段名 from 表名;

    SELECT `boyName`,id from `boys`;

    • 1

  • 3.//用条件查询表中的某条记录：SELECT 字段名 FROM 表名 WHERE 查询条件;

    SELECT `boyName` FROM `boys` WHERE id=5;

    • 1

  • 4.给字段名起别名 as（对查询出来的数据起作用）

    SELECT boyName as '姓名' from boys;

    • 1

  • 字段值去重：distinct(字段) 【单独使用】【常用】

    SELECT DISTINCT(boyfriend_id) from beauty;//只显示第一次出现的boyfriend_id

    • 1
where条件（4.12）

  • 比较运算符：= > < >= <= !=(即<>)

    select * from `user` where age <5; 

    • 1

  • 判断是否为null: is NULL 、 is not NULL

    select * from `boys` where userCP is NULL;
SELECT * from `boys` where userCP is not NULL;

    • 1
    • 2

  • 逻辑运算符： AND（&） or（|） not（！）

    SELECT * FROM `boys` WHERE userCP=57 or userCP=777;
SELECT * FROM `boys` WHERE not userCP=57;

    • 1
    • 2

  • 区间判断：between and (可读性不好)

    SELECT *FROM `boys` WHERE userCP BETWEEN 57 AND 777;//【包含57 和777】

    • 1

  • 多个等值判断：in

    SELECT * FROM `boys` WHERE id IN (1,4,5,8);   
SELECT * FROM `boys` WHERE id=1 or id=4 or id=5 ore id=8;   

    • 1
    • 2

  • 模糊判断：

    • %——>若干个字符（0个，1个，多个）
    SELECT * FROM `boys` WHERE boyName LIKE '张%';//查询以张开头的的人
SELECT * FROM `boys` WHERE boyName LIKE '%%云%';//查以云开头，或以云结尾的

    • 1
    • 2
    • _——>表示一个字符
    SELECT * FROM `boys` WHERE boyName LIKE '_云%';//查第二个字符是云的
SELECT * FROM `boys` WHERE boyName LIKE '%云_';//查倒数第二个字符是云的

    • 1
    • 2
内置函数(4.13)
字符串处理函数

  • -concat(字符串1,字符串2····)：从左到右合并字符串

    select concat(id,boyName) FROM boys;

    • 1

  • trim(str):去掉左右所有空格

    SELECT trim(boyName) FROM boys;

    • 1

  • litrm(str):去掉左所有空格

  • rtrim(str):去掉右所有空格

  • substring(str,x,y):从x位置开始，连续取y个str子串

    SELECT substring(`boyName`,1,2) from boys;

    • 1
数值处理函数

  • abs（x）：返回绝对值

    SELECT abs(-12);

    • 1

  • ceil(x):返回大于x的最小整数

  • floor(x):返回小于x的最大整数

  • mod(x,y):返回x与y的模

    SELECT mod(13,24);//模为13，13%24=0···13
SELECT mod(27,24);//模为3,27%24=1···3

    • 1
    • 2

  • rand():返回（0-1）之间的随机数 select round(rand()*100);

    

    • 1

  • round(x,y):返回参数x的y位小数的四舍五入结果

    SELECT round(3.6785,2);

    • 1
日期处理函数

  • year(日期/日期时间类型字段)：返回年份

    SELECT year(borndate) from beauty;
SELECT * from beauty where year(borndate)=1988;

    • 1
    • 2

  • month(日期/日期时间类型字段)：返回月份

    SELECT month(borndate) from beauty;

    • 1

  • day(日期/日期时间类型字段)：返回日期

    SELECT day(borndate) from beauty;

    • 1
聚合函数

特点：只返回一条记录；只能查询一个字段；多个聚合函数可以一起查询

select avg(boyfriend_id) as '平均年龄',max(boyfriend_id) from beauty;//多个聚合函数可以一起查询


  • 1
  • 2

  • 1.count(*):返回满足条件的记录数，尽量不再查询其他字段，没意义

    • SELECT COUNT(*) FROM 表名; //查询表中的记录的总条数

    • null：不算条数——>尽量用count(*)，避免字段的条数为null，出错

    SELECT COUNT(*) FROM `boys`; <==>SELECT COUNT(1) FROM `boys`;//count(1)查询得快
SELECT COUNT(*)  as '记录数' FROM `boys`;
SELECT COUNT(0) FROM `boys`;//查询第一个字段的总条数

    • 1
    • 2
    • 3

  • 2.sum(数值类型的字段)：返回满足条件的字段值的和

    SELECT sum(boyfriend_id) from beauty;//返回boyfriend_id的总和

    • 1

  • 3.max(数值类型的字段)：返回满足条件的字段值的最大值

    SELECT max(boyfriend_id) from beauty;

    • 1

  • 4.min(数值类型的字段)：返回满足条件的字段值的最小值

    SELECT min(boyfriend_id) from beauty;//若最小记录有多条相同的，也只返回一条

    • 1

  • 5.avg(数值类型的字段)：返回满足条件的字段值的平均值

    SELECT avg(boyfriend_id) from beauty;

    • 1
排序语句order by

(与where查询平级)

order by【默认排序是asc升序】

格式：order by 主关键字 排序规则(asc|desc)，次要关键字 排序规则，次次要关键字 排序规则

SELECT * FROM boys ORDER BY userCP asc, id desc;//按cp数量升序排，id降序排
SELECT * FROM boys ORDER BY userCP asc, id;//按cp数量升序排，id按默认升序排

  • 1
  • 2
取记录范围limit

记录的索引值从0开始

  • limit 值n：从索引值0开始，连续取值n条记录(默认从0开始)

    SELECT * from boys LIMIT 0,5;//从boys表中从0起连续取5条记录

    • 1

  • limit 索引值，值1：从索引值开始，连续取值1条记录
    项目中的翻页代码：select * from 表名 limit(页码-1)*每页记录数，每页记录数

分组查询group by

配合聚合函数使用（此时返回不止一条）；务必把分组的字段也查询出来

  • 返回的记录数即分组字段的不重复值的数量

    SELECT borndate,count(*) from beauty GROUP BY borndate;//查borndate务必把分组的字段也查询出来
select * from beauty GROUP BY borndate;//按照生日分组
select * ,count(*)from beauty GROUP BY borndate;//按照生日分组，并统计数量

    • 1
    • 2
    • 3

  • distinct与group by的区别

    • group by侧重分组统计，distinct侧重去除重复记录

    distinct是每种类型取一条数据；group by是按每个类型进行分组（不止1条数据）

    • SQL的执行阶段不同，先执行group by，后执行distinct
    • group by配合聚合函数使用，distinct无法直接配合聚合函数
    • group by的效率高于distinct
筛选数据having

对已经筛选的数据进行二次筛选

select * from boys HAVING userCP>50;
select nationality,count(*) from tb_student where sex='男' group by nationality having count(*)>30;//将男生按照民族进行分组并输出民族和各民族男生数量【1次筛选】，再加入having关键字后，可以筛选出其中男生数量>30的民族【即2次筛选】

  • 1
  • 2
  • where和having的区别
    • where在group by前；having在group by后
    • where是对原始数据(分组前数据)的条件过滤；having是对统计后的数据(分组后)的条件过滤
    • where后不能用聚合函数；having后可以

7.15更新

多表联合查询(4.14)

外连接不能用where；内连接可以用where，但不建议

on与where的区别：

where更慢，在两张表合并成一张大表后(笛卡尔积)再筛选
on：再筛选再笛卡尔积

设计表的三范式：

● 属性不可分割
● 表中要有主键（主键约束）
● 外键约束

内连接inner join

即内联合
查询两表间的共同数据；返回相交数据；连接条件：on；

-- select 字段 from 表1 inner join 表2 on 表1.字段 =表2.字段 inner join 表3 on 表1，字段=表2，字段 
SELECT * from `project` inner join `department`;//笛卡尔积（两表数据相乘）

SELECT * from `project` inner join `department` on `project`.dnum=`department`.dnumber; //两表相乘后寻找相交数据

SELECT employees.emp_name,department.com_name  -- 要表明哪个表的字段
FROM employees
inner JOIN department 
on employees.com_id=department.com_id -- 外键=主键

SELECT employees.*   4   -- 只查询特定表的所有内容  
FROM employees   1
inner JOIN department  3 
on employees.com_id=department.com_id  2

SELECT employees.*
FROM employees,department 
where  employees.com_id=department.com_id

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
-- 内连接 3张表
SELECT *
FROM employees 
right join department 
on  employees.com_id=department.com_id
inner join hobby  -- 多个表连接
on hobby.emp_id=employees.emp_id

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

外连接

SELECT b.* 
 FROM tb_student as a
 join tb_student as b
 on a.name='郑新伟'  -- 此时a表只剩1条数据
 and a.age<b.age -- 

  • 1
  • 2
  • 3
  • 4
  • 5
左外连接

返回两张表的交集数据+左表的剩余数据

SELECT * 4
FROM employees  1 
left join department  3  -- 返回两张表的交集数据+左表的剩余数据
on  employees.com_id=department.com_id 2 

  • 1
  • 2
  • 3
  • 4
右外连接

返回两张表的交集数据+右表的剩余数据

SELECT *
FROM employees 
 right join department 
on  employees.com_id=department.com_id

  • 1
  • 2
  • 3
  • 4
全外连接

要求：得左右两表的字段数一样，eg：左表的字段数有5个，右表的字段数3个，此时报错

SELECT *
FROM employees 
 right join department 
on  employees.com_id=department.com_id
union  -- 将两表的剩余数据都显示出来
SELECT *
FROM employees 
left join department 
on  employees.com_id=department.com_id

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

自连接

单表操作；执行快

SELECT  b.*
from employees as a
inner join employees as b
on a.emp_name='王三' and a.emp_salary<b.emp_salary

  • 1
  • 2
  • 3
  • 4

嵌套连接

执行慢；查询一个表

SELECT name,position
FROM job
where position=( --  查询到咨询顾问的有哪些人
SELECT position
FROM job 
where  name='张洁') -- 查询到一个字段：‘张洁’的职位——>咨询顾问

SELECT *
FROM student as b
inner join (select * FROM school where id=1) as a  -- 嵌套中查询到一张表
-- 里面的语句可以作为特定条件查询，得到id=1的信息
on b.id=a.id -- 将id=1的信息与student进行比对

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

子查询

事务

注意事项

● 每条语句都有自己的事务，但多条语句加在一起就得自己开启事务

● 事务的4特征（面试）

● 事务的隔离级别：mysql默认用第三级别：可重复读；串行化：把多线程换成单线程，即不能同时操作一张表 oracle默认读已提交

● 事务中出现的问题： ——>解决办法：提高数据库的隔离级别

● 脏读：一个用户读取一个还未提交的数据 (最低级别)

● 不可重复读：
● 幻读：——>继续提高隔离级别

事务的概念

● 一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败，单元中的每条 SQL 语句都相互依赖，形成一个整体，如果某条 SQL 语句执行失败或者出现错误，那么整个单元就会回滚，撤回到事务最初的状态，如果单元中所有的 SQL 语句都执行成功，则事务就顺利执行。

2.事务的数据准备
3.未管理事务演示

-- 张三给李四转账500元
UPDATE account SET money=money-500 WHERE NAME='张三';-- 1.张三账户-500
出错了...
UPDATE account SET money=money+500 WHERE NAME='李四';-- 2.李四账户+500
-- 该场景下，这两条sql语句要么同时成功，要么同时失败。就需要被事务所管理！

  • 1
  • 2
  • 3
  • 4
  • 5
管理事务演示

操作事务的三个步骤

  1. 开启事务：记录回滚点，并通知服务器，将要执行一组操作，要么同时成功、要么同时失败
  2. 执行sql语句：a. 执行具体的一条或多条sql语句
  3. 结束事务(提交|回滚)
    ■ 提交：没出现问题，数据进行更新
    ■ 回滚：出现问题，数据恢复到开启事务时的状态
    ● 开启事务
● 管理事务演示
START TRANSACTION;--或 begin; -- 开启事务
-- 张三给李四转账500元
UPDATE account SET money=money-500 WHERE NAME='张三';
-- 出错了...
UPDATE account SET money=money+500 WHERE NAME='李四';
ROLLBACK;-- 回滚事务(出现问题)
COMMIT;-- 提交事务(没出现问题)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
5事务的提交方式

● 提交方式
○ 自动提交(MySQL默认为自动提交)
○ 手动提交
● 修改提交方式
○ 查看提交方式

-- 标准语法
SELECT @@AUTOCOMMIT;  -- 1代表自动提交    0代表手动提交
○ 修改提交方式
-- 标准语法
SET @@AUTOCOMMIT=数字;

-- 修改为手动提交
SET @@AUTOCOMMIT=0;

-- 查看提交方式
SELECT @@AUTOCOMMIT;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

事务的四大特征(ACID)

原子性(atomicity)
○ 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响***（不可分割）***
一致性(consistency)
○ 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态***（事务开启前后，总量不变）***
○ 拿转账来说，假设张三和李四两者的钱加起来一共是2000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是2000，这就是事务的一致性
隔离性(isolcation)
○ 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
持久性(durability)
○ 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

7.事务的隔离级别

隔离级别的概念

○ 多个客户端操作时 ,各个客户端的事务之间应该是隔离的，相互独立的 , 不受影响的。
○ 而如果多个事务操作同一批数据时，则需要设置不同的隔离级别 , 否则就会产生问题 。
○ 我们先来了解一下四种隔离级别的名称 , 再来看看可能出现的问题

四种隔离级别

1 读未提交 read uncommitted
2 读已提交 read committed
3 可重复读 repeatable read
4 串行化 serializable

可能引发的问题

脏读（读未提交导致） 一个用户读取了还未提交的数据，发现两次结果不一致 是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致
不可重复读 是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致
幻读 select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或不存在执行delete删除，却发现删除成功

● 查询数据库隔离级别
-- 标准语法 5.0
SELECT @@TX_ISOLATION;

-- mysql 8.0
SELECT @@transaction_isolation;

● 修改数据库隔离级别

-- 标准语法
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

-- 修改数据库隔离级别为read uncommitted
SET GLOBAL TRANSACTION ISOLATION LEVEL read uncommitted;

-- 查看隔离级别
SELECT @@transaction_isolation;  -- 修改后需要断开连接重新开

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
8.事务隔离级别演示
●  脏读的问题 
  ○ 窗口1
-- 查询账户表
select * from account;

-- 设置隔离级别为read uncommitted
set global transaction isolation level read uncommitted;

-- 开启事务
start transaction;

-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 窗口2查询转账结果 ,出现脏读(查询到其他事务未提交的数据)

-- 窗口2查看转账结果后，执行回滚
rollback;
  ○ 窗口2
-- 查询隔离级别
SELECT @@transaction_isolation;

-- 开启事务
start transaction;

-- 查询账户表
select * from account;
●  解决脏读的问题和演示不可重复读的问题 
  ○ 窗口1
-- 设置隔离级别为read committed
set global transaction isolation level read committed;

-- 开启事务
start transaction;

-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 窗口2查看转账结果，并没有发生变化(脏读问题被解决了)

-- 执行提交事务。
commit;

-- 窗口2查看转账结果，数据发生了变化(出现了不可重复读的问题，读取到其他事务已提交的数据)
  ○ 窗口2
-- 查询隔离级别
SELECT @@transaction_isolation;

-- 开启事务
start transaction;

-- 查询账户表
select * from account;
●  解决不可重复读的问题 
  ○ 窗口1
-- 设置隔离级别为repeatable read
set global transaction isolation level repeatable read;

-- 开启事务
start transaction;

-- 转账
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- 窗口2查看转账结果，并没有发生变化

-- 执行提交事务
commit;

-- 这个时候窗口2只要还在上次事务中，看到的结果都是相同的。只有窗口2结束事务，才能看到变化(不可重复读的问题被解决)
  ○ 窗口2
-- 查询隔离级别
SELECT @@transaction_isolation;

-- 开启事务
start transaction;

-- 查询账户表
select * from account;

-- 提交事务
commit;

-- 查询账户表
select * from account;
●  幻读的问题和解决 
  ○ 窗口1
-- 设置隔离级别为repeatable read
set global transaction isolation level repeatable read;

-- 开启事务
start transaction;

-- 添加一条记录
INSERT INTO account VALUES (3,'王五',1500);

-- 查询账户表，本窗口可以查看到id为3的结果
SELECT * FROM account;

-- 提交事务
COMMIT;
  ○ 窗口2
-- 查询隔离级别
SELECT @@transaction_isolation;

-- 开启事务
start transaction;

-- 查询账户表，查询不到新添加的id为3的记录
select * from account;

-- 添加id为3的一条数据，发现添加失败。出现了幻读
INSERT INTO account VALUES (3,'测试',200);

-- 提交事务
COMMIT;

-- 查询账户表，查询到了新添加的id为3的记录
select * from account;
  ○ 解决幻读的问题
/*
	窗口1
*/
-- 设置隔离级别为serializable
set global transaction isolation level serializable;

-- 开启事务
start transaction;

-- 添加一条记录
INSERT INTO account VALUES (4,'赵六',1600);

-- 查询账户表，本窗口可以查看到id为4的结果
SELECT * FROM account;

-- 提交事务
COMMIT;



/*
	窗口2
*/
-- 查询隔离级别
SELECT @@transaction_isolation;

-- 开启事务
start transaction;

-- 查询账户表，发现查询语句无法执行，数据表被锁住！只有窗口1提交事务后，才可以继续操作
select * from account;

-- 添加id为4的一条数据，发现已经存在了，就不会再添加了！幻读的问题被解决
INSERT INTO account VALUES (4,'测试',200);

-- 提交事务
COMMIT;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
9.隔离级别总结

注意：隔离级别从小到大安全性越来越高，但是效率越来越低 , 所以不建议使用READ UNCOMMITTED 和 SERIALIZABLE 隔离级别.

10.事务的总结

● 一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败。例如转账操作

● 开启事务：start transaction;

● 回滚事务：rollback;

● 提交事务：commit;

● 事务四大特征
○ 原子性
○ 持久性
○ 隔离性
○ 一致性

● 事务的隔离级别
○ read uncommitted(读未提交)
○ read committed (读已提交)
○ repeatable read (可重复读)
○ serializable (串行化)

JDBC

简介

java数据库连接，即Java语言中用来规范客户端来访问数据库的应用程序接口，是一组用Java语言编写的类和接口组成的。

编写顺序：

  1. 导jar包
  2. 注册驱动——>8：com.mysql.cj.jdbc.Driver；5：com.mysql.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");//工具类准备就绪

  • 1
  1. 与数据库创建连接 地址（主机地址：端口号/数据库名）；用户名；密码
conn= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mytext","root","123456");

  • 1
  1. 预执行sql语句:只是连接，还未执行
    //执行修改语句，此处有漏洞
preparedStatement = conn.prepareStatement("UPDATE school set schName='黑龙江' where id=5; ");//修改语句

  • 1
  1. 执行sql，并接收返回结果
  2. ?占位符——>为了解决sql注入漏洞
  3. 给问号占位符赋值
  4. 关闭连接通道和 (先开后关)
    ● 关闭顺序：1.结果集 2.预执行对象 3.连接对象
public class Demo {
    public static void main(String[] args){
        List<User> users=getUser();
        System.out.println(users);
    }
    //jdbc方法
    public static List<User> getUser(){
        Connection conn=null; //连接对象
        PreparedStatement preparedStatement=null; //预执行sql对象
        PreparedStatement preparedStatement2=null;
        ResultSet re=null; //装结果集
        List<User> userList=new ArrayList<>();
        
        try{
            //1.导mysql的jar包 2.注册驱动——>8：com.mysql.cj.jdbc.Driver；5：com.mysql.jdbc.Driver
            Class.forName("com.mysql.cj.jdbc.Driver");//工具类准备就绪
            //3.与数据库创建连接 地址（主机地址：端口号/数据库名）；用户名；密码
            conn= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mytext","root","123456");
            System.out.println(conn); //打印堆区地址
            
            //4.预执行sql语句:只是连接，还未执行
            preparedStatement = conn.prepareStatement("UPDATE school set schName='黑龙江' where id=5; ");//修改语句
            //5.执行sql
            int i = preparedStatement.executeUpdate();//执行增删改语句,返回受影响条数
            System.out.println("受影响条数为"+i);
            
            System.out.println("----------------Query的预执行和实际执行------------");
            //7.?占位符——>为了解决sql注入漏洞
            String sql="SELECT * FROM boys;"; //sql注入漏洞
            preparedStatement2 = conn.prepareStatement(sql);//查询返回结果集
            //8.给问号赋值
            /* preparedStatement2.setString(1,"1");
            preparedStatement2.setString(2,"张无忌");*/
            re=preparedStatement2.executeQuery();
            //此处返回的是结果表，而不是实际表
            while (re.next()){ //第n次调用则判断第n条语句是否存在，返回boolean值；
                int id=re.getInt("id"); //括号里的id可以用1代替
                String name=re.getString("boyName");
                int userCp=re.getInt("userCP");
                //System.out.println(id+name+userCp);
                User user =new User();  //每次循环时都得重创建一个新对象
                user.setId(id);//将
                user.setBoyName(name);
                user.setUserCP(userCp);
                userList.add(user);
            }
        }catch(Exception e){
            System.out.println("出现异常");
        }finally{
            //6.关闭连接通道和 (先开后关)
            try{ //关流时可能会出现异常
                if(re!=null){  //1.关结果集
                    re.close();
                }
                if(preparedStatement!=null){ //2.关预执行连接对象
                    preparedStatement.close();
                }
                if(preparedStatement2!=null){
                    preparedStatement.close();
                }
                if(conn!=null){ //3.关连接对象
                    conn.close();
                }
            }catch(Exception e){
                e.printStackTrace();
            }
        }
        return userList;
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70

若有错误纰漏，欢迎指正~

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/IT小白/article/detail/475654
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号