MySQL

1、初识MySQL

JavaEE： 企业级Java开发 Web

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

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

数据库(存数据，Txt, Excel，word)

只会写代码，学好数据库，基本混饭吃！

操作系统，数据结构与算法！当一个不错的程序猿！

离散数学，数字电路，体系结构，编译原理。+实战经验，高级程序猿优秀的程序猿

为什么学习数据库

1、岗位需求

2、现在的世界，大数据时代~,得数据库者得天下。

3、被迫需求：存数据

4、数据库是所有软件体系中最核心的存在 DBA

什么是数据库

数据库（DB, Database）

概念：数据仓库，软件，安装在操作系统（window,inux,mac、…. ）之上！SQL，可以存储大量的数据。500万！

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

数据库分类

关系型数据库：(SQL)

• MYSQL, Oracle, Sql Server, DB2, SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储，学员信息表，考勤表，…

非关系型数据库：( NoSQL) Not Only

• Redis, MongDB
• 非关系型数据库，对象存储，通过对象的自身的属性来決定。

DBMS（数据库管理系统）

• 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据；
• MYSQL,数据库管理系统！

MySQL简介

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

前世：瑞典MySQL AB 公司

今生：属于 Oracle 旗下产品

MySQL 是最流行的关系型数据库管理系统之一。

在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

开源的数据库软件。

体积小、速度快、总体拥有成本低。

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

官网：https://www.mysql.com/

安装建议：

1. 尽量不要用exe，注册表，卸载麻烦
2. 尽可能使用压缩包安装

安装教程：https://www.kuangstudy.com/bbs/1373432201856712705

SQLyog安装和使用

1. 无脑安装

2. 注册

3. 打开连接数据库

   

4. 新建一个数据库 school

   

   每一个sqlyog的执行操作，本质就是对应了一个sql，可以在软件的历史记录中查看

5. 新建一张表 student

   字段：id name age
   

   

6. 查看表

   

   

7. 添加多条记录

   

连接数据库

命令行连接

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

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

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

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

show tables; --查看数据库中所有的表

describe student; --显示数据库中所有的表的信息

create database yt; --创建数据库

exit; --退出连接

-- 单行注释（SQL 的本来的注释）
/*
(sql的多行注释)
hello
world
*/

数据库 xxx 语言 CRUD 增删改查！

DDL 定义

DML 操作

DQL 查询

DCL 控制

2、操作数据库

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

mysql关键字不区分大小写

操作数据库

创建数据库

create database if not exists yt;

删除数据库

drop database if exists yt;

使用数据库

-- ` 键 在tab 键的上面，如果你的表名或字段名是一个特殊字符，就需要带``
use `yt`;

查看数据库

show databases; -- 查看数据库

学习思路：

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

数据库的列类型

数值

字符串

时间日期

null

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

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

Unsigned：

• 无符号的整数
• 不能申明为负数

zerofill：

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

自增：

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

非空：

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

默认：

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

扩展：

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

操作数据库表

创建数据库表（重点）

格式：

create table [if not exists]  `表名`(
	`字段名` 列类型  [属性]  [索引]   [注释],
	`字段名` 列类型  [属性]  [索引]   [注释],
	`字段名` 列类型  [属性]  [索引]   [注释],
	......
	`字段名` 列类型  [属性]  [索引]   [注释],
)[表类型] [字符集设置] [注释]

例子：

-- 注意：使用英文()，表的名字和字段尽量使用``括起来
-- 所有语句后面加,(英文的)，最后一个不用加
-- 
-- NOT NULL    不为空
-- AUTO_INCREMENT 自增
-- DEFAULT 默认值 
-- COMMENT  注释
create table if not exists `student`(
	`id` int(10) 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

常用命令：

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

数据库表的类型

数据库引擎

• InnoDB 默认使用
• MyISAM 早些年使用

常规使用操作：

• MyISAM：节约空间、速度较快
• InnoDB：安全性高、事物的处理、多表多用户 操作

在物理空间存在的位置

所有的数据库文件都存在data目录下，本质还是文件的存储！一个数据库对应一个文件夹！

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

• InnoDB：所有的表都保存在同一个数据文件中，在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1 文件

• MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。

  • .frm(文件存储表定义)

  • MYD(MYData，存储数据文件)

  • MYI(MYIndex，存储索引文件)

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

charset=utf8

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

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

在mysql的安装目录下找到my.ini，打开my.cnf以后，在[client]和[mysqld]下面均加上default-character-set=utf8，保存并关闭。

修改删除表

修改

-- 修改表名 
alter table 旧表名 rename as 新表名
-- 增加表的字段 
alter table 表名 add 字段名 列属性
-- 修改表的字段 （重命名，修改约束）
alter table 表名 modify 字段名 列属性 -- 修改约束
alter table 表名 change 旧字段名 新字段名 [列属性] -- 重命名
-- 删除表的字段
alter table 表名 drop 字段名

删除

-- 删除表（如果表存在再删除）
drop table if exists 表名

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

注意：

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

3、MySQL数据管理

外键（了解即可）

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

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(10) 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 '年级id',
	`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 IF NOT EXISTS `Student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pasw` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(3) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '生日',
	`gradeid` INT(10) NOT NULL COMMENT 'grade学号',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
	`email` VARCHAR(80) 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 外键的表(外键表的哪个字段)

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

