01、初始Mysql

连接数据库

命令行连接

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

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; -- 修改用户密码
flush privileges; -- 刷新权限


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

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

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

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

exit; -- 退出连接

-- 单行注释（SQL注释）
/*
多行注释
*/

数据库xx语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

02、操作数据库

2.1操作数据库

-- 创建数据库
CREATE DATABASE [IF NOT EXISTS] wetos
-- 删除数据库
DROP DATABASE [IF EXISTS] westos
-- tab键的上面，如果表明或者字段名是一个特殊字符，就需要带``
USE `school`
-- 查看数据库
SHOW DATABASES -- 查看所有的数据库

2.2数据库的列类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节 常用的int类型
• 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.Date

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

null

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

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

<mark>Unsigned:</mark>

• 无符号的整数
• 声明了该列不能声明为负数

<mark>zerofill：</mark>

• 0填充的
• 不足的位数，使用0来填充， int(3), 5 , 005

<mark>自增：</mark>

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

<mark>非空</mark> NULL not null

• 假设设置为not null， 如果不给它赋值，则会报错
• NULL，如果不填写值，默认就位null

<mark>默认：</mark>

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

拓展：

/*每一个表，都必须存在以下五个字段！未来做项目用的，表示一个记录存在意义！

id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/

2.4 创建数据库表（重点）

-- 目标：创建一个school数据库
-- 创建学生表（列，字段） 使用SQL创建
-- 学号int 登录密码varchar(20) 姓名， 性别varchar(2)，出生日期（datetime），家庭住址，email
-- 注意点：使用英文()，标的名称和字段尽量使用 ``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来
-- 所有的语句后面加,(英文的)，最后一个不用加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键
CREATE TABLE
IF
	NOT EXISTS `student` (
		`id` INT ( 4 ) NOT NULL auto_increment COMMENT '学号',
		`name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名',
		`pwd` VARCHAR ( 20 ) NOT NULL DEFAULT '123456' COMMENT '密码',
		`sex` VARCHAR ( 2 ) NOT NULL DEFAULT '女' COMMENT '性别',
		`birthday` datetime DEFAULT NULL COMMENT '出生日期',
		`address` VARCHAR ( 100 ) DEFAULT NULL COMMENT '家庭住址',
		`email` VARCHAR ( 50 ) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY ( `id` ) 
	) ENGINE = INNODB DEFAULT CHRASET = utf8

格式

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

常用命令

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

2.5 数据表的类型

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

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

常规使用操作

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

在物理空间存在的文职

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

本质还是文件的存储！

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

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

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

CHARSET=utf8

不设置话，回事mysql默认的字符集编码~（不支持中文）

MySQL的默认编码时Latin1，不支持中文

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

character-set-server=utf8

2.6 修改删除表

修改

-- AS 可以用空格代替
-- 修改表名 ALTER TABLE 旧表明 RENAME AS 新名字
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段 ALTER TABLE 表明 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)

-- 修改表的字段 （重名名，修改约束）
ALTER TABLE teacher1 MODIFY age VARCHAR(11)	-- 修改约束
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 	字段重命名

-- 删除表的字段
ALTER TABLE teacher1 DROP age1

删除

-- 删除表（如果表存在再删除）
DROP TABLE IF EXISTS teacher1

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

注意点：

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

03、MySQL数据管理

3.1 外键（了解即可）

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

CREATE TABLE `grade`(
	`gradeid` INT(10) not null auto_increment comment '年级id',
	`gradename` varchar(50) not null comment '年级名称',
	primary key (`gradeid`)
)engine=innodb default charset=utf8;

-- 学生表的gradeid字段 要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） reference 引用

create table if not exists `student` (
	`id` int(4) not null auto_increment comment '学号',
	`name` varchar(30) not null default '匿名' comment '姓名',
	`pwd` varchar(20) not null default '123456' comment '密码',
	`sex` varchar(2) not null default '女' comment '性别',
	`birthday` datetime default null comment '出生日期',
	`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

删除外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

方式二：创建表成功后，添加外键约束

CREATE TABLE `grade`(
	`gradeid` INT(10) not null auto_increment comment '年级id',
	`gradename` varchar(50) not null comment '年级名称',
	primary key (`gradeid`)
)engine=innodb default charset=utf8;

-- 学生表的gradeid字段 要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） reference 引用

create table if not exists `student` (
	`id` int(4) not null auto_increment comment '学号',
	`name` varchar(30) not null default '匿名' comment '姓名',
	`pwd` varchar(20) not null default '123456' comment '密码',
	`sex` varchar(2) not null default '女' comment '性别',	
	`birthday` datetime default null comment '出生日期',
	`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 哪个表(哪个字段)

以上的操作都是物理外键，数据库级别的外键，不建议使用！（避免数据库过多造成困扰，这里了解即可）

<mark>最佳实践</mark>

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

3.2 DML语言（全部记住）

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

DML语言：数据操作语言

• Insert
• Update
• Delete

3.3 添加

insert

-- 插入语句（添加）
-- insert into 表明([字段名1， 字段2， 字段3]) values('值1', '值2', '值3', ...)
insert into `grade` (`gradename`) values('大四')

-- 由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配）
insert into `grade` values('大三')

-- 一般写插入语句，我们一定要数据和字段意义对应！

-- 插入多个字段
insert into `grade`(`gradename`) 
values('大二'),('大一')

insert into student(`name`) values('张三')

insert into student(`name`,`pwd`,`sex`) values('张三','aaa','男')


insert into student(`name`,`pwd`,`sex`) 
values('李四','aaa','男')('王五','aaa','男')

语法：insert into 表明([字段名1， 字段2， 字段3]) values('值1'), ('值2'), ('值3'), ...

注意事项：

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

3.4 修改

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

-- 修改学院名字
update `student` set `name` = 'zzzzy' where id = 1

-- 不能指定条件的情况下，会改动所有表！
update `student` set `name` = '长江七号'

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

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

操作符会放回布尔值

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5!=6	true
>	大于	5>6	false
<	小于	5<6	true
<=	小于或等于	5<=6	true
>=	大于或等于	5>=6	false
BETWEEN... and...	在某个范围内	[2,5]	true
AND	和（与）	5>1 and 1>2	false
OR	或	5>1 or 1>2	true

-- 通过多个条件定位数据
UPDATE student set name = '嘿嘿嘿' where name = 'hhh' and id = 2;

语法：UPDATE 表名 set column_name = value,[column_name = value...] where 条件

注意：

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

update student set birthday = current_time where name = '嘿嘿嘿'

3.5 删除

delete命令

语法：delete from (表名) [where 条件]

-- 删除数据（避免这样写，会全部删除）
delete from student

-- 删除指定数据
delete from student where id = 1;

TRUNCATE 命令

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

-- 清空student表
TRUNCATE student

delete 和 truncate的区别

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

04、DQL查询数据（最重点）

4.1 DQL

（Data Query LANGUAGE：数据查询语言）

• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询它都能做
• <mark>数据中最核心的语言，最重要的语句</mark>
• 使用频率最高的语句

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 {[offser,]row_count | row_countOFFSET offset}]	-- 指定查询的记录从哪条至哪条

注意：[] 括号代表可选，{}括号代表必选

顺序很重要

select 去重 要查询的字段 from 表 （注意：表和字段可以取别名）
xxx join 要连接的表 on 等值判断
where （具体的值，子查询语句）
Group By （通过哪个字段来分组）
Having （过滤分组后的信息，条件和where是一样的，位置不同）
Order By... （通过哪个字段排序） [升序/降序]
Limit startIndex, pagesize

业务层面：
查询：跨表，跨数据库...

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 别名

去重 distinct

作用：去除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

<mark>数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量...</mark>

语法：select 表达式 from 表

4.3 where条件子句

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

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

逻辑运算符

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

<mark>尽量使用英文字母</mark>

-- ====================where=============
SELECT `studentno` ,`studentresult` FROM result

-- 查询考试成绩在 95~100分之间
SELECT `studentno` ,`studentresult` FROM result
WHERE `studentresult` >= 95 AND `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	如果操作符为NULL，结果为真
IS NOT NULL	a is not null	如果操作符为not null，结果为真
BETWEEN	a between b and c	若a在b和c之间，则结果为真
Like	a like b	SQL匹配，如果a匹配b，则结果为真
In	a in (a1, a2, a3...)	假设a在a1，或者a2... 其中的某一个值，结果为真

-- ====================模糊查询=============
-- 查询姓张的同学
-- 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 '张_'

-- 查询名字中间有嘉字的同学， %嘉%
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('北京朝阳','广东深圳');

-- ===== 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

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

4.4 联表查询

JOIN 对比

-- ========== 联表查询 join ==========

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result

/*
1. 分析需求，分析查询的字段来自哪些表（连接查询）
2. 确定使用哪种连接查询？ 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的studentno = 成绩表 studentno
*/

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

