/* 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"