Inserting Data Mysql

Basic commands on mysql command line:

  % mysql

  mysql> USE wbluhm;
  
  mysql> SELECT * FROM books;
  
  mysql> INSERT INTO books 
      -> VALUES ("My Life", "Mickey Mouse", "Disney", 
      ->         "Biography", "What can I say?", 9.95);
  
  mysql> SELECT * FROM books;
  
  mysql> DELETE FROM books WHERE title="My Life";
  
  mysql> SELECT * FROM books;
  
  mysql> exit;
  
  %

Inserting data with .sql scripts:

 

SQL script example: populate_books.sql

-- populates the books table

INSERT INTO books 
  VALUES ("MySQL", "Paul DuBois", "New Riders", "MySQL", 
          "Excellent book, but doesn't cover Java API", 49.99);

INSERT INTO books
  VALUES ("Beginning XML", "David Hunter", "Wrox", "XML",
          "Well recommended, fairly comprehensive", 39.99);

INSERT INTO books 
  VALUES ("Java How to Program", "Paul Deitel", "Prentice Hall", "Java",
          "Good textbook, extremely detailed", 68.00);

INSERT INTO books
  VALUES ("Thinking in Java", "Bruce Eckel", "Prentice Hall", "Java",
          "Well written, free on the web", 0.00); 

INSERT INTO books
  VALUES ("The Java Programming Language", "Ken Arnold", "Addison Wesley", "Java",
          "Considered to be from the source", 37.95);

INSERT INTO books
  VALUES ("Learning Perl", "Randal Schwartz", "O'Reilly", "Perl",
          "Not a bad start", 29.95);

INSERT INTO books
  VALUES ("Programming Perl", "Larry Wall", "O'Reilly", "Perl",
          "Usually considered THE reference", 44.95);

INSERT INTO books
  VALUES ("Effective Perl Programming", "Joseph Hall", "Addison Wesley", "Perl",
          "Great tips, not for beginners", 34.95);

 % mysql wbluhm < populate_books.sql
  
  % mysql wbluhm
  
  mysql> SELECT * FROM books;
  
  mysql> DELETE FROM books; 
  
  mysql> SELECT * FROM books;
  
  mysql> exit;
  
  %

Inserting data from files

 

data file: books.data

MySQL   Paul DuBois     New Riders      MySQL   ...     49.99
Beginning XML   David Hunter    Wrox    XML     ...     39.99
...
Effective Perl Programming      Joseph Hall     Perl    ...     34.95

 

 

% mysql
  
  mysql> USE wbluhm
  
  mysql> LOAD DATA INFILE '/users/science/wbluhm/mysql/books.data'
      -> INTO TABLE books
      -> FIELDS TERMINATED BY '\t'
      -> LINES TERMINATED BY '\n'
      -> IGNORE 0 LINES
      -> (title, author, publisher, topic, comment, price);

  mysql> SELECT * FROM books;

  mysql> exit;
  
  %
 

Source :http://www.wbluhm.com/MySQLTut.html