Connecting to a Database Account

Accessing MySQL

On our UNIX systems in the Computer Systems Lab at Jefferson, the MySQL server is running on the Linux host known as threat.tjhsst.edu which is also our web server. The student system administrators have designed the database server so that users must have a password protected account in which they can create tables of data for use in their applications. If you desire an account for MySQL, request one from one of the student system administrators.

In the examples used here, we will assume that there is a user who has an account called "games". We will also assume that the password on that account is "One2Three". In order to connect to the database from another system in the lab, the user does not have to be on threat, but must type a request for mysql identifying the host, the user's account, and the desire to enter a password. The request looks like this:

mysql -h threat.tjhsst.edu -u games -p

The server on threat will request that the user to enter the password for that account, and if everything is correct, the prompt will change to:

mysql>

At that time, the person will have entered an interactive mode with the database.

As an alternative, the user can even supply the password at time of database request by using the following approach:

mysql --host=threat.tjhsst.edu --user=games --password="One2Three"

Note the use of the "double: minus sign when the name of the command line argument is spelled out, and also that the password must be enclosed in quotes with no spaces used in the assignment to that variable.

Connecting to the Database

Although the user had to have an account to access MySQL, there is a secondary action that requires that the user connect to a database.

What are the Databases?
To see active databases on the system, type:

mysql> SHOW DATABASES;

Note that commands in the interative mode must be terminated by a semicolon (;). The MySQL commands are NOT case sensitive, although database names and variables used in the other commands are sensitive to upper and lowercase letters. After the command is issued, the MySQL server will reply to the command by printing a table similar to the following:
```
+----------+
| Database |
+----------+
| dhyatt   |
| games    |
| guidance |
| intranet |
| test     |
+----------+
5 rows in set (0.0 sec)
```
Connect to an Existing Database
Currently, there are five active databases on the server, one of which we are interested in, the database called games. To connect to that database, type:

mysql> CONNECT games;

The system will respond:
```
Connection id:    5494
Current database: games
```

Show Available Tables in the Database
To show what tables are currently being used in the database, type:

mysql> SHOW TABLES;

The system will respond:

+-----------------+
| Tables in games |
+-----------------+
| images          |
| scores          |
| temp            |
+-----------------+
3 rows in set (0.00 sec)

Show the Contents of a Table
To show what all the fields are and what values they currently have, type the command:

mysql> SELECT * FROM scores;

The system will respond:

+---------+------+
| Name    | Num  |
+---------+------+
| Phyllis |  987 |
| Randy   | 1285 |
| Don     |  919 |
| Mark    |    0 |
| Mary    |  567 |
| Bob     |   23 |
| Pete    |  456 |
| Sally   |  333 |
+---------+-----+
8 rows in set (0.00 sec)

Identifying Problems
The only two problems that generally occur in interactive mode are when a command is mistyped and cannot be executed, or the semicolon is left out. In the first case, the system will make it very obvious that an error was made:
- Making a Syntax Error in the Command:
  For instance, if the user types:
  
  mysql>SELECT * IN scores;
  
  The system will respond:
  
  ERROR 1064: You have an error in your SQL syntax near 'IN scores' at line 1
  
  The problem is that the command should say "FOR scores" and not " IN scores".
- Leaving out the Semicolon
  If the user doesn't finish the command by forgetting the semicolon as in the following example:
  
  mysql>SELECT * FROM scores
  
  The server will give no reply but will type an arrow made out of a minus sign and the greater than symbol. The user must type in a semicolon in order for the command to be completed.
```
    ->
    -> ; 
```
  At that point, the command will be executed just as before. As you will see in other examples later on, the ability to enter long queries that span multiple lines will be very useful.
Quitting MySQL
To leave the MySQL environment, just type quit.