Forem: Fernanda Fernandes

Database for newbies: Hands On

Fernanda Fernandes — Tue, 26 Mar 2024 18:00:00 +0000

Knowing how to deal with databases are really important for every developer, so I will guide you through a tutorial on how to create one using MySQL on the terminal.

If you are a little bit lost when it comes to databases, I highly recommend you my first article of this series, where I explain the theory about databases. For now, lets get to the pratical part!

Hands on!
Preparing the environent
Creating our first database
- Datatypes and properties
CRUD
- Create
- Read
- Update
- Delete

Hands on!

There are many ways of creating a database, but I usually do it using MySQL on the terminal and DBeaver to visualize them when I need it. I decided to approach only MySQL on the terminal, specially because it is pretty simple to learn and very important to know the basics. Maybe one day I can show how DBeaver works. For now, I will show you how to deal with database through terminal, but remember - there are different ways of doing it.

Preparing the environment

First of all, you will have to install MySQL. I recommend you to install the MySQL Installer, which will have a lot of softwares to be downloaded, such as MySQL Workbench, MySQL Shell and many others. You should be able to download it on this link MySQL Installer and run the program. Then you should install MySQL Server, MySQL Workbench and MySQL Shell.

If you use other operational system than windows, or is having some issues you can ask me or try to take a look on some videos or articles more focused on the step-to-step installation.

After doing it, you will have to define the windows variable system. You should go on System's propriety>Advanced and then Environment variables

Find the system variable called "Path" and double click it. Then you should click on "New" and copy the MySQL server's path on it.

To test if it is working, open the terminal ( command prompt ) and typemysql. If an error saying that you have no access shows up, try te command mysql -h localhost -u root -p. It will ask the password and it will connect on MySQL.

Creating our first database

Now that we had set up our environment, we can work on the database! First of all, since we are going to do it using the terminal, you will have to open it and then connect on MySQL, which can be done with mysql command or mysql -h localhost -u root -p. You should be able to see something similar to the picture below.

Important: every MySQL command must finish with ";", and it is a good practice to write it on capital letters.

After that, it is important to acknowledge some important database commands, such as:

CREATE DATABASE <database-name>; : creates the database with the name specified on "database-name";
SHOW DATABASES; : shows the databases created;
DROP DATABASE <database-name> : delete the database informed and its values;
USE <database-name>: to select the database and be able to work on it.

Now the idea is to create a database named "store" and then a table named "products", which will have the fields id (the primary key), name, price and quantity.

To create this database we will use the command on the terminal:

CREATE DATABASE store;

After creating it, we will create the table named "products". We must select and enter the database first and then create the table with its fields, which will be id, name, price and quantity.

USE store; //Selecting the database
CREATE TABLE products
    ( id int auto_increment primary key,
      name varchar(40) NOT NULL, 
      price double(10,2) NOT NULL,
      quantity int NOT NULL
    );

Ok, now we have our database and our first table created! To visualize it we can use SHOW DATABASES; or SHOW TABLES; to see the tables created. We can also use DESCRIBE products; if we want to see the table's description.

Datatypes and Properties

Every time we create a table, we have to define the type of our data and some properties like if it is a primary key, a foreign key, if the data can be null, if it is unique... There are many properties and datatypes so We can use what will fit us the best.

One of the most common datatypes are:

Int : used for integer numbers, allows 4 bytes
Double: float number with double precision
Decimal(p,s) : used to store decimal numbers. "p" defines how many numbers will be allowed on the left side of the comma. "s" defines how many will be allowed on the right side of the comma.
Date : stores a date, which can be from 01/01/0001 to 12/31/9999.
Timestamp : stores an unique number that will be updated every time a table line is updated or created.
Varchar(n) : stores characters. "n" shows that we can define the amount of characters we are expecting to receive.

The properties are really important for our database, it will gives us more details. One of the most common ones are:

Not null : when applied to the data, means it can not be null, so it has to be given an value.
Auto_increment: mostly applied to primary keys, allows automatic generation of unique numerical values for a columns. Very used to create unique indentifiers.
Primary key: property used to define the data as primary key of the table
Foreign key : property used to define the data as foreign key of the table, creating a relationship between tables.

CRUD

