MySQL部分知识梳理回顾

左右内连接

--内连接
select s.StudentNo,StudentName,SubjectNo,StudentResult
from student s
inner 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

--右连接
select s.StudentNo,StudentName,SubjectNo,StudentResult
from student s
right 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

--三表连接查询
select s.StudentNo,StudentName,SubjectNo,StudentResult,SubjectName
from student s
right join result r
on r.StudentNo = s.StudentNo
inner join subject sub
on r.SubjectNo = sub.SubjectNo

----from 表 xxx join 连接的表 on 交叉条件

自连接

一张表拆成两张一样的表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5QudLOjY-1618838002780)(C:\Users\dell\AppData\Roaming\Typora\typora-user-images\image-20210410140910463.png)]

---查询父子信息
select `categoryName` as `父栏目`,`categoryName` as `子栏目`
from `category` as a,`category` as b
where a.`categoryid` = b.`categoryid`

排序

---asc升序   desc降序
order by 字段名 asc/desc    

分页

• 缓解数据库压力
• 体验感更好，瀑布流应用

语法：limit（查询起始下标，pagesize）

网页应用：当前，总的页数，页面的大小

【pagesize：页面大小】

【（n-1）*pagesize：起始值】

【n：当前页】

【数据总数/页面大小=总页数】

每页显示五条

第一页： limit 0,5

第二页： limit 5,5

第三页： limit 10,5

---查询java第一学年课程成绩排名前十的学生并且分数要大于80的学生信息
select StudentNo,StudentName,StudentResult,SubjectName
from student s
inner join result r
on s.StudentNo = r.StudentNo
inner join subject sub
on r.SubjectNo = sub.SubjectNo
where SubjectName='Java第一学年' and StudentResult>=80 
order by StudentResult desc
limit 0,10

子查询和嵌套查询

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

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

----1、查询数据库结构1的所有考试结果（学号，科目编号，成绩）降序排列
---方式一：使用连接查询
select StudentNo,SubjectResult,r.SubjectNo
from result r
inner join subject sub
on r.SubjectNo = sub.SubjectNo
where SubjectName = '数据库结构1'
order by SubjectResult desc

---方式二：使用子查询
select StudentNo,SubjectResult,SubjectNo
from result
where SubjectNo=(
select SubjectNo 
from subject
where SubjectName='数据库结构1'
)
order by SubjectResult desc

---查询数据库结构1的学生学号
select SubjectNo from subject where SubjectName='数据库结构1'

--分数不小于80分的学生的学号和姓名
select distinct s.StudentNo,StudentName
from student s
inner join result r
on r.StudentNo=s.StudentNo
where StudentResult > 80

--高等数学2编号分数不小于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='高等数学2'
)

---查询课程编号高等数学2 分数不小于80分的学生的学号和姓名
select StudentNo,StudentName
from student 
where StudentNo IN(
    select StudentNo
    from result 
    where StudentResult > 80 AND StudentNo=(
    select SubjectNo 
    from subject 
    where SubjectName='高等数学2'
    )
)

Mysql函数

常用函数

----数学运算
select abs(-8)     ---绝对值
select Ceiling(9,4)    ---向上取整
select floor(9,4)  ---向下取整
select random()   ---返回一个0-1之间随机数
select sign()   ---判断一个数的符号  负数返回-1，正数返回1
----字符串函数
select char_length('天天开心')  ---字符串长度
select concat('w','a','n','m')   ---拼接字符串
select insert('我爱编程',1,2,'超级热爱')  ---查询，从某个长度开始替换某个长度
select lower('WWWW')   ----小写字母
select upper('wwww')    ----大写字母
select instr('qzvz','q')   ---返回第一次出现的字串的索引
select replace('努力就会成功','努力','坚持')    ----tihuan1
select substr('努力一定就会成功',4,5)  ---返回指定的字符串（源字符串，截取位置，截取长度）
select reverse('努力一定就会成功')   ---反转

