MySQL学习总结-详细版(包括下载安装)

一、准备工作

1.1 安装建议

  • 不推荐是用exe安装,存在注册表难以卸载的情况。

  • 使用压缩包安装,卸载简单。

1.2 安装MySQL

  1. 下载对应版本的压缩包

  2. 放到自己对应的软件安装目录

  3. 将解压后的bin目录配置到环境变量下

  4. 新建mysql配置:

    1. 在解压后的目录中新建配置文件,my.ini

    2. [mysqld]
      basedir=D:install_dirMySQLmysql
      datadir=D:install_dirMySQLmysqldata
      port=3306
      skip-grant-tables
      
    3. 解释,上面中之后两个目录需要换成自己对应的解压目录即可,basedir是解压目录;datadir是自己解压目录下的data目录,等会自己创建;

  5. 启动管理员模式下的cmd,并将路径切换至mysql下的目录,然后输入mysqld -install(安装mysql):

    1. 这里出现了安装失败,可能之前安装过mysql,问题如下:image-20210131164143435
    2. 解决方案,就是删除存在的文件夹:image-20210131164407602
  6. 再输入mysqld --initialize -insecure --user=mysql 初始化数据文件:

    1. 出现莫名初始化不了data目录下的系统默认数据库,最后解决办法,换了一个版本的数据库解决。
  7. 然后再次启动mysql然后用命令mysql -u root -p进入mysql管理界面(初始密码可为空,p后面不要有空格,因为空格也算一个字符)

  8. 进入界面后更改密码:update mysql.user set authentication_string = password('123456') where user = 'root' and Host = 'localhost';

  9. 最后输入flush privileges;刷新权限

  10. 修改my.ini文件删除最后一句skip-grant-tables

  11. 重启mysql即可正常使用:

    • net stop mysql
    • net start mysql

1.3 安装SQLyog

  1. 下载:

  2. 无脑下一步,然后安装即可

  3. 然后,直接连接,一个连接地址为:本地localhost 等价于127.0.0.1;用于名为root;密码为自己设置的,然后连接即可

二、简单MySQL操作

2.1 基本语句

--命令行的连接数据库
mysql -uroot -p密码
--mysql语句要为分号结尾
show databases;--显示数据库
use 数据库名;--切换数据库
describe 表名;--查看表
exit;--退出连接
create database 数据库名;--创建数据库
-- 单行注释
/*

多行注释
*/

2.2 数据库xxx语言

  1. DDL 定义

  2. DML 管理

  3. DCL 控制

  4. DQL 查询

2.3 操作数据库

2.3.1了解,学会对比SQLyog可视化历史记录

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

2.3.2数据库的列类型

数值

  • tinyint :十分小的数据 1个字节

  • smallint: 较小的数据 2个字节

  • mediumint: 中等大小字节 3个字节

  • 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 保存大文本

时间日期

  • data:YYYY-MM-DD,日期格式

  • time:HH:mm:ss,时间格式

  • datetime:YYYY-MM-DD HH:mm:ss 最常用的

  • timestamp:时间戳,1970.1.1到现在的毫秒数!也较为常用!

  • year:年份表示

    null

  • 没有值,未知

  • 不要使用NULL进行运算,结果为NULL

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

  1. Unsigned:

    • 无符号的整数;
    • 声明了该列不能声明为负数
  2. zerofill:

    • 0填充的;

    • 不足的位数,使用0来填充

  3. 自增:

    • 通常理解为自增,自动在上一条记录的基础上+1(默认);
    • 通常用来设计唯一的主键~index,必须是整数类型;
    • 可以自定义设计主键自增的起始值和步长
  4. 非空:NUll not null

    • 假设设置为not null,如果不给它赋值,就会报错!

    • NULL,如果不填写值,默认就是null

  5. 默认:

    • 设置默认的值!
    • sex,默认值为男,如果不指定该列的值,则会有默认的值!

