Revision as of 09:20, 28 February 2012

SQL Orientation

SQL Databases are made up of tables. Tables are created by the developer or architect and are empty on creation. Similar to a spreadsheet, a table's properties are defined by its columns. Columns can be different data types (strings, integers, booleans, floats, binary, etc).

Basic Database Navigation

MySQL Navigation

The MySQL developers made database navigation fairly intuitive. Show databases: Displays a list of databases that the current user has access to SHOW DATABASES; Show tables [in ...]: Displays a list of table names in the currently selected database (if no database was specified), or a list of tables in the specified database SHOW TABLES IN information_schema; Show fields in [table_name]: Displays a list of column names in the chosen table: SHOW FIELDS IN information_schema.routines;

PostgreSQL

\l             - Lists all databases
\dn            - Lists all schemas in the current database
\dt            - Lists all tables in the current database
\d [table_name]- Lists columns in table in the current database

Basic Queries

SELECT - Select data from a table

SELECT statements can contain clauses such as "WHERE", "LIMIT", "ORDER BY" and "GROUP BY" to find values that match specified patterns and filter results sets.

• The basic syntax of a SELECT statement:

SELECT [column_name(s)] FROM [database_name(s)].[TABLE_NAME(s)] WHERE [condition] GROUP BY [column_name] ORDER BY [column_name] [ASC|DESC] LIMIT [ROW COUNT] OFFSET [START ON ROW]

For example, let's do a simple SELECT query on the following table (named "People" for our example).

 +----------------------+
 |ID| NAME     |STATE   |
 +----------------------+
 |1 | John Doe |New York|
 +--+----------+--------+
 |2 | Jane Doe |Florida |
 +--+----------+--------+

Let's perform a SELECT Query on our "People" Table, for the column "state".

SELECT state FROM people;

You should get an output similar to the following:

 +---------+
 |STATE    |
 +---------+
 |New York |
 +---------+
 |Florida  |
 +---------+

Lets demonstrate the use of a simple WHERE clause.

SELECT name FROM people WHERE id > 3; 

The above command would logically would be "select the name, from the rows in people where id is greater than three."; Which in this case would return nothing, because we only have IDs 1 and 2.

Let's say we added some new data to our table. it now looks like this:

+----------------------+
|ID| NAME      |STATE   |
+--------------+-------+
|1 | John Doe  |New York|
+--+-----------+--------+
|2 | Jane Doe  |Florida |
+--+-----------+--------+
|3 | Billy Bob |New York|
+--+-----------+--------+

The GROUP BY clause, groups results by column, and returns rows with unique values in the secified column.

 
SELECT name FROM people WHERE id > 0 GROUP BY state;
 

would output

+-----------+
| NAME      |
+-----------+
| John Doe  |
+-----------+
| Jane Doe  |
+-----------+

The order by clause will order the rows, by a value in a column, ascending or descending (ASC/DESC).

If we have this table:

+------------------------+
|AGE| NAME     | STATE   |
+--------------+---------+
|22 | John Doe | New York|
+--+-----------+---------+
|31 | Billy Bob |New York|
+--+-----------+---------+
|26 | Jane Doe | Florida |
+--+-----------+---------+

and we ran the command

 
SELECT * FROM people ORDER BY age ASC;
 

the output would show

+------------------------+
|AGE| NAME     | STATE   |
+--------------+---------+
|22 | John Doe | New York|
+--+-----------+---------+
|26 | Jane Doe | Florida |
+--+-----------+---------+
|31 | Billy Bob |New York|
+--+-----------+---------+

The LIMIT clause, is very simple. it limits your results.

 
SELECT * FROM state WHERE age > 22 LIMIT 1;
 

Would return the following:

+------------------------+
|AGE| NAME     | STATE   |
+--+-----------+---------+
|26 | Jane Doe | Florida |
+--+-----------+---------+

Now that we know how the Limit, Order By, Group By, and Where clauses work, let's roll it all into one.

If we ran this command on our current table:

SELECT * FROM people WHERE age < 31 GROUP BY state ORDER BY age LIMIT 1;

+--------------+---------+
|22 | John Doe | New York|
+--+-----------+---------+

UPDATE - Modify rows in a table

The UPDATE command is used to update specific rows in a table with a new value. It has the ability to alter a large amount of data with a single query, and as such can be a very dangerous command when access to it is granted to the wrong people.

For example:

UPDATE customers SET age=20 WHERE name='Richard'

This will set the value of the 'name' row in the 'age' column to 20 wherever 'name' is 'Richard'.

Executing this query in an interactive environment will return the number of rows that were altered. If the WHERE clause is omitted, every row in the named table will be edited in accordance with this query.

Updating multiple columns

It is possible to alter the contents of multiple columns in a table with a single UPDATE query in the following manner:

UPDATE customer SET name='Richard' AND age='20' AND paid='yes' WHERE id='4'

INSERT - Add rows to a table

The basic format of the INSERT statement is:

INSERT INTO TABLE (COLUMN, COLUMN, COLUMN) VALUES (VALUE, VALUE, VALUE)

The number of columns and values must be the same.

It is similar to the UPDATE statement in that it allows you to alter the contents of entries in a table. However, the INSERT statement allows you to add a new row to the table specified, inserting data into whichever columns you choose (with a minimum of one) when you initialise it. Any columns not specified are simply left blank.

For example:

INSERT INTO customers (name, age, paid) VALUES ('Richard', '23', 'yes')

DELETE - Delete rows from a table

The format of the DELETE statement is:

DELETE FROM TABLE WHERE COLUMN=VALUE

This will delete a row from a table where the column is equal to the value specified. It is relatively simple to use, for example:

DELETE FROM customers WHERE age='20'