If you want to see what databases you possess, use the following command:
MySQL> show databases; +---------- + | Database | +---------- + | JDOE | | MySQL | +---------- + 9 rows in set (0.01 sec)
To change/switch to a different database, use the following command
MySQL> use database_name; For example: MySQL> use JDOE; Database changed
2. Create tables in a database by executing the follow commands to MySQL database:
create table movie ( movie_id int(10) NOT NULL, title varchar(50) , genre varchar(20) , rating varchar(10), primary key (movie_id) ); create table customer ( customer_id int(10) NOT NULL, name varchar(50) , address varchar(50) , credit_card_number varchar(20), primary key (customer_id) );
To view tables in a database, use the following command,
MySQL> show tables; +-----------------+ | Tables_in_JDOE | +-----------------+ | customer | | movie | | rents | +-----------------+
To view the structure of a table in a database, use the following command,
MySQL> desc movie; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | movie_id | int(10) | NO | PRI | NULL | | | title | varchar(50) | YES | | NULL | | | genre | varchar(20) | YES | | NULL | | | rating | varchar(10) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
Now you can insert data records into the tables you just created using SQL insert statement. For example,
insert into movie values(101, 'Sixth Sense, The', 'thriller horror', 'PG-13'); insert into customer values(108, 'John Doe', '1201 Wesleyan St', '2435 4332 1567 3234');
Alternative way to create tables in a database
You may type the above two scripts in text files using a text editor such as notepad at your local PC and save them as create_movie.sql and create_customer.sql, respectively. Use ftp to transfer the files to the LINUX server. Execute sql scripts/commands in a file as follows:
MySQL database_name -u user_id -p < file_name
For example:
-sh-3.2$ MySQL JDOE -u jdoe -p < create_movie.sql Enter password: -sh-3.2$ MySQL JDOE -u jdoe -p < create_customer.sql Enter password:
After you finish with creating tables and inserting data entries, query your tables and make sure the data entries you inserted are the same as those in the textbook.
MySQL> select * from movie; +---------+--------------------+------------------+--------+ | movie_id | title | genre | rating | +---------+--------------------+------------------+--------+ | 101 | Sixth Sense, The | thriller horror | PG-13 | | 102 | Back to the Future | comedy adventure | PG | | 103 | Monsters, Inc. | animation comedy | G | | 104 | Field of Dreams | fantasy drama | PG | | 105 | Alien | sci-fi horror | R | | 106 | Unbreakable | thriller | PG-13 | | 107 | X-Men | action sci-fi | PG-13 | | 5022 | Elizabeth | drama period | R | | 5793 | Independence Day | action sci-fi | PG-13 | | 7442 | Platoon | action drama war | R | +---------+--------------------+------------------+--------+ 10 rows in set (0.00 sec) MySQL> select * from customer; +------------+---------------+-----------------------+------------------+ |customer_id | name | address |credit_card_number| +------------+---------------+-----------------------+------------------+ | 101 | Dennis Cook | 123 Main Street | 2736237123440382 | | 102 | Doug Nickle | 456 Second Ave | 7362748659573638 | | 103 | Randy Wolf | 789 Elm Street | 4253477362524436 | | 104 | Amy Stevens | 321 Yellow Brick Road | 9876543212345678 | | 105 | Robert Person | 654 Lois Lane | 1122334455667788 | | 106 | David Coggin | 987 Broadway | 8473968448473784 | | 107 | Susan Klaton | 345 Easy Street | 2435433215673232 | +------------+---------------+-----------------------+------------------+ 7 rows in set (0.00 sec)
mysql> select * from movie;
+----------+--------------------+------------------+--------+
| movie_id | title | genre | rating |
+----------+--------------------+------------------+--------+
| 101 | Sixth Sense, The | thriller horror | PG-13 |
| 102 | Back to the Future | comedy adventure | PG |
| 103 | Monsters, Inc. | animation comedy | G |
| 104 | Field of Dreams | fantasy drama | PG |
| 105 | Alien | sci-fi horror | R |
| 106 | Unbreakable | thriller | PG-13 |
| 107 | X-Men | action sci-fi | PG-13 |
| 5022 | Elizabeth | drama period | R |
| 5793 | Independence Day | action sci-fi | PG-13 |
| 7442 | Platoon | action drama war | R |
| 201 | Proposal | Drama | PG-13 |
+----------+--------------------+------------------+--------+
11 rows in set (0.03 sec)
mysql> select title, rating from movie where genre = 'action sci-fi';
+------------------+--------+
| title | rating |
+------------------+--------+
| X-Men | PG-13 |
| Independence Day | PG-13 |
+------------------+--------+
2 rows in set (0.00 sec)
mysql> select * from movie where genre like '%sci-fi%';
+----------+------------------+---------------+--------+
| movie_id | title | genre | rating |
+----------+------------------+---------------+--------+
| 105 | Alien | sci-fi horror | R |
| 107 | X-Men | action sci-fi | PG-13 |
| 5793 | Independence Day | action sci-fi | PG-13 |
+----------+------------------+---------------+--------+
3 rows in set (0.00 sec)
mysql> select * from movie where genre like '%sci-fi%' and rating = 'PG-13';
+----------+------------------+---------------+--------+
| movie_id | title | genre | rating |
+----------+------------------+---------------+--------+
| 107 | X-Men | action sci-fi | PG-13 |
| 5793 | Independence Day | action sci-fi | PG-13 |
+----------+------------------+---------------+--------+
2 rows in set (0.00 sec)
mysql> select * from movie where genre='thriller' or rating = 'PG-13';
+----------+------------------+-----------------+--------+
| movie_id | title | genre | rating |
+----------+------------------+-----------------+--------+
| 101 | Sixth Sense, The | thriller horror | PG-13 |
| 106 | Unbreakable | thriller | PG-13 |
| 107 | X-Men | action sci-fi | PG-13 |
| 5793 | Independence Day | action sci-fi | PG-13 |
| 201 | Proposal | Drama | PG-13 |
+----------+------------------+-----------------+--------+
5 rows in set (0.00 sec)
mysql> select * from customer
-> ;
+-------------+---------------+-----------------------+---------------------+
| customer_id | name | address | credit_card_number |
+-------------+---------------+-----------------------+---------------------+
| 101 | Dennis Cook | 123 Main Street | 2736 2371 2344 0382 |
| 102 | Doug Nickle | 456 Second Avenus | 7362 7486 5957 3638 |
| 103 | Randy Wolf | 789 Elm Street | 3254 3477 362524436 |
| 104 | Amy Stevens | 321 Yellow Brick Road | 4250 3477 362524436 |
| 105 | Robert Person | 654 Lois Lane | 1122 3344 5566 7788 |
| 106 | David Coggin | 987 Broadway | 8473 9687 4847 3784 |
| 107 | Susan Klaton | 345 Easy Street | 2435 4332 1567 3232 |
| 108 | John Doe | 1201 Wesleyan St | 2435 4332 1567 3234 |
+-------------+---------------+-----------------------+---------------------+
8 rows in set (0.00 sec)
mysql> update customer set credit_card_number = '3254 3477 362524436' where customer_id='103';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from customer;
+-------------+---------------+-----------------------+---------------------+
| customer_id | name | address | credit_card_number |
+-------------+---------------+-----------------------+---------------------+
| 101 | Dennis Cook | 123 Main Street | 2736 2371 2344 0382 |
| 102 | Doug Nickle | 456 Second Avenus | 7362 7486 5957 3638 |
| 103 | Randy Wolf | 789 Elm Street | 3254 3477 362524436 |
| 104 | Amy Stevens | 321 Yellow Brick Road | 4250 3477 362524436 |
| 105 | Robert Person | 654 Lois Lane | 1122 3344 5566 7788 |
| 106 | David Coggin | 987 Broadway | 8473 9687 4847 3784 |
| 107 | Susan Klaton | 345 Easy Street | 2435 4332 1567 3232 |
| 108 | John Doe | 1201 Wesleyan St | 2435 4332 1567 3234 |
+-------------+---------------+-----------------------+---------------------+
8 rows in set (0.00 sec)
mysql> delete from customer where customer_id='103';
Query OK, 1 row affected (0.00 sec)
mysql> select * from customer;
+-------------+---------------+-----------------------+---------------------+
| customer_id | name | address | credit_card_number |
+-------------+---------------+-----------------------+---------------------+
| 101 | Dennis Cook | 123 Main Street | 2736 2371 2344 0382 |
| 102 | Doug Nickle | 456 Second Avenus | 7362 7486 5957 3638 |
| 104 | Amy Stevens | 321 Yellow Brick Road | 4250 3477 362524436 |
| 105 | Robert Person | 654 Lois Lane | 1122 3344 5566 7788 |
| 106 | David Coggin | 987 Broadway | 8473 9687 4847 3784 |
| 107 | Susan Klaton | 345 Easy Street | 2435 4332 1567 3232 |
| 108 | John Doe | 1201 Wesleyan St | 2435 4332 1567 3234 |
+-------------+---------------+-----------------------+---------------------+
7 rows in set (0.00 sec)
mysql> insert into customer values(103, 'Randy Wolf', '789 Elm Street', '3254 3477 362524436');
Query OK, 1 row affected (0.00 sec)
mysql> select * from customer;
+-------------+---------------+-----------------------+---------------------+
| customer_id | name | address | credit_card_number |
+-------------+---------------+-----------------------+---------------------+
| 101 | Dennis Cook | 123 Main Street | 2736 2371 2344 0382 |
| 102 | Doug Nickle | 456 Second Avenus | 7362 7486 5957 3638 |
| 103 | Randy Wolf | 789 Elm Street | 3254 3477 362524436 |
| 104 | Amy Stevens | 321 Yellow Brick Road | 4250 3477 362524436 |
| 105 | Robert Person | 654 Lois Lane | 1122 3344 5566 7788 |
| 106 | David Coggin | 987 Broadway | 8473 9687 4847 3784 |
| 107 | Susan Klaton | 345 Easy Street | 2435 4332 1567 3232 |
| 108 | John Doe | 1201 Wesleyan St | 2435 4332 1567 3234 |
+-------------+---------------+-----------------------+---------------------+
8 rows in set (0.00 sec)