SQL/MySQL/Table Index/Backup Table
Содержание
Add line separator to the backup file
<source lang="sql">
Drop table CDs; CREATE TABLE CDs (
CDID SMALLINT NOT NULL PRIMARY KEY, CDName VARCHAR(50) NOT NULL, InStock SMALLINT UNSIGNED NOT NULL, Category VARCHAR(20)
);
INSERT INTO CDs VALUES (101, "Blood", 10, "Rock"),
(102, "Jazz", 17, "Jazz"), (103, "Class", 9, "Classical"), (104, "Violin", 24, NULL), (105, "Blues", 2, "Blues"), (106, "Tires", 12, "Country"), (107, "Essence", 5, "New Age"), (108, "Magic", 42, "Classical"), (109, "Name", 20, "Opera"), (110, "Fire", 23, "Country"), (111, "Live", 18, "Jazz"), (112, "Blues", 22, "Blues"), (113, "Stages", 42, "Blues");
SELECT CDName, InStock, Category INTO OUTFILE "CDsOut.txt"
LINES STARTING BY "*" TERMINATED BY "**\n"
FROM CDs WHERE Category="Blues" OR Category="Jazz";
</source>
Backup file indicating line and record separator
<source lang="sql">
Drop table CDs; CREATE TABLE CDs (
CDID SMALLINT NOT NULL PRIMARY KEY, CDName VARCHAR(50) NOT NULL, InStock SMALLINT UNSIGNED NOT NULL, Category VARCHAR(20)
);
INSERT INTO CDs VALUES (101, "Blood", 10, "Rock"),
(102, "Jazz", 17, "Jazz"), (103, "Class", 9, "Classical"), (104, "Violin", 24, NULL), (105, "Blues", 2, "Blues"), (106, "Tires", 12, "Country"), (107, "Essence", 5, "New Age"), (108, "Magic", 42, "Classical"), (109, "Name", 20, "Opera"), (110, "Fire", 23, "Country"), (111, "Live", 18, "Jazz"), (112, "Blues", 22, "Blues"), (113, "Stages", 42, "Blues");
SELECT CDName, InStock, Category INTO OUTFILE "CDsOut.txt"
FIELDS TERMINATED BY "," ENCLOSED BY "\"" LINES TERMINATED BY "<<end>>\n"
FROM CDs WHERE Category="Blues" OR Category="Jazz";
</source>
Backup file into txt file indicating the line terminator
<source lang="sql">
Drop table CDs; CREATE TABLE CDs (
CDID SMALLINT NOT NULL PRIMARY KEY, CDName VARCHAR(50) NOT NULL, InStock SMALLINT UNSIGNED NOT NULL, Category VARCHAR(20)
);
INSERT INTO CDs VALUES (101, "Blood", 10, "Rock"),
(102, "Jazz", 17, "Jazz"), (103, "Class", 9, "Classical"), (104, "Violin", 24, NULL), (105, "Blues", 2, "Blues"), (106, "Tires", 12, "Country"), (107, "Essence", 5, "New Age"), (108, "Magic", 42, "Classical"), (109, "Name", 20, "Opera"), (110, "Fire", 23, "Country"), (111, "Live", 18, "Jazz"), (112, "Blues", 22, "Blues"), (113, "Stages", 42, "Blues");
SELECT CDName, InStock, Category INTO OUTFILE "CDsOut.txt"
FIELDS TERMINATED BY "," ENCLOSED BY "\"" ESCAPED BY "\""
FROM CDs;
</source>
Backup records into txt file 2
<source lang="sql">
Drop table CDs; CREATE TABLE CDs (
CDID SMALLINT NOT NULL PRIMARY KEY, CDName VARCHAR(50) NOT NULL, InStock SMALLINT UNSIGNED NOT NULL, Category VARCHAR(20)
);
INSERT INTO CDs VALUES (101, "Blood", 10, "Rock"),
(102, "Jazz", 17, "Jazz"), (103, "Class", 9, "Classical"), (104, "Violin", 24, NULL), (105, "Blues", 2, "Blues"), (106, "Tires", 12, "Country"), (107, "Essence", 5, "New Age"), (108, "Magic", 42, "Classical"), (109, "Name", 20, "Opera"), (110, "Fire", 23, "Country"), (111, "Live", 18, "Jazz"), (112, "Blues", 22, "Blues"), (113, "Stages", 42, "Blues");
SELECT CDName, InStock, Category INTO OUTFILE "CDsOut.txt"
FIELDS TERMINATED BY "," ENCLOSED BY """
FROM CDs;
</source>
Backup selected records into txt file
<source lang="sql">
Drop table CDs; CREATE TABLE CDs (
CDID SMALLINT NOT NULL PRIMARY KEY, CDName VARCHAR(50) NOT NULL, InStock SMALLINT UNSIGNED NOT NULL, Category VARCHAR(20)
);
INSERT INTO CDs VALUES (101, "Blood", 10, "Rock"),
(102, "Jazz", 17, "Jazz"), (103, "Class", 9, "Classical"), (104, "Violin", 24, NULL), (105, "Blues", 2, "Blues"), (106, "Tires", 12, "Country"), (107, "Essence", 5, "New Age"), (108, "Magic", 42, "Classical"), (109, "Name", 20, "Opera"), (110, "Fire", 23, "Country"), (111, "Live", 18, "Jazz"), (112, "Blues", 22, "Blues"), (113, "Stages", 42, "Blues");
SELECT CDName, InStock, Category INTO OUTFILE "CDsOut.txt"
LINES STARTING BY "*" TERMINATED BY "**"
FROM CDs WHERE Category="Blues" OR Category="Jazz";
</source>
Dump record into txt file
<source lang="sql">
Drop table CDs; CREATE TABLE CDs (
CDID SMALLINT NOT NULL PRIMARY KEY, CDName VARCHAR(50) NOT NULL, InStock SMALLINT UNSIGNED NOT NULL, Category VARCHAR(20)
);
INSERT INTO CDs VALUES (101, "Blood", 10, "Rock"),
(102, "Jazz", 17, "Jazz"), (103, "Class", 9, "Classical"), (104, "Violin", 24, NULL), (105, "Blues", 2, "Blues"), (106, "Tires", 12, "Country"), (107, "Essence", 5, "New Age"), (108, "Magic", 42, "Classical"), (109, "Name", 20, "Opera"), (110, "Fire", 23, "Country"), (111, "Live", 18, "Jazz"), (112, "Blues", 22, "Blues"), (113, "Stages", 42, "Blues");
SELECT CDName, InStock, Category
INTO DUMPFILE "CDsOut.txt"
FROM CDs WHERE CDID=110;
</source>