mysql技术总结

JavaEE：企业级开发，主要就是web

前端（页面-展示数据）、后端（数据逻辑）、数据库（保存数据）

1，简介

数据库（db，database），数据仓库，用于存储数据。

关系型数据库（sql）：行、列

mysql、oracle、sql server、db2、sqlLite
通过表和表之间，行和列之间的关系进行数据存储，用户信息系统

非关系型数据库（nosql，not only sql）：

redis、MongoDB
非关系型数据库，对象存储，通过对象的自身属性来决定

数据库管理系统（DBMS，database management system）：

科学有效的管理数据，维护和获取数据
mysql本质就是一个数据库管理系统

MySQL是一个关系型数据库管理系统，由瑞典[MySQL AB](https://baike.baidu.com/item/MySQL AB/2620844) 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

2，安装

2.1，安装mysql

我这边直接使用docker安装的，当然也可以直接在官网下载安装.

Docker不适合部署数据库的原因

# 安装最新版的8.0.xmysql
docker run -d --name ac-mysql \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /opt/docker/mysql/data:/var/lib/mysql \
mysql:latest


# 进入docker容器，执行命令进入mysql的命令行（注意密码要紧贴着-p才有效）
mysql -uroot -p123456

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; -- 更新密码

注意：8.x.x版本的数据库连接时需要额外设置 allowPublicKeyRetrieval=true

2.2，安装DBeaver

dbeaver是免费和开源（GPL）为开发人员和数据库管理员通用数据库工具。

易用性是该项目的主要目标，是经过精心设计和开发的数据库管理工具。免费、跨平台、基于开源框架和允许各种扩展写作（插件）。它支持任何具有一个JDBC驱动程序数据库。它可以处理任何的外部数据源。

我这边使用的这个免费的管理工具，还挺好用的。

备注：连接上后，可以勾选显示系统对象

create DATABASE if not exists test;
show databases;
use test -- 切换数据库 不需要；号
show tables;

-- 单行注释
/*
多行注释
*/

3，操作数据库

操作库 > 表 > 字段，mysql的关键字不区分大小写

DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）

固定的语法和关键字需要记住！

3.1，库操作

创建数据库、数据表&字段

create DATABASE if not exists test;		-- 增
use test; -- 使用
show databases;	
drop database if exists test;	-- 删除
show databases;

-- auto_increment 自增
CREATE table if not exists `student`(
	`id` int(4) not null auto_increment comment '学号',
	`name` varchar(30) not null default '匿名' comment '姓名',
	`pwd` varchar(20) not null default '123456' comment '密码姓名',
	`sex` varchar(2) not null default '未知' comment '性别',
	`birthday` datetime default null comment '出生日期',
	`address` varchar(100) default null comment '家庭地址',
	`email` varchar(50) default null comment '邮箱',
	primary key(`id`) 
)engine=innodb default charset=utf8;

-- 显示创建的命令
show CREATE database school;
show CREATE table student ;
desc student ;

备注：mysql 字段或者库名、表名使用`符号，与我之前使用的postgres不一样！

3.2，数据库列操作

数值、字符串、时间日期、null（空值，不要使用null进行运算）

数值

type	数据量	字节数
tinyint	十分小	1
smallint	较小	2
mediumint	中等	3
int	标准的整数	4
bigint	较大的数据	8
float	浮点数	4
double	浮点数	8
decimal	字符串形式的浮点数（金融计算精度）	decimal类型怎么算字节数

举个例子，数据库的商品售价字段类型被定义为Decimal(18,9)，18这个数代表着商品售价最长可以到18位，而9这个数字表示小数点后面有9位数字，那么18-9=9，也就得出了整数位可以有9位。

对于decimal类型来说，每4个字节存9个数字，那么以上数据一共有18位，所以会有18/9x4=8,再加上小数点会占一个字节，所以8+1=9，因此decimal(18,9)占用9个字节。

字符串

type	描述	字节数
char	字符串固定大小	0~255
varchar	可变字符串（常用）	0~65535
tinytext	微型文本	2^8-1
text	文本串（保存大文本）	2^16-1

日期

type	描述	字节数
date	YYYY-MM-DD，日期	0~255
time	HH:mm:ss 时间格式	0~65535
datetime	YYYY-MM-DD HH:mm:ss
timestamp	时间戳，1970.1.1到现在的毫秒数	常用

3.3，字段属性

Unsigned：

无符号整数
声明该列不能为负值

zerofill：

0填充
不足的位数使用0填充，int（3）----003

自增：

自动在上一条记录的基础上+1（默认值是+1，也可以自定义配置步长）
通常用于设计唯一的主键~index，必须是整数

非空：

假设设置为not null，如果不给它设置值，就会报错！
null，

备注：每一个表都必须存在下面的五个字段

id 主键、'version' 乐观锁、is_delete 伪删除、gmt_create创建时间、gmt_updatex修改时间

3.4，数据表的类型

数据库引擎主要有innodb、myisam

引擎类型	MYISAM	INNODB
事务支持	no	yes
数据行锁	no，表锁	yes
外键约束	no	yes
全文索引	yes	no
表空间大小	较小	较大，约为2倍

常规使用操作：

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

在物理空间存在的位置

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

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

INNODB在数据库表中，有一个*.ibd文件（以student表为例）
myisam在数据表中有三个文件（以teacher表为例）：
- .sdi，表结构的定义文件
- .MYD，数据文件
- .MYI，索引文件

数据库表的字符集编码（charset=utf8），不设置的话，会使用mysql默认的字符集编码Latin1（不支持中文）。1，可以在创建的时候设置（建议使用这个，鲁棒性更高）；2，也可以直接在my.ini里面配置默认编码charset=utf8

3.5，修改删除表

所有的创建和删除尽量加上判断以免报错

alter table teacher rename as teacher1;
alter table teacher1 add `other` varchar(255);
alter table teacher1 modify `other` int(1);	-- 不可以重命名，只能修改字段类型和约束
alter table teacher1 change `other` `other1` varchar (11); --change用来重命名，不能修改字段约束

alter table teacher1 drop `other1`;	-- 删除数据
drop table if exists teacher1 ;

注意：

`` 字段名，使用这个符号包裹
注释 -- ， /**/
sql关键字大小写不敏感，建议大家写小写的
所有的符号全部使用英文

3.6，外键

了解即可

-- 1，直接创建表的时候，添加外键
-- 先创建外键的表
CREATE table `grade`(
	`gredeid` int(10) not null auto_increment comment 'id',
	`name` varchar (30) comment '年级名称',
	primary key (`id`)
)engine=innodb default charset=utf8;

-- 定义外键key
CREATE table if not exists `student`(
	`id` int(4) not null auto_increment comment '学号',
	`name` varchar(30) not null default '匿名' comment '姓名',
	`pwd` varchar(20) not null default '123456' comment '密码姓名',
	`sex` varchar(2) not null default '未知' comment '性别',
	`birthday` datetime default null comment '出生日期',
	`gredeid` int(10) not null comment '学生年级',
	`address` varchar(100) default null comment '家庭地址',
	`email` varchar(50) default null comment '邮箱',
	primary key(`id`) ,
	key `fk_gradeid`(`gredeid`),
	constraint `fk_gradeid` foreign key (`gredeid`) references `grade`(`gredeid`)
)engine=innodb default charset=utf8;

-- 2，也可以在表创建好之后，通过alter来添加外键
alter table `student` add constraint `fk_gradeid` foreign key (`gredeid`) references `grade`(`gredeid`);

-- 删除表的时候，必须要先删除外键字段的来源表，之后才能删除这张表
Cannot drop table 'grade' referenced by a foreign key constraint 'fk_gradeid' on table 'student'.

备注：以上都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰）

阿里巴巴编码规范：【强制】不得使用外键与级联，一切外键概念必须在应用层级解决。

最佳实践

数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
我们想使用多张表的数据，想使用外键（程序去实现）

4，数据库管理

4.1，DML

数据库的意义：数据存储与管理

4.1.1，insert

INSERT into grade  (`name`)values('三年级一班'); -- 如果不填写字段它会一一匹配

INSERT into student (`name`,gredeid)values('张三',1);

INSERT into student (`name`,gredeid)values('张三',1),('李四',1); -- 批量

注意事项：

字段和字段之间使用英文逗号隔开
字段是可以省略的，但是后面的值必须要一一对应
可以插入多条数据，values后面的值，需要使用英文逗号隔开 values(),(),()

4.1.2，update

UPDATE
	`student`
set
	`name` = 'wanyu',
	address = 'hangzhou',
	birthday = now()
where
	id = 1;

操作符	含义	样例	结果
=	等于	5=6	false
<> 或者!=	不等于	5<>6	true
>
<
>=
<=
between ... and ...	在范围内	[2,5]
and	逻辑与
or	逻辑或

注意:

column_name是数据库的列，尽量带上``
条件筛选，如果没有指定，会修改全量的列
value，是一个具体的值，可以是一个变量
多个设置属性之间，使用英文逗号隔开

4.1.3，delete

DELETE from student WHERE id = 2; -- 当前企业级的删除都是逻辑软删除
DELETE from student; -- 谨慎使用该语句，小心被通报！

TRUNCATE`student`;-- 清空数据表，表结构和索引不会变

-- 测速区别
CREATE table `test`(
	`id` int(4) not null auto_increment,
	`coll` varchar(20) not null,
	primary key(`id`)
)engine=innodb default charset=utf8;

INSERT into `test` (`coll`) values ('1'),('2'),('3');
SELECT * from `test`;
DELETE from `test`;
INSERT into `test` (`coll`) values ('1'),('2'),('3');
SELECT * from `test`;

TRUNCATE`test`;
INSERT into `test` (`coll`) values ('1'),('2'),('3');
SELECT * from `test`;

DELETE与TRUNCATE区别：

相同点：都能删除数据，都不会删除表结构
不同点：
- truncate 重新设置自增列（计数器规律）
- truncate不会影响事务

了解即可：delete删除的问题，重启数据库，现象

如果使用innodb，自增序列会从1开始（存储在内存中，类似redis）
如果使用MyISAM，继续使用上一个自增量（存在文件中，不会丢失）

4.2，DQL

data quary language 数据查询语言

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

4.2.1，数据准备

狂神说Java MySQL P16 school.sql

-- 数据准备
CREATE DATABASE  `school`;
USE `school`;

/*Table structure for table `grade` */

DROP TABLE IF EXISTS `grade`;

CREATE TABLE `grade`
(
    `GradeID`   INT(11)     NOT NULL AUTO_INCREMENT COMMENT '年级编号',
    `GradeName` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`GradeID`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

/*Data for the table `grade` */

INSERT INTO `grade`(`GradeID`, `GradeName`)
VALUES (1, '大一'),
       (2, '大二'),
       (3, '大三'),
       (4, '大四'),
       (5, '预科班');

/*Table structure for table `result` */

DROP TABLE IF EXISTS `result`;

CREATE TABLE `result`
(
    `StudentNo`     INT(4)   NOT NULL COMMENT '学号',
    `SubjectNo`     INT(4)   NOT NULL COMMENT '课程编号',
    `ExamDate`      DATETIME NOT NULL COMMENT '考试日期',
    `StudentResult` INT(4)   NOT NULL COMMENT '考试成绩',
    KEY `SubjectNo` (`SubjectNo`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

/*Data for the table `result` */

INSERT INTO `result`(`StudentNo`, `SubjectNo`, `ExamDate`, `StudentResult`)
VALUES (1000, 1, '2013-11-11 16:00:00', 85),
       (1000, 2, '2013-11-12 16:00:00', 70),
       (1000, 3, '2013-11-11 09:00:00', 68),
       (1000, 4, '2013-11-13 16:00:00', 98),
       (1000, 5, '2013-11-14 16:00:00', 58);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student`
(
    `StudentNo`    INT(4)       NOT NULL COMMENT '学号',
    `LoginPwd`     VARCHAR(20) DEFAULT NULL,
    `StudentName`  VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `Sex`          TINYINT(1)  DEFAULT NULL COMMENT '性别，0或1',
    `GradeId`      INT(11)     DEFAULT NULL COMMENT '年级编号',
    `Phone`        VARCHAR(50)  NOT NULL COMMENT '联系电话，允许为空',
    `Address`      VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
    `BornDate`     DATETIME    DEFAULT NULL COMMENT '出生时间',
    `Email`        VARCHAR(50)  NOT NULL COMMENT '邮箱账号允许为空',
    `IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`StudentNo`),
    UNIQUE KEY `IdentityCard` (`IdentityCard`),
    KEY `Email` (`Email`)
) ENGINE = MyISAM
  DEFAULT CHARSET = utf8;

/*Data for the table `student` */

INSERT INTO `student`(`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`, `Email`,
                      `IdentityCard`)
VALUES (1000, '123456', '张伟', 0, 2, '13800001234', '北京朝阳', '1980-01-01 00:00:00', 'text123@qq.com',
        '123456198001011234'),
       (1001, '123456', '赵强', 1, 3, '13800002222', '广东深圳', '1990-01-01 00:00:00', 'text111@qq.com',
        '123456199001011233');

/*Table structure for table `subject` */

DROP TABLE IF EXISTS `subject`;

CREATE TABLE `subject`
(
    `SubjectNo`   INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `ClassHour`   INT(4)      DEFAULT NULL COMMENT '学时',
    `GradeID`     INT(4)      DEFAULT NULL COMMENT '年级编号',
    PRIMARY KEY (`SubjectNo`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

/*Data for the table `subject` */

INSERT INTO `subject`(`SubjectNo`, `SubjectName`, `ClassHour`, `GradeID`)
VALUES (1, '高等数学-1', 110, 1),
       (2, '高等数学-2', 110, 2),
       (3, '高等数学-3', 100, 3),
       (4, '高等数学-4', 130, 4),
       (5, 'C语言-1', 110, 1),
       (6, 'C语言-2', 110, 2),
       (7, 'C语言-3', 100, 3),
       (8, 'C语言-4', 130, 4),
       (9, 'Java程序设计-1', 110, 1),
       (10, 'Java程序设计-2', 110, 2),
       (11, 'Java程序设计-3', 100, 3),
       (12, 'Java程序设计-4', 130, 4),
       (13, '数据库结构-1', 110, 1),
       (14, '数据库结构-2', 110, 2),
       (15, '数据库结构-3', 100, 3),
       (16, '数据库结构-4', 130, 4),
       (17, 'C#基础', 130, 1);

4.2.2，查询语句

SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]

select

SELECT * from student s ;
SELECT StudentNo ,StudentName  from student s ; 
SELECT distinct StudentNo ,StudentName  from student s ; -- 去重
SELECT StudentNo as 学号,StudentName as 姓名 from student s ; -- 给字段起别名
SELECT CONCAT('姓名:',StudentName) from student s ; -- 拼接字符串

SELECT * from `result` r ;
SELECT StudentNo,StudentResult +1  from `result` r ; -- 结果+1

SELECT VERSION(); -- 查询版本

SELECT 100*3-1 as result ;	-- 运算
SELECT @@auto_increment_increment; -- 查询自增步长


SELECT * from student s where StudentName like '%张%';
SELECT * from student s where StudentName like '张_';
SELECT * from student s where StudentName not like '张_';
SELECT * from student s WHERE StudentName in ('张伟');

where

作用检索符合条件的值，尽量使用英文字母

运算符	语法	描述
and,&&	a and b,a && b	逻辑与
or,\|\|	a or b,a\|\|b	逻辑或
not,!	not a,!a	逻辑非

连表查询

本质就是左中右三种，其他的都是条件限制导致

很多大公司基本上很少使用连表查询，因为不利于后续业务发展需要进行的分库、分表

-- 连表查询 --
SELECT s.StudentNo ,s.StudentName ,r.SubjectNo ,r.StudentResult  
FROM student s inner join `result` r 
on r.StudentNo = s.StudentNo ;

SELECT s.StudentNo ,s.StudentName ,r.SubjectNo ,r.StudentResult  
FROM student s right join `result` r 
on r.StudentNo = s.StudentNo ;


SELECT s.StudentNo ,s.StudentName ,r.SubjectNo ,r.StudentResult  
FROM student s left join `result` r 
on r.StudentNo = s.StudentNo ;

SELECT s.StudentNo ,s.StudentName ,r.SubjectNo ,r.StudentResult  
FROM student s left join `result` r on r.StudentNo = s.StudentNo 
inner join subject s2 on r.StudentNo = s2.SubjectNo ;

类型	描述
inner join	如果表中至少有一个匹配，就返回
left join	左表中的所有数据返回，即使有右表中没有匹配
right join	右表中的所有数据返回，即使有左表中没有匹配

自连接

自己的表盒自己的表连接，核心：1张表拆分郑2张一样的表，有父子层级关系

-- 
select gdp.code as "父级code",gdp."name" as "父子级名称",gd.code as "子级code",gd."name" as "子级名称"
from ge_department gd,ge_department gdp
where gd.parent_code = gdp.code ;

分页

SELECT *
from subject s 
limit 10 offset 10; -- limit<个数> offset<偏移量>

缓解数据库的压力，给人的体验更好，瀑布流

子查询(qian)

select *
from ge_department gd 
where parent_code in (select code from ge_department gd2 where name = 'xxx');

效率:子查询<关联查询，但是又因为后续业务发展分库、分表，一般又少用连表查

分组

group by , having

SELECT SubjectName  ,AVG(r.StudentResult) as '平均',min(r.StudentResult),max(r.StudentResult)
FROM `result` r 
left  join subject s 
on r.StudentNo = s.SubjectNo 
GROUP by s.SubjectNo 
HAVING 平均>10;

递归查询

4.2.3，函数

普通函数

select abs(-8);
SELECT ceiling (5.2); -- 向上取整
SELECT FLOOR(5.2) ;	-- 向下取整
SELECT RAND();	-- 1以内的随机数
SELECT sign(0);-- 判断负、0、正数
SELECT CHAR_LENGTH('hello'); -- 获取 长度
SELECT CONCAT('h','ello') ;
SELECT insert('hello world!',7,-1,'wanyu');
SELECT UPPER('hello') ;
SELECT LOWER('ABC');
SELECT REPLACE ('hello world','world','wanyu');
SELECT SUBSTR('hello world',7,5) ; -- 偏移量、个数
SELECT REVERSE('hello world iloveyou') ;

-- 时间日期
SELECT NOW() ;
SELECT CURRENT_TIME() ; 
SELECT CURRENT_DATE() ; 
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 count(*)、Count(1)、Count(0)的区别和执行效率比较

函数名称	描述
count()	计数，列（忽略null）、*、1
sum()	求和
avg()	平均值
max
min

数据库级MD5加密

MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。MD5由美国密码学家罗纳德·李维斯特（Ronald Linn Rivest）设计，于1992年公开，用以取代MD4算法。这套算法的程序在 RFC 1321 标准中被加以规范。1996年后该算法被证实存在弱点，可以被加以破解，对于需要高度安全性的数据，专家一般建议改用其他算法，如SHA-2。2004年，证实MD5算法无法防止碰撞（collision），因此不适用于安全性认证，如SSL公开密钥认证或是数字签名等用途。

MD5不可逆，MD5破解的原理其实就是一个字典！

SELECT md5('123456');

INSERT into testmd5 values 
(1,'wanyu','123456'),
(2,'zhangsan','123456'),
(3,'lisi','123456');

UPDATE testmd5  set pwd = MD5(pwd) WHERE id =1;
SELECT * from testmd5 ;
INSERT into testmd5 values 
(4,'wanyu',md5('123456'));

SELECT (md5('123456') = pwd) as flag,testmd5.*
FROM testmd5;

5，事务

什么是事务，将一组sql放在一个批次去执行，要么全部成功，要么全部失败

ACID：原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（duration）

原子性（atomicity）：普朗克常量、最小不可分
一致性（consistency）：事务前后的数据完整性要保证一致性
隔离性（isolation）：互斥，多个事务互不干扰
持久性（duration）：一旦提交不可逆，事务结束后，数据被持久化

隔离性导致的问题

脏读：一个事务读取到另外一个未提交事务的数据
不可重复读：在一次事务内多次读取的结果不一样
虚读（幻读）：一个事务内读取到别人新插入的数据

mysql默认是开启事务自动提交的

-- 事务
set autocommit=1; -- 0关闭、1开启

start transaction;	-- 事务开始
insert xx1;
insert xx2;
commit;	-- 提交，事务结束

rollback; -- 回滚，返回到原来的情况
savepoint -- 保存点，设置一个事务保存点
release savepoint -- 撤销保存点

6，索引

索引（index）是帮助数据库更高效的获取数据的数据结构！

6.1，索引分类

主键索引（primary key）
- 唯一标识，主键不可重复，一张表中只能有一个主键
唯一索引（unique key）
- 避免重复的列出现，一张表中可以由多个唯一索引
常规索引（key/index）
- 默认的，index，key关键字来设置
全文索引（fulltext）
- 在特定的数据库引擎才支持，myisam

索引的使用

创建表的时候给字段添加索引
创建完毕后，增加索引

show index from `student`;	-- 查询表中的索引
alter table student add fulltext  index `studentName`(`studentName`);

-- explain 分析sql执行的状况
explain select * from student ; -- 非全文索引
explain SELECT * from student s WHERE MATCH(StudentName)  against('刘');

6.2，测试

数据准备

-- 创建表
CREATE table `app_user` (
	`id` bigint(20) unsigned not null auto_increment,
	`name` varchar (50) default ' ',
	`email` varchar (50) not null ,
	`phone` varchar (20) default '' ,
	`gender` tinyint (4) unsigned default 0  ,
	`password` varchar (100) not null  ,
	`age` tinyint (4) default 0  ,
	`create_time` datetime default current_timestamp ,
	`update_time` timestamp null default current_timestamp on
UPDATE	current_timestamp ,	
	primary key(`id`)
)engine = innodb 
default charset = utf8 ;

-- 创建function
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),'12345@qq,com',concat('18', floor(rand()*((9999999-100000)+ 10000000000))),floor(rand()* 2),UUID(),floor(rand()* 100));
		set i = i +1;
	end while ;
	return i;
end;-- mysql-8.x.x版本需要额外加命令deterministic

SELECT mock_data();	-- 执行function

测试数据

SELECT * FROM app_user au WHERE `name`='用户9999'; -- 耗时0.79s
explain SELECT * FROM app_user au WHERE `name`='用户9999'; -- 由于没有索引，查询要遍历所有数据！

CREATE index id_app_user_name on app_user (`name`);--创建一个所有

SELECT * FROM app_user au WHERE `name`='用户9999'; -- 9ms
explain SELECT * FROM app_user au WHERE `name`='用户9999';

6.3，索引原则

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

索引的数据结构

hash类型的索引

Btree：innodb默认的索引类型

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

7，权限与备份

7.1，权限

CREATE USER 'wanyu'@'%' IDENTIFIED BY '123456';-- 创建用户，下面的是给与权限
grant all privileges on *.* to wanyu; -- 所有的库、所有的表授予全部权限,除了授权权限以外
show grants for wanyu;	-- 查看某用户权限
revoke all privileges on *.* from wanyu;	-- 撤销所有权限
drop user wanyu; -- 删除用户

set password = password('123456');-- 修改当前账户密码
set password for wanyu = password('123456');-- 修改当前账户密码
rename user wanyu to wanyu2; -- 修改名称
show grants for root@localhost;

7.2，备份

为什么要备份数据：

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

mysql备份数据的方式：

直接拷贝物理文件（因为数据库存储在硬盘）
使用dbeaver等工具拷贝出
使用命令行导出dump数据

# 导出多张表 表1 表2 表3
mysqldump -hlocalhost -uwanyu -p school student > /var/lib/mysql/schoolStudent.sql

# 直接导出数据库
mysqldump -hlocalhost -uwanyu -p school > /var/lib/mysql/school.sql

# 导入
# 登录的情况下，切换到指定的数据库，执行如下命令
source /var/lib/mysql/school.sql

8，规约数据库设计

8.1，数据库设计

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

糟糕的数据库设计：

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

良好的数据库设计：

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

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

分析需求：分析业务和需求处理的数据库需求
概要设计：设计关系图、ER图

设计数据库步骤（类似博客）：

收集信息，分析需求
- 用户表（用户登录注销，个人信息）
- 分类表（文件分类）
- 文章表（文章的信息）
- 评论表（评论信息）
- 友链表（友链信息）
- 自定义表（类似redis k-v这种）
标识实体（把需求落实到每个字段）
标识实体之间的关系
- 写博客：user->blog
- 创建分类：user->category
- 关注：user->user
- 友链：links
- 评论：user-user-blog

8.2，三大范式

为什么需要数据规范化？

信息重复
更新异常
插入异常
- 无法正常显示信息
删除异常

三大范式（规范数据库）

第一范式（1NF）

原子性（最小不可分）

第二范式（2NF）

每张表只描述一件事情，和主键直接相关（前提满足第一范式）

第三范式（3NF）

确保数据表中的每一列数据都和主键直接相关，而不能间接相关（前提满足第一、二范式）

关系型数据库设计：三大范式的通俗理解

规范与性能的问题：关联的表不得超过3张表

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

9，jdbc

9.1，数据库驱动

驱动：声卡、显卡、数据库

我们的程序会通过数据库驱动和数据库连接

9.2，jdbc

sun公司为了简化开发人员的（对于数据库的统一）操作，提供了一个（java操作数据库的）规范，俗称JDBC

这些规范的实现是由具体的厂商去实现，对于开发人员来说，只需要掌握JDBC接口的操作即可

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>

9.3，sql注入

sql存在漏洞，通过修改传入值获取到非预期数据

样例：

String sql = "select * from users where `name`=" +userName;

// 当我们输入具体的账户名预期会有对应的用户信息输出
// 但是当我们输入如下入参，就会返回所有数据！
userName = " '' or 1=1";

PreparedStatement 可以防止sql注入

区别使用占位符防止，手动给参数赋值

PreparedStatement st = conn.PreparedStatement("select * from users where `name`= ?");
st.setString(1,"");
st.executeQuery();

9.4，事务

try{
    conn.setAutoCommit(false);// 关闭自动提交事务后，就会开启事务
    st = conn.preparedStatement(sql1);
    st.executeUpdate();
    st = conn.preparedStatement(sql2);
    st.executeUpdate();
    conn.commit();// 提交事务
}catch(Exception e){
    conn.rollback();// 出现问题回滚，其实出现问题会默认回滚，该处为显示回滚
}

10，集群搭建

Mysql集群搭建

mysql各个集群方案的优劣

参考链接

MySQL最新教程通俗易懂

mysql官网

posted on 2022-03-07 00:19 周健康阅读(160) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

周健康的技术博客