狂神说-MySQL

一、初始MySQL

JavaEE：企业级Java开发 Web

前端（页面：展示：数据）

后台 （连接点：连接数据库JDBC,连接前端（控制视图跳转，给前端传递数据））

数据库（存数据，Txt,Excel,Word）

1、概念

• 数据库：(DB,DataBase)

• 概念:数据仓库，软件，安装在操作系统之（windows,Linux。mac）上的！SQL,可以存储大量的数据，500万!

• 作用:存储数据，管理数据 Excel

2、数据库分类

• 关系型数据库：(SQL)
  • MySQL, Oracle, sql Server, DB2, SQLite
  • 通过表和表之间，行和列之间的关系进行数据的存储
• 非关系型数据库：(NoSQL) Not Only SQL
  • Redis, MongDB
  • 非关系型数据库，对象存储，通过对象自身的属性来决定。
• DBMS(数据库管理系统)
  • 数据库的管理软件，科学有效的管理我们的数据，维护和获取数据
  • MySQL ，数据管理系统

3、数据库引擎

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

• 优劣对比：
  • MYISAM：节约空间，速度较快
  • INNODB：安全性高，支持事务处理，多表多用户操作

在物理空间存在的位置：
所有的数据库文件都存在 data 目录下，一个文件夹就对应一个数据库
本质还是文件的存储！
MySQL 引擎在物理文件上的区别

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

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

CHARSET=utf8

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

• 建议在 my.ini 中配置默认的编码
• character-set-server=utf8

二、操作数据库

• 修改密码

UPDATE mysql.user SET aunthentication_string=PASSWORD('123456') 
WHERE USER='root' AND HOST='localhost';--更改用户名为root的密码
flush privileges; --刷新权限
show databases;--查看所有的数据库

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

show tables;--查看数据库中所有的表
describe student;--显示数据库中所有的表的信息
create database westos;--创建一个数据库

exit;--退出连接

--单行注释（sql本来注释）
/*
多行注释
*/

2.1、操作数据库

• 创建数据库

CREATE DATABASE IF NOT EXISTS westos

• 删除数据库

DROP DATABASE IF EXISTS westos

• 特殊字符要加上

`user`

• 常用命令-找语句的方法

show create database school -- 查看创建数据库的语句
show create table student -- 查看student数据表的定义语句
desc student -- 显示表的结构

2.2、数据库的列类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小 3个字节
• int 标准的整数 4个字节（常用）
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节 （精度问题）
• decimal 字符串形式的浮点数,金融计算的时候，一般用

字符串

• char 字符串固定大小 0-255
• varchar 可变字符串 0-65535（常用）
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1 (保存大文本)

时间日期

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

null

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

2.3、数据库的字段类型

• unsigened：
  • 无符号的整数
  • 声明该列不能声明负数
• zerofill：
  • 0填充的
  • 10的长度 1 – 0000000001 不足位数用0 填充
• 自增：
  • 通常理解为自增，自动在上一条记录的基础上+1
  • 通常用来设计唯一的主键 index,必须是整数类似
  • 可以自定义设置主键自增的起始值和步长
• 非空 NULL not Null
  • 假设设置为 not null，如何不给他赋值，就会报错
  • NULL 如果不填写，默认为NULL
• 默认：
  • 设置默认的值
  • 如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 CHARSET=utf8

• 格式

/*
CREATE TABLE [IF NOT EXISTS] 表名( 
	`字段名` 数据类型和长度 [属性] [索引] [注释], 
	`字段名` 数据类型和长度 [属性] [索引] [注释], 
	......
	`字段名` 数据类型和长度 [属性] [索引] [注释], 
	[PRIMARY KEY (`字段名`) ]
)[表类型][字符集设置][注释]; 
*/

• 修改表

-- 修改表 alter table `旧表名` name as `新表名`
ALTER TABLE `teacher` RENAME AS `teacher1`
-- 添加字段 alter table `表名` add `字段` 约束
ALTER TABLE `teacher1` ADD `age` INT(10)
-- 修改表的字段（修改约束,重命名）
-- alter table 表名 modify 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
-- alter table 表名 change 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(2) -- 字段重命名

