狂神说Mysql课堂学习笔记,jdbc(含代码,下载链接)

连接数据库

命令行连接!

mysql -uroot -p123456

命令行连接

mysql -uroot -p123456 --连接数据库


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

mysql > use school; -- 切换数据库use数据库名
database changed

show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有的表的信息

create database westos; -- 创建一个数据库 

创建数据库

CREATE DATABASE IF NOT EXISTS westos

删除数据库

DROP DATABASE IF EXISTS westos

2操作数据库

2.2数据库的列类型

数值

tinyint 十分小的数据 一个字节

smallint 较小的数据 两个字节

int 标准的数据 四个字节

字符串类型

char 0-255

varchar 可变字符串 0-65535

text 文本串 2^16 - 1 保存大文本

时间日期类型

date YYYY-MM-DD 日期格式

time HH:MM:SS 事件格式

datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式

null类型

没有值,位置

2.4 创建数据表

创建一个school数据库
目标
学号int,姓名name,密码varchar (20),性别varchar(2),出生日期(datatime),家庭佳址,email
注意点,使用英文 ()表的名称 和字段尽量使用

AUTO INCREMENT = 自增

NOT NULL = 非空

DEFAULT ‘匿名’ = 默认值为 :匿名

PRIMARY KEY(`id`) = 主键,一般写在后面

