Installing MySQL on Windows

In this section you will learn how to install MySQL 5.0 on windows system. The MySQL 3.21 was the first version for the windows. Windows installer of MySQL includes auto installer with Configuration Wizard that support for easy installation.

MySQL Installing packages
The MySQL Installing packages comes in three flavors, you can choose any of the Installer package as per your requirements and your expertise level. These packages are:

1. The Essential packageThe  Essential package has a Similar filename to MySQL-essential-5.0.25-win32.msi and it contains the minimum set of files needed for installation MySQL on Windows, including the Configuration Wizard except embedded server and benchmark suite. This package is recommended most of the users as the user can  install multiple instances of MySQL on a single server host and the advanced user can complete control over server configuration.
     
2. The Complete packageThe complete package has all the needed files that are required for windows installation including configuration wizard, embedded server and bench mark suite. This package has a similar filename to  MySQL 5 . 0 . 25- win32 zip . For using it, your system must have a tool that can unpack .zip files.
    
3. The Noinstall ArchiveThe Noinstall archive package has a similar filename to mysql-noinstall-5.0 .25 -win32 . zip. This package contain all the needed set of  files  for a complete installation wizard excluding all the configuring wizard. This package has a facility of  installing manually not automatically. For using this package, there must be a tool for unpacking .zip files in your system. 

Downloading MySQL 
The latest version of MySQL can be downloaded from http://www.mysql.com. In this tutorial we are using mysql-essential-5.0.24a-win32.msi, which is the latest version of MySQL at the time of writing the tutorial. You download the windows installer from http://www.mysql.com and save on your hard disk.
Understanding the MySQL Installation wizardMySQL Installation Wizard uses the new installer technologies for Microsoft Windows. And that?s why it is an installer for MySQL server. This MySQL installation wizard is combined with the MySQL configuration wizard which allows user to install and configure a MySQL server, and it can be used immediately after installation. MySQL provide the standard installation for all MySQL Server distribution or higher version 4. 1. 5 . In this version MySQL need to shout down and remove their existing MySQL installation and manually installation before the MySQL wizard.  This installation process use of this technology to provide a smoother and flexible process.
MySQL Installing TypeThere are three type of MySQL Installation:-

• Typical 
  This MySQL installing process install the  MySQL server,command- line client and command line utilities. This command line and command client included in mysqldump, myisamchk and several tools to manage the MySQL Server.
    
• Complete
  By this installation type we install all the component included in the installation package. And this package include the component such as support script, the embedded server library. MySQL complete installer also included the benchmark suit and documentation.
    
• Custom
  MySQL is use Custom installation type command that provide you the complete control over package that you want to install and also the installation path. If you choose the Custom type then you can change the installation path or components , which is installed by Installation Wizard

MySQL Installation Wizard made some ChangesMySQL installation wizard can begin the process of installation only after clicking the Install button and make some changes to your system that describe in the following section -
Change to the Registry:MySQL installation wizard generate one windows registry key in the situation of typical installation, that is located in HKEY_LOCAL_MACHINE\SOFTWARE\MySQL AB.MySQL Wizard generates a key that is named after the major version of server which has been installed, like MySQL Server 5.0. It has two String values one is Location and another is Version. The location has the path to installation directory. And the version has the release number.
MySQL used these registry keys to help external tools to identifying the installed location of the MySQL server. This Registry keys does not required to run server and you want to install MySQL using thenoinstall zip archive, then the registry keys are not created .
Change to the Start Menu:MySQL installation  wizard create a new entry in the start menu windows. This MySQL menu heading named after the major version of MySQL that you have to installed. There are some entries are created with in the new start menu section :
MySQL command Line Client: This is shortcut for the MySQL command - line client and is configured to connect the root user. The shortcut prompts for a root user password when  you connected .

MySQL server instance configuration wizard: This is a shortcut for the Configuration wizard . For configuring the new server or reconfiguring the existing server you can use this shortcut.

MySQL Documentation: MySQL Documentation is the link to the MySQL Server documentation and it is stored in the installation directory. But if you have installed the Essential installation package then this option is not available.
 
Change to  the file System: By default the MySQL server installs to C:\Program File\MySQL Server 5.0 location. On a developer machine a typical MySQL installation can be look like this :

