MySQL/Queries

All of the following queries need a table selected to work.

Select

Grabs one or more records from a database table on success. If the query has no matches, then it returns nothing.

The most basic SELECT statement, which grabs all records from a table:

SELECT * FROM <table_name>;

Ex, this query gets all data from the "adoption_puppies" table:

SELECT * FROM adoption_puppies;

To only pull data from one or more specific table columns:

SELECT <columns> FROM <table_name>;

Ex, this query gets only "name" and "breed" data from the "adoption_puppies" table:

SELECT name, breed FROM adoption_puppies;

To limit the number of records that return (using some criteria), we can add a WHERE clause:

SELECT <columns> FROM <table_name> WHERE <where_clause>;

Ex, this query gets only "name" and "age" data for all Golden Retrievers from the "adoption_puppies" table:

SELECT name, age, FROM adoption_puppies WHERE breed == "Golden Retriever";

Creates a new record in a table.

Note that the ordering of fields matter. That is, the ordering of the field names should match the ordering of the values for each respective field.

INSERT INTO <table_name> ( <field_1>, <field_2>, ..., <field_n>)
VALUES ( <field_1_value>, <field_2_value>, ..., <field_n_value> );

Ex:

INSERT INTO adoption_puppies ( name, age )
VALUES ( "Spot", "3 Months" );

Updates an existing record in a table.

UPDATE <table_name>
SET <updated_fields>
WHERE <where_clause>;

Ex:

UPDATE adoption_puppies
SET age = "6 Months", name = "Spot"
WHERE name = "Spot", age = "3 Months";

To update one or more fields of all records in a table, skip the WHERE clause:

UPDATE adoption_puppies
SET adopted = 1;

Removes one or more records in a table.

Warn: Be very careful with delete queries. They cannot be undone! Consider backing up your database beforehand, if dealing with important data.

DELETE FROM <table_name> WHERE <where_clause>;

Ex:

DELETE FROM adoption_puppies WHERE name == "Spot";

To delete all records from a table, skip the where clause:

DELETE FROM <table_name>;