MySQL学习笔记

最新推荐文章于 2022-08-06 20:48:08 发布
原创 最新推荐文章于 2022-08-06 20:48:08 发布 · 1.2k 阅读 · 3 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #java

本文详细介绍了MySQL数据库的基础知识，包括数据库的分类、MySQL的特点与安装，以及数据库的操作如创建、删除和使用。重点讲解了数据类型、字段属性、表的创建与修改，还探讨了DML语言、DQL查询数据、事务处理和索引。此外，文章还深入讨论了JDBC的使用，包括数据库驱动、连接管理、Statement和PreparedStatement对象，以及JDBC操作事务和数据库连接池的概念。

1、初识MySQL

java EE：企业级java开发 web

前端（页面：展示，数据！）

后台（连接点：连接数据库JDBC，连接前端（控制，控制视图跳转，和给前端传递数据））

数据库（存数据，Txt，Excel，word）

只会写代码，学好数据库，基本混饭吃

操作系统，数据结构与算法！当一个不错的程序员！

离散数学，数字电路，体系结构，编译原理。+实战经验，高就程序员优秀的程序员

1.1、为什么学习数据库

1、岗位需求

2、现在的世界，大数据时代~，得数据库者得天下。

3、被迫需求：存数据

4、数据库是所有软件体系最核心的存在 DBA

1.2、什么是数据库

数据库（DB，DataBase）

概念：数据仓库，软件，安装在操作系统（windows、Mac、linux）之上！SQL，可以存储大量的数据。500万

作用：存储数据，管理数据

1.3、数据库分类

关系型数据库：
  • MySQL，Oracle，Sql Server，DB2，SQLlist
  • 通过表和表之间，行和行之间的关系进行数据的存储， 学员信息表，考勤表…
非关系型数据库：
  • Redis，MongDB
  • 非关系型数据库，对象存储，通过对象的自身的属性来决定。

DBMS（数据库管理系统）

  • 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据；
  • MySQL，数据库管理系统！

1.4、MySQL

MySQL是一个关系型数据库管理系统

前世：瑞典的MySQL AB公司

今生：属于Oracle旗下产品

MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

体积小、速度快、总体拥有成本低，招人成本低，所有人必须会~

中小型网站、或者大型网站，集群！

官网：https://www.mysql.com/

安装建议：

1、尽量不要使用exe，

2、尽可能使用压缩包安装~

1.5 安装MySQL

教程：https://www.cnblogs.com/hellokuangshen/p/10242958.html

2、操作数据库

2.1、操作数据库（了解）

1、创建数据库
create database [if not exists] ...
2、删除数据库
drop database [if exist] ...
3、使用数据库
--tab键的上面，如果你的表名或者字段名是一个特殊字符，就需要带``
use `...`
4、查看数据库
show database -- 查看所有的数据库

2.2数据库的数据类型

数值
  • int 标准的证书 4个字节
  • bigint 较大的数据 8个字节
  • float 浮点数 4个字节
  • double 浮点数 8个字节（精度问题！）
  • decimal 字符串形式的浮点数 金融计算时，一般是使用decimal
字符串
  • char 字符串固定大小的 0~255
  • varchar 可变字符串 0~65535 常用的变量 String
  • tinytext 微型文本 2^8-1
  • text 文本串 2^16-1 保存大文本
时间日期

java.util.Date

  • date 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：
  • 无符号的整数
  • 声明了该列不能声明为负数
zerofill：
  • 0填充的
  • 不足的位数，使用0来填充， int（3）， 5 — 005
自增：
  • 通常理解为自增，自动在上一条记录的基础上+1（默认）
  • 通常用来设计唯一的主键~ index，必须是整数类型
  • 可以自定义设计主键资政的起始值和步长
非空 NULL not null：
  • 假设设置为not null，如果不给它赋值，就会报错！
  • MULL，如果不填写值，默认就是null！
默认：
  • 设置默认的值！
  • sex，默认值为男，如果不指定该列的值，就会有默认的值！

2.4、创建数据库表（重点）

create table if not exist `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` datatme defalut null comment '出生日期',
    `address` varchar(100) defalut null comment '家庭住址',
    `email` varchar(50) defalut null comment '邮箱',
    primary key(`id`)
)ending=innoDB default charset=utf8

格式

