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.
4. Removing information: deleting data from the database.
You can remove a row from a table with the DELETE query:
DELETE FROM table_name WHERE clause
You can delete a column from a table by using ALTER query:
ALTER TABLE table_name DROP column_name
Or you could remove the whole thing and start over again with
DROP TABLE table_name
or
DROP DATABASE database_name
Have fun!