-- right join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s    -- 这里用空格代替了AS
RIGHT JOIN result r
ON s.studentno = r.`studentno`

-- left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s    -- 这里用空格代替了AS
LEFT JOIN result r
ON s.studentno = r.`studentno`
/*
  如果关键子使用where的话，则会两表所有信息，效果如inner join
  left join 左连接：展示左表所有数据，右表符合on条件的数据，右表不符合的则为空显示。
  right join 右连接：展示右表所有数据，左表符合on条件的数据，左表不符合的则为空显示。
*/

-- 查询缺考的同学
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s    -- 这里用空格代替了AS
LEFT JOIN result r
ON s.studentno = r.`studentno`
WHERE `studentresult` IS NULL

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

-- 我要查询哪些数据select...
-- 从那几个表中查 from 表 xxx join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

-- from a left join b
-- from a right join b

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

自连接（作为了解）

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

父类

categorid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

pid	categorid	categoryName
3	4	数据库
2	8	办公
3	6	web开发
5	7	美术设计

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

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

4.5 分页和排序

排序

-- 排序： 升序ASC， 降序DESC
-- ascending和descending

-- 查询的结果根据成绩降序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
RIGHT JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = 'C语言-1'
ORDER BY `studentresult` DESC

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

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

-- 第一页 limit 0,5 (1-1)*5
-- 第二页 limit 5,5 (2-1)*5
-- 第三页 limit 10,5 （3-1）*5
-- 第n也 limit 0,5 （n-1）*pageSize， pageSize
-- 【pageSize：页面大小】
-- 【（n-1）* pageSize:起始值】
-- 【n：当前页】
-- 【数据总数/页面大小=页面总数】

