In this article, We will explore the basics of MySQL query language for beginners to gain a better understanding of MySQL.

content:

• Introduction to SQL
• Data and Database
• Table
• SQL basic queries

Introduction to MySQL:

Modern society is extremely dependent on data, but in order to manage it effectively, one must be able to master data management. A database and data can only be manipulated if we know the language that this database understands, which SQL is.
As the core of relational databases, SQL is used by most companies around the world because of its enormous popularity.

What is SQL?

It is abbreviated as “structured query language”
It is the language used to perform actions as update, retreat, manipulate and store data on rational database.

History of SQL:

• SQL was developed at IBM by Donald D. Chamberline and Raymond F. Boyce in the early 1970s.
• It was initially called SEQUEL(Structured English Query Language)
• SQL is a powerful language that uses simple English sentences
• It uses common English words such as select, insert, update and many more to perform
• SQL is declarative language( when you write a query you have to tell what needs to be done
• And you don’t have to worry regarding the flow of query. It will be handled internally by DBMS using.

Features of SQL:

• SQL has well defined standards
• SQL is easy to learn
• With the help of SQL, one can create multiple views
• Probability of code in SQL is a prominent feature

What is Data and Database?

Data:

Data is a collection of facts, figures and values from different sources like as;
Transport, geographical, cultural, scientific, financial, statistical, natural etc.

Database:

"Database is allocation where data is stored in certain n formats".

Example:

An example is a library that contains a large number of books. Now imagine that the library is a database and these books are the data in it
Like as;

• firstly, data from user is processed
• Next, we converted into meaningful format
• Then stored it as schema & raw data

Types of Databases in MySQL:

1. Distributed database
2. Object oriented database
3. Centralized database
4. Operational database
5. Graph database
6. NoSQL Database
7. Cloud database
8. Rational database

MySQL Popular databases:

• Mongo DB
• Words press
• Microsoft axes
• Microsoft SQL Server
• MySQL
• ORACLE

How to create a database in MySQL?

MySQL:

Currently, SQL queries are executed against the MySQL database.
It might be a mystery to you why I use MySQL, but let me explain. It's a workbench database management system, whereas SQL is a language for communicating with databases. In other words, MySQL allows us to communicate with the database by using SQL queries.
Let me explain the first topic to you, which is how to create a database, in the following syntax:
create database edureka; (keywords for database creation)

I am using edureka as the name of my database management system. Click on the lightning icon after selecting this entire statement in MySQL. You can see at the bottom that the database with the name edureka has been created.

How to delete a database?

Following that, we will discuss how to delete a database with the following syntax;

Drop database edureka; (keywords for database creation)

Select this entire query and click on the lightning icon. So, we can see that database with name edureka has been deleted

Table:

Next we are going to discuss table. Well,

“table is a collection of data in a tabular form”
Table -----> class
Name

"Person" is the name of the table here. Tables must have names when they are created.

What are tuples?

“Single row of table which contain a single record”, for that relation here we have five tuples in our table.

What are attributes?

Features of an entity is called attributes.
And attribute has a name and a data type. Here are four attributes in this table.

Table constraints in MySQL:

Despite the fact that the table stores the data, the data needs to be of a certain format to ensure data integrity. The format of the data needs to be decided at the time of table creation. The constraint must be changed if we want to change it, and we have to delete the table and create a new one with the new constraints.

So, few of the constraints are following while creating a table in MySQL;

• 1. Check default
• 2. Foreign key
• 3. Index
• 4. Unique
• 5. Primary key
• 6. Not null

MySQL commands:

MySQL queries with examples are following:

create a table command in MySQL Workbench?

create table class(
classID int not null auto_increment,
fname varchar(20),
lname varchar(20),
address varchar(30),
city varchar(15),
marks int,
primary key(classID)
);

(keywords for the creation of table in MySQL)

So the syntax for select statement is;
Select * from student; (key words to display the whole table)

It is important to ensure that the table name is unique. In a database, there cannot be two tables with the same name. Within the table, we have declared the columns along with their data types. The query must be ended with a semicolon. By clicking the lightning icon, you can now execute this query. As a result, we can see that a table named class has been created.

And by executing this we can see the table on MySQL console as follows;

Where command in MySQL:

Filtering records is done with it. By using this clause, we can extract only records that meet the specified criteria.
Let's assume the following scenario to demonstrate the where clause. For example, if I want to display the names of students who are from Sargodha, then my "where" clause query will look like this;

select fname
from class
where city='sargodha';

(key words for where query clause)
Now, select this query and execute it by clicking on the lightning icon. So, this is now our out put.

AND Command in MySQL:

This operator displays a record if all the conditions separated by AND Are TRUE.
Now, let’s look at the syntax.
Select column from table where condition1 and condition 2 is true.

select * from student
where fname='yusra' and lname='liaqat';

(key words for AND statement)
And by executing this, I can get the output for the student name yusra liaqat.

OR Command in MySQL:

OR operator usually displays a record if any of the condition separated by OR is TRUE.
And the syntax for this is follow;
Select column from table where condition1 or condition 2 is true.

select * from student
where fname='yusra' or lname='liaqat';

(key words for OR statement)

NOT Command in MySQL:

This operator displays a record if the condition/conditions are NOT TRUE.
Like, I want to display the details of student who does not have the first name as zeeshan. To achieve this I am using OR operator;

Select * from class
Where not fname= ‘zeeeshan’; 

(key words for NOT statement)

INSERT INTO Command in MySQL:

In order to insert any new data into a table, we can use the INSERTY query.
The table name and columns must be specified for that. After that, we need to specify the values.
The syntax for this is as follows;

INSERT INTO  class(fname, lname, address, city, marks)
VALUES(‘ jia’,’nawaz’,#08 mg road’,’multan’,’389); 
Select * from class;

(key words for INSERT INTO statement)

MySQL Data Aggregation functions:

It is a function that groups the values of multiple rows based on certain criteria and writes a single value. We often use aggregate functions with group by and having clauses of the select statement so, we will be discussing them later part of the article.
Aggregate functions are following, so let’s discuss them one by one.

MySQL COUNT Command:

This function returns the number of rows that match by specified criteria. So the syntax is as follows;

select count (classID)
from class

; (key words count statement)

In the output we can see that the count of the student ID is 5

MySQL AVG Command:

This function returns the average value of a numeric column. So the syntax for this is as follows;

select avg (marks)
from class;  

(key words for AVG statement)

LET’S EXECUTE THIS STATEMENT BECAUSE I am trying to find the average marks of students. So average marks of the student is following;

MySQL SUM Command:

This function returns the total sum of a numeric column.
And the syntax for it is;

select sum(marks)
from class;

(key words for SUM statement)

And here, in this example I am trying to the total sum of total marks code by all the students. So, select this query and execute it by clicking on the lightning icon.

MySQL MIN Command:

This function returns the minimum value of the selected column.
And the syntax for this is follows;

select fname, lname, min(marks)
from class; 

(key words for SUM statement)

And here, in this example we are trying to calculate the minimum marks code by all the students. So, select this query and execute it by clicking on the lightning icon.

MySQL MAX Command:

This function returns the maximum value of the selected column.
And the syntax for it is as follows;

select fname, lname, max(marks)
from class;

(key words for SUM statement)

And here, in this example we are trying to calculate the maximum marks code by all the students. So, select this query and execute it by clicking on the lightning icon.

MySQL GROUP BY Command:

It is used in SQL to arrange identical data into groups with the help of some functions.
For instance, if the column in a table consists of similar data or values in different rows then we can use group by function to group the data and the syntax for this is as follows;

select count(classID), city
from class
group by city;

(key words for GROUP BY statement)

In this program, I am counting the number of students from different cities, and if there is a student from the same city, the count will be incremented at the end, and the output will have the city name along with the number of students from that city.
The count of students from different cities is 1, since the students come from different cities, so we can see how effective this query is if the data is large.

MySQL HAVING Command:

It is used to decide which group will be included in the final result set based on certain conditions.
Because the where keyword could not be used with aggregate functions like sum, count, min, max, and so on, SQL added the HAVING clause. The syntax for this is as follows;

Select fname, sum(marks)
From class
Group by fname
Having sum(marks)>400;

(key words for HAVING statement)

MySQL ORDER BY Command:

• Ascending or descending results are sorted using this keyword.
• By default, the order by keyword will sort the records ascending or descending.
• Let me now explain the syntax by selecting and executing these key words.
• The names of cities should be arranged in descending order based on their starting alphabets, for example. The same process will be repeated for asec order, except only the word will be exchanged with the desc.

Select count(classID), city
From class
Group by city
Order by city desc;

(key words for ORDER BY statement)

MySQL UPDATE command:

The update command is used to modify rows in a table.
Now let’s look at the syntax for update statement
Here, I want to change the name of student with student ID 1.

Update class
Set fname = ‘Amar’, lname = ‘Kumar’
Where classID = 1;

(key words for UPDATE statement)

MySQL DELETE command:

The SQL delete command is used to delete rows that are no longer required from the database tables

Delete from student
Where city = ‘sargodha’;

(key words for delete statement)

Conclusion:

We have covered all the basic concepts of MySQL in this article. Thanks for taking the time to read this article. I hope you found it interesting and informative. I hope you understand what MySQL data, a database, a table, and some basic SQL queries are. while executing these queries, I hope you guys have a lot of fun.
Please share your feedback with us as well.
Thank YOU!!