1,初始MySql
1.1,为什么要学习数据库
- 岗位需求
- 大数据时代,得数据者得天下
- 被迫需求:存数据
- 数据库是所有软件体系中最核心的存在
1.2,什么是数据库
DB(DataBase)
概念:是指长期存储在计算机内的,有组织,可共享的数据的集合。数据库中的数据按一定的数学模型组织、描述和存储,具有较小的冗余,较高的数据独立性和易扩展性,并可为各种用户共享。
作用:存储数据,管理数据
1.3,数据库分类
关系型数据库:(SQL)
- MySql, Oracle, Sql Server, DB2, SQLlite
- 通过表和表之间,行和列之间的关系进行数据的存储
**非关系型数据库: ** (NoSQL)Not Only SQL
- Redis, MongDB
- 对象存储,通过对象自身属性来决定
1.4,MySql
-
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于Oracle旗下产品。
-
MySQL是一种关系型数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
-
MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。
命令行操作(以管理员身份运行命令提示符)
mysql -uroot -p123456 --连接数据库
update mysql.user set authentication_string=password('123') where user='root' and Host='localhost';--修改密码
show datebase;
use school;
show tables;
describe student;
exit;--退出连接
-
SQL (Structured Query Language)
-
DDL (Data Definition Language)
-
DML (Data Manipulation Language)
-
DQL (Data Query Language)
-
DCL: (Data Control Language)
操作数据库>操作数据库中的表>操作数据库中的表中的数据
2,操作数据库
2.1,操作数据库
- 创建数据库
CREATE DATABASE [IF NOT EXISTS] ym
- 查看数据库
SHOW DATABASES
- 使用数据库
USE ym--如果是特殊字符,需要带``,即 use `ym`
- 删除数据库
DROP DATABASE [IF EXISTS] ym
2.2,数据库列数据类型
数值型
- tinyint 十分小的数据 1字节
- smallint 较小的数据 2字节
- mediumint 中等大小的数据 3字节
- int 标准的整数 4字节
- bigint 较大的数据 8字节
- float 单精度浮点数 4字节
- double 双精度浮点数 8字节
- decimal 字符串形式的浮点数 一般用于金融计算
字符串
- char 字符串固定大小 0~255
- varchar 可变字符串 0~65536
- tinytext 微型文本 2^8 -1
- text 文本串 2^16 -1 保存大文本
时间日期
- date YYYY-MM-DD 日期格式
- time HH-mm-ss 时间格式
- datetime YYYY-MM-DD HH-mm-ss 最常用的日期时间格式
- timestamp 时间戳,1970.01.01到现在的毫秒数
- year 年份
null
- 空值
2.3,数据库的字段属性
Unsigned:
- 无符号的整数
- 声明了该列不能为负数
zerofill:
- 0填充
- 不足的位数,用0填充 eg:int(3) 5—>005
自增:
- 通常理解为自增,自动在上一条记录的基础上+1(默认)
- 通常用来设计唯一的主键
- 可以设置起始值和步长
非空:null,not null
- 假设设置为not null,如果不赋值,就会报错
- null,如果不填写,默认就是null
默认default:
- 设置默认的值,如果不指定该列的值,则该列为默认值
/*
每一张表,都必须存在以下5个字段
id 主键
`version` 乐观锁
`is_delete` 伪删除
`gmt_create` 创建时间
`gmt_update` 修改时间
*/
2.4,创建数据库表
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别', `birthday` DATETIME DEFAULT NULL COMMENT '出生日期', `address` VARCHAR(30) DEFAULT NULL COMMENT '家庭住址', `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
格式:
create table [if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
…………
`字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]
常用命令:
show create database `school`--查看创建数据库的语句
show create table `student`--查看创建表的语句
desc `student` --显示表的结构
2.5数据表的类型
/*
数据库引擎
InnoDB 默认使用的
MyISAM 早些年使用的
*/
MyISAM | InnoDB | |
---|---|---|
事务支持 | x | √ |
数据行锁定 | x | √ |
外键约束 | x | √ |
全文索引 | √ | x |
表空间的大小 | 较小 | 较大,约为2倍 |
节约空间,速度较快 | 安全性高 |
在物理空间存在的位置
所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库
MySql引擎在物理文件上的区别:
-
InnoDB在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
-
MyISAM对应的文件
*.frm 表结构的定义文件
*.MYD 数据文件(data)
*.MYI 索引文件()index
设置数据库表的字符集编码
charset=utf8
mysql默认的字符集编码为Latin1,不支持中文
2.6,删除修改表
sqlyog注释快捷键:
**Ctrl+Shift+C **将选中行注释
**Ctrl+Shift+R **去除选中行注释
修改表 alter
-- 修改表名
-- ALTER TABLE `oldName` RENAME AS `newName`
ALTER TABLE `student` RENAME AS `stu`
-- 增加表的字段
-- ALTER TABLE `表名` ADD 字段名 属性列
ALTER TABLE `stu` ADD `age` INT(10)
-- 修改表的字段
ALTER TABLE `stu` CHANGE `age` `age1` INT(11)-- 字段重命名
ALTER TABLE `stu` MODIFY `age1` VARCHAR(10)-- 修改约束
-- 删除表的字段
--ALTER TABLE `表名` DROP `字段名`
ALTER TABLE `stu` DROP `age1`
删除表 drop
-- 删除表
DROP TABLE IF EXISTS `stu`
注意:
- 所有的创建和删除操作尽量加上判断,以免报错
- 字段名用``包裹
- 注释 – /**/
- sql关键字大小写不敏感,建议小写
- 所有的符号用英文的
3,MySql数据管理
3.1,外键
给表添加外键约束
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
--alter table 表名 add constraint 约束名 foreign key (作为外键的列) references 哪个表(哪一列)
-
删除有外键关系的表的时候,必须先删除引用了别人的表(从表),再删除被引用了的表(主表)
-
以上物理级别的数据库外键不建议使用
-
数据库就是单纯的表,只有行和列
-
要想使用多张表的数据,使用外键的话,用程序去实现
3.2,DML语言
数据操纵语言
-
insert
-
update
-
delete
3.3,添加 insert
insert into 表名 ([字段名1],[字段名2]) values(‘值1’),(‘值2’)
INSERT INTO `grade` (`gradename`)
VALUES('大一'),
('大二'),
('大三'),
('大四')
INSERT INTO `student`
(`name`,`pwd`,`sex`,`birthday`,`address`,`email`,`gradeid`)
VALUES
('ym','111','女','2021.04.05','地球','111@111','1'),
('ymm','111','女','2021.04.05','地球','111@111','2'),
('ymmm','111','女','2021.04.05','地球','111@111','3')
3.4,修改 update
update 表名 set 列名=值,列名=值 where 条件
UPDATE `student` SET `pwd` = '999' , sex = '男' WHERE `id` = 3 AND `name`='ymmm'
3.5,删除 delete truncate
delete from 表名 where 条件
DELETE FROM `student` WHERE `id`=3
TRUNCATE 表名
delete | truncate | |
---|---|---|
相同点 | 都能删除数据,不会删除表结构 | |
不同点 | 删除一条或多条或所有记录, | 删除所有记录,重新设置自增列,计数器归0 |
delete删除,重启数据库后,
- InnoDB 自增量会从1开始 (存在内存中,断电即失)
- MyISAM 继续从上一个自增量开始 (存在文件中,不会丢失)
4,DQL查询数据
4.1,DQL
Data Query Language:数据查询语言
select 完整语法:
4.2,指定字段查询
select 字段1 ,…… from 表名
SELECT * FROM `student`
AS 起别名
concat()拼接
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM `student`
SELECT CONCAT('0000',`studentno`)AS 学号,`studentname` AS 姓名 FROM `student`
distinct 去重,相同的结果只保留一个
SELECT * FROM `result`
SELECT `studentno` FROM `result`
SELECT DISTINCT `studentno` FROM `result`
select 表达式 from 表名
SELECT VERSION()-- 查看系统版本
SELECT 100+100-5 AS 计算结果 -- 用来计算
SELECT `studentresult`+1 AS 学生分数 FROM `result` -- 所有学生的分数都加1
数据库中的表达式:函数,计算表达式,系统变量,文本值,……
4.3,where条件子句
逻辑运算符
- 逻辑与 and &&
- 逻辑或 or ||
- 逻辑非 not !
模糊查询 比较运算符(结果为true ,false)
- is null
- is not null
- between……and……
- like
- in
like 结合 通配符
% | 替代0个或多个字符 |
---|---|
_ | 仅替代一个字符 |
[charlist] | 字符列中的任何单一字符 |
[^charlist] [!charlist] | 不在字符列中的任何单一字符 |
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张%'
4.4,联表查询
思路:
- 分析需求,分析查询的字段来自哪些表
- 确定使用哪种连接查询
- 确定交叉点
- 条件
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS su
ON su.`subjectno` = r.`subjectno`
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
自连接
自己的表和自己的表连接,核心是:一张表拆成两张表
父类:
categogyid | categoryname |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类:
pid | categoryid | categoryname |
---|---|---|
3 | 4 | 数据库 |
3 | 6 | web开发 |
5 | 7 | ps技术 |
2 | 8 | 办公信息 |
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库,web开发 |
美术设计 | ps技术 |
SELECT a.`categoryname` AS 父栏目,b.`categoryname` AS 子栏目
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
4.5,分组和过滤
查询 课程平均分 大于80 的 不同课程 的 平均分,最高分,最低分
SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM `subject` AS sub
INNER JOIN `result` AS r
ON sub.`subjectno`=r.`subjectno`
ORDER BY sub.`subjectno`
HAVING AVG(`studentresult`) >70
4.6,排序和分页
排序 order by
把查询结果根据某字段按==升序(asc)或降序(desc)==排序
……
where……
order by ……
分页 limit
limit 数据起始下标,页面大小
第一页 limit 0 , 5
第二页 limit 5 , 5
第三页 limit 10 , 5
……
第 n 页 limit (n-1)*5 , 5
第 n 页 limit (n-1)*pagesize, pagesize
n:当前页,第n页
pagesize:页面大小,一页存几条数据
(n-1)*pagesize:页面的起始值,该页从第几条数据开始
总页数=(数据条数/页面大小)向上取整
4.7,子查询和嵌套查询
SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
WHERE `subjectno`=(SELECT `subjectno` FROM `subject` WHERE `subjectname`='C语言-1')
ORDER BY `studentresult` DESC
LIMIT 0,5
5,MySql函数
官网文献资料:官网文献资料
5.1,常用函数
数学运算
SELECT ABS(-6.5)-- 取绝对值
SELECT RAND()-- 取随机数
SELECT CEILING(6.5)-- 向上取整
SELECT FLOOR(6.5)-- 向下取整
SELECT SIGN(-8)-- 判断一个数的符号,正数返回1,负数返回-1,0返回0
字符串函数
SELECT CHAR_LENGTH('坚持就是胜利')-- 查询字符串长度,6
SELECT CONCAT('坚持','就是','胜利,加油')-- 字符串拼接,坚持就是胜利,加油
SELECT INSERT('坚持就会有结果',1,3,'努力')-- 从第一个字符(坚)开始,替换掉三个(坚持就),努力会有结果
SELECT UPPER('JavaPython123哈哈')-- 把含有的字母都转为大写
SELECT LOWER('JavaPython123哈哈')-- 把含有的字母都转为小写
SELECT INSTR('JavaPython','va')-- 判断子串在母串中是否存在,不存在返回0,存在返回字串的第一个字符在母串中的索引,3
SELECT REPLACE('javasqlpython','sql','and')-- javaandpython,替换
SELECT SUBSTR('坚持就是胜利',2,3)-- 从第二位(持)开始截取,截取三位(持就是)
SELECT REVERSE('琦卓刘')-- 反转字符串
时间和日期函数
SELECT CURRENT_TIME()-- 当前时间
SELECT NOW()-- 当前日期和时间
SELECT LOCALTIME()-- 当前日期和时间
SELECT SYSDATE()-- 系统日期和时间
SELECT YEAR(NOW())-- 年
SELECT MONTH('2021-02-04')-- 月
SELECT DAY(NOW())-- 日
SELECT HOUR(NOW())-- 时
SELECT MINUTE(NOW())-- 分
SELECT SECOND(NOW())-- 秒
系统
SELECT SYSTEM_USER()-- root@localhost
SELECT USER()-- root@localhost
SELECT VERSION()-- 5.7.20-log
5.2,聚合函数
SELECT COUNT(`email`) FROM `student`-- count(字段),会忽略所有的null值
SELECT COUNT(*) FROM `student`-- count(*) 不会忽略所有的null值,本质是计算行数
SELECT COUNT(1) FROM `student`-- count(1) 不会忽略所有的null值,本质是计算行数
SELECT SUM(`studentresult`) AS 成绩总和 FROM `result`
SELECT AVG(`studentresult`) AS 平均成绩 FROM `result`
SELECT MAX(`studentresult`) AS 最高分 FROM `result`
SELECT MIN(`studentresult`) AS 最低分 FROM `result`
5.3,数据库级别的MD5加密
MD5信息摘要算法(MD5 Message-Digest Algorithm),一种被广泛使用的密码散列函数,可以产生出一个128位(16字节)的散列值(hash value)
MD5不可逆
相同值的md5值也一样
-- 插入数据的时候给密码加密
INSERT INTO `testmd5` VALUES (1,'h',MD5('333444'))
-- 给id=3的密码加密
UPDATE `testmd5` SET pwd = MD5(pwd) WHERE `id`=3
6,事务
事务 transaction
一般是指要做的或所做的事情
指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。
事务的特性ACID:
-
原子性(atomicity)。一个事务是一个不可分割的工作单位,事务中包括的操作要么都做,要么都不做。
-
一致性(consistency)。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一个事务在执行之前和执行之后,数据库都必须处于一致性状态。
-
隔离性(isolation)。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
-
持久性(durability)。持久性也称永久性(permanence),指一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
隔离所导致的问题
- 脏读:指一个事务读到了另一个事务未提交的(提交前的)数据
- 不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同
- 虚读(幻读):指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致
模拟场景
-- 模拟转账
SET autocommit=0
START TRANSACTION
UPDATE `account` SET money=money-50 WHERE `name`='A'
UPDATE `account` SET money=money+50 WHERE `name`='B'
COMMIT;
ROLLBACK;
SET autocommit=1
7,索引
索引(index)是帮助mysql高效获取数据的数据结构
7.1,索引的分类
- 主键索引 Primary Key
- 唯一索引 Unique Key
- 常规索引 Key/index
- 全文索引 FullText
基础语法
-- 在创建表的时候给字段添加索引
-- 表创建完毕后,增加索引
SHOW INDEX FROM `student`-- 显示所有的索引信息
-- 增加一个全文索引
ALTER TABLE school.student ADD FULLTEXT INDEX `srudentname`(`studentname`)
-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM student
7.2,测试索引
插入1000000条数据,分析建立索引前后查找的快慢
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`)VALUES(CONCAT('用户',i),'11111qq.com','123456789',FLOOR(RAND()*2));
SET i=i+1;
END WHILE;
RETURN i;
END;
SELECT mock_data() -- 执行此函数 生成一百万条数据
-- 建立索引前
SELECT * FROM `app_user` WHERE `name`='用户9999'-- 总耗时 : 1.415 sec
CREATE INDEX id_app_user ON `app_user`(`name`)
-- 建立索引后
SELECT * FROM `app_user` WHERE `name`='用户9999'-- 总耗时 : 0.003 sec
7.3,索引原则
- 索引不是越多越好
- 不要对经常变动的数据添加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
8,权限管理和备份
用户权限
-- 创建用户
-- CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER ym IDENTIFIED BY '123456'
-- 修改指定用户的密码
SET PASSWORD FOR root@localhost = PASSWORD('root')
SET PASSWORD FOR ym =PASSWORD('ym')
-- 用户名重命名
RENAME USER ym TO ymm
-- 用户授权,all privileges on 库.表 全部的权限,除了grant
GRANT ALL PRIVILEGES ON *.* TO ymm
-- 查询权限
SHOW GRANTS FOR ymm
SHOW GRANTS FOR root@localhost
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM ymm
-- 删除用户
DROP USER ymm
备份
-
备份原因:保证数据不丢失;数据转移
-
备份方式:
- 直接copy物理文件
- 在sqlYog这种可视化工具中手动导出
- 使用命令行导出 musqldump
-- 导出
-- mysqldump -h主机名 -u用户名 -p密码 数据库 [表名1 表名2] > 存放路径
-- 导入
source 路径
9,规范数据库设计
数据库三大范式
参考:参考
-
第一范式(1NF):要求数据库表的每一列都是不可分割的原子数据项。
-
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖),第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。
-
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖),第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
10,JDBC
10.1,数据库驱动
10.2,JDBC
Java数据库连接(Java Database Connectivity,简称JDBC)是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。
java.sql
javax.sql
还需要导入一个jar包,mysql-connector-java-5.1.38.jar
10.3,第一个JDBC程序
创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE `users`(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
-
IDEA中创建一个项目
-
导入数据库驱动
新建lib
把jar包copy到lib中
jar包add as library
- 编写测试代码
package com.ym.lesson01;
import java.sql.*;
public class FirstJdbcDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1,加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2,填写信息 url,username,password
String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "root";
//3,连接数据库,返回数据库对象 connection
Connection connection = DriverManager.getConnection(url, username, password);
//4,通过connection创建执行sql的对象 statement
Statement statement = connection.createStatement();
//5,statement执行sql语句,返回结果集 resultSet
String sql= "SELECT * FROM `users`";
ResultSet resultSet = statement.executeQuery(sql);
//6,循环得到每条数据
while(resultSet.next()){
System.out.println("id:"+resultSet.getObject("id"));
System.out.println("NAME:"+resultSet.getObject("NAME"));
System.out.println("PASSWORD:"+resultSet.getObject("PASSWORD"));
System.out.println("email:"+resultSet.getObject("email"));
System.out.println("birthday:"+resultSet.getObject("birthday"));
System.out.println("============");
}
//7,释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
- 加载驱动
- 连接数据库,返回数据库对象 connection
- 通过connection创建执行sql的对象 statement
- statement执行sql语句,返回结果集 resultSet
- 释放连接
10.4, statement对象
-
jdbc中的statement对象用于向数据库发送sql语句
-
想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查的sql语句即可
-
statement对象的executeUpdate方法,用于向数据库发送 增,删,改 的sql语句。executeUpdate执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化。受影响的行数)
-
statement对象的executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的resultSet对象
db.propertied
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root
JdbcUtils
package com.ym.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
//提升作用域
private static String driver=null;
private static String url=null;
private static String username=null;
private static String password=null;
static{
try {
//读取配置文件db.properties,返回输入流
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 (Exception e) {
e.printStackTrace();
}
}
//2,获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//3,释放资源
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();
}
}
}
}
insert
package com.ym.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertTest {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
//获取链接
conn = JdbcUtils.getConnection();
//创建执行sql的st对象
st = conn.createStatement();
String sql="INSERT INTO `users`(id,NAME,PASSWORD,email,birthday) VALUES(6,'ym','123456','ym@qq.com','2021-04-07')";
//执行sql语句
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放连接
JdbcUtils.release(conn,st,null);
}
}
}
delete
package com.ym.lesson02.utils;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class DeleteTest {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql="DELETE FROM `users` WHERE `id`=4";
int i = statement.executeUpdate(sql);
if(i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,null);
}
}
}
update
package com.ym.lesson02.utils;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class UpdateTest {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "UPDATE `users` SET `NAME`='lzq' WHERE `id`=6";
int i = statement.executeUpdate(sql);
if(i>0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,null);
}
}
}
Query
package com.ym.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SelectTest {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql="SELECT `id`,`NAME`,`PASSWORD` FROM `users` WHERE id IN(1,2,3)";
resultSet = statement.executeQuery(sql);
while(resultSet.next()){
System.out.println(
"id:"+resultSet.getInt("id")+
",NAME:"+resultSet.getString("NAME")+
",PASSWORD:"+resultSet.getString("PASSWORD"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
sql注入
package com.ym.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Sql注入 {
public static void main(String[] args) {
// login("lisi","123456");
login(" 'or'1=1"," 'or'1=1");
}
public static void login(String username,String password){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
// SELECT * FROM `users` WHERE `NAME`='lisi' AND `PASSWORD`='123456'
String sql="SELECT * FROM `users` WHERE `NAME`='"+username+"' and `PASSWORD`='"+password+"'";
resultSet = statement.executeQuery(sql);
while(resultSet.next()){
System.out.println(
"id:"+resultSet.getInt("id")+
",NAME:"+resultSet.getString("NAME")+
",PASSWORD:"+resultSet.getString("PASSWORD"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
10.5,PreparedStatement对象
insert
package com.ym.lesson03;
import com.ym.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class InsertTest {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement st = null;
try {
connection = JdbcUtils.getConnection();
//使用?占位符代表参数值
String sql="INSERT INTO `users`(id,NAME,PASSWORD,email,birthday) VALUES(?,?,?,?,?)";
//预编译sql
st = connection.prepareStatement(sql);
//手动给参数赋值
st.setInt(1,4);
st.setString(2,"xxx");
st.setString(3,"123456");
st.setString(4,"111@111");
st.setDate(5,null);
//执行,返回受影响的行数
int i = st.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,st,null);
}
}
}
delete
package com.ym.lesson03;
import com.ym.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DeleteTtest {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement st = null;
try {
connection = JdbcUtils.getConnection();
String sql="delete from users where id=?";
st = connection.prepareStatement(sql);
st.setInt(1,4);
int i = st.executeUpdate();
if(i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,st,null);
}
}
}
update
package com.ym.lesson03;
import com.ym.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class UpdateTest {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement st = null;
try {
connection = JdbcUtils.getConnection();
String sql="update users set `NAME`=? where `id`=?";
st = connection.prepareStatement(sql);
st.setString(1,"ym");
st.setInt(2,2);
int i = st.executeUpdate();
if(i>0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,st,null);
}
}
}
query
package com.ym.lesson03;
import com.ym.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SelectTest {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
connection = JdbcUtils.getConnection();
String sql = "select * from users where `id`in (?,?)";
st = connection.prepareStatement(sql);
st.setInt(1,1);
st.setInt(2,2);
rs = st.executeQuery();
while(rs.next()){
System.out.println(
"id:"+rs.getInt("id")+
",NAME:"+rs.getString("NAME")+
",PASSWORD:"+rs.getString("PASSWORD"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,st,rs);
}
}
}
sql注入
package com.ym.lesson03;
import com.ym.lesson02.utils.JdbcUtils;
import java.sql.*;
public class Sql注入 {
public static void main(String[] args) {
// login("ym","123456");
login(" ' ' or 1=1"," ' 'or 1=1");
}
public static void login(String username,String password){
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql="SELECT * FROM `users` WHERE `NAME`=? and `PASSWORD`=?";
statement = connection.prepareStatement(sql);
statement.setString(1,username);
statement.setString(2,password);
resultSet = statement.executeQuery();
while(resultSet.next()){
System.out.println(
"id:"+resultSet.getInt("id")+
",NAME:"+resultSet.getString("NAME")+
",PASSWORD:"+resultSet.getString("PASSWORD"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
10.6,使用IDEA连接数据库
10.7,操作事务
package com.ym.lesson04;
import com.ym.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TransactionTest {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement pst = null;
try {
connection = JdbcUtils.getConnection();
connection.setAutoCommit(false);//关闭数据库的自动提交,默认自动开启事务
String sql1 = "update account set money=money-100 where name ='A' ";
pst = connection.prepareStatement(sql1);
pst.executeUpdate();
/*int x=1/0;//会报错*/
String sql2 = "update account set money=money+100 where name ='B' ";
pst = connection.prepareStatement(sql2);
pst.executeUpdate();
connection.commit();//业务完毕,提交事务
System.out.println("执行成功!");
} catch (SQLException e) {
/*try {
connection.rollback();//失败则回滚,默认会回滚,不写也可
} catch (SQLException ex) {
ex.printStackTrace();
}*/
e.printStackTrace();
}finally {
JdbcUtils.release(connection,pst,null);
}
}
}
10.8,数据库连接池
数据库连接–执行完毕–释放连接 浪费系统资源
池化技术:准备一些预先的资源,过来就连接预先准备好的
常用连接数
最小连接数
最大连接数
等待超时
编写连接池,实现接口DataSource
开源数据源
DBCP
C3P0
Druid
使用了这些数据库连接池之后,我们在项目中就不需要编写连接数据库的代码了
DBCP
C3P0