Archive:PHP and MySQL Programming/Basic Queries

TOC

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`
       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`;