Common MySQL Statements
Short reference to some useful MySQL statements to create and update table structures (metadata) and do some common queries, updates and deletions from tables.


Creating a domain
CREATE DOMAIN AS <domain_name> AS CHAR(5);

Managing Table Structure

Creating tables

CREATE TABLE <table_name> ( <attribute_name> <attribute_type> <”” | PRIMARY KEY | NOT NULL > );

Deleting tables

DROP TABLE <table_name>;

Deleting columns

ALTER TABLE <table_name> DROP <column_name>

Adding columns

ALTER TABLE <table_name> ADD <attribute_name> <attribute_type> <conditions> ; 

Managing data

Deleting rows

DELETE FROM <table_name> ;

*Deletes all rows from the table

DELETE FROM <table_name> 
WHERE <attribute_name> <condition> <value> ;

*Deletes rows that match condition

Inserting rows

INSERT INTO <table_name> ( <attributeAname>, ... , <attributeName> )
VALUES ( '<attributeA_value>', .... , '<attributeZ_value>' );

Updating Rows

UPDATE <table_name> SET <attribute_name> = <value> 
WHERE <attribute_name> <condition> <value> ;

Searching Rows (basic)

SELECT < * | <attribute_list > 
ROM <table_name> 
WHERE <search_condition> 
[ ORDER BY <attribute> ];