-- 删除表的字段：alter table 表名 drop 字段名
ALTER TABLE teacher1 DROP age1
-- 删除表：drop table if exists 表名
DROP TABLE IF EXISTS teacher1

• 所有的创建和删除都尽量加上判断，以免报错
• 注意点：
  • 字段名用 `` 包裹
  • sql 关键字大小写不敏感，建议写小写

三、MySQL数据管理

3.1、外键

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

-- 学生表的 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

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

• 以上的操作都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰）

• 最佳实践：

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

3.2、DML语言

• 数据库意义：数据存储，数据管理
• DML（Data Manipulation Language）数据库操作语言

3.3、添加

insert

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

-- 由于主键自增我们可以省略字段名，但是它会一一匹配，所以参数必须包含所有字段的值
INSERT INTO `grade` VALUES (3, '大三')

-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES('大一'),('大二')

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

INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('张三','aaaaaa','男')

INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES('李四','aaaaaa','男'),('王五','bbbbbb','男')

语法：insert into 表名([字段名1,字段名2,字段名3]) values(‘值1’),(‘值2’),(‘值3’),…)

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

3.4、修改

update

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

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

-- 修改多个属性
UPDATE `student` SET `name`='狂神',email = '552456727@qq.com' WHERE id = 1;

-- 通过多个条件定位数据
UPDATE `student` SET `name` = '阿巴' WHERE `name`='狂神' AND sex='女'

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

• where 语句操作符
  • <> 或者 != ：不等于
  • between A and B ：AB之间（包括AB）

3.5、删除

delete命令

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

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

TRUNCATE 命令

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

-- 清空 student 表
TRUNCATE `student`

delete命令 和 TRUNCATE 命令 的区别

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

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

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

DELETE FROM `test` -- 不会影响自增

TRUNCATE `test` -- 自增会归零

• delete 删除的问题：重启数据库现象
  • InnoDB：自增列会从1开始（存在内存当中的，断电即失）
  • MyISAM：继续从上一个自增量开始（存在文件中的，不会丢失）

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

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]  -- 排序 DESC 降序 ASC 升序
    [limit (起始位置，查询长度)] 

4.1、基本概念

• Data Query Language：数据查询语言
• 所有的查询操作都使用：select
• 简单复杂的查询它都能做，最重要！

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

去重

• 作用：去除 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，函数，计算表达式，系统变量。。

• select 表达式 from 表

4.3、where 条件子句

• 作用：检索数据中符合条件的值
• 搜索的条件由一个或者多个表达式组成！结果为：布尔值

逻辑运算符

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 80 AND 100

-- 查询除了1000号学生以外的同学的成绩
SELECT studentno,`studentresult` FROM result
WHERE studentno != 1000
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,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（具体的一个或者多个值，不能用%，%是和like搭配使用的）===============
-- 查询 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

4.4、联表查询

• on 和 where 的区别：不管 on 上的条件是否为真都会返回 left 或 right 表中的记录

JOIN对比

操作符名称	描述
Inner join	如果表中有至少一个匹配，则返回行
left join	从左表中返回所有的行，即使右表中没有匹配
right join	从右表中返回所有的行，即使左表中没有匹配

• 七种Join
  
• 测试

/*
连接查询
  如果需要多张数据表的数据进行查询，则可通过连接运算符实现多个查询
- 内连接 inner join
  查询两个表中的结果集中的交集
- 外连接 outer join
  - 左外连接 left join
    以左表作为基准，右表来一一匹配，匹配不上的，返回左表的记录，右表以NULL填充
  - 右外连接 right join
    以右表作为基准，左表来一一匹配，匹配不上的，返回右表的记录，左表以NULL填充
*/

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

--  ==========联表查询============
-- Join（表）on (判断的条件) 连接查询
-- where 等值查询
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 AS s
RIGHT JOIN result AS r
ON r.`studentno` = s.`studentno`

-- left 左连接（查询所有同学，不考试的也会查出来）
SELECT s.studentno, studentname, subjectno, studentresult
FROM student s
LEFT JOIN result r
ON s.`studentno` = r.`studentno`

