TeamIn this article, we are going to discover the most popular MySQL commands! MySQL commands help developers to make better CRUD applications. CRUD stands for Create, Read, Update, and Delete. For instance, when you visit a website, you will be able to:
- Register a profile (Create)
- Search products or services (Read)
- Add new information to your profile (Update)
- Delete your profile (Delete)
Almost every website contains these functionalities. Most importantly, when you perform each of these mentioned actions, you send a request to a database and require a response. For example, when you create a profile, you need feedback that your profile has been created. From that point of view, a database is an organized collection of data.
If you want to become a web developer and create CRUD web applications, these are the best free Java courses!
MySQL Database Management System
When a programmer writes an application, he or she needs to decide how to organize data. There is an option to store the data in memory (in-memory database), but this can be used only temporarily because after the program executes, all data will be deleted.
Therefore, we need a system that has the ability to organize the data efficiently in the long run! There is a solution and it is called Database Management System or DBMS. By using DBMS, we can separate the business logic of our application and data storage. In short, we get two new processes. Our application no longer needs to manage its data.
MySQL is a free relational database management system. Relational databases store and organize data into tables with rows and columns. Each table represents one entity.
For instance, one entity could be called “Country” and another “City”. Basically, we have two tables with countries and cities. If we want to establish relations between these two entities, we need something called a foreign key. On the other hand, a primary key is a unique value for each record in our table.
Most Common MySQL Data Types
There are different types of data that can be stored in a database. You can store numbers, strings, and dates. These are the most common MySQL data types:
- INTEGER – a standard integer
- BIGINT – a large integer
- DECIMAL – a fix-point number
- BIT – A bit-field
- CHAR – a fixed-length character string
- VARCHAR – a variable-length character string
- DATETIME – date and time in the format: YYYY-MM-DD, HH:MM:SS
- TEXT – a variable width character string
Structured Query Language
As we said earlier, the communication between an application and DBMS is standardized. We use Structured Query Language, in this case, MySQL commands to create, manipulate, and control data in a database.
Create a database
In the following example, we are going to create a database called – project. In this database, we will place our tables. To create a new database we use keywords CREATE and DATABASE and then write the name of our database.
create database project;
Use or Delete database
After we have created our database, we can use it or delete it. Before we start creating tables, we need to point out what database we are actually using.
use project;
If you don’t want to use this database anymore, delete it.
drop database project;
Create and Connect Tables
Finally, when we have the database, we are able to create entities. In this example, we are going to create two tables – country and city.
The first table is called country. It has an id, a name and a primary key. We set the id of this table as the primary key.
create table country (
id integer,
name varchar(255),
primary key(id)
);
The second table is called – city. It has the following fields: a city_id, name, country_id (the third column in the table in which we will place a reference to the id of a particular country), primary key, and foreign key which references country(id). Thanks to the foreign key, we will be able to connect a city with the correspondent country and place that number in the country_id column.
create table city (
city_id integer,
name varchar (255),
country_id integer,
primary key(city_id),
foreign key(country_id) references country(id)
);
Insert Values into Tables
We have created tables, but they are empty. We need to insert values into tables. Firstly, we insert countries.
insert into country values (1, ‘UK’);
insert into country values (2, ‘France’);
Secondly, we insert cities. Last but not least, we add the values of foreign keys. As you can see, the value of France’s primary key is 2. So if we want to connect Paris to France, we need to set 2 as the city’s foreign key.
insert into city values (1, ‘London’, 1);
insert into city values (2, ‘Paris’, 2);
