I dived in to Python and Django became my go-to tool for the kind of projects I do (data-intensive). I decided at that time (late ’00s) that it would be nice to get to understand how I can work with the database, just in case my framework ORM needs help. So I came up with my personal learning exercises to become familiar with my database of choice then. I think that these series of articles will help the next lot of database ninjas.
MySQL
This article is adequate for an absolute beginner to begin to tinker with the MySQL database. If you do not have an installation on your localhost, get an installer here — Download MySQL Community Server.
Introduction
MySQL has an interesting story. Even with the purchase of MySQL by Oracle and the split that made MariaDB, MySQL is still the most popular Open Source Relational Database Management System (RDBMS) for Open Source software development.
MySQL 5 was an important release as it brought enterprise features such as Stored Procedures and Triggers into MySQL. Now with Big Data flying around all over the place, Relational Databases have simply had to be faster and more efficient. For ingesting lots of data to be sorted out later, NoSQL has been the answer. For data that is already structured, SQL is still the best way to go.
Today, unless one is building a “static” web site, a web developer will deal with databases at one point in time. A lot of frameworks and tools like SQLAlchemy abstract the database from the “middleware’, in this case, Python. For Frontend developers, once one is past HTML5/CSS3/ECMAScript 6 or the React /Vue “concepts” and enters into Rails(Ruby)/ Django(Python)Angular(JavaScript) web frameworks, a massive paradigm shift will occur. But in many cases, you may never write a line of SQL.
The Relational Database
A Database is a store for data, either as files or as URLs to where the actual data is are stored. In its basic form, a database can be a list of articles associated to each other by one or more attributes. For example, a list of men’s names and another list of dates.
A Relational Database (RDB) is where the list of men’s names can be related to the list of dates. Let the relation between the two lists be birthdays. The two lists would be put into a table as two columns and the table would called ‘birthdays’. This is table is a Relational Database. How one chooses to view the data from it; such as, how many men were born in April or when was John born, depends on the queries made.
MySQL is often the choice of web developers with database functionality needs. Other SQL databases such as PostgreSQL or Microsoft’s MSSQL Server may use slightly different query syntax, but the relational database design concepts shown will still apply.
This article assumes that MySQL 5+ is installed on the computer or server being used and the syntax used will work on your command line interface of choice.
Installation
I am on macOS. Once I followed all the instructions, I opened the Terminal to start up mysql. After a fresh install, the mysql command is buried in /usr/local/mysql/bin. Create an alias so that you can call the command easily. You can operate as root, though it is best practice to create a new user and login as the new user. I will assume that you know how and have logged in as a registered user and not as root. To get to know who to do that, check out the manual.
Previously, KEYWORDS were written in CAPITAL CASE letters. But now, keywords may be entered in any lettercase. So these are all the equivalent:
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;
Part 1: Designing an RDB using the Command Line Interface
I have picked specific names for my database and tables, and I encourage this practice as it improves security, how? It is harder for hackers to guess the names of the tables. Of course there must also be proper documentation and version control, oh yes, of the database.
I will use lower case letters to be in sync with CLI (Command Line Interface) commands. Let us see what we have for databases:
mysql> show databases;
+ — — — — — — — — — — +
| Database |
+ — — — — — — — — — — +
| information_schema |
| mysql |
| performance_schema |
| sys |
+ — — — — — — — — — — +
4 rows in set (0.00 sec)
These are the default databases used by mysql. In the console printouts for the show databases command, I will leave out these particular databases.
The Database: Waks Noma
Our database will hold the information of members of a web design company, the technologies they use, the projects they have singly or jointly worked on and the duration of the projects.
Pick any name for your database since the ones I have used might be abstract and make sense in a pidgin of Swahili known as Sheng’.
Let the design company be called Waks Noma. So we create a database for the company:
mysql> create database waks_noma;
Query OK, 1 row affected (0.00 sec)
To see that we successfully created the table:
mysql> show databases;
+ — — — — — — — — — — +
| Database |
+ — — — — — — — — — — +
| waks_noma |
+ — — — — — — — — — — +
i) Creating the Jobs Table: majobo
We need to work out what we will be setting up as the item of the database that every thing else will revolve around. This item is what we give a “primary id”. We are choosing between the designer or the job element. This is thinking through and in many cases drawing out is Database Schema Design. Schema design should be taken seriously at the onset of every database development.
It is good practice, to plan out the relationships between items in the database. In our case, we want to show off the jobs, and then the designers on these jobs. So we will give jobs the Primary Key.
Our first table consist of the jobs done by the company, Waks Noma.
mysql> use waks_noma;
And then we create the table ‘majobo’:
create table majobo (
job_id int not null auto_increment primary key,
job_name varchar(20) not null, job_desc text not null,
job_pic_url varchar(255) not null
);
We query the database to see the results,
mysql> show tables;
and we get:
+ — — — — — — — — — — -+
| Tables_in_waks_noma |
+ — — — — — — — — — — -+
| majobo |
+ — — — — — — — — — — -+
1 row in set (0.00 sec)
We check out whether the table is as we want it:
mysql> describe majobo;
We get:
+ — — — — — — + — — - — — —- + — — -+ — — — — - - -+ — — — — — — — +
| Field | Type | Null | Key | Default | Extra |
+ — — — — — - + — — — — — -— + — - -+ — - + — — - -+ — — — — — — — +
| job_id | int(11) | NO | PRI | NULL | auto_increment|
| job_name | varchar(20) | NO | | NULL | |
| job_desc | text | NO | | NULL | |
| job_pic_url | varchar(255) | NO | | NULL | |
+ — — — — — — -+ — — — — — — + — — — + — — -+ — — -+ — — — — — — — +
4 rows in set (0.02 sec)
We are in business!
NOTE: One can store images in a MySQL database, but it is not advisable for speed and stability reasons. Instead store the URLs to the images and other media as shown.
ii) Loading the table with Values
There are two ways to load the database with values:
- through the shell prompt
- through uploading data from a text file
This is not only restricted to inserting data but deleting data too through the shell prompt. For data loading through a file, one deletes all the previous entries and makes corrections in the file, and uploads the data again. If the data set entered through the file is large and one need to make a change on one record then that too can be done.
a) Insert and Delete through the Shell Prompt
In the real world, the shell prompt is rarely directly used unless the user is adding the odd entry or two. However as MySQL script files are written and run, these instructions would reside in a script file. We are styling the code as if it were in a script file.
/*** INSERT sample data into majobo ***/
insert into majobo (job_name, job_desc, job_pic_url)
values ('The Warehouse', 'Developing a comprehensive website that
will deliver on all fronts. Client-side was developed in ReactJS.
Server-side was developed Django REST API and incorporating some
custom modules', 'https://www.waksnoma.ke/thewarehouse/'
);
/*** end INSERT ***/
The result looks like this, formatted to fit into the page:
mysql> select * from majobo;
+ — — — — + — — — — — — — -+ — — — — — — — — -+ — — — — — — — — — +
| job_id | job_name | job_desc | job_pic_url |
+ — — — — + — — — — — — — -+ — — — — — — — — -+ — — — — — — — — — +
| 1 | The Warehouse | Developing a comprehensive website that will deliver on all fronts. Client-side was developed in ReactJS. Server-side was developed Django REST API and incorporating some custom modules | https://www.waksnoma.ke/thewarehouse/ |
+ — — — — + — — — — — — — -+ — — — — — — — — -+ — — — — — — — — — +
1 row in set (0.00 sec)
You will notice that we never put a value for the job_id. This shows that our auto_increment is working as it should. We add another set of data:
/*** INSERT sample data into majobo ***/
insert into majobo (job_name, job_desc, job_pic_url)
values ('aim to succeed','This was a graphic design poster job
that was to introduce the school open day. The client company has
a group of schools',
'https://www.waksnoma.ke/waks/aimingposter.jpg'
);
/*** end INSERT ***/
This gives us a table that has the following features:
mysql> select * from majobo;
+ - - - -+ - - - - - - - -+ - - - - - - - + - - - - - - - - - - - -+
| job_id | job_name | job_desc | job_pic_url |
+ - - - -+ - - - - - - - -+ - - - - - - - + - - - - - - - - - - - -+
| 1 | The Warehouse | Developing a comprehensive website that will deliver on all fronts. Client-side was developed in ReactJS. Server-side was developed Django REST API and incorporating some custom modules | https://www.waksnoma.ke/thewarehouse/ |
| 2 | aim to succeed | This was a graphic design poster job that was to introduce the school open day. the client company has a group of schools | https://www.waksnoma.ke/waks/aimingposter.jpg |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
2 rows in set (0.00 sec)
Question: What if we want to put in content data that,
especially in the description, has an apostrophe, ' ?
Let's take the above example to illustrate this.
/*** INSERT sample data into majobo ***/
insert into majobo (job_name, job_desc, job_pic_url)
values ("Aim to Succeed","This was a graphic design poster job that
was to introduce the school's open day. The client company has a
group of schools","https://www.waksnoma.ke/waks/aimingposter.jpg"
);
/*** end INSERT ***/
Note however instead of using the single quotation marks to isolate the data into strings, double quotation marks are used instead. This way the description will allow the “… school’s open day …” to enter into the database without issues. So our database now has:
mysql> select * from majobo;
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
| job_id | job_name | job_desc | job_pic_url |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
| 1 | The Warehouse | Developing a comprehensive website that will deliver on all fronts. Client-side was developed in ReactJS. Server-side was developed Django REST API and incorporating some custom modules | https://www.waksnoma.ke/thewarehouse/ |
| 2 | aim to succeed | This was a graphic design poster job that was to introduce the school open day. the client company has a group of schools | https://www.waksnoma.ke/waks/aimingposter.jpg |
| 3 | Aim to Succeed | This was a graphic design poster job that was to introduce the school's open day. The client company has a group of schools | https://www.waksnoma.ke/waks/aimingposter.jpg |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
3 rows in set (0.00 sec)
NOTE: Double quotation marks work just as well as single quotation marks in defining strings in the data. This is true in a number of programming and scripting languages.
Sadly, in the majobo table, there are two entries that look similar and they need to be removed. We will remove entry id=2 and id=3.
mysql> delete from majobo where job_name = ‘Aim to Succeed’ order by job_id limit 2;
Result:
mysql> select * from majobo;
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
| job_id | job_name | job_desc | job_pic_url |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
| 1 | The Warehouse | Developing a comprehensive website that will deliver on all fronts. Client-side was developed in ReactJS. Server-side was developed Django REST API and incorporating some custom modules | https://www.waksnoma.ke/thewarehouse/ |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
To clear the entire table, the command is:
mysql> delete from majobo;
Checking the table:
mysql> select * from majobo;
Empty set (0.00 sec)
Resetting the AUTO_INCREMENT
Since we have erased all the data from the table, we need to reset our auto_increment in order for us to have data integrity.
mysql> alter table majobo auto_increment = 0;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
b) Insert data from a File
The data to be put into majobo can be loaded from a file in either a remote location or from the local drive.
Here is a view of our text file with in plain text with tab separated values.
The Warehouse The client is an art and entertainment facilitator. The web space was required to showcase the various groups that they are involved with. It is a multimedia site with blogs and comments. https://www.waksnoma.ke/waks/thewarehouse/
Aim to Succeed This was a graphic design poster job that was to introduce the school open day. The client company has a group of schools and the poster was to be put up in selected shopping centres and malls. https://www.waksnoma.ke/waks/aimingposter.jpg
Oiling Life The client is an established petroleum company that needed a company brochure of all their lubricating products. The brochure was to be given to the company's service stations and car speciality shops. https://www.waksnoma.ke/waks/oilinglife.pdf
Dying to Give The main piece was a video documentary on the state of organ donation in East Africa and what needs to be done to make sure that the awareness is raised of this selfless giving. The client is a major cardiological hospital. https://www.waksnoma.ke/waks/dyingtogive.mov
Depending on where the file is on your local drive, our instructions to load it are:
mysql> load data local infile ‘[absolute_path]/mawaks.txt’ into table majobo;
Query OK, 4 rows affected, 4 warnings (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
Our table now looks like this:
mysql> select * from majobo;
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
| job_id | job_name | job_desc | job_pic_url |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
| 1 | The Warehouse | The client is an art and entertainment facilitator. The web space was required to showcase the various groups that they are involved with. It is a multimedia site with blogs and comments. | https://www.waksnoma.ke/waks/thewarehouse/ |
| 2 | Aim to Succeed | This was a graphic design poster job that was to introduce the school open day. The client company has a group of schools and the poster was to be put up in selected shopping centers and malls. |
https://www.waksnoma.ke/waks/aimingposter.jpg |
| 3 | Oiling Life | The client is an established petroleum company that needed a company brochure of all their lubricating products. The brochure was to be given to the company's service stations and car speciality shops. |
https://www.waksnoma.ke/waks/oilinglife.pdf |
| 4 | Dying to Give | The main piece was a video documentary on the state of organ donation in East Africa and what needs to be done to make sure that the awareness is raised of this selfless giving. The client is a major cardiological hospital. | https://www.waksnoma.ke/waks/dyingtogive.mov |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
4 rows in set (0.00 sec)
Going back to our assignment, let us lay out our table in a way we can see its design.
mysql> describe majobo;
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
| Field | Type | Null | Key | Default | Extra |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
| job_id | int(11) | NO | PRI | NULL | auto_increment |
| job_name | varchar(20) | NO | | NULL | |
| job_desc | text | NO | | NULL | |
| job_pic_url | varchar(255) | NO | | NULL | |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
4 rows in set (0.24 sec)
Let us draw a graphical representation of majobo, we get:
The diagram shown above is an Entity Relationship Diagram. Database ERDs are used to show relationships between tables in database as well as table structures. Because ERDs help visualize table structures and relationships, they often lead us to better database designs.
Conclusion
We have built a database and in the process used the most important queries in MySQL, namely, CREATE, USE, DESCRIBE, INSERT, SELECT, DELETE and ALTER.
I will publish the next part - Designing a database with MySQL using a Command Line Interface — Part 2 in a few days.
This article was first published on Medium.com
Top comments (0)