准备工作
首先我们需要创建两张表,并插入一些数据
shop表：
| Item | Quantity | Cost |
|---|---|---|
| apple | 20 | 2.3 |
cast表：
| Name | Price |
|---|---|
| apple | 1.5 |
代码：
-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: mnesiaexited: stoppedtype: 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: mnesiaexited: stoppedtype: 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}]
