Querying a Database

Simple Selections

In the last section we learned how to manipulate the content of a database. Thus, we learned how to insert, delete and update records in a database table. However, a database wpuld not be of much use if the user is not able to search or extract the data from it. As we already saw in the last section of this tutorial this process in MySQL is accomplished through the SELECT statement.

Let us recollect how we used this command in the last section. We wanted to list the whole content of a database table, thus we used:

mysql> SELECT * FROM Customer;

and got the following result:

cname	ccity	cphone	cid
Codd	London	223344	1
Scerbakov	St.Petersburg	443322	2

2 rows in set (0.00 sec)

At the example of DELETE and UPDATE statements we explained how to use the WHERE clause to find a particular record within a table. The WHERE clause may be also used with the SELECT statement:

mysql> SELECT * FROM Customer
-> WHERE ( cname = 'Codd' );

The result of this statement is:

cname	ccity	cphone	cid
Codd	London	223344	1

1 row in set (0.00 sec)

We actually executed a basic SQL block which consists of:

SELECT ( < target list > )
FROM ( < relation name > )
WHERE ( < predicate > )

Note that the "*" symbol in the first row means that we want to see all columns in the selected record. If we want to see just specific columns from a table we have to specify our target list in the sense of the column names. For instance we may issue the following command:

mysql> SELECT ccity FROM Customer
-> WHERE ( cname = 'Codd' );

This command results in:

ccity

London

1 row in set (0.00 sec)

More Advanced Selections

Now we will see how we can make a more complex SELECT statements. In this section we will make use of logical operators to execute more advanced searches. MySQL includes full support of all basic logical operations.

And

The And operator may be used as follows:

mysql> SELECT * FROM Customer
-> WHERE ( ccity = 'London' )
-> AND ( cname = 'Codd' );

The result:

cname	ccity	cphone	cid
Codd	London	223344	1

1 row in set (0.00 sec)

Or

The Or operator may be used:

mysql> SELECT * FROM Customer
-> WHERE ( ccity = 'London' )
-> OR ( cphone = 443322 );

The result is:

cname	ccity	cphone	cid
Codd	London	223344	1
Scerbakov	St.Petersburg	443322	2

2 rows in set (0.00 sec)

Not

The Not operator may be used as follows:

mysql> SELECT * FROM Customer
-> WHERE ( ccity != 'St.Petersburg' );

The result:

cname	ccity	cphone	cid
Codd	London	223344	1

1 row in set (0.00 sec)

Order By

The Order By operator will sort the result of a select statment:

mysql> SELECT * FROM Customer
-> ORDER BY cphone;

The result:

cname	ccity	cphone	cid
Codd	London	223344	1
Scerbakov	St.Petersburg	443322	2

1 row in set (0.00 sec)

An Example

Now we will make an advanced example of querying our test database. We already inserted a number of records into the "Customer" table. For the test purposes we inserted some new records into "Customer" table as well as a number of records into "Product" and "Transaction" table.

Here is the complete list of all records for these tables.

Customer table:

cname	ccity	cphone	cid
Codd	London	223344	1
Scerbakov	St.Petersburg	443322	2
Harry	Vienna	112233	3
Manfred	Graz	111111	4
Jackie	Hong Kong	666611	5

5 rows in set (0.00 sec)

Product table:

pname	pprice	pid
CPU	23	1
Graphic Accelerator	12	2
Network Interface Card	3	3
Hard Drive	13	4

4 rows in set (0.00 sec)

Transaction table:

cid	pid	tdate	tqnt
1	1	2000-11-13	1
1	2	2000-11-13	1
1	3	2000-11-13	2
1	4	2000-11-13	1
2	1	2000-11-13	10
2	2	2000-11-13	14
2	3	2000-11-13	35
2	4	2000-11-13	8
3	1	2000-11-13	1
4	2	2000-11-13	1
4	3	2000-11-13	1
5	1	2000-11-13	1
5	2	2000-11-13	1
5	3	2000-11-13	1
5	4	2000-11-13	1

15 rows in set (0.00 sec)

Now, let us for instance get names of all customers that have bouth a CPU. We will write something like:

mysql> SELECT Customer.cname from Customer, Product, Transaction
> WHERE ( Customer.cid = Transaction.cid ) AND
> ( Product.pid = Transaction.pid ) AND
> ( Product.pname = 'CPU' );

The result is as follows:

cname

Codd

Scerbakov

Harry

Jackie

4 rows in set (0.00 sec)

Note that the predicates ( Customer.cid = Transaction.cid ) and ( Product.pid = Transaction.pid ) actually JOINS these tables over the specified attribute, i.e., over cid and pid respectively.