---
title: MySQL Notes
h1: MySQL Notes
description: Things I don't want to forget about MySQL.
author: JWRR
date: January 15, 2019
theme: a
...
[View markdown](mysql-notes.md)

{{ toc }}

<a name="create-mysql-database"></a>
## Create MySQL Database on hostgator

The following link walks through how to use Hostgator's CPanel Database
Wizard to create a MySQL database.  It also walks through how to create a
user account and password for the database (each database will have its own
user and it is NOT the same to your linux account.

<a href="http://jwrr.com/content/MantisBT-PHP-Based-Bug-Tracking#setup-mysql-database">
Create database with cpanel database wizard</a>

<a name="run-mysql-client"></a>
## Run MySQL client from command line

The following link walks through how to setup putty to access your Hostgator
account with SSH.

[Connect to Hostgator server with Putty](http://jwrr.com/content/Hostgator-SSH-with-PuTTY)

<a name="putty"></a>
## Start mysql client from the Putty command line

Hostgator has started the MySQL server program and you as a shared user can't
start/stop the server (as far as I know). But you can start the mysql client
from the command line.  Eventually you probably won't used the mysql command
line and you'll probably do what you need from php.

Start mysql client. **-u** is the your new username that you created when
you setup the database. **-p** just causes the mysql to ask for your password.

```
  mysql -u jwrr_dummy -p
```

You should see the mysql welcome screen and the mysql prompt.

<a name="show-mysql-databases"></a>
## Show MySQL Databases

Use the [show databases](http://dev.mysql.com/doc/refman/5.0/en/show-tables.html)
command to see what databases are available.  You should see your new database.
In this example you should see **jwrr_dummy**.

<b>Important Note:</b> The semicolon **;** is required at the end of **EVERY**
mysql command.  If you see the **->** prompt, just enter **;** .

```
mysql> show databases;


+--------------------+
| Database           |
+--------------------+
| information_schema |
| jwrr_dummy         |
+--------------------+
2 rows in set (0.08 sec)
```

<a name="select-mysql-database"></a>
## Select Your MySQL Database

The **use** command selects the database you want to use

```
use jwrr_dummy;
```

<a name="add-table-to-mysql-database"></a>
## Add MySQL Table to Database

Use the [Create Table](http://dev.mysql.com/doc/refman/5.0/en/create-table.html)
command to add a table to the database.

In this example a table is create that has two fields (columns).  The first
field is 'id' and it is a unique identifying key.  I made it of type **bigint**
but [mysql has many integer types](http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html).
The second field is the name and is a 32-character string.

```
  create table users (id bigint not null auto_increment primary key, name char(32));
```

<a name="add-column-to-mysql-table"></a>
## Add Column Field to Existing MySQL Table

The [Alter Table](http://dev.mysql.com/doc/refman/5.0/en/alter-table.html) lets
you add or delete columns from your MySQL table.

```
mysql> alter table users add (strength tinyint);

Current database: jwrr_dummy

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

<a name="show-columns-in-mysql-table"></a>
## Show MySQL Table Columns, Fields, Schema

The [Show Columns](http://dev.mysql.com/doc/refman/5.0/en/show-columns.html)

```
mysql> show columns from users;
Current database: jwrr_dummy

+----------+------------+------+-----+---------+----------------+
| Field    | Type       | Null | Key | Default | Extra          |
+----------+------------+------+-----+---------+----------------+
| id       | bigint(20) | NO   | PRI | NULL    | auto_increment |
| name     | char(32)   | YES  |     | NULL    |                |
| strength | tinyint(4) | YES  |     | NULL    |                |
+----------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
```

<a name="add-entry-to-mysql-table"></a>
## Insert Entry to MySQL Table

Use the [MySQL Insert Command](http://dev.mysql.com/doc/refman/5.5/en/insert.html)
to add entries into a table. The followng example adds one entry into the
table, and then adds multiple entries into the table.

```
  insert into users (name,strength) values ('george',5);

  insert into users (name,strength) values ('abe',6), ('teddy',7);
```

<a name="search-mysql-table"></a>
## Search MySQL Table and Select Columns and Rows

The [MySQL Select Command](http://dev.mysql.com/doc/refman/5.0/en/select.html)
is the main way to read the database and select the entries and fields of interest.
The following example selects all the columns and rows.

```
# Select all columns and rows
mysql> select * from users;
+----+--------+----------+
| id | name   | strength |
+----+--------+----------+
|  1 | george |        6 |
|  2 | abe    |        8 |
|  3 | teddy  |       13 |
+----+--------+----------+
3 rows in set (0.00 sec)
```

This example selects specific columns from all rows.

```
mysql> select name,strength from users;
+--------+----------+
| name   | strength |
+--------+----------+
| george |        6 |
| abe    |        8 |
| teddy  |       13 |
+--------+----------+
3 rows in set (0.00 sec)
```

The following selects all entries with strength less than or equal to 8, and returns just the names.

```
mysql> select name from users where strength<=8;
+--------+
| name   |
+--------+
| george |
| abe    |
+--------+
2 rows in set (0.00 sec)
```

<a name="search-two-mysql-tables"></a>
## Search Two MySQL tables and Combine the results

The following adds another table to the database. Let's call the
table 'teams'.

```
mysql> create table teams (id bigint not null auto_increment primary key, name char(32), color char(32));
Query OK, 0 rows affected (0.00 sec)
```

The following adds entries into the 'teams' table.

```
mysql> insert into teams (name,color) values ('orcs','blue'), ('elves','green'), ('dwarves','red'), ('wizards','blue');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
```

The following uses the **select** command to show how the 'teams' table looks.

```
mysql> select * from teams;

+----+---------+-------+
| id | name    | color |
+----+---------+-------+
|  1 | orcs    | blue  |
|  2 | elves   | green |
|  3 | dwarves | red   |
|  4 | wizards | blue  |
+----+---------+-------+
4 rows in set (0.00 sec)
```

The following uses **alter** to modify the **users** table to add a pointer
from the 'users' table to the 'teams' table

```
mysql> alter table users add (team integer);

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
```

Let's take a look to see that the pointer was added.

```
mysql> select * from users;
+----+--------+----------+------+
| id | name   | strength | team |
+----+--------+----------+------+
|  1 | george |        6 | NULL |
|  2 | abe    |        8 | NULL |
|  3 | teddy  |       13 | NULL |
+----+--------+----------+------+
3 rows in set (0.00 sec)
```

The following example uses **update** assigns a team to each user.

```
mysql> update users set team=1 where name='george';
mysql> update users set team=2 where name='abe';
mysql> update users set team=3 where name='teddy';
```

The following uses **insert** to add more users.

```
mysql> insert into users (name,strength,team) values('gandolf',2,4),('legalos',4,2),('gimli',16,3);
```

Now we%apos;re ready for some interesting stuff. First list every userd and
her/his associated team. Note that the user name is from the **users** table
and the team is from the **teams** table.

```
mysql> select users.name,teams.name from users,teams where users.team=teams.id;

+---------+---------+
| name    | name    |
+---------+---------+
| george  | orcs    |
| abe     | elves   |
| legalos | elves   |
| teddy   | dwarves |
| gimli   | dwarves |
| gandolf | wizards |
+---------+---------+
6 rows in set (0.00 sec)
```

Now find all users that have a team color of blue. Note, the orcs and wizards
were both assigned the color blue so both george the orc and gandolf the wizard

```
mysql> select users.name,teams.name from users,teams where users.team=teams.id and teams.color='blue';

+---------+---------+
| name    | name    |
+---------+---------+
| george  | orcs    |
| gandolf | wizards |
+---------+---------+
2 rows in set (0.00 sec)
```

<a name="mysql-php"></a>
## Using PHP to Access MySQL

<b><i>Note: The MySQL precedural links below are no longer recommended but
I&apos;ll bet they&apos;re used more than the recommended object-oriented
approach.  Here is good example of how to use the recommended
[object-oriented PHP-MySQL mysqli object](http://www.php.net/manual/en/mysqli.query.php).</i></b>

Using PHP to access MySQL is similar to what we just did using the mysql
client. The basic steps are:

* [Connect and Login to the MySQL Server (mysql_connect)](http://www.php.net/manual/en/function.mysql-connect.php)
* [Check for errors after each step (mysql_error)](http://www.php.net/manual/en/function.mysql-error.php)
* [Select the database to USE (mysql_select_db)](http://www.php.net/manual/en/function.mysql-select-db.php)
* [Build a SEARCH string and send it to the MySQL server (mysql_query)](http://www.php.net/manual/en/function.mysql-query.php)
* [Get the results from MySQL (mysql_fetch_array)](http://www.php.net/manual/en/function.mysql-fetch-array.php)
* [mysql_free_result](http://www.php.net/manual/en/function.mysql-free-result.php)
* [mysql_close](http://www.php.net/manual/en/function.mysql-close.php)

Here is a [PHP-MySQL example](http://www.php.net/manual/en/mysql.examples-basic.php)