狂神说数据库笔记

1.初识数据库

1.1、 什么是数据库

数据库（DB， DateBase）

概念：数据仓库，软件，安装在操作系统上（Windows，linux，mac…）之上，可以存储大量数据。500万左右！

作用：存储数据，管理数据

1.2、数据库分类

关系型数据库（SQL）

Mysql, Oracle, SQl server, DB2, SQLLlite

通过表和表之间，行和列之间的关系进行数据的存储，

非关系型数据库 （NoSQL） Not Only SQl

Redis, MongDB

非关系型数据库，对象存储，通过对象自身的属性来决定

DBMS(数据库管理系统)

• 数据库的管理软件，管理、维护和获取数据
• MySQl、数据库管理系统

1.3、MySQl简介

MySQl是一个关系型数据库管理系统

前世：瑞典Oracle旗下产品

今生：属于Oracle旗下产品

MySQl是最好的RDBMS（Relational Database Management System， 关系数据型数据库）之一。

开源的数据库软件

体积小、速度快

中小型网站、或者大型网站，集群！

安装建议：

1.尽量不要exe，会进入注册表

2.尽可能使用压缩包安装

1.4、连接数据库

命令行连接！

mysql -uroot -p123456 --连接数据库

flush privileges -- 刷新权限

-----------------------------------
--所有的语句都使用；结尾
show databases; --查看所有的数据库

mysql> ues school; --切换数据库 ues 数据库名
Database changed

-----------------------------------
show tables; --查看数据库中所有的表
describe student; --显示数据库中的指定表的信息

------------------------------------
create database westos; --创建一个数据库

exit; --退出连接

--单行注释（SQL本来的注释）
/* 多行注释
dasj
*/

数据库xxx语言 CRUD增删改查 （业务程序猿）

DDl 定义

DMl 操作

DQL 查询

DCL 控制

2.操作数据库

操作数据库>操作数据库中的表>操作数据库中的数据

mysql关键字不区分大小写

2.1 操作数据库（了解）

1.创建数据库

CREATE DATABASES [ IF NOT EXISTS] 某某

[]表示可选

2.删除数据库

DROP DATABASES IF EXISTS westos

3.使用数据库

如果你的表名或者字段名是一个特殊字符，就需要带 ``

USE school

SELECT `user` FROM westos

4.查看数据库

SHOW DATABASES --查看所有的数据库

对比，Navicat的可视化操作

• 对照可视化历史记录查看sql
• 固定的语法或关键字必选要强行记住

2.2 数据库的列类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中度大小的数据 3个字节
• int 标准的数据 4个字节 INT(M) ，M表示最大显示宽度，与存储大小或类型包含的值的范围无关
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串形式的浮点数 （金融计算的时候，一般使用decimal）

字符串

• char 字符串固定大小 0~255
• varchar 可变字符串 0~65535
• tinytext 微信文本 2^8 - 1
• text 文本串 2^16 - 1

时间日期

java.unti.Data

• data YYYY-MM-DD ，日期格式
• time HH：mm ：ss 时间格式
• datetime YYYY-MM-DD HH：mm：ss 最常用的时间格式
• timestamp 时间戳， 1970.1.1到现在的毫秒数！
• year 年份表示

null

• 没有值，未知
• 注意，不要使用NULL进行数值运算，结果为NULL

2.3、数据库的字段属性（重点）

Unsigned:

• 无符号的整数

• 声明了该列不能声明为负数

zerofill：

• 0填充
• 不足的位数，用0填充

自增

• 通常理解为自增，自动在上一条记录的基础上+1（默认）
• 通常用来设计唯一的主键~index，必须是整数类型
• 可以自定义设计主键自增的起始值和步长

非空 NUT NULL

• 假设设置为not null ，如果不给他赋值，就会报错！
• NULL， 如果不填写值，默认就是NULL

默认

• 设置默认的值
• sex， 默认值为男， 如果不指定该列的值，就会有默认的值

阿里巴巴规范

/* 每一个表，都必须存在以下五个字段!未来做项目用的，表示一个记录存在的意义
id 主键
`version`  乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_updata 修改时间
*/

2.4、创建数据库

-- 目标：创建一个school数据库
-- 创建学生表（列，字段） 使用SQL创建
-- 学号int 登录密码varchar（20） 姓名，性别varchar（2），出生日期（datatime），家庭住址，email

