MogDB 使用樣本數(shù)據(jù)集 Mogila
MogDB 提供了一個(gè)樣本數(shù)據(jù)集 Mogila，本數(shù)據(jù)集借鑒了適用于 MySQL 的?Sakila 示例數(shù)據(jù)庫(kù)。Sakila?最初由 MySQL AB 文檔團(tuán)隊(duì)的 Mike Hillyer 開發(fā)，其目的是提供一個(gè)可用于書籍、教程、文章、樣本等示例的標(biāo)準(zhǔn) schema。
Mogila 數(shù)據(jù)集是一個(gè)關(guān)于 DVD 出租店信息的數(shù)據(jù)庫(kù)，包含有關(guān)電影（如標(biāo)題、類別、女演員）、出租店（如地址、工作人員、客戶）和出租的信息。您可以使用 Mogila 數(shù)據(jù)庫(kù)進(jìn)行各種功能測(cè)試。
Mogila 適用于 MogDB 2.1 及更高版本。
實(shí)體 - 關(guān)系模型圖
下圖展示了 Mogila 數(shù)據(jù)庫(kù)表和視圖的概覽。您可以查看不同表之間如何通過各個(gè)字段相互關(guān)聯(lián)。例如，film?表具有?title?和?description?列。它還通過列?language_id?和?original_language_id?與?language?表相關(guān)聯(lián)。因此您可以聯(lián)結(jié)這兩個(gè)表來獲取每部電影的語言，或者列出特定語言的所有電影。
在 MogDB 容器版中使用 Mogila
MogDB 容器版本已經(jīng)內(nèi)置了 Mogila 樣本數(shù)據(jù)庫(kù)，無需額外安裝。
安裝 MogDB 容器版。
使用樣本數(shù)據(jù)庫(kù) Mogila：
docker exec -it mogdb bashomm@eb7aef3f860f:~$ gsql -d mogila -p5432gsql ((MogDB x.x.x build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr ?)Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.mogila=# \dt ? ? ? ? ? ? ? ? ? ? ? ? ?List of relations Schema | ? ? Name ? ? ?| Type ?| Owner | ? ? ? ? ? ? Storage--------+---------------+-------+-------+---------------------------------- public | actor ? ? ? ? | table | mogdb | {orientation=row,compression=no} public | address ? ? ? | table | mogdb | {orientation=row,compression=no} public | category ? ? ?| table | mogdb | {orientation=row,compression=no} public | city ? ? ? ? ?| table | mogdb | {orientation=row,compression=no} public | country ? ? ? | table | mogdb | {orientation=row,compression=no} public | customer ? ? ?| table | mogdb | {orientation=row,compression=no} public | film ? ? ? ? ?| table | mogdb | {orientation=row,compression=no} public | film_actor ? ?| table | mogdb | {orientation=row,compression=no} public | film_category | table | mogdb | {orientation=row,compression=no} public | inventory ? ? | table | mogdb | {orientation=row,compression=no} public | language ? ? ?| table | mogdb | {orientation=row,compression=no} public | payment ? ? ? | table | mogdb | {orientation=row,compression=no} public | rental ? ? ? ?| table | mogdb | {orientation=row,compression=no} public | staff ? ? ? ? | table | mogdb | {orientation=row,compression=no} public | store ? ? ? ? | table | mogdb | {orientation=row,compression=no}(15 rows)mogila=#
在 MogDB 企業(yè)版中使用 Mogila
安裝 MogDB 企業(yè)版。
創(chuàng)建樣本數(shù)據(jù)庫(kù) mogila 及 mogdb 用戶，然后登出：
# 切換到omm用戶[root@test ~]# su - omm# 登錄postgres數(shù)據(jù)庫(kù)，根據(jù)實(shí)際情況填寫端口號(hào)[omm@test ~]$ gsql -d postgres -p5432 -rgsql ((MogDB x.x.x build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr ?)Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.MogDB=#create database mogila DBCOMPATIBILITY='PG';CREATE DATABASEMogDB=#create user mogdb password 'Enmo@123';CREATE ROLEMogDB=#\q[omm@test ~]$下載 mogila，并上傳至服務(wù)器中，進(jìn)入 mogila 所在目錄。
創(chuàng)建模式對(duì)象，然后手動(dòng)插入數(shù)據(jù)：
gsql -d mogila -p5432 -f mogila-schema.sqlgsql -d mogila -p5432 -f mogila-data.sql復(fù)制或者通過 1 個(gè)腳本創(chuàng)建模式對(duì)象并插入數(shù)據(jù)，如果您已經(jīng)完成步驟 4.1 和 4.2，則無需執(zhí)行步驟 5。
4.2 插入所有數(shù)據(jù)：
4.1 創(chuàng)建所有模式對(duì)象（表等）：
創(chuàng)建所有模式對(duì)象（表等）并插入所有數(shù)據(jù)：
gsql -d mogila -p5432 -f mogila-insert-data.sql使用樣本數(shù)據(jù)庫(kù) Mogila：
[omm@test ~]$ gsql -d mogila -p5432 -rgsql ((MogDB x.x.x build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr ?)Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.mogila=# \dt ? ? ? ? ? ? ? ? ? ? ? ? ?List of relations Schema | ? ? Name ? ? ?| Type ?| Owner | ? ? ? ? ? ? Storage--------+---------------+-------+-------+---------------------------------- public | actor ? ? ? ? | table | mogdb | {orientation=row,compression=no} public | address ? ? ? | table | mogdb | {orientation=row,compression=no} public | category ? ? ?| table | mogdb | {orientation=row,compression=no} public | city ? ? ? ? ?| table | mogdb | {orientation=row,compression=no} public | country ? ? ? | table | mogdb | {orientation=row,compression=no} public | customer ? ? ?| table | mogdb | {orientation=row,compression=no} public | film ? ? ? ? ?| table | mogdb | {orientation=row,compression=no} public | film_actor ? ?| table | mogdb | {orientation=row,compression=no} public | film_category | table | mogdb | {orientation=row,compression=no} public | inventory ? ? | table | mogdb | {orientation=row,compression=no} public | language ? ? ?| table | mogdb | {orientation=row,compression=no} public | payment ? ? ? | table | mogdb | {orientation=row,compression=no} public | rental ? ? ? ?| table | mogdb | {orientation=row,compression=no} public | staff ? ? ? ? | table | mogdb | {orientation=row,compression=no} public | store ? ? ? ? | table | mogdb | {orientation=row,compression=no}(15 rows)mogila=#
示例查詢
本節(jié)通過一些查詢來展示如何使用樣本數(shù)據(jù)庫(kù) Mogila。所有查詢結(jié)果僅展示前 10 項(xiàng)。
按長(zhǎng)度排序列出所有電影
select film_id, title, length from film order by length desc;|film_id|title ? ? ? ? ? ? |length||-------|------------------|------||426 ? ?|HOME PITY ? ? ? ? |185 ? ||690 ? ?|POND SEATTLE ? ? ?|185 ? ||609 ? ?|MUSCLE BRIGHT ? ? |185 ? ||991 ? ?|WORST BANGER ? ? ?|185 ? ||182 ? ?|CONTROL ANTHEM ? ?|185 ? ||141 ? ?|CHICAGO NORTH ? ? |185 ? ||349 ? ?|GANGS PRIDE ? ? ? |185 ? ||212 ? ?|DARN FORRESTER ? ?|185 ? ||817 ? ?|SOLDIERS EVOLUTION|185 ? ||872 ? ?|SWEET BROTHERHOOD |185 ? |列出每個(gè)電影類別中有多少部電影
select category.name, count(category.name) category_count from category left join film_category on category.category_id = film_category.category_id left join film on film_category.film_id = film.film_id group by category.name order by category_count desc;|name ? ? ? |category_count||-----------|--------------||Sports ? ? |74 ? ? ? ? ? ?||Foreign ? ?|73 ? ? ? ? ? ?||Family ? ? |69 ? ? ? ? ? ?||Documentary|68 ? ? ? ? ? ?||Animation ?|66 ? ? ? ? ? ?||Action ? ? |64 ? ? ? ? ? ?||New ? ? ? ?|63 ? ? ? ? ? ?||Drama ? ? ?|62 ? ? ? ? ? ?||Sci-Fi ? ? |61 ? ? ? ? ? ?||Games ? ? ?|61 ? ? ? ? ? ?|顯示按出演電影的數(shù)量排序的演員
select actor.first_name, actor.last_name, count(actor.first_name) featured_count from actor left join film_actor on actor.actor_id = film_actor.actor_id group by actor.first_name, actor.last_name order by featured_count desc;|first_name|last_name|featured_count||----------|---------|--------------||SUSAN ? ? |DAVIS ? ?|54 ? ? ? ? ? ?||GINA ? ? ?|DEGENERES|42 ? ? ? ? ? ?||WALTER ? ?|TORN ? ? |41 ? ? ? ? ? ?||MARY ? ? ?|KEITEL ? |40 ? ? ? ? ? ?||MATTHEW ? |CARREY ? |39 ? ? ? ? ? ?||SANDRA ? ?|KILMER ? |37 ? ? ? ? ? ?||SCARLETT ?|DAMON ? ?|36 ? ? ? ? ? ?||VIVIEN ? ?|BASINGER |35 ? ? ? ? ? ?||VAL ? ? ? |BOLGER ? |35 ? ? ? ? ? ?||GROUCHO ? |DUNST ? ?|35 ? ? ? ? ? ?|獲取所有活躍客戶的列表，按其姓名排序
select first_name, last_name from customer where active = 1 order by first_name asc;|first_name|last_name||----------|---------||MARY ? ? ?|SMITH ? ?||PATRICIA ?|JOHNSON ?||LINDA ? ? |WILLIAMS ||BARBARA ? |JONES ? ?||ELIZABETH |BROWN ? ?||JENNIFER ?|DAVIS ? ?||MARIA ? ? |MILLER ? ||SUSAN ? ? |WILSON ? ||MARGARET ?|MOORE ? ?||DOROTHY ? |TAYLOR ? |查看租 DVD 數(shù)量最多的客戶，以及租借次數(shù)
select customer.first_name, customer.last_name, count(customer.first_name) rentals_count from customer left join rental on customer.customer_id = rental.customer_id group by customer.first_name, customer.last_name order by rentals_count desc;|first_name|last_name|rentals_count||----------|---------|-------------||ELEANOR ? |HUNT ? ? |46 ? ? ? ? ? ||KARL ? ? ?|SEAL ? ? |45 ? ? ? ? ? ||CLARA ? ? |SHAW ? ? |42 ? ? ? ? ? ||MARCIA ? ?|DEAN ? ? |42 ? ? ? ? ? ||TAMMY ? ? |SANDERS ?|41 ? ? ? ? ? ||WESLEY ? ?|BULL ? ? |40 ? ? ? ? ? ||SUE ? ? ? |PETERS ? |40 ? ? ? ? ? ||MARION ? ?|SNYDER ? |39 ? ? ? ? ? ||RHONDA ? ?|KENNEDY ?|39 ? ? ? ? ? ||TIM ? ? ? |CARY ? ? |39 ? ? ? ? ? |查看每個(gè)出租店的總收入
select store.store_id, sum(payment.amount) as "total revenue" from store left join inventory on inventory.store_id = store.store_id left join rental on rental.inventory_id = inventory.inventory_id left join payment on payment.rental_id = rental.rental_id where payment.amount is not null group by store.store_id order by sum(payment.amount) desc;|store_id|total revenue||--------|-------------|| ? ? ? 2| ? ? 33726.77|| ? ? ? 1| ? ? 33689.74|按總收入列出前 5 個(gè)電影類型
select category.name, film.title, sum(payment.amount) as "gross revenue" from film left join film_category on film_category.film_id = film.film_id left join category on film_category.category_id = category.category_id left join inventory on inventory.film_id = film.film_id left join rental on rental.inventory_id = inventory.inventory_id left join payment on payment.rental_id = rental.rental_id where payment.amount is not null group by category.name, film.title order by sum(payment.amount) desc limit 5;| ? name ? ? | ? ? ? title ? ? ? | gross revenue||------------|-------------------|--------------||Music ? ? ? | TELEGRAPH VOYAGE ?| ? ? ? ?231.73||Documentary | WIFE TURN ? ? ? ? | ? ? ? ?223.69||Comedy ? ? ?| ZORRO ARK ? ? ? ? | ? ? ? ?214.69||Sci-Fi ? ? ?| GOODFELLAS SALUTE | ? ? ? ?209.69||Sports ? ? ?| SATURDAY LAMBS ? ?| ? ? ? ?204.72|film.description?的數(shù)據(jù)類型為?text?，支持全文搜索查詢，搜索所有包含?documentary?和?robot?的描述select film.title, film.description from film where to_tsvector(film.description) @@ to_tsquery('documentary & robot');| ?title ? ? ? ? ?| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?description ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ||-----------------|--------------------------------------------------------------------------------------------------------------------||CASPER DRAGONFLY | A Intrepid Documentary of a Boat And a Crocodile who must Chase a Robot in The Sahara Desert ? ? ? ? ? ? ? ? ? ? ? ||CHAINSAW UPTOWN ?| A Beautiful Documentary of a Boy And a Robot who must Discover a Squirrel in Australia ? ? ? ? ? ? ? ? ? ? ? ? ? ? ||CONTROL ANTHEM ? | A Fateful Documentary of a Robot And a Student who must Battle a Cat in A Monastery ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?||CROSSING DIVORCE | A Beautiful Documentary of a Dog And a Robot who must Redeem a Womanizer in Berlin ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ||KANE EXORCIST ? ?| A Epic Documentary of a Composer And a Robot who must Overcome a Car in Berlin ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ||RUNNER MADIGAN ? | A Thoughtful Documentary of a Crocodile And a Robot who must Outrace a Womanizer in The Outback ? ? ? ? ? ? ? ? ? ?||SOUTH WAIT ? ? ? | A Amazing Documentary of a Car And a Robot who must Escape a Lumberjack in An Abandoned Amusement Park ? ? ? ? ? ? ||SWEDEN SHINING ? | A Taut Documentary of a Car And a Robot who must Conquer a Boy in The Canadian Rockies ? ? ? ? ? ? ? ? ? ? ? ? ? ? ||VIRGIN DAISY ? ? | A Awe-Inspiring Documentary of a Robot And a Mad Scientist who must Reach a Database Administrator in A Shark Tank |復(fù)制
清理
如需清理環(huán)境并刪除樣本數(shù)據(jù)庫(kù)，請(qǐng)運(yùn)行以下命令：
\c postgres;DROP DATABASE mogila;
