【可视化工具操作数据库,MySQL基础】

一、如何使用终端操作数据库

点击查看

二、如何使用可视化工具操作数据库

1、可视化数据库操作工具:

  • Navicat
  • SQLyog

新建数据库时:

  • 表名:自拟

  • 引擎选:InnoDB

  • 字符集选:utf8

  • 排序规则选:utf8_general_ci

2、数据库的字段属性(重点)

unsigned:

  • 无符号的整数
  • 声明了该列不能声明为负数

zerofill:

  • 0填充
  • 不足的位数,使用0来填充,int(3): 5—005

自增:

  • 通常理解为自增,自动在上一条记录的基础上+1(默认)
  • 通常用来设计唯一的主键 index,必须时整数类型
  • 可以自定义设计主键自增的起始值和步长

==非空:==not null

  • 假设设置为not null,如果不给他赋值,就会报错!
  • NULL,如果不填写,默认就是null

默认:

  • 设置默认的值
  • gender,默认值为 男,如果不指定该列的值,则会有默认值!

拓展:

/*每个表,都必须存在以下五个字段!表示一个记录存在意义!
id			主键
'version'	乐观锁
is_delete	伪删除
gmt_creat	创建时间
gmt_update	修改时间
*/

可视化工具中使用命令行建表:

-- 创建表格注意点:使用英文(),表的名称 和 字段 尽量使用'' 括起来
-- 字符串使用单引号括起来!
-- 所有的语句后面加 ,(英文符号) 最后一个字段不用加
-- PRIMARY KEY 主键,一般一个表只有一个唯一的主键
CREATE TABLE IF NOT EXISTS 'student'(
	'id' INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    'name' VARCHAR(25) NOT NULL DEFAULT '匿名' COMMENT '姓名'
    
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

格式:[ ]内表示可选

CREAT TABLE [IF NOT EXISTS] '表明'(
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ······
    '字段名' 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释];

可视化工具中查看SQL语句:

SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构

关于数据库引擎(数据表的类型):

  • INNODB:默认使用
  • MYISAM:早些年使用的
MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间的大小较小较大,约为M的2倍

常规使用操作:

  • MYISAM 节约空间,速度较快
  • INNODB 安全性高,事务的处理,多表多用户操作

在物理空间存在的位置:

  • 所有的数据库文件都存在data目录下,本质还是文件的存储!

MySQL引擎在物理文件上的区别:

  • INNODB 在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
  • MYISAM对应文件
    • *.frm 表结构的定义文件
    • *.MYD 数据文件(data)
    • *.MYI 索引文件(index)

设置数据表的字符集编码

CHARSET=utf8

不设置的话,会是mysql默认的字符集编码~(不支持中文!)

MySQL的默认编码是Latin1,不支持中文

在my.ini中配置默认的编码

character-set-server=utf8

3、DML语言

  • 添加

语法:insert into 表名([字段名1,字段名2,...])values('值1','值2',...)[,('值1','值2',...)...];

注意事项:

  1. 字段和字段之间使用英文逗号隔开
  2. 字段是可以省略的,但是后面的值必须要一一对应
  3. 同时可以插入多条数据,values后面的值需要使用逗号隔开即可:values(),()...
  • 修改

语法:update 表名 set 'colnum_name' ='value' [,'colnum_name1' ='value1'...] where[条件]

条件:where子句 运算符

注意事项:

  1. colnum_name 是数据库的列,尽量带上’ ’
  2. 条件,筛选的条件,如果没有指定,则会修改所有的列
  3. value,是一个具体的值,也可以是一个变量
  4. 多个设置的属性之间,使用英文逗号隔开
操作符含义范围结果
=等于1=2false
<> 或 !=不等于1<>2true
> >=大于 大于等于
< <=小于 小于等于
between …and…在某个范围内[1,2]
and &&我和你5>1 and 1>2false
or ||我或你5>1 or 1>2true
not !not a取反
  • 删除

语法:delete from 表名[where 条件]

-- 删除数据 (避免这样写,会全部删除)
delete from 'student'

-- 删除指定数据
delete from 'student' where id = 1;

truncate命令

用法:truncate 'student' -- 清空student表

作用:完全清空一个数据库表,表的结构和索引约束不会变!

delete与truncate 区别:

  • 相同点:都能删除数据,都不会删除表结构
  • 不同点:
    • truncate:重新设置,自增列 计数器会归零
    • truncate:不会影响事务

了解:delete删除的问题,重启数据库

  • InnoDB:自增列会从1开始(存在内存当中的,断电即失)
  • MyISAM:继续从上一个自增量开始(存在文件中的,不会丢失)

4、DQL查询数据(重点)

data query language

  • 所有的查询操作都用它 select
  • 简单、复杂的查询都能做
  • 数据库中最核心的语言,最重要的语句
  • 使用频率最高的语句

SELECT 语法

在这里插入图片描述

注意:[ ]括号代表可选的,{ } 括号代表必选的

指定查询字段

语法:select 字段 ... from 表

-- 查询全部的学生
select *from student 
-- 查询指定字段
select 'StudentNo','StudentName' from student
--别名
select 'StudentNo' as 学号,'StudentName' as 学生姓名 from student as s

别名,给结果起一个名字as ,可以给字段起别名,也可以给表起别名

函数concat(a,b) 连接a和b

select concat('姓名:', StudentName) as 新名字 from student

  • 去重 distinct

select distinct 'StudentNo' from result

作用:去除select语句查询出来的结果中重复的数据,重复的只显示一条。

  • 查询系统版本(函数)

select version()

  • 计算结果(表达式)

select 100*2-1 as 计算结果

eg: select 'StudentNo','StudentResult' +1 as '提分后' from result

  • 查询自增的步长(变量)

select @@auto_increment_increment

数据库中的表达式:文本值,列,Null,函数,计算表达式,系统变量···

用法:select 表达式 from 表

模糊查询:比较运算符
运算符语法描述
is nulla is null如果操作符为NULL,结果为真
is not nulla is not null如果操作符不为null,结果为真
betweena between b and c若a在b和c之间,则结果为真
likea like bSQL匹配,若a匹配b,则结果为真
ina in(a1,a2,a3…)假设a在a1,a2…其中的某一个之中,结果为真
-- 模糊查询
-- *********like**************
-- like结合 %(代表0到任意个字符)	_(代表一个字符)
-- 查询姓刘的同学
select 'StudentNo','StudentName' from 'student' where StudentName like '刘%'

-- 查询姓刘的同学,名字后面只有一个字的
select 'StudentNo','StudentName' from 'student' where StudentName like '刘_'

-- 查询姓刘的同学,名字后面只有两个字的
select 'StudentNo','StudentName' from 'student' where StudentName like '刘__'

-- 查询名字中间有嘉的同学
select 'StudentNo','StudentName' from 'student' where StudentName like '%嘉%'

-- *********in(具体的一个或多个值)**************
-- 查询1001,1002,1003号学员
select 'StudentNo','StudentName' from 'student' where StudentNo in (1001,1002,1003)

-- *********null  not null**************
-- 查询地址为空的学生
select 'StudentNo','StudentName' from 'student' where address='' or address is null
-- 查询地址不为空的学生
select 'StudentNo','StudentName' from 'student' where address='' or address is not null
联表查询(JOIN)

left join right join inner join

JOIN对比

在这里插入图片描述

-- 联表查询
-- 查询参加了考试的学生(学号,姓名,科目编号,分数)
select * from student
selsct * from result
/* 思路
1. 分析需求,分析查询的字段来自那些表,(连接查询)
2. 确定使用哪种连接查询? 7种
确定交叉点(两个表中哪个数据是相同的)
判断的条件:学生表中的 studentNo = 成绩表中的studentNo
*/
select s.studentNo,studentName,subjectNo,studentResult 
from student as s
inner join result as r
where s.studentNo = r.studentNo

--right join
select s.studentNo,studentName,subjectNo,studentResult 
from student s
right join result r
on s.studentNo = r.studentNo

--left join
select s.studentNo,studentName,subjectNo,studentResult 
from student s
left join result r
on s.studentNo = r.studentNo

-- 查询缺考的同学
select s.studentNo,studentName,subjectNo,studentResult 
from student s
left join result r
on s.studentNo = r.studentNo
where studentResult is NULL

right join与left join的区别:

操作描述
inner join如果表中至少有一个匹配,就返回行
right join会从右表中返回所有的值,即使左表中没有匹配
left join会从左表中返回所有的值,即使右表中没有匹配

join(连接的表) on (判断条件)连接查询

where 等值查询

-- 三表联查 student,result,subject
-- 学号,姓名,科目名,分数
select studentNo,studentName,SubjectName,StudentResult
from student s
right join result r 
on r.studentNo = s.studentNo
inner join subject sub
on r.SubjectNo = sub.SubjectNo

-- 要查询哪些数据 select ···
-- 从哪几个表中查 from 表 XXX join 连接的表 on 交叉条件
-- 假设存在一种多张表查询,先查询两张表然后再增加

自连接(了解)

自己的表和自己的表连接,核心:一张表拆成两张一样的表即可

在这里插入图片描述

父类

categoryidcategoryName
2信息技术
3软件开发
5美术设计

子类

pidcategoryidcategoryName
34数据库
28办公信息
36web开发
57ps技术

操作:查询父类对应的子类关系

父类子类
信息技术办公信息
软件开发数据库
软件开发web开发
美术设计ps技术
-- 查询父子信息
select a.categoryName as '父栏目', b.categoryName as '子栏目'
from category as a,category as b
where a.categoryid = b.pid

在这里插入图片描述

子查询

where(这个值是计算出来的)

本质:再where语句中嵌套一个子查询语句

where(select * from)

-- 1、查询 数据库结构-1 的所有考试结果,降序排序
-- 方式一:使用联表查询
select studentName,r.subjectNo,studentResult 
from result r
inner join subject sub
on r.subjectNo = sub.subjectNo
where SubjectName = '数据库结构-1'
order by studentResult DESC
-- 方式二:使用子查询
select studentName,subjectNo,studentResult 
from result
where subjectNo = (
    select subjectNo from subject
    where SubjectName = '数据库结构-1'
)

查询小结:

顺序很重要

select 去重 要查询的字段 from 表(注意:表和字段可以取别名)
xxx join 要连接的表 on 等值判断
where(具体的值,子查询语句)
Group By(通过哪个字段来分组)
Having(过分组后的信息,条件和where是一样的,位置不同)
Order By…(通过哪个字段排序)[升序/降序]
Limit startindex,pagesize

业务层面:

查询:跨表,跨数据库······

5、MySQL函数

常用函数
-- 数学运算
select ABS(-8) -- 绝对值
select CEILING(3.4) -- 向上取整
select FLOOR(3.4) -- 向下取整
select RAND() --  返回一个0-1之间的随机数

-- 字符串函数
select CHAR_LENGTH('长风破浪会有时') -- 字符串长度
select CONCAT('A','B','C') -- 拼接字符串
select INSERT('12345',1,2,'abc') -- 查询,替换
SELECT LOWER('XiaoEr')-- 小写字母
SELECT UPPER('XiaoEr') -- 小写字母

-- 时间和日期
select CURRENT_DATE()-- 获取当前日期
select NOW() -- 获取当前的时间
select LOCALTIME() -- 获取本地时间
select SYSDATE() -- 获取系统时间
select YEAR(NOW()) -- 获取当前年份
select MONTH(NOW()) -- 获取当前月份
select DAY(NOW()) -- 获取当前日期
select HOUR(NOW()) 
select MINUTE(NOW())
select SECOND(NOW()) 

-- 系统
select SYSTEM_USER()
select USER()
select VERSION()
聚合函数(常用)
函数名描述
COUNT()计数
SUM()求和
AVG()平均值
MAX()最大值
MIN()最小值
············
select COUNT(studentName) from student; --count(字段),会忽略所有的null值
select COUNT(*) from student; --count(*),不会忽略所有的null值,本质 计算行数
select COUNT(1) from student; --count(1),不会忽略所有的null值

select SUM(sutdentResult) as 总和 from result
select AVG(sutdentResult) as 平均分 from result

-- 练习:查询不同课程的平均分,最高分,最低分,平均分大于80分
-- 核心:根据不同的课程分组
select SubjectName,AVG(studentResult) as 平均分,MAX(studentResult) as 最高分,MIX(studentResult) as 最低分
from result r
inner join subject sub
on r.subjectNo = sub.subJectNo
group by r.subjectNo -- 通过什么字段来分组
having 平均分>80 -- 过滤分组的记录必须满足的次要条件

分页limit 和排序order by

排序:升序ASC,降序DESC

order by 通过哪个字段排序,怎么排

-- 查询的结果根据成绩升序 排序
select s.studentNo,studentName,subjectNo,studentResult 
from student s
right join result r
on s.studentNo = r.studentNo
order by studentResult ASC
分页

可以缓解数据库压力,给人好的体验

-- 分页,每页只显示5条数据
-- 语法:limit 起始值,页面的大小
-- limit 0,5	1~5	第一页
-- limit 1,5	2~6
-- limit 5,5	6~10第二页
-- 第N页 limit (n-1)*pageSize,pageSize
-- 【pageSize:页面大小,(n-1)*pageSize:页面起始值,n:当前页】
-- 【总页数 = 数据总数/页面大小】
select s.studentNo,studentName,subjectNo,studentResult 
from student s
right join result r
on s.studentNo = r.studentNo
order by studentResult ASC
limit 0,5

分组和过滤

-- 练习:查询不同课程的平均分,最高分,最低分,平均分大于80分
-- 核心:根据不同的课程分组
select SubjectName,AVG(studentResult) as 平均分,MAX(studentResult) as 最高分,MIX(studentResult) as 最低分
from result r
inner join subject sub
on r.subjectNo = sub.subJectNo
group by r.subjectNo -- 通过什么字段来分组
having 平均分>80 -- 过滤分组的记录必须满足的次要条件

数据库级别的MD5加密(扩展)

MD5码以512位分组来处理输入的信息,且每一分组又被划分为16个32位子分组,经过了一系列的处理后,算法的输出由四个32位分组组成,将这四个32位分组级联后将生成一个128位散列值。

主要增强算法复杂度和不可逆性。

MD5不可逆,但具体的值的MD5是一样的。

MD5破解网站的原理,背后有一个字典,只能破解一些简单的数字。

-- MD5加密
CREATE TABLE'testmd5(
    'id' INT(4) NOT NULL,
    name VARCHAR(20) NOT NULL,
    'pwd'VARCHAR(50) NOT NULL,
    PRIMARY KEY('id')
)ENGINE=INNODB DEFAULT CHARSET=utf8
--明文密码
INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1
UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码
-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))
--如何校验:将用户传递进来的密码,进行md5加密,然后比对加密后的值
SELECT*FROM testmd5 WHERE name ='xiaoming'AND pwd=MD5('123456')

