SQL/MySQL/Table Index/Backup Table

Материал из SQL эксперт
Перейти к: навигация, поиск

Add line separator to the backup file

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";



Backup file indicating line and record separator

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";



Backup file into txt file indicating the line terminator

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;



Backup records into txt file 2

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;



Backup selected records into txt file

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";



Dump record into txt file

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;
Источник — «http://sqle.ru/index.php?title=SQL/MySQL/Table_Index/Backup_Table&oldid=5489»