PHP & MySQL Tutorial - Table and Query

A PRIMARY KEY is a constraint that is applied to a column to uniquely identify each row of that database table. It ensures that the values in each row of that column are unique and never change, so those values can be used to reference any specified row.

By setting the PRIMARY KEY constraint it is possible to manipulate data on specific rows of the database table.

Any column can be set as the PRIMARY KEY but is often the first column that is used to provide a unique identifying number.

Any column set as the PRIMARY KEY must meet the following criteria:

1. Each field in that column must have a value - it should not be empty or have a NULL value.
2. Each value in that column must be unique - there must be no duplications.
3. Each value in that column never be modified or updated.
4. Each value in that column cannot be re-used - when a row is deleted its PRIMARY KEY value cannot be re-assigned as the PRIMARY KEY value of a new row.

After a table created, data can be entered into it with insert into command:

insert into table_name values(value1, value2, value3);

The data values are entered as comma-separated arguments to the value() function; the list must correspond to the number of table columns and each value must be of the correct data type.

Another way to insert data into a table is to specify the names of the columns where the data is to be added.

Entire table can be viewed with select * from command, followed by the name of the table.

The definition of a column can be altered using alter table and modify commands.

alter table table_name modify field_name type modifier

New columns can be added to an existing table using alter table and add.

alter table table_name add field_name type modifier

The following example uses the alter table to add a new column called price to the book table from the previous section. Data can be entered into the new column using update command.

We can do the same inserting with SQL Gate by running sql script from SQL Query Editor:

Then, we see the data we just put shown in the table.

For other data, we do the same thing.

all data values in a table column can be changed using update command with set:
update table_name set field_name = new_value;

Individual column values can be changed by adding a qualifier to the syntax with where:
update table_name set field_name = new_value where id = int;

SQL allows the data to be queried for specific information. For example, books published after 2005 can be found with this:

Records can be deleted from a table using delete from command followed by the table name. So, the command delete from book; would remove all the records from the book table.

Portion of records can be deleted from a table by adding a where qualifier to the delete from command to identify one or more rows.

Specific columns can be deleted from a table using alter table command with drop keyword.

The whole table can be deleted from a database using drop table command.

An entire database can be deleted with the command drop database.