mysql技术总结
JavaEE：企业级开发，主要就是web
前端（页面-展示数据）、后端（数据逻辑）、数据库（保存数据）
1，简介
数据库（db，database），数据仓库，用于存储数据。
关系型数据库（sql）：行、列
- mysql、oracle、sql server、db2、sqlLite
- 通过表和表之间，行和列之间的关系进行数据存储，用户信息系统
非关系型数据库（nosql，not only sql）：{key：value}
- 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安装的，当然也可以直接在官网下载安装.
# 安装最新版的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，数据准备
-- 数据准备
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默认的索引类型
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();// 出现问题回滚，其实出现问题会默认回滚，该处为显示回滚
}