Introduction
In this article I explain the MySQL cheat sheet in PHP. I will use common MySQL commands during this article to assist you to apply with MySQL and effectively. Here, first is a discussion of working with a database. Using the following queries, you can approriately work on MySQL. The following queries describe that.
Working with Database
When a database does not exist the database server creates a new database. Such as:
<?php
$con = mysql_connect ("localhost","root","");
$db="Vinod";
if (!$con)
{
die ('Could not connect: ' . mysql_error());
}
if (mysql_query ("CREATE DATABASE If Not exists $db",$con))
{
echo "Your Database Created Which Name is : $db";
}
else
{
echo "Error creating database: " . mysql_error();
}
mysql_close ($con);
?>
To drop the database use this query:
<?php
$con = mysql_connect ("localhost","root","");
$db="Vinod";
if (!$con)
{
die ('Could not connect: ' . mysql_error());
}
if (mysql_query ("DROP DATABASE IF EXISTS $db",$con))
{
echo "Your Database removed Which Name is : $db";
}
else
{
echo "Your database $db not extst: " . mysql_error();
}
mysql_close ($con);
?>
To show all the databases in your database server use:
![da1.jpg]()
Working with table
To list all tables in your database, you can use this query:
Create table in your database.
![da2.jpg]()
CREATE table IF NOT EXISTS table_name(create_clause) [table_options]
[[IGNORE|REPLACE] select];
Delete table permanently.
DROP table IF EXISTS table_name [ name,]
[RESTRICT | CASCADE];
Get information about table or column.
DESCRIBE table [column_name]
desc table [column_name];
Working with Index
Create an index on your table.
CREATE [UNIQUE|FULLTEXT] INDEX index_name
ON table (colu_name,);
Remove index from table.
DROP INDEX index_name;
Retrieving data from table
Retrieving all data from your table.
SELECT * FROM table_name;
Retrieve specific column data from your database table.
SELECT column_name, column_name2….
FROM table_name;
Retrieve unique record from your database.
SELECT DISTINCT (column_name)
FROM table_name;
Retrieve data from multiple table then using join.
SELECT * FROM tab_name1
INNER JOIN tab_name2 ON conditions;
SELECT * FROM tab_name1
LEFT JOIN tab_name2 ON conditions;
SELECT * FROM tab_name1;
Counting number of rows from your database tables.
SELECT COUNT (*) FROM table_name;
Sorting data ascending or descending.
SELECT column_name1, column_name2
FROM table_name
ORDER BY column_name1 ASC [DESC], column_name2 ASC [DESC];
Retrieve data from your table in group.
SELECT * FROM table_name
GROUP BY column_name;