MySQL , MySQL Server , MySQL Workbench , SQL Commands

MySQL

Relational Database Management System

MySQL Server | MySQL Workbench | SQL Commands

MySQL RDBMS

MySQL is a open source ( Free ) and most popular Relational Database Management System ( RDBMS ) . MySQL is the most preferred choice for RDBMS that provides relational database functionality to the websites and web based applications.

MySQL with its proven track record of  performance , reliability , scalability and ease-of-use  has become  default  choice  for  some of the largest  web applications such as Facebook and YouTube.

This tutorial is a comprehensive resource and specially designed to give you a quick start to MySQL . This tutorial has been drafted in a most simplified language  and will guide logically step by step in My SQL database programming.

MySQL

Table Of Contents

What Is MySQL ?

The  database  is  a  crucial  component of  most  software applications  which are designed  to  store the  information about  some real world business entities such as  employee database , customer database ,  student database  and so on .

Most of these software application are web applications which are based on the client server architecture.

The database is also an important feature for most of the websites . All this data is stored on the web server and therefore we need a web server based RDBMS to create and manage the database.

The MySQL  database is one of the most popular choice for in web based applications for  providing database functionality to these applications such as data storage on web servers , data retrieval from web server  and  other database functionality provided by any RDBMS.

MySQL Relational Database Management System

MySQL Offers very powerful features  that  are available for absolutely free .  MySQL  database is open source  ,  that means   the user has access to the source code under license   that  can  be modified as per  needs .

Oracle also offers a paid  enterprise version of My SQL , which offers some additional plug-ins , to augment the existing functionality .

My SQL , which was originally conceived by the Swedish company My SQL AB which  was acquired by Sun Microsystems  in  2008 . Oracle acquired and  bought Sun Microsystems in 2010.

However , the web developers can still use My SQL under the GNU General Public License ( GPL )  but the enterprises version is a paid version and user has to  obtain a commercial license from Oracle.

What Is A RDBMS ?

The Relational Database is a database that is based on the relational database model . The Relational Database Model was  proposed and  developed by E.F. Codd  who was an English computer scientist  while he was working with  IBM .

The Relational Database  Management System ( RDBMS ) is a database management system ( DBMS ) that allows the creation , management and administration of relational databases.

The Relational Databases are  the most commonly used databases in the industry today. Some  of the  most popular DBMS being used  are  based  on the relational model  . The most popular and widely used RDBMS  includes  MySQL , Oracle   MS SQL server , Mango DB , Maria DB And there are many more.

RDBMS , MySQL
RDBMS , Commonly Used DBMS , Database Management System

Despite the advent of new database technology such as NOSQL and object Oriented databases , the relational databases are still extensively being used in the industry  and RDBMS usage is staidly growing. MySQL is the most popular and widely used RDBMS in the world.

MySQL RDBMS Functions

MySQL with its proven track record of  performance , reliability , scalability and ease of use  has become  default  choice  for  the web developers to provide database functionality for web sites and web applications .

MySQL Functions

MySQL Advantages

12 Major MySQL Advantages

MySQL is the most popular RDBMS and  offers many advantages as compared to its competitors . Some of these advantages include :

  • Ease Of Use
  • Robust Data Security
  • Database Scalability
  • High Performance
  • Multi Platform Support
  • Very High Up-time
  • Flexibility Of Open Source
  • Stable Memory Management.
  • Comprehensive Transaction Support
  • Workflow Administration And Control
  • Reduced Operations Cost
  • Multi Development Interface Support

What Is Relational Database ?

The relational databases implements the  relational database model  and conforms to the set of rules defined by E F Codd for relational database.

The relational database is logically organized  as  group of inter related tables . Each table can have multiple rows and columns . A table is a collection  of  records and  represents a database entity .

Each row in a table represents a record ( Tuple / Row ) which consist of number of data fields . The  columns  in a table  represents  an  attribute  for  an  entity . The intersection of row and column is referred as field which actually hols the data.

RDBMS Relational DB Structure
RDBMS Relational DB Table Example

In relational  database , an entity  is a real world object or business concept that needs to be represented into the database.  An database entity is represented  by a two  dimensional  table.

Depending upon the functional dependencies that exists between various data elements , the relationship between the two tables (  Entities ) is established by using the database keys . The database keys are defined to create association between tables by using  primary key and foreign key .

MySQL Download And Installation

You can download and install MySQL Sever community version which is a free version . However there are two approaches to go about My SQL installation .

The first approach is to download and install the standalone version of MySQL Server and the second approach is to download and install a web server solution stack in which My SQL is bundled as RDBMS  .

If you are a web developer and if you wish to use your local machine ( either desktop Or laptop computer )  for developing a website or a web application  then you will need a web server solution stack .

How Website Works , MySQL RDBMS
WAMP , LAMP XAMPP Web Server Stack

MySQL Download And Installation

Web Sever Stack

WAMP Stack | LAMP Stack | XAMPP Stack

A web server solution stack is a bundle of four components which provide a web server run-time environment on your local machine . These components include web server software , RDBMS and server side programming language .

Some of the most commonly used web server stack include WAMP , LAMP and XAMPP . Your local computer will effectively function as a web server once the web server stack is downloaded and installed on your machine.

Web Server Stack , WAMP , MySQL Download

For example the web server stack WAMP will install Apache web server , My SQL RDBMS and Php server side programming language.

The Web server stack will allow you to first  design and develop any website on your local computer during the development stage . Once the website is ready  then you can upload on the live web server with the help of any FTP program such as Filezilla.

MySQL Download And Installation

MySQL Installer

My SQL can also be installed as a single standalone product . If you wish to download and install only My SQL as a single product then follow the following steps :

