MySQL认知与简单操作

数据库列的类型

数值

数值类型	描述	大小
tinyint	十分小的数值	1个字节
smallint	较小的数值	2个字节
mediumint	中等大小的数值	3个字节
int	标准的整数	4个字节 常用
bigint	较大的整数	8个字节
float	单精度浮点数	4个字节
double	双精度浮点数	8个字节
decimal	字符串形式的浮点数，通常用于金融计算

字符串

字符串类型	描述	大小
char	字符串固定的大小 会填充	0~255 ？
varchar	可变字符串 常用的字符串 不填充	0~65535 ？
tinytext	微型文本	2^8-1 ？
text	文本串	2^16-1

时间日期

时间日期类型	描述	格式
date	日期 格式	YYYY-MM-DD
time	时间格式	HH:mm:ss
datetime	时间日期格式 最常用	YYYY-MM-DD HH:mm:ss
timestamp	时间戳	1770.1.1到现在时间的毫秒数
year	表示年份

null

没有值，未知，尽量不要使用null进行运算，计算了结果也是null

整数和字符串总结：

int(M)：M指的是最大显示宽度，最大显示宽度为255，显示宽度和存储大小或类型包装的值无关。

varchar(M)：M个字符，可变长度。MySQL5.0之后：中英文一样。但在低版本中，英文肯定还是20，但中文要diao一点，一个占三个字节，所以存不了20个汉字，最多就6,7个

char(M)：M个字符，不可变长度，M范围（0~255）， 如果知道文本的长度，最好直接用定长char

char和varchar可以有默认值，test不能指定默认值。

浮点数总结：

float:浮点型，含字节数为4，32bit，数值范围为-3.4E38~3.4E38（7个有效位）

double:双精度实型，含字节数为8，64bit数值范围-1.7E308~1.7E308（15个有效位）

decimal:数字型，128bit，不存在精度损失，常用于银行帐目计算。（28个有效位）

因为在建表时，我价格字段就用的DECIMAL，之前用的float,
提问：为什么不用float?
回答：float，double等非标准类型，在数据库中保存的是近似值，而DECIMAL是以字符串的形式保存数值。打个比方，我存的是整数的时候，他就整数给我处理了，（举一反三？？）我存0.00给我实际存个0，我存14.00实际给我存个14，是都可以存浮点数，但是涉及到钱，金额这方面还是精准着来。

DECIMAL(M,D)
a指定指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38。
b指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0。

也就是说，M就是总长度，D就是小数点后面的长度。比如：
DECIMAL（5,4）=>总长度不超过5位数字，并且小数点后头必须要4位数字：1.2345
DECIMAL（14,9）=>总长度14位数字，整数5位，小数点后9位：12345.123456789

注意：
超出范围或者长度不够会被截断或补位
example:DECIMAL（9,4）

1. insert 12.12=>12.1200 因为小数点后未满4位，补0。
2. insert 12.12345=>12.1235 小数点只留4位，多余的自动四舍五入截断
3. insert 1234567.12=> 失败，因为小数点未满4位，补0变成1234567.1200，超过了9位。严格模式下报错，非严格模式存成999999.999。
4. 若插入的值未指定小数部分或者小数部分不足D位则会自动补到D位小数，若插入的值小数部分超过了D为则会发生截断，截取前D位小数。M值得是整数部分加小数部分的总长度，也即插入的数字整数部分不能超过M-D位，否则不能成功插入，会报超出范围的错误。

数据库的字段属性

• 列名
• 数据类型
• 长度
• 默认值 设置默认值，sex设置默认值为男，即， 记录中没有sex属性没有明确指定的情况下，默认设置为男
• 是否为主键
• 是否为空
• 是否为非负数（Unsigned ） :无符号的整数，声明了该列为Unsigned，值不能为负数
• 是否自增（高级设置可以更改自增量）自动在上一条记录的基础上加1，通常用来设计主键~index,必须是整数类型
• 是否零填充（Zerofill） 0填充，int (6) ，写入6，即为000006，填充直到达到指定宽度
• 是否更新
• 注释（comment,评论）

