1.初始MySQL
太白烂了,差不多花了两个礼拜才把狂神的mysql视频看完,主要是最近节日有点多,溜出去玩了。接下来一个月要好好学习了。
1.1 什么是数据库
数据库(DB,DataBase)
DBMS(数据库管理系统)
概念:数据库是数据仓库,数据库是安装在操作系统(windows,linux,mac…)之上的软件
作用:存储数据,管理数据
1.2 数据库分类
关系型数据库:(SQL)
- MySQL、Oracle 、Sql Server、DB2、SQLlite
- 通过表和表之间,行和列之间的关系进行数据的存储
非关系型数据库:(NoSQL ) not only
- Redis , MongDB
- 非关系数据库,对象存储,通过对象自身的属性来决定。
DBMS(数据库管理系统)
- 数据库的管理软件,科学有效的管理我们的数据,维护和获取数据
- 用数据库管理系统来操控数据库
- MySQL是一个数据库管理系统,也是一个关系型数据库管理系统(RDBMS)
- 我们是学习SQL语言,SQL是一个操作数据库语言,可以存储大量的数据,可存储500万。
1.3 MySQL
前世:雅典MySQL AB公司
今生:属于Oracle旗下的产品
MySQL是最好的RDBMS应用软件之一。
属于开源的数据库软件,体积小,速度快,成本低
1.4命令行连接数据库
可以在cmd中的命令框中写,也可以在sqlog中的询问里面写。我们这边在sqlog中的询问中写
1. mysql -uroot -p123456 -- 注释:连接数据库
2. update mysql.user set authentication_string=password('123456') where user='root' and Host ='localhost'; -- 修改密码
3. flush privileges; -- 刷新权限
4. exit -- 退出连接
5. ctrl + C -- 强行终止
6. 注释 --
多行注释: /* */
--------------------------------------------------------------
-- 所有的语句都使用分号;结尾
--------------------------------------------------------------
1. show databases; -- 查看所有的数据库
2. use school; -- 切换数据库名school
3. show tables; -- 查看数据库里面的表 ,(里面有student)
4. describe student; -- 显示数据库中所有表的信息
------------------------------------------------------------------------
create database westos; -- 创建一个数据库westos。westos是数据库的名字
sql的学习东西:
DDL 数据库定义语言
DML 数据库操作语言
DQL 数据库查询语言
DCL 数据库控制语言
程序员鄙视链: CV程序员 > API程序员 > CRUD程序员
1.5数据库的列类型
数值
-
tinyint 十分小的数据 1个字节
-
smallint 较小的数据 2个字节
-
mediumint 中度大小的数据 3个字节
-
int 标准的数据 4个字节
-
bigint 较大的数据 8个字节
-
float 浮点数 4个字节
-
double 浮点数 8个字节
-
decimal 字符串形式的浮点数 (金融计算的时候,一般使用decimal)
字符串
- char 字符串固定大小 0~255
- varchar 可变字符串 0~65535 常用的 类似于String
- tinytext 微信文本 2^8 - 1
- text 文本串 2^16 - 1 保存大文本
时间日期
java.util.tiem
- data YYYY-MM-DD ,日期格式
- time HH:mm :ss 时间格式
- datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳, 1970.1.1到现在的毫秒数!
- year 年份表示
null
- 没有值,未知
- 注意,不要使用NULL进行数值运算,结果为NULL
1.6、数据库的字段属性(重点)
默认
- 设置默认的值
- 假如sex, 默认值为男, 如果不指定该列的值,就会有默认的值
非空 NUT NULL
- 假设设置为not null ,如果不给他赋值,就会报错!
- NULL, 如果不填写值,默认就是NULL
Unsigned:
- 无符号的整数
- 声明了该列不能声明为负数
自增:
- 通常理解为自增,自动在上一条记录的基础上+1(默认)
- 通常用来设计唯一的主键~index,必须是整数类型
- 可以自定义设计主键自增的起始值和步长(在高级中设置)
zerofill:
- 0填充
- 不足的位数,用0填充
阿里巴巴规范(拓展):
/* 每一个表,都必须存在以下五个字段!未来做项目用的,表示一个记录存在的意义
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_updata 修改时间
*/
2.操作数据库(重点)
操作数据库–>操作数据库里面的表–>操作数据库中表的数据
mysql关键字不区分大小写
可以在cmd中的命令框中写,也可以在sqlog中的询问里面写。我们这边在sqlog中的询问中写
2.1 操作数据库
1.创建数据库
create Database westos; -- westos是创建的数据库的名字
create Database if not exists westos; -- 如果不存在westos,则创建
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci -- 创建数据库
2.使用数据库
USE `school`; -- 在tab键上面。如果表明或者字段名是一个特色的字符,就加一个``
USE school;
3.查看所有数据库
show databases; -- 查看所有数据库
4.查看创建数据库的语句
SHOW CREATE DATABASE school; -- 查看创建数据库的语句
5.删除数据库
drop database westos; -- 移除数据库
drop database if exists westos; -- 如果存在westos,则移除数据库
2.2 操作数据库里面的表(没有往表格中插入数据)
-
目标:创建一个school数据库
-
创建学生表(列,字段) 使用SQL创建
-
学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
1.创建表
-- 模板
CREATE TABLE [IF NOT EXISTS] `表明`(
`字段名` 列类型 [属性] [索引] [注释], -- `字段名` 列类型必须要有
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型[属性] [索引] [注释]
)[表类型][字符集设置][注释]
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',-- id int类型 不为空 自增 注释:学号
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', -- name varchar类型 不为空 默认为匿名 注释:姓名
`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',-- password varchar类型 不为空 默认123456 注释:密码
`sex` VARCHAR(2) NOT NULL DEFAULT '女' DEFAULT '女' COMMENT '性别',-- sex varchar类型 不为空 默认女 注释:性别
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',-- birthday datetime类型 默认为null类型 注释为:出生日期
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址', -- address varchar类型 默认为Null 注释为:家庭地址
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',-- email varchar类型 默认为null 注释为:邮箱
PRIMARY KEY (`id`)-- 设置主键
)ENGINE = INNODB DEFAULT CHARSET = utf8-- 引擎设置为INNODB类型 默认编码为utf8
2.修改表
-- 修改表名。
alter table `teacher` rename `teacher1` ;-- 修改表名。将表teacher重命名为teacher1
-- 增加字段
alter table teacher1 add age int(4);-- 增加字段
-- 修改表的字段(重命名,修改约束也就是修改里面的值)
alter table teacher1 modify age varchar(10);-- 修改teacher1表格中的age字段为varchar类型。修改修改约束
alter table teacher1 change age age1 int(10);-- 字段重命名
-- 删除表的字段
alter table teacher1 drop age1;
show tables; -- 查看数据库里面的表 ,(里面有student)
describe student; -- 显示数据库中所有表的信息
3.删除表
drop table if exists teacher1;
2.查看数据库表的语句
SHOW CREATE TABLE student; -- 查看数据库表的语句
3.查看表的结构
DESC student; -- 查看表的结构
4.数据库引擎
-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为MYISAM的两倍 |
节约空间,速度快 | 安全性高,事务的处理,多表多用户操作 | |
-- 在物理空间存在的位置
所有的数据库文件都存在data目录下,一个文件夹对于一个数据库
本质话说文件的存储!
MySQl引擎在物理文件上的区别:
InnoDB 在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
MYISAM 对应文件:
*.frm -表结构的定义文件
*.MYD 数据文件(data)
*.MYI 索引文件
-- 设置数据库表的字符集编码 CHARSET=utf8
不设置的话不设置的话,会是MySQL默认的字符集编码Latin1(不支持中文)
可以在my.ini中配置默认的编码 character-set-server=utf8
3.MYSQL的数据管理(3.2为DML语言)
3.1 外键(了解即可)
以下操作都是物理外键,数据库级别的外键,不建议使用!(避免数据库最多造成困扰)
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)列(字段)
- 我们想使用多张表的数据,想使用外键(程序去实现)
方式一:在创建表的时候,增加约束(麻烦)
删除有外键关系的表的时候,必须要先删除引用别人的表(从表),再删除被引用的表(主表)(即先删除student表,再删除grade表)
CREATE TABLE `grade`(
`gradeid` INT (10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(10) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',-- id int类型 不为空 自增 注释:学号
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', -- name varchar类型 不为空 默认为匿名 注释:姓名
`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',-- password varchar类型 不为空 默认123456 注释:密码
`sex` VARCHAR(2) NOT NULL DEFAULT '女' DEFAULT '女' COMMENT '性别',-- sex varchar类型 不为空 默认女 注释:性别
-- 通过外键与`grade`绑定
`gradeid` INT (10) NOT NULL COMMENT '学生的年级',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',-- birthday datetime类型 默认为null类型 注释为:出生日期
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址', -- address varchar类型 默认为Null 注释为:家庭地址
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',-- email varchar类型 默认为null 注释为:邮箱
PRIMARY KEY (`id`),-- 设置主键
-- 学生表的grade字段,要去引用年级表的grade。外键
-- 定义外键key
-- 给这个外键添加约束(执行引用)
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
方式二:创建表成功后,添加外键约束
-- 正常创建表
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',-- id int类型 不为空 自增 注释:学号
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', -- name varchar类型 不为空 默认为匿名 注释:姓名
`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',-- password varchar类型 不为空 默认123456 注释:密码
`sex` VARCHAR(2) NOT NULL DEFAULT '女' DEFAULT '女' COMMENT '性别',-- sex varchar类型 不为空 默认女 注释:性别
-- 通过外键与`grade`绑定
`gradeid` INT (10) NOT NULL COMMENT '学生的年级',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',-- birthday datetime类型 默认为null类型 注释为:出生日期
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址', -- address varchar类型 默认为Null 注释为:家庭地址
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',-- email varchar类型 默认为null 注释为:邮箱
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
添加外键约束命令行
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`)
3.2 DML语言(数据库管理语言 全部记住。 往表格中填充数据)
数据库管理语言 : 数据存储 与 数据管理
DML语言:数据操作语言
- insert 添加
- update 修改
- delete 添加
3.3 添加 insert
-- 一般写插入语句,数据一定和字段一一对应
1. -- 在表格中插入数据:INSERT INTO 表名(字端名1) VALUES('值1')
INSERT INTO `grade`(`gradeName`) VALUES('大2')
2. -- 在表格中插入同一字段大的多个数据:INSERT INTO 表名({字端名1,字段2,字段3}) VALUES ('值1'),('值2'),('值3'),...)
INSERT INTO `grade`(`gradename`) VALUES ('11'),('22'),('33'),('44')
3. -- 在表格中插入多个字段:(value后面一个括号代表一行的内容)
-- INSERT INTO 表名({字端名1,字段2,字段3}) VALUES('值1','值2','值3',...)
INSERT INTO `student`(`name`,`password`,`sex`) VALUES ('张三','aaaaaa','男')
-- 也可以加多个,VALUES后面的值,需要用(),隔开
INSERT INTO `student`(`name`,`password`,`sex`) VALUES ('张三','aaaaaa','男'),('张三2','aaaaaa','男')
-- 或者 字段是可以省略,但是后面的值必须要一一对应,不能少
INSERT INTO `student` VALUES (4,'张三','aaaaaa','男','1','2000-01-01','西安','email')
-- 也可以加多个,VALUES后面的值,需要用(),隔开
INSERT INTO `student` VALUES (4,'张三','aaaaaa','男','1','2000-01-01','西安','email'),(5,'张 三','aaaaaa','男','1','2000-01-01','西安','email')
3.4 修改update
update 修改谁(表) set 列名 = 新值 where 条件
UPDATE `student` SET `name` = '小王子' WHERE id =1; -- 当条件为id=1的时候,设置student表格中id为1的名字为“小王子”
-- 不指定条件的情况下,会改动所有表!
UPDATE `student` SET `name` = '小王子'
-- 修改多个属性,逗号隔开
UPDATE `student` SET `name` = '小王子' ,email = '66666666666@qq.com' WHERE id =2;
-- 新值,是一个具体的值,也可以是一个变量
UPDATE `student` SET `birthday` = CURRENT_TIME WHERE name = '1001001' AND sex = '女'
UPDATE `student` SET `birthday` = '2010-01-01 01:01:01'
条件:where子句 运算符 id等于某个值,大于某个值,在某个区间内修改…
3.5 删除 delete 与清空 truncate
delete from 表名 [where 条件]
-- 删除数据 (避免这样写)
DELETE FROM `student`
-- 删除指定数据
DELETE FROM `student` WHERE `id` = 1
TRUNCATE命令,完全清空一个数据库表,表的结果和索引约束不会变
-- TRUNCATE 表明
TRUNCATE `student`
delete与truncate区别:
-
相同点:都能删除数据,都不会删除表结构
-
不同
- TRUNCATE重新设置 自增列 计数器会归零
- TRUNCATE 不会影响事务
了解即可:DELETE删除的问题,重启数据库,现象
- innoDB 自增列会重1开始(存在内存当中,不会丢失)
- MyISAM 继续从上一个自增量开始(存在文件中,不会丢失)
4.DQL(数据库查询语言) 重点中的重点
4.1 DQL(select 的完整语法)
(data query LANGUAGE:数据查询语言)
- 所有的查询操作都用他 select
- 简单的查询,复杂的查询都能做
- 数据库中最核心的语言,最重要的语言
- 使用频率最高的语言
SELECT [ALL(默认) | DISTINCT(去重)]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 排序。指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条至哪条
4.2 指定查询字段 select
-- 查询一张表的所有信息 SELECT * FROM `表格`
SELECT * FROM `student`; -- 查student表格中的所有信息
SELECT * FROM `result` -- 查result表格中的所有信息
-- 查询某一行
SELECT * FROM `student` where id =1;
-- 查询一张表的指定字段
SELECT `studentno` FROM `student`;-- 查student表格中的studentno字段
SELECT `studentno`,`studentname` FROM `student`;-- 查student表格中的studentno,studentname 字段
-- 别名,给字段起一个名字,用as语句
SELECT `studentno` AS 学号 FROM `student`;
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM `student`;
SELECT `studentno` AS 学号 FROM `student` as s;-- 也可以给表名起一个别名
-- 函数 Concat(a,b),拼接
SELECT CONCAT('姓名:', `studentname`) AS 新名字 FROM `student`
-- 去重 distinct :去除select查询出来的结果中重复的数据,重复的数据只显示一条
SELECT DISTINCT `studentno` FROM `result` -- 发现重复数据,去重
-- select的其它用法。用于数据库的表达式, selsect 表达式from 表
-- 数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量
SELECT VERSION() -- 查询系统版本 (函数)
SELECT 100*3-1 AS 计算结果 -- select可以用作加减乘除
SELECT @@auto_increment_increment -- 查询自增的步长 (变量)
-- 学员考试成绩 +1 分查看
SELECT `studentno`,`studentresult`+ 1 AS '提分后' FROM `result` -- `studentresult`+ 1 分数所有加了一分
4.3 where条件字句(逻辑运算符+模糊查询)
作用:检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成 。 结果都是布尔值
逻辑运算符:and && ,or || ,not !。尽量使用英文符号
-- 查询学生编号与学生成绩
SELECT `studentno`,`studentresult` FROM result
-- 查询考试成绩在90分到100之间 and
SELECT `studentno`,`studentresult` FROM result WHERE `studentresult`>=95 AND `studentresult`<=100;
-- 模糊查询 BETWEEN 值 AND 值
SELECT `studentno`,`studentresult` FROM result WHERE `studentresult` BETWEEN 60 AND 100;
-- 除了1000号学生之外的学生成绩 !=
SELECT `studentno`,`studentresult` FROM result WHERE `studentno` != 1000;
-- 除了1000号学生之外的学生成绩 != not
SELECT `studentno`,`studentresult` FROM result WHERE NOT `studentno`=1000;
详细了解模糊查询
模糊查询本质是比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果a为null则结果为真 |
is not null | a is not null | 如果a不为null则结果为真 |
between and | a between b and c | 若a在b与c之间则结果为真 |
like | a like b | 如果a能够匹配到b则结果为真 |
in | a in (a1,a2,a3,a4…) | 如果a 是a1,a2,a3…的其中某一个值则为真 |
-- -- like 结合%(代表0-任意一个字符)与_ (一个字符)
-- 查询姓刘的同学
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '刘%';-- 刘后面有多少字符都用%代替
-- 查询姓刘的同学,名字后只有一个字的
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '刘_';
-- 查询姓刘的同学,名字后只有2个字的
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '刘__';-- 刘后面两个杠
-- 查询名字中间有‘晓’的同学
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '%晓%';
-- in
-- 查询1000 1001 1002 学员的信息
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN (1000,1001,1002);
-- 查询在北京的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN ('浙江嘉兴','广东深圳'); -- in里面是具体的值
-- is null 与 is not null
-- null 与空字符串是不一样的
-- 查询地址为空的学生
SELECT `studentno`,`studentname`,`address` FROM `student`
WHERE `address`='';
UPDATE `student` SET `borndate`=NULL WHERE `studentno`=1000; -- 令studentno为1000的borndate为null
SELECT `studentno`,`studentname`,`address`,`borndate` FROM `student`
WHERE `borndate` IS NOT NULL;
SELECT `studentno`,`studentname`,`address`,`borndate` FROM `student`
WHERE `borndate` IS NULL;
4.4 联表查询 join on
Join on 对比
操作 | 描述 |
---|---|
LEFT JOIN | 会从左表中返回所有的值,即使右表中没有匹配 |
INNER JOIN | 返回两个表中都有的值 |
RIGHT JOIN | 会从右表中返回所有的值,即使左表中没有匹配 |
-- 查询参加考试的同学(学号,姓名,科目表号,分数),要从不同的表里面拿
/*
1.分析查询的字段来自于拿一张表,超过一张表要使用联表查询
2.确定使用哪一种连接查询,有七种连接查询
3.确定交叉点,不同表中哪个是相同的,哪几个是不同的
判断条件:学生表`student`中的`studentno`要与 `result`表中的`studentno`相同
*/
-- LEFT JOIN
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` -- s.`studentno`,让系统知道你想要的是哪一个
FROM `student` AS s -- as可以省略
LEFT JOIN `result` AS r
ON s.`studentno` = r.`studentno`;
-- INNER JOIN
-- 由于`result`与`student`都有`studentno`,所以我们要对把表格起相应的别名.使用别名来引用相应的值
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` -- s.`studentno`,让系统知道你想要的是哪一个
FROM `student` AS s
INNER JOIN `result` AS r-- 并集inner join
ON s.`studentno` = r.`studentno`;
-- RIGHT JOIN
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` -- s.`studentno`,让系统知道你想要的是哪一个
FROM `student` AS s -- as可以省略
RIGHT JOIN `result` AS r
ON s.`studentno` = r.`studentno`;
-- 查询缺考的同学 ,在左连接的基础上查,在join on基础上加where
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` -- s.`studentno`,让系统知道你想要的是哪一个
FROM `student` AS s -- as可以省略
LEFT JOIN `result` AS r
ON s.`studentno` = r.`studentno`
WHERE r.`studentresult` IS NULL; -- where另加条件
-- LEFT JOIN 与 INNER JOIN 与RIGHT JOIN可以一起用。
-- 有多张表,先慢慢查询两张表,再慢慢加
-- 查询:学号、学生姓名,科目名称,分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno`= r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`;-- `result`的`subjectno`与`subject`的`subjectno`内
-- on后面的值可以不出现在select后面
-- 查询参加高等数学-1的同学:学号、学生姓名,科目名称,分数
SELECT s.`studentno`,s.`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'
4.5 自连接
自连接:自己的表和自己的表连接
核心:一张表拆成两张一样的表即可
比如一张表如下,拆成父类表与子类表
categoryid(当前id) | pid(父类id) | categoryname |
---|---|---|
2 | 1 | 信息技术 |
3 | 1 | 软件开发 |
4 | 3 | 数据库 |
5 | 1 | 美术设计 |
6 | 3 | web开发 |
7 | 5 | ps设计 |
8 | 2 | 办公信息 |
按照树状图可拆
父类表
categoryid(当前id) | categoryname |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类表
pid(父类id) | categoryid(当前id) | categoryname |
---|---|---|
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps设计 |
-- 查询父类对应子类的关系
-- 查询父子关系,把一张表看好才能够两张一模一样的的表
SELECT a.`categoryname`AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b -- 把一张表看成两张一模一样的表
WHERE a.`categoryid`=b.`pid`;
得出结果如下:
4.6 分页 limit 与 排序 order by
**在代码中,排序 order by 要写在 分页 limit下面 **
order by + 按照哪一列 + 升序或者降序
-- 排序order by:升序ASC 或者 降序 DeSC
-- opder by 按照哪一列进行排序
-- 查询结果根据成绩studentresult进行升序排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'
ORDER BY `studentresult` ASC; -- 按照`studentresult`升序排序
-- 或者按照降序去排 ORDER BY `studentresult` desc;
limit + 起始位置的索引 + 一页展示多少个值
-- 分页
-- 为什么分页:缓解数据库压力,给人一个好的体验。(一般图片使用瀑布流)
-- 分页每一页只显示一条数据
-- limit 当前起始位置索引(第一个位置的索引为0),页面大小。
-- limit要写在order by 排序下面
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'
ORDER BY `studentresult` ASC
LIMIT 0,1;-- 第一个数的位置的索引为0,展示五个值
-- 第一页展示5个数 linit 0,5
-- 第二页展示5个数 linit 5,5
-- 第三页展示5个数 linit 10,5
-- ......
-- 第n页 linit (n-1)*pagesieze,pagesieze
-- 【n为当前页】,【(n-1)*pagesieze起始值】,【pagesieze为页面大小】
-- 【总页数 = 数据总数/页面大小】
练习
-- 查询 高等数学-1 ,课程排名前十的同学,并且分数要大于60的学生信息,(学号、姓名、课程名称、分数)、
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON sub.`subjectno`= r.`subjectno`
WHERE `subjectname`='高等数学-1' AND `studentresult`>=60
ORDER BY `studentresult` ASC -- 排序
LIMIT 0,10; -- 分页
4.7 子查询
原本where(值是固定的),而我们用子查询使where(值是计算出来的)
本质:在where语句中嵌套一个子查询语句
-- 查询高等数学-1的所有考试结果,(学号,科目编号,科目名称,成绩),降序排列
-- 1.方式一:使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`= '高等数学-1'
ORDER BY `studentresult` DESC
-- 2.方式二:使用子查询嵌套进去(由里到外)
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno`=(
SELECT `subjectno` FROM `subject` AS sub
WHERE `subjectname`='高等数学-1'
)
ORDER BY `studentresult` DESC
-- 查询所有高等数学-1的科目名
SELECT `subjectno` FROM `subject` WHERE `subjectname`='高等数学-1';
练习
-- 喜欢用哪一种方法左就用哪一种
-- 联表法
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`= sub.`subjectno`
WHERE `subjectname`='高等数学-1' AND `studentresult`>=40
-- 在这个基础上增加一个科目,高等数学-1
-- 查询高等数学-1的编号
SELECT DISTINCT s.`studentno`,`studentname`-- DISTINCT去重
FROM `student` AS s
INNER JOIN result AS r
ON s.`studentno`=r.`studentno`
WHERE `studentresult`>=40 AND `subjectno`= (
SELECT `subjectno` FROM `subject` -- select `subjectno` from `subject` where `subjectname`='高等数学-1'
WHERE `subjectname`='高等数学-1'
)
-- 再改造(由里及外
SELECT studentno, studentname FROM student WHERE studentno in (
SELECT studentno FROM result WHERE studentresult>80 AND `subjectno` = (
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
)
)
4.8 分组GROUP BY 和过滤 HAVING
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
-- 查询不同课程的最高分与最低分,平均分大于60
-- 核心:根据不同的课程分组
SELECT `subjectname` ,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) 最低分
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subjectno`= sub.`subjectno`
-- 分组了不能用where 只能用having
GROUP BY r.`subjectno` -- 指定字段按照哪几个字段来分组,分组查询
HAVING AVG(`studentresult`)>60 -- 过滤分组的记录必须满足的必要条件
5.MySql官方函数
5.1 常用函数(并不常用)
-- ====================================常用函数==============
-- 数学运算
SELECT ABS(-8);-- 绝对值
SELECT CEILING(9.4) -- 向上取整 10
SELECT FLOOR(9.4) -- 向下取整 9
SELECT RAND()-- 随机数0-1
SELECT SIGN(-10) -- 判断参数符号 负数返回-1,正数返回0,0返回0
-- 字符串函数
SELECT CHAR_LENGTH('无敌是多么寂寞') -- 字符串长度 7
SELECT CONCAT('wo','se') -- 拼接字符串
SELECT INSERT('我爱编程hello_world',1,2,'超级热爱') -- 在指定位置插入字符串,最多指定字符数。从某个位置开始替换某个长度。超级热爱编程hello_world
SELECT LOWER('lcP') -- 大写改成小写,小写保持不变。
SELECT UPPER('Lcp') -- 小写改成大写字母
SELECT INSTR('lcp','c') -- 返回第一次出现的字符串索引 2
SELECT REPLACE ('坚持就能唱歌','唱歌','成功')-- 替换字符串
SELECT SUBSTR('dskfn',3,2) -- 返回指定的字符串(同Java)SELECT SUBSTR('dskfn',位置,长度)
SELECT REVERSE('abcdefg') -- 反转
-- 查询姓 张 的同学 名字 变为 周
SELECT REPLACE(studentname, '张', '周') FROM student
WHERE studentname LIKE '张%'
-- 时间和日期函数 (记住)
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE()-- 与CURRENT_DATE()是同义词
SELECT NOW()
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
-- 系统
SELECT SYSTEM_USER() -- 当前用户
SELECT VERSION() -- 版本
5.2 聚合函数(常用)
函数名称 | 描述 |
---|---|
count() | 计数 |
sum() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
-- ==================聚合函数=================
-- 这三个都能统计表中的数据。用第一个
SELECT COUNT(`studentname`) FROM `student` -- 对`student`表中的`studentname`计数.-- count(字段),会忽略所有的null值。
SELECT COUNT(*) FROM `student`-- ,不会忽略null , 本质,计算行数
SELECT COUNT(1) FROM `student`-- 不会忽略所有的null 本质 计算行数
/*
无主键:count(1)比count(x)快
有主键:count(主键)最快
count(*)与count(1)都包括null统计,而count(column)不包括null统计*/
SELECT SUM(`studentresult`) AS 总和 FROM `result`
SELECT AVG(`studentresult`) AS 平均分 FROM `result`
SELECT MAX(`studentresult`) AS 最高分 FROM `result`
SELECT MIN(`studentresult`) AS 最低分 FROM `result`
5.3 数据库级别的MD5加密(扩展)
MD5信息摘要算法(英语:MD5 Message-Digest Algorithm),一种被广泛使用的[密码散列函数],可以产生出一个128位(16[字节]的散列值(hash value)
主要增强算法复杂度和不可逆性
MD5不可逆,具体的值的MD5是一样的
MD5破解网站的原理,背后有一个字典,MD5加密后的值,加密的前值
-- ==============测试md5加密=============
-- 明文密码
INSERT INTO `testmd5` VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')
-- 加密
UPDATE `testmd5` SET `pwd`=MD5(`pwd`) WHERE `id`=1 -- 加密
UPDATE `testmd5` SET `pwd`=MD5(`pwd`)-- 全部加密
-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4,'小明',MD5('123456')),(5,'小王',MD5('123456'))
-- 如何校验:将用户传递进来的密码,进行md5加密,然后比对加密后的值
-- 查md5
SELECT *FROM `testmd5` WHERE `name` = '小明' AND `pwd`=MD5('123456');
6.事务
6.1 什么是事务?
要么都成功,要么都失败
事务的原则:ACID原则 原子性、一致性、隔离性、持久性 (脏读、幻读)
原子性:要么都成功,要么都失败
一致性:事务前后数据完整性要保证一致
无论怎么转,最后A与B的值总和一定是1000
持久性:表示事务结束后的数据不随着外界原因导致数据丢失
/*
事务没有提交,恢复到原状
事务已经提交了,持久化数据库,事务一旦提交了就不可逆
*/
/*
操作前A:800,B:200
操作后A:600,B:400
如果在操作前(事务还没有提交)服务器宕机或者断电,那么重启数据库以后,数据状态应该为
A:800,B:200
如果在操作后(事务已经提交)服务器宕机或者断电,那么重启数据库以后,数据状态应该为
A:600,B:400
*/
隔离性:隔离性是多个用户并发访问数据库时,数据库为每个用户开启的事务,不能被其他事务的操作数据所干扰,事务之间要相互隔离
隔离所导致的一些问题:
脏读:
指一个事务读取了另外一个事务未提交的数据。
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
(一般是行影响,多了一行)
6.2 事务的执行
-- ================事务==========
-- mysql是默认开启事务自动提交
-- set autocommit = 0 关闭自动提交
-- set autocommit = 1 开启(默认的值),每执行一句就默认提交
-- ===================================================================
-- 为了学习,我们需要手动去处理事务
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启 START TRANSACTION
START TRANSACTION -- 标记一个事务的开始,从这个之后的sql都在同一个事务内
INSERT xx
INSERT xx
-- 提交:持久化 commit
commit
-- 回滚,回到原来的样子(失败要回滚) ROLLBACK
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
例子
-- 事务例子:银行转账。要一个一个执行
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci -- 创建数据库
USE shop -- 使用数据库shop
CREATE TABLE `account` ( -- 创建一个表格
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL, -- 钱设置DECIMAL 小数点左边9位,小数点右边1位
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
INSERT INTO `account`(`name`,`money`) VALUES ('A',2000),('B',10000)-- 在表格中插入数据
-- 实现转账,转账是一个事务的操作。A-500,B+500
-- 手动处理事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务,后面的代码都放到这个事务之中,后面两行代码为一组事务
UPDATE `account` SET `money`=`money`-500 WHERE `name`='A'-- A-500
UPDATE `account` SET `money`=`money`+500 WHERE `name`='B'-- B+500
COMMIT;-- 如果上述代码成功了,则提交事务,事务一旦提交则持久化,不会被回滚了
ROLLBACK;-- 如果上述代码失败了,则回滚。要在没有提交事务之前回滚
SET autocommit = 1 -- 开启自动提交,回复默认值
7.索引
MySQL官方对索引的定义为:**索引(Index)是帮助MySQL高效获取数据的数据结构。**提取句子主干,就可以得到索引的本质:索引是数据结构。
7.1 索引的分类
-
主键索引 (PRIMARY KEY):唯一的标识,主键不可重复 ,只能有一个列作为主键
-
唯一索引 (UNIQUE KEY):避免重复的列出现,唯一索引可以重复,多个列都可以标识位为一索引
-
常规索引(KEY / INDEX):默认的,index,key 关键词来设置
-
全文索引 (FullText):在特定的数据库引擎下才有,以前只能在MyISAM,现在基本上都有了。快速定位数据
在一个表中主键索引 (PRIMARY KEY)只能有一个,而唯一索引可以有多个
7.2 索引的基础语法
-- ===================索引的基础语法使用===========================
-- 显示所有的索引信息
SHOW INDEX FROM `student`
-- 增加一个全文索引 ADD FULLTEXT INDEX 索引名(列名)
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname`(`studentname`)
-- explain 分析sql执行的状况
EXPLAIN SELECT *FROM `student` -- 常规索引,非全文索引
SELECT *FROM `student` WHERE MATCH(`studentname`) AGAINST ('张') -- 全文索引
-- 如何创建索引?
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后,增加索引
7.3 测试索引
-- 创建索引后查询数据消耗时间显著降低
-- 索引在小数据量的时候,用处不大,但是在大数据的时候,区别十分明显
-- =======================测试索引===================
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别(0:男;1:女)',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0' COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) -- 主键索引
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
-- sqlog可以用来写函数(了解即可)
-- 插入100万条数据 DELIMITER
DELIMITER $$-- 写函数之前必写的,表头
CREATE FUNCTION mock_data() -- 创建函数
RETURNS INT -- 创建的函数值返回int类型
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES (CONCAT('用户',i),
'2845965112@qq.com',
CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),
FLOOR(RAND()*2),
UUID(),
FLOOR(RAND()*100)
);
-- 插入语句
SET i = i+1;
END WHILE;
RETURN i;
END;
SELECT mock_data();
-- 没加索引,查询很慢,是用遍历查询
SELECT *FROM app_user WHERE `name`='用户9929'; -- 查询较慢
EXPLAIN SELECT *FROM app_user WHERE `name`='用户9929'; -- 分析信息,查了9929条
-- 创建索引,查询快,直接查的唯一定位索引
-- create index(FULL INDEX) 索引名 on 表(字段)
CREATE INDEX id_app_user ON app_user(`name`) -- 添加索引
SELECT *FROM app_user WHERE `name`='用户9929'; -- 创建索引速度变快了
EXPLAIN SELECT *FROM app_user WHERE `name`='用户9929'; -- 分析信息,唯一查询了一条
7.4索引原则
- 索引不是越多越好
- 不要对进程变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加载常用来查询的字段上
InnoDB的默认数据结构:Btree
http://blog.codinglabs.org/articles/theory-of-mysql-index.html
8.权限管理与备份
8.1 用户管理
sql可视化管理
用户表:mysql下的 mysql.user
用户的本质:对mysql.user这张表进行增删改查
-- 用户权限
-- 创建一个新的用户
CREATE USER luchao IDENTIFIED BY '123456';
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR luchao = PASSWORD('123456')
-- 重命名RENAME USER 原来名字TO 新的名字
RENAME USER luchao TO LuChao
-- 用户授权 ALL PRIVILEGES 全部的权限, 库.表。 *.*全部的权限
-- 除了给别人授权,其它的人都能改
GRANT ALL PRIVILEGES ON *.* TO LuChao;
-- 查看权限
SHOW GRANTS FOR LuChao
SHOW GRANTS FOR root@localhost -- 查看管理员的权限
-- root用户的权限,GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 删除(撤销)权限,在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM LuChao -- 撤销全部权限
-- 删除用户
DROP USER LuChao
8.2 mysql的备份
为什么要备份:
- 保证重要的数据不丢失
- 数据转移 A---->B
MySQl数据库备份的方式
- 直接拷贝物理文件
- 在sqlyog这种可视化软件直接导出 : 在想要导出的库与表中,右键–》备份/导出 --》备份表作为sql转储
- 使用命令行(cmd)导出 mysqldump 命令行使用
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名1 表名2 表名3 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
# 导入
# 登录的情况下 mysql -uroot -p123456 -- 注释:连接数据库,切换到指定数据库
# source 备份文件
source d:/a.sql
mysql -u用户名 -p密码 库名 < 备份文件
备份数据库,防止数据丢失,sql文件
9.规范数据库的设计
9.1为什么需要设计?
当数据库比较复杂,我们就需要设计了
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计
- 节省内存空间
- 保证数据库的完整型
- 方便我们开发系统
软件开发中,关于数据库的设计
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系E—R图
设计数据库的步骤:(个人博客)
- 收集信息,分析需求
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表(文章分类,谁创建的)
- 文章表(文章的信息)
- 友链表(友情链接信息)
- 自定义表(系统消息,某个关键的字,或者一些主子段) key :value
- 评论表
- 标示实体(把需求落地到某个字段)
- 标示实体之间关系
9.2三大范式
为什么需要数据规范化
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
第一范式(1NF)
保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
每张表只描述一件事情
第三范式(3NF)
前提:满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关
规范性和性能的问题
-
关联查询的表不得超过三张表
-
考虑商业化的需求和目标,(成本,用户体验!)数据库的性能更加重要
-
在规范性能的问题的时候,需要适当的考虑一下 规范性
-
故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询 )
-
故意增加一些计算列(从大数据量降为小数据量的查询:索引
10、JDBC(重点)
10.1什么是JDBC ?
SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(java操作数据库)规范,俗称JDBC。这些规范的实现有具体的厂商去做对于开发人员,我们只需要掌握JDBC接口的操作即可!
倒入两个包:java.sql javax.sql
还需要导入一个数据库驱动包mysql-connector-java-版本号jar
这张图我是直接复制粘贴的
10.2 编写JDBC程序
创建测试数据库项目,在sqlyog里面运行
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE `users`(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
步骤:
1.加载驱动
2.填写用户信息
3.连接。连接成功,数据库对象 Connection 代表数据库
4.执行SQL的对象
5.执行SQL的对象,去执行SQL,可能存在结果,查看返回结果
import com.mysql.jdbc.Driver;
import java.sql.*;
public class JdbcFirst {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1. 加载驱动
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());推荐使用下一个
Class.forName("com.mysql.jdbc.Driver");//加载驱动,固定写法,需要异常抛出。
//2.设置用户信息、密码和url。
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";// 固定操作
String username="root";
String password="123456";
//Oracle 1521
// jdbc:oralce:thin:@localhost:1521:sid
//3.连接。连接成功返回数据库对象。Connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);//驱动管理
//connection代表数据库
//数据库设置自动提交 connection.setAutoCommit();
//事务提交 connection.commit();
//事务回滚 connection.rollback();
//4.创建sql对象statement,用来下一步执行sql对象
Statement statement = connection.createStatement();
// statement.executeQuery();//查询操作,返回resultset
// statement.execute();//执行任何sql
// statement.executeUpdate();//更新,插入,删除,返回一个受影响的行数
//5.用sql对象statement执行sql语句。可能存在结果,查看返回的结果。
String sql ="SELECT *FROM `users`";
ResultSet resultSet = statement.executeQuery(sql);//执行sql对象的statement查询操作,返回结果。
// ResultSet 查询的结果集,封装了所以的查询结果。结果集中封装了查询对象
// resultSet.getObject();//在不知道列类型下使用
// resultSet.getString();//如果知道则指定使用
// resultSet.getInt();
//还可以用于遍历
// resultSet.next(); //移动到下一个
// resultSet.afterLast();//移动到最后
// resultSet.beforeFirst();//移动到最前面
// resultSet.previous();//移动到前一行
// resultSet.absolute(row);//移动到指定行
while(resultSet.next()){//用循环查看结果,如果有下一个数据
//可以用resultSet.getObject是实在不知道对面属性的时候用resultSet.getObject("mysql列名"));
System.out.println("id = "+resultSet.getObject("id"));
System.out.println("name = " +resultSet.getObject("NAME"));
System.out.println("pwd= "+resultSet.getObject("PASSWORD"));
System.out.println("email = "+resultSet.getObject("email"));
System.out.println("birth = "+resultSet.getObject("birthday"));
System.out.println("====================================");
}
//6.释放连接,从后面往前释放对象。释放资源必须做
resultSet.close();
statement.close();
connection.close();
}
}
10.3 JDBC的增删改查
Jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
statement对象会引发SQL注入问题
Statement对象的executeUpdate方法,用于向数据库发送增、删、改的sq|语句, executeUpdate执行完后, 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发生查询语句,executeQuery方法返回代表查询结果的ResultSet对象。
插入insert
Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("插入成功");
}
删除delete
Statement statement = connection.createStatement();
String sql = "delete from user where id =1";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("删除成功");
}
修改
Statement statement = connection.createStatement();
String sql = "update user set name ='' where name = ''";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("修改成功");
}
读取,查询
Statement statement = connection.createStatement();
String sql = "select * from user where id =1";
ResultSet rs= statement.executeQuery(sql);
if(rs.next()){
System.out.println("");
}
代码实现:避免重复使用代码
1.提取工具类
编写配置文件:db.properties 文件
driver=com.mysql.jdbc.Driver
url= jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
编写工具类:JdbcUtils
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import com.mysql.jdbc.Driver;
import java.sql.*;
//工具包,引入db.properties配置文件
//读取配置文件
public class JdbcUtils {
private static String driver=null;
private static String url=null;
private static String username=null;
private static String password=null;
static{
try{
//读取配置文件,返回一个输入流
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);//加载流,最后所有的信息都在properties对象里面
//读取db.properties的信息
driver=properties.getProperty("driver");//拿db.properties的driver数据
url=properties.getProperty("url");//拿db.properties的driver数据
username=properties.getProperty("username");//拿db.properties的driver数据
password=properties.getProperty("password");//拿db.properties的driver数据
//加载驱动。驱动只用加载一次
Class.forName(driver);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
//获取连接方法
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放连接方法
public static void release(Connection conn, Statement st, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2.使用工具类
插入
import com.luchao.demo02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
//由于在try-dinalli里面connection不会释放,所以得写出去
Connection conn=null;//数据库对象初始化为null
Statement st=null;//创建sql对象
ResultSet rs=null;//ResultSet Statement运行的返回集。查询的结果集,封装了所以的查询结果。结果集中封装了查询对象
//使用utils简化
try {
conn= JdbcUtils.getConnection();//获取数据库
st= conn.createStatement();//获得sql执行对象
String sql="INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" +
"VALUES(5,'kuangshen','123456','24736743@qq.com','2021-01-01')";
int i =st.executeUpdate(sql);// 插入
if(i>0)
{
System.out.println("插入成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
//由于在try-dinalli里面定义connection不会释放,所以得初始化,写在try外面
//释放连接
JdbcUtils.release(conn,st,rs);
}
}
}
删除
package com.luchao.demo02;
import com.luchao.demo02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
//由于在try-dinalli里面connection不会释放,所以得写出去
Connection conn=null;//数据库对象初始化为null
Statement st=null;//创建sql对象
ResultSet rs=null;//ResultSet Statement运行的返回集。查询的结果集,封装了所以的查询结果。结果集中封装了查询对象
//使用utils简化
try {
conn= JdbcUtils.getConnection();//获取数据库
st= conn.createStatement();//获得sql执行对象
String sql="DELETE FROM `users` WHERE `id` = 5";
int i =st.executeUpdate(sql);// 插入
if(i>0)
{
System.out.println("删除成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
//由于在try-dinalli里面定义connection不会释放,所以得初始化,写在try外面
//释放连接
JdbcUtils.release(conn,st,rs);
}
}
}
改
package com.luchao.demo02;
import com.luchao.demo02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
//由于在try-dinalli里面connection不会释放,所以得写出去
Connection conn=null;//数据库对象初始化为null
Statement st=null;//创建sql对象
ResultSet rs=null;//ResultSet Statement运行的返回集。查询的结果集,封装了所以的查询结果。结果集中封装了查询对象
//使用utils简化
try {
conn= JdbcUtils.getConnection();//获取数据库
st= conn.createStatement();//获得sql执行对象
String sql="UPDATE `users` SET `NAME`='chaolu' WHERE id =4";
int i =st.executeUpdate(sql);// 插入
if(i>0)
{
System.out.println("改成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
//由于在try-dinalli里面定义connection不会释放,所以得初始化,写在try外面
//释放连接
JdbcUtils.release(conn,st,rs);
}
}
}
查
package com.luchao.demo02;
import com.luchao.demo02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
//由于在try-dinalli里面connection不会释放,所以得写出去
Connection conn=null;//数据库对象初始化为null
Statement st=null;//创建sql对象
ResultSet rs=null;//ResultSet Statement运行的返回集。查询的结果集,封装了所以的查询结果。结果集中封装了查询对象
//使用utils简化
try {
conn= JdbcUtils.getConnection();//获取数据库
st= conn.createStatement();//获得sql执行对象
String sql="select *from users where id =1";
rs = st.executeQuery(sql);//查询完毕会返回一个结果集ResultSet对象。id,name pwd emalil birth都在里面
while(rs.next())//判断是否有下一行
{
System.out.println("id = "+rs.getInt("id"));
System.out.println("name = " +rs.getString("NAME"));
System.out.println("pwd= "+rs.getString("PASSWORD"));
System.out.println("email = "+rs.getString("email"));
System.out.println("birth = "+rs.getString("birthday"));
//或者System.out.println("id = "+rs.getObject("id"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//由于在try-dinalli里面定义connection不会释放,所以得初始化,写在try外面
//释放连接
JdbcUtils.release(conn,st,rs);
}
}
}
10.4 sql注入
SQL存在漏洞,会被攻击,导致数据泄露
通过字符串的拼接,在登录界面对数据库进行一系列非法操作
Statement创建的对象会有SQL注入问题,如下:
import com.luchao.demo02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQL注入 {
public static void main(String[] args) {
//正常登录login("chaolu","123456");
//sql注入,拼接字符串,会让信息泄露
login("'or'1=1","123456");
}
//写一个登录业务
public static void login(String username,String password){
//由于在try-dinalli里面connection不会释放,所以得写出去
Connection conn=null;//数据库对象初始化为null
Statement st=null;//创建sql对象
ResultSet rs=null;//ResultSet Statement运行的返回集。查询的结果集,封装了所以的查询结果。结果集中封装了查询对象
//使用utils简化
try {
conn= JdbcUtils.getConnection();//获取数据库
st= conn.createStatement();//获得sql执行对象
String sql="select *from users where `NAME`='"+username+"' and `password`='"+password+"'";
rs = st.executeQuery(sql);//查询完毕会返回一个结果集ResultSet对象。id,name pwd emalil birth都在里面
while(rs.next())//判断是否有下一行
{
System.out.println("name = " +rs.getString("NAME"));
System.out.println("pwd= "+rs.getString("PASSWORD"));
System.out.println("========================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//由于在try-dinalli里面定义connection不会释放,所以得初始化,写在try外面
//释放连接
JdbcUtils.release(conn,st,rs);
}
}
}
解决SQL注入问题,使用preparedStatement对象,防止SQL注入
10.5 PreparedStatement对象
PreparedStatement防止SQL注入,并且效率更高了
PreparedStatement的本职:把传递进来的参数当做字符
假设其中有转移字符,会被直接转义掉。比如说‘ ‘引号会被直接转义
PreparedStatement与Statement区别:
Statement:
//先创建sql对象,在编译sql。
conn= JdbcUtils.getConnection();//获取数据库
st= conn.createStatement();//获得sql执行对象
String sql="INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" +
"VALUES(5,'kuangshen','123456','24736743@qq.com','2021-01-01')";
int i =st.executeUpdate(sql);// 插入
PreparedStatement:
conn = JdbcUtils.getConnection();//在demo02里面
//先编写sql语句,使用?占位符代替参数,然后在创建sql对象
String sql="INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql);//创建sql对象,需要预编译sql参数,先写sql,不执行
//手动给参数赋值
st.setInt(1,5);//第一个问号,赋值5
st.setString(2,"kuangshen");//第二个问号,赋值"kuangshen"
st.setString(3,"123456");
st.setString(4,"2453685226@qq.com");
//Date().getTime()这是java的时间在java.util.Date()下面,要转换成sql的时间在java.sql.Date
//获得时间戳
st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
//执行
int i=st.executeUpdate();
if(i>0){
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
prepareStatement插入
package com.luchao.demo03;
import com.luchao.demo02.utils.JdbcUtils;
import java.sql.*;
//使用preparedStatement 防止SQL注入。denmo02全是Statement会引发SQL注入问题
public class TestInsert {
public static void main(String[] args) {
Connection conn =null;
PreparedStatement st =null;
try {
conn = JdbcUtils.getConnection();//在demo02里面
//先编写sql语句,使用?占位符代替参数
String sql="INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql);//需要预编译sql参数,先写sql,不执行
//手动给参数赋值
st.setInt(1,5);//第一个问号,赋值5
st.setString(2,"kuangshen");//第二个问号,赋值"kuangshen"
st.setString(3,"123456");
st.setString(4,"2453685226@qq.com");
//Date().getTime()这是java的时间在java.util.Date()下面,要转换成sql的时间在java.sql.Date
//获得时间戳
st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
//执行
int i=st.executeUpdate();
if(i>0){
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
prepareStatement删除
package com.luchao.demo03;
import com.luchao.demo02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDelete {
public static void main(String[] args) {
Connection conn =null;
PreparedStatement st =null;
try {
conn = JdbcUtils.getConnection();//在demo02里面
//先编写sql语句,使用?占位符代替参数
String sql="delete from `users` where id=?";
st = conn.prepareStatement(sql);//需要预编译sql参数,先写sql,不执行
//手动给参数赋值
st.setInt(1,5);//第一个问号,赋值5
//执行
int i=st.executeUpdate();
if(i>0){
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
prepareStatement改
package com.luchao.demo03;
import com.luchao.demo02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection conn =null;
PreparedStatement st =null;
try {
conn = JdbcUtils.getConnection();//在demo02里面
//先编写sql语句,使用?占位符代替参数
String sql="update `users` set `NAME`=? where id=?";
st = conn.prepareStatement(sql);//需要预编译sql参数,先写sql,不执行
//手动给参数赋值
st.setString(1,"luchao");
st.setInt(2,5);//第一个问号,赋值5
//执行
int i=st.executeUpdate();
if(i>0){
System.out.println("更新成功");
}else {
System.out.println("更新失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
prepareStatement查
package com.luchao.demo03;
import com.luchao.demo02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class TestSelect {
public static void main(String[] args) {
Connection conn =null;
PreparedStatement st =null;
ResultSet rs=null;
try {
conn = JdbcUtils.getConnection();
//编写sql
String sql="select *from `users` where id = ?";
//预编译,创建sql对象
st = conn.prepareStatement(sql);
//传递参数
st.setInt(1,4);
//执行查询executeQuery返回ResultSet
rs=st.executeQuery();
while(rs.next()) {
System.out.println( rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);//释放连接,也就是释放对象
}
}
}
防止sql注入,用的是10.4的sql的例子
package com.luchao.demo02;
import com.luchao.demo02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQL注入 {
public static void main(String[] args) {
//正常登录login("chaolu","123456");
//sql注入,拼接字符串,会让信息泄露
login("'or'1=1","123456");
}
//写一个登录业务
public static void login(String username,String password){
//由于在try-dinalli里面connection不会释放,所以得写出去
Connection conn=null;//数据库对象初始化为null
Statement st=null;//创建sql对象
ResultSet rs=null;//ResultSet Statement运行的返回集。查询的结果集,封装了所以的查询结果。结果集中封装了查询对象
//使用utils简化
try {
conn= JdbcUtils.getConnection();//获取数据库
//编写sql
String sql="select *from users where `NAME`='"+username+"' and `password`='"+password+"'";
st= conn.createStatement();//预编译,获得sql执行对象
rs = st.executeQuery(sql);//查询完毕会返回一个结果集ResultSet对象。id,name pwd emalil birth都在里面
while(rs.next())//判断是否有下一行
{
System.out.println("name = " +rs.getString("NAME"));
System.out.println("pwd= "+rs.getString("PASSWORD"));
System.out.println("========================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//由于在try-dinalli里面定义connection不会释放,所以得初始化,写在try外面
//释放连接
JdbcUtils.release(conn,st,rs);
}
}
}
10.6、IDEA连接MySql数据库
点击"应用",然后"确定" 。然后进行加载表的操作
10.7 JDBC事务
要么都成功,要么都失败
ACID原则
A原子性:要么全部完成,要么都不完成
C一致性:结果总数不变
I隔离性:多个进程互不干扰
D持久性:一旦提交不可逆,持久化到数据库了
隔离性的问题:
脏读: 一个事务读取了另一个没有提交的事务
不可重复读:在同一个事务内,重复读取表中的数据,表发生了改变
虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致
使用sql来实现事务
-- 实现转账,转账是一个事务的操作。A-500,B+500
-- 手动处理事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务,后面的代码都放到这个事务之中,后面两行代码为一组事务
UPDATE `account` SET `money`=`money`-500 WHERE `name`='A'-- A-500
UPDATE `account` SET `money`=`money`+500 WHERE `name`='B'-- B+500
COMMIT;-- 如果上述代码成功了,则提交事务,事务一旦提交则持久化,不会被回滚了
ROLLBACK;-- 如果上述代码失败了,则回滚。要在没有提交事务之前回滚
SET autocommit = 1 -- 开启自动提交,回复默认值
使用Java来实现事务
try {
conn=JdbcUtils.getConnection();//获取数据库
//事务的操作
//1.关闭数据库的自动提交功能,自动开启事务。包含了SET autocommit = 0;
conn.setAutoCommit(false);
//2.SQL语言的编写执行。下面是一组业务
String sql1="update `account` set `money` =`money`-500 where name ='A'" ;
st=conn.prepareStatement(sql1);//st获取sql1的对象,为sql1执行做准备
st.executeUpdate();//执行st语句,也就是执行sql1的语句
String sql2="update `account` set `money` =`money`+500 where name ='B'" ;
st=conn.prepareStatement(sql2);//st获取sql2的对象,为sql2执行做准备
st.executeUpdate();//执行st语句,也就是执行sql2的语句
//3.业务完毕,提交事务 持久化
conn.commit();
System.out.println("执行成功!");
} catch (SQLException e) {
try {
//4.如果失败就回滚事务
System.out.println("执行失败");
conn.rollback();//如果失败就回滚事务
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
e.printStackTrace();
}finally {
//5.释放资源 开启自动提交,回复默认值 包含了SET autocommit = 1
JdbcUtils.release(conn,st,rs);
}
使用Java来实现事务,完整的代码
package com.luchao.demo04;
import com.luchao.demo02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//利用Java来实现事务
public class TestTransaction {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st =null;
ResultSet rs=null;
try {
conn=JdbcUtils.getConnection();//获取数据库
//事务的操作
//1.关闭数据库的自动提交功能,自动开启事务SET autocommit = 0;
conn.setAutoCommit(false);
//2.SQL语言的编写执行。下面是一组业务
String sql1="update `account` set `money` =`money`-500 where name ='A'" ;
st=conn.prepareStatement(sql1);//st获取sql1的对象,为sql1执行做准备
st.executeUpdate();//执行st语句,也就是执行sql1的语句
String sql2="update `account` set `money` =`money`+500 where name ='B'" ;
st=conn.prepareStatement(sql2);//st获取sql2的对象,为sql2执行做准备
st.executeUpdate();//执行st语句,也就是执行sql2的语句
//3.业务完毕,提交事务 持久化
conn.commit();
System.out.println("执行成功!");
} catch (SQLException e) {
try {
//4.如果失败就回滚事务
System.out.println("执行失败");
conn.rollback();//如果失败就回滚事务
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally {
//5.释放资源,开启自动提交,回复默认值SET autocommit = 1
JdbcUtils.release(conn,st,rs);
}
}
}
11.JDBC数据库连接池
数据库连接–执行完毕–释放。是十分浪费系统资源的,所以使用池化技术
池化技术:准备一些预先的资源,过来就连接预先准备好的
举例子:
假设有15个员工(员工是预先资源,顾客来了直接服务),10个员工是常用的也就是最少的去接待顾客,另外5个是备用的,当顾客超过15个时候需要排队等待。
常用连接数 10
最少连接数:10 跟常用连接数一样
最大连接数 :15 业务最高承载上限
如果超过最大连接数则排队等待
等待超时:100ms,如果一个链接等待超过100ms则断开
连接池,实现一个接口 DateSource(在sql类下面)
常用的开源数据库连接池: DBCP C3P0 Druid: 阿里巴巴
使用了这些数据库连接池之后,我们在项目开发中就不需要编写连接数据库的代码了。
(不用再写conn=JdbcUtils.getConnection,JdbcUtils.getConnection也是我们自己编写的,都不用写了)
我们这边就学习DBCP C3P0
11.1 DBCP开源数据库连接池
1.使用commons-dbcp-1.4.jar commons-pool-1.6.jar
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
#初始化连接
initialSize=10
#最大连接数量
maxActive=50
#最大空闲连接
maxIdle=20
#最小空闲连接
minIdle=5
#超时等待时间以毫秒为单位 6000毫秒/1000等于60秒
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:【属性名=property;】
#注意:user 与 password 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8
#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的只读(read-only)状态。
#如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix)
defaultReadOnly=
#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_COMMITTED
3.编写工具类JdbcUtils_DBCP
package com.luchao.demo05.utils;
import com.luchao.demo02.utils.JdbcUtils;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils_DBCP {
private static DataSource dataSource=null;
static{
try{
//读取配置文件,返回一个输入流
InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcp.properties");
Properties properties = new Properties();
properties.load(in);//加载流,最后所有的信息都在properties对象里
//创建数据源 工厂模式 --》去创建对象
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection() ;//从数据源中获取连接
}
//释放连接
public static void release(Connection conn, Statement st, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- 编写测试类
package com.luchao.demo05;
import com.luchao.demo05.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDBCB {
//插入操作,与用PreparedStatemrnt相差不大,只是将JdbcUtils改为JdbcUtils_DBCP
public static void main(String[] args) {
Connection conn =null;
PreparedStatement st =null;
try {
conn = JdbcUtils_DBCP.getConnection();//在demo02里面
//先编写sql语句,使用?占位符代替参数
String sql="INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql);//需要预编译sql参数,先写sql,不执行
//手动给参数赋值
st.setInt(1,6);//第一个问号,赋值6
st.setString(2,"kuangshen");//第二个问号,赋值"kuangshen"
st.setString(3,"123456");
st.setString(4,"2453685226@qq.com");
//Date().getTime()这是java的时间在java.util.Date()下面,要转换成sql的时间在java.sql.Date
//获得时间戳
st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
//执行
int i=st.executeUpdate();
if(i>0){
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils_DBCP.release(conn,st,null);
}
}
}
11.2 C3P0开源数据库连接池
1.需要用到的jar包c3p0-0.9.5.5.jar 与 mchange-commons-java-0.2.19.jar
2.配置文件c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!--
c3p0的缺省(默认)配置
如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省(默认)
-->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<!--
c3p0的命名配置
如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");这样写就表示使用的是name是MySQL
-->
<name-config name="MySQL">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</name-config>
</c3p0-config>
3.编写工具类JdbcUtils_C3P0
package com.luchao.demo06.utils;
import com.luchao.demo05.utils.JdbcUtils_DBCP;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtils_C3P0 {
private static ComboPooledDataSource dataSource=null;
static{
try{
//后缀名为xml的,不用读
//创建对象
dataSource = new ComboPooledDataSource("MySQL"); //配置文件的写法
// dataSource=new ComboPooledDataSource();//代码版配置,不建议使用
// dataSource.setDriverClass();
// dataSource.setUser();
// dataSource.setPassword();
// dataSource.setJdbcUrl();
// dataSource.setMaxPoolSize();
// dataSource.setMinPoolSize();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection() ;//从数据源中获取连接
}
//释放连接
public static void release(Connection conn, Statement st, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- 编写测试类
package com.luchao.demo06;
import com.luchao.demo05.utils.JdbcUtils_DBCP;
import com.luchao.demo06.utils.JdbcUtils_C3P0;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TesstC3P0 {
public static void main(String[] args) {
//插入操作,与用PreparedStatemrnt相差不大,只是将JdbcUtils改为JdbcUtils_C3P0
Connection conn =null;
PreparedStatement st =null;
try {
conn = JdbcUtils_C3P0.getConnection();
//先编写sql语句,使用?占位符代替参数
String sql="INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql);//需要预编译sql参数,先写sql,不执行
//手动给参数赋值
st.setInt(1,7);//第一个问号,赋值6
st.setString(2,"C3P0");//第二个问号,赋值"kuangshen"
st.setString(3,"123456");
st.setString(4,"2453685226@qq.com");
//Date().getTime()这是java的时间在java.util.Date()下面,要转换成sql的时间在java.sql.Date
//获得时间戳
st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
//执行
int i=st.executeUpdate();
if(i>0){
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils_C3P0.release(conn,st,null);
}
}
}
结论:无论用什么数据源,本质还是一样的,DataSource接口不会变,方法就不会变