About MySQL Databases and SQL Commands ~ Computer Tips

In the last post we ‘created
a database’ from our script as an example to test the working
of MySQL. Actually a database is a set of tables for storing data. The tables
in a database may be logically related with each other. MySQL supports unlimited
number of databases and for each application you may use a different one

To understand relation between a database and tables consider an example of
a database oriented Note
Keeping application. Since it needed user authorization we can store
user information such as username and password in one table, upgrading that
system to multi-user capable is also possible because a table can store any
number of rows (i.e. different set of username & password, in this case).
We’ll also need a second table for storing note data. Thus we’re
using a database having two tables to store data related to a single application.

The following image illustrates this relation quite well:

MySQL database server supports the creation, storing and retrieval of data using
the Structured Query Language. SQL is quite easy to understand and work with,
probably because it is very much plain English. Below I’m stating a few
useful SQL commands:

1. CREATE DATABASE

It creates a database named ‘temp’. We discussed about it in the
post Installing,
Configuring and Testing MySQL Database Server on localhost

2. CREATE TABLE

It creates a table inside a database. The database to be worked with should
first be selected using the following PHP command:

$db->slelect_bd($dbname);

it could be run after first creating the database through a SQL query. After
selection of database the following SQL query will create a table inside that
database (since a table is not an independent entity).

CREATE TABLE one (id INT, name VARCHAR(20));

The above query creates a table ‘one’ with two data fields id of
type integer and name of type character (20 characters long). This table can
hold any number of data.

3. INSERT INTO

After creation of table we can insert data into it with the following SQL query:

INSERT INTO one (id, name) VALUES (1,’PHP’);

4. SELECT

It is used to request data form a table.

SELECT * FROM one;

It will request all (*) the rows from the table ‘one’.

NOTE: Though SQL commands end with a ‘;’ semicolon, you don’t
have to have it when querying database from PHP. SQL commands from PHP works
both in UPPER and lower case.

Previous Articles: