Seidenberg Documentation

  1. Connect to vulcan with your supplied username and password.

  2. At the command prompt ($), type:

   mysql -p
  1. Enter your password again at the prompt.

(note: you were supplied a username and password for vulcan by your professor, this username and password was used to create an account on vulcan and an account in mysql. If you changed your vulcan password, the mysql password is still set to the initial password you were given by your professor. The point is that there are two different account databases — one for sshing to vulcan, the other for connecting to mysql).

You should have a prompt that looks like this: mysql>

  1. At the mysql> prompt, type:
   show databases;

The case (show databases or SHOW DATABASES or even Show Databases) does not matter for mysql commands. Although all mysql commands should end with a semicolon (;).

The command should return something that looks like this:

   mysql> show databases;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | test               |
   | testuser           |
   +--------------------+
   3 rows in set (0.00 sec)
  1. Select your database (kind of like changing a directory on a unix shell) with the “use” command: use testuser;
   mysql> use testuser;
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A

   Database changed

Note that your database names may be different (you’ll usually have a database with your username in it).

  1. By default, blank databases are created at account creation time, so there won’t be any tables to show. If there were, you could enter: show tables;
   mysql> show tables;
   +--------------------+
   | Tables_in_testuser |
   +--------------------+
   | Cart               |
   | Customer           |
   | DVD                |
   | Greatest_Yanks     |
   ...
   | survey             |
   | tables_priv        |
   | test               |
   | testac             |
   | user               |
   +--------------------+
   21 rows in set (0.00 sec)

And you would see all of the tables in the database “testuser”. To get more information about the individual table, try running: describe stock;

   mysql> describe stock;
   +----------+---------+------+-----+---------+-------+
   | Field    | Type    | Null | Key | Default | Extra |
   +----------+---------+------+-----+---------+-------+
   | prodid   | char(4) | NO   | PRI |         |       |
   | depid    | char(4) | NO   | PRI |         |       |
   | quantity | int(11) | YES  |     | NULL    |       |
   +----------+---------+------+-----+---------+-------+
   3 rows in set (0.00 sec)

This shows the structure of the table (note: I’m not quite sure about the student’s knowledge of relational databases and if they know what this means).

To actually show the data in the table, try issuing this command: select * from stock;

   mysql> select * from stock;
   +--------+-------+----------+
   | prodid | depid | quantity |
   +--------+-------+----------+
   | p1     | d1    |     1000 |
   | p1     | d2    |     -100 |
   | p1     | d4    |     1200 |
   | p2     | d4    |     1500 |
   | p2     | d1    |     -400 |
   | p2     | d2    |     2000 |
   | p3     | d1    |     3000 |
   +--------+-------+----------+
   7 rows in set (0.14 sec)

Sometimes your database will contain much more information than this (perhaps that simple select query will run for days). You can use the limit keyword to limit the amount of data returned from a query: select * from stock limit 5;

   mysql> select * from stock limit 5;
   +--------+-------+----------+
   | prodid | depid | quantity |
   +--------+-------+----------+
   | p1     | d1    |     1000 |
   | p1     | d2    |     -100 |
   | p1     | d4    |     1200 |
   | p2     | d4    |     1500 |
   | p2     | d1    |     -400 |
   +--------+-------+----------+
   5 rows in set (0.00 sec)

I know absolutely nothing about the purpose of the “stock” table, but what if we wanted to actually do something with this data, like sum the quantity column?

First, we need to get just the quantity column. We can do this by replacing the * in our previous query with the column name (we’re also removing the limit, since we want to see all of the quantities): select quantity from stock;

   mysql> select quantity from stock;
   +----------+
   | quantity |
   +----------+
   |     1000 |
   |     -100 |
   |     1200 |
   |     1500 |
   |     -400 |
   |     2000 |
   |     3000 |
   +----------+
   7 rows in set (0.00 sec)

Now SQL has a number of built in functions to manuipulate data. One of them is sum(). So we can issue that last query (just hit your up arrow to bring it back, then move your cursor back to quantity), but with sum(quantity): select sum(quantity) from stock;

   mysql> select sum(quantity) from stock;
   +---------------+
   | sum(quantity) |
   +---------------+
   |          8200 |
   +---------------+
   1 row in set (0.03 sec)

There’s a whole lot more that SQL can do, but that’s enough for now.

  1. To quit, type quit at the mysql> prompt.

Last edited, November 24, 2014 by treivik, pc=1.2.0-dev