create table [if not exist] `表名`(
    `字段名` 列类型 [属性] [索引] [注释],
    ......
    `字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符串设置][注释]

常用命令

show create database school --查看创建数据库的语句
show create table student --查看student数据库表的定义语句
desc student --显示表的结构

2.5、数据表的类型

--关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用
*/
MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键不支持支持
全文索引支持不支持
表空间大小较小较大，约为2倍

常规使用操作

  • MYISAM 节约空间，速度较快
  • INNODB 安全性高，事务的处理，多表多用户操作
在物理空间存在的位置下

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库

本质还是文件的存储！

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

  • InnoDb在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
  • MyIASM对应的文件：
    • *.frm 表结构的定义文件
    • *.MYD 数据文件（data）
    • *.MYI 索引文件（index）
设置数据库表的字符集编码
charset=utf8

不设置的话，会是mysql默认的字符集编码~（不支持中文！）

MySQL的默认编码是Latin1，不支持中文

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

character-set-server=utf8

2.6、修改删除表

修改
--修改表名  alter table 旧表名 rename as 新表名
alter table teacher rename as teacher1
--增加表的字段 alter table 表名 add 字段名 列表名
alter table teacher1 add int(11)

--修改表的字段（重命名，修改约束！）
--alter table 表名 modify 字段名 列属性[]
alter table teacher1 modify age varchar(11) --修改约束
--alter table 表名 change 旧名字 新名字 列属性[]
alter table teacher1 change age age1 int(1) --字段重命名

--删除表的字段：alter table 表名 drop 字段名
alter table teacher1 drop age1
删除
--删除表（如果表存在就删除）
drop table if exist teacher1

所有的修改和删除操作尽量加上判断，以免报错~

3、MySQL的数据管理

3.1、外键（了解即可）

方式一、在创建表的时候，增加约束（麻烦，比较复杂）

create table `grade`(
    `gradeid` int(10) not null auto_increment comment '年级id',
    `gradename` varchar not null comment '年级名称',
    primary key(`gradeid`)
)edning=innodb default charser=utf8


--学生表的gradeid字段 要去引用年级表的额gradeid
--定义外键key
--给这个外键添加约束（执行引用） references引用

create table if not exist `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` datatme defalut null comment '出生日期',
    `gradeid` int(10) not null auto_increment comment '学生的年级',
    `address` varchar(100) defalut null comment '家庭住址',
    `email` varchar(50) defalut null comment '邮箱',
    primary key(`id`),
    key `FK_gradeif`(`gradeid`),
    constraint `FK_gradeid` foreing key(`gradeid`) referents `grade`(`gradeid`)
)ending=innoDB default charset=utf8

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

方式二、创建表成功后，添加外键约束

create table `grade`(
    `gradeid` int(10) not null auto_increment comment '年级id',
    `gradename` varchar not null comment '年级名称',
    primary key(`gradeid`)
)edning=innodb default charser=utf8

create table if not exist `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` datatme defalut null comment '出生日期',
    `gradeid` int(10) not null auto_increment comment '学生的年级',
    `address` varchar(100) defalut null comment '家庭住址',
    `email` varchar(50) defalut null comment '邮箱',
    primary key(`id`)
)ending=innoDB default charset=utf8

--创建表的时候没有外键关系
alter table `student`
add constraint `FK_gradeid` foreing key(`gradeid`) references `grade`(`gradeid`);

--alter table 表 add constraint 约束名 foreing key(作为外键的列) references 那个表(哪个字段)

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

最佳实现

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

3.2、DML语言（全部记住）

**数据库的意义：**数据存储，数据管理

DML语言：数据操作语言

  • insert
  • update
  • dalete

3.3、添加

--插入语句（添加）
--insert into 表名([字段名1，字段名2，字段名3])values('值1'),('值2'),('值3',....)
insert into `grade`(`gradename`) values('大四')

--由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配）
insert into `grade` values('大三')

--一般写插入语句，我们一定要数据和字段一一对应！
insert into `grade`(`name`,`sex`,`pwd`) values('张三','男','123456')，('李四','女','987654')
--插入多个字段
insert into `grade`(`gradename`) values('大二'),('大一')

注意事项：

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

3.4、修改

update 修改谁 (条件) set原来的值=新值
--修改学元员名字，带了简介
update `student` set `name`='zhanghui' where id=1

--不指定条件的情况下，会改动所有表
update `student` set `name`='zhangsan'

--修改多个属性，逗号隔开
update `student` set `name`='zhanghui',`email`='zh980412@163.com' where id=1

--语法：
--update 表名 set colnum_name=value，[colnum_name=value,....] where [条件]

条件：where字句运算符

操作符含义范围结果
=等于5=6false
<>或！=不等于5<>6true
>
<
>=
<=
between…and…在某个范围内[2,5]
and我和你&&5>1 and 1>2false
or我或你||5>1 or 1>2true
--通过多个条件定位数据
update `studet` set `name`='张三' where `name`='李四' and sex='女'

注意：

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

3.5、删除

delete

语法：delete from 表名 [where 条件]

-- 删除数据（避免这样写，会全部删除）
delete from student

--删除指定的数据
delete from `student` where id=1
truncate命令

作用：完全清空一个数据库表，表的结构和索引约束不会变

--清空student表
truncate `student`
delete 和 truncate 区别
  • 相同点：都能删除数据，都不会删除表结构
  • 不同：
    • truncate 重新设置 自增列 计数器会归零
    • truncate 不会影响事务
--测试 delete 和 truncate 区别
create table `test`(
    `id` int(4) not null auto_increment,
    `coll` varchar(20) not null,
    primary key(`id`)
)ending=innodb default charset=utf8

insert into `test`(`coll`) value('1'),('2'),('3')

delete from `test` --不会影响自增

truncate table `test` --自增会归零

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

  • InnoDB 自增列会从1开始（存在内存当中的，断电即失）
  • MYISAM 继续从上一个自增量开始（存在文件中，不会丢失）

4、DQL查询数据（最重点）

4.1、DQL

（Data Query Language：数据查询语言）

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

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 别名 表名 as 别名
去重 distinct

作用：去除select查询出来的结果中重复的数据，重复数据只显示一条

--查询一下有哪些同学参加了考试，成绩
select * from result --查询全部的考试成绩
select `StudentNo` from result --查询有哪些同学参加了考试
select distnct `StudentNo` from result --发现重复数据，去重
数据库的列(表达式)
select version() --查询系统版本(函数)
select 100*3-1 as 计算结果 --用来计算(表达式)
select @@auto_increment_increment --查询自增的步长(变量)

--学员考试成绩+1分查看
select `StudentNo`,`StudentResult`+1 as '提分后' from result

数据库中的表达式：文本值，列，null，函数，计算表式，系统变量…

select 表达式 from 表

4.3、where条件字句

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

搜索的条件由一个或者多个表达式组成！结果 布尔值

逻辑运算符
运算符语法描述
and &&a and b a&&b逻辑与，两个都为真，结果为真
or ||a or b a||b逻辑或，一个为真，则结果为真
not ！not ！a逻辑非，真为假，假为真

尽量使用英文字母

--===============================  where  ===============================
select studentNo,`StudentResulet` from result

--查询考试成绩在95~100分之间
select studentNo,`StudentResult` from result where StudentResult>=95 and StidentResult<=100

--and   &&
select studentNo,`StudentResult` from result where StudentResult>=95 && StidentResult<=100

--模糊查询(区间)
select studentNo,`StudentResult` from result where StudentResult between 95 and 100

--除了1000号学生之外的学生的成绩
select studentNo,`StudentResult` from result where studentNo!=1000

-- !=   not
select studentNo,`StudentResult` from result where not studentNo=1000
模糊查询：比较运算符
运算符语法描述
is nulla is null如果操作符为null，结果为真
is not nulla is not null如果操作符不为null，结果为真
betweena between b and c若a在b和c之间，则结果为真
likea like bSQL匹配，如果a匹配b，则结果为真
ina in(a1,a2,a3)假设a在a1，或者a2…其中的某一个值中，结果为真
--====================================  模糊查询  ==================================
--查询姓刘的同学
--like结合 %(代表0到任意个字符)     (一个字符)
select `StudentNo`,`StudentName` from `student` where StudentName like '刘%'

--查询姓刘的同学，名字后面只有一个字的
select `StudentNo`,`StudentName` from `student` where StudentName like '刘_'

--查询姓刘的同学，名字后面只有两个字的
select `StudentNo`,`StudentName` from `student` where StudentName like '刘__'

--查询名字中有嘉字的同学 %嘉%
select `StudentNo`,`StudentName` from `student` where StudentName like '%嘉%'

--============ in  =============
--查询1001,1002,1003号学员
select `StudentNo`,`StudentName` from `student` where StudentName in (1001,1002,1003)

--查询在北京的学生
select `StudentNo`,`StudentName` from `student` where `Address` in ('安徽','河南洛阳')

--============null    not null===============
--查询地址为空的学生 null ''
select `StudentNo`,`StudentName` from `student` where address='' or address is null

--查询有出生日期的学生   不为空
select `StudentNo`,`StudentName` from `student` where `BornData` is not null

4.4、联表查询

join

在这里插入图片描述

--=================================  联表查询 join  ======================================
--查询参加了考试的同学(学号，姓名，科目编号，分数)
select * from student
select * from result

/*思路
1.分析需求，分析查询的字段来自哪些表（连接查询）
2.确定使用哪种连接查询？ 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 studentNo = 成绩表 studentNo
*/

-- join （练接的表） on （判断条件） 连接查询
-- where  等值查询
select s.studentNo,studentName,subjectNo,studentResult
from student as s
inner join result as r
where s.studentNo=r.studentNo


select s.studentNo,studentName,subjectNo,studentResult
from student as s
inner join result as r
on 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,subjectNo,studentResult
from student as s
left join result as r
on s.studentNo=r.studentNo
where StudentResult is null


--思考题（查询参加了考试的同学信息：学号，学生姓名，科目名，分数）
/*思路
1.分析需求，分析查询的字段来自哪些表,student,result,subject（连接查询）
2.确定使用哪种连接查询？ 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 studentNo = 成绩表 studentNo
*/

select s.studentNo,studentName,subjectNo,studentResult
from student as s
right join result as r
on r.studentNo=s.studentNo
inner join subject as sub
on r.studentNo=sub.studentNo



--我要查询哪些数据 select...
--从哪几个表中查 from 表 XXX join 连接的表 on 交叉条件
--假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

--from a left Join b
--from a right Join b
自连接(了解)

自己表和自己的表连接，核心：一张表拆为两张一样的表即可

父类

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

子类

pidcategoryidcategoryName
34数据库
28办公信息
36web开发
57美术设计

操作：查询父类对应的子类关系

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

select a.`categoryName` as '父栏目',b.`categoryName` as '子栏目'
from `category` as a,`category` as b
where a.`categoryid`=b.`pid`

--查询学员所属的年级（学号，学生的姓名，年级名称）
select studentNo,studentName,`GradeName`
from student s
inner join `grade` g
on s.`GradeID`=g.`GradeID`


--查询科目所属的年级（科目名称，年级名称）
select `SubjectName`,`GradeName`
from `subject` sub
inner join `grade` g
on sub.`GradeID`=g.`GradeID`


--思考题（查询参加了数据库结构-1考试的同学的信息：学号，学生姓名，科目名，分数）
select s.`StudentNo`,`StudentName`,s.`SubjectName`,`StudentResult`
from student s
inner join `result` r
on s.`StudentNo`=r.`StudentNo`
inner `subject` sub
on r.`SubjectNo`=sub.`SubjectNo`
where subjectName='数据库结构-1'

4.5、分页和排序

排序
-- ================================  分页 limit 和排序 order  ===============================

--排序：升序 asc ，降序desc
--order by 通过哪个字段排序，怎么排
--查询的结果根据 成绩降序 排序
select s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
from student s
inner join `result` r
on s.`StudentNo`=sub.`StudentNo`
where subjectName='数据库结构-1'
order by StudentResult asc
分页
--100万
--为什么要分页
--缓解数据库压力，给人的体验更好，瀑布流

--分页，每页只显示五条数据
--语法：limit 起始值,页面的大小
select s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
from student s
inner join `result` r
on s.`StudentNo`=sub.`StudentNo`
where subjectName='数据库结构-1'
order by StudentResult asc
limit 0,5

--第一页  limit 0,5
--第二页  limit 5,5
--第三页  limit 10,5
--第N页  limit (n-1)*pageSize,pageSize
-- 【pageSize：页面大小】
--【(n-1)*pageSize：起始值】
--【n:当前页】
--【数据总数/页面大小=总页数】

语法：limit(查询起始下标,pageSize)

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

4.6、子查询

where（这个值是计算出来的）

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

--============================  where  ===========================

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

--方式二：使用子查询(由里及外)
select `StudentNO`,r.`SubjectNo`,`SubjectResult`
from `result`
where SubjectNo=(
    select SubjectNo from `subject`
    where SubjectName='数据库结构-1'
)
order by StudentResult desc


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

--查询课程为高等数学-2 且分数不小于80分的同学的学号和姓名
select distinct s.`StudentNo`,`StudentName`
from student s
inner join `result` r
on s.`StudentNo`=r.`StudentNo`
inner join 'subject' sub
on r.`SubjectNo`=sub.`SubjectNo`
where `SubjectName`='高等数学-2' and StudentResult>=80

--在这个基础上增加一个科目， 高等数学-2
--查询 高等数学-2 的编号
select distinct s.`StudentNo`,`StudentName`
from student s
inner join `result` r
on s.`StudentNo`=r.`StudentNo`
where `SubjectResult`>=80 and `SubjectNo`=(
    select `SubjectNo` from `subject`
    where `SubjectName`='高等数学-2'
)

--再改造(由里及外)
select `StudentNo`，`StudentName` from `student` where `StudentNo` in(
    select `StudentNo` from `result` where `StudentResult`>=80 and `SubjectNo`(
        select `SubjectNo` from `subject` where `SubjectName`='高等数学-2'
    )
)

4.7、分组和过滤

--查询不同课程的平均分，最高分，最低分，平均分大于80
--核心：（根据不同的课程分组）
select SubjectName,avg(StudentResult) as 平均分,max(StudentResult) as 最高分,min(StudentResult) as 最低分
from `result` r
inner join 'subject' sub
on r.`SubjectNo`=sub.`SubjectNo`
group by r.`SubjectNo` --通过什么来分组
having 平均分 > 80

5、MySQL函数

5.1、常用函数

-- ============================ 常用函数  =========================

--数学运算
select abs(-8)  --绝对值
select ceiling(9.4)  --向上取整
select floor(9.4)  --向下取整
select rand()  --返回一个1~0之间的随机数
select sign(10)  --判断一个数的符号  0-0   负数返回-1，正数返回 1

--字符串函数
select char_length()  --字符串长度
select concat() --拼接字符创
select insert()  --查询，替换（从某个位置开始替换某个长度）
select lower()  --小写字母
select upper()  --大写字母
select instr()  --返回第一次出现的子串的索引
select replace()  --替换出现的指定字符串
select substr()  --返回指定的字符串（源字符串，截取的位置，截取的长度）
select reverse()  --反正字符串

--查询姓 周的同学，名字  纣
select replace(`StudentName`,'周','纣') from `student`
where `StudentName` like '周%'

--时间和日期函数（记住）
select current_date()  --获取当前时期
select curdate()  --获取当前日期
select now()  --获取当前时间
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 user()
select version()

5.2、聚合函数（常用）

函数名称描述
count()计数
sum()求和
avg()平均值
max()最大值
min()最小值
……
--  ============================  聚合函数  =========================
-- 都能够统计 表中的数据（想查询一个表中有多少个记录，就使用这个count()）
select count(StudentName) from student  --count(字段)，会忽略所有的null值
select count(*) from student  --count(*)，不会忽略null值  本质  计算行数
select count(1) from result  --count(1)，不会忽略null值  本质  计算行数

select sum(`StudentResult`) as 总和 result
select avg(`StudentResult`) as 平均分 result
select max(`StudentResult`) as 最高分 result
select min(`StudentResult`) as 最低分 result

5.3、数据库级别的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')
)ending=innodb default charset=utf8

--明文密码
insert into testmd5 value(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
--加密
update testmd5 set pwd=MD5(pwd) where id=1
update testmd5 set pwd=MD5(pwd) --加密全部的密码

--插入的时候加密
insert into testmd5 value(4,'xiaoming',MD5('123456'))

--如何校验：将用户传递进来的密码，进行md5加密，然后对比加密后的值
select * from testmd5 where `name`='xiaoming' and pwd=MD5('123456')

6、事务

6.1、什么是事务

要吗成功，要么失败

——————

1、SQL执行 A 给 B 转账 A 1000 —>200 B 200

2、SQL执行 B收到A的钱 A 800 —> B 400

——————

将一组SQL放在一个批次中执行

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

参考博客链接：https://blog.youkuaiyun.com/dengjili/article/details/82468576

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

事务前后的数据晚自习要保证一致

持久性（Durability）

事务一旦提交则不可逆，被持久化到数据库中！

隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

隔离所导致的一些问题
脏读：

指一个事务读取了另外一个事务未提交的数据。

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

幻读

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一般是行影响，多了一行）

执行事务
--===========================  事务  ===========================

--mysql 是默认开启数据库自动提交的
set autocommit = 0  /* 关闭 */
set autocommit = 1  /* 开启（默认的）*/

--手动处理事务
set autocommit = 0 --关闭自动提交
--事务开启
start transaction --标记一个书屋的开始，从这个之后的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(30) not null,
    `money` decimal(9,2) not null,
    primary key(`id`)
)ending=innodb default charset=utf8

insert into account(`name`,`money`)
value('A',2000.00),('B',10000.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
    • 快速定位数据

基础语法

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

--显示所有的索引信息
show index from student

--增加一个全文索引(索引名)  列名
alter table school.student add fulltext index `studentname`(`studentname`);

--explain 分析sql执行的状况

explain select * from student； --非全文索引

explain select * from student where match(studentname) against('刘');

7.2、测试索引

-- 插入100万数据
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`,`gender`,`password`,``age) value(concat('用户',i),'zh980412@163.com',concat('18',floor(rand()*((999999999-100000000)+1000000000))),floor(rand()*2,uuid(),floor(rand()*100))
         set i=i+1;
   end while;
   return i;
end;

select * from app_user where `name`='用户9999'; --0.993sec
                                                                              
explain select * from app_user where `name`='用户9999';
select * from student

--id_表名_字段名
--create index 索引名 on 表(字段)  
create index id_app_user_name on app_user(`name`); 
select * from app_user where `name`='用户9999'; --0.001sec
explain select * from app_user where `name`='用户9999';

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

7.3、索引原则

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

Hash 类型的索引

Btree：InnoDB的默认数据结构~

阅读：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

8、权限管理

8.1、用户管理

SQL yog 可视化管理
SQL命令操作

用户表：mysql.user

本质：读这张表进行增删改查

--创建用户 create user 用户名 identified by '密码'
create user zhangsan identified by '123456'

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

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

--重命名  rename user 原来名字 to 新的名字
rename user zhangsan to zhangsan1

--用户授权 all privileges 全部的权限  ， 库.表
--all privileges 除了给别人授权，其他的都能干
grant all privileges on *.* to zhangsan1

--查询权限
show grant for zhangsan1  --查看指定用户的权限
show grant for root@localhost 

--root 用户的权限：grant all privileges on *.* to 'root@localhost' with grant option

--撤销权限  revoke 哪些权限 ， 在哪个库撤销 ， 给谁撤销
revoke all privileges on *.* from zhangsan1

--删除用户
drop user zhangsan

8.2、数据库备份

为什么要备份

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

MySQL数据库备份的方式

  • 直接拷贝物理文件

  • 在SQLyog这种可视化工具中手动导出

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

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

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

# mysql -h 主机  -u 用户名  -p 密码  数据库  > 物理磁盘位置/文件名
mysql -hlocalhost -uroot -p123456 school >D:/c.sql

# 导入
# 登录的情况下，切换到指定的数据库
# source 备份文件
source d/:a.sql

mysql -u用户名 -p密码 库名< 备份文件

假设你要备份数据库，防止数据丢失。

把数据库给朋友！sql文件给别人即可！

9、规范数据库设计

9.1、为什么需要设计

但实际比较复杂的时候，我们就需要设计了

糟糕的数据库设计：

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

良好的数据库设计

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

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

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

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

  • 收集信息，分析需求
    • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
    • 分类表（文章分类，谁创建的）
    • 文章表（文章的信息）
    • 友链表（友链信息）
    • 自定义表（系统信息，某个关键的字，或者一些主字段） key：value
    • 说说表（发表心情 … id … content … create_time)
  • 标识实体（把需求落地到每个字段）
  • 标识实体之间的关系
    • 写博客：user–>blog
    • 创建分类：user–>category
    • 关注：user–>user
    • 友链：links
    • 评论：user–>user-blog

