To log in mysql database:
mysql database_name -u username -pYou need to substitute the database_name and username with your own.
For example:
mysql JDOE -u jdoe -p
or
mysql -u jdoe -p Enter password: mysql> use JDOE;
You might need to change password of your database. To change your MySQL password, use the following command:
mysql> set password = password("your new password");
If you want to see what databases you can view, 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
To view tables in a database, use the following command,
mysql> show tables; +-----------------+ | Tables_in_JDOE | +-----------------+ | customer | | movie | | rents | +-----------------+
The following are example scripts used to create the tables: celllar, movie and customer. Create those tables in a database by executing the follow commands to mysql database:
DROP TABLE IF EXISTS cellar; CREATE TABLE cellar ( bin_num int(10) NOT NULL, wine char(20) default NULL, producer char(20) default NULL, year int(4) default NULL, bottles int(4) default NULL, ready int(4) default NULL, PRIMARY KEY (bin_num) ); 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) );
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_cellar.sql Enter password: -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 they were created correctly.
INSERT INTO CELLAR (BIN_NUM, WINE, PRODUCER, YEAR, BOTTLES, READY) VALUES (53, 'Pinot Noir', 'Saintsbury', 1997, 6, 2001); mysql> select * from cellar; +---------+----------------+--------------+------+---------+-------+ | bin_num | wine | producer | year | bottles | ready | +---------+----------------+--------------+------+---------+-------+ | 2 | Chardonnay | Buena Vista | 2001 | 1 | 2003 | | 3 | Chardonnay | Geyeser Peak | 2001 | 5 | 2003 | | 6 | Chardonnay | Simi | 2000 | 4 | 2002 | | 12 | Joh Riesling | Jekel | 2002 | 1 | 2003 | | 21 | Fume Blanc | Ch St. Jean | 2001 | 4 | 2003 | | 22 | Fume Blanc | Robt Mondavi | 2000 | 2 | 2002 | | 30 | Gewurztraminer | Ch St. Jean | 2002 | 3 | 2003 | | 43 | Cab Sauvignon | Windsor | 1995 | 12 | 2004 | | 45 | Cab Sauvignon | Geyeser Peak | 1998 | 12 | 2006 | | 48 | Cab Sauvignon | Robt Mondavi | 1997 | 12 | 2008 | | 50 | Pinot Noir | Gary Farrell | 2000 | 3 | 2003 | | 51 | Pinot Noir | Fetzer | 1997 | 3 | 2004 | | 52 | Pinot Noir | Dehlinger | 1999 | 2 | 2002 | | 58 | Merlot | Clos du Bois | 1998 | 9 | 2004 | | 64 | Zinfandel | Cline | 1998 | 9 | 2007 | | 72 | Zinfandel | Rafanelli | 1999 | 2 | 2007 | +---------+----------------+--------------+------+---------+-------+ 16 rows in set (0.00 sec) Which wines are ready in 2003? mysql> select * from cellar where ready = 2003; +---------+----------------+--------------+------+---------+-------+ | bin_num | wine | producer | year | bottles | ready | +---------+----------------+--------------+------+---------+-------+ | 2 | Chardonnay | Buena Vista | 2001 | 1 | 2003 | | 3 | Chardonnay | Geyeser Peak | 2001 | 5 | 2003 | | 12 | Joh Riesling | Jekel | 2002 | 1 | 2003 | | 21 | Fume Blanc | Ch St. Jean | 2001 | 4 | 2003 | | 30 | Gewurztraminer | Ch St. Jean | 2002 | 3 | 2003 | | 50 | Pinot Noir | Gary Farrell | 2000 | 3 | 2003 | +---------+----------------+--------------+------+---------+-------+ 6 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 | +---------+--------------------+------------------+--------+ 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)