夯实基础系列三:数据库知识总结

前言

对于后端开发人员来说,经常会和数据打交道,今天总结下数据库相关的知识。包括MySQL,JDBC基础,JDBC进阶,MongoDB,性能优化。以下对这些内容做一些简单的总结,同时我也有完整的思维导图,博客上不方便展示,若有需要,请关注微信公众号永伦的小屋,后台回复 数据库 即可获取。

image
image
image

细节

1. MySQL

1.1 数据库的概述
  • DBMS

    • 解释:关系型数据库管理系统

    • 库管+N个仓库

  • 常见的数据库管理系统

    • MySQL

    • Orcale

    • DB2

    • SQL Server

    • Sybase

1.2 安装MySQL
  • 目录结构

    • mysql.exe:客户端

    • mysqld.exe:服务器

    • my.ini:总配置文件

  • 在windows下开户和关闭mysql服务器

    • net start mysql

    • net stop mysql

  • 登录和退出mysql客户端

    • mysql -u用户名 -p密码 -hIP

    • exit或quit

1.3 SQL
  • 概述

    • 解释:结构化查询语言

    • ISO:国际标准化组织

    • ISO 定义了很多 SQL 标准,例如:SQL99

    • 各个数据库厂商需要遵循这个标签!但各个厂商也有自己的方言

  • SQL的分类

    • DDL:数据定义语言

    • DML:数据操作语言

    • DCL:数据控制语言

    • DQL(不在标准中):数据查询语言

1.4 DDL
  • 数据库的操作

    • create database

    • show databases

    • use mydb1

    • alter database

    • drop database

  • 表的操作

    • create table

    • show tables

    • desc mytable1

    • drop table

    • alter table

      • add

      • modify

      • change

      • drop

      • rename to

1.5 DML
  • 插入记录

  • 修改记录

  • 删除记录

    • delete

    • truncat

1.6 DCL
  • 创建用户

  • 授权

  • 撤消权限

  • 查看权限

  • 删除用户

1.7 DQL
  • 基本查询

    • 查看整张表

      • select * from 表名
    • 列控制

      • 列运算:select sal * 1.5 from emp

      • 列运算:连接字符串:select concat('我的名称是', ename) from emp

      • 处理 NULL 值:select sal + IFNULL(comm, 0) from emp

      • select ename 姓名 from emp

      • 去除完全重复的行:select distinct comm from emp

  • 条件查询

    • 模糊查询

      • 下划线

      • 百分号

  • 排序

    • desc

    • asc

  • 聚合函数

    • count

    • min

    • max

    • sum

    • avg

  • 分组

    • group by

    • having

  • limit

1.8 约束
  • 约束保证数据的完整性和一致性

  • 约束分为表级约束和列级约束

    • 对一个数据列建立的约束,列级约束

    • 对多个数据列建立的约束,表级约束

  • 约束类型包括:

    • NOT NULL

    • PRIMARY KEY

    • UNIQUE KEY

    • DEFAULT

    • FOREIGN KEY

1.9 中文无法插入解决办法
  • dos 下不支持直接使用 utf8,set names gbk,再插入即可。
1.10 连接类型
  • 内连接

    • 显示左表及右表符合连接条件的记录
  • 左外连接

    • 显示左表的全部记录及右表符合连接条件的记录
  • 右外连接

    • 显示右表的全部记录及左表符合连接条件的记录
  • 自身连接

    • 同一个数据表对其自身进行连接

2. JDBC

2.1 JDBC 的原理
  • 是由 JavaEE 提供的连接数据库的规范

  • 需要由各大数据库的厂商提供对 JDBC 的实现类

2.2 四大核心类
  • DriverManager

    • getConnection
  • Connection

    • createStatement

    • prepareStatement(String sql)

  • Statement

    方法: 1.executeUpdate()-->增、删、改  2.executeQuery()-->查 3.addBatch(String sql) 4.executeBatch() 5.execute(String sql)

  • ResultSet

    • getXxx()系统方法

    • 移动光标系统方法

2.3 四大参数
  • driverClassName

  • url

  • username

  • password

2.4 预编译语句集
  • 防SQL攻击

  • 可读性提高了

  • 效率高

2.5 DAO模式
  • 面向接口编程

    • DAO接口

    • DAO实现,可以提供多个实现

    • DAO工厂

    • DAO配置文件:提供实现类名称

2.6 时间类型的转换
  • 领域对象中的日期时间,必须为 util 的Date

  • 在 JDBC 中使用的都是 sql 的 Date

  • insert、update、delete,需要把领域对象中的 util 的 Date 转换成 sql 的 Date

  • 在 select 时,需要把 sql 的 Date 赋给领域对象的 util 的 Date 类型的属性,这不需要处理

2.7 大数据存取
  • 把文件转换成 Blob 类型

  • 把 Blob 类型转换成文件

