MySQL

1. 初识MySQL

javaEE：企业级java开发 Web

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

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

数据库（存数据，Txt，Excel，word）

操作系统，数据结构与算法！

离散数学，数字电路，体系结构，编译原理。

1.1 为什么学习数据库

  1. 岗位需求

  2. 大数据时代

  3. 被迫需求：存数据

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

 

1.2 什么是数据库

数据库（DB，DataBase）

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

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

 

1.3 数据库分类

关系型数据库：行、列 （SQL）

  • MySQL,Oracle，Sql Severse，DB2，SQLite

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

非关系型数据库：（key:value) （NoSQL）Not Only

  • Redis，MongDB

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

 

DBMS（数据库管理系统）

  • 数据库的管理软件，科学有效的管理我们的数据。维护获取数据；

  • MySQL，数据库管理系统

 

1.4 MySQL简介

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

前世：瑞典MySQL AB 公司开发

今生：属于 Oracle 旗下产品

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

开源的数据库软件~

体积小、速度快、总体拥有成本低，招人成本低

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

 

安装建议：

  1. 尽量不要使用exe，注册表

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

 

1.5 安装MySQL

  1. 解压

  2. 放到自己的目录下

  3. 配置环境变量

  4. 新建MySQL配置文件 ini

    [mysqld]
    basedir=D:\environment\mysql-5.7.35\
    datadir=D:\environment\mysql-5.7.35\data
    port=3306
    skip-grant-tables

  5. 管理员模式下的CMD，运行所有的命令

  6. 安装mysql服务

    mysqld -install

  7. 初始化数据库文件

    mysqld --initialize-insecure --user=mysql

  8. 启动mysql，进去修改密码

    net start mysql

  9. 进入mysql通过命令行(-p 后不加空格)，修改密码（sql语句后加分号）

    mysql -u root -p

    1. update mysql.user set authentication_string=password('123456') where user='root' and Host ='localhost';

    2. flush privileges;

  10. 注掉ini中的跳过密码（#skip-grant-tables）

  11. 重启mysql。连接测试，如果连接成功就ok了

    1. net stop mysql

    2. net start mysql

    3. mysql -u root -p

 

1.6 安装sqlyog

  1. 安装

  2. 注册

  3. 打开连接数据库

  4. 新建一个数据库school

  5. 新建一张表student

字段： id，name，age，

  1. 查看表

  2. 添加信息

 

1.7 连接数据库

命令行连接

mysql -u root -p -- 连接数据库
​
update mysql.user set authentication_string=password('123456') where user='root' and Host ='localhost'; -- 修改用户密码
flush privileges -- 刷新权限
所有的语句都用;结尾
show databases; -- 查询所有的数据库
use school  -- 切换数据库  use 数据库名
show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有的表的信息
create database westos; -- 创建一个数据库
​
exit； -- 退出连接
​
-- 单行注释（SQL的本来的注释）
/*
sql的多行注释
*/

 

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

DDL 定义

DML 操作管理

DQL 查询

DCL 控制

 

2. 操作数据库

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

关键字不区分大小写

2.1 操作数据库

  1. 创建数据库

    CREATE  DATABASE [IF NOT EXISTS] westos

  2. 删除数据库

    DROP DATABASE [IF EXISTS] westos

  3. 使用数据库

    USE  `school`

  4. 查看数据库

    SHOW DATABASE

 

2.2 数据库的列类型

数值

  • tinyint 十分小的数据 1个字节

  • smallint 较小的数据 2个字节

  • mediumint 中等大小的数据 3个字节

  • int 标准的整数 4个字节

  • bigint 较大的数据 8个字节

  • float 浮点数 4个字节

  • double 浮点数 8个字节

  • decimal 字符串形式的浮点数 金融计算的时候。一般是使用decimal

 

字符串

  • char 字符串固定大小 0~255

  • varchar 可变字符串 0~65535 常用 String

  • tinytext 微型文本 2^8-1

  • text 文本串 2^16-1

 

时间日期

java.util.Date

 

  • date YYY-MM-DD 日期

  • time HH:mm:ss 时间

  • datetime YYY-MM-DD HH:mm:ss 最常用的时间格式

  • timestamp 时间戳 1970.1.1到现在的毫秒数

  • year 年份

 

