数据库和JDBC基础内容
数据库
数据库简单原理图
.
使用命令行窗口连接MySQL数据库
.
MySQL三层结构-破除MySQL神秘
基本说明
- 所谓安装MySQL数据库，就是在主机安装一个数据库管理系统(DBMS)，这个管理程序可以管理多个数据库。DBMS(DataBase Manage System)
- 一个数据库中可以创建多个表，以保存数据(信息)。
- 数据库管理系统(DBMS)、数据库和表的关系如图所示：
.
数据在数据库中的存储方式
.
SQL语句分类
- DDL：数据定义语言【create 表，库...】
- DML：数据操作语言【增加 insert，修改 update，删除 delete】
- DQL：数据查询语言【select】
- DCL：数据控制语言【管理数据库：比如用户权限 grant-授权 revoke-撤销】
数据库
创建数据库
create database if not exists 数据库名称 create_specification, create_specification,...
# 说明
# 1.if not exists 可以省略不写。但是如果新建的表已经存在，不写则报错；加上之后不报错，但是报警告.
# 2.create_specification 创建规范
# (1) character set charset_name （charset_name-字符集名称）
# (2) collate collation_name （collate-核对规则，排序规则；collation_name-排序规则名称）
# 3.character set: 指定数据库采用的字符集，如果不指定字符集，默认utf8
# 4.collate: 指定数据库字符集的校对规则(排序规则)(常用的utf8_bin【区分大小写】、utf8_general_ci【不区分大小写】 注意默认是utf8_general_ci【不区分大小写】)
# 删除数据库hsp_db01
drop database hsp_db01
# 1.创建一个名称为hsp_db01的数据库
create database hsp_db01
# 2.创建一个使用utf8字符集的hsp_db02数据库
create database hsp_db02 character set utf8
# 3.创建一个使用utf8字符集，并带校对规则的hsp_db03数据库
create database hsp_db03 character set utf8 collate utf_bin
# 校对规则 utf8_bin[区分大小写] 默认是utf8_general_ci[不区分大小写]
# utf8_bin 下 tom 和 Tom不同
# utf8_general_ci 下 tom 和 Tom相同
# select 查询；* 表示所有字段；from 从哪个表；
# where 从哪个字段 name = 'tom' 查询名字是 tom
select * from t1 where name = 'tom'
查看、删除数据库
# 显示数据库语句
show databases
# 显示数据库创建语句
show create database db_name
# 数据库删除语句[一定要慎用]
drop database [if exists] db_name
# 查看当前数据库服务器中的所有数据库 databases 与 database要区分 最后是否有 's'
show databases
# 查看前面创建的 hsp_db01 数据库的定义信息
show create database `hsp_db01`
# 在创建数据库，表的时候，为了规避关键字，可以使用反引号解决 tab键上的就是反引号[英文输入法下]
# 删除前面创建的 hsp_db01数据库
drop database `hsp_db01`
备份恢复数据库
# 备份数据库(注意：在DoS执行) 命令行
mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > D:\\文件名.sql
# 恢复数据库(注意：进入MySQL命令行再执行)
Source 文件名.sql
# database03.sql 备份 hsp_db02 和 hsp_db03库中的数据，并恢复
# 备份要在 Dos下执行，mysqldump指令其实在mysql安装目录\bin
# 这个备份的文件，就是对应的sql语句
mysqldump -u root -p -B hsp_db02 hsp_db03 > D:\\bak.sql
# 删除数据库
drop database hsp_db02
# 1.恢复数据库(注意：进入MySQL命令行再执行)
# 进入MySQL命令行
mysql -u root -p
# 恢复数据
source d:\\bak.sql
# 2.恢复方法
# 直接将bak.sql的内容放到查询编辑器中执行
备份恢复数据库的表
# 备份库的表(注意：在DoS执行) 命令行
mysqldump -u 用户名 -p 密码 数据库名 表1 表2 表n > d:\\文件名.sql
# 注意：(1) 这里不能再写 -B
# (2) -p 后面的密码可以自行选择
备份练习
# 1.这是一个ecshop 的数据库，包括ecshop所有的表，请导入到mysql数据库中[备份]
# 2.再将ecshop整个数据库备份到你的D:\\ecshop.sql
# 3.将mysql的ecshop数据库删除，并通过备份的d:\\ecshop.sql恢复
mysqldump -u root -p ecshop > D:\\ecshop.sql # 在DoS执行
drop database ecshop # 进入MySQL命令行再执行
mysql -u root -p # 在DoS执行
source D:\\ecshop.sql # 进入MySQL命令行再执行
这里需要自己练习.........！！！！
表
创建表
create table table_name (
field1 datatype,
field2 datatype,
field3 datatype
) character set 字符集 collate 校对规则 engine 引擎
# character set 字符集 collate 校对规则 engine 引擎 —— 都可以省略
# field：指定列名
# datatype：指定列类型(字段类型)
# character set：如不指定则为所在数据库的字符集
# collate：如不指定则为所在数据库的校对规则
# engine：引擎
# 指令创建表
# 注意：hsp_db02创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。
# 例：user表
# id 整形
# name 字符串
# password 字符串
# birthday 日期
create table `user` (
id int,
`name` varchar(255),
`password` varchar(255),
`birthday` date
) character set utf8 collate utf8_bin engine innodb;
MySQL常用数据类型(列类型/数据类型)
示意图
.
数值型(整数)的基本使用
- 说明，使用规范：在能够满足需求的情况下，尽量选择占用空间小的类型。
# 以tinyint为例 演示整型的一个范围 有符号：-128~127；无符号：0~255
# 说明：表的字符集，校验规则，存储引擎，使用默认
# 1.如果没有指定 unsigned，则 tinyint就是有符号的
create table t3 (
id tinyint);
# 2.如果指定 unsigned，则 tinyint 就是无符号的 0~255
create table t4 (
id tinyint unsigned);
insert into t3 values(-128);
select * from t3;
insert into t4 values(255);
select * from t4;
- 如何定义一个无符号的整数
create table t10 (id tinyint); # 默认是有符号的
create table t11 (id tinyint unsigned); # 无符号的
数值型(bit)的使用
- 基本使用
# 演示bit类型使用
# 说明
# 1.bit(m) m 在 1~64
# 2.添加数据 m单位：位, 范围按你给定的位数来确定，比如m=8 表示一个字节 0~255
# 3.显示按照bit
# 4.查询时，仍然可以按照数a
create table t5 (num bit(8));
insert into t5 values(255);
select * from t5;
- 细节说明
# bit 字段显示时，按照 位的方式显示
# 查询的时候仍然可以用使用添加的数值
# 如果一个值只有 0，1 可以考虑使用bit(1)，可以节约空间
# 位类型，M指定位数，默认值1，范围1~64
# 使用不多
数值型(小数)的基本使用
# 1.float/double [unsigned]
# float 单精度，double 双精度
# 2.decimal[M, D] [unsigned]
# 可以支持更加精确的小数位。
# M是小数位数(精度)的总数【整数部分+小数部分】，D是小数点(标度)后面的位数【只包含小数部分】
# 如果D是0，则值没有小数部分。M最大65。D最大是30。如果D被省略，默认为0。如果M被省略，默认是10.
# 建议：如果希望小数的精度高，推荐使用decimal
# 演示decimal类型、float类型、double类型
# 创建表
create table t06 (
num1 float,
num2 double,
num3 decimal(30, 20));
# 添加数据
insert into t06 values(88.12345678912345, 88.12345678912345, 88.12345678912345);
# 查询数据
select * from t06;
# decimal可以存放很大的数
create table t07 (
num decimal(65));
insert into t07 values(8999985561489414981974891);
select * from t07;
create table t08 (
num bigint unsigned);
insert into t08 values(8999985561489414981974891);
select * from t08;
字符串的基本使用
# char(size) -> 0~255字符
# 固定长度字符串 最大255 字符
# varchar(size) -> 0~65535字节
# 可变长度字符串 最大65532字节【utf8编码最大21844字符 1-3个字节用于记录大小】
# utf8 varchar(size) size-> (65535 - 3) / 3 = 21844
# gbk varchar(size) size-> (65535 - 3) / 2 = 32766
# 在不同的编码下，每一个字符所占的字节空间不一样
# 演示字符串类型使用 char varchar
# 注释快捷键 shift+ctrl+c，取消注释shift+ctrl+r
create table t09 (
`name` char(256));
create table t10 (
`name` varchar(65535));
create table t11 (
`name` varchar(32766)) charset gbk;
字符串使用细节
# 细节1：
# char(4) //这个4表示字符数(最大255)，不是字节数，不管是中文还是字母都是放4个，按字符计算.
# varchar(4) //这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据. 占用的具体空间看编码方式
# 不管是 中文还是英文字母，都是最多存放4个，是按照字符来存放的
# 细节2：
# char(4) 是定长(固定的大小)，就是说，即使你插入 'aa', 也会占用 分配的4个字符的空间.这就可能会造成空间的浪费
# varchar(4)是变长(变化的大小)，就是说，如果你插入了 'aa', 实际占用空间大小并不是4个字符，而是按照实际占用空间来分配. (老韩提示：varchar本身还需要占用1-3个字节来记录存放内容的长度) L(实际数据大小)+(1-3)个字节
# 细节3：
# 什么时候使用 char, 什么时候使用varchar
# 1.如果数据是定长，推荐使用char, 比如md5的密码，邮编，手机号，身份证号码等. char(32)->md5加密
# 2.如果一个字段的长度是不确定，我们使用varchar，比如留言，文章
# 3.查询速度：char > varchar
# 细节4：
# 在存放文本时，也可以使用Text数据类型.可以将text列视为varchar列，注意text不能有默认值.大小0~2^16字节
# 如果希望存放更多字符，可以选择mediumtext 0-2^24 或者 longtext 0-2^32
# 字符串使用细节
# char(4) 和 varchar(4) 这个4表示的都是字符，而不是字节
# 不区分字符是汉字还是字母
create table t12 (
`name` char(4));
insert into t12 values('abcde');
insert into t12 values('软柠柠吖a');
select * from t12;
create table t13 (
`name` varchar(4));
insert into t13 values('abcde');
insert into t13 values('软柠柠吖a');
select * from t13;
# 如果varchar 不够用，可以考虑使用mediumtext 或者longtext
# 如果想简单点，可以直接使用text.
# java中用双引号表示字符串
# MySQL中用单引号表示字符串
create table t14 (
content text,
content2 mediumtext,
content3 longtext);
insert into t14 values('软柠柠吖1', '软柠柠吖200', '软柠柠吖300~');
select * from t14;
日期类型
# 日期类型的基本使用
create table birthday (
t1 date,
t2 datetime,
t3 timestamp not null default current_timestamp on update current_timestamp);
# timestamp 时间戳
insert into birthday (t1, t2) values('2022-11-11', '2022-11-11 10:10:10');
# 细节1：
# timestamp 在 insert和update时，自动更新
# 演示时间相关的类型
# 创建一张表，date，datetime，timestamp
create table t15 (
birthday date, -- 生日 年月日
job_time datetime, -- 记录年月日，时分秒
login_time timestamp
not null default current_timestamp
on update current_timestamp); -- 登录时间，如果希望login_time列自动更新，需要配置
insert into t15(birthday, job_time) values('2022-11-11', '2022-11-11 10:10:10');
-- 如果我们更新 t15表的某条记录，login_time列会自动的以当前时间进行更新
select * from t15;
创建表示例代码
# 创建t1表
CREATE TABLE t1 (
id INT,
`name` VARCHAR (20),
sex CHAR(1),
birthday DATE,
entry_date DATETIME,
job VARCHAR (255),
Salary DOUBLE,
`resume` TEXT
) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
# 插入一条数据
INSERT INTO t1 VALUES(100, '软柠柠', '男', '2022-3-21', '2025-5-15',
'博客', 5000, '软柠柠吖');
# 查看数据
SELECT * FROM t1;
# 删除表
DROP TABLE t1;
列操作
# 修改列-基本介绍
# 使用alter table 语句追加，修改，或删除列的语法.
添加列结构-add
#------------------------列-------------------------------
# 1.添加列 -> 可以使用括号来一次性添加多个字段(添加多列)
alter table `table_name` add(
column_name datatype [not null default 默认值]
[, column_name datatype]...);
# default expr:
# eg. not null default '' after 列名 -> 非空并指定默认值 '' 并且指定在那一列的后面添加一列
修改列结构-modify
# 2.修改列 -> 注意：一次只能修改一列(一个字段)，不能加括号来修改多列(多个字段)否则会出现异常
alter table `table_name`(表名) modify column_name datatype [not null default 默认值];
# default expr -> not null default ''
删除列结构-drop
# 3.删除列 -> 一次只能删除一列，不能使用括号
alter table `table_name`(表名) drop column_name;
修改列名-change
# 4.修改列名
alter table 表名 change column 旧列名 新列名 datatype [not null default 默认值];
表操作
查看表结构-desc
#------------------------表-------------------------------
# 1.查看表的结构
desc 表名; -- 可以查看表的列
修改表名-rename
# 2.修改表名
rename table 旧表名 to 新表名;
修改表字符集
# 3.修改表字符集
alter table 表名 character set 字符集;
mysql 的 Invalid use of NULL value 问题
# Invalid use of NULL value -> 无效地使用空值
# 解决方案
# 1.在创建表的列结构时必须加上 not null
# 2.使用 修改列语句 进行非空修改
alter table `table_name`(表名) modify column_name datatype [not null default 默认值];
修改表示例代码
# 1.添加image列 varchar类型
ALTER TABLE t1 ADD(
image2 VARCHAR(255) NOT NULL DEFAULT '' after `resume`,
image3 VARCHAR(255) NOT NULL DEFAULT '' after image2);
# 2.修改job列，长度为60
ALTER TABLE t1 MODIFY image2 VARCHAR(60);
ALTER TABLE t1 MODIFY image2 VARCHAR(66) NOT NULL;
# 3.删除sex列
ALTER TABLE t1 DROP sex;
# 4.修改表名 employee
RENAME TABLE t1 TO employee;
RENAME TABLE employee TO t1;
# 5.修改表的字符集utf8
ALTER TABLE employee CHARACTER SET utf8;
# 6.修改列名 -> Invalid use of NULL value问题的引出
ALTER TABLE employee CHANGE `image3` `image333` VARCHAR(23) NOT NULL DEFAULT '1';
ALTER TABLE employee CHANGE COLUMN `image2` `image22` VARCHAR(64) NOT NULL;
ALTER TABLE employee CHANGE COLUMN `image1` `image111` VARCHAR(64) NOT NULL DEFAULT '';
# 7.查看表结构
DESC employee;
DESC t1;
数据库C[create]R[read]U[update]D[delete]语句
insert -- 添加数据
update -- 更新数据
delete -- 删除数据
select -- 查找数据
insert语句
基本介绍
# 使用 insert 语句向表中插入数据
insert into `table_name` [(`column` [, `column`...])] values (`value [, value...]);
示例代码
# 创建一张goods表
CREATE TABLE `goods` (
id INT,
goods_name VARCHAR(10),
price DOUBLE);
# 添加数据
INSERT INTO `goods` (id, goods_name, price) VALUES (1, '哇哈哈', 2.5);
INSERT INTO `goods` (id, goods_name, price) VALUES (2, '方便面', 1.5);
# 查看数据
SELECT * FROM goods;
注意事项
# 1.插入的数据应与字段的数据类型相同。
# 比如 把'abc' 添加到 int类型会错误
# 但是 可以把 '123' 添加到 int类型，因为MySQL底层会尝试将String转成int,但前提是该String是可以转成int的，如果该字符串无法转成int 则报错. '123.456'小数型也可以添加
# 2.数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
# 3.在values中列出的数据位置必须与被加入的列的排列位置相对应。
# 4.字符和日期型数据应包含在单引号中。
# 5.列可以插入空值【前提是该字段允许为空】，
insert into `table_name` values (null);
# 6.添加多条记录
insert into `table_name` (列名...) values(), (), ();
# 7.如果是给表中的所有字段添加数据，可以不写前面的字段名称。
# 8.默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错。
# 如果某个列 没有指定 not null, 那么当添加数据时，没有给定值，则会默认给null.
# 如果我们希望指定某个列的默认值，可以在创建表时指定. -> not null default 默认值;
update语句
基本介绍
# 使用 update语句修改表中数据
update `table_name` set `col_name1`=expr [, col_name2=expr] [where 条件]
# expr: 既可以是具体的值，也可以为一个表达式.
# 如果where不写，则默认对表的所有数据修改.
示例代码
# 演示update语句
# 1.将所有员工的薪水修改为5000元
# 如果没有带where 条件，会修改所有的记录，因此要小心
UPDATE employee SET Salary=5000;-- 一定要慎用
# 2.将姓名为 king 的员工的薪水修改为3000元
# 字符串和日期要写单引号！！
UPDATE employee
SET Salary=30000
WHERE `name`='king';
# 3.将 king222 的薪水在原有基础上 增加 1000元
UPDATE employee
SET Salary = Salary + 1000
WHERE NAME='king222';
-- 可以修改多个列
UPDATE employee
SET Salary = Salary + 1000, job = '腾讯'
WHERE NAME='king222';
# 查询数据
SELECT * FROM employee;
注意事项
# 1.update语法可以用新值更新原有表行中的各列.
# 2.set子句指定要修改哪些列和要给予哪些值. -> 列 和 值
# 3.where子句指定应更新哪些行。如果没有where子句，则更新所有行(记录)。【一定要慎用】 -> 行
# 4.如果需要修改多个字段的值，可以通过
set 字段1=值1, 字段2=值2...
delete语句
基本介绍
# 使用delete语句删除表中的数据
delete from `table_name` [where 条件]
示例代码
# 删除delete语句演示
-- 删除表中名称为'king'的记录(行)
DELETE FROM employee WHERE NAME = 'king';
-- 删除表中所有记录, 一定要慎用
DELETE FROM employee;
-- delete语句不能删除某一列的值(可使用update设为null或者'')
UPDATE employee SET job = '' WHERE NAME='king';
-- 删除表(结构)
DROP TABLE employee;
注意事项
# 1.如果不使用where子句，将删除表中所有数据。
# 2.delete语句不能删除某一列的值(可使用update设为null或者'')
# 3.使用delete语句仅删除记录，不删除表本身。如果删除表，使用drop table语句。
drop table 表名;
select语句
基本介绍
select [distinct] *|{column1, column2, column3...} from `table_name`;
# 注意事项
# 1.select指定查询哪些列的数据 -> 列
# 2.column指定列名。
# 3.*号代表查询所有列。
# 4.from指定查询哪张表。
# 5.distinct可选，指显示结果时，是否去掉重复数据。
# 使用表达式对查询的列进行运算
select * | {column1 | expression, column2 | expression...} from `table_name`;
# 在select语句中可使用as语句
select `column_name` as 别名 from 表名;
where子句
# 在where子句中经常使用的运算符 -> 过滤
# 1.比较运算符
# > < >= <= = <> != 大于 小于 大于等于 小于等于 不等于 不等于
# between ...and... 显示在某一区间的值 闭区间
# not between ...and... 显示不在某一区间的值
# in(set) 显示在in列表的值，例如：in (100, 200);
# like '张%' 模糊查询 '张%' -> 名字以张开头就可以,一个'张'也可以
# not like '张%' 模糊查询
# is null 判断是否为空
# 2.逻辑运算符
# and 多个条件同时成立
# or 多个条件任一成立
# not 不成立，例如：where not (salary > 100);
order by
# 使用order by子句排序查询结果 -> 排序
select column1, column2, column3..
from `table_name`
order by column_name asc|desc;
# 1.order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的别名
# 2. asc->升序【默认】、desc->降序
# 3.order by 子句应位于select语句的结尾
示例代码
# select语句
-- 创建student表
CREATE TABLE student(
id INT NOT NULL DEFAULT 1,
NAME VARCHAR(20) NOT NULL DEFAULT '',
chinese FLOAT NOT NULL DEFAULT 0.0,
english FLOAT NOT NULL DEFAULT 0.0,
math FLOAT NOT NULL DEFAULT 0.0
);
-- 添加数据
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩顺平',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'宋江',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'关羽',88,98,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'欧阳锋',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(700,'韩蓉700',175,165,130);
-- 查询数据
SELECT * FROM student;
-- 查询表中所有学生的信息
SELECT * FROM student;
-- 查询表中所有学生的姓名和对应的英语成绩
SELECT NAME, english FROM student;
-- 过滤表中重复数据 distinct -> 要查询的记录，每个字段都相同，才会去重
SELECT DISTINCT english FROM student;
-- 统计每个学生的总分
SELECT `name`, (chinese + english + math) FROM student;
-- 在所有学生总分加10分的情况
SELECT `name`, (chinese + english + math + 10) FROM student;
-- 使用别名表示学生分数
SELECT `name` AS '姓名', (chinese + english + math) AS total_score FROM student;
# 使用where子句，进行过滤查询
-- 查询姓名为赵云的学生的成绩
SELECT * FROM student
WHERE `name` = '赵云';
-- 查询英语成绩大于90分的学生
SELECT * FROM student
WHERE english > 90;
-- 查询总分大于200分的所有同学
SELECT * FROM student
WHERE (chinese + english + math) > 200;
-- 查询math大于60，并且(and)id 大于90的学生成绩
SELECT * FROM student
WHERE math > 60 AND id > 90;
-- 查询英语成绩大于语文成绩的同学
SELECT * FROM student
WHERE english > chinese;
-- 查询总分大于200 并且 数学成绩小于语文成绩的 姓韩的同学
-- 韩% 表示 名字以韩开头的就可以，%对应0到多
SELECT * FROM student
WHERE (chinese + english + math) > 200 AND math < chinese AND NAME LIKE '韩%';
-- 查询英语分数在80~90之间的同学
SELECT * FROM student
WHERE english >= 80 AND english <= 90;
SELECT * FROM student
WHERE english BETWEEN 80 AND 90; -- between..and..是闭区间
-- 查询数学分数在 89，90，91的同学
SELECT * FROM student
WHERE math = 89 OR math = 90 OR math = 91;
# 是()小括号， 而不是 {}
SELECT * FROM student
WHERE math IN (89, 90, 91);
-- 查询所有姓韩的学生的成绩
SELECT * FROM student
WHERE `name` LIKE '韩%';
-- 查询数学成绩>80, 语文成绩>80的同学
SELECT * FROM student
WHERE math > 80 AND chinese > 80;
# 演示order by使用
-- 对数学成绩排序后输出【升序】
SELECT * FROM student
ORDER BY math;
-- 对总分按从高到低的顺序输出[降序] -- 使用别名排序
SELECT `name`, (chinese + english + math) AS total_name FROM student
ORDER BY total_name DESC;
-- 对姓韩的学生成绩[总分]排序输出(升序) -- where + order by
SELECT `name`, (chinese + english + math) AS total_name FROM student
WHERE NAME LIKE '韩%'
ORDER BY total_name; -- 使用别名，推荐使用！！！
SELECT `name`, (chinese + english + math) AS total_name FROM student
WHERE NAME LIKE '韩%'
ORDER BY (chinese + english + math); -- 未使用别名，有点麻烦，也不是很推荐
SELECT * FROM student
WHERE NAME LIKE '韩%'
ORDER BY (chinese + english + math);-- 无法显示总分，不好，不推荐
合计/统计函数
statistics 统计.
count
# count 返回查询的结果一共有多少行(行的总数)
select count(*) | count(列名) from `table_name`
[where where_definition]
# mysql统计函数的使用
-- 统计一个班级共有多少学生？
SELECT COUNT(*) FROM student;
-- 统计数学成绩大于90的学生有多少？
SELECT COUNT(*) FROM student
WHERE math > 90;
-- 统计总分大于250的人数有多少？
SELECT COUNT(*) FROM student
WHERE (math + chinese + english) > 250;
-- count(*) 和 count(列名) 的区别
# count(*) ->返回满足条件的记录的行数
# count(列名) ->统计满足条件的某列有多少个，但是会排除 为null的列
CREATE TABLE t2 (
`name` VARCHAR(20));
INSERT INTO t2 VALUES('tom');
INSERT INTO t2 VALUES('jack');
INSERT INTO t2 VALUES('mary');
INSERT INTO t2 VALUES(NULL);
INSERT INTO t2 VALUES('null');
SELECT * FROM t2;
SELECT COUNT(*) FROM t2; -- 5
SELECT COUNT(NAME) FROM t2; -- 4
sum
# sum函数 返回满足where条件的行的和 - 一般使用在数值列
select sum(列名), sum(列名)... from `table_name`
[where where_definition]
# 注意：
# 1.sum仅对数值起作用，对其他没有意义。
# 2.对多列求和，","号不能少。
# 演示sum函数的使用
-- 统计一个班级数学总成绩
SELECT SUM(math) FROM student;
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(chinese) AS math_total_score, SUM(english), SUM(math) FROM student;
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(chinese + english + math) FROM student;
-- 统计一个班级语文成绩平均分
SELECT SUM(chinese)/COUNT(chinese) FROM student;
SELECT SUM(`name`) FROM student;-- 对名字求和，没有意义
avg
# avg函数 返回满足where条件的一列的平均值.
select avg(列名), avg(列名)... from `table_name`
[where where_definition]
# avg的使用
-- 求一个班级数学平均分
SELECT AVG(math) FROM student;
-- 求一个班级总分平均分
SELECT AVG(math + chinese + english) FROM student;
max/min
# max/min函数 返回满足where条件的一列的最大/最小值
select max(列名), min(列名) from `table_name`
[where where_definition]
# max 和 min的使用
-- 求班级最高分和最低分(数值范围在统计中特别有用)
SELECT MAX(math + chinese + english), MIN(math + chinese + english) FROM student;
-- 求班级数学最高分和最低分
SELECT MAX(math) AS math_high_score, MIN(math) AS math_low_score FROM student;
分组统计
group by
# 使用group by 子句对列进行分组
select column1, column2... from `table_name`
group by `column`
# group by 用于对查询的结果分组统计
having
# 使用having 子句对分组后的结果进行过滤
select column1, column2... from `table_name`
group by `column`
having...
# having子句 用于限制分组显示结果.
-- 部门表
CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
);
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');
SELECT * FROM dept;
-- 员工表
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED ,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) ,/*红利 奖金*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
);
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);
SELECT * FROM emp;
-- 工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*工资级别*/
losal DECIMAL(17,2) NOT NULL,/*该级别最低工资*/
hisal DECIMAL(17,2) NOT NULL/*该级别最高工资*/
);
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
SELECT * FROM salgrade;
# group by + having 的使用
-- 如何显示每个部门的平均工资和最高工资？
SELECT AVG(sal), MAX(sal), deptno FROM emp
GROUP BY deptno;
-- 显示每个部门的每种岗位的平均工资和最低工资？
SELECT AVG(sal), MIN(sal), deptno, job FROM emp
GROUP BY deptno, job;
-- 显示平均工资低于2000的部门号和它的平均工资？// 别名->效率高
-- 写sql语句的思路：化繁为简，各个击破
SELECT deptno, AVG(sal) AS avg_sal FROM emp
GROUP BY deptno
HAVING avg_sal < 2000;
.
字符串函数
# 演示字符串函数的使用，使用emp表来演示
-- charset(str/column) 返回字符串的字符集
SELECT CHARSET(ename) FROM emp;
-- concat(str [,...]) 连接字符串
SELECT CONCAT(ename, ' 工作是 ', job) FROM emp;
-- instr(str, substring) 返回substring在str中出现的位置，没有返回0
-- dual 亚元表，系统表 可以作为测试表使用
SELECT INSTR('hanshunping', 'ping') FROM DUAL;
-- ucase(str) 转换成大写
SELECT UCASE(ename) FROM emp;
-- lcase(str) 转换成小写
SELECT LCASE(ename) FROM emp;
-- left(str, length) 从str中的左边起取length个字符
SELECT LEFT(ename, 2) FROM emp;
-- right(str, length) 从str中的右边起取length个字符
SELECT RIGHT(ename, 2) FROM emp;
-- length(str) str的长度【按照字节】
SELECT LENGTH(ename) FROM emp;
SELECT LENGTH('软柠柠') FROM emp; -- 9
SELECT LENGTH('rnn') FROM emp; -- 3
-- replace(str, search_str, replace_str) 在str中用replace_str替换search_str
-- 即：search_str -替换为-> replace_str
-- 如果是manager 就替换成 经理
# 注意 '' 来包裹字符串
SELECT ename, REPLACE(job, 'MANAGER', '管理') FROM emp;
-- strcmp(str1, str2) 逐字符比较两字符串大小
SELECT STRCMP('rnn', 'rnn') FROM DUAL;
-- substring(str, position [, length]) 从str的position开始【从1开始计算】，取length个字符
-- 从ename列的第一个位置开始取出2个字符
SELECT SUBSTRING(ename, 1, 2) FROM emp;
-- ltrim(str)、rtrim(str)、trim 去除前端空格或后端空格或两端空格
SELECT LTRIM(' rnn ') FROM DUAL;
SELECT RTRIM(' rnn ') FROM DUAL;
SELECT TRIM(' rnn ') FROM DUAL;
-- 以首字母小写的方式显示所有员工emp表的姓名
-- 方法1：
SELECT CONCAT(LCASE(SUBSTRING(ename, 1, 1)), SUBSTRING(ename, 2)) AS low_sup_case1 FROM emp ;
-- 方法2：
SELECT CONCAT(LCASE(LEFT(ename, 1)), SUBSTRING(ename, 2)) AS low_sup_case2 FROM emp ;
数学函数
# 演示数学相关函数
-- abs(num) 绝对值
SELECT ABS(-10) FROM DUAL;
-- bin(decimal_number) 十进制转二进制 decimal->十进制的
SELECT BIN(11) FROM DUAL;
-- ceiling(number2) 向上取整，得到比num2 大(或等于)的最小整数
SELECT CEILING(2.3) FROM DUAL;
-- conv(number2, from_base, to_base) 进制转换
-- 下面的含义是 'A' 是十六进制的 'A'，转成2进制输出
SELECT CONV('A', 16, 2) FROM DUAL;
-- floor(number2) 向下取整，得到比num2 小(或等于)的最大整数
SELECT FLOOR(-1.1) FROM DUAL;
-- format(number, decimal_places) 保留小数位数【四舍五入】 decimal_places->小数位
SELECT 3.6666 FROM DUAL;
SELECT FORMAT(3.666, 2) FROM DUAL;
-- hex(decimal_number) 转十六进制
SELECT HEX(10) FROM DUAL;
-- least(number1, number2 [, ...]) 求最小值
SELECT LEAST(10, 0, -1, -20) FROM DUAL;
-- mod(numerator, denominator) 求余 numerator->分子 denominator->分母
SELECT MOD(10, 3) FROM DUAL;
-- rand([seed]) 返回一个随机数，其范围为 0 <= v <= 1.0
-- 如果使用rand() 每次返回不同的随机数，在0 <= v <= 1.0
-- 如果使用rand(seed) 返回随机数，范围0 <= v <= 1.0，如果seed不变，该随机数也就不变了。
SELECT RAND() FROM DUAL;
SELECT RAND(6) FROM DUAL;
时间日期函数
基本介绍
# 日期时间相关函数
-- current_date() 当前日期 年月日
SELECT CURRENT_DATE() FROM DUAL;
-- current_time() 当前时间 时分秒
SELECT CURRENT_TIME() FROM DUAL;
-- current_timestamp() 当前时间戳 年月日+时分秒
SELECT CURRENT_TIMESTAMP() FROM DUAL;
-- last_day(日期) 返回该日期所在月份的最后一天
select last_day('2011-11-11') from dual;
-- now() 返回当前时间戳 年月日+时分秒
SELECT NOW() FROM DUAL;
-- date(datetime) 返回datetime的日期部分 年月日
SELECT DATE(NOW()) FROM DUAL;
-- date_add(date2, interval d_value d_type) 在date2中加上日期或时间
SELECT DATE_ADD(NOW(), INTERVAL 10 MINUTE) FROM DUAL;
-- date_sub(date2, interval d_value d_type) 在date2中减去一个日期或时间
SELECT DATE_SUB(NOW(), INTERVAL 10 MINUTE) FROM DUAL;
-- datediff(date1, date2) 两个日期差(结果是天),大的时间写在前面 即 date1>date2
SELECT DATEDIFF('2011-11-11', '1990-1-1') FROM DUAL;
-- timediff(date1, date2) 两个时间差(多少小时多少分钟多少秒)
SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL;
## 上面函数的细节说明
# interval 间隔
# 1.date_add()中的 interval后面可以是 year、month、day、hour、minute、second
# 2.date_sub()中的 interval后面可以是 year、month、day、hour、minute、second
# 3.datediff(date1, date2)得到的是天数，而且是date1-date2的天数，因此可以取负数
# 4.这四个函数的日期类型可以是 date, datetime或者timestamp
-- year|month|day|date(datetime) 获取对应的年|月|日
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT YEAR('2001-11-11') FROM DUAL;
-- unix_timestamp() 返回的是 1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
-- from_unixtime() 可以把一个unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过from_unixtime转换
-- 在实际开发中，我们经常使用int来保存一个unix时间戳，然后使用from_unixtime()进行
-- 转换，还是非常又实用价值的。
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y-%m-%d %H:%i:%s') FROM DUAL;
示例代码
-- 创建测试表
CREATE TABLE mes(
id INT,
content VARCHAR(30),
send_time DATETIME);
SELECT * FROM mes;
-- 添加一条记录
INSERT INTO mes VALUES(1, '北京新闻', CURRENT_TIMESTAMP);
INSERT INTO mes VALUES(1, '上海新闻', NOW());
INSERT INTO mes VALUES(1, '广州新闻', NOW());
# 案例
-- 显示所有新闻信息，发布日期只显示 日期，不显示时间
SELECT id, content, DATE(send_time) FROM mes;
-- 请查询在10分钟内发布的新闻
SELECT * FROM mes
WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();
SELECT * FROM mes
WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE);
-- 请在mysql的sql语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11', '1990-1-1') FROM DUAL;
-- 请用mysql的sql语句求出你活了多少天？[练习] 1986-11-11 出生
SELECT DATEDIFF(NOW(), '1986-11-11') FROM DUAL;
-- 如果能活到80岁，求出还能活多少天？[练习] 1986-11-11 出生
SELECT DATEDIFF(DATE_ADD('1986-11-11 10:10:11', INTERVAL 80 YEAR), NOW()) FROM DUAL;
加密和系统函数
基本介绍
# 演示加密函数和系统函数
-- user() 查询用户
-- 可以查看登录到mysql的有哪些用户，以及登录的IP
SELECT USER() FROM DUAL;-- 用户@IP地址
-- database() 查询当前使用数据库名称
SELECT DATABASE() FROM DUAL;
SELECT DATABASE();
-- md5(str) 为字符串算出一个md5 的32位的字符串，常用(用户密码)加密
-- root密码是 root -> 加密md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('root') FROM DUAL;
SELECT LENGTH(MD5('root')) FROM DUAL;
-- password(str) 加密函数, MySQL数据库的用户密码就是 password函数加密
SELECT PASSWORD('root') FROM DUAL;
-- select * from mysql.user \G 从原文密码str 计算并返回密码字符串
-- 通常用于对MySQL数据库的用户密码加密
-- mysql.user 表示 数据库.表
SELECT * FROM mysql.user;-- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
示例代码
-- 演示用户表，存放密码时，是md5加密后的
CREATE TABLE users (
id INT,
`name` VARCHAR(32) NOT NULL DEFAULT '',
pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO users VALUES(100, 'tom', 'root');
INSERT INTO users VALUES(100, 'jack', MD5('root'));
SELECT * FROM users;
SELECT * FROM users
WHERE `name` = 'jack' AND pwd = MD5('root');
if流程控制函数
基本介绍
# 流程控制语句
-- if(expr1, expr2, expr3) 如果expr1为True，则返回expr2;
-- 如果expr1为False，则返回expr3. 【一真大师】
SELECT IF(TRUE, '北京', '上海') FROM DUAL;
-- ifnull(expr1, expr2) 如果expr1不为空null，则返回expr1，否则返回expr2.
-- expr1 -> 可以是表达式，也可以是具体的值.
-- column_name -> 只能是存在的列名，不能是具体的值.
SELECT IFNULL('mary', '软柠柠吖') FROM DUAL;
-- select case
-- when expr1 then expr2
-- when expr3 then expr4
-- else expr5 end[类似多重分支]
-- 如果expr为True，则返回expr2; 如果expr3为True，则返回expr4; 都不满足返回expr5
SELECT CASE
WHEN TRUE THEN 'jack'
WHEN FALSE THEN 'tom'
ELSE 'mary' END
示例代码
# 案例
SELECT * FROM emp;
-- 1.查询emp表，如果comm是null，则显示0.0
## 注意：判断是否为空null, 要使用 is null
## 判断不为空null, 使用 is not null
SELECT ename, IF(comm IS NULL, 0.0, comm) FROM emp;
SELECT ename, IFNULL(comm, 0.0) FROM emp;
-- 2.如果emp表的job 是 clerk 则显示 职员，如果是manager 则显示 经理，如果是
-- salesman 则显示 销售人员，其他显示正常
## 一条语句最多只能出现一个; 分号！
## 因为job是具体的值，就不能使用is, 而是使用等于号=
SELECT ename, (SELECT CASE
WHEN job = 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售人员'
ELSE job END) AS job, job
FROM emp;
mysql表查询--加强
查询加强
where子句
-- 使用where子句
-- 如何查找 1992.1.1后入职的员工？
## 说明：在mysql中，日期类型可以直接比较，需要注意格式！
SELECT * FROM emp
WHERE hiredate >= '1992-01-01';
like模糊查询
-- 如何使用like操作符(模糊查询)
-- % ->表示0到多个任意字符
-- _ ->表示单个任意字符
-- 如何显示首字符为S的员工姓名和工资？
SELECT ename, sal FROM emp
WHERE ename LIKE 'S%';
-- 如何显示第三个字符为大写O的所有员工的姓名和工资？
SELECT ename, sal FROM emp
WHERE ename LIKE '__O%';
order by排序
-- 使用order by子句
-- 如何按照工资从低到高的顺序[升序]，显示雇员的信息？
SELECT * FROM emp
ORDER BY sal ASC;
-- 按照部门号升序而雇员的工资降序排序，显示雇员信息？
SELECT * FROM emp
ORDER BY deptno, sal DESC;
其它
-- 如何显示没有上级的雇员的情况
SELECT * FROM emp
WHERE mgr IS NULL;
-- 查询表结构
DESC emp;
分页查询
limit分页
# 分页查询
-- 1.按雇员的id号升序取出，每页显示3条记录，请分别显示 第1页、第2页、第3页
-- 2.基本语法 select ... limit start, rows
-- 表示从 start+1 行开始取，取出rows行，start从0开始计算
# 第1页
SELECT * FROM emp
ORDER BY empno
LIMIT 0, 3;
# 第2页
SELECT * FROM emp
ORDER BY empno
LIMIT 3, 3;
# 第3页
SELECT * FROM emp
ORDER BY empno
LIMIT 6, 3;
-- 3.公式!!
SELECT * FROM emp
ORDER BY empno
LIMIT 每页显示记录数 * (第几页 - 1), 每页显示记录数
-- 练习：按雇员的empno号降序取出，每页显示5条记录。请分别显示 第3页，第5页
# 第3页
SELECT * FROM emp
ORDER BY empno DESC
LIMIT 10, 5;
# 第5页
SELECT * FROM emp
ORDER BY empno DESC
LIMIT 20, 5;
分组增强
group by分组
# 增强group by 的使用
SELECT * FROM emp;
-- 使用分组函数和分组子句 group by
-- 1.显示每种岗位的雇员总数、平均工资
SELECT COUNT(job), FORMAT(AVG(sal), 2), job FROM emp
GROUP BY job;
-- 2.显示雇员总数，以及获得补助的雇员数
-- 思路：获得补助的雇员数 就是comm 列为 非null，就是count(列)，如果该列的值为null
-- 是不会被统计的
SELECT COUNT(*), COUNT(comm) FROM emp;
-- 扩展：统计没有获得补助的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL)) FROM emp;
SELECT COUNT(*), COUNT(*) - COUNT(comm) FROM emp;
-- 3.显示管理者的总人数
SELECT COUNT(DISTINCT mgr) FROM emp;
-- 4.显示雇员工资的最大差额
SELECT MAX(sal) - MIN(sal) FROM emp;
总结-顺序问题
# 数据分组的总结
-- 1.如果select语句同时包含有group by, having, limit, order by
-- 那么它们的顺序是 group by, having, order by, limit
SELECT column1, column2, column3.. FROM `table_name`
GROUP BY column_name
HAVING `condition`
ORDER BY column_name
LIMIT `start`, `rows`;
-- 案例
-- 请统计各个部门group by 的平均工资，并且是大于1000的 having，
-- 并且按照平均工资从高到低排序，order by
-- 取出前两行记录 limit
SELECT deptno, AVG(sal) AS avg_sal FROM emp
GROUP BY deptno
HAVING avg_sal > 1000
ORDER BY avg_sal DESC
LIMIT 0, 2;
mysql多表查询
基本介绍
- 多表查询是指基于两个和两个以上的表查询。在实际应用中，查询单个表可能不能满足你的需求。
示意图
.
多表查询
# 多表查询
-- 显示雇员名，雇员工资以及所在部门的名字【笛卡尔积】
# (1)当我们需要指定显示某个表的列时，需要 表名.列名
# (2)技巧注意：多表查询的条件不能少于 表的个数-1，否则会出现笛卡尔积
SELECT ename, sal, dname, emp.`deptno` FROM emp, dept
WHERE emp.`deptno` = dept.`deptno`;
-- 如何显示部门号为10的部门名、员工名和工资
SELECT dname, ename, sal FROM emp, dept
WHERE emp.`deptno` = dept.`deptno` AND dept.`deptno` = 10;
-- 显示各个员工的姓名，工资，及其工资的级别
SELECT ename, sal, grade FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal; -- 使用范围来过滤
-- 过滤条件不仅仅是 = 也有可能是一个范围，只要结果能返回true 或 false 就可以
-- 显示雇员名，雇员工资及所在部门的名称，按照部门降序排列
SELECT ename, sal, dname FROM emp, dept
WHERE emp.`deptno` = dept.`deptno`
ORDER BY dname DESC;
自连接
- 自连接是指在同一张表的连接查询【将同一张表看做两张表】
# 多表查询 自连接
-- 显示公司员工和他的上级的名字
-- 员工名字 在emp, 上级的名字 在emp
-- 员工和上级是通过 emp表的 mgr关联的
# 小结：自连接的特点
# (1)把同一张表当做两张表使用
# (2)需要给表取别名 表名 表别名
# (3)列名不明确，可以指定列的别名 列名 as 列的别名
SELECT `ename` AS '职员名', boss.`ename` AS '上级名'
FROM emp worker, emp boss
WHERE worker.`mgr` = boss.`empno`;
-- Not unique table/alias -> 非唯一表/别名
-- Column 'ename' in field list is ambiguous -> 字段列表中的“ename”列不明确
子查询
- 子查询是指嵌入在其他
sql语句中的select语句，也叫嵌套查询.
单行子查询
- 单行子查询是指只返回一行数据的子查询的语句
- 单行单列
多行子查询
- 多行子查询指返回多行数据的子查询 使用关键字
in - 多行单列
# 子查询演示
-- 单行子查询 单行使用= 或 in
-- 显示与SMITH同一部门的所有员工
SELECT deptno FROM emp
WHERE ename = 'SMITH';
SELECT ename FROM emp
WHERE deptno = (
SELECT deptno FROM emp
WHERE ename = 'SMITH');
-- 多行子查询 多行使用in
-- 如何查询和部门10的工作相同的雇员的姓名，岗位，工资，部门号，但是不含10号部门自己的雇员.
-- 1.查询到10号部门有哪些工作 - 需要distinct关键字去重
SELECT DISTINCT job FROM emp
WHERE deptno = 10
SELECT ename, job, sal, deptno FROM emp
WHERE job IN (
SELECT DISTINCT job FROM emp
WHERE deptno = 10) AND deptno != 10;
# 小结
# 注意分号的问题，一条语句只能有一个分号，千万小心括号里面的 子查询语句的后面 一定不要带分号
# 单行查询使用 = 或 in
# 多行查询只能使用 in
# 单行和多行查询 结果都是单列
-- 使用=会出现错误 Subquery returns more than 1 row -> 子查询返回超过1行
子查询临时表
- 可以将子查询当做一张临时表使用
# 子查询临时表
-- 查询 ecshop中各个类别中，价格最高的商品
SELECT goods_id, cat_id, goods_name, shop_price FROM ecs_goods
-- 1.得到各个类别中，价格最高的商品 max + group by 当做一个临时表
# 把子查询当做一个临时表可以解决很多复杂的查询
SELECT MAX(shop_price), cat_id FROM ecs_goods
GROUP BY cat_id;
-- 2.使用临时表查询
# cat_id需要指定那张表,因为有重复
SELECT goods_id, ecs_goods.cat_id, goods_name, shop_price
FROM ecs_goods, (
# cat_id不需要指定，没有重复
SELECT MAX(shop_price) AS max_price, cat_id FROM ecs_goods
GROUP BY cat_id
) temp
WHERE temp.cat_id = ecs_goods.cat_id
AND temp.max_price = shop_price;
all 和 any
- 在多行子查询中使用
all或any操作符
# all 和 any 的使用
-- 显示工资比部门30的所有员工的工资高的员工的姓名，工资和部门号
SELECT ename, sal, deptno FROM emp
WHERE sal > ALL(
SELECT sal
FROM emp
WHERE deptno = 30
)
# 等价于
SELECT ename, sal, deptno FROM emp
WHERE sal > (
SELECT MAX(sal)
FROM emp
WHERE deptno = 30
)
-- 显示工资比部门30的其中一个员工的工资高的员工的姓名，工资和部门号
SELECT ename, sal, deptno FROM emp
WHERE sal > ANY(
SELECT sal
FROM emp
WHERE deptno = 30
)
# 等价于
SELECT ename, sal, deptno FROM emp
WHERE sal > (
SELECT MIN(sal)
FROM emp
WHERE deptno = 30
)
多列子查询
- 多列子查询则是指查询返回多个列数据的子查询语句
# 多列子查询
-- 查询与ALLEN的部门和岗位完全相同的所有雇员(并不包含SIMTH本人)
-- 语法：(字段1, 字段2...) = (select 字段1, 字段2 from ...)
-- 1.查询SMITH的部门和岗位
SELECT deptno, job
FROM emp
WHERE ename = 'ALLEN';
-- 2.把上面的查询当做一个子查询来使用，并且使用多列子查询
SELECT ename
FROM emp
WHERE (deptno, job) = (
SELECT deptno, job
FROM emp
WHERE ename = 'ALLEN'
) AND ename != 'ALLEN'
子查询练习
# 子查询练习
-- 查找每个部门工资高于本部门平均工资的人的资料
-- 这里要用到数据查询的小技巧：把一个子查询当做一个临时表使用
-- 1.每个部门的部门号和平均工资
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno
-- 2.把上面的结果当做临时表，和 emp 进行多表查询
SELECT ename, sal, avg_sal, emp.`deptno`
FROM emp, (
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno) temp
WHERE emp.`deptno` = temp.deptno AND sal > avg_sal;
-- 查找每个部门工资最高的人的详细资料
-- 1.临时表 包含最高工资和部门号
SELECT deptno, MAX(sal) AS max_sal
FROM emp
GROUP BY deptno
SELECT ename, job, sal, emp.`deptno`
FROM emp, (
SELECT deptno, MAX(sal) AS max_sal
FROM emp
GROUP BY deptno
) temp
WHERE emp.`deptno` = temp.deptno AND emp.`sal` = temp.max_sal;
-- 查询每个部门的信息(包括：部门名，编号，地址)和人员数量
-- 1.临时表 包括部门号和人员数量 用emp构建
SELECT deptno, COUNT(deptno) AS counts
FROM emp
GROUP BY deptno
-- 2.查询
SELECT dname, dept.`deptno`, loc, counts
FROM dept, (
SELECT deptno, COUNT(deptno) AS counts
FROM emp
GROUP BY deptno
) temp
WHERE dept.`deptno` = temp.deptno;
-- 另一种写法
# 表名.* 表示将该表所有列都显示出来
# ,可以简化sql语句
# 在多表查询中，当多个表的列不重复时，才可以直接写列名
SELECT dept.*, counts AS '人数'
FROM dept, (
SELECT deptno, COUNT(deptno) AS counts
FROM emp
GROUP BY deptno
) temp
WHERE dept.`deptno` = temp.deptno;
表复制
- 自我复制数据(蠕虫复制)
- 有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。
# 表的复制
CREATE TABLE my_tab01 (
id INT,
`name` VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT);
SELECT * FROM my_tab01;
-- 演示如何自我复制
-- 1.先把emp 表的记录复制到 my_tab01
INSERT INTO my_tab01
(id, `name`, sal, job, deptno)
SELECT empno, ename, sal, job, deptno
FROM emp;
-- 2.自我复制 1->2->4->8->16 指数级增长
INSERT INTO my_tab01
SELECT * FROM my_tab01;
SELECT COUNT(*) FROM my_tab01;
表去重
# 表的去重
-- 如何删除掉一张表的重复记录
-- 1.先创建一张表 my_tab02
-- 2.让my_tab02 有重复的记录
# 下面的语句 把emp表的结构(列), 复制到 my_tab02
CREATE TABLE my_tab02 LIKE emp;
DESC my_tab02;
SELECT * FROM my_tab02;
INSERT INTO my_tab02
SELECT * FROM emp;
-- 3.考虑去重 my_tab02的记录
# 思路
# (1) 先创建一张临时表 my_tmp, 该表的结构 和 my_tab02一样
CREATE TABLE my_tmp LIKE my_tab02;
# (2) 把my_tab02的记录 通过distinct 关键字 处理后 把记录复制到 my_tmp
INSERT INTO my_tmp
SELECT DISTINCT * FROM my_tab02;
# (3) 清除掉 my_tab02 记录
DELETE FROM my_tab02;
# (4) 把 my_tmp 表的记录复制到 my_tab02
INSERT INTO my_tab02
SELECT * FROM my_tmp;
# (5) drop掉 临时表 my_tmp
DROP TABLE my_tmp;
SELECT * FROM my_tab02;
合并查询
- 有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号。
union,union all.
# 合并查询
-- 1.union all 就是将两个查询结果合并，不会去重
-- 该操作符用于取得两个结果集的并集。
-- 当使用该操作符时，不会取消重复行。
SELECT ename, sal, job
FROM emp
WHERE sal > 2500
UNION ALL
SELECT ename, sal, job
FROM emp
WHERE job = 'MANAGER'
-- union
-- 该操作符与 union all相似，但是会自动去掉结果集中重复行(自动去重)
SELECT ename, sal, job
FROM emp
WHERE sal > 2500
UNION
SELECT ename, sal, job
FROM emp
WHERE job = 'MANAGER'
外连接
- 前面我们学习的查询，是利用 where 子句对两张表或者多张表，形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示。
左外连接
- 如果左侧的表 完全显示，我们就说是左外连接.
- 左侧的表 完全显示：指的是左边的表 跟右边的表 即使没有匹配的记录，左侧的表的内容也全部显示(完全显示)
右外连接
- 如果右侧的表 完全显示，我们就说是右外连接.
# 外连接
# 左外连接语法：
SELECT ... FROM 表1 LEFT JOIN 表2 ON 条件 [表1：就是左表，表2：就是右表]
# 右外连接语法：
SELECT ... FROM 表1 RIGHT JOIN 表2 ON 条件 [表1：就是左表，表2：就是右表]
-- 创建 stu
CREATE TABLE stu (
id INT,
`name` VARCHAR(32));
INSERT INTO stu VALUES (1, 'jack'), (2, 'tom'), (3, 'kity'), (4, 'nono');
SELECT * FROM stu;
-- 创建 exam
CREATE TABLE exam(
id INT,
grade INT);
INSERT INTO exam VALUES (1, 56), (2, 76), (11, 8);
SELECT * FROM exam;
-- 使用左外连接
-- (显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id，成绩显示为空)
SELECT stu.`id`, `name`, grade
FROM stu LEFT JOIN exam
ON stu.`id` = exam.`id`;
-- 右外连接 (显示所有成绩，如果没有名字匹配，显示空)
-- 即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来
SELECT stu.`id`, `name`, grade
FROM stu RIGHT JOIN exam
ON stu.`id` = exam.`id`;
-- 列出部门名称和这些部门的员工信息(名字和工作)，同时列出那些没有员工的部门
-- 使用左外连接实现
SELECT dname, ename, job
FROM dept LEFT JOIN emp
ON dept.`deptno` = emp.`deptno`
ORDER BY dname;
-- 使用右外连接实现
SELECT dname, ename, job
FROM emp RIGHT JOIN dept
ON dept.`deptno` = emp.`deptno`
ORDER BY dname;
mysql约束
基本介绍
- 约束用于确保数据库的数据满足特定的商业规则。
- 在mysql中，约束包括：
not null、unique、primary key、foreign key和check五种。
primary key(主键)
- 用于唯一标识表行的数据，当定义主键约束后，该列的值不能重复。
# 基本语法
字段名 字段类型 primary key
# 细节
# 1.primary key不能重复而且不能为null
# 2.一张表最多只能有一个主键，但可以是复合主键
# 3.主键的指定方式 有两种
# (1)直接在字段名后指定：字段名 字段类型 primary key
# (2)在表定义最后写：primary key(列名1, 列名2...)
# 4.使用desc 表名，可以看到primary key的情况
# 5.提醒：在实际开发中，每个表往往都会设计一个主键
# 主键使用
CREATE TABLE t17
(id INT PRIMARY KEY,-- 表示id列是主键
`name` VARCHAR(32),
email VARCHAR(32));
-- 主键列的值是不可以重复的
INSERT INTO t17
VALUES (1, 'jack', 'jack@sohu.com');
INSERT INTO t17
VALUES (2, 'tom', 'tom@sohu.com');
# Duplicate entry '1' for key 'PRIMARY'
# 重复的数据 '1' 对于主键
INSERT INTO t17
VALUES (1, 'hsp', 'hsp@sohu.com');
SELECT * FROM t17;
# 细节
# 1.primary key不能重复而且不能为null
-- Column 'id' cannot be null
-- 列“id”不能为空
INSERT INTO t17
VALUES (NULL, 'hsp', 'hsp@sohu.com');
# 2.一张表最多只能有一个主键，但可以是复合主键(比如 id + name)
-- Multiple primary key defined
-- 定义了多个主键
CREATE TABLE t18-- 错误！！
(id INT PRIMARY KEY,-- 表示id列是主键
`name` VARCHAR(32) PRIMARY KEY,
email VARCHAR(32));
-- 演示复合主键 (id 和 name 做成复合主键)
CREATE TABLE t18
(id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(id, `name`) -- 这里就是复合主键
);
INSERT INTO t18
VALUES (1, 'hsp', 'hsp@sohu.com');
INSERT INTO t18
VALUES (1, 'jack', 'jack@sohu.com');
-- Duplicate entry '1-hsp' for key 'PRIMARY'
-- 重复的数据 '1-hsp' 对于主键
INSERT INTO t18
VALUES (1, 'hsp', 'xxx@sohu.com');
SELECT * FROM t18;
# 3.主键的指定方式 有两种
# (1)直接在字段名后指定：字段名 字段类型 primary key
CREATE TABLE t19
(id INT PRIMARY KEY,-- 表示id列是主键
`name` VARCHAR(32),
email VARCHAR(32));
# (2)在表定义最后写：primary key(列名1, 列名2...)
CREATE TABLE t20
(id INT,-- 表示id列是主键
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(id)
);
# 4.使用desc 表名，可以看到primary key的情况(约束情况)
DESC t18
not null(非空)
- 如果在列上定义了not null，那么当插入数据时，必须为列提供数据。
# 语法
字段名 字段类型 not null
unique(唯一)
- 当定义了唯一约束后，该列值是不能重复的。
# 语法
字段名 字段类型 unique
# 细节
# 1.如果没有指定not null，则unique字段可以有多个null
# 由于null值不是具体的值，因此每个null值均不同，可以添加多个null值
# 2.如果一个列(字段)，是 unique not null 使用效果类似 primary key
# 3.一张表可以有多个unique字段
# unique 的使用
CREATE TABLE t21
(id INT UNIQUE, -- 表示id列是不可以重复的
`name` VARCHAR(32),
email VARCHAR(32));
INSERT INTO t21
VALUES (1, 'jack', 'jack@sohu.com');
-- Duplicate entry 重复条目
INSERT INTO t21
VALUES (1, 'tom', 'tom@sohu.com');
SELECT * FROM t21;
-- unique使用细节
-- 1.如果没有指定not null，则unique字段可以有多个null
-- 由于null值不是具体的值，因此每个null值均不同，可以添加多个null值
INSERT INTO t21
VALUES (NULL, 'tom', 'tom@sohu.com');
-- 2.如果一个列(字段)，是 unique not null 使用效果类似 primary key
-- 3.一张表可以有多个unique字段
CREATE TABLE t22
(id INT UNIQUE, -- 表示id列是不可以重复的
`name` VARCHAR(32) UNIQUE, -- 表示 name列不能重复
email VARCHAR(32));
DESC t22;
foreign key(外键)
- 用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束。当定义外键约束后，要求外键列数据 必须在主表的主键列存在 或是为null。
# 语法
foreign key (本表/从表的字段名) references 主表名(主键名或unique字段名)
# 细节
# 1.外键指向的表的字段，要求是primary key 或者是 unique
# 2.表的类型是 innodb, 这样的表才支持外键
# 3.外键字段的类型要和主键字段的类型一致(长度可以不同)
# 4.外键字段的值，必须在主键字段中出现过，或者为null [前提是外键字段允许为null]
# 5.一旦建立主外键的关系，数据不能随意删除了.
# 6.即使此时的主表就是本表，也要写主表名(和本表名相同)，否则会报错.
# 7.两个括号都不能省略不写！！！！
# 外键演示
-- 创建 主表 my_class
CREATE TABLE my_class (
id INT PRIMARY KEY,-- 班级编号
`name` VARCHAR(32) NOT NULL DEFAULT ''
);
DROP TABLE my_class
-- 创建 从表 my_stu
CREATE TABLE my_stu (
id INT PRIMARY KEY,-- 学生编号
`name` VARCHAR(32) NOT NULL DEFAULT '',
class_id INT,-- 学生所在班级的编号
-- 下面指定外键关系
FOREIGN KEY (class_id) REFERENCES my_class(id));
-- 测试数据
INSERT INTO my_class
VALUES (100, 'java'), (200, 'web');
SELECT * FROM my_class;
INSERT INTO my_stu
VALUES (1, 'tom', 100);
INSERT INTO my_stu
VALUES (2, 'jack', 200);
# Cannot add or update a child row: a foreign key constraint fails
# 无法添加或更新子行：外键约束失败
INSERT INTO my_stu
VALUES (3, 'rnny', 300);
INSERT INTO my_stu
VALUES (4, 'hsp', NULL); -- 可以，没有写 not null
-- Cannot delete or update a parent row: a foreign key constraint fails
-- 无法删除或更新父行：外键约束失败
-- 只有把所有的引用关系都删除，才能删除或更新父行。
DELETE FROM my_class
WHERE id = 100;
SELECT * FROM my_stu;
check(检查)
- 用于强制行数据必须满足的条件，假定在
sal列上定义了check约束，并要求sal列值在10002000之间，如果不在10002000之间就会提示出错。 - 提示：Oracle 和 SQL server 均支持 check，但是MySQL5.7 目前还不支持check，只做语法校验，但不会生效。
- 基本语法
列名 类型 check (check条件)
# 细节
# 1.在mysql中实现check的功能，一般是在程序中控制，或者通过触发器完成。
# 2.括号不能省略
# check的使用
-- MySQL5.7 目前还不支持check，只做语法校验，但不会生效。
-- Oracle 和 SQL server，这两个数据库是真的生效.
-- 测试
CREATE TABLE t23 (
id INT PRIMARY KEY,
`name` VARCHAR(32),
sex VARCHAR(6) CHECK (sex IN ('男', '女')),
sal DOUBLE CHECK (sal > 1000 AND sal < 2000)
);
INSERT INTO t23
VALUES (1, 'jack', 'mid', 500);
SELECT * FROM t23;
设计商店表
# 商店售货系统表
-- 创建shop_db数据库
CREATE DATABASE shop_db;
USE shop_db;
SHOW TABLES;
-- 创建goods商品表
CREATE TABLE goods (
goods_id INT PRIMARY KEY,
goods_name VARCHAR(64) NOT NULL DEFAULT '',
unitprice DECIMAL(10, 2) NOT NULL DEFAULT 0
CHECK (unitprice BETWEEN 1.0 AND 9999.99),
category INT NOT NULL DEFAULT 0,
provider VARCHAR(64) NOT NULL DEFAULT ''
);
SELECT * FROM goods;
-- 创建customer客户表
CREATE TABLE customer (
customer_id CHAR(8) PRIMARY KEY,
`name` VARCHAR(64) NOT NULL DEFAULT '',
address VARCHAR(64) NOT NULL DEFAULT '',
email VARCHAR(32) UNIQUE NOT NULL,
sex ENUM('男', '女') NOT NULL,-- 这里使用的是枚举类型，这个是生效的
card_id CHAR(18)
);
SELECT * FROM customer;
DESC customer;
-- 创建purchase购买表
CREATE TABLE purchase (
order_id INT UNSIGNED PRIMARY KEY, -- 外键约束在后
customer_id CHAR(8) NOT NULL DEFAULT '', -- 外键约束在后
goods_id INT NOT NULL DEFAULT 0,
nums INT NOT NULL DEFAULT 0,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
);
SELECT * FROM purchase;
DESC purchase;
自增长
- 在某张表中，存在一个id列(整数)，我们希望在添加记录的时候，该列从1开始，自动增长.
# 语法
字段名 整数类型 primary key auto_increment
# 细节
-- 1.一般来说自增长是和primary key配合使用的.
-- 2.自增长也可以单独使用[但是需要配合一个unique]
-- 3.自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)
-- 4.自增长默认从1开始，你也可以通过如下命令修改
alter table 表名 auto_increment = 新的开始值;
-- 5.如果你添加数据时，给自增长字段(列) 指定了具体值，则以指定的具体值为准。
-- 如果指定了自增长，一般来说，就按照自增长的规则来添加数据。[去麦当劳里吃肯德基 找抽！]
# 给自增长的字段 添加值的方式
insert into `table_name`(字段1, 字段2...) values (null, '值'...)
-- 值1给字段2；值2给字段3
insert into `table_name`(字段2, 字段3...) values ('值1', '值2'...)
insert into `table_name` values (null, '值1',...)
- 示例代码：
# 自增长 的使用
-- 创建表
CREATE TABLE t24 (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR (32) NOT NULL DEFAULT '',
`name` VARCHAR (32) NOT NULL DEFAULT ''
) ;
DESC t24;
-- 测试自增长的使用
INSERT INTO t24
VALUES (NULL, 'tom@qq.com', 'tom');
SELECT * FROM t24;
INSERT INTO t24
VALUES (NULL, 'jack@qq.com', 'jack');
INSERT INTO t24 (email, `name`)
VALUES ('rnny@qq.com', 'rnny');
-- 修改默认的自增长开始值
CREATE TABLE t25 (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR (32) NOT NULL DEFAULT '',
`name` VARCHAR (32) NOT NULL DEFAULT ''
) ;
ALTER TABLE t25 AUTO_INCREMENT = 666;
INSERT INTO t25 (email, `name`)
VALUES ('rnny@qq.com', 'rnny');
SELECT * FROM t25;
INSERT INTO t25
VALUES (999, 'milan@qq.com', 'milan'); -- 指定自增长的值，无意义
INSERT INTO t25
VALUES (NULL, 'scott@qq.com', 'scott');-- 按照指定的值增长
mysql索引
索引优化速度
- 提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，查询速度就可能提高百倍千倍。
# 索引优化速度
-- 在没有创建索引时，我们的查询一条记录 耗时3.8s
SELECT * FROM emp
WHERE empno = 1234567;
-- 使用索引优化，体验索引的牛
-- 在没有创建索引前，emp.ibd 文件大小是 524M
-- 创建empno索引后，emp.ibd 文件大小是 655M [索引本身也会占用空间.]
-- 创建ename索引后，emp.ibd 文件大小是 827M
CREATE INDEX empno_index ON emp(empno);
-- 创建索引后，查询的速度如何？耗时0.009s
SELECT * FROM emp
WHERE empno = 1234578;
## 创建索引后，只对创建了索引的列有效，对其他列无效！！
# 是不是建立一个索引就能解决所有的问题？不是
# ename上没有建立索引会怎样？ 查询耗时：4s 优化后耗时：0.016s
SELECT * FROM emp
WHERE ename = 'RipZco';
-- 在ename上创建索引
CREATE INDEX ename_index ON emp(ename);
# 根据ename和empno创建索引
CREATE INDEX ename_empno_index ON emp(ename, empno);
索引的原理
- 没有索引为什么会慢？因为全表扫描。
- 使用索引为什么会快？形成一个索引的数据结构，比如二叉树。
- 索引的代价：
- 磁盘占用。
- 对DML(insert、delete、update)语句的效率影响。
- 在我们项目中，select[90%]多 还是update，delete，insert[10%]操作多？
- 索引原理示意图：
.
索引的类型
-
主键索引：主键自动的为主索引 (类型 Primary key)
-
唯一索引（unique）
-
普通索引（index）
-
全文索引（fulltext）【适用于MyISAM】
一般开发，不使用mysql自带的全文索引，而是使用：全文搜索 Solr 和 ElasticSearch(ES)
create table t1 (
id int primary key,-- 主键，同时也是索引，称为主键索引.
name varchar(32) unique,-- 唯一性约束，同时也是索引，称为unique索引.
age int);
索引使用
添加索引
# 添加普通索引(2种方式) ---> 普通
create index 索引名称 on 表名(列名1, 列名2...);
或
alter table 表名 add index [索引名称] (索引列名);
# 添加唯一索引(3种方式) ---> 唯一
创建表的时候直接指定 unique
或
create unique index 索引名称 on 表名(列名1, 列名2...);
或
alter table 表名 add unique index [索引名称] (索引列名);
# 添加主键索引(2种方式) ---> 主键
创建表的时候直接指定 primary key
或
alter table 表名 add primary key(列名);
# 如何选择？
# 1.如果某列的值 是不会重复的，则优先考虑使用unique索引(快)，否则使用普通索引。
# 注意：
# Non_unique为0 -> 是唯一索引；为1 -> 不是唯一索引
删除索引
# 删除索引[普通、唯一、主键都通用]
drop index 索引名称 on 表名;
或
alter table 表名 drop index 索引名称;
# 删除主键索引[特别方式]
alter table 表名 drop primary key;
修改索引
# 修改索引
-- 先删除旧索引，再添加新的索引
查询索引
# 查询索引(四种方式)
show index from 表名;
show indexes from 表名;
show keys from 表名;
desc 表名;-- 不推荐，显示的信息不详细
示例代码
# mysql的索引使用
### 添加索引
-- 创建索引
CREATE TABLE t25 (
id INT,
`name` VARCHAR(32),
email VARCHAR(32)
);
-- 查询表是否有索引
SHOW INDEXES FROM t25;
-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t25(id);
-- 添加普通索引
CREATE INDEX id2_index ON t25(id);
# 或
ALTER TABLE t25 ADD INDEX id3_index(id);
# 如何选择？
# 1.如果某列的值 是不会重复的，则优先考虑使用unique索引(快)，否则使用普通索引。
-- 添加主键索引
ALTER TABLE t25 ADD PRIMARY KEY(email);
### 删除索引
-- 删除索引
DROP INDEX id4_index ON t25;
DROP INDEX `primary` ON t25;
-- 删除主键索引
ALTER TABLE t25 DROP PRIMARY KEY;
### 修改索引
-- 先删除，再添加新的索引
### 查询索引
SHOW INDEX FROM t25;
SHOW INDEXES FROM t25;
SHOW KEYS FROM t25;
DESC t25;-- 不推荐，显示的信息不详细
应用场景
# 小结：哪些列上适合使用索引？
-- 1.较频繁的作为查询条件字段应该创建索引，例如编号
select * from emp where empno = 1;
-- 2.唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件，例如性别
select * from emp where sex = '男';
-- 3.更新非常频繁的字段不适合创建索引
select * from emp where login_count = 1;
-- 4.不会出现在where子句中的字段不该创建索引
mysql事务
什么是事务
- 事务用于保证数据的一致性，它由一组相关的
DML语句组成，该组的DML(insert、delete、update)语句当做一个整体，要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性。
事务和锁
- 当执行事务操作时(DML语句)，mysql会在表上加锁，防止其它用户修改表的数据.这对用户来讲是非常重要的。
事务操作
5个重要的事务操作
# mysql数据库控制台事务的几个重要操作
start transaction -- 开始一个事务
savepoint 保存点名 -- 设置保存点
rollback to 保存点名 -- 回退(回滚)事务到保存点
rollback -- 回退(回滚)全部事务
commit -- 提交事务，所有的操作生效，不能回退
示意图
.
回退事务
- 在介绍回退事务前，先介绍一下保存点(savepoint).保存点是事务中的点，用于取消部分事务。当结束事务时(commit)，会自动的删除该事务所定义的所有保存点.当执行回退事务时，通过指定保存点可以回退到指定的点。
提交事务
- 使用commit语句可以提交事务。当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁、数据生效。当使用commit语句结束事务后，其它会话【其它连接】将可以查看到事务变化后的新数据【所有数据就正式生效】。
示例代码
# 事务的具体操作
-- 1.创建一张测试表
CREATE TABLE t27 (
id INT,
`name` VARCHAR(32)
);
-- 2.开始事务
START TRANSACTION;
-- 3.设置保存点
SAVEPOINT a;
-- 4.执行DML操作
INSERT INTO t27 VALUES (100, 'rnny');
SELECT * FROM t27;
SAVEPOINT b;
-- 执行DML操作
INSERT INTO t27 VALUES (200, 'jack');
-- 5.回退到 b
ROLLBACK TO b;
-- 6.继续回退
ROLLBACK TO a;
-- 如果这样，表示直接回退到事务开始的状态
ROLLBACK;
事务细节
- 如果不开始事务，默认情况下，
DML操作是自动提交的，不能回滚。 - 如果开始一个事务，你没有创建保存点. 你可以执行
rollback，默认就是回退到你事务开始的状态。 - 你也可以在这个事务中(还没有提交时)，创建多个保存点.比如：
savepoint aaa;
执行DML;
savepoint bbb;
- 你可以在事务没有提交前，选择回退到哪个保存点.
- mysql的事务机制需要
innodb的存储引擎才可以使用，myisam不好使。 - 开始一个事务
start transaction;
或
set autocommit = off;
4种隔离级别
事务隔离级别介绍
- 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
- 如果不考虑隔离性，可能会引发如下问题：
- 脏读
- 不可重复读
- 幻读
- 查看事务隔离级别：查看当前会话隔离级别
select @@tx_isolation
脏读(dirty read)
- 当一个事务读取另一个事务尚未提交的改变(insert、delete、update)时，产生脏读。
不可重复读(nonrepeatable read)
- 同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
幻读(phantom read)
- 同一查询在同一事务中多次进行，由于其它提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。
事务隔离级别
- 概念：MySQL隔离级别定义了事务与事务之间的隔离程度。
| MySQL隔离级别(4种) | 脏读 | 不可重复读 | 幻读 | 加锁读 |
|---|---|---|---|---|
| 读未提交(Read uncommitted) | ✔️ | ✔️ | ✔️ | 不加锁 |
| 读已提交(Read committed) | ❌ | ✔️ | ✔️ | 不加锁 |
| 可重复读(Repeatable read) | ❌ | ❌ | ❌ | 不加锁 |
| 可串行化(Serializable) | ❌ | ❌ | ❌ | 加锁 |
- 说明：✔️可能出现，❌不会出现。
查看or设置隔离级别
# 1.查看当前会话隔离级别
select @@tx_isolation;
# 2.查看系统当前隔离级别
select @@global.tx_isolation;
# 3.设置当前会话隔离级别
set session transaction isolation level repeatable read;
# 4.设置系统当前隔离级别
set global transaction isolation level 你设置的级别;
# 5.mysql默认的事务隔离级别是 repeatable read，一般情况下，没有特殊要求，没有必要修改(因为该级别可以满足绝大部分项目需求)
# 6.全局修改，修改my.ini配置文件，在最后加上
-- 可选参数有：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
[mysqld]
transaction-isolation = REPEATABLE-READ
### 注意：上面的语句最后不能加分号，加上分号就会启动不了mysql服务。
事务的ACID特性
- 原子性(Atomicity)
- 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
- 一致性(Consistency)
- 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
- 隔离性(Isolation)
- 事物的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
- 持久性(Durability)
- 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
mysql表类型和存储引擎
基本介绍
temporary 临时.
- MySQL的表类型由存储引擎(Storage Engines)决定，常用包括MyISAM、innoDB、Memory等。
- MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoDB.
- 这六种又分为两类，一类是"事务安全型"(transaction-safe)，比如：InnoDB；其余都属于第二类，称为"非事务安全型"(non-transaction-safe)，比如：myisam和memory
- 显示当前数据库支持的存储引擎：
show engines;
主要的存储引擎/表类型特点
| 特点 | Myisam | InnoDB | Memory |
|---|---|---|---|
| 批量插入的速度 | 高 | 低 | 高 |
| 事务安全 | ❌ | 支持 | ❌ |
| 锁机制 | 表锁 | 行锁 | 表锁 |
| 存储限制 | 没有 | 64TB | 有 |
| 支持外键 | ❌ | 支持 | ❌ |
细节说明
- 这里重点介绍三种：MyISAM、InnoDB、Memory.
- MyISAM 不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求。
- InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些 并且 会占用更多的磁盘空间以保留数据和索引。
- Memory存储引擎使用存在内存中的内容来创建表。每个Memory表只实际对应一个磁盘文件。Memory类型的表访问非常快，因为它的数据是放在内存中的，并且默认使用hash索引。但是一旦MySQL服务关闭(或重启)，表中的数据就会丢失掉，表的结构还在。
三种存储引擎表使用案例
-- 表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES;
-- innodb存储引擎
-- 1.支持事务 2.支持外键 3.支持行级锁
-- myisam存储引擎
-- 1.添加速度快 2.不支持外键和事务 3.支持表级锁
CREATE TABLE t29 (
id INT,
`name` VARCHAR(32)
) ENGINE MYISAM;
START TRANSACTION;
SAVEPOINT t1;
INSERT INTO t29 VALUES (100, 'jack');
SELECT * FROM t29;
ROLLBACK TO t1;
-- memory存储引擎
-- 1.数据存储在内存中【重启/关闭MySQL服务，数据丢失 但是表结构还在】
-- 2.执行速度很快(没有IO读写) 3.默认支持hash索引
CREATE TABLE t30 (
id INT,
`name` VARCHAR(32)
) ENGINE MEMORY;
INSERT INTO t30 VALUES (100, 'jack');
INSERT INTO t30 VALUES (200, 'tom');
INSERT INTO t30 VALUES (300, 'rnny');
DESC t30;
SELECT * FROM t30;
# 指令修改存储引擎
alter table `t30` engine = innodb;
如何选择表的存储引擎
- 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快。
- 如果需要支持事务，选择InnoDB.
- Memory存储引擎就是将数据存储在内存中，由于没有磁盘IO的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法：用户的在线状态)
Memory的使用示意图
.
修改存储引擎
alter table 表名 engine = 存储引擎;
视图(view)
需求
- emp 表的列信息有很多，有些信息是个人重要信息(比如：sal，comm，mgr，hiredate)，如果我们希望某个用户只能查询emp表的(empno, ename, job和deptno)信息，有什么办法？=》视图.
基本概念
- 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表(基表)。
- 视图不创建数据，类比java引用。
视图原理示意图
.
基本使用
# 创建视图
create view 视图名 as select语句;
# 修改视图--更新成新的视图
alter view 视图名 as select语句;
# 显示视图的创建指令
show create view 视图名;
# 删除视图
drop view 视图名1, 视图名2...;
# 视图的使用
-- 创建一个视图emp_view01, 只能查询emp表的(empno, ename, job 和deptno) 信息
-- 创建视图
CREATE VIEW emp_view01
AS
SELECT empno, ename, job, deptno FROM emp;
-- 查看视图
DESC emp_view01;
SELECT * FROM emp_view01;
SELECT ename FROM emp_view01;
-- 更新成新的视图
ALTER VIEW emp_view01
AS
SELECT ename, job, deptno FROM emp;
-- 显示视图的创建语句
SHOW CREATE VIEW emp_view01;
-- 删除视图
DROP VIEW emp_view01;
细节讨论
- 创建视图后，到数据库(
D:\mysql5.7\mysql-5.7.19-winx64\data\hsp_db02)去看，对应视图只有一个视图结构文件(形式：视图名.frm), 并没有数据文件。 - 视图的数据变化会影响到基表，基表的数据变化也会影响到视图【insert、delete、update】。
- 视图中可以再使用视图，数据仍然来自基表。
视图最佳实际
- 安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
- 性能。关系数据库的数据常常会分表存储，使用外键建立这些表之间的关系。这时，数据库查询通常会用到连接(join)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用
join查询数据。 - 灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
视图课堂练习
# 视图课后练习
-- 针对emp, dept 和 salgrade三张表，创建一个视图emp_view03，
-- 可以显示雇员编号，雇员名，雇员部门名称和薪水等级
-- 【即使用三张表，构建一个视图】
SELECT empno, ename, dname, grade
FROM emp, dept, salgrade
WHERE emp.`deptno` = dept.`deptno`
AND (sal BETWEEN salgrade.`losal` AND salgrade.`hisal`);
CREATE VIEW emp_view03
AS
SELECT empno, ename, dname, grade
FROM emp, dept, salgrade
WHERE emp.`deptno` = dept.`deptno`
AND (sal BETWEEN salgrade.`losal` AND salgrade.`hisal`);
SELECT * FROM emp_view03;
DESC emp_view03;
mysql用户管理
mysql用户
mysql.user表说明
authentication 身份验证、认证.
- MySQL中的用户，都存储在系统数据库mysql中的user表里，即
mysql.user表
.
- 其中user表的重要字段说明：
- host：允许登录的"位置"，localhost表示该用户只允许本机登录，也可以指定ip地址，比如：192.168.1.100
- user：用户名。
- authentication_string：密码，是通过mysql的password()函数加密之后的密码。
查看用户列表
# 查看用户列表
select *
from mysql.user;
创建用户
# 创建用户
create user '用户名'@'允许登录位置' identified by '密码';
# 说明：创建用户，同时指定密码
删除用户
# 删除用户
drop user '用户名'@'允许登录位置';
用户修改密码
# 修改自己的密码
set password = password('密码');
# 修改他人的密码(需要有修改用户密码权限)
set password for '用户名'@'登录位置' = password('密码');
示例代码
# mysql用户的管理
-- 原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的MySQL操作权限。
-- 所以，MySQL数据库管理人员(root)，根据需要创建不同的用户，赋给相应的权限，
-- 供开发人员使用。
-- 1.创建新的用户
# 解读：(1)'rnny'@'localhost' 表示用户的完整信息
# 'rnny' 用户名；'localhost' 登录的IP
# (2)'123' 密码，但是注意 存放到mysql.user表时，是password('123') 加密后的密码
CREATE USER 'rnny'@'localhost' IDENTIFIED BY '123';
SELECT PASSWORD(123);
SELECT `host`, `user`, `authentication_string`
FROM mysql.user;
-- 2.删除用户
DROP USER 'rnny'@'localhost';
# 修改自己的密码
SET PASSWORD = PASSWORD('root');
# 修改他人的密码(需要有修改用户密码权限)
SET PASSWORD FOR 'rnny'@'localhost' = PASSWORD('123');
用户管理示意图
.
mysql中的权限
查看用户权限列表
# 查看用户q列表
select *
from mysql.user;
root给用户授权
# 基本语法
grant 权限列表 on 数据库名.表名 to '用户名'@'登录位置' [identified by '密码'];
# 说明：
# 1.权限列表，多个权限用逗号分开
grant select on...
grant select, delete, create on...
grant all【privileges】 on... -- 表示赋予该用户在该对象上的所有权限
# 2.特别说明
# *.* -> 代表本系统中的所有数据库的所有对象(表，视图，存储过程)
# 库名.* -> 表示某个数据库中的所有数据对象(表，视图，存储过程等)
# 3.identified by 可以省略，也可以写出.
# (1)如果用户存在，就是修改该用户的密码.
# (2)如果该用户不存在，就是创建该用户！
root回收用户权限
# 基本语法
revoke 权限列表 on 数据库名.表名 from '用户名'@'登录位置';
权限生效指令
-- 如果权限没有生效，可以执行下面命令
# 基本语法：
flush privileges;
用户管理+权限练习题
# 用户管理 + 权限练习题
-- 1.创建一个用户rnny，密码 123，并且只可以本地登录，不让远程登录MySQL
CREATE USER 'rnny'@'localhost' IDENTIFIED BY '123';
-- 查看用户列表
SELECT * FROM mysql.`user`;
-- 2.创建库和表 testdb 下的 news表，要求：使用root用户创建
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE news (
id INT,
content VARCHAR(64)
);
INSERT INTO news VALUES (1, 'rnny');
INSERT INTO news VALUES (2, 'scott');
SELECT * FROM news;
-- 3.给用户分配查看news表和添加数据的权限
GRANT SELECT, INSERT
ON testdb.`news`
TO 'rnny'@'localhost';
-- 4.增加权限
GRANT UPDATE
ON testdb.`news`
TO 'rnny'@'localhost';
-- 5.修改密码为 abc，要求：使用root用户完成
SET PASSWORD FOR 'rnny'@'localhost' = PASSWORD('rnny');
-- 6.演示回收权限
-- 回收rnny用户 在testdb.news表的所有权限
REVOKE ALL ON testdb.news FROM 'rnny'@'localhost';
REVOKE SELECT, UPDATE, INSERT ON testdb.news FROM 'rnny'@'localhost';
-- 7.使用root用户删除你的用户
DROP USER 'rnny'@'localhost';
细节说明
-
在创建用户的时候，如果不指定Host，则默认为%，%表示所有ip都有连接权限【可以远程登录】。
create user 用户名; -
也可以这样指定
# 表示用户在 192.168.1.* 的ip可以登录mysql create user '用户名'@'192.168.1.*'; -
在删除用户的时候，如果host不是%，则需要明确指定：'用户名'@'host值'
# 默认就是(等价于) drop user '用户名'@'%' drop user '用户名'; # 删除指定ip的用户，必须明确指定 '用户名'@'host值' drop user '用户名'@'192.168.1.*';
数据库问题合集
1、解决MySQL删除数据后，自增id的断层问题
- 解决：先删除id这个字段，再把id这个字段按建表时的要求添加到首位
alter table students drop id;
alter TABLE students add id int(11) primary key auto_increment FIRST;
- 这种方法可以解决任意情况下的断层，可以用作动态更新id序列.
2、
JDBC和数据库连接池
JDBC概述
基本介绍
JDBC为访问不同的数据库提供了统一的接口，为使用者屏蔽了细节问题。Java程序员使用JDBC，可以连接任何提供了JDBC驱动程序的数据库系统，从而完成对数据库的各种操作。JDBC的基本原理
.
- 说明：JDBC是Java提供一套用于数据库操作的接口API，Java程序员只需要面向这套接口编程即可。不同的数据库厂商，需要针对这套接口，提供不同的实现。
JDBC模拟实现
// JDBC接口
public interface JDBCInterface {
// 连接
Object getConnection();
// crud
void crud();
// 关闭
void close();
}
// MySQL
public class MysqlJDBCImpl implements JDBCInterface {
@Override
public Object getConnection() {
System.out.println("得到MySQL的连接");
return null;
}
@Override
public void crud() {
System.out.println("完成MySQL的crud");
}
@Override
public void close() {
System.out.println("关闭MySQL");
}
}
// Oracle
public class OracleJDBCImpl implements JDBCInterface {
@Override
public Object getConnection() {
System.out.println("得到Oracle的连接");
return null;
}
@Override
public void crud() {
System.out.println("完成Oracle的crud");
}
@Override
public void close() {
System.out.println("关闭Oracle");
}
}
// 测试类
public class TestJDBC {
public static void main(String[] args) {
// 完成对MySQL的操作
JDBCInterface jdbcInterface = new MysqlJDBCImpl();
// 通过接口来调用实现类【动态绑定】
jdbcInterface.getConnection();
jdbcInterface.crud();
jdbcInterface.close();
System.out.println("==========");
// 完成对Oracle的操作
jdbcInterface = new OracleJDBCImpl();
// 通过接口来调用实现类【动态绑定】
jdbcInterface.getConnection();
jdbcInterface.crud();
jdbcInterface.close();
}
}
JDBC API
- JDBC API 是一系列的接口，它统一和规范了应用程序与数据库的连接、执行SQL语句，并得到返回结果等各类操作，相关类和接口在java.sql 与 javax.sql.
.
JDBC程序编写步骤
- 注册驱动 - 加载Driver类
- 获取连接 - 得到Connection
- 执行增删改查 - 发送SQL给MySQL执行
- 释放资源 - 关闭相关连接
示例代码
public class JDBC01 {
public static void main(String[] args) {
// 前置工作：在项目下创建一个文件夹比如 libs
// 将 mysql.jar 拷贝到该目录下，右键 选择 Add as library... 加入到项目中
try {
// 1、注册驱动
// 创建driver对象
Driver driver = new Driver();
// 2、获取连接
// 解读
// (1) jdbc:mysql:// 规定好的(两个冒号:), 表示协议, 表示通过jdbc的方式连接mysql
// (2) localhost 主机, 也可以是ip地址
// (3) 3306 表示mysql监听的端口
// (4) hsp_db02 表示连接到MySQL DBMS 的哪个数据库
// (5) MySQL的连接本质就是前面学过的socket连接
String url = "jdbc:mysql://localhost:3306/hsp_db02";
// 将 用户名和密码放入到Properties 对象中
Properties properties = new Properties();
// 说明 user 和 password 是规定好的key值，后面的value值根据实际情况写.
properties.setProperty("user", "root");// 用户
properties.setProperty("password", "root");// 密码
Connection connect = driver.connect(url, properties);
// 3、执行增删改查
// 增
// String sql = "insert into actor values(null, 'Rnny', '男', '2001-01-01', '888')";
// 改
// String sql = "update actor set name = '软柠柠吖' where id = 1";
// 删
String sql = "delete from actor where id = 1";
// statement 用于执行静态SQL语句并返回其生成的结果对象
Statement statement = connect.createStatement();
// 如果是 DML语句，返回的就是影响的行数
int rows = statement.executeUpdate(sql);
System.out.println(rows > 0 ? "成功" : "失败");
// 4、释放资源
statement.close();
connect.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
获取数据库连接的五种方式
方式一：静态加载
@Test
public void connect01() throws SQLException {
// 1、注册驱动 - 获取Driver实现类对象
// 直接使用com.mysql.jdbc.Driver(), 属于静态加载，灵活性差，依赖强
Driver driver = new com.mysql.jdbc.Driver();
// 2、获取连接
String url = "jdbc:mysql://localhost:3306/hsp_db02";
// 将 用户名和密码放入到Properties 对象中
Properties properties = new Properties();
// 说明 user 和 password 是规定好的key值，后面的value值根据实际情况写.
properties.setProperty("user", "root");// 用户
properties.setProperty("password", "root");// 密码
Connection connect = driver.connect(url, properties);
// com.mysql.jdbc.JDBC4Connection@27ddd392
System.out.println(connect);
}
方式二：反射动态加载
@Test
public void connect02() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
// 1、注册驱动 - 使用反射加载Driver类，动态加载，更加的灵活，减少依赖性
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) aClass.newInstance();
// 2、获取连接
String url = "jdbc:mysql://localhost:3306/hsp_db02";
// 将 用户名和密码放入到Properties 对象中
Properties properties = new Properties();
// 说明 user 和 password 是规定好的key值，后面的value值根据实际情况写.
properties.setProperty("user", "root");// 用户
properties.setProperty("password", "root");// 密码
Connection connect = driver.connect(url, properties);
// com.mysql.jdbc.JDBC4Connection@27ddd392
System.out.println(connect);
}
方式三：DriverManager
@Test
// 方式3：使用DriverManager 替代 Driver 进行统一管理
public void connect03() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
// 使用反射加载Driver
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver dirver = (Driver) aClass.newInstance();
// 创建url 和 user 和 password
String url = "jdbc:mysql://localhost:3306/hsp_db02";
String user = "root";
String password = "root";
DriverManager.registerDriver(dirver);// 注册Driver驱动
Connection connection = DriverManager.getConnection(url, user, password);
// com.mysql.jdbc.JDBC4Connection@19e1023e
System.out.println(connection);
}
方式四：自动完成注册 -- 推荐使用.
@Test
// 方式4：使用Class.forName 自动完成注册驱动，简化代码
// 这种方式获取连接是使用最多的，推荐使用
public void connect04() throws ClassNotFoundException, SQLException {
// 使用反射加载了 Driver类
// 在加载 Driver类时，完成注册
/*
源码解读：
1、静态代码块，在类加载时，会执行一次.
2、DriverManager.registerDriver(new Driver());// 注册驱动
3、因此注册driver的工作已经完成
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
*/
Class.forName("com.mysql.jdbc.Driver");
// 创建url 和 user 和 password
String url = "jdbc:mysql://localhost:3306/hsp_db02";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
// com.mysql.jdbc.JDBC4Connection@27ddd392
System.out.println(connection);
}
- 提示
- mysql驱动5.1.6可以无需Class.forName("com.mysql.jdbc.Driver");
- 从jdk1.5以后使用了jdbc4，不再需要显示调用Class.forName() 注册驱动而是自动调用驱动，去jar包下META-INF\services\java.sql.Driver文本中的类名称去注册
- 建议还是写上 Class.forName("com.mysql.jdbc.Driver"), 更加明确.
方式五：配置文件 - 开发使用.
代码
@Test
// 方式5、在方式4的基础上改进，增加配置文件，让连接mysql更加灵活
public void connect05() throws IOException, ClassNotFoundException, SQLException {
// 通过Properties对象获取配置文件的信息
Properties properties = new Properties();
InputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties");
properties.load(inputStream);
// 获取相关的值
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
// 建议写上，更加明确
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
// com.mysql.jdbc.JDBC4Connection@27ddd392
System.out.println(connection);
}
jdbc.properties
user=root
password=root
url=jdbc:mysql://localhost:3306/hsp_db02
driver=com.mysql.jdbc.Driver
ResultSet[结果集]
基本介绍
- 表示数据库结果集的数据表，通常通过执行查询数据库的语句(select语句)生成。
- ResultSet对象保持一个光标指向其当前数据行。最初，光标位于第一行之前。
- next方法将光标移动到下一行，并且由于在ResultSet对象中没有更多的行时返回false，因此可以在while循环中使用循环来遍历结果集。
示例代码
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-07
* 演示select 语句返回ResultSet，并取出结果
*/
public class ResultSet01 {
public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
// 通过Properties对象获取配置文件的信息
Properties properties = new Properties();
properties.load(ClassLoader.getSystemResourceAsStream("jdbc.properties"));
// 获取相关的值
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
// 1、加载驱动，建议写上
Class.forName(driver);
// 2、获取连接
Connection connection = DriverManager.getConnection(url, user, password);
// 3、得到statement
Statement statement = connection.createStatement();
// 4、组织SQL
String sql = "select id, name, sex, borndate from actor";
// 执行给定的SQL语句，该语句返回单个ResultSet对象
/*
+----+------+-----+---------------------+
--resultSet--> | id | name | sex | borndate |
+----+------+-----+---------------------+
| 1 | rnny | 男 | 2001-11-11 00:00:00 |
| 2 | RNNY | 男 | 2001-11-11 00:00:00 |
+----+------+-----+---------------------+
表的结果 <---> ResultSet
老韩阅读resultSet对象的结构 - debug源码
utf-8 一个汉字占3个字节
2 个汉字占3*2=6个字节
一个字母占1个字节(ASCII)
2 个字母占1*2=2个字节
一个数字占1个字节(ASCII)
2 个数字占1*2=2个字节
*/
ResultSet resultSet = statement.executeQuery(sql);
// 5、使用while循环取出数据
while (resultSet.next()) {// 让光标向后移动，如果没有更多行，则返回false
int id = resultSet.getInt(1);// 获取该行第1列
String name = resultSet.getString(2);// 获取该行第2列
String sex = resultSet.getString(3);
Date borndate = resultSet.getDate(4);
System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate);
}
// 6、关闭连接
resultSet.close();
statement.close();
connection.close();
}
}
ResultSet底层结构图
.
Statement - 接口
基本介绍
-
Statement对象 用于执行静态SQL语句并返回其生成的结果对象。
-
在连接建立后，需要对数据库进行访问，执行 命令或是SQL语句，可以通过
- Statement【存在SQL注入, 不能用, 面试的时候会问】
- PreparedStatement【预处理】
- CallableStatement【存储过程】
-
Statement对象执行SQL语句，存在SQL注入风险.
. -
SQL注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的SQL语句段或命令，恶意攻击数据库。
# sql注入 # 用户名： 1' or # 万能密码： or '1' = '1 -
要防范SQL注入，只要用PreparedStatement(从Statement扩展而来)取代Statement就可以了。
PreparedStatement
基本介绍
-
PreparedStatement 执行的 SQL 语句中的参数用问号(?, 也叫占位符)来表示，调用PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数是要设置的SQL语句中的参数(问号)的索引(从 1 开始)，第二个是设置的SQL语句中的参数的值.
String sql = "select * from admin where name = ? and password = ?" -
调用executeQuery()：返回ResultSet对象.
-
调用executeUpdate()：执行更新，包括增、删、修改.
- 注意：这里executeQuery() 和 executeUpdate() 的参数列表都为空，不能加上参数，加上就错了！！
预处理好处
- 不再使用 + 拼接SQL语句，减少语法错误.
- 有效解决了SQL注入问题！
- 大大减少了编译次数，效率较高.
示例代码
预处理查询
public class PreparedStatement01 {
public static void main(String[] args) {
// 创建Properties对象
Properties properties = new Properties();
// 使用类加载器创建输入流，读取配置文件
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
try {
// 加载
properties.load(is);
is.close();
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
// 1、注册驱动
Class.forName(driver);
// 2、获得连接
Connection connection = DriverManager.getConnection(url, user, password);
// 3、得到PreparedStatement
// 3.1、组织SQL SQL语句的 ? 就相当于占位符
String sql = "select * from admin where name = ? and pwd = ?";
// 3.2、preparedStatement 对象是实现了PreparedStatement接口的实现类的对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 3.3、给 ? 赋值
preparedStatement.setString(1, "1' or");
preparedStatement.setString(2, "or '1' = '1");
// 4、执行select语句 使用executeQuery()
// 如果执行的是dml(update、insert、delete) 使用executeUpdate()
// 注意：这里执行executeQuery(), 不要在写sql
// ResultSet resultSet = preparedStatement.executeQuery(sql);// 这种写法完全错误
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String name = resultSet.getString("name");
password = resultSet.getString("pwd");
System.out.println(name + "\t" + password);
}
// 5、关闭资源
resultSet.close();
preparedStatement.clearParameters();
connection.close();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
预处理DML
public class PreparedStatementDML {
public static void main(String[] args) {
// 创建Properties对象
Properties properties = new Properties();
// 使用类加载器创建输入流，读取配置文件
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
try {
// 加载
properties.load(is);
is.close();
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
// 1、注册驱动
Class.forName(driver);
// 2、获得连接
Connection connection = DriverManager.getConnection(url, user, password);
// 3、得到PreparedStatement
// 3.1、组织SQL SQL语句的 ? 就相当于占位符
// 添加记录
// String sql = "insert into admin values (?, ?)";
// 修改记录
// String sql = "update admin set pwd = ? where name = ?";
// 删除记录
String sql = "delete from admin where name = ?";
// 3.2、preparedStatement 对象是实现了PreparedStatement接口的实现类的对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 3.3、给 ? 赋值
// 添加
// preparedStatement.setString(1, "rnny");
// preparedStatement.setString(2, "r123");
// 修改
// preparedStatement.setString(1, "Rnny");
// preparedStatement.setString(2, "rnny");
// 删除
preparedStatement.setString(1, "rnny");
// 4、执行dml语句 使用executeUpdate()
int rows = preparedStatement.executeUpdate();
System.out.println(rows > 0 ? "成功" : "失败");
// 5、关闭资源
preparedStatement.clearParameters();
connection.close();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
JDBC的相关API小结
[JDBC API示意图](JDBC API.xmind).
.
JDBC工具类-JDBCUtils开发
- 封装JDBCUtils说明：在jdbc操作中，获取连接和释放资源是经常使用到的，可以将其封装JDBC连接工具类JDBCUtils.
.
示例代码
工具类
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-11
* 这是一个工具类，完成MySQL的连接和关闭资源
*/
public class JDBCUtils {
// 定义相关的属性(4个)，因为只需要一份，因此，我们做成static的
private static String user;// 用户名
private static String password;// 密码
private static String url;// url
private static String driver;// 驱动名
static {
Properties properties = new Properties();
try {
properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties"));
// 读取相关的属性值
user = properties.getProperty("user");
password = properties.getProperty("password");
url = properties.getProperty("url");
driver = properties.getProperty("driver");
} catch (IOException e) {
// 在实际开发中，我们可以这样处理
// -> 我们将编译异常转换成运行异常, 抛出去(throw)
// 此时调用者就有两种选择
// 1、捕获异常
// 2、默认处理
throw new RuntimeException(e);
}
}
// 连接数据库，返回Connection
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
// 1、将编译异常转换成 运行异常
// 2、调用者，可以选择捕获该异常，也可以选择默认处理该异常，比较方便
throw new RuntimeException(e);
}
}
// 关闭相关资源
/*
1.ResultSet结果集
2.Statement 或者 PreparedStatement
3.Connection
4.如果需要关闭资源，就传入对象，否则传入 null
*/
public static void close(ResultSet set, PreparedStatement preparedStatement, Connection connection) {
if (set != null) {
try {
set.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
工具类的使用 - dml
public class JDBCUtilsUse {
@Test
public void testDML() {
// 1、得到连接
Connection connection = null;
// 2、组织sql
// 修改
String sql = "update actor set name = ? where id = ?";
// 添加
// String sql = "insert into actor values (?, ?, ?, ?, ?)";
// 删除
// String sql = "delete from actor where id = ?";
// 3、创建PreparedStatement对象
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
// 给占位符赋值
preparedStatement.setString(1, "RNNY");
preparedStatement.setInt(2, 1);
// 执行
int rows = preparedStatement.executeUpdate();
System.out.println(rows > 0 ? "成功" : "失败");
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭资源
JDBCUtils.close(null, preparedStatement, connection);
}
}
}
工具类的使用 - 查询
public class JDBCQuery {
@Test
public void select() {
// 1、获取连接
Connection connection = null;
// 2、组织sql
String sql = "select * from actor where id = ?";
// 3、创建PreparedStatement
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
// 给? 赋值
preparedStatement.setInt(1, 2);
// 执行，得到结果集
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String sex = resultSet.getString("sex");
Date borndate = resultSet.getDate("borndate");
String phone = resultSet.getString("phone");
System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(resultSet, preparedStatement, connection);
}
}
}
事务
基本介绍
- JDBC程序中当一个Connection对象创建时，默认情况下是自动提交事务：每次执行一个SQL语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
- JDBC程序中为了让多个SQL语句作为一个整体执行，需要使用事务。
- 调用Connection的setAutoCommit(false); 可以取消自动提交事务。
- 在所有的SQL语句都成功执行后，调用 Connection 的 commit(); 方法提交事务。
- 在其中某个操作失败或者出现异常时，调用 Connection 的 rollback(); 方法回滚事务。
使用事务解决经典的转账问题
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-11
* 演示在jdbc中 如何使用事务
*/
public class TransactionAccount {
// 没有使用事务
@Test
public void noTransaction() {
// 操作转账的业务
// 1、得到连接
Connection connection = null;
// 2、组织sql
String sql = "update account set money = money - 100 where id = '1'";
String sql2 = "update account set money = money + 100 where id = 2";
// 3、创建PreparedStatement对象
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();// 在默认情况下，connection是默认自动提交
preparedStatement = connection.prepareStatement(sql);
// 执行第一条sql
preparedStatement.executeUpdate();
int i = 1 / 0;// 抛出异常
preparedStatement = connection.prepareStatement(sql2);
// 执行第一条sql
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, preparedStatement, connection);
}
}
// 使用事务来解决
@Test
public void useTransaction() {
// 1、获取连接
Connection connection = null;
// 2、组织sql
String sql = "update account set money = money - 100 where id = 1";
String sql2 = "update account set money = money + 100 where id = 2";
// 3、创建PreparedStatement对象
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
// 将connection 设置为不自动提交
connection.setAutoCommit(false);// 开启了事务
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
// int i = 1/0;// 抛出异常
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
connection.commit();
} catch (Exception e) {
// 这里我们可以进行回滚，即撤销执行的SQL
// 默认回滚到事务开始的状态
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
System.out.println("执行发生了异常，撤销执行的SQL");
} finally {
JDBCUtils.close(null, preparedStatement, connection);
}
}
}
批处理
Batch processing 批处理.
batch 一批.
基本介绍
-
当我们需要成批插入或者更新记录时，可以采用java的批量处理(批处理)更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率。
-
JDBC的批量处理语句包括下面的方法：
- addBatch() -> 添加需要批量处理的SQL语句或参数
- executeBatch() -> 执行批量处理语句
- clearBatch() -> 清空批处理包的语句
-
JDBC连接MySQL时，如果要使用批处理功能，请再url中加参数：不要忘了?
-
?rewriteBatchedStatements=true -
注意：
- ? 不能忘记
- Statements 最后的 s 不能忘记
- sql语句 中的 values () -> 即 values 与 () 之间要有空格！！
-
-
批处理往往和PreparedStatement一起搭配使用，既可以减少编译次数，又能减少运行次数，效率大大提高。
示例代码
java代码
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-12
* 演示Java 批处理
*/
public class Batch01 {
// 传统方法，添加5000条数据的到admin2
@Test
public void noBatch() {
// 获取连接
Connection connection = null;
// 组织sql
String sql = "insert into admin2 values (null , ?, ?)";
// 创建PreparedStatement对象
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
preparedStatement.setString(1, "rnny" + i);
preparedStatement.setString(2, "r12" + i);
preparedStatement.executeUpdate();
}
long end = System.currentTimeMillis();
// 传统方法 耗时 -> 6106
System.out.println("传统方法 耗时 -> " + (end - start));
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, preparedStatement, connection);
}
}
// 使用批处理方式 添加数据
@Test
public void useBatch() {
// 获取连接
Connection connection = null;
// 编写sql
String sql = "insert into admin2 values (null, ?, ?)";
// 创建PreparedStatement对象
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 0; i < 5555; i++) {
preparedStatement.setString(1, "RNNY" + i);
preparedStatement.setString(2, "R" + i);
// 将sql 语句加入到批处理包中 -> 看源码
/*
// 1.第一次先创建ArrayList -> elementData => Object[]
// 2.elementData => Object[] 就会存放我们预处理的sql语句
// 3.当elementData满后, 就按1.5倍扩容
// 4.当到达指定值时，就executeBatch()
// 5.批量处理会减少我们发送sql语句的网络开销，并且减少编译次数，因此效率高。
public void addBatch() throws SQLException {
if (this.batchedArgs == null) {
this.batchedArgs = new ArrayList();
}
this.batchedArgs.add(new PreparedStatement.BatchParams(....));
}
*/
preparedStatement.addBatch();
// 当有1000条记录时，再批量执行
if ((i + 1) % 1000 == 0) {
preparedStatement.executeBatch();
// 清空一把
preparedStatement.clearBatch();
}
}
preparedStatement.executeBatch();
long end = System.currentTimeMillis();
// 批处理方式 耗时-> 233
System.out.println("批处理方式 耗时-> " + (end - start));
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null, preparedStatement, connection);
}
}
}
properties配置文件
user=root
password=root
url=jdbc:mysql://localhost:3306/hsp_db02?rewriteBatchedStatements=true
driver=com.mysql.jdbc.Driver
数据库连接池
传统方式获取Connection问题分析
- 传统的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将Connection对象加载到内存中，再验证ip地址，用户名和密码(0.05s~1s时间)。需要数据库连接的时候，就向数据库要求一个，频繁的进行数据库连接操作将占用很多的系统资源，容易造成服务器的崩溃。
- 每一次数据库连接，使用完后都得断开，如果程序出现异常而未能关闭，将导致数据库内存泄漏，最终将导致重启数据库。
- 传统获取连接的方式，不能控制创建的连接数量，如连接过多，也可能导致内存泄漏，MySQL崩溃。
- 解决传统开发中的数据库连接问题，可以采用数据库连接池技术(connection pool)
数据库连接池的基本介绍
- 预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从 "缓冲池" 中取出一个，使用完毕之后再放回去。
- 放回去是什么意思？这里不是关闭连接，而是取消对连接的引用。
- 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重现建立一个。
- 当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。
示意图
.
数据库连接池的种类(分类)
- JDBC的数据库连接池使用 javax.sql.DataSource 来表示，DataSource只是一个接口，该接口通常由第三方提供实现.
- C3P0 数据库连接池，速度相对较慢，稳定性不错(hibernate，spring)
- DBCP 数据库连接池，速度相对c3p0较快，但不稳定。
- Proxool 数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点。
- BoneCP 数据库连接池，速度快。
- Druid(德鲁伊) 是阿里提供的数据库连接池，集DBCP、C3P0，Proxool 优点于一身的数据库连接池。
C3P0
1.导入2个jar包和1个xml配置文件
[mysql-connector-java-5.1.7-bin](工具包 - jar包\jdbc). 这个MySQL驱动jar包还是需要导入滴~
[C3P0的jar包和xml配置文件](工具包 - jar包\jdbc\c3p0).
2.示例代码
方式1：手动设置参数
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-12
* 演示C3P0 的使用
*/
public class C3P001 {
@Test
// 方式1：相关参数，在程序中指定 url、user、password等
public void testC3P0_01() throws Exception {
// 1、创建一个数据源对象
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
// 2、通过配置文件mysql.properties 获取相关连接的信息
Properties properties = new Properties();
properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("mysql.properties"));
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
// 3、给数据源 comboPooledDataSource 设置相关参数
// 注意：连接管理是由 comboPooledDataSource 来管理
comboPooledDataSource.setDriverClass(driver);
comboPooledDataSource.setJdbcUrl(url);
comboPooledDataSource.setUser(user);
comboPooledDataSource.setPassword(password);
// 设置初始化连接数
comboPooledDataSource.setInitialPoolSize(10);
// 设置最大连接
comboPooledDataSource.setMaxPoolSize(50);
// 4、获取连接
// 测试连接池的效率，连接mysql数据库 5000次
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
Connection connection = comboPooledDataSource.getConnection();// 这个方法就是从 DataSource 接口实现的
// System.out.println("连接成功~");
connection.close();
}
long end = System.currentTimeMillis();
// C3P0 连接数据库5000次 耗时 -> 328
System.out.println("C3P0 连接数据库5000次 耗时 -> " + (end - start));
}
}
方式2：使用配置文件【推荐】
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-12
* 演示C3P0 的使用
*/
public class C3P001 {
@Test
// 方式2：使用配置文件模板来完成
// 1.将c3p0-config.xml 拷贝到 src目录下
// 2.该文件指定了连接数据库和连接池的相关参数
public void testC3P0_02() throws SQLException {
// new ComboPooledDataSource("hsp_edu");
// 参数configName 是 c3p0-config.xml 配置文件中 named-config标签的 name值
// 即 configName -> "hsp_edu"
// <named-config name="hsp_edu">
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hsp_edu");
// 测试5000次连接MySQL
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
Connection connection = comboPooledDataSource.getConnection();
// System.out.println("连接OK~");
connection.close();
}
long end = System.currentTimeMillis();
// c3p0的第二种方式 耗时 -> 424
System.out.println("c3p0的第二种方式 耗时 -> " + (end - start));
}
}
Druid【推荐】
1.导入2个jar包和1个properties配置文件
[mysql-connector-java-5.1.7-bin](工具包 - jar包\jdbc). 这个MySQL驱动jar包还是需要导入滴~
[Druid的jar包和properties配置文件](工具包 - jar包\jdbc\druid-1.1.10).
2.Java代码
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-12
* 测试Druid 的使用
*/
public class Druid01 {
@Test
public void testDruid() throws Exception {
// 1、导入 Druid jar包
// 2、导入 配置文件 druid.properties, 将该文件拷贝到项目的src目录下
// 3、创建Properties对象，读取配置文件
Properties properties = new Properties();
properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties"));
// 4、创建一个指定参数的数据库连接池，Druid连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
long start = System.currentTimeMillis();
for (int i = 0; i < 500000; i++) {
Connection connection = dataSource.getConnection();
// System.out.println("连接成功~");
connection.close();
}
long end = System.currentTimeMillis();
// Druid 连接5000次 耗时 -> 565
// Druid 连接500000次 耗时 -> 685
System.out.println("Druid 连接500000次 耗时 -> " + (end - start));
}
}
德鲁伊工具类
工具类代码实现
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-12
* 基于druid 数据库连接池的工具类
*/
public class JDBCUtilsByDruid {
private static DataSource ds;
// 在静态代码块中完成 ds初始化
// 静态代码块是在加载类的时候执行，且只会执行一次。
// 因此数据源 只会初始一次.
// 所以 静态代码块就非常适合做 静态初始化的操作.
static {
Properties properties = new Properties();
try {
properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
// 获取连接 getConnection
public static Connection getConnection() {
try {
return ds.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
// 关闭连接
// 注意：在数据库连接池技术中，close() 方法不是真的断掉连接
// 而是把使用的Connection对象放回的连接池
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
// 将编译异常转成运行异常抛出
throw new RuntimeException(e);
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
工具类的使用
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-12
*/
public class JDBCUtilsByDruidUse {
@Test
public void testJDBCUtilsByDruidUse() {
// 获取连接
Connection connection = null;
// 组织sql
String sql = "select * from actor";
// 创建PreparedStatement对象
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtilsByDruid.getConnection();
System.out.println(connection);
// class com.alibaba.druid.pool.DruidPooledConnection
System.out.println(connection.getClass());// 运行类型
preparedStatement = connection.prepareStatement(sql);
// 得到结果集
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
System.out.println(id + "\t" + name);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
}
}
}
Apache-DBUtils
问题引入
- 关闭connection后，resultSet 结果集就无法使用了。
- resultSet 不利于数据的管理。
- 示意图：
.
土办法完成封装
Actor类
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-13
* Actor 对象 和 actor表的记录对应
*/
public class Actor {// JavaBean, POJO, Domain对象
private Integer id;
private String name;
private String sex;
private Date borndate;
private String phone;
public Actor() {// 一定要给一个无参构造器[反射需要]
}
public Actor(Integer id, String name, String sex, Date borndate, String phone) {
this.id = id;
this.name = name;
this.sex = sex;
this.borndate = borndate;
this.phone = phone;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBorndate() {
return borndate;
}
public void setBorndate(Date borndate) {
this.borndate = borndate;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Actor{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", borndate=" + borndate +
", phone='" + phone + '\'' +
'}';
}
}
演示类
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-12
*/
public class JDBCUtilsByDruidUse2 {
// 使用老师的土方法来解决ResultSet =封装=> ArrayList
@Test
public void testSelectToArrayList() {
// 获取连接
Connection connection = null;
// 组织sql
String sql = "select * from actor where id >= ?";
// 创建PreparedStatement对象
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
// 创建ArrayList集合，存放actor对象
ArrayList<Actor> list = new ArrayList<>();
try {
connection = JDBCUtilsByDruid.getConnection();
preparedStatement = connection.prepareStatement(sql);
// 给占位符? 设值
preparedStatement.setInt(1, 1);
// 执行select 获取结果集
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String sex = resultSet.getString("sex");
Date borndate = resultSet.getDate("borndate");
String phone = resultSet.getString("phone");
// 把得到的resultSet 的记录，封装成 Actor对象，放入到list集合
list.add(new Actor(id, name, sex, borndate, phone));
}
// 因为ArrayList 和 connection 没有任何关联, 所以该集合可以复用。
list.stream().map(Actor::getName).forEach(System.out::println);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
}
}
}
Apache-DBUtils 的基本介绍
- commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的封装，使用dbutils能极大简化jdbc编码的工作量。
- DBUtils类
- QueryRunner类：该类封装了SQL的执行，是线程安全的。可以实现增、删、改、查、批处理。
- 使用QueryRunner类实现查询
- ResultSetHandler接口：该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式。
/*
ArrayHandler：把结果集中的每一行数据转成对象数组
ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中
BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
ColumnListHandler：将结果集中某一列的数据存放到List中
KeyedHandler(name)：将结果集中的每行数据都封装到Map里，再把这些map再存到一个map里，其key为指定的key.
MapHandler：将结果集中的第一行数据封装到一个Map里，key为列名，value就是对应的值。
MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List.
*/
应用实例
- 使用DBUtils + 数据库连接池(德鲁伊)方式，完成对actor表的crud
1.导入相关jar包.
MySQL驱动：[mysql-connector-java-5.1.7-bin](工具包 - jar包\jdbc\MySQL驱动). 这个MySQL驱动jar包还是需要导入滴~
德鲁伊连接池和配置文件：[Druid的jar包和properties配置文件](工具包 - jar包\jdbc\druid-1.1.10).
Apache-DBUtils：[commons-dbutils-1.3.jar](工具包 - jar包\jdbc\Apache).
2.Query
.
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-13
*/
public class DBUtils_USE {
// 使用Apache-DBUtils 工具类 + druid 完成对表的crud操作
@Test
// 查询结果是多行记录, 即ArrayList集合
public void testQueryMany() throws SQLException {// 返回结果是多行的情况
// 1、获取连接 (druid)
Connection connection = JDBCUtilsByDruid.getConnection();
// 2、使用Apache-DBUtils 类和接口，先引入DBUtils相关的jar包
// 3、创建QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 4、这就可以执行相关的方法，返回ArrayList 结果集
String sql = "select * from actor where id >= ?";
// 注意：sql语句也可以查询部分列
// String sql = "select id, name from actor where id >= ?";
// 说明：
// (1) list 方法就是执行sql语句，得到ResultSet ---封装到---> ArrayList集合中
// (2) 返回一个集合
// (3) connection：连接
// (4) sql：执行的SQL语句
// (5) new BeanListHandler<>(Actor.class)：将ResultSet记录 -> Actor对象 -> 封装到ArrayList集合中
// 底层使用反射机制 去获取Actor类的属性，然后进行封装.
// (6) 2 就是给sql语句中的? 赋值，可以有多个值，因为这里是可变参数 Object... params
// (7) 底层得到的ResultSet, 会在query关闭，也会关闭PreparedStatement
/*
public <T> T list(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
PreparedStatement stmt = null;// 定义PreparedStatement
ResultSet rs = null;// 接收返回的ResultSet
Object result = null;// 返回ArrayList
try {
stmt = this.prepareStatement(conn, sql);// 创建PreparedStatement
this.fillStatement(stmt, params);// 对sql 的占位符? 赋值
rs = this.wrap(stmt.executeQuery());// 执行sql，返回ResultSet
result = rsh.handle(rs);// 将返回的ResultSet -转换成-> ArrayList [使用到反射, 对传入的class对象处理]
} catch (SQLException var33) {
this.rethrow(var33, sql, params);
} finally {
try {
this.close(rs);// 关闭ResultSet
} finally {
this.close((Statement)stmt);// 关闭PreparedStatement
}
}
return result;
}
*/
List<Actor> list =
queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
list.forEach(System.out::println);
// 5、释放资源(至关重要)
JDBCUtilsByDruid.close(null, null, connection);
}
@Test
// 查询结果是单行记录(单个对象)
// Single 单个的, 单一的
public void testQuerySingle() throws SQLException {
// 1、获取连接
Connection connection = JDBCUtilsByDruid.getConnection();
// 2、创建QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 3、组织sql, 查询单个对象
String sql = "select * from actor where id = ?";
// 单行记录 <---> 单个对象，使用的是 BeanHandler
Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 1);
System.out.println(actor);
// 4、关闭资源
JDBCUtilsByDruid.close(null, null, connection);
}
@Test
// 查询结果是单行单列 -> 返回的就是Object
// Scalar 单行单列的值
public void testQueryScalar() throws SQLException {
// 1、获取连接
Connection connection = JDBCUtilsByDruid.getConnection();
// 2、创建QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 3、组织sql, 查询单行单列的值, 返回的是Object
String sql = "select name from actor where id = ?";
// 4、这里使用ScalarHandler
Object o = queryRunner.query(connection, sql, new ScalarHandler(), 1);
System.out.println(o);
// 关闭资源
JDBCUtilsByDruid.close(null, null, connection);
}
}
3.DML操作
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-13
*/
public class DBUtils_DML {
// 使用Apache-DBUtils 工具类 + druid 完成DML操作 (update、insert、delete)
@Test
public void testDML() throws SQLException {
// 1、获取连接
Connection connection = JDBCUtilsByDruid.getConnection();
// 2、创建QueryRunner
QueryRunner queryRunner = new QueryRunner();
// 3、组织sql
// 更新
// String sql = "update actor set name = ? where id = ?";
// 添加
// String sql = "insert into actor values (null, ?, ?, ?, ?)";
// 删除
String sql = "delete from actor where id = ?";
// 解读
// (1) 执行dml语句，操作是 queryRunner.update() -> update, delete, insert同样适用
// (2) 返回的是受影响的行数
// affected 受影响的
// int affectedRows = queryRunner.update(connection, sql, "rnny", 1);// update
// int affectedRows = queryRunner.update(connection, sql, "软柠柠吖", "女", "1999-12-12", "999");// insert
int affectedRows = queryRunner.update(connection, sql, 1);
System.out.println(affectedRows > 0 ? "执行成功" : "执行没有影响到表");
// 关闭资源
JDBCUtilsByDruid.close(null, null, connection);
}
}
数据库表和JavaBean 的数据类型的映射关系
.
BasicDAO
- DAO和增删改查通用方法——BasicDAO
分析问题
- Apache-DBUtils + Druid 简化了JDBC开发，但还有不足：
- SQL语句是固定的，不能通过参数传入，通用性不好，需要进行改进，以便更方便的执行 增删改查。
- 对于select操作，如果有返回值，返回类型不能固定，需要使用泛型。
- 将来的表很多，业务需求复杂，不可能只靠一个Java类完成。
- 引出=》BasicDAO 画出示意图，看看实际开发中，应该如何处理
示意图
.
基本说明
- DAO：data access object 数据访问对象
- 这样的通用类，称为BasicDAO，是专门和数据库交互的，即完成对数据库(表)的crud操作。
- 在BasicDAO的基础上，实现一张表 对应一个DAO，更好的完成功能，
- 比如 Customer表 -> Customer.java类(javabean) -> CustomerDAO.java
应用实例
示意图
.
完成一个简单设计
- com.rnny.utils：工具类
- com.rnny.domain：JavaBean
- com.rnny.dao：存放XxxDAO和BasicDAO
- com.rnny.test：存放测试类
导包
MySQL驱动：[mysql-connector-java-5.1.7-bin](工具包 - jar包\jdbc\MySQL驱动). 这个MySQL驱动jar包还是需要导入滴~
德鲁伊连接池和配置文件：[Druid的jar包和properties配置文件](工具包 - jar包\jdbc\druid-1.1.10).
Apache-DBUtils：[commons-dbutils-1.3.jar](工具包 - jar包\jdbc\Apache).
目录结构
.
dao
BasicDAO
package com.rnny.dao_.dao;
import com.rnny.dao_.utils.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-14
* 开发BasicDAO，是其它DAO的父类
*/
public abstract class BasicDAO<T> {// 泛型指定具体类型
private QueryRunner qr = new QueryRunner();
// ==========================查询=============================
/**
* 多行查询
*
* @param sql sql语句，可以有 ?
* @param clazz 传入一个类的Class对象，比如Actor.class
* @param parameters 传入 ? 具体的值，可以是多个
* @return 根据Actor.class 返回对应的ArrayList集合
*/
public List<T> selectMulti(String sql, Class<T> clazz, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(connection);
}
}
/**
* 单行查询
* @param clazz 传入一个类的Class对象，比如Actor.class
* @param sql sql语句，可以有 ?
* @param parameters 传入 ? 具体的值，可以是多个
* @return 根据Actor.class 返回对应的actor对象
*/
public T selectSingle(Class<T> clazz, String sql, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return qr.query(connection, sql, new BeanHandler<T>(clazz), parameters);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(connection);
}
}
/**
* 查询单行单列
* @param sql 查询的sql语句
* @param parameters ? 的参数
* @return 返回单行单列的值
*/
public Object selectScalar(String sql, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return qr.query(connection, sql, new ScalarHandler(), parameters);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtilsByDruid.close(connection);
}
}
// ==========================DML=============================
// 开发通用的DML方法，针对任意的表
/**
*
* @param sql sql语句
* @param parameters 占位符? 的参数
* @return 受影响的行数
*/
public int update(String sql, Object... parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return qr.update(connection, sql, parameters);
} catch (SQLException e) {
throw new RuntimeException(e);// 将编译异常 -> 运行异常 抛出
} finally {
JDBCUtilsByDruid.close(connection);
}
}
}
ActorDAO
package com.rnny.dao_.dao;
import com.rnny.dao_.domain.Actor;
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-14
*/
public class ActorDAO extends BasicDAO<Actor> {
// 1、就有 BasicDAO 的方法
// 2、根据业务需求，可以编写特有的方法
}
domain
Actor
package com.rnny.dao_.domain;
import java.util.Date;
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-14
* JavaBean、Domain、JOPO
*/
public class Actor {
private Integer id;
private String name;
private String sex;
private Date borndate;
private String phone;
public Actor() {
}
public Actor(Integer id, String name, String sex, Date borndate, String phone) {
this.id = id;
this.name = name;
this.sex = sex;
this.borndate = borndate;
this.phone = phone;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBorndate() {
return borndate;
}
public void setBorndate(Date borndate) {
this.borndate = borndate;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Actor{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", borndate=" + borndate +
", phone='" + phone + '\'' +
'}';
}
}
test
TestDAO
package com.rnny.dao_.test;
import com.rnny.dao_.dao.ActorDAO;
import com.rnny.dao_.domain.Actor;
import org.junit.Test;
import java.util.List;
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-14
* 测试类
*/
public class TestDAO {
// 测试ActorDAO 对actor表crud操作
@Test
public void testActorDAO() {
ActorDAO actorDAO = new ActorDAO();
// 1、多行查询
List<Actor> list = actorDAO.selectMulti("select * from actor where id > ?", Actor.class, 1);
System.out.println("===多行查询===");
list.forEach(System.out::println);
// 2、单行查询
Actor actor = actorDAO.selectSingle(Actor.class, "select * from actor where id = ?", 2);
System.out.println("===单行查询===");
System.out.println(actor);
// 3、单行单列查询
Object o = actorDAO.selectScalar("select name from actor where id = ?", 3);
System.out.println("==单行单列查询==");
System.out.println(o);
// 4、dml操作
// 插入
String insertSQL = "insert into actor values(null, ?, ?, ?, ?)";
int affectedRows = actorDAO.update(insertSQL, "rnny", "女", "2012-12-23", "888");
System.out.println(affectedRows > 0 ? "执行成功" : "该操作没有影响到表");
// 更新
String updateSQL = "update actor set name = ? where id = ?";
affectedRows = actorDAO.update(updateSQL, "king", "2");
System.out.println(affectedRows > 0 ? "执行成功" : "操作没有影响到表");
// 删除
String deleteSQL = "delete from actor where name = ?";
affectedRows = actorDAO.update(deleteSQL, "rnny");
System.out.println(affectedRows > 0 ? "执行成功" : "操作没有影响到表");
}
}
utils
JDBCUtilsByDruid
package com.rnny.dao_.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author 软柠柠吖(Runny)
* @date 2022-05-14
* JDBCUtilsByDruid 工具类
*/
public class JDBCUtilsByDruid {
// 定义数据源
private static DataSource ds;
// 静态代码块初始化数据源-ds
static {
// 创建properties对象
Properties properties = new Properties();
try {
// 使用类加载器加载配置文件
properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
// 关闭连接
public static void close(Connection connection) {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
配置文件
类加载器
src目录和类加载器示意图
.
在java项目中，如何获取src根目录下的属性文件/资源文件呢？
方式一：
// 在java项目中，如何获取src根目录下的属性文件/资源文件呢？
@Test
public void test() throws Exception {
// 方式1、类名.class.getResourceAsStream("/文件名")
// 注意：这种方式需要 /
// 1、获得输入流
InputStream inputStream = HomeWork.class.getResourceAsStream("/jdbc.properties");
// 2、获得路径url, url不是String
URL url = HomeWork.class.getResource("/jdbc.properties");
// 方式2、类名.class.getClassLoader().getResourceAsStream("文件名")
// 1、获得输入流
inputStream = HomeWork.class.getClassLoader().getResourceAsStream("jdbc.properties");
// 2、获得路径url, url不是String
url = HomeWork.class.getClassLoader().getResource("jdbc.properties");
// 方式3、
// 方式4、Thread.currentThread().getContextClassLoader().getResourceAsStream("文件名")
// 1、获得输入流
inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties");
// 2、获得路径url, url不是String
url = Thread.currentThread().getContextClassLoader().getResource("jdbc.properties");
}
方式二：
静态
public class ClassLoader01 {
public static void main(String[] args) throws Exception {
// 针对静态情况
// 类名.class.getClassLoader().getResourceAsStream("文件名");
InputStream is = ClassLoader01.class.getClassLoader().getResourceAsStream("c.properties");
Properties p = new Properties();
p.load(is);
is.close();
System.out.println(p);
}
}
非静态
public class ClassLoader01 {
public static void main(String[] args) throws Exception {
// 针对非静态情况
// this.getClass().getClassLoader().getResourceAsStream("文件名");
InputStream is = this.getClass().getClassLoader().getResourceAsStream("c.properties");
Properties p = new Properties();
p.load(is);
is.close();
System.out.println(p);
}
}
配置文件的读取
读取properties
static {
Properties properties = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
properties.load(is);
}
// 定义数据源
private static DataSource ds;
// 静态代码块初始化数据源-ds
static {
// 创建properties对象
Properties properties = new Properties();
try {
// 使用类加载器加载配置文件
properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException(e);// 将编译异常 -> 运行异常 抛出
}
}