6、事务

点击参考博客链接

在 MySQL 中,事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性

比如我们的银行转账:

-- a -> -100
UPDATE user set money = money - 100 WHERE name = 'a';

-- b -> +100
UPDATE user set money = money + 100 WHERE name = 'b';

在实际项目中,假设只有一条 SQL 语句执行成功,而另外一条执行失败了,就会出现数据前后不一致。

因此,在执行多条有关联 SQL 语句时,事务可能会要求这些 SQL 语句要么同时执行成功,要么就都执行失败

如何控制事务 - COMMIT / ROLLBACK

在 MySQL 中,事务的自动提交状态默认是开启的

-- 查询事务的自动提交状态
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+

自动提交的作用:当我们执行一条 SQL 语句的时候,其产生的效果就会立即体现出来,且不能回滚

什么是回滚?举个例子:

CREATE DATABASE bank;

USE bank;

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    money INT
);

INSERT INTO user VALUES (1, 'a', 1000);

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

可以看到,在执行插入语句后数据立刻生效,原因是 MySQL 中的事务自动将它提交到了数据库中。那么所谓回滚的意思就是,撤销执行过的所有 SQL 语句,使其回滚到最后一次提交数据时的状态。

在 MySQL 中使用 ROLLBACK 执行回滚:

-- 回滚到最后一次提交
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