9.2、三大范式

为什么需要数据规范化

  • 信息重复
  • 更新异常
  • 插入异常
    • 无法正常显示信息
  • 删除异常
    • 丢失有效信息
三大范式（了解）

第一范式（1NF）

原子性：保证每一列不可再分

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

前提：满足第一范式和第二范式

第三范式需要确保数据库表中的每一列数据都和主键直接相关，而不能间接相关。

（规范数据库的设计）

规范性 和 性能的问题

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

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

10、JDBC（重点）

10.1、数据库驱动

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

我们的程序会通过 数据库 驱动，和数据库打交道！

10.2、JDBC

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

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

对于开发人员来说，我们只需要掌握JDBC接口的操作即可！

java.sql

javax.sql

还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar

10.3、第一个JDBC程序

创建测试数据库
create database jdbcStudy character set utf8 collate utf8_general_ci

use jdbcStudey

create table users(
    id int primary key
    name varchar(40),
    password varchar(40)
    email varchar(60);
    birthday date
);

insert into users(id,name,password,email,birthday)
values(1,'zhangsan','123456','zs@163.com','1998-06-12'),
(2,'lisi','123456','ls@163.com','1998-04-12'),
(3,'wangwu','123456','ww@163.com','1997-06-12'),

1、创建一个普通项目

