Create a MySQL Database Table

Problem

Create a table in a MySQL database to store data.

tl;dr

Create a database:

mysql -u myusername -p
create database john-portfolio;
use john-portfolio;

Create a table in the database:

create table stocks (ID int not null primary key auto_increment, name varchar(20), ticker varchar(4), shares int(5), price decimal(5,2));

Add an entry to a table:

insert into stocks (id, name, ticker, shares, price) values (null, "Microsoft", "MSFT", "995", "46.24");

Show entries in your table:

select * from stocks;

Solution

Traditionally, individual data units were kept as individual entries in things such as ledgers or accounting books. Earliest forms of data management software worked the same way - you would have a block of text storing the information you would need. However, one of the biggest things computers grew as quickly as they did were their utility for storing and keeping track of large amounts of complex data.

With modern computer databases, not only can you easily store or look up any information you have entered, but you can also modify any or all entries at will. Individual entries can be grouped not only by an overaching category (i.e. "payments"), but also specific type (i.e. "purchases," "wages," "rent," or "repairs"). With non-computerized accounting books, you either had to look up payments and then the information you needed, or you had to make individual sub-categories for each specific type. In which case, you had to go through more effort to aggregate the total information.

Modern databases make information like this significantly easier to manage. MySQL, currently developed by Oracle, is the most popular database, with around 56% market share as of August 2014. In its most basic form, MySQL stores information in tables inside each database.

Create the table

For example, you may wish to create a database named john-portfolio, consisting of a portfolio for your client John, and in it, you wish to store all of the assets under your management.

To do so, log in to MySQL with your user credentials and create the new database:

mysql -u myusername -p
create database john-portfolio;
use john-portfolio;

Each command in MySQL is followed by a semicolon (";"). If you enter something but do not add a semi-colon at the end, MySQL will read it as a part of a single command. As such, Enter key is very similar to typing in a space, although the new text will be on the next line. If you accidentally hit enter, you can always escape the -> prompt by adding a semicolon and hitting enter again:

MySQL> crea
-> ;
MySQL>

After the database, you may wish to create a table specifically for stocks. Suppose you need to list the specific stocks, as well as the price you bought them at. Each row would be an individual transaction recording the purchase of X amount of shares of a company. As such, you would create columns for company name, ticker, number of shares, and price you bought them at. Note that MySQL commands are not case-sensitive, but table/database names and values of specific elements are case sensitive. You can create the table like this:

create table stocks (ID int not null primary key auto_increment, name varchar(20), ticker varchar(4), shares int(5), price decimal(5,2));

You can also use a line break (enter) after each element for easier entry and readability, such as:

create table stocks
(ID int not null primary key auto_increment,
name varchar(20),
ticker varchar(4),
shares int(5),
price decimal(5,2));

The "ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT" element is a self-incrementing counter. The first entry you make will have an ID of 1, the second entry will have an ID of 2, etc. If you delete entry with ID 5, you will have entries with IDs 4 and then 6, but no #5.

You can see details about your table via description stocks; command:

mysql> desc stocks;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| ticker | varchar(4) | YES | | NULL | |
| shares | int(5) | YES | | NULL | |
| price | decimal(5,2) | YES | | NULL | |
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Add an entry to the table

You can add an entry like this:

insert into stocks (id, name, ticker, shares, price) values (null, 'Microsoft', 'MSFT', '5', '46.24');

Null is a counter token and assigns a numeric value to each row in the table. It is extremely important to have so you can single out specific rows when you have several rows with identical entries. Also, make sure to use quote marks (' ' or " ") around each specified value in VALUES so MySQL can parse them. Leaving out quotes will lead to an error. I.e:

Either of these is good:

(null, 'Microsoft', 'MSFT', '995', '46.24')
(null, "Microsoft","MSFT","995","46.24")

This is bad and will give you an error:

mysql> insert into stocks (id, name, ticker, shares, price) values (null, Microsoft, MSFT, 5, 46.24);
ERROR 1054 (42S22): Unknown column 'Microsoft' in 'field list'

View your table

To see the entries in your table:

select * from stocks;

mysql> select * from stocks;
+----+-----------+--------+--------+--------+
| ID | name | ticker | shares | price |
+----+-----------+--------+--------+--------+
| 1 | Microsoft | MSFT | 995 | 46.24 |
| 2 | Amazon | AMZN | 30 | 290.74 |
| 3 | Google | GOOG | 71 | 508.08 |
+----+-----------+--------+--------+--------+
3 rows in set (0.00 sec)


Was this article helpful?

mood_bad Dislike 0
mood Like 2
visibility Views: 4456