MogDB 使用样本数据集Mogila
MogDB 使用样本数据集 Mogila
MogDB 提供了一个样本数据集 Mogila，本数据集借鉴了适用于 MySQL 的Sakila 示例数据库。Sakila最初由 MySQL AB 文档团队的 Mike Hillyer 开发，其目的是提供一个可用于书籍、教程、文章、样本等示例的标准 schema。
Mogila 数据集是一个关于 DVD 出租店信息的数据库，包含有关电影（如标题、类别、女演员）、出租店（如地址、工作人员、客户）和出租的信息。您可以使用 Mogila 数据库进行各种功能测试。
Mogila 适用于 MogDB 2.1 及更高版本。
实体-关系模型图
下图展示了 Mogila 数据库表和视图的概览。您可以查看不同表之间如何通过各个字段相互关联。例如，film表具有title和description列。它还通过列language_id和original_language_id与language表相关联。因此您可以联结这两个表来获取每部电影的语言，或者列出特定语言的所有电影。
在 MogDB 容器版中使用 Mogila
MogDB 容器版本已经内置了 Mogila 样本数据库，无需额外安装。
安装 MogDB 容器版。
使用样本数据库 Mogila：
docker exec -it mogdb bash
omm@eb7aef3f860f:~$ gsql -d mogila -p5432
gsql ((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 企业版中使用 Mogila
安装 MogDB 企业版。
创建样本数据库 mogila 及 mogdb 用户，然后登出：
切换到omm用户
[root@test ~]# su - omm
登录postgres数据库，根据实际情况填写端口号
[omm@test ~]$ gsql -d postgres -p5432 -r
gsql ((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 DATABASE
MogDB=#create user mogdb password '*@';
CREATE ROLE
MogDB=#\q
[omm@test ~]$
下载 mogila，并上传至服务器中，进入 mogila 所在目录。
创建模式对象，然后手动插入数据：
4.1 创建所有模式对象（表等）：
gsql -d mogila -p5432 -f mogila-schema.sql
4.2 插入所有数据：
gsql -d mogila -p5432 -f mogila-data.sql
复制
或者通过 1 个脚本创建模式对象并插入数据，如果您已经完成步骤 4.1 和 4.2，则无需执行步骤 5。
创建所有模式对象（表等）并插入所有数据：
gsql -d mogila -p5432 -f mogila-insert-data.sql
使用样本数据库 Mogila：
[omm@test ~]$ gsql -d mogila -p5432 -r
gsql ((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=#
示例查询
本节通过一些查询来展示如何使用样本数据库 Mogila。所有查询结果仅展示前 10 项。
按长度排序列出所有电影
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 |
| 列出每个电影类别中有多少部电影 |
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 |
| 显示按出演电影的数量排序的演员 |
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 数量最多的客户，以及租借次数 |
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 |
| 查看每个出租店的总收入 |
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 个电影类型 |
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 的数据类型为 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 |
复制
清理
如需清理环境并删除样本数据库，请运行以下命令：
\c postgres;
DROP DATABASE mogila;
浙公网安备 33010602011771号