Using Database

A database needs to be able to receive information for storage and to deliver information on request.

The MySQL database responds to four types of requests:

Adding information: Adding a row to a table
Retrieving information: Looking at the data
Updating information: Changing information in an existing row
Removing information: Deleting data from the database

Adding Information

Every database needs data. For example, you might want to add data to your database so that your users can look at it. Or you might want to create an empty database for users to put data into. In either scenario, data is added to the database.

The SQL statement LOAD can read data from a text file. So, if the data is already in a computer file, you can work with that file. There is no need to type all the data again. If the data isn't yet in a computer file and there’s a lot of data, it might be faster to enter that data into the computer in a text file and transfer it into MySQL as a second step.

Adding one row at a time

If you have a small amount of data, you can add one row at a time to the table. PHP scripts often need to add one row at a time. For example, when a PHP script accepts the data from a customer in a form, it usually needs to enter the information for the customer into the database in a new row.

INSERT INTO table_name (column_name, column_name,... ,column_name)
VALUES (value, value,..., value)

You use the INSERT statement to add a row to a database. This statement tells MySQL which table to add the row to and what the values are for the fields in the row.

Retrieving Information

After data has been entered into a database, you can browse through the data to see whether the entered data looks correct or to get an idea of what type of data is in the database.

SELECT * FROM table_name

This query gets all the data from a table. You can find out how many records are in the table and get a general idea of the data by browsing the output.

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

This query retrieves the values from all the rows for the indicated columns.

Retrieving Data in Specific Order

In a SELECT query, ORDER BY affect the order in which the data is delivered.

ORDER BY: To sort information, add this phrase to your SELECT query:

ORDER BY column_name

The data is sorted by column_name in ascending order. You can sort in descending order by adding DESC after the column name.

Retrieve Data by Groups

GROUP BY: To group information, use the following phrase:

GROUP BY column_name

The rows that have the same value of column_name are grouped together.

Retrieving Data from Specific Rows

Frequently, all the information from a table is not required. You want information only from selected rows. Three SQL keywords are frequently used to specify the source of the information:

WHERE: Allows you to request information from database objects with certain characteristics. For example, you can request the names of customers who live in particular area, or you can list only products that are a certain category.

LIMIT: Allows you to limit the number of rows from which information is retrieved. For example, you can request the information from only the first three rows in the table.

DISTINCT: Allows you to request information from only one row of identical rows. For example, in a Login table, you can request loginName but specify no duplicate names, thus limiting the response to one record for each member.

WHERE keyword

The basic format of the WHERE clause is:

WHERE expression AND|OR expression AND|OR expression

The expression specifies a value to compare with the values stored in the database. Only the rows containing a match for the expression are selected. You can use as many expressions as needed, each one separated by AND or OR. When you use AND, both of the expressions connected by the AND must be true in order for the row to be selected. When you use OR, only one of the expressions connected by the OR must be true for the row to be selected.

column = value
column > value
column >= value
column < value
column <= value
column BETWEEN value1 AND value2
column IN (value1, value2, …)
column NOT IN (value1, value2, …)
column LIKE value
column NOT LIKE value

LIMIT keyword

LIMIT specifies how many rows can be returned. The form for LIMIT is

LIMIT start_number,number_of_rows

The first row that you want to retrieve is the start_number, and the number of rows to retrieve is number_of_rows. If the start_number is not specified, 0 (first row) is assumed.

DISTINCT keyword

Rows in the table can have identical values in one or more columns. However, in some cases, when you SELECT a column, you don't want to retrieve multiple rows with identical values. You want to retrieve the value only once. For example, suppose you have a table of products with one field called Category. The data contains many products in each category, but you want to display a list of all the categories available in the database.

To prevent a SELECT query from returning all identical records, add the keyword DISTINCT immediately after SELECT, as follows:

SELECT DISTINCT Category FROM Product

Combining information from more than one table

Sometimes your question requires information from more than one table. You can do this question easily in a single SELECT query by combining multiple tables. Two keywords can be used 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, if your query selected 6 rows from one table and 5 rows from another table, the result would contain 11 rows.

JOIN: The tables are combined side by side, and the information is retrieved from both tables.

UNION

UNION is used to combine the results from two or more select queries. The results from each query are added to the result set following the results of the previous query. The format of the UNION query is as follows:

SELECT query UNION ALL SELECT query...

JOIN

Combining tables side by side is a join. Tables are combined by matching data in a column; the column that they have in common. The combined results table produced by a join contains all the columns from both tables.

The two common types of joins are an inner join and an outer join. The difference between an inner and outer join is in the number of rows included in the results table.

Inner join: The results table produced by an inner join contains only rows that existed in both tables.
Outer join: The combined table produced by an outer join contains all rows that existed in one table with blanks in the columns for the rows that did not exist in the second table.

Updating Information in Database

Changing information in an existing row is updating the information. For instance, you might need to change the address of a customer or you might need to add phone number that a customer left blank when he originally entered his information.

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

In the SET clause, you list the columns to be updated and the new values to be inserted. List all the columns that you want to change in one statement.

Removing Information from Database

Be very careful when removing information. After you drop the data, it’s gone forever. It cannot be restored. You can remove a row or a column from a table, or you can remove the entire table or database and start over.

DELETE FROM table_name WHERE clause

If you use a DELETE statement without a WHERE clause, it will delete all the data in the table.

You can delete a column from a table by using the ALTER statement:

ALTER TABLE table_name DROP column_name

You can remove the entire table or database with

DROP TABLE table_name

DROP DATABASE database_name