Archive:PHP and MySQL Programming/Basic Queries

2007-03-01T07:37:17Z

122.164.138.21: Insert

[[Programming:PHP and MySQL|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 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;

[[Category:PHP]]
[[Category:MySQL]]

IdeaWazaWiki - User contributions [en]

Archive:PHP and MySQL Programming/Basic Queries