最佳实践

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

DML语言（全部记住）

数据库意义：数据存储、数据管理
DML语言：数据操作语言

• insert
• update
• delete

添加

insert

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

-- 由于主键自增我们可以省略（如果不写表的字段，它就会一一匹配
INSERT INTO `grade` VALUES('大三')

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

-- 插入多个字段
INSERT INTO `grade`(`gradename`)
VALUES('大二'),('大一')
INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES('李四','3243','女'),('李发的','343243','女'),('发的四','32323243','女');

**语法：**insert into 表名(字段1,字段2,字段3…) values(值1,值2,值3…),(值1,值2,值3…)…
注意事项

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

修改

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

-- 修改学员的名字
UPDATE `student` SET `name`='平庸' WHERE id=1

-- 不指定条件的情况下，会修改所有的数据！
UPDATE `student` SET `name`='平庸' 

-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='仔细',`address`='武汉' WHERE id=3

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

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

-- 通过多个条件定位数据
UPDATE `student` SET `name`='仔细',`address`='武汉' WHERE `name`='平庸' AND sex='男'

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

注意

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

UPDATE `student` SET `name`='仔细',`birthday`=CURRENT_TIME WHERE `name`='仔细' AND sex='女'

删除

delete

语法：DELETE FROM 表名 WHERE [条件]

-- 删除数据（避免这样写，会删除全部)
DELETE FROM `student`

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

TRUNCATE命令

-- 清空 student 表
TRUNCATE `Student`

delete和TRUNCATE区别

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

例子：

-- 测试delete和 TRUNCATE区别
CREATE TABLE `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAR(30) NOT NULL,
	PRIMARY KEY(`id`)
)

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

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

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

INNODB 自增列会重1开始（存在内存当中，断电即失）
MYISAM，继续从上一个自增了开始（存在文件中的，不会丢失）

4、DQL查询数据

DQL

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

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

select完整语法：

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[AS alias1]][,table.field1[AS alias2]][,....]}
FROM table_name [AS table_alias]
[LEFT | RIGHT | INNER JOIN table_name2]  -- 联合查询
[WHERE ...}  -- 指定结果需要满足的条件
[GROUP BY .....] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[OFFSET,] ROW_COUNT | rou_countoffset OFFSET}];
-- 指定查询的记录从那条至哪条

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

CREATE DATABASE IF NOT EXISTS `school`
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
    `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
    `phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
    `address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
    `borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
    `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentno`),
    UNIQUE KEY `identitycard`(`identitycard`),
    KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
	`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
	`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `classhour` INT(4) DEFAULT NULL COMMENT '学时',
    `gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
    PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;