由于所有执行过的 SQL 语句都已经被提交过了,所以数据并没有发生回滚。那如何让数据可以发生回滚?

-- 关闭自动提交
SET AUTOCOMMIT = 0;

-- 查询自动提交状态
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+

将自动提交关闭后,测试数据回滚:

INSERT INTO user VALUES (2, 'b', 1000);

-- 关闭 AUTOCOMMIT 后,数据的变化是在一张虚拟的临时数据表中展示,
-- 发生变化的数据并没有真正插入到数据表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

-- 数据表中的真实数据其实还是:
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

-- 由于数据还没有真正提交,可以使用回滚
ROLLBACK;

-- 再次查询
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

那如何将虚拟的数据真正提交到数据库中?使用 COMMIT :

INSERT INTO user VALUES (2, 'b', 1000);
-- 手动提交数据(持久性),
-- 将数据真正提交到数据库中,执行后不能再回滚提交过的数据。
COMMIT;

-- 提交后测试回滚
ROLLBACK;

-- 再次查询(回滚无效了)
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

总结

  1. 自动提交

    • 查看自动提交状态:SELECT @@AUTOCOMMIT

    • 设置自动提交状态:SET AUTOCOMMIT = 1

  2. 手动提交

    • @@AUTOCOMMIT = 0 时,使用 COMMIT 命令提交事务,结束事务。
  3. 事务回滚

    @@AUTOCOMMIT = 0 时,使用 ROLLBACK 命令回滚事务。

