/* Chapter 11 - Examples */
  
  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, 'Bloodshot', 10, 'Rock'),
  (102, 'New Orleans Jazz', 17, 'Jazz'),
  (103, 'Music for Ballet Class', 9, 'Classical'),
  (104, 'Music for Solo Violin', 24, NULL),
  (105, 'Mississippi Blues', 2, 'Blues'),
  (106, 'Mud on the Tires', 12, 'Country'),
  (107, 'The Essence', 5, 'New Age'),
  (108, 'The Magic of Satie', 42, 'Classical'),
  (109, 'La Boheme', 20, 'Opera'),
  (110, 'Ain\'t Ever Satisfied', 23, 'Country'),
  (111, 'Live in Paris', 18, 'Jazz'),
  (112, 'Richland Woman Blues', 22, 'Blues'),
  (113, 'Stages', 42, 'Blues');
  
  SELECT CDName, InStock, Category INTO OUTFILE 'CDsOut.txt'
     FIELDS
        TERMINATED BY ','
        ENCLOSED BY '"'
  FROM CDs;
  
  SELECT CDName, InStock, Category INTO OUTFILE 'CDsOut.txt'
     FIELDS
        TERMINATED BY ','
        ENCLOSED BY '\"'
        ESCAPED BY '\''
  FROM CDs;
  
  SELECT CDName, InStock, Category INTO OUTFILE 'CDsOut.txt'
     LINES
        STARTING BY '*'
        TERMINATED BY '**'
  FROM CDs
  WHERE Category='Blues' OR Category='Jazz';
  
  SELECT CDName, InStock, Category INTO OUTFILE 'CDsOut.txt'
     LINES
        STARTING BY '*'
        TERMINATED BY '**\n'
  FROM CDs
  WHERE Category='Blues' OR Category='Jazz';
  
  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';
  
  SELECT CDName, InStock, Category
     INTO DUMPFILE 'CDsOut.txt'
  FROM CDs
  WHERE CDID=110;
  
  CREATE TABLE CDs2
  (
     CDID SMALLINT NOT NULL PRIMARY KEY,
     CDName VARCHAR(50) NOT NULL,
     InStock SMALLINT UNSIGNED NOT NULL
  )
  SELECT CDID, CDName, InStock
  FROM CDs
  WHERE Category='Blues' OR Category='Jazz';
  
  CREATE TABLE CDs2a
  (
     CDID SMALLINT NOT NULL PRIMARY KEY,
     CDName VARCHAR(5) NOT NULL,
     InStock SMALLINT UNSIGNED NOT NULL
  )
  SELECT CDID, CDName, InStock
  FROM CDs
  WHERE Category='Blues' OR Category='Jazz';
  
  REPLACE INTO CDs2
  SELECT CDID, CDName, InStock
  FROM CDs
  WHERE Category='Country' OR Category='Rock';
  
  CREATE TABLE CDs3
  (
     CDName VARCHAR(50) NOT NULL,
     InStock SMALLINT UNSIGNED NOT NULL,
     Category VARCHAR(20)
  );
  
  SELECT CDName, InStock, Category INTO OUTFILE 'CDsCountry.sql'
     FIELDS
        TERMINATED BY ','
        ENCLOSED BY '"'
  FROM CDs WHERE Category='Country';
  
  LOAD DATA INFILE 'CDsCountry.sql'
  INTO TABLE CDs3
  FIELDS
     TERMINATED BY ','
     ENCLOSED BY '"';
  
  SELECT CDName, InStock, Category INTO OUTFILE 'CDs3.sql'
     FIELDS
        TERMINATED BY ','
        ENCLOSED BY '*'
  FROM CDs WHERE Category='Classical';
  
  mysqlimport --user=root --password=pw1 --fields-terminated-by="," --fields-enclosed-by="*" test "c:\program files\mysql\mysql server 4.1\data\test\CDs3.sql"