mysql技术总结
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安装的,当然也可以直接在官网下载安装.
# 安装最新版的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();// 出现问题回滚,其实出现问题会默认回滚,该处为显示回滚
}