夯實基礎系列三：資料庫知識總結

白色蝸牛發表於2020-04-06

前言

對於後端開發人員來說，經常會和資料打交道，今天總結下資料庫相關的知識。包括MySQL，JDBC基礎，JDBC進階，MongoDB，效能優化。以下對這些內容做一些簡單的總結，同時我也有完整的思維導圖，部落格上不方便展示，若有需要，底部公眾號回覆資料庫或者加我微信獲取：919201148。

細節

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 類的 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 連線

操作：
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
複製程式碼

4.5 資料庫使用

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排序*
複製程式碼

4.6 資料更新 update

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
複製程式碼

4.7 MongoDB 方法API

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:選擇資料庫，如果資料庫不存在，在第一次儲存資料的時候會建立資料庫。
複製程式碼

4.8 命令小結

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()
複製程式碼

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估計使用全表掃描要比使用索引快，則不使用索引。

關注公眾號：

回覆【Java經典】，獲取 Java 經典電子書；回覆【Java學習】，獲取整個 Java 體系學習資料；回覆【Java專案】，獲取Java專案的學習資料；回覆【網際網路認知】，獲取網際網路認知類相關學習資料；回覆【技術學習】，獲取更多技術學習資料；回覆【MySQL學習】，獲取MySQL學習資料；回覆【面試】，獲取面試相關資料；

資料庫基礎知識總結（轉）
2010-09-01
資料庫
JavaScript夯實基礎系列（三）：this
2019-03-18
JavaScript
資料庫基礎知識整理與複習總結
2020-10-11
資料庫
資料庫基礎知識
2024-04-03
資料庫
達夢資料庫基礎知識（三）達夢資料庫記憶體結構
2021-10-26
資料庫記憶體
資料結構基礎知識
2014-11-16
資料結構
基礎知識梳理~資料庫
2019-12-14
資料庫
資料庫基礎重要知識
2018-09-14
資料庫
GEO資料庫基礎知識
2018-12-21
資料庫
Java基礎知識總結
2018-11-08
Java
React 基礎知識總結
2018-11-02
React
索引基礎知識總結
2020-11-26
索引
MySql基礎知識總結
2016-09-20
MySql
php基礎知識總結
2017-08-31
PHP
JavaSE基礎知識總結
2016-04-19
Java
基礎夯實：基礎資料結構與演算法(一)
2022-01-17
資料結構演算法
資料庫基礎知識介紹！
2022-04-18
資料庫
資料庫基礎知識講解
2017-11-08
資料庫
NoSQL資料庫的基礎知識
2014-12-29
SQL資料庫
達夢資料庫基礎知識（二）資料庫邏輯結構
2021-10-26
資料庫
JS基礎知識深入總結
2019-02-22
JS
ES 基礎知識點總結
2021-07-21
Java基礎知識點總結
2018-10-20
Java
Java基礎知識總結-1
2020-12-20
Java
Redis 基礎知識點總結
2020-12-19
Redis
SpringIOC基礎知識總結
2020-11-23
Spring
CSS基礎知識點總結
2018-02-18
CSS
cocosCreator基礎知識總結
2016-10-27
【JavaScript的基礎知識總結】
2017-01-08
JavaScript
Jsp基礎知識總結
2008-04-11
JS
TCP/IP 基礎知識總結
2024-04-01
TCP
C# 基礎知識系列-13 常見類庫（三）
2020-04-28
C#
JavaScript夯實基礎系列（四）：原型
2019-03-18
JavaScript原型
JavaScript夯實基礎系列（五）：類
2019-03-18
JavaScript
達夢資料庫基礎知識（一）資料庫物理儲存結構
2021-10-26
資料庫
公共基礎知識-資料結構-樹
2021-03-14
資料結構
資料庫安裝以及基礎知識
2019-02-26
資料庫
夯實Java基礎系列1：Java物件導向三大特性（基礎篇）
2019-10-29
Java物件