MySQL基础

根据以下学习视频，个人整理的笔记

https://www.bilibili.com/video/BV1NJ411J79W?spm_id_from=333.999.0.0&vd_source=7a8946d22777450e46486d5fd60d8d4d

初始MySQL

JavaEE：企业级Java开发 Web

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

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

数据库（存数据，txt，excel，word）

学会操作系统，数据结构与算法，离散数学，编译原理，数字电路，体系结构加上实战经验才能当上高级程序员

为什么要学习数据库

• 岗位需求
• 现在的世界，大数据时代，得数据者得天下
• 被迫需求：存数据
• 数据库是所有软件体系中最核心的存在 DBA（数据库管理员）专门研究数据库

什么是数据库

数据库（DB）

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

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

数据库分类

• 关系型数据库（SQL）（Excel）行和列

  • MySQL，Oracle，Sql Server，DB2，SQLlite

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

• 非关系型数据库（NoSQL：Not Only SQL）（{key：value}）键值

  • Redis，MongDB
  • 对象存储，通过对象自身的属性来决定

DBMS：数据库管理系统

• 数据库的管理软件，能够科学有效地管理我们的数据，维护和获取数据
• MySQL，本质就是数据库管理系统

MySQL简介

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

• 前世：由瑞典 MySQL AB 公司开发

• 今生：属于 Oracle 旗下产品

• MySQL是最好的RDBMS（关系数据库管理系统）应用软件之一

• 开源的数据库软件

• 体积小、速度快、总体拥有成本低，所有人必须会

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

• MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言

企业常用：5.7版本，稳！

安装建议

• 尽量不要使用exe的安装，卸载麻烦
• 尽可能使用压缩包安装

mysql下载链接：https://cdn.mysql.com//archives/mysql-5.7/mysql-5.7.19-winx64.zip

为了更好的学习,我们需要可视化界面，而不仅仅是通过命令行黑窗口管理数据库。SQLyog 就是一个快速而简洁的图形化管理MYSQL数据库的工具。

SQLyog下载可以网上搜

安装MySQL

• 解压
• 把这个包放到自己的电脑环境目录下
• 添加环境变量，在path下添加MySQL的bin目录路径就ok了
• 在MySQL的目录下（E:\Environment\mysql-5.7.19-winx64）新建mysql配置文件 （.ini）

新建配置文件的内容（目录一定要换成自己的）

[mysqld]
basedir=E:\Environment\mysql-5.7.19-winx64\
datadir=E:\Environment\mysql-5.7.19-winx64\data\
port=3306
skip-grant-tables

• 启动管理员模式下的CMD，运行所有的命令
  • 切换到mysql的bin目录下
  • 执行 mysqld -install 命令

如图所示则安装成功，若出现问题上网查找解决

我这里出现了应用程序无法正常启动(0xc000007b)的问题
查阅资料后, 得知原因是系统dll动态链接库或者c++依赖库缺失导致安装失败, 所以需要修复这些库
解决方案：
使用DirectX_Repair4.0工具解决
下载工具后解压即可
以管理员身份运行DirectX Repair.exe文件
工具=>选项=>扩展 | 点击开始扩展
工具=>选项=>高级 | 勾选智能检测，c++强力修复  点击确定
回到主页 | 点击检测并修复

再重新去cmd中安装即可

• 没问题的话，接下来继续在cmd中执行命令
  • 输入 mysqld --initialize-insecure --user=mysql 命令初始化数据文件

不报错则成功，此时mysql目录下会多一个data文件夹

• 输入命令 net start mysql 启动mysql服务

• 然后用命令 mysql -u root -p 这里要注意，-p后面一定不能加空格，然后进入mysql管理界面（密码可以为空）

• 执行命令 update mysql.user set authentication_string=password(‘123456’) where user=‘root’ and Host = ‘localhost’; 更新mysql的root密码

• 执行命令 flush privileges; 刷新权限

• 然后修改my.ini文件删除最后一句skip-grant-tables，或者加#注释掉，因为这一句是跳过密码进去的
• 最后重启mysql即可正常使用

exit     #退出mysql   ctrl+c 暴力结束退出

