MySQL Basic Commands

April 15, 2008

MySQL database server it self supports bunch of common SQL commands which can be utilise for data mining. If you wish to build up a web application which uses MySQL database to store data, you will need to familiarize the following 7 sql commands which I categorize it as “Basic SQL Commands”:

  1. SELECT
  2. UPDATE
  3. INSERT
  4. DELETE

SELECT Query

Select command is used to read data from the database. Before running SELECT query, you need to know which table name and table fieldname (if you wish to display specific field in the table instead of all). MySQL do support on wild card (*) for field name. Wildcard represents a shortcut for to display all field in mentioned table.

Below are the basic SELECT command:

SELECT <field_name>|* FROM <table_name>

Eg:
SELECT username,password from user_details

Explanation:
This SELECT query is trying to read record from the table named “user_details”. This query will display two field which is “username” and “password”.

Update Query

Update command is used to update existing data in the database. Before running UPDATE query, you need to know which table name and table fieldname to be updated. UPDATE query always comes in pair with the WHERE clause to make sure that the correct record is being updated.

Below are the basic UPDATE command:

UPDATE <tablename> SET <fieldname>=<value> WHERE <fieldname>=<value>

Eg:
UPDATE user_details SET password = “1234556″ WHERE username = “vickson”

Explanation:
This UPDATE query is trying to updates all existing record in the database if the record is having field “username”’s value equal to “vickson”. If the WHERE clause is being stripped off, this will apply to all records in the user_details table which ends with all the record’s password field value is “1234556″.

INSERT INTO Query

This INSERT INTO query is used to insert data into the database. There are two ways to write INSERT query that is :

INSERT INTO <tablename> (<fieldname1>,<fieldname2>,..) VALUES (”data1″,”data2″,…)

OR

INSERT INTO <tablename> VALUES (”data1″,”data2″,…)

Eg:
INSERT INTO user_details (username,password) values (”vickson”,”1234556″);

INSERT INTO user_details values (”vickson”,”1234556″);

Explanation:
This INSERT query is trying to insert data into user_details table with “vickson” as username and “1234556″ as password.

Notice that the second INSERT query do not specify the fieldname, the MySQL will match the values provided according field orders during the table creation.

DELETE Query

By the query name it self, you know this query is to delete a record in the table. Similar to UPDATE query, this DELETE query is recommended to be executed with WHERE clause to make sure you deleted the correct record.

Below are the basic DELETE command:

DELETE FROM <table_name> WHERE <fieldname>=<value>

Eg:
DELETE FROM user_details WHERE username = “vickson”

Explanation:
This DELETE query is trying to delete record in the table which is having username field value as “vickson”. Notice that, if the WHERE clause is being stripped off, this will mean that to delete all records in the user_details table.

By understanding these 4 query commands, there is no problem for you to code simple/basic php application with mysql database. However, you may need to use a more complicated sql queries depends on how complicated is your php application.

�

Leave a Reply