MySQL
Relational Database Management System
MySQL Server | MySQL Workbench | SQL Commands
MySQL RDBMS
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 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 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.
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 .
- Data Storage
- Data Retrieval
- Data Queries
- Data Reports
- Data Access Control
- Data Security
- Data Administration
- Data Consistency And Integrity
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.
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 .
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.
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 .
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 .
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 .
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.
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.
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 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.
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 .
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.
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.
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.
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 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
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
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
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 .
