elang mnesia 数据库操作
来源:互联网 发布:ubuntu交叉编译器 编辑:程序博客网 时间:2024/06/07 19:58
Mnesia是一个分布式数据库管理系统,适合于电信和其它需要持续运行和具备软实时特性的Erlang应用,越来越受关注和使用,但是目前Mnesia资料却不多,很多都只有官方的用户指南。下面的内容将着重说明 Mnesia 数据库如何实现SQL查询,实现select / insert / update / where / order by / join / limit / delete等SQL操作。
示例中表结构的定义:
- %% 账号表结构
- -record( y_account,{ id, account, password }).
- %% 资料表结构
- -record( y_info, { id, nickname, birthday, sex }).
1、Create Table / Delete Table 操作
- %%===============================================
- %% create table y_account ( id int, account varchar(50),
- %% password varchar(50), primary key(id)) ;
- %%===============================================
- %% 使用 mnesia:create_table
- mnesia:create_table( y_account,[{attributes, record_info(fields, y_account)} ,
- {type,set}, {disc_copies, [node()]} ]).
- %%===============================================
- %% drop table y_account;
- %%===============================================
- %% 使用 mnesia:delete_table
- mnesia:delete_table(y_account) .
注:参数意义可以看文档,{type,set} 表示id作为主键,不允许id重复,如果改为 {type,bag},id可以重复,但整条记录不能重复
2、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).
3、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).
4、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).
5、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).
6、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).
7、Limit 查询
- %%===============================================
- %% select * from y_account limit 2
- %%===============================================
- %% 使用 mnesia:select
- F = fun() ->
- MatchHead = #y_account{ _ = '_' },
- mnesia:select(y_account, [{MatchHead, [], ['$_']}], 2, none)
- end,
- mnesia:transaction(F).
- %% 使用 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).
8、Select count(*) 查询
- %%===============================================
- %% select count(*) from y_account
- %%===============================================
- %% 使用 mnesia:table_info
- F = fun() ->
- mnesia:table_info(y_account, size)
- end,
- mnesia:transaction(F).
9、Delete 查询
- %%===============================================
- %% delete from y_account where id=5
- %%===============================================
- %% 使用 mnesia:delete
- F = fun() ->
- mnesia:delete({y_account, 5})
- end,
- mnesia:transaction(F).
注:使用qlc模块查询,需要在文件顶部声明“-include_lib("stdlib/include/qlc.hrl").”,否则编译时会产生“Warning: qlc:q/1 called, but "qlc.hrl" not included”的警告。
0 0
- elang mnesia 数据库操作
- mnesia数据库操作
- Erlang数据库Mnesia操作
- Mnesia数据库操作整理(1)
- Erlang数据库-(二)Mnesia操作
- Mnesia数据库操作整理(2)
- Elang
- Mnesia用户手册:三,构建Mnesia数据库
- Mnesia用户手册:三,构建Mnesia数据库
- Mnesia用户手册:三,构建Mnesia数据库
- mnesia数据库性能测试
- [Erlang]Mnesia模拟mysql操作
- 如何使用erlang mnesia 数据库
- mnesia数据库上创建表
- Mnesia数据库集群(基础)
- mnesia
- erlang(十五)--Mnesia用户手册:三,构建Mnesia数据库
- erlang数据库Mnesia主键自增
- github学习历程
- JMX 介绍
- jsp和servlet的关系?答:jsp就是servlet,tomcat会把jsp编译成servlet,servlet就是一个java类,在tomcat下的work文件夹中
- linux性能分析及调优__cpu 性能瓶颈调优可调性能参数 、内存性能瓶颈可调性能参数(操作系统设置swap的目的、在写程序时、如何使自己的内存不被换出swap,常驻物理内存)、磁盘I/O可调性能参
- eNSP组网设计与配置实验
- elang mnesia 数据库操作
- binder学习笔记(十)—— 穿越到驱动层
- Java设计模式(八):桥接模式bridge
- git常用命令让你几步摆脱图形工具提交项目
- 一种快速求解最大团问题的算法
- IEEE 802.15.4协议完整中文版 - 1. 概述
- Java网络编程(二) - 15分钟搭建我的第一个 Spring MVC 项目
- vim、gcc/g++、ctags简析
- 低功耗蓝牙(BLE)