C:\Program Files\MySQL\MySQL Server 5.0 C:\Program Files\MySQL\MySQL Administrator 1.0 C:\Program Files\MySQL\MySQL Query Browser 1.0

MySQL Testing the Installation 

C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqlshow" C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqlshow" -u root mysql C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin" version status proc C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql" test

MySQL Downloading and Installing wizardThis installation package can be download from http://dev.mysql.com/downloads/. But you have to extract this zip archive first. If there is setup.exe is available then double click on this file for starting the installation. But if there is an .msi available then double click on this

Basics Of MySQL

MySQL is a open source Relational Database Management System. MySQL is very fast reliable and flexible Database Management  System. It provides a very high performance and it is multi threaded and multi user Relational Database management system.

MySQL is one of the most popular relational database Management System on the web. The MySQL Database has become the world's most popular open source  Database, because it is free and available on almost all the platforms. The MySQL can run on Unix , window, and Mac OS. MySQL is used for the internet applications as it provides good speed and is very secure. MySQL was developed to manage large volumes of data at very high speed to overcome the problems of existing solutions. MySQL can be used  for verity of applications but it is mostly used for the web applications on the internet.  

MySQL Features

1. MySQL are very fast and much reliable for any type of application.
2. MySQL is very Lightweight application.
3. MySQL command line tool is very powerful and can be used to run SQL queries against database. 
4. MySQL Supports indexing and binary objects.
5. It is allow changes to structure of  table while server is running.
6. MySQL has a wide user base.
7. It is a very fast thread-based memory allocation system. 
8. MySQL Written in C and C++ language. 
9. MySQL code is tested with different compilers.
10. MySQL is available as a separate program for use in a client/server network environment.
11. The MySQL available for the most Unix operating platform.
12. MySQL are the available for window operating system window NT, window 95 ,and window 98.
13. MySQL available for OS/2.
14. Programming libraries for C, Python, PHP, Java , Delphi etc. are available to connect to MySQL database.

MySQL Advantages:

Reliability and Performance MySQL is very reliable and high performance relational database management system. It can used to store many GB's of data into database.
Availability of Source MySQL source code is available that's why now you can recompile the source code.

Cross-Platform supportMySQL supports more then twenty different platform  including the major Linux  distribution .Mac OS X ,Unix and Microsoft windows.

Large pool of Trained and Certified DevelopersMySQL is very popular and it is world most popular open source Database. So it is easy to find high quality staff around the world.

Powerful Uncomplicated software   The MySQL has most capabilities to handle most corporate database application and used to very easy and fast
MySQL 5 Features
There are three feature of MySQL 5.0 version:-

• Views
  Views is a virtual table which acts as a table but it contains no data. Views are created using columns from one or more tables.
    
• Stored Procedures and Functions
  MySQL 5.0 now support Stored Procedures and Functions. This allows you to embed business logic at database level.
    
• Triggers
  The Triggers is another very imported feature available with MySQL 5.0. Now can add some business logic whenever data is inserted, Deleted or updated in the table.                                                              

• Installing MySQL on Windows

Database Management System (DBMS)

A Database Management System (DBMS) sometimes called a database manager or database system is a set of computer programs that controls the creation, organization, maintenance, and retrieval of data from the database stored in a computer. It allows the individuals or entities to easily access and use the data from database. An excellent database system helps the end users to easily access and use the data and also stores the new data in a systematic way. It knows better the actual physical location of the data.

A DBMS is a system software package that ensures the integrity and security of the data. The most typical DBMS is a relational database management system (RDBMS). A newer kind of DBMS is the object-oriented database management system (ODBMS). The DBMS are categorized according to their data types and structure. It accepts the request for the data from an application program and instructs the operating system to transfer the appropriate data to the end user. A standard user and program interface is the Structured Query Language (SQL).

Database Tutorials:

• SQL
• MySQL

There are many Data Base Management System like MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, FoxPro and many more that work independently and freely but also allow other database systems to be integrated with them. For this DBMS software comes with an Open Database Connectivity (ODBC) driver ensuring the databases to be integrated with it.

A DBMS includes four main parts: modeling language, data structure, database query language, and transaction mechanisms Modeling language.

Modeling Language: A data modeling language to define the schema (the overall structure of the database) of each database hosted in the DBMS, according to the DBMS database model. The schema specifies data, data relationships, data semantics, and consistency constraints on the data. The four most common types of models are the:

