Howto – A short MySQL tutorial with examples – 01
Following on my previous article on databases and database planning, I have decided to add a quick practical tour of a MySQL database. MySQL and MariaDB are equivalent for the purpose of this article as they both use the exact same syntax. We will assume that you have access to a MySQL/MariaDB server and have sufficient rights to create and edit a database. The syntax convention is to use CAPITALS for MySQL commands and lowercase for data, labels or database/table names. All commands are terminated with a semi-colon(;).
Setting up our database
Log in to the database from a terminal session so that you may execute SQL commands or “queries” directly and do the following:
Create our test database called “testdb” by typing “CREATE DATABASE testdb;” and then press ENTER.
CREATE DATABASE testdb; Query OK, 1 row affected (0.05 sec)
Then nominate the database for use with “USE testdb;”.
USE testdb; Database changed
Now we need to create our tables to contain the data. We must specify the table name and each column name. The columns also need specifications such as data type to be stored in the column as well as the length or possible length of the data. We will also add an AUTO-INCREMENT column and a UNIQUE KEY.
CREATE TABLE clients (idx MEDIUMINT NOT NULL AUTO_INCREMENT, fname CHAR(30) NOT NULL, lname CHAR(45) NOT NULL, phone CHAR(25) NOT NULL, PRIMARY KEY (idx), UNIQUE (lname,fname) ); Query OK, 0 rows affected (0.37 sec)
This will create an empty table that does not allow duplicate entries for the last-name, first-name combination. Next, we will create the table for personal details.
CREATE TABLE clientdetails (idxp MEDIUMINT NOT NULL, home VARCHAR(75), work VARCHAR(30), occup VARCHAR(25), id VARCHAR(20), bank VARCHAR(30), bankinfo VARCHAR(75), incomecat INT(1) ); Query OK, 0 rows affected (0.07 sec)
Now that we have completed the table to contain client information, we can add the second last table for our client vehicles.
CREATE TABLE clientdetails (idxp MEDIUMINT NOT NULL, home VARCHAR(75), work VARCHAR(30), occup VARCHAR(25), id VARCHAR(20), bank VARCHAR(30), bankinfo VARCHAR(75), incomecat INT(1) ); Query OK, 0 rows affected (0.07 sec)
Our last table is for the income categories of our clients.
CREATE TABLE catdef (incomecat INT(2) NOT NULL, bracket INT(10) ); Query OK, 0 rows affected (0.06 sec)
We now have four tables. Once we have added all the data, they will look similar to this. Only the table names will be different:
Now we get to the interesting part.
Adding data to your MySQL database
First show your database tables to make sure all are there:
SHOW TABLES; +------------------+ | Tables_in_testdb | +------------------+ | catdef | | clientdetails | | clients | | clientvehicles | +------------------+ 4 rows in set (0.00 sec)
Let us populate the table clients first.
INSERT INTO clients (fname,lname,phone)
VALUES
('Joe','Smith','5552846'),
('Susan','Harris','6549920'),
('Peter','Jones','7920122');
Query OK, 3 rows affected (0.10 sec)
Records: 3 Duplicates: 0 Warnings: 0
Now we can do a “SELECT” statement to display the data we have just entered.
SELECT * FROM clients; +-----+-------+--------+---------+ | idx | fname | lname | phone | +-----+-------+--------+---------+ | 1 | Joe | Smith | 5552846 | | 2 | Susan | Harris | 6549920 | | 3 | Peter | Jones | 7920122 | +-----+-------+--------+---------+ 3 rows in set (0.03 sec)
I will now provide the lines that you can copy and paste to create the rest of the data we need.
Table 2 – Client Information:
INSERT INTO clientdetails
VALUES
('1','3 Halfmoon cresc, Mountainview','Woolworths','Manager','5801021234086','NationalBank','acc 321543123','3'),
('2','241 Honey Ave, Hibiscus Heights','Masons','Accountant','7510214321082','SecurityHold','acc 876576545','2'),
('3','84 Hamilton Cl, Cyprus Canyon','Shell','Salesman','8011229876087','MoneSafe','acc 102210123','2');
Table 3 – Client Vehicle Info
INSERT INTO clientvehicles
VALUES
('1','Jaguar','XF','2015','12598','CA 223344','2018-04-11','2017-01-15','6521','Oil & Filters','None'),
('2','Ford','Mondeo','2013','52344','CF 123212','2017-11-23','2015-12-03','34565','Oil, Filters & Plugs','Left rear tyre bald patch'),
('3','Mini','Cooper S','2010','87433','CY 982212','2018-01-15','2017-01-12','70211','Oil, Filters & Plugs','LF CV Joint Boot worn');
Table 4 – Income Categories
INSERT INTO catdef
VALUES
('1','10000'),
('2','20000'),
('3','30000'),
('4','40000');
Now that we have completed the data input, we can query each table to check. We have already queried the table “clients”. We shall now query the other tables.
SELECT * FROM clientdetails; +------+---------------------------------+------------+------------+---------------+--------------+---------------+-----------+ | idxp | home | work | occup | id | bank | bankinfo | incomecat | +------+---------------------------------+------------+------------+---------------+--------------+---------------+-----------+ | 1 | 3 Halfmoon cresc, Mountainview | Woolworths | Manager | 5801021234086 | NationalBank | acc 321543123 | 3 | | 2 | 241 Honey Ave, Hibiscus Heights | Masons | Accountant | 7510214321082 | SecurityHold | acc 876576545 | 2 | | 3 | 84 Hamilton Cl, Cyprus Canyon | Shell | Salesman | 8011229876087 | MoneSafe | acc 102210123 | 2 | +------+---------------------------------+------------+------------+---------------+--------------+---------------+-----------+ 3 rows in set (0.00 sec)
SELECT * FROM clientvehicles; +------+--------+----------+------+---------+-----------+-------------+-------------+-------------+----------------------+---------------------------+ | idxv | make | model | year | mileage | reg | servicedate | lastservice | lastmileage | workdone | notes | +------+--------+----------+------+---------+-----------+-------------+-------------+-------------+----------------------+---------------------------+ | 1 | Jaguar | XF | 2015 | 12598 | CA 223344 | 2018-04-11 | 2017-01-15 | 6521 | Oil & Filters | None | | 2 | Ford | Mondeo | 2013 | 52344 | CF 123212 | 2017-11-23 | 2015-12-03 | 34565 | Oil, Filters & Plugs | Left rear tyre bald patch | | 3 | Mini | Cooper S | 2010 | 87433 | CY 982212 | 2018-01-15 | 2017-01-12 | 70211 | Oil, Filters & Plugs | LF CV Joint Boot worn | +------+--------+----------+------+---------+-----------+-------------+-------------+-------------+----------------------+---------------------------+ 3 rows in set (0.00 sec)
SELECT * FROM catdef; +-----------+---------+ | incomecat | bracket | +-----------+---------+ | 1 | 10000 | | 2 | 20000 | | 3 | 30000 | | 4 | 40000 | +-----------+---------+ 4 rows in set (0.00 sec)
In our next article, we will learn how to write queries against the above data.
Happy Hosting!
