MySQL Basic Commands With Example

April 8, 2022

Mysql Basic Commands are commands that must be known by a server or website admin, because these commands will definitely be used in making a system. If you want to learn Database, especially MySQL Server, you can use this documentation as a learning medium. The basic MySQL commands are like Create Database, Create Table, Insert, Select, Update, Delete. Here we will add how to create a user for MySQL with some specifics such as being able to be accessed by several networks or IPs only.

MySQL Basic Commands are simple and very easy to understand, so MySQL has become a very popular database system used among personal, corporate and agency. In addition, MySQL is widely used because it has a pretty good performance to handle from small to enterprise scale. Therefore it is suitable for beginners, students or for software developers.

Terms and Conditions

Intention
One MySQL Server with Root Access.
RAM 2 GB
HDD 40 GB

Step 1: Setting Up Database Server (Mysql Basic Commands)

MySQL Basic Commands. For server installation you can follow our previous Documentation on Database Installation with Debian 10. In this documentation we have provided installation steps, security and how to access the database.

mysql -u root -p

After that, enter the password

Step 2: MySQL Create Database Command (Mysql Basic Commands)

MySQL basic commands to create a database. Here we create a database with the name “dixmata”, you can replace it with the database name you want.

CREATE DATABASE dixmata;

If you want to see the results of the database that has been created, use the following command:

SHOW DATABASES;

Step 3: Create Table Commands (Mysql Basic Commands)

Before creating a table, you must first enter into the database that was created previously. How to enter the database with the following command use nama_database;

use dixmata;

If you have successfully entered the database, then we can create tables in the database. Here’s an example of the basic command we used to create a table, with the table name Books

CREATE TABLE Buku
(
Nim int,
Nama varchar (20),
Kode_Jurusan char(10),
Alamat text
);

To view the tables in the database use the command:

show tables;

And if you want to see the table structure use the command

DESCRIBE Buku;

Step 4: Insert Data (Mysql Basic Commands)

The Insert command is used to add data to the table. Follow the following command to enter data into the field that was created earlier

INSERT INTO Buku (Nim, Nama, Kode_Jurusan, Alamat)
VALUES ('140234', 'Dixmata Labs', 'A23', 'Payakumbuh');

See the results use the following command

SELECT * FROM Buku;

Create Multi data

If you want to add two data or two rows at once

INSERT INTO Buku (Nim, Nama, Kode_Jurusan, Alamat) VALUES
 ('140235', 'SebariKode', 'A24', 'Pariaman'), 
 ('140236', 'Ayongoding', 'A25', 'Gaduik');

Step 5: Update Data (Mysql Basic Commands)

Update is used to update data or modify data that has been created. follow the following command

UPDATE Buku
SET Nama = 'Achmad Ikhbal', Alamat= 'Perumanas Bonai Indah'
WHERE NIM = 140234;

And the result is that the previous data has been successfully updated or modified.

Step 6: Delete Data

Delete command is used to delete data

Perintah Delete Satu Data

For the Delete command, use a condition marked by using the WHERE command

DELETE FROM Buku WHERE Nama= 'Achmad Ikhbal';

Command Delete All Data

DELETE FROM Buku;

Step 7 : Drop

The drop command is used to delete a table or database. Here is the command

Drop Table

This command will delete the table in the database

DROP TABLE Buku;

Drop Database

This command is useful for deleting Database

DROP DATABASE dixmata;

Step 8 : Adding User (Mysql Basic Commands)

User is used for authentication if you want to enter the database, User is provided with a password. In making this user there are several ways, which are very influential in the security of a database. You can create your own user with several roles, for example you can create a user with a role that can only read or view, without being able to add or delete the contents of the database.

For this MySQL Basic Command Documentation, we only share the basics of user creation for small and medium scale web or application developers. The documentation for creating this user is like creating a user with access restrictions, namely Localhost, IP Address, or Network/Subnet. For clarity, let’s go into the documentation.

Creating a User with Localhost Access (Mysql Basic Commands)

This user can only login only on localhost or on the server itself. So database security is guaranteed, because no user or external access will be able to enter the database.

CREATE USER 'user_database'@'localhost' identified by 'Password';
GRANT ALL PRIVILEGES ON nama_database.* TO 'user_database'@'localhost';
flush privileges;

Creating User with IP-Address Access (Mysql Basic Commands)

For this user, it is almost the same as localhost, this user only logs into the database if he uses the IP-Address that we input at the time of user creation. In this documentation we use the IP 192.168.10.10, so if you want to log into the database you must use that IP.

CREATE USER 'user_database'@'192.168.10.10/255.255.255.0' identified by 'password';
GRANT ALL PRIVILEGES ON nama_database.* TO 'user_database'@'192.168.10.10/255.255.255.0';
flush privileges;

Creating User with Subnet Access

This user creation is the same as before, only this user can login with the Subnet that we created at the time of user creation, and of course there are more access points to login to the database.

CREATE USER 'user_database'@'192.168.10.0/255.255.255.0' identified by 'password';
GRANT ALL PRIVILEGES ON nama_database.* TO 'user_database'@'192.168.10.0/255.255.255.0';
flush privileges;

How to Login with User

mysql -u user_database -p

When using this command, you will be confirmed with the password that was entered at the time of user creation.

Viewing Users and Hosts

select user, host from mysql.user;

How to delete a user

To delete a user, we must know the user and host names that were previously created, the user and host names can be seen by using the command select user, host from mysql.user;

In the list above we will delete the user ‘user database’ with host ‘192.168.10.0/255.255.255.0’. Then the order

DROP USER 'user_database'@'192.168.10.10/255.255.255.0';

Conclusion

Thank you for following our Documentation, these are the Basics that need to be known for beginners in Database introduction. For advanced levels we will discuss in the next Documentation. Please for criticism and suggestions so that our website can be even better.

Source : MySQL Basic Command | Dixmata Labs