---时间和日期函数
select current_date()    ---获取当前日期
select current()    ---获取当前日期
select now()   ---获取当前时间
select localtime  ---获取本地时间
select sysdate()  ---系统时间
select system_user()  ---当前系统用户
select version()   ---获取版本

聚合函数(常用)

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

having跟在group by后面

数据库级别的MD5加密

什么是MD5？

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

MD5不可逆，具体的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 charcet=utf8

---明文密码
insert into testmd5 values(1,'zhangsan','123456'),(1,'lisi','123456'),(1,'wangwu','123456')

---加密
update testmd5 set pwd=md5(pwd) where id=1    ---加密一行
update testmd5 set pwd=md5(pwd) ---加密全部密码

---插入时候加密
insert into testmd5 values(4,'zhangsan',md5('123456'))

如何校验，将用户传过来的密码，进行MD5加密，然后比对加密后的值

select * fromtestmd5 where name='xiaoming' and pwd=md5('123456')

事务

要么都成功，要么都失败

将一组sql放在一个批次中去执行

事务原则：ACID 原子性，一致性，隔离性，持久性 （脏读，幻读……）

**原子性：**针对一个事物

**一致性：**最终一致性，过程一致性，针对一个事务操作前后状态一致

**隔离性：**多个用户同时操作，主要是排除其他事务对本次事务的影响（互不干扰）

**持久性：**表示事务的结束状态不会随着外界原因导致数据丢失（事务未提交，恢复到原状，如果提交，持久化到数据库），事务一旦提交不可逆

隔离产生的问题

**脏读：**一个事务读取了另一个事务未提交的数据

**不可重复读：**在一个事务读取表中某一行数据时，多次读取结果不同

**幻读：**指在一个事务内读取了别人新插入的事务，导致前后读取不一致

执行事务

mysql默认开启事务，自动提交

set autocommit=0  关闭

set autocommit=1 开启
---事务开启
start transation   标记一个事务的开始，从这个之后的sql都在同一个事务内
insert  xx
insert  xx
---提交:持久化  (成功！)
commit
---回滚：回滚到原来样子(失败！)
rollback
---事务结束
set autocommit=1 开启自动提交
savepoint 保存点名   ---设置一个事物的保存点
rollback to savepoint 保存点名 ---回滚到保存点
release savepoint 保存点名   ---撤销保存点

模拟场景

---转账
create datebase shop character  set utf8 collate utf8_general_ci
use shop
create table account(
    `id` int(3) not null auto_increment,
    `name` varchar(10) not null,
    `money`  decimal(9,2) not null,
    primary key (`id`)
)engine=innerdb default charset=uf8

insert into account(`name`,`money`)
values('A',2000.00),('B',1000.00)

---模拟转账，开启事务
set autocommit=0;   ---关闭自动提交
start transactton   ---开启一个事务（一组事务）
update account set money=money-500 where `name`='A'  ---A减500
update account set money=money+500 where `name`='B'   ---B加500

commit;  ---提交事务，就被持久化
rollback;  ---回滚

set autocommit=1;    ---恢复默认值

索引介绍以及索引分类

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

索引的分类

• 主键索引 PRIMARY KEY :唯一标识，不可重复，只能有一个列作为主键
• 唯一索引 UNIQUE KEY :避免重复的列出现，可以重复，多个列都可以是唯一索引
• 常规索引 KEY/INDEX :默认的，index，key关键字设置
• 全文索引 FULLTEXT :在特定的数据库引擎下才有，MYISAM，快速定位数据

---索引的使用
---1、在创建表的时候给字段增加索引
---2、创建完毕后，增加索引

---显示所有索引信息
show index from student
---增加一个索引(索引名) 列名
alter table school.student add fulltext  index `studentName`(`studentName`);

---EXPLAIN 分析sql执行的状况
explain select * from student ;   ---常规索引，非全文索引

select * from student where matce(studentName) against('刘')
  