2.3.4 使用SQL创建表的一些注意点

  1. 使用英文(),表的名称和字段尽量使用``括起来;

  2. auto_increment 自增;

  3. 字符串使用单引号括起来;

  4. 所有的语句后面加,(英文的),最后一个不用加;

  5. primary key主键,一般一个表只有一个唯一键;

  6. 格式:

    CREATE TABLE [IF NOT EXISTS] `表名`(
    	'字段名' 列类型 [属性] [索引] [注释],
        '字段名' 列类型 [属性] [索引] [注释],
        ....
        '字段名' 列类型 [属性] [索引] [注释]
    )[表类型][字符集设置][注释]
    
  7. 常用命令:

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

2.4 关于数据库表类型

  1. INNODB与MYISAM的区别

image-20210201142914502

  1. 在物理空间的存在位置:

    • 所有数据库文件都在data目录下,一个文件夹就对应一个数据库,本质还是文件的存储;

    • MySQL引擎在物理文件上的区别:InnoDB在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件;MYISAM对应文件,frm文件表结构定义文件,MYD数据文件,MYI索引文件

  2. 设置数据库表的字符集编码:charset=utf8,MySQL默认不支持中文

2.5 表的一些操作

--修改表名 alter table 旧表名 rename as 新表名
alter table teacher rename as teacher1
--增加表的字段 alter table 表名 add 字段名 列属性
alter table teacher1 add age int(11)
--修改表的字段(重命名,修改约束!)
--alter table 表名 modify 字段名 列属性
alter table teacher1 modify age varchar(11) --修改约束
--alter table 表名 change 旧名字 新名字
alter table teacher1 change age age1 int(1) --字段重命名
-- change用来字段重命名,不能修改字段类型和约束;
-- modify不用来字段重命名,只能修改字段类型和约束;

--删除表的字段 alter table 表名 drop 字段名
alter table teacher1 drop age1
--删除表 drop table if exists 表名
drop table if exists teacher1
  • 所有创建和删除尽量加上判断,以免报错

三、MySQL的数据管理

