PHP and MySQL Programming/Basic Queries

Introduction
Here are a few basic queries to get you going. For more detailed information, have a look at the online documentation on the MySQL website http://dev.mysql.com/doc/refman/5.1/en/index.html.

SELECT
The SELECT query is the standard means by which to retrieve information from the database. The syntax for the SELECT query is as follows:

mysql> SELECT field1, field2, ...        FROM table_name [WHERE condition1 AND condition2 ...       [ORDER BY field1, field2, etc...] [LIMIT number]];

Example:

mysql> SELECT * FROM books; mysql> SELECT Title, Author FROM books WHERE Year > 2001 AND Year < 2006 ORDER BY Author ASC LIMIT 100;

INSERT
The INSERT statement is used to insert data into a particular table of a database. The Syntax is as follows:

mysql> INSERT INTO table_name (field1, field2, etc...) VALUES ('val1', 'val2', etc...);

Example:

mysql> INSERT INTO books (ISBN, Title, Author, Year) VALUES ('1234567890', 'Programming PHP and MySQL', 'Poisson, R.W', 2006);

DELETE
The DELETE statement is used to remove row(s) from a table. The syntax is as follows:

mysql> DELETE FROM table_name [WHERE condition1, condition2 etc...];

Example:

mysql> DELETE FROM books WHERE ISBN = '1234567890';

ALTER
ALTER is used to modify the structure of a table. Here is the syntax for ALTER:

ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ...   alter_specification: ADD [COLUMN] column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (column_definition,...) | ADD INDEX [index_name] [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name,...) | ADD FULLTEXT [INDEX] [index_name] (index_col_name,...) [WITH PARSER parser_name] | ADD SPATIAL [INDEX] [index_name] (index_col_name,...) | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name] | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP INDEX index_name | DROP FOREIGN KEY fk_symbol | DISABLE KEYS | ENABLE KEYS | RENAME [TO] new_tbl_name | ORDER BY col_name | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name] | DISCARD TABLESPACE | IMPORT TABLESPACE | table_options | partition_options | ADD PARTITION (partition_definition) | DROP PARTITION partition_names | COALESCE PARTITION number | REORGANIZE PARTITION partition_names INTO (partition_definitions) | ANALYZE PARTITION partition_names | CHECK PARTITION partition_names | OPTIMIZE PARTITION partition_names | REBUILD PARTITION partition_names | REPAIR PARTITION partition_names | REMOVE PARTITIONING

DROP
DROP is used to completely remove a table or database. The syntax is as follows:

mysql> DROP table_name;

TRUNCATE
TRUNCATE is used when you want to remove the data, but not the structure of a table. The syntax is as follows:

mysql> TRUNCATE table_name;