-- 查缺考的同学（左连接应用场景）
SELECT s.studentno, studentname, subjectno, studentresult
FROM student s
LEFT JOIN result r
ON s.`studentno` = r.`studentno`
WHERE studentresult IS NULL

-- 思考题：查询参加了考试的同学信息（学号，学生姓名，科目名，分数）
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

自连接

/*
自连接：
  数据表与自身进行连接
  
需求：从一个包含栏目ID，栏目名称和父栏目ID的表中
      查询父栏目名称和其它子栏目名称
*/

-- 创建表并插入数据

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技术');

-- 编写SQL语句，将栏目的父子关系呈现出来（父栏目名称，子栏目名称）
-- 核心思想：把一张表看成两张一模一样的表，然后将这两张表连接查询（自连接）
SELECT a.categoryname AS '父栏目', b.categoryname AS '子栏目'
FROM category AS a, category AS b
WHERE a.`categoryid` = b.`pid`

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

-- 查询学员及其所属的年级（学号，学生姓名，年级名）
SELECT studentno AS 学号, studentname AS 学生姓名, gradename AS 年级名称
FROM student s
INNER JOIN grade g
ON s.`gradeid` = g.`gradeid`

-- 查询科目及其所属年级（科目名称，年级名称）
SELECT subjectname AS 科目名称, gradename AS 年级名称
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 r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '数据库结构-1'

4.5、排序和分页

排序

--  ==========排序===========
/*
语法：order by
  order by 语句用于根据指定的列对结果集进行排序
  order by 语句默认按照ASC升序对记录进行排序
  如果希望按照 降序 对记录进行排序，可以使用 DESC 关键字
*/

-- 查询 数据库结构-1 的所有考试结果（学号，学生姓名，科目名称，成绩）
-- 按成绩降序排序
SELECT s.studentno, studentname, subjectname, studentresult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC

分页

-- ===========分页=============
/* 
语法：limit(起始下标, 查询长度） -- 个人觉得这样好理解
      limit[pageNo:页码,pageSize:单页面显示条数]  -- 原版本
好处：用户体验，网络传输，查询压力
*/
-- 每页显示5条数据
SELECT s.studentno, studentname, subjectname, studentresult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC, studentno
LIMIT 0,5

-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECT s.studentno, studentname, subjectname, studentresult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = 'JAVA第一学年'
ORDER BY studentresult DESC
LIMIT 0,10

4.6、子查询

/*============== 子查询 ================
什么是子查询?
   在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
   嵌套查询可由多个子查询组成,求解的方式是由里及外;
   子查询返回的结果一般都是集合,故而建议使用IN关键字;
*/
-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT studentno, r.subjectno, studentresult
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC

-- 方法二:使用子查询(执行顺序:由里及外)
SELECT studentno, subjectno, studentresult
FROM result
WHERE subjectno = (
  SELECT subjectno FROM `subject`
  WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC

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

-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT r.studentno, studentname
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
WHERE studentresult >= 80

-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.studentno, studentname
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
WHERE studentresult >= 80 AND subjectno = (
  SELECT subjectno FROM `subject`
  WHERE subjectname = '高等数学-2'
)

-- 方法三:使用子查询
-- 分步写简单sql语句,然后将其嵌套起来
SELECT studentno, studentname
FROM student
WHERE studentno IN (
  SELECT studentno FROM result
  WHERE studentresult >= 80 AND subjectno = (
    SELECT subjectno FROM `subject`
    WHERE subjectname = '高等数学-2'  
  )
)


-- 练习题目:查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数)
SELECT s.studentno, studentname, studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = 'C语言-1'
ORDER BY studentresult DESC
LIMIT 0,5


-- 练习题目:使用子查询,查询郭靖同学所在的年级名称
SELECT gradename FROM grade
WHERE gradeid = (
  SELECT gradeid FROM student
  WHERE studentname = '郭靖'
)

五、MySQL函数

5.1、常用函数

数据函数

 SELECT ABS(-8);  /*绝对值*/
 SELECT CEILING(9.4); /*向上取整*/
 SELECT FLOOR(9.4);   /*向下取整*/
 SELECT RAND();  /*随机数,返回一个0-1之间的随机数*/
 SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/

