How To Manage Database Using phpMyAdmin 3.4 On CentOS 6 (MySQL)

Tuesday, August 16, 2011

A database needs to be able to receive information for storage and to deliver information on request. You’re likely to perform four types of task on your database: adding information, updating information, retrieving information and removing information.

1. Adding information: adding a row to a table

Step 1. Adding one row at a time with an SQL query

You use the INSERT query to add a row to a database. This query tells MySQL which table to add the row to and what the values are for the fields in the row. The general form of the query is:

INSERT INTO tablename (column_name, column_name, …,)

VALUES (value, value, …, value)

The following INSERT query adds a row to the Pet table:

Besides, you can create the catalog using phpMyAdmin.

Step 2. Adding a bunch of data

If you have a large amount of data to enter and it’s already in a computer file, you can transfer the data from the existing computer file to your MySQL database using phpMyAdmin. A data file for the Pet table might look like this (Excel):

To insert data into your database table with phpMyAdmin, you just need to click the Import tab at the top of the page.

2. Updating information: changing information in an existing row. This include adding data to a blank field in an existing row.

If you’re updating database information from an application, you use an SQL query. The UPDATE query is straightforward:

UPDATE table_name SET column=value,column=value,.... WHERE clause

For instance, to update an address in Member table, use this query:

UPDATE Member SET street=”227 Nguyen Van Cu”,

phone=”0986681535”

WHERE loginName=”hanthuy”

3. Retrieving information: looking at the data. This request does not remove data from the database.

You use SELECT query to ask the database questions. The simplest, most basic SELECT query is:

SELECT * FROM table_name

This query retrieves all the information from the table.

Step 1. Retrieving specific information

To retrieve specific information, list the columns containing the information you want. For example:

SELECT column_name, column_name,... FROM table_name

You can perform mathematical operations on columns when you select them.

Step 2. Retrieving data in a specific order

In a SELECT query, ORDER BY and GROUP BY affect the order in which the data is delivered to you:

SELECT * FROM Pet ORDER BY DESC petName

SELECT * FROM Pet GROUP BY petType

Step 3. Retrieving data from a specific source

Three SQL words are frequently used to specify the source of the information:

WHERE: allows you request information from database objects with certain characteristics.

LIMIT: allows you to limit the number of rows from which information is retrieved.

DISTINCT: allows you to request information from only one row of identical rows.

For example, you can use the following query to find the first 10 people in Member Directory database whose anems begin which D, who live in HCMC, and who have an 8 in either their phone or fax number:

SELECT lastName,firstName FROM Member

WHERE lastName LIKE “D%”

AND city = “HCMC”

AND (phone LIKE “%8%” OR fax LIKE “%8%”)

LIMIT 10

Step 4. Combining information from tables

You can use UNION in a SELECT query to combine information from two or more tables.

UNION: Rows are retrieved from one or more tables and stored together, one after the other, in a single result.

For example, you can cat a list of all members, both current and resigned, with the following query:

SELECT lastName,firstName FROM Member UNION ALL

SELECT lastName,firstName FROM OldMember

Note: If ALL is not included, duplicate lines are not added to the result.