语法：limit(查询起始下标, pageSize)

4.6 子查询

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

本质：<mark>在where语句中嵌套一个查询语句</mark>

-- ========== 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

-- 方式二：使用子查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
WHERE `studentno` =
(	SELECT `subjectno`
	FROM `subject`
	WHERE `subjectname` = '高等数学-1'
)
ORDER BY `studentresult` DESC

-- 查询所有高等数学-1 的学生学号

-- 分数不小于80分的学生的学号和姓名
SELECT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON r.`studentno` = s.`studentno`
WHERE `studentresult` >= 80
-- 在这个基础上增加一个科目，高等数学-2
-- 查询高等数学-2 的编号
 SELECT 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.`subjectno`,`subjectname`
FROM `student` s
INNER JOIN result r
ON s.`studentno` = r.`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'
  )
)

05、 MySQL函数

5.1 常用函数

-- 数***算
select abs(-8) -- 绝对值
select ceiling(9.4) -- 向上取整
select floor(9.4) -- 向下取整
select rand() -- 返回一个0到1之间的随机数
select sign(-10) -- 判断一个数的符号 0返回0，负数返回-1，正数返回1

-- 字符串函数
select char_length('无限进步')	-- 字符串长度
select concat('我','爱','d') -- 拼接字符串
select insert('我爱d',2,2,'超级热爱') -- 查询，从某个位置开始替换某个长度
select lower('iLoveD') -- 转小写字母
select upper('iLoveD') -- 转大写字母
select instr('iloved','d') -- 返回第一次出现的子串的索引
select replace('ta说ta想要暴富','说','暴富') -- 替换出现的指定字符串
select substr('ta说ta想要暴富',7,3) -- 返回指定的子字符串 (原字符串，截取的位置，截取的长度）
select reverse('ta说ta想要暴富') -- 反转

-- 查询姓周的同学，名字 邹
select replace(studentname,'周','邹') from student 
where studentname like '周%'

-- 时间和日期（记住）
select current_date() -- 获取当前日期
select curdate() -- 获取当前日期
select now() -- 获取当前的时间
select localtime() -- 本地时间
select sysdate() -- 获取系统时间

select year(now())
SELECT month(NOW())
SELECT day(NOW())
SELECT hour(NOW())
SELECT minute(NOW())
SELECT second(NOW())

-- 系统
select system_user()
select user()
select version()

5.2 聚合函数

函数名称
COUNT()	计数
SUM()	求和
AVG()	平均数
MAX()	最大值
MIN()	最小值

-- ===============聚合函数===================
-- 都能够统计 表中的数据（想查询一个表中有多少个记录，就使用这个count()）
SELECT COUNT(`studentname`) FROM student; -- Count(指定列）会忽略所有的null值
SELECT COUNT(*) FROM student; -- Count(*)，不会忽略null值，本质：计算行数
SELECT COUNT(1) FROM result; -- Count(1)，不会忽略null值

SELECT SUM(`studentresult`) AS 总和 FROM `result`
SELECT AVG(`studentresult`) AS 平均数 FROM `result`
SELECT MAX(`studentresult`) AS 最高分 FROM `result`
SELECT MIN(`studentresult`) AS 最低分 FROM `result`

-- 查询不同课程的平均分，最高分，最低分
-- 核心： （根据不同的课程分组）
SELECT any_value(`subjectname`), AVG(`studentresult`) AS 平均分, MAX(`studentresult`) AS 最高分, MIN(`studentresult`) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
-- where 条件中不能包含聚组函数
GROUP BY r.`subjectno` -- 通过什么字段来分组
HAVING 平均分>80

5.3 数据库级别的MD5加密（扩展）

什么是MD5加密

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

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

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

-- =================测试MD5 加密=====================

CREATE TABLE `testmd5`(
  `id` INT(4) 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,'zhangsan','123456'),(2,'wangwu', '12345'),(3,'lisi','123')

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1

UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming', MD5('12345'))

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

06、 事务

6.1 什么是事务

要么都成功，要么都失败

---

1. SQL执行 A给B转账 A 1000 -- > 200 B200
2. SQL执行 B收到A的钱 A 800 -- > B 400

---

将一组SQL放在一个批次中去执行

事务原则：ACID原则 原子性，一致性，隔离性，持久性 （脏读，幻读）

原子性(Atomicity)：要么都成功，要么都失败

一致性(Consistenncy)：事务前后的数据完整性要保证一直，100

持久性(Durability)：事务提交，事务一旦提交则不可逆，被持久化到数据库中

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

隔离所导致的一些问题

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

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

虚度（幻读）：只在一个事物内读取到了别的事物插入的数据，导致前后读取不一致

模拟场景

-- ==================事务====================

-- mysql 是默认开启事务自动提交
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启（默认的）

-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交

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

INSERT xx

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

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

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


-- 转账
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
USE shop

CREATE TABLE `account`(
  `id` INT(3) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL,
  `money` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY(`id`)
  )ENGINE = INNODB DEFAULT CHARSET=utf8
  
  INSERT INTO account(`name`,`money`)
  VALUES ('a',2000.00),('b',10000.00)

-- 模拟转账：事务
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; -- 恢复默认值

07、 索引

官方定义：索引（Index）是帮助MySQL高效获取数据的数据结构。

提取句子主干，就可以得到索引的本质：索引是数据结构。

7.1 索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

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

基础

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

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

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

-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student; -- 非全文索引

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

7.2 测试索引

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=utf8mb4 COMMENT = 'app用户表'

-- 插入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`,`email`,`phone`,`gender`,`password`,`age`)
	VALUES(CONCAT('用户',i),'24736743@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` = '用户9999'

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'

SELECT * FROM student

-- id_表明_字段名
-- create index 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);

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