-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `subjectno` INT(4) NOT NULL COMMENT '课程编号',
    `examdate` DATETIME NOT NULL COMMENT '考试日期',
    `studentresult` INT (4) NOT NULL COMMENT '考试成绩',
    KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 插入成绩数据  这里仅插入了一组，其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

指定查询字段

-- 查询全部的学生 ： Select 字段 from 表
SELECT * FROM student

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

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

-- 函数  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 * 54 AS 计算结果 -- 用来计算 （表达式）
SELECT
	@@auto_increment_increment -- 查询自增的步长（变量）

数据库中的表达式：文本值、列、Null、函数、计算表达式、系统变量…
Select 表达式 from 表

where条件语句

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

逻辑运算符

尽量使用英文字母

-- ========================================= 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 95 AND 100

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

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

模糊查询：比较运算符

-- ===============  模糊查询   ===================
-- 查询姓张的同学
-- 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 ('北京')

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

联表查询

JOIN 对比

-- ===============  联表查询JOIN  ==================

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

/*思路
1、分析需求，分析查询的字段来自哪些表（连接查询）
2、确定使用哪种连接查询？ 7中
确定交叉点（这两个表中哪个数据是相同的)
判断的条件：学生表中 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


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

-- right join
SELECT s.studentno,studentname,`subjectno`,`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno

-- left join
SELECT s.studentno,studentname,`subjectno`,`studentresult`
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno

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

-- 思考题（查询了参加考试的同学信息：学号，学生姓名，科目名，分数）
/*思路
1、分析需求，分析查询的字段来自哪些表，student、result、subject（连接查询）
2、确定使用哪种连接查询？ 7中
确定交叉点（这两个表中哪个数据是相同的)
判断的条件：学生表中 studentNo = 成绩表 studentno
*/

-- right join
SELECT s.studentno,studentname,`subjectname`,`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

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

自连接

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

父类：

子类：

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

-- 查询父子信息：把一张表看成两个一模一样的表
select a.`categoryname`as '父栏目',b.`categoryname` as '子栏目'
from `category` as a, `category` as b
where a.`categoryid`=b.`pid`

分页和排序

排序

-- 排序：升序 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

排序

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

-- 分页，每页只显示五条数据
-- 语法：limit起始值，页面大小
-- 网页应用：当前，总页数，页面大小
-- limit 0,5     1~5
-- limit 1,5     2~6
-- limit 6,5
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresu`
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
-- 第N页 limit （n-1)*5,5   (n-1)*pagesize,pagesize

-- 【pagesize:页面大小】
-- 【(n-1)*pagesieze：起始值】
-- 【n：当前页】
-- 【数据总数/页面大小 = 总页数】

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

子查询

where（这个值是计算出来的）
本质：在where语句中嵌套一个子查询语句

-- 1、查询 数据库结构-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 r.`subjectno`=sub.`subjectno`
where `subjectname`='高等数学-2' and studentresult>=80

-- 分数不小于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'
)

-- 在改造（由里及外）
SELECT DISTINCT `studentno`,`studentname` from student where studentno in(
	select studentno from result where studentresult>80 and subjectno={
		SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
	}
)

分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心： （根据不同的课程分组）
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 平均分 > 80

select小结

5、MySQL函数

官网：https://dev.mysql.com/doc/refman/8.0/en/functions.html

常用函数

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

-- 数学运算
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('我','爱','你们') -- 拼接字符串
SELECT INSERT('我爱编程hellowold',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长度
SELECT LOWER('Yangtao')  -- 小写字母
SELECT UPPER('yangtao')  -- 大写字母
SELECT INSTR('yangtao','a')  -- 返回第一次出现的子串的索引
SELECT REPLACE('狂神说坚持就能成功','坚持','努力')  -- 替换出现的指定字符串
SELECT SUBSTR('yt说坚持就能胜利',4,6)  -- 返回指定的字符串（源字符串，截取的位置，截取的长度)
SELECT REVERSE('清晨我上啊') -- 反转