了解： 每一个表都必须存在以下5个字段。

• id 主键
• version 乐观锁
• is_delete 伪删除
• gmt_create 创建时间
• gmt_update 修改时间

DDL 数据库定义语言

创建数据库表

show create database school --查看创建数据库的语句
show create table student --查看Student数据表的定义语句   通过可视化窗口创建好表，调用这个语句，会得到原生创建表的语句，方便偷懒
desc  student --显示表的结构
--高级设置中可以添加表注释，校验，延迟键写入等内容

设置数据库表的字符集编码

(default) charset=utf8

不设置的话，默认编码（）不支持中文字符，最好在创建表的时候设置，尽量别通过改配置文件（my.ini中添加 character-set-server=uft8）去设置----导致本机上的数据库，放到其他默认编码的机器上运行的会炸掉。

修改和删除数据表字段

--修改表名
alter table teacher rename as teacher1
--增加表中字段
alter teacher1 add  age int(6) not null default 18 comment '年龄'
--修改表中的字段，（修改该字段名，修改字段类型和约束）
alter table teacher1 modify age verchar(11) --修改约束
alter table teacher1 change age,age1 -- 修改字段名
--删除表中的字段
alter table teacher1 drop age1
--删除表 （删除表结构和内容） truncate也属于DDL语言，和 delete 属于DML语言一样，只删除表中的数据，不删除表中的结构 
drop table if exists teacher1

结论：

• modify 能修改字段类型和约束，不能用来修改字段名
• change 用来修改字段名，不能修改字段类型和约束
• 创建和删除表的时候尽量加上，前提判断条件 if not exists or if exists，字段名尽量用’ '包裹

数据库级别的外键处理

• constraint 限制
• foreign key 外键
• references 引用

设置物理外键（数据库级别的外键）的方式：

1.在创建表的时候，增加约束（麻烦），不建议

--创建student表的时候执行以下语句
key 'FK_gradeid'('gradeid')        -- 从表字段               -- 主表字段，即本表中的grageid引用主表grade中的gradeid字段
constraint 'FK_gradeid' foreign key('gradeid') references 'grade' ('gradeid');
         --'FK_gradeid'为约束名

删除有外键的表的情况下，得先删除从表student （引用别人的表），才能删除主表grade（被人引用的表）

2.创建表成功后，利用修改字段操作，增加外键关系

--利用修改字段操作，增加外键关系
alter table 'student' add constranit 'FK_gradeid' foreign key('gradeid') references 'garde'('gardeid');

总结：
以上操作创建的外键是物理上的外键，即数据库级别的外键，（数据库中表多了，关系就非常乱，不好删）不建议使用
解决方法：
想象成数据库就是单纯的表，只用来存数据，只有行(记录)和列（字段）。当我们要使用多张表的数据，需要添加外键的逻辑就用Java程序去实现

关于数据库引擎（数据表类型）

engine=innodb

数据库引擎辨析

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文检索	支持	不支持
表空间大小	较小	较大，约2倍

MYISAM：节约空间，速度较快，早些年经常使用

INNODB：安全性高，支持事务处理和多表多用户操作 ，默认使用

物理空间位置

所有的数据库文件都存在data文件夹下,一个文件夹对应一个数据库，本质是文件存储

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

