mysql学习

1初识MySQL

JavaEE: 企业级Java开发 Web

只会写代码,学习数据库,基本混饭吃;

还需要学习: 数据结构＋算法!

离散数学,数字电路,体系结构,编译原理. +实战经验,高级程序员

1.1为什么要学数据库

• 岗位需求
• 现在的世界,大数据时代,得数据者得天下.
• 被迫需求: 存数据, 去IOE不用国外的产品
• 数据库是所有软件体系中最核心的存在 DBA:专门管理数据库的

1.2什么是数据库

数据库(DB,DataBase)

概念:数据仓库,软件,安装在操作系统之上的

作用:存数据,管理数据

1.3数据库分类

• 关系型数据库:行,列

  • MySQL
  • Oracle
  • Sql Server
  • DB2
  • SQLite

  通过表和表之间,列和列之间的关心进行存储

• 非关系型数据库

  • Redis
  • MongoDB

  以对象存储,通过对象自身的属性来决定.

DBMS(数据库管理系统)

• 数据库的管理软件
• MySQL, 数据库管理系统!

1.4MySQL简介

1. MySQL是一个**关系型数据库管理系统****，**由瑞典[MySQL AB](https://baike.baidu.com/item/MySQL AB/2620844) 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
2. MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
3. MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

1.5连接数据库

命令行连接!

mysql -uroot -p123456  --连接数据库
---------------------------
--所有的语句都用;(英文)结尾
show databases;  --查看所有数据库
mysql> user ***  --切换到数据库***
----
show tables;   --查看数据库中所有的表
describe ***;  --显示这张表的信息,和Navicat中的设计表相似
----
create database westos; --创建一个数据库

exit; --退出数据库连接
-- 单行注释
/*
多行注释
*/

1.6数据库语言

• DDL: 数据库定义语言
• DML:数据库操作语言
• DQL:数据库查询语言
• DCL:数据库控制语言

2操作数据库

操作数据库>操作数据库中的表>操作数据库中表的数据

2.1操作数据库(了解)

1. 创建数据库

   create DATABASE [if not EXISTS] school;   --[]可选
   

2. 删除数据库

   drop database if EXISTS school;
   

3. 使用数据库

   --如果表名或者字段名是一个特殊字符,就需要带 ` `
   use mybatis_plus
   

4. 查看数据库

   show databases;   --查看所有的数据库
   

学习思路:

• 对照Navicat可视化历史记录查看sql
• 固定的语法或者关键字必须要强行记住

2.2数据库的列类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节(最常用)
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串类型的浮点数 金融计算的时候一般使用decimal(精度问题)

字符串

• char 字符串大小 0-255
• varchar 可变字符串 0-65535(常用)
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1

时间日期

• data YYYY-MM-DD 日期格式
• time HH:mm:ss 时间格式
• datatime YYYY-MM-DD HH:mm:ss 最常用的时间格式
• timestamp 时间戳 1970.1.1到现在的毫秒数
• year 年份表示

null

• 没有值,空值
• 注意不要使用NULL进行运算,结果为NULL

2.3数据库的字段属性(重点)

Unsigned:

• 无符号的整数

• 声明了该字段不能声明为复数

零填充:

• 0填充的
• 不足位数的,使用0来填充, int(3), 输入5 存储的就是005

自增:

• 自动在上一条的基础上+1(默认)
• 通畅用来设计唯一的主键 ~index,必须是整数类型
• 可以自定义主键的起始值和步长

非空:

• 假设设置为 nut null, 如果不赋值们就会报错
• Null,如果不填写值,默认就是null

2.4使用语句建表

注意点:

• 使用英文(), 标的名称和字段尽量使用``括起来
• 字符串使用 ‘ ‘ 括起来
• 所有语句后面加 , 最后一个不用加
• primary key (‘id’)

drop table if EXISTS `student`;
create table if not EXISTS `student`(
 `id` int(4) not null AUTO_INCREMENT COMMENT '学号',
 `name` VARCHAR(30)  COMMENT '姓名',
 `password` VARCHAR(20) DEFAULT '123456' COMMENT '密码',
 `sex` varchar(2) not null DEFAULT '女' comment '性别',
 `birthday` TIMESTAMP not null DEFAULT '0000-00-00 00:00:00' COMMENT '出生日期', 
    
--CURRENT_TIMESTAMP
    
 PRIMARY key (id)
)ENGINE=INNODB charset=utf8 comment = '学生基础表'

格式:

create table [if not exists] `表名`(
	'字段名'  列类型 [属性] [索引] [注释],
    .....
    '字段名'  列类型 [属性] [索引] [注释],
    '字段名'  列类型 [属性] [索引] [注释]  --最后一个没有逗号
)[表类型] [字符集设置] [提示]

-- 一些操作
show create database school --查看数据库的创建语句
show  create table `student` --查看表的创建语句
DESC `student` --显示表的结构

2.5数据表的类型

1. INNODB (默认)
2. MYISAM(早些年使用的)

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	比较小	较大,约为2倍MYISAM

常规使用操作:

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

在物理空间存在的位置

所有的数据库文件都存在data目录下

本质还是文件的存储

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

• INNODB 在数据库表中只有一个 *.frm文件 以及上级目录下的ibdata1 文件

• MYISAM对应文件

  • *.frm -表结构的定义文件
  • *.MYD -数据文件(data)
  • *.MYI -存放索引的

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

CHARSET=utf8

不设置的话会是MySQL默认的字符集编码 (不支持中文!)

默认 : Latin1编码集

在my.ini中配置默认的编码

character-set-server = utf8

2.6修改删除表

修改

-- 修改表名
alter table `student` rename as `student1`;

-- 增加表的字段
alter table `student1` add email VARCHAR(20) not null comment '邮箱';

-- 修改表的字段 (重命名,修改约束!)
-- 修改约束
alter table `student1` MODIFY sex VARCHAR(3) DEFAULT '男';
-- 重命名 ＋ 修改约束  实测是可以修改约束的
alter table `student1` change sex sex1 varchar(2);
-- 删除表的字段
alter table `teacher1` drop email

删除

-- 如果表存在就删除
drop table if exists `teacher1`

所有的创建和删除操作最好加上判断,以免报错

注意点:

• 注意使用``,字段名使用这个包裹
• 注释的使用
• sql关键字大小写不敏感,最好小写
• 所有符号用英文,用中文就报错

3.Mysql的数据管理

3.1外键(了解)

3.2DML语言(背下来)

数据库意义: 数据存储,数据管理

DML语言: 数据操作语言

• insert
• update
• delete

3.3添加 insert

-- 插入语句
-- 由于主键自增,所以可以省略主键
insert into `grade` (gname) values ('17信工2班');

-- 如果不写表的字段,就会一一匹配
insert into `grade`  values (2,'17信工2班');

-- 一次插入多个值
insert into `grade` (gname) values ('17信工2班'),('17信工1班'),('16信工2班');

语法: insert into 表名 ([字段1],[字段2],[字段3]) values (‘值1’),(‘值2’),(‘值3’)

注意事项:

1. 字段和字段之间使用英文逗号隔开
2. 字段是可以省略的,但是后面的值必须要一一对应,不能少
3. 可以同时插入多条数据 values 后面的值,用逗号隔开即可

3.4修改 update

-- 不指定条件会修改所有数据
update `student` set `sname` = '大啵啵' where sid = 1; 
-- 修改多个
update `student` set sname = '大啵啵',`ssex`='男' where sid = 1; 

--语法
update 表名 set column_name1 = value1 , column_name2 = value2  where [条件]

条件 where子句 运算符 id = ? , id>? , 在某个区间

where后面是条件判断,返回值是个Boolean值

操作符	含义	范围	结果
=	等于	5=6	false
<>或者!=	不等于	<>2	除了2 ,都会变
>
<
or	我或者你	5>1 and 1>2	true
>=
<=
between … and …	在某个区间	between 2 and 5	区间在2到5包含2和5
and	我和你	5>1 and 1>2	false

-- 通过多个条件定位数据
update `student` set sname = '小啵啵', ssex = '男' where sid<2 and ssex = '女';
update `student` set sbirthday =   CURRENT_TIMESTAMP, ssex = '男' where sid<2 ;

注意:

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

3.5删除 delete

delete命令

-- 删除数据(避免这样写,这样子会全部删除)
delete from `student`
delete from `student` where id = 1; 

truncate命令

-- 作用: 完全情况一个数据库表
-- 标的结构和索引约束不会变
truncate `student`

• 相同点: 都能删除数据,不会删除表结构
• 不同点:
  • truncate 重新设置自增,自增会归零
  • truncate 不会影响事务
  • delete 不会影响自助,上次到5.后面再加还是从6开始

DELETE删除的问题,重启数据库,现象:

这里是数据库版本问题,新版已经修复这个问题了

• INNODB 自增列会从1开始.断点及失
• MYISAM 继续从上一个自增量(存在文件中的,不会丢失)

4DQL查询数据(最重要)

4.1DQL

(Data Query Language: 数据查询语言)

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

操作要用到的表

学生表

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;

年级表

-- 创建年级表
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 auto_increment = 6 default charset = utf8;

科目表

-- 创建科目表
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 auto_increment = 19 default charset = utf8;

select 完整语法

sql关键字的优先级

from > where > group by > having > order by

select [ALL|DISTINCT]
{table.foeld[as a alias1]}
from table_name [as table_alias]
	[left | right | inner join table_name2] -- 联合查询
	[where ...] -- 指定结果需要满足的条件
    [group by ...] -- 制定结果按照哪几个字段来分组
    [having] -- 过滤分组后必须满足的次要条件
    [order by ...] -- 指定查询记录按照一个或者几个条件排序
    [limit {[offset,]row_count| row_countOFFSET offset}]; -- 指定查询的记录从哪一条开始

成绩表

-- 创建成绩表
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;

4.2 指定查询字段

-- 查询全部学生  select 字段 from 表
select * from `student`;

-- 查询指定字段
select studentno,studentname from `student`;

-- 别名,给结果起个名字 AS  可以给字段起别名也可以给表起别名
select `studentno` as 学号,`studentname` as 学生姓名 from `student` as s ;

-- 函数 Concat (a,b)
SELECT CONCAT('姓名: ',studentname) as 新名字 from `student`;

语法: select 字段,...,字段 from 表

有的时候列名不是那么的见名知意,那我们就可以起别名

字段名 as 别名 表名 as 别名

去重 distinct

作用: 去除select 语句查询出来的结果中重复的数据,只显示一条

-- 查询有哪些同学参加了考试,哪些同学有成绩
select * from `result`; -- 查询全部的成绩
select `studentno` from `result`;-- 发现重复数据,需要去重
select distinct `studentno` from `result`; -- 最后只出现了一个学号

数据库的列 表达式

select version(); -- 查询mysql的系统版本
select 3*7-2 as 计算结果 -- 用来计算
select @@AUTO_INCREMENT_INCREMENT;-- 查询自增的步长 (变量)

-- 学员考试成绩＋1分查看
select `studentno`,`studentresult`as '提升分数之前' from `result`;
select `studentno`,`studentresult`+1 as '提升分数之后' from `result`;

数据库中的表达式: 文本值,列,Null,函数表达式,系统变量

select 表达式 from 表

4.3where条件子句

作用: 检索数据中**符合条件**的值

搜索的条件一般是一个或者多个表达式构成

逻辑运算符

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

最好使用英文符号

where 使用

-- 查询考试成绩大于90分的成绩
select `studentno`,`studentresult` from `result` where studentresult BETWEEN 80 and 100 ;
-- 并且学生的id不等于1000
select `studentno`,`studentresult` from `result` where studentresult BETWEEN 80 and 100 and studentno != 1000;

模糊查询: 本质也是比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null,则结果为真
is not null	a is not null	如果操作符不为null,则结果为真
between	a between b and c	如果a在b和c之间,结果为真
like	a like b	SQL匹配,如果a匹配b,则结果为真
in	a in (a1 , a2 …)	假设a 在 (a1 , a2 …)其中某个,结果为真

-- ====模糊查询===
-- 查询姓刘的同学   
-- like 结合 % 代表0-任意一个字符    _ 代表一个字符
select * from student where studentname like '张%';
select * from student where studentname like '_强';
-- 查询名字当中有 伟 字的同学
select * from student where studentname like '%伟%';

-- ====in==== (具体的一个或者多个值)
-- 查询1001, 1002 , 1003号学员
-- 查询不在1001, 1002 , 1003号学员
select * from student where studentno in (1001,1002,1003);
select * from student where studentno not in (1001,1002,1003);
-- 查询地址在北京的学生
select * from student where address in ('北京朝阳');

-- ====null  not null====
select * from student where address is not null;
select * from student where address is null;

4.4联表查询

-- ====连表查询====
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
select * from student ;
select * from result ;
/*
1.分析需求,分析查询的字段来自哪些表
2.确定使用哪种连接查询   7种
3.确定一个交叉点,两个表中哪个数据是相同的
4.判断条件  学生表中的studentno = 成绩表的 studentno
*/
-- join on 连接查询
-- where 等值查询

-- inner join
select s.studentno,studentname,subjectno,studentresult 
from student as s
inner join result as r 
where s.studentno = r.studentno;

-- right join
select s.studentno,studentname,subjectno,studentresult 
from student as s
right join result as r
on s.studentno = r.studentno;
 
-- left join
select s.studentno,studentname,subjectno,studentresult 
from student as s
left join result as r
on s.studentno = r.studentno;

-- 查询缺考的同学
select s.studentno,studentname from student as s
left join 
result as r
on s.studentno = r.studentno 
where studentresult is null;

操作	描述
Inner join	如果表中至少有一个匹配,就返回行
left join	即使右表中没有匹配,也会从左表中返回所有的值 注意上面的left join的执行结果
right join	即使左表中没有匹配,也会从右表中返回所有的值 注意上面的right join的执行结

-- 查询参加考试的同学信息: 学号,学生姓名,科目名称,分数
select s.studentno,studentname,subjectname,studentresult 
from student as s
right join result as r 
on s.studentno = r.studentno
left join  subject as t
on r.subjectno = t.subjectno;

-- 查询学院所属的年级(学号,姓名,年级名称)
select studentno,studentname,gradename 
from student as stu
inner join grade as gra
where stu.gradeid = gra.gradeid;

-- 查询科目所属的年级(科目名称,年级名称)
select sub.subjectname,gra.gradename
from `subject` as sub
inner join grade as gra
where sub.gradeid = gra.gradeid;

-- 查询参加了高等数学1的考试同学信息: 学号,姓名,科目名,分数
select stu.studentno,stu.studentname,sub.subjectname,res.studentresult
from student as stu
inner join result as res , `subject` as sub
where stu.studentno = res.studentno and sub.subjectno = res.subjectno
and subjectname = '高等数学-1';
/*
关键点:
我要查询哪些数据select ...
从哪几个表中查FROM表  XXX Join 连接的表  on 交叉查询
假设查询多张表,慢慢来,先查询两张表,然后再慢慢增加
*/

-- From a left join b  以左边的表为基准
-- From a right join b  以右边的表为基准

自连接(了解)

自己的表和自己的表连接, 核心:一张表拆分为两张一样的表

父类:

categoryid	categoryname
2	信息技术
3	软件开发
5	美术设计

子类:

pid	categoryid	categoryname
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

操作:查询父类对应的子类id

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息
-- 把一张表看成两张一模一样的表
select a.categoryname as '爹',b.categoryname as '儿'
from category as a, category as b
where a.categoryid = b.pid;

4.5分页和排序

排序:

语法 order by 排序字段 [排序规则 asc or desc] 默认升序asc

-- 成绩降序排序
select stu.studentno,stu.studentname,sub.subjectname,res.studentresult
from student as stu
inner join result as res , `subject` as sub
where stu.studentno = res.studentno and sub.subjectno = res.subjectno
and subjectname = '高等数学-1' ORDER BY studentresult DESC;

-- 查询高等数学-3  课程成绩排名前十的学生,并且分数大于80的学生信息(学号,姓名,课程名称,课程成绩)
select stu.studentno,stu.studentname,sub.subjectname,res.studentresult
from student as stu 
inner join `subject` as sub , `result` as res 
where stu.studentno = res.studentno and sub.subjectno = res.subjectno and subjectname = '高等数学-3' and studentresult>80 ORDER BY studentresult DESC limit 10;

分页:

为什么要分页:
- 降低数据库压力
- 提升用户体验

瀑布流: 不断加载, 不断加载(一般图片这样加载)

-- 分页
-- limit 从第几条数据开始,显示几条  0,1   从第1个显示1条数据   0是第一条数据
select stu.studentno,stu.studentname,sub.subjectname,res.studentresult
from student as stu
inner join result as res , `subject` as sub
where stu.studentno = res.studentno and sub.subjectno = res.subjectno
and subjectname = '高等数学-1' ORDER BY studentresult DESC limit 0,1;

4.6子查询

之前的判断条件都在 where里面 , 并且值是确定的. 假设要让值变为计算出来的

本质: 在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 asc

-- 方式二: 使用子查询()
select studentno,r.subjectno,r.studentresult
from `result` r
where subjectno = (
select subjectno from `subject` where subjectname = '高等数学-1'
) 
order by r.studentresult;

-- 查询分数不小于68分的 高等数学-2 学号 姓名
-- 方式一 : 连接查询
select stu.studentno,studentname,sub.subjectname,r.studentresult
from student stu
inner join result r
on stu.studentno = r.studentno
inner JOIN `subject` sub 
on sub.subjectno = r.subjectno
where subjectname = '高等数学-2' and studentresult>=68 order by studentresult desc ;

-- 方式二 : 子查询
select stu.studentno,studentname,r.studentresult
from student stu
inner join result r
on stu.studentno = r.studentno
where studentresult>=68 and subjectno = (select subjectno from `subject` where subjectname = '高等数学-2') 

-- 查询 高等数学-1 成绩高的同学的学生信息. 姓名 学号 课程名称 课程分数
SELECT s.studentno ,s.studentname, subjectname ,studentresult
from student as s
inner join result r
on s.studentno=r.studentno
inner join `subject` sub
on r.subjectno = sub.subjectno
where subjectname = '高等数学-1' ORDER BY studentresult DESC limit 1;

5MySQL函数

官方文档:https://dev.mysql.com/doc/refman/5.7/en/

5.1 一般函数

-- 一般函数
-- 数学运算
select abs(-8);  -- 绝对值
select ceiling(9.4); -- 向上取整
select floor(9.4); -- 向下取证
select rand(); -- 返回一个0-1的随机数    最最常用
select sign(-2); -- 判断一个数的符号   返回-1 0 1

-- 字符串函数
select CHARACTER_LENGTH("啵啵啵啵啵"); -- 字符串长度
select concat('我','爱','你们'); -- 拼接字符串
select insert("我爱你们,啵啵啵",1,2,'滴滴滴'); -- 插入,替换  从第一个位置替换2个长度为滴滴滴
select upper('sosososoAAAA');-- 转为大写
select lower('sosososoAAAA');-- 转为小写
select instr('bobo','b'); -- 后面再前面的位置
select replace('坚持就能成功','坚持','努力'); -- 指定位置更换
select substr('坚持就能成功',2,5); -- 第二个开始截取5个
select REVERSE('ABCD'); -- 反转

-- 时间和日期函数(记住)
select CURRENT_DATE;   -- 2021-08-12
select CURRENT_TIME;   -- 14:49:23
select NOW(); -- 2021-08-12 14:49:54
select LOCALTIME; -- 2021-08-12 14:50:18
select LOCALTIMESTAMP; -- 2021-08-12 14:50:18
select SYSDATE(); -- 2021-08-12 14:50:47
select YEAR(now());-- 2021
select MONTH(now());-- 8
select day(now());-- 12
select HOUR(now());-- 14
select MINUTE(now());-- 53
select SECOND(now());-- 31

-- 系统
select USER();-- root@localhost
select CURRENT_USER;   -- root@localhost
select VERSION(); -- 5.7.25

5.2聚合函数(常用)

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

-- 查询不同课程的平均分,最高分,最低分,平均分大于80分的
-- 核心: 分组 GROUP BY r.subjectno  通过什么来分组
-- having :过滤分组后需要满足的次要条件
select s.subjectno,subjectname, avg(studentresult) as '平均分',max(studentresult) as '最高分',min(studentresult) as '最低分' 
from result r
inner join `subject` s 
on r.subjectno = s.subjectno
GROUP BY r.subjectno
having avg(studentresult)>80;

where 和 having的区别

 where:
    - where是一个约束声明,使用where来约束来自数据库的数据;
    - where是在结果返回之前起作用的;
    - where中不能使用聚合函数。

 having:
    - having是一个过滤声明;
    - 在查询返回结果集以后，对查询结果进行的过滤操作;
    - 在having中可以使用聚合函数。 

5.3 数据库级别的MD5加密(扩展)

什么是MD5?

• MD5不可逆,具体的值的MD5是一样的

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

• MD5破解网站的原理,背后有一个庞大的数据字典做匹配

-- ====测试MD5加密====
create table if not exists `testmd5`(
	`id` int(4) not null,
	`name` VARCHAR(20) not null,
	`pwd` varchar(50) not null,
	PRIMARY key (id)
)ENGINE=INNODB CHARSET=utf8

-- 明文密码
INSERT into testmd5 values(1,'zhangsan','123456'),
(2,'lisi','123456'),
(3,'dd','123456'),
(4,'ww','123456'),
(5,'33','123456'),
(6,'ff','123456'),
(7,'hh','123456');

-- 加密密码
update testmd5 set pwd=MD5(pwd) where id = 1;
update testmd5 set pwd=MD5(pwd);-- 加密全部的密码
INSERT into testmd5 values(8,'名明',MD5('123456')); -- 插入的时候加密
-- 如何校验: 把用户传递进来的密码进行md5加密,然后对比加密后的值

6事务(Transation)

6.1什么是事务

要么都成功,要么都失败

---

1. SQL执行 A给B转账
2. SQL执行 B收到A的转账

---

把一组SQL放到一个批次中去执行~

事务原则: ACID 原子性,一致性,隔离性,持久性 (脏读,幻读,不可重复读)

• 原子性 — Atomicity
  • 针对同一个事务,要么都完成,要么都不完成
• 一致性 — Consistency
  • 最终一致性: 转账的结果,总价值一定和开始是不变的
• 隔离性 — Isolation
  • A C 同时给 B转100元, 他们之间不会相互影响
  • 一旦出现问题就会出现以下问题
    • 脏读: 一个事务读取到了另一个事务还未提交的数据
    • 不可重复读: 在一个事务内读取某一行的数据,多次读取结果不同,例如生成报表的时候,有人转入300元(已提交)
    • 幻读: 一个事务内读取到了别的事务插入的数据,导致前后读取不一样
• 持久性 — Durablity
  • 事务没有提交,恢复到原状
  • 事务已经提交,持久化到数据库

执行事务

-- mysql是自动提交事务的
set autocommit = 0; -- 关闭
set autocommit = 1; -- 提交(默认开启)

-- 手动处理事务
set autocommit = 0; -- 关闭自动提交
-- 事务开启
 start TRANSACTION -- 标记一个事务的开始,从这之后的SQL语句,都在同一个事务内,要么一起成功,要么一起失败
 insert xxx
 insert xxx
 -- 这个时候上面两个事务但凡有一个失败,都会一起提交不上去
 -- 提交: 持久化(成功!)
 commit
 -- 回滚: 回到原来的样子
 rollback
 
-- 事务结束
set autocommit = 1; -- 提交(默认开启)

-- 了解
savepoint 保存点名  -- 设置一个事务的保存点(当一个事务很长的时候)
rollback to savepoint 保存点名 -- 回滚到保存点
release savepoint 保存点名 -- 删除指定的保存点

模拟场景

-- 转账
create database shop CHARSET utf8 ;
use shop;

-- 建表
create table `account`(
	`id` int(3) not null AUTO_INCREMENT,
	`name` VARCHAR(30) not null,
	`money` DECIMAL(8,2) not null,
	primary key (id)
)ENGINE = INNODB CHARSET=utf8;

insert into account(`name`,`money`)
value ('A',2000.00),('B',11000.00);

-- 模拟转账: 事务
set AUTOCOMMIT = 0; -- 关闭自动提交
start TRANSACTION ; -- 开启一个事务
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 -- 回复默认值

7索引

MySQL官方对索引的定义为: 索引(index)是帮组MySQL高效获取数据的数据结构. 提取句子的主干,就可以获得索引的本质:索引是数据结构

7.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` where MATCH(studentname) against ('张'); -- 全文索引

7.2 测试索引

准备工作

DROP table if EXISTS app_user;
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 DEFAULT '123456',
`age` tinyint(4) DEFAULT 0,
`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;

-- 信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1
SET GLOBAL log_bin_trust_function_creators = 1;

-- 插入100万数据.
drop function if exists mock_data ;
DELIMITER $$
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user` (`name`,`email`,`phone`,`gender`,`age`) 
		VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2),FLOOR(RANd()*100));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;
-- 这种删除只删除数据,不会删除表的结构
delete  FROM `app_user`;
select mock_data();

-- id_表名_字段名
create INDEX id_app_user_name on app_user (`name`);

EXPLAIN select * from `app_user` where name = '用户999999';
EXPLAIN select * from `app_user`;

索引在小数据量的时候用处不大, 大数据的时候区别十分明显

7.3索引原则

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

索引的数据结构

文章地址链接:http://blog.codinglabs.org/articles/theory-of-mysql-index.html

Hash 类型的索引

BTree: INNODB的默认数据结构

8权限管理和备份

8.1用户管理

Navicat可视化管理

SQL命令操作

用户表: mysql.user

本质: 读这张表进行CRUD

-- 创建用户 create user 用户名 identified by '密码';
create user bobo identified by '123456';
select * from mysql.`user`;

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

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

-- 重命名  rename 原名 bobo to 新的名字
rename user bobo to bobo2

-- 用户授权 ALL PRIVILEGES 授予全部权限  库·表  唯一不会授予给别人授权的权限
GRANT ALL PRIVILEGES on *.*  to bobo2 -- 给bobo2 授予所有的权限

-- 查看一个人有什么权限
show grants for bobo2;
show grants for root@localhost; -- 查看系统权限

-- 撤销权限 revoke 哪些权限  在哪个库  给谁撤销
revoke all PRIVILEGES on *.* from bobo2;

-- 删除用户 
drop user bobo2;

8.2 MySQL备份

为什么要备份:

• 保证数据不丢失
• 数据转移

MySQL备份的方式:

• 直接拷贝物理文件 data

• 使用Navicat 可视化工具手动导出

• 使用命令行导出mysqldump

  # mysqldomp  -h主机 -u用户名 -p密码 数据库名 表名   导出单表
  C:\Users\ECCOM>mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
  mysqldump: [Warning] Using a password on the command line interface can be insecure.
  
  #导出多表
  C:\Users\ECCOM>mysqldump -hlocalhost -uroot -p123456 school student result >D:/b.sql
  mysqldump: [Warning] Using a password on the command line interface can be insecure.
  
  #导入sql文件
  #1. 命令行登录数据库
  C:\Users\ECCOM>mysql -uroot -p
  #2.选择数据库  
  use school
  #3.选择执行SQL文件
  mysql> source d:/a.sql
  

  备份数据库,防止数据丢失

9规范数据库设计

9.1为什么需要设计

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

糟糕的数据库设计:

• 数据冗余,浪费空间
• 数据插入和删除都会麻烦,可能会产生异常[屏蔽使用物理外键]
• 程序的性能差

良好的数据库设计:

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

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

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

设计数据库的步骤:(个人博客)

• 收集信息,分析需求
  • 用户表(用户登录注销, 用户个人信息, 写博客, 创建分类)
  • 分类表(文章分类, 谁创建的)
  • 文章表(文章的信息)
  • 友情链接表
  • 自定义表(系统信息, 某个关键的字,或者一些关键的字段 两列 key : value)
• 标识实体类(把需求落地到每个字段)
• 标识实体之间的关系(表与表之间的关系)

9.2三大范式

为什么要数据规范化?

• 信息重复
• 更新异常
• 插入异常
  • 无法正常显示信息
• 删除异常
  • 信息删除不干净

三大范式

• 第一范式(1NF):
  • 原子性: 保证每一列不可再分
• 第二范式(2NF):
  • 前提,满足1NF
  • 每张表只描述一件事情
• 第三范式(3NF):
  • 前提,满足1NF,2NF
  • 表中,每列数据都和主键之间相关,而不间接相关

(作用: 规范数据库的设计)

规范性和性能的问题

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

• 考虑商业化的需求和目标(成本,用户体验(响应速度))
• 在考虑性能问题的时候,适当考虑规范性!
• 故意给一些表增加冗余字段(多表联查变为单表查询)
• 复议增加一些计算列(从大数据量降低为小数据量 : 增加索引)

10JDBC

10.1数据库驱动

我们的程序会通过数据库驱动,才能和数据库打交道!

10.2JDBC

SUN公司为简化开发,对数据库操作提供了一个统一规范 Java database connect

这些规范由具体的厂商去做~

对于开发人员来说,我们只需要学习JDBC接口操作即可!

10.3第一个JDBC程序

CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `jdbcStudy`;

drop table if EXISTS `users`;
CREATE TABLE `users`(
 `id` INT PRIMARY KEY,
 `NAME` VARCHAR(40),
 `PASSWORD` VARCHAR(40),
 `email` VARCHAR(60)
);

INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`)
VALUES(1,'zhangsan','123456','zs@sina.com'),
(2,'lisi','123456','lisi@sina.com'),
(3,'wangwu','123456','wangwu@sina.com');

