一、SQL语句
1、SQL语句分类
SQL分类：
- 数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等。关键 字：create，alter，drop等
- 数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键 字：insert，delete，update等
- 数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用 户
- 数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select， from，where等
SQL通用语法：
- SQL语句可以单行或多行书写，以分号结尾
- 可使用空格和缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
例如：SELECT * FROM user。 - 同样可以使用/**/的方式完成注释
- MySQL中的我们常使用的数据类型如下
| 类型名称 | 说明 |
|---|---|
| int（integer） | 整数类型 |
| double | 小数类型 |
| decimal（m,d） | 指定整数位与小数位长度的小数类型 |
| date | 日期类型，格式为yyyy-MM-dd，包含年月日，不包含时分秒 |
| datetime | 日期类型，格式为 YYYY-MM-DD HH:MM:SS，包含年月日时分秒 |
| timestamp | 日期类型，时间戳 |
| varchar（M） | 文本类型， M为0~65535之间的整数 |
2、DDL
2.1数据库操作database
1.创建数据库
create database 库名; #使用默认的编码表创建
create database 库名 character set 编码表; #使用指定的编码表创建
2.使用数据库
use 库名;
3.查看某个数据库的定义的信息
show create database 库名;
4.查看所有数据库
show databases;
5.查看正在使用的数据库
select database();
6.删除数据库
drop database 库名;
2.2表操作：table
1.创建表
创建表
格式:
create table 表名(
字段名1 类型(长度) 约束,
字段名2 类型(长度) 约束,
字段名3 类型(长度) 约束 #最后一个字段后面不用写逗号
);
类型：
varchar(n) 字符串
int 整型
double 浮点
date 时间
timestamp 时间戳
约束：
primary key 主键，被主键修饰字段中的数据，不能重复、不能为null。
/*
约束: 就是给某个字段/列的取值添加限制
比如: 某个列的取值不能重复(唯一),不能为空(非空)
主键: 作为主键的字段的取值不能重复(唯一),不能为空(非空)
主键约束: 被作为主键的字段,添加主键约束后,该字段则不能重复(唯一),不能为空(非空)
一般情况下使用id(编号)作为主键,不能使用具有业务意义的字段作为主键
如何添加主键约束?*/
在定义表结构时,作为主键的字段后面 + primary key
/*
注意:
如果主键的类型为int,可以设置主键自动增长,这样mysql负责维护主键的值,添加数据时,不用指定主键的值了
主键添加自动增长的方式:*/
在定义表结构时,作为主键的字段后面 + primary key auto_increment;
#不能设置每次增长多少,但是可以设置主键的值从几开始
ALTER TABLE 数据库表名 AUTO_INCREMENT=数据值;
2.操作表
- 查看数据库中的所有表：
show tables;
- 查看表结构：
desc 表名;
#desc student;
- 删除表
drop table 表名;
#drop table student;
- 查看表中所有内容
select * from 表名;
#select * from student;
- 查看表的创建信息
show create table 表名;
#show create table student;
3.修改表结构
- 添加列（字段）
alter table 表名 add `列名` 类型(长度) 约束;
#alter table student add `age` INT(10) NOT NULL;
- 修改列（字段）的类型、长度、约束
alter table 表名 modify `列名` 类型(长度) 约束
#alter table student modify `age` VARCHAR(20) NOT NULL;
- 修改列名（字段名）
alter table 表名 change `旧列名` `新列名` 类型(长度) 约束;
#alter table student change `age` `number` VARCHAR(100) NOT NULL;
- 删除列（字段）
alter table 表名 drop `列名`;
#alter table student drop `age`;
- 修改表名
rename table `旧表名` to `新表名`;
#rename table `student` to `teacher`;
#rename table `teacher` to `student`;
- 修改表的字符集
alter table 表名 character set 字符集
#alter table student character set gbk;
3、 DML
3.1插入表记录：insert(增)
- 语法：
-- 向表中插入某些字段
insert into 表名 (字段1,字段2,字段3..) values (值1,值2,值3..);
#insert into student(sid,name,sex,age) values(1,'小红','女',18);
-- 指定列值添加 主键自动增长,mysql帮助维护,添加数据时,可以不用写
insert into student(name,sex,age) values('小红','女',18);
-- 注意: 如果不指定字段,values中需要写null,为主键占一个位置
insert into student values(NULL,'小红','女',18);
-- 向表中插入所有字段,字段的顺序为创建表时的顺序
insert into 表 values (值1,值2,值3..);
- 批量添加
-- 指定字段添加
insert into student(sid,name,sex,age) values(1,'小红','女',18),(2,'小明','男',20);
-- 当主键自动增长时,可以不为主键添加内容
insert into student(name,sex,age) values('小红','女',18),('小明','男',20);
-- 不指定字段时，values中需要写null,为主键占一个位置
insert into student values (NULL,'小刚','男',21),(NULL,'小李','男',22);
-
注意：
值与字段必须对应，个数相同，类型相同
值的数据大小必须在字段的长度范围内
除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）
如果要插入空值，可以不写字段，或者插入null。
3.2更新表记录：update(改）
- 更新表中所有记录的指定字段
update 表名 set 字段名=值,字段名=值,...;
#update student set `name`='张三',`age='25';
- 更新表中符合条件记录的指定字段
update 表名 set 字段名=值,字段名=值,... where 条件;
#update student set `age='25' `sex`='男' where `name`='张三';
- java和mysql中的符号对应
/*
java mysql
> >
>= >=
< <
<= <=
== =
!= != 或者 <>
&& and
|| or
! not
*/
-- 并且关系
update student set `name`='王五' where `age`=18 and `sex`='男';
-- 或者关系
update student set `name`='王五' where `age`=18 or `sid`='6';
-- 不等于关系
update student set `name`='王五' where `age`!=18;
3.2删除表记录：delete(删)
- **方式一：**delete
#不写条件限制,会删除表中的所有记录
delete from 表名 where 条件;
#delete from student where `sid`=2;
- **方式二：**truncate
#删除表，清空表中所有数据，不记录id
truncate table 表名;
delete和truncate的区别?
- delete: 删除表的所有数据，会记录id的最大值，再次添加数据时会从id最大值下一位开始
- TRUNCATE: 删除表的所有数据，先摧毁整个表结构，重新创建，不会记录id的最大值，id从新开始
3.3 SQL约束
3.3.1.主键约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
-
主键必须包含唯一的值。
-
主键列不能包含 NULL 值。
-
每个表都应该有一个主键，并且每个表只能有一个主键。
方式一：
- 在定义表结构时,作为主键的字段后面+primary key
-- 在创建时添加
create table 表名(字段 类型 PRIMARY KEY , 字段 类型 条件约束 );
#create table student(sid INT PRIMARY KEY,name VARCHAR(10));
方式二：
- 在定义表结构时，已经指定了所有的字段，在最后使用constraint关键字，添加主键约束
-- 在创建时，添加
1.create table 表名(字段 类型 条件约束 , 字段 类型 条件约束 ,
constraint 主键约束名称 primary key(作为主键的字段名)
);
#create table teacher(sid INT,name VARCHAR(10),
# constraint pr_sid primary key(sid)
# );
2.create table 表名(字段 类型 条件约束 , 字段 类型 条件约束 , primary key(作为主键的字段名));
#create table teacher(sid INT,name VARCHAR(10), primary key(sid));
方式三：
- 在定义完表结构后,通过修改表结构方式
-- 在创建完成后，修改表结构
1.alter table 表名 add constraint 主键约束名称 primary key(作为主键的字段名称);
#alter table teacher add constraint pr_sid primary key(sid);
2.alter table 表名 add primary key(作为主键的字段名次);
#alter table teacher add primary key(sid);
删除主键：
alter table 表名 drop primary key;
#alter table student drop primary key;
3.3.2非空约束
-
NOT NULL 约束强制列不接受 NULL 值。
-
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
方式一：
- 定义表结构时,字段名后面+not null
create table 表名(字段 类型 not null 其他约束条件 , 字段 类型 NOT NULL 其他约束条件 ));
#create table teacher(sid INT not null,name VARCHAR(10));
方式二：
- 通过修改表结构的方式
alter table 表名 modify `字段` 类型(长度) 约束;
#alter table teacher modify `sid` INT NOT NULL;
删除非空约束：
alter table 表名 modify `字段` 类型(长度);
#修改表结构时，不添加NOT NULL约束即可删除
3.3.3唯一约束
- 指定列的值 不能重复.
注意:
- UNIQUE 和 PRIMARY KEY 约束均为列提供了唯一性的保证。PRIMARY KEY 是自动定义的 UNIQUE 约束。
- 每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
- UNIQUE 不限制 null 值 出现的次数
方式一：
- 在定义表结构时,作为主键的字段后面+unique
-- 在创建时添加
create table 表名(字段 类型 UNIQUE , 字段 类型 条件约束 );
#create table student(sid INT UNIQUE ,name VARCHAR(10));
方式二：
- 在定义表结构时，已经指定了所有的字段，在最后使用constraint关键字，添加唯一约束
-- 在创建时，添加
1.create table 表名(字段 类型 条件约束 , 字段 类型 条件约束 ,
constraint 唯一约束名称 UNIQUE(添加唯一约束的字段名)
);
#create table teacher(sid INT,name VARCHAR(10),
# constraint un_sid UNIQUE(sid)
# );
2.create table 表名(字段 类型 条件约束 , 字段 类型 条件约束 , UNIQUE(添加唯一约束的字段名));
#create table teacher(sid INT,name VARCHAR(10), UNIQUE(sid));
方式三：
- 在定义完表结构后,通过修改表结构方式
-- 在创建完成后，修改表结构
1.alter table 表名 add constraint 唯一约束名称 UNIQUE(添加唯一约束的字段名);
#alter table teacher add constraint un_sid UNIQUE(sid);
2.alter table 表名 add UNIQUE(添加唯一约束的字段名);
#alter table teacher add UNIQUE(sid);
删除唯一约束：
-
如果指定了唯一约束名称，则必须通过唯一约束名称,来删除唯一约束
alter table 表名 drop index 唯一约束名称 #alter table student drop index un_sid; -
如果没有指定唯一约束名称，则必须通过字段名称删除唯一约束
alter table 表名 drop index 字段名; #alter table student drop index sid;
3.3.4默认约束
- 用于指定字段默认值。
- 当向表中插入记录时，如果没有明确的为字段赋值，则自动赋予默认值。
方式一：
- 在定义表结构的时候添加默认约束
create table 表名(字段 类型 DEFAULT '默认值' , 字段 类型
);
#create table teacher(sid INT DEFAULT '001',name VARCHAR(10),
# );
方式二：
- 修改表结构时添加默认约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 DEFAULT '默认值';
#ALTER TABLE persons MODIFY address VARCHAR(255) DEFAULT '北京市';
设置默认约束后添加数据：
INSERT INTO 表名(字段名1，字段名2，字段名3...[无需写入设置过默认约束的字段]) VALUES(值1，值2，值3...)
#INSERT INTO persons(id,firstname,lastname) VALUES(NULL,'肖','战');
删除默认约束：
ALTER TABLE 表名 MODIFY 字段名 数据类型;
#ALTER TABLE persons MODIFY address VARCHAR(255);
#修改表结构时，不添加默认约束即可删除
4、DQL
4.1 查询表记录：select(查)
1.1简单查询
- 查询表中所有数据
select * from 表名;
#select * from product;
- 查询表中指定字段的数据
select 字段名，字段名... from 表名;
#select pid,pname from product;
- 字段名设置别名
/*注意:
1、as可以省略
2、' ' 单引号可以省略,但是如果别名中有单引号 ' ，就不能省略了
3/表名也是可以起别名的，但是不能写单引号 ' '
*/
1.select 字段名 as '别名' , 字段名 as '别名' ... from 表名;
#select pname as '商品名称' , price as '商品价格' from product;
2.select 字段名 '别名' , 字段名 '别名' ... from 表名;#省略as
#select pname '商品名称' , price '商品价格' from product;
3.select 字段名 别名 , 字段名 别名 ... from 表名;#省略as和单引号
#select pname 商品名称 , price 商品价格 from product;
- 设置表别名
select 字段名 as '别名' , 字段名 as '别名' ... from 表名 as 表别名;#表别名不能加单引号
#select pname as '商品名称' , price as '商品价格' from product as 商品大全;
- 去掉重复数据
select distinct 字段名 from 表名;#查询时
#select distinct price from product;
- 运算查询和多次查询
select 字段名,字段名,字段名和运算表达式 from 表名;
#select pname,price,price+100 from product; 查询两次价格，第二次价格加100
1.2条件查询
| 比较运算符 | < ,>, <=,>=, =, != 或<> | 小于、大于、大于(小于)等于、等于、不等于 |
|---|---|---|
| BETWEEN …AND… | 显示在某一区间的值(含头含尾) | |
| IN(值1,值2…) | 显示在in列表中的值，例：in(100,200) | |
| LIKE ‘张pattern’ | **模糊查询：**Like语句中，% 代表零个或多个任意字符，_ 代表一个字符， 例如：first_name like '_a%'; | |
| IS NULL | 判断是否为空 | |
| 逻辑运行符 | and | 多个条件同时成立 |
| or | 多个条件任一成立 | |
| not | 不成立，例：where not(salary>100); |
- 练习
#查询商品名称为“花花公子”的商品所有信息：
SELECT * FROM product WHERE pname = '花花公子';
#查询价格为800商品
SELECT * FROM product WHERE price = 800;
#查询价格不是800的所有商品
SELECT * FROM product WHERE price != 800;
SELECT * FROM product WHERE price <> 800;
SELECT * FROM product WHERE NOT(price = 800);
#查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;
#查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price >= 200 AND price <=1000;
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
#查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price = 200 OR price = 800;
SELECT * FROM product WHERE price IN (200,800);
#查询含有'霸'字的所有商品
SELECT * FROM product WHERE pname LIKE '%霸%';
#查询以'香'开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';
#查询第二个字为'想'的所有商品
SELECT * FROM product WHERE pname LIKE '_想%';
#商品没有分类的商品
SELECT * FROM product WHERE category_id IS NULL;
#查询有分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL;
SELECT * FROM product WHERE NOT(category_id IS NULL);
1.3排序查询
- 格式:
SELECT * FROM 表名 ORDER BY 排序字段 ASC 或者 DESC;
#ASC 升序 (默认)
#DESC 降序
- 练习:
#使用价格排序(降序)
SELECT * FROM product ORDER BY price DESC;
#在价格排序(降序)的基础上，以分类排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC;
#显示商品的价格(去重复)，并排序(降序)
SELECT DISTINCT price FROM product ORDER BY price DESC;
1.4聚合函数
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。
五个聚合函数：
-
**count：**统计指定列不为NULL的记录行数；
-
#统计表中，category_id字段内容不为NULL的个数 SELECT COUNT(category_id) FROM product;
-
-
**sum：**计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
-
#计算指定的，price字段内容的总和，如果内容不是数值类型，则计算结果为0 SELECT SUM(price) FROM product;
-
-
**max：**计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
-
#查询指定字段的内容中的最大值 SELECT MAX(price) FROM product;
-
-
**min：**计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
-
#查询指定字段的内容中的最小值 SELECT MIN(price) FROM product;
-
-
**avg：**计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
-
#查询字段category_id的内容为'c002'的所有商品的平均价格 SELECT AVG(price) FROM product WHERE category_id = 'c002';
-
练习：
- 查询商品的总条数
#通过通配符查询
SELECT COUNT(*) FROM product;
#通过所有商品的共性字段名查询
SELECT COUNT(pid) FROM product;
- 查询商品的价格总和
SELECT SUM(price) FROM product;
- 查询价格大于200商品的总条数
#使用通配符查询
SELECT COUNT(*) FROM product WHERE price>200;
#使用所有商品的共性字段名查询
SELECT COUNT(pid) FROM product WHERE price>200;
SELECT COUNT(price) FROM product WHERE price>200;
- 查询分类为’c001’的所有商品价格的总和
#查看category_id字段内容为'c001'的所有商品的价格
SELECT price FROM product WHERE category_id='c001';
#查看category_id字段内容为'c001'的所有商品的价格总和
SELECT SUM(price) FROM product WHERE category_id='c001';
- 查询分类为’c002’所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id='c002';
- 查询商品的最大价格和最小价格
#查询两次所有价格
SELECT price,price FROM product;
#对第一次查询的价格取最大值，对第二次查询的价格取最小值。分别添加别名
SELECT MAX(price) AS '最大值',MIN(price) AS '最小值' FROM product;
- 查询pid为1 3 7 商品价格的平均值
#查询pid为1 3 7的所有商品的价格
SELECT price FROM product WHERE pid IN(1,3,7);
#查询pid为1 3 7的所有商品的价格的平均值
SELECT AVG(price) FROM product WHERE pid IN(1,3,7);
- 插入pid为14的商品
INSER INTO product VALUES(14,'炒肝',NULL,NULL);
- 查询pid为1 3 7 14 商品价格的平均值
#pid的值为NULL的不参与运算
SELECT AVG(price) FROM product WHERE pid IN(1,3,7,14);
- 统计指定列(category_id)不为NULL的记录行数
SELECT COUNT(category_id) FROM product WHERE category_id IS NOT NULL;
1.5分组查询
分组查询是指使用group by字句对查询信息进行分组。
- 格式
SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组条件;
注意:
1、分组一般要和聚合函数一起使用
2、作为分组的字段,一般建议显示出来,方便查看数据
3、如果聚合函数作为条件,只能使用having
分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。
-
having与where的区别:
-
having是在分组后对数据进行过滤.
where是在分组前对数据进行过滤
-
having后面可以使用聚合函数(统计函数)
where后面不可以使聚合函数。
-
- 演示
/*统计每门课程的平均分
1.分组: 按照课程分组
2.聚合函数: 平均分 avg
*/
SELECT sname,AVG(score) FROM scores GROUP BY sname;
/*
#统计每门课程的平均分,且只显示平均分>70分的信息
1.分组: 按照课程分组
2.聚合函数: 平均分 avg
3.条件: 平均分>70,需要使用到聚合函数,只能使用having
*/
SELECT sname,AVG(score) FROM scores GROUP BY sname HAVING AVG(score)>70;
练习：
- 统计各个分类商品的个数
#统计product表中category_id字段的各个类型的个数，并且过滤掉为NULL的值
SELECT category_id,COUNT(*) FROM product WHERE category_id IS NOT NULL GROUP BY category_id;
- 统计各个分类商品的个数,且只显示个数大于1的信息
SELECT category_id,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*)>1;
- 统计各个分类商品的平均价格
SELECT category_id,AVG(price) FROM product GROUP BY category_id;
- 统计各个分类商品的平均价格,且只显示平均价格>800的信息
SELECT category_id,AVG(price) FROM product GROUP BY category_id HAVING AVG(price)>800;
#起别名的形式
SELECT category_id,AVG(price) '平均价格' FROM product GROUP BY category_id HAVING 平均价格>800;
4.2 SQL语句执行顺序
写的顺序：select … from… where… group by… having… order by… limit
执行顺序：from… where…group by… having… select … order by… limit
5、数据库备份与导入
- 数据库导出（备份）
mysqldump -u用户名 -p密码 数据库名>生成的脚本文件路径
- 数据库导入
mysql -uroot -p密码 数据库名 < 文件路径
6、多表操作
6.1 表与表之间的关系
- 一对多关系：
- 常见实例：客户和订单，分类和商品，部门和员工.
- 一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键.
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WQnLeILO-1655612112723)(img/08.png)]
- 多对多关系：
- 常见实例：学生和课程、用户和角色
- 多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UzyMgUud-1655612112724)(img/09.png)]
- 一对一关系：(了解)
- 在实际的开发中应用不多.因为一对一可以创建成一张表.
- 两种建表原则：
- 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一unique。
- 外键是主键：主表的主键和从表的主键，形成主外键关系。
6.2 外键约束
现在我们有两张表“分类表”和“商品表”，为了表明商品属于哪个分类，通常情况下，我们将在商品表上添加一列，用于存放分类cid的信息，此列称为：外键
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UUj6bSX1-1655612112724)(img/10.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bTqA7xsO-1655612112725)(img/11.png)]
/*
此时“分类表category”称为：主表
“cid”我们称为主键。“商品表products”称为：从表
category_id称为外键。
我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。
*/
-
外键特点：
- 从表外键的值是对主表主键的引用。
- 从表外键类型，必须与主表主键类型一致。
-
声明外键约束
CREATE TABLE products(
pid INT PRIMARY KEY,
pname VARCHAR(100),
price INT,
cid INT,
FOREIGN KEY (cid) REFERENCES category(cid)
CONSTRAINT fk_p_cid FOREIGN KEY(cid) REFERENCES category(cid)
);
-- 添加外键约束
#1.创建表时，添加外键约束,该方式从表会自动创建外键名。
#如果要删除外键约束，可以用show create table 表名 命令，查看外键名后进行删除
CREATE TABLE products(
字段1 类型 PRIMARY KEY , 字段2 类型 条件约束 , ... ,
FOREIGN KEY (从表外键字段名) REFERENCES 主表名(主表的主键)
);
#1.1 创建表时，添加外键约束，自定义外键名
CREATE TABLE products(
字段1 类型 PRIMARY KEY , 字段2 类型 条件约束 , ... ,
CONSTRAINT 外键名 FOREIGN KEY(从表外键字段) REFERENCES 主表名(主表的主键)
);
#2.修改表结构，添加外键约束
alter table 从表 add [constraint 外键名称] foreign key 从表名(从表外键字段名)
references 主表名(主表的主键);
-- 删除外键约束
[外键名称]用于删除外键约束的，一般建议“_fk”结尾
alter table 从表 drop foreign key 外键名称
- 使用外键目的：
- 保证数据完整性
6.3 一对多操作
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U9alqbtH-1655612112725)(img/12.png)]
- category分类表，为一方，也就是主表，必须提供主键cid
- products商品表，为多方，也就是从表，必须提供外键category_id
#创建分类表
create table category(
cid varchar(32) PRIMARY KEY ,
cname varchar(100) -- 分类名称
);
# 创建商品表
CREATE TABLE `products` (
pid varchar(32) PRIMARY KEY ,
name VARCHAR(40) ,
price INT ,
category_id VARCHAR(32)
);
#分类表为主表，商品表为从表，在从表添加外键约束
alter table products add constraint product_fk foreign key products(category_id) references category (cid);
6.4多对多
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JzedpzFl-1655612112726)(img/13.png)]
- 商品和订单多对多关系，将拆分成两个一对多。
- products商品表，为其中一个一对多的主表，需要提供主键pid
- orders 订单表，为另一个一对多的主表，需要提供主键oid
- pro_ord中间表，为另外添加的第三张表，需要提供两个外键oid和pid
#创建中间表
create table pro_ord(pid INT , oid INT );
#在中间表pid字段上添加外键约束，与products商品表pid字段关联
alter table pro_ord add constraint fk_pr foreign key pro_ord(pid) references products(pid);
#在中间表oid字段上添加外键约束，与orders订单表oid字段关联
alter table pro_ord add constraint fk_or foreign key pro_ord(oid) references orders(oid);
结构图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yzcP3J6T-1655612112726)(img/55.png)]
6.5多表查询
创建表：
#创建分类表
CREATE TABLE category (
cid VARCHAR(32) PRIMARY KEY ,
cname VARCHAR(50)
);
#创建商品表
CREATE TABLE products(
pid VARCHAR(32) PRIMARY KEY ,
pname VARCHAR(50),
price INT,
flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
cid VARCHAR(32),
CONSTRAINT products_fk FOREIGN KEY products(cid) REFERENCES category (cid)#添加外键约束
);
添加表数据：
#分类表添加信息
INSERT INTO category VALUES('c001','家电');
INSERT INTO category VALUES('c002','服饰');
INSERT INTO category VALUES('c003','化妆品');
#商品表添加信息
INSERT INTO products VALUES('p001','联想',5000,'1','c001');
INSERT INTO products VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products VALUES('p003','雷神',5000,'1','c001');
INSERT INTO products VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products VALUES('p007','劲霸',2000,'1','c002');
INSERT INTO products VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products VALUES('p009','相宜草',200,'1','c003');
6.5.1交叉连接查询
(基本不会使用-得到的是两个表的乘积) [了解]
- 语法：
select * from A,B.....;
6.5.2内连接查询
- 隐式内连接：
select * from 表名A,表名B where 条件;
#1.查询所有商品的记录,要求显示商品及所属的分类信息
SELECT * FROM category,products WHERE category.cid=products.cid;
#起别名
SELECT c.*,p.* FROM category c,products p WHERE c.cid=p.cid;
#起别名，显示指定字段
SELECT p.pid,p.pname,p.price,p.flag,c.cname FROM category c,products p WHERE c.cid=p.cid;
#2.只查询cname字段为'化妆品'的记录,要求显示商品及所属的分类信息
SELECT c.*,p.* FROM category c,products p WHERE c.cid=p.cid AND c.cname='化妆品';
#3.查询哪些分类的商品已经上架
SELECT * FROM category c,products p WHERE p.cid=c.cid AND p.flag=1;
#4.查询每种分类商品的个数，通过category表的cid字段分组组
SELECT c.cid,c.cname,COUNT(p.pname) FROM category c , products p
WHERE c.cid=p.cid GROUP BY c.cid;
- 显示内连接：
select * from 表名A inner join 表名B on 条件;- 使用的关键字 inner join – inner可以省略
-- 显示内连接完成以上操作
#1.查询所有商品的记录,要求显示商品及所属的分类信息
SELECT * FROM category INNER JOIN products ON category.cid=products.cid;
#起别名
SELECT * FROM category c INNER JOIN products p ON c.cid=p.cid;
#inner可以省略，on可以换成where
SELECT * FROM category c JOIN products p WHERE c.cid=p.cid;
#2.只查询所有'化妆品'的记录,要求显示商品及所属的分类信息
SELECT * FROM category c INNER JOIN products p ON p.cid=c.cid AND c.cname='化妆品';
#可替换为 ON ... WHERE
#可替换为 WHERE ... AND
#3.查询哪些分类的商品已经上架
SELECT * FROM category c INNER JOIN products p ON p.cid=c.cid AND p.flag=1;
6.5.3外连接
OUTER关键字可以省略
1.左外连接: select 字段1,字段2 ... from 表A left outer join 表B on 条件
#查询所有商品的记录,要求显示商品及所属的分类信息
SELECT * FROM category c LEFT OUTER JOIN products p ON p.cid=c.cid;
2.右外连接: select 字段1,字段2 ... from 表A right outer join 表B on 条件
#查询所有商品的记录,要求显示商品及所属的分类信息
SELECT * FROM category c RIGHT OUTER JOIN products p ON p.cid=c.cid;
3.注意：
-
左外连接查询
以left outer join 左侧的表为标准，左表中所有的记录都会显示，无论右表中是否也有与之对应的内容
-
右外连接查询
以right outer join 右侧的表为标准，右表中所有的记录都会显示，无论左表中是否也有与之对应的内容
-
隐式内连接:
连接查询的两个表，必须保证两表的相关联的字段，都有与之对应的内容时才会显示
4.案例演示：
#主表内容，新增一个‘汽车’分类，与从表无关
+------+--------+
| cid | cname |
+------+--------+
| c001 | 家电 |
| c002 | 服饰 |
| c003 | 化妆品 |
| c004 | 汽车 |
+------+--------+
#使用内连接查询两表，无法显示主表与从表无关联的内容
SELECT * FROM category c,products p WHERE p.cid=c.cid;
+------+--------+------+------------+-------+------+------+
| cid | cname | pid | pname | price | flag | cid |
+------+--------+------+------------+-------+------+------+
| c001 | 家电 | p001 | 联想 | 5000 | 1 | c001 |
| c001 | 家电 | p002 | 海尔 | 3000 | 0 | c001 |
| c001 | 家电 | p003 | 雷神 | 5000 | 1 | c001 |
| c002 | 服饰 | p004 | JACK JONES | 800 | 1 | c002 |
| c002 | 服饰 | p005 | 真维斯 | 200 | 0 | c002 |
| c002 | 服饰 | p006 | 花花公子 | 440 | 1 | c002 |
| c002 | 服饰 | p007 | 劲霸 | 2000 | 1 | c002 |
| c003 | 化妆品 | p008 | 香奈儿 | 800 | 1 | c003 |
| c003 | 化妆品 | p009 | 相宜草 | 200 | 1 | c003 |
+------+--------+------+------------+-------+------+------+
#使用左外连接查询两表，left outer join 左侧的表为标准，左表中所有的记录都会显示
SELECT * FROM category c LEFT OUTER JOIN products p ON p.cid=c.cid;
+------+--------+------+------------+-------+------+------+
| cid | cname | pid | pname | price | flag | cid |
+------+--------+------+------------+-------+------+------+
| c001 | 家电 | p001 | 联想 | 5000 | 1 | c001 |
| c001 | 家电 | p002 | 海尔 | 3000 | 0 | c001 |
| c001 | 家电 | p003 | 雷神 | 5000 | 1 | c001 |
| c002 | 服饰 | p004 | JACK JONES | 800 | 1 | c002 |
| c002 | 服饰 | p005 | 真维斯 | 200 | 0 | c002 |
| c002 | 服饰 | p006 | 花花公子 | 440 | 1 | c002 |
| c002 | 服饰 | p007 | 劲霸 | 2000 | 1 | c002 |
| c003 | 化妆品 | p008 | 香奈儿 | 800 | 1 | c003 |
| c003 | 化妆品 | p009 | 相宜草 | 200 | 1 | c003 |
| c004 | 汽车 | NULL | NULL | NULL | NULL | NULL |
+------+--------+------+------------+-------+------+------+
-- 右外连接与左外连接相反，暂不做演示
- 左外链接，内连接，右外连接 的区别
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-beCI7EEE-1655612112726)(img/88.png)]
6.6子查询
子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。
语法：select ....查询字段 ... from ... 表.. where ... 查询条件
6.6.1查询结果作为条件
-- 一.查询“化妆品”分类商品详情
#1.先在分类表中查询“化妆品”分类的字段cid的值
SELECT `cid` FROM category WHERE cname='化妆品';
#2.用查询到的cid作为条件，在商品表products中查询所有的化妆品分类商品详情
SELECT * FROM products WHERE `cid`='c003';
# 使用子查询查询代替以上步骤，把在category表中查询cid的查询语句作为条件
SELECT * FROM products WHERE cid=(SELECT cid FROM category WHERE cname='化妆品');
-- 二.查询“化妆品”分类上架商品详情
#1.先在分类表中查询“化妆品”分类的字段cid的值
SELECT `cid` FROM category WHERE cname='化妆品';
#2.用查询到的cid和flag=1作为条件，在商品表products中查询所有的化妆品的上架信息（flag=1为上架）
SELECT * FROM products WHERE cid='c003' AND flag=1;
#使用子查询查询代替以上步骤，把在category表中查询cid的查询语句作为条件
SELECT * FROM products p WHERE p.flag='1'
AND p.cid=(SELECT cid FROM category WHERE cname='化妆品');
-- 三.查询“化妆品”或者“家电”两个分类上架商品详情
#1.查询“化妆品”或者“家电”两个分类的cid
SELECT cid FROM category WHERE cname='化妆品' OR cname='家电';
#2.用查询到的cid和flag=1作为条件，查询“化妆品”或者“家电”两个分类上架商品详情
SELECT * FROM products WHERE cid IN('c003','c001') AND flag='1';
#使用子查询代替以上步骤
SELECT * FROM products WHERE
cid IN(SELECT cid FROM category WHERE cname='化妆品' OR cname='家电') AND flag=1;
6.6.2查询结果作为表
-- 一.查询“化妆品”分类商品详情
#1.先在分类表中查询“化妆品”分类的字段cid的值
SELECT * FROM category WHERE cname='化妆品';
#将步骤1的查询结果作为表，与商品表products表内连接
SELECT cc.cname,p.pname FROM products p,(SELECT c.* FROM category c WHERE c.cname='化妆品') cc WHERE p.cid=cc.cid;
-- 二.查询“化妆品”和“家电”两个分类上架商品详情
#1.先在分类表中查询“化妆品”和“家电”分类的cid值
SELECT * FROM category WHERE cname IN('家电','化妆品')
#将步骤1的查询结果作为表，与商品products表内连接
SELECT cc.cname,p.pname,p.flag FROM products p,(SELECT * FROM category WHERE cname IN('家电','化妆品')) cc WHERE p.cid=cc.cid AND flag=1;
7、索引
7.1 优势和劣势
优势：
-
类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
-
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
劣势
-
实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
-
虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
7.2 索引结构
索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：
- BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
- HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
- R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
- Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。
MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持
| 索引 | InnoDB引擎 | MyISAM引擎 | Memory引擎 |
|---|---|---|---|
| BTREE索引 | 支持 | 支持 | 支持 |
| HASH 索引 | 不支持 | 不支持 | 支持 |
| R-tree 索引 | 不支持 | 支持 | 不支持 |
| Full-text | 5.6版本之后支持 | 支持 | 不支持 |
我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。
7.3 索引分类
-
单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引
-
唯一索引 ：索引列的值必须唯一，但允许有空值
-
复合索引 ：即一个索引包含多个列
7.4索引语法
7.4.1 创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON 表名(字段名...);
7.4.2 查看索引
SHOW INDEX FROM 表名;
7.4.3 删除索引
DROP INDEX 索引名 ON 表名;
7.4.4 ALTER命令
alter table 表名 add primary key(字段名);
#该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
2). alter table 表名 add unique 索引名(字段名));
#这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
3). alter table 表名 add index 索引名(字段名));
#添加普通索引， 索引值可以出现多次。
二、JDBC
1、JDBC概述
JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API。JDBC是Java访问数据库的标准规范，可以为不同的关系型数据库提供统一访问，它由一组用Java语言编写的接口和类组成。
JDBC需要连接驱动，驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定，设备提供商为设备提供驱动软件，通过软件可以与该设备进行通信。
今天我们使用的是mysql的驱动mysql-connector-java-5.1.37-bin.jar
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rDjsv211-1655612112726)(img/19.png)]
JDBC规范（掌握四个核心对象）：
- DriverManager:用于注册驱动
- Connection: 表示与数据库创建的连接
- Statement: 操作数据库sql语句的对象
- ResultSet: 结果集或一张虚拟表
2、JDBC原理
Java提供访问数据库规范称为JDBC，而生产厂商提供规范的实现类称为驱动。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hs0FWGmI-1655612112727)(img/20.png)]
JDBC是接口，驱动是接口的实现，没有驱动将无法完成数据库连接，从而不能操作数据库！每个数据库厂商都需要提供自己的驱动，用来连接自己公司的数据库，也就是说驱动一般都由数据库生成厂商提供。
3、步骤介绍
/*
1. 注册驱动.
告诉JVM,使用的是哪个数据库
2. 获得连接.
java是面向对象的编程语言,任何事物都可以定义类,创建对象
就是连接数据库,获取到数据库的连接对象,Connection对象
3. 获得执行sql语句的对象
Connection连接对象获取执行sql语句的Statement对象
4. 执行sql语句，并返回结果
Statement对象,执行增删改,返回结果,返回int数字,代表的是影响的行数
Statement对象,执行查询,返回结果集ResultSet对象
5. 处理结果
增删改,返回结果,返回int数字,代表的是影响的行数,不用处理
执行查询,返回结果集ResultSet对象,需要处理,遍历或者封装对象
6. 释放资源.
Connection对象
Statement对象
ResultSet对象
以上3个对象,都有close方法
*/
4、JDBC连接
4.1注册驱动
/*
方式一：（不使用）
new Driver() 创建对象,需要加载.class文件到内存,
而Driver类中有个静态代码块,加载时,就被执行。静态代码块中,也有注册驱动的动作
导致: 注册了两遍
*/
DriverManager.registerDriver(new Driver());
//方式二: （不使用）类名是写死了 不用
new Driver();
/*
方式三:
通过反射获取类的Class对象,
首先检测该类是否被加载到内存,没有加载,就会加载
目前第一次使用,类就会被加载,静态代码块会被执行,
内部就完成了驱动的注册
好处: 参数是String类型的全类名,可以写到配置文件中
*/
Class.forName("com.mysql.jdbc.Driver");
4.2获取连接对象
/*
java.sql.DriverManager类: 管理一组 JDBC 驱动程序的基本服务。
静态方法:
public static Connection getConnection(String url, String user, String password)
试图建立到给定数据库 URL 的连接。
参数:
String url: 要连接的数据库地址 固定写法 jdbc:mysql://localhost:3306/数据库名称
String user: 用户名
String password: 密码
返回值类型:
java.sql.Connection接口: 代表到数据库的连接对象,方法内部必然返回该接口的实现类对象
*/
//定义数据库连接地址，用户名，密码
String url="jdbc:mysql://localhost:3306/myqqq";
String userName="root";
String passWrod="root";
// 调用方法，获取数据库连接对象
Connection connection = DriverManager.getConnection(url, userName, passWrod);
4.3获取SQL语句对象
/*
java.sql.Connection接口: 我们拿到的是实现类对象
成员方法:
public Statement createStatement() : 创建一个 Statement 对象,用来执行sql语句
返回值类型:
java.sql.Statement接口: 必然返回实现类对象,用来执行sql语句的,返回结果
成员方法:
public int executeUpdate(String sql) : 执行给定 SQL 语句，只能执行增,删,改
参数:
String sql: sql语句
返回值类型:
int: 代表影响的行数
*/
//获取sel语句执行对象
Statement statement = connection.createStatement();
4.4执行（增删改）数据
public class JDBCDemo1 {
//添加数据库表内容（增）
@Test
public void addOne() throws Exception {
// 1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取数据库连接Connection对象
String url="jdbc:mysql://localhost:3306/myqqq";
String userName="root";
String passWrod="root";
Connection connection = DriverManager.getConnection(url, userName, passWrod);
// 3.获取执行sql语句的statement对象
Statement statement = connection.createStatement();
// 4.Statement对象调用executeUpdate方法执行sql语句，获取结果
//定义SQL语句
String sql="insert into scores values(1,100,'柳岩') ";
int result=statement.executeUpdate(sql);
if (result>0){
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
//关闭资源
statement.close();
connection.close();
}
//删除数据库表数据（删）
@Test
public void deleteOne () throws Exception{
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取数据库连接对象Connection
Connection connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/myqqq", "root", "root");
//3.获取sql语句执行对象statement
Statement statement = connection.createStatement();
//4.statement对象调用executeUpdate方法执行sql语句
//定义sql语句
String sql="delete from scores where `sid`=5";
int result = statement.executeUpdate(sql);
if (result>0){
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
}
//修改数据库表内容（改）
@Test
public void updateOne() throws Exception{
// 1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接Connection对象
//定义数据库连接地址
String url="jdbc:mysql://localhost:3306/myqqq";
//定义用户名
String userName="root";
//定义密码
String passWord="root";
Connection connection = DriverManager.getConnection(url, userName, passWord);
// 3.获取执行SQL语句的Statement对象
Statement statement = connection.createStatement();
// 4.Statement对象调用executeUpdate方法执行sql语句，获取结果
//定义sql语句
String sql="update scores set score=60 where sid=1";
int result = statement.executeUpdate(sql);
if (result>0){
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
}
4.5查询数据（查）
public class JDBCDemo2 {
@Test
//查询一条记录
public void selectOne() throws ClassNotFoundException, SQLException {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取数据库连接Connection对象
String url="jdbc:mysql://localhost:3306/myqqq";
String userName="root";
String passWrod="root";
Connection connection = DriverManager.getConnection(url, userName, passWrod);
//3.获取执行sql语句的statement对象
Statement statement = connection.createStatement();
//4.Statement对象调用executeUpdate方法执行sql语句，获取结果
//定义SQL查询语句
String sql="select * from scores where sid=1;";
ResultSet resultSet = statement.executeQuery(sql);//executeQuery方法返回的是结果集
if (resultSet.next()){
int sid = resultSet.getInt(1);//根据列的编号获取查询到的值
int score = resultSet.getInt(2);
String sname = resultSet.getString("sname");//根据列名获取
System.out.println("编号："+sid+"姓名："+sname+"分数："+score);
}else {
System.out.println("没有该学生！");
}
//关闭资源
connection.close();//关闭连接对象资源
statement.close();//关闭sql语句执行对象资源
resultSet.close();//关闭结果集对象资源
}
@Test
//查询多条记录
public void selectAll() throws Exception{
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取数据库连接对象Connection
Connection connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/myqqq", "root", "root");
//3.获取执行sql语句的statement
Statement statement = connection.createStatement();
//4.执行sql语句
//定义sql语句
String sql="select * from scores";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
Object sid = resultSet.getObject(1);
Object score = resultSet.getObject("score");
Object sname = resultSet.getObject(3);
System.out.println("编号："+sid+" 姓名："+sname+" 分数："+score);
}
//关闭资源
connection.close();//关闭连接对象资源
statement.close();//关闭sql语句执行对象资源
resultSet.close();//关闭结果集对象资源
}
}
5、自定义JDBC工具类
import java.sql.*;
public class JDBCUtils {
private JDBCUtils() {
}
private static String driver = "com.mysql.jdbc.Driver";
private static String userName = "root";
private static String passWord = "root";
private static String url = "jdbc:mysql://localhost:3306/myqqq";
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new RuntimeException("注册驱动失败");
}
}
/**
* @return 数据库连接对象Connection
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
Connection connection = DriverManager.getConnection(url, userName, passWord);
return connection;
}
/**
* 关闭资源
* @param connection
* @param statement
* @param resultSet
*/
public static void closeResource(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
}
}
}
}
- 使用自定义的JDBC工具类
import utils.JDBCUtils;
import java.sql.*;
public class UtilsJDBCDemo {
public static void main(String[] args) throws SQLException {
Connection connection = JDBCUtils.getConnection();
Statement statement = connection.createStatement();
String sql="select * from scores";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
Object sid = resultSet.getObject(1);
Object score = resultSet.getObject(2);
Object sname = resultSet.getObject(3);
System.out.println("编号："+sid+" 姓名："+sname+" 分数："+score);
}
JDBCUtils.closeResource(connection,statement,resultSet);
}
}
6、异常处理完整格式
import java.sql.*;
public class TryCatchDemo {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取数据库连接对象
connection =
DriverManager.getConnection("jdbc:mysql://localhost:3306/myqqq", "root", "root");
//获取sql语句操作对象
statement = connection.createStatement();
//定义并执行sql语句
String sql = "select * from scores";
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
Object sid = resultSet.getObject(1);
Object score = resultSet.getObject(2);
Object sname = resultSet.getObject(3);
System.out.println("编号：" + sid + " 姓名：" + sname + " 分数：" + score);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
7、将查询到的数据封装成对象和集合
/*
使用JDBCUtil工具类，将查询到的数据封装成对象和集合
*/
public class Demo1 {
public static void main(String[] args) throws SQLException {
//查询一条信息
Scanner sc=new Scanner(System.in);
System.out.println("请您输入您要查询用户的uid：");
String uid = sc.next();
User user = queryOneUserById(uid);
if (user!=null){
System.out.println("您查询的用户为："+user);
}else {
System.out.println("查询的用户不存在！");
}
System.out.println("----------------------------");
//查询所有信息
List<User> list = queryAllUser();
for (User user1 : list) {
System.out.println(user1);
}
}
//根据uid查询一个User对象
public static User queryOneUserById(String uid) throws SQLException {
//1.获取Connection连接对象
Connection connection = JDBCUtil.getConnection();
//获取执行sql语句的statement对象
Statement statement = connection.createStatement();
//执行sql语句操作
String sql="select * from users where uid='"+uid+"'";
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next()){
uid = resultSet.getString("uid");
String uname = resultSet.getString("uname");
String upass = resultSet.getString("upass");
User user=new User(uid,uname,upass);
return user;
}
//释放资源
JDBCUtil.closeResource(connection,statement,resultSet);
return null;
}
//查询所有User对象，返回存储User对象的List集合
public static List<User> queryAllUser() throws SQLException {
//1.获取Connection连接对象
Connection connection = JDBCUtil.getConnection();
//获取执行sql语句的statement对象
Statement statement = connection.createStatement();
//执行sql语句操作
String sql="select * from users ";
ResultSet resultSet = statement.executeQuery(sql);
//创建list集合存储User对象
List<User> list=new ArrayList<>();
while (resultSet.next()){
String uid = resultSet.getString("uid");
String uname = resultSet.getString("uname");
String upass = resultSet.getString("upass");
User user=new User(uid,uname,upass);
//把查询出的user对象添加到list集合中
list.add(user);
}
//释放资源
JDBCUtil.closeResource(connection,statement,resultSet);
//返回list集合
return list;
}
}
三、SQL注入和预处理
1、sql注入
import java.sql.*;
import java.util.Scanner;
/*
键盘输入用户名和密码实现登录
*/
public class JDBCLogin {
public static void main(String[] args) throws SQLException {
Scanner sc=new Scanner(System.in);
System.out.println("请输入用户名：");
String uname = sc.nextLine();
System.out.println("请输入密码：");
String upass = sc.nextLine();
Connection connection = JDBCUtil.getConnection();
Statement statement = connection.createStatement();
String sql="select * from users where uname='"+uname+"' and upass='"+upass+"'";
System.out.println(sql);
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
}
}
- 正确执行的情况：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-t2sYp8eV-1655612112727)(img/15.png)]
- sql注入：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WKqCIgYH-1655612112728)(img/16.png)]
- 此时，上述查询语句时永远可以查询出结果的。那么用户就直接登录成功了，显然我们不希望看到这样的结果，这 便是SQL注入问题。 为此，我们使用PreparedStatement来解决对应的问题。
2、预处理机制
/*
JDBC模拟用户登录
注意:
SQL注入攻击:
使用+拼接sql语句,存在安全隐患,可能会在sql语句中拼接一个or后面写永远成立的条件,
使得sql语句前面的所有条件都失效了
解决方案: 使用PreparedStatement,表示预编译的 SQL 语句的对象
java.sql.Connection接口,成员方法
public PreparedStatement prepareStatement(String sql)
创建一个 PreparedStatement 对象来将参数化的 SQL 语句发送到数据库。
参数:
String sql: sql语句,参数使用?代替
返回值:
java.sql.PreparedStatement接口: 方法内部必然返回实现类对象
成员方法:
public void setString(int index, String x) : 用来给sql中String参数赋值的
参数:
int index: 第几个?,从1开始
String x: 给?赋值的具体数据
public void setObject(int index, String x): 用来给sql中Object参数赋值的
参数:
int index: 第几个?,从1开始
String x: 给?赋值的具体数据
public ResultSet executeQuery() :
执行查询,返回结果集,不用传递sql语句,创建PreparedStatement对象时已经指定了sql语句
public int executeUpdate() :
执行增删改,返回影响的行数,不用传递sql语句,创建PreparedStatement对象时已经指定了sql语句
*/
public class JDBCLogin2 {
public static void main(String[] args) throws Exception {
//1.获取页面数据
Scanner sc=new Scanner(System.in);
System.out.println("请输入用户名：");
String uname = sc.nextLine();
System.out.println("请输入密码：");
String upass = sc.nextLine();
//2.获取数据库连接Connection对象
Connection connection = JDBCUtil.getConnection();
//3.定义sql语句,参数使用?代替
String sql="select * from users where uname=? and upass=?";
//4.Connection对象获取执行sql语句的PreparedStatement对象,传递sql语句
PreparedStatement preparedStatement = connection.prepareStatement(sql);
System.out.println(preparedStatement);
//5.PreparedStatement对象调用方法,给sql语句中的?赋值
preparedStatement.setObject(1,uname);
preparedStatement.setObject(2,upass);
//6.PreparedStatement对象执行查询,获取结果
ResultSet resultSet = preparedStatement.executeQuery();
//7.处理结果,给页面响应信息回去
if (resultSet.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
//8.关闭资源
JDBCUtil.closeResource(connection,preparedStatement,resultSet);
}
}
- 有效防止sql注入
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2n95eiXA-1655612112728)(img/17.png)]
2.1实现增删改
public class JDBCInsert {
@Test
public void insertOne() throws Exception {
//1.获取数据库连接Connection对象
Connection connection = JDBCUtil.getConnection();
//2.定义sql语句,参数使用?代替
String sql="insert into users values(?,?,?)";
//3.Connection对象获取执行sql语句的PreparedStatement对象,传递sql语句
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//4.PreparedStatement对象调用方法,给sql语句中的?赋值
preparedStatement.setObject(1,"u006");
preparedStatement.setObject(2,"yzyookun");
preparedStatement.setObject(3,"552499meme");
//5.PreparedStatement对象执行查询,获取结果
int result = preparedStatement.executeUpdate();
//6.处理结果,给页面响应信息回去
if (result>0){
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
//7.关闭资源
JDBCUtil.closeResource(connection,preparedStatement,null);
}
@Test
public void deleteOne() throws Exception{
//1.获取数据库连接Connection对象
Connection connection = JDBCUtil.getConnection();
//2.定义sql语句,参数使用?代替
String sql="delete from users where uid=?";
//3.Connection对象获取执行sql语句的PreparedStatement对象,传递sql语句
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//4.PreparedStatement对象调用方法,给sql语句中的?赋值
preparedStatement.setObject(1,"u006");
//5.PreparedStatement对象执行查询,获取结果
int result = preparedStatement.executeUpdate();
//6.处理结果,给页面响应信息回去
if (result>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
JDBCUtil.closeResource(connection,preparedStatement,null);
//7.关闭资源
}
@Test
public void updateOne() throws Exception{
Connection connection = JDBCUtil.getConnection();
String sql="update users set uname=? , upass=? where uid=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
System.out.println(preparedStatement);
preparedStatement.setObject(1,"baoqiang");
preparedStatement.setObject(2,"majinlian");
preparedStatement.setObject(3,"u005");
int result = preparedStatement.executeUpdate();
if (result>0){
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
JDBCUtil.closeResource(connection,preparedStatement,null);
}
}
3、连接池
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bx3QLssx-1655612112728)(img/21.png)]
3.1 C3P0基本使用
/*
使用JDBC进行增删改查,需要频繁的获取和销毁连接Connection对象,消耗系统资源
连接池: 用来管理连接对象
Oracle公司,制定一套连接池的规范/规则/标准
javax.sql.DataSource接口: 连接池/数据源
抽象方法:
public abstract Connection getConnection(): 获取连接Connection对象
所以只要是连接池,必然实现DataSource接口,覆盖重写getConnection方法,获取连接对象
C3P0既然是连接池,就必然实现DataSource接口,覆盖重写getConnection方法，也就是肯定有一个类实现DataSource接口,覆盖重写getConnection方法
通过查看API,发现有个类ComboPooledDataSource,实现了DataSource接口,也就是创建ComboPooledDataSource类对象,就是在创建连接池对象
连接池的使用步骤:
1.创建连接池对象
2.连接池对象,调用getConnection方法,获取连接Connection对象
*/
import com.mchange.v2.c3p0.*;
public class Demo1 {
public static void main(String[] args) throws Exception {
//创建C3P0连接池的对象
ComboPooledDataSource cpds = new ComboPooledDataSource();
//设置驱动类名
cpds.setDriverClass( "com.mysql.jdbc.Driver" );
//设置数据库连接地址
cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/myqqq" );
//设置数据库用户名
cpds.setUser("root");
//设置数据库密码
cpds.setPassword("root");
//连接池对象调用getConnection方法，回去Connection对象
Connection connection = cpds.getConnection();
}
}
3.2 C3P0配置文件使用
3.2.1 c3p0.properties
#将配置文件放在src目录下，创建C3P0连接池的对象时，会自动读取src根目录下的c3p0.properties配置文件
c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/myqqq
c3p0.user=root
c3p0.password=root
- 使用
public class Demo2 {
public static void main(String[] args) throws Exception {
//创建C3P0连接池的对象
//创建对象时，会自动读取src根目录下的c3p0.properties配置文件
ComboPooledDataSource cpds = new ComboPooledDataSource();
//连接池对象调用getConnection方法，回去Connection对象
Connection connection = cpds.getConnection();
}
}
3.2.2 c3p0-config.xml
<c3p0-config>
<!-- 使用默认的配置读取连接池对象 -->
<default-config>
<!-- 连接参数 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/myqqq</property>
<property name="user">root</property>
<property name="password">root</property>
<!-- 连接池参数 -->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">10</property>
<property name="checkoutTimeout">2000</property>
<property name="maxIdleTime">1000</property>
</default-config>
</c3p0-config>
- 配置文件参数信息
| 参数 | 说明 |
|---|---|
| initialPoolSize | 初始连接数 |
| maxPoolSize | 最大连接数 |
| checkoutTimeout | 最大等待时间 |
| maxIdleTime | 最大空闲回收时间 |
- **初始连接数 ：**刚创建好连接池的时候准备的连接数量
- **最大连接数 ：**连接池中最多可以放多少个连接
- **最大等待时间 ：**连接池中没有连接时最长等待时间
- **最大空闲回收时间 ：**连接池中的空闲连接多久没有使用就会回收
3.2.3 优先顺序
-
1、以编程方式设置配置值。
-
2、配置值取自c3p0-config.xml文件的缺省配置。
-
3、c3p0.properties文件中指定的配置值
3.3 C3P0工具类实现查询
public class Demo3 {
public static void main(String[] args) throws SQLException {
//获取Connection连接对象
Connection connection = C3P0Util.getConnection();
//定义sql语句
String sql="select * from users where uid=?";
//Connection对象调用preparedStatement方法获取sql语句执行对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//preparedStatement对象调用方法给？赋值
preparedStatement.setObject(1,"u005");
//preparedStatement对象调用方法执行sql语句
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
Object uid = resultSet.getObject(1);
Object uname = resultSet.getObject(2);
Object upass = resultSet.getObject(3);
System.out.println("编号："+uid+" 用户名："+uname+" 密码："+upass);
}else {
System.out.println("没有查询到结果");
}
C3P0Util.closeResource(connection,preparedStatement,resultSet);
}
}
4、阿里Druid连接池
4.1配置文件
druid.properties，配置文件可随意命名，最好使用该名称。
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day19
username=root
password=root
4.2 DruidUtils工具类
public class DruidUtils {
public static DataSource dataSource;
static {
try {
//手动读取配置文件，将InputStream对象传给DruidDataSourceFactory类的createDataSource方法
InputStream is = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
//创建properties对象存储配置文件信息
Properties properties = new Properties();
//加载配置信息
properties.load(is);
//获取DataSource对象
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//定义方法获取连接对象
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
}
5、DBUtil简化工具包
DBUtils三个核心功能介绍
- QueryRunner中提供对sql语句操作的API.
- ResultSetHandler接口，用于定义select操作后，怎样封装结果集
- DbUtils类，它就是一个工具类,定义了关闭资源与事务处理的方法
5.1构造方法
使用DBUtils工具类: 目的简化JDBC的开发步骤,不关心Connection连接对象如何获取
-
核心类QueryRunner
1、空参构造:
public QueryRunner(): 可以直接创建对象
但是没有传递Connection连接对象。所以该对象调用方法，执行增删改查时，必须传入参数Connection连接对象
2、有参构造:
public QueryRunner(DataSource ds) : 创建对象，必须传递连接池对象
创建QueryRunner对象时，传递了连接池对象，它就可以从连接池中获取连接对象，用完后，返还连接池。所以该对象调用方法，执行增删改查时，不用传Connection连接对象
5.2空参构造实现（增删改）
/*
执行增删改的方法:
public int update(Connection conn, String sql, Object... params)
只能执行增删改,获取int结果,代表影响的行数
参数:
1.Connection conn: 连接对象
2.String sql: sql语句,参数用?代替
3.Object... params: 可变参数,传递参数列表,数组,不传参 作用: 给sql中?进行赋值的
步骤:
1.空参创建QueryRunner对象
2.QueryRunner对象调用update方法,传递连接对象,sql语句,以及给?赋值的具体的数据,执行增删改,获取结果
3.处理结果
*/
public class DBUtilDemo1 {
//添加一条数据
@Test
public void addOne() throws SQLException {
//1.空参创建QueryRunner对象
QueryRunner queryRunner=new QueryRunner();
//定义sql语句
String sql="insert into users values(?,?,?)";
//获取连接对象
Connection connection = C3P0Util.getConnection();
//2.QueryRunner对象调用update方法,传递连接对象,sql语句,以及给?赋值的具体的数据,执行增删改,获取结果
int result = queryRunner.update(connection, sql, "u006", "wanglei", "ww112211");
if (result>0){
System.out.println("数据添加成功");
}else {
System.out.println("数据添加失败");
}
C3P0Util.closeResource(connection,null,null);
}
//删除一条数据
@Test
public void deleteOne() throws SQLException {
//创建QueryRunner对象
QueryRunner queryRunner = new QueryRunner();
//定义sql语句
String sql="delete from users where uname=?";
//获取Connection连接对象
Connection connection = C3P0Util.getConnection();
//2.QueryRunner对象调用update方法,传递连接对象,sql语句,以及给?赋值的具体的数据,执行增删改,获取结果
int result = queryRunner.update(connection, sql, "123");
if (result>0){
System.out.println("删除一条数据成功");
}else {
System.out.println("删除一条数据失败");
}
}
//修改一条数据
@Test
public void updateOne() throws SQLException {
//1.空参创建QueryRunner对象
QueryRunner queryRunner = new QueryRunner();
//2.QueryRunner对象调用update方法,传递连接对象,sql语句,以及给?赋值的具体的数据,执行增删改,获取结果
//获取连接对象，定义sql语句全部省略简写
int result = queryRunner.update(C3P0Util.getConnection(), "update users set upass=? where uname=?", "aabbcc", "baoqiang");
//3.处理结果
if (result>0){
System.out.println("修改一条数据成功");
}else {
System.out.println("修改一条记录失败");
}
}
}
5.3有参构造实现（增删改）
/*
有参构造:
public QueryRunner(DataSource ds) : 创建对象,必须传递连接池对象
既然创建QueryRunner对象时,传递了连接池对象,它就可以从连接池中获取连接,用完后,返还连接池
所以该对象调用方法,执行增删改查时,不用传Connection连接对象
*/
//1.创建QueryRunner对象时，传入连接池对象
//2.QueryRunner对象调用update方法,传递连接对象,sql语句,以及给?赋值的具体的数据,执行增删改,获取结果
//3.处理结果
public class DBUtilDemo2 {
//增加一条记录
@Test
public void addOne() throws SQLException {
//1.创建QueryRunner对象时，传入连接池对象
QueryRunner queryRunner=new QueryRunner(C3P0Util.getDataSource());
//2.QueryRunner对象调用update方法,传递连接对象,sql语句,以及给?赋值的具体的数据,执行增删改,获取结果
int result = queryRunner.update("insert into users values(?,?,?)", "u007", "yzyookun", "1230.0");
//3.处理结果
if (result>0){
System.out.println("增加一条记录成功");
}else {
System.out.println("增加一条记录失败");
}
}
//删除一条数据
@Test
public void deleteOne() throws SQLException {
//1.创建QueryRunner对象时，传入连接池对象
QueryRunner queryRunner = new QueryRunner(C3P0Util.getDataSource());
//2.QueryRunner对象调用update方法,传递连接对象,sql语句,以及给?赋值的具体的数据,执行增删改,获取结果
int result = queryRunner.update("delete from users where uid=?", "u007");
if (result>0){
System.out.println("删除一条数据成功");
}else {
System.out.println("删除一条数据失败");
}
}
//修改一条数据
@Test
public void updateOne() throws SQLException {
//1.创建QueryRunner对象时，传入连接池对象
QueryRunner queryRunner=new QueryRunner(C3P0Util.getDataSource());
//2.QueryRunner对象调用update方法,传递连接对象,sql语句,以及给?赋值的具体的数据,执行增删改,获取结果
int result = queryRunner.update("update users set upass=? where uname=?", "223232", "wanglei");
if (result>0){
System.out.println("修改一条数据成功");
}else{
System.out.println("修改一条数据失败");
}
}
}
5.4 查询操作
/*
执行查询方法:
public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) :
执行查询,根据参数ResultSetHandler获取对应的结果
参数:
1.Connection conn: 连接对象
2.String sql: sql语句,参数用?代替
3.ResultSetHandler<T> rsh: 结果集处理器,接口,必然传递实现类对象
实现类: 根据不同的方式处理ResultSet,返回不同结果
4.Object... params: 可变参数,传递参数列表,数组,不传参 作用: 给sql中?进行赋值的
*/
5.4.1 BeanHandler
- 将结果集中第一条记录封装到一个指定的javaBean中
javaBean指的是一个自定义的类，在开发中常用于封装数据
特点：
1.需要实现接口：java.io.Serializable ，通常实现接口这步骤省略了，不会影响程序。
2.提供私有字段：private 类型 字段名;
3.提供getter/setter方法：
4.提供无参构造
/*
ResultSetHandler实现类：
BeanHandler: 返回指定类型的对象
结果集处理器: BeanHandler
作用: 把查询结果的第一行,封装成指定类型的对象
构造方法:
public BeanHandler(Class clazz): 传递Class类型的对象,参数填什么，结果就封装成什么类型的对象
*/
@Test
public void queryUsersByUid() throws SQLException {
//1.创建QueryRunner对象时，传入连接池对象
QueryRunner queryRunner = new QueryRunner();
//定义sql语句
String sql="select * from users where uid=?";
//2.QueryRunner对象调用update方法,传递连接对象,sql语句,以及给?赋值的具体的数据,执行增删改,获取结果
Users user = queryRunner.query(C3P0Util.getConnection(), sql, new BeanHandler<>(Users.class),"u002");
System.out.println(user);
}
5.4.2 BeanListHandler
- 将结果集中每一条记录封装到指定的javaBean中，将这些javaBean在封装到List集合中
/*
ResultSetHandler实现类
结果集处理器:
BeanListHandler
作用: 把查询结果的每一行,封装成指定类型的对象,存储到List集合中
构造方法:
public BeanListHandler(Class clazz):
传递Class类型的对象,告诉结果集处理器,List集合中存储的数据的类型
*/
//查询所有记录，每条记录封装成一个Users对象,存储到List集合中
@Test
public void queryAllUsers() throws SQLException {
//1.创建QueryRunner对象时，传入连接池对象
QueryRunner queryRunner = new QueryRunner();
//定义sql语句
String sql="select * from users ";
//2.QueryRunner对象调用update方法,传递连接对象,sql语句,以及给?赋值的具体的数据,执行增删改,获取结果
List<Users> result = queryRunner.query(C3P0Util.getConnection(), sql, new BeanListHandler<>(Users.class));
for (Users users : result) {
System.out.println(users);
}
}
5.4.3 ColumnListHandler
- 将结果集中指定的列的字段值，封装到一个List集合中
/*
结果集处理器: ColumnListHandler
作用: 把查询结果的某一列,存储到List集合对象
构造方法:
public ColumnListHandler(): 把第一列的内容存储到List集合中
public ColumnListHandler(int index): 把指定编号对应的列的内容存储到List集合中
public ColumnListHandler(String name): 把指定列名对应的列的内容存储到List集合中
*/
public class DBUtileDemo3 {
//查询指定列的数据,存储到List集合中
@Test
public void queryColumn() throws SQLException {
//1.创建QueryRunner对象时
QueryRunner queryRunner = new QueryRunner();
//定义sql语句
String sql="select * from users";
//2.QueryRunner对象调用update方法,传递连接对象,sql语句,以及给?赋值的具体的数据,执行增删改,获取结果
//获取默认第一列的数据
List<Object> result = queryRunner.query(C3P0Util.getConnection(), sql, new ColumnListHandler());
//获取指定列数的数据
// List<Object> result1 = queryRunner.query(C3P0Util.getConnection(), sql, new ColumnListHandler(2));
//获取指定列明的数据
// List<Object> result2 = queryRunner.query(C3P0Util.getConnection(), sql, new ColumnListHandler("uname"));
for (Object o : result) {
System.out.println(o);
}
}
}
5.4.4 ScalarHandler
- 用于单数据。例如select count(*) from 表操作
/*
结果集处理器: ScalarHandler 用途: 用于聚合函数
作用: 获取查询结果的第一行的某一列
构造方法:
public ScalarHandler(): 获取查询结果的第一行的第一列
public ScalarHandler(int index): 获取查询结果的第一行的第index列
public ScalarHandler(String name): 获取查询结果的第一行的name列
*/
//获取结果中第一行中的指定列对应的值
@Test
public void queryOnlyColumn() throws SQLException {
//1.创建QueryRunner对象
QueryRunner queryRunner = new QueryRunner();
//定义sql语句
String sql="select * from users";
//2.QueryRunner对象调用update方法,传递连接对象,sql语句,以及给?赋值的具体的数据,执行增删改,获取结果
//获取查询的数据的第一行的指定列名的数据
Object result = queryRunner.query(C3P0Util.getConnection(), sql, new ScalarHandler("upass"));
System.out.println(result);
}
5、小结
DBUtils工具
- 作用：简化JDBC的操作
DBUtils常用类与方法
-
QueryRunner 用来执行SQL语句对象
- update(Connection conn, String sql, Object… params) 插入表记录、更新表记录、删除表记录
- **update(String sql, Object… params)**创建构造方法时已经传入连接池对象，所以不需要传入连接对象
- query(Connection conn, String sql, ResultSetHandler handler, Object… params) 查询表记录
- query(String sql, ResultSetHandler handler, Object… params) 创建构造方法时已经传入连接池对象，所以不需要传入连接对象
-
ResultSetHandler 接口， 处理结果集的对象
- **BeanHandler：**将结果集中第一条记录封装到一个指定的javaBean中
- **BeanListHandler：**将结果集中所有数据封装到指定的javaBean中，将这些javaBean封装到List集合中
- **ScalarHandler：**它是用于单数据。例如select count(*) from 表操作。
- **ColumnListHandler：**将结果集中指定的列的字段值，封装到一个List集合中
四、事物
1、事物分层
view（表示层，页面展示层）
Service（业务处理层，业务逻辑层）
DAO（持久化层，数据访问层，数据操作层）
-
开发中，常使用分层思想
- 不同的层次结构分配不同的解决过程，各个层次间组成严密的封闭系统
-
不同层级结构彼此平等
-
分层的目的是：
- 解耦
- 可维护性
- 可拓展性
- 可重用性
-
不同层次，使用不同的包表示
- com.myweb 公司域名倒写
- com.myweb.dao DAO层
- con.myweb.service service层
- con.myweb.domain javaben
- con.myweb.utls 工具
2、mysql事务操作
| sql语句 | 描述 |
|---|---|
| start transaction /begin | 开启事务 |
| commit | 提交事务，把数据真正的持久化到数据库中 |
| rollback | 回滚事务，把数据回退到开启事务之前的状态 |
3、JDBC事物操作
| connection.setAutoCommit(false) | 开启事物（关闭自动提交） |
| connection.commit() | 提交事务，把数据真正的持久化到数据库中 |
| connection.rollback() | 回滚事务，把数据回退到开启事务之前的状态 |
3.1 jdbc事物完成转账功能
/*
步骤:
1.获取连接Connection对象
2.Connection对象开启事务
3.Connection对象获取执行sql语句的Statement对象
4.定义2条sql语句(2条update语句:扣款,收款)
5.Statement对象执行sql语句,获取结果
6.如果sql语句正常执行,没有出现问题,提交事务
7.处理结果
8.如果sql语句执行过程中出现问题,回滚事务
9.关闭资源
*/
public class Demo2 {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
//1.获取连接Connection对象
connection = JDBCUtil.getConnection();
//2.Connection对象开启事务
connection.setAutoCommit(false);
//3.Connection对象获取执行sql语句的Statement对象
statement = connection.createStatement();
//4.定义2条sql语句(2条update语句:扣款,收款)
String tomsql = "update account set money=money-1000 where name='tom'";
String jerrysql = "update account set money=money+1000 where name='jerry'";
//5.Statement对象执行sql语句,获取结果
int tomResult = statement.executeUpdate(tomsql);
System.out.println(1 / 0);//出了异常
int jerryResult = statement.executeUpdate(jerrysql);
//7.处理结果
if (tomResult > 0) {
System.out.println("tom扣款成功");
} else {
System.out.println("tom扣款失败");
}
if (jerryResult > 0) {
System.out.println("jerry收款成功");
} else {
System.out.println("jerry收款失败");
}
//6.如果sql语句正常执行,没有出现问题,提交事务
//提交事务
connection.commit();
} catch (Exception e) {
e.printStackTrace();
//8.如果sql语句执行过程中出现问题,回滚事务
if (connection != null) {
try {
connection.rollback();
System.out.println("程序出现异常，事物已回滚");
} catch (SQLException ex) {
ex.printStackTrace();
}
}
} finally {
//9.关闭资源
JDBCUtil.closeResource(connection, statement, null);
}
}
}
3.2 DBUtil工具包优化转账功能
/*
核心类QueryRunner
空参构造: ---要想使用QueryRunner管理事务,必须使用空参构造
public QueryRunner(): 可以直接创建对象,但是没有传递Connection连接对象
所以该对象调用方法,执行增删改查时,必须传Connection连接对象
有参构造:
public QueryRunner(DataSource ds) : 创建对象,必须传递连接池对象
创建QueryRunner对象时,传递连接池对象,执行增删改查的方法时,
QueryRunner对象会从连接池获取连接对象,但是获取的是哪个连接对象,我们不知道,
我们也看不见,所以我们不能完成事务管理的工作
*/
public class Demo3 {
public static void main(String[] args) {
Connection connection = null;
try {
//1.获取QueryRunner对象
QueryRunner queryRunner = new QueryRunner();
//2.使用C3P0工具类获取Connection连接对象
connection = C3P0Util.getConnection();
//3.开启事物
connection.setAutoCommit(false);
//4.自定义sql语句
String tomsql = "update account set money=money-? where name=?";
String jerrysql = "update account set money=money+? where name=?";
//5.执行sql语句
int tomResult = queryRunner.update(connection, tomsql, "1000", "tom");
System.out.println(1 / 0);//出了异常
int jerryResult = queryRunner.update(connection, jerrysql, "1000", "jerry");
//6.提交事物
connection.commit();
//7.处理执行结果
if (tomResult > 0) {
System.out.println("tom扣款成功");
} else {
System.out.println("tom扣款失败");
}
if (jerryResult > 0) {
System.out.println("jerry收款成功");
} else {
System.out.println("jerry收款失败");
}
} catch (Exception e) {
e.printStackTrace();
//8出现异常，事物回滚
if (connection != null) {
try {
connection.rollback();
System.out.println("程序出现异常，事物已回滚");
} catch (SQLException ex) {
ex.printStackTrace();
}
}
} finally { //9.关闭资源
C3P0Util.closeResource(connection, null, null);
}
}
}
3.3DbUtils类再次优化转账功能
/*
DBUtils工具包中的工具类DbUtils
静态方法:
public static void commitAndCloseQuietly(Connection conn)
提交事务,关闭连接,内部进行try-catch异常处理
public static void rollbackAndCloseQuietly(Connection conn):
回滚事务,关闭连接,内部进行try-catch异常处理
*/
public class Demo4 {
public static void main(String[] args) {
Connection connection = null;
try {
//1.获取QueryRunner对象
QueryRunner queryRunner = new QueryRunner();
//2.使用C3P0工具类获取Connection连接对象
connection = C3P0Util.getConnection();
//3.开启事物
connection.setAutoCommit(false);
//4.自定义sql语句
String tomsql = "update account set money=money-? where name=?";
String jerrysql = "update account set money=money+? where name=?";
//5.执行sql语句
int tomResult = queryRunner.update(connection, tomsql, "1000", "tom");
System.out.println(1 / 0);//出了异常
int jerryResult = queryRunner.update(connection, jerrysql, "1000", "jerry");
//6.提交事物
DbUtils.commitAndCloseQuietly(connection);//提交事务,关闭连接,内部进行try-catch异常处理
//7.处理执行结果
if (tomResult > 0) {
System.out.println("tom扣款成功");
} else {
System.out.println("tom扣款失败");
}
if (jerryResult > 0) {
System.out.println("jerry收款成功");
} else {
System.out.println("jerry收款失败");
}
} catch (Exception e) {
e.printStackTrace();
//8出现异常，事物回滚
if (connection != null) {
//回滚事务,关闭连接,内部进行try-catch异常处理
DbUtils.closeQuietly(connection);
System.out.println("程序出现异常，事物已回滚");
}
}
}
}
4、分层完成转账功能
4.1 Dao层
/*
dao层定义AccountDao类，操作数据库
进账方法：
名称：in
参数：连接对象，进账账户，进账金额
出账方法：
名称：out
参数：连接对象，出账账户，出账金额
*/
public class AccountDao {
//进账方法
public void in(Connection connection,String inName,double inMoney) throws SQLException {
//1创建queryRunner对象
QueryRunner queryRunner = new QueryRunner();
//2定义sql语句
String sql="update account set money= money+? where name=?";
//3执行sql语句
queryRunner.update(connection,sql,inMoney,inName);
}
//出账方法
public void out(Connection connection,String outName,double outMoney) throws SQLException {
QueryRunner queryRunner = new QueryRunner();
String sql="update account set money= money-? where name=?";
queryRunner.update(connection,sql,outMoney,outName);
}
}
4.2 Service层
/*
service层定义AccountService类，操作dao层的AccountDao类
转账方法：
名称：transfer
参数：进账账户，出账账户，转账金额
注意：连接对象，Service层自己创建，进账账户、转账账户、转账金额由view层传递
注意：
1.Service层负责异常处理
2.负责事务管理
步骤：
1.创建AccountDao对象
2.获取连接对象
3.开启事务
4.调用方法（进账/出账）实现转账
5.方法正常执行，提交事务
6.方法出现异常，回滚事物
7.关闭资源
*/
public class AccountService {
public void transfer(String inName,String outName,double transferMoney){
Connection connection=null;
try {
//1.创建AccountDao对象
AccountDao accountDao = new AccountDao();
//2.获取连接对象
connection = C3P0Util.getConnection();
//3.开启事务
connection.setAutoCommit(false);
//4.调用方法（进账/出账）实现转账
//调用进账方法
accountDao.in(connection, inName, transferMoney);
//调用出账方法
System.out.println(1/0);//出现异常
accountDao.out(connection, outName, transferMoney);
//5.方法正常执行，提交事务
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
if (connection!=null) {
try {
//6.方法出现异常，回滚事物
connection.commit();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
} finally {
//7.关闭资源
C3P0Util.closeResource(connection,null,null);
}
}
}
4.3 view层
/*
转账页面：
调用Service层
*/
public class AccountPagge {
public static void main(String[] args) {
String inName="jerry";
String outName="tom";
double transferMoney=1000;
//调用Service层
//创建AccountService对象
AccountService accountService = new AccountService();
//调用方法完成转账
accountService.transfer(inName,outName,transferMoney);
}
}
五、ThreadLocal
1、分析
- 在“事务传递Connection参数案例”中，我们必须传递Connection对象，才可以完成整个事务操作。
如果不传递参数，是否可以完成？
在JDK中给我们提供了一个工具类：
-
ThreadLocal，此类可以在一个线程中共享数据。
-
java.lang.ThreadLocal 该类提供了线程局部 (thread-local) 变量，用于在当前线程中共享数据。
/*
java.lang.ThreadLocal<T>类
作用: 实现线程共享局部变量
内部使用Map集合,Map集合有2个泛型,K: 键的类型,V代表值的类型
ThreadLocal<T>类:
只有一个泛型,代表的是值的类型,内部把键的类型已经规定为了Thread,使用当前线程对象,作为键的值
所以ThreadLocal<T>类,泛型T表示的是给当前线程对象,绑定的值的类型
成员方法:
public void set(T t): 给当前线程对象,绑定一个T类型的变量t
相当于Map集合:
map.put(Thread.currentThread,t)
public T get(): 获取当前线程对象上,绑定的值
相当于Map集合:
map.get(Thread.currentThread)
public T remove(): 删除当前线程对象上绑定的值
相当于Map集合:
map.remove(Thread.currentThread)
*/
public class ThreadLocalDemo1 {
public static void main(String[] args) {
ThreadLocal<String> threadLocal=new ThreadLocal<>();
//在当前线程上绑定字符串
threadLocal.set("qqqq");
//获取值
String s = threadLocal.get();
System.out.println(s);
//开启SubThread线程，传入threadLocal作为参数
new SubThread(threadLocal).start();
}
}
class SubThread extends Thread {
private ThreadLocal<String> tl;
public SubThread(ThreadLocal<String> tl) {
this.tl = tl;
}
@Override
public void run() {
//获取当前线程对象上绑定的值
String str = tl.get();
System.out.println("run...."+str);//run....null
//在当前线程上绑定字符串
tl.set("abc");
//获取当前线程对象上绑定的值
str = tl.get();
System.out.println("run...."+str);//run...abc.
}
}
2、使用ThreadLocal自定义工具类
import java.sql.Connection;
import java.sql.SQLException;
/*
自定义连接Connection对象管理工具
*/
public class ConnectionManager {
/*
借助ThreadLocal实现线程中局部变量的数据共享
*/
private static ThreadLocal<Connection> threadLocal=new ThreadLocal<>();
private ConnectionManager(){
}
/*
定义静态方法。获取连接对象
此方法，保证获取的是同一个Connection连接对象
*/
public static Connection getConnection() throws SQLException {
//首先从ThreadLocal对象中获取Connection对象
Connection connection=threadLocal.get();
//判断connection是否为null，为null说明没有绑定对象
if (connection==null){
connection=C3P0Util.getConnection();
//绑定到ThreadLocal中
threadLocal.set(connection);
}
return connection;
}
//定义方法，开启事务
public static void setAutoCommit() throws SQLException {
Connection connection = ConnectionManager.getConnection();
connection.setAutoCommit(false);
}
//定义提交事务方法
public static void commit() throws SQLException {
Connection connection = ConnectionManager.getConnection();
connection.commit();
}
//定义回滚事务方法
public static void rollback() throws SQLException {
Connection connection = ConnectionManager.getConnection();
connection.rollback();
//把连接对象从ThreadLocal对象中移除
threadLocal.remove();
}
}
3、优化转账功能
3.1 Dao层
/*
使用自定义ConnectionManager类优化代码
dao层定义AccountDao类，操作数据库
进账方法：
名称：in
参数：进账账户，进账金额
出账方法：
名称：out
参数：出账账户，出账金额
注意：
1，连接对象从ConnectionManager工具类中获取，他保证同一个线程当中获取的是同一个连接对象
*/
public class AccountDao {
//进账方法
public void in(String inName,double inMoney) throws SQLException {
//1创建queryRunner对象
QueryRunner queryRunner = new QueryRunner();
//2定义sql语句
String sql="update account set money= money+? where name=?";
//3执行sql语句
queryRunner.update(ConnectionManager.getConnection(),sql,inMoney,inName);
}
//出账方法
public void out(String outName,double outMoney) throws SQLException {
QueryRunner queryRunner = new QueryRunner();
String sql="update account set money= money-? where name=?";
queryRunner.update(ConnectionManager.getConnection(),sql,outMoney,outName);
}
}
3.2 Service层
/*
service层定义AccountService类，操作dao层的AccountDao类
转账方法：
名称：transfer
参数：进账账户，出账账户，转账金额
注意：连接对象从ConnectionManager工具类中获取，他保证同一个线程当中获取的是同一个连接对象
进账账户、转账账户、转账金额由view层传递
注意：
1.Service层负责异常处理
2.负责事务管理
步骤：
1.创建AccountDao对象
2.获取连接对象
3.开启事务
4.调用方法（进账/出账）实现转账
5.方法正常执行，提交事务
6.方法出现异常，回滚事物
7.关闭资源
*/
public class AccountService {
public void transfer(String inName,String outName,double transferMoney){
Connection connection=null;
try {
//1.创建AccountDao对象
AccountDao accountDao = new AccountDao();
//2.获取连接对象
connection = ConnectionManager.getConnection();
//3.开启事务
ConnectionManager.setAutoCommit();
//4.调用方法（进账/出账）实现转账
//调用进账方法
accountDao.in(inName, transferMoney);
//调用出账方法
System.out.println(1/0);//出现异常
accountDao.out(outName, transferMoney);
//5.方法正常执行，提交事务
ConnectionManager.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
//6.方法出现异常，回滚事物
ConnectionManager.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
//7.关闭资源
C3P0Util.closeResource(connection,null,null);
}
}
}
3.3 view层
/*
转账页面：
调用Service层
*/
public class AccountPagge {
public static void main(String[] args) {
String inName="jerry";
String outName="tom";
double transferMoney=1000;
//调用Service层
//创建AccountService对象
AccountService accountService = new AccountService();
//调用方法完成转账
accountService.transfer(inName,outName,transferMoney);
}
}
六、事务总结
1、 事务特性：ACID
- **原子性（Atomicity）**原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发 生。
- **一致性（Consistency）**事务前后数据的完整性必须保持一致。
- **隔离性（Isolation）**事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务 所干扰，多个并发事务之间数据要相互隔离。
- **持久性（Durability）**持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使 数据库发生故障也不应该对其有任何影响。
2、并发访问问题
如果不考虑隔离性，事务存在三种并发访问问题。
1.脏读：一个事务读到了另一个事务未提交的数据.
2.不可重复读：一个事务读到了另一个事务已经提交(update)的数据。引发另一个事务，在事务中的多次查询结 果不一致。
3.虚读 /幻读：一个事务读到了另一个事务已经提交(insert)的数据。导致另一个事务，在事务中多次查询的结果 不一致。
3、隔离级别：解决问题
- 数据库规范规定了4种隔离级别，分别用于描述两个事务并发的所有情况。
-
read uncommitted 读未提交，一个事务读到另一个事务没有提交的数据。
a)存在：3个问题（脏读、不可重复读、虚读）。
b)解决：0个问题
-
read committed 读已提交，一个事务读到另一个事务已经提交的数据。
a)存在：2个问题（不可重复读、虚读）。
b)解决：1个问题（脏读）
-
repeatable read:可重复读，在一个事务中读到的数据始终保持一致，无论另一个事务是否提交。
a)存在：1个问题（虚读）。
b)解决：2个问题（脏读、不可重复读）
-
serializable 串行化，同时只能执行一个事务，相当于事务中的单线程。
a)存在：0个问题。
b)解决：3个问题（脏读、不可重复读、虚读）
-
安全和性能对比
-
安全性：
serializable > repeatable read > read committed > read uncommitted -
性能 ：
serializable < repeatable read < read committed < read uncommitted
-
-
常见数据库的默认隔离级别：
-
MySql：
repeatable read -
Oracle：
read committed
-
4、隔离级别演示
4.1查询数据库的隔离级别
show variables like '%isolation%';
或
select @@tx_isolation;
4.2设置数据库的隔离级别
set session transactionisolation level 级别字符串- 级别字符串： read uncommitted 、 read committed 、 repeatable read 、 serializable
- 例如：
set session transaction isolation level read uncommitted; - 例如：
set session transaction isolation level read committed; - 例如：
set session transaction isolation level repeatable read; - 例如：
set session transaction isolation level serializable;
- 例如：
4.2.1、读未提交：read uncommitted
- A窗口设置隔离级别
- AB同时开始事务
- A 查询
- B 更新，但不提交
- A 再查询？-- 查询到了未提交的数据
- B 回滚
- A 再查询？-- 查询到事务开始前数据
4.2.2、读已提交：read committed
- A窗口设置隔离级别
- AB同时开启事务
- A查询
- B更新数据，但不提交
- A再查询？–数据不变，解决了【脏读】
- B提交
- A再查询？–数据改变，存在问题【不可重复度】
4.2.3、可重复读：repeatable read
- A窗口设置隔离级别
- AB 同时开启事务
- A查询
- B更新， 但不提交
- A再查询？–数据不变，解决问题【脏读】
- B提交
- A再查询？–数据不变，解决问题【不可重复读】
- A提交或回滚
- A再查询？–数据改变，另一个事务
4.2.4、串行化：serializable
- A窗口设置隔离级别
- AB同时开启事务
- A查询
-
B更新？–等待(如果A没有进一步操作，B将等待超时)
-
A回滚
-
B 窗口？–等待结束，可以进行操作
ConnectionManager.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } } finally { //7.关闭资源 C3P0Util.closeResource(connection,null,null); }}
}
-
### 3.3 view层
```java
/*
转账页面：
调用Service层
*/
public class AccountPagge {
public static void main(String[] args) {
String inName="jerry";
String outName="tom";
double transferMoney=1000;
//调用Service层
//创建AccountService对象
AccountService accountService = new AccountService();
//调用方法完成转账
accountService.transfer(inName,outName,transferMoney);
}
}
六、事务总结
1、 事务特性：ACID
- **原子性（Atomicity）**原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发 生。
- **一致性（Consistency）**事务前后数据的完整性必须保持一致。
- **隔离性（Isolation）**事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务 所干扰，多个并发事务之间数据要相互隔离。
- **持久性（Durability）**持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使 数据库发生故障也不应该对其有任何影响。
2、并发访问问题
如果不考虑隔离性，事务存在三种并发访问问题。
1.脏读：一个事务读到了另一个事务未提交的数据.
2.不可重复读：一个事务读到了另一个事务已经提交(update)的数据。引发另一个事务，在事务中的多次查询结 果不一致。
3.虚读 /幻读：一个事务读到了另一个事务已经提交(insert)的数据。导致另一个事务，在事务中多次查询的结果 不一致。
3、隔离级别：解决问题
- 数据库规范规定了4种隔离级别，分别用于描述两个事务并发的所有情况。
-
read uncommitted 读未提交，一个事务读到另一个事务没有提交的数据。
a)存在：3个问题（脏读、不可重复读、虚读）。
b)解决：0个问题
-
read committed 读已提交，一个事务读到另一个事务已经提交的数据。
a)存在：2个问题（不可重复读、虚读）。
b)解决：1个问题（脏读）
-
repeatable read:可重复读，在一个事务中读到的数据始终保持一致，无论另一个事务是否提交。
a)存在：1个问题（虚读）。
b)解决：2个问题（脏读、不可重复读）
-
serializable 串行化，同时只能执行一个事务，相当于事务中的单线程。
a)存在：0个问题。
b)解决：3个问题（脏读、不可重复读、虚读）
-
安全和性能对比
-
安全性：
serializable > repeatable read > read committed > read uncommitted -
性能 ：
serializable < repeatable read < read committed < read uncommitted
-
-
常见数据库的默认隔离级别：
-
MySql：
repeatable read -
Oracle：
read committed
-
4、隔离级别演示
4.1查询数据库的隔离级别
show variables like '%isolation%';
或
select @@tx_isolation;
4.2设置数据库的隔离级别
set session transactionisolation level 级别字符串- 级别字符串： read uncommitted 、 read committed 、 repeatable read 、 serializable
- 例如：
set session transaction isolation level read uncommitted; - 例如：
set session transaction isolation level read committed; - 例如：
set session transaction isolation level repeatable read; - 例如：
set session transaction isolation level serializable;
- 例如：
4.2.1、读未提交：read uncommitted
- A窗口设置隔离级别
- AB同时开始事务
- A 查询
- B 更新，但不提交
- A 再查询？-- 查询到了未提交的数据
- B 回滚
- A 再查询？-- 查询到事务开始前数据
4.2.2、读已提交：read committed
- A窗口设置隔离级别
- AB同时开启事务
- A查询
- B更新数据，但不提交
- A再查询？–数据不变，解决了【脏读】
- B提交
- A再查询？–数据改变，存在问题【不可重复度】
4.2.3、可重复读：repeatable read
- A窗口设置隔离级别
- AB 同时开启事务
- A查询
- B更新， 但不提交
- A再查询？–数据不变，解决问题【脏读】
- B提交
- A再查询？–数据不变，解决问题【不可重复读】
- A提交或回滚
- A再查询？–数据改变，另一个事务
4.2.4、串行化：serializable
- A窗口设置隔离级别
- AB同时开启事务
- A查询
- B更新？–等待(如果A没有进一步操作，B将等待超时)
- A回滚
- B 窗口？–等待结束，可以进行操作
1483
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