After learning all of this about database, we can finally get to create a CRUD on the database. CRUD means create, read, update and delete, and it is really important for every developer, especially the back-end ones, to know how to do it.

In real life, CRUD is made using a programming language to get the data sent and store on the database. However, you need to know the SQL commands used to manipulate a database because even though you are going to use PHP, Java, Node or Python to do it, you probably will have to use the same SQL commands. After this article I will write another one creating a PHP application to show you how it works!

I highly recommend that you do these steps on your terminal to see how MySQL works! And, of course, feel free to try new things.

Create

To create or insert a data on our table we use the command INSERT INTO.

INSERT INTO products VALUES (null, 'Rice', 5, 10); //One way of doing it 
INSERT INTO products (name, price, quantity) VALUES ('Rice', 5, 10); //Another way of inserting data

In the first option is important to understand that the values informed must be at the same sequence of the table (id, name, price and quantity). The id can be null because it is an auto_increment data. And in the second option we inform which datas will be given. Also every time we insert a string, we have to use quotes.

Read

To read information of a table we use the command SELECT . There is some ways of selecting information.

SELECT * FROM products; //Example 1
SELECT * FROM products WHERE id = 1; //Example 2  
SELECT (name) FROM products WHERE quantity<5; //Example 3

We use ' * ' to say that we want to read all the fields - or columns - of the table, just like example 1. If we do not want it, we can inform which ones we want, like example 3.
We use the clause " WHERE " to filter the information we want to return. For example if we wanted to know which product holds the id equals to number 1, we would use the example 2. It is also possible to use the 'where' to return the products that has less than 5 quantities, just as in example 3. Except for specific situations, we shouldn't do queries without "WHERE".

Update

Is pretty usual to update the data registered, and we use the command UPDATE to do it. We can change the price of a product, for example, and we will use the "WHERE" in here too.

UPDATE products SET price=8, quantity=3 WHERE id=1;

In this example we are updating the product's price and quantity. Note that the WHERE is really important to define which register will be updated.

Delete

The delete operation, as you imagine, delete some register. The WHERE is really important in this type of query since if you do not use it, you will delete all the data from the table. So remember: Always use WHERE!

DELETE FROM products WHERE id=2;

And that's it!

I hope you've learned how to create a database and how to do CRUD operations on it! Remember that there are different ways of doing it, so feel free to research more about it.

If you have any questions you can send me a message! This content was created with my notes during the 100 days of code, in which I learned about database, PHP and Laravel.

And thanks to the people that reviewed my article and helped me to get here:

Database for newbies: Theorethical Approach

Fernanda Fernandes — Wed, 20 Mar 2024 19:28:31 +0000

Either if you have just started learning programming or if you already have some experience with it, I'm pretty sure you already asked yourself (or others) what a database really is, how you can deal with it, what is the difference between relational and non relational databases and what they are used for.

When it comes to programming, feels like our doubts are endless and sometimes we can feel lost in this sea of information. I will talk about some concepts that every beginner should know to create its first database. I hope to teach you at least something about database, one of the most important parts of the softwares.

What is database?
Types of database
- Relational
- Non-Relational
What is SQL?

What is database?

Before everything, it's important to understand what is data. Well, it is a value, like 21. But this data won't have a meaning unless we organize it, creating an information. For example, this number, all alone, could be an age, could be the total people that logged on your website or even your state number. Now if I say to you that we have the data: age = 21 , we can pretty much understand that the value 21 is someone's age. That's what we call information.

Knowing that, I can tell you that database is a collection of organized information. I like to imagine that databases are similar the process of moving to a new home. For example, when we are moving, it is normal to pack our things inside boxes. We would have a box for books, one for clothes and another for electronics. And in the end, we put everything inside a truck and take it to our new home.

In this example, our database would be the truck, because we have many boxes of specific things - a collection of organized information. This information is structured and normally stored in a computer system. (We use databases to store data, access and maintain it. )

To control a database, we are going to use what we call as Database Management System (DBMS), softwares used to visualize the database's tables, its information, and where we can also run queries (commands to do actions on the database) and many other things. It's basically a interface between the user - the person using the database - and the database itself.

We can say that every time we create, delete, update and read - and so much more - we will deal with a DBMS.