测试索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

---插入100万条数据
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
DETERMINISTIC
BEGIN
 DECLARE num INT DEFAULT 1000000;
 DECLARE i INT DEFAULT 0;
 WHILE i < num DO
  INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'24736743@qq.com',CONCAT('18',FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
  SET i = i + 1;
 END WHILE;
 RETURN i;
END $$
SELECT mock_data() -- 执行此函数 生成一百万条数据

select * from app_user where name='用户99999';
 
 ---id_表名_字段名
 ---create index 索引名 on 表（字段）
create index id_app_user_name on app_user(`name`);
select * from app_user where name='用户99999';

索引在小数据的时候，用处不大，但是大数据的时候，非常明显

索引原则

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

索引的数据结构

Hash 类型的索引

Btree：InnoDB的默认数据结构

用户管理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3v4es1WP-1618837941097)(C:\Users\dell\AppData\Roaming\Typora\typora-user-images\image-20210413144443425.png)]

SQL命令

用户表:mysql.user
本质：对这张表进行增删改查
---创建用户   create user 用户名 identified by 密码
create user zhangsan identified by '123456'


--修改密码(修改当前用户)
set password=password('111111')


--修改密码(修改指定用户)
set password for 用户 =password('密码')

---给用户重命名  rename user 原名 to 新名字
rename user zhangsan to lisi

---用户授权  grant all privileges授予全部权限  库.表
---grant all privileges授予全部权限 除了给别人授权不能做
grant all privileges on *.* to lisi

---查看权限
show grant for lisi    ---查看指定用户权限
show grant for root@localhost

---撤销权限
revoke all privileges on *.* from 用户


---删除用户
drop user 用户名

数据库备份

• 保证重要数据不丢失
• 做数据转移

MySQL数据库备份的方式

• 直接拷贝物理文件

• 在可视化工具sqlyog中手动导出

  在想要导出的表或者库中，右键选择备份或者导出

  选中sql转储

• 使用命令行导出 mysqldump 命令行使用

# mysqldump -h主机 -u用户 -p密码  数据库 表名  ->物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student   ->D:/a.sql
# mysqldump -h主机 -u用户 -p密码  数据库 表1 表2 表3  ->物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student teacher result  ->D:/b.sql
# mysqldump -h主机 -u用户 -p密码  数据库->物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school ->D:/c.sql


---导入
先登录数据库
use 数据库
source 备份文件
source d:/a.sql

规范数据库设计

当数据库表比较复杂时候，就需要设计了

糟糕的数据库设计：

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

良好的数据库设计：

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

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

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

设计数据库步骤（个人博客）

• 收集信息，分析需求

  ​ 用户表（用户登录注销，用户的个人信息，写博客，创建分类）

  ​ 分类表（文章分类，谁创建的）

  ​ 文章表（文章的信息）

  ​ 评论表

  ​ 友链表（友链信息）

  ​ 自定义表（系统信息，某个关键的字或者主字段） key：value

• 标识实体（把需求落地到每个字段）

  写博客 user—>blog

  创建分类 user—>categoy

  关注 user—>user

  友链

  评论 user—>user blog

三大范式

• 信息重复

• 更新异常

• 插入异常

  无法正常显示信息

• 删除异常

  丢失有效的信息

三大范式

第一范式（1NF）：

原子性，保证每一列不能再分

第二范式（1NF）：

满足第一范式前提下，每张表只描述一件事情

第三范式（1NF）：

满足第一范式和第二范式，第三范式需要确保表中的每一列数据和主见直接相关，而不是间接相关。

（规范数据库设计）

规范性和性能的问题

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

• 考虑商业化的需求和目标（成本，用户体验）数据库的性能更加重要
• 在规范性能的问题的时候，需要适当的考虑一下规范性
• 故意给某些表增加一些冗余的字段（从夺标查询中变为单表查询）
• 故意增加一些计算列（从大数据量降低为小数据量的查询；增加索引）