2、导入数据库驱动

3、编写测试代码

public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.zhang.jdbc.Driver");//固定写法，加载驱动

        //2.用户信息和url
        //useUnicode=true&characterEncoding=utf8&useSSl=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSl=true";
        String username = "root";
        String password = "123456";

        //3.连接成功，数据库对象  connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        //4.执行SQL的对象  statement 执行sql对象
        Statement statement = connection.createStatement();

        //5.执行SQL的对象 去 执行SQL，可能存在结果，查看返回结果
        String sql = "select * from users";

        ResultSet resultSet = statement.executeQuery(sql); //返回结果集，结果集中封装了我们全部的查询出来的结果

        while(resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("NAME"));
            System.out.println("password=" + resultSet.getObject("PASSWORD"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birthday=" + resultSet.getObject("birthday"));
        }

        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();

    }
}

步骤总结：

1、加载驱动

2、连接数据库 DriverManager

3、获得执行sql的对象 Statement

4、获得返回的结果集

5、释放连接

DriverManager
//DriverManager.registerDriver(new com.zhang.jdbc.Driver());
Class.forName("com.zhang.jdbc.Driver");//固定写法，加载驱动
Connection connection = DriverManager.getConnection(url, username, password);

//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚

connection.commit();
connection.rollback();
connection.setAutoCommit();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSl=true";