-- 注意点，使用英文（）， 表的名称 和 字段尽量使用 `` 括起来
-- 字符串使用 单引号括起来
-- AUTO_INCREMENT 自增
-- 所有的语句后面加， （英文的），最后一个不用加
-- DELETE 默认值 COMMENT 注释
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键
CREATE TABLE if not EXISTS `student`(
	`id` INT(4) NOT NUll AUto_increment COMMENT '学号',
	`name` VARCHAR(30) NOT NUll DEFAULT '匿名' COMMENT '姓名',
	`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULl COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

CREATE TABLE [IF NOT EXISTS] `表明`(
	`字段名` 列类型[属性] [索引] [注释],
    `字段名` 列类型[属性] [索引] [注释],
    ......
    `字段名` 列类型[属性] [索引] [注释]
)[表类型][字符集设置][注释]

常用命令

SHOW CREATE DATABASE school -- 查看创建数据库的语句 
SHOW CREATE TABLE student -- 查看数据库表的定义语句
DESC student -- 显示表的结构

2.5、数据表的类型

-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

常规使用操作：

• MYISAM 节约空间，速度快
• INNODB 安全性高，事务的处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在data目录下

本质话说文件的存储！

MySQl引擎在物理文件上的区别

• InnoDB 在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
• MYISAM 对应文件
  • *.frm -表结构的定义文件
  • *.MYD 数据文件（data）
  • *.MYI 索引文件

设置数据库表的字符集编码

CHARSET=utf8

不设置的话，会是MySQL默认的字符集编码~（不支持中文）

mysql的默认编码是Latin1，不支持中文

在my.ini中配置默认的编码

character-set-server=utf8

2.6、删改数据库

修改表

-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE sys_student RENAME As sys_student_info
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE sys_student_info ADD age INT(11)

-- 修改表的字段（重命名，修改约束）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[] modify
ALTER TABLE sys_student_info MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE sys_student_info CHANGE age age1 INT(1) -- 修改字段名

-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名 
ALTER TABLE sys_student_info DROP age1

删除表

-- 删除表（如果存在）
-- DROP TABLE IF EXISTS 表名
DROP TABLE IF EXISTS sys_student_info

所有的创建和删除操作尽量加上判断，以免报错

注意点：

• `` 字段名，使用这个这个包裹！
• 注释 – /**/
• sql关键字大小写不敏感
• 所有的符号用英语

3.MySQL数据管理

3.1、外键（了解即可）

方式一：在创建表的时候，增加约束（麻烦，比较复杂）

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

-- 学生表的gradeid 字段 要去引用年级表的 gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） reference 应用
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '年级',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN key (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）（即先删除school表，再删除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

SHOW CREATE TABLE student

-- 学生表的gradeid 字段 要去引用年级表的 gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） reference 应用
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '年级',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `Fk_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪个表(哪个字段)

以上操作都是物理外键，数据库级别的外键，不建议是由！（避免数据库最多造成困扰）

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据）列（字段）
• 我们想使用多张表的数据，想使用外键（程序去实现）

3.2、DML语言（全部记住）

**数据库意义：**数据存储，数据管理

DML语言：数据操作语言

• insert
• update
• delete

3.3 、添加

-- 插入语句（添加）
-- INSERT INTO 表名（{字端名1，字段2，字段3}） VALUES（'值1','值2','值3',...)
INSERT INTO `grade`(`gradeName`) VALUES('大四')

-- 由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配）
INSERT INTO `grade` VALUES ('大三') -- 报错，字段不匹配,相当于下面
INSERT INTO `grade`(`gradeid`,`gradeName`)  VALUES ('大三',null)

-- 一般写插入语句,数据一定和字段一一对应

-- 插入多个字段,值值用()隔开
INSERT INTO `grade`(`gradeName`) VALUES('大二'),('大一')


INSERT INTO `student`(`name`) VALUES ('张三')

INSERT INTO `student`(`name`, `password`) VALUES ('张三', '2434')

INSERT INTO `student`(`name`, `password`, `sex`) VALUES ('张三', '2434','女')

语法：INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)

注意事项：

1. 字段和字段之间使用 英文逗号 隔开
2. 字段是可以省略，但是后面的值必须要一一对应，不能少
3. 可以同时插入多条语句，VALUES后面的值，需要用**，**隔开即可 valuse(),()

3.4、修改

update 修改谁（条件） set 原来的值 = 新值

-- 修改学员名字
UPDATE `student` SET `name` = '漠殇' WHERE id = 1;

-- 不指定条件的情况下，会改动所有表！
UPDATE `student` SET `name` = '1001001'

-- 修改多个属性，逗号隔开
UPDATE `student` SET `name` = '漠殇', email = '1243242@qq.com' WHERE id = 1

-- 语法：
-- UPDATE 表名 SET colnum_name = value,[colnum_name = value, ....] where[条件]

条件：where子句 运算符 id等于某个值，大于某个值，在某个区间内修改…

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5<>6	true
>
<
<=
>=
BETWEEN … AND,	闭合区间内	[2,5]	[2,5]
AND	&&	5>1 and 1>2	false
OR	||	5>1 or 1>2	true

语法：UPDATE 表名 set colnum_name = value,[colnum_name = value,…] where[条件]

注意：

1. colnum_name 是数据库的列，尽量带上``
2. 条件：筛选的条件，如果没有指定，则会修改所有的列
3. value，是一个具体的值，也可以是一个变量
4. 多个设置的属性之间，使用英文逗号隔开

UPDATE `student` SET `birthday` = CURRENT_TIME WHERE name = '1001001' AND sex = '女'

