MySQL Database and Tables
What is a database?
When an amount of data is stored in an organized way, that is called a Database.
In computers, a database is managed by a software called Database management System.
What is a table?
A table is a set of data values. These values are organized using a vertical columns and horizontal rows. Columns are identified by their names.
Pictorial representation of a database with tables
Sample table : author
MySQL create database
In MySQL, the create statement is used to create a database.
Syntax
CREATE database [database_name];
Arguments
| Name | Description |
|---|---|
| database_name | A database. |
Example :
The following command creates 'bookinfo' database.
CREATE DATABASE bookinfo;
The database names are case sensitive under Unix but this restriction does not apply in Windows. This is also true for table names. The best practice is to use same letter case for creating database as well as table.
Note : A database which have just been created is not current database. The user must have to instruct to make it the current database. A database needs to be created only once but a user must have to select it each time he intends to work with that database.
PHP script
<?php
$host="localhost"; //hostname
$username="root"; //username
$password="DataSoft123"; //password
$con=mysql_connect("$host", "$username", "$password")or die("cannot connect");
$result = mysql_query("CREATE DATABASE mydatabase");
if (!$result)
echo "Database not created" . mysql_error();
else
echo "Database created";
mysql_close($con);
?>
MySQL show database
SHOW statement displays a list of currently existing databases on the server.
Syntax
SHOW [expression];
Arguments
| Name | Description |
|---|---|
| expression | Databases. |
Example :
The following MySQL statement will show the current database.
SHOW databases;
The list of databases shown bellow by the statement may be different to the other user's machine. SHOW DATABASES does not show databases for those you don't have SHOW DATABASES privilege.
Output
PHP script
<?php
include('dbopen.php');
$sql = "SHOW DATABASES";
$result = mysql_query($sql);
while($row=mysql_fetch_array($result))
{
echo "<table>";
echo "<tr>";
echo "<td align='center'>" . $row['Database'] . "</td>";
echo "</tr>";
echo "</table>";
}
?>
MySQL select database
MySQL select database statement is used to see which database is currently selected.
Syntax
SELECT [expression];
Arguments
| Name | Description |
|---|---|
| expression | Keyword DATABASE followed by (). |
Example :
The following MySQL statement will show the current database.
SELECT DATABASE();
Output
MySQL use database
MySQL use statement is used to change the database from default to the database you mention following the use argument.
Syntax
Use [database_name];
Arguments
| Name | Description |
|---|---|
| database_name | A database. |
Example :
In the following example the bookinfo database is currently active. The semicolon is not required at the end for this statement but there is no harm if you use it.
PHP script
<?php
include('dbopen.php');
$sql = "USE bookinfo";
$result = mysql_query($sql);
}
?>
MySQL show tables statement
'show tables' statement displays a list of the tables in the database in use.If there is no table in the database, it returns empty rows.
Syntax
SHOW [expression];
Arguments
| Name | Description |
|---|---|
| expression | The key word tables. |
The above statement displayed the list of tables in the database 'bookinfo'.
SHOW tables;
Output
PHP script
<?php
include('dbopen.php');
$sql = "SHOW TABLES FROM bookinfo";
$result = mysql_query($sql);
while($row=mysql_fetch_array($result))
{
echo "<table>";
echo "<tr>";
echo "<td align='center'>" . $row['Tables_in_bookinfo'] . "</td>";
echo "</tr>";
echo "</table>";
}
?>