net stop mysql   #停止mysql服务
net start mysql  #开启mysql服务

mysql -u root -p #输入密码进入mysql

（在mysql下执行的sql语句后面一定要加分号）

sc delete mysql   #清空服务，干掉mysql再重新安装

Sqlyog 软件安装和使用

• 安装路径可以选择自己的路径

旗舰版的打开后需要输入key

• 名称：kuangshen
• 证书密钥：8d8120df-a5c3-4989-8f47-5afc79c56e7c

输入后即可注册成功

• 创建一个连接，名称随意（尽量英文）
• mysql的默认端口号为3306
• 输入密码即可连接**（切记要先启动mysql服务，启动mysql服务一定要用管理员模式去运行cmd输入命令启动。启动mysql服务后才能连接）**

以后我们建的所有的数据库信息都在data目录下

sqlyog 默认的四个数据库不要去动它

创建一个新的数据库的默认选项（保证中文不乱码）

创建school的例子

查看历史记录可以看见创建一个数据库的SQL语句

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

选中school数据库右键，创建表的时候

• 引擎默认选择为InnoDB
• 字符集选utf8
• 核对选utf8_general_ci

查看表

• 选中student右键，打开表
• 可以直接输入数据，添加完后点击刷新然后保存

基本命令行操作

命令行连接

sql本质上的注释符号为 – 这里的–后面加个空格

在sqlyog实际操作中的注释符号为 # 或者 – 这里的–后面加个空格

mysql -u root -p123456  --连接数据库

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

-- 刷新权限
flush privileges;

-- -----------------------------------------------------------
-- --------------mysql下的所有的语句都使用分号结尾-----------------



#查询所有的数据库
show databases;

#切换数据库（这里切换为school数据库）
use school;

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

#显示数据库中选定的表的信息
describe student;

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

#退出mysql连接
exit;



#单行注释
-- 单行注释
/*
多行注释
*/

数据库XXX语言

• DDL 定义

• DML 操作

• DQL 查询

• DCL 控制

操作数据库

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

MySQL的关键字不区分大小写

• 创建数据库

CREATE DATABASE IF NOT EXISTS westos;
-- IF NOT EXISTS 可选

• 删除数据库

DROP DATABASE IF EXISTS westos;
-- IF EXISTS 可选

• 使用数据库

-- 如果表名或者字段名是一个特殊字符，就需要带 ``
USE `school`;

• 查看所有的数据库

SHOW DATABASES;

学习思路

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

列的数据类型讲解

• 数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等大小的数据 3个字节
  • int 标准的整数（常用的）4个字节
  • bigint 较大的数据 8个字节
  • float 浮点数 4个字节
  • double 浮点数 8个字节（精度问题！）
  • decimal 字符串形式的浮点数 金融计算的时候，一般使用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 时间戳 这个也较为常用
  • year 年份表示

• null

  • 空值，未知
  • 注意，不要使用NULL进行运算。运算的结果为NULL

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

• Unsigned

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

• zerofill

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

• 自增

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

• 非空 not NULL

  • 假设设置为not null，如果不给它赋值，就会报错
  • 如果不设置not null，没有值的话默认就是null

• 默认

  • 设置默认的值
  • 例如sex，可以设置默认值为男

拓展：

有些项目的规范，每一个表都必须存在以下五个字段！这几个字段未来做项目用的

• id 主键

• `version` 乐观锁

• is_delete 伪删除

• gmt_create 创建时间

• gmt_update 修改时间

创建数据库表

例子

-- 注意：使用英文括号，表的名称和字段尽量使用 `` 括起来
-- AUTO_INCREMENT 自增
-- COMMENT 注释
-- 字符串一般使用单引号括起来
-- 所有的语句后面加 ,      最后一个字段不用加
-- 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

-- IF NOT EXISTS 可选
-- 字段名和列类型 是必写的
-- ENGINE=INNODB 表的类型

常用命令的补充

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

数据表的类型

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

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

常规的使用操作：

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

在物理空间存在的位置

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

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

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

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

charset=utf8

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

我们可以在 my.ini 中配置默认的编码

character-set-server=utf8

修改和删除数据表字段

• 修改

