SQL query for the erlangmnesia Database

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.