• Hierarchical model
• Network model
• Relational model
• Object model

The optimal structure depends on the natural organization of the application's data, and on the application's requirements that include transaction rate (speed), reliability, maintainability, scalability, and cost.

Data Structures: Data structures which includes fields, records, files and objects optimized to deal with very large amounts of data stored on a permanent data storage device like hard disks, CDs, DVDs, Tape etc.

Database Query Language: Using the Database Query Language (DQL) users can formulate requests and generate reports. It also controls the security of the database. The DQL and the report writer allows users to interactively interrogate the database, analyze its data and update it according to the users privileges on data. For accessing and using personal records there is a need of password to retrieve the individual records among the bunch of records. For Example: the individual records of each employee in a factory.

Transaction mechanisms modeling language: The transaction mechanism modeling language ensures about data integrity despite concurrent user accesses and faults. It maintains the integrity of the data in the database by not allowing more than one user to update the same record at the same time. The unique index constraints prevent to retrieve the duplicate records like no two customers with the same customer numbers (key fields) can be entered into the database.

The Latest Trend

Among several types of DBMS, Relational Database Management System (RDBMS) and Object-oriented Database Management System (OODBMS) are the most commonly used DBMS software.

The RDBMS is a Database Management System (DBMS) based on the relational model in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables. It was introduced by E. F. Codd, which is the most popular commercial and open source databases now days. The most popular RDBMS is:

• MySQL
• PostgreSQL
• Firebird
• SQLite
• DB2
• Oracle

OODBMS: Object-Oriented Database Management System (OODBMS) in short Object Database Management System (ODBMS) is a database management system (DBMS) that supports the modeling and creation of data as objects. It includes some kind of support for classes of objects and the inheritance of class properties and methods by subclasses and their objects. An ODBMS must satisfy two conditions: it should be an object-oriented programming language and a DBMS too.

OODBMS extends the object programming language with transparently persistent data, concurrency control, data recovery, associative queries, and other database capabilities.
At present it is on its development stage and used in Java and other Object Oriented programming language.

Earlier it was introduced to replace the RDBMS due to its better performance and scalability but the inclusion of object-oriented features in RDBMS and the origin of Object-relational mappers (ORMs) made it enough powerful to defend its persistence. The higher switching cost also played a vital role to defend the existence of RDBMS. Now it is being used as a complement, not a replacement for relational databases.

Now it is being used in embedded persistence solutions in devices, on clients, in packaged software, in real-time control systems, and to power websites. The open source community has created a new wave of enthusiasm that's now fueling the rapid growth of ODBMS installations.

The most commonly DBMS are:

• SQL
• MySQL
• SQLite
• PostgreSQL
• Microsoft Access
• SQL Server
• FileMaker
• DB2
• Oracle
• Firebied
• DBASE
• Clipper
• FoxPro

Drop Table in Database

The Drop Statement in SQL Command is used to delete or remove indexes, tables and database. The Syntax used for Drop table statement in database is given as:

Drop Table table_ Name;

The above syntax is used to delete a table.

table Name : specify the name of the table, which you want to delete from database.

Understand with Example

The Tutorial illustrates an example from Drop Table in Database. In this tutorial we show you a table created in database using show tables.

Now we create another table 'student information'. The insert statement is used to add the record to the table 'student information'.

The select statement is used to retrieve the record from the table 'student information'.

The show tables show you the list of table available in database.

The drop statement is used to delete a table from database.

Once the table is deleted using drop statement, the table is no longer to be shown in the database. When we write show tables, this will show you the table available in database.

Where Clause in SQL

The Where clause in SQL query is used with the SELECT keyword.' Where' clause search the data with a particular condition. In case the data matches with condition, return you the specific value of particular data value.

Syntax

SELECT column FROM table WHERE column operator value

The Select is used to retrieve the record from a table.

column : This specify the name of the column, which you want to retrieve from table.

From : The From Keyword specify the name of the table from where data is retrieved.

table :This specify the name of the table.

Where column : The clause where search the data with a particular condition. If data matches with the condition ,return you the specific value of data.

The List of Operator can be used as

Operator	Description
=	Equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	If you know the exact value you want to return for at least one of the columns

Understand with Example

The Tutorial illustrate an example from where clause in SQL. In this Tutorial we use a select query to retrieve the record from table 'country'.