• MYISAM 在数据库表 对应文件： *.from -表结构定义文件， *.MYD - 数据文件(data）, *.MYI -索引文件（index）

• INNODB 在数据库表中只有一个*.from文件(表结构文件)和*.ibd文件(数据文件)，以及上级目录中的ibdata1数据文件

• .frm文件

  .ibd文件和.ibdata文件：

  这两种文件都是存放innodb数据的文件，之所以用两种文件来存放innodb的数据，是因为innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。

  独享表空间存储方式使用.ibd文件，并且每个表一个ibd文件

  共享表空间存储方式使用.ibdata文件，所有表共同使用一个ibdata文件

DML语言（数据库操作语言）

添加：

--插入语句  字段可以省略，后边的值要一一对应
insert  into 'grade'('gradename') values ('大一')
--一般写插入语句，一定要数据和字段一一对应

--一次插入多个记录
insert into 'grade'('gradename')values('大一'),('大二')

修改：

--修改表中数据
--将id为1的学生的age设置为5
update  'student' set 'age'=5,where 'id'=1;
--将所有学生都设置为5
update 'student' set 'age'=5

--一次修改一个记录中多个字段数据
update 'student' set 'age'=5,'name'='肖',where 'id'=1;

--多条件定位
update 'student' set 'age'=10 where  'name'='小鸟一号'and 'id'=1;

条件：where 子句 运算符 id 处在那个记录空间，就在哪个记录中修改

操作符 会返回布尔值

操作符	含义
==	等于
<> 或 !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
between 2 and 4	2<=id<=5

多条件定位操作符	含义
and	A&&B同时满足
or	A| | B，条件A,B满足一个

总结： 筛选的条件，如果没有指定，就会修改所有记录的该字段。set colnum_name = 具体的值（变量，当前时间等等）

删除：

delete

--删除整个表 ，表的结构和索引约束条件不变
delete from 'student'
--根据条件删除具体的字段
delete from 'student' where id=1;

truncate 命令

-- 删除整个表，表的结构和索引约束条件不变
truncate 'student' 

区别：

相同点：都能清空表中的数据，都不会删除表的结构

不同点：1.truancate 属于DDL语言，delete属于DML语言2.在功能上，truncate是清空一个表的内容，它相当于delete from table_name。
3.因此，用delete删除整个表的数据时，会产生大量的rollback（日志及回滚），占用很多的rollback segments， 而truncate不会。
4、在内存中，用delete删除数据，表空间中其被删除数据的表占用的空间还在，便于以后的使用。 5.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger(触发器) 6. 安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及。7. 速度,一般来说: drop>; truncate >; delete

DQL 数据库查询语言

Data Query Language 可以完成各种查询，数据库的核心语言，使用频率最高

使用格式：

select all|distinct
{table.*|table.feild1 as 'xxx'}
from table_name1 as 'xxx'
left|right|inner join table_name2 as 'xxx'
where ...
group by...
having ...
order by...
limit  (start,end)  --查询0到5 条记录  即 limit(0,5)

简单 select语句和别名使用

--查询所有的学生信息
select* from student
-- 查询指定的一个或几个字段
select 'name', 'age' from student
-- 查询指定的字段，并将字段赋予别名（别名不用打引号）   （也可以将表赋予别名）
select 'name' as 姓名, 'age' as 年龄 from student
--函数 concat （a,b）拼接函数
select concat('姓名：','name') as 新姓名 from student 

别名使用：有的时候，查询的字段的列名没有那么见名知意，就需要我们给查询出来的字段赋予别名

• 字段名 as 别名
• 表名 as 别名

去重操作和数据库的表达式

--查询哪些同学来上课了（同一个学生可能上不同的课，只需要统计该学生一次） , 发现重复数据就删除
select distinct 'StudentNo' from result 

数据库的列

--查询系统版本号（函数）
select version()
-- 用于简单计算（计算表达式）
select 100*5-100 as 结果
-- 查询自增的步长（变量）
select  @@auto_increment_increment
--成绩+1 查看
select 'name' as 名字,'grade'+1 as 成绩+1后 from student 

数据库中的表达式：文本，列，null, 计算表达式，系统变量，函数…

select 表达式 from 表

逻辑运算符和模糊查询（比较运算符）之Where 子句

检索 数据中 符合条件的值, 搜索条件由一个或多个表达式组成，Where 子句的返回值一定是布尔值

逻辑运算符

逻辑运算符	含义
and	A&&B A and B 同时满足
or	A| | B，A or B 条件A,B满足一个
not	not A 不满足A条件

--逻辑运算符
-- 成绩不等于100
select 'StudentNo','StudentResult' from result 
where StudentResult ！=100
-- != 等价于not
select 'StudentNo','StudentResult' from result 
where  not StudentResult=100

--查询成绩在95~100
select 'StudentNo','StudentResult' from result 
where StudentResult >=95 and StudentResult<=100
-- && 等价于 and
select 'StudentNo','StudentResult' from result 
where StudentResult >=95 && StudentResult<=100
--模糊查询（区间）
select 'StudentNo','StudentResult' from result 
where StudentResult between 95 and 100

模糊查询： 比较运算符

运算符	语法	描述
is null	a is null
is not null	a is not null
between and	a between b and c
like	a like b	SQL匹配，如果a匹配上b 则结果为真
in	a in ( a, a1,a3…)	假设a 在a1,a2,a3…其中的某一个值，结果为真

--模糊查询
=============like============
--查询姓为肖的同学，like 结合'%'（代表0到任意个字符）'_'（代表一个字符）
select 'StuentNo','StudentName' from Stuent
where StudentNmae like '肖%';
-- 查询姓为肖的同学，名只有一个字
select 'StuentNo','StudentName' from Stuent
where StudentNmae like '肖_';
-- 查询姓为肖的同学，肖在姓名中间
select 'StuentNo','StudentName' from Stuent
where StudentNmae like '%肖%';
=============in=============
--in中是具体的值，不能使用'%'或'_',这两者只能在like 中使用。
-- 查询 1001，1002，1003 号学员
select 'StudentNo','StudentName' from Student
where 'StudentNo' in('1001','1002','1003')
==========null  not null=====
-- 查询地址为空的同学
select 'StudentNo','StudentName' from Student
where address is null or address ='';
--查询出生日期不为空的同学
select 'StudentNo','StudentName' from Student
where bornDate is not null
=========between   and ======
--查询成绩在95~100
select 'StudentNo','StudentResult' from result 
where StudentResult between 95 and 100

联表查询

联表查询 Join on (where)

MySQL Join 的7种理论建表语句

--join on 联表查询        
--join on   连接查询（俩表之间判断的条件）     where  等值查询（具体细节筛选）
--inner join
select s.studentNo ,studentName,SubjectNo,StudentResult
from student as s
inner join result as r
on s.studentNo =r.studentNo;

--right join或left join 表面没啥区别 ，实际区别如下下
select s.studentNo ,studentName,SubjectNo,StudentResult
from student as s
right join result as r
on s.studentNo =r.studentNo;

-- right join 和left join 的区别:  如果只在student表种添加数据, 采用left join 查询，可以显示刚刚添加的数据在student表中的信息，若采用right join 查寻，则不显示信息
select s.studentNo ,studentName,SubjectNo,StudentResult
from student as s
left join result as r
on s.studentNo =r.studentNo;

-- 查询参加考试的同学，即使在student表中添加数据，数据的考试信息为空，照样不显示。
select s.studentNo ,studentName,SubjectNo,StudentResult
from student as s
right join result as r
on s.studentNo =r.studentNo;

-- 查询缺考的同学, 先查左表
select s.studentNo ,studentName,SubjectNo,StudentResult
from student as s
left join result as r
on s.studentNo =r.studentNo;
where  r.StudentResult is null

-- 查询 参加(数据结构1)考试的学生的信息：学号，姓名，科目名，分数
select s.studentNo,studentName,SbujectName,'StudentREsult'
from student s
right join result r
on s.studentNo=r.studentNo
inner join subject as sub
on r.SubjectNo=sub.SubjectNO;
where sub.SubjectName ='数据结构1';

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

总结：
join on 连接查询（俩表之间判断的条件） where 等值查询（具体细节筛选）
面对多表查询，先查询两张表，再慢慢增加。

自连接（特殊的联表查询）

创建自连接用表

category Id	pid(父类id)	category Name
2	1	信息技术
3	1	软件开发
4	3	数据库
5	1	美术设计
6	3	web开发
7	5	PS技术
8	2	办公信息
Auto	null	null

自己的表和自己的表连接，核心：一张表拆成两张一样的表

信息技术，软件开发，美术设计 继承于 父表 (pid 为1 的表—>顶级父表）

所以一级父表如下：

category Id	pid	category Name
2	1	信息技术
3	1	软件开发
5	1	美术设计

二级父表如下：

category Id	pid	category Name
8	2	办公信息
4	3	数据库
6	3	web开发
S7	5	PS技术

操作： 实现的查询效果：

一级父类	二级父类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	PS技术

-- 操作实现：
select 'f.categoryName' as '一级父类','z.categoryName' as '二级父类'
from  'Category' as f,'Category' as z
where f.categoryId = z.pid;

分页和排序

排序

升序：ASC 降序：DESC

select s.Student.No,StudentName,SubjectNasme,StudentResult
from student as s
inner join result r
on s.StudentNo= r.Student.No
inner join subject sub
on r.SubjectNo =sub.SubjectNo
Where subjectName ='数据结构1'
order by StudentResult ASC
limit 0, 5

分页

--第一页 limit 0,5          起始索引： （1-1）*5
--第二页 limit 5,10                   （2-1）*5    
--第三页 limit 10,15                  （3-1）*5    
--第n页 limit 15,20                   （n-1）*pageSize, pageSize
pageSize: 页面大小
(n-1)*pageSize: 起始值
n: 当前页
总页数： 数据总数/页面大小
limit(查询起始下标，pageSize)

练习： 查询Java第一学年 课程成绩再前10的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）

select s.'StudentNo','StudentName','SubjectName','StudentResult'
from 'Student' s
inner join 'result' r
on s.StudentNo = r.StudentNo
inner join 'subject' sub
on sub.'SubjectNo'=r.'SubjectNo'
where StudentResult>80 and SubjectName='Java'
order by StudentResult desc
limit 0,10

子查询

子查询本质：在where语句中嵌套一个子查询语句

形式：where(select * from)

-- 查询数据库结构1 的所有考试结果（学号，科目号，成绩） 降序排列
--方式一  连接查询（由外到里）
select 'StudentNo', r.'SubjectNo','StudentResult' 
from 'result' r
inner join 'subject' sub
on r.SubjectNo=sub.SubjectNo
where SubjectName='数据库结构1'
order by StudentResult desc
--方式二  子查询(由里到外)
select 'StudentNo', 'SubjectNo','StudentResult' 
from result
where SubjectNo=(
     select SubjectNo
     from  subject
    where SubjectName='数据库结构1'
)
order by StudentResult desc

--连接查询和子查询结合的方式，查询高数不小于80分的学生的学号，姓名
select distinct s.'StudentNo','StudentName'
from 'student' s
inner join result r
on r.StudentNo=s.StudentNo
where 'StudentResult'>=80 and 'SubjectNo'=(
   select SubjectNo
    from  'Subject'
    where SubjectName='高数'
)
--子查询的方式，查询高数不小于80分的学生的学号，姓名
select distinct 'StudentNo','StudentName'
from 'student' s
where StudentNo=(
     select  'StudentNo'
    from result
    where  'StudentResult'>=80 and 'SubjectNo'=(
   select SubjectNo
    from  'Subject'
    where SubjectName='高数')
)

where 子查询的效率更快，它只负责查询，不像 连接查询那样还得负责联表

函数

常用函数

数学运算

select abs(-8)
select ceiling(9.4)
select floor(9.4)
select rand()--返回0~1之间的一个随机数

字符串

select char_length('问哈他附近的卡')--获取字符串长度
select concat ('我','很开心')
select insert ('我做的饭难吃',5,2,'超级好吃')--我做的饭超级好吃
select lower('XiaoMeng')--XIAOMENG
select upper('XiaoMeng')--xiaomeng
select instr('我做的饭不要轻易去吃','饭')--打印4（第一次出现字串的索引）
select replace('这个过桥米线我吃吐了','过桥米线'，'方便面')--替换字符串
select substr('男生学习做饭还是挺好的',4,5)--打印'做饭还是挺好的' （筛选字串）
select reverse('helloworld')--打印 dlrowolleh  字符串反转

--练习 查询姓 周的同学，把姓改成邹
select replace(studentname,'周','邹')
from student
where studentname like'周'

时间日期函数

select current_data() --获取当前的日期
select curdate()-- 获取当前日期
select now()--获取当前的时间
select localtime()--获取本地时间
select year (now())  --获取当前的年份
select year (now())  --获取当前的年份
select year (now())  --获取当前的年份
select year (now())  --获取当前的年份
select year (now())  --获取当前的年份
select year (now())  --获取当前的年份

聚合函数

函数名称	描述
count()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值

--统计表中有多少记录
select count(studentname)from student--忽略所有的null
select count(*)from student-- 不会忽略null  本质：计算行数，但是这样查会将所有的字段（所有列）都走一遍
select count(1)from student--不会忽略null 本质：计算行数，这样查数据，只需要查询一个字段（一列）

--求所有学生成绩总和和平均值
select sum('studentResult') as '总和',avg('StudentResult') as '平均值' from result

--查询课程的平均分大于80的课程的平均分，最高分，最低分 
--核心：（根据不同的课程分组）
select SubjectName,avg(StudentResult),Max(StudentResult),min(StudentResult)
from result r
inner join 'subject' sub
on r.'SubjectNo'=sub.'SubjectNo'
Group by r.SubjectNo --通过什么字段分组
having avg(StudentResult)>80

DCL 数据控制语言

用户权限管理

use mysql;
# 查看用户表
select*from user;

# 创建新用户
create user xiao identified by '162656';

# 为当前用户修改密码
#set password =password('xxxxxxx');
# 为指定用户修改密码
#set password for xiao=password('162656');

-- 为新用户授予权限  database.table
-- *.*为用户授予全部权限(除了授权grant权限)
grant all privileges on *.* to xiao;

-- 查看普通用户权限   '@'%'代表localhost
show grants for xiao;

-- 查看管理员root权限   with grant option root用户具有授权权限
show grants for root@localhost;

-- 撤销权限    在那个库或表   被撤销者
revoke all privileges  on *.*  from xiao;

-- 删除用户
drop user  xiao ;

MySQL备份

数据导出：

-- mysqldump -h 主机 -u 用户-p密码  数据库  > 物理磁盘位置      导出数据库
mysqldump -hlocalhost -uroot -pxiaomeng xm > C:/a.sql
-- mysqldump -h 主机 -u 用户-p密码  数据库  表名  > 物理磁盘位置      导出数据库中某张表
mysqldump -hlocalhost -uroot -pxiaomeng xm > C:/a.sql
-- mysqldump -h 主机 -u 用户-p密码  数据库  表名1 表名2  > 物理磁盘位置      导出数据库中多张表
mysqldump -hlocalhost -uroot -pxiaomeng xm class student1 > C:/a.sql

数据导入：

-- 登录的状态下，use 某个数据库，然后source 备份文件.sql 就可以导入数据库中的表，如果是导入数据库，就不用切换到数据库，直接source 备份文件.sql导入
-- 不登陆的情况下，mysql -u 用户 -p 密码  (库名)< 备份文件.sql

TCL事务控制语言

事务管理（ACID）

• 原子性（Atomicity）
  原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性（Consistency）
  事务前后数据的完整性必须保持一致。
• 隔离性（Isolation）
  事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
• 持久性（Durability）
  持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

解释：

1.原子性： 原子性表示，这两个步骤一起成功，或者一起失败，不能只发生其中一个动作

2.一致性：针对一个事务操作前与操作后的状态一致

3.隔离性：针对多个用户同时操作，主要是排除其他事务对本次事务的影响

4.持久性：事务一旦提交，则不可逆转，即使服务器断电，服务器重启后数据也会被被持久化到数据库中，若事务未提交时，服务器断电，会回到提交前状态，不改变数据库内容。

隔离性的隐藏问题

• 脏读：指一个事务读取了另外一个事务未提交的数据。
• 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。
• 虚读（huan’du）是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

实际操作

use xm;
create table if not exists `shop`(
    `id`int(5)not null auto_increment primary key ,
    `money` decimal(10,5) not null ,
    `name` varchar(10)not null
)engine innodb,character set utf8;
alter table `shop` modify `money` decimal(10,2) not null ;
insert into `shop` (`money`,`name`)values (1000,'小鸟一号'),(200,'小鸟二号');
#模拟转账
#关闭事务自动提交
set autocommit =0;
# 开启事务
start transaction;
update shop set money=money-100
where `name`='小鸟一号';
update shop set money=money+100
where `name`='小鸟二号';
#提交事务
commit ;
#回滚
rollback ;
#开启事务自动提交
set autocommit =1;

索引

索引：是帮助MySQL高效获取数据的数据结构

索引分类

• 主键索引（PRIMARY KEY）
• • 唯一的标识，主键不可以重复，只能有一个列作为主键
• 唯一索引（UNIQUE KEY）
• • 数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
• 常规索引(KEY/INDEX)
• • 默认的index.key关键字来设置
• 全文索引(FULLTEXT)
• • 在特定的数据库引擎下才有，MYISM ，起到快速定位数据的作用

索引在小数据量的时候，区别不大，但在大数据量查询的时候，区别明显

设置索引的几种方式

--创建索引
CREATE INDEX indexName ON table_name (column_name)
--修改表结构（添加索引）
ALTER table tableName ADD INDEX indexName(columnName)
--创建表的时候指定列为索引
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);  
--删除索引
DROP INDEX [indexName] ON mytable; 
--查看表中所有的索引
SHOW INDEX FROM table_name

/*
不要对经常变动的表加索引
小数据量的表不要加索引
索引加在常用于查询的字段上
索引的数据结构：InnoDB的默认索引类型是BTree
*/

数据库级别实现MD5加密

MD5 ：第五代信息摘要算法 增强算法复杂度和不可逆性 ， MD5不可逆，具体值的MD5是一样的。

--update  更新某个字段
update school set pwd=md5(pwd)
where id=1;
--验证信息
select * from student
where 'name'='meng'and pwd=MD5('123456')

数据库设计

设计初衷

数据库比较复杂的时候数据库就需要设计

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

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

• 分析需求：分析业务和需要处理数据库的需求
• 概要设计：设计关系图E-R图

设计步骤：收集信息，分析需求，标识实体之间的关系

数据库三大范式

数据规范化的必要性：

避免信息重复，更新异常，插入异常，删除异常

• 第一范式(1NF)原子性：保证每一列的元素不可再分 比如 家庭信息列 可以分为 家庭人口列和家庭地址列
• 第二范式(1NF) 满足第一范式的前提下：每一张表只描述一件事情。1.非码属性必须完全（直接，或间接）依赖主键（在第一范式的基础上消除非主属性对主码的部分函数依赖）2. 确保数据库表中的每一列都和主键相关。针对表中的联合主键而言，确保数据库表中的每一列都和联合主键相关，不能只与主键的某一部分相关。
• 第三范式(1NF) 满足第二范式的前提下：任何非主属性不依赖于其他非主属性（消除传递依赖）确保数据库表中的每列数据都和主键直接相关，不能间接相关。 例如：学生表中存在 学号，姓名，性别，班主任名称，班主任年龄；其中所有的属性都完全依赖于学号主键，但是班主任年龄 直接依赖的是班主任名称，而不是主键学号，所以根据数据库第三范式的要求，把学生表进行拆分。即 学生表：学号，姓名，性别；教师表 ：班主任名称，班主任年龄；

规范化和性能问题：

关联查询的表不能超过三张表

• 考虑商业化的需求和目标（成本和用户体验）数据库的性能更加重要（变相的空间换时间）、
• 在规范性能的问题的时候，需要适当的考虑规范性
• 故意给表中增加一些冗余字段（多表查询变成单表查询，性能提升）
• 故意给表中增加列（例如：增加count列，每插入记录count+1,查询总记录数的时候直接查count ,而不用遍历整个表求和。或者加入索引，把大数据量的查询变成小数据量的查询，但是索引BTree更加占用内存）