暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MogDB 使用样本数据集Mogila

MogDB 使用样本数据集Mogila

原创 由迪 2022-06-22
376

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表相关联。因此您可以联结这两个表来获取每部电影的语言，或者列出特定语言的所有电影。

img

在MogDB容器版中使用Mogila

MogDB容器版本已经内置了Mogila样本数据库，无需额外安装。

  1. 安装MogDB容器版。

  2. 使用样本数据库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

  1. 安装MogDB企业版。

  2. 创建样本数据库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 'Enmo@123';
CREATE ROLE
MogDB=#\q
[omm@test ~]$

    复制

  3. 下载mogila，并上传至服务器中，进入mogila所在目录。

  4. 创建模式对象，然后手动插入数据：

    • 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。

  5. 创建所有模式对象（表等）并插入所有数据：

    gsql -d mogila -p5432 -f mogila-insert-data.sql

    复制

  6. 使用样本数据库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;

复制
墨力计划mogdb
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

Mogdb 学习记录（共582篇）
Mogdb 学习指南

评论

相关阅读

MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
MogDB
284次阅读
2025-04-17 10:41:41
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
云和恩墨
179次阅读
2025-04-16 09:52:02
手动安装MogDB5.0.11企业版（x86单机）安装指南
张鹏
26次阅读
2025-04-03 10:00:20