7.3 索引原则

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

---

索引的数据结构！（了解）

Hash类型的索引

Btree：InnoDB的默认数据结构

08、权限管理和备份

8.1 用户管理

SQL命令操作

用户表：mysql.user

本质：读这张表的增删改查

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER d IDENTIFIED BY '123456'

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('123456')

-- 修改密码（修改制定用户密码）
SET PASSWORD FOR d = PASSWORD('111111')

-- 重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER d TO dd

-- 用户授权 grant all privileges 全部的权限， 库.表
-- all privileges 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO dd

-- 查询权限
SHOW GRANTS FOR dd -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost

-- root用户权限：GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` with grant option

-- 撤销权限 revoke 哪些权限， 在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM dd

-- 删除用户
DROP USER dd

8.2 MySQL备份

为什么要备份：

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

MySQL数据库备份的方式

• 直接拷贝物理文件
• 在Sqlyog这种可视化工具中手动导出
  • 在想要导出的表或者库中，右键，选择备份的表或者库
• 使用命令行导出 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:/b.sql

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

# 导入
# 登录的情况下，切换到指定的数据库
# source 备份文件

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

假设你要备份数据库，防止数据库丢失

吧数据库给朋友！sql文件给别人即可

09、规范数据库设计

9.1 为什么需要设计

<mark>当数据库比较复杂的时候，我们就需要设计了</mark>

糟糕的数据库设计：

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

良好的数据库设计：

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

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

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

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

• 收集信息，分析需求
  • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
  • 分类表（文章分类，谁创建的）
  • 文章表（文章的信息）
  • 评论表
  • 友链表（友链信息）
  • 自定义表（系统信息，某个关键的子，或者一些主字段）key：value
• 标识实体（把需求落地到每个字段）
• 标识实体之间的关系
  • 写博客：user-->blog
  • 创建分类：user->category
  • 关注：uset-->user
  • 友链：links
  • 评论：user-user-blog

9.2 三大范式

为什么需要数据规范化？

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

三大范式

第一范式（1NF，确保每列保持原子性）

如果数据库表中的所有字段值<mark>都是不可分解的原子值</mark>，就说明该数据库表满足了第一范式。

比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。

第二范式（2NF，确保表中的每列都和主键相关）

前提：满足第一范式

第二范式需要<mark>确保数据库表中的每一列都和主键相关</mark>，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

订单信息表

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。

第三范式（3NF，确保每列都和主键列直接相关，而不是间接相关）

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

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

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。

10、JDBC（重点）

10.1 数据库驱动

我们程序会通过数据库驱动和数据库打交道

10.2 JDBC

sun 公司为了简化开发人员的（对数据库的统一）操作，提供了一个（java操作数据库的）规范俗称jdbc

这些规范的实现有具体的厂商去做

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

java.sql

javax.sql

还需要导入数据库驱动包

10.3 第一个JDBC程序

1. 创建一个普通项目
2. 导入数据库驱动
3. 编写测试代码

package com.lesson01;

import java.sql.*;

// 我的第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法
        //2.用户信息和url
        // useUnicode=true&characterEncoding=utf8mb4&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";
        //3.连接成功，数据库对象 Connection 代表数据库
        Connection connection = DriverManager.getConnection(url,username,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("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();
    }
}

步骤总结：

1. 加载驱动
2. 连接数据库DriverManager
3. 获得执行sql对象 statement
4. 获得返回的结果集
5. 释放连接

---

DriverManager

// DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
        Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法

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

URL

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

// mysql -- 3306
// jdbc:mysql://主机地址：端口号/数据库名?参数1&参数2&参数3

// oralce -- 1521
// jdbc:oracle:thin:@localhost:1521

Statement执行SQL对象 PrepareStatement执行SQL的对象

String sql = "select * from user";


statement.executeQuery();	// 查询操作返回 ResultSet
statement.execute();	// 执行任何SQL
statement.executeUpdate();	// 更新、插入、删除。都是用这个，返回一个受影响的行数

ResultSet查询的结果集：封装了所有的查询结果

获得指定的数据类型

resultSet.getObject(); // 在不知道类型的情况下使用
// 如果知道列的类型就使用指定的类型
resultSet.getObject();
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
...

遍历指针

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

释放资源

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

10.4 statement对象

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

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

Statement. executeQuery方法用于向数据库发送查询语句，executequery方法返回代表查询结果的Result Sety对象

---

CRUD操作-create

使用executeUpdate(String sql)方法完成数据库添加操作

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

CRUD操作-delete

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

CRUD操作-update

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

CRUD操作-read

Statement st = conn.createStatement();
String sql = "selete * from user where id = 1";
// ResultSet rs = st.executeUpdate(sql);
ResultSet rs = st.executeQuery(sql); // 效率更高
if(rs.next()){
    // 根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

代码实现

1. 提取工具类
2. 编写增删改的方法，executeUpdate
3. 查询

// dp.properties 文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

// 工具类 JdbcUtils.java
package com.lesson02.utils;

import com.mysql.cj.protocol.Resultset;

import javax.swing.plaf.nimbus.State;
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("dp.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 | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // 获取连接
    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();
            }
        }
    }
}


// TestInsert.java 测试插入
package com.lesson02;

import com.lesson02.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) {

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

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "Insert into users(id, `NAME`, `PASSWORD`, `email`, `birthday`) \n"+
                    "VALUES(4, 'd', '123456', '123@qq.com', '2020-01-01')";

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

// 测试查询
package com.lesson02;

import com.lesson02.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) {

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

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            String sql = "select * from users where id = 1";

            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL注入问题

sql存在漏洞，会被攻击导致漏洞

package com.lesson02;

import com.lesson02.utils.JdbcUtils;

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

//测试SQL注入
public class SQLzhuru {
    public static void main(String[] args) {
        login("'or' 1=1", "123456");
    }

    public static void login(String username, String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            String sql = "select * from users where `name` = '" + username+"'and `password` = '"+password+"'";

            rs = st.executeQuery(sql);
            if(rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

10.5 PreparedStatement对象

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

1. 新增
2. 删除
3. 更新
4. 查询
5. 防止SQL注入

package com.lesson03;

import com.lesson02.utils.JdbcUtils;

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

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
            // String sql = "delete from users where id = ?"
            // String sql = "update users | set `NAME` = ? = where id = ?"

            st = conn.prepareStatement(sql);

            st.setInt(1,44);
            st.setString(2,"ddd");
            st.setString(3,"123456");
            st.setString(4,"123@163.com");
            // 注意点： sql.Date    数据库  java.sql.Date()
            //          util.Date  Java    new Date().getTime() 获取时间戳
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));

            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

10.6 使用IDEA连接数据库

10.7 事务

<mark>要么都成功，要么都失败</mark>

ACID原则

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

一致性：总数不变

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

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

隔离性的问题：

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

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

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

处理事务的方法：

代码实现

1、开启事务conn.setautocommit(false);

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

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

package com.lesson04;

import com.lesson02.utils.JdbcUtils;

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

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

        try {
            conn = JdbcUtils.getConnection();
            conn.setAutoCommit(false);

            String sql1 = "update account set money = money-100 where name = 'a'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            String sql2 = "update account set money = money+100 where name = 'b'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            conn.commit();
            System.out.println("成功！");

            conn.setAutoCommit(true);

        } catch (SQLException e) {
            try {
                conn.rollback();    // 如果失败，就回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.9 数据库连接池

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

连接 --- 释放 十分浪费系统资源

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

---

--- 开门-- 业务员：等待 ---服务---关门

最小连接数：10

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

等待超时：100ms

---

<mark>编写连接池，实现一个接口 Datasource</mark>

---

开源数据源实现（拿来即用）

DBCP

C3P0

Druid：阿里巴巴

---

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

DBCP

需要用到的jar包

commons-dbcp-1.4 commons-pool-1.6

---

C3P0

需要用到的jar包

c3p0-0.9.5.5 mchange-commons-java-0.2.19

---

结论

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

Druid

以上，就是我看b站遇见狂神说MySQL视频学习总结的笔记