第一部分
我們繼續創建簡單的證券交易所模擬器。這是我們要做的:
- 讓我們建立一個資料庫組織圖。
- 我們將描述它儲存的內容、方式和位置。
- 讓我們看看數據是如何相互關聯的。
- 讓我們以 SQL 表建立指令CREATE TABLE(SQL 語言的資料定義語言 ( DDL ))為例開始學習 SQL 的基礎知識 。
- 讓我們繼續寫Java程式。我們使用 JDBC 和三層架構以程式設計方式建立資料庫的 java.sql 來實現 DBMS 的主要功能。
這兩部分內容更加冗長,因為我們需要從內部熟悉 SQL 的基礎知識和 DBMS 的組織,並與 Java 進行類比。為了不讓程式碼清單讓您感到厭煩,最後有該程式對應的提交 github 儲存庫的連結。
資料庫管理系統設計
應用說明
您已經聽說組織資料儲存是程式設計的一個組成部分。讓我提醒您,我們應用程式的目的是最簡單的交換模擬:
- 股票的價值在交易日內可以依照規定的規則而改變;
- 有初始資金的交易者;
- 交易者可以根據他們的演算法買賣股票。
交易
所以固定時間段(在我們的例子中為 1 分鐘)運行。在價格變動期間,股票價格可能會發生變化,然後交易者可以買入或賣出股票。
交換仿真資料結構
我們稱之為單獨的交換實體模型。為了避免舍入錯誤,我們將透過一個類別來處理財務金額
BigDecimal
(詳細資訊可以在文章末尾的連結中找到)。讓我們更詳細地描述每個模型的結構:
促銷:
屬性 |
類型 |
描述 |
name |
斯丁 |
姓名 |
changeProbability |
整數 |
每個價格變動的利率變化機率(百分比) |
startPrice |
大十進位 |
初始投資成本 |
delta |
整數 |
當前值可以更改的最大百分比量 |
分享價格:
屬性 |
類型 |
描述 |
operDate |
本地日期時間 |
設定費率的時間(tick) |
share |
晉升 |
促銷連結 |
rate |
大十進位 |
分享價格 |
商人:
屬性 |
類型 |
描述 |
name |
細繩 |
設定費率的時間(tick) |
sfreqTick |
整數 |
交易頻率。由交易者執行操作之前的時間段(以跳動點為單位)指定 |
cash |
大十進位 |
除股票以外的金額 |
traidingMethod |
整數 |
交易者使用的演算法。讓我們將其設定為常數,演算法的實作將在Java程式碼中(在下面的部分中) |
changeProbability |
整數 |
完成操作的機率,百分比 |
about |
細繩 |
每個價格變動的利率變化機率(以百分比表示) |
交易者行動:
屬性 |
類型 |
描述 |
operation |
整數 |
交易類型(買入或賣出) |
traider |
商人 |
交易者連結 |
shareRate |
分享價格 |
與股票價格的連結(分別是股票本身、其利率和發行時間) |
amount |
長的 |
本次交易涉及的股份數量 |
為了確保每個模型的唯一性,我們將新增一個
longid
類型的屬性。該屬性在模型實例中是
唯一的,並且可以唯一地識別它。引用其他模型(交易者、股票、股票價格)的屬性可以使用此屬性來唯一標識對應的模型。我立刻想到我們可以用來 儲存這樣的數據,對應的模型在哪裡。但是,請嘗試在以下條件下在程式碼中實現此功能:
id
Map<Long, Object>
Object
- 資料大小明顯超過可用 RAM 的大小;
- 預計可以從十幾個不同的地方存取資料;
- 需要同時修改和讀取資料的能力;
- 有必要確保資料的形成和完整性規則;
……您將面臨需要適當資格和時間來實施的任務。沒有必要「重新發明輪子」。很多事情已經為我們想好了並寫好了。所以我們將使用多年來已經測試過的東西。
在 Java 中儲存數據
讓我們考慮一下行動。在 Java 中,我們為此模型創建了一個特定的類,
Share
其中包含字段
name
,
changeProbability
,
startPrice
,
delta
。許多共享儲存為
Map<Long, Share>
,其中密鑰是每個共享的唯一識別碼。
public class Share {
private String name;
private BigDecimal startPrice;
private int changeProbability;
private int delta;
}
Map<Long, Share> shares = new HashMap<>();
shares.put(1L, new Share("ibm", BigDecimal.valueOf(20.0), 15, 10));
shares.put(2L, new Share("apple", BigDecimal.valueOf(14.0), 25, 15));
shares.put(3L, new Share("google", BigDecimal.valueOf(12.0), 20, 8));
...
shares.put(50L, new Share("microsoft", BigDecimal.valueOf(17.5), 10,4 ));
若要透過 ID 存取所需的促銷活動,請使用方法
shares.get(id)
。對於按名稱或價格找到股票的任務,我們將循環遍歷所有記錄以查找我們需要的股票,依此類推。但我們會採取相反的方式,將值儲存在 DBMS 中。
DBMS 中的資料存儲
讓我們為 DBMS 制定一組初始的資料儲存規則:
- DBMS 中的資料被組織成表 ( TABLE ),表是一組記錄。
- 所有記錄都具有相同的欄位集。它們是在創建表時設定的。
- 此欄位可以設定為預設值 ( DEFAULT )。
- 對於表,您可以設定約束 ( CONSTRAINT ) 來描述其資料的要求,以確保其完整性。這可以在表格建立階段( CREATE TABLE )完成或稍後新增(ALTER TABLE ... ADD CONSTRAINT)。
- 最常見的約束:
- 主鍵是 PRIMARY (在我們的例子中是 Id )。
- 唯一值欄位UNIQUE(車輛表的VIN)。
- 檢查CHECK欄位(百分比值不能大於100)。欄位的私有限制之一是NOT NULL或NULL,它禁止/允許在表格欄位中儲存 NULL。
- 連結到第三方表FOREIGN KEY(連結到股價表中的股票)。
- 索引INDEX(對欄位建立索引以加快在其中搜尋值的速度)。
- 如果記錄的欄位值與限制(CONSTRAINT)相矛盾,則不會發生記錄的修改( INSERT、UPDATE )。
- 每個表可以有一個(或多個)關鍵字段,可用於唯一標識一筆記錄。這樣的字段(或多個字段,如果它們形成組合鍵)形成表的主鍵 - PRIMARY KEY。
- 主鍵保證了表中記錄的唯一性;在主鍵上建立索引,可以根據鍵值快速存取整個記錄。
- 擁有主鍵可以更輕鬆地在表之間建立連結。接下來,我們將使用人工主鍵:對於第一筆記錄
id = 1
,後續的每筆記錄都會插入表中,並且 id 值加一。此鍵通常稱為AutoIncrement或AutoIdentity。
實際上是一個股票表:
在這種情況下是否可以使用股票名稱作為鍵?總的來說 - 是的,但有可能某些公司發行不同的股票並僅以自己的名稱命名。在這種情況下,將不再有唯一性。在實務中,經常使用人工主鍵。同意,在包含人員記錄的表中使用全名作為唯一鍵並不能確保唯一性。以及使用全名和出生日期的組合。
DBMS 中的資料型別
與其他程式語言一樣,SQL 也具有資料類型。以下是最常見的 SQL 資料類型:
整數類型
SQL類型 |
SQL 同義詞 |
Java中的匹配 |
描述 |
INT |
INT4,整數 |
java.lang.Integer |
4 位元組整數,-2147483648 … 2147483647 |
布林值 |
布爾、位 |
java.lang.布林值 |
真假 |
天音 |
|
java.lang.Byte |
1 位元組整數,-128 … 127 |
小智 |
INT2 |
java.lang.Short |
2 位元組整數,-32768 … 32767 |
BIGINT |
INT8 |
java.lang.Long |
8 位元組整數,-9223372036854775808 … 9223372036854775807 |
自動遞增 |
增量 |
java.lang.Long |
表獨有的增量計數器。如果插入一個新值,它就會加一,產生的值永遠不會重複。 |
真實的
SQL類型 |
SQL 同義詞 |
Java中的匹配 |
描述 |
小數(N,M) |
十二月,數字 |
java.math.BigDecimal |
固定精度小數(N 個整數位和 M 個小數位)。主要設計用於處理財務數據。 |
雙倍的 |
浮點8 |
java.lang.Double |
雙精度實數(8 位元組)。 |
真實的 |
浮點數4 |
java.lang.Real |
單精度實數(4 位元組)。 |
細繩
SQL類型 |
SQL 同義詞 |
Java中的匹配 |
描述 |
VARCHAR(N) |
NVARCHAR |
java.lang.String |
長度為 N 的 UNICODE 字串。長度限制為 2147483647 將字串的全部內容載入到記憶體中。 |
日期和時間
SQL類型 |
SQL 同義詞 |
Java中的匹配 |
描述 |
時間 |
|
java.time.LocalTime、java.sql.Time |
儲存時間(最多奈秒),轉換為DATETIME時,日期設定為1970年1月1日。 |
日期 |
|
java.time.LocalDate、java.sql.Timestamp |
以 yyyy-mm-dd 格式儲存日期,時間設定為 00:00 |
約會時間 |
時間戳 |
java.time.LocalDateTime、java.sql.Timestamp |
儲存日期+時間(不考慮時區)。 |
大量資料的存儲
SQL類型 |
Java中的匹配 |
描述 |
BLOB |
java.io.InputStream、java.sql.Blob |
儲存二進位資料(圖片、檔案...)。 |
CLOB |
java.io.Reader、java.sql.Clob |
與 VARCHAR 不同,儲存大型文字資料(書籍、文章...)是將資料分部分載入記憶體。 |
SQL 編寫風格
對於許多語言,都有程式碼格式指南。通常,此類文件包含變數、常數、方法和其他語言結構的命名規則。因此,對於 Python,有 PEP8,對於
Java - Oracle Code Conventions for Java。已經為 SQL 建立了幾個不同的集,它們之間略有不同。無論如何,您應該養成在格式化程式碼時遵循規則的習慣,尤其是在團隊中工作時。例如,規則可以是以下(當然,您可以為自己制定一套不同的規則,主要是將來堅持它們):
- 關鍵字和保留字,包括命令和運算符,必須用大寫字母書寫:CREATE TABLE、CONSTRAINT...
- 表格、欄位和其他物件的名稱不應與 SQL 語言關鍵字重合(請參閱文章末尾的連結),但可以包含它們。
- 表名稱應反映其用途。它們以小寫字母書寫。名稱中的單字之間用底線分隔。末尾的單字必須是複數:traders(交易者)、share_rates(份額率)。
- 表格字段名稱應映射其用途。它們必須以小寫字母書寫,名稱中的單字必須採用駝峰式格式,且結尾的單字必須使用單數:name(名稱)、share_rates(分享率)。
- 人工關鍵字段必須包含單字 id。
- CONSTRAINT 名稱必須遵循表命名約定。它們還必須包括其中涉及的欄位和表,以語義前綴開頭:check_(檢查欄位值)、pk_(主鍵)、fk_(外鍵)、uniq_(欄位唯一性)、idx_(索引)。範例:pk_traider_share_actions_id(trader_share_actions 表的 id 欄位上的主鍵)。
- 依此類推,當你學習 SQL 時,規則清單將會被補充/更改。
資料庫管理系統設計
在建立 DBMS 之前,需要對其進行設計。最終模式包含表格、一組欄位、約束、鍵、欄位的預設條件、表格和其他資料庫實體之間的關係。在 Internet 上,您可以找到許多免費的線上/離線設計器來設計小型 DBMS。嘗試在搜尋引擎中輸入「免費資料庫設計器」之類的內容。此類應用程式具有有用的附加屬性:
- 可以產生SQL指令來建立DBMS。
- 在圖表上直觀地顯示設定。
- 允許您移動表格以獲得更好的視覺化效果。
- 在圖表上顯示鍵、索引、關係、預設值等。
- 他們可以遠端儲存 DBMS 模式。
例如,
dbdiffo.com突出顯示鍵、顯示非空白欄位和帶有 NN 標籤的 AI(自動增量)計數器:
在 DBMS 中建立表
所以我們有一個圖表。現在讓我們繼續建立表 (CREATE TABLE)。為此,我們建議獲得初步數據:
- 表名
- 欄位名稱和類型
- 字段的限制(CONSTRAINTS)
- 欄位的預設值(如果可用)
- 主鍵 (PRIMARY KEY) 如果可用
- 表之間的連接(外鍵)
我們不會詳細研究 CREATE TABLE 命令的所有選項;我們將使用為交易者建立表格的範例來了解 SQL 基礎知識:
CREATE TABLE traiders(
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
freqTiсk INTEGER NOT NULL,
cash DECIMAL(15,2) NOT NULL DEFAULT 1000,
tradingMethod INTEGER NOT NULL,
changeProbability INTEGER NOT NULL DEFAULT 50,
about VARCHAR(255) NULL
);
ALTER TABLE traiders ADD CONSTRAINT check_traiders_tradingMethod
CHECK(tradingMethod IN (1,2,3));
ALTER TABLE traiders ADD CONSTRAINT check_traiders_changeProbability
CHECK(changeProbability <= 100 AND changeProbability > 0)
讓我們仔細看看:
CREATE TABLE traiders
(欄位描述)- 建立具有指定名稱的表格;在描述中,欄位以逗號分隔。任何命令都以分號結尾。
- 欄位描述以其名稱開頭,後面跟著其類型、CONSTRAINT 和預設值。
id BIGINT AUTO_INCREMENT PRIMARY KEY
– 整數類型的 id 欄位是一個主鍵和一個增量計數器(對於 id 欄位的每個新記錄,都會產生一個值,該值比該表先前建立的值大 1)。
cash DECIMAL(15,2) NOT NULL DEFAULT 1000
– 現金字段,小數,小數點前 15 位,小數點後兩位(財務數據,例如美元和美分)。不能接受 NULL 值。如果沒有給定值,它將獲得值 1000。
about VARCHAR(255) NULL
– about 字段,最長 255 個字元的字串,可以接受空值。
注意,我們可以在建表後設定部分
CONSTRAINT條件。讓我們考慮一下修改表格結構及其欄位的建構:
ALTER TABLE table_name ADD CONSTRAINT constrain_name CHECK (condition)使用範例:
CHECK(tradingMethod IN (1,2,3))
– TradingMethod欄位只能取值1,2,3
CHECK(changeProbability <= 100 AND changeProbability > 0)
–changeProbability欄位可以取1到100範圍內的整數值
表之間的關係
為了分析表之間關係的描述,我們來看看share_rates的建立:
CREATE TABLE share_rates(
id BIGINT AUTO_INCREMENT PRIMARY KEY,
operDate datetime NOT NULL,
share BIGINT NOT NULL,
rate DECIMAL(15,2) NOT NULL
);
ALTER TABLE share_rates ADD FOREIGN KEY (share) REFERENCES shares(id)
可以如下設定另一個表格的值的引用:
ALTER TABLE
table_from_which_referred
ADD FOREIGN KEY
(field_that_referred)
REFERENCES
table_to_which_referred (field_that_referred to) 讓我們有關於
股票的記錄,例如,對於 id=50,我們儲存初始價格為 的 Microsoft 股票17.5,增量為20,變化機率為4%。對於
share_rates表,我們得到三個主要屬性:
- 我們只需要在share欄位中儲存shares表中id鍵的值,以便使用它從shares表中取得其餘資訊(名稱等)。
- 我們無法為不存在的促銷活動制定費率。您無法在 share 欄位中插入不存在的值(shares 表中沒有具有此 id 的記錄),因為表格之間不會存在對應關係。
- 我們無法刪除在 share_rates 中設定費率的股票中的股票條目。
最後兩點用於確保儲存資料的完整性。您可以使用本文末尾的 github 儲存庫連結來查看我們模擬的 SQL 表的建立以及相應類別方法的 Java 實作中的 SQL 查詢範例。
第三部分
GO TO FULL VERSION