Learning MySQL with basic SQL Commands

Introduction:

MySQL is an open-source relational database management system. MySQL is free and open-source. MySQL is ideal for both small and large applications.

To interact with MySQL database or MySQL server, we have to learn Structure Query Language.

Installation:

But before we start here is the documentation link from where installation of MySQL and its components:

The Awesome Stuff:

There are different types of Commands in SQL:
1] Data Definition Language (DDL) Commands
2] Data Manipulation Language (DML) Commands
3] Data Control Language (DCL) Commands
4] Transaction Control Language (TCL) Commands
5] Data Query Language (DQL) Commands

DATA DEFINITION LANGUAGE (DDL) Commands:

When we want to define the structure of the table or database which we create, DDL Commands come in handy.

CREATE Command:

It is very necessary to create a database first before going ahead to create tables.

CREATE DATABASE databaseName;

After the creation of the database, we have to tell the MySQL Command Line Client that we are going to work on the above-created database. We can do so like this:

USE databaseName;

Now, we can start MySQL Learning Journey.

MySQL keeps data in the form of tables. There can be more than one table in a database. We query for the data through the tables. Most of the commands work on the table or tables we create in the database.
So to create a table, we can do this:

CREATE TABLE Detail(Column Names Datatype.....);

We can have any number of columns and specifying the data type of that column is very necessary.
The data types used in MySQL, shown here in this link:
Data types in MySQL Documentation

DROP Command:

Sometimes we feel that we have created a table that is not necessary as in its existence can be justified with any other table.
Then we have used DROP Command.

DROP TABLE TableName;

Also, we can delete the whole database.

DROP DATABASE databaseName;

Though it is highly unlikely that we have to delete a database with records in it but it comes in very handy when we have to delete a table. Remember, if the table is connected to another table with a foreign key, first we have to delete that table to which it is connected. After deleting that table we can then safely delete the current table (to be deleted). Otherwise, it will produce an error.

ALTER Command:

Now, to change our table contents or any column data type, we can use ALTER Command. We can use ALTER for not only modifying purposes but also can be used to add a column inside the table like this:

ALTER TABLE tableName ADD columnName ColumnDefination/s;
ALTER TABLE tableName MODIFY ColumnDefination/s;
example:
ALTER TABLE details ADD (Address VARCHAR(50));
ALTER TABLE details MODIFY (Name VARCHAR(30));

TRUNCATE Command:

In MySQL, the data inserted into the tables are in the form of rows. These records can be inserted as well as deleted.
So, to delete all records from the table, we can use truncate in this way:

TRUNCATE TABLE tableName;

But use TRUNCATE carefully as it even frees up the space which was taken by the records.

With the TRUNCATE command, we have completed the data definition language (DDL) commands.

DATA MANIPULATION LANGUAGE (DML) COMMANDS:

DML commands are used to make changes in the data in the database.
But there is a catch. DML commands are not auto-committed i.e. aren't auto-saved as they can be ROLLBACK too.

INSERT command:

The command we all are most anxious to use is the INSERT command. It answers the Question:
How to insert a record into the table?

INSERT INTO tableName(Col1,Col2,Col3,....,Coln) VALUES(Val1, Val2, Val3,....,Valn);

example:

INSERT INTO detail(Name,Rollnumber,City,Country) VALUES('Siddhesh Shankar',19070122168,'Mumbai','India')('John Jones', 1907134444,'Amsterdam','Netherlands');

UPDATE Command:

The command is used to change the existing records in the table. Any records which are not correct can cause a lot of problems while querying. Hence as soon as the wrong record is been put into the database, it needs to be rectified immediately.

UPDATE tableName SET ColumnName1 = Val1, ColumnName2 = value2, ..... ColumnName n = valn WHERE condition;

Example:

UPDATE details SET Name = 'Roger Kook', City= 'Riga' WHERE ID= 1;

Here, the WHERE clause condition is very important. Without the correct condition, we can end up making a bigger mistake in the database which can cause more problems.

DELETE Command:

Now, after seeing the UPDATE as well as the TRUNCATE command, I was thinking that is there any command which is user-friendly or understands that a user might want to delete only some rows instead of the whole table records.

Yes, there is a command called as DELETE command.
Again the WHERE Clause Condition is super important.

DELETE FROM TableName WHERE Condition;

DATA CONTROL LANGUAGE (DCL) Commands:

DCL is used to grant and take back authority from any database user. In other words, we can fiddle around with the rights, permissions a user has for the specific database.

COMMIT Command:

COMMIT command is used to save all the changes, creations we have done with the database.

COMMIT;

example:

DELETE FROM details WHERE ID = 1; COMMIT;

ROLLBACK Command:

This command is related to TRUNCATE Command as the TRUNCATE command can be ROLLBACK or can be undone. So, ROLLBACK can undo only those changes that aren't saved yet.

ROLLBACK;

example:

DELETE FROM details WHERE ID = 1; ROLLBACK;

SAVEPOINT Command:

We can create a save point which means that we can save the state of our changes so that if in the future we want to ROLLBACK to that spot, we can do so easily.
It is like gaming where we can save our level before going for the next mission because if we lose in the mission ahead, we can start from the last save point/load point instead of going around the whole game once again.

SAVEPOINT savepointName;

DATA QUERY LANGUAGE (DQL) Command:

Here it gets super exciting as we get to work through the records in the database via querying.
There is only SELECT Command but there are loads of variations with different clauses which can be used with it.

If we want to get all the records in a specific table:
SELECT * FROM TableName;

But sometimes we need to get records specific to what the use cases are. Here comes the use of variations or clauses. Some of them are:

LIKE Clause:

LIKE is used when we want to perform pattern matching.
SELECT * FROM tableName WHERE columnName LIKE 'Condition';

Here the condition is having the utmost importance as according to that, the pattern matching takes place. It uses wildcards like % and _.

Here are some examples of how LIKE Clause can be used:

SELECT * FROM details WHERE Name LIKE 'a%';
This shows all the records in the table details whose names start with 'a'.

SELECT * FROM details WHERE Name LIKE '%s';
This shows all the records in the table details whose names end with s.

SELECT * FROM details WHERE Name LIKE '_t%';
This shows all the records in the table details whose names have 't' in the second position.

Lastly,
SELECT * FROM details WHERE Name LIKE 't___%';
This shows all the records in the table details whose names have 't' in the start and the length of the name is at least 3 characters.

With the use of wildcards, we can get the records from the table easily.

ORDERBY Clause:

The ORDERBY Clause helps a lot when we want the records to be in an ordered format.
Ascending or descending order gives a lot of information. For example, if we want the maximum of the price column where all the prices are stored. We can either use the maximum function in SQL i.e. MAX(), but we can also display the whole set of records with the highest in the first spot.

SELECT * FROM TableName ORDERBY col1,col2,...,coln ASC;
This is for the ascending order format.

SELECT * FROM TableName ORDERBY col1,col2,...,coln DESC;

For example:
SELECT name, prices, id FROM details ORDERBY prices DESC;