3.5、删除

delete命令

语法 delete from 表名 [where 条件]

-- 删除数据 （避免这样写）
DELETE FROM `student`

-- 删除指定数据
DELETE FROM `student` WHERE `id` = 1

TRUNCATE命令

作用：完全清空一个数据库表，表的结构和索引约束不会变

-- 清空表 
TRUNCATE `grade`

DELETE 和 TRUNCATE 区别

• 相同点：都能删除数据，都不会删除表结构
• 不同
  • TRUNCATE重新设置 自增列 计数器会归零
  • TRUNCATE 不会影响事务

-- 测试delete 和TRUNCATE区别
CREATE TABLE `test` (
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAR(10) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

INSERT INTO `test`(`coll`) VALUES ('1'),('2'),('3')

DELETE FROM `test` -- 不会影响自增
TRUNCATE TABLE `test` -- 自增会归零

了解即可：DELETE删除的问题，重启数据库，现象

• innoDB 自增列会重1开始（存在内存当中，不会丢失）
• MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）

4.DQL查询数据（最重点）

4.1、DQL

（data query LANGUAGE：数据查询语言）

• 所有的查询操作都用他 select
• 简单的查询，复杂的查询都能做
• 数据库中最核心的语言，最重要的语言
• 使用频率最高的语言

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 字段 FROM 表
SELECT * From `student`

-- 查询指定字段
SELECT `studentno`,`studentname` FROM `student`

-- 别名，给结果起一个名字 AS, 可以给字段起别名，也可以给表起别名
SELECT `studentno` AS 学号, `studentname` AS 学生姓名 FROM `student` AS s

-- 函数 Concat(a,b)
SELECT CONCAT('姓名:', `studentname`) AS 新名字 FROM `student`

语法SELECT 字段... FROM 表

有的时候，列名字不是那么的见名知意。我们就可以起别名 AS 字段名 as 别名 表名 as 别名

去重：distant

作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM `result` -- 查询全部的考试成绩
SELECT `studentno` FROM `result` -- 查询有哪些同学参加了考试
SELECT DISTINCT `studentno` FROM `result` -- 发现重复数据，去重

数据库的列（表达式）

SELECT VERSION() -- 查询系统版本 (函数）
SELECT 100*3-1 AS 计算结果 -- 用来计算 （表达式）
SELECT @@auto_increment_increment -- 查询自增的步长 （变量）

-- 学员考试成绩 +1 分查看
SELECT `studentno`,`studentresult`+ 1 AS '提分后' FROM `result`

数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量

selsect 表达式from 表

4.3、Where 条件子句

作用：检索数据中符合条件的值

搜索的条件由一个或者多个表达式组成 。 结果都是布尔值

运算符	语法	描述
and &&	a and b a&&b	逻辑与
or ||	a or b a||b	逻辑或
not !	not a !a	逻辑非

SELECT `Studentno`, `studentresult` FROM result -- 列也不区分大小写

-- 查询考试成绩在95~100分之间
SELECT `studentno`, `studentresult` FROM result
WHERE studentresult >= 95 And studentresult <= 100

-- and &&
SELECT `studentno`, `studentresult` FROM result
WHERE studentresult >= 95 && studentresult <= 100

-- 模糊查询（区间）
SELECT `studentno`, `studentresult` FROM result
WHERE studentresult BETWEEN 95 And 100

-- 除了1000 号学生之外的成绩
SELECT `studentno`, `studentresult` FROM result
WHERE studentno != 1000;

-- != NOT
SELECT `studentno`, `studentresult` FROM result
WHERE not studentno = 1000;

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null
is not null	a is not null
BETWEEN AND	a between b and c	a在b和c之间
Like	a like b	sql匹配，如果a匹配b，结果为真
In	a in(a1,a2,a3…)	假设a在a1，a2，a3…其中一个，结果为真

-- =======================模糊查询======================
-- 查询姓刘的同学
-- like 结合 %(代表0到任意个字符） _（一个字符）
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentname` LIKE '张%'

-- 查询修张的同学，名字后面只有一个字的
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentname` LIKE '张_'

-- 查询修张的同学，名字后面只有两个个字的
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentname` LIKE '张__'

-- 查询名字中间带有伟的 %伟%
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentname` LIKE '%伟%'

-- =====in =====
-- 查询1001，1002，1003号学员
SELECT `studentno`, `studentname` FROM `student`
WHERE `studentno` IN(1001, 1002, 1003)

-- 查询在北京的学员
SELECT `studentno`, `studentname` FROM `student`
WHERE  `address` IN('北京朝阳');

SELECT `studentno`, `studentname` FROM `student`
WHERE  `address` IN('北京'); -- 查不出来


-- ====== null  not null ====

-- 查询地址为空的学生 null ''
SELECT `studentno`, `studentname` FROM `student`
WHERE  `address` = '' OR `address` IS NULL


-- 查询有出生日期 不为空
SELECT `studentno`, `studentname` FROM `student`
WHERE  `borndate` IS NOT NULL

4.4、联表查询

JOIN 对比

七种jion理论

no 和 where 的区别
on是生成临时表时使用的条件，采用左外连接时，左外连接是以左表为基础的，所以左表的记录将会全部表示出来,而右表只会显示符合搜索条件的记录。也就是说emp是左表，dept是右表，条件是emp的deptno与dept中的deptno相等且为40时才连接，但emp表中不存在deptno为40的记录，也就是右表没有符合条件的记录，而记录不足的地方均用NULL来补充。
而where是在临时表生成好后，再对临时表进行过滤。也就是说emp表与dept的连接条件只是emp的deptno与dept中的deptno相等,然后在对生成的临时表进行筛选，由于emp表中不存在deptno为40的记录，所以未找到符合条件的记录。

由于内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所有在内连接时on和where的结果是相同的。而左外、右外与全连接由于它的特殊性，on和where造成的差别大小取决于表达式和表中的数据。

SELECT s.studentno , s.studentname, subjectno, studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno

SELECT student.studentno, student.studentname, result.subjectno, result.studentresult
FROM student
INNER JOIN result
WHERE student.studentno = result.studentno

-- Right Join
SELECT s.studentno, studentname, subjectno, studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno

-- Left Join
SELECT s.studentno, studentname, subjectno, studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno

-- 查询缺考的同学
-- Left Join
SELECT s.studentno, studentname, subjectno, studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno
WHERE studentresult IS NULL

-- 这里发现 = null查询不到想要的结果，即这种写法要规避
-- SQL实际上使用is null 和 is not null判断字段为空，注意为空不代表为空字符串和0
-- 而null = null和null<>null其实返回的都是false，任何值和null做运算的结果都是false
SELECT s.studentno, studentname, subjectno, studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno
WHERE studentresult = NULL


- 思考题（查询了参加考试的同学信息：学号，学生姓名，科目名，分数）
/*思路
1.分析需求，分析查询的字段来自哪些表，student,result, subject（连接查询）
2.确定使用哪种连接查询？ 7种
确定交叉点（这两个表中的哪个数据是相同的）
判断条件： 学生表中的studentno = 成绩表 studentno
*/
SELECT s.studentno, studentname, subjectname, studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

-- 我要查询那些数据 select 。。。
-- 从那几个表中查FROM表 xxx join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，从两张慢慢增加

-- from a left jion b
-- from a rigth join b

-- 查询科目所属的年级（科目名称，年级名称）
SELECT `subjectname`, gradename
FROM `subject` sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

-- 查询了参加 高等数据-1 考试的同学信息： 学号，学生姓名，科目名称，分数
SELECT s.studentno, studentname, `subjectname`, studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE `subjectname` = '高等数学-1'

操作	描述
lnner join	如果表中至少有一个匹配，就返回行
left jion	会从左表中返回所有的值，即使右表中没有匹配
rigth jion	会从右表中返回所有的值，即使左表中没有匹配

自连接

自己的表和自己的表连接。核心：一张表拆分为两张一样的表即可

父类

categoryid	categeoryName
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categeryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

CREATE TABLE `school`.`category`(
	`categoryid` INT(3) NOT NULL COMMENT 'id', 
	`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1',
	`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', 
	 PRIMARY KEY (`categoryid`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
insert into `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values ('3', '1', '软件开发');
insert into `school`.`category` (`categoryid`, `PId`, `categoryname`) values ('5', '1', '美术设计');
insert iNTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES ('4', '3', '数据库'); 
insert into `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) values ('8', '2', '办公信息');
insert into `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) values ('6', '3', 'web开发'); 
inserT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) valueS ('7', '5', 'ps技术');

-- 查询父子信息 : 把一张表看为两个一模一样的的表
SELECT a.`categoryname` AS '父栏目' , b.`categoryname` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`

4.5、分页和排序

排序

-- 排序： 升序 ASC， 降序 DESC

-- order BY 通过哪个字段排序，怎么排
-- 根据查询结果根据 成绩降序	排序
SELECT s.studentno, studentname, `subjectname`, studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE `subjectname` = '高等数学-1'
ORDER BY studentresult ASC

分页

-- 为什么要分页？
-- 缓解数据库压力，给人的体验更好

-- 分页，每页只显示五条数据
-- 语法：limit 起始值，页面的大小
-- 网页应用： 当前页，总的页数，页面的大小
-- LIMIT 0,5 1~5
-- LIMIT 1,5 1~5
SELECT s.studentno, studentname, `subjectname`, studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE `subjectname` = '高等数学-1'
ORDER BY studentresult ASC
LIMIT 5,5

-- 第一页 LIMIT 0,5  （1 - 1）* 5
-- 第二页 LIMIT 5,5 （2 - 1）* 5
-- 第三页 LIMIT 10,5 （3 - 1）* 5
-- 第四页 LIMIT 15,5 （4 - 1）* 5  （n - 1)*pagesize, pagesize
-- [pagesize:页面大小]
-- [（n - 1)*pagesize 起始值]
-- [n: 当前页]
-- [数据总数/页面大小 + 1= 总页数]


-- 查询 JAVA第一学年 课程成绩排名前十的学生，并且分数要大于80的学生（学号，姓名，课程名称，分数）
SELECT s.studentno, studentname, `subjectname`, studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = 'Java程序设计-1' AND studentresult > 80
ORDER BY studentresult DESC
LIMIT 0,10

4.6、子查询

where（这个值是计算出来的）

本质在where语句中嵌套一个子查询语句

where（select * from）

-- ==================== WHERE ==================
-- 1.查询数据库结构-1的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：使用连接查询
SELECT studentno, subjectname, studentresult
FROM result r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC

-- 方式二：使用子查询（由里及外）
-- 查询所有数据库结构-1的学生学号
SELECT studentno, subjectno, studentresult
FROM result
WHERE `subjectno` = (
	SELECT subjectno FROM `subject`
	WHERE `subjectname` = '数据库结构-1'
)
ORDER BY studentresult DESC

-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.studentno, studentname
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE studentresult >= 80

-- 在这个基础上增加一个科目，高等数学-2
-- 查询 高等数学-2 的编号
SELECT DISTINCT s.studentno, studentname
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE studentresult >= 80 AND subjectno = (
	SELECT `subjectno` FROM `subject`
	WHERE subjectname = '高等数学-2'
)

-- 查询课程为 高等数学-2 且分数不小于 80 的同学的学号和姓名
SELECT  s.studentno, studentname
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE `subjectname` = '高等数学-2' AND studentresult >= 80

-- 再改造
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.7、分组和过滤

-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）
SELECT `subjectname`, AVG(studentresult) AS 平均分, MAX(studentresult) AS 最高分, MIN(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
GROUP BY r.subjectno -- 通过什么字段分组
HAVING 平均分 > 60

4.8、select小结

5、MySQl函数

官网：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

5.1、常用函数（并不常用）

-- ======================= 常用函数===========================

-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(-10) -- 判断参数符号 负数返回-1，正数返回0，0返回0

-- 字符串函数
SELECT CHAR_LENGTH('黑') -- 字符串长度
SELECT CONCAT('wo','se') -- 拼接字符串
SELECT INSERT('hellloword',1,2,'hei') -- 查询，从某个位置开始替换某个长度
SELECT LOWER('lcP') -- 小写字母
SELECT UPPER('Lcp') -- 大写字母
SELECT INSTR('lcp','c') -- 返回第一次出现的字符串索引
SELECT REPLACE('dsdkdsj', 'ds', 'fuj') -- 替换出现的指定字符串
SELECT SUBSTR('dskfn',4 , 6) -- 返回指定的字符串（同Java）
SELECT REVERSE('dskksdfsegs') -- 反转

-- 查询姓 张 的同学 名字 变为 周
SELECT REPLACE(studentname, '张', '周') FROM student
WHERE studentname LIKE '张%'

-- 时间和日期函数 （记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE()
SELECT NOW()
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间

-- 系统
SELECT SYSTEM_USER() -- 当前用户
SELECT VERSION() -- 版本

5.2、聚合函数

函数名称	描述
COUNT（）	计数
SUM（）	求和
AVG（）	平均值
MAX（）	最大值
MIN（）	最小值
…

-- ================ 聚合函数==============
-- 都能统计表中的数据(查询表有多少个记录）
SELECT COUNT(studentname) FROM student; -- COUNT(指定列)，会忽略所有的null值

SELECT COUNT(*) FROM student; -- COUNT(*)，不会忽略null ， 本质，计算行数
SELECT COUNT(1) FROM result; -- COUNT(1)，不会忽略所有的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信息摘要算法**（英语：MD5 Message-Digest Algorithm），一种被广泛使用的[密码散列函数]，可以产生出一个128位（16[字节]的散列值（hash value）

主要增强算法复杂度和不可逆性

MD5不可逆，具体的值的MD5是一样的

MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密的前值

-- =============== 测试MD5 加密=================
CREATE TABLE `testmd5` (
	`id` INT(3) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO `testmd5` VALUES(1, '张三', '123456'),(2, '李四', '123456'),(3, '王五 ', '123456')

-- 加密
UPDATE `testmd5` SET pwd = MD5(`pwd`) -- 加密所有密码

-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4, '小明', MD5('123456'))

-- 如何校验：将用户传递进来的密码，进行md5加密，然后对比加密后的值
SELECT *FROM testmd5 WHERE `name` = '小明' AND pwd = MD5('123456')

6、事务

6.1、什么是事务

要么都成功，要么都失败

参考博客：https://blog.csdn.net/dengjili/article/details/82468576

ACID

原子性（Atomicity）：要么都成功，要么都失败

一致性（Consistency）：事务前后数据完整性要保证一致

隔离性（Isolation）：

隔离性是多个用户并发访问数据库时，数据库为每个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离

持久性（Durability） – 事务提交

• 事务没有提交，恢复原状
• 事务提交，持久化到数据库

隔离所导致的一些问题

脏读：

指一个事务读取了另外一个事务未提交的数据。

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一般是行影响，多了一行）

执行事务

-- mysql是默认开始事务自动提交的 
SET autocommit = 0 -- 关闭

-- 手动处理事务
SET autocommit = 0 -- 关闭自动条件

-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

-- 提交：持久化（成功）
COMMIT 
-- 回滚：回到原来的样子（失败）
ROLLBACK

-- 事务结束
SET autocommit = 1 -- 开启自动提交

SAVEPOINT 保存点名  --  设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名  -- 回滚到保存点
RELEASE  SAVEPOINT 保存点		-- 撤销保存点

模拟场景

-- 转账

CREATE DATABASE `shop` /*!40100 DEFAULT CHARACTER SET utf8 */

CREATE TABLE `account` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `money` decimal(9,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

-- 模拟转账 ： 事务
SET autocommit = 0; -- 关闭自动事务
START TRANSACTION -- 开启一个事务

UPDATE account SET money = money - 500 WHERE `name` = 'A'
UPDATE account SET money = money + 500 WHERE `name` = 'B' 

COMMIT; -- 提交事务
ROLLBACK; -- 回滚

SET autocommit = 1;

SHOW CREATE DATABASE shop
SHOW CREATE TABLE account

在Java中如何实现

7、索引

参考博客：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

7.1、索引的分类

• 主键索引 （PRIMARY KEY）
  • 唯一的标识，主键不可重复 ，只能有一个列作为主键
• 唯一索引 （UNIQUE KEY）
  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识位 唯一索引
• 常规索引（KEY / INDEX)
  • 默认的，index，key 关键词来设置
• 全文索引 （FullText)
  • 在特定的数据库引擎下才有，MyISAM
  • 快速定位数据

基础语法

-- 索引的使用
-- 1.在创建表的时候给字段添加索引
-- 2.创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student

-- 添加一个全文索引 （索引名） 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`);

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 常规索引， 非全文索引

SELECT * FROM student WHERE MATCH(studentname) AGAINST('张')

7.2、测试索引

-- 插入100万数据.
DELIMITER $$
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`eamil`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

select mock_data()

创建索引后查询数据消耗时间显著降低

索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显

7.3、索引原则

• 索引不是越多越好
• 不要对进程变动数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用查询的字段上

索引的数据结构

Hash类型的索引

Btree：InnoDB的默认数据结构

8、权限管理和备份

8.1、用户管理

navicat 可视化管理

SQL命令操作

用户表：mysql.user

用户的本质：对mysql.user这张表进行增删改查

8.2、MySql备份

为什么要备份：

• 保证重要的数据不丢失
• 数据转移

MySQl数据库备份的方式

• 直接拷贝物理文件

• 在可视化软件直接导出

• 使用命令行导出 mysqldump 命令行使用

# mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名1 表名2 表名3 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

# 导入
# 登录的情况下，切换到指定数据库
# source 备份文件
source d:/a.sql

mysql -u用户名 -p密码 库名 < 备份文件

9、规范数据库设计

9.1、为什么需要设计

当数据比较大的时候，就需要设计

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
• 程序的性能差

良好的数据库设计

• 节省内存空间
• 保证数据库的完整型
• 方便我们开发系统

软件开发中，关于数据库的设计

• 分析需求：分析业务和需要处理的数据库的需求
• 概要设计：设计关系E—R图

设计数据库的步骤：（个人博客）

• 收集信息，分析需求

  • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
  • 分类表（文章分类，谁创建的）
  • 文章表（文章的信息）
  • 友链表（友情链接信息）
  • 评论表
  • 自定义表（系统消息，某个关键的子，或者一些主子段） key ：value

• 标示实体（把需求落地到某个字段）

  用户表

文章表

==博客表==

​

评论表

​

友链表

	==关注表==

​

• 标识实体之间的关系
  • 写博客user–> blog
  • 创建分类：user–>category

9.2、三大范式

为什么需要数据规范化

• 信息重复
• 更新异常
• 插入异常
  • 无法正常显示信息
• 删除异常
  • 丢失有效的信息

三大范式

第一范式（1NF）

原子性：保证每一列不可再分

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

前提：满足第一范式和第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

规范性和性能的问题

关联查询的表不得超过三张表

• 考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
• 在规范性能的问题的时候，需要适当的考虑一下 规范性
• 故意给某些表增加一些冗余的字段。（从多表查询中变为单表查询 ）
• 故意增加一些计算列（从大数据量降为小数据量的查询：索引）

10、JDBC（重点）

10.1、数据库驱动

程序要通过 数据库驱动，和数据库打交道

10.2、JDBC

SUN公司为了简化开发人员的（对数据库的统一）操作，提供了一个（java操作数据库）规范，俗称JDBC。这些规范的实现有具体的厂商去做

对于开发人员，我们只需要掌握JDBC接口的操作即可！

java.sql javax.sql

还需要导入一个数据库驱动包mysql-connector-java-版本号jar

10.3、第一个JDBC程序

创建数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
	id INT PRIMARY KEY,
	NAME VARCHAR(40),
	PASSWORD VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);

INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

public static void main(String[] args) throws ClassNotFoundException, SQLException {
		//1.加载驱动
		Class.forName("com.mysql.jdbc.Driver");
		
		//2.用户信息
		//useUnicode = true 支持中文编码
		//characterEncoding = utf8 设定字符集
		//useSSL = true 安全连接
		String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true & characterEncoding = utf8 && useSSL = true";
		String user = "root";
		String password = "lcp5211314..";
		
		//3.连接成功，数据库对象 Connection 代表数据库
		Connection connection = DriverManager.getConnection(url, user, password);
		
		//4.执行SQL的对象
		Statement statement = connection.createStatement();
		
		//5.执行SQL的对象，去执行SQL，可能存在结果，查看返回结果
		String sql = "SELECT * FROM users";
		
		ResultSet resultSet = statement.executeQuery(sql); //返回的结果集
		
		while(resultSet.next()) {
			System.out.println("id = " + resultSet.getObject("id"));
			System.out.println("name = " + resultSet.getObject("NAME"));
			System.out.println("password = " + resultSet.getObject("PASSWORD"));
			System.out.println("email = " + resultSet.getObject("email"));
			System.out.println("birthday = " + resultSet.getObject("birthday"));
		}
		
		//6.释放连接
		resultSet.close();
		statement.close();
		connection.close();
	}

步骤：

1.加载驱动

2.用户信息

3.连接成功，数据库对象 Connection 代表数据库

4.执行SQL的对象

5.执行SQL的对象，去执行SQL，可能存在结果，查看返回结果

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//固定写法
Connection connection= DriverManager.getConnection(url,name,password);

//connection代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();

URL

String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=false";

//mysql 默认3306
//协议://主机地址:端口号/数据库名？参数1&参数2&参数3

//Oracle   1521
//jdbc:oralce:thin:@localhost:1521:sid

statement 执行SQL的对象 pPrepareStatement 执行SQL的对象

String sql="SELECT * FROM users";//编写Sql

statement.executeQuery();
statement.execute();
statement.executeUpdate();//更新，插入，删除，返回一个受影响的行数

123456

ResultSet 查询的结果集，封装了所以的查询结果

获得指定的数据类型

ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部查询的结果
        resultSet.getObject();//在不知道列类型下使用
        resultSet.getString();//如果知道则指定使用
        resultSet.getInt();
        


1234567

遍历,指针

        resultSet.next(); //移动到下一个
        resultSet.afterLast();//移动到最后
        resultSet.beforeFirst();//移动到最前面
        resultSet.previous();//移动到前一行
        resultSet.absolute(row);//移动到指定行

123456

释放内存

//6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();//耗资源

10.4statement对象

Jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。

Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sq|语句， executeUpdate执行完后， 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。

Statement.executeQuery方法用于向数据库发生查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作-create

使用executeUpdate(String sql)方法完成数据添加操作，示例操作：

 Statement statement = connection.createStatement();
        String sql = "insert into user(...) values(...)";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("插入成功");
        }

1234567

CRUD操作-delete

使用executeUpdate(String sql)方法完成数据删除操作，示例操作：

Statement statement = connection.createStatement();
        String sql = "delete from user where id =1";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("删除成功");
        }

1234567

CURD操作-update

使用executeUpdate(String sql)方法完成数据修改操作，示例操作：

Statement statement = connection.createStatement();
        String sql = "update user set name ='' where name = ''";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("修改成功");
        }

1234567

CURD操作-read

使用executeUpdate(String sql)方法完成数据查询操作，示例操作：

Statement statement = connection.createStatement();
        String sql = "select * from  user where id =1";
        ResultSet rs= statement.executeQuery(sql);
        if(rs.next()){
            System.out.println("");
        }

1234567

代码实现

1.提取工具类

package com.kuang.lesson02.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    static {
        try{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);
            driver=properties.getProperty("driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");

            //1.驱动只用加载一次
            Class.forName(driver);

        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    //2.获取连接
    public static Connection getConnection() throws Exception{
        return DriverManager.getConnection(url, username, password);
    }
    //3.释放资源
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {

        if(rs!=null){
            rs.close();
        }
        if (st!=null){
            st.close();
        }
        if(conn!=null){
            conn.close();
        }

    }
}


123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354

2.编写增删改的方法，exectueUpdate

package com.kuang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import static com.kuang.lesson02.utils.JdbcUtils.*;

public class TestInnsert {
    public static void main(String[] args){
        Connection conn =null;
        Statement st = null;
        ResultSet rs =null;



        try {
             conn = getConnection();//获取连接
            st = conn.createStatement();//获取SQL执行对象
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(5,'sanjin','123456','233223@qq.com','2020-01-01')";

            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }
        JdbcUtils.release(conn,st,rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}


1234567891011121314151617181920212223242526272829303132333435

3.查询 executeQuery

package com.kuang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import static com.kuang.lesson02.utils.JdbcUtils.*;

public class TestInnsert {
    public static void main(String[] args) throws SQLException {
        Connection conn =null;
        Statement st = null;
        ResultSet rs =null;



        try {
             conn = getConnection();//获取连接
            st = conn.createStatement();//获取SQL执行对象
            String sql = "select * from users";
            rs=st.executeQuery(sql);//查询完毕返回结果集

            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        JdbcUtils.release(conn,st,rs);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

}

123456789101112131415161718192021222324252627282930313233343536

SQL注入问题

sql存在漏洞，会被攻击导致数据泄露 SQL会被拼接 or

package com.kuang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import static com.kuang.lesson02.utils.JdbcUtils.getConnection;

public class SQL注入 {
    public static void main(String[] args) {


        //SQL注入
 login("' or '1=1","123456");
    }
    public static void login(String name,String password){


        Connection conn =null;
        Statement st = null;
        ResultSet rs =null;



        try {
            conn = getConnection();//获取连接
            st = conn.createStatement();//获取SQL执行对象
            String sql = "select * from users where `NAME`='"+ name +"'  AND `PASSWORD`='"+ password +"'" ;
            rs=st.executeQuery(sql);//查询完毕返回结果集

            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
            JdbcUtils.release(conn,st,rs);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.release(conn,st,rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}


123456789101112131415161718192021222324252627282930313233343536373839404142434445464748

10.5 PreparedStatement对象

PreparedStatement 可以防止SQL注入 ，效率更高。

1. 新增
2. 删除
3. 查询

package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test {
    public static void main(String[] args) {
        Connection connection= null;
        PreparedStatement pstm=null;
        try {

            connection = JdbcUtils.getConnection();
            //区别
            //使用问好占位符代替参数
            String sql = "insert into users(id,`NAME`) values(?,?)";
            pstm = connection.prepareStatement(sql);//预编译sql，先写sql然后不执行
            //手动赋值
            pstm.setInt(1,8);
            pstm.setString(2,"SANJIN");

            //执行
            int i = pstm.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.release(connection,pstm,null);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

1234567891011121314151617181920212223242526272829303132333435363738394041

防止SQL注入本质，传递字符 带有“ ”，转义字符会被转义

10.6 使用IDEA连接数据库

连接成功后，可以选择数据库

双击数据库

更新数据

编写sql代码的地方

10.7 JDBC事务

要么都成功，要么都失败

ACID原则

原子性：要么全部完成，要么都不完成

一致性：结果总数不变

隔离性：多个进程互不干扰

持久性：一旦提交不可逆，持久化到数据库了

隔离性的问题：

脏读： 一个事务读取了另一个没有提交的事务

不可重复读：在同一个事务内，重复读取表中的数据，表发生了改变

虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致

代码实现

1. 开启事务conn.setAutoCommit(false);

2. 一组业务执行完毕，提交事务

3. 可以在catch语句中显示的定义回滚，但是默认失败会回滚

   package com.kuang.lesson04;
   
   import com.kuang.lesson02.utils.JdbcUtils;
   
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   
   public class Action {
       public static void main(String[] args) {
   
           Connection conn =null;
           PreparedStatement ps = null;
           ResultSet rs = null;
   
           try {
               conn = JdbcUtils.getConnection();
               //关闭数据库的自动提交功能， 开启事务
               conn.setAutoCommit(false);
               //自动开启事务
               String sql = "update account set money = money-500 where id = 1";
               ps =conn.prepareStatement(sql);
               ps.executeUpdate();
               String sql2 = "update account set money = money-500 where id = 2";
               ps=conn.prepareStatement(sql2);
               ps.executeUpdate();
   
               //业务完毕，提交事务
               conn.commit();
               System.out.println("操作成功");
           } catch (Exception e) {
               try {
                   //如果失败，则默认回滚
                   conn.rollback();//如果失败，回滚
               } catch (SQLException throwables) {
                   throwables.printStackTrace();
               }
               e.printStackTrace();
           }finally {
               try {
                   JdbcUtils.release(conn,ps,rs);
               } catch (SQLException throwables) {
                   throwables.printStackTrace();
               }
           }
       }
   }
   

10.8数据库连接池

数据库连接–执行完毕–释放

连接–释放 十分浪费资源

池化技术： 准备一些预先的资源，过来就连接预先准备好的

常用连接数 100

最少连接数：100

最大连接数 ： 120 业务最高承载上限

排队等待，

等待超时：100ms

编写连接池，实现一个接口 DateSource

开源数据源实现(拿来即用)

DBCP

C3P0

Druid: 阿里巴巴

使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了

DBCP

需要用到的jar包

dbcp.ar

C3P0

结论

无论使用什么数据源，本质是不变的，DateSource接口不会变，方法就不会变