CREATE TABLE IF NOT EXISTS `student2` (
	`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 '性别',
	`birthdey` DATETIME DEFAULT NULL COMMENT '生日',
	`adress` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

格式

CREATE TABLE [IF NOT EXISTS] `表名` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`字段名` 列类型 [属性] [索引] [注释]
	PRIMARY KEY(`id`)
)[表类型] [字符集设置]

SHOW CREATE DATABASE school --查看创建数据库的语句
SHOW CREATE TABLE student – 查看student数据表的定义语句

DESC student – 显示表的结构

engine : 引擎

右键-改变表[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Oy5i7Hda-1646288483975)(C:\Users\胡子恒\AppData\Roaming\Typora\typora-user-images\image-20220205231141849.png)]

数据库引擎:

innodb 默认使用

myisam 早些年使用的

区别

myisam innodb
事务(两个sql语句执行,
要么都成功要么都失败)
不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大,约为两倍
  • myisam 节约空间
  • innodb 安全性高,事务的处理,多表多用户操作

在物理空间存在的位置

myisam 对应的文件

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

  • .myd --表结构的定义文件
  • .frm --数据文件(data) sdi
  • .myi --索引文件(index)

innodb对应的是ibd文件

字符集编码设置

charset = utf8

mysql 默认的编码是latin1

不支持中文

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

character-set-server = utf8

2.5修改表名

ALTER TABLE student2 RENAME AS student1  
--修改表名
ALTER TABLE student1 ADD age INT(11)
--修改字段名
ALTER TABLE student1 MODIFY age VARCHAR(11)
--修改约束modify
ALTER TABLE student1 CHANGE age age1 INT(1)
--字段重命名change
ALTER TABLE student1 DROP age1; 
--删除表中的字段age1

DROP TABLE IF EXISTS teacher1
--删除表

3 mysql的数据管理

3.1 外键(了解)

ALTER TABLE `student1`
ADD CONSTRAINT `fk_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
--将student1的gradeid设为外键,(grade中的gradeid就不能轻易删除了)

3.2DML语言(全记住)

dml数据操作语言

  • insert
  • update
  • delete

3.3 添加insert

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

INSERT INTO `grade`(`gradename`,`gradeid`) VALUES('大四','3')
INSERT INTO `grade` VALUES('2','一二三') --省略字段,但要一一对应
INSERT INTO `student1`(`id`,`name`,`pwd`,`sex`) VALUES('2','胡子','12345','男')


INSERT INTO `student1`(`id`,`name`) VALUES('3','张三'),('4','李四')
--同时插入多个数据

3.4 修改update

 ` UPDATE `student1` SET `name`='狂神2' WHERE id = '2'

--再student1 表中,id为2的name改为狂神2

UPDATE `student1` SET `name` = '狂神2' , `email` = '8191131892@qq.com' WHERE id = 2
--修改多个属性

条件where 子句,id等于某个值,再某个区间修改。。。

操作符 含义 范围 结果
= 等于
<> 或 != 不等于 5<>6 true
>= 大于
between 2 and 4 2和4范围内
and
or
UPDATE `student1` SET `name` = '小虎' WHERE NAME='狂神2' AND pwd='12345'
--and  多个条件

3.5 删除delete 和truncate

DELETE FROM `student1` WHERE `name` = '小虎'


DELETE FROM `student1`
--删除表student1的所有

**truncate **用它清空

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

TRUNCATE `student`

相同点

  • 相同点:都能删除数据,不改变表结构
  • 不同点:
    • truncate 重新设置自增列, 计数器归零
    • truncate 不会影响事务

delete 删除表后不会改变自动增量

(了解即可)delete表后,重启数据库

  • INNODB 自增会从1开始
  • MYISAM 继续从上一个增量开始

4 DQL查询数据(最重点)

4.1 DQL

(data query language 数据查询语言)

年级表代码

-- 创建年级表
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;

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


INSERT INTO `grade`(`GradeID`, `GradeName`)
VALUES (1, '大一'),
       (2, '大二'),
       (3, '大三'),
       (4, '大四'),
       (5, '预科班');
       
INSERT INTO `result`(`StudentNo`, `SubjectNo`, `ExamDate`, `StudentResult`)
VALUES (1000, 1, '2013-11-11 16:00:00', 85),
       (1000, 2, '2013-11-12 16:00:00', 70),
       (1000, 3, '2013-11-11 09:00:00', 68),
       (1000, 4, '2013-11-13 16:00:00', 98),
       (1000, 5, '2013-11-14 16:00:00', 58);
       
       
INSERT INTO `subject`(`SubjectNo`, `SubjectName`, `ClassHour`, `GradeID`)
VALUES (1, '高等数学-1', 110, 1),
       (2, '高等数学-2', 110, 2),
       (3, '高等数学-3', 100, 3),
       (4, '高等数学-4', 130, 4),
       (5, 'C语言-1', 110, 1),
       (6, 'C语言-2', 110, 2),
       (7, 'C语言-3', 100, 3),
       (8, 'C语言-4', 130, 4),
       (9, 'Java程序设计-1', 110, 1),
       (10, 'Java程序设计-2', 110, 2),
       (11, 'Java程序设计-3', 100, 3),
       (12, 'Java程序设计-4', 130, 4),
       (13, '数据库结构-1', 110, 1),
       (14, '数据库结构-2', 110, 2),
       (15, '数据库结构-3', 100, 3),
       (16, '数据库结构-4', 130, 4),
       (17, 'C#基础', 130, 1);

select 字段 from 表

SELECT `subjectname`,`classhour` FROM SUBJECT
--查询指定字段`subjectname`,`classhour` 

SELECT `subjectname` AS 学号,`classhour` AS 学生姓名 FROM SUBJECT 
--给字段起别名
SELECT CONCAT('学号为:',`subjectname`)AS 新输出格式 FROM SUBJECT
--拼接字符串

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

4.2去重distinct

SELECT DISTINCT `classhour` FROM SUBJECT

4.3where

SELECT `subjectno`,`gradeid` FROM SUBJECT WHERE gradeid>=1 AND gradeid <=2

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

模糊查询:比较运算符

运算符 语法 描述
is null a is null 如果操作符为null,结果为真
is not null
between a between b and c 匹配结果再b和c之间,结果为真
like a like b
in a in(a1,a2,a3…)
SELECT `subjectno`,`subjectname` FROM `subject` 
WHERE `subjectname` LIKE '数%'
-- subjectname 为‘数’开头的

WHERE `subjectname` LIKE '数_'  -- ‘数’后面一个字
WHERE `subjectname` LIKE '数__'  -- ‘数’后面两个字
WHERE `subjectname` LIKE '%数%'  -- 查询名字中‘有数’的字段
-- %用于like

SELECT `subjectno`,`subjectname` FROM `subject` 
WHERE `classhour` IS NULL
-- 查询classhour为null的记录

4.4联表查询joinON


SELECT SUBJECT.`subjectno`,`subjectname`,`studentresult`
FROM SUBJECT
INNER JOIN result
WHERE SUBJECT.`subjectno` = result.`subjectno`
-- 表subject和表result中都拥有的subject
操作 描述
inner join 如果表中至少一个匹配,就返回行
left join 会从左表中返回所有的值,即使右表中没有匹配
right join 会从游标中返回所有的值,即使坐标中没有匹配
SELECT SUBJECT.`subjectno`,`subjectname`,`studentresult`
FROM SUBJECT RIGHT JOIN result
ON SUBJECT.`subjectno` = result.`subjectno`

join (连接的表) on (判断的条件)连接查询

where 等值查询

4.5分页和排序

顺序:ASC,降序:DESC

SELECT *
FROM result
ORDER BY studentresult ASC

分页

limit 起始值,页面的大小

第一页 :limit 0,5

第二页:limit 5,5

第n页: limit 5*(n-1),5

4.6 子查询

嵌套,由里及外

SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `studentno` IN(
	SELECT `studentno` FROM `subject`
	WHERE `subjectname` LIKE '高%'
)
ORDER BY `studentresult` DESC

5 mysql函数

官网:https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

5.1 常用函数

SELECT ABS(-8)  -- 绝对值
SELECT CEILING(9.1) -- 向上取整
SELECT RAND() -- 返回一个0-1的随机数
SELECT SIGN(-2)  -- 返回参数的符号,负数返回-1
-- 字符串长度
SELECT CHAR_LENGTH('胡子恒a')
SELECT CONCAT('我','爱','你们') -- 连接字符串
SELECT INSERT('我爱我编程',2,2,'超级热爱') -- 替换第2个开始,三个字
SELECT LOWER('HuZiHeng') -- 小写字母
SELECT UPPER('HuZiHeng') -- 大写字母
SELECT INSTR('kuangshen','h')   -- 字母出现的起始位置
SELECT REPLACE('坚持就能成功','坚持','努力')   -- 替换出现的字符串


-- 日期和时间函数
SELECT CURRENT_DATE()  -- 获取当前日期
SELECT CURDATE()
SELECT NOW() -- 日期+时间
SELECT LOCALTIME()  
SELECT SYSDATE()
-- 具体时间  year,month,day,hour,minute,second
SELECT YEAR(NOW())  
SELECT HOUR(NOW())
  
-- 系统用户
SELECT SYSTEM_USER()
SELECT USER()

5.2 聚合函数(常用)

函数名称 描述
count() 计数
avg() 平均值
max() 最大值
sum() 求和
-- 分组
SELECT * FROM `subject`
GROUP BY  `GradeID`
-- 分组后where用having

5.3 md5加密算法

密码加密

CREATE TABLE `testmd5`(
	`id` INT(4) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(3,'lisi','123456'),(2,'wangwu','123456')

UPDATE testmd5 SET pwd = MD5(pwd) 

6 事务

解释连接:https://blog.csdn.net/dengjili/article/details/82468576/

要么都成功,要么都失败

例子:A给B转200块钱钱

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

原子性

要么都成功,要么都失败

一致性

事务前后的数据完整性要保证一致,(不超过1000)

持久性

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

隔离性

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


脏读:

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

不可重复读:

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

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据,导致前后读取数量总量不一致。
(一般是行影响,如下图所示:多了一行)


-- mysql 默认开始事务自动提交的
SET autocommit = 0  /*关闭*/
SET autocommit = 1  /*开启(默认的)*/
-- 手动处理事务
-- 事务开启
START TRANSACTION -- 标记一个事务的开始,在这个时候的sql都在同一个事务内

INSERT xx
INSERT xx

-- 提交:持久化(成功)
COMMIT
-- 回滚:回到原来的样子(失败)
ROLLBACK

-- 事务结束
SET autocommit = 1   -- 开启自动提交

-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名  -- 回滚到保存点
RELEASE SAVEPOINT 保存点名  -- 撤销保存点 

模拟转账

-- 模拟转账
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION  -- 开启一个事务

UPDATE account SET money = money -500 WHERE `name` = 'A'  ;-- 转账过程
UPDATE account SET money = money +500 WHERE `name` = 'B'

COMMIT; -- 提交事务,被持久化了
ROLLBACK;  -- 回滚

SET autocommit = 1; -- 恢复默认值

7 索引

7.1 索引的分类

  • 主键索引(primary key)
    • 唯一的标识,主键不可重复
  • 唯一索引(unique key)
    • 避免重复的列出现,唯一索引可以重复,多个列可以标识唯一索引
  • 常规索引(key / index)
    • 默认的index,key关键字来设置
  • 全文索引(full text)
    • 再特定的数据库引擎下才有
USE school
-- 显示所有索引信息
SHOW INDEX FROM `subject`
-- 添加全文索引
ALTER TABLE `subject` ADD FULLTEXT INDEX `SubjectName`(`SubjectName`);

-- explain 分析sql执行的状况
EXPLAIN SELECT *FROM student; -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘'); 

7.2 创建百万条数据

先建立表

-- 一百万条数据检验索引的效果。
use school;-- 数据库school
DROP TABLE IF EXISTS `app_user`
CREATE TABLE `app_user` (
	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
	`email` varchar(50) NOT NULL COMMENT '用户邮箱',
	`phone` varchar(20) DEFAULT '' COMMENT '手机号',
	`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别(0:男;1:女)',
	`password` varchar(100) NOT NULL COMMENT '密码',
	`age` tinyint(4) DEFAULT '0' COMMENT '年龄', 
	`create_time`  timestamp NULL DEFAULT NULL COMMENT '创建时间',
    `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    -- Mysql 5.5版本,需要注意:`create_time`和`update_time` 插入数据时,针对创建时间字段:在sql里now()  或者在代码里new date()更改后的sql,把默认值给个空,否则报错。
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';

在创建百万条数据

DROP FUNCTION IF EXISTS mock_data;
SET GLOBAL log_bin_trust_function_creators = 1;  
-- 写函数之前必须要写,标志:$$
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
-- 注意returns,否则报错。
BEGIN
DECLARE num INT DEFAULT 1000000;
-- num 作为截止数字,定义为百万,
DECLARE i INT DEFAULT 0;
WHILE i < num DO
   INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), CONCAT('100',i,'@qq.com'), CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
   SET i = i + 1;
END WHILE;
RETURN i;
END;

最后在执行

SELECT mock_data();
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'
-- 耗时 0.408

-- 常规索引名命名方式  id_表名_字段名
-- create index 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'  
-- 建立索引后速度贼快 0.002sec

7.3 索引原则

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

索引的数据结构

Btree:innoDB 默认的数据结构

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

8 权限管理和备份

8.1 用户管理

-- 新建用户
CREATE USER xiaohu IDENTIFIED BY '123456'
 -- 修改当前密码
SET PASSWORD = PASSWORD('123456')
-- 修改用户密码
SET PASSWORD FOR xiaohu = PASSWORD('123456')

-- 查看权限
SHOW GRANTS FOR xiaohu 
SHOW GRANTS FOR root@localhost

RENAME USER xiaohu TO xiaohu2  -- 重命名

GRANT ALL PRIVILEGES ON *.* TO xiaohu2 -- 设置所有权限

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON *.* FROM xiaohu2

-- 撤销用户
DROP USER xiaohu

-- 删除用户
DROP USER xiaohu2

8.2 MySql备份

  • 备份方式
    • 拷贝物理文件,date目录
    • sqlyog可视化工具中,手动导出
    • 选中表,右建,备份or导出
    • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BjQpKCCR-1646288483978)(C:\Users\胡子恒\AppData\Roaming\Typora\typora-user-images\image-20220211191851110.png)]
    • 使用命令行导出,mysqldump 命令行使用
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名  >  物理磁盘名 
C:\Users\胡子恒>mysqldump -hlocalhost -uroot -p123456 school student > D:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

# 导出
法二:先登录,#source 备份文件
	source D://a.sql
法一:mysql -u用户名 -p密码 库名 < 备份文件

9 数据库设计

9.1 为什么要设计

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

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

  • 手机信息,分析需求

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

  • 标识实体之间的关系

    • user --> blog
    • user --> categary
    • user --> user
    • 评论 ->user- user-comment

三大范式(面试会聊)

第一范式(1NF)

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

第二范式(2NF)

前提:满足第一范式

每张表只描述一件事情

第二范式(3NF)

规定数据表中每一列数据都和主键直接相关,而不能间接相关

规范性 和 性能的问题

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

  • 考虑商业化的需求和目标,(成本,用户体验)数据库性能更重要
  • 规范性能的问题的时候,需要适当考虑一下规范性
  • 会故意给某些表加一些冗余的字段(多表查询变成单表查询)
  • 故意增加一些计算列

10 JDBC(重点)

10.1 数据库驱动

10.2 JDBC

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

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

java.sql

10.3 第一个JDBC程序

1 创一个普通项目

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

2 导入驱动

下载jar驱动

地址:https://dev.mysql.com/downloads/file/?id=484819

,放在lib目录下

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

3 编写测试代码


import javax.xml.transform.Result;
import java.sql.*;

public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
//        1,加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");//固定写法
//        比视频里多加了这句话serverTimezone = UTC
        String url = "jdbc:mysql://localhost:3306/jdbcstudy ? useUnicode=true & characterEncoding=utf8 & useSSL=false & serverTimezone = UTC ";//jdbc是数据库的名称
        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的对象,可能存在结构,查看返回结果
        String sql = "SELECT * FROM users";
        ResultSet resuleSet = statement.executeQuery(sql);

        while (resuleSet.next()){
            System.out.print("id = " + resuleSet.getObject("id"));
            System.out.print(",name = " + resuleSet.getObject("NAME"));
            System.out.print(",pwd = " + resuleSet.getObject("PASSWORD"));
            System.out.print(",email = " + resuleSet.getObject("email"));
            System.out.println(",birthday = " + resuleSet.getObject("birthday"));
        }


//        6,释放连接
        resuleSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结

1.加载驱动

2.连接数据库DriverManger

3.获得执行sql的对象Statement

4.获得返回的结果

5.释放连接

10.4 statement对象

代码实现 增删改查

1.提取工具类

package com.kuang.lesson02.utils;

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

/**
 * @author shkstart
 * @create 2022-02-27-21:31
 */
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 e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() 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 {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

2.编写增删改的方法, executeUpdate

更新的代码

package com.kuang.lesson02.utils;

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

/**
 * @author shkstart
 * @create 2022-02-27-23:08
 */
public class Testupdate {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "UPDATE users SET `NAME` = 'kuangshen111',`email` = '11111@qq.com' WHERE id = 2";
            int i = st.executeUpdate(sql);
            if( i > 0){
                System.out.println("更新成功!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            JdbcUtils.release(conn,st,rs);
        }
    }
}

3 查询

package com.kuang.lesson02.utils;

import jdk.nashorn.internal.objects.annotations.Where;

import javax.swing.plaf.nimbus.State;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author shkstart
 * @create 2022-02-27-23:18
 */
public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            String sql = "select * from users where id = 1";
            rs = st.executeQuery(sql); //查询完毕,会返回一个结果集
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

sql注入问题

sql存在漏洞,会被攻击导致数据泄露

login("'or ' 1=1","'or ' 1=1");

代码如下,

package com.kuang.lesson02;

import com.kuang.lesson02.utils.JdbcUtils;

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

/**
 * @author shkstart
 * @create 2022-03-02-12:59
 */
public class SQL注入 {
    public static void main(String[] args) {
//        login("kuangshen","123456");
//        SQL注入
        login("'or ' 1=1","'or ' 1=1");

    }

//    登录业务
    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
//          SELECT * FROM users WHERE `NAME` = 'wangwu' AND `PASSWORD` = '123456'
            String sql = "select * from users where `NAME` = '" + username + "' AND  `password` = '123456" + "'";
            rs = st.executeQuery(sql); //查询完毕,会返回一个结果集
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

jdvautils代码如下

package com.kuang.lesson02.utils;

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

/**
 * @author shkstart
 * @create 2022-02-27-21:31
 */
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 e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() 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 {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

10.5 PrepareStatement

可以防止sql注入,并且效率更高

本质是:将传递进来的参数当作字符

查询代码

package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

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

/**
 * @author shkstart
 * @create 2022-03-02-16:14
 */
public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
//            区别
//            使用?占位符代替参数
            String sql = "select * from users WHERE id = ?";
            st = conn.prepareStatement(sql);//预编译sql,然后不执行
//            手动给参数赋值
            st.setInt(1,1);
//            执行
            rs = st.executeQuery();
            if(rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            JdbcUtils.release(conn,st,null);
        }
    }
}

插入代码

package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

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

/**
 * @author shkstart
 * @create 2022-03-02-13:55
 */
public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();
//            区别
//            使用?占位符代替参数
            String sql = "DELETE from users where id = ?";
            st = conn.prepareStatement(sql);//预编译sql,然后不执行
//            手动给参数赋值
            st.setInt(1,6);

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

防止sql注入

package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

import java.sql.*;

/**
 * @author shkstart
 * @create 2022-03-02-12:59
 */
public class SQL注入 {
    public static void main(String[] args) {
//        SQL注入
        login("huziheng","123456");

//        login("'or ' 1=1","'or ' 1=1");
    }
//    登录业务
    public static void login(String username,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();

            String sql = "select * from users where `NAME` = ? AND `password` = ?";
            st = conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);

            rs = st.executeQuery(); //查询完毕,会返回一个结果集
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.7 使用idea连接数据库

需要导入jar的目录,不然会连接失败!!

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

输入账号密码,然后连接

连接成功后,点击设置

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

双击数据库就能查看到内容

修改内容后,需要点db图标

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

编写sql代码的地方

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

切换数据库

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

连接失败原因可能是,版本不匹配

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

10.8 事务

要么都成功,要么都失败

ACID原则

**原子性:**要么全部完成,要么都不完成

**一致性:**总数不变

**隔离性:**多个进程互不干扰

**持久性:**一旦提交不可逆

隔离性的问题:

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

不可重复读:在同一个十五内,重复读取表中的数据,表数据发生了改变

虚度(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致

代码实现

        import com.kuang.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 conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
//            关闭数据库的自动提交功能
            conn.setAutoCommit(false);//开启事务

            String sql1 = "update account set money = money - 100 where name = 'A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            String sql2 = "update account set money = money + 100 where name = 'B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

//            业务完毕,提交事务
            conn.commit();
            System.out.println("成功!");

        } catch (SQLException e) {
            try {
                conn.rollback();//如果失败就回滚事务
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.9 数据库连接池

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

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

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

– 开门 – 业务员: 等待 – 服务 – 关门

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

开放数据源 (拿来就用)

DBCP

C3P0

Druid: 阿里巴巴

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

DBCP

commons-dbcp-1.4.jar

commons-pool-1.6.jar

蓝奏云下载连接

https://wwe.lanzouw.com/il5gX00x44xa

https://wwe.lanzouw.com/iOD9i00x44vi

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

先一处java8的jar,然后再三个一起添加

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

代码如下

package com.kuang.lesson05;

import com.kuang.lesson02.utils.JdbcUtils;
import com.kuang.lesson05.utils.jdbcutils_DBCP;

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

/**
 * @author shkstart
 * @create 2022-03-03-10:55
 */
public class TestDBCP {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;


        try {
            conn = jdbcutils_DBCP.getConnection();
//            区别
//            使用?占位符代替参数
            String sql = "insert into users(id,`NAME`,`password`,`email`,`birthday`)values(?,?,?,?,?)";
            st = conn.prepareStatement(sql);//预编译sql,然后不执行
//            手动给参数赋值
            st.setInt(1,8);
            st.setString(2,"kuangshen");
            st.setString(3,"123456");
            st.setString(4,"819114173@qq.com");
            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_DBCP.release(conn,st,null);
        }
    }
}

jdbcutils_DBCP

package com.kuang.lesson05.utils;

import com.kuang.lesson02.utils.JdbcUtils;
import org.apache.commons.dbcp.BasicDataSourceFactory;

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

/**
 * @author shkstart
 * @create 2022-03-03-10:44
 */
public class jdbcutils_DBCP {

    private static DataSource dataSource = null;
    static {
        try{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

//            创建数据源  工厂模式  --》 创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
//    获取连接
    public static Connection getConnection()throws SQLException{
        return dataSource.getConnection();//从数据源中获取连接
    }
    //释放连接资源
    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 {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

c3p0

上一篇:利用Python抢回在蚂蚁森林把逝去的能量


下一篇:Spring Boot 整合 Redis