View source for Archive: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 = '123456789ĞÁ0';

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

[[../Creating a Table/|Previous: Creating a Table]] | [[../|TOC]] | [[../Common Functions and Operators/|Next: Common Functions and Operators]]

[[Category:PHP and MySQL Programming|{{SUBPAGENAME}}]]