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

[edit | edit source ]

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

[edit | edit source ]

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

[edit | edit source ]

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

[edit | edit source ]

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