Examples of Database Management System:

MySQL
SQL Server
Oracle Database

There are so many options and we should choose the one that fit us and the project better.

Types of database

The most important types of database, the ones every beginner should know are:

Relational Database
Non-relational Database

Relational

This type of database is structured using tables with columns and rows, like the image below, and we use SQL to deal with this structure.
In a table, we will have:

Columns, which defines what every piece of data is (ID, Name, Age...) and its type;
Row, which holds the data given.

Every row in the table is a record with an exclusive ID, which we call as a key. In this table, for example, the key would be the numbers 1, 2, 3... We can see that every record has its own key, used to identify itself.

We have at least three types of keys that every beginner should acknowledge:

Key : it is basically one or more columns in a table that will be responsible for identifying in an exclusive way the rows.
Primary Key : this type will ensure uniqueness to the register, will refer the register on a relationship between tables and it is indexed, which increases the efficiency of our DBMS.
Foreign Key : it is a column that will kind of connect the tables using the primary key of each of them. We use this type of key to create relationships between tables. It is important to understand that the table that has the foreign key is called reference table and the one that is being referred is called referenced table or father table.

Non relational

Non relational database, also called as NoSQL (Not Only SQL, nowadays), are a type of database that don't use the table, and its columns and rows, as an organization scheme. It offers more flexibility because you won't have to worry about creating a table as we have to do on relational databases.

The storage on NoSQL is optimized and it adapts itself to fit every need. For example, it is possible to storage data as key/value, as JSON documents or as graphics.

NoSQL databases were created to solve the problem people were suffering until the late 90's, which was the difficulty to design databases that could cope with the scale and agility needed using the relational database, that wasn't able to do it at that time.

One of the most important features of NoSQL is:

Higher scalability
No complex relationship
Reduced costs

Non relational databases also have types, and two of them are:

Key Value: the most common one. Every item on the database will be stored as an attribute name (or 'key') with its value. For example: {"name": "Maria"}. The key is "name" and the value is "Maria". The most used databases in these cases are Redis, DynamoDB and Berkeley DB.
Document: stores each key with its document, and its principal feature is having all the information inside one document. This document normally is a JSON but it can also be a XML, for example. The most famous document database is MongoDB

There are a lot more information about this type of databases but this is the basics you have to know to start! Besides that, don't let this beginning stop you from digging deeper to learn more.

What is SQL?

SQL stands for Structured Query Language, a programming language used to work with databases created on the labs of IBM around the 70's. After a while, it became a pattern when it comes to data management.

We use this programming language to run queries on the database and we can retrieve, create or delete information from the database using the most various parameters. We just have to follow the syntax, just like any other programming language.

SQL has many commands but the most important ones are:

SELECT: search rows on the database table.
INSERT: insert new rows on the table.
UPDATE: used to update the data that already exists on the table.
DELETE: delete a register.

It is important to say that we use this commands with other parameters so we can make the queries very specific. And it is a convention to use SQL on Capslock.

SQL also has some conditions that we can add on our query in order to modify the registers that will be returned, like:

FROM: used to say what table will be consulted.
ORDER BY: used to organize the data returned in a specific order.
WHERE: used to specify the conditions of the data to be return. For example, if we want to see the users we have on our table that are older than 18 years old, we would use the 'where'.

There are many other commands in SQL but this is the very basic we need to know to create our first table and learn the commands to create our first CRUD, which stands for Create, Read, Update and Delete.

And that's it!

Through this article we learned the basics of database, like what it is, it's types and what is SQL. Now, we must learn how to create one, topic covered on the next article of this series.

This content was created based on my notes during the 100 days of code, in which I learned about database, PHP and Laravel, so I am really proud of it, since I knew almost nothing about it before starting the challenge. Also, feel free to ask me questions if needed!

And thanks to the people that reviewed my article and helped me to get here:

Forem: Fernanda Fernandes

Database for newbies: Hands On

Table of contents

Hands on!

Preparing the environment

Creating our first database

Datatypes and Properties

CRUD

Create

Read

Update

Delete

And that's it!

Database for newbies: Theorethical Approach

Table of contents

What is database?

Types of database

Relational

Non relational

What is SQL?

And that's it!