//mysql -- 3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3

//oracle -- 1521
//jdbc:oracle:thin:@localhost:1521:sid
Statement 执行SQL的对象 PrepareStatement
String sql = "select * from users";//编写SQL

statement.executeQuery();//查询操作，返回ResultSet
statement.execute();//执行任何SQL
statement.executeUpdate();//更新，插入，删除 都是用这个，返回一个受影响的行数
ResultSet 查询的结果集：封装了所有的查询结果

获得指定的类型

resultSet.getObject();//在不知道列类型的情况下使用
resultSet.getInt();
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getFloat();
resultSet.getDouble();
resultSet.getDate();
....

遍历，指针

resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一数据
resultSet.previous();//移动到前一行
resultSet.absolute(raw);//移动到指定行
释放资源
resultSet.close();
statement.close();
connection.close();

10.4statement对象

jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可

Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完成后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）。

Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作-crate

使用executeUpdate(String sql)方法完成数据的添加操作，示例操作：

Statement st = com.createStatement();
String sql = "insert into user(...) value(...)";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功！！！");
}
CRUD操作-delete
Statement st = com.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("删除成功！！！");
}
CRUD操作-update
Statement st = com.createStatement();
String sql = "update user set name='' where name=''";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("修改成功！！！");
}
CRUD操作-read

