erlang mnesia 数据库基本查询

　　示例中表结构的定义：

　　%% 账号表结构

　　-record( y_account,{ id, account, password }).

　　%% 资料表结构

　　-record( y_info, { id, nickname, birthday, sex }).

　　1、select 查询

　　查询全部记录

%%===============================================

%%  select * from y_account

%% 使用 mnesia:select

f = fun() ->

matchhead = #y_account{ _ = '_' },

guard = [],

result = ['$_'],

mnesia:select(y_account, [{matchhead, guard, result}])

end,

mnesia:transaction(f).

%% 使用 qlc

q = qlc:q([e || e <- mnesia:table(y_account)]),

qlc:e(q)

　　查询部分字段的记录

%%  select id,account from y_account

matchhead = #y_account{id = '$1', account = '$2', _ = '_' },

result = ['$$'],

q = qlc:q([[e#y_account.id, e#y_account.account] || e <- mnesia:table(y_account)]),

　2、insert / update 操作

　　mnesia是根据主键去更新记录的，如果主键不存在则插入

%%    insert into y_account (id,account,password) values(5,"xiaohong","123")

%%     on duplicate key update account="xiaohong",password="123";

%% 使用 mnesia:write

acc = #y_account{id = 5, account="xiaohong", password="123"},

mnesia:write(acc)

　　3、where 查询

%%    select account from y_account where id>5

guard = [{'>', '$1', 5}],

result = ['$2'],

q = qlc:q([e#y_account.account || e <- mnesia:table(y_account), e#y_account.id>5]),

　　如果查找主键 key=x 的记录，还可以这样子查询：

%%   select * from y_account where id=5

mnesia:read({y_account,5})

    如果查找非主键 field=x 的记录，可以如下查询：

%%   select * from y_account where account='xiaomin'

matchhead = #y_account{ id = '_', account = "xiaomin", password = '_' },

　　4、order by 查询

%%   select * from y_account order by id asc

qlc:e(qlc:keysort(2, q, [{order, ascending}]))

%% 使用 qlc 的第二种写法

order = fun(a, b) ->

b#y_account.id > a#y_account.id

qlc:e(qlc:sort(q, [{order, order}]))

　　5、join 关联表查询

%%   select y_info.* from y_account join y_info on (y_account.id = y_info.id)

%%      where y_account.account = 'xiaomin'

q = qlc:q([y || x <- mnesia:table(y_account),

x#y_account.account =:= "xiaomin",

y <- mnesia:table(y_info),

x#y_account.id =:= y#y_info.id

]),

　　6、limit 查询

%%   select * from y_account limit 2

qc = qlc:cursor(q),

qlc:next_answers(qc, 2)

　　注：使用qlc模块查询，需要在文件顶部声明“-include_lib("stdlib/include/qlc.hrl").”，否则编译时会产生“warning: qlc:q/1 called, but "qlc.hrl" not included”的警告。   

最新内容请见作者的github页：http://qaseven.github.io/