select * from country;

The select query return you the records or rows from table country. In case the data matches with the specified condition, return you the specific value of record.

Output of the above query is

The select query used with where clause, which search the data with a particular condition.

select * from country where countryname='India';

Output of the above query is

Delete a Records in Database

The Delete Statement is used to delete the records and rows from the table. The generalized Syntax used for delete query in database are as :

delete from table name;

The above syntax is used to delete an entire record and rows from a table.

delete from "name of table" where "columnname" operator "value";

The Syntax is used to delete the records or rows from a table followed by where clause, that include the condition on column.

Understand with Example

The Tutorial illustrates an example to delete Records in Database. In this Tutorial, we create a table country, in which we add a record or rows into table using insert into query.

The select retrieve the record from a table country.

insert into country values('India','Delhi'); insert into country (countryname) values('Norway'); select * from country;

The delete query is used to delete the record from table country followed by where clause, which include the condition upon the column specified in query.

The select query shows you the current column field and its record value from table country.

delete from country where countryname='Norway'; select * from country;

Update Records in Database

The Update Records in database is used to modify or change the value of records which match with a specified criteria. The update query is accompanied by where clause that specify the condition on column specified. The Syntax used for Update Records are given as:

update "name of table" set "column name"="new value" where column name=" "

The above Syntax in SQL is used to update the table

set column name : Specify the name of the column where you want to set a new value.

where : The where clause specify the condition on the basis of which set a new value into a table.

Understand with Example

The Tutorial illustrates an example from update record in database. In this Tutorial, the code run a select query to retrieve the records from a table country. The Table show you the record from table country. The Update query is used to specify the name of the column where you want to set a new value. In this database, we update a table country and set the new value to the column 'countrycapital' using whereclause specify the condition on column specified upon the basis of which you a set a new value into a table.

Syntax used in Update Query

The select is used to retrieve the record from table country.

The Update statement is used to update the table country where you want to set a new value for a column and clause where specify the condition upon the basis on which you set a new record into the column table.

select * from country; update country set countrycapital='oslo'where countryname='norway';

Output of the above Query

Select data from Table in Database

The Select Statement is used to retrieve the selected data from table in database. The select query in the database retrieve selected data , match with column name specified by you. The Syntax used in simple select statement is given below

select column name from table;

The select query retrieve the record value of specified column in the syntax from table.

select * from table;

The select query retrieve all the column present in the specified table.

The Keyword from indicates the table, from where the data is retrieve.

Understand with Example

The Tutorial illustrates an example, how to retrieve the data from table. In this Tutorial we create a table country. The syntax used to retrieve the record from database are as :

The insert statement is used to insert the record into a table country.

The select statement is used to retrieve the record from table.

insert into country (countryname) values('Norway'); insert into country (countryname,countrycapital) values('Norway','Oslo'); select * from country;

Insert Records in Table

The Insert data in Table is used to insert records or rows into the table in database. The Syntax used to insert records into a table is given below:

insert into Table values('first value',' last value');

The insert into is used to add the records followed by the name of table. The open parenthesis follow the table name, that include list of values enclosed inside a parenthesis. The value given into the table will be positioned at respective rows and match up with column name specified in the table.

Understand with Example

The Tutorial illustrates an example from Insert data in Table. In this Tutorial we create a table name 'country'.

The describe country show you the field, data type ,attribute used in table country.

The insert into syntax is used to add the records into a table country.

describe country; insert into country values('India','Delhi');

output is displayed as

The select statement command shows you the list of record inserted into rows of table country.

select * from country;

output is displayed as

Create Table in Database

The objects in a Relational database System is called Tables. The Tables contain the information on data for the database. Each Table in a database is uniquely identified by their names. It comprised of columns and rows. The column in a table contain the name of column, data type and attribute incase used in column. Columns in a Table are also called Field. The Rows contains the set of records and data for the columns. Rows in a Table are Tuple.The collection of rows and column collectively called cell in a Table.

Understand with Example

The Tutorial illustrates you a simple example for the beginners, to create a table in database. In this Tutorial, we are using MySQL command query to create a table in database name countryinfo.

The create database is used to create database name countryinfo.

The use is used to show the database

The create table is used to create a table name country in database

The show table is used to display the table name in database.

