qileilove

          blog已經轉移至github,大家請訪問 http://qaseven.github.io/

          erlang mnesia 數據庫基本查詢

          Mnesia是一個分布式數據庫管理系統,適合于電信和其它需要持續運行和具備軟實時特性的Erlang應用,越來越受關注和使用,但是目前Mnesia資料卻不多,很多都只有官方的用戶指南。下面的內容將著重說明 如何做 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
          F = fun() ->
          Q = qlc:q([E || E <- mnesia:table(y_account)]),
          qlc:e(Q)
          end,
          mnesia:transaction(F).
            查詢部分字段的記錄
          %%===============================================
          %%  select id,account from y_account
          %%===============================================
          %% 使用 mnesia:select
          F = fun() ->
          MatchHead = #y_account{id = '$1', account = '$2', _ = '_' },
          Guard = [],
          Result = ['$$'],
          mnesia:select(y_account, [{MatchHead, Guard, Result}])
          end,
          mnesia:transaction(F).
          %% 使用 qlc
          F = fun() ->
          Q = qlc:q([[E#y_account.id, E#y_account.account] || E <- mnesia:table(y_account)]),
          qlc:e(Q)
          end,
          mnesia:transaction(F).

           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
          F = fun() ->
          Acc = #y_account{id = 5, account="xiaohong", password="123"},
          mnesia:write(Acc)
          end,
          mnesia:transaction(F).
            3、Where 查詢
          %%===============================================
          %%    select account from y_account where id>5
          %%===============================================
          %% 使用 mnesia:select
          F = fun() ->
          MatchHead = #y_account{id = '$1', account = '$2', _ = '_' },
          Guard = [{'>', '$1', 5}],
          Result = ['$2'],
          mnesia:select(y_account, [{MatchHead, Guard, Result}])
          end,
          mnesia:transaction(F).
          %% 使用 qlc
          F = fun() ->
          Q = qlc:q([E#y_account.account || E <- mnesia:table(y_account), E#y_account.id>5]),
          qlc:e(Q)
          end,
          mnesia:transaction(F).
            如果查找主鍵 key=X 的記錄,還可以這樣子查詢:
          %%===============================================
          %%   select * from y_account where id=5
          %%===============================================
          F = fun() ->
          mnesia:read({y_account,5})
          end,
          mnesia:transaction(F).
              如果查找非主鍵 field=X 的記錄,可以如下查詢:
          %%===============================================
          %%   select * from y_account where account='xiaomin'
          %%===============================================
          F = fun() ->
          MatchHead = #y_account{ id = '_', account = "xiaomin", password = '_' },
          Guard = [],
          Result = ['$_'],
          mnesia:select(y_account, [{MatchHead, Guard, Result}])
          end,
          mnesia:transaction(F).
            4、Order By 查詢
          %%===============================================
          %%   select * from y_account order by id asc
          %%===============================================
          %% 使用 qlc
          F = fun() ->
          Q = qlc:q([E || E <- mnesia:table(y_account)]),
          qlc:e(qlc:keysort(2, Q, [{order, ascending}]))
          end,
          mnesia:transaction(F).
          %% 使用 qlc 的第二種寫法
          F = fun() ->
          Q = qlc:q([E || E <- mnesia:table(y_account)]),
          Order = fun(A, B) ->
          B#y_account.id > A#y_account.id
          end,
          qlc:e(qlc:sort(Q, [{order, Order}]))
          end,
          mnesia:transaction(F).


            5、Join 關聯表查詢
          %%===============================================
          %%   select y_info.* from y_account join y_info on (y_account.id = y_info.id)
          %%      where y_account.account = 'xiaomin'
          %%===============================================
          %% 使用 qlc
          F = fun() ->
          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
          ]),
          qlc:e(Q)
          end,
          mnesia:transaction(F).
            6、Limit 查詢
          %%===============================================
          %%   select * from y_account limit 2
          %%===============================================
          %% 使用 qlc
          F = fun() ->
          Q = qlc:q([E || E <- mnesia:table(y_account)]),
          QC = qlc:cursor(Q),
          qlc:next_answers(QC, 2)
          end,
          mnesia:transaction(F).
            注:使用qlc模塊查詢,需要在文件頂部聲明“-include_lib("stdlib/include/qlc.hrl").”,否則編譯時會產生“Warning: qlc:q/1 called, but "qlc.hrl" not included”的警告。

          posted on 2013-10-14 10:06 順其自然EVO 閱讀(2338) 評論(0)  編輯  收藏 所屬分類: 數據庫

          <2013年10月>
          293012345
          6789101112
          13141516171819
          20212223242526
          272829303112
          3456789

          導航

          統計

          常用鏈接

          留言簿(55)

          隨筆分類

          隨筆檔案

          文章分類

          文章檔案

          搜索

          最新評論

          閱讀排行榜

          評論排行榜

          主站蜘蛛池模板: 垫江县| 集安市| 黔西县| 新竹县| 怀远县| 许昌县| 宜丰县| 阿拉善右旗| 杭州市| 棋牌| 阳东县| 林口县| 凤山县| 金平| 德惠市| 诸暨市| 祁阳县| 梁平县| 碌曲县| 龙陵县| 两当县| 行唐县| 满洲里市| 南陵县| 克东县| 新宾| 边坝县| 梓潼县| 萍乡市| 遵化市| 徐闻县| 贵溪市| 舟曲县| 浙江省| 天水市| 永康市| 会同县| 若羌县| 延庆县| 冷水江市| 精河县|