The only commands you need to handle databases in MySQL - MySQL Basic Commands

@jesalmofficial 62

over 1 year ago

StemSocial

Mastering Databases: The Basics

Shoutout to Udemy

In this article you'll find:

Introduction
How is Data Structured in MySQL?
Primary Keys and Foreign Keys
C.R.U.D
SELECT
UPDATE
DELETE

A lot of people see programming languages and queries as a Hydra with a thousand heads. You understand one, and then you have to fight the 999 remaining. However, It doesn't have to be like that, as the best way to learn is to just begin with the most important concepts and then venture into the world to get a hang out of the details.

That's precisely why, In this post, I'll talk about the most important MySQL commands so you can create your own databases and handle your tables like a real PRO. Who knows, this might help you land a great client in the future ;).

That being said. Let's begin!

How is Data Structured in MySQL?

Shoutout to Oracle DBA

To understand what we'll do with each command, we have to understand how the data in our database will be structured. When we're working with databases we'll hear the terms relational and non-relational.

You might ask what these two are. Well, relational is a term used to describe databases where the data is "stored" in a structured way, meaning in the form of tables. The data within these tables can be related to the data in other tables in what we know as dependencies.

In the case of non-relational databases, the data is more oriented towards documents, where the information is stored like you would write a to-do list.

Since we're using MySQL, we'll be working with Relational Databases. Looking deeper into the concept of tables, these store the information in columns and rows. Columns describe the fields that define the type of information that will be stored within them and the rows will be all the data we write within these fields.

As we can see in this Image, we'll see the columns (fields) that identify the Id of the customer, his/her first name, last name and the date on which the instruction was created. Each row will contain all the info of the user, while the cells will contain the type of data that the fields ask for.

But why the Id? You see, whenever we'll execute, update or delete data, we'll need to have one sure way to identify a specific row. Two people can have the same first and even second name. Also, more than a single order can be made at the same time. That's why Ids exist, to give us a unique way to inspect our information.

Now you know how a database table works, but there's more to know before we get to the instructions. Let's talk about Primary Keys and Foreign Keys

Primary Keys and Foreign Keys

Shoutout to Luna Modeler

Remember IDs and how they're unique? Well, they're primary keys. A primary key is a unique field that is used to reference the contents of one register (or row). The characteristics of a primary key are:

It is unique (There are NOT registers with the same primary key in a table).
It can't be empty (No field that's a primary key can be empty or Null).
It autoincrements (Whenever we create a new register, a new Id is created)

While Foreign Keys are certain fields that establish a link between two tables, Meaning that one column in a table can be related to a column in a different table. The main differences from the primary key are:

A foreign key can have repeated registers.
They can be null or empty.

And the main one: A foreign key refers to the field that is the primary key in the other table. Here's an image to reflect these differences:

Now that we have all these concepts into account, we can begin to make our queries in MySQL Workbench.

C.R.U.D => C.S.U.D

Shoutout to Quackit Tutorials

Sounds frightening, Huh? Well, nothing to fear. CRUD stands for the basic queries we realize with any table in an SQL database:

C for Create
R for Read
U for Update
D for Delete

Whenever we want to access and modify something in our code, we'll use one of these operations. The thing is, In MySQL not all of them are called like that. We have:

Create
Select
Update
Delete (For the contents of a table) and Drop (For databases and tables)

Which is what we'll use throughout our MySQL environment to interact with our databases, tables, columns and registers. First of all, we have to create a database. To do this we enter our SQL Editor and write the following instruction:

CREATE DATABASE IF NOT EXISTS your_database_name;

The create database part is pretty obvious. However, we use the conditional If Not Exists to verify If there is not a database with the same name. If There's not, the database will be created successfully. If there's already one, then we'll face an error.
Then, to access our database, we just use:

USE DATABASE your_database_name;

Once we have this, we'll have to create a table to insert the info. To do this, we'll enter the fields we'll use.

CREATE TABLE IF NOT EXISTS table_name(  
    column_definition1,  
    column_definition2,  
    ........,  
    table_constraints  
);

In a similar fashion to the way we created our database, we have all we need to create our table. We use the If not exists to verify If there are already tables with the same name. However, we don't want to create an empty table, that's why we'll have to define the fields It will have.

We do this by opening parentheses and using the following instructions with each field:

'name_of_the_field' VALUE_TYPE [CONDITIONS]

First, we put the name of the field and we design the value types. The most common are:

INT = Integer
VARCHAR(Max number of character) = A string with a limited number of characters
BOOLEAN = False or True

And at the end of this query, we have to define which field will be the Primary Key, an attribute usually reserved for the Id. To do this, we write:

PRIMARY KEY (field_for_primary_key)

And, If we have any foreign key, we repeat the same procedure, plus, an additional instruction:

FOREIGN KEY (field_for_foreign_key) REFERENCES table_being_referenced(primary_key_of_the_other_field)