2.8 批处理
  • 添加批

  • 执行批

3. JDBC_进阶

3.1 事务
  • ACID

    • 原子性

    • 一致性

    • 隔离性

    • 持久性

  • mysql 中开启和关闭事务

    • 开启事务:START TRANSACTION

    • 结束事务

      • 提交事务:COMMIT

      • 回滚事务:ROLLBACK

  • JDBC 中开启和关闭事务

    • 开启事务:connection.setAutoCommit(false)

    • 结束事务

      • connection.commit()

      • connection.rollback()

  • 格式:

    try { con.setAutoCommit(false);//开始事务 ... con.commit();//提交事务 } catch(...) { con.rollback();//回滚事务 }

3.2 三种并发读问题
  • 脏读

    • 读到未提交
  • 不可重复读

    • 两次读取不一致,读取到另一事务修改的记录
  • 幻读

    • 两次读取不一致,读取到另一事务插入的记录
3.3 四种隔离级别
  • 串行化

  • 可重复读

    • 防止了脏读、不可重复读 MySQL
  • 读已提交

    • 防止了脏读 Oracle
  • 读未提交

3.4 连接池
  • 必须实现 javax.sql.DataSource

  • DBCP

  • C3P0

3.5 JNDI
  • 在 tomcat 的 conf/catalina/localhost 下创建 xml 文件

    • 配置<Context>元素

    • 在<Context>中配置<Resource>元素

  • 在代码中使得 Context 类的 lookup 方法来获取资源

    • java:comp/env/ 资源名称
3.6 ThreadLocal
  • 内部有一个 Map

    • key 是当前线程

4. MongoDB

4.1 MongoDB 安装
  • 在 github 上获取源码包 mongo-r2.6.5.zip

  • 解压缩

unzip mongo-r2.6.5.zip

  • 编译源码文件

cd mongo-r2.6.5

scons all -j 12 //12 为 CPU 核数,用来加速编译过程

若未安装 scons,使用命令 sudo apt-get install scons

4.2 编译后的文件
  • mongod:mongodb 的启动文件,用来部署数据库

  • mongo:链接服务器的客户端

  • mongoimport,,mongoexport:用来导入导出数据库

  • mongodump,mongorestore:用来导入导出二进制数据,一般用来数据的备份与恢复。

  • mongooplog 操作日志

  • mongostat:用来查看 mongodb 服务器的各种状态。

4.3 MongoDB 搭建
  • 建立相关目录

    • data (用于存放数据文件)

    • log(用于存放日志文件)

    • conf(用于存放数据库配置文件 mongod.conf)

    • bin(用于存放数据库可执行文件 mongod)

  • 编辑配置文件

    mongod.conf{ port = 12345 /监听端口/ dbpath = data /指定数据文件/ logpath = log/mongod.log /指定日志文件/ fork = ture /linux后台运行标志,window无效/ }

  • 运行程序

mongod -f conf/mongod.conf /运行mongod 加载指定配置文件/

  • 查看运行状态

    data 和 log 目录 tail mongod.log /查看日志/

4.4 MongoDB 连接

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" contenteditable="false" cid="n502" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">操作:
1、拷贝到指定目录 :cp mongo bin
2、执行程序 mongo 127.0.0.1:12345/test(ip端口数据库)~
3、关闭 mongodb use admin -》db.shutdownServer(),kill -15 进程
4、重新启动 mongodb:numactl --interleave=all bin/mongod -f conf/mongod.conf</pre>

4.5 数据库使用

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" contenteditable="false" cid="n504" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">1、连接数据库
/bin/mongo 127.0.0.1:12345
2、显示数据库
show dbs
3、切换数据库
use imooc(库名)
4、切换后删除数据库
db.dropDatabase()
5、创建数据库
use imooc(库名)
6、查看数据库
show dbs
7、创建 imooc_collection 并插入数据
db.imooc_collection.insert({x:1}) -> json 数据
_id全局唯一不重复,可自行定义不重复字段
db.imooc_collection.insert({x:1,_id:1})
插入多条语句(支持js语法)
for(i=3;i<100;i++) db.imooc_collection.insert({x:i})
8、显示表结构
show collections
9、查询数据表中数据
a) 查询所有 db.imooc_collection.find()
b) 条件查询(x:1的数据) db.imooc_collection.find({x:1})
c)高级查询
db.imooc_collection.find().count()/统计条数/
db.imooc_collection.find().skip(3).limit(2).sor({x:1})/过滤掉前三条并限制返回2条且使用x排序</pre>

4.6 数据更新 update

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" contenteditable="false" cid="n506" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">1.db.imooc_collection.update({x:1},{x:999}) #将x为1的数据更新为x=999
update接收两个参数,第一个是过滤条件,这里是x=1的数据,第二个是需要修改的目标值
2.另一种情况,一条数据包含三个字段值,如

