Mysql与JDBC

MySQL

JavaEE：企业级Java开发 Web

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

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

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

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

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

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

为什么学数据库

  1. 岗位需求

  2. 现在的世界，大数据时代，得数据者得天下

  3. 被迫需求：存数据

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

什么是数据库

数据库 （DB DataBase）

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

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

数据库分类

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

  • MySQL，oracle，sql server，DB2，SQLlite

  • 通过表和表之间，行和列之间的关系进行数据的存储， 学员信息表，考勤表

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

  • redis，mongodb

  • 非关系型数据库，对象存储，通过对象自身的属性来决定

DBMS（数据库管理系统）

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

  • MySQL，数据库管理系统

连接数据库

mysql -uroot -p123456 --连接数据库
​
show database; -- 显示数据库
create database school; -- 创建数据库
use school; -- 使用某个数据库
show tables; -- 显示表
desc student; -- 显示student表格式

数据库 xxx 语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

数据库列类型

数值

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

  • smallint 较小的数据 2个字节

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

  • int 标准的整数 4个字节 常用的 int

  • bigint 较大的数据 8个字节

  • float 浮点数 四个字节

  • double 浮点数 8个字节（精度问题）

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

字符串

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

  • varchar 可变字符串 0～65535 常用的变量 String

  • tinytext 微型文本 2^8 - 1

  • text 文本串 2^16 - 1 保存大文本

时间日期

java.util.Date

  • date YYYY-MM-DD，日期格式

  • time HH:mm:ss 时间格式

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

  • timestamp 时间戳，1970.1.1到现在的毫秒数，也较为常用

  • year 年份表示

NULL

  • 没有值，未知

  • 不要使用NULL进行运算，结果为NULL

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

Unsigned:

  • 无符号的整数

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

zerofill:

  • 0填充

  • 不足的位数，使用0来填充，int（3），5---005

自增：

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

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

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

非空 Not Null：

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

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

默认：

  • 设置默认的值

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

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

image-20210922185045219

常用命令：

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

数据表的类型

-- 关于数据库引擎
/*
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

修改删除表

修改

-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
-- 修改表的字段（重命名，修改约束！）
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重命名
-- 删除表的字段
ALTER TABLE teacher1 DROP age1

删除

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

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

注意点：

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

  • 注释 -- /**/

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

  • 所有符号用英文

事务

要么都成功，要么都失败

——————

1、sql执行 A给B转账

2、sql执行 B收到A的钱

——————

将一组sql放在一个批次去执行

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

ACID原则

  • 原子性：要么都成功，要么都失败

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

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

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

如果存在干扰，隔离会导致的一些问题：

脏读：

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

不可重复读：

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

虚读（幻读）：

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

（一般是行影响，多了一行）

执行事务

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

模拟转账

-- 转账
-- 创建数据库shop
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
​
-- 使用数据库shop
USE shop
​
-- 创建账户表account
CREATE TABLE `account`(
  `id` INT(3) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL,
  `money` DECIMAL(9,2) NOT NULL
)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

    • 快速定位数据

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

索引原则

  • 索引不是越多越好

  • 不要对进程变动数据加索引

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

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

用户管理

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER xxx IDENTIFIED BY '123456'
​
-- 修改密码 （修改当前用户密码）
SET PASSWORD = PASSWORD('123456')
​
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR xxx = PASSWORD('111111')
​
-- 重命名 RENAME USER 原来名字 TO 新名字
RENAME USER xxx TO xxx2
​
-- 删除用户
DROP USER xxx
​
-- 用户授权ALL PRIVILEGES 全部的权限，库.表
-- ALL PRIVILEGES 除了给别人授权，什么都能干
GRANT ALL PRIVILEGES ON *.* TO xxx
​
-- 查询权限
SHOW GRANTS FOR xxx -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost -- 查询root用户权限
​
-- ROOT用户权限: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
​
-- 撤销权限 REVOKE 哪些权限 在哪个库撤销 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM xxx;
​

MySQL 备份

为什么要备份：

  • 保证重要的数据不丢失

  • 数据转移

MySQL备份的方式

  • 直接拷贝物理文件

  • 在可视化工具上操作

  • 使用命令行导出 mysqldump

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

规范数据库设计

为什么需要设计

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

糟糕的数据库设计：

  • 数据冗余，浪费空间

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

  • 程序的性能差

良好的数据库设计：

  • 节省内存空间

  • 保证数据库的完整性

  • 方便我们开发系统

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

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

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

三大范式

为什么需要数据规范化

  • 信息重复

  • 更新异常

  • 插入异常

    • 无法正常显示信息

  • 删除异常

    • 丢失有效信息

三大范式

第一范式（1NF）

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

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

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

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

规范性和性能的问题

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

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

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

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

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

数据库连接池

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

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

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

最小连接数：10

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

等待超时：100ms

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

开源数据源实现

DBCP

C3P0

Druid：阿里巴巴

 

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

DBCP

需要用到的jar包

commons-dbcp-1.4、commons-pool-1.6

 

 

 

posted @ 2021-09-27 12:40  Neason  阅读(309)  评论(0)    收藏  举报