Database Basics

To log in mysql database:

  1. Open PuTTY
  2. Type cscdevprod01.txwes.edu in the host name textbox and click Open to log in the server machine named cscdevprod01.
  3. Use the following command to connect MySQL database:
    mysql database_name -u user_id -p
    You need to substitute the database_name and username with your own.

For example:

mysql ZHANG -u yzhang -p

or

mysql -u yzhang -p
Enter password:
mysql> use ZHANG;

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 	|
+----------	+
| ZHANG    	|
| 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 ZHANG;

Database changed

To view tables in a database, use the following command,


mysql> show tables;
+-----------------+
| Tables_in_ZHANG |
+-----------------+
| 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 ZHANG -u yzhang -p < create_movie.sql
Enter password:

-sh-3.2$ mysql ZHANG -u yzhang -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 |
+---------+---------------+--------------+------+---------+-------+
|      53 | Pinot Noir    | Saintsbury   | 1997 |       6 |  2001 |
|      72 | Zinfandel     | Rafanelli    | 1995 |       2 |  2003 |
|      58 | Merlot        | Clos du Bois | 1994 |       9 |  2000 |
|      52 | Pinot Noir    | Dehlinger    | 1996 |       2 |  1998 |
|      43 | Cab Sauvignon | Windsor      | 1991 |      12 |  2000 |
+---------+---------------+--------------+------+---------+-------+
5 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)