SQL-2

3. Inserting Data
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;

%
4. Querying Data (back to top)
Basic commands on mysql command line:
% mysql

mysql> USE wbluhm;

mysql> SELECT * FROM books;

mysql> SELECT title, price
-> FROM books
-> ;

mysql> SELECT title, author
-> FROM books
-> WHERE topic="Java";

mysql> SELECT title, author, price
-> FROM books
-> WHERE topic="Java"
-> ORDER BY price;

mysql> SELECT title, author, price
-> FROM books
-> WHERE topic="Java"
-> ORDER BY price DESC;

mysql> USE samp_db;

mysql> SHOW TABLES;

mysql> DESCRIBE president;

mysql> SELECT last_name, first_name, birth
-> FROM president
-> WHERE death IS NULL
-> ORDER BY last_name;

mysql> DESCRIBE student;

mysql> DESCRIBE absence;

mysql> SELECT student.name, absence.date
-> FROM student JOIN absence
-> WHERE student.student_id = absence.student_id;

mysql> exit;

%
Querying data with .sql scripts:

SQL script example: select_java_books.sql

SELECT title, author, price FROM books
WHERE topic="Java"
ORDER BY price;

% mysql wbluhm < select_java_books.sql

% mysql -t wbluhm < select_java_books.sql