-- 查询姓 张的同学，  名字  邹
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()

聚合函数(常用)

-- =================  聚合函数  ======================
-- 都能够统计  表中的数据（想查询一个表中有多少个记录，就使用这个count()）
select count(`borndate`) from student   -- count（字段），会忽略所有的null值
SELECT COUNT(*) FROM student   -- count（*），不会忽略null值， 本质  计算行数
SELECT COUNT(1) FROM student   -- 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

数据库级别的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,'lisi','123456'),(3,'wangwu','123456')

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

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

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

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

6、事务

什么是事务

要么都成功，要么都失败

1、SQl执行 A给B转账 A 1000 --> 200 B 200

2、SQL执行 B收到A的钱 A 800 --> B 400

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

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

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）
事务前后数据的完整性必须保持一致。

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

持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

隔离所导致的一些问题

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

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

页面统计查询值

点击生成报表的时候，B有人转账进来300（事务已经提交）

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

执行事务

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

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

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

INSERT xxx
INSERT xxx

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

-- 事务借宿
SET autocommit = 1 -- 开启自动提交

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

模拟事务

-- 转账
CREATE DATABASE IF NOT EXISTS shop CHARACTER SET utf8 COLLATE utf8_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 autoccomit = 1;  -- 恢复默认值

7、索引

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

索引的分类

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

主键索引（PRIMARY KEY）

• 唯一表示，主键不可重复，只能有一个列作为主键

唯一索引（UNIQUE KEY）

• 避免重复出现，唯一索引的可以重复，多个列都可以标示唯一索引

常规索引（KEY/INDEX）

• 默认的， index/key关键字来设置

全文索引（FullText）

• 在特定的数据库引擎下才有，MyISAM
• 快速定位数据

基础语法

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

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

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

-- EXPLAIN 分析sql执行的状况

EXPLAIN SELECT * FROM student; -- 非全文索引

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

测试索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'


-- 插入100万数据.
DELIMITER $$
-- 写函数之前必须要写，标志
CREATE FUNCTION moca_data ()
RETURNS INT 
DETERMINISTIC
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),'264324@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 

INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('用户',i),'264324@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100))
SELECT moca_data() -- 执行此函数 生成一百万条数据

SELECT * FROM app_user WHERE `name`='用户9999';  -- 1.845 sec
SELECT * FROM app_user WHERE `name`='用户9999';  -- 1.045 sec
SELECT * FROM app_user WHERE `name`='用户9999';  -- 0.994 sec


-- id_表名_字段名
-- CREATE INDEX 索引名 ON 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM app_user WHERE `name`='用户9999';  -- 0.180 sec

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

创建索引前：

创建索引后：

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

索引原则

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

索引的数据结构

Hash 类型的索引
Btree：INNODB的默认数据结构

8、权限管理和备份

用户管理

SQLyog可视化管理

SQL命令操作

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

-- 创建用户 
CREATE USER yt IDENTIFIED BY '123456'

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

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR yt = PASSWORD('123456')

-- 重命名  RENAME USER 原来名字 TO 新的名字
RENAME USER yt TO yt2

-- 用户授权 ALL PRIVILEGES ON 全部权限 , 库.表
-- ALL privileges 除了给别人的授权，其它都能够干
GRANT ALL PRIVILEGES ON *.* TO yt2

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

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

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

-- 删除用户
DROP USER yt2

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

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

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

假设你要备份数据库，防止数据丢失。
把数据库给朋友！sql文件给别人即可

9、规范数据库设计

为什么需要设计

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

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

良好的数据库设计：

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

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

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

设计数据库的歩奏：（个人博客）

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

三大范式

为什么需要数据规范化？

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

三大范式

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，

但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，

这样就不满足第二范式的要求，调整如下，需分成两个表：

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，

而不是主键“学号”，所以需做如下调整：

（规范数据库的设计）
规范性和性能问题
关联查询的表不得超过三张表

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

10、JDBC

数据库驱动

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

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

JDBC

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