create database countryinfo;

Output is displayed as

Output is displayed as

use countryinfo;

create table country(countryname varchar(15),countrycapital varchar(15));

Output is displayed as

show tables;

Output is displayed as

SQL Introduction

Quick Introduction to SQL: SQL is Structure Query Language. This Query language is used to connect with the database. It is considered as standard language for relation database management systems according to (American National Standards institute). The SQL have own statement, which helps you to perform various tasks such as insert, update or retrieve the data from database, In others word it is a database sub language for querying and modifying relational database. The common relational database using SQL are: Oracle, Microsoft SQL Server,Ingress,Access,etc.Normally. The most database use SQL ,but these database have their own property, which are used on their system. The standard common command used in SQL are" Select"," Insert"," Update","Delete","Create","Drop" and Truncate, which are performed on database.

Data Definition Language: The Data Definition language is used to create,alter,drop or delete a table in database. This include a index through which you can make a link between table in database. The list of Data Definition language are as given below

Statements	Description
Create	The Create Table is used to create a table in database
Alter	The Update is used to alter the table
Drop	The Drop Table is used to drop table in database

Data Manipulation Language :The Data Manipulation Language is used to select, insert into,delete,update from database table. The following keyword perform the particular task are given below:

Statements	Description
Select	The Select is used to display the records available in table.
Update	The Update is used to modify or change in table
Delete	The Delete is used to record from table
Insert into	The Insert into is used to add records into table

Structure of SQL Language

The SQL is a keyword based language. Each statement in SQL start with a unique keyword. The SQL statements include a clauses begin with a keyword. SQL syntax is independent of case-sensitive.

Syntax for SQL Statement

1) Elements of database are tables,columns,views,user,schemas,names,which begin with a letter(a-z), digits(0-9) and underscore(_).

2)literals used in SQL Statement are quoted strings,datetime values, numeric values.

3)delimiter are + -,( ),=,<>,*,||,?;

Names in the database is case sensitive, include spaces and delimiters, include keywords surrounds with double quotation marks("").For Example

"SELECT","Create""INSERT","DELETE"etc.

SQL Tutorials for Beginner

SQL for Beginner

The Tutorial brings a unique introduction on SQL for beginners, who want to learn and understand SQL in easy steps. The Tutorial provides you a illustrative examples, which is easy to understand and learn using on-line SQL interpreter.

The Tutorial Support you from the basic concept of SQL and showing the easiest elaborative examples for the beginner. The beginner will reached into the immediate result as the SQL command is submitted. It bring the beginner to create your own tables and perform various statement like select, insert, delete, update, drop etc on tables.

  

SQL Introduction

Quick Introduction to SQL: SQL is Structure Query Language. This Query language is used to connect with the database. It is considered as standard language for relation database management systems according to (American National Standards institute).


Create Table in Database


The objects in a Relational database System is called Tables. The Tables contain the information on data for the database. Each Table in a database is uniquely identified  by their names.


Insert Records in Table


The Insert data in Table is used to insert  records or rows into the table in database.
  
Select data from Table in Database


The Select Statement is used to retrieve the selected data from table in database. The select query in the database retrieve selected data , match with column name specified by you.
  
Update Records in Database


The Update Records in database is used to modify or change the value of records which match with a specified criteria. The update query is accompanied by where clause that specify the condition on column specified.
  
Delete a Records in Database


The Delete Statement is used to delete the records and rows from the table.


Where Clause in SQL


The Where clause in SQL query is used  with the SELECT keyword.' Where' clause search the data with a particular condition. In case the data matches with condition, return you the specific value of particular data value.
  
Drop Table in Database


The Drop Statement in SQL Command is used to delete or remove indexes, tables and database. 
  

SQL Example, Codes and Tutorials

SQL Tutorials:Collection of SQL tutorials for beginners and advance programmers. We have developed many tutorials and examples on SQL. Here you will find everything to get started with SQL and then learn the advance concepts of SQL. The Standard Query Language or SQL for short is standard language to interact with the database. SQL allows you to create, delete, update tables and data stored in the table. SQL provides everything to help the programmer to interact with database and develop enterprise applications.

In this tutorial you will learn how to you user SQL to interact with database. We have used MySQL database to make the process simple. You can easily download MySQL from mysql.com and install on your machine to experiment and learn the SQL concepts.