db.imooc_collection.insert({x:100,y:100,z:100})
如果直接执行>db.imooc_collection.update({z:100},{y:99}) #将z为100的数据中的y更新为99
这样会将x和z覆盖掉,只剩下y:99
为了避免这种情况,需要:
db.imooc_collection.update({z:100},{set:{y:99}}) #加入set操作符 set操作符为部分更新操作符,使用set后,内容中存在的字段会被更新,而不存在的字段会保持原状 ​ 如果查找的数据不存在则创建: db.user.update({name: 'admin'}, {name: 'admin-updated'}, true) 第三个参数为true即可 ​ mongoDB:update方法有四个参数 第一个参数:查找数据的条件,如{c:1} 表示查找c为1的数据 第二个参数:要更新的数据,如{c:2} 跟新符合条件的数据c为2,默认只更新第一个符合条件的数据。 第三个参数:boolean类型,更新数据不存在时是否创建一条数据,默认为false,设置为true时,自动创建数据。 第四个参数:boolean类型,跟新数据时是否更新所有符合条件的数据,默认为false,只跟新一条符合条件的数据,设置为true时,更新所有符合条件的数据。 ​ 如db.collection.update({c:1},{set{c:2}},false,true)
update(旧数据,{set:新数据},false,true),只能使用部分更新操作符号SET</pre>

4.7 MongoDB 方法API

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" contenteditable="false" cid="n508" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">db.test.save({1:"hello"});保存数据到test集合中
db.test.insert({1:"hello"});插入数据到test集合中(和insert功能相同)
find:一个参数,查找数据的条件,不填则查找所有数据
update:上章说过
remove:删除数据,一个参数(必须,否则会报错):条件,默认删除所有符合条件的数据。
drop:没有参数,删除当前数据表
count:查找数据的条数
sort:排序,一个参数,排序条件,{c:1} 根据c排序, 1为正序,-1为倒序。
show dbs:查询所有数据库
show tables:查询数据表
show collections; 显示当前选择的db中的集合
use dbname:选择数据库,如果数据库不存在,在第一次保存数据的时候会创建数据库。</pre>

4.8 命令小结

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" contenteditable="false" cid="n510" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-size: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; padding: 8px 1em 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; caret-color: rgb(51, 51, 51); color: rgb(51, 51, 51); font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; text-decoration: none; background-position: inherit inherit; background-repeat: inherit inherit;">scons all
mongod -f file
mongo ip:port
show dbs
use db
show collections
db.collection.insert();
db.collection.update()
db.collection.delete()
db.collection.count();
db.collection.find()
db.collection.getIndexes()
db.collection.ensuerIndex()</pre>

5. 性能优化

5.1 MySQL 性能优化
  • 表的设计合理化(符合3NF)

    • 1NF 是对属性的原子性约束,要求属性(列)具有原子性,不可再分解;(只要是关系型数据库都满足1NF)

    • 2NF 是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性;

    • 3NF 是对字段冗余性的约束,它要求字段没有冗余。 没有冗余的数据库设计可以做到。

  • 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引 unique、全文索引]

    • 较频繁的作为查询条件字段应该创建索引

    • 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件

    • 更新非常频繁的字段不适合创建索引

    • 不会出现在 WHERE 子句中的字段不该创建索引

  • 分表技术(水平分割、垂直分割)

  • 读写[写: update/delete/add]分离

  • 存储过程 [模块化编程,可以提高速度]

  • 对 mysql 配置优化 [配置最大并发数 my.ini, 调整缓存大小 ]

  • mysql 服务器硬件升级

  • 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

5.2 SQL语句优化
  • 通过 show status 命令了解各种 SQL 的执行频率。

  • 定位执行效率较低的 SQL 语句-(重点 select)

  • 通过 explain 分析低效率的 SQL

  • 确定问题并采取相应的优化措施

5.3 索引
  • 索引的类型

    • 主键索引,主键自动的为主索引 (类型 Primary)

    • 唯一索引 (UNIQUE)

    • 普通索引 (INDEX)

    • 全文索引 (FULLTEXT) [适用于MyISAM] ——》sphinx + 中文分词 coreseek [sphinx 的中文版 ]

    • 综合使用=>复合索引

  • 可能使用到索引

    • 对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用。

    • 对于使用like的查询,查询如果是 '%aaa' 不会使用到索引, 'aaa%' 会使用到索引。

  • 不使用索引

    • 如果条件中有 or,即使其中有条件带索引也不会使用。

    • 对于多列索引,不是使用的第一部分,则不会使用索引。

    • like 查询是以%开头

    • 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须'')

    • 如果mysql估计使用全表扫描要比使用索引快,则不使用索引。

image

推荐阅读更多精彩内容