使用executeQuery(String sql)方法完成数据的查询操作，示例操作：

Statement st = com.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
    //根据获得列的数据类型，分别调用rs的相应方法映射到java对象中
}
代码实现

1、提取工具类

package com.zhang.lesson02.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {

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

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

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //驱动只用加载一次
            Class.forName(driver);

        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

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

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

2、编写增删改的方法，EXEcuteUpdate

package com.zhang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) {

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            Connection connection = JdbcUtils.getConnection();//获取数据库连接
            st = con.createStatement();//获取SQL的执行对象
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`)"+
                    "values(4,'zhangsan','123456','zh980412@163.com','1998-02-05')";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功！！！");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        JdbcUtils.release(con,st,rs);
    }
}

package com.zhang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestDelete {
    public static void main(String[] args) {

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            Connection connection = JdbcUtils.getConnection();//获取数据库连接
            st = con.createStatement();//获取SQL的执行对象
            String sql = "delete from users where id = 4";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功！！！");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        JdbcUtils.release(con,st,rs);
    }
}

package com.zhang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestUpdate {
    public static void main(String[] args) {

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            Connection connection = JdbcUtils.getConnection();//获取数据库连接
            st = con.createStatement();//获取SQL的执行对象
            String sql = "update users ser `NAME`='zhangsan',`email`='zh980412@163.com' while id=1";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("更新成功！！！");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        JdbcUtils.release(con,st,rs);
    }
}

3、查询 executeQuery

package com.zhang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestSelect {
    public static void main(String[] args){

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            st = con.createStatement();
            //SQl
            String sql = "select * from users where id=1";
            rs = st.executeQuery(sql);//查询
            if(rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }
    }
}
SQL注入的问题

sql存在漏洞，会被攻击导致数据泄露 SQL会被拼接 or

package com.zhang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQl注入 {
    
    public static void main(String[] args) {
        //login("zhangsan","123456");
        login(" 'or '1=1"," ' or '1=1");
    }

    public static void login(String username,String passwprd){
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            st = con.createStatement();
            //SQl
            //select * from users where `NAME`='zhangsan' and `password`='123456';
            //select * from users where `NAME`=' 'or '1=1' and `password`=' 'or '1=1';
            String sql = "select * from users where `NAME`='"+username+"' and `password`='"+passwprd+"'";
            rs = st.executeQuery(sql);//查询
            if(rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }
    }
}

10.5、PreparedStatement对象

PreparedStatement可以预防SQL注入。效率更好！

1、新增

package com.zhang.lesson03;

import com.zhang.lesson01.JdbcFirstDemo;
import com.zhang.lesson02.utils.JdbcUtils;
import java.util.Date;
import java.sql.*;

public class TestInsert {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pst = null;

        try {
            con = JdbcUtils.getConnection();
            //区别
            //使用？占位符代替参数
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
            pst = con.prepareStatement(sql);//预编译SQl，先写SQl，然后不执行
            //手动给参数赋值
            pst.setInt(1,4);
            pst.setString(2,"zhangsan");
            pst.setString(3,"123456");
            pst.setString(4,"zh980412@163.com");
            //注意点：sql.Date    数据库     java.sql.Date
            //      util.Date    java     new Date().getTime()   获得时间戳
            pst.setDate(5,new java.sql.Date(new Date().getTime()));
            //执行
            int i = pst.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(con,pst,null);
        }
    }
}

2、删除

package com.zhang.lesson03;

import com.zhang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TestDelete {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pst = null;

        try {
            con = JdbcUtils.getConnection();
            //区别
            //使用？占位符代替参数
            String sql = "delete from users where id=?";
            pst = con.prepareStatement(sql);//预编译SQl，先写SQl，然后不执行
            //手动给参数赋值
            pst.setInt(1,4);
            //执行
            int i = pst.executeUpdate();
            if(i>0){
                System.out.println("删除成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(con,pst,null);
        }
    }
}

3、更新

package com.zhang.lesson03;

import com.zhang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TestUpdate {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pst = null;

        try {
            con = JdbcUtils.getConnection();
            //区别
            //使用？占位符代替参数
            String sql = "update users set `NAME`=?  where id=?;";
            pst = con.prepareStatement(sql);//预编译SQl，先写SQl，然后不执行
            //手动给参数赋值
            pst.setString(1,"张三");
            pst.setInt(2,1);
            //执行
            int i = pst.executeUpdate();
            if(i>0){
                System.out.println("更新成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(con,pst,null);
        }
    }
}

4、查询

package com.zhang.lesson03;

import com.zhang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestSelect {
    public static void main(String[] args) {

        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();

            String sql = "select * from users where id=?;";

            pst = con.prepareStatement(sql);

            pst.setInt(1,1);

            rs = pst.executeQuery();

            if(rs.next()){
                System.out.println(rs.getString("NAME"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,pst,rs);
        }
    }
}

5、防止SQL注入

package com.zhang.lesson03;

import com.zhang.lesson02.utils.JdbcUtils;
import java.sql.*;

public class SQl注入 {
    public static void main(String[] args) {
        //login("zhangsan","123456");
        login("'' or 1=1","'' or 1=1");
    }

    public static void login(String username,String passwprd){

        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            //PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符
            //假设其中存在转义字符，比如说‘ 会被直接转义
            String sql = "select * from users where `NAME`=? and `password`=?";
            pst = con.prepareStatement(sql);

            pst.setString(1,username);
            pst.setString(2,passwprd);

            rs = pst.executeQuery();
            if(rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }
    }
}

10.7、使用IDEA连接数据库

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

10.8、JDBC操作事务

要么都成功，要么都失败

ACID原则

原子性：要么全部完成，要么都不完成

一致性：总数不变

隔离性：多个事务互不干扰

持久性：一旦提交不可逆，持久化到数据库了

隔离性的问题：

脏读：一个事务读取了另一个没有提交的事务

不可重复的；在同一个事务内，重复读取表中的数据，表数据发生了改变

幻读（虚读）：在一个事务内，读取了别人插入的数据，导致前后读出来结果不一致

代码实现

1、开启事务 con.setAutoCommit(false)

2、一组业务执行完毕，提交事务

3、可以再catch语句中显示的定义回滚语句，但默认失败就会回滚

package com.zhang.lesson04;

import com.zhang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {
            con = JdbcUtils.getConnection();
            //关闭数据库自动提交，自动会开启事务
            con.setAutoCommit(false);//开启事务
            
            String sql1 = "update account set money=money-100 where name=A;";
            pst = con.prepareStatement(sql1);
            pst.executeUpdate();

            int x = 1/0;

            String sql2 = "update account set money=money+100 where name=b;";
            pst = con.prepareStatement(sql2);

            con.commit();
            System.out.println("成功！");
        } catch (SQLException throwables) {
            //如果失败，默认回滚
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,pst,rs);
        }
    }
}

10.9、数据库连接池

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

连接—释放 十分浪费资源

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

--------开门—业务员 ： 等待—服务----

最小连接数：10

最大连接数：15业务最高承载上限

等待超时：100ms

编写连接池，实现一个接口 DataSource

开源数据源实现（拿来即用）

DBCP

C3P0

Druid：阿里巴巴

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

DBCP

需要用到的jar包

commons-dbcp-1.4、commons-pool-1.6

C3P0

需要导入的jar包

c3p0-0.9.5.5、mchange-commons-java-0.2.19

结论

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

Druid

stem.out.println(rs.getString(“NAME”));
System.out.println(rs.getString(“password”));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(con,st,rs);
}
}
}




### 10.7、使用IDEA连接数据库

[外链图片转存中...(img-qO38Pb7R-1606715263427)]





### 10.8、JDBC操作事务

**要么都成功，要么都失败**

> ##### ACID原则

原子性：要么全部完成，要么都不完成

一致性：总数不变

隔离性：多个事务互不干扰

持久性：一旦提交不可逆，持久化到数据库了



隔离性的问题：

脏读：一个事务读取了另一个没有提交的事务

不可重复的；在同一个事务内，重复读取表中的数据，表数据发生了改变

幻读（虚读）：在一个事务内，读取了别人插入的数据，导致前后读出来结果不一致



> ##### 代码实现

1、开启事务  **con.setAutoCommit(false)**

2、一组业务执行完毕，提交事务

3、可以再catch语句中显示的定义回滚语句，但默认失败就会回滚

```java
package com.zhang.lesson04;

import com.zhang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {
            con = JdbcUtils.getConnection();
            //关闭数据库自动提交，自动会开启事务
            con.setAutoCommit(false);//开启事务
            
            String sql1 = "update account set money=money-100 where name=A;";
            pst = con.prepareStatement(sql1);
            pst.executeUpdate();

            int x = 1/0;

            String sql2 = "update account set money=money+100 where name=b;";
            pst = con.prepareStatement(sql2);

            con.commit();
            System.out.println("成功！");
        } catch (SQLException throwables) {
            //如果失败，默认回滚
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,pst,rs);
        }
    }
}

