Utilitas ekspor data dapat digunakan untuk mengekspor data yang ada dalam database ke file di luar database.
Sintaks Ekspor
<select statement>::=
SELECT
[<select option> [<select
option>...]]
{* | <select list>}
[<export definition>]
[
FROM
<table reference> [{, <table reference>}...]
[WHERE
<expression> [{<operator> <expression>}...]]
[GROUP
BY <group by definition>]
[HAVING
<expression> [{<operator> <expression>}...]]
[ORDER
BY <order by definition>]
[LIMIT
[<offset>,] <row count>]
[PROCEDURE
<procedure name> [(<argument> [{, <argument>}...])]]
[{FOR
UPDATE} | {LOCK IN SHARE MODE}]
]
<export definition>::=
INTO OUTFILE ‘<filename>’
[<export option> [<export option>]]
| INTO DUMPFILE ‘<filename>’
<export option>::=
{FIELDS
[TERMINATED
BY ‘<value>’]
[[OPTIONALLY]
ENCLOSED BY ‘<value>’]
[ESCAPED
BY ‘<value>’]}
| {LINES
[STARTING
BY ‘<value>’]
[TERMINATED
BY ‘<value>’]}
Ekspor Data ke OUT FILE
INTO OUTFILE '<filename>' [<export option> [<export option>]]
<export option>::=
{FIELDS
[TERMINATED BY '<value>']
[[OPTIONALLY] ENCLOSED BY '<value>']
[ESCAPED BY '<value>']}
| {LINES
[STARTING BY '<value>']
[TERMINATED BY '<value>']}
<export option>::=
{FIELDS
[TERMINATED BY '<value>']
[[OPTIONALLY] ENCLOSED BY '<value>']
[ESCAPED BY '<value>']}
| {LINES
[STARTING BY '<value>']
[TERMINATED BY '<value>']}
Mengekspor data ke DUMP FILE tidak diperlukan delimiter, baik kolom (field) maupun baris (line). Biasanya digunakan untuk menyimpan satu baris data.
Contoh:
SELECT *
INTO DUMPFILE "d:/file dump mahasiswa.txt"
FROM mahasiswa WHERE nim="1";
Menyalin Data 1 (cont’d)
Berikutnya, pergunakan perintah INSERT (tanpa VALUES) yang diikuti dengan perintah SELECT (yaitu data yang diambil dari tabel Mahasiswa)
INSERT INTO mahasiswa_backupMaka, isi data pada tabel mahasiswa_backup sama dengan isi tabel mahasiswa.
SELECT * FROM mahasiswa;
Mengimpor Data
- Mengimpor data adalah mengambil data yang semula berada di file penyimpanan (eksternal) ke dalam database.
- Mekanisme pengimporan data harus memperhatikan format data yang sama ketika mengekspor data.
LOAD DATA INFILE 'file mahasiswa.txt'
INTO TABLE mahasiswa_backup;
- Mengekspor, menyalin, dan mengimpor data juga dimungkinkan lintas database.
- Mekanisme kerja ekspor/impor berbeda dengan mekanisme backup/restore.
- Masing-masing memiliki kelebihan dan kekurangan sendiri-sendiri.
EmoticonEmoticon