/**
 * 我的第一个JDBC程序
 */
public class JdbcDemo1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8";
        String username = "root";
        String password = "123456";
        //3.连接成功,数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);

        //4.执行SQL的对象
        Statement statement = connection.createStatement();
        //5.执行SQL的对象去执行SQL,可能存在结果,查看返回结果
        String sql = "select * from users";
        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next()){
            System.out.print("id=" + resultSet.getObject("id")+"   ");
            System.out.print("name=" + resultSet.getObject("name")+"   ");
            System.out.print("pwd=" + resultSet.getObject("password")+"   ");
            System.out.println("email=" + resultSet.getObject("email")+"   ");
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

依赖:

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

步骤总结

• 加载驱动
• 连接数据库DriverManager
• 获得执行SQL的对象statement
• 获得返回的结果集
• 释放连接

DriverManager

//固定写法加载驱动
Class.forName("com.mysql.jdbc.Driver");
//数据库对象
Connection connection = DriverManager.getConnection(url, username, password   );
//数据库设置自动提交
//事务提交
//事务滚回
connection.rollback();
connection.commit();
connection.setAutoCommit(true);                                       

URL

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8";
String username = "root";
String password = "123456";
//mysql  3306   jdbc:mysql:
//oracle 1521 	jdbc:oracle:thin:@localhost:1521:sid

Statement,PreparedStatement对象,执行SQL的类

Statement statement = connection.createStatement();
statement.executeQuery(); //查询操作,返回一个ResultSet
statement.execute(); //执行任何sql
statement.executeUpdate(); //更新,插入,删除都用它,返回受影响的行数

Resultset结果集,封装了所有的结果集

resultSet.getObject() //在不知道类型的情况下使用, 如果知道类型就是用指定的类型

resultSet.next(); //移动到下一个数据
resultSet.previous(); //移动到前一个数据
resultSet.absolute(row); //移动到指定行

释放资源

resultSet.close();
statement.close();
connection.close();

10.4statement对象

配置工具类JdbcUtils.java

public class JdbcUtils {

    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    static {
        try {
            InputStream stream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(stream);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            //1.驱动只用加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //2.获取连接
    public static Connection getConnector() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    //3.释放链接资源
    public static void release(Connection con, Statement sta, ResultSet set){
        if (set!=null){
            try {
                set.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (con!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(sta!=null){
            try {
                sta.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

编写配置文件db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?serUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

编写测试类test.java

public class Test {
    public static void main(String[] args) throws SQLException {
        String s = "select * from users";
        Connection connector = JdbcUtils.getConnector();
        Statement statement = connector.prepareStatement(s);
        ResultSet resultSet = statement.executeQuery(s);
        while (resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
        }
    }
}

SQL注入问题

SQL存在漏洞, 会被攻击,导致用户数据泄露 SQL会被拼接 or 1=1”

10.5PreparedStatement对象

PreparedStatement 可以防止SQL注入!并且效率更高!

String s = "select * from users";
Statement statement = connector.prepareStatement(s);
//本质,把传递进来的参数当做字符

需要先写SQL但是不执行,存在一个预编译的过程

10.6JDBC操作事务

事务:要么都成功要么都失败

ACID:原子性,一致性,隔离性,持久性

10.7数据库连接池

数据库连接- - - 执行完毕- - - 释放

连接- - -释放 这个过程很浪费资源的

池化技术: 准备一些预先的资源,过来就连接预先准备好的

• 最小连接数:100
• 常用连接数:10
• 最大连接数:100 业务最高承载上线,超过100的连接, 就等待
• 存在一个等待超时: 1000ms

开源数据源实现

DBCP

C3P0

Druid: 阿里巴巴

使用了这些数据库连接池之后,我们再项目开发中,就不需要编写连接数据库的代码了

DBCP(拿来即用)

引入依赖:

<dependency>
            <groupId>commons-pool</groupId>
            <artifactId>commons-pool</artifactId>
            <version>1.6</version>
        </dependency>
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>

编写工具类:

DbcpUtils.java

public class DbcpUtils {
    private static DataSource dataSource = null;


    static {
        try {
            InputStream stream = com.bo.lession02.utils.JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpConfig.properties");
            Properties properties = new Properties();
            properties.load(stream);

            //创建数据源  工厂模式 --- >创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //2.获取连接
    public static Connection getConnector() throws SQLException {
        return dataSource.getConnection(); //从数据源中获取连接
    }

    //3.释放链接资源
    public static void release(Connection con, Statement sta, ResultSet set) {
        if (set != null) {
            try {
                set.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (sta != null) {
            try {
                sta.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

编写测试类:

test.java

public class Test {
    public static void main(String[] args) throws SQLException {
        String s = "select * from users";
        Connection connector = DbcpUtils.getConnector();
        Statement statement = connector.prepareStatement(s);
        ResultSet resultSet = statement.executeQuery(s);
        while (resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
        }
        DbcpUtils.release(connector,statement,resultSet);
    }
}

C3P0(拿来即用)

导入依赖

<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.5</version>
</dependency>
<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>mchange-commons-java</artifactId>
    <version>0.2.19</version>
</dependency>

编写工具类

C3p0Utils.java

public class Test {
    public static void main(String[] args) throws SQLException {
        String s = "select * from users";
        Connection connector = C3p0Utils.getConnector();
        Statement statement = connector.prepareStatement(s);
        ResultSet resultSet = statement.executeQuery(s);
        while (resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
        }
        C3p0Utils.release(connector,statement,resultSet);
    }
}

编写配置文件

c3po-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
    c3p0的缺省（默认）配置
    如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省（默认）&#45;&#45;-->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&amp;characterEncoding=utf8&amp;uesSSL=false&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>
    <!--
    C3P0的命名配置,
    如果代码中的ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");这样写就代表使用的是name= mysql的
    -->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&amp;characterEncoding=utf8&amp;uesSSL=true&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
</c3p0-config>

结论

无论使用什么数据源, 本质还是一样的,DataSource接口是不会变的,方法就不会变