字符串函数

 SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
 SELECT CONCAT('我','爱','程序');  /*合并字符串,参数可以有多个*/
 SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');  /*替换字符串,从某个位置开始替换某个长度*/
 SELECT LOWER('KuangShen'); /*小写*/
 SELECT UPPER('KuangShen'); /*大写*/
 SELECT LEFT('hello,world',5);   /*从左边截取*/
 SELECT RIGHT('hello,world',5);  /*从右边截取*/
 SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  /*替换字符串*/
 SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/
 SELECT REVERSE('狂神说坚持就能成功'); /*反转
 
 -- 查询姓周的同学,改成邹
 SELECT REPLACE(studentname,'周','邹') AS 新名字
 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 VERSION();  /*版本*/
 SELECT USER();     /*用户*/

5.2、聚合函数

函数名称	描述
COUNT()	返回满足Select条件的记录总和数，如 select count(*) 【不建议使用 *，效率低】
SUM()	返回数字字段或表达式列作统计，返回一列的总和。
AVG()	通常为数值字段或表达列作统计，返回一列的平均值
MAX()	可以为数值字段，字符字段或表达式列作统计，返回最大的值。
MIN()	可以为数值字段，字符字段或表达式列作统计，返回最小的值。

 -- 聚合函数
 /*COUNT:非空的*/
 SELECT COUNT(studentname) FROM student;
 SELECT COUNT(*) FROM student;
 SELECT COUNT(1) FROM student;  /*推荐*/
 
 -- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
 -- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
 -- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；
 -- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。
 /*
 很多人认为count(1)执行的效率会比count(*)高，原因是count(*)会存在全表扫描，而count(1)可以针对一个字段进行查询。其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。
 
 下面它们之间的一些对比：
 
 1）在表没有主键时，count(1)比count(*)快
 2）有主键时，主键作为计算条件，count(主键)效率最高；
 3）若表格只有一个字段，则count(*)效率较高。
 */
 
 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破解原理，背后有一个字典，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,'kuangshen','123456'),(2,'qinjiang','456789'),(3,'wangwu','123456')

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

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

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

六、事务

6.1、什么是事务

要么都成功，要么都失败

---

SQL执行， A给B转账 A 1000 –> 200 B 200
SQL 执行， B收到A的钱 A 800 — B 400

---

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

事务原则 ： ACID原则

原子性是事务的基础，持久性和隔离性是手段，一致性是目的

• 原子性（Atomic）

  • 要么都成功，要么都失败

• 一致性（Consist）

  • 事务前后的数据完整性要保持一致

• 隔离性（Isolated）

  • 事务发生，在有多个用户并发时，互不干扰

• 持久性（Durable）

  • 事务一旦提交就不可逆转，被持久化到数据库中

事务隔离级别

• 脏读：

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

• 不可重复读：

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

• 虚读(幻读)

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

执行事务

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

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

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

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


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

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

-- 保存点  
SAVEPOINT -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT -- 回滚到保存点
RELEASE SAVEPOINT -- 删除保存点

模拟场景

/*
课堂测试题目

A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000

创建数据库shop和创建表account并插入2条数据
*/
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account (`name`,`cash`) VALUES('A',2000.00),('B',10000.00)

-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务
UPDATE account SET cash=cash-500 WHERE `name`='A' -- A减500
UPDATE account SET cash=cash+500 WHERE `name`='A' -- A加500
COMMIT -- 提交事务
ROLLBACK -- 回滚
SET autocommit = 1  -- 恢复默认值

6.2、事务的四种隔离级别及应用场景

https://blog.csdn.net/shenxinde/article/details/123522935

七、索引

作用

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
• 全文检索字段进行搜索优化

7.1、分类

• 主键索引 (Primary Key)
• 唯一索引 (Unique)
• 常规索引 (Index)
• 全文索引 (FullText)

主键索引

主键 : 某一个属性组能唯一标识一条记录

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

唯一索引

• 作用 : 避免同一个表中某数据列中的值重复
• 与主键索引的区别：
  • 主键索引只能有一个
  • 唯一索引可能有多个

CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
   -- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

常规索引

• 作用 : 快速定位特定数据
• 注意 :
  • index 和 key 关键字都可以设置常规索引
  • 应加在查询找条件的字段
  • 不宜添加太多常规索引,影响数据的插入,删除和修改操作

CREATE TABLE `result`(
   -- 省略一些代码
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)

-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

全文索引

• 作用：快速定位特定数据
• 注意：
  • 只能用于MyISAM类型的数据表
  • 只能用于CHAR , VARCHAR , TEXT数据列类型
  • 适合大型数据集

/*
#方法一：创建表时
  　　CREATE TABLE 表名 (
               字段名1 数据类型 [完整性约束条件…],
               字段名2 数据类型 [完整性约束条件…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [索引名] (字段名[(长度)] [ASC |DESC])
               );


#方法二：CREATE在已存在的表上创建索引
       CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
                    ON 表名 (字段名[(长度)] [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上创建索引
       ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            索引名 (字段名[(长度)] [ASC |DESC]) ;
                           
                           
#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;


#显示索引信息: SHOW INDEX FROM student;
*/

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);

/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。
-- 对于表中的每个记录行，MATCH() 返回一个相关性值。
-- 即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

/*
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/

基础语法

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

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

-- 增加一个全文索引：（索引名） 列名
-- 意思是，可以给我们创建的索引起一个名字，用来分开、分辨不同的索引
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`)

-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM student -- 非全文索引
EXPLAIN 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 NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

-- 插入100w条数据
DROP FUNCTION IF EXISTS mock_data;
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), '552456727@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' -- 0.041 sec
EXPLAIN SELECT * FROM app_user WHERE NAME = '用户9999' -- 0.001 sec

-- 创建索引
CREATE INDEX idx_app_user_name ON app_user(NAME) -- 10.031 sec
EXPLAIN SELECT * FROM app_user WHERE NAME = '用户9999' -- 0.001 sec
SELECT * FROM app_user WHERE NAME = '用户9999' -- 0 sec

7.3、索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表建议不要加索引
• 索引一般应加在查找条件的字段

索引的数据结构

• 我们可以在创建上述索引的时候，为其指定索引类型，分两类
  • hash类型的索引：查询单条快，范围查询慢
  • btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

阅读： http://blog.codinglabs.org/articles/theory-of-mysql-index.html

八、权限管理和备份

8.1、用户管理

使用SQLyog 创建用户，并授予权限演示

基本命令

/* 用户和权限管理 */ 

-- 刷新权限
FLUSH PRIVILEGES

-- 增加用户 
CREATE USER kuangshen IDENTIFIED BY '123456'
/*
  - 语法：CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
  - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
  - 只能创建用户，不能赋予权限。
  - 用户名，注意引号：如 'user_name'@'192.168.1.1'
  - 密码也需引号，纯数字密码也要加引号
*/

-- 重命名用户 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user

-- 设置密码
SET PASSWORD = PASSWORD('密码')    -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码')    -- 为指定用户设置密码

-- 删除用户 DROP USER kuangshen2
DROP USER 用户名

-- 分配权限/添加用户
/*
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
  - all privileges 表示所有权限
  - *.* 表示所有库的所有表
  - 库名.表名 表示某库下面的某表
*/

-- 查看权限   SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
-- 查看当前用户权限
SHOW GRANTS; 
--或 
SHOW GRANTS FOR CURRENT_USER; 
--或 
SHOW GRANTS FOR CURRENT_USER();

-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名    -- 撤销所有权限

8.2、MySQL 备份

• 数据库备份必要性
  • 保证重要数据不丢失
  • 数据转移
• MySQL数据库备份方法

  • mysqldump备份工具

  • 数据库管理工具，如SQLyog
    

  • 直接拷贝数据库文件和相关配置文件

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

-- 导出
-- 1. 导出一张表 -- 　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
　　mysqldump -uroot -p123456 school student >D:/a.sql
-- 2. 导出多张表 -- mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
mysqldump -uroot -p123456 school student result >D:/a.sql

-- 3. 导出所有表 -- mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
mysqldump -uroot -p123456 school >D:/a.sql

