Manipulating the Content of a Database

Manipulating the content of a database in MySQL may be done in three ways: addition, deletion and modification of records. SQL is the language the user uses to manipulate the content of a database. As all other computer languages SQL is strict about command syntax. Any mistyped parantheses, commas or semicolons will result in an error message. Thus, the user should take care about command syntax.

In this section we will again use our "exercise" database to illustrate examples of the content manipulation in MySQL. Recollect, the "exercise" database has three tables: Customer, Product and Transaction with the following structure:

Customer:

Field	Type	Null	Key	Default	Extra
cname	varchar(50)	YES	.	NULL	.
ccity	varchar(25)	YES	.	NULL	.
cphone	int(11)	YES	.	NULL	.
cid	int(11)	.	PRI	0	auto_increment

Product:

Field	Type	Null	Key	Default	Extra
pname	varchar(50)	YES	.	NULL	.
pprice	int(11)	YES	.	NULL	.
pid	int(11)	.	PRI	0	auto_increment

Transaction:

Field	Type	Null	Key	Default	Extra
cid	int(11)	.	PRI	0	.
pid	int(11)	.	PRI	0	.
tdate	date	.	PRI	0000-00-00	.
tqnt	int(11)	YES	.	NULL	.

Insertion

Now, we will insert our first record into the Customer table. In MySQL the insertion of a record into a table is accomplished using the INSERT command:

mysql> INSERT INTO Customer VALUES
> ('Codd', 'London', 223344, NULL);

The result of the execution of INSERT command will look similar to this:

Query OK, 1 row affected (0.07 sec)

Let us now step by step explain the syntax of INSERT command. In the first row of the INSERT command:

mysql> INSERT INTO Customer VALUES

we specify the command itself ( INSERT INTO ... VALUES ) and the name of the table in which we want to insert new values.

In the following rows we specify the values that we want to insert into a particular table:

> ('Codd', 'London', 223344, NULL);

The values have to be specified within parantheses. It is important to remember that the same number of values has to be inserted as datatypes that are contained within a record of a particular table. Also note that the sequence of the values has to match with the sequence of datatypes within the record. In our example:

'Codd' becomes the value of cname attribute
'London' becomes the value of ccity attribute
223344 becomes the value of cphone attribute
NULL stands for the value of cid attribute ( the value of cid attribute will be actually calculated as we will see later on )

Let us now explain how the values within the parantheses match the datatypes of a record:

Single quotations have to be placed around a value for the datatype VARCHAR. Note single qoute marks as in 'Codd' for the value of the attribute cname or as in 'London' for the value of the attribute ccity. Both cname and ccity attributes are of datatype VARCHAR. If the user tries to insert a value for the datatype VARCHAR without single quotations an error will occur.
Single quotations are not required in the case of insetion of a value for the INT datatype. For the value of the attribute cphone we entered 223344 without quotations.
For the value of the cid attribute we entered NULL. NULL allows any attribute with the characteristic AUTO_INCREMENT to be automatically assigned a value. If this record is the first record that is inserted into the table then the assigned value will be 1. For all other records the assigned value will be the value aasigned to the last inserted record incremented by 1.

Selection

The searching and querying capabilities of MySQL will be explained in more details in the next section of this tutorial. Now we will just present the simplest form of selection of records from a table. The following command lists all records contained in a table. In this section we use this command just to show the results of other commands, such as INSERT, DELETE, or UPDATE command.

Executing the following command:

mysql> SELECT * FROM Customer;

results with:

cname	ccity	cphone	cid
Codd	London	223344	1

1 row in set (0.00 sec)

If we look closer on our SELECT command we can notice that it has the following structure:

between SELECT and FROM keywords we specify which columns from a particular table we want to see - in our case we had * which means that we want to list all columns from the particular table
after FROM keyword we specify the name of the table that we want to list - in our case Customer table.

Now we will insert some more records into the Customer just to have some more data that we can play with:

mysql> INSERT INTO Customer VALUES
-> ('Nick', 'St.Petersburg', 443322, NULL);

mysql> INSERT INTO Customer VALUES
-> ('Harry', 'Los Angeles', 112233, NULL);

Now the result of the select command:

mysql> SELECT * FROM Customer;

is as follows:

cname	ccity	cphone	cid
Codd	London	223344	1
Nick	St.Petersburg	443322	2
Harry	Los Angeles	112233	3

3 rows in set (0.05 sec)

Deletion

In order to delete records from a table the user uses the DELETE command. The user should specify with the DELETE command the name of the table from which records should be deleted as well as which records should be deleted. The later may be specified with the WHERE clause. Here is an example of a DELETE command:

mysql> DELETE FROM Customer
> WHERE (cname = "Harry");

As we may see in:

> WHERE (cname = "Harry");

in order to find records to delete we match values of a particular attribute ( cname ) with the specified value ( "Harry" ). In our example, all records that have "Harry" as the value for the cname attribute will be selected and afterwards deleted. Now the result of the select command:

mysql> SELECT * FROM Customer;

is as follows:

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

2 rows in set (0.01 sec)

Modification

In MySQL the user has the possibility to update or alter the data that is already entered into a table. This is accomplished by using the UPDATE command. In order to find records that have to be to altered, the UPDATE command make use of the WHERE clause. The syntax for the WHERE clause is the same as by the DELETE command. Let us now have a look on an example:

mysql> UPDATE Customer SET cname = "Scerbakov"
> WHERE (cname = "Nick");

If we list the content of the Customer table:

mysql> SELECT * FROM Customer;

we now get the following result:

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

2 rows in set (0.00 sec)