10.9、数据库连接池

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

连接—释放 十分浪费资源

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

--------开门—业务员 ： 等待—服务----

最小连接数：10

最大连接数：15业务最高承载上限

等待超时：100ms

编写连接池，实现一个接口 DataSource

开源数据源实现（拿来即用）

DBCP

C3P0

Druid：阿里巴巴

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

DBCP

需要用到的jar包

commons-dbcp-1.4、commons-pool-1.6

C3P0

需要导入的jar包

c3p0-0.9.5.5、mchange-commons-java-0.2.19

结论

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

Druid

Apache

Amovy
关注
精选资源
MySQL学习笔记.zip
02-23
这份“MySQL学习笔记”将引导我们深入理解其核心概念和实用技能。 一、MySQL简介 MySQL是一个开源、免费的数据库系统，由瑞典的MySQL AB公司开发，后被Oracle公司收购。它的设计目标是速度、可移植性和简洁性，支持...
mysql中 的 ENGINE = innodb; 是什么意思？
qmvyq的专栏
09-12 2万+
存储引擎是innodb。innoDB 是 MySQL 上第一个提供外键约束的数据存储引擎，除了提供事务处理外，InnoDB 还支持行锁，提供和 Oracle 一样的一致性的不加锁读取，能增加并发读的用户数量并提高性能，不会增加锁的数量。InnoDB 的设计目标是处理大容量数据时最大化性能，它的 CPU 利用率是其他所有基于磁盘的关系数据库引擎中最有效率的。 InnoDB 是一套放在 MySQL
MySQL语法入门
abcnull 的博客
03-19 422
数据库操作： 创建数据库 CREATE DATABASE 数据库名; 创建数据库设置UTF-8编码 CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8; 创建数据库设置UTF-8编码并带校对规则 CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
SQL语句整理
weixin_43960165的博客
06-09 214
MySQL 简介 mysql 是一种关系型（中等）数据库，用来存储海量数据，最常见的使用是web端后台的数据存储。 是一种磁盘释数据库，访问需要通过tcp链接后进行访问 Mysql数据库引擎 Mysql在V5.1之前默认存储引擎是MyISAM；在此之后默认存储引擎是InnoDB 1. MyISAM：MYISAM强调了快速读取操作，这可能就是为什么MySQL受到了WEB开发如此青睐的主要原因：在...
mysql中engine=innodb和engine=myisam的区别
com360的专栏
06-26 2万+
这两种都是mysql的数据库的存储引擎的类型，一个支持事物等数据库的高级功能，一个不支持。两个各有用处，各有优劣。 1/ISAM  ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：
InnoDB逻辑存储结构
爱我所爱0505
08-06 1838
介绍了InnoDB存储引擎逻辑结构的组成部分，页的行格式及页数据页结构
mysql学习笔记
12-16
这份“mysql学习笔记”涵盖了MySQL的基础知识，包括但不限于数据库的概念、SQL语言的使用、以及更深入的多表操作。 在“day08入门笔记”中，你可能会学到以下内容： 1. **数据库基本概念**：了解什么是数据库，...
精选资源
数据库 MySQL 学习笔记高级篇.md
09-01
数据库 MySQL 学习笔记高级篇.md
除了 P 站，程序员摸鱼还喜欢上哪些网站？
singwhatiwanna
08-01 4286
来源：搜罗软件说到p站，估计不知道的人已经很少了，除了p站还有哪些站？应该很多人思考过这个问题了二次元可是有ABCDEGKNMT站...等等诸多圣地的！外面的世界很精彩，别抱着一个站啃...
MySQL之——崩溃-修复损坏的innodb：innodb_force_recovery
热门推荐
冰河的专栏
08-15 5万+
转载请注明出处：http://blog.youkuaiyun.com/l1028386804/article/details/77199194 一、问题描述 今天在线运行的一个mysql崩溃了。 查看错误日志，如下： ----------------------------------------- 161108 11:36:45 mysqld_safe Starting mysqld daemon...
[InnoDB系列] -- SHOW INNODB STATUS 探秘
weixin_33873846的博客
05-22 312
原文作者： Peter Zaitsev原文来源： http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/译者：叶金荣（Email:），转载请注明译者和出处，并且不能用于商业用途，违者必究。 很多人让我来阐述一下 SHOW INNODB STATUS 的输出信息, 了解 SHOW INNO...
获取文件编码
tansitongba
04-19 179
转载自：http://www.iteye.com/topic/540316 要用到三个包：chardet.jarantlr.jarcpdetector_1.0.7.jar importinfo.monitorenter.cpdetector.io.CodepageDetectorProxy; importinfo.monitorenter.cpdetector.io...
MySQL中ENGINE=InnoDB、AUTO_INCREMENT的意思
阿晖的博客
04-26 4万+
CREATE TABLE `hui_user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户表id', `username` varchar(50) NOT NULL COMMENT '用户名', `password` varchar(50) NOT NULL COMMENT '用户密码，MD5加密', `email` var...
mysql aesc_2_MySQL
weixin_32770541的博客
01-19 336
MySQL关系型数据库1970s - E.F.Codd - IBM研究员 - System R理论基础：关系代数和集合论具体表象：用二维表来保存数据行：一条记录 - 例如：学生的信息列：一个字段 - 例如：学生的某个属性(姓名、年龄、性别等)主键列：能够唯一标识一条记录的列，例如：学生的学号编程语言：SQL - 结构化查询语言DDL - 数据定义语言 - 建立库、二维表等使用create - 创建...
mysql innodb 导入_MySQL?innodb表修复以及导入优化
weixin_42623010的博客
02-05 249
今天经历了一次痛苦的mysql表修复操作，感觉还是比较有意义的，写出来供大家参考某客户的mysql出现异常，经常自动停止，err中如下记录090614 2:47:09 [Note] D:\MySQL\bin\mysqld-nt: ready forconnections.Version: '5.0.17-nt' socket: '' port: 3306 MySQL Community Editi...
InnoDB和MYIASM区别
jc_benben的专栏
08-11 1700
InnoDB与Myisam区别: 最新的版本都是用INNODB作为默认存储引擎 一，构成上的区别: 每个MyISAM在磁盘上存储成三个文件。第一个 文件的名字以表的名字开始，扩展名指出文件类型；创建时刻，create table时候 .frm文件存储表定义。 数据文件的扩 展名为.MYD (MYData)。 索引文件的扩 展名是.MYI (MYIndex)。     INNODB:分为共享和独立表...
ENGINE=InnoDB
weixin_33691817的博客
09-01 3507
最开始用MySQL Administrator建数据库的时候，表缺省是InnoDB类型，也就没有在意。后来用Access2MySQL导数据的时候发现只能导成 MyISAM类型的表     区别如下原来是MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。     MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经...
MySQL MyISAM/InnoDB高并发优化经验
weixin_33881753的博客
11-16 604
为什么80%的码农都做不了架构师？>>> ...
MySQL 学习笔记
最新发布
10-08
以下是一份系统的MySQL学习笔记整理，涵盖核心概念与实践要点，结合了您提供的引用内容： --- ### **一、MySQL基础架构** 1. **逻辑架构** - 连接层：客户端连接管理、认证 - 服务层：查询解析、优化、缓存...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值