-- 4. 导出一个库 -- mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
mysqldump -uroot -p123456 -B school >D:/a.sql

可以-w携带备份条件

-- 导入
-- 1. 在登录mysql的情况下：source 备份文件
source D:/a.sql
　　
-- 2. 在不登录的情况下
　　mysql -u用户名 -p密码 库名 < 备份文件

九、规范数据库设计

9.1、为什么需要数据库设计

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

• 糟糕的数据库设计：
  • 数据冗余，浪费空间
  • 数据库插入和删除都会麻烦，异常【屏蔽使用物理外键】
  • 程序的性能差
• 良好的数据库设计：
  • 节省内存空间
  • 保证数据库的完整性
  • 方便我们开发系统
• 软件项目开发周期中数据库设计 ：
• 需求分析阶段: 分析客户的业务和数据处理需求
• 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.
• 设计数据库步骤
  • 收集信息，分析需求
    • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
    • 分类表（文章分类，谁创建的）
    • 文章表（文章的信息）
    • 友链表（友链信息）
    • 自定义表（系统信息，某个关键的字，或者某些主字段）
    • 说说表（发表心情…id ，content ，create_time）
  • 标识实体（把需求落地到每个字段）
  • 标识实体之间的关系
    • 写博客 user –> blog
    • 创建分类 user –> category
    • 关注 user –> user
    • 友链 links
    • 评论 user –> user -> blog（就是自连接表，父id 和 子id）

9.2、三大范式

• 为什么需要数据规范化？
  • 信息重复
  • 更新异常
  • 插入异常
  • 删除异常
    • 无法正常显示异常
  • 删除异常
    • 丢失有效的信息

三大范式

• 第一范式（1NF）
  • 原子性：保证每一列不可再分
• 第二范式（2NF）
  • 前提：满足第一范式
  • 每张表只描述一件事情（非部分依赖）
• 第三范式（3NF）
  • 前提：满足第一范式和第二范式
  • 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关（非间接依赖）

规范性和性能的问题

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

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

十、JDBC

10.1、数据库驱动

驱动：声卡，显卡，数据库

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

10.2、JDBC

• SUN 公司为了简化开发人员的（对数据库的统一）操作，提供了一个(Java操作数据库的)规范，俗称 JDBC
• 这些规范的实现由具体的厂商去做
• 对于开发人员来说，我们只需要掌握JDBC的接口操作即可

java.sql
javax.sql
还需要导入一个数据库驱动包

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')

• 1.创建一个普通项目
• 2.导入数据库驱动
  • 注意：导入的是jar包，刚下载过来的是zip包，要先解压
    
• 3.编写测试代码

package com.kuang.lesson01;

import java.sql.*;

// 我的第一个 JDBC 程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver"); // 固定写法，加载驱动

        //2.用户信息和 url
        //注意！这里要添加时区，并且要令useSSL=false
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "123456";

        //3.连接成功，数据库对象  Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        //4.执行 SQL 的对象  Statement 执行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"));
            System.out.println("==============================");
        }

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

    }
}

• 步骤总结：
  • 1.加载驱动
  • 2.连接数据库 DriverManager
  • 3.获取执行SQL的对象 Statement
  • 4.获得返回的结果集
  • 5.释放连接

常用对象解释

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver"); // 固定写法，加载驱动

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

上面的推荐使用下面那行代码，因为上面那行本身也会注册一次，如下图所示。

URL

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC";

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

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

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

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

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

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

• 获得指定的数据类型

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

• 遍历，指针

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

释放资源

resultSet.close();
statement.close();
connection.close();

10.4、statement 对象

• JDBC中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
• Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sq|语句， executeUpdate执行完后， 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
• Statement.executeQuery方法用于向数据库发生查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作-create

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

CRUD操作-delete

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

CURD操作-update

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

CURD操作-read

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

代码实现

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 (Exception 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();
            }
        }
    }
}

2、编写增删改的方法，executeUpdate

• 增

package com.kuang.lesson02;

import com.kuang.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();    //获得SQL的执行对象
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(5,'shuaige','123456','552456727@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.kuang.lesson02;

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

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

        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) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

• 改