With the first one, we identify what field of our current table we want to design as our foreign key, and with REFERENCES, we take the field in the other table that we want to make reference to by specifying first the name of the table.

One simple way to understand all this is to set an example. Let's say that we want to create a specific database. In this case, Its name will be practice_db. Then, we use:

CREATE DATABASE practice_db;

Now, we'll have created the database, but we still have to select It and create the fields, for this, we execute the following queries:

USE practice_db;

And for our first table, that we'll call user:

CREATE TABLE user (
    id int UNIQUE NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    age int not null,
    PRIMARY KEY (id)
);

Now, if you check your new table in your SQL handling software, you should see something like this:

But we also want to create a second table that has a foreign key for the user's id. We'll name it notes:

CREATE TABLE notes (
    id int UNIQUE NOT NULL AUTO_INCREMENT,
    title varchar(50) NOT NULL,
    content varchar(2000) not null,
    user_id int not null,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES user(id)
);

Now, we'll have created both our tables in our database. However, we still haven't introduced any data to them. For example, If we want to create a new register for our user table, we use:

insert into table_name (field_names) values (values_for_fields);

Note: in the field names, we don't put id, since It will fill Itself automatically every time we create a new register.

If we use this for our user table:

insert into user (name, age) values ('Dave', '25');
insert into user (name, age) values ('Jenny', '24');
insert into user (name, age) values ('Junior', '5');

Then, if we look back at our table, usin:

select * from user;

Note: * is used to reference everything. In this case, we want to select everything from the table.

We'll have:

Doing the same to create our notes:

insert into notes (title, content, user_id) values ('Title 1', 'Henry Ford', 3);
insert into notes (title, content, user_id) values ('Title 2', 'To-Do List', 2);
insert into notes (title, content, user_id) values ('Title 3', 'Music', 2);

SELECT * FROM notes;

We see:

Now we'd see what we can do with the commands create (To create databases and tables) and insert to put data into these.

However, we still have to take a look at the remaining instructions.

SELECT

Shoutout to SQLSPlus

Remember when we used select * to see every field in our table? Well, this is the main function of the command: To return a table with a determined amount of fields that come from our selected table. If we want to be more specific and select only a few fields from user, we use:

SELECT title, content FROM notes;

And we'll see, as we expect, only the title and content fields in notes. But this doesn't only work with columns, as we can also use It with registers:

SELECT * FROM notes where id=2;

We can even use certain conditions like:

SELECT title, content FROM notes where id>2;

Note: You can use certain methods like left join, right join and inner join, to see info from different tables at the same time. However, since this is a post teaching basics of MySQL, we won't discuss them.

UPDATE

Shoutout to C# Corner

To modify or update a register we have already written, we have to use the UPDATE instruction. This in the following manner:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Where we first specify the name of our table, then we use SET to locate the fields we want to change and add the new values. All of this will happen If a condition is met. However, in most cases, we use a user's id for convenience. An example of this:

If we want to change the value of the second register in our notes table. We just have to write:

UPDATE notes SET title='Productivity Methods' where id=2;

And if we select the table again, we'll see the change:

Note: If you have the "Safe Update" mode activated, whenever you use a field that isn't the Id for the condition, you'll find yourself with an error that says:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

However, It's recommended to use safe mode since an error in an update or delete method can render our tables useless if we deactivate It.

DELETE

Shoutout to Software Testing Help

The last of our CRUD methods. As we pointed out before, this is what we use If we want to delete something inside a Table. However, If we want to delete tables or databases, we'll use the instruction Drop.

If we want to erase a particular part of our table. Then we'll use:

DELETE FROM table_name WHERE condition;

or 

DELETE FROM table_name -- If you want to clear the whole table

And if we want to delete a table or db:

DROP DATABASE databasename;

or

DROP TABLE table_name;

For example, If we want to delete the first note from our notes table, we just have to type:

DELETE FROM notes WHERE id=1;

Then, we'll see:

Note: Beware. If you try to delete a register that has in It a primary key referenced by a foreign key, you'll get the following error:
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails

For example, If we tried to delete the user "Jenny" with the id 2, we wouldn't be able to, since she's being referenced in the notes table by user_id 2 times.

And If we want to drop our notes table entirely:

DROP TABLE notes;

And after deleting, we'll see that If we try to use select on It, we'll get:

Same if we delete our database after the DROP:

DROP DATABASE practice_db

Trying to use this database:

In this way, you'll be able to delete every aspect of your database.

After knowing how to use the basic commands of MySQL, you'll be able to control your databases in a really easy way, being able to create, select, update and delete data within them. Now you won't have any problem storing your info.

I hope this article could have been of use to you, providing you with the simplicity that SQL conveys when It's time to create databases. Use this to aid you in your programming journey. I'll be more than glad to hear about your progress. Thank you for your support and good luck!

@jesalmofficial.png