java.sql
javax.sql
还需要导入一个数据库驱动包mysql-connector-java-5.1.47.jar

第一个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、导入数据库驱动

3、编写测试代码

public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动
        Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动
        //2、用户信息和url
        //useUnicode=true&characterEncoding=utf8&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  执行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("psw = "+ 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. 连接数据库DriverManager
3. 获得执行的sql的对象 Statement
4. 获得返回的结果集
5. 释放连接

DiverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.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
//协议： //主机地址:端口号/数据库名?参数1&参数2&参数3

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

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

获取指定的数据类型

resultset.getobject();   //在不知道列类型的情况下使用
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();

遍历，指针

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

statement 执行SQL语句 PrepareStatement执行SQL的对象

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

statement对象

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

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

使用executeUpdate（String sql）方法完成数据添加操作

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

使用executeUpdate（String sql）方法完成数据添加操作

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

使用executeUpdate（String sql）方法完成数据添加操作

Statement st = conn.createStatement();
String sql = "select * from user where id = 1";
int num = st.executeUpdate(sql);
if(num>0){
	//根据获取列的数据类型，分别调用rs相应方法映射到java对象中
}

代码实现

1、提取工具类

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=root

JdbcUtils

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");
            //驱动只用加载一次
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    /**
     * 获取连接
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * 释放连接资源
     *
     * @param conn
     * @param st
     * @param res
     */
    public static void release(Connection conn, Statement st, ResultSet res) {
        if (res != null) {
            try {
                res.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
    }
}

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

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

        try {
            conn = JdbcUtils.geConnection();  // 获取数据库连接
            st = conn.createStatement(); // 获得SQL执行的对象
            String sql = "insert into `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)values(5,'bingning','123456','233445@qq.com','2000-10-23')";
            int i = st.executeUpdate(sql);
            if(i > 0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,res);
        }
    }
}

删 Jdbcdelete

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

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

            String sql = "DELETE FROM users WHERE id = 5";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,res);
        }
    }
}

更新Jdbcupdate

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

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

3、编写查找的方法
查找 JdbcSelect

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

        try {
            conn = JdbcUtils.geConnection();  // 获取数据库连接
            st = conn.createStatement(); // 获得SQL执行的对象
            String sql = "SELECT * FROM users WHERE id =1";
            ResultSet resultSet = st.executeQuery(sql);
            while(resultSet.next()){
                System.out.println("name = "+ resultSet.getObject("NAME"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,res);
        }
    }
}

SQL注入的问题

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

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

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

        try {
            conn = JdbcUtils.geConnection();  // 获取数据库连接
            st = conn.createStatement(); // 获得SQL执行的对象
            //SELECT * FROM users WHERE `NAME`= ''or'1=1' AND `password`=''or'1=1'
            String sql = "SELECT * FROM users WHERE `NAME`= '"+name+"' AND `password`='" + password + "'";
            ResultSet resultSet = st.executeQuery(sql);
            while(resultSet.next()){
                System.out.println("name = "+ resultSet.getObject("NAME"));
                System.out.println("=============");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,res);
        }
    }
}

PreparedStatement对象

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