package com.kuang.lesson02;

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

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

        try {
            conn = JdbcUtils.getConnection();   //获取数据库连接
            st = conn.createStatement();    //获得SQL的执行对象
            String sql = "UPDATE users SET `NAME`='shuaige', `email`='552456727@qq.com' WHERE id = 1";
            int i = st.executeUpdate(sql);
            if (i>0) {
                System.out.println("更新成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

3、查询 executeQuery

package com.kuang.lesson02;

import com.kuang.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();

            //SQL
            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);
        }
    }
}

4、SQL注入问题

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

package com.kuang.lesson02;

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

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

    //登录业务
    public static void login(String username, String password){

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

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

            // SELECT * FROM users WHERE `NAME` = 'kuangshen' AND `password` = '123456';

            // SELECT * FROM users WHERE `NAME` = '' or '1=1' AND `password` = '' or '1=1';
            String sql = "select * from users where `NAME` = '" + username + "' AND `password` = '" + password + "'";

            rs = st.executeQuery(sql);  // 查询完毕会返回一个结果集
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
                System.out.println("===========================");
            }

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

    }
}

10.5、PreparedStatement 对象

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

插入

package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;
import java.util.Date;
import java.sql.*;

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(?,?,?,?,?)";

            st = conn.prepareStatement(sql);    //预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1, 4);    //id
            st.setString(2, "shuaige");
            st.setString(3, "123123");
            st.setString(4, "552456727@qq.com");
            //注意点： sql.Date     数据库     java.sql.Date()
            //         util.Date    Java       new Date().getTime() 获得时间戳
            //注意，获得时间戳的代码需要导入包：import java.util.Date;
            st.setDate(5, new java.sql.Date(new Date().getTime()));

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

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, null);  //没有结果集，所以参数为null
        }

    }
}

删除

package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

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

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

        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用 ? 占位符代替参数
            String sql = "delete from users where id=?";

            st = conn.prepareStatement(sql);    //预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1, 4);

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("删除成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, null);  //没有结果集，所以参数为null
        }

    }
}

修改

package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

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

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

        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用 ? 占位符代替参数
            String sql = "update users set `NAME` = ? where id=?;";

            st = conn.prepareStatement(sql);    //预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            st.setString(1, "阿巴阿巴");
            st.setInt(2, 1);

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("修改成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, null);  //没有结果集，所以参数为null
        }

    }
}

解决SQL注入问题

package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

import java.sql.*;

public class SQLInjection {
    public static void main(String[] args) {
//        login("lisi", "123456");
        login("'' or 1=1", "123456");
    }

    //登录业务
    public static void login(String username, String password){

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

        try {
            conn = JdbcUtils.getConnection();
            // PreparedStatement 防止SQL注入的本质，把传递进来的参数当作字符
            // 假设其中存在转义字符，比如说 ' 会被直接转义
            String sql = "select * from users where `NAME`=? and `PASSWORD`=?";

            st = conn.prepareStatement(sql);
            st.setString(1, username);
            st.setString(2, password);

            rs = st.executeQuery();  // 查询完毕会返回一个结果集
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
                System.out.println("===========================");
            }

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

    }
}

10.6、使用IDEA连接数据库

• 连接成功后，选择数据库
  
• 双击数据库
  
• 更新数据
  
• 编写sql代码的地方
  

10.7、JDBC事务

要么都成功，要么都失败

ACID原则

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

一致性：结果总数不变

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

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

隔离性的问题：

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

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

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

代码实现

• 数据库插表和插入数据

/*创建账户表*/
create table account(
  id int primary key auto_increment,
  name varchar(40),
  money float
);

/*插入测试数据*/
insert into account(name, money) VALUES ('A',1000);
insert into account(name, money) VALUES ('B',1000);
insert into account(name, money) VALUES ('C',1000);

• Java代码
  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 TestTransaction1 {
    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("成功");

        } catch (SQLException e) {
            try {
                conn.rollback();    // 如果失败则回滚事务
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

10.8、数据库连接池

介绍了几种连接池和相应所需的配置，但其实在后面MyBatis中会有更详细的介绍，所以这一块内容暂且省略。