3. SQL Creating or altering Commands

Let us use the STUDENTS table below to demonstrate how SQL is built up.

Commands that alter a database

If the table did not exist in the first place we could use the CREATE command

CREATE TABLE STUDENTS (`ID` INT(11), `Surname` TEXT, `OtherNames` TEXT, `AGE` INT(3))

First of all the Students table was named, then a set of column names and their data type and length are declared. The data type is chosen to be the most suitable for the kind of data to be stored in that field. For example an INTeger with three digits was selected for the 'Age' field, the surname and othernames fields are TEXT whilst the primary key field ID is integer with 11 digits allowing for possibly millions of records.

Commands to change the structure of a table

Once a database table is created, it is possible to change its structure later on with the ALTER command.

Adding a new field:

ALTER TABLE table_name ADD column_name datatype

Removing an existing field

ALTER TABLE table_name DROP COLUMN column_name

Commands to manage the data

Let us insert a row of data into the table

INSERT INTO `student` (`ID`, `Surname`, `OtherNames`, `Age`) VALUES ('103', 'Singh', 'Manjit', '14');

This time the column names are identified and their corresponding values are set.

Formatting SQL correctly is vital to make the query work, if a single apostrophe ` or other detail is missed then the query fails and quite often it does not tell you why , it reports an unhelpful 'Invalid SQL' statement.

Let us update an existing record

UPDATE `student` SET `Age`=15 WHERE `ID`=103

The Update command is used and the table identified. Then the fields to be updated are set up, in this case the Age field is updated. The WHERE command specifies a condition that cuts down the number of rows to be affected. In this case only alter the row whose ID is 103.

A pretty significant command is DROP which can delete an entire database with one command - use sparingly.

Be cautious

Commands that can alter the database need to be checked very carefully as a mistake could alter unintended rows. For example if above, we typed in WHERE 1 instead of WHERE `ID`=103, this would change every row to have age 15.

Tip: Build your query with the harmless SELECT command first of all (next page) - and examine the results being returned. This will prove that the query accesses the data you were expecting. Then change the SELECT to the appropriate SQL command with the same conditions.

Tip: Some database server languages (MySQL for example) support the LIMIT clause that comes after the main SQL query, this can be used to limit the maximum number of records that can be altered by the query, thus preventing accidentally changing every record

       UPDATE `student` SET `Age`=15 WHERE `ID`=103 LIMIT 1