连接数据库
命令行连接!
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