What is MySQL?

MySQL is a database management system. A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL Server.

MySQL is a relational database management system. A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility. The SQL part of “MySQL” stands for “Structured Query Language.” SQL is the most common standardized language used to access databases and is defined by the ANSI/ISO SQL Standard.

MySQL software is Open Source(Free of cost). The MySQL software uses the GPL (GNU General Public License). The MySQL Database Server is very fast, reliable, and easy to use. MySQL Server works in client/server or embedded systems. MySQL is a relational database system that is used to store information. MySQL can store many types of data from something as tiny as a single character to as large as complete files or graphics. Although it can be accessed by most programming languages, it is often coupled with PHP because they work together with ease. Information stored in a MySQL database hosted on a web server can be accessed from anywhere in the world with a computer.

MySQL is a multithreaded, multi-user SQL database management system (DBMS). The program runs as a server providing multi-user access to a number of databases. MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now a subsidiary of Sun Microsystems, which holds the copyright to most of the code base.

Some of the features of MySQL include,

A broad subset of ANSI SQL 99, as well as extensions
Cross-platform support
Stored procedures
Triggers
Cursors
Updateable Views
True VARCHAR support
INFORMATION_SCHEMA
Strict mode
SSL support
Query caching
Sub-SELECTs (i.e. nested SELECTs)

The following features are implemented by MySQL but not by some other RDBMS software:

Multiple storage engines
Native storage engines (MyISAM, Falcon, Merge, Memory (heap), Federated etc…)
Partner-developed storage engines (InnoDB, solidDB, NitroEDB, BrightHouse)
Community-developed storage engines (memcached, httpd, PBXT)
Custom storage engines

MySQL Downloads

Your Ad Here

MySQL Basic Syntax

As concerned with PHP, we web developers generally use MySQL to store and retrieve data, to and from the database. We assume that you have successfully installed MySQL Server and it is running well. Here we will discuss general MySQl queries. The nice thing about MySQL is that the "code" is very easy for humans to read, as opposed to harder programming languages like C or C++. Human readable English like language is used to query a database table.

To get started with PHP-MySQL scripting, we first need to connect with database through PHP script. Following is the code to connect to MySQL Database using PHP Script

Example #1 Connecting MySQL Database With PHP

<?php
$host = "localhost";// Your database host, Generally "localhost".
$user = "root";// Your database account username.
$password = "root";// Your Password to access database account.
$dbase = "test";// Your database to be used.

mysql_connect($host,$user,$password);
mysql_select_db("test"); // select database "test" to use.
?>

OR directly we can write as follows,

<?php
mysql_connect("localhost","root","root");
mysql_select_db("test"); // select database "test" to use.
?>

You can save above code to some php file like dbconnect.php and you can include to any php file where database connection is required. Lat say we save this file to dbconnect.php. To include this newly created file use following code.

<?php
include("database.php");
?>

To close database connection use the following code,

<?php
// Close connection.
mysql_close();
?>

Example #2 Basic Query Commands in MySQL"

CREATE Command: The Create command is used to create a table by specifying the tablename, fieldnames and constraints as shown below:

Syntax:
$create_query=("CREATE TABLE tblName");

Example:
$create_query=("CREATE TABLE tblstudent(fldstudid int(10) NOTNULL AUTO_INCREMENT PRIMARY KEY,fldstudName VARCHAR(250) NOTNULL,fldstudentmark int(4) DEFAULT '0'");

SELECT Command: The Select command is used to select the records from a table using its field names. To select all the fields in a table, '*' is used in the command. The result is assigned to a variable name as shown below:

Syntax:
$select_query=("SELECT field_names FROM tablename");

Example:
$select_query=("SELECT * FROM tblstudent");

DELETE Command: The Delete command is used to delete the records from a table using conditions as shown below:

Syntax:
$delete_query=("DELETE * FROM tablename WHERE condition");

Example:
$delete_query=("DELETE * FROM tblstudent WHERE fldstudid=2");

INSERT Command: The Insert command is used to insert records into a table. The values are assigned to the field names as shown below:

Syntax:
$insert_query=("INSERT INTO tblname(fieldname1,fieldname2..) VALUES(value1,value2,...) ");

Example:
$insert_query=("INSERT INTO Tblstudent(fldstudName,fldstudmark)VALUES(Baskar,75) ");

UPDATE Command: The Update command is used to update the field values using conditions. This is done using 'SET' and the fieldnames to assign new values to them.

Syntax:
$update_query=("UPDATE Tblname SET (fieldname1=value1,fieldname2=value2,...) WHERE fldstudid=IdNumber");

Example:
$update_query=("UPDATE Tblstudent SET (fldstudName=siva,fldstudmark=100) WHERE fldstudid=2");

DROP Command: The Drop command is used to delete all the records in a table using the table name as shown below:

Syntax:
$drop_query=("DROP tblName");

Example:
$drop_query=("DROP tblstudent");