Erlang数据库：Mnesia(一) —— 数据库查询

准备工作

首先我们需要创建两张表,并插入一些数据
shop表：

cast表：

代码：

-module(test_mnesia).
-author("81391").

%% API
-export([init_mnesia_schema/0, start/0, reset_tables/0]).
-include_lib("stdlib/include/qlc.hrl").
-record(shop, {item, quantity, cost}).
-record(cost, {name, price}).

%% 初始化 Mnesia 数据库模式和表
init_mnesia_schema() ->
  mnesia:create_schema([node()]),
  mnesia:start(),
  mnesia:create_table(shop, [{attributes, record_info(fields, shop)}]),
  mnesia:create_table(cost, [{attributes, record_info(fields, cost)}]),
  mnesia:stop().

%% 启动 Mnesia 并等待表格准备就绪
start() ->
  mnesia:start(),
  mnesia:wait_for_tables([shop, cost], 20000).

%% 重置表格，清空数据并重新插入示例数据
reset_tables() ->
  mnesia:clear_table(shop),
  mnesia:clear_table(cost),
  F = fun() ->
    lists:foreach(fun mnesia:write/1, example_tables())
      end,
  mnesia:transaction(F).

%% 测试数据
example_tables() ->
  [ %% shop table data
    {shop, apple, 20, 2.3},
    {shop, orange, 100, 3.8},
    {shop, pear, 200, 3.6},
    {shop, banana, 420, 4.5},
    {shop, potato, 2456, 1.2},
    %% cost table data
    {cost, apple, 1.5},
    {cost, orange, 2.4},
    {cost, pear, 2.2},
    {cost, banana, 1.5},
    {cost, potato, 0.6}].

首先创建数据库表，插入数据

Eshell V8.1  (abort with ^G)
1> c(test_mnesia).              
{ok,test_mnesia}
2> test_mnesia:init_mnesia_schema().  
stopped
3> test_mnesia:start().               

=INFO REPORT==== 1-Dec-2024::16:12:08 ===
    application: mnesia
    exited: stopped
    type: temporary
ok
4> test_mnesia:reset_tables().    
{atomic,ok}

获取指定表中所有数据

%% 使用 QLC 查询指定表所有数据
%% 等同于SQL语句: select * from shop;
select_all(Table) ->
  do(qlc:q([X || X <- mnesia:table(Table)])).

%% 执行查询的辅助函数
do(Q) ->
  F = fun() ->
    qlc:e(Q)
      end,
  {atomic, Val} = mnesia:transaction(F),
  Val.

测试结果：

1> test_mnesia:start().               

=INFO REPORT==== 1-Dec-2024::16:19:36 ===
    application: mnesia
    exited: stopped
    type: temporary
ok
2> test_mnesia:reset_tables().      
{atomic,ok}
3> test_mnesia:select_all(shop).      
[{shop,potato,2456,1.2},
 {shop,apple,20,2.3},
 {shop,orange,100,3.8},
 {shop,pear,200,3.6},
 {shop,banana,420,4.5}]

qlc:q/1 它负责把参数中的查询语句编译为用来执行数据库查询的内部形式。
qlc:e/1 用于执行由 qlc:q/1 生成的查询，并返回查询的结果。qlc:e/1 会在执行时处理 Mnesia 表中的数据，生成一个结果集。
mnesia:transaction/1 执行这个查询事务

获取表中指定数据

%% 获取shop表中指定数据
%% select item, quantity from shop;
demo(select_some) ->
  do(qlc:q([{X#shop.item, X#shop.quantity} || X <- mnesia:table(shop)])).

测试结果：

4> test_mnesia:demo(select_some).                
[{potato,2456},
 {apple,20},
 {orange,100},
 {pear,200},
 {banana,420}]

获取表中满足条件的数据

%% 获取shop表中库存小于250的商品
%% select item from shop
%% where quantity < 250;
demo(reorder) ->
  do(qlc:q([{X#shop.item} || X <- mnesia:table(shop), X#shop.quantity < 250])).

测试结果：

6> test_mnesia:demo(reorder).  
[{apple},{orange},{pear}]

联表查询

%% 获取shop表中库存小于250且价格小于2.0的商品
%% select item from shop
%% join cost
%% on shop.item = cost.name
%% where quantity < 250 and cost.price < 2.0;
demo(join) ->
  do(qlc:q([{X#shop.item} || X <- mnesia:table(shop),
                              X#shop.quantity < 250,
                              Y <- mnesia:table(cost),
                              X#shop.item =:= Y#cost.name, %%关联语句
                              Y#cost.price < 2.0]
  )).

测试结果：

8> test_mnesia:demo(join).     
[{apple}]