狂神说Mysql课堂学习笔记，jdbc（含代码，下载链接）

2023-08-11 14:16:58

连接数据库

命令行连接！

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

码农公寓

连接数据库

2操作数据库

2.2数据库的列类型

2.4 创建数据表

2.5修改表名

3 mysql的数据管理

3.1 外键（了解）

3.2DML语言（全记住）

3.3 添加insert

3.4 修改update

3.5 删除delete 和truncate

4 DQL查询数据（最重点）

4.1 DQL

4.2去重distinct

4.3where

4.4联表查询joinON

4.5分页和排序

4.6 子查询

5 mysql函数

5.1 常用函数

5.2 聚合函数(常用)

5.3 md5加密算法

6 事务

7 索引

7.1 索引的分类

7.2 创建百万条数据

7.3 索引原则

8 权限管理和备份

8.1 用户管理

8.2 MySql备份

9 数据库设计

9.1 为什么要设计

三大范式（面试会聊）

10 JDBC（重点）

10.1 数据库驱动

10.2 JDBC

10.3 第一个JDBC程序

10.4 statement对象

10.5 PrepareStatement

10.7 使用idea连接数据库

10.8 事务

10.9 数据库连接池

相关文章