事务的实际应用,让我们再回到银行转账项目:

-- 转账
UPDATE user set money = money - 100 WHERE name = 'a';

-- 到账
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

这时假设在转账时发生了意外,就可以使用 ROLLBACK 回滚到最后一次提交的状态:

-- 假设转账发生了意外,需要回滚。
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

这时我们又回到了发生意外之前的状态,也就是说,事务给我们提供了一个可以反悔的机会。假设数据没有发生意外,这时可以手动将数据真正提交到数据表中:COMMIT

手动开启事务 - BEGIN / START TRANSACTION

事务的默认提交被开启 ( @@AUTOCOMMIT = 1 ) 后,此时就不能使用事务回滚了。但是我们还可以手动开启一个事务处理事件,使其可以发生回滚:

-- 使用 BEGIN 或者 START TRANSACTION 手动开启一个事务
-- START TRANSACTION;
BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

-- 由于手动开启的事务没有开启自动提交,
-- 此时发生变化的数据仍然是被保存在一张临时表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 测试回滚
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

仍然使用 COMMIT 提交数据,提交后无法再发生本次事务的回滚。

BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 提交数据
COMMIT;

-- 测试回滚(无效,因为表的数据已经被提交)
ROLLBACK;

事务开启:

  1. 修改默认提交 set autocommit=0;
  2. begin;
  3. start transaction;

