Untitled

Now, we can start creating our tables. Make sure you have already logged into your database before following along with this demo. See the Database Accounts guide for details.

Offices Table

Start by creating a offices table. Each office should have a unique id, which will be the primary key of the table. In this case, we will just automatically increment the id value every time we insert into the table. Copy/paste the following at the prompt:

CREATE TABLE offices (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  office CHAR(5) NOT NULL
);

You will see output similar to the snippet below. Notice that the mysql> text is the actual prompt, and the -> symbol indicates a multi-line command and appears automatically after you press Enter. You will also get a status message after each command. In this case, we see that the query was okay. If there was an issue, you’ll see an error instead.

mysql> CREATE TABLE offices (
    ->   id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   office CHAR(5) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

Verify that the offices table was created correctly with the SHOW TABLES and DESCRIBE statements. Below gives both the statements and the expected output, so make sure not to copy/paste the output into the mysql prompt:

SHOW TABLES;

• Open to View Expected Output

DESCRIBE offices;

• Open to View Expected Output

If all looks good, then we can start inserting values into our table. We can insert multiple values at once using the following syntax:

INSERT INTO offices
(office)
VALUES ('HPS'), ('SLE'), ('CASA'), ('CAPS');

To see all of the rows you entered into your table, use the SELECT statement:

SELECT * FROM offices;

• Open to View Expected Output

To sort the rows, use the ORDER BY clause:

SELECT * FROM offices ORDER BY office ASC;