-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1

-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)

-- 修改表的字段（重命名，修改约束）
-- ALTER TABLE 表名 MODIFY 字段名 列属性
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字
ALTER TABLE teacher1 CHANGE age age1 INT(11) -- 字段重命名 可同时修改约束

• 删除

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

-- 删除表
DROP TABLE IF EXISTS teacher1

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

数据库级别的外键

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

例子

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


CREATE TABLE `grade` (
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年纪id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

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

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

例子

-- 学生表的 gradeid 字段，要去引用年级表的 gradeid
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建表的时候没有外键关系
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪个表（哪个字段）;


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

以上的操作都是物理外键，数据库级别的外键，我们不建议使用。避免数据库过多造成困扰。以上代码看懂即可

最佳实践

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

DML语言（全部记住）

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

DML 语言：数据操作语言

• insert

• update

• delete

• 添加

例子

-- 插入语句
-- insert into 表名(字段名1，字段名2，字段名3) values('值1','值2','值3')
-- 同一个字段插入多个值，注意括号
-- insert into 表名(字段名1) values('值1'),('值2'),('值3')
INSERT INTO `grade`(`gradename`) VALUES('大四');

-- 由于主键自增我们可以省略
-- 一般写插入语句，我们一定要数据和字段一一对应

-- 插入多个字段
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('王五','bbbbbb','女'),('王八','abcabc','女');

字段名可以不写，但添加的值必须要一一对应，不能少

• 修改

例子

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

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

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

-- 语法
-- UPDATE 表名 SET colnum_name=value WHERE 条件;

-- 通过多个条件定位数据
UPDATE `student` SET `name`='哈哈哈' WHERE `name`='长江七号' AND `sex`='女';

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

操作符	含义	范围	结果
=	等于	5=6	false
<> 或 !=	不等于	5<>6	true
>
<
<=
>=
BETWEEN…AND…	在某个范围内，闭合区间
AND	两个条件都成立
OR	或者

value，可以是一个具体的值，也可以是一个变量

UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='哈哈哈' AND `sex`='男';

• 删除

例子

-- 删除指定数据，不加where会全部删除
DELETE FROM `student` WHERE `id`=1;

-- truncate  完全清空一个数据库表，表的结构和索引约束不会变
-- 清空student表
TRUNCATE `student`;

DELETE FROM `student`;

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

-- 测试 truncate 和 delete 区别
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），自增列则会重新开始。因为它是存在内存中的

• 如果重启数据库（类型为myisam）则继续从上一个自增量开始。因为它是存在文件中的，不会丢失

DQL查询数据（重点）

• 所有的查询操作都用它 Select

• 简单的查询，复杂的查询它都能做

• 数据库中最核心的语言，最重要的语句

• 使用频率最高的语句

• 指定查询字段

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

-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student

-- 别名，可以给字段起别名  也可以给表起别名 AS  有时候可以省略AS
SELECT `StudentNo` AS 学号,`StudentName` AS 学生名字 FROM student AS S

-- 拼接函数 Concat(a,b)
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student

• 去重查询

-- 去重查询
-- 查询一下有哪些同学参加了考试
SELECT * FROM result -- 查询全部的考试成绩

SELECT DISTINCT `StudentNo` FROM result -- 去除所有重复的StudentNo，只显示一条

• 数据库的列

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

-- 学员考试成绩+1   查看
SELECT `StudentNo`,`StudentResult`+1 AS '提升一分后' FROM result

数据库中的表达式

• 文本的值
• 列
• NULL
• 函数
• 计算表达式
• 系统变量

select + 表达式

• where 条件字句

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

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

逻辑运算符

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

尽量使用英文字母

例子

-- where
SELECT `StudentNo`,`StudentResult` FROM result

-- 查询考试成绩在 95~100 分之间的
SELECT `StudentNo`,`StudentResult` FROM result WHERE StudentResult>=95 AND StudentResult<=100

-- 模糊查询
SELECT `StudentNo`,`StudentResult` FROM result WHERE StudentResult BETWEEN 95 AND 100

-- 查询除了1000号学生之外的成绩
SELECT `StudentNo`,`StudentResult` FROM result WHERE StudentNo!=1000

SELECT `StudentNo`,`StudentResult` FROM result WHERE NOT StudentNo=1000

• 模糊查询 本质是比较运算符

运算符	语法	描述
IS NULL		如果操作符为NULL，结果为真
IS NOT NULL
BETWEEN
Like	a like b	如果a能够匹配b，则结果为真
in	a in (a1,a2,…)	假设a在其中某一个值，结果为真

例子

-- 模糊查询
-- 查询姓刘的同学
-- 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

• 联表查询

JOIN 对比

例子

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

/* 分析思路：
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 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


-- 查询了参加考试的同学信息：学号，学生姓名，科目名称，分数
SELECT s.studentNo,StudentName,SubjectName,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo = r.studentNo
INNER JOIN `subject` AS sub
ON r.subjectNo = sub.subjectNo



-- select 字段名 from 表 join 连接的表 on 交叉条件
-- 假设存在多表查询，慢慢来，先从两张表的查询开始

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

from a left join b a为左表，b为右表

• 自连接（作为了解）

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

举例子

父类

pid	categoryid	categoryName
1	2	信息技术
1	3	软件开发
1	5	美术设计

子类

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

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

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

例子

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

• 排序和分页

排序

-- 分页 limit 和 排序 order by
-- 排序：升序 ASC ，降序 DESC

-- 查询的结果根据 成绩降序 排序
SELECT s.studentNo,StudentName,SubjectName,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo = r.studentNo
INNER JOIN `subject` AS sub
ON r.subjectNo = sub.subjectNo
WHERE SubjectName = '数据库结构-1'
ORDER BY `StudentResult` DESC

分页

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

-- 分页，每页只显示五条数据
-- LIMIT 
SELECT s.studentNo,StudentName,SubjectName,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo = r.studentNo
INNER JOIN `subject` AS sub
ON r.subjectNo = sub.subjectNo
WHERE SubjectName = '数据库结构-1'
ORDER BY `StudentResult` DESC
LIMIT 0,5    -- 起始值,当前页面大小

-- 第一页   limit 0,5          (1-1)*5
-- 第二页   limit 5,5          (2-1)*5
-- 第三页   limit 10,5         (3-1)*5
-- 第N页   limit (n-1)*5,5     (n-1)*pagesize,pagesize

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

综合练习

-- 查询 JAVA第一学年 课程成绩排名前十的学生，并且分数要大于80 的学生信息（学号，姓名，课程名称，分数）
SELECT s.StudentNo,StudentName,SubjectName,StudentResult
FROM student AS s
INNER JOIN `result` AS r
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub
ON sub.subjectno = r.subjectno
WHERE SubjectName = 'JAVA第一学年' AND StudentResult>=80
ORDER BY StudentResult DESC
LIMIT 0,10

• 子查询

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

例子

-- 子查询

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

-- 方式二 使用子查询（由里及外）
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` 
WHERE SubjectNo = (
	SELECT SubjectNo FROM `subject` WHERE SubjectName = '数据库结构-1'
)
ORDER BY StudentResult DESC

-- 查询所有数据库结构-1的学生学号
-- select SubjectNo from `subject` where SubjectName = '数据库结构-1'



-- 查询课程为高等数学-2，且分数不小于80分的学生信息（学号，姓名）
-- 方式一
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student AS s
INNER JOIN result AS r
ON r.studentno = s.studentno
INNER JOIN `subject` AS sub
ON sub.subjectno = r.subjectno
WHERE StudentResult>=80 AND SubjectName = '高等数学-2'

-- 方式二
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student AS s
INNER JOIN result AS r
ON r.studentno = s.studentno
WHERE StudentResult>=80 AND SubjectNo = (
	SELECT SubjectNo FROM `subject` WHERE SubjectName = '高等数学-2'
)

• 分组及过滤

例子

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

MySQL的函数

• 常用函数

-- 常用函数

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

-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航')   -- 返回字符串长度
SELECT CONCAT('我','爱','你们') -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱')  -- 插入，替换
SELECT LOWER('KUANGSHEN') -- 转小写
SELECT UPPER('kuangshen')
SELECT INSTR('kuangshen','h') -- 查询字符的index
SELECT REPLACE('狂神说坚持就能成功','坚持','努力') -- 替换字符
SELECT SUBSTR('狂神说坚持就能成功',4,2) -- 返回指定的字符串
SELECT REVERSE('abcd') -- 反转字符串

-- 查询姓周的同学，将周改为邹
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 MIN(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

• 聚合函数（这个我们用得比较多）

函数名称	描述
count()	计数
sum()
avg()
max()
min()

例子

-- 聚合函数

-- 都能统计表中的数据  想查询一个表中有多少个记录，就使用这个count
SELECT COUNT(studentname) FROM student  -- count(指定列)  会忽略null值
SELECT COUNT(*) FROM student; -- 不会忽略null值，本质计算行数
SELECT COUNT(1) FROM student; -- 不会忽略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加密
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

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

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

事务

什么是事务？

要么都成功，要么都失败

---

SQL执行 A给B 转账

SQL执行 B收到A的钱

---

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

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

原子性（A）：针对行为的一致。要么都成功要么都失败

一致性（C）：针对结果的一致。事务前后的数据完整性要保证一致

隔离性（I）：排除其它事务对本次事务的影响

持久性（D）：一旦出现问题，那么在事务提交前，恢复原状；在事务提交后，持久化到数据库。事务一旦提交则不可逆。

隔离所导致的一些问题

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

• 幻读

• 不可重复读

执行事务

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

-- 手动处理事务

-- 第一步关闭自动提交
SET autocommit = 0
-- 事务开启
START TRANSACTION -- 标记一个事务的开启，从这个之后的sql 都在同一个事务内


INSERT xx
INSERT xx

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



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




-- 作为了解
SAVEPOINT -- 设置一个事务的保存点（了解即可）
ROLLBACK TO SAVEPOINT -- 回滚到保存点
RELEASE SAVEPOINT -- 撤销保存点

模拟场景

例子

-- 转账
CREATE DATABASE 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',1000.00)


-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务（一组事务）
UPDATE account SET money=money-500 WHERE `name`='a'
UPDATE account SET money=money+500 WHERE `name`='b'

COMMIT; -- 提交事务，事务一旦提交则持久化
ROLLBACK; -- 回滚事务

SET autocommit = 1; -- 恢复默认值

索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。 即索引的本质是数据结构

索引的分类

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

基础语法

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

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


-- 增加一个索引 (索引名) 列名
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`);

-- 分析sql执行的状况
EXPLAIN SELECT * FROM student  -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘');

测试索引

只作了解

-- 插入100万条数据
DELIMITER $$ -- 写函数之前必须要写这个，相当于标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
		
	WHILE i<num DO
	
		-- 插入语句
		
		SET i=i+1;
	END WHILE;
	RETURN i;
END;



-- 索引名：id_表名_字段名
-- CREATE INDEX 索引名 ON 表字段;
CREATE INDEX id_app_user_name ON app_user(`name`);
	
	
	
-- 通过实践证明当数据量很大时，索引能够大幅度减少查询时间，相当于直接定位

索引原则

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

MySQL权限管理

SQL命令操作

用户表：在mysql数据库下的user表

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

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

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

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


-- 重命名
RENAME USER kuangshen TO kuangshen2

-- 用户授权  给kuangshen2授予对所有的库所有的表所有 权限（除了给别人授权的权限没有）
GRANT ALL PRIVILEGES ON *.* TO kuangshen2

-- 查看权限
SHOW GRANTS FOR kuangshen2

SHOW GRANTS FOR root@localhost -- 查看root的权限
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- root用户的权限有给别人授权的权限

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2

-- 删除用户
DROP USER kuangshen

MySQL备份

备份方式

• 直接拷贝物理文件，data
• 在Sqlyog这种可视化工具中手动导出
  • 在想要导出的表或库中，右键选择备份或导出
  • 选中sql转储
• 命令行（cmd）导出 mysqldump

#mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 >物理磁盘位置/文件名

mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

#主机的地址可以是远程的


#导入
#先登录
mysql -uroot -p123456

#选择数据库，如果要导入的是数据库就没必要选择数据库
use school;
#导入表
source D:/a.sql

规范数据库设计

为什么需要设计？

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

糟糕的数据库设计

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

良好的数据库设计

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

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

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

模拟

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

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

三大范式

为什么需要数据规范化？

信息重复，更新导致异常，插入异常，删除异常

三大范式

• 第一范式

要求数据库表的每一列都是不可分割的原子数据项

• 第二范式

前提满足第一范式，需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关

• 第三范式

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

• 规范性和性能的问题？

阿里的规定：关联查询的表不得超过三张表

• 考虑商业化的需求和目标（成本，用户体验）
• 数据库的性能更加重要
• 故意给某些表增加一些冗余的字段（从多表查询中变为单表查询）
• 故意增加一些计算列（从大数据量降为小数据量的查询，也可以增加索引）

JDBC（重点）

• 数据库驱动

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

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

• JDBC

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

这些规范的实现，由具体的厂商去做。对于开发人员，只需要掌握JDBC接口的操作即可

java.sql

javax.sql

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

以下是狂神用的五个包

我的第一个JDBC程序

第一步，创建测试数据库

eclipse

• 创建一个普通项目
• 导入数据库驱动
• 然后选中导入的文件，右键选择buildpath，选择add to buildpath，才能生效
• 编写测试代码

package jdbc;

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

//我的第一个jdbc测试程序
public class JdbcFirstDemo {
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		
		//加载驱动
		Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
		
		
		//连接信息
		//用户信息和url
		//useUnicode=true&characterEncoding=utf8&useSSL=true
		//  支持中文编码    设置中文字符集编码为utf8    使用安全连接
		String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
		String username = "root";
		String password = "123456";
		
		
		//连接成功，返回数据库对象    
		Connection connection = DriverManager.getConnection(url,username,password);
		//connection这个对象就代表数据库，可以去干数据库级别的事情
        //connection.rollback();        事务回滚
        //connection.commit();			事务提交
        //connection.setAutoCommit();	数据库设置自动提交
        
        //connection.setAutoCommit(false); 在java中关闭数据库的自动提交后，会自动开启事务，和mysql中需要自己开启事务不一样
		//connection.commit();		  业务完成提交事务
        //connection.rollback();      如果失败则回滚事务
       
		//执行  sql的对象 
		Statement statement = connection.createStatement();
		
		
		//用执行sql的对象 去 执行sql，可能存在结果，查看返回结果
		String sql = "select * from users";//编写sql
		
		
		ResultSet resultSet = statement.executeQuery(sql);//返回的结果集，结果集 中封装了我们全部的查询出来的结果
		//statement.executeUpdate();  更新、插入、删除都用这个，返回一个受影响的行数
		//statement.execute();   执行任何sql
        
        
		while(resultSet.next()) {
			System.out.println("id=" + resultSet.getObject("id"));
			System.out.println("name=" + resultSet.getObject("name"));
			System.out.println("pwd=" + resultSet.getObject("password"));
			System.out.println("email=" + resultSet.getObject("email"));
			System.out.println("birthday=" + resultSet.getObject("birthday"));
			System.out.println("========================================");
		}
		
		//释放连接
		resultSet.close();
		statement.close();
		connection.close();
	}
}

执行sql的对象有两个：

Statement 执行sql的对象

PrepareStatement 执行sql对象

ResultSet

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

遍历，指针

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

Statement 对象

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

例子

提取工具类

package lesson02.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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");
			
			
			//加载驱动，只需加载一次
			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(Exception e) {
				e.printStackTrace();
			}
			
		}
		if(st!=null) {
			try {
				st.close();
			}catch(Exception e) {
				e.printStackTrace();
			}
			
		}
		if(conn!=null) {
			try {
				conn.close();
			}catch(Exception e) {
				e.printStackTrace();
			}
			
		}
		
		
	}

}

配置文件 db.properties

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

主函数

package lesson02;

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

import lesson02.utils.JdbcUtils;

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(4,'kuangshen','123456','398156587@qq.com','2020-12-15');";
			
			int i = st.executeUpdate(sql);
			
			if (i>0) {
				System.out.println("插入成功");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, st, rs);
		}
		
		
		
		
	}
}

SQL注入问题

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

因此我们以后不用 Statement 执行sql

package lesson02;

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

import lesson02.utils.JdbcUtils;

public class SQLzhuru {
	public static void main(String[] args) {
		
		//login("lisi","123456");
		
		login("'or'1=1","'or'1=1");//sql存在拼接情况，导致数据会泄露
		
		
		
	}
	
	
	//登录业务
	public static void login(String username,String password) {
		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		
		try {
			conn = JdbcUtils.getConnection();//获取数据库连接
			st = conn.createStatement();//获得sql执行对象
			
			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"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, st, rs);
		}
		
		
	}
	
	
}

PrepareStatement 对象

PrepareStatement：可以防治SQL注入，并且效率更高

例子

package lesson03;

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

import lesson02.utils.JdbcUtils;

public class TestInsert {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		
		try {
			conn = JdbcUtils.getConnection();
			
			//与Statement的区别
			//使用问号占位符代替参数
			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, "qinjiang");//name
			st.setString(3, "123456");//password
			st.setString(4, "qwqwqw@qq.com");//email
			//注意点： sql.Date     数据库         java.sql.Date()
			//        util.Date	   Java       new Date().getTime()获得时间戳
			st.setDate(5, new java.sql.Date(new Date().getTime()));//birthday
			
			
			//执行
			int i = st.executeUpdate();
			
			if(i>0) {
				System.out.println("插入成功");
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.release(conn, st, rs);
			
		}
		
		
	}
}

例子

package lesson02;

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

import lesson02.utils.JdbcUtils;

public class SQLzhuru {
	public static void main(String[] args) {
		
		//login("lisi","123456");
		
		login("'or'1=1","'or'1=1");//无法查询
		
		
		
	}
	
	
	//登录业务
	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=?";//Mybatis
			
			st = conn.prepareStatement(sql);//获得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"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, st, rs);
		}
		
		
	}
	
	
}

使用IDEA连接数据库

连接成功的前提，要导入包，保证mysql服务开启

连接数据库成功后，选择要导入的数据库

提交修改的值

sql编辑器

JDBC操作事务

代码实现

• 开启事务（即在java中关闭数据库的自动提交后，会自动开启事务）
• 一组业务执行完毕，提交事务
• 可以在catch语句中显示地定义回滚语句，其实默认失败也会自动回滚事务

数据库连接池

数据库连接—执行完毕—数据库释放 连接和释放其实很浪费系统系统资源

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

最小连接数：

最大连接数：

等待超时：

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

市面上比较知名的有两个实现类

开源数据源实现（拿来即用）它们能帮你加载驱动和连接数据库

• DBCP
• C3P0
• Druid

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

DBCP需要用到的jar包

• commons-dbcp-1.4
• commons-pool-1.6

还需要dbcp的配置文件，具体代码上网查

例子

#连接设置   这里面的名字，是DBCP数据源中定义好的
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000


#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;] 
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

package lesson05.utils;

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

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import lesson02.utils.JdbcUtils;

public class JdbcUtils_DBCP {

	private static DataSource dataSource = null;
	
	
	static {
		try {
			
			InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcp.properties");
			
			Properties properties = new Properties();
			
			properties.load(in);
			
			
			
			//创建数据源       这里涉及到工厂模式
			dataSource = BasicDataSourceFactory.createDataSource(properties);
			
			
			
			
			
			
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
		
	//获取连接
	public static Connection getConnection() throws SQLException{
		 return dataSource.getConnection();//从数据源中获取连接
	}
	
	//释放连接资源
	public static void release(Connection conn,Statement st,ResultSet rs) {
		
		if(rs!=null) {
			try {
				rs.close();
			}catch(Exception e) {
				e.printStackTrace();
			}
			
		}
		if(st!=null) {
			try {
				st.close();
			}catch(Exception e) {
				e.printStackTrace();
			}
			
		}
		if(conn!=null) {
			try {
				conn.close();
			}catch(Exception e) {
				e.printStackTrace();
			}
			
		}
		
		
	}

}

package lesson05;

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

import lesson02.utils.JdbcUtils;
import lesson05.utils.JdbcUtils_DBCP;

public class DBCP {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		
		try {
			conn = JdbcUtils_DBCP.getConnection();
			
			//与Statement的区别
			//使用问号占位符代替参数
			String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES(?,?,?,?,?);";
			
			st = conn.prepareStatement(sql);//预编译SQL，先写sql，然后不执行
			
			//手动给参数赋值
			st.setInt(1, 6);//id
			st.setString(2, "qinjiang");//name
			st.setString(3, "123456");//password
			st.setString(4, "qwqwqw@qq.com");//email
			//注意点： sql.Date     数据库         java.sql.Date()
			//        util.Date	   Java       new Date().getTime()获得时间戳
			st.setDate(5, new java.sql.Date(new Date().getTime()));//birthday
			
			
			//执行
			int i = st.executeUpdate();
			
			if(i>0) {
				System.out.println("插入成功");
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils_DBCP.release(conn, st, rs);
			
		}
	}
	
	
	
}

C3P0需要导入的jar包

• c3p0-0.9.5.5
• mchange-commons-java-0.2.19

还需要查c3p0-config.xml的配置文件，具体代码上网查**（c3p0-config.xml 文件名字一定要是这个）**

例子

<?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=true
		</property>
		<property name="user">root</property>
		<property name="password">123456</property>
		
		<property name="acquireIncrement">5</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">5</property>
		<property name="maxPoolSize">20</property>
	</default-config>
	
	
	
	<name-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=true</property>
		<property name="user">root</property>
		<property name="password">123456</property>
		
		

		<property name="acquireIncrement">5</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">5</property>
		<property name="maxPoolSize">20</property>
		
	</name-config>
</c3p0-config>

package lesson06.utils;

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

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import lesson02.utils.JdbcUtils;

public class JdbcUtils_C3P0 {
	private static ComboPooledDataSource dataSource = null;
	
	
	static {
		try {
			
	
			
			
			
			//创建数据源       这里涉及到工厂模式
			dataSource = new ComboPooledDataSource("MySQL");//如果不写MySQL则使用默认数据源
			//dataSource = new ComboPooledDataSource("MySQL");如果这样写就代表使用配置文件里面name=MySQL的数据源
			
			
			
			
			
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
		
	//获取连接
	public static Connection getConnection() throws SQLException{
		 return dataSource.getConnection();//从数据源中获取连接
	}
	
	//释放连接资源
	public static void release(Connection conn,Statement st,ResultSet rs) {
		
		if(rs!=null) {
			try {
				rs.close();
			}catch(Exception e) {
				e.printStackTrace();
			}
			
		}
		if(st!=null) {
			try {
				st.close();
			}catch(Exception e) {
				e.printStackTrace();
			}
			
		}
		if(conn!=null) {
			try {
				conn.close();
			}catch(Exception e) {
				e.printStackTrace();
			}
			
		}
		
		
	}
}

package lesson06;

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

import lesson05.utils.JdbcUtils_DBCP;
import lesson06.utils.JdbcUtils_C3P0;

public class C3P0 {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		
		try {
			conn = JdbcUtils_C3P0.getConnection();//原来是自己实现的，现在是用别人的
			
			//与Statement的区别
			//使用问号占位符代替参数
			String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES(?,?,?,?,?);";
			
			st = conn.prepareStatement(sql);//预编译SQL，先写sql，然后不执行
			
			//手动给参数赋值
			st.setInt(1, 6);//id
			st.setString(2, "qinjiang");//name
			st.setString(3, "123456");//password
			st.setString(4, "qwqwqw@qq.com");//email
			//注意点： sql.Date     数据库         java.sql.Date()
			//        util.Date	   Java       new Date().getTime()获得时间戳
			st.setDate(5, new java.sql.Date(new Date().getTime()));//birthday
			
			
			//执行
			int i = st.executeUpdate();
			
			if(i>0) {
				System.out.println("插入成功");
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils_C3P0.release(conn, st, rs);
			
		}
	}
	
	
	
}

结论

• 无论使用什么数据源，本质还是一样的，实现一个接口 DataSource 不会变，方法 getConnection() 就不会变