事务手动提交:

  • commit;

事务手动回滚:

  • rollback;
事务的 ACID 特征与使用

(atomicity、consistency、isolation、durability)

事务的四大特征:

  • A 原子性:事务是最小的单位,不可以再分割,表示两个步骤一起成功,或者一起失败,不能只发生其中一个动作;
  • C 一致性:事务要求同一事务中的 SQL 语句,必须保证同时成功或者失败,表示事务完成后,符合逻辑运算;
  • I 隔离性:事务1 和 事务2 之间是具有隔离性的;
  • D 持久性:事务一旦结束 ( COMMIT ) ,就不可以再返回了 ( ROLLBACK ),表示事务结束后的数据不随外界原因导致数据丢失 。
事务的隔离性

事务的隔离性可分为四种 ( 性能从低到高 )

  1. READ UNCOMMITTED ( 读取未提交 )

    如果有多个事务,那么任意事务都可以看见其他事务的未提交数据

  2. READ COMMITTED ( 读取已提交 )

    只能读取到其他事务已经提交的数据

  3. REPEATABLE READ ( 可被重复读 )

    如果有多个连接都开启了事务,那么事务之间不能共享数据记录,否则只能共享已提交的记录。

  4. SERIALIZABLE ( 串行化 )

    所有的事务都会按照固定顺序执行,执行完一个事务后再继续执行下一个事务的写入操作

查看当前数据库的默认隔离级别:

-- MySQL 8.x, 
-- GLOBAL表示系统级别,不加表示会话级别。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                | -- MySQL的默认隔离级别,可以重复读。
+--------------------------------+

-- MySQL 5.x
SELECT @@GLOBAL.TX_ISOLATION;
SELECT @@TX_ISOLATION;

修改隔离级别:使用set

-- 设置系统隔离级别,LEVEL 后面表示要设置的隔离级别 (READ UNCOMMITTED)。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 查询系统隔离级别,发现已经被修改。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+
脏读

测试 READ UNCOMMITTED ( 读取未提交 ) 的隔离性:

INSERT INTO user VALUES (3, '小明', 1000);
INSERT INTO user VALUES (4, '淘宝店', 1000);

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

-- 开启一个事务操作数据
-- 假设小明在淘宝店买了一双800块钱的鞋子:
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

-- 然后淘宝店在另一方查询结果,发现钱已到账。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+

由于小明的转账是在新开启的事务上进行操作的,而该操作的结果是可以被其他事务(另一方的淘宝店)看见的,因此淘宝店的查询结果是正确的,淘宝店确认到账。但就在这时,如果小明在它所处的事务上又执行了 ROLLBACK 命令,会发生什么?

-- 小明所处的事务
ROLLBACK;

-- 此时无论对方是谁,如果再去查询结果就会发现:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

这就是所谓的脏读,一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。

读取已提交

把隔离级别设置为 READ COMMITTED

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+

这样,再有新的事务连接进来时,它们就只能查询到已经提交过的事务数据了。但是对于当前事务来说,它们看到的还是未提交的数据,例如:

-- 正在操作数据事务(当前事务)
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

-- 虽然隔离级别被设置为了 READ COMMITTED,但在当前事务中,
-- 它看到的仍然是数据表中临时改变数据,而不是真正提交过的数据。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+


-- 假设此时在远程开启了一个新事务,连接到数据库。
$ mysql -u root -p12345612

-- 此时远程连接查询到的数据只能是已经提交过的
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

但是这样还有问题,那就是假设一个事务在操作数据时,其他事务干扰了这个事务的数据。例如:

-- 小张在查询数据的时候发现:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+

-- 在小张求表的 money 平均值之前,小王做了一个操作:
START TRANSACTION;
INSERT INTO user VALUES (5, 'c', 100);
COMMIT;

-- 此时表的真实数据是:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+

-- 这时小张再求平均值的时候,就会出现计算不相符合的情况:
SELECT AVG(money) FROM user;
+------------+
| AVG(money) |
+------------+
|  820.0000  |
+------------+