STEP - 1

To download MySQL Installer , Google search “ MySQL Download” . Select the first link which will take you the MySQL Download Page .

MySQL Installer Step 1

STEP - 2

Select the Download  section and scroll down to find MySQL Community Edition ( GPL )  download link . This is a free version of MySQL Server available for non commercial use .

MySQL Installer Step 2

STEP - 3

Select the Download  section and scroll down to find MySQL Community Edition ( GPL )  download link . This is a free version of MySQL Server available for non commercial use .

MySQL Installer Step 3

STEP - 4

Now , select the operating system from drop down list being used on machine you wish to install My SQL . Once the operating system is selected  then select the MySQL Installer MSI download link.

MySQL Installer Step 4

STEP - 5

Select the operating system from drop down list being used on machine you wish to install My SQL . Once the operating system is selected  then select the MySQL Installer MSI download link.

This page will display two MySQL Installer MSI download links. The first link with 18.5 M size will need to download some components during installation . Whereas the second link with 393.4 M is complete installer file that needs to be downloaded.

MySQL Installer Step 5

STEP - 6

Now you will be asked to either login Or Signup  to your Oracle account . You can skip this step by selecting the download link given bellow without any Oracle account .

Download the My SQL Installer complete file and complete the installation just like any other software .

MySQL Installer Step 6

MySQL Download And Installation

MySQL Workbench Installation

MySQL Installer Video

MySQL Workbench

MySQL Workbench is a Visual database designing and modelling  tool for database architects , designers and developers . MySQL Workbench offers a GUI environment and a graphical interface for data modelling .

MySQL Workbench offers a very user friendly graphical interface and the database designers can use various graphical elements to visualize and create various data models necessary for building a database.

MySQL Workbench

The database architects and designers create various data models to visualize and communicate the various design features of the relational database being designed .

The data models created during the database design process includes Conceptual , Logical and Physical Data Model . These data models serves as a blue prints for actually building a database using a specific database management system such as My SQL.

MySQL Workbench

What Is Data Modelling ?

In the context of database design , a data model is simply a diagrammatic representation of the database’s internal structural details  . In  other words , a  data model is simply a diagram that displays  a  set of tables ( database entities )  and the relationship between these tables .

The  data model makes it very easy  to  understand  how  different database entities are being  represented by tables and Relationships ( functional dependencies ) that exists between these tables .

Types Of Data Models

The database designers construct  different types of  data models. These data models constructed includes  conceptual data model, logical data model  and  physical data Model .

Each data model   highlights  different  feature of the database design  and  presents  different  view of the database with different abstraction.

The conceptual, logical and physical  data  models are three different ways of modelling data during database design process .

Data Modelling
Data Modelling Conceptual Logical Physical Design

The database designers create three types of data models  during the database design stage. These data models include conceptual data model, logical data model  and  physical data Model .

Each data model   highlights  different  database design  features  and  presents  different  abstraction view.

READ MORE ON DATA MODELLING

SQL

What Is SQL ?

The SQL stands for Structured Query Language  used to communicate with the Database Management System ( DBMS / RDBMS ) to perform various database operations .

The user interacts with the database through an application software . The application software in tern use various SQL commands and statements to direct the database management system ( DBMS ) to perform specific operations.

The SQL was the first commercial database query language introduced for E.F Codd’s Relational Database Model. The SQL is supported by almost all RDBMS including MySQL , Oracle , Mango DB and MS SQL Server.

What Is SQL , SQL , RDBMS

MySQL Workbench

SQL Visual Editor

The Structured Query Language ( SQL ) allows the database user  to perform various database operations and manipulate the relational databases. The SQL is a central component to manage any relational databases.

MySQL Workbench , SQL Visual Editor

SQL Visual Editor Features

  • The MySQL workbench, has built in SQL visual editor to write and execute database queries.
  • The Visual SQL editor allows the database developers to create , modify and execute queries on the MySQL server relational databases. The data can be viewed and explored .

  • The Visual SQL editor has the syntax color highlighting feature that  helps the developer to easily write and debug the SQL statements.

  • The Visual SQL editor allows multiple queries to run and the results are automatically displayed in the different pans.

  • The Visual SQL editor allows the storage of database queries for future use . These queries are saved in the history panel and can be later on.

SQL Commands

Depending upon the type of database operation performed on relational database , the SQL commands can be grouped  under  four  major  categories.

SQL , Structured Query Language

SQL Commands

Data Definition Language

( DDL )

The DDL is subset of SQL and the main purpose of the data definition language ( DDL ) commands is to create , modify , delete various database objects which defines the database structure.

The  DDL statements create, modify, and remove database objects such as database , tables, indexes, triggers, procedures and users.

SQL Commands , DDL , Data Definition Language

SQL Commands

Data Manipulation Language

( DML )

The DML is also a subset of SQL and the main purpose of the data manipulation  language commands is to  manipulation the data already present in database.

This manipulation of data  involves inserting data into database tables, retrieving existing data from tables, deleting data from existing tables and modifying existing data.

SQL Commands , DML , Data Manipulation Language

SQL Commands

Data Control Language

( DCL )

The DCL is also subset of SQL and the main purpose of the data control  language commands is to control access to data stored in a database.

The database administrator can define the data access permissions to various users with the help of DCL .

SQL Commands , DCL , Data Control Language

SQL Commands

Transaction Control Language

( TCL )

The TCL is also a subset of SQL . The Transaction Control Language( TCL ) commands are used to manage transactions in the database.

The TCL statements are  used to manage the changes being made to the data in a table by DML statements. It also allows statements to be grouped together into logical transactions .

SQL Commands , TCL , Transaction Control Language
Don`t copy text!