SQL query for the erlangmnesia Database

Mnesia is a distributed database management system. It is suitable for telecommunications and other Erlang applications that require continuous operation and have soft real-time features. It is becoming increasingly popular and used, but currently there are not many Mnesia materials, many of them only have official user guides. The following content focuses on how the Mnesia database implements SQL queries and selectinsertup

Mnesia is a distributed database management system. It is suitable for telecommunications and other Erlang applications that require continuous operation and have soft real-time features. It is becoming increasingly popular and used, but currently there are not many Mnesia materials, many of them only have official user guides. The following content focuses on how the Mnesia database implements SQL queries and select/insert/up

Mnesia is a distributed database management system. It is suitable for telecommunications and other Erlang applications that require continuous operation and have soft real-time features. It is becoming increasingly popular and used, but currently there are not many Mnesia materials, many of them only have official user guides. The following content focuses on how the Mnesia database implements SQL queries and SQL operations such as select, insert, update, where, order by, join, limit, and delete.

In the example, the table structure is defined as follows:

% Account table structure-record (y_account, {id, account, password}). % data table structure-record (y_info, {id, nickname, birthday, sex }).

1. Create Table/Delete Table operation

% ===================================================== ==========%% % Create table y_account (id int, account varchar (50), % password varchar (50), primary key (id )); % ===================================================== ============%% % use mnesia: create_tablemnesia: create_table (y_account, [{attributes, record_info (fields, y_account) },{ type, set}, {disc_copies, [node ()]}]). % ===================================================== ==========%% % drop table y_account; % ===================================================== ============%% % use mnesia: delete_tablemnesia: delete_table (y_account ).

Note: For parameter meanings, refer to the document. {type, set} indicates that id is used as the primary key. Duplicate IDs are not allowed. If it is changed to {type, bag}, the id can be repeated, but the entire record cannot be repeated.

2. Select query

Query all records

% ===================================================== ==========%% % Select * from y_account % %=================================== ==========================================%% use mnesia: selectF = fun ()-> MatchHead = # y_account {_ = '_'}, Guard = [], Result = ['$ _'], mnesia: select (y_account, [{MatchHead, Guard, Result}]) end, mnesia: transaction (F ). % use qlcF = fun ()-> Q = qlc: q ([E | E <-mnesia: table (y_account)]), qlc: e (Q) end, mnesia: transaction (F ).

Query records of some fields

% ===================================================== ==========%% % Select id, account from y_account % ======================================== ================%% % use mnesia: selectF = fun ()-> MatchHead = # y_account {id = '$ 1', account =' $ 2', _ = '_'}, Guard = [], result = ['$'], mnesia: select (y_account, [{MatchHead, Guard, Result}]) end, mnesia: transaction (F ). % use qlcF = 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 operations

Mnesia updates records based on the primary key. If the primary key does not exist, it inserts

% ===================================================== ===========% % Insert into y_account (id, account, password) values (5, "xiaohong", "123") % on duplicate key update account = "xiaohong", password = "123 "; % ===================================================== ============%% % use mnesia: writeF = fun ()-> Acc = # y_account {id = 5, account = "xiaohong", password = "123"}, mnesia: write (Acc) end, mnesia: transaction (F ).

4. Where Query

% ===================================================== ==========%%% Select account from y_account where id> 5% %=========================== ============================================%% use mnesia: selectF = fun ()-> MatchHead = # y_account {id = '$ 1', account =' $ 2', _ = '_'}, guard = [{'>', '$ 1', 5}], Result = [' $ 2'], mnesia: select (y_account, [{MatchHead, Guard, result}]) end, mnesia: transaction (F ). % use qlcF = 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 ).

If you want to query records whose primary key is key = X, you can query the records as follows:

%%===============================================%%   select * from y_account where id=5%%===============================================F = fun() ->mnesia:read({y_account,5})end,mnesia:transaction(F).

If you want to query records with non-primary key field = X, you can query the records as follows:

%%===============================================%%   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 Query

% ===================================================== ==========%% % Select * from y_account order by id asc % %============================= ============================================%% % use qlcF = fun () -> Q = qlc: q ([E | E <-mnesia: table (y_account)]), qlc: e (qlc: keysort (2, Q, [{order, ascending}]) end, mnesia: transaction (F ). % use the second method of qlc F = fun ()-> Q = qlc: q ([E | E <-mnesia: table (y_account)]), order = fun (A, B)-> B # y_account.id> A # y_account.idend, qlc: e (qlc: sort (Q, [{order, Order}]) end, mnesia: transaction (F ).

6. Join Table query

% ===================================================== ===========% % Select y_info. * from y_account join y_info on (y_account.id = y_info.id) % where y_account.account = 'xiaomin' % ============================ ============================%% use qlcF = 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 Query

% ===================================================== ==========%% % Select * from y_account limit 2% %=========================== ==========================================%% use mnesia: selectF = fun ()-> MatchHead = # y_account {_ = '_'}, mnesia: select (y_account, [{MatchHead, [], ['$ _']}], 2, none) end, mnesia: transaction (F ). % use qlcF = 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 (*) Query

% ===================================================== ===========% % Select count (*) from y_account % ========================================== ==============%% % use mnesia: table_infoF = fun ()-> mnesia: table_info (y_account, size) end, mnesia: transaction (F ).

9. Delete Query

% ===================================================== ==========%% % Delete from y_account where id = 5% %=============================== ============================================%% use mnesia: deleteF = fun ()-> mnesia: delete ({y_account, 5}) end, mnesia: transaction (F ).

Note: For queries using the qlc module, you must declare "-include_lib (" stdlib/include/qlc. hrl "). ", otherwise the" Warning: qlc: q/1 called, but "qlc. hrl "not supported ded" Warning.

Update description:

Added mnesia: select-based limit Query