1、新增

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

        try {
            conn = JdbcUtils.geConnection();
            //区别
            //使用?占位符代替参数

            String sql = "insert into users (`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values (?,?,?,?,?);";
            st = conn.prepareStatement(sql); // 预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,5);  // id
            st.setString(2,"xian");
            st.setString(3,"234535");
            st.setString(4,"2342533@qq.com");
            //注意点： sql.Date  数据库  java.sql.Date()
            //        util.Date java  new Date().getTime()获取时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));
            //执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

2、删除

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

        try {
            conn = JdbcUtils.geConnection();
            //区别
            //使用?占位符代替参数

            String sql = "delete from users where id=?";
            st = conn.prepareStatement(sql); // 预编译SQL，先写sql，然后不执行

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

            //执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

3、修改

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

        try {
            conn = JdbcUtils.geConnection();
            //区别
            //使用?占位符代替参数

            String sql = "update users set `NAME` = ? where id=?;";
            st = conn.prepareStatement(sql); // 预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            st.setString(1,"冰凝");
            st.setInt(2,3);
            //执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("更新成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

4、查询

public class SelectTest {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet res = null;
        try {
            conn = JdbcUtils.geConnection();
            //区别
            //使用?占位符代替参数

            String sql = "select * from users where id=?;";
            st = conn.prepareStatement(sql); // 预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,2);
            //执行
            res = st.executeQuery();

            while(res.next()){
                System.out.println("name = "+ res.getObject("NAME"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,res);
        }
    }
}

5、防止SQL注入问题

public class SQLZR01 {
    public static void main(String[] args) {
//        login("bingning","123456");
        login("''or1=1","''or1=1");
    }

    public static void login(String name,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet res = null;

        try {
            conn = JdbcUtils.geConnection();  // 获取数据库连接

            //prepareStatement防止SQL注入的本质，把传递进来的参数当做字符
            String sql = "SELECT * FROM users WHERE `NAME`=? AND `password`=?";
            st = conn.prepareStatement(sql); // 获得SQL执行的对象
            st.setString(1,name);
            st.setString(2,password);
            res = st.executeQuery(sql); //查询完毕会返回一个结果
            while(res.next()){
                System.out.println("name = "+ res.getObject("NAME"));
                System.out.println("=============");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,res);
        }
    }
}

使用IDEA连接MySQL

连接前提：必须导入

1、添加MySQL

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

3、双击查看数据库

4、控制台

连接失败，查看原因

事务

要么都成功，要么都失败

ACID原则

代码实现

1、开启事务com.setAutoCommit(false);
2、一组业务执行完毕，提交事务
3、可以在catch语句中显示的定义，回滚语句，但默认失败就会回滚

 public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement st=null;
        try {
            connection=JdbcUtils.getConnection();
//        3、通知数据库开启事务
            connection.setAutoCommit(false);
            String sql = "update account set money = money-100 where name = 'A'";
            st=connection.prepareStatement(sql);
            st.executeUpdate();
//            制造错误
//            int i = 1/0;
            String sql1 = "update account set money = money+100 where name = 'B'";
            st=connection.prepareStatement(sql1);
            st.executeUpdate();
            connection.commit(); //以上两条SQL都执行成功了就提交事务
            System.out.println("success");
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }finally {
          JdbcUtils.release(connection,st,null);
        }

    }

数据库连接池

数据库连接 - - 执行完毕 - - 释放
连接 - - 释放 十分浪费系统的资源
池化技术：准备些预先的资源，过来就连接预先准备好的

最小连接数：10
最大练接数：15
等待超时：100ms

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

开源数据源实现（拿过来使用就好）

DBCP
C3P0
Druid：阿里巴巴

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

• JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：
  • DBCP 是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持。
  • C3P0 是一个开源组织提供的一个数据库连接池，**速度相对较慢，稳定性还可以。**hibernate官方推荐使用
  • Proxool 是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点
  • BoneCP 是一个开源组织提供的数据库连接池，速度快
  • Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不确定是否有BoneCP快
• DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池
• DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度。
• 特别注意：
  • 数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。
  • 当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close( ); 但conn.close( )并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

DBCP

需要用到的jar包
commons-dbcp1.4、``commons-pool-1.6`

工具类：

package com.yt.dbcp;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * ClassName:JdbcDbcp
 * Package:com.yt.dbcp
 * Description:
 *
 * @date:2021/7/12 15:28
 * @author:yt
 */

public class JdbcDbcp {
    private static DataSource dataSource=null;
    static {
        try {
            InputStream in = JdbcDbcp.class.getClassLoader().getResourceAsStream("dbcp.properties");
            Properties properties = new Properties();
            properties.load(in);
            //创建数据源 工厂模式 --> 创建
            dataSource=BasicDataSourceFactory.createDataSource(properties);
        }  catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 获取连接
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        //从数据源中获取连接
        return dataSource.getConnection();
    }

    /**
     * 释放连接资源
     *
     * @param conn
     * @param st
     * @param res
     */
    public static void release(Connection conn, Statement st, ResultSet res) {
        if (res != null) {
            try {
                res.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
    }

}

配置文件：

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=root
#<!--minIdle: 最小空闲连接-->
minIdle=5
#<!--maxIdle: 最大空闲连接-->
maxIdle=20
#<!--maxActive: 最大连接数量-->
maxActive=50
#<!--initialSize: 初始化连接-->
initialSize=10
#<!--maxWait: 超时等待时间以毫秒为单位 1000等于60秒-->
maxWai=60000
connectionProperties=useUnicode=true;characterEncoding=utf8
defaultAutoCommit=true

C3P0

需要用到的jar包
c3po-0.9.5.5、``mchange-commons-java-0.2.19`

工具类：

package com.yt.c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

/**
 * ClassName:JdbcC3p0
 * Package:com.yt.c3p0
 * Description:
 *
 * @date:2021/7/12 15:50
 * @author:yt
 */

public class JdbcC3p0 {
    private static DataSource dataSource=null;
    static {
        dataSource = new ComboPooledDataSource();
    }


    /**
     * 获取连接
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        //从数据源中获取连接
        return dataSource.getConnection();
    }

    /**
     * 释放连接资源
     *
     * @param conn
     * @param st
     * @param res
     */
    public static void release(Connection conn, Statement st, ResultSet res) {
        if (res != null) {
            try {
                res.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
    }
}

配置文件：

<?xml version="1.0" encoding="UTF-8"?>

<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property> <!-- he's important, but there's only one of him -->
    </default-config>

    <named-config name="mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false</property>
        <property name="user">root</property>
        <property name="password">root</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property> <!-- he's important, but there's only one of him -->
    </named-config>
</c3p0-config>

Druid

需要用到的jar包

druid-1.2.6.jar

工具类：

package com.yt.druid;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * ClassName:JdbcDruid
 * Package:com.yt.druid
 * Description:
 *
 * @date:2021/7/12 16:20
 * @author:yt
 */

public class JdbcDruid {
    private static DataSource dataSource=null;
    static {
        try {
            InputStream in = JdbcDruid.class.getClassLoader().getResourceAsStream("druid.properties");
            Properties properties = new Properties();
            properties.load(in);
            //创建数据源 工厂模式 --> 创建
            dataSource= DruidDataSourceFactory.createDataSource(properties);
        }  catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 获取连接
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        //从数据源中获取连接
        return dataSource.getConnection();
    }

    /**
     * 释放连接资源
     *
     * @param conn
     * @param st
     * @param res
     */
    public static void release(Connection conn, Statement st, ResultSet res) {
        if (res != null) {
            try {
                res.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
    }

}

配置文件：

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=root
initialSize=10
maxActive=15
maxWait=3000

结论

</property>
        <property name="password">root</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property> <!-- he's important, but there's only one of him -->
    </named-config>
</c3p0-config>

Druid

需要用到的jar包

druid-1.2.6.jar

工具类：

package com.yt.druid;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * ClassName:JdbcDruid
 * Package:com.yt.druid
 * Description:
 *
 * @date:2021/7/12 16:20
 * @author:yt
 */

public class JdbcDruid {
    private static DataSource dataSource=null;
    static {
        try {
            InputStream in = JdbcDruid.class.getClassLoader().getResourceAsStream("druid.properties");
            Properties properties = new Properties();
            properties.load(in);
            //创建数据源 工厂模式 --> 创建
            dataSource= DruidDataSourceFactory.createDataSource(properties);
        }  catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 获取连接
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        //从数据源中获取连接
        return dataSource.getConnection();
    }

    /**
     * 释放连接资源
     *
     * @param conn
     * @param st
     * @param res
     */
    public static void release(Connection conn, Statement st, ResultSet res) {
        if (res != null) {
            try {
                res.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        }
    }

}

配置文件：

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=root
initialSize=10
maxActive=15
maxWait=3000

结论

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