MySQL Tutorial

Mysql is an Open Source Relational Database which supports SQL query. How data will be stored is decided by Mysql Engine. Mysql provides full flexibility while choosing Mysql engines. In Mysql, there are two most popular engines called MyISAM and INNODB. If we do not want transactional properties and we do not want to use row-level locking than we can use MyISAM. Data Insertion is faster in INNODB.

Note: Transactional properties mean atomicity, IF you visited to ATM and you initiated your transaction, so first you inserted ATM, your Password, how much you want to withdraw and finally it will return the money. So all the steps are captured if any steps failed all the steps consider as fail. And transaction will be rolled back.

Why do we need to learn MySQL?

There are many reasons why we need to learn MySQL some of important are given below:

Job Options: You can be a good Backend database engineer, because MySQL uses sql query which is common for any Relational Database like Oracle, SqlServer, etc.
Open Source: Since MySQL is an Open source, so you do not have to pay for using MySQL.
MySQL handles better Security: There are many access and roles are available and a MySQL super admin can grand various roles and can manage them. Because Super Admin can grant roles with the limited roles users can also perform limited work on database. It makes our database secure.
MySQL Supports: MySQL supports almost all platforms and operating systems like Windows, Linux,UNIX, macOS etc which makes it suitable for any kind of application.
MySQL performance: If we compare MySQL with other Relational Database like Oracle, Sybase, etc then we will see MySQL is a little faster with lower features.
MySQL is Scalable: In general MySQL support upto 4GM limit, however, this can also be upgraded up to 8TB to meet your needs.

Applications of MySQL

MySQL is mostly used by Web applications. Because it is an open-source and also many cloud-based servers like AWS charges very less to deploy MySQL on their server. Many small and medium startups are going with MySQL only. MySQL can also be used for ERP solutions as it provides Relational database so managing reports and analyzing data would be also very easy in MySQL.

Example

In the below example, we are creating a Table user in the database users. Please go through the example below along with the screen.

use users; //Switching to users database CREATE TABLE user (UserID int,UserlastName varchar(255),UserFirstName varchar(255),Address varchar(255),City varchar(255),age int );//designing schema and attributes of Person table. DESCRIBE user; //fetching previously created table structure

This image shows existing databases and selecting users database;

In the below image we are creating table name user.

In the below image, we are displaying details of the table which we created above

Prerequisites

To start with MySQL we do not require to learn any programming languages , UI tools provide various ways to create, insert and delete. If you have not heard about MySQL Workbench, then you should try it. It is a complete UI tool for MySQL.To start with MySQL, We should learn JOINS, insert, select, basics of data stored in table and about its attributes.

Target Audience

A Web Developer: A web developer is the one who gets the data from the end-user and stores data into MySQL into the required format. He can also fetch the data from MySQL and display it to end-users. Developers should learn more of its syntax of Like JOIN,AGGREGATE, SUM, ORDER BY, GROUP BY, etc commands because to show data to end-user they need to use all these components.

A Database Admin: A database admin is the one who creates all the roles on the particular database, for example, if the database name is users than for this database he will create various users like user1,user2,user2. And all these users will be granted different types of roles according to work they are going to perform the users database.