3.1、外键(了解即可)

  • 方式一:创建表的时候, 直接定义

  • 方式二:创建表的时候没有外键,然后单独进行定义

    -- alter table `表名`
    -- add constraint `FK_外键名` foreign key(`外键名`) references `引用表名`(`引用键名`);
    alter table `student`
    add constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`);
    
  • 以上外键都是物理层面的外键,数据库级别外键,不建议使用

  • 最佳实践

    1. 数据库就是单纯的表,只是用来存数据,只有行(数据)和列(字段)
    2. 我们想使用多张表的数据,想使用外键(程序去实现)

3.2、DML语言(全部记住)

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

3.3、 添加

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

注意事项:

  • 语法insert into 表名([字段名1,字段名2,字段名3,..]) values('值1'),('值2'),('值3'.....)
  • 字段之间是使用英文逗号隔开
  • 字段可以省略,但必须一一对应,不能少
  • 可以插入多条数据,values后面的值,需要使用,隔开即可values(),(),()

3.4、修改

-- 修改学员名字,带了条件
update `student` set `name`='ts' where id = 1;

--不指定条件的情况下,会修改所有表!
update `student` set `name`='长江七号'

--修改多个属性,逗号隔开
update `student` set `name`='ts', `email`='23343@qq.com' where id = 1;

--语法:
update 表名 set colnum_name = value, [colnum_name = value, .....] where [条件]
  • 条件后的操作符:图片image-20210201171056330
  • 注意事项
    1. colnum_name 是数据库的列,尽量带上``
    2. 条件,筛选的条件,如果没有指定,则会修改所有的列
    3. value,是一个具体的值,也可以是一个变量
    4. 语法:update 表名 set colnum_name = value, [colnum_name = value, .....] where [条件]

3.5、删除

  • delete 命令
-- 删除数据
delete from `student`

--删除指定数据
delete from `student` where id = 1;

  • truncate 命令
-- 清空student表
truncate `student`
  • delete 的区别和truncate
  1. 相同点:都能删除数据,不会删除表结构
  2. 不同:
    • truncate 重新设置 自增列 计数器会归零
    • truncate 不会影响事务

四、DQL查询数据(最重点)

4.1、DQL

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

  • 所有的查询操作都用它,Selete

  • 简单的查询,复杂的查询它都能做

  • 数据库中最核心的语言,最重要的语句

  • 使用频率最高的语句

  • select语法:

    select [all | distinct]
    {* | table.* | [table.field1[as alias1][,table.field2[as alias2]][....]]}
    from table_name [as table_alias]
    	[left | right | inner join table_name2 on ] --联合查询
    	[where ...] --指定结果需满足的条件
    	[Group by ...] --指定结果按照哪几个字段来分组
    	[having] --过滤分组的记录必须满足的次要条件
    	[order by ...] --指定查询记录按一个或多个条件排序
    	[limit {[offset,]row_count | row_countOFFSET offset}];
    	-- 指定查询的记录从哪条至哪条
    -- 注意:[]括号代表可选的,{}括号代表必选的
    
-- 去重 distint
select * from result -- 查询全部成绩
select `StudentNo` from result 
select distinct `StudentNo` from result --发现重复数据,去重

--数据库的列(表达式)
select version() --查询系统版本(函数)
select 100*3-1 as 计算结果 --表达式
select @@auto_increment_increment --查询自增的步长(变量)


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

  • select 表达式 from 表

4.2、where条件子句

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

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

  1. 逻辑运算符:图片image-20210202094418332
-- and &&
select studentNo, StudentResult from result
where StudentResult >= 95 and StudentResult <= 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;
  1. 模糊查询 :比较运算符图片image-20210202094852398
-- 查询姓刘的同学
-- like结合 %(代表0到任意个字符) _(一个字符)
select StudentNo, StudentName from student
where StudentName like '刘%';

--in (具体的一个或则多个值)
--查询1001,1002,1003号学员
select StudentNo, StudentName from student
where StudentNo 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 BornDate is not null;

4.3、联表查询

  • join对比:图片image-20210202101701140
-- join (连接的表) on (判断的条件) 连接查询
-- where 等值查询

/*
1. 分析需求,分析查询字段来自哪些表;
2. 确定使用哪种连接查询? 7种
确定交叉点(这两个标表中哪个数据是相同的)
判断的条件:学生表中studentNo = 成绩表studentNo
*/
  • 自连接

自己的表和自己的表连接,核心:一张表拆成两张表

4.4、分页和排序

  • 排序:升序Asc,降序Desc
select s.StudentNo, StudentName, SubjectName, StudentResult
from student s
inner join result r
on s.StudentNo = r.StudentNo
inner join subject sub
on r.SubjectNo = sub.SubjectNo
where subjectName = '数据结构'
order by StudentResult asc
  • 分页:

    1. 缓解数据库压力,给人的体验更好,瀑布流
-- 分页,每页只显示五条数据
-- 语法:limit(查询起始下标, pageSize)
select s.StudentNo, StudentName, SubjectName, StudentResult
from student s
inner join result r
on s.StudentNo = r.StudentNo
inner join subject sub
on r.SubjectNo = sub.SubjectNo
where subjectName = '数据结构'
order by StudentResult asc
-- 第n页 limit (n-1) * pageSize, pageSize

4.5、子查询

  • 本质:是在where里面写一个查询语句,由里及外。
select distinct s.StudentNo, StudentName
from student s
inner join result r
on r.StudentNo = r.StudentNo
where StudentResult >= 80 and SubjectNo = (
	select SubjectNo from subject
    where SubjectName = '高等数学-2'
)

4.6、分组和过滤

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

五、MySQL的函数

5.1、常用函数

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


--字符串函数
select char_length() -- 字符串长度
select concat('wo', 'da') -- 拼接字符串
select insert('dasd', 1, 2, 'das') --查询,从某个位置替换某个长度
select lower('') -- 转小写
select upper('') --大写
select instr('', '') --返回第一次出现的子串的索引
select replace('dahsjdak', 'da', 'dsad') --替换出现的字符串


--时间和日期函数
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、聚合函数(常用)

  1. count() :计数

  2. sum():求和

  3. avg():平均值

  4. max():最大值

  5. min():最小值

    .....

select count(studentname) from student; --count(字段), 指定列,会忽略所有null值
select count(*) from student; --count(*), 不会忽略null值, 本质计算行数
select count(1) from student; --count(1), 不会忽略null值

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

5.3、数据库级别的MD5加密

  1. 定义:主要是增强算法复杂度和不可逆
  2. MD5不可逆,具体的值的md5是一样的
  3. MD5破解网站的原理,背后有一个字典,MD5加密后的值,加密的前值

六、事务

6.1、什么是事务

  • 要么成功,要么失败
  • 将一组SQL放在一个批次中去执行

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

1、原子性(Atomicity)

  • 要么都成功,要么都失败

2、一致性(Consistency)

  • 事务前后的数据完整性要保证一致

3、隔离性(Isolation)

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

  • 隔离所导致的一些问题

  1. 脏读:指一个事务读取了另一个事务未提交的数据。
  2. 不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同。这个不一定是错误,只是某些场合不对。
  3. 虚读(幻读):是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。

4、持久性(durability)- 事务提交

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

6.2、事务执行

-- 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 保存点名 -- 撤销保存点

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、测试索引

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

7.3、索引原则

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

索引的数据结构

Hash类型索引

MySQL索引背后的数据结构及算法原理http://blog.codinglabs.org/articles/theory-of-mysql-index.html

Btree:InnoDB的默认数据

八、权限管理和备份

8.1、用户管理

  1. SQL可视化管理

image-20210202221140896

  1. SQL命令操作
  • 用户表:mysql.user

    -- 创建用户 create user 用户名 indentified by '密码'
    create user ts indentified by '123456'
    
    -- 修改密码(修改当前用户密码)
    set password = password('123231')
    
    -- 修改密码(修改指定用户密码)
    set password for ts = password('4324242')
    
    -- 重命名 rename user 原来名字 to 新名字
    rename user ts to ruixi
    
    -- 用户授权 all privileges 全部的特权, 库.表
    -- all privileges除了给别人授权的权限
    grant all provileges on *.* to ruixi
    
    -- 查权限
    show grants for ruixi
    show grants for root@localhost
    
    -- 撤销权限 revoke
    revoke all privileges on *.* from ruixi
    
    -- 删除用户
    drop user ruixi
    

8.2、数据库备份

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

MySQL数据库备份方式

  1. 直接拷贝物理文件

  2. 在可视化工具中手动导出

  3. 使用命令行导出,mysqldump 命令行使用

    # mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
    mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
    
    # mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名1 表名2 表名3 > 物理磁盘位置/文件名
    mysqldump -hlocalhost -uroot -p123456 school student1 student2>D:/a.sql
    
    # mysqldump -h 主机 -u 用户名 -p 密码 数据库 > 物理磁盘位置/文件名
    
  4. 导入:

    • 先登入进入,然后使用命令:source d:/a.sql

九、规范数据库设计

9.1、为什么需要设计?

良好的数据库设计

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

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

  1. 分析需求:分析业务和需要处理的数据库的需求
  2. 概要设计:设计关系图E-R图

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

  • 收集信息,分析需求
    • 用户表(用户登录注销,用户个人信息,写博客,创建分类)
    • 分类表(文章分类)
    • 文章表(文章信息)
    • 友情链接(友链信息)
    • 自定义表(系统信息,某个关键字,某些字段)
  • 标识实体(把需求落实到字段)
  • 标识实体之间的关系
    • 写博客:user--->blog
    • 创建分类:user---->category
    • 关注:user---->user
    • .............

9.2、数据库三大范式

1、为什么需要数据规范化?

  • 信息重复
  • 更新异常
  • 插入异常
  • 删除异常

2、三大范式

第一范式(1NF)

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

第二范式(2NF)

  • 前提:满足第一范式

  • 每张表只描述一件事情

第三范式(3NF)

  • 前提:满足第一范式和第二范式

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

3、规范性和性能问题

  • 关联查询的表不得超过三张表
  • 考虑商业化的需求和目标,(成本,用户体验)数据库的性能更加重要
  • 在规范性能的问题的时候,需要适当考虑一下规范性
  • 故意给某些表增加一些冗余的字段(多表查询变成单表查询)
  • 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)

十、JDBC

10.1、数据库驱动

  • 程序会通过数据库驱动和数据库打交道

10.2、JDBC

sun公司为了简化开发人员的(对数据库的统一)操作,提供了一个(Java操作数据库的)规范,俗称JDBC

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

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

  • java.sql
  • javax.sql
  • 导入一个数据库驱动包

10.3、第一个JDBC程序

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;
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, 'ruixi', '123456', 'ts@qq.com', '1999-8-21'),
(2, 'qwe', '123456', 't32s@qq.com', '1998-8-21'),
(3, 'xi', '123456', 't232s@qq.com', '1997-8-21');
  1. 创建一个普通项目

  2. 导入数据库驱动

  3. 编写测试代码:

    package com.ruixi.lesson01;
    
    import java.sql.*;
    
    /**
     * @Author: ruixi
     * @Date: 2021/2/3 10:33
     */
    //第一个JDBC程序
    public class jdbcDemo01 {
            public static void main(String[] args) throws ClassNotFoundException, SQLException {
                    //1. 加载驱动
                    Class.forName("com.mysql.cj.jdbc.Driver");
                    //2. 用户信息和url
                    String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
                    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.println("id = " + resultSet.getObject("id"));
                            System.out.println("name = " + resultSet.getObject("name"));
                            System.out.println("pwd = " + resultSet.getObject("password"));
                            System.out.println("email = " + resultSet.getObject("email"));
                            System.out.println("birth = " + resultSet.getObject("birthday"));
                            System.out.println("==================================");
                    }
                    //6. 释放连接
                    resultSet.close();
                    statement.close();
                    connection.close();
    
            }
    }
    
    

    步骤总结:

    1. 加载驱动
    2. 连接数据库DriverManager
    3. 获得执行sql的对象Statement
    4. 获得返回的结果集
    5. 释放连接
  4. 遇见问题:发现连接不了,可能是驱动版本问题,解决方法

10.4、statement对象

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

Statement对象的executeUpdate方法,用于向数据库发送,增、删、改的sql语句,executeUpdate执行完后,将会返回一个整数(即几行发生了数据变化)

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

  • CRUD操作-create
Statement st = conn.createStatement();
String sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if(num > 0){
    System.out.println("插入成功!!!");
}

代码实现

  1. 提取工具类

    package com.ruixi.lesson02.utils;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.*;
    import java.util.Properties;
    
    /**
     * @Author: ruixi
     * @Date: 2021/2/3 14:36
     */
    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");
                            //1. 驱动只用加载一次
                            Class.forName(driver);
                    } catch (IOException | ClassNotFoundException e) {
                            e.printStackTrace();
                    }
            }
    
            //获取连接
            public static Connection getConnetion() throws SQLException {
                    return DriverManager.getConnection(url, username, password);
            }
    
            //释放连接资源
            public static void release(Connection conn, Statement st, ResultSet rs){
                    if(rs != null){
                            try{
                                    rs.close();
                            }catch(SQLException e){
                                    e.printStackTrace();
                            }
                    }
                    if(st != null){
                            try{
                                    rs.close();
                            }catch(SQLException e){
                                    e.printStackTrace();
                            }
                    }
                    if(conn != null){
                            try{
                                    rs.close();
                            }catch(SQLException e){
                                    e.printStackTrace();
                            }
                    }
            }
    }
    
    
  2. 编写增删改的方法,executeUpdate 查询使用executeQuery();

    package com.ruixi.lesson02;
    
    import com.ruixi.lesson02.utils.JdbcUtils;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * @Author: ruixi
     * @Date: 2021/2/3 15:00
     */
    public class TestInsert {
            public static void main(String[] args) {
                    Connection conn = null;
                    Statement st = null;
                    ResultSet rs = null;
    
                    try{
                            conn = JdbcUtils.getConnetion();
                            st = conn.createStatement();
                            String sql = "INSERT INTO users(id, `name`, `password`, `email`, `birthday`) " +
                                    "VALUES(5, 'ts', '123456', '32134@qq.com', '1212-02-02');";
                            int i = st.executeUpdate(sql);
                            if(i > 0){
                                    System.out.println("插入成功!!!");
                            }
                    } catch (SQLException e) {
                            e.printStackTrace();
                    }finally {
                            JdbcUtils.release(conn, st, rs);
                    }
            }
    }
    
    

10.5、SQL注入问题和PrepareStatement对象

  • sql存在漏洞,导致数据泄露
  • PrepareStatement防止SQL注入,同时更快;本质是把传进来的参数当做字符,假设其中存在转义字符会被直接转义
  • 代码:
package com.ruixi.lesson03;

import com.ruixi.lesson02.utils.JdbcUtils;

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

/**
 * @Author: ruixi
 * @Date: 2021/2/3 15:35
 */
public class TestInsert {
        public static void main(String[] args) {
                Connection conn = null;
                PreparedStatement st = null;

                try{
                        conn = JdbcUtils.getConnetion();

                        //区别
                        //使用? 占位符代替参数
                        String sql = "insert into users(id, `name`, `password`, `email`, `birthday`) values(?, ?, ?, ?, ?)";

                        st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
                        st.setInt(1, 5);
                        st.setString(2, "tsruixi");
                        st.setString(3, "43242");
                        st.setString(4, "3424223@qq.com");
                        //注意点: sql.Date 数据库 java.sql.Date()
                        //               util.Date Java     new Date.getTime() 获得时间戳
                        st.setDate(5, new java.sql.Date(new Date().getTime()));

                        //执行
                        int i = st.executeUpdate();
                        if(i > 0){
                                System.out.println("插入成功!!!");
                        }
                } catch (SQLException e) {
                        e.printStackTrace();
                }finally {
                        JdbcUtils.release(conn, st, null);
                }
        }
}

10.6、使用IDEA连接数据库

  • 第一步创建连接,可能存在驱动版本不一致,连接失败的情况,也可能不存在驱动

image-20210203162900868

  • 然后调整使用的数据库

image-20210203162756303

10.7、数据库连接池

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

连接---释放 十分浪费系统资源

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

最小连接数: 10

最大连接数:15

等待超时:100ms

编写一个连接池,实现一个DataSource

开源数据源实现

  • DBCP
  • C3P0
  • Druid:阿里巴巴

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

作者:睿晞
身处这个阶段的时候,一定要好好珍惜,这是我们唯一能做的,求学,钻研,为人,处事,交友……无一不是如此。
劝君莫惜金缕衣,劝君惜取少年时。花开堪折直须折,莫待无花空折枝。
曾有一个业界大牛说过这样一段话,送给大家:   “华人在计算机视觉领域的研究水平越来越高,这是非常振奋人心的事。我们中国错过了工业革命,错过了电气革命,信息革命也只是跟随状态。但人工智能的革命,我们跟世界上的领先国家是并肩往前跑的。能身处这个时代浪潮之中,做一番伟大的事业,经常激动的夜不能寐。”
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利.
原文地址:https://www.cnblogs.com/tsruixi/p/14369801.html