null

  • 没有值，未知

  • 注意，不要使用null进行运输，结果为null

 

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

Unsigned：

  • 无符号的整数

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

zerofill：

  • 0填充的

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

自增：

  • 通常理解为自增，自动在上一条的基础上 +1（默认）

  • 通常用来设计唯一的主键~index，必须是整数

  • 可以自定义设计主键自增的起始值和步长

非空： NULL not null

  • 假设设置为 not null，如果不给它赋值，机会报错！

  • NULL，如果不填写值，默认值null

默认：

  • 设置默认的值

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

每个表，都必须存在以下五个字段：

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

 

2.4 创建数据库表

CREATE TABLE `student1` (
`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

常用语句：

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

 

2.5 数据表的类型

-- 关于数据库引擎
/*
INNODB  默认使用~
MYISAM  早些年使用
*/
 MYISAMINNODB
事务支持 不支持 支持
数据行锁定 不支持 表锁 支持 行锁
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大，约为2倍

常规的使用操作：

  • MYISAM 节约空间，速度快

  • INNODB 安全性高，事物的处理，多表多用户操作

 

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库

本质还是文件的存储！

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

  • INNODB 在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件

  • MYISAM 对应的文件

    • *.frm 表结构的定义文件

    • *.MYD 数据文件(data)

    • *.MYI 索引文件 (index)

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

CHARSET=utf8

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

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

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

character-set-server=utf8

2.6 修改删除表

修改

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

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

-- 删除表的字段 ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher2 DROP age

 

删除

-- 删除表(如果表存在再删除)
DROP TABLE teacher2 IF EXISTS teacher2

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

 

注意点：

  • `` 字段名使用这个包裹

  • 注释 -- /**/

  • sql关键字大小写不敏感，建议大家写小写

  • 所有的符号全部用英文

 

3. MySQL数据管理

3.1 外键（了解即可）

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


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

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

CREATE TABLE `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

CREATE TABLE `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 那个表(那个字段)

 

以上的操作都是物理外键，数据库级别的外键，不建议使用!(避免数据库过多造成困扰)

最佳实践

  • 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）

  • 我们想使用多张表的数据，想使用外键（程序实现）

3.2 DML语言**

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

DML语言：数据操作语言

  • insert

  • update

  • delet

 

3.3 添加

insert

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

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

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

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

INSERT INTO `student` (`name`,`pwd`,`sex`,`gradeid`) VALUES ('张三','123456','女','12')
INSERT INTO `student` (`name`,`pwd`,`sex`,`gradeid`) VALUES ('李四','146956','男','13')

语法：insert INTO 表名([字段名1，字段2，字段3]) VALUES ('值1'),('值2')

注意事项：

  1. 字段和字段之间使用英文逗号隔开

  2. 字段是可以省略的，但是后面的值必须一一对应

  3. 可以同时插入多条数据 values (),()

3.4 修改

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

-- 修改学员的名字,带了条件
UPDATE `student` SET `name`='小三' WHERE id=1;
-- 不指定条件的情况下，会改变所有表！

UPDATE `student` SET `name`='小三'

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

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

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

操作符会返回布尔值

操作符含义范围结果
= 等于 5=6 false
<>或!= 不等于 5<>6 true
> 大于 5>6 false
< 小于 5<6 true
>= 大于等于 5>=6 false
<= 小于等于 5<=6 true
BETWEEN ... and ... 在某个范围内 [2,5]  
AND 我和你 && 5>1 and 1>2 false
OR 我或你 || 5>1 or 1>2 true 
-- 通过多个条件定位数据,无上限
UPDATE `student` SET `name`='ddd' WHERE `name`='xx' AND sex='男'

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

注意：

  • colnum_name 是数据库的列，尽量带上``

  • 条件，筛选的条件，如果没有指定，则会修改所有的列

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

  • 多个设置的属性之间，使用英文逗号隔开

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

 

3.5 删除

delete 命令

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

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

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

 

TRUNCATE 命令

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

-- 清空 studeng 表
TRUNCATE `student`

 

delete 和 truncate 区别

  • 相同点：都能删除数据，都不会删除表结构

  • 不同：

    • TRUNCATE 重新设置 自增列 计数器会归零

    • TRUNCATE 不会影响事务

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

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

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

TRUNCATE TABLE `test` -- 自增会归零

DELETE 删除的问题，重启数据库，现象

  • innoDB 自增列会从1开始 （存在内存当中的，断电即失）

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

 

4. DQL查询数据***

4.1 DQL

(Data Query LANGUAGE :数据查询语言)

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

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

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

  • 使用频率最高的语句

 

SELECT 语法

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

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

 

4.2 指定查询字段

-- 查询所有的学生   SELECT 字段 FROM 表
SELECT * FROM student

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

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

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

语法：SELECT 字段... FROM 表

有的时候，列名字不是那么的见名知意，我们起别名 AS 字段名 AS 别名 表明 AS 别名

 

去重 distinct

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

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

 

数据库的列 （表达式）

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

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

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

select 表达式 from 表

 

4.3 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

-- and   &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE StudentResult>=95 && StudentResult<=100

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

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

-- !=  not
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT studentno =1000

 

模糊查询 ： 比较运算符

运算符语法描述
is NULL a is null 如果操作符为null，结果为真
is NOT NULL a is not null 如果操作符为not null，结果为真
between a between b and c 若a在b与c之间，结果为真
like a like b SQL匹配，如果a匹配到b，结果为真
in a in (a1,a2,a3,...) 假设a在a1，或者a2...其中的某一个值中，结果为真 
-- =============== 模糊查询  ==============
-- 查询姓刘的同学
-- like 结合  %（代表0到任意个字符）   _（一个字符）
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘%'

-- 查询姓刘的同学,名字后只有一个字的
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘_'

-- 查询姓刘的同学,名字后只有两个字的
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘__'

-- 查询名字中间有家字的同学  %家%
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%家%'

-- ======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 =============
-- 查询地址为空的学生  
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IS NULL OR address =''

-- 查询有出生日期的同学，代表不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL

 

4.4 联表查询

JOIN 对比

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


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

SELECT s.studentNo,studentName,subjectNo,studentResult
FROM `student` AS s
INNER JOIN `result` AS r
WHERE s.studentNo = r.studentNo

-- right join
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM `student` s
RIGHT JOIN result r
ON s.studentNo = r.studentNo


-- left join
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM `student` s
LEFT JOIN result r
ON s.studentNo = r.studentNo
操作描述
inner join 如果表中至少有一个匹配，就会返回行
left join 即使右表中没有匹配，会从左表中返回所有的值
right join 即使左表中没有匹配，会从右表中返回所有的值 
-- ===================  联表查询  ===========
--查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM `student`
SELECT * FROM `result`


/* 思路：
1.分析需求，分析查询的字段来自那些表（连接查询）
2.确定使用哪种连接查询？
确定交叉点（这两个表中那个数据式相同的）
判断的条件：学生表中的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` s
RIGHT JOIN result r
ON s.studentNo = r.studentNo


-- left join
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM `student` s
LEFT JOIN result r
ON s.studentNo = r.studentNo

-- 查询缺考的同学
SELECT s.studentNo,studentName,subjectNo,studentResult
FROM `student` s
LEFT JOIN result r
ON s.studentNo = r.studentNo
WHERE studentresult = NULL

-- 思考题（查询了参加考试的同学信息：学号，学生姓名，科目名称，分数）

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

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

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

-- from a  left join b
-- from a  rjght join b

自连接

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

父类

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

子类

pidcategroyidcategoryName
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`

 

4.5 分页和排序

 

排序

SELECT s.`StudentNo`,`SubjectName`,`StudentName`,`StudentResult`
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE subjectname= '数据库结构-1'
ORDER BY studentresult DESC

 

分页

-- 分页
-- 缓解数据库压力，给人好的体验，瀑布流

-- 分页，每页只显示五条数据
-- 语法  limt 起始值  ，页面的大小
-- LIMIT 0,5  1~5
-- LIMIT 1,5  2~6
-- LIMIT 6,5  7~11
SELECT s.`StudentNo`,`SubjectName`,`StudentName`,`StudentResult`
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE subjectname= '数据库结构-1'
ORDER BY studentresult DESC
LIMIT 6,5

-- 第一页  limit 0，5
-- 第二页  limit 5，5
-- 第三页  limit 10，5
-- 第n页   limit (n-1)*pageSize,pageSize
-- [pageSize :页面大小，(n-1)*pageSize 起始值]
-- [n： 当前页 ]
-- [总数/页面大小 = 总页数]

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

 

4.6 子查询

where (值是固定的,这个值是计算出来的)

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

where(select * from)

-- ============ where =====================

-- 1.查询 数据库结构-1 的所有考试结果（学号，科目名称，成绩），降序
SELECT r.`StudentNo`,`SubjectName`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE `SubjectName`= '数据库结构-1'
ORDER BY `StudentResult` DESC

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

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

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


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

-- 改造
SELECT `StudentNo`,`StudentName` FROM student WHERE studentNo IN(
    SELECT studentNo FROM result WHERE `StudentResult`>=80 AND `SubjectNo`=(
         SELECT subjectNo FROM `subject` WHERE `SubjectName`= '高等数学-2'
    )
)
-- 查询 C语言-1 前五名同学的成绩的信息（学号，姓名，分数）
SELECT s.`StudentNo`,`StudentName`,`StudentResult` 
FROM student  s 
INNER JOIN result r
ON s.studentno=r.studentno
WHERE `SubjectNo`=(
      SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = 'C语言-1'
  )
ORDER BY `StudentResult` DESC
LIMIT 0,5

 

4.7 分组和过滤

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

 

5. MySQL函数

5.1 常用函数

-- ===================== 常用函数  ============
-- 数学运算
SELECT ABS(-8)  -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 随机数 0~1
SELECT SIGN(53) -- 判断一个数的符号

-- 字符串函数
SELECT CHAR_LENGTH('dsadsad') -- 字符串长度
SELECT CONCAT('1','2','3') -- 拼接字符串
SELECT INSERT('helloworld',1,2,'pwd') -- 插入，替换
SELECT LOWER('DASDASD')  -- 小写
SELECT UPPER('dsadasd')  -- 大写
SELECT INSTR('helloworld','o') -- 返回第一次出现的位置
SELECT REPLACE('helloworld','w','d') -- 替换
SELECT SUBSTR('helloworld',4,6) -- 截取  
SELECT REVERSE('helloworld') -- 反转

-- 查询姓周的同学，名字周改为邹
SELECT REPLACE(studentName,'周','邹') FROM student 
WHERE studentName LIKE '周%'

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

SELECT YEAR(NOW())

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

 

5.2 聚合函数(常用)

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

 

-- ========================= 聚合函数 ===============
-- 都能够统计表中的数据

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

 

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

什么是MD5

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

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

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

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

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


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

-- 插入的时候加密
INSERT INTO testmd5 VALUES('5','小七',MD5('dsds4545'))

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

6. 事务

6.1 什么是事务

要么都成功，要么都失败

1.SQL执行 A 给 B 转账 A1000 ----> B200

2.SQL执行 B 收到 A 的钱 A800 B400

 

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

 

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

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

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

持久性（Durability） -- 事务提交

事务一旦提交则不可逆，被持久化到数据库中！

隔离性（Isolation）

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

 

隔离所导致的问题

脏读：

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

不可重复读：

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

虚读（幻读）

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

 

执行事务

--   ==========事务==================================
 -- mysql 是默认开启事务自动提交的
 SET autocommit = 0 /*关闭*/
 SET autocommit = 1 /*开启(默认的)*/
 
 -- 手动处理事务
 SET autocommit = 0 -- 关闭自动提交
 
 -- 事务开启
 START TRANSACTION  -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
 
 INSERT xx
 
 -- 提交:持久化（成功！）
 COMMIT
 -- 回滚：回到原来的样子（失败！）
 ROLLBACK
 
 -- 事务结束
 SET autocommit = 1 -- 开启自动提交
 
 -- 了解
 SAVEPOINT -- 保存点名称  设置一个事务的保存点
 ROLLBACK TO SAVEPOINT -- 回滚到保存点
 RELEASE SAVEPOINT -- 撤销保存点

 

模拟场景

-- 转账
CREATE DATABASE shop CHARACTER SET 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'
UPDATE `account` SET money=money+500 WHERE `name`='B'

COMMIT;
ROLLBACK;

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

7. 索引

MySQL官方对索引的定义为：索引(index)是帮助MySQL高效获取数据的数据结构。

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

7.1 索引的分类

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

  • 主键索引 PRIMARY KEY

    • 唯一的标识，主键不可以重复，只能有一个列作为主键

  • 唯一索引 UNIQUE KEY

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

  • 常规索引 KEY/INDEX

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

  • 全文索引 Fulltext

    • 在特定的数据库引擎上才有，MyISAM

    • 快速定位索引

 

-- 索引的使用
-- 创建表的时候给字段增加索引
-- 创建完毕后增加索引

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

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

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

 

7.2 测试索引

CREATE TABLE `app_user`(
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
  `email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
  `phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
  `gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0：男；1：女）',
  `password` VARCHAR(100) NOT NULL COMMENT '密码',
  `age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
 
 
 -- 插入一百万条数据
DELIMITER $$  -- 写函数之前必写


CREATE FUNCTION mock_data()
RETURNS INT 
BEGIN
    DECLARE num INT DEFAULT 1000000;
    DECLARE i INT DEFAULT 0;
    
    WHILE i<num DO
	-- 插入语句
	INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
        VALUES(CONCAT('用户',i),'12451525@163.com',CONCAT('18',FLOOR(RAND()*(99999999-100000000)+1000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
	SET i = i+1;
        END WHILE;
        RETURN i;
END; 
SELECT mock_data()

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

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

 

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

 

7.3 索引原则

  • 索引不是越多越好

  • 不要对经常变动的数据加索引

  • 小数据量的表不需要加索引

  • 索引一般加在常用来查询的字段上

 

索引的数据结构

Hash类型的索引

Btree：innoDB的默认数据结构~

 

8. 权限管理和备份

8.1 用户管理

SQL yog可视化管理

 

SQL命令操作

用户表：mysql.user

本质：对这种表进行增删改查

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

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

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


-- 重命名   RENAME USER 原名 TO 新名字
RENAME USER yy44 TO yy4396

-- 授予全部权限  ALL PRIVILEGES 库.表
-- 除了给别人授权 什么都能干
GRANT ALL PRIVILEGES ON *.* TO yy4396

-- 查看权限
SHOW GRANTS FOR yy4396
SHOW GRANTS FOR root@localhost

-- root权限 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION


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

-- 删除用户
DROP USER yy4396

 

8.2 MySQL备份

为什么要备份：

  • 保证重要的数据不丢失

  • 数据转移

MySQL数据库备份的方式

  • 直接拷贝物理文件

  • 在sqlyog这种可视化工具中手动导出

    • 在想要导出的表或者库中，右键选择备份或导出

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

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

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

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

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

#mysqlp -u 用户名 -p 密码 数据库 < 物理磁盘位置/文件名

防止数据丢失

 

9 .规范数据库设计

9.1 为什么需要设计

当数据比较复杂的时候

糟糕的数据库设计：

  • 数据冗余，浪费空间

  • 数据插入和删除都会麻烦，异常[屏蔽使用物理外键]

  • 程序的性能差

良好的数据库设计：

  • 节省内存空间

  • 保证数据的完整性

  • 方便我们开发系统

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

  • 分析需求：分析业务和需要处理的数据库的需求

  • 概要设计：设计关系图 E-R图

 

设计数据库的步骤

  • 收集信息，分析需求

    • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）

    • 分类表（文章分类，谁创建的）

    • 文章表（文章的信息）

    • 评论表

    • 友链表（友链信息）

    • 自定义表（系统信息，某个关键的字，或者一些主字段） key: value

  • 标识实体（把需求落地到每个字段）

  • 标识实体 之间的关系

    • user->blog 写博客

    • user->category 创建分类

    • user->user 关注

    • links 友链

    • user-user-blog 评论

 

9.2 三大范式

为什么需要数据规范化

  • 信息重复

  • 更新异常

  • 插入异常

    • 无法正常显示信息

  • 删除异常

    • 丢失有效的信息

 

三大范式

第一范式（1NF）

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

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

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

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

 

规范数据库的设计

 

规范性和性能

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

  • 考虑商业化的需求和目标（成本，用户体验） 数据库的性能更加重要

  • 在规范性能的问题的时候，需要适当的考虑一下 规范性！

  • 故意给某些表增加一些冗余的字段 （从多表查询变为单表查询）

  • 故意增加一些计算列 （从大数据量降低为小数据量的查询：索引）

10. JDBC（重点）

10.1 数据库驱动

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

 

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

 

10.2 JDBC

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

这些规范的实现由具体的厂商取做~

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

 

java.sql

javax.sql

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

 

10.3 第一个JDBC程序

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy

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

INSERT INTO users (id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhangsan','123456','zs@163.com','1980-12-04'),
(2,'lisi','123456','ls@163.com','1548-1-12'),
(3,'wangwu','123456','ww@163.com','2020-1-3');

 

  1. 创建一个普通项目

  2. 导入数据库驱动

  3. 编写测试代码

package com.yyy.lesson01;

import java.sql.*;

/**
 * @Auther: Y43
 * @Date: 2021/8/22 15:26
 * @Description:
 */
//我的第一个jdbc程序
public class JdbcFirstDemo1 {
    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=FALSE";
        String username= "root";
        String password= "123123";

        //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("id="+resultSet.getObject("name"));
            System.out.println("id="+resultSet.getObject("password"));
            System.out.println("id="+resultSet.getObject("email"));
            System.out.println("id="+resultSet.getObject("birthday"));
        }
        //6.释放连接

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

步骤总结：

  1. 加载驱动

  2. 连接数据库 DriverManager

  3. 获得执行SQL的对象 Statement

  4. 获得返回的结果集

  5. 释放连接

 

DriverManager

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

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

 

URL

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

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

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

 

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

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

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

 

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

获得指定的数据类型

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

 

遍历,指针

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

 

释放资源

resultSet.close();
statement.close();
connection.close();//耗资源，用完关闭

 

10.4 statement对象

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

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

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

 

CRUD操作-create

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

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

 

CRUD操作-delete

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

 

CRUD操作-update

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

 

CRUD操作-read

Statement statement = connection.createStatement();
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
    //根据获取列的数据类型，分别调用resultSet的相应方法映射到java对象中
}

 

代码实现

1.提取工具类

package com.yyy.lesson01;

import java.sql.*;

/**
 * @Auther: Y43
 * @Date: 2021/8/22 15:26
 * @Description:
 */
//我的第一个jdbc程序
public class JdbcFirstDemo1 {
    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=FALSE";
        String username= "root";
        String password= "123123";

        //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("id="+resultSet.getObject("name"));
            System.out.println("id="+resultSet.getObject("password"));
            System.out.println("id="+resultSet.getObject("email"));
            System.out.println("id="+resultSet.getObject("birthday"));
        }
        //6.释放连接

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

    }
}

 

db.properties

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

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

package com.yyy.lesson02.utlis;

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

/**
 * @Auther: Y43
 * @Date: 2021/8/22 16:28
 * @Description:
 */
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('7','laoliu','123456','ll@163.com','2020-3-5')";

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

        }
    }
}

 

package com.yyy.lesson02.utlis;

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

/**
 * @Auther: Y43
 * @Date: 2021/8/22 16:53
 * @Description:
 */
public class TestDelete {
    public static void main(String[] args) {

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

        try {
            conn = JdbcUtils.getConnection();//获取数据库连接
            st = conn.createStatement();//获得sql的执行对象
            String sql = "DELETE FROM users WHERE id=7";

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

        }
    }
}

 

package com.yyy.lesson02.utlis;

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

/**
 * @Auther: Y43
 * @Date: 2021/8/22 16:55
 * @Description:
 */
public class TestUpdate {
    public static void main(String[] args) {

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

        try {
            conn = JdbcUtils.getConnection();//获取数据库连接
            st = conn.createStatement();//获得sql的执行对象
            String sql = "UPDATE users SET `name`='llll',`email`='121212@123.com' WHERE id=1";

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

        }
    }
}

 

3.查询

package com.yyy.lesson02.utlis;

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

/**
 * @Auther: Y43
 * @Date: 2021/8/22 17:01
 * @Description:
 */
public class TestSelect {
    public static void main(String[] args) {

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

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

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

            rs=st.executeQuery(sql);//查询完毕会返回一个结果集

            if (rs.next()){
                System.out.println(rs.getString("name"));
            }

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

 

SQL 注入的问题

sql存在漏洞，会被攻击倒置数据泄露 SQL会被拼接

package com.yyy.lesson02.utlis;

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

/**
 * @Auther: Y43
 * @Date: 2021/8/22 17:08
 * @Description:
 */
public class SQL注入 {
    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;
        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 throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

 

10.5 PrepareStatement对象

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

1.新增

package com.yyy.lesson03;

import com.yyy.lesson02.utlis.JdbcUtils;

import java.sql.*;
import java.util.Date;

/**
 * @Auther: Y43
 * @Date: 2021/8/23 12:33
 * @Description:
 */
public class TestInsert {
    public static void main(String[] args) {

        Connection conn=null;
        PreparedStatement st=null;

        try {
            conn=JdbcUtils.getConnection();

            //区别
            //使用?占位符代替参数

            String sql="INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES(?,?,?,?,?)";

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


            //手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"yyy");
            st.setString(3,"123123");
            st.setString(4,"124151@163.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.删除

package com.yyy.lesson03;

import com.yyy.lesson02.utlis.JdbcUtils;

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

/**
 * @Auther: Y43
 * @Date: 2021/8/23 12:47
 * @Description:
 */
public class TestDelete {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement st=null;

        try {
            conn=JdbcUtils.getConnection();

            //区别
            //使用?占位符代替参数

            String sql="delete from users where id=?";

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


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

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



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

3.更新

package com.yyy.lesson03;

import com.yyy.lesson02.utlis.JdbcUtils;

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

/**
 * @Auther: Y43
 * @Date: 2021/8/23 12:47
 * @Description:
 */
public class TestUpdate {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement st=null;

        try {
            conn=JdbcUtils.getConnection();

            //区别
            //使用?占位符代替参数

            String sql="update users set `name` =?  where id=? ;";

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


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


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



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

4.查询

package com.yyy.lesson03;

import com.yyy.lesson02.utlis.JdbcUtils;

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

/**
 * @Auther: Y43
 * @Date: 2021/8/23 12:51
 * @Description:
 */
public class TestSelect {
    public static void main(String[] args) {

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

        try {
            conn=JdbcUtils.getConnection();

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

            st=conn.prepareStatement(sql);

            st.setInt(1,2);

            //执行
            rs=st.executeQuery();

            if (rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("email"));
            }


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

5.防止SQL注入

package com.yyy.lesson03;

import com.yyy.lesson02.utlis.JdbcUtils;

import java.sql.*;

/**
 * @Auther: Y43
 * @Date: 2021/8/22 17:08
 * @Description:
 */
public class SQL注入 {
    public static void main(String[] args) {
        //login("lisi","123456");
        login("' 'or 1=1","123456");
    }

    //登录
    public static void login(String username,String password){
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;

        try {
            conn= JdbcUtils.getConnection();
            // PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符
            // 假设其中存在转义字符，比如说'会被直接转义

            String sql="select * from users where `name`=? and `password`=?";

            st=conn.prepareStatement(sql);

            st.setString(1,username);
            st.setString(2,password);


            rs=st.executeQuery();

            while (rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
            }

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

10.6 事务

 

要么都成功，要么都失败

ACID原则

原子性：要么全部成功，要么全部失败

一致性：总数不变

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

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

隔离性的问题：

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

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

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

代码实现

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

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

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

package com.yyy.lesson04;

import com.yyy.lesson02.utlis.JdbcUtils;

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

/**
 * @Auther: Y43
 * @Date: 2021/8/23 13:36
 * @Description:
 */
public class TestTransaction2 {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;

        try {
            conn=JdbcUtils.getConnection();
            //关闭数据库的自动提交,自动会开启事务
            conn.setAutoCommit(false);

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

          //正常
          //报错  int x=1 / 0;

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

            //业务完毕提交事务
            conn.commit();
            System.out.println("操作成功");

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

10.7 数据库连接池

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

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

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

最小连接数：10(常用连接)

最大连接数：100 （业务最高承载上线）

等待超时：100ms

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

开源数据源实现

DBCP

C3P0

Druid:阿里巴巴

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

DBCP

需要用到的jar包

commons-dbcp-1.4

commons-pool-1.6

配置文件dbcp.properties

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=FALSE
username=root
password=123123

#初始化连接
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 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

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

工具

package com.yyy.lesson05.utlis;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @Auther: Y43
 * @Date: 2021/8/22 16:17
 * @Description:
 */
public class JdbcUtils_DBCP {

    private static DataSource dataSource=null;

    static {
        try{
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.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 (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

测试

package com.yyy.lesson05.utlis;

import com.yyy.lesson02.utlis.JdbcUtils;

import java.sql.*;

/**
 * @Auther: Y43
 * @Date: 2021/8/23 14:18
 * @Description:
 */
public class TestDBCP {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement st=null;

        try {
            conn=JdbcUtils_DBCP.getConnection();

            //区别
            //使用?占位符代替参数

            String sql="INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES(?,?,?,?,?)";

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


            //手动给参数赋值
            st.setInt(1,5);
            st.setString(2,"yyy");
            st.setString(3,"123123");
            st.setString(4,"124151@163.com");
            //注意点  sql.Date    数据库   java.sql.Date
            //       util.Date   java  new Date().getTime() 获得时间戳
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));

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

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_DBCP.release(conn,st,null);
        }
    }
}

 

C3P0

需要用到的jar包

c3p0-0.9.5.5.jar

mchange-commons-java-0.2.19.jar

配置文件c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
    c3p0的缺省（默认）配置
    如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省（默认）
    -->
    <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&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">123123</property>
        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>


    <!--
    c3p0的命名配置
    如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");这样写就表示使用的是name是MySQL
    -->
    <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=false&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">123123</property>
        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </name-config>
</c3p0-config>

工具

package com.yyy.lesson05.utlis;

import com.mchange.v2.c3p0.ComboPooledDataSource;
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;

/**
 * @Auther: Y43
 * @Date: 2021/8/22 16:17
 * @Description:
 */
public class JdbcUtils_c3p0 {
    private static DataSource dataSource=null;
    //private static ComboPooledDataSource dataSource=null;

    static {
        try{

            //代码版配置
//            dataSource=new ComboPooledDataSource();
//            dataSource.setDriverClass();
//            dataSource.setUser();
//            dataSource.setPassword();
//            dataSource.setJdbcUrl();
//
//            dataSource.setMaxPoolSize();
//            dataSource.setMinPoolSize();

            //创建数据源  工厂模式  创建对象
             dataSource = new ComboPooledDataSource("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 (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

测试

package com.yyy.lesson05.utlis;

import com.yyy.lesson05.utlis.JdbcUtils_c3p0;

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

/**
 * @Auther: Y43
 * @Date: 2021/8/23 14:18
 * @Description:
 */
public class Testc3p0 {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement st=null;

        try {
            conn= JdbcUtils_c3p0.getConnection();

            //区别
            //使用?占位符代替参数

            String sql="INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES(?,?,?,?,?)";

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


            //手动给参数赋值
            st.setInt(1,7);
            st.setString(2,"yyy");
            st.setString(3,"123123");
            st.setString(4,"124151@163.com");
            //注意点  sql.Date    数据库   java.sql.Date
            //       util.Date   java  new Date().getTime() 获得时间戳
            st.setDate(5,new Date(new java.util.Date().getTime()));

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

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_c3p0.release(conn,st,null);
        }
    }
}

 

总结

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

posted @ 2021-08-23 15:53  y4396  阅读(80)  评论(0)    收藏  举报