虽然 READ COMMITTED 让我们只能读取到其他事务已经提交的数据,但还是会出现问题,就是在读取同一个表的数据时,可能会发生前后不一致的情况。这被称为不可重复读现象 ( READ COMMITTED )

(可以想一个极端情况来理解:你刚SELECT完,立马计算AVG,在你输入代码的时候有人提交新数据改变了表,这就是不可重复性。)

幻读

将隔离级别设置为 REPEATABLE READ ( 可被重复读取 ) :

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+

测试 REPEATABLE READ ,假设在两个不同的连接上分别执行 START TRANSACTION :

-- 小张 - 成都
START TRANSACTION;
INSERT INTO user VALUES (6, 'd', 1000);

-- 小王 - 北京
START TRANSACTION;

-- 小张 - 成都
COMMIT;

当前事务开启后,没提交之前,查询不到,提交后可以被查询到。但是,在提交之前其他事务被开启了,那么在这条事务线上,就不会查询到当前有操作事务的连接。相当于开辟出一条单独的线程。

无论小张是否执行过 COMMIT ,在小王这边,都不会查询到小张的事务记录,而是只会查询到自己所处事务的记录:

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+

这是因为小王在此之前开启了一个新的事务 ( START TRANSACTION ) ,那么在他的这条新事务的线上,跟其他事务是没有联系的,也就是说,此时如果其他事务正在操作数据,它是不知道的。

然而事实是,在真实的数据表中,小张已经插入了一条数据。但是小王此时并不知道,也插入了同一条数据,会发生什么呢?

INSERT INTO user VALUES (6, 'd', 1000);
-- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'

报错了,操作被告知已存在主键为 6 的字段。这种现象也被称为幻读,一个事务提交的数据,不能被其他事务读取到

事务a和事务b同时操作一张表,事务a提交的数据,也不能被事务b读到,就可以造成幻读。

串行化

顾名思义,就是所有事务的写入操作全都是串行化的。什么意思?把隔离级别修改成 SERIALIZABLE :

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+

还是拿小张和小王来举例:

-- 小张 - 成都
START TRANSACTION;

-- 小王 - 北京
START TRANSACTION;

-- 开启事务之前先查询表,准备操作数据。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
|  6 | d         |  1000 |
+----+-----------+-------+

-- 发现没有 7 号王小花,于是插入一条数据:
INSERT INTO user VALUES (7, '王小花', 1000);

此时会发生什么呢?由于现在的隔离级别是 SERIALIZABLE ( 串行化 ) ,串行化的意思就是:假设把所有的事务都放在一个串行的队列中,那么所有的事务都会按照固定顺序执行,执行完一个事务后再继续执行下一个事务的写入操作 ( 这意味着队列中同时只能执行一个事务的写入操作 ) 。

根据这个解释,小王在插入数据时,会出现等待状态,直到小张执行 COMMIT 结束它所处的事务,或者出现等待超时。

问题:串行化性能特差!

性能比较:(隔离级别越高,性能越差)

read uncommitted > read committed > repeatable read (默认级别)>serializable

资料来源:一天学会 MySQL 数据库_哔哩哔哩_bilibili

事务总结
-- mysql 是默认开启事务自动提交的
set autocommit = 0 -- 关闭
set autocommit = 1 -- 开启(默认)

-- 手动处理事务
set autocommit = 0 -- 关闭自动提交

--事务开启
start transaction -- 标记一个事务的开始,从这个之后的sql都在同一个事务内

insert xx
insert xx

-- 提交:持久化(成功)
commit
-- 回滚:回到原来的样子(失败)
rollback

-- 事务结束
set autocommit = 1 -- 开启自动提交

-- 了解
savepoint 保存点名 -- 设置一个事务的保存点
rollback to savepoint 保存点名  --回滚到保存点
release savepoint 保存点名  -- 撤销保存点

7、索引

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构
提取句子主干,就可以得到索引的本质:索引是数据结构。

索引的分类
  • 主键索引(primary key)
    • 唯一的标识,主键不可重复,只能有一个列作为主键
  • 唯一索引(unique key)
    • 避免重复的列出现,唯一索引可以重复,多个列都可以标识 唯一索引
  • 常规索引(key/index)
    • 默认的,inedx/key关键字来设置
  • 全文索引(fulltext)
    • 在特定的数据库引擎下才有,MyISAM
    • 快速定位数据
基础语法
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后,增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student

-- 增加一个全文索引(索引名)列名
ALTER TABLE school.student ADD FULLTEXT INDEX 'studentName'('studentName');

-- EXPLAIN 分析sq1执行的状况
EXPLAIN SELECT * FROM student;--非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');

索引在小数据量的时候,用处不大,但是在大数据的时候,区别十分明显。

CodingLabs - MySQL索引背后的数据结构及算法原理

索引原则
  • 索引不是越多越好
  • 不要对进程变动数据加索引
  • 小数据量的表不需要加索引
  • 索引一般加在常用来查询的字段上

索引的数据结构

Hash类型的索引

Btree:InnoB

8、权限管理和备份

了解

9、规范数据库设计

当数据库比较复杂时,我们需要规范设计

糟糕的数据库设计:

  • 数据冗余,浪费空间
  • 数据库插入和删除都会很麻烦、异常【屏蔽使用物理外键】
  • 程序的性能差

良好的数据库设计

  • 节省内存空间
  • 保证数据库的完整性
  • 方便系统开发、

软件开发中,关于数据库的设计

  • 分析需求:分析业务和需要处理六的数据库的需求
  • 概要设计:设计关系图E-R图(流程图)

设计数据库的步骤:(个人博客)

  • 收集信息,分析需求
    • 用户表(登录注销,个人信息,写博客,创建分类)
    • 分类表(文章分类,谁创建)
    • 评论表
    • 友链表(友链信息)
    • 自定义表(系统信息,某个关键的字,或一些主字段)key:value
  • 标识实体(把需求落地到每个字段)
  • 标识实体之间的关系
    • 写博客:user—>blog
    • 创建分类:user—>category
    • 关注:user—>user
    • 友链:link
    • 评论:user—>user-blog

资料来源:【狂神说Java】MySQL最新教程通俗易懂】

数据库的三大设计范式
(1)1NF

只要字段值还可以继续拆分,就不满足第一范式。

范式设计得越详细,对某些实际操作可能会更好,但并非都有好处,需要对项目的实际情况进行设定。

(2)2NF

在满足第一范式的前提下,其他列都必须完全依赖于主键列。如果出现不完全依赖,只可能发生在联合主键的情况下:

-- 订单表
CREATE TABLE myorder (
    product_id INT,
    customer_id INT,
    product_name VARCHAR(20),
    customer_name VARCHAR(20),
    PRIMARY KEY (product_id, customer_id)
);

实际上,在这张订单表中,product_name 只依赖于 product_idcustomer_name 只依赖于 customer_id 。也就是说,product_namecustomer_id 是没用关系的,customer_nameproduct_id 也是没有关系的。

这就不满足第二范式:其他列都必须完全依赖于主键列!

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

拆分之后,myorder 表中的 product_idcustomer_id 完全依赖于 order_id 主键,而 productcustomer 表中的其他字段又完全依赖于主键。满足了第二范式的设计!

(3)3NF

在满足第二范式的前提下,除了主键列之外,其他列之间不能有传递依赖关系。

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    customer_phone VARCHAR(15)
);

表中的 customer_phone 有可能依赖于 order_idcustomer_id 两列,也就不满足了第三范式的设计:其他列之间不能有传递依赖关系。

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    phone VARCHAR(15)
);

修改后就不存在其他列之间的传递依赖关系,其他列都只依赖于主键列,满足了第三范式的设计!

三、如何在编程语言中操作数据库

  • JDBC : sun发布的 一个 java程序和数据库之间通信的 规范(接口)
  • 各大数据库厂商去实现JDBC规范(实现类),这些实现类打成压缩包,就是所谓的jar包

1 JDBC常规操作:

1)JDBC连接MySQL数据库
package com.atguigu.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

//目标:和数据库建立连接
public class Demo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.添加jar
        //2.加载驱动
        //org.gjt.mm.mysql.Driver
        Class.forName("com.mysql.cj.jdbc.Driver");
        //3.通过驱动管理器获取连接对象
        //3-1.准备URL
        String url = "jdbc:mysql://localhost:3306/fruitdb";
        //3-2.准备用户名
        String user = "root";
        //3-3.准备密码
        String pwd = "123456";
        Connection conn = DriverManager.getConnection(url,user,pwd);

        System.out.println("conn = " + conn);
    }
}
2)JDBC对数据库进行增、删、改
package com.atguigu.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Demo02 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.通过驱动管理器获取连接对象
        //url 表示 和 数据库通信的地址
        //如果url中需要带参数,则需要使用?进行连接
        //如果需要带多个参数,则从第二个参数开始使用&连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useSSL=false&useUnicode=true&characterEncoding=utf-8","root","123456");
        //3.编写SQL语句
        //id , fname , price , fcount , remark
        String sql = "insert into t_fruit values(0,?,?,?,?)";
        //4.创建预处理命令对象
        PreparedStatement psmt = conn.prepareStatement(sql);
        //5.填充参数
        psmt.setString(1,"草莓");
        psmt.setInt(2,10);
        psmt.setInt(3,30);
        psmt.setString(4,"草莓很好吃");
        //6.执行更新(增删改),返回影响行数
        int count = psmt.executeUpdate();
        System.out.println(count > 0 ? "添加成功!" : "添加失败!");
        //7.释放资源(关闭连接 , 先关闭psmt,后关闭conn)
        psmt.close();
        conn.close();
    }
}

修改和删除:

package com.atguigu.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

//JDBC - 修改和删除
public class Demo03 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Fruit fruit = new Fruit(33,"猕猴桃","猕猴桃是水果之王");

        Class.forName("org.gjt.mm.mysql.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
        String sql = "update t_fruit set fname = ? , remark = ? where fid = ? " ;
        PreparedStatement psmt = conn.prepareStatement(sql);
        psmt.setString(1,fruit.getFname());
        psmt.setString(2,fruit.getRemark());
        psmt.setInt(3,fruit.getFid());

        int count = psmt.executeUpdate();

        System.out.println(count > 0 ? "修改成功!" : "修改失败!");

        psmt.close();
        conn.close();
    }
}

//JDBC - 修改和删除
public class Demo04 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.gjt.mm.mysql.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
        String sql = "delete from t_fruit where fid = ? " ;
        PreparedStatement psmt = conn.prepareStatement(sql);
        psmt.setInt(1,6);
        int count = psmt.executeUpdate();

        System.out.println(count > 0 ? "删除成功!" : "删除失败!");

        psmt.close();
        conn.close();
    }
}

3)JDBC对数据库进行查询操作

查询所有的库存:

package com.atguigu.jdbc;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//JDBC - 查询所有的库存
public class Demo05 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.gjt.mm.mysql.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
        //3.编写SQL语句
        String sql = "select * from t_fruit" ;
        //4.创建预处理命令对象
        PreparedStatement psmt = conn.prepareStatement(sql);
        //5.执行查询,返回结果集
        ResultSet rs = psmt.executeQuery();
        //6.解析结果集
        List<Fruit> fruitList = new ArrayList<>();
        while(rs.next()){
            //1表示读取当前行的第一列的数据
            //getInt , 因为这一列是int类型,所以使用getInt
            //getInt(结果集的列名)
            //int fid = rs.getInt("fid");
            int fid = rs.getInt(1);
            String fname = rs.getString("fname");
            int price = rs.getInt(3);
            int fcount = rs.getInt(4);
            String remark = rs.getString(5);

            Fruit fruit = new Fruit(fid , fname , price , fcount , remark );
            fruitList.add(fruit);
        }
        //7.释放资源
        rs.close();
        psmt.close();
        conn.close();

        fruitList.forEach(System.out::println);
    }
}

查询指定fid的库存记录:

package com.atguigu.jdbc;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//JDBC - 查询指定fid的库存记录
public class Demo06 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.gjt.mm.mysql.Driver");
        //url ->    jdbc:mysql://ip:port/dbname?参数列表
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
        //3.编写SQL语句
        String sql = "select * from t_fruit where fid = ? " ;
        //4.创建预处理命令对象
        PreparedStatement psmt = conn.prepareStatement(sql);
        //5.填充参数
        psmt.setInt(1,33);
        //6.执行查询,返回结果集
        ResultSet rs = psmt.executeQuery();
        //6.解析结果集

        if(rs.next()){
            int fid = rs.getInt(1);
            String fname = rs.getString("fname");
            int price = rs.getInt(3);
            int fcount = rs.getInt(4);
            String remark = rs.getString(5);
            Fruit fruit = new Fruit(fid , fname , price , fcount , remark );
            System.out.println(fruit);
        }
        //7.释放资源
        rs.close();
        psmt.close();
        conn.close();
    }
}
4) 添加操作时获取自增列主键值
package com.atguigu.jdbc;

import java.sql.*;

//JDBC - 查询指定fid的库存记录
public class Demo07 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.gjt.mm.mysql.Driver");
        //url ->    jdbc:mysql://ip:port/dbname?参数列表
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
        //3.编写SQL语句
        String sql = "select count(*) from t_fruit" ;
        //4.创建预处理命令对象
        PreparedStatement psmt = conn.prepareStatement(sql);
        //5.执行查询,返回结果集
        ResultSet rs = psmt.executeQuery();
        //6.解析结果集
        if(rs.next()){
            int count = rs.getInt(1);
            System.out.println("总记录条数:"+count);
        }
        //7.释放资源
        rs.close();
        psmt.close();
